V_QUERY_PLANNEDGIFTADDITIONREVENUE
Provides the ability to query for planned gift addition 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 |
| PLANNEDGIFTADDITIONID | uniqueidentifier | 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 | |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 8/17/2011 2:46:33 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.91.1535.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_PLANNEDGIFTADDITIONREVENUE 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],
PLANNEDGIFTADDITIONREVENUE.ID as [PLANNEDGIFTADDITIONID],
dbo.UFN_PLEDGE_GETAMOUNTPAID(PLANNEDGIFTADDITIONREVENUE.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],
PLANNEDGIFTADDITIONREVENUE.DATEADDED,
PLANNEDGIFTADDITIONREVENUE.DATECHANGED,
PLANNEDGIFTADDITIONREVENUE.TSLONG,
ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
ADDEDBY.USERNAME as ADDEDBY_USERNAME,
CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
CHANGEDBY.USERNAME as CHANGEDBY_USERNAME
/*#EXTENSION*/
from dbo.PLANNEDGIFTADDITIONREVENUE
inner join dbo.REVENUE on REVENUE.ID = PLANNEDGIFTADDITIONREVENUE.REVENUEID
left join dbo.CHANGEAGENT ADDEDBY on ADDEDBY.ID = PLANNEDGIFTADDITIONREVENUE.ADDEDBYID
left join dbo.CHANGEAGENT CHANGEDBY on CHANGEDBY.ID = PLANNEDGIFTADDITIONREVENUE.CHANGEDBYID
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],
PLANNEDGIFTADDITIONREVENUE.ID as [PLANNEDGIFTADDITIONID],
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],
PLANNEDGIFTADDITIONREVENUE.DATEADDED,
PLANNEDGIFTADDITIONREVENUE.DATECHANGED,
PLANNEDGIFTADDITIONREVENUE.TSLONG,
ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
ADDEDBY.USERNAME as ADDEDBY_USERNAME,
CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
CHANGEDBY.USERNAME as CHANGEDBY_USERNAME
/*#EXTENSION*/
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
left join dbo.CHANGEAGENT ADDEDBY on ADDEDBY.ID = PLANNEDGIFTADDITIONREVENUE.ADDEDBYID
left join dbo.CHANGEAGENT CHANGEDBY on CHANGEDBY.ID = PLANNEDGIFTADDITIONREVENUE.CHANGEDBYID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID) NF