V_QUERY_RECEIPTEMAILOUTPUT
View used for email output for the receipt process.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | ID | |
| CONSTITUENTNAME | nvarchar(154) | yes | Constituent name |
| CONSTITUENTLOOKUPID | nvarchar(100) | yes | Constituent lookup ID |
| 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 |
| DATE | datetime | yes | Date |
| PAYMENTAMOUNT | money | Payment amount | |
| RECEIPTAMOUNT | money | Receipt amount | |
| TOTALBENEFITAMOUNT | money | yes | Total benefit amount |
| PAYMENTMETHOD | nvarchar(14) | yes | Payment method |
| ISSUER | nvarchar(100) | yes | Stock issuer |
| SYMBOL | nvarchar(25) | yes | Stock symbol |
| NUMBEROFUNITS | decimal(20, 3) | yes | Stock number of units |
| MEDIANPRICE | decimal(19, 4) | yes | Stock median price |
| PROPERTYGIKSUBTYPE | nvarchar(100) | yes | Property/GIK subtype |
| GIVENANONYMOUSLY | varchar(3) | Anonymous | |
| PORTIONSUBJECTTOVAT | money | yes | Portion subject to VAT |
| VATTAXRATEDESCRIPTION | nvarchar(50) | yes | VAT tax rate description |
| VATTAXRATE | decimal(7, 3) | yes | VAT tax rate |
| VATAMOUNT | money | yes | VAT amount |
| GIFTINKINDITEMNAME | nvarchar(100) | yes | Gift-in-kind item name |
| GIFTINKINDDISPOSITION | nvarchar(7) | yes | Gift-in-kind disposition |
| GIFTINKINDNUMBEROFUNITS | int | yes | Gift-in-kind number of units |
| GIFTINKINDFAIRMARKETVALUE | money | yes | Gift-in-kind fair market value per unit |
| BENEFITSWAIVED | varchar(3) | Benefits waived | |
| TRANSACTIONCURRENCYID | uniqueidentifier | yes | Transaction currency ID |
| TRANSACTIONCURRENCY | nvarchar(3) | yes | Transaction currency |
| JOBTITLE | nvarchar(100) | yes | Job title |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 8/17/2011 2:44:02 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.91.1535.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_RECEIPTEMAILOUTPUT AS
select
REVENUE.ID,
CONSTITUENT.NAME as CONSTITUENTNAME,
CONSTITUENT.LOOKUPID as CONSTITUENTLOOKUPID,
(select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.PRIMARYADDRESSEE = 1) as ADDRESSEE,
(select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.PRIMARYSALUTATION = 1) as SALUTATION,
CONTACT.NAME as CONTACT,
(select top 1 RELATIONSHIP.POSITION
from dbo.RELATIONSHIP
where RELATIONSHIP.RECIPROCALCONSTITUENTID = CONTACT.ID
and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID) as POSITION,
EMAILADDRESS.EMAILADDRESS,
REVENUE.DATE as DATE,
REVENUE.TRANSACTIONAMOUNT as PAYMENTAMOUNT,
REVENUE.RECEIPTAMOUNT as RECEIPTAMOUNT,
(select sum(RB.TRANSACTIONTOTALVALUE) from dbo.REVENUEBENEFIT RB where RB.REVENUEID = REVENUE.ID) as TOTALBENEFITAMOUNT,
REVENUEPAYMENTMETHOD.PAYMENTMETHOD as PAYMENTMETHOD,
STOCKDETAIL.ISSUER as ISSUER,
STOCKDETAIL.SYMBOL as SYMBOL,
STOCKDETAIL.NUMBEROFUNITS as NUMBEROFUNITS,
STOCKDETAIL.TRANSACTIONMEDIANPRICE as MEDIANPRICE,
PROPERTYGIKSUBTYPE =
case when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE in (5,8) then PTC.DESCRIPTION
when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 6 then GIKTC.DESCRIPTION
else null end,
case REVENUE.GIVENANONYMOUSLY when 1 then 'Yes' else 'No' end as GIVENANONYMOUSLY,
[REVENUEVAT].TRANSACTIONAMOUNTTOTAX as [PORTIONSUBJECTTOVAT],
[VATTAXRATE].DESCRIPTION as [VATTAXRATEDESCRIPTION],
[VATTAXRATE].TAXRATE as [VATTAXRATE],
[REVENUEVAT].TRANSACTIONVATAMOUNT as VATAMOUNT,
[GIK].ITEMNAME as [GIFTINKINDITEMNAME],
[GIK].DISPOSITION as [GIFTINKINDDISPOSITION],
[GIK].NUMBEROFUNITS as [GIFTINKINDNUMBEROFUNITS],
[GIK].TRANSACTIONFAIRMARKETVALUE as [GIFTINKINDFAIRMARKETVALUE],
case REVENUE.BENEFITSWAIVED when 1 then 'Yes' else 'No' end as BENEFITSWAIVED,
REVENUE.TRANSACTIONCURRENCYID,
dbo.UFN_CURRENCY_GETISO(REVENUE.TRANSACTIONCURRENCYID) as TRANSACTIONCURRENCY,
case when CONSTITUENT.ISORGANIZATION = 1 then
(select top 1 RELATIONSHIPJOBINFO.JOBTITLE
from dbo.RELATIONSHIP
inner join dbo.RELATIONSHIPJOBINFO on RELATIONSHIP.RELATIONSHIPSETID = RELATIONSHIPJOBINFO.RELATIONSHIPSETID
inner join dbo.CONSTITUENT CONTACT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONTACT.ID
where RELATIONSHIP.ISPRIMARYCONTACT = 1
and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID)
else
null
end as JOBTITLE
from dbo.REVENUE
inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
inner join dbo.[UFN_ADDRESSPROCESS_EMAILS](5, null) as EMAILADDRESS on CONSTITUENT.ID = EMAILADDRESS.CONSTITUENTID
left join dbo.CONSTITUENT CONTACT on EMAILADDRESS.CONTACTID=CONTACT.ID
left join dbo.STOCKDETAIL on REVENUEPAYMENTMETHOD.ID = STOCKDETAIL.ID
left join dbo.PROPERTYDETAIL PD on PD.ID = REVENUEPAYMENTMETHOD.ID
left join dbo.PROPERTYSUBTYPECODE PTC on PTC.ID = PD.PROPERTYSUBTYPECODEID
left join dbo.GIFTINKINDPAYMENTMETHODDETAIL GIK on GIK.ID = REVENUEPAYMENTMETHOD.ID
left join dbo.GIFTINKINDSUBTYPECODE GIKTC on GIKTC.ID = GIK.GIFTINKINDSUBTYPECODEID
left join dbo.REVENUEVAT on REVENUE.ID = REVENUEVAT.ID
left join dbo.VATTAXRATE on REVENUEVAT.VATTAXRATEID = VATTAXRATE.ID
where REVENUE.TRANSACTIONTYPECODE in (0, 4, 7);