V_QUERY_SPONSORSHIPBATCHEXCEPTIONREPORT
View used for output for Sponsorship Batch Exception Report
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ORIGINALBATCHNUMBER | nvarchar(100) | Original batch number | |
| OWNER | nvarchar(128) | yes | Owner |
| ID | uniqueidentifier | System record ID | |
| CONSTITUENTNAME | nvarchar(154) | yes | Constituent name |
| CONSTITUENTLOOKUPID | nvarchar(100) | yes | Constituent lookup id |
| AMOUNT | money | Amount | |
| PAYMENTMETHOD | nvarchar(47) | yes | Payment method |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 8/17/2011 2:35:53 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.91.1535.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_SPONSORSHIPBATCHEXCEPTIONREPORT AS
/*Note: BATCHSPONSORSHIP.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],
(select USERNAME from dbo.APPUSER where APPUSER.ID = [ORIGINAL].APPUSERID) as [OWNER],
BATCHSPONSORSHIP.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 = BATCHSPONSORSHIP.CONSTITUENTID
union
select NAME from dbo.BATCHSPONSORSHIPCONSTITUENT where BATCHSPONSORSHIPCONSTITUENT.ID = BATCHSPONSORSHIP.CONSTITUENTID
) as U
) as CONSTITUENTNAME,
(select LOOKUPID from dbo.CONSTITUENT where CONSTITUENT.ID = BATCHSPONSORSHIP.CONSTITUENTID) as CONSTITUENTLOOKUPID,
BATCHSPONSORSHIP.AMOUNT,
BATCHSPONSORSHIP.PAYMENTMETHOD
from dbo.BATCHSPONSORSHIP
inner join dbo.BATCH as [ORIGINAL] on [ORIGINAL].ID = BATCHSPONSORSHIP.BATCHID
left join dbo.BATCH on BATCH.ORIGINATINGBATCHID = [ORIGINAL].ID