USP_BBACONSTITUENTFILECONSTITUENCIESIMPORT

This procedure is used by the Target Analytics Constituent File Import Record Operation for saving the constituencies from a Target Analytics Constituent Import File to the database.
Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTID	uniqueidentifier	INOUT
@CONSTITUENCYCODEID	uniqueidentifier	INOUT
@CHANGEAGENTID	uniqueidentifier	IN
@DATEFROM	datetime	IN
@DATETO	datetime	IN
Definition

Copy


            CREATE procedure dbo.USP_BBACONSTITUENTFILECONSTITUENCIESIMPORT (
                @CONSTITUENTID uniqueidentifier output,
                @CONSTITUENCYCODEID uniqueidentifier output,                
                @CHANGEAGENTID uniqueidentifier,
                @DATEFROM datetime = null,
                @DATETO datetime = null
            ) as
                set nocount on;

                declare @CURRENTDATE datetime;
                set @CURRENTDATE = getdate();

                if @CHANGEAGENTID is null
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                begin try
                    declare @DATEFROM_UPDATED datetime;
                    declare @DATETO_UPDATED datetime;

                    with  CTE_ALLDATESFORCONSTITUENCY as (
                        select
                            @DATEFROM DATEFROM,
                            @DATETO DATETO
                        union
                        select
                            DATEFROM,
                            DATETO
                        from
                            dbo.CONSTITUENCY
                        where
                            CONSTITUENTID = @CONSTITUENTID and
                            CONSTITUENCYCODEID = @CONSTITUENCYCODEID and
                            (
                                ( DATETO   between @DATEFROM and @DATETO) or
                                (@DATETO   between  DATEFROM and  DATETO) or
                                ( DATEFROM between @DATEFROM and @DATETO) or
                                (@DATEFROM between  DATEFROM and  DATETO) or

                                ( DATEFROM is null and @DATEFROM <=  DATETO)   or
                                (@DATEFROM is null and  DATEFROM <= @DATETO)   or
                                ( DATETO   is null and @DATETO   >=  DATEFROM) or
                                (@DATETO   is null and  DATETO   >= @DATEFROM) or

                                (@DATEFROM is null and  DATEFROM is null) or
                                (@DATETO   is null and  DATETO   is null) or
                                (@DATEFROM is null and @DATETO   is null) or
                                ( DATEFROM is null and  DATETO   is null)
                            ))
                    select
                        @DATEFROM_UPDATED = nullif(min(isnull(DATEFROM,'1/1/1753')),'1/1/1753'),
                        @DATETO_UPDATED = nullif(max(isnull(DATETO,'12/31/9999')),'12/31/9999')
                    from
                        CTE_ALLDATESFORCONSTITUENCY;

                    delete
                        dbo.CONSTITUENCY
                    where
                        CONSTITUENTID = @CONSTITUENTID and
                        CONSTITUENCYCODEID = @CONSTITUENCYCODEID and
                        (
                            ( DATETO   between @DATEFROM and @DATETO) or
                            (@DATETO   between  DATEFROM and  DATETO) or
                            ( DATEFROM between @DATEFROM and @DATETO) or
                            (@DATEFROM between  DATEFROM and  DATETO) or

                            ( DATEFROM is null and @DATEFROM <=  DATETO)   or
                            (@DATEFROM is null and  DATEFROM <= @DATETO)   or
                            ( DATETO   is null and @DATETO   >=  DATEFROM) or
                            (@DATETO   is null and  DATETO   >= @DATEFROM) or

                            (@DATEFROM is null and  DATEFROM is null) or
                            (@DATETO   is null and  DATETO   is null) or
                            (@DATEFROM is null and @DATETO   is null) or
                            ( DATEFROM is null and  DATETO   is null)
                        );

                    insert into dbo.CONSTITUENCY (
                        CONSTITUENTID,
                        CONSTITUENCYCODEID,
                        DATEFROM,
                        DATETO,
                        ADDEDBYID,
                        CHANGEDBYID,
                        DATEADDED,
                        DATECHANGED
                    )
                    values (
                        @CONSTITUENTID,
                        @CONSTITUENCYCODEID,
                        @DATEFROM_UPDATED,
                        @DATETO_UPDATED,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @CURRENTDATE
                    );
                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;