V_QUERY_INVITEEPROCESS_SEASONALADDRESSOUTPUT
View used for constituent seasonal address output with the invitation process.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| INVITEEID | uniqueidentifier | Invitee ID | |
| CONSTITUENTID | uniqueidentifier | Constituent system ID | |
| LOOKUPID | nvarchar(100) | yes | Constituent lookup ID |
| EVENTNAME | nvarchar(100) | yes | Event name |
| CONSTITUENTNAME | nvarchar(154) | yes | Constituent name |
| FORMALADDRESSEE | nvarchar(700) | yes | Formal addressee |
| PRIMARYSALUTATION | nvarchar(700) | yes | Primary salutation |
| PRIMARYCONTACT | nvarchar(154) | yes | Primary contact |
| PRIMARYPOSITION | nvarchar(100) | yes | Primary position |
| 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 |
| PRIMARYADDRESSEE | nvarchar(700) | yes | Primary addressee |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 8/17/2011 2:35:30 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.91.1535.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_INVITEEPROCESS_SEASONALADDRESSOUTPUT AS
select
INVITEE.ID as INVITEEID,
INVITEE.CONSTITUENTID as CONSTITUENTID,
CONSTITUENT.LOOKUPID as LOOKUPID,
EVENT.NAME as EVENTNAME,
NF.NAME as CONSTITUENTNAME,
(select FORMATTEDNAME from dbo.NAMEFORMAT inner join dbo.NAMEFORMATTYPECODE CODE on NAMEFORMAT.NAMEFORMATTYPECODEID = CODE.ID where CODE.DESCRIPTION = 'Formal Addressee' and NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID) as FORMALADDRESSEE,
(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)
NF_CONTACT.NAME
from
dbo.RELATIONSHIP
cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RELATIONSHIP.RECIPROCALCONSTITUENTID) NF_CONTACT
where
RELATIONSHIP.ISPRIMARYCONTACT = 1
and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID)
else
null
end as PRIMARYCONTACT,
case when CONSTITUENT.ISORGANIZATION = 1 then
(select top(1)
RELATIONSHIP.POSITION
from
dbo.RELATIONSHIP
where
RELATIONSHIP.ISPRIMARYCONTACT = 1 and
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID)
else
null
end as PRIMARYPOSITION,
(select ADDRESSBLOCK from dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate())) as ADDRESSBLOCK,
(select CITY from dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate())) as CITY,
(select STATE from dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate())) as STATE,
(select POSTCODE from dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate())) as POSTCODE,
(select COUNTRY from dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate())) as COUNTRY,
(select EMAILADDRESS from dbo.EMAILADDRESS where CONSTITUENTID = CONSTITUENT.ID and ISPRIMARY = 1) as EMAILADDRESS,
(select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.PRIMARYADDRESSEE = 1) as PRIMARYADDRESSEE
from
dbo.INVITEE
left join dbo.EVENT on INVITEE.EVENTID = EVENT.ID
left join dbo.CONSTITUENT on INVITEE.CONSTITUENTID = CONSTITUENT.ID
left join dbo.ADDRESS on
CONSTITUENT.ID = ADDRESS.CONSTITUENTID
and ADDRESS.ISPRIMARY = 1
and ADDRESS.DONOTMAIL = 0
left join dbo.STATE on ADDRESS.STATEID = STATE.ID
left join dbo.COUNTRY on ADDRESS.COUNTRYID = COUNTRY.ID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF