V_QUERY_PLEDGEREMINDERPROCESS_SPONSORSHIP
View used for sponsorship output for the pledge reminder process.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| REVENUEID | uniqueidentifier | System record id | |
| CONSTITUENTNAME | nvarchar(154) | yes | Constituent name |
| CONSTITUENTLOOKUPID | nvarchar(100) | yes | Constituent lookup ID |
| PRIMARYADDRESSEE | nvarchar(700) | yes | Primary addressee |
| PRIMARYSALUTATION | nvarchar(700) | yes | Primary salutation |
| PRIMARYCONTACT | nvarchar(154) | yes | Primary contact |
| ADDRESSBLOCK | nvarchar(150) | yes | Address block |
| CITY | nvarchar(50) | yes | City |
| STATE | nvarchar(50) | yes | State |
| POSTCODE | nvarchar(12) | yes | Post code |
| COUNTRY | nvarchar(100) | yes | Country |
| EMAILADDRESS | UDT_EMAILADDRESS | yes | Email address |
| TOTALRECURRINGGIFTAMOUNT | money | Total Amount | |
| GIVENANONYMOUSLY | varchar(3) | Anonymous | |
| RECURRINGGIFTDATE | datetime | yes | Date |
| RECURRINGGIFTBALANCE | int | yes | Pledge balance |
| AMOUNTPAID | money | yes | Amount paid |
| APPEALNAME | nvarchar(100) | yes | Appeal name |
| DESIGNATION1NAME | nvarchar(512) | yes | Designation 1 |
| DESIGNATION1PUBLICNAME | nvarchar(512) | yes | Designation 1 public name |
| DESIGNATION1AMOUNT | money | yes | Designation 1 amount |
| DESIGNATION2NAME | nvarchar(512) | yes | Designation 2 |
| DESIGNATION2PUBLICNAME | nvarchar(512) | yes | Designation 2 public name |
| DESIGNATION2AMOUNT | money | yes | Designation 2 amount |
| POSITION | nvarchar(100) | yes | Position |
| REVENUELOOKUPID | nvarchar(100) | yes | Revenue lookup ID |
| CORRESPONDINGSPONSORLOOKUPID | nvarchar(100) | yes | Corresponding sponsor lookup ID |
| CORRESPONDINGSPONSORNAME | nvarchar(154) | yes | Corresponding sponsor name |
| OPPORTUNITYTYPE | varchar(7) | yes | Opportunity type |
| OPPORTUNITYLOOKUPID | nvarchar(100) | yes | Opportunity lookup ID |
| OPPORTUNITYNAME | nvarchar(50) | yes | Opportunity name |
| OPPORTUNITYLOCATION | nvarchar(100) | yes | Opportunity location |
| TRANSACTIONCURRENCYID | uniqueidentifier | Transaction currency ID | |
| TRANSACTIONCURRENCY | nvarchar(3) | yes | Transaction currency |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 3/19/2013 1:33:04 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_PLEDGEREMINDERPROCESS_SPONSORSHIP AS
select
FINANCIALTRANSACTION.ID as REVENUEID,
CONSTITUENT.NAME as CONSTITUENTNAME,
CONSTITUENT.LOOKUPID as CONSTITUENTLOOKUPID,
(select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.PRIMARYADDRESSEE = 1) as PRIMARYADDRESSEE,
(select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.PRIMARYSALUTATION = 1) as PRIMARYSALUTATION,
case when CONSTITUENT.ISORGANIZATION = 1 then
(select top (1)
CONTACT.NAME
from dbo.RELATIONSHIP
inner join dbo.CONSTITUENT CONTACT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONTACT.ID
where RELATIONSHIP.ISPRIMARYCONTACT = 1
and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID)
else
null
end as PRIMARYCONTACT,
ADDRESS.ADDRESSBLOCK as ADDRESSBLOCK,
ADDRESS.CITY as CITY,
STATE.ABBREVIATION as STATE,
ADDRESS.POSTCODE as POSTCODE,
COUNTRY.DESCRIPTION as COUNTRY,
EMAILADDRESS.EMAILADDRESS,
FINANCIALTRANSACTION.TRANSACTIONAMOUNT as TOTALRECURRINGGIFTAMOUNT,
case REVENUE_EXT.GIVENANONYMOUSLY when 1 then 'Yes' else 'No' end as GIVENANONYMOUSLY,
cast(FINANCIALTRANSACTION.DATE as datetime) as RECURRINGGIFTDATE,
null as RECURRINGGIFTBALANCE,
coalesce((select sum(AMOUNT) from dbo.RECURRINGGIFTACTIVITY where SOURCEREVENUEID = FINANCIALTRANSACTION.ID and TYPECODE = 0), 0) as AMOUNTPAID,
APPEAL.NAME as APPEALNAME,
(select DESIGNATIONNAME from dbo.UFN_REVENUE_GETORDEREDDESIGNATIONS(FINANCIALTRANSACTION.ID) where ROWNUMBER = 1) as DESIGNATION1NAME,
(select DESIGNATIONPUBLICNAME from dbo.UFN_REVENUE_GETORDEREDDESIGNATIONS(FINANCIALTRANSACTION.ID) where ROWNUMBER = 1) as DESIGNATION1PUBLICNAME,
(select AMOUNT from dbo.UFN_REVENUE_GETORDEREDDESIGNATIONS(FINANCIALTRANSACTION.ID) where ROWNUMBER = 1) as DESIGNATION1AMOUNT,
(select DESIGNATIONNAME from dbo.UFN_REVENUE_GETORDEREDDESIGNATIONS(FINANCIALTRANSACTION.ID) where ROWNUMBER = 2) as DESIGNATION2NAME,
(select DESIGNATIONPUBLICNAME from dbo.UFN_REVENUE_GETORDEREDDESIGNATIONS(FINANCIALTRANSACTION.ID) where ROWNUMBER = 2) as DESIGNATION2PUBLICNAME,
(select AMOUNT from dbo.UFN_REVENUE_GETORDEREDDESIGNATIONS(FINANCIALTRANSACTION.ID) where ROWNUMBER = 2) as DESIGNATION2AMOUNT,
case when CONSTITUENT.ISORGANIZATION = 1 then
(select top 1 RELATIONSHIP.POSITION from dbo.RELATIONSHIP
inner join dbo.CONSTITUENT CONTACT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONTACT.ID
where RELATIONSHIP.ISPRIMARYCONTACT = 1
and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID)
else
null
end as POSITION,
FINANCIALTRANSACTION.CALCULATEDUSERDEFINEDID as REVENUELOOKUPID,
dbo.UFN_CONSTITUENT_GETLOOKUPID(SPONSORSHIP.CONSTITUENTID) as CORRESPONDINGSPONSORLOOKUPID,
dbo.UFN_CONSTITUENT_BUILDNAME(SPONSORSHIP.CONSTITUENTID) as CORRESPONDINGSPONSORNAME,
case when SPONSORSHIP.SPONSORSHIPOPPORTUNITYID is not null then
case when SPONSORSHIPOPPORTUNITYCHILD.ID is not null then
'Child'
else
'Project'
end
else
null
end as OPPORTUNITYTYPE,
SPONSORSHIPOPPORTUNITY.LOOKUPID as OPPORTUNITYLOOKUPID,
dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(SPONSORSHIPOPPORTUNITY.ID) as OPPORTUNITYNAME,
SPONSORSHIPLOCATION.NAME as OPPORTUNITYLOCATION,
FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
dbo.UFN_CURRENCY_GETISO(FINANCIALTRANSACTION.TRANSACTIONCURRENCYID) as TRANSACTIONCURRENCY
from dbo.FINANCIALTRANSACTION
inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
left join dbo.CONSTITUENT on FINANCIALTRANSACTION.CONSTITUENTID = CONSTITUENT.ID
left join dbo.ADDRESS on ADDRESS.CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID and ADDRESS.ISPRIMARY = 1 and ADDRESS.DONOTMAIL = 0
left join dbo.COUNTRY on ADDRESS.COUNTRYID = COUNTRY.ID
left join dbo.STATE on ADDRESS.STATEID = STATE.ID
left join dbo.EMAILADDRESS on EMAILADDRESS.CONSTITUENTID=CONSTITUENT.ID and EMAILADDRESS.ISPRIMARY = 1
left join dbo.APPEAL on REVENUE_EXT.APPEALID = APPEAL.ID
left join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = FINANCIALTRANSACTION.ID
left join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
left join dbo.SPONSORSHIP on SPONSORSHIP.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
left join dbo.SPONSORSHIPOPPORTUNITY on SPONSORSHIPOPPORTUNITY.ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID
left join dbo.SPONSORSHIPOPPORTUNITYCHILD on SPONSORSHIPOPPORTUNITYCHILD.ID = SPONSORSHIPOPPORTUNITY.ID
left join dbo.SPONSORSHIPLOCATION on SPONSORSHIPLOCATION.ID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID
where FINANCIALTRANSACTION.TYPECODE = 2
and FINANCIALTRANSACTION.DELETEDON is null
and SPONSORSHIP.STATUSCODE = 1