V_QUERY_PLANNEDGIFTACKNOWLEDGEMENTPROCESS_SEASONALADDRESSOUTPUT
PView used for seasonal address output for the acknowledgement process.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| PLANNEDGIFTID | uniqueidentifier | System record ID | |
| CONSTITUENTID | uniqueidentifier | yes | Constituent system ID |
| CONSTITUENTNAME | nvarchar(154) | yes | Constituent name |
| PRIMARYADDRESSEE | nvarchar(700) | yes | Primary addressee |
| PRIMARYSALUTATION | nvarchar(700) | yes | Primary salutation |
| PRIMARYCONTACT | nvarchar(154) | yes | Primary contact |
| ADDRESSBLOCK | nvarchar(150) | yes | Address block |
| CITY | nvarchar(50) | yes | City |
| STATE | nvarchar(50) | yes | State |
| POSTCODE | nvarchar(12) | yes | Post code |
| COUNTRY | nvarchar(100) | yes | Country |
| 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 |
| POSITION | nvarchar(100) | yes | Position |
| 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_PLANNEDGIFTACKNOWLEDGEMENTPROCESS_SEASONALADDRESSOUTPUT AS
select
PLANNEDGIFT.ID as PLANNEDGIFTID,
CONSTITUENT.ID as CONSTITUENTID,
NF.NAME as CONSTITUENTNAME,
(select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.PRIMARYADDRESSEE = 1) as PRIMARYADDRESSEE,
(select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.PRIMARYSALUTATION = 1) as PRIMARYSALUTATION,
case when CONSTITUENT.ISORGANIZATION = 1 then
(select top (1)
NF_CONTACT.NAME
from dbo.RELATIONSHIP
cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RELATIONSHIP.RECIPROCALCONSTITUENTID) NF_CONTACT
where RELATIONSHIP.ISPRIMARYCONTACT = 1
and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID)
else
null
end as PRIMARYCONTACT,
-- POSITION ****************,
(select ADDRESSBLOCK from dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate())) as ADDRESSBLOCK,
(select CITY from dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate())) as CITY,
(select STATE from dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate())) as STATE,
(select POSTCODE from dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate())) as POSTCODE,
(select COUNTRY from dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate())) as COUNTRY,
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 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,
-- BENEFICIARY 2 INFORMATION ***********,
(select BENEFICIARYNAME from dbo.UFN_PLANNEDGIFT_GETORDEREDBENEFICIARIES(PLANNEDGIFT.ID) where ROWNUMBER = 2) as BENEFICIARY2NAME,
(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,
-- 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],
case when CONSTITUENT.ISORGANIZATION = 1 then
(select top 1 RELATIONSHIP.POSITION from dbo.RELATIONSHIP
inner join dbo.CONSTITUENT CONTACT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONTACT.ID
where RELATIONSHIP.ISPRIMARYCONTACT = 1
and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID)
else
null
end as POSITION,
PLANNEDGIFTREVENUE.REVENUEID,
REVENUE.DONOTRECEIPT,
PLANNEDGIFT.TRANSACTIONCURRENCYID as PLANNEDGIFTTRANSACTIONCURRENCYID,
ASSET.TRANSACTIONCURRENCYID as ASSETTRANSACTIONCURRENCYID
from
dbo.PLANNEDGIFT
left join dbo.PROSPECTPLAN on PLANNEDGIFT.PROSPECTPLANID = PROSPECTPLAN.ID
left join dbo.CONSTITUENT on CONSTITUENT.ID = PLANNEDGIFT.CONSTITUENTID
left join dbo.PLANNEDGIFTASSET as ASSET on PLANNEDGIFT.ID = ASSET.PLANNEDGIFTID
left join dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1 and ADDRESS.DONOTMAIL = 0
left join dbo.COUNTRY on ADDRESS.COUNTRYID = COUNTRY.ID
left join dbo.STATE on ADDRESS.STATEID = STATE.ID
left join dbo.EMAILADDRESS on EMAILADDRESS.CONSTITUENTID=CONSTITUENT.ID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
left join dbo.PLANNEDGIFTREVENUE on PLANNEDGIFT.ID = PLANNEDGIFTREVENUE.ID
left join dbo.REVENUE on PLANNEDGIFTREVENUE.REVENUEID = REVENUE.ID