V_QUERY_CONSTITUENTEMAILADDRESS
This provides the ability to query for email address information.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| CONSTITUENTID | uniqueidentifier | Constituent ID | |
| EMAILADDRESSTYPECODEID_TRANSLATION | nvarchar(100) | yes | Email address type |
| EMAILADDRESS | UDT_EMAILADDRESS | Email address | |
| ISPRIMARY | bit | Primary email address | |
| 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 |
| DONOTEMAIL | bit | Do not email | |
| INFOSOURCECODEID_TRANSLATION | nvarchar(100) | yes | Information source |
| INFOSOURCECOMMENTS | nvarchar(256) | Information source comments | |
| RELATIONSHIPID | uniqueidentifier | yes | Relationship ID |
| ORIGIN | nvarchar(9) | yes | Origin |
| ORIGININFO | nvarchar(100) | yes | Origin information |
| STARTDATE | datetime | yes | Start date |
| ENDDATE | datetime | yes | End date |
| BOUNCED | int | Bounced | |
| EMAILADDRESSTYPECODEID | uniqueidentifier | yes | |
| INFOSOURCECODEID | uniqueidentifier | yes | |
| SPAMCOMPLAINT | int | ||
| CONFIDENTIAL | bit | ||
| DONOTEMAILREASONCODEID_TRANSLATION | nvarchar(100) | yes | |
| DONOTEMAILREASONCODEID | uniqueidentifier | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 5/3/2024 2:17:15 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.3700.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_CONSTITUENTEMAILADDRESS AS
select
EMAILADDRESS.ID,
EMAILADDRESS.CONSTITUENTID,
EMAILADDRESSTYPECODE.DESCRIPTION as [EMAILADDRESSTYPECODEID_TRANSLATION],
EMAILADDRESS.EMAILADDRESS,
EMAILADDRESS.ISPRIMARY,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
EMAILADDRESS.DATEADDED,
EMAILADDRESS.DATECHANGED,
EMAILADDRESS.TSLONG,
EMAILADDRESS.DONOTEMAIL,
INFOSOURCECODE.DESCRIPTION as [INFOSOURCECODEID_TRANSLATION],
EMAILADDRESS.INFOSOURCECOMMENTS,
EMAILADDRESS.RELATIONSHIPID,
EMAILADDRESS.ORIGIN,
case
when [ORIGINCODE] <> 0 then
dbo.UFN_EMAILADDRESS_ORIGINCODE_GETDESCRIPTION([ORIGINCODE])
else INFOSOURCECODE.DESCRIPTION
end as [ORIGININFO],
EMAILADDRESS.STARTDATE,
EMAILADDRESS.ENDDATE,
case when EXISTS(SELECT ID FROM dbo.EMAILINVALIDRECIPIENT WHERE EMAILINVALIDRECIPIENT.ADDRESS = EMAILADDRESS.EMAILADDRESS AND EMAILINVALIDRECIPIENT.ISBLACKLISTED = 1 and CATEGORY <> 112) then 1 else 0 end as BOUNCED,
[EMAILADDRESS].[EMAILADDRESSTYPECODEID],
[EMAILADDRESS].[INFOSOURCECODEID],
case when EXISTS(SELECT ID FROM dbo.EMAILINVALIDRECIPIENT WHERE EMAILINVALIDRECIPIENT.ADDRESS = EMAILADDRESS.EMAILADDRESS AND EMAILINVALIDRECIPIENT.ISBLACKLISTED = 1 and CATEGORY = 112) then 1 else 0 end as SPAMCOMPLAINT,
EMAILADDRESS.ISCONFIDENTIAL AS CONFIDENTIAL,
DONOTEMAILREASONCODE.DESCRIPTION as [DONOTEMAILREASONCODEID_TRANSLATION],
EMAILADDRESS.DONOTEMAILREASONCODEID
/*#EXTENSION*/
from dbo.EMAILADDRESS
left join dbo.EMAILADDRESSTYPECODE on EMAILADDRESSTYPECODE.ID = EMAILADDRESS.EMAILADDRESSTYPECODEID
left join dbo.INFOSOURCECODE on INFOSOURCECODE.ID = EMAILADDRESS.INFOSOURCECODEID
left join dbo.DONOTEMAILREASONCODE on DONOTEMAILREASONCODE.ID = EMAILADDRESS.DONOTEMAILREASONCODEID
left join dbo.CHANGEAGENT as [ADDEDBY] on ADDEDBY.ID = EMAILADDRESS.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on CHANGEDBY.ID = EMAILADDRESS.CHANGEDBYID