V_QUERY_PLANNEDGIFT
Enables query for planned gift records.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| CONSTITUENTID | uniqueidentifier | Constituent ID | |
| PROSPECTPLANID | uniqueidentifier | yes | Prospect Plan ID |
| VEHICLE | nvarchar(34) | yes | Vehicle |
| SUBTYPE | nvarchar(34) | yes | Subtype |
| EXPECTEDMATURITY | UDT_YEAR | Expected maturity | |
| ISREVOCABLE | bit | Is revocable | |
| ISANONYMOUS | bit | Is anonymous | |
| EXPECTEDGIFTAMOUNT | money | Gift amount | |
| GIFTDATE | datetime | yes | Gift date |
| RECOGNITIONAMOUNT | money | Recognition amount | |
| NETPRESENTVALUE | money | Net present value | |
| NETPRESENTVALUEDATE | datetime | yes | Net present value date |
| REMAINDERVALUE | money | Remainder value | |
| REMAINDERVALUEDATE | datetime | yes | Remainder value date |
| DISCOUNTRATE | decimal(10, 5) | yes | Discount rate |
| PAYOUTRATE | decimal(10, 5) | yes | Payout rate |
| PAYOUTAMOUNT | money | Payout amount | |
| PAYMENTPERIODSTART | datetime | yes | Payment period start |
| PAYMENTPERIODEND | datetime | yes | Payment period end |
| PAYMENTFREQUENCY | nvarchar(12) | yes | Payment frequency |
| TERMTYPE | nvarchar(41) | yes | Term type |
| TERMENDDATE | datetime | yes | Term end date |
| YEARSINTERM | tinyint | Years in term | |
| TRUSTTAXIDNUMBER | nvarchar(100) | Trust tax ID number | |
| POOLEDINCOMEFUNDCODEID | uniqueidentifier | yes | Pooled income fund ID |
| POOLEDINCOMEFUNDUNITS | int | Pooled income fund units | |
| POOLEDINCOMEFUNDTOTALUNITS | int | Pooled income fund total units | |
| POOLEDINCOMEFUNDPERCENT | decimal(38, 16) | yes | Pooled income fund percent |
| LIFEINSURANCEPREMIUM | money | Life insurance premium | |
| LIFEINSURANCEPREMIUMFREQUENCY | nvarchar(12) | yes | Life insurance premium frequency |
| LIFEINSURANCEPREMIUMDUEDATE | datetime | yes | Life insurance premium due date |
| STATUS | nvarchar(16) | yes | Status |
| 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 |
| POOLEDINCOMEFUND | nvarchar(100) | yes | Pooled income fund |
| REMAINDERMANPERCENT | decimal(7, 6) | Remainderman % | |
| DISCOUNTDATE | datetime | yes | Discount rate date |
| PROBATEDATE | datetime | yes | Probate date |
| PROBATESTATUS | nvarchar(18) | yes | Probate status |
| ISTESTAMENTARY | bit | Is testamentary | |
| ISLIVINGTRUST | bit | Living trust | |
| GIFTVALUEISNOMINAL | bit | Gift value is nominal | |
| ISCONTINGENT | bit | Gift is contingent | |
| ORGISPOLICY | bit | Organization is policy | |
| ORGISBENEFICIARY | bit | Organization is beneficiary | |
| TRUSTHELDOUTSIDE | bit | Trust held outside | |
| TOTALPAYOUT | money | Total payout | |
| REALIZEDVALUE | money | yes | Realized amount |
| OTHERSUBTYPECODEID | uniqueidentifier | yes | Other subtype code ID |
| OTHERSUBTYPE | nvarchar(100) | yes | Other subtype |
| BASECURRENCYID | uniqueidentifier | yes | Base currency ID |
| BASEEXCHANGERATEID | uniqueidentifier | yes | Base exchange rate |
| ORGANIZATIONEXPECTEDGIFTAMOUNT | money | Gift amount (organization currency) | |
| ORGANIZATIONRECOGNITIONAMOUNT | money | Recognition amount (organization currency) | |
| ORGANIZATIONNETPRESENTVALUE | money | Net present value (organization currency) | |
| ORGANIZATIONREMAINDERVALUE | money | Remainder value (organization currency) | |
| ORGANIZATIONPAYOUTAMOUNT | money | Payout amount (organization currency) | |
| ORGANIZATIONLIFEINSURANCEPREMIUM | money | Life insurance premium (organization currency) | |
| ORGANIZATIONTOTALPAYOUT | money | Total payout (organization currency) | |
| ORGANIZATIONREALIZEDVALUE | money | yes | Realized amount (organization currency) |
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | Organization exchange rate |
| TRANSACTIONEXPECTEDGIFTAMOUNT | money | Gift amount (transaction currency) | |
| TRANSACTIONRECOGNITIONAMOUNT | money | Recognition amount (transaction currency) | |
| TRANSACTIONNETPRESENTVALUE | money | Net present value (transaction currency) | |
| TRANSACTIONREMAINDERVALUE | money | Remainder value (transaction currency) | |
| TRANSACTIONPAYOUTAMOUNT | money | Payout amount (transaction currency) | |
| TRANSACTIONLIFEINSURANCEPREMIUM | money | Life insurance premium (transaction currency) | |
| TRANSACTIONTOTALPAYOUT | money | Total payout (transaction currency) | |
| TRANSACTIONREALIZEDVALUE | money | yes | Realized amount (transaction currency) |
| TRANSACTIONCURRENCYID | uniqueidentifier | yes | Transaction currency ID |
| TRIGGERDATE | datetime | yes | Trigger date |
| ISFLIP | bit | FLIP situation | |
| EXCL_EXPDATE | datetime | yes | Expiration date |
| EXCL_RATIO | decimal(6, 3) | Exclusion ratio | |
| DONOTRECEIPT | bit | Do not receipt | |
| TRIGGEREVENTCODEID | uniqueidentifier | yes | Trigger event code ID |
| TRIGGEREVENT | nvarchar(100) | yes | Trigger event |
| LIFEINSURANCEPOLICYNUMBER | nvarchar(50) | Policy number | |
| LIFEINSURANCEDATEISSUED | datetime | yes | Issued date |
| LIFEINSURANCEFACEVALUE | money | Face value | |
| TRANSACTIONLIFEINSURANCEFACEVALUE | money | Face value (transaction currency) | |
| ORGANIZATIONLIFEINSURANCEFACEVALUE | money | Face value (organization currency) | |
| LIFEINSURANCEDOESINCLUDEDIVIDENDPARTICIPATION | bit | Dividend participation | |
| LIFEINSURANCEISLOANALLOWED | bit | Loan allowed | |
| LIFEINSURANCEOUTSTANDINGLOANAMOUNT | money | Outstanding loan amount | |
| TRANSACTIONLIFEINSURANCEOUTSTANDINGLOANAMOUNT | money | Outstanding loan amount (transaction currency) | |
| ORGANIZATIONLIFEINSURANCEOUTSTANDINGLOANAMOUNT | money | Outstanding loan amount (organization currency) |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 5/3/2024 2:18:18 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.3700.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_PLANNEDGIFT AS
select
PLANNEDGIFT.ID,
PLANNEDGIFT.CONSTITUENTID,
PLANNEDGIFT.PROSPECTPLANID,
PLANNEDGIFT.VEHICLE,
PLANNEDGIFT.SUBTYPE,
PLANNEDGIFT.EXPECTEDMATURITY,
PLANNEDGIFT.ISREVOCABLE,
PLANNEDGIFT.ISANONYMOUS,
PLANNEDGIFT.EXPECTEDGIFTAMOUNT,
PLANNEDGIFT.GIFTDATE,
PLANNEDGIFT.RECOGNITIONAMOUNT,
PLANNEDGIFT.NETPRESENTVALUE,
PLANNEDGIFT.NETPRESENTVALUEDATE,
PLANNEDGIFT.REMAINDERVALUE,
PLANNEDGIFT.REMAINDERVALUEDATE,
PLANNEDGIFT.DISCOUNTRATE*100 DISCOUNTRATE,
PLANNEDGIFT.PAYOUTRATE*100 PAYOUTRATE,
PLANNEDGIFT.PAYOUTAMOUNT,
PLANNEDGIFT.PAYMENTPERIODSTART,
PLANNEDGIFT.PAYMENTPERIODEND,
PLANNEDGIFT.PAYMENTFREQUENCY,
PLANNEDGIFT.TERMTYPE,
PLANNEDGIFT.TERMENDDATE,
PLANNEDGIFT.YEARSINTERM,
PLANNEDGIFT.TRUSTTAXIDNUMBER,
PLANNEDGIFT.POOLEDINCOMEFUNDCODEID,
PLANNEDGIFT.POOLEDINCOMEFUNDUNITS,
PLANNEDGIFT.POOLEDINCOMEFUNDTOTALUNITS,
PLANNEDGIFT.POOLEDINCOMEFUNDPERCENT*100 POOLEDINCOMEFUNDPERCENT,
PLANNEDGIFT.LIFEINSURANCEPREMIUM,
PLANNEDGIFT.LIFEINSURANCEPREMIUMFREQUENCY,
PLANNEDGIFT.LIFEINSURANCEPREMIUMDUEDATE,
PLANNEDGIFT.STATUS,
PLANNEDGIFT.DATEADDED,
PLANNEDGIFT.DATECHANGED,
PLANNEDGIFT.TSLONG,
ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
ADDEDBY.USERNAME as ADDEDBY_USERNAME,
CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
CHANGEDBY.USERNAME as CHANGEDBY_USERNAME,
(select POOLEDINCOMEFUNDCODE.DESCRIPTION from dbo.POOLEDINCOMEFUNDCODE where PLANNEDGIFT.POOLEDINCOMEFUNDCODEID = POOLEDINCOMEFUNDCODE.ID) [POOLEDINCOMEFUND],
PLANNEDGIFT.REMAINDERMANPERCENT as REMAINDERMANPERCENT,
PLANNEDGIFT.DISCOUNTDATE as DISCOUNTDATE,
PLANNEDGIFT.PROBATEDATE as PROBATEDATE,
PLANNEDGIFT.PROBATESTATUS as PROBATESTATUS,
PLANNEDGIFT.ISTESTAMENTARY,
PLANNEDGIFT.ISLIVINGTRUST,
PLANNEDGIFT.GIFTVALUEISNOMINAL,
PLANNEDGIFT.ISCONTINGENT,
PLANNEDGIFT.ORGISPOLICY,
PLANNEDGIFT.ORGISBENEFICIARY,
PLANNEDGIFT.TRUSTHELDOUTSIDE,
PLANNEDGIFT.TOTALPAYOUT,
dbo.UFN_PLANNEDGIFT_GETREALIZEDAMOUNT(PLANNEDGIFT.ID) as REALIZEDVALUE,
PLANNEDGIFT.OTHERSUBTYPECODEID,
dbo.UFN_PLANNEDGIFTOTHERSUBTYPECODE_GETDESCRIPTION(PLANNEDGIFT.OTHERSUBTYPECODEID) as [OTHERSUBTYPE],
PLANNEDGIFT.BASECURRENCYID,
PLANNEDGIFT.BASEEXCHANGERATEID,
PLANNEDGIFT.ORGANIZATIONEXPECTEDGIFTAMOUNT,
PLANNEDGIFT.ORGANIZATIONRECOGNITIONAMOUNT,
PLANNEDGIFT.ORGANIZATIONNETPRESENTVALUE,
PLANNEDGIFT.ORGANIZATIONREMAINDERVALUE,
PLANNEDGIFT.ORGANIZATIONPAYOUTAMOUNT,
PLANNEDGIFT.ORGANIZATIONLIFEINSURANCEPREMIUM,
PLANNEDGIFT.ORGANIZATIONTOTALPAYOUT,
dbo.UFN_PLANNEDGIFT_GETORGANIZATIONREALIZEDAMOUNT(PLANNEDGIFT.ID) as ORGANIZATIONREALIZEDVALUE,
PLANNEDGIFT.ORGANIZATIONEXCHANGERATEID,
PLANNEDGIFT.TRANSACTIONEXPECTEDGIFTAMOUNT,
PLANNEDGIFT.TRANSACTIONRECOGNITIONAMOUNT,
PLANNEDGIFT.TRANSACTIONNETPRESENTVALUE,
PLANNEDGIFT.TRANSACTIONREMAINDERVALUE,
PLANNEDGIFT.TRANSACTIONPAYOUTAMOUNT,
PLANNEDGIFT.TRANSACTIONLIFEINSURANCEPREMIUM,
PLANNEDGIFT.TRANSACTIONTOTALPAYOUT,
dbo.UFN_PLANNEDGIFT_GETTRANSACTIONREALIZEDAMOUNT(PLANNEDGIFT.ID) as TRANSACTIONREALIZEDVALUE,
PLANNEDGIFT.TRANSACTIONCURRENCYID,
PLANNEDGIFT.TRIGGERDATE,
PLANNEDGIFT.ISFLIP,
PLANNEDGIFT.EXCL_EXPDATE,
PLANNEDGIFT.EXCL_RATIO,
PLANNEDGIFT.DONOTRECEIPT,
PLANNEDGIFT.TRIGGEREVENTCODEID,
dbo.UFN_PLANNEDGIFTTRIGGEREVENTCODE_GETDESCRIPTION(PLANNEDGIFT.TRIGGEREVENTCODEID) as [TRIGGEREVENT],
PLANNEDGIFT.LIFEINSURANCEPOLICYNUMBER,
PLANNEDGIFT.LIFEINSURANCEDATEISSUED,
PLANNEDGIFT.LIFEINSURANCEFACEVALUE,
PLANNEDGIFT.TRANSACTIONLIFEINSURANCEFACEVALUE,
PLANNEDGIFT.ORGANIZATIONLIFEINSURANCEFACEVALUE,
PLANNEDGIFT.LIFEINSURANCEDOESINCLUDEDIVIDENDPARTICIPATION,
PLANNEDGIFT.LIFEINSURANCEISLOANALLOWED,
PLANNEDGIFT.LIFEINSURANCEOUTSTANDINGLOANAMOUNT,
PLANNEDGIFT.TRANSACTIONLIFEINSURANCEOUTSTANDINGLOANAMOUNT,
PLANNEDGIFT.ORGANIZATIONLIFEINSURANCEOUTSTANDINGLOANAMOUNT
/*#EXTENSION*/
from dbo.PLANNEDGIFT
left join dbo.CHANGEAGENT ADDEDBY on ADDEDBY.ID = PLANNEDGIFT.ADDEDBYID
left join dbo.CHANGEAGENT CHANGEDBY on CHANGEDBY.ID = PLANNEDGIFT.CHANGEDBYID