USP_DATALIST_PLANNEDGIFTADDITIONREVENUE

Returns all revenue records associated with a planned gift addition.
Parameters

Parameter	Parameter Type	Mode	Description
@PLANNEDGIFTADDITIONID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
Definition

Copy


              CREATE procedure dbo.USP_DATALIST_PLANNEDGIFTADDITIONREVENUE
                (
                    @PLANNEDGIFTADDITIONID uniqueidentifier
                )
                as
                set nocount on;

                --Pull in the associated revenue

                select
                    REVENUE.ID as [REVENUEID],
                    REVENUE.DATE as [REVENUEDATE],
                    REVENUE.TRANSACTIONAMOUNT as [REVENUEAMOUNT], 
                    REVENUE.TRANSACTIONTYPE as [REVENUETRANSACTIONTYPE],
                    REVENUE.CONSTITUENTID as [REVENUECONSTITUENTID],
                    NF.NAME as [REVENUECONSTITUENT],
                    null as [PARENTID],
                    dbo.UFN_PLEDGE_GETAMOUNTPAID(PLANNEDGIFTADDITIONREVENUE.REVENUEID) as [TOTALPAYMENTS],
                    REVENUE.TRANSACTIONCURRENCYID as [REVENUETRANSACTIONCURRENCYID]
                from dbo.PLANNEDGIFTADDITIONREVENUE
                inner join dbo.REVENUE on REVENUE.ID = PLANNEDGIFTADDITIONREVENUE.REVENUEID
                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID) NF
                where PLANNEDGIFTADDITIONREVENUE.ID = @PLANNEDGIFTADDITIONID
                union all
                --Pull in any payments for the above revenue

                select 
                    REVENUE.ID as [REVENUEID],
                    REVENUE.DATE as [REVENUEDATE],
                    INSTALLMENTSPLITPAYMENT.AMOUNT as [REVENUEAMOUNT], 
                    REVENUE.TRANSACTIONTYPE as [REVENUETRANSACTIONTYPE],
                    REVENUE.CONSTITUENTID as [REVENUECONSTITUENTID],
                    NF.NAME as [REVENUECONSTITUENT],
                    PLANNEDGIFTADDITIONREVENUE.REVENUEID as [PARENTID],
                    null as [TOTALPAYMENTS],
                    INSTALLMENTSPLITPAYMENT.APPLICATIONCURRENCYID as [REVENUETRANSACTIONCURRENCYID]
                from dbo.PLANNEDGIFTADDITIONREVENUE 
                inner join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.PLEDGEID = PLANNEDGIFTADDITIONREVENUE.REVENUEID
                inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID) NF
                where PLANNEDGIFTADDITIONREVENUE.ID = @PLANNEDGIFTADDITIONID