V_QUERY_PLANNEDGIFTREVENUE
Provides the ability to query for planned gift revenue.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| REVENUEID | uniqueidentifier | Revenue ID | |
| REVENUEDATE | datetime | yes | Date |
| REVENUEAMOUNT | money | Amount | |
| REVENUETRANSACTIONTYPE | nvarchar(21) | yes | Transaction type |
| REVENUECONSTITUENTID | uniqueidentifier | yes | Revenue constituent ID |
| REVENUECONSTITUENT | nvarchar(154) | yes | Constituent |
| PLANNEDGIFTID | uniqueidentifier | yes | System record ID |
| TOTALPAYMENTS | money | yes | Total payments |
| BASECURRENCYID | uniqueidentifier | yes | Base currency ID |
| BASEEXCHANGERATEID | uniqueidentifier | yes | Base exchange rate |
| ORGANIZATIONREVENUEAMOUNT | money | Amount (organization currency) | |
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | Organization exchange rate |
| TRANSACTIONREVENUEAMOUNT | money | Amount (transaction currency) | |
| TRANSACTIONCURRENCYID | uniqueidentifier | yes | Transaction currency ID |
| DONOTRECEIPT | bit | Do not receipt |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 8/17/2011 2:26:16 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.91.1535.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_PLANNEDGIFTREVENUE AS
--Pull in the associated revenue
select
REVENUE.ID as [REVENUEID],
REVENUE.DATE as [REVENUEDATE],
REVENUE.AMOUNT as [REVENUEAMOUNT],
REVENUE.TRANSACTIONTYPE as [REVENUETRANSACTIONTYPE],
REVENUE.CONSTITUENTID as [REVENUECONSTITUENTID],
NF.NAME as [REVENUECONSTITUENT],
PLANNEDGIFTREVENUE.ID as [PLANNEDGIFTID],
dbo.UFN_PLEDGE_GETAMOUNTPAID(PLANNEDGIFTREVENUE.REVENUEID) as [TOTALPAYMENTS],
REVENUE.BASECURRENCYID as [BASECURRENCYID],
REVENUE.BASEEXCHANGERATEID as [BASEEXCHANGERATEID],
REVENUE.ORGANIZATIONAMOUNT as [ORGANIZATIONREVENUEAMOUNT],
REVENUE.ORGANIZATIONEXCHANGERATEID as [ORGANIZATIONEXCHANGERATEID],
REVENUE.TRANSACTIONAMOUNT as [TRANSACTIONREVENUEAMOUNT],
REVENUE.TRANSACTIONCURRENCYID as [TRANSACTIONCURRENCYID],
REVENUE.DONOTRECEIPT as [DONOTRECEIPT]
/*#EXTENSION*/
from dbo.PLANNEDGIFTREVENUE
inner join dbo.REVENUE on REVENUE.ID = PLANNEDGIFTREVENUE.REVENUEID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID) NF
union all
--Pull in any payments for the above revenue
select
REVENUE.ID as [REVENUEID],
REVENUE.DATE as [REVENUEDATE],
REVENUESPLIT.AMOUNT as [REVENUEAMOUNT],
REVENUE.TRANSACTIONTYPE as [REVENUETRANSACTIONTYPE],
REVENUE.CONSTITUENTID as [REVENUECONSTITUENTID],
NF.NAME as [REVENUECONSTITUENT],
PLANNEDGIFTREVENUE.ID as [PLANNEDGIFTID],
0 as [TOTALPAYMENTS],
REVENUESPLIT.BASECURRENCYID as [BASECURRENCYID],
REVENUESPLIT.BASEEXCHANGERATEID as [BASEEXCHANGERATEID],
REVENUESPLIT.ORGANIZATIONAMOUNT as [ORGANIZATIONREVENUEAMOUNT],
REVENUESPLIT.ORGANIZATIONEXCHANGERATEID as [ORGANIZATIONEXCHANGERATEID],
REVENUESPLIT.TRANSACTIONAMOUNT as [TRANSACTIONREVENUEAMOUNT],
REVENUESPLIT.TRANSACTIONCURRENCYID as [TRANSACTIONCURRENCYID],
REVENUE.DONOTRECEIPT as [DONOTRECEIPT]
/*#EXTENSION*/
from dbo.PLANNEDGIFTREVENUE
inner join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.PLEDGEID = PLANNEDGIFTREVENUE.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
union all
--Pull in any revenue directly paying this planned gift
select
REVENUE.ID as [REVENUEID],
REVENUE.DATE as [REVENUEDATE],
REVENUESPLIT.AMOUNT as [REVENUEAMOUNT],
REVENUE.TRANSACTIONTYPE as [REVENUETRANSACTIONTYPE],
REVENUE.CONSTITUENTID as [REVENUECONSTITUENTID],
NF.NAME as [REVENUECONSTITUENT],
PLANNEDGIFTREVENUESPLIT.PLANNEDGIFTID as [PLANNEDGIFTID],
0 as [TOTALPAYMENTS],
REVENUESPLIT.BASECURRENCYID as [BASECURRENCYID],
REVENUESPLIT.BASEEXCHANGERATEID as [BASEEXCHANGERATEID],
REVENUESPLIT.ORGANIZATIONAMOUNT as [ORGANIZATIONREVENUEAMOUNT],
REVENUESPLIT.ORGANIZATIONEXCHANGERATEID as [ORGANIZATIONEXCHANGERATEID],
REVENUESPLIT.TRANSACTIONAMOUNT as [TRANSACTIONREVENUEAMOUNT],
REVENUESPLIT.TRANSACTIONCURRENCYID as [TRANSACTIONCURRENCYID],
REVENUE.DONOTRECEIPT as [DONOTRECEIPT]
/*#EXTENSION*/
from dbo.PLANNEDGIFTREVENUESPLIT
inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = PLANNEDGIFTREVENUESPLIT.REVENUESPLITID
inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID) NF