UFN_REVENUE_GETREVENUESTREAMS

            CREATE function dbo.UFN_REVENUE_GETREVENUESTREAMS
            (
                @TRANSACTIONID uniqueidentifier
            )
            returns @RESULT table
            (
                ID uniqueidentifier,
                CONSTITUENTID uniqueidentifier, 
                CONSTITUENTNAME nvarchar(255),
                APPLIED money,
                BALANCE money,
                AMOUNTDUE money,
                DATEDUE datetime,
                TYPECODE tinyint,
                RECEIPTAMOUNT money,
                DONOTRECEIPT bit,
                DESCRIPTION nvarchar(255)
            )
            as
            begin
                insert into @RESULT 
                --PLEDGE PAYMENTS

                select
                    dbo.UFN_PLEDGE_GETPLEDGEFROMPAYMENT(REVENUE.ID) as [ID],
                    CONSTITUENT.ID as [CONSTITUENTID],
                    CONSTITUENT.NAME as [CONSTITUENTNAME],
                    REVENUESPLIT.AMOUNT as [APPLIED],
                    dbo.UFN_PLEDGE_GETBALANCE(dbo.UFN_PLEDGE_GETPLEDGEFROMPAYMENT(REVENUE.ID)) + REVENUE.AMOUNT as [BALANCE],
                    dbo.UFN_INSTALLMENT_GETOLDINSTALLMENTBALANCE(dbo.UFN_REVENUE_GETOLDNEXTINSTALLMENT(dbo.UFN_PLEDGE_GETPLEDGEFROMPAYMENT(REVENUE.ID), REVENUE.ID), REVENUE.ID) as [AMOUNTDUE],
                    INSTALLMENT.DATE as [DATEDUE],
                    case
                        when REVENUESPLIT.APPLICATIONCODE = 2 then 1
                        when REVENUESPLIT.APPLICATIONCODE = 7 then 3
                           else null end as [TRANSACTIONTYPECODE],
                    REVENUE.RECEIPTAMOUNT as [RECEIPTAMOUNT],
                    [PLEDGEREVENUE].DONOTRECEIPT as [DONOTRECEIPT],
                    '' as [DESCRIPTION]
                from dbo.REVENUE
                inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
                left join dbo.INSTALLMENT on INSTALLMENT.ID = dbo.UFN_REVENUE_GETOLDNEXTINSTALLMENT(dbo.UFN_PLEDGE_GETPLEDGEFROMPAYMENT(REVENUE.ID), REVENUE.ID)
                left join dbo.REVENUE as [PLEDGEREVENUE] on [PLEDGEREVENUE].ID = INSTALLMENT.REVENUEID
                left join dbo.CONSTITUENT on CONSTITUENT.ID = [PLEDGEREVENUE].CONSTITUENTID
                where REVENUE.ID = @TRANSACTIONID and REVENUESPLIT.APPLICATIONCODE in (2, 7)

                union all
                --RECURRING GIFT PAYMENTS                        

                select 
                    SOURCEREVENUEID as [ID],
                    CONSTITUENT.ID as [CONSTITUENTID],
                    CONSTITUENT.NAME as [CONSTITUENTNAME],
                    REVENUE.AMOUNT as [APPLIED],
                    0 as [BALANCE],
                    TEMPLATE.AMOUNT as [AMOUNTDUE],
                    RECURRINGGIFTACTIVITY.SCHEDULEDATE as [DATEDUE],
                    2 as [TRANSACTIONTYPECODE],
                    REVENUE.RECEIPTAMOUNT as [RECEIPTAMOUNT],
                    REVENUE.DONOTRECEIPT as [DONOTRECEIPT],
                    '' as [DESCRIPTION]
                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 = SOURCEREVENUEID
                where REVENUE.ID = @TRANSACTIONID and REVENUESPLIT.APPLICATIONCODE  = 3

                union all
                --EVENT REGISTRATION FEE PAYMENTS

                select
                    REGISTRANT.ID,
                    CONSTITUENT.ID as [CONSTITUENTID],
                    CONSTITUENT.NAME as [CONSTITUENTNAME],
                    REVENUESPLIT.AMOUNT as [APPLIED],
                    dbo.UFN_EVENTREGISTRANT_GETBALANCE(REGISTRANT.ID) + REVENUESPLIT.AMOUNT as [BALANCE],
                    dbo.UFN_EVENTREGISTRANT_GETBALANCE(REGISTRANT.ID) + REVENUESPLIT.AMOUNT as [AMOUNTDUE],
                    [EVENT].STARTDATE as [DATEDUE], --JamesWill CR260844-113006 2007/03/15 Use the event start date as the due date

                    6 as [TRANSACTIONTYPECODE],
                    REVENUE.RECEIPTAMOUNT as [RECEIPTAMOUNT],
                    REVENUE.DONOTRECEIPT as [DONOTRECEIPT],
                    [EVENT].NAME as [DESCRIPTION]
                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 = @TRANSACTIONID

            order by CONSTITUENTID, TRANSACTIONTYPECODE;

            return;
        end