V_QUERY_RECEIPTING_SEASONALADDRESSPAYMENT
View used for seasonal address output for the receipting process.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| TRANSACTIONID | uniqueidentifier | Transaction ID | |
| CONSTITUENTNAME | nvarchar(154) | yes | Constituent name |
| CONSTITUENTLOOKUPID | nvarchar(100) | yes | Constituent lookup ID |
| PRIMARYADDRESSEE | nvarchar(700) | yes | Primary addressee |
| PRIMARYSALUTATION | nvarchar(700) | yes | Primary salutation |
| PRIMARYCONTACT | nvarchar(154) | yes | Primary contact |
| POSITION | nvarchar(100) | yes | Position |
| 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 |
| 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 | |
| ID | uniqueidentifier | ID | |
| AMOUNTTOTAX | 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:34:34 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.91.1535.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_RECEIPTING_SEASONALADDRESSPAYMENT AS
select
REVENUE.ID as TRANSACTIONID,
CONSTITUENT.NAME as CONSTITUENTNAME,
CONSTITUENT.LOOKUPID as CONSTITUENTLOOKUPID,
(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)
CONTACT.NAME
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 PRIMARYCONTACT,
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,
(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,
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,
REVENUE.ID,
[REVENUEVAT].TRANSACTIONAMOUNTTOTAX as AMOUNTTOTAX,
[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 REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
left join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
left join dbo.ADDRESS on ADDRESS.CONSTITUENTID = REVENUE.CONSTITUENTID 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 and EMAILADDRESS.ISPRIMARY = 1
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)