USP_DATALIST_CONSTITUENTUNMATCHINGGIFTREVENUE

                CREATE procedure dbo.USP_DATALIST_CONSTITUENTUNMATCHINGGIFTREVENUE
                (
                    @CONSTITUENTID uniqueidentifier = null    
                )
                as
                set nocount on;

                select
                    REVENUE.ID, 
                    REVENUE.TYPE TRANSACTIONTYPE,
                    -- Translate typecodes to be consistent with UFN_CONSTITUENTUNREALIZEDREVENUE_BYACCOUNTSYSTEM

                    case REVENUE.TYPECODE 
                        when 6 then 9 
                        when 8 then 10 
                        else REVENUE.TYPECODE 
                    end TRANSACTIONTYPECODE,
                    CONSTITUENT.ID,
                    CONSTITUENT.NAME,
                    dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID),
                    dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(dbo.UFN_REVENUE_GETNEXTINSTALLMENT(REVENUE.ID)),
                    INSTALLMENT.DATE,
                    MATCHINGGIFTCONSTITUENT.NAME,
                    (
                        select
                            dbo.UDA_BUILDLIST(DESIGNATION.USERID) as DESIGNATIONLIST 
                        from
                            dbo.FINANCIALTRANSACTIONLINEITEM
                            inner join dbo.REVENUESPLIT_EXT REVENUESPLIT on REVENUESPLIT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                            inner join dbo.DESIGNATION on REVENUESPLIT.DESIGNATIONID = DESIGNATION.ID
                        where
                            FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = REVENUE.ID
                    ) as DESIGNATIONLIST,
                    REVENUE.TRANSACTIONCURRENCYID,
                    null,
              null,
              null as MEMBERSHIPPROGRAMNAME,
              null as MEMBERSHIPLEVELNAME
                from 
                    dbo.FINANCIALTRANSACTION REVENUE                
                    inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
                    -- Adding join predicate on REVENUE.ID = INSTALLMENT.REVENUEID even though it isn't necessary to get the correct result

                    -- since without it, a table scan is done on INSTALLMENT.  

                    inner join dbo.INSTALLMENT on (REVENUE.ID = INSTALLMENT.REVENUEID and INSTALLMENT.ID = dbo.UFN_REVENUE_GETNEXTINSTALLMENT(REVENUE.ID))
                    inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
                    left outer join REVENUEMATCHINGGIFT on REVENUE.ID = REVENUEMATCHINGGIFT.ID
                    left outer join REVENUE MATCHINGGIFTREVENUE on MATCHINGGIFTREVENUE.ID = REVENUEMATCHINGGIFT.MGSOURCEREVENUEID
                    left outer join CONSTITUENT MATCHINGGIFTCONSTITUENT on MATCHINGGIFTREVENUE.CONSTITUENTID = MATCHINGGIFTCONSTITUENT.ID
                where 
                    CONSTITUENT.ID = @CONSTITUENTID
                    and dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) > 0
                    and REVENUESCHEDULE.ISPENDING = 0    --Isn't Pending

                    and (REVENUEMATCHINGGIFT.ISACTIVE = 1 or REVENUE.TYPECODE <> 3)
                          and REVENUE.TYPECODE <> 15

                union all

                select
                    REVENUE.ID, 
                    REVENUE.TYPE TRANSACTIONTYPE,
                    REVENUE.TYPECODE TRANSACTIONTYPECODE,
                    CONSTITUENT.ID,
                    CONSTITUENT.NAME,
                    dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID),
                    NEXTINSTALLMENT.BALANCE,
                    NEXTINSTALLMENT.DATE,
                    MATCHINGGIFTCONSTITUENT.NAME,
                    (
                        select
                            dbo.UDA_BUILDLIST(DESIGNATION.USERID) as DESIGNATIONLIST 
                        from 
                            dbo.FINANCIALTRANSACTIONLINEITEM 
                            inner join dbo.REVENUESPLIT_EXT REVENUESPLIT on REVENUESPLIT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                            inner join dbo.DESIGNATION on REVENUESPLIT.DESIGNATIONID = DESIGNATION.ID
                        where
                            FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = REVENUE.ID
                    ) as DESIGNATIONLIST,
                    REVENUE.TRANSACTIONCURRENCYID,
                    SPONSORSHIPINFO.SPONSORSHIPOPPORTUNITY as SPONSORSHIPOPPORTUNITY,
              case 
                        when (
                            select top 1 TYPECODE 
                            from dbo.REVENUESPLIT 
                            where REVENUESPLIT.REVENUEID = REVENUE.ID
                        ) = 17    
                            then 1 
                        else 0 
                        end SPONSORSHIPRGADDITIONALGIFT,
              MEMBERSHIPINFO.PROGRAMNAME as MEMBERSHIPPROGRAMNAME,
              MEMBERSHIPINFO.LEVELNAME as MEMBERSHIPLEVELNAME
                from 
                    dbo.FINANCIALTRANSACTION REVENUE                
                    inner join dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
                    inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
                    left outer join REVENUEMATCHINGGIFT on REVENUE.ID = REVENUEMATCHINGGIFT.ID
                    left outer join FINANCIALTRANSACTION MATCHINGGIFTREVENUE on MATCHINGGIFTREVENUE.ID = REVENUEMATCHINGGIFT.MGSOURCEREVENUEID
                    left outer join CONSTITUENT MATCHINGGIFTCONSTITUENT on MATCHINGGIFTREVENUE.CONSTITUENTID = MATCHINGGIFTCONSTITUENT.ID
                    left outer join(
                        select
                            SP.REVENUEID,
                            coalesce(
                                (
                                    select NAME 
                                    from SPONSORSHIPOPPORTUNITYCHILD 
                                    where ID=S.SPONSORSHIPOPPORTUNITYID
                                ),  
                                (
                                    select NAME 
                                    from SPONSORSHIPOPPORTUNITYPROJECT 
                                    where ID = S.SPONSORSHIPOPPORTUNITYID
                                )
                            ) SPONSORSHIPOPPORTUNITY  
                        from dbo.REVENUESPLIT SP
                            inner join SPONSORSHIP S on S.REVENUESPLITID = SP.ID  
                            inner join SPONSORSHIPOPPORTUNITY SO on SO.ID = S.SPONSORSHIPOPPORTUNITYID  
                        where S.ISMOSTRECENTFORCOMMITMENT = 1  
                    ) SPONSORSHIPINFO on SPONSORSHIPINFO.REVENUEID = REVENUE.ID
          left outer join(
                  select
                      MS.REVENUEID,
                      MP.NAME as [PROGRAMNAME],
                      ML.NAME as [LEVELNAME]
                  from
                      dbo.REVENUESPLIT MS
                      inner join MEMBERSHIPTRANSACTION MT on MT.REVENUESPLITID = MS.ID
                      inner join MEMBERSHIP M on M.ID = MT.MEMBERSHIPID
                      inner join MEMBERSHIPPROGRAM MP on MP.ID = M.MEMBERSHIPPROGRAMID
                      inner join MEMBERSHIPLEVEL ML on ML.ID = M.MEMBERSHIPLEVELID
              ) MEMBERSHIPINFO on MEMBERSHIPINFO.REVENUEID = REVENUE.ID
                outer apply dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENTINFO(REVENUE.ID,null) NEXTINSTALLMENT
                where 
                    CONSTITUENT.ID = @CONSTITUENTID
                    and REVENUE.TYPECODE = 2                --Recurring Gift

                    and REVENUESCHEDULE.STATUSCODE in (0,5)        --Active or lapsed

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

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


                union all

                select
                    REGISTRANT.ID,
                    'Event Registration',
                    6, --Event Registration,

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

                    [EVENT].NAME,
                    '' as DESIGNATIONLIST,
                    EVENT.BASECURRENCYID as TRANSACTIONCURRENCYID,
             null,
       null,
       null as MEMBERSHIPPROGRAMNAME,
       null as MEMBERSHIPLEVELNAME
                from 
                    dbo.REGISTRANT
                    inner join dbo.CONSTITUENT on CONSTITUENT.ID = REGISTRANT.CONSTITUENTID
                    inner join dbo.[EVENT] on [EVENT].ID = REGISTRANT.EVENTID
                where 
                    CONSTITUENT.ID =  @CONSTITUENTID
                    and dbo.UFN_EVENTREGISTRANT_GETBALANCE(REGISTRANT.ID) > 0

                ; --Obligatory semi-colon