V_QUERY_CONSOLIDATEDRECEIPTS_SUMMARYOUTPUT

View used for consolidated gift summary output for constituents.

Fields

Field	Field Type	Null	Description
CONSTITUENTID	uniqueidentifier		Constituent ID
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
BASECURRENCYID	uniqueidentifier	yes

Definition

Copy

/*
Generated by Blackbaud AppFx Platform
Date:  3/19/2013 1:41:24 AM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_CONSOLIDATEDRECEIPTS_SUMMARYOUTPUT AS



select 
    CONSTITUENT.ID as CONSTITUENTID,
    (select coalesce(sum(AMOUNT),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
        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
        and not exists (select RR.ID from REVENUERECEIPT RR where RR.REVENUEID = R.ID and R.NEEDSRERECEIPT = 0)) as CONSTITUENTRECEIPTAMOUNT,
    (select coalesce(sum(RB.QUANTITY * RB.UNITVALUE),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
        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
        and not exists (select RR.ID from REVENUERECEIPT RR where RR.REVENUEID = R.ID and R.NEEDSRERECEIPT = 0)) as NUMBEROFTRANSACTIONS,
    (select coalesce(sum(RV.AMOUNTTOTAX),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
        and not exists (select RR.ID from REVENUERECEIPT RR where RR.REVENUEID = R.ID and R.NEEDSRERECEIPT = 0)) as TOTALPORTIONSUBJECTTOVAT,
    (select coalesce(sum(RV.VATAMOUNT),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
        and not exists (select RR.ID from REVENUERECEIPT RR where RR.REVENUEID = R.ID and R.NEEDSRERECEIPT = 0)) as TOTALVATAMOUNT,
    REVENUE.BASECURRENCYID as BASECURRENCYID
from 
    dbo.CONSTITUENT
inner 
    join dbo.REVENUE on CONSTITUENT.ID = REVENUE.CONSTITUENTID
where
    REVENUE.TRANSACTIONTYPECODE in (0,4,7)
    and REVENUE.DONOTRECEIPT = 0
    and REVENUE.RECEIPTTYPECODE = 1
    and not exists(select RR.ID from REVENUERECEIPT RR where RR.REVENUEID = REVENUE.ID and REVENUE.NEEDSRERECEIPT = 0)
group by 
    CONSTITUENT.ID, REVENUE.BASECURRENCYID