V_QUERY_SPONSORSHIP
This provides the ability to query all sponsorship fields.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| CONSTITUENTID | uniqueidentifier | CONSTITUENTID | |
| SPONSORSHIPPROGRAMID | uniqueidentifier | SPONSORSHIPPROGRAMID | |
| SPONSORSHIPOPPORTUNITYID | uniqueidentifier | SPONSORSHIPOPPORTUNITYID | |
| REVENUESPLITID | uniqueidentifier | yes | REVENUESPLITID |
| STARTDATE | date | yes | Start date |
| ENDDATE | date | yes | End date |
| SPONSORSHIPLOCATIONID | uniqueidentifier | yes | SPONSORSHIPLOCATIONID |
| CHILDGENDER | nvarchar(13) | yes | Preferred gender |
| ISHIVPOSITIVE | nvarchar(13) | yes | Preferred HIV positive |
| HASCONDITION | nvarchar(13) | yes | Preferred has condition |
| ISORPHANED | nvarchar(13) | yes | Preferred is orphaned |
| ISSOLESPONSORSHIP | bit | Sole sponsorship | |
| 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 |
| STATUS | nvarchar(25) | yes | Status |
| REASON | nvarchar(100) | yes | Last action reason |
| ACTION | nvarchar(24) | yes | Last action |
| PLANNEDENDDATE | date | yes | Expiration date |
| FINANCIALSPONSOR | uniqueidentifier | yes | Financial sponsor |
| GIFTSPONSORSHIP | int | Gift sponsorship | |
| SENDREMINDER | bit | yes | Send reminders |
| EXPIRATIONREASON | nvarchar(100) | yes | Expiration reason |
| COMMITMENTID | nvarchar(100) | yes | Commitment ID |
| REVENUEID | uniqueidentifier | yes | |
| SPONSORSHIPREASONID | uniqueidentifier | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 1/21/2016 5:07:41 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.154.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_SPONSORSHIP AS
select
SPONSORSHIP.ID,
SPONSORSHIP.CONSTITUENTID,
SPONSORSHIP.SPONSORSHIPPROGRAMID,
SPONSORSHIP.SPONSORSHIPOPPORTUNITYID,
SPONSORSHIP.REVENUESPLITID,
SPONSORSHIP.STARTDATE,
SPONSORSHIP.ENDDATE,
SPONSORSHIP.SPONSORSHIPLOCATIONID,
SPONSORSHIP.CHILDGENDER,
SPONSORSHIP.ISHIVPOSITIVE,
SPONSORSHIP.HASCONDITION,
SPONSORSHIP.ISORPHANED,
SPONSORSHIP.ISSOLESPONSORSHIP,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
SPONSORSHIP.DATEADDED,
SPONSORSHIP.DATECHANGED,
SPONSORSHIP.TSLONG,
case SPONSORSHIP.STATUSCODE
when 1 then
case LASTTRANSACTION.ACTIONCODE
when 6 then 'Active (Transfer pending)'
when 5 then 'Active (Reassigned)'
else 'Active' end
when 2 then
case LASTTRANSACTION.ACTIONCODE
when 1 then 'Transferred'
when 2 then
case SPONSORSHIP.ID
when LASTTRANSACTION.CONTEXTSPONSORSHIPID then
case SPONSORSHIPREASON.REASONTYPECODE when 11 then 'Expired' else 'Cancelled' end
when LASTTRANSACTION.DECLINEDSPONSORSHIPID then 'Declined' end
when 3 then 'Terminated'
when 5 then 'Reassigned'
when 7 then
case SPONSORSHIP.ID
when LASTTRANSACTION.CONTEXTSPONSORSHIPID then 'Transferred'
when LASTTRANSACTION.DECLINEDSPONSORSHIPID then 'Declined' end
when 8 then 'Transferred' end
else SPONSORSHIP.STATUS
end STATUS,
case when SPONSORSHIP.STATUSCODE = 2 or (SPONSORSHIP.STATUSCODE = 1 and LASTTRANSACTION.ACTIONCODE = 6) then SPONSORSHIPREASON.REASON end REASON,
LASTTRANSACTION.ACTION,
SPONSORSHIP.PLANNEDENDDATE,
REVENUE.CONSTITUENTID FINANCIALSPONSOR,
case
when isnull(REVENUE.CONSTITUENTID,SPONSORSHIP.CONSTITUENTID) = SPONSORSHIP.CONSTITUENTID then 0
else 1
end GIFTSPONSORSHIP,
REVENUESCHEDULE.SENDPLEDGEREMINDER as SENDREMINDER,
EXPIRATIONREASON.REASON EXPIRATIONREASON,
(select LOOKUPID from dbo.SPONSORSHIPCOMMITMENT where SPONSORSHIPCOMMITMENT.ID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID) COMMITMENTID,
REVENUESPLIT.FINANCIALTRANSACTIONID REVENUEID,
SPONSORSHIPREASON.ID as SPONSORSHIPREASONID
from dbo.SPONSORSHIP
inner join dbo.SPONSORSHIPTRANSACTION CREATETRANSACTION on CREATETRANSACTION.SPONSORSHIPCOMMITMENTID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID and CREATETRANSACTION.TRANSACTIONSEQUENCE = (select min(TRANSACTIONSEQUENCE) from dbo.SPONSORSHIPTRANSACTION MINSEQUENCE where MINSEQUENCE.SPONSORSHIPCOMMITMENTID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID and MINSEQUENCE.TARGETSPONSORSHIPID = SPONSORSHIP.ID)
inner join dbo.SPONSORSHIPTRANSACTION LASTTRANSACTION on LASTTRANSACTION.SPONSORSHIPCOMMITMENTID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID and LASTTRANSACTION.TRANSACTIONSEQUENCE = (select max(TRANSACTIONSEQUENCE) from dbo.SPONSORSHIPTRANSACTION MAXSEQUENCE where MAXSEQUENCE.SPONSORSHIPCOMMITMENTID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID and SPONSORSHIP.ID in(MAXSEQUENCE.CONTEXTSPONSORSHIPID,MAXSEQUENCE.TARGETSPONSORSHIPID,MAXSEQUENCE.DECLINEDSPONSORSHIPID) and MAXSEQUENCE.ACTIONCODE <> 9)
left join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.ID = SPONSORSHIP.REVENUESPLITID
left join dbo.FINANCIALTRANSACTION REVENUE on REVENUESPLIT.FINANCIALTRANSACTIONID = REVENUE.ID
left join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
left join dbo.SPONSORSHIPREASON on SPONSORSHIPREASON.ID = LASTTRANSACTION.SPONSORSHIPREASONID
left join dbo.SPONSORSHIPREASON as EXPIRATIONREASON on EXPIRATIONREASON.ID = SPONSORSHIP.EXPIRATIONREASONID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = SPONSORSHIP.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = SPONSORSHIP.CHANGEDBYID