V_QUERY_PLANNEDGIFTACKNOWLEDGEMENTEMAILOUTPUT
View used for email output for the planned gift acknowledgement process.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| PLANNEDGIFTID | uniqueidentifier | System record ID | |
| CONSTITUENTID | uniqueidentifier | Constituent system ID | |
| CONSTITUENTNAME | nvarchar(154) | yes | Constituent name |
| ADDRESSEE | nvarchar(700) | yes | Addressee |
| SALUTATION | nvarchar(700) | yes | Salutation |
| CONTACT | nvarchar(154) | yes | Contact |
| POSITION | nvarchar(100) | yes | Position |
| EMAILADDRESS | UDT_EMAILADDRESS | yes | Email address |
| VEHICLECODE | tinyint | Vehicle code | |
| VEHICLE | nvarchar(34) | yes | Vehicle |
| EXPECTEDMATURITY | UDT_YEAR | Expected maturity year | |
| ISREVOCABLE | bit | Revocable | |
| ISANONYMOUS | varchar(9) | Anonymous | |
| NETPRESENTVALUE | money | Net present value amount | |
| NETPRESENTVALUEDATE | datetime | yes | Net present value date |
| REMAINDERVALUE | money | Remainder value amount | |
| REMAINDERVALUEDATE | datetime | yes | Remainder value date |
| RECOGNITIONAMOUNT | money | Recognition amount | |
| GIFTAMOUNT | money | yes | Gift amount |
| GIFTDATE | datetime | yes | Gift date |
| PAYOUTRATE | decimal(6, 5) | Payout percentage | |
| PAYOUTAMOUNT | money | Payout amount | |
| PAYMENTFREQUENCYCODE | tinyint | Payment frequency | |
| PAYMENTPERIODSTART | datetime | yes | Payment period start date |
| PAYMENTPERIODEND | datetime | yes | Payment period end date |
| DISCOUNTRATE | decimal(6, 5) | Discount rate | |
| DESIGNATION1NAME | nvarchar(512) | yes | Designation 1 name |
| DESIGNATION1AMOUNT | money | yes | Designation 1 amount |
| DESIGNATION2NAME | nvarchar(512) | yes | Designation 2 name |
| DESIGNATION2AMOUNT | money | yes | Designation 2 amount |
| BENEFICIARY1NAME | nvarchar(154) | yes | Beneficiary 1 name |
| BENEFICIARY1AGE | int | yes | Beneficiary 1 age |
| BENEFICIARY1DEPENDENCY | tinyint | yes | Beneficiary 1 dependency |
| BENEFICIARY1TYPE | nvarchar(100) | yes | Beneficiary 1 type |
| BENEFICIARY2NAME | nvarchar(154) | yes | Beneficiary 2 name |
| BENEFICIARY2AGE | int | yes | Beneficiary 2 age |
| BENEFICIARY2DEPENDENCY | tinyint | yes | Beneficiary 2 dependency |
| BENEFICIARY2TYPE | nvarchar(100) | yes | Beneficiary 2 type |
| POOLEDINCOMEFUNDCODEID | uniqueidentifier | yes | Pooled income fund code ID |
| POOLEDINCOMEFUNDTOTALUNITS | int | Pooled income fund total units | |
| POOLEDINCOMEFUNDPERCENT | decimal(37, 19) | yes | Pooled income fund percent units |
| ASSETTYPE | nvarchar(100) | yes | Asset type |
| ASSETDESCRIPTION | nvarchar(1000) | yes | Asset description |
| ASSETVALUE | money | yes | Asset value |
| ASSETCOSTBASIS | money | yes | Asset cost basis |
| ASSETVALUATIONMETHODCODE | nvarchar(100) | yes | Asset valuation method |
| ASSETVALUATIONSOURCE | nvarchar(200) | yes | Asset valuation source |
| RELATIONSHIP1NAME | nvarchar(154) | yes | Relationship 1 name |
| RELATIONSHIP1TYPE | nvarchar(100) | yes | Relationship 1 type |
| RELATIONSHIP2NAME | nvarchar(154) | yes | Relationship 2 name |
| RELATIONSHIP2TYPE | nvarchar(100) | yes | Relationship 2 type |
| POOLEDINCOMEFUND | nvarchar(100) | yes | Pooled income fund |
| REVENUEID | uniqueidentifier | yes | Revenue ID |
| DONOTRECEIPT | bit | yes | Do not receipt |
| PLANNEDGIFTTRANSACTIONCURRENCYID | uniqueidentifier | yes | Planned gift transaction currency ID |
| ASSETTRANSACTIONCURRENCYID | uniqueidentifier | yes | Asset transaction currency ID |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 8/17/2011 2:48:04 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.91.1535.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_PLANNEDGIFTACKNOWLEDGEMENTEMAILOUTPUT AS
select
PLANNEDGIFT.ID as PLANNEDGIFTID,
PLANNEDGIFT.CONSTITUENTID as CONSTITUENTID,
NF.NAME as CONSTITUENTNAME,
(select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = PLANNEDGIFT.CONSTITUENTID and NAMEFORMAT.PRIMARYADDRESSEE = 1) as ADDRESSEE,
(select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = PLANNEDGIFT.CONSTITUENTID and NAMEFORMAT.PRIMARYSALUTATION = 1) as SALUTATION,
NF_CONTACT.NAME as CONTACT,
(select top 1 RELATIONSHIP.POSITION
from dbo.RELATIONSHIP
where RELATIONSHIP.RECIPROCALCONSTITUENTID = EMAILADDRESS.CONTACTID
and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = PLANNEDGIFT.CONSTITUENTID) as POSITION,
EMAILADDRESS.EMAILADDRESS as EMAILADDRESS,
-- PLANNEDGIFT INFORMATION ***********,
PLANNEDGIFT.VEHICLECODE as VEHICLECODE,
PLANNEDGIFT.VEHICLE as VEHICLE,
PLANNEDGIFT.EXPECTEDMATURITY as EXPECTEDMATURITY,
PLANNEDGIFT.ISREVOCABLE as ISREVOCABLE,
case when PLANNEDGIFT.ISANONYMOUS = 0 then '' else 'anonymous' end as ISANONYMOUS,
PLANNEDGIFT.TRANSACTIONNETPRESENTVALUE as NETPRESENTVALUE,
PLANNEDGIFT.NETPRESENTVALUEDATE as NETPRESENTVALUEDATE,
PLANNEDGIFT.TRANSACTIONREMAINDERVALUE as REMAINDERVALUE,
PLANNEDGIFT.REMAINDERVALUEDATE as REMAINDERVALUEDATE,
PLANNEDGIFT.TRANSACTIONRECOGNITIONAMOUNT as RECOGNITIONAMOUNT,
PLANNEDGIFT.TRANSACTIONGIFTAMOUNT as GIFTAMOUNT,
PLANNEDGIFT.GIFTDATE as GIFTDATE,
-- PAYOUT INFORMATION ***********,
PLANNEDGIFT.PAYOUTRATE as PAYOUTRATE,
PLANNEDGIFT.TRANSACTIONPAYOUTAMOUNT as PAYOUTAMOUNT,
PLANNEDGIFT.PAYMENTFREQUENCYCODE as PAYMENTFREQUENCYCODE,
PLANNEDGIFT.PAYMENTPERIODSTART as PAYMENTPERIODSTART,
PLANNEDGIFT.PAYMENTPERIODEND as PAYMENTPERIODEND,
PLANNEDGIFT.DISCOUNTRATE as DISCOUNTRATE,
-- DESIGNATION 1 INFORMATION ***********,
(select DESIGNATIONNAME from dbo.UFN_PLANNEDGIFT_GETORDEREDDESIGNATIONS_2(PLANNEDGIFT.ID) where ROWNUMBER = 1) as DESIGNATION1NAME,
(select TRANSACTIONAMOUNT from dbo.UFN_PLANNEDGIFT_GETORDEREDDESIGNATIONS_2(PLANNEDGIFT.ID) where ROWNUMBER = 1) as DESIGNATION1AMOUNT,
-- DESIGNATION 2 INFORMATION ***********,
(select DESIGNATIONNAME from dbo.UFN_PLANNEDGIFT_GETORDEREDDESIGNATIONS_2(PLANNEDGIFT.ID) where ROWNUMBER = 2) as DESIGNATION2NAME,
(select TRANSACTIONAMOUNT from dbo.UFN_PLANNEDGIFT_GETORDEREDDESIGNATIONS_2(PLANNEDGIFT.ID) where ROWNUMBER = 2) as DESIGNATION2AMOUNT,
-- BENEFICIARY 1 INFORMATION ***********,
(select BENEFICIARYNAME from dbo.UFN_PLANNEDGIFT_GETORDEREDBENEFICIARIES(PLANNEDGIFT.ID) where ROWNUMBER = 1) as BENEFICIARY1NAME,
--(select BENEFICIARYRELATIONSHIP from dbo.UFN_PLANNEDGIFT_GETORDEREDBENEFICIARIES(PLANNEDGIFT.ID) where ROWNUMBER = 1) as BENEFICIARY1RELATIONSHIP,
(select BENEFICIARYAGE from dbo.UFN_PLANNEDGIFT_GETORDEREDBENEFICIARIES(PLANNEDGIFT.ID) where ROWNUMBER = 1) as BENEFICIARY1AGE,
(select BENEFICIARYDEPENDENCY from dbo.UFN_PLANNEDGIFT_GETORDEREDBENEFICIARIES(PLANNEDGIFT.ID) where ROWNUMBER = 1) as BENEFICIARY1DEPENDENCY,
(select BENEFICIARYTYPE from dbo.UFN_PLANNEDGIFT_GETORDEREDBENEFICIARIES(PLANNEDGIFT.ID) where ROWNUMBER = 1) as BENEFICIARY1TYPE,
--(select CONSTITUENTISBENEFICIARY from dbo.UFN_PLANNEDGIFT_GETORDEREDBENEFICIARIES(PLANNEDGIFT.ID) where ROWNUMBER = 1) as CONSTITUENTISBENEFICIARY1,
-- BENEFICIARY 2 INFORMATION ***********,
(select BENEFICIARYNAME from dbo.UFN_PLANNEDGIFT_GETORDEREDBENEFICIARIES(PLANNEDGIFT.ID) where ROWNUMBER = 2) as BENEFICIARY2NAME,
--(select BENEFICIARYRELATIONSHIP from dbo.UFN_PLANNEDGIFT_GETORDEREDBENEFICIARIES(PLANNEDGIFT.ID) where ROWNUMBER = 2) as BENEFICIARY2RELATIONSHIP,
(select BENEFICIARYAGE from dbo.UFN_PLANNEDGIFT_GETORDEREDBENEFICIARIES(PLANNEDGIFT.ID) where ROWNUMBER = 2) as BENEFICIARY2AGE,
(select BENEFICIARYDEPENDENCY from dbo.UFN_PLANNEDGIFT_GETORDEREDBENEFICIARIES(PLANNEDGIFT.ID) where ROWNUMBER = 2) as BENEFICIARY2DEPENDENCY,
(select BENEFICIARYTYPE from dbo.UFN_PLANNEDGIFT_GETORDEREDBENEFICIARIES(PLANNEDGIFT.ID) where ROWNUMBER = 2) as BENEFICIARY2TYPE,
--(select CONSTITUENTISBENEFICIARY from dbo.UFN_PLANNEDGIFT_GETORDEREDBENEFICIARIES(PLANNEDGIFT.ID) where ROWNUMBER = 2) as CONSTITUENTISBENEFICIARY2,
-- POOLED INCOME FUND INFORMATION ***********,
PLANNEDGIFT.POOLEDINCOMEFUNDCODEID as POOLEDINCOMEFUNDCODEID,
PLANNEDGIFT.POOLEDINCOMEFUNDTOTALUNITS as POOLEDINCOMEFUNDTOTALUNITS,
PLANNEDGIFT.POOLEDINCOMEFUNDPERCENT as POOLEDINCOMEFUNDPERCENT,
-- ASSET INFORMATION ***********,
dbo.UFN_ASSETTYPECODE_GETDESCRIPTION(ASSET.ASSETTYPECODEID) as ASSETTYPE,
ASSET.DESCRIPTION as ASSETDESCRIPTION,
ASSET.TRANSACTIONVALUE as ASSETVALUE,
ASSET.TRANSACTIONCOSTBASIS as ASSETCOSTBASIS,
dbo.UFN_ASSETVALUATIONMETHODCODE_GETDESCRIPTION(ASSET.ASSETVALUATIONMETHODCODEID) as ASSETVALUATIONMETHODCODE,
ASSET.VALUATIONSOURCE as ASSETVALUATIONSOURCE,
-- RELATIONSHIP 1 INFORMATION ***********,
(select RELATIONSHIPNAME from dbo.UFN_PLANNEDGIFT_GETORDEREDRELATIONSHIPS(PLANNEDGIFT.ID) where ROWNUMBER = 1) as RELATIONSHIP1NAME,
(select RELATIONSHIPTYPE from dbo.UFN_PLANNEDGIFT_GETORDEREDRELATIONSHIPS(PLANNEDGIFT.ID) where ROWNUMBER = 1) as RELATIONSHIP1TYPE,
-- RELATIONSHIP 2 INFORMATION ***********,
(select RELATIONSHIPNAME from dbo.UFN_PLANNEDGIFT_GETORDEREDRELATIONSHIPS(PLANNEDGIFT.ID) where ROWNUMBER = 2) as RELATIONSHIP2NAME,
(select RELATIONSHIPTYPE from dbo.UFN_PLANNEDGIFT_GETORDEREDRELATIONSHIPS(PLANNEDGIFT.ID) where ROWNUMBER = 2) as RELATIONSHIP2TYPE,
(select POOLEDINCOMEFUNDCODE.DESCRIPTION from POOLEDINCOMEFUNDCODE where PLANNEDGIFT.POOLEDINCOMEFUNDCODEID = POOLEDINCOMEFUNDCODE.ID) [POOLEDINCOMEFUND],
PLANNEDGIFTREVENUE.REVENUEID,
REVENUE.DONOTRECEIPT,
PLANNEDGIFT.TRANSACTIONCURRENCYID as PLANNEDGIFTTRANSACTIONCURRENCYID,
ASSET.TRANSACTIONCURRENCYID as ASSETTRANSACTIONCURRENCYID
from dbo.PLANNEDGIFT
left join dbo.PROSPECTPLAN on PLANNEDGIFT.PROSPECTPLANID = PROSPECTPLAN.ID
inner join dbo.[UFN_ADDRESSPROCESS_EMAILS](6, null) as EMAILADDRESS on PLANNEDGIFT.CONSTITUENTID = EMAILADDRESS.CONSTITUENTID
left join dbo.PLANNEDGIFTASSET as ASSET on PLANNEDGIFT.ID = ASSET.PLANNEDGIFTID
cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PLANNEDGIFT.CONSTITUENTID) NF
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(EMAILADDRESS.CONTACTID) NF_CONTACT
left join dbo.PLANNEDGIFTREVENUE on PLANNEDGIFT.ID = PLANNEDGIFTREVENUE.ID
left join dbo.REVENUE on PLANNEDGIFTREVENUE.REVENUEID = REVENUE.ID