UFN_REVENUEUPDATEBATCH_GETAPPLICATIONSTREAM

    CREATE function dbo.UFN_REVENUEUPDATEBATCH_GETAPPLICATIONSTREAM
            (
                @REVENUEID uniqueidentifier,
        @CONSTITUENTID uniqueidentifier
            )
        returns @RESULT table
    (
      ID uniqueidentifier,
        APPLICATIONID uniqueidentifier,
        CONSTITUENTID uniqueidentifier,
        CONSTITUENTNAME nvarchar(255),
        APPLIED money,
        BALANCE money,
        AMOUNTDUE money,
        DATEDUE datetime,
        TYPECODE tinyint,
        DESCRIPTION nvarchar(255),
    MEMBERSHIPS xml,
        DESIGNATIONLIST nvarchar(512),    
        SPONSORSHIPOPPORTUNITY nvarchar(255),
    REVENUESPLITID uniqueidentifier,
    TRANSACTIONCURRENCYID uniqueidentifier,
    APPLICATIONCURRENCYID uniqueidentifier,
    PDACCOUNTSYSTEMID uniqueidentifier,
    SPONSORSHIPRGADDITIONALGIFT bit,
        RECEIPTAMOUNT money
    )
            as
            begin    


    insert into @RESULT
    -- Payments towards:  Pledge (2), Planned Gift (6), Matching Gift (7), Grant Award (8), Donor Challenge (13)

    select
      '00000000-0000-0000-0000-000000000000' as ID, 
        INSTALLMENTSPLITPAYMENT.PLEDGEID as APPLICATIONID,
        CONSTITUENT.ID as CONSTITUENTID,
        (  
            select case PLEDGEREVENUE.TRANSACTIONTYPECODE  
            when 3 then  
            CONSTITUENT.NAME + ' - ' +  
            (  
                select   
                C1.NAME + ' (' + C1.LOOKUPID + ')'
                from   
                dbo.REVENUEMATCHINGGIFT MG1   
                inner join dbo.REVENUE R1 on R1.ID = MG1.MGSOURCEREVENUEID   
                inner join dbo.CONSTITUENT C1 on C1.ID = R1.CONSTITUENTID   
                where   
                MG1.ID = PLEDGEREVENUE.ID  
            )  
            else   
            CONSTITUENT.NAME  
            end  
        ) as CONSTITUENTNAME,  
        REVENUESPLIT.TRANSACTIONAMOUNT as [APPLIED],
        dbo.UFN_PLEDGE_GETBALANCE(INSTALLMENTSPLITPAYMENT.PLEDGEID) as [BALANCE],
        case 
            when [PLEDGEREVENUE].TRANSACTIONTYPECODE in (1 ,3, 4, 6, 8) 
                then dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(dbo.UFN_REVENUE_GETNEXTINSTALLMENT([PLEDGEREVENUE].ID))
            when [PLEDGEREVENUE].TRANSACTIONTYPECODE = 2 and REVENUESCHEDULE.STATUSCODE = 0 
                then NEXTINSTALLMENT.BALANCE
            when [PLEDGEREVENUE].TRANSACTIONTYPECODE = 2 and REVENUESCHEDULE.STATUSCODE in (1, 2, 3, 4) 
                then 0
            else null 
        end as [AMOUNTDUE],
        case 
            when [PLEDGEREVENUE].TRANSACTIONTYPECODE in (1, 3, 4, 6, 8) 
                then INSTALLMENT.DATE
            when [PLEDGEREVENUE].TRANSACTIONTYPECODE = 2 
                then NEXTINSTALLMENT.DATE
            else null
        end as [DATEDUE],
        case PLEDGEREVENUE.TRANSACTIONTYPECODE when 6 then 9 when 8 then 10 else PLEDGEREVENUE.TRANSACTIONTYPECODE end as REVENUETYPECODE,
        '' as DESCRIPTION,
        null as MEMBERSHIPS,
    (select VANITYNAME from dbo.DESIGNATION where ID = REVENUESPLIT.DESIGNATIONID) as DESIGNATIONLIST,
        '' as SPONSORSHIPOPPORTUNITY,
    REVENUESPLIT.ID as REVENUESPLITID,
        REVENUESPLIT.TRANSACTIONCURRENCYID,
        INSTALLMENTSPLITPAYMENT.APPLICATIONCURRENCYID,
    null as PDACCOUNTSYSTEMID,
    0 SPONSORSHIPRGADDITIONALGIFT,
        REVENUE.RECEIPTAMOUNT
    from dbo.REVENUE
    inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
    inner join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.PAYMENTID = REVENUESPLIT.ID
    inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID
    left join dbo.REVENUE as [PLEDGEREVENUE] on [PLEDGEREVENUE].ID = INSTALLMENTSPLITPAYMENT.PLEDGEID
    left join dbo.INSTALLMENT on INSTALLMENT.ID = dbo.UFN_REVENUE_GETNEXTINSTALLMENT([PLEDGEREVENUE].ID)
    left join dbo.CONSTITUENT on CONSTITUENT.ID = [PLEDGEREVENUE].CONSTITUENTID
    left join dbo.REVENUESPLITGIFTAID on REVENUESPLITGIFTAID.ID = REVENUESPLIT.ID
    left join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = [PLEDGEREVENUE].ID
    outer apply dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENTINFO([PLEDGEREVENUE].ID,null) NEXTINSTALLMENT
    where REVENUE.ID = @REVENUEID and REVENUESPLIT.APPLICATIONCODE in (2, 6, 7, 8, 13)
    group by
        REVENUESPLIT.ID,
        INSTALLMENTSPLITPAYMENT.PLEDGEID,
        REVENUESPLIT.APPLICATIONCODE,
        PLEDGEREVENUE.TRANSACTIONTYPECODE,
        PLEDGEREVENUE.CONSTITUENTID,
        CONSTITUENT.ID,
        REVENUESPLIT.DESIGNATIONID,
        CONSTITUENT.NAME,
        PLEDGEREVENUE.ID,
        REVENUESPLIT.TRANSACTIONAMOUNT,
        REVENUESPLIT.TRANSACTIONCURRENCYID,
        INSTALLMENTSPLITPAYMENT.APPLICATIONCURRENCYID,
        REVENUESCHEDULE.STATUSCODE,
        INSTALLMENT.DATE,
        REVENUE.RECEIPTAMOUNT,
        NEXTINSTALLMENT.BALANCE,
        NEXTINSTALLMENT.DATE

    union all
    -- recurring gift payments

    select                 
      '00000000-0000-0000-0000-000000000000' as ID, 
        TEMPLATE.ID as APPLICATIONID,
        CONSTITUENT.ID as CONSTITUENTID,
        CONSTITUENT.NAME,  
        REVENUESPLIT.TRANSACTIONAMOUNT as APPLIED,
        TEMPLATESPLIT.TRANSACTIONAMOUNT as BALANCE,
        0.00 as AMOUNTDUE,
        RECURRINGGIFTACTIVITY.SCHEDULEDATE as DATEDUE,
        TEMPLATE.TRANSACTIONTYPECODE as REVENUETYPECODE,
        '' as DESCRIPTION,
        null as MEMBERSHIPS,
        (select VANITYNAME from dbo.DESIGNATION where ID = REVENUESPLIT.DESIGNATIONID) as DESIGNATIONLIST,
        (select coalesce((select NAME from SPONSORSHIPOPPORTUNITYCHILD where ID=SPONSORSHIP.SPONSORSHIPOPPORTUNITYID),  
                     (select NAME from SPONSORSHIPOPPORTUNITYPROJECT where ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID)) opportunity 
            from dbo.SPONSORSHIP 
            inner join dbo.SPONSORSHIPOPPORTUNITY OPP on OPP.ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID
            where SPONSORSHIP.ID = dbo.UFN_SPONSORSHIP_ID_FROM_REVENUEID( TEMPLATE.ID)
            and SPONSORSHIP.ISMOSTRECENTFORCOMMITMENT = 1) as SPONSORSHIPOPPORTUNITY,
    REVENUESPLIT.ID as REVENUESPLITID,
        REVENUESPLIT.TRANSACTIONCURRENCYID,
        RECURRINGGIFTACTIVITY.APPLICATIONCURRENCYID,
    null as PDACCOUNTSYSTEMID,
    case when REVENUESPLIT.TYPECODE = 17 then 1 else 0 end SPONSORSHIPRGADDITIONALGIFT,
        REVENUE.RECEIPTAMOUNT
    from dbo.REVENUE
    inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
    inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
    inner join dbo.RECURRINGGIFTACTIVITY on PAYMENTREVENUEID = REVENUESPLIT.ID
    inner join dbo.REVENUE TEMPLATE on TEMPLATE.ID = RECURRINGGIFTACTIVITY.SOURCEREVENUEID
    left outer join dbo.REVENUESPLIT TEMPLATESPLIT on TEMPLATESPLIT.REVENUEID = TEMPLATE.ID and TEMPLATESPLIT.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID
    left join dbo.REVENUESPLITGIFTAID on REVENUESPLITGIFTAID.ID = REVENUESPLIT.ID
    where REVENUE.ID = @REVENUEID and REVENUESPLIT.APPLICATIONCODE  = 3

    union all

    -- Membership

    select
      '00000000-0000-0000-0000-000000000000' as ID, 
        MEMBERSHIP.ID as APPLICATIONID,
        CONSTITUENT.ID,  
        CONSTITUENT.NAME,    
        REVENUESPLIT.TRANSACTIONAMOUNT as [APPLIED], 
        0 as BALANCE,
        0 as AMOUNTDUE,  
        MEMBERSHIP.EXPIRATIONDATE,  
        5 as REVENUETYPECODE, --Membership  

        dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID) + ' - ' + dbo.UFN_MEMBERSHIPLEVEL_GETNAME(MEMBERSHIP.MEMBERSHIPLEVELID) as description,
        (select 
        '00000000-0000-0000-0000-000000000000' as ID, 
        MEMBERSHIPPROGRAMID, 
        MEMBERSHIPLEVELID, 
        MEMBERSHIPLEVELTERMID, 
        MEMBERSHIPLEVELTYPECODEID, 
        NUMBEROFCHILDREN, 
        COMMENTS, 
        ISGIFT, 
        SENDRENEWALCODE, 
        EXPIRATIONDATE, 
        GIVENBYID, 
        (select
            MEMBER.ID,
            MEMBER.CONSTITUENTID,
            MEMBER.ISPRIMARY,
            (select 
                MEMBERSHIPCARD.ID,
                MEMBERSHIPCARD.NAMEONCARD,
                MEMBERSHIPCARD.EXPIRATIONDATE
                from dbo.MEMBERSHIPCARD
                where MEMBERSHIPCARD.MEMBERID = MEMBER.ID
                for xml raw('ITEM'),type,elements,BINARY BASE64) MEMBERSHIPCARDS
        from dbo.MEMBER
        where MEMBER.MEMBERSHIPID = MEMBERSHIPSUB.ID 
        for xml raw('ITEM'),type,elements,BINARY BASE64) MEMBERS
    from dbo.MEMBERSHIP MEMBERSHIPSUB
    where MEMBERSHIPSUB.ID = MEMBERSHIP.ID
    for xml raw('ITEM'),type,elements,BINARY BASE64) MEMBERSHIPS,
        '' as DESIGNATIONLIST,  
        '' as SPONSORSHIPOPPORTUNITY,
    REVENUESPLIT.ID as REVENUESPLITID,
      REVENUESPLIT.TRANSACTIONCURRENCYID,
      REVENUESPLIT.TRANSACTIONCURRENCYID [APPLICATIONCURRENCYID],
    null as PDACCOUNTSYSTEMID,
    0 SPONSORSHIPRGADDITIONALGIFT,
        REVENUE.RECEIPTAMOUNT
    from dbo.MEMBERSHIP
    inner join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIP.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
    inner join dbo.REVENUESPLIT on MEMBERSHIPTRANSACTION.REVENUESPLITID = REVENUESPLIT.ID
    inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
    inner join dbo.CONSTITUENT on CONSTITUENT.ID = @CONSTITUENTID
    inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID 
    where REVENUE.ID = @REVENUEID and REVENUESPLIT.APPLICATIONCODE = 5

    union all

    -- Event Reg Payments

    select
      '00000000-0000-0000-0000-000000000000' as ID, 
        REGISTRANT.ID as APPLICATIONID,
        CONSTITUENT.ID as CONSTITUENTID,
        CONSTITUENT.NAME as CONSTITUENTNAME,
        REVENUESPLIT.TRANSACTIONAMOUNT as [APPLIED],
        dbo.UFN_EVENTREGISTRANT_GETBALANCE(REGISTRANT.ID) as [BALANCE],
        dbo.UFN_EVENTREGISTRANT_GETBALANCE(REGISTRANT.ID) as AMOUNTDUE,
        EVENT.STARTDATE as [DATEDUE],
        6 as [REVENUETYPECODE],
        EVENT.NAME as [DESCRIPTION],
    null as MEMBERSHIPS,
        case REVENUESPLIT.TYPECODE when 1 then ''
        else (select VANITYNAME from dbo.DESIGNATION where ID = REVENUESPLIT.DESIGNATIONID) --Use designation list to determine if this split is a charitable event registration split (event with designations)

        end as DESIGNATIONLIST, 
        '' as SPONSORSHIPOPPORTUNITY,
    REVENUESPLIT.ID as REVENUESPLITID,
        REVENUESPLIT.TRANSACTIONCURRENCYID,
        [PAYMENT].APPLICATIONCURRENCYID,
    null as PDACCOUNTSYSTEMID,
    0 SPONSORSHIPRGADDITIONALGIFT,
        REVENUE.RECEIPTAMOUNT
    from dbo.REGISTRANT
    inner join dbo.CONSTITUENT on CONSTITUENT.ID = REGISTRANT.CONSTITUENTID
    inner join dbo.[EVENT] on [EVENT].ID = REGISTRANT.EVENTID
    inner join dbo.EVENTREGISTRANTPAYMENT as [PAYMENT] on [PAYMENT].REGISTRANTID = REGISTRANT.ID
    inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = [PAYMENT].PAYMENTID
    inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
    where REVENUE.ID = @REVENUEID and 
    REVENUESPLIT.APPLICATIONCODE = 1;

  declare @APPLICATIONCOUNT int;
  select @APPLICATIONCOUNT = count(*) from @RESULT
  if @APPLICATIONCOUNT > 0
    begin
        insert into @RESULT
        select 
           null as ID,
           REVENUEID,
               CONSTITUENTID,
               CONSTITUENTNAME,
               APPLIED,
               BALANCE,
               AMOUNTDUE,
               DATEDUE,
               REVENUETYPECODE,
               DESCRIPTION,
           null as MEMBERSHIPS,
               DESIGNATIONLIST,
               SPONSORSHIPOPPORTUNITY,
           null as REVENUESPLITID,
           null as TRANSACTIONCURRENCYID,
           APPLICATIONCURRENCYID,
           PDACCOUNTSYSTEMID,
           SPONSORSHIPRGADDITIONALGIFT,
                     0 as RECEIPTAMOUNT
        from dbo.UFN_CONSTITUENTUNREALIZEDREVENUE(@CONSTITUENTID, 0, null, null, null) OPENCOMMITMENTS
        where 
            not exists (select 'X' from @RESULT APPLIED where APPLIED.APPLICATIONID = OPENCOMMITMENTS.REVENUEID) and
            OPENCOMMITMENTS.REVENUETYPECODE <> 100; -- Exclude opportunities since they aren't supported in batch

        end

            return;
        end