V_QUERY_RECEIPTING_STANDARDPAYMENT
View used for standard 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_STANDARDPAYMENT 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,
ADDRESS.ADDRESSBLOCK as ADDRESSBLOCK,
ADDRESS.CITY as CITY,
STATE.ABBREVIATION as STATE,
ADDRESS.POSTCODE as POSTCODE,
COUNTRY.DESCRIPTION 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)