V_QUERY_SPONSORSHIPOPPORTUNITY
This provides the ability to query all sponsorship opportunity fields.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| NAME | nvarchar(50) | yes | Name |
| SPONSORSHIPOPPORTUNITYGROUPID | uniqueidentifier | SPONSORSHIPOPPORTUNITYGROUPID | |
| SPONSORSHIPLOCATIONID | uniqueidentifier | SPONSORSHIPLOCATIONID | |
| AVAILABILITY | nvarchar(11) | yes | Availability |
| SPONSORSHIPREASONID | uniqueidentifier | yes | SPONSORSHIPREASONID |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| TYPE | varchar(7) | Type | |
| LOOKUPID | nvarchar(100) | yes | Lookup ID |
| LASTDOCUMENTATIONDATE | date | yes | Last documentation date |
| ELIGIBILITY | nvarchar(10) | yes | Eligibility |
| STATUS | varchar(6) | yes | Status |
| ACTIVESPONSORS | smallint | yes | Active sponsors |
| PENDINGTRANSFERSOUT | int | yes | Pending transfers out |
| PENDINGTRANSFERSIN | int | yes | Pending transfers in |
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_SPONSORSHIPOPPORTUNITY AS
select
SPONSORSHIPOPPORTUNITY.ID,
dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(SPONSORSHIPOPPORTUNITY.ID) NAME,
SPONSORSHIPOPPORTUNITY.SPONSORSHIPOPPORTUNITYGROUPID,
SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID,
SPONSORSHIPOPPORTUNITY.AVAILABILITY,
SPONSORSHIPOPPORTUNITY.SPONSORSHIPREASONID,
--SPONSORSHIPREASON.REASON,
--SPONSORSHIPREASON.REASONTYPE,
--SPONSORSHIPREASON.TRANSFERSPONSORSFORINELIGIBLECHILD,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
SPONSORSHIPOPPORTUNITY.DATEADDED,
SPONSORSHIPOPPORTUNITY.DATECHANGED,
SPONSORSHIPOPPORTUNITY.TSLONG,
case when SPONSORSHIPOPPORTUNITYCHILD.ID is not null then 'Child' else 'Project' end [TYPE],
SPONSORSHIPOPPORTUNITY.LOOKUPID,
SPONSORSHIPOPPORTUNITY.LASTDOCUMENTATIONDATE,
case when SPONSORSHIPOPPORTUNITYCHILD.ID is not null then SPONSORSHIPOPPORTUNITY.ELIGIBILITY else null end [ELIGIBILITY],
case when SPONSORSHIPOPPORTUNITYCHILD.ID is null and SPONSORSHIPOPPORTUNITY.ELIGIBILITYCODE = 1 then 'Open'
when SPONSORSHIPOPPORTUNITYCHILD.ID is null and SPONSORSHIPOPPORTUNITY.ELIGIBILITYCODE = 2 then 'Closed' end [STATUS],
dbo.UFN_SPONSORSHIPOPPORTUNITY_ACTIVESPONSORSHIPS(SPONSORSHIPOPPORTUNITY.ID)[ACTIVESPONSORS],
(select count(ST.ID)
from dbo.SPONSORSHIP S
inner join SPONSORSHIPTRANSACTION ST on S.ID = ST.CONTEXTSPONSORSHIPID
where ST.ACTIONCODE=6 and (select SS.STATUSCODE from dbo.SPONSORSHIP SS where SS.ID=ST.TARGETSPONSORSHIPID)=0
and S.SPONSORSHIPOPPORTUNITYID = SPONSORSHIPOPPORTUNITY.ID) [PENDINGTRANSFERSOUT],
(select count(ST.ID)
from dbo.SPONSORSHIP S
inner join dbo.SPONSORSHIPTRANSACTION ST on S.ID = ST.TARGETSPONSORSHIPID
where S.STATUSCODE = 0 and ST.ACTIONCODE = 6 and S.SPONSORSHIPOPPORTUNITYID = SPONSORSHIPOPPORTUNITY.ID) [PENDINGTRANSFERSIN]
from dbo.SPONSORSHIPOPPORTUNITY
--left join dbo.SPONSORSHIPREASON SPONSORSHIPREASON on SPONSORSHIPOPPORTUNITY.SPONSORSHIPREASONID = SPONSORSHIPREASON.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = SPONSORSHIPOPPORTUNITY.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = SPONSORSHIPOPPORTUNITY.CHANGEDBYID
left join dbo.SPONSORSHIPOPPORTUNITYCHILD on SPONSORSHIPOPPORTUNITY.ID = SPONSORSHIPOPPORTUNITYCHILD.ID