USP_REVENUEBATCHCOMMIT_SPLITSWITHCHILDREN_ADDFROMXML

            CREATE procedure dbo.USP_REVENUEBATCHCOMMIT_SPLITSWITHCHILDREN_ADDFROMXML
            (
                @ID uniqueidentifier,
                @SPLITS xml,
                @REVENUESPLITAPPLICATIONCODE tinyint,
                @REVENUESPLITTYPECODE tinyint,
                @OTHERTYPECODEID uniqueidentifier,
                @CATEGORYCODEID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier,
                @CURRENTDATE datetime,
                @OPPORTUNITYID uniqueidentifier,
                @ADDITIONALAPPLICATIONS bit,
                   @SPLITSDECLININGGIFTAID xml = null output,
                @ISGIFTAIDSPONSORSHIPSPLITS xml = null output,
                @TRANSACTIONCURRENCYID uniqueidentifier = null output,
                @BASECURRENCYID uniqueidentifier = null output,
                @BASEEXCHANGERATEID uniqueidentifier = null output,
                @ORGANIZATIONEXCHANGERATEID uniqueidentifier = null output,
            @REVENUESPLITSPONSORSHIPID xml = null output
            )
            as
                set nocount on;

                declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                if @TRANSACTIONCURRENCYID is null
                    set @TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID;

                if @BASECURRENCYID is null
                    set @BASECURRENCYID = @ORGANIZATIONCURRENCYID;

                declare @SPLITSDECLININGGIFTAIDTBL table
                (
                    REVENUESPLITID uniqueidentifier
                )

                declare @ISGIFTAIDSPONSORSHIPTBL table
                (
                  REVENUESPLITID uniqueidentifier
                )

                declare @OPPORTUNITYSPLITSTBL table
                (
                    REVENUESPLITID uniqueidentifier,
                    OPPORTUNITYID uniqueidentifier
                )  
            declare @REVENUESPLITSPONSORSHIPIDTBL table
            (
                    REVENUESPLITID uniqueidentifier,
                    SPONSORSHIPID uniqueidentifier
                )              
                -- Update the application code on the splits

                declare @PAYMENTREVENUESPLITS xml
                if @ADDITIONALAPPLICATIONS = 0
                begin
                    --JamesWill 2010-07-13 Make sure both XMLs have the same sets of columns by adding empty and unused columns as necessary

                  set @PAYMENTREVENUESPLITS = (    select
                          case when nullif(T.c.value('(ID)[1]','uniqueidentifier'), '00000000-0000-0000-0000-000000000000') is null then newid() else T.c.value('(ID)[1]','uniqueidentifier') end ID,
                          coalesce(@REVENUESPLITAPPLICATIONCODE, T.c.value('(APPLICATIONCODE)[1]','tinyint')) APPLICATIONCODE,
                          coalesce(@REVENUESPLITTYPECODE, T.c.value('(TYPECODE)[1]','tinyint')) TYPECODE,
                          T.c.value('(AMOUNT)[1]','money') as 'AMOUNT',
                          T.c.value('(DESIGNATIONID)[1]','uniqueidentifier') as 'DESIGNATIONID',
                          @CATEGORYCODEID as CATEGORYCODEID,
                          --case when T.c.exist('./CAMPAIGNS/CAMPAIGNS/ITEM') = 1 then T.c.query('(CAMPAIGNS/CAMPAIGNS/ITEM)') else null end CAMPAIGNS,

                          T.c.value('(DECLINESGIFTAID)[1]','bit') as 'DECLINESGIFTAID',
                          null as OPPORTUNITYID,
                          T.c.value('(ISGIFTAIDSPONSORSHIP)[1]','bit') as 'ISGIFTAIDSPONSORSHIP',
                          null as OTHERTYPECODEID,
                          case when T.c.exist('./CAMPAIGNS/ITEM') = 1 then T.c.query('(CAMPAIGNS/ITEM)') else null end as CAMPAIGNS,
                          null as SPONSORSHIPID
                          from @SPLITS.nodes('/SPLITS/ITEM') T(c)
                          for xml raw('ITEM'),type,elements,root('SPLITS'),binary base64)
                end
                else
                begin
                  set @PAYMENTREVENUESPLITS = (    select
                                                      newid() ID,
                                                      T.c.value('(APPLICATIONCODE)[1]','tinyint') APPLICATIONCODE,
                                                      T.c.value('(TYPECODE)[1]','tinyint') TYPECODE,
                                                      T.c.value('(AMOUNT)[1]','money') AMOUNT,
                                                      T.c.value('(DESIGNATIONID)[1]','uniqueidentifier') DESIGNATIONID,
                                               T.c.value('(CATEGORYCODEID)[1]','uniqueidentifier') CATEGORYCODEID,
                                                      T.c.value('(OTHERTYPECODEID)[1]','uniqueidentifier') OTHERTYPECODEID,
                                                      T.c.value('(DECLINESGIFTAID)[1]','bit') DECLINESGIFTAID,
                                                      coalesce(T.c.value('(OPPORTUNITYID)[1]','uniqueidentifier'), null) OPPORTUNITYID,
                                                      T.c.value('(ISGIFTAIDSPONSORSHIP)[1]','bit') ISGIFTAIDSPONSORSHIP,
                                                      case when T.c.exist('./CAMPAIGNS/ITEM') = 1 then T.c.query('(CAMPAIGNS/ITEM)') else null end as CAMPAIGNS,
                                         T.c.value('(SPONSORSHIPID)[1]','uniqueidentifier') SPONSORSHIPID
                                         from @SPLITS.nodes('/SPLITS/ITEM') T(c)
                                                     for xml raw('ITEM'),type,elements,root('SPLITS'),binary base64)

                end

            --Multicurrency - Process the splits xml to calculate the base and organization amounts and place them in proper nodes.

                --JamesWill 2010-07-13 WI107559 Use a function that knows about all of the columns in @PAYMENTREVENUESPLITS

                --***IMPORTANT*** IF YOU CHANGE THE COLUMNS IN @PAYMENTREVENUESPLITS, YOU ***MUST*** UPDATE UFN_REVENUEBATCH_PAYMENTSPLIT_CONVERTAMOUNTSINXML

                if @ADDITIONALAPPLICATIONS = 1 --Couple sponsorshipid and revenuesplitid here before we lose it below.

            begin
                    insert into @REVENUESPLITSPONSORSHIPIDTBL (REVENUESPLITID, SPONSORSHIPID)
                    select
                        T.c.value('(ID)[1]','uniqueidentifier') REVENUESPLITID,
                        T.c.value('(SPONSORSHIPID)[1]','uniqueidentifier') SPONSORSHIPID
                    from @PAYMENTREVENUESPLITS.nodes('/SPLITS/ITEM') T(c)
                    where
                        T.c.value('(ID)[1]','uniqueidentifier') is not null and
                        coalesce(T.c.value('(SPONSORSHIPID)[1]','uniqueidentifier'),'00000000-0000-0000-0000-000000000000') <> '00000000-0000-0000-0000-000000000000';  
            end

            set @PAYMENTREVENUESPLITS = dbo.UFN_REVENUEBATCH_PAYMENTSPLIT_CONVERTAMOUNTSINXML(@PAYMENTREVENUESPLITS,@BASECURRENCYID,@ORGANIZATIONEXCHANGERATEID,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID)
                exec dbo.USP_REVENUE_GETSPLITS_2_CUSTOMADDFROMXML @ID, @PAYMENTREVENUESPLITS, @CHANGEAGENTID, @CURRENTDATE;

                insert into @SPLITSDECLININGGIFTAIDTBL (REVENUESPLITID)
                select
                    T.c.value('(ID)[1]','uniqueidentifier')
                from @PAYMENTREVENUESPLITS.nodes('/SPLITS/ITEM') T(c)
                where T.c.value('(DECLINESGIFTAID)[1]','bit') = 1

                insert into @ISGIFTAIDSPONSORSHIPTBL (REVENUESPLITID)
                select
                  T.c.value('(ID)[1]','uniqueidentifier')
                from @PAYMENTREVENUESPLITS.nodes('/SPLITS/ITEM') T(c)
                where T.c.value('(ISGIFTAIDSPONSORSHIP)[1]','bit') = 1

                -- Populate a temporary table of opportunity IDs.


                if @ADDITIONALAPPLICATIONS = 0
                begin
                    if @OPPORTUNITYID is not null and @OPPORTUNITYID <> '00000000-0000-0000-0000-000000000000'
                        insert into @OPPORTUNITYSPLITSTBL (REVENUESPLITID, OPPORTUNITYID)
                        select
                            T.c.value('(ID)[1]','uniqueidentifier') REVENUESPLITID,
                            @OPPORTUNITYID OPPORTUNITYID
                        from @PAYMENTREVENUESPLITS.nodes('/SPLITS/ITEM') T(c)
                end
                else if @ADDITIONALAPPLICATIONS = 1
                begin
                    insert into @OPPORTUNITYSPLITSTBL (REVENUESPLITID, OPPORTUNITYID)
                    select
                        T.c.value('(ID)[1]','uniqueidentifier') REVENUESPLITID,
                        T.c.value('(OPPORTUNITYID)[1]','uniqueidentifier') OPPORTUNITYID
                    from @PAYMENTREVENUESPLITS.nodes('/SPLITS/ITEM') T(c)
                    where
                        T.c.value('(OPPORTUNITYID)[1]','uniqueidentifier') is not null and
                        T.c.value('(OPPORTUNITYID)[1]','uniqueidentifier') <> '00000000-0000-0000-0000-000000000000';
                end

                -- Create the REVENUESPLITOTHER rows if the application type is Other

                if @REVENUESPLITAPPLICATIONCODE = 4
                begin
                    insert into dbo.REVENUESPLITOTHER 
                    (
                        ID, 
                        OTHERTYPECODEID, 
                        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                    )
                    select
                        ID,
                        @OTHERTYPECODEID,
                        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                    from dbo.REVENUESPLIT
                    where
                        REVENUEID = @ID and
                        APPLICATIONCODE = 4 -- Other application code

                end
                else if @ADDITIONALAPPLICATIONS = 1
                begin
                    insert into dbo.REVENUESPLITOTHER 
                    (
                        ID, 
                        OTHERTYPECODEID, 
                        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                    )
                    select
                        T.c.value('(ID)[1]','uniqueidentifier'),
                        T.c.value('(OTHERTYPECODEID)[1]','uniqueidentifier'),
                        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                    from @PAYMENTREVENUESPLITS.nodes('/SPLITS/ITEM') T(c)
                    where T.c.value('(APPLICATIONCODE)[1]','tinyint') = 4
               end

                if ((@CATEGORYCODEID is not null) and (@PAYMENTREVENUESPLITS.exist('(/SPLITS/ITEM/CATEGORYCODEID)') = 0))
                begin
                    insert into dbo.REVENUECATEGORY (ID, GLREVENUECATEGORYMAPPINGID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    select
                        ID,
                        @CATEGORYCODEID,
                        @CHANGEAGENTID, 
                        @CHANGEAGENTID, 
                        @CURRENTDATE, 
                        @CURRENTDATE
                    from dbo.UFN_REVENUE_GETSPLITS_FROMITEMLISTXML(@PAYMENTREVENUESPLITS);
                end

                --Add opportunities

                insert into dbo.REVENUEOPPORTUNITY (ID, OPPORTUNITYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                select
                    REVENUESPLITID,
                    OPPORTUNITYID,
                    @CHANGEAGENTID, 
                    @CHANGEAGENTID, 
                    @CURRENTDATE, 
                    @CURRENTDATE
                from @OPPORTUNITYSPLITSTBL

                declare @REVENUEDATE datetime
                select @REVENUEDATE = DATE from dbo.REVENUE where ID = @ID

                -- Update opportunity accepted status

                update dbo.OPPORTUNITY set
                    STATUSCODE = 3,  -- Accepted

                    ASKDATE = coalesce(ASKDATE, @REVENUEDATE),
                    ACCEPTEDASKAMOUNT = ASKAMOUNT,  
                    TRANSACTIONACCEPTEDASKAMOUNT = TRANSACTIONASKAMOUNT,  
                    ORGANIZATIONACCEPTEDASKAMOUNT = ORGANIZATIONASKAMOUNT, 
                    RESPONSEDATE = @REVENUEDATE,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                from
                    dbo.OPPORTUNITY
                    inner join @OPPORTUNITYSPLITSTBL TEMPTBL on TEMPTBL.OPPORTUNITYID = OPPORTUNITY.ID
                where
                    STATUSCODE <> 3

                -- Add campaigns

                declare @REVENUESPLITID uniqueidentifier, @CAMPAIGNS xml
                declare CAMPAIGNCURSOR cursor local fast_forward for 
                select
                    ID,
                    CAMPAIGNS
                from dbo.UFN_REVENUEBATCH_SPLITSWITHCHILDREN_FROMITEMLISTXML(@PAYMENTREVENUESPLITS)

                open CAMPAIGNCURSOR

                fetch next from CAMPAIGNCURSOR into @REVENUESPLITID, @CAMPAIGNS

                while @@FETCH_STATUS = 0
                begin
                    exec dbo.USP_REVENUESPLIT_CAMPAIGNS_UPDATEFROMXML @REVENUESPLITID, @CAMPAIGNS, @CHANGEAGENTID;

                    fetch next from CAMPAIGNCURSOR into @REVENUESPLITID, @CAMPAIGNS
                end

                close CAMPAIGNCURSOR
                deallocate CAMPAIGNCURSOR

                set @SPLITSDECLININGGIFTAID = (    select
                                                    REVENUESPLITID
                                                from @SPLITSDECLININGGIFTAIDTBL 
                                                for xml raw('ITEM'),type,elements,root('SPLITSDECLININGGIFTAID'),BINARY BASE64)

        set @ISGIFTAIDSPONSORSHIPSPLITS = (    select
                                                    REVENUESPLITID
                                                from @ISGIFTAIDSPONSORSHIPTBL 
                                                for xml raw('ITEM'),type,elements,root('ISGIFTAIDSPONSORSHIPSPLITS'),BINARY BASE64)
            set @REVENUESPLITSPONSORSHIPID = (select
                                             REVENUESPLITID,
                                             SPONSORSHIPID
                                             from @REVENUESPLITSPONSORSHIPIDTBL
      for xml raw('ITEM'),type,elements,root('REVENUESPLITSPONSORSHIPID'),BINARY BASE64)