USP_BBNC_ADDCAMPAIGNSTOREVENUE

Adds campaigns to revenue created for Blackbaud Internet Solutions, by defaulting based on the designations assigned to that revenue item.
Parameters

Parameter	Parameter Type	Mode
@REVENUEID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CHANGEDATE	datetime	IN
@CAMPAIGNS	xml	IN
Definition

Copy


            CREATE procedure dbo.USP_BBNC_ADDCAMPAIGNSTOREVENUE
            (
                @REVENUEID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier,
                @CHANGEDATE datetime,
                @CAMPAIGNS xml = null
            )
            as
            set nocount on;

                begin try
                    if(    select count(REVENUESPLITCAMPAIGN.ID) 
                        from dbo.REVENUESPLIT 
                        inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUESPLIT.ID 
                        where REVENUESPLIT.REVENUEID = @REVENUEID) > 0
                    begin
                        raiserror('ERR_ARGUMENT_ALREADYHASCAMPAIGNS', 13, 1)
                    end


                    --insert those campaigns which are passed as parameters

                    if(@CAMPAIGNS is not null)
                    begin
                        declare @CAMPAIGNTABLE as table
                        (
                            CAMPAIGNID  uniqueidentifier ,
                            CAMPAIGNSUBPRIORITYID  uniqueidentifier 
                        )            


                        insert into @CAMPAIGNTABLE(CampaignId,CAMPAIGNSUBPRIORITYID)
                        select  
                            CAMP.C.value('CAMPAIGNID[1]', 'uniqueidentifier'),  
                            CAMP.C.value('CAMPAIGNSUBPRIORITYID[1]', 'uniqueidentifier')
                        FROM   @CAMPAIGNS.nodes('//CAMPAIGNS/ITEM') CAMP(C)

                        -- Populate revenuesplitcampaigns table with campaigns associated with this event.

                        insert into dbo.REVENUESPLITCAMPAIGN
                        (
                            REVENUESPLITID,
                            CAMPAIGNID,
                            CAMPAIGNSUBPRIORITYID,
                            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                        )

                        select
                            REVENUESPLIT.ID,
                            CAMPAIGNTABLE.CAMPAIGNID,
                            CAMPAIGNTABLE.CAMPAIGNSUBPRIORITYID,
                            @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
                        from @CAMPAIGNTABLE as CAMPAIGNTABLE
                            inner join dbo.CAMPAIGN on CAMPAIGNTABLE.CAMPAIGNID = CAMPAIGN.ID
                            inner join dbo.REVENUESPLIT as REVENUESPLIT on REVENUESPLIT.REVENUEID = @REVENUEID
                        where
                            CAMPAIGN.ISACTIVE=1
                            and CAMPAIGN.ID not in (select CAMPAIGNID from dbo.REVENUESPLITCAMPAIGN where REVENUESPLITID = REVENUESPLIT.ID); -- not already added by Designation Campaigns above                


                end                
                    exec dbo.USP_REVENUE_ADDCAMPAIGNS @REVENUEID, @CHANGEAGENTID, @CHANGEDATE                
                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR
                    return 1
                end catch