USP_GLOBALCHANGE_ADDCONSTITUENTSITE

            CREATE procedure dbo.USP_GLOBALCHANGE_ADDCONSTITUENTSITE
            (
                @IDSETREGISTERID uniqueidentifier, 
                @SITEID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @ASOF as datetime = null,
                @REMOVEUNQUALIFIED bit,
                @NUMBERADDED int = 0 output,
                @NUMBEREDITED int = 0 output,
                @NUMBERDELETED int = 0 output,
                @CURRENTAPPUSERID uniqueidentifier = null
            )
            as            
                set nocount off;

                declare @CURRENTDATE datetime

                set @CURRENTDATE = getdate();
                set @NUMBERADDED = 0;
                set @NUMBEREDITED = 0;
                set @NUMBERDELETED = 0; 

                declare @BYPASSSECURITY bit;
                declare @BPID uniqueidentifier;
                declare @BYPASSSITESECURITY bit;
                declare @USERHASSITEACCESS bit;

                set @BPID = '3269A1D1-31CB-4D28-945C-B7623A3EFCCA';

                set @USERHASSITEACCESS = dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_FORSITE(@CURRENTAPPUSERID, @BPID, @SITEID)

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

                -- If the user should not bypass security and does not have rights to the chosen site, exit the procedure.

                if @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);

                if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
                begin 
                    insert into @SELECTION (ID) 
                    select 
                        SELECTEDCONSTITUENTS.ID 
                    from 
                        dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID) SELECTEDCONSTITUENTS
                        left join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@CURRENTAPPUSERID, @BPID) as CONSTIT_RACS on SELECTEDCONSTITUENTS.ID = CONSTIT_RACS.ID

                    where
                        (@BYPASSSECURITY = 1 or CONSTIT_RACS.ID is not null)
                        and 
                        (
                            @BYPASSSITESECURITY = 1 or 
                            exists 
                            (
                                select 1 
                                from dbo.CONSTITUENTSITE
                                where CONSTITUENTSITE.CONSTITUENTID = SELECTEDCONSTITUENTS.ID 
                                and dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_FORSITE(@CURRENTAPPUSERID, @BPID, CONSTITUENTSITE.SITEID) = 1
                            )
                        )
                end
                else
                begin
                    insert into @SELECTION (ID) select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID);                
                end

                --AdamBu - Bug 40526 - If the selection used is static, make sure none of the records it contains have been deleted.

                if exists(
                    select 1
                    from dbo.IDSETREGISTER
                    where ID = @IDSETREGISTERID and STATIC = 1
                )
                begin
                    delete @SELECTION
                    where ID not in(
                        select ID from CONSTITUENT
                    )
                end


                begin try
                    if @REMOVEUNQUALIFIED = 1 
                    begin
                        declare @CONTEXTCACHE varbinary(128);

                        /* Cache current context information@ */
                        set @CONTEXTCACHE = CONTEXT_INFO();

                        /* Set CONTEXT_INFO to @CHANGEAGENTID */
                        set CONTEXT_INFO @CHANGEAGENTID;

                        /* delete records */
                        if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
                        begin
                            delete dbo.CONSTITUENTSITE 
                            from dbo.CONSTITUENTSITE
                                left join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@CURRENTAPPUSERID, @BPID) as CONSTIT_RACS on CONSTITUENTSITE.CONSTITUENTID = CONSTIT_RACS.ID
                            where 
                                CONSTITUENTSITE.SITEID = @SITEID 
                                and CONSTITUENTSITE.CONSTITUENTID not in(select ID from @SELECTION)
                                and (@BYPASSSECURITY = 1 or CONSTIT_RACS.ID is not null)
                                and (@BYPASSSITESECURITY = 1 or @USERHASSITEACCESS = 1)
                        end
                        else
                        begin
                            delete from dbo.CONSTITUENTSITE where CONSTITUENTSITE.SITEID = @SITEID and CONSTITUENTSITE.CONSTITUENTID not in(select ID from @SELECTION)
                        end

                        set @NUMBERDELETED = @@ROWCOUNT;

                        /* Reset CONTEXT_INFO to previous value */
                        if not @contextCache is null
                            set CONTEXT_INFO @CONTEXTCACHE;
                    end

                    insert into dbo.CONSTITUENTSITE(ID, CONSTITUENTID, SITEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED) 
                    select newid(), SELECTION.ID, @SITEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE from @SELECTION as SELECTION where SELECTION.ID not in(select CONSTITUENTID from dbo.CONSTITUENTSITE where CONSTITUENTSITE.SITEID = @SITEID);

                    set @NUMBERADDED = @@ROWCOUNT;
                end try

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