V_QUERY_RECEIPTING_GIFTDETAILOUTPUT

View used for consolidated gift detail output for the receipting process.

Fields

Field	Field Type	Null	Description
REVENUEID	uniqueidentifier		Revenue ID
CONSTITUENTID	uniqueidentifier	yes	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
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_GIFTDETAILOUTPUT 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,
  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 coalesce(sum(RB.TRANSACTIONTOTALVALUE),0) 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.MEDIANPRICE 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 coalesce(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 coalesce(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 coalesce(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 coalesce(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 coalesce(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
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
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)