V_QUERY_AUCTIONITEMBATCHEXCEPTIONREPORT
View used for output of the Auction Item 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 |
| ID | uniqueidentifier | System Record ID | |
| DONOR | nvarchar(154) | yes | Donor |
| DONORLOOKUPID | nvarchar(100) | yes | Donor lookup ID |
| NAME | nvarchar(100) | Item name | |
| VALUE | money | Item value | |
| ISOCURRENCYCODE | nvarchar(3) | yes | ISO currency code |
| CURRENCYSYMBOL | nvarchar(5) | yes | Currency symbol |
| SYMBOLDISPLAYSETTINGCODE | tinyint | yes | Symbol display setting code |
| DECIMALDIGITS | tinyint | yes | Decimal digits |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 8/17/2011 2:36:01 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.91.1535.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_AUCTIONITEMBATCHEXCEPTIONREPORT AS
/*Note: BATCHAUCTIONITEM.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
[ORIGINAL].BATCHNUMBER as [ORIGINALBATCHNUMBER],
BATCH.BATCHNUMBER as [BATCHNUMBER],
(select USERNAME from dbo.APPUSER where APPUSER.ID = [BATCH].APPUSERID) as [OWNER],
BATCHAUCTIONITEM.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.CONSTITUENT where CONSTITUENT.ID = BATCHAUCTIONITEM.CONSTITUENTID
union
select NAME from dbo.BATCHAUCTIONITEMCONSTITUENT where BATCHAUCTIONITEMCONSTITUENT.ID = BATCHAUCTIONITEM.CONSTITUENTID
) as U
) as [DONOR],
(select LOOKUPID from dbo.CONSTITUENT where CONSTITUENT.ID = BATCHAUCTIONITEM.CONSTITUENTID) as [DONORLOOKUPID],
BATCHAUCTIONITEM.NAME,
BATCHAUCTIONITEM.VALUE,
CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
CURRENCYPROPERTIES.CURRENCYSYMBOL,
CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE,
CURRENCYPROPERTIES.DECIMALDIGITS
from dbo.BATCHAUCTIONITEM
inner join dbo.BATCH as [ORIGINAL] on [ORIGINAL].ID = BATCHAUCTIONITEM.BATCHID
left join dbo.BATCH on BATCH.ORIGINATINGBATCHID = [ORIGINAL].ID
outer apply dbo.UFN_CURRENCY_GETPROPERTIES(BATCHAUCTIONITEM.TRANSACTIONCURRENCYID) CURRENCYPROPERTIES