V_QUERY_CONSTITUENTMAILPREFERENCE
Provides the ability to query a constituent's mail preferences.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| CONSTITUENTID | uniqueidentifier | Constituent ID | |
| MAILTYPE | nvarchar(29) | yes | Mail type |
| ACKNOWLEDGEMENTID | uniqueidentifier | yes | Acknowledgement process ID |
| CORRESPONDENCEID | uniqueidentifier | yes | Correspondence process ID |
| PLEDGEREMINDERID | uniqueidentifier | yes | Pledge reminder process ID |
| BUSINESSUNIT | nvarchar(100) | yes | Business unit |
| CATEGORY | nvarchar(100) | yes | Appeal Category |
| EVENTCATEGORY | nvarchar(100) | yes | Event Category |
| SITE | nvarchar(250) | yes | Site |
| CORRESPONDENCECODE | nvarchar(100) | yes | Correspondence code |
| RECEIPTTYPE | nvarchar(13) | yes | Receipt type |
| SENDMAIL | bit | Send mail | |
| DELIVERYMETHOD | nvarchar(5) | yes | Delivery method |
| DONOTSENDOTHERCHANNEL | bit | Exclude from other delivery method | |
| ADDRESSID | uniqueidentifier | yes | Address ID |
| EMAILADDRESSID | uniqueidentifier | yes | Email address ID |
| USESEASONALADDRESS | bit | Uses seasonal address | |
| USEPRIMARYADDRESS | bit | Uses primary address | |
| USEPRIMARYEMAIL | bit | Uses primary email address | |
| COMMENTS | nvarchar(500) | Comments | |
| 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 |
| SITEID | uniqueidentifier | yes | Site ID |
| BUSINESSUNITCODEID | uniqueidentifier | yes | |
| APPEALCATEGORYCODEID | uniqueidentifier | yes | |
| EVENTCATEGORYCODEID | uniqueidentifier | yes | |
| CORRESPONDENCECODEID | uniqueidentifier | yes | |
| CONSTITUENTSOLICITCODEID | uniqueidentifier | yes | |
| CONSTITUENTSOLICITCODE | nvarchar(100) | yes | |
| HASINHERITEDCONSENT | bit | ||
| SOURCEEVIDENCECODEID | uniqueidentifier | yes | |
| SOURCEEVIDENCECODE | nvarchar(100) | yes | |
| SOURCEFILE | nvarchar(260) | yes | |
| PRIVACYPOLICY | nvarchar(260) | yes | |
| SUPPORTINGINFORMATION | nvarchar(max) | yes | |
| CONSENTSTATEMENT | nvarchar(max) | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 2/9/2018 5:33:06 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.174.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_CONSTITUENTMAILPREFERENCE AS
select
MAILPREFERENCE.ID,
MAILPREFERENCE.CONSTITUENTID,
MAILPREFERENCE.MAILTYPE,
MAILPREFERENCE.ACKNOWLEDGEMENTID,
MAILPREFERENCE.CORRESPONDENCEID,
MAILPREFERENCE.PLEDGEREMINDERID,
BUSINESSUNITCODE.DESCRIPTION as BUSINESSUNIT,
APPEALCATEGORYCODE.DESCRIPTION as CATEGORY,
EVENTCATEGORYCODE.DESCRIPTION as EVENTCATEGORY,
SITE.NAME as SITE,
CORRESPONDENCECODE.NAME CORRESPONDENCECODE,
MAILPREFERENCE.RECEIPTTYPE,
MAILPREFERENCE.SENDMAIL,
case MAILPREFERENCE.SENDMAIL
when 0 then ''
else MAILPREFERENCE.DELIVERYMETHOD
end as DELIVERYMETHOD,
MAILPREFERENCE.DONOTSENDOTHERCHANNEL,
MAILPREFERENCE.ADDRESSID,
MAILPREFERENCE.EMAILADDRESSID,
MAILPREFERENCE.USESEASONALADDRESS,
MAILPREFERENCE.USEPRIMARYADDRESS,
MAILPREFERENCE.USEPRIMARYEMAIL,
MAILPREFERENCE.COMMENTS,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
MAILPREFERENCE.DATEADDED,
MAILPREFERENCE.DATECHANGED,
MAILPREFERENCE.TSLONG,
SITE.ID as SITEID,
[MAILPREFERENCE].[BUSINESSUNITCODEID],
[MAILPREFERENCE].[CATEGORYCODEID] as [APPEALCATEGORYCODEID],
[MAILPREFERENCE].[EVENTCATEGORYCODEID],
[MAILPREFERENCE].[CORRESPONDENCECODEID],
MAILPREFERENCE.CONSTITUENTSOLICITCODEID,
SOLICITCODE.DESCRIPTION as CONSTITUENTSOLICITCODE,
MAILPREFERENCE.HASINHERITEDCONSENT,
case MAILPREFERENCE.HASINHERITEDCONSENT
when 0 then MAILPREFERENCE.SOURCECODEID
when 1 then CONSTITUENTSOLICITCODE.SOURCECODEID
end as SOURCEEVIDENCECODEID,
case MAILPREFERENCE.HASINHERITEDCONSENT
when 0 then (select DESCRIPTION from dbo.DATAPROTECTIONEVIDENCESOURCECODE where ID = MAILPREFERENCE.SOURCECODEID)
when 1 then (select DESCRIPTION from dbo.DATAPROTECTIONEVIDENCESOURCECODE where ID = CONSTITUENTSOLICITCODE.SOURCECODEID)
end as SOURCEEVIDENCECODE,
case MAILPREFERENCE.HASINHERITEDCONSENT
when 0 then MAILPREFERENCE.SOURCEFILEPATH
when 1 then CONSTITUENTSOLICITCODE.SOURCEFILEPATH
end as SOURCEFILE,
case MAILPREFERENCE.HASINHERITEDCONSENT
when 0 then MAILPREFERENCE.PRIVACYPOLICYFILEPATH
when 1 then CONSTITUENTSOLICITCODE.PRIVACYPOLICYFILEPATH
end as PRIVACYPOLICY,
case MAILPREFERENCE.HASINHERITEDCONSENT
when 0 then MAILPREFERENCE.SUPPORTINGINFORMATION
when 1 then CONSTITUENTSOLICITCODE.SUPPORTINGINFORMATION
end as SUPPORTINGINFORMATION,
case MAILPREFERENCE.HASINHERITEDCONSENT
when 0 then MAILPREFERENCE.CONSENTSTATEMENT
when 1 then CONSTITUENTSOLICITCODE.CONSENTSTATEMENT
end as CONSENTSTATEMENT
from dbo.MAILPREFERENCE
left join dbo.BUSINESSUNITCODE on BUSINESSUNITCODE.ID = MAILPREFERENCE.BUSINESSUNITCODEID
left join dbo.APPEALCATEGORYCODE on APPEALCATEGORYCODE.ID = MAILPREFERENCE.CATEGORYCODEID
left join dbo.EVENTCATEGORYCODE on EVENTCATEGORYCODE.ID = MAILPREFERENCE.EVENTCATEGORYCODEID
left join dbo.SITE on SITE.ID = MAILPREFERENCE.SITEID
left join dbo.CORRESPONDENCECODE on CORRESPONDENCECODE.ID = MAILPREFERENCE.CORRESPONDENCECODEID
left join dbo.CONSTITUENTSOLICITCODE on MAILPREFERENCE.CONSTITUENTSOLICITCODEID = CONSTITUENTSOLICITCODE.ID
left join dbo.SOLICITCODE on CONSTITUENTSOLICITCODE.SOLICITCODEID = SOLICITCODE.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on ADDEDBY.ID = MAILPREFERENCE.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on CHANGEDBY.ID = MAILPREFERENCE.CHANGEDBYID