USP_GLOBALCHANGE_MOVEREVENUESPLITCAMPAIGN

            CREATE procedure dbo.USP_GLOBALCHANGE_MOVEREVENUESPLITCAMPAIGN
            (
                @SOURCECAMPAIGNID uniqueidentifier = null,
                @TARGETCAMPAIGNID uniqueidentifier = null,
                @SUBPRIORITYMAPPING xml = null,
                @ASOF datetime = null,
                @NUMBERADDED int = 0 output,
                @NUMBEREDITED int = 0 output,
                @NUMBERDELETED int = 0 output,
                @CHANGEAGENTID uniqueidentifier = null,
                @CURRENTAPPUSERID uniqueidentifier = null
            )
            as
                set nocount off;

                begin try
                    set @NUMBERADDED = 0
                    set @NUMBEREDITED = 0
                    set @NUMBERDELETED = 0

                    if @SOURCECAMPAIGNID = @TARGETCAMPAIGNID
                        raiserror('BBERR_MOVEREVENUESPLITSCAMPAIGN_SOURCESAMEASTARGET',13,1);

                    declare @ISSYSADMIN bit;
                    declare @BPID uniqueidentifier;

                    set @ISSYSADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
                    set @BPID = '3269A1D1-31CB-4D28-945C-B7623A3EFCCA';

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

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

                    update
                        dbo.REVENUESPLITCAMPAIGN
                    set
                        REVENUESPLITCAMPAIGN.CAMPAIGNID = @TARGETCAMPAIGNID,
                        REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID = MAPPEDSUBPRIORITIES.CAMPAIGNSUBPRIORITYID,
                        REVENUESPLITCAMPAIGN.CHANGEDBYID = @CHANGEAGENTID,
                        REVENUESPLITCAMPAIGN.DATECHANGED = @CURRENTDATE
                    from 
                        dbo.REVENUESPLITCAMPAIGN as OUTERREVENUESPLITCAMPAIGN
                        inner join 
                        (
                            select
                                REVENUESPLITCAMPAIGN.ID as REVENUESPLITCAMPAIGNID,
                                SUBPRIORITYMAPPING.TARGETSUBPRIORITYID as CAMPAIGNSUBPRIORITYID
                            from
                                dbo.REVENUESPLITCAMPAIGN
                            left join (
                                select
                                    T.c.value('(SOURCESUBPRIORITYID)[1]','uniqueidentifier') AS 'SOURCESUBPRIORITYID',
                                    T.c.value('(TARGETSUBPRIORITYID)[1]','uniqueidentifier') AS 'TARGETSUBPRIORITYID'
                                from
                                    @SUBPRIORITYMAPPING.nodes('/SUBPRIORITYMAPPING/ITEM') T(c)
                            ) as SUBPRIORITYMAPPING on REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID = SUBPRIORITYMAPPING.SOURCESUBPRIORITYID or (REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID is null and SUBPRIORITYMAPPING.SOURCESUBPRIORITYID is null)
                            where
                                REVENUESPLITCAMPAIGN.CAMPAIGNID = @SOURCECAMPAIGNID
                                and 
                                (
                                    @ISSYSADMIN = 1
                                    or exists
                                    (
                                        select 1 
                                        from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLITCAMPAIGN.REVENUESPLITID) REVSITES
                                        where dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_FORSITE(@CURRENTAPPUSERID, @BPID, REVSITES.SITEID) = 1
                                    )
                                )

                        ) as MAPPEDSUBPRIORITIES on OUTERREVENUESPLITCAMPAIGN.ID = MAPPEDSUBPRIORITIES.REVENUESPLITCAMPAIGNID

                    -- Prevent duplicate rows from being generated

                    and not exists (    select 1 
                                        from dbo.REVENUESPLITCAMPAIGN
                                        where
                                            OUTERREVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUESPLITCAMPAIGN.REVENUESPLITID and
                                            REVENUESPLITCAMPAIGN.CAMPAIGNID = @TARGETCAMPAIGNID and
                                            (REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID = MAPPEDSUBPRIORITIES.CAMPAIGNSUBPRIORITYID or
                                            (REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID is null and MAPPEDSUBPRIORITIES.CAMPAIGNSUBPRIORITYID is null))
                                    );

                    set @NUMBEREDITED = @@ROWCOUNT

                    -- Delete any rows that are still associated with the source campaign since

                    -- they should only remain if they would have caused a duplicate row

                    declare @contextCache varbinary(128);

                    --cache current context information

                    set @contextCache = CONTEXT_INFO();

                    --set CONTEXT_INFO to @CHANGEAGENTID

                    set CONTEXT_INFO @CHANGEAGENTID;

                    delete from dbo.REVENUESPLITCAMPAIGN
                    where 
                        CAMPAIGNID = @SOURCECAMPAIGNID
                        and
                        (
                            @ISSYSADMIN = 1
                            or exists
                            (
                                select 1 
                                from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLITCAMPAIGN.REVENUESPLITID) REVSITES
                                where dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_FORSITE(@CURRENTAPPUSERID, @BPID, REVSITES.SITEID) = 1
                            )
                        )

                    set @NUMBERDELETED = @@ROWCOUNT

                    --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