USP_GLOBALCHANGE_ADDCONSTITUENTSOLICITICODE

        CREATE procedure dbo.USP_GLOBALCHANGE_ADDCONSTITUENTSOLICITICODE
        (
                @IDSETREGISTERID uniqueidentifier = null, 
                @SOLICITCODEID uniqueidentifier  = null,
                @STARTDATE datetime =null,
                @ENDDATE datetime=null,
                @COMMENTS nvarchar(100) = null,
                @CHANGEAGENTID uniqueidentifier = null,
                @ASOF as datetime = null,
                @NUMBERADDED int = 0 output,
                @NUMBEREDITED int = 0 output,
                @NUMBERDELETED int = 0 output,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @CONSENTPREFERENCECODE tinyint = 0,
                @SOURCEEVIDENCECODEID uniqueidentifier = null,
                @SOURCEFILE nvarchar(260) = null,
                @PRIVACYPOLICY nvarchar(260) = null,
                @SUPPORTINGINFORMATION nvarchar(max) = null,
                @CONSENTSTATEMENT nvarchar(max) = null
            )
            as
                set nocount off;

                declare @CURRENTDATE datetime = getdate();

                set @NUMBERADDED = 0;
                set @NUMBEREDITED = 0;
                set @NUMBERDELETED = 0; 

                declare @BPID uniqueidentifier = '3269A1D1-31CB-4D28-945C-B7623A3EFCCA';
                declare @BYPASSSECURITY bit;
                declare @BYPASSSITESECURITY bit;
                declare @USERHASSITEACCESS bit;
                declare @SITEID uniqueidentifier;

                exec dbo.USP_SECURITY_APPUSER_BYPASSSECURITYFORBUSINESSPROCESS @CURRENTAPPUSERID, @BPID, @BYPASSSECURITY output, @BYPASSSITESECURITY output;

                select @SITEID = SITEID from dbo.SOLICITCODE where ID = @SOLICITCODEID;
                set @USERHASSITEACCESS = dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_FORSITE(@CURRENTAPPUSERID, @BPID, @SITEID)

                -- If the user should not bypass security and does not have rights to the given solicit code, exit the procedure.

                if @BYPASSSECURITY = 0 and @BYPASSSITESECURITY = 0 and @USERHASSITEACCESS = 0
                    return 0;

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

                declare @SELECTION table (ID uniqueidentifier not null primary key)

                begin try
                    -- retrieve records that the user can add solicit codes to

                    insert into @SELECTION(ID)
                        select ID from dbo.UFN_CONSTITUENT_GETRECORDSINSELECTION_FORBUSINESSPROCESS(@CURRENTAPPUSERID, @IDSETREGISTERID, @BPID, @BYPASSSECURITY, @BYPASSSITESECURITY);

                    declare @CONSENT tinyint;
                    select @CONSENT = CONSENTCODE from SOLICITCODE where ID = @SOLICITCODEID

                    -- validate consent codes, close where the same code exists with an end date of yesterday

                    if @CONSENT <> 0
                    begin
                        declare @UPDATEROWS table
                        (
                          ROWNUMBER bigint,
                          CONSTITUENTSOLICITCODEID uniqueidentifier,
                          AUTOENDDATE date
                        );

                        declare @INPUT table
                        (
                          SOLICITCODEID uniqueidentifier,
                          NEW_STARTDATE date
                        );

                        -- Populate temp table with selected data

                        insert into @INPUT
                        select
                            @SOLICITCODEID AS 'SOLICITCODEID',
                            @STARTDATE AS 'STARTDATE';

                        insert into @UPDATEROWS
                        select
                          ROW_NUMBER() over (partition by CONSTITUENTSOLICITCODE.CONSTITUENTID, CONSTITUENTSOLICITCODE.SOLICITCODEID order by CONSTITUENTSOLICITCODE.STARTDATE desc) as ROWNUMBER,
                          CONSTITUENTSOLICITCODE.ID as CONSTITUENTSOLICITCODEID,
                          dateadd(day, -1, INPUT.NEW_STARTDATE) as AUTOENDDATE
                        from @INPUT INPUT
                        inner join dbo.CONSTITUENTSOLICITCODE on INPUT.SOLICITCODEID = CONSTITUENTSOLICITCODE.SOLICITCODEID
                        inner join dbo.SOLICITCODE on CONSTITUENTSOLICITCODE.SOLICITCODEID = SOLICITCODE.ID
                        where
                        CONSTITUENTSOLICITCODE.CONSTITUENTID in (select ID from @SELECTION)
                        and ( SOLICITCODE.CONSENTCODE = 1 
                              OR
                              SOLICITCODE.CONSENTCODE = 2) -- EU Consent / Advanced Consent

                        and CONSTITUENTSOLICITCODE.STARTDATE < INPUT.NEW_STARTDATE
                        and CONSTITUENTSOLICITCODE.ENDDATE is null

                        update dbo.CONSTITUENTSOLICITCODE
                        set
                            ENDDATE = AUTOENDDATE,
                            DATECHANGED = @CURRENTDATE,
                            CHANGEDBYID = @CHANGEAGENTID
                        from @UPDATEROWS UPDATEROWS
                        where
                            CONSTITUENTSOLICITCODE.ID = UPDATEROWS.CONSTITUENTSOLICITCODEID
                            and UPDATEROWS.ROWNUMBER = 1;

                        -- Mark associated mail preferences as Do not send when the ended solicit code is no longer active

                        update MAILPREFERENCE set
                          SENDMAIL = 0,
                          USESEASONALADDRESS = 0,
                          USEPRIMARYADDRESS = 0,
                          USEPRIMARYEMAIL = 0,
                          CHANGEDBYID = @CHANGEAGENTID,
                          DATECHANGED = @CURRENTDATE
                        from @UPDATEROWS UPDATEROWS
                          inner join dbo.MAILPREFERENCE on UPDATEROWS.CONSTITUENTSOLICITCODEID = MAILPREFERENCE.CONSTITUENTSOLICITCODEID
                        where UPDATEROWS.AUTOENDDATE < @CURRENTDATE;

                    end

                    insert into dbo.[CONSTITUENTSOLICITCODE]
                        ([ID],[SOLICITCODEID],[CONSTITUENTID],[STARTDATE],[ENDDATE],[COMMENTS],[SEQUENCE],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED],
                         [CONSENTPREFERENCECODE],[SOURCECODEID],[SOURCEFILEPATH],[PRIVACYPOLICYFILEPATH],[SUPPORTINGINFORMATION],[CONSENTSTATEMENT])
                    select
                        newid(),
                        @SOLICITCODEID,
                        selection.ID,
                        @STARTDATE,
                        @ENDDATE,
                        @COMMENTS,
                        (select coalesce(max(SEQUENCE),0)+1 from dbo.CONSTITUENTSOLICITCODE where CONSTITUENTID = selection.ID),
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @CURRENTDATE,
                        @CONSENTPREFERENCECODE,
                        @SOURCEEVIDENCECODEID,
                        @SOURCEFILE,
                        @PRIVACYPOLICY,
                        @SUPPORTINGINFORMATION,
                        @CONSENTSTATEMENT
                    from
                        @SELECTION selection
                    where not exists
                      (
                        select
                          1
                        from
                          dbo.CONSTITUENTSOLICITCODE
                        where
                          CONSTITUENTID = selection.ID and
                          SOLICITCODEID = @SOLICITCODEID and
                          dbo.UFN_DATES_AREDATESOVERLAPPING(@STARTDATE, @ENDDATE, STARTDATE, ENDDATE) = 1
                      );

                    set @NUMBERADDED = @@ROWCOUNT
                end try

                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch