USP_GLOBALCHANGE_CHANGERELATIONSHIPMANAGER

            CREATE procedure dbo.USP_GLOBALCHANGE_CHANGERELATIONSHIPMANAGER
            (
                @IDSETREGISTERID uniqueidentifier, 
                @CURRENTFUNDRAISERID uniqueidentifier,
                @NEWFUNDRAISERID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @ASOF as datetime = null,
                @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
                    declare @CONTEXTCACHE varbinary(128);

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

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


                    if @NEWFUNDRAISERID is null
                    begin

                        /* delete records */
                        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 = @CURRENTFUNDRAISERID 
                            and (RELATIONSHIPMANAGER.CONSTITUENTID in (select ID from @SELECTION))
                            and (@BYPASSSECURITY = 1 or CONSTIT_RACS.ID is not null)
                            and (
                                @BYPASSSITESECURITY = 1 
                                or exists (
                                    select 1 
                                    from dbo.CONSTITUENT
                                    left join dbo.CONSTITUENTSITE on CONSTITUENTSITE.CONSTITUENTID = CONSTITUENT.ID
                                    where CONSTITUENT.ID = RELATIONSHIPMANAGER.CONSTITUENTID 
                                        and dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_FORSITE(@CURRENTAPPUSERID, @BPID, CONSTITUENTSITE.SITEID) = 1
                                )
                            );

                        set @NUMBERDELETED = @@ROWCOUNT;
                    end                    
                    else
                    begin
                        /* delete records where both managers exist*/
                        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 = @CURRENTFUNDRAISERID 
                            and (@IDSETREGISTERID is null or 
                                    RELATIONSHIPMANAGER.CONSTITUENTID in (select ID from @SELECTION))
                            and exists(select top 1 ID from dbo.RELATIONSHIPMANAGER SUB
                                        where SUB.FUNDRAISERID = @NEWFUNDRAISERID and 
                                            SUB.CONSTITUENTID = RELATIONSHIPMANAGER.CONSTITUENTID)
                            and (@BYPASSSECURITY = 1 or CONSTIT_RACS.ID is not null)
                            and (
                                @BYPASSSITESECURITY = 1 
                                or exists (
                                    select 1 
                                    from dbo.CONSTITUENT --JamesWill WI170986 2011-08-24 Make sure to check if null site is allowed for constituents with no entries in CONSTITUENTSITE

                                    left join dbo.CONSTITUENTSITE on CONSTITUENTSITE.CONSTITUENTID = CONSTITUENT.ID
                                    where CONSTITUENT.ID = RELATIONSHIPMANAGER.CONSTITUENTID 
                                        and dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_FORSITE(@CURRENTAPPUSERID, @BPID, CONSTITUENTSITE.SITEID) = 1
                                )
                            );

                        set @NUMBERDELETED = @@ROWCOUNT;

                        update dbo.RELATIONSHIPMANAGER
                        set FUNDRAISERID = @NEWFUNDRAISERID
                        from dbo.RELATIONSHIPMANAGER 
                            left join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@CURRENTAPPUSERID, @BPID) as CONSTIT_RACS on RELATIONSHIPMANAGER.CONSTITUENTID = CONSTIT_RACS.ID
                        where FUNDRAISERID = @CURRENTFUNDRAISERID
                            and (@IDSETREGISTERID is null
                                or CONSTITUENTID in (select ID from @SELECTION))
                            and (@BYPASSSECURITY = 1 or CONSTIT_RACS.ID is not null)
                            and (
                                @BYPASSSITESECURITY = 1 
                                or exists (
                                    select 1 
                                    from dbo.CONSTITUENT --JamesWill WI170986 2011-08-24 Make sure to check if null site is allowed for constituents with no entries in CONSTITUENTSITE

                                    left join dbo.CONSTITUENTSITE on CONSTITUENTSITE.CONSTITUENTID = CONSTITUENT.ID
                                    where CONSTITUENT.ID = RELATIONSHIPMANAGER.CONSTITUENTID 
                                        and dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_FORSITE(@CURRENTAPPUSERID, @BPID, CONSTITUENTSITE.SITEID) = 1
                                )
                            );

                        set @NUMBEREDITED = @@ROWCOUNT;
                    end


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

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