V_QUERY_REVENUEBATCHEXCEPTIONREPORT
View used for output for Revenue Batch Exception Report
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ORIGINALBATCHNUMBER | nvarchar(100) | Original batch number | |
| BATCHNUMBER | nvarchar(100) | yes | Batch number |
| OWNER | nvarchar(128) | yes | Owner |
| ISPAYMENT | int | Is payment | |
| ISPLEDGE | int | Is pledge | |
| ISRECURRINGGIFT | int | Is recurring gift | |
| PAYMENTAMOUNT | money | Payment amount | |
| PLEDGEAMOUNT | money | Pledge amount | |
| RECURRINGGIFTAMOUNT | money | Recurring gift amount | |
| ID | uniqueidentifier | System record ID | |
| CONSTITUENTNAME | nvarchar(154) | yes | Constituent name |
| CONSTITUENTLOOKUPID | nvarchar(100) | yes | Constituent lookup ID |
| DATE | datetime | Date | |
| TYPE | nvarchar(100) | yes | Revenue type |
| AMOUNT | money | Amount | |
| PAYMENTMETHOD | nvarchar(100) | yes | Payment method |
| CURRENCYID | uniqueidentifier | CURRENCYID | |
| CURRENCYNAME | nvarchar(100) | CURRENCYNAME | |
| ISO4217 | nvarchar(3) | ISO4217 | |
| DECIMALDIGITS | tinyint | DECIMALDIGITS | |
| CURRENCYSYMBOL | nvarchar(5) | CURRENCYSYMBOL | |
| SYMBOLDISPLAYSETTINGCODE | tinyint | SYMBOLDISPLAYSETTINGCODE | |
| REVENUELOOKUPID | nvarchar(100) | 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_REVENUEBATCHEXCEPTIONREPORT AS
/*Note: BATCHREVENUE.ID is the ID from the original batch, not the new exception batch*/
/*Since an exception batch may not have been created, use a left join in the second join (which */
/*is joining on the exception batch table*/
select
--Summary Info
[ORIGINAL].BATCHNUMBER as [ORIGINALBATCHNUMBER],
BATCH.BATCHNUMBER as [BATCHNUMBER],
(select USERNAME from dbo.APPUSER where APPUSER.ID = [BATCH].APPUSERID) as [OWNER],
case when BATCHREVENUE.TYPECODE = 0 then 1 else 0 end as [ISPAYMENT],
case when BATCHREVENUE.TYPECODE = 1 then 1 else 0 end as [ISPLEDGE],
case when BATCHREVENUE.TYPECODE = 3 then 1 else 0 end as [ISRECURRINGGIFT],
case when BATCHREVENUE.TYPECODE = 0 then BATCHREVENUE.AMOUNT else 0 end as [PAYMENTAMOUNT],
case when BATCHREVENUE.TYPECODE = 1 then BATCHREVENUE.AMOUNT else 0 end as [PLEDGEAMOUNT],
case when BATCHREVENUE.TYPECODE = 3 then BATCHREVENUE.AMOUNT else 0 end as [RECURRINGGIFTAMOUNT],
--End Summary Info
BATCHREVENUE.ID,
(
-- There should never be more than 1 row returned from the union but just in case an ID was reused,
-- in the two tables, "top 1" is used
select top 1 U.NAME from
(
select NAME from dbo.UFN_CONSTITUENT_DISPLAYNAME(BATCHREVENUE.CONSTITUENTID) NF
union
select NAME from dbo.BATCHREVENUECONSTITUENT where BATCHREVENUECONSTITUENT.ID = BATCHREVENUE.CONSTITUENTID
) as U
) as CONSTITUENTNAME,
(select LOOKUPID from dbo.CONSTITUENT where CONSTITUENT.ID = BATCHREVENUE.CONSTITUENTID) as CONSTITUENTLOOKUPID,
BATCHREVENUE.DATE,
cast(BATCHREVENUE.TYPE as nvarchar(100)) TYPE,
BATCHREVENUE.AMOUNT,
cast(BATCHREVENUE.PAYMENTMETHOD as nvarchar(100)) PAYMENTMETHOD,
CURRENCY.ID CURRENCYID,
CURRENCY.NAME CURRENCYNAME,
CURRENCY.ISO4217,
CURRENCY.DECIMALDIGITS,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.SYMBOLDISPLAYSETTINGCODE,
BATCHREVENUE.REVENUELOOKUPID
from dbo.BATCHREVENUE
inner join dbo.CURRENCY on CURRENCY.ID = BATCHREVENUE.TRANSACTIONCURRENCYID
inner join dbo.BATCH as [ORIGINAL] on [ORIGINAL].ID = BATCHREVENUE.BATCHID
left join dbo.BATCH on BATCH.ORIGINATINGBATCHID = [ORIGINAL].ID