USP_GLOBALCHANGE_ADDRELATIONSHIPMANAGER

            CREATE procedure dbo.USP_GLOBALCHANGE_ADDRELATIONSHIPMANAGER
            (
                @IDSETREGISTERID uniqueidentifier, 
                @FUNDRAISERID 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 @BPID uniqueidentifier = '3269A1D1-31CB-4D28-945C-B7623A3EFCCA';

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

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

                declare @SELECTION table (ID uniqueidentifier not null primary key);                
                insert into @SELECTION (ID) 
                select ID 
                from dbo.UFN_CONSTITUENT_GETRECORDSINSELECTION_FORBUSINESSPROCESS(@CURRENTAPPUSERID, @IDSETREGISTERID, @BPID, @BYPASSSECURITY, @BYPASSSITESECURITY);

                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 */
                        -- Only delete for constituents that the user would have rights to, but that weren't included in the selection.

                        delete dbo.RELATIONSHIPMANAGER
                        from dbo.RELATIONSHIPMANAGER 
                            left join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@CURRENTAPPUSERID, @BPID) as CONSTIT_RACS on RELATIONSHIPMANAGER.CONSTITUENTID = CONSTIT_RACS.ID
                        where RELATIONSHIPMANAGER.FUNDRAISERID = @FUNDRAISERID 
                            and RELATIONSHIPMANAGER.CONSTITUENTID not in (select ID from @SELECTION)
                            and (@BYPASSSECURITY = 1 or CONSTIT_RACS.ID is not null)
                            and (
                                @BYPASSSITESECURITY = 1 
                                or exists (
                                    select 1 
                                    from dbo.CONSTITUENTSITE
                                    where CONSTITUENTSITE.CONSTITUENTID = RELATIONSHIPMANAGER.CONSTITUENTID 
                                        and dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_FORSITE(@CURRENTAPPUSERID, @BPID, CONSTITUENTSITE.SITEID) = 1
                                )
                            );
                        set @NUMBERDELETED = @@ROWCOUNT;

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

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

                    set @NUMBERADDED = @@ROWCOUNT;
                end try

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