USP_REVENUEBATCH_APPLYTOREVENUESTREAMS

            CREATE procedure dbo.USP_REVENUEBATCH_APPLYTOREVENUESTREAMS
            (
                @TRANSACTIONID uniqueidentifier,
                @REVENUESTREAMS xml,
                @CONSTITUENTID uniqueidentifier, 
                @DATE datetime, 
                @PAYMENTMETHODCODE tinyint, 
                @BATCHNUMBER nvarchar(100), 
                @POSTDATE datetime, 
                @POSTSTATUSCODE tinyint,
                @DONOTRECEIPT bit,
                @DONOTACKNOWLEDGE bit,
                @FINDERNUMBER bigint = 0,
                @SOURCECODE nvarchar(50) = '',
                @APPEALID uniqueidentifier = null,                
                @MAILINGID uniqueidentifier = null,
                @CHANNELCODEID uniqueidentifier = null,
                @CHECKDATE dbo.UDT_FUZZYDATE = '00000000',
                @CHECKNUMBER nvarchar(20) = '',
                @CONSTITUENTACCOUNTID uniqueidentifier = null,
                @REFERENCEDATE dbo.UDT_FUZZYDATE = '00000000',
                @REFERENCENUMBER nvarchar(20) = '',
                @CARDHOLDERNAME nvarchar(255) = '',
                @CREDITCARDNUMBER nvarchar(4) = '',
                @CREDITTYPECODEID uniqueidentifier = null,
                @AUTHORIZATIONCODE nvarchar(20) = '',
                @EXPIRESON dbo.UDT_FUZZYDATE = '00000000',
                @ISSUER nvarchar(100) = '',
                @NUMBEROFUNITS decimal(20,3) = 0,
                @SYMBOL nvarchar(25) = '',
                @MEDIANPRICE decimal(19,4) = 0,
                @SALEDATE datetime = null,
                @SALEAMOUNT money = null,
                @BROKERFEE money = null,
                @SALEPOSTSTATUSCODE tinyint = null,
                @SALEPOSTDATE datetime = null,
                @PROPERTYSUBTYPECODEID uniqueidentifier = null,
                @GIFTINKINDSUBTYPECODEID uniqueidentifier = null,
                @CHANGEAGENTID uniqueidentifier,
                @CREATIONDATE datetime,
                @UNAPPLIEDMATCHINGGIFTSPLITS xml = null,
                @TOTALAMOUNTAPPLIED money output,
                @REVENUEID uniqueidentifier output,
                @KEYALREADYOPEN bit = 0,
                @OTHERPAYMENTMETHODCODEID uniqueidentifier = null,
                @REFERENCE nvarchar(255) = null,
                @CATEGORYCODEID uniqueidentifier = null,
                @SPLITSDECLININGGIFTAID xml = null output,
                @COVENANTGIFTSPLITS xml = null output,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @SHOULDDEFAULTRECEIPTTYPECODE tinyint = 0
            )
            as 
            set nocount on;

            declare @APPLICATIONID uniqueidentifier;
            declare @APPLICATIONTYPE tinyint;
            declare @APPLIEDAMOUNT money;
            declare @OVERPAYMENTAPPLICATIONTYPECODE tinyint;
            declare @SPLITRECEIPTTYPECODE tinyint;
            declare @PERPAYMENTPREFERENCEEXISTS bit = 0;

            declare @SPLITSGIFTAIDINFOTBL table
            (
                REVENUESPLITID uniqueidentifier,
                DECLINESGIFTAID bit,
                ISCOVENANT bit
            )

            declare APPLICATIONSCURSOR cursor local fast_forward for
                select
                    APPLICATIONID,
                    APPLIED,
                    TYPECODE,
                    OVERPAYMENTAPPLICATIONTYPECODE
                from dbo.UFN_REVENUEBATCH_GETAPPLICATIONS_FROMITEMLISTXML(@REVENUESTREAMS)
                where APPLIED > 0;

            declare @EMPTYGUID uniqueidentifier;
            set @EMPTYGUID = '00000000-0000-0000-0000-000000000000';

            begin try
                set @TOTALAMOUNTAPPLIED = 0;
                open APPLICATIONSCURSOR;

                fetch next from APPLICATIONSCURSOR into @APPLICATIONID, @APPLIEDAMOUNT, @APPLICATIONTYPE, @OVERPAYMENTAPPLICATIONTYPECODE;

                while @@FETCH_STATUS = 0 
                begin 
                    set @REVENUEID = @TRANSACTIONID

                    declare @SPLITTYPECODE tinyint, @SPLITAPPLICATIONCODE tinyint
                    set @SPLITAPPLICATIONCODE = 
                            case @APPLICATIONTYPE 
                                when 1 then 2 --Pledge Payment

                                when 3 then 7 --MGPledge Payment

                                when 2 then 3 --Recurring Gift Payment

                                when 6 then 1 --Event Registration Payment

                                when 4 then 6 --Planned gift

                                when 5 then 5 --Membership

                                when 9 then 8 --Grant Award Payment

                                when 10 then 13 --Donor challenge payment

                                else 99
                            end;

                    -- Pull the split's type code from the source revenue split unless it's an event registrant

                    if @SPLITAPPLICATIONCODE <> 1
                        select @SPLITTYPECODE = TYPECODE from dbo.REVENUESPLIT where ID = @APPLICATIONID
                    else
                        set @SPLITTYPECODE = 1

                    if @SPLITAPPLICATIONCODE = 99 
                        raiserror('The application type is not supported.', 13, 1);

                    --Clear Appeal and Source code for event registrations

                    if @SPLITAPPLICATIONCODE = 1 
                        select
                            @FINDERNUMBER = 0,
                            @SOURCECODE = N'',
                            @APPEALID = null,                            
                            @MAILINGID = null,
                            @CHANNELCODEID = null

                    if @SHOULDDEFAULTRECEIPTTYPECODE = 1 and (@SPLITAPPLICATIONCODE = 2 or @SPLITAPPLICATIONCODE = 3)
                    begin
                        set @SPLITRECEIPTTYPECODE = dbo.UFN_CONSTITUENT_GETRECEIPTPREFERENCE(@CONSTITUENTID,@SPLITAPPLICATIONCODE);

                        if @SPLITRECEIPTTYPECODE = 0
                            set @PERPAYMENTPREFERENCEEXISTS = 1;

                    end

                    exec dbo.USP_REVENUEREFERENCE_ADD @REVENUEID, @REFERENCE, @CHANGEAGENTID;

                    if @APPLICATIONTYPE in(
                        1, -- Pledge 

                        3, -- MG Pledge

                        4, -- Planned Gift

                        9, -- Grant Award

                        10 -- Donor Challenge

                     )
                    begin
                        declare @REVENUESPLITAPPLICATIONTYPE tinyint
                        set @REVENUESPLITAPPLICATIONTYPE = 
                          case @APPLICATIONTYPE
                            when 1 then 2
                            when 3 then 7
                            when 4 then 6
                            when 9 then 8
                            when 10 then 13
                          end;

                        declare @CREATEDSPLITS xml
                        declare @AMOUNTPAID money
                        exec dbo.USP_PLEDGE_ADDPAYMENT
                            @REVENUEID = @REVENUEID,
                            @APPLICATIONID = @APPLICATIONID,
                            @APPLIEDAMOUNT = @APPLIEDAMOUNT,
                            @CONSTITUENTID = @CONSTITUENTID,
                            @DATE = @DATE,
                            @UNAPPLIEDMATCHINGGIFTSPLITS = @UNAPPLIEDMATCHINGGIFTSPLITS,
                            @APPLICATIONTYPE = @REVENUESPLITAPPLICATIONTYPE,
                            @AMOUNTPAID = @AMOUNTPAID output,
                            @CREATIONDATE = @CREATIONDATE,
                            @CHANGEAGENTID = @CHANGEAGENTID,
                            @CREATEDSPLITS = @CREATEDSPLITS output,
                            @OVERPAYMENTAPPLICATIONTYPECODE = @OVERPAYMENTAPPLICATIONTYPECODE,
                            @BUSINESSUNITSAPPLIED = 1

                        -- Default the declines gift aid and covenant values for a pledge payment split from the pledge split

                        insert into @SPLITSGIFTAIDINFOTBL 
                        (
                            REVENUESPLITID,
                            DECLINESGIFTAID,
                            ISCOVENANT
                        )
                        select
                            T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
                            REVENUESPLITGIFTAID.DECLINESGIFTAID,
                            REVENUESPLITGIFTAID.ISCOVENANT
                        from @CREATEDSPLITS.nodes('/SPLITS/ITEM') T(c)
                        inner join dbo.REVENUESPLITGIFTAID on T.c.value('(SOURCEREVENUESPLITID)[1]','uniqueidentifier') = REVENUESPLITGIFTAID.ID
                        where 
                            REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or 
                            REVENUESPLITGIFTAID.ISCOVENANT = 1

                        set @TOTALAMOUNTAPPLIED = @TOTALAMOUNTAPPLIED + @AMOUNTPAID
                    end 

                    if @APPLICATIONTYPE = 2 --Recurring Gift

                    begin
                        declare @RECURRINGGIFTCREATEDSPLITS xml
                        exec dbo.USP_RECURRINGGIFT_ADDPAYMENT 
                            @REVENUEID = @REVENUEID,
                            @APPLICATIONID = @APPLICATIONID,
                            @APPLIEDAMOUNT = @APPLIEDAMOUNT,
                            @CONSTITUENTID = @CONSTITUENTID,
                            @DATE = @DATE,
                            @CREATIONDATE = @CREATIONDATE,
                            @CHANGEAGENTID = @CHANGEAGENTID,
                            @CREATEDSPLITS = @RECURRINGGIFTCREATEDSPLITS output,
                            @BUSINESSUNITSAPPLIED= 1

                        set @TOTALAMOUNTAPPLIED = @TOTALAMOUNTAPPLIED + @APPLIEDAMOUNT;

                        -- Default the declines gift aid value for a pledge payment split from the pledge split

                        insert into @SPLITSGIFTAIDINFOTBL 
                        (
                            REVENUESPLITID,
                            DECLINESGIFTAID
                        )
                        select
                            T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
                            1
                        from @RECURRINGGIFTCREATEDSPLITS.nodes('/SPLITS/ITEM') T(c)
                        inner join dbo.REVENUESPLITGIFTAID on T.c.value('(SOURCEREVENUESPLITID)[1]','uniqueidentifier') = REVENUESPLITGIFTAID.ID
                        where REVENUESPLITGIFTAID.DECLINESGIFTAID = 1
                    end 

                    if @APPLICATIONTYPE = 6 --Event Registration

                    begin

                        -- commit the event registration

                        declare @REGISTRANTID uniqueidentifier;
                        set @REGISTRANTID = @APPLICATIONID;

                        if exists(select top(1) 1 from dbo.BATCHREVENUEREGISTRANT where ID = @APPLICATIONID)
                        begin
                            declare @EVENTID uniqueidentifier;
                            declare @DATEPURCHASED datetime;
                            declare @MAINEVENTID uniqueidentifier;
                            declare @PACKAGEREGISTRATIONS xml;
                            declare @SINGLEEVENTREGISTRATIONS xml;
                            declare @WAIVEBENEFITS bit;
                            declare @REGISTRANTMAPPINGS xml;
                            declare @BATCHID uniqueidentifier;
                            declare @REGISTRANTCONSTITUENTID uniqueidentifier;
                            declare @ISWALKIN bit;

                            select
                                @EVENTID = EVENT.ID,
                                @MAINEVENTID = case
                                    when [EVENT].[MAINEVENTID] is not null then [EVENT].[MAINEVENTID]
                                    when exists(select ID from dbo.EVENT [SUBEVENT] where [SUBEVENT].[MAINEVENTID] = [EVENT].[ID]) then [EVENT].[ID]
                                    else null
                                end,
                                @DATEPURCHASED = dbo.UFN_DATE_GETEARLIESTTIME(getdate()),
                                @WAIVEBENEFITS = BATCHREVENUEREGISTRANT.BENEFITSWAIVED,
                                @BATCHID = BATCHREVENUEREGISTRANT.BATCHID,
                                @REGISTRANTCONSTITUENTID = BATCHREVENUEREGISTRANT.CONSTITUENTID,
                                @ISWALKIN = coalesce(BATCHREVENUEREGISTRANT.ISWALKIN,0)
                            from
                                dbo.BATCHREVENUEREGISTRANT
                            left outer join
                                dbo.EVENT on EVENT.ID = BATCHREVENUEREGISTRANT.EVENTID
                            where
                                BATCHREVENUEREGISTRANT.ID = @APPLICATIONID;

                            declare @MAINEVENTIDPARAMETER uniqueidentifier;
                            set @MAINEVENTIDPARAMETER = coalesce(@MAINEVENTID, @EVENTID);

                            exec dbo.USP_REVENUEBATCHREGISTRANT_GETREGISTRATIONSANDREGISTRANTMAPPINGS 
                                @MAINEVENTIDPARAMETER,
                                @REGISTRANTCONSTITUENTID,
                                @PACKAGEREGISTRATIONS output,
                                @SINGLEEVENTREGISTRATIONS output,
                                @REGISTRANTMAPPINGS output,
                                @BATCHID;

                            if not exists 
                            (
                                select top (1)
                                    REGISTRANT.ID
                                from
                                    @REGISTRANTMAPPINGS.nodes('/REGISTRANTMAPPINGS/ITEM/REGISTRANTREGISTRATIONMAPS/ITEM') T(c)
                                    inner join dbo.REGISTRANT on
                                        T.c.value('EVENTID[1]', 'uniqueidentifier') = REGISTRANT.EVENTID
                                        and @REGISTRANTCONSTITUENTID = REGISTRANT.CONSTITUENTID
                            )
                            begin

                                declare @UNKNOWNGUESTWELLKNOWNGUID uniqueidentifier;
                                set @UNKNOWNGUESTWELLKNOWNGUID = 'C776DAB5-65B2-4258-ADAE-AE396D28E251';

                                -- commit all of the batch constituents used as guests on the registration

                                declare @NEWCONSTITUENTS table (CONSTITUENTID uniqueidentifier);
                                insert into @NEWCONSTITUENTS
                                (
                                    CONSTITUENTID
                                )
                                select
                                    T.c.value('GUESTCONSTITUENTID[1]', 'uniqueidentifier')
                                from
                                    @REGISTRANTMAPPINGS.nodes('/REGISTRANTMAPPINGS/ITEM') T(c)
                                where
                                    not exists(select ID from dbo.CONSTITUENT where CONSTITUENT.ID = T.c.value('GUESTCONSTITUENTID[1]', 'uniqueidentifier'))
                                and
                                    T.c.value('GUESTCONSTITUENTID[1]', 'uniqueidentifier') <> @UNKNOWNGUESTWELLKNOWNGUID;

                                declare NEWCONSTITUENTCURSOR cursor local fast_forward for
                                    select CONSTITUENTID from @NEWCONSTITUENTS;

                                declare @NEWCONSTITUENTID uniqueidentifier;            
                                open NEWCONSTITUENTCURSOR;

                                fetch next from NEWCONSTITUENTCURSOR into @NEWCONSTITUENTID;
                                while @@FETCH_STATUS = 0 
                                begin 

                                    exec dbo.USP_REVENUEBATCH_CONSTITUENT_ADD 
                                        @NEWCONSTITUENTID OUTPUT, 
                                        @CHANGEAGENTID,
                                        @NEWCONSTITUENTID;

                                    exec dbo.USP_REVENUEBATCH_CONSTITUENT_DELETE @NEWCONSTITUENTID, @CHANGEAGENTID;

                                    fetch next from NEWCONSTITUENTCURSOR into @NEWCONSTITUENTID;
                                end                                
                                deallocate NEWCONSTITUENTCURSOR

                                -- Alter @REGISTRANTMAPPINGS and @PACKAGEREGISTRATIONS to look like an add instead of an edit

                                set @REGISTRANTMAPPINGS =
                                (
                                    select
                                        T.c.query('*[local-name()!="REGISTRANTREGISTRATIONMAPS" 
                                                        and local-name()!="REGISTRANTPACKAGEID" 
                                                        and local-name()!="PREFERENCES" 
                                                        and local-name()!="EVENTID"
                                                        and local-name()!="EVENTPRICEID"]
                                                        '),
                                        '00000000-0000-0000-0000-000000000000' [REGISTRANTPACKAGEID],
                                        coalesce(T.c.value('EVENTID[1]', 'uniqueidentifier'), '00000000-0000-0000-0000-000000000000') [EVENTID],
                                        coalesce(T.c.value('EVENTPRICEID[1]', 'uniqueidentifier'), '00000000-0000-0000-0000-000000000000') [EVENTPRICEID],
                                        (
                                            select
                                                TInner.c.query('*[local-name()!="REGISTRANTPREFERENCEID"]')
                                            from
                                                T.c.nodes('PREFERENCES/ITEM') TInner(c)
                                            for xml path('ITEM'), type
                                        ) [PREFERENCES],
                                        T.c.value('BATCHREVENUEREGISTRANTID[1]', 'uniqueidentifier') [BATCHREVENUEREGISTRANTID]
                                    from
                                        @REGISTRANTMAPPINGS.nodes('/REGISTRANTMAPPINGS/ITEM') T(c)
                                    for xml path('ITEM'), root('REGISTRANTMAPPINGS'), type
                                );

                                set @PACKAGEREGISTRATIONS =
                                (
                                    select
                                        T.c.query('*[local-name()!="PACKAGEREGISTRANTREGISTRATIONS"]'),
                                        (
                                            select
                                                TInner.c.query('*[local-name()!="PACKAGEREGISTRANTREGISTRATIONID"]')
                                            from
                                                T.c.nodes('PACKAGEREGISTRANTREGISTRATIONS/ITEM') TInner(c)
                                            for xml path('ITEM'), type
                                        ) [PACKAGEREGISTRANTREGISTRATIONS]
                                    from
                                        @PACKAGEREGISTRATIONS.nodes('/PACKAGEREGISTRATIONS/ITEM') T(c)
                                    for xml path('ITEM'), root('PACKAGEREGISTRATIONS'), type
                                );

                                declare @PACKAGESPRICES xml;
                                --JamesWill WI147843 2011-07-06 Pull in multi-level event prices

                                set @PACKAGESPRICES = dbo.UFN_REGISTRATIONPACKAGE_GETPACKAGESPRICES_3_TOITEMLISTXML(@EVENTID, @MAINEVENTID, @CURRENTAPPUSERID);

                                --Add REGISTRANT record

                                exec dbo.USP_REGISTRANT_UNIFIEDUPDATE
                                    @ID = @APPLICATIONID output,
                                    @CURRENTAPPUSERID = @CURRENTAPPUSERID,
                                    @SECURITYCONTEXTFORMINSTANCEID = 'CC548990-BB24-4BC9-B39F-A8EA5D574C27', --RevenueBatchCommitDataForm.Add.xml dataform instance ID

                                    @CHANGEAGENTID = @CHANGEAGENTID,
                                    @CURRENTDATE = @CREATIONDATE,
                                    @EVENTID = @EVENTID,
                                    @CONSTITUENTID = @REGISTRANTCONSTITUENTID,
                                    @DATEPURCHASED = @DATEPURCHASED,
                                    @PACKAGEREGISTRATIONS = @PACKAGEREGISTRATIONS,
                                    @PACKAGESPRICES = @PACKAGESPRICES,
                                    @SINGLEEVENTREGISTRATIONS = @SINGLEEVENTREGISTRATIONS,
                                    @WAIVEBENEFITS = @WAIVEBENEFITS,
                                    @REGISTRANTMAPPINGS = @REGISTRANTMAPPINGS,
                                    @DELETEDREGISTRANTREGISTRATIONMAPS = NULL,
                                    @BYPASSSECURITY = 1, --Don't check security from batch - if user had rights to add to revenue streams, allow them to commit

                                    @ISWALKIN = @ISWALKIN;

                                --Delete BATCHREVENUEREGISTRANT record

                                exec dbo.USP_REVENUEBATCH_REGISTRANT_DELETE @BATCHREVENUEREGISTRANTID=@APPLICATIONID, @CHANGEAGENTID=@CHANGEAGENTID;

                            end
                            else
                            begin
                                update dbo.BATCHREVENUEAPPLICATION
                                set
                                    REGISTRANTID = REGISTRANT.ID,
                                    BATCHREVENUEREGISTRANTID = null,
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = getdate()
                                from
                                    dbo.BATCHREVENUEREGISTRANT
                                inner join
                                    dbo.REGISTRANT on REGISTRANT.EVENTID = BATCHREVENUEREGISTRANT.EVENTID
                                    and REGISTRANT.CONSTITUENTID = @REGISTRANTCONSTITUENTID
                                inner join
                                    BATCHREVENUEAPPLICATION on BATCHREVENUEAPPLICATION.BATCHREVENUEREGISTRANTID = BATCHREVENUEREGISTRANT.ID
                                    and BATCHREVENUEAPPLICATION.BATCHREVENUEID = @TRANSACTIONID;
                            end

                            -- REGISTRANT.ID may not be @APPLICATIONID when registration was added by a multi-component event registration

                            select
                                @REGISTRANTID = REGISTRANT.ID
                            from
                                dbo.BATCHREVENUEREGISTRANT
                                inner join dbo.REGISTRANT on
                                    BATCHREVENUEREGISTRANT.EVENTID = REGISTRANT.EVENTID
                                    and @REGISTRANTCONSTITUENTID = REGISTRANT.CONSTITUENTID
                            where
                                BATCHREVENUEREGISTRANT.ID = @APPLICATIONID;
                        end

                        exec dbo.USP_EVENT_ADDPAYMENT 
                            @REVENUEID = @REVENUEID, 
                            @APPLICATIONID = @REGISTRANTID, 
                            @APPLIEDAMOUNT = @APPLIEDAMOUNT,
                            @CREATIONDATE = @CREATIONDATE,
                            @CHANGEAGENTID = @CHANGEAGENTID;

                        set @TOTALAMOUNTAPPLIED = @TOTALAMOUNTAPPLIED + @APPLIEDAMOUNT;
                    end

                    if @APPLICATIONTYPE = 5 --Membership

                    begin
                        declare @MEMBERSHIPS xml;
                        set @MEMBERSHIPS = 
                        (
                        select
                            T.c.query('./MEMBERSHIPS/ITEM') 
                            from @REVENUESTREAMS.nodes('/REVENUESTREAMS/ITEM') T(c)
                            where T.c.value('(APPLICATIONID)[1]','uniqueidentifier') = @APPLICATIONID
                            for xml raw('MEMBERSHIPFIELDS'), type, elements, binary base64)

                        exec dbo.USP_MEMBERSHIP_ADDPAYMENT
                            @REVENUEID = @REVENUEID,
                            @CONSTITUENTID = @CONSTITUENTID,
                            @MEMBERSHIPID = @APPLICATIONID,
                            @AMOUNT = @APPLIEDAMOUNT,
                            @TRANSACTIONDATE = @DATE,
                            @MEMBERSHIPS = @MEMBERSHIPS,
                            @CREATIONDATE = @CREATIONDATE,
                            @CHANGEAGENTID = @CHANGEAGENTID;

                        set @TOTALAMOUNTAPPLIED = @TOTALAMOUNTAPPLIED + @APPLIEDAMOUNT;

                    end

                    fetch next from APPLICATIONSCURSOR into @APPLICATIONID, @APPLIEDAMOUNT, @APPLICATIONTYPE, @OVERPAYMENTAPPLICATIONTYPECODE;

                end

                if @SHOULDDEFAULTRECEIPTTYPECODE = 1 and (@SPLITAPPLICATIONCODE = 2 or @SPLITAPPLICATIONCODE = 3)
                begin
                    -- If at least one per payment preference exists, this revenue must be per payment.

                    if @PERPAYMENTPREFERENCEEXISTS = 1
                        update dbo.REVENUE_EXT
                        set RECEIPTTYPECODE = 0, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CREATIONDATE
                        where ID = @REVENUEID;
                    -- Otherwise all splits preferences were consolidated.

                    else
                        update dbo.REVENUE_EXT
                        set RECEIPTTYPECODE = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CREATIONDATE
                        where ID = @REVENUEID;
                end
            end try
            begin catch
                exec dbo.USP_RAISE_ERROR;
            end catch
            --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

            close APPLICATIONSCURSOR;
            deallocate APPLICATIONSCURSOR;

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

            set @COVENANTGIFTSPLITS = (    select
                                            REVENUESPLITID
                                        from @SPLITSGIFTAIDINFOTBL
                                        where ISCOVENANT = 1
                                        for xml raw('ITEM'),type,elements,root('COVENANTGIFTSPLITS'),BINARY BASE64)