V_QUERY_RECEIPTING_GIFTSUMMARYOUTPUT
View used for consolidated gift summary output for the receipting process.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| CONSTITUENTID | uniqueidentifier | Constituent 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 |
| CONSTITUENTPAYMENTAMOUNT | money | yes | Total constituent payment amount |
| CONSTITUENTRECEIPTAMOUNT | money | yes | Total constituent receipt amount |
| CONSTITUENTBENEFITAMOUNT | money | Total constituent benefit amount | |
| NUMBEROFTRANSACTIONS | int | yes | Total number of transactions |
| TOTALPORTIONSUBJECTTOVAT | money | yes | Total portion subject to VAT |
| TOTALVATAMOUNT | money | yes | Total VAT amount |
| TRANSACTIONCURRENCYID | uniqueidentifier | yes | Transaction currency ID |
| TRANSACTIONCURRENCY | nvarchar(3) | Transaction currency | |
| JOBTITLE | nvarchar(100) | yes | Job title |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 11/11/2014 4:14:45 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_RECEIPTING_GIFTSUMMARYOUTPUT AS
/*###WITH_CLAUSE_BEGIN###*/
with REVENUE_CTE as (
select -- don't need distinct here; REVENUEID is in the select clause
REVENUE.CONSTITUENTID,
REVENUE.TRANSACTIONCURRENCYID,
REVENUE.ID as REVENUEID,
REVENUE.TRANSACTIONAMOUNT,
REVENUE.RECEIPTAMOUNT
from dbo.REVENUE
where REVENUE.TRANSACTIONTYPECODE in (0, 4, 7)
and REVENUE.DONOTRECEIPT = 0
and REVENUE.RECEIPTTYPECODE = 1
and not exists (select RR.ID from REVENUERECEIPT as RR where RR.REVENUEID = REVENUE.ID and REVENUE.NEEDSRERECEIPT = 0)
), BENEFITS_CTE as (
select
REVENUE_CTE.CONSTITUENTID,
sum(REVENUEBENEFIT.TRANSACTIONTOTALVALUE) as CONSTITUENTBENEFITAMOUNT -- REVENUEBENEFIT is one-to-optional-many with REVENUE; can't join above because it will affect aggregates
from dbo.REVENUEBENEFIT
inner join REVENUE_CTE on REVENUE_CTE.REVENUEID = REVENUEBENEFIT.REVENUEID
group by REVENUE_CTE.CONSTITUENTID
), AMOUNTS_CTE as (
select
REVENUE_CTE.CONSTITUENTID,
REVENUE_CTE.TRANSACTIONCURRENCYID,
sum(REVENUE_CTE.TRANSACTIONAMOUNT) as CONSTITUENTPAYMENTAMOUNT,
sum(REVENUE_CTE.RECEIPTAMOUNT) as CONSTITUENTRECEIPTAMOUNT,
count(REVENUE_CTE.REVENUEID) as NUMBEROFTRANSACTIONS, -- don't need distinct here; REVENUE_CTE is distinct already
sum(isnull(REVENUEVAT.TRANSACTIONAMOUNTTOTAX, 0)) as TOTALPORTIONSUBJECTTOVAT, -- REVENUEVAT is one-to-optional-one with REVENUE
sum(isnull(REVENUEVAT.TRANSACTIONVATAMOUNT, 0)) as TOTALVATAMOUNT
from REVENUE_CTE
left join REVENUEVAT on REVENUEVAT.ID = REVENUE_CTE.REVENUEID
group by REVENUE_CTE.CONSTITUENTID, REVENUE_CTE.TRANSACTIONCURRENCYID
)
/*###WITH_CLAUSE_END###*/
select
CONSTITUENT.ID as CONSTITUENTID,
CONSTITUENT.NAME as CONSTITUENTNAME,
CONSTITUENT.LOOKUPID as CONSTITUENTLOOKUPID,
PRIMARYADDRESSEE.FORMATTEDNAME as PRIMARYADDRESSEE,
PRIMARYSALUTATION.FORMATTEDNAME as PRIMARYSALUTATION,
CONTACT.NAME as PRIMARYCONTACT,
PRIMARYCONTACT.POSITION as POSITION,
ADDRESS.ADDRESSBLOCK as ADDRESSBLOCK,
ADDRESS.CITY as CITY,
STATE.ABBREVIATION as STATE,
ADDRESS.POSTCODE as POSTCODE,
COUNTRY.DESCRIPTION as COUNTRY,
EMAILADDRESS.EMAILADDRESS,
/*###WITH_FIELDS_BEGIN###*/
AMOUNTS_CTE.CONSTITUENTPAYMENTAMOUNT,
AMOUNTS_CTE.CONSTITUENTRECEIPTAMOUNT,
isnull(BENEFITS_CTE.CONSTITUENTBENEFITAMOUNT, 0) as CONSTITUENTBENEFITAMOUNT,
AMOUNTS_CTE.NUMBEROFTRANSACTIONS,
AMOUNTS_CTE.TOTALPORTIONSUBJECTTOVAT,
AMOUNTS_CTE.TOTALVATAMOUNT,
AMOUNTS_CTE.TRANSACTIONCURRENCYID,
CURRENCY.ISO4217 as TRANSACTIONCURRENCY,
/*###WITH_FIELDS_END###*/
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
order by RELATIONSHIPJOBINFO.STARTDATE desc)
else null end as JOBTITLE
from dbo.CONSTITUENT
/*###WITH_JOINS_BEGIN###*/
inner join AMOUNTS_CTE on AMOUNTS_CTE.CONSTITUENTID = CONSTITUENT.ID
inner join dbo.CURRENCY on CURRENCY.ID = AMOUNTS_CTE.TRANSACTIONCURRENCYID
left join BENEFITS_CTE on BENEFITS_CTE.CONSTITUENTID = AMOUNTS_CTE.CONSTITUENTID
/*###WITH_JOINS_END###*/
-- address joins
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
-- email address join
left join dbo.EMAILADDRESS on EMAILADDRESS.CONSTITUENTID = CONSTITUENT.ID and EMAILADDRESS.ISPRIMARY = 1
-- addressee / salutation joins
left join dbo.NAMEFORMAT as PRIMARYADDRESSEE on PRIMARYADDRESSEE.CONSTITUENTID = CONSTITUENT.ID and PRIMARYADDRESSEE.PRIMARYADDRESSEE = 1
left join dbo.NAMEFORMAT as PRIMARYSALUTATION on PRIMARYSALUTATION.CONSTITUENTID = CONSTITUENT.ID and PRIMARYSALUTATION.PRIMARYSALUTATION = 1
-- organization contact joins
left join dbo.RELATIONSHIP as PRIMARYCONTACT on CONSTITUENT.ISORGANIZATION = 1 and PRIMARYCONTACT.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID and PRIMARYCONTACT.ISPRIMARYCONTACT = 1
left join dbo.CONSTITUENT as CONTACT on CONTACT.ID = PRIMARYCONTACT.RECIPROCALCONSTITUENTID