V_QUERY_SPONSORSHIPBATCHCONTROLREPORT
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 |
| Description | nvarchar(700) | yes | Description |
| NAME | nvarchar(154) | yes | Child/Project |
| TYPE | varchar(7) | Opportunity Type | |
| DATE | datetime | Date | |
| PROGRAM | nvarchar(100) | PROGRAM |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 8/17/2011 2:35:57 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.91.1535.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_SPONSORSHIPBATCHCONTROLREPORT AS
select
SPONSORSHIP.ID,
NF.NAME as CONSTITUENTNAME,
CONSTITUENT.LOOKUPID,
CASE WHEN REVENUESPLIT.DESCRIPTION IS NOT NULL
THEN SUBSTRING(REVENUESPLIT.DESCRIPTION,12,LEN(REVENUESPLIT.DESCRIPTION) - 11)
END as Description,
case when exists(select SPONSORSHIPOPPORTUNITYCHILD.NAME from dbo.SPONSORSHIPOPPORTUNITYCHILD where SPONSORSHIPOPPORTUNITYCHILD.ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID) then (select top 1 SPONSORSHIPOPPORTUNITYCHILD.NAME from dbo.SPONSORSHIPOPPORTUNITYCHILD where SPONSORSHIPOPPORTUNITYCHILD.ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID) else (select top 1 SPONSORSHIPOPPORTUNITYPROJECT.NAME from dbo.SPONSORSHIPOPPORTUNITYPROJECT where SPONSORSHIPOPPORTUNITYPROJECT.ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID) end as NAME,
case when exists(select SPONSORSHIPOPPORTUNITYCHILD.NAME from dbo.SPONSORSHIPOPPORTUNITYCHILD where SPONSORSHIPOPPORTUNITYCHILD.ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID) then 'Child' else 'Project' end as TYPE,
SPONSORSHIP.DATEADDED DATE,
SPONSORSHIPPROGRAM.NAME as PROGRAM
from dbo.SPONSORSHIP
inner join dbo.SPONSORSHIPPROGRAM on SPONSORSHIPPROGRAM.ID = SPONSORSHIP.SPONSORSHIPPROGRAMID
inner join dbo.CONSTITUENT on CONSTITUENT.ID = SPONSORSHIP.CONSTITUENTID
left join dbo.SPONSORSHIPAFFILIATEPROGRAM SAP on SAP.ID = SPONSORSHIP.SPONSORSHIPPROGRAMID
left join dbo.REVENUESPLIT on REVENUESPLIT.ID = SPONSORSHIP.REVENUESPLITID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF