V_QUERY_RECEIPTING_EMAILGIFTSUMMARYOUTPUT
View used for consolidated email 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 |
| 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_EMAILGIFTSUMMARYOUTPUT 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,
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###*/
-- email address join
inner join dbo.UFN_ADDRESSPROCESS_EMAILS(5, null) as EMAILADDRESS on CONSTITUENT.ID = EMAILADDRESS.CONSTITUENTID
-- 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 (there seems to be the possibility that the contact returned by email address processing might not be the primary contact,
-- but that's the way it was before as well, so...)
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 EMAILADDRESS.CONTACTID = CONTACT.ID