V_QUERY_REVENUEBATCHCONTROLREPORT

View used for output for Revenue Batch Control Report.

Fields

Field	Field Type	Null	Description
ID	uniqueidentifier		System record ID
CONSTITUENTNAME	nvarchar(154)	yes	Constituent name
LOOKUPID	nvarchar(100)	yes	Constituent lookup ID
AMOUNT	money		Amount
TYPE	nvarchar(100)	yes	Revenue type
DATE	datetime	yes	Date
PAYMENTMETHOD	nvarchar(100)	yes	PAYMENTMETHOD
DESIGNATION	nvarchar(max)	yes	Designation
APPEAL	nvarchar(100)	yes	Appeal
ISPAYMENT	int		ISPAYMENT
ISPLEDGE	int		ISPLEDGE
ISRECURRINGGIFT	int		ISRECURRINGGIFT
PAYMENTAMOUNT	money		PAYMENTAMOUNT
PLEDGEAMOUNT	money		PLEDGEAMOUNT
RECURRINGGIFTAMOUNT	money		RECURRINGGIFTAMOUNT
KEYNAME	nvarchar(100)		Key name
FIRSTNAME	nvarchar(50)		First name
MIDDLENAME	nvarchar(50)		Middle name
MGCOUNT	int	yes	MGCOUNT
MGAMOUNT	money	yes	MGAMOUNT
MEMBERSHIPAMOUNT	money	yes	MEMBERSHIPAMOUNT
MEMBERSHIPCOUNT	int	yes	MEMBERSHIPCOUNT
MGDETAIL	xml	yes	MGDETAIL
MEMBERSHIPDETAIL	xml	yes	MEMBERSHIPDETAIL
REVENUECURRENCYID	uniqueidentifier	yes	REVENUECURRENCYID
REVENUELOOKUPID	nvarchar(100)	yes	Revenue lookup ID

Definition

Copy

/*
Generated by Blackbaud AppFx Platform
Date:  9/30/2015 12:58:59 AM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.153.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_REVENUEBATCHCONTROLREPORT AS



                select 
                    REVENUE.ID,
                    CONSTITUENT_NF.NAME as CONSTITUENTNAME,
                    CONSTITUENT.LOOKUPID,
                    REVENUE.TRANSACTIONAMOUNT AMOUNT,
                    cast(dbo.UFN_REVENUE_BUILDTYPELIST_FORTRANSACTIONID(REVENUE.ID) as nvarchar(100)) as [TYPE],
                    REVENUE.DATE,
                    (
                        select cast(dbo.UDA_BUILDLIST(distinct PAYMENTMETHOD) as nvarchar(100))
                        from dbo.REVENUEPAYMENTMETHOD
                        where REVENUEID = REVENUE.ID
                    ) as PAYMENTMETHOD,
                    dbo.UFN_REVENUE_BUILDSPLITLIST_FORTRANSACTIONID(REVENUE.ID) as [DESIGNATION],
                    APPEAL.NAME as [APPEAL],
                    case when REVENUE.TRANSACTIONTYPECODE = 0 then 1 else 0 end as [ISPAYMENT],
                    case when REVENUE.TRANSACTIONTYPECODE = 1 then 1 else 0 end as [ISPLEDGE],
                    case when REVENUE.TRANSACTIONTYPECODE = 2 then 1 else 0 end as [ISRECURRINGGIFT],
                    case when REVENUE.TRANSACTIONTYPECODE = 0 then REVENUE.TRANSACTIONAMOUNT else 0 end as [PAYMENTAMOUNT],
                    case when REVENUE.TRANSACTIONTYPECODE = 1 then REVENUE.TRANSACTIONAMOUNT else 0 end as [PLEDGEAMOUNT],
                    case when REVENUE.TRANSACTIONTYPECODE = 2 then REVENUE.TRANSACTIONAMOUNT else 0 end as [RECURRINGGIFTAMOUNT],
                    CONSTITUENT.KEYNAME,
                    CONSTITUENT.FIRSTNAME,
                    CONSTITUENT.MIDDLENAME,
                    (
                        select count(ID) 
                        from dbo.REVENUEMATCHINGGIFT 
                        where MGSOURCEREVENUEID = REVENUE.ID
                    ) as [MGCOUNT],
                    (
                        select coalesce(sum(MGREVENUE.TRANSACTIONAMOUNT), 0) 
                        from dbo.REVENUEMATCHINGGIFT 
                            inner join dbo.REVENUE as [MGREVENUE] on [MGREVENUE].ID = REVENUEMATCHINGGIFT.ID 
                        where REVENUEMATCHINGGIFT.MGSOURCEREVENUEID = REVENUE.ID
                    ) as [MGAMOUNT],
                    (
                        select coalesce(sum(REVENUESPLIT.TRANSACTIONAMOUNT), 0) 
                        from dbo.REVENUESPLIT 
                        where REVENUESPLIT.APPLICATIONCODE = 5 and REVENUESPLIT.REVENUEID = REVENUE.ID
                    ) as [MEMBERSHIPAMOUNT],
                    (
                        select count(REVENUESPLIT.ID)
                        from dbo.REVENUESPLIT
                        where REVENUESPLIT.APPLICATIONCODE = 5 and REVENUESPLIT.REVENUEID = REVENUE.ID
                    ) as [MEMBERSHIPCOUNT],
                    (
                        select REVENUE.ID, MGREVENUE.TRANSACTIONAMOUNT, MGREVENUE.TRANSACTIONCURRENCYID
                        from dbo.REVENUEMATCHINGGIFT 
                            inner join dbo.REVENUE as [MGREVENUE] on [MGREVENUE].ID = REVENUEMATCHINGGIFT.ID 
                        where REVENUEMATCHINGGIFT.MGSOURCEREVENUEID = REVENUE.ID
                        for xml raw('ITEM'),type,elements,BINARY BASE64
                    ) as [MGDETAIL],
                    (
                        select REVENUE.ID, REVENUESPLIT.TRANSACTIONAMOUNT, REVENUESPLIT.TRANSACTIONCURRENCYID
                        from dbo.REVENUESPLIT 
                        where REVENUESPLIT.APPLICATIONCODE = 5 and REVENUESPLIT.REVENUEID = REVENUE.ID
                        for xml raw('ITEM'),type,elements,BINARY BASE64
                    ) as [MEMBERSHIPDETAIL],                    
                    REVENUE.TRANSACTIONCURRENCYID REVENUECURRENCYID,
                    REVENUE.LOOKUPID as REVENUELOOKUPID
                from dbo.REVENUE                               
                    inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) CONSTITUENT_NF
                    --JamesWill CR277662-062107 Payments split among revenue with and without appeals were being duplicated

                    left join dbo.APPEAL on APPEAL.ID = REVENUE.APPEALID
                where REVENUE.TRANSACTIONTYPECODE <> 3 --Exclude matching gift claims (3)