UFN_CONSTITUENT_GETREVENUESTREAMS

            CREATE function dbo.UFN_CONSTITUENT_GETREVENUESTREAMS
            (
                @CONSTITUENTID 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

                declare @CURRENTDATE datetime;
                set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());            

                declare @HOUSEHOLDSCANBEDONORS bit;
                set @HOUSEHOLDSCANBEDONORS = dbo.UFN_INSTALLATIONINFO_GETHOUSEHOLDSCANBEDONORS();

                declare @HOUSEHOLDID uniqueidentifier;
                if @HOUSEHOLDSCANBEDONORS = 1
                    select 
                        top(1) @HOUSEHOLDID = GROUPMEMBER.GROUPID
                    from 
                        dbo.GROUPMEMBER
                    left outer join 
                        dbo.GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID
                    where 
                        GROUPMEMBER.MEMBERID = @CONSTITUENTID
                    and 
                        GROUPDATA.GROUPTYPECODE = 0
                    and 
                        dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GROUPMEMBER.ID) = 1;

                -- include related household records

                with CONSTITUENTCTE as
                (
                    select 
                        @HOUSEHOLDID as ID
                    union
                    select 
                        case when dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@CONSTITUENTID) = 1 then 
                            (case when @HOUSEHOLDSCANBEDONORS = 1 then @CONSTITUENTID else null end)
                            else @CONSTITUENTID
                        end
                    union
                    select 
                        GM.MEMBERID
                    from 
                        dbo.GROUPMEMBER GM
                    left outer join 
                        dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                    where 
                        GM.GROUPID = @HOUSEHOLDID
                    or
                        GM.GROUPID = @CONSTITUENTID
                    and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
                            or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
                            or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE))
                )
                insert into @RESULT
                    select    --Pledges and MGPledges

                        REVENUE.ID,
                        CONSTITUENT.ID,
                        CONSTITUENT.NAME,
                        0,
                        dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) as [Balance],
                        dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(dbo.UFN_REVENUE_GETNEXTINSTALLMENT(REVENUE.ID)),
                        INSTALLMENT.DATE,
                        REVENUE.TRANSACTIONTYPECODE,
                        0 as [RECEIPTAMOUNT],
                        0 as [DONOTRECEIPT],
                        ''
                    from dbo.REVENUE                    
                    inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
                    inner join dbo.INSTALLMENT on INSTALLMENT.ID = dbo.UFN_REVENUE_GETNEXTINSTALLMENT(REVENUE.ID)
                    inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
                    inner join CONSTITUENTCTE on CONSTITUENTCTE.ID = REVENUE.CONSTITUENTID
                    where dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) > 0
                    and REVENUESCHEDULE.ISPENDING = 0    --Isn't Pending

                union all
                    select    --Recurring Gifts

                        REVENUE.ID, 
                        CONSTITUENT.ID,
                        CONSTITUENT.NAME,
                        0,
                        REVENUE.AMOUNT as [Balance],
                        NEXTINSTALLMENT.BALANCE,
                        NEXTINSTALLMENT.DATE,
                        REVENUE.TRANSACTIONTYPECODE,
                        0 as [RECEIPTAMOUNT],
                        0 as [DONOTRECEIPT],
                        ''
                    from dbo.REVENUE                    
                    inner join dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
                    outer apply dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENTINFO(REVENUE.ID,null) NEXTINSTALLMENT
                    inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
                    inner join CONSTITUENTCTE on CONSTITUENTCTE.ID = REVENUE.CONSTITUENTID
                    where REVENUE.TRANSACTIONTYPECODE = 2                --Recurring Gift

                        and REVENUESCHEDULE.STATUSCODE  in (0,5)        --Active & Lapsed

                        and REVENUESCHEDULE.ISPENDING = 0                    --Isn't pending

                        and REVENUE.AMOUNT > 0                    --Has Value???

                union all
                    select -- Event registrations

                        REGISTRANT.ID,
                        CONSTITUENT.ID,
                        CONSTITUENT.NAME,
                        0,
                        dbo.UFN_EVENTREGISTRANT_GETBALANCE(REGISTRANT.ID) as [Balance],  
                        dbo.UFN_EVENTREGISTRANT_GETBALANCE(REGISTRANT.ID),  
                        [EVENT].STARTDATE,        --JamesWill CR260844-113006 2007/03/15 Use the event start date as the due date

                        6 as [REVENUETYPECODE], --Event Registration Fee

                        0 as [RECEIPTAMOUNT],
                        0 as [DONOTRECEIPT],
                        [EVENT].NAME
                    from dbo.REGISTRANT 
                    inner join dbo.CONSTITUENT on CONSTITUENT.ID = REGISTRANT.CONSTITUENTID
                    inner join dbo.[EVENT] on [EVENT].ID = REGISTRANT.EVENTID
                    inner join CONSTITUENTCTE on CONSTITUENTCTE.ID = REGISTRANT.CONSTITUENTID
                    where dbo.UFN_EVENTREGISTRANT_GETBALANCE(REGISTRANT.ID) > 0
                union all
                    select
                        O.ID,
                        C.ID,
                        C.NAME,
                        0,
                        O.AMOUNT - dbo.UFN_OPPORTUNITY_GETAMOUNTPAID(O.ID),
                        null, -- amount due

                        null, -- date due

                        100, -- arbitrary application type code for opportunities, resolved in USP_REVENUE_APPLYTOREVENUESTREAMS

                        0,
                        0,
                        dbo.UFN_OPPORTUNITY_GETDESCRIPTION(O.ID)
                    from 
                        dbo.OPPORTUNITY O
                    inner join 
                        dbo.PROSPECTPLAN PP on PP.ID = O.PROSPECTPLANID
                    inner join 
                        CONSTITUENTCTE on CONSTITUENTCTE.ID = PP.PROSPECTID
                    inner join 
                        dbo.OPPORTUNITYDESIGNATION on OPPORTUNITYDESIGNATION.OPPORTUNITYID = O.ID
                    left outer join 
                        dbo.CONSTITUENT C on C.ID = PP.PROSPECTID;

                return;
            end