V_QUERY_SPONSORSHIPMARKETING
This provides the ability to query all sponsorship marketing fields.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| CONSTITUENTID | uniqueidentifier | Constituent | |
| CONSTITUENTNAME | nvarchar(154) | yes | Sponsor Name |
| DONORNAME | nvarchar(154) | yes | Donor Name |
| CONSTITUENTADDRESS | nvarchar(500) | yes | Sponsor Address |
| DONORADDRESS | nvarchar(500) | yes | Donor Address |
| SPONSORSHIPOPPORTUNITYIDCHILD | nvarchar(50) | yes | Child |
| SPONSORSHIPOPPORTUNITYIDPROJECT | nvarchar(100) | yes | Project |
| SPONSORSHIPLOCATIONID | nvarchar(100) | yes | Sponsorship Location |
| SPONSORSHIPCHILDPROGRAMID | nvarchar(100) | yes | Sponsorship Child Program |
| SPONSORSHIPPROJECTPROGRAMID | nvarchar(100) | yes | Sponsorship Project Program |
| REASON | nvarchar(100) | yes | Last action reason |
| ACTION | nvarchar(24) | yes | Last action |
| GIFTSPONSORSHIP | int | Gift sponsorship | |
| LOOKUPID | nvarchar(100) | yes | Opportunity lookup ID |
| 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 | yes | Date added |
| DATECHANGED | datetime | yes | Date changed |
| TSLONG | bigint | yes | Timestamp value |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 11/11/2014 4:11:53 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_SPONSORSHIPMARKETING AS
with [OPPORTUNITYCHILDNAME] as (
select
[SPONSORSHIPOPPORTUNITYCHILD].[ID],
dbo.[UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION]([SPONSORSHIPOPPORTUNITYCHILD].[ID]) as [NAME]
from dbo.[SPONSORSHIPOPPORTUNITYCHILD]
), [PROGRAMNAME] as (
select
[ID],
[NAME]
from dbo.[SPONSORSHIPPROGRAM]
)
select
SPONSORSHIP.ID,
SPONSORSHIP.CONSTITUENTID,
dbo.UFN_CONSTITUENT_BUILDNAME(SPONSORSHIP.CONSTITUENTID) as CONSTITUENTNAME,
dbo.UFN_CONSTITUENT_BUILDNAME(REVENUE.CONSTITUENTID) as DONORNAME,
(select top 1 FULLADDRESS from dbo.UFN_CONSTITUENT_GETADDRESSES(convert(nvarchar(255),SPONSORSHIP.CONSTITUENTID)+'1')) as CONSTITUENTADDRESS,
(select top 1 FULLADDRESS from dbo.UFN_CONSTITUENT_GETADDRESSES(convert(nvarchar(255),REVENUE.CONSTITUENTID)+'1')) as DONORADDRESS,
case when [SOG].[SPONSORSHIPOPPORTUNITYTYPECODE] = 1 then [OPPORTUNITYCHILDNAME].[NAME] else null end as [SPONSORSHIPOPPORTUNITYIDCHILD],
case when SOG.SPONSORSHIPOPPORTUNITYTYPECODE = 2 then SOP.NAME else null end as SPONSORSHIPOPPORTUNITYIDPROJECT,
SL.NAME as SPONSORSHIPLOCATIONID,
case when [SOG].[SPONSORSHIPOPPORTUNITYTYPECODE] = 1 then [PROGRAMNAME].[NAME] else null end as [SPONSORSHIPCHILDPROGRAMID],
case when [SOG].[SPONSORSHIPOPPORTUNITYTYPECODE] = 2 then [PROGRAMNAME].[NAME] else null end as [SPONSORSHIPPROJECTPROGRAMID],
SR.REASON as REASON,
ST.ACTION,
case when REVENUE.CONSTITUENTID = SPONSORSHIP.CONSTITUENTID then 0 else 1 end GIFTSPONSORSHIP,
SO.LOOKUPID,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
SO.DATEADDED,
SO.DATECHANGED,
SO.TSLONG
from dbo.SPONSORSHIP
left join dbo.SPONSORSHIPOPPORTUNITY SO on SO.ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID
left join dbo.SPONSORSHIPOPPORTUNITYGROUP SOG on SOG.ID = SO.SPONSORSHIPOPPORTUNITYGROUPID
left join [OPPORTUNITYCHILDNAME] on [OPPORTUNITYCHILDNAME].[ID] = [SO].[ID]
left join dbo.SPONSORSHIPOPPORTUNITYPROJECT SOP on SO.ID = SOP.ID
left join dbo.SPONSORSHIPTRANSACTION ST on ST.SPONSORSHIPCOMMITMENTID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID and ST.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 [PROGRAMNAME] on [PROGRAMNAME].[ID] = [SPONSORSHIP].[SPONSORSHIPPROGRAMID]
left join dbo.SPONSORSHIPLOCATION SL on SL.ID = SO.SPONSORSHIPLOCATIONID
left join dbo.SPONSORSHIPREASON SR on SR.ID = ST.SPONSORSHIPREASONID
left join dbo.REVENUESPLIT on REVENUESPLIT.ID = SPONSORSHIP.REVENUESPLITID
left join dbo.REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = SPONSORSHIP.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = SPONSORSHIP.CHANGEDBYID