V_QUERY_RECEIPTING_EMAILGIFTDETAILOUTPUT

View used for consolidated gift detail email output for the receipt process.

Fields

Field	Field Type	Null	Description
REVENUEID	uniqueidentifier		Revenue ID
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
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
CONSTITUENTPAYMENTAMOUNT	money	yes	Total constituent payment amount
CONSTITUENTRECEIPTAMOUNT	money	yes	Total constituent receipt amount
CONSTITUENTBENEFITAMOUNT	money	yes	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
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
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:  11/11/2014 4:23:59 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_RECEIPTING_EMAILGIFTDETAILOUTPUT AS



/*###WITH_CLAUSE_BEGIN###*/
/*###WITH_CLAUSE_END###*/
select
  REVENUE.ID REVENUEID,
  CONSTITUENT.ID as CONSTITUENTID,
  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,
  CONTACT.NAME 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,
  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,

/*###WITH_FIELDS_BEGIN###*/
  (select isnull(sum(TRANSACTIONAMOUNT),0) 
   from dbo.REVENUE R 
   where R.CONSTITUENTID = CONSTITUENT.ID 
     and R.TRANSACTIONTYPECODE in (0,4,7)
     and R.DONOTRECEIPT = 0
     and R.RECEIPTTYPECODE =1 -- consolidated

     and R.TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID
     and not exists (select RR.ID from REVENUERECEIPT RR where RR.REVENUEID = R.ID and R.NEEDSRERECEIPT = 0)) as CONSTITUENTPAYMENTAMOUNT,
  (select isnull(sum(RECEIPTAMOUNT),0) 
   from dbo.REVENUE R 
   where R.CONSTITUENTID = CONSTITUENT.ID 
     and R.TRANSACTIONTYPECODE in (0,4,7)
     and R.DONOTRECEIPT = 0
     and R.RECEIPTTYPECODE = 1 -- consolidated

     and R.TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID
     and not exists (select RR.ID from REVENUERECEIPT RR where RR.REVENUEID = R.ID and R.NEEDSRERECEIPT = 0)) as CONSTITUENTRECEIPTAMOUNT,
  (select isnull(sum(RB.TRANSACTIONTOTALVALUE),0) 
   from dbo.REVENUE R 
   inner join dbo.REVENUEBENEFIT RB on R.ID = RB.REVENUEID 
   where R.CONSTITUENTID = CONSTITUENT.ID
     and R.TRANSACTIONTYPECODE in (0,4,7)
     and R.DONOTRECEIPT = 0
     and R.RECEIPTTYPECODE = 1 -- consolidated

     and R.TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID
     and not exists (select RR.ID from REVENUERECEIPT RR where RR.REVENUEID = R.ID and R.NEEDSRERECEIPT = 0)) as CONSTITUENTBENEFITAMOUNT,
  (select count(distinct R.ID) 
   from dbo.REVENUE R 
   where R.CONSTITUENTID = CONSTITUENT.ID 
     and R.TRANSACTIONTYPECODE in (0,4,7)
     and R.DONOTRECEIPT = 0
     and R.RECEIPTTYPECODE = 1 -- consolidated

     and R.TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID
     and not exists (select RR.ID from REVENUERECEIPT RR where RR.REVENUEID = R.ID and R.NEEDSRERECEIPT = 0)) as NUMBEROFTRANSACTIONS,
  (select isnull(sum(RV.TRANSACTIONAMOUNTTOTAX),0) 
   from dbo.REVENUE R
   left join REVENUEVAT RV on R.ID = RV.ID
   where R.CONSTITUENTID = CONSTITUENT.ID 
     and R.TRANSACTIONTYPECODE in (0,4,7)
     and R.DONOTRECEIPT = 0
     and R.RECEIPTTYPECODE = 1 -- consolidated

     and R.TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID
     and not exists (select RR.ID from REVENUERECEIPT RR where RR.REVENUEID = R.ID and R.NEEDSRERECEIPT = 0)) as TOTALPORTIONSUBJECTTOVAT,
  (select isnull(sum(RV.TRANSACTIONVATAMOUNT),0) 
   from dbo.REVENUE R
   left join REVENUEVAT RV on R.ID = RV.ID
   where R.CONSTITUENTID =  CONSTITUENT.ID  
     and R.TRANSACTIONTYPECODE in (0,4,7)
     and R.DONOTRECEIPT = 0
     and R.RECEIPTTYPECODE = 1 -- consolidated

     and R.TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID
     and not exists (select RR.ID from REVENUERECEIPT RR where RR.REVENUEID = R.ID and R.NEEDSRERECEIPT = 0)) as TOTALVATAMOUNT,    
/*###WITH_FIELDS_END###*/

  [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],

/*###WITH_FIELDS_CURRENCY_BEGIN###*/
  [REVENUE].[TRANSACTIONCURRENCYID],
  [CURRENCY].[ISO4217] as [TRANSACTIONCURRENCY],
/*###WITH_FIELDS_CURRENCY_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)
  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
left join dbo.[CURRENCY] on [CURRENCY].[ID] = [REVENUE].[TRANSACTIONCURRENCYID]
/*###WITH_JOINS_BEGIN###*/
/*###WITH_JOINS_END###*/
where 
  REVENUE.TRANSACTIONTYPECODE in (0, 4, 7)
  and REVENUE.DONOTRECEIPT = 0
  and REVENUE.RECEIPTTYPECODE = 1 -- consolidated

  and not exists(select RR.ID from REVENUERECEIPT RR where RR.REVENUEID = REVENUE.ID and REVENUE.NEEDSRERECEIPT = 0)