USP_REVENUEBATCH_APPLYTOSINGLEAPPLICATION

      CREATE procedure dbo.USP_REVENUEBATCH_APPLYTOSINGLEAPPLICATION
            (
                @TRANSACTIONID uniqueidentifier,
                @SINGLEAPPLICATIONID uniqueidentifier,
                @APPLICATIONTYPECODE tinyint,
                @APPLICATIONAMOUNT money,
                @CONSTITUENTID uniqueidentifier, 
                @DATE datetime, 
                @PAYMENTMETHODCODE tinyint, 
                @BATCHNUMBER nvarchar(60), 
                @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,
                @TRANSACTIONCURRENCYID uniqueidentifier, 
                @BATCHROWID uniqueidentifier = null,
                @SHOULDDEFAULTRECEIPTTYPECODE tinyint = 0,
                @REVENUESTREAMS xml = null
            )
            as 
            set nocount on;

            declare @RECEIPTTYPECODE tinyint;

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

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

            begin try
                set @REVENUEID = @TRANSACTIONID

                declare @SPLITTYPECODE tinyint, @SPLITAPPLICATIONCODE tinyint
                set @SPLITAPPLICATIONCODE = 
                        case @APPLICATIONTYPECODE 
                            when 5 then 2 --Pledge Payment

                            when 8 then 7 --MGPledge Payment

                            when 4 then 3 --Recurring Gift Payment

                            when 7 then 1 --Event Registration Payment

                            when 6 then 6 --Planned gift

                            when 2 then 5 --Membership

                            when 10 then 13 -- Donor challenge payment

              when 1 then 3 --Sponsorship Payment

                            when 9 then 8 --Grant Award Payment

                            else 255
                        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 = @SINGLEAPPLICATIONID
                else
                    set @SPLITTYPECODE = 1

                if @SPLITAPPLICATIONCODE = 255 
                    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 @RECEIPTTYPECODE = dbo.UFN_CONSTITUENT_GETRECEIPTPREFERENCE(@CONSTITUENTID,@SPLITAPPLICATIONCODE);

                    -- If the receipt type is 'Per payment', all of the revenue details in the transaction must also be 'Per payment'

                    if @RECEIPTTYPECODE = 0
                        update dbo.REVENUE 
                        set RECEIPTTYPECODE = @RECEIPTTYPECODE, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CREATIONDATE
                        where ID = @REVENUEID;
                end

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

                if @APPLICATIONTYPECODE in (
                    5,--Pledge

                    8,--MG Pledge

                    6,--Planned Gift

                    9,--Grant Award

                    10--Donor Challenge

                )
                begin
                    declare @REVENUESPLITAPPLICATIONTYPE tinyint
                    set @REVENUESPLITAPPLICATIONTYPE =
                        case @APPLICATIONTYPECODE
                            when 5 then 2
              when 6 then 6
              when 8 then 7
                            when 9 then 8
                            when 10 then 13
                        end;

                    if(@REVENUESTREAMS is null)
                    begin
                        -- Pull the default pledge overpayment method

                        declare @OVERPAYMENTAPPLICATIONTYPECODE tinyint;
                        select top 1
                            @OVERPAYMENTAPPLICATIONTYPECODE = DEFAULTAPPLICATIONTYPECODE
                        from dbo.PLEDGEOVERPAYMENTOPTIONS;
                    end
                    else
                    begin
                        select
                        @OVERPAYMENTAPPLICATIONTYPECODE = OVERPAYMENTAPPLICATIONTYPECODE
                        from dbo.UFN_REVENUEBATCH_GETAPPLICATIONS_FROMITEMLISTXML(@REVENUESTREAMS)
                    end

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

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

                if @APPLICATIONTYPECODE = 4 or @APPLICATIONTYPECODE = 1 --Recurring Gift or Sponsorship

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

                    -- 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 @APPLICATIONTYPECODE = 7 --Event Registration

                begin

                    -- commit the event registration

                    declare @REGISTRANTID uniqueidentifier;
                    set @REGISTRANTID = @SINGLEAPPLICATIONID;

                    if exists(select top(1) 1 from dbo.BATCHREVENUEREGISTRANT where ID = @SINGLEAPPLICATIONID)
                    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;

                        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
                        from
                            dbo.BATCHREVENUEREGISTRANT
                        left outer join
                            dbo.EVENT on EVENT.ID = BATCHREVENUEREGISTRANT.EVENTID
                        where
                            BATCHREVENUEREGISTRANT.ID = @SINGLEAPPLICATIONID;

                        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
                            --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

                            close NEWCONSTITUENTCURSOR;
                            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;
                            set @PACKAGESPRICES = dbo.UFN_REGISTRATIONPACKAGE_GETPACKAGESPRICES_2_TOITEMLISTXML(coalesce(@MAINEVENTID, @EVENTID));

                            --Add REGISTRANT record

                            exec dbo.USP_REGISTRANT_UNIFIEDUPDATE
                                @ID = @SINGLEAPPLICATIONID 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;

                            --Delete BATCHREVENUEREGISTRANT record

                            exec dbo.USP_REVENUEBATCH_REGISTRANT_DELETE @BATCHREVENUEREGISTRANTID=@SINGLEAPPLICATIONID, @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 = @SINGLEAPPLICATIONID;
                    end

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

                if @APPLICATIONTYPECODE = 2 --Membership

                begin
                declare @MEMBERSHIPS xml;
                declare @MEMBERS xml = null;

                declare @CLEARGIFTMEMBERSHIP bit;
                set @CLEARGIFTMEMBERSHIP = 0;
                if exists (select 1 from dbo.UFN_MEMBERSHIP_GETMEMBERS(@SINGLEAPPLICATIONID) where CONSTITUENTID = @CONSTITUENTID)
                    set @CLEARGIFTMEMBERSHIP = 1;

                -- @BATCHROWID may not be present in earlier versions so maintain backwards compatibility

                if @BATCHROWID is null or not exists (select BATCHREVENUEAPPLICATIONMEMBERSHIP.id
                                          from   
                                                        DBO.BATCHREVENUEAPPLICATION inner join 
                                                        DBO.BATCHREVENUEAPPLICATIONMEMBERSHIP on BATCHREVENUEAPPLICATIONMEMBERSHIP.ID = BATCHREVENUEAPPLICATION.BATCHREVENUEAPPLICATIONMEMBERSHIPID 
                                                      where  
                                                        BATCHREVENUEAPPLICATION.BATCHREVENUEID  = @BATCHROWID and 
                                                        BATCHREVENUEAPPLICATIONMEMBERSHIP.MEMBERSHIPID = @SINGLEAPPLICATIONID)
                begin
                    set @MEMBERS =  
                    (
                        select
                        M.ID,
                        M.CONSTITUENTID,
                        M.ISPRIMARY,
                        (
                        select
                            MC.ID,
                            MC.NAMEONCARD,
                            MC.EXPIRATIONDATE
                            from dbo.MEMBERSHIPCARD MC
                            where MC.MEMBERID = M.ID and MC.STATUSCODE <> 2
                            for xml raw('ITEM'),type,elements,root('MEMBERSHIPCARDS'),BINARY BASE64
                        )
                        from dbo.MEMBER M
                        where MEMBERSHIPID = @SINGLEAPPLICATIONID and ISDROPPED = 0
                        for xml raw ('ITEM'), type, elements, root('MEMBERS'),BINARY BASE64
                    );

          set @MEMBERSHIPS =  
          (
            select 
                MEMBERSHIPLEVELID,
                MEMBERSHIPLEVELTERMID,
                MEMBERSHIPLEVELTYPECODEID,
                NUMBEROFCHILDREN,
                COMMENTS,
                case @CLEARGIFTMEMBERSHIP when 1 then 0 else ISGIFT end as ISGIFT,
                SENDRENEWALCODE,
                NULL,
              @MEMBERS,
              case @CLEARGIFTMEMBERSHIP when 1 then null else GIVENBYID end as GIVENBYID
            from dbo.MEMBERSHIP 
            where MEMBERSHIP.ID = @SINGLEAPPLICATIONID
            for xml raw('ITEM'),type,elements,root('MEMBERSHIPFIELDS'),BINARY BASE64
            );

                end
                else
                begin                        
                    declare @BATCHREVENUEMEMBERSHIPID uniqueidentifier = null;

                    select
                      @BATCHREVENUEMEMBERSHIPID = BATCHREVENUEAPPLICATIONMEMBERSHIP.id
                    from   
                      DBO.BATCHREVENUEAPPLICATION inner join 
                      DBO.BATCHREVENUEAPPLICATIONMEMBERSHIP on BATCHREVENUEAPPLICATIONMEMBERSHIP.ID = BATCHREVENUEAPPLICATION.BATCHREVENUEAPPLICATIONMEMBERSHIPID 
                    where  
                      BATCHREVENUEAPPLICATION.BATCHREVENUEID  = @BATCHROWID and 
                      BATCHREVENUEAPPLICATIONMEMBERSHIP.MEMBERSHIPID = @SINGLEAPPLICATIONID;  

                    set @members =
                    (
                      select 
                        ID,
                        CONSTITUENTID,
                        ISPRIMARY,
                        dbo.UFN_REVENUEBATCH_GETMEMBERSHIPMEMBERMEMBERSHIPCARDS_TOITEMLISTXML(ID)
                      from
                        dbo.BATCHREVENUEAPPLICATIONMEMBERSHIPMEMBER
                      where
                        BATCHREVENUEAPPLICATIONMEMBERSHIPID = @BATCHREVENUEMEMBERSHIPID
                      for xml raw('ITEM'), type, elements, root('MEMBERS'), binary base64
                    );

                end

        if @MEMBERSHIPS is NULL
                  set @MEMBERSHIPS =  
                          (
                            select 
                              MEMBERSHIPLEVELID,
                              MEMBERSHIPLEVELTERMID,
                              MEMBERSHIPLEVELTYPECODEID,
                              NUMBEROFCHILDREN,
                              COMMENTS,
                              case @CLEARGIFTMEMBERSHIP when 1 then 0 else ISGIFT end as ISGIFT,
                              SENDRENEWALCODE,
                              EXPIRATIONDATE,          
                              @members,
                              case @CLEARGIFTMEMBERSHIP when 1 then null else GIVENBYID end as GIVENBYID
                            from DBO.BATCHREVENUEAPPLICATIONMEMBERSHIP 
                            where BATCHREVENUEAPPLICATIONMEMBERSHIP.ID = @BATCHREVENUEMEMBERSHIPID
                            for xml raw('ITEM'),type,elements,root('MEMBERSHIPFIELDS'),BINARY BASE64
                          );


                    exec dbo.USP_MEMBERSHIP_ADDPAYMENT
                        @REVENUEID = @REVENUEID,
                        @CONSTITUENTID = @CONSTITUENTID,
                        @MEMBERSHIPID = @SINGLEAPPLICATIONID,
                        @AMOUNT = @APPLICATIONAMOUNT,
                        @TRANSACTIONDATE = @DATE,
                        @MEMBERSHIPS = @MEMBERSHIPS,
                        @CREATIONDATE = @CREATIONDATE,
                        @CHANGEAGENTID = @CHANGEAGENTID;
                end
            end try
            begin catch
                exec dbo.USP_RAISE_ERROR;
            end catch

            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)