USP_REVENUEBATCH_ADDITIONALAPPLICATIONWITHCHILDREN_UPDATEFROMXML

            CREATE procedure dbo.USP_REVENUEBATCH_ADDITIONALAPPLICATIONWITHCHILDREN_UPDATEFROMXML
            (
                @BATCHREVENUEID uniqueidentifier,
                @ADDITIONALAPPLICATIONSSTREAM xml,
                @DATE datetime,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null
            )

            as

            set nocount on;

            if @CHANGEAGENTID is null
                exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

            if @CHANGEDATE is null 
                set @CHANGEDATE = getdate()

            declare @ADDITIONALAPPLICATIONS table
            (
                ID uniqueidentifier,
                TYPECODE tinyint,
                APPLIED money,
                DESIGNATIONID uniqueidentifier, 
                OTHERTYPECODEID uniqueidentifier,
                DECLINESGIFTAID bit,
                ISGIFTAIDSPONSORSHIP bit,
                OPPORTUNITYID uniqueidentifier,
                SPONSORSHIPID uniqueidentifier,
                CATEGORYCODEID uniqueidentifier,
                DIDCAMPAIGNSDEFAULT bit,
                CAMPAIGNS xml,
                REVENUESPLITID uniqueidentifier
            );

            insert into @ADDITIONALAPPLICATIONS(ID, TYPECODE, APPLIED, DESIGNATIONID, OTHERTYPECODEID, DECLINESGIFTAID, ISGIFTAIDSPONSORSHIP, OPPORTUNITYID, SPONSORSHIPID, CATEGORYCODEID, DIDCAMPAIGNSDEFAULT, CAMPAIGNS, REVENUESPLITID)
                select
                    case when ID = '00000000-0000-0000-0000-000000000000' then newid() else coalesce(ID, newid()) end ID,
                    TYPECODE,
                    APPLIED,
                    DESIGNATIONID, 
                    OTHERTYPECODEID,
                    DECLINESGIFTAID,
                    coalesce(ISGIFTAIDSPONSORSHIP, 0),
                    OPPORTUNITYID,
                    SPONSORSHIPID,
                    CATEGORYCODEID,
                    DIDCAMPAIGNSDEFAULT,
                    cast((select T.c.query('CAMPAIGNS/ITEM') from CAMPAIGNS.nodes('/') T(c)) as xml) as CAMPAIGNS,
          REVENUESPLITID
                from dbo.UFN_REVENUEBATCH_GETADDITIONALAPPLICATIONS_FROMITEMLISTXML(@ADDITIONALAPPLICATIONSSTREAM);

            if exists (select 1 from @ADDITIONALAPPLICATIONS where DESIGNATIONID is null)    
                raiserror('Please enter at least one designation.', 13, 1);

            set @ADDITIONALAPPLICATIONSSTREAM =
                (
                    select ID, TYPECODE, APPLIED, DESIGNATIONID, OTHERTYPECODEID, DECLINESGIFTAID, ISGIFTAIDSPONSORSHIP, OPPORTUNITYID, SPONSORSHIPID, CATEGORYCODEID, DIDCAMPAIGNSDEFAULT, CAMPAIGNS, REVENUESPLITID
                    from @ADDITIONALAPPLICATIONS
                    for xml raw('ITEM'),type,elements,root('ADDITIONALAPPLICATIONSSTREAM'),binary base64
                );

            exec dbo.USP_REVENUEBATCH_GETADDITIONALAPPLICATIONS_UPDATEFROMXML @BATCHREVENUEID, @ADDITIONALAPPLICATIONSSTREAM, @CHANGEAGENTID, @CHANGEDATE;

            -- Add campaigns

            declare @BATCHREVENUEADDITIONALAPPLICATIONID uniqueidentifier, @CAMPAIGNS xml, @DIDCAMPAIGNSDEFAULT bit, @DESIGNATIONID uniqueidentifier
            declare CAMPAIGNCURSOR cursor local fast_forward for 
            select
                ID,
                CAMPAIGNS,
                coalesce(DIDCAMPAIGNSDEFAULT, 0),
                DESIGNATIONID
            from
            dbo.UFN_REVENUEBATCH_ADDITIONALAPPLICATIONWITHCHILDREN_FROMITEMLISTXML(@ADDITIONALAPPLICATIONSSTREAM)

            open CAMPAIGNCURSOR

            fetch next from CAMPAIGNCURSOR into @BATCHREVENUEADDITIONALAPPLICATIONID, @CAMPAIGNS, @DIDCAMPAIGNSDEFAULT, @DESIGNATIONID

            while @@FETCH_STATUS = 0
            begin

                -- Default the campaigns for the designation

                if @DIDCAMPAIGNSDEFAULT = 0
                begin
                    set @CAMPAIGNS = (  select distinct
                                            CAMPAIGNID,
                                            CAMPAIGNSUBPRIORITYID
                                        from dbo.UFN_DESIGNATION_GETCAMPAIGNSTODEFAULT(@DESIGNATIONID, @DATE)
                                        for xml raw('ITEM'),type,elements,root('CAMPAIGNS'),binary base64)
                end
      else
                begin
                  set @CAMPAIGNS = (select
                                      ID,
                                      CAMPAIGNID,
                                      CAMPAIGNSUBPRIORITYID,
                                      REVENUESPLITCAMPAIGNID
                                    from dbo.UFN_REVENUEBATCH_GETADDITIONALAPPLICATIONCAMPAIGNS_FROMITEMLISTXML(@CAMPAIGNS)
                                    where CAMPAIGNID is not null
                                    for xml raw('ITEM'),type,elements,root('CAMPAIGNS'),binary base64)
                end

                exec dbo.USP_REVENUEBATCH_GETADDITIONALAPPLICATIONCAMPAIGNS_UPDATEFROMXML @BATCHREVENUEADDITIONALAPPLICATIONID, @CAMPAIGNS, @CHANGEAGENTID;

                fetch next from CAMPAIGNCURSOR into @BATCHREVENUEADDITIONALAPPLICATIONID, @CAMPAIGNS, @DIDCAMPAIGNSDEFAULT, @DESIGNATIONID
            end

            close CAMPAIGNCURSOR
            deallocate CAMPAIGNCURSOR