V_QUERY_BBDW_MAILPREFERENCE
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| CONSTITUENTMAILPREFERENCESYSTEMID | uniqueidentifier | yes | |
| CONSTITUENTDIMID | int | yes | |
| SENDMAIL | bit | yes | |
| USESEASONALADDRESS | bit | yes | |
| USEPRIMARYADDRESS | bit | yes | |
| DONOTSENDOTHERCHANNEL | bit | yes | |
| CONSTITUENTMAILPREFERENCECOMMENT | nvarchar(500) | yes | |
| BUSINESSUNIT | nvarchar(100) | yes | |
| APPEALCATEGORY | nvarchar(100) | yes | |
| CORRESPONDENCECODE | nvarchar(255) | yes | |
| DELIVERYMETHOD | nvarchar(5) | yes | |
| MAILTYPE | nvarchar(29) | yes | |
| RECEIPTTYPE | nvarchar(13) | yes | |
| ACKNOWLEDGEMENTPROCESS | nvarchar(100) | yes | |
| CORRESPONDENCEPROCESS | nvarchar(100) | yes | |
| PLEDGEREMINDERPROCESS | nvarchar(100) | yes | |
| CONSTITUENTSOLICITCODESYSTEMID | uniqueidentifier | yes | |
| HASINHERITEDCONSENT | bit | yes | |
| SOURCECODE | nvarchar(100) | yes | |
| SOURCEFILEPATH | nvarchar(260) | yes | |
| PRIVACYPOLICYFILEPATH | nvarchar(260) | yes | |
| SUPPORTINGINFORMATION | nvarchar(max) | yes | |
| CONSENTSTATEMENT | nvarchar(max) | yes | |
| SOLICITCODE | nvarchar(100) | yes |
Definition
Copy
create view BBDW.[V_QUERY_BBDW_MAILPREFERENCE] as
select
cmp.[CONSTITUENTMAILPREFERENCESYSTEMID],
cmp.[CONSTITUENTDIMID],
cmp.[SENDMAIL],
cmp.[USESEASONALADDRESS],
cmp.[USEPRIMARYADDRESS],
cmp.[DONOTSENDOTHERCHANNEL],
cmp.[CONSTITUENTMAILPREFERENCECOMMENT],
bu.[BUSINESSUNIT],
mp.[APPEALCATEGORY],
mp.[CORRESPONDENCECODE],
mp.[DELIVERYMETHOD],
mp.[MAILTYPE],
mp.[RECEIPTTYPE],
ap.[PROCESSNAME] as [ACKNOWLEDGEMENTPROCESS],
cp.[PROCESSNAME] as [CORRESPONDENCEPROCESS],
prp.[PROCESSNAME] as [PLEDGEREMINDERPROCESS],
cmp.[CONSTITUENTSOLICITCODESYSTEMID],
cmp.[HASINHERITEDCONSENT],
cmp.[SOURCECODE],
cmp.[SOURCEFILEPATH],
cmp.[PRIVACYPOLICYFILEPATH],
cmp.[SUPPORTINGINFORMATION],
cmp.[CONSENTSTATEMENT],
sc.[SOLICITCODE]
from BBDW.[FACT_CONSTITUENTMAILPREFERENCE] cmp
left join BBDW.[DIM_MAILPREFERENCE] mp on cmp.[MAILPREFERENCEDIMID] = mp.[MAILPREFERENCEDIMID] and cmp.[MAILPREFERENCEDIMID] <> 0
left join BBDW.[DIM_BUSINESSUNIT] bu on cmp.[BUSINESSUNITDIMID] = bu.[BUSINESSUNITDIMID] and cmp.[BUSINESSUNITDIMID] <> 0
left join BBDW.[DIM_PROCESS] ap on cmp.[ACKNOWLEDGEMENTPROCESSDIMID] = ap.[PROCESSDIMID] and cmp.[ACKNOWLEDGEMENTPROCESSDIMID] <> 0
left join BBDW.[DIM_PROCESS] cp on cmp.[CORRESPONDENCEPROCESSDIMID] = cp.[PROCESSDIMID] and cmp.[CORRESPONDENCEPROCESSDIMID] <> 0
left join BBDW.[DIM_PROCESS] prp on cmp.[PLEDGEREMINDERPROCESSDIMID] = prp.[PROCESSDIMID] and cmp.[PLEDGEREMINDERPROCESSDIMID] <> 0
left join BBDW.[FACT_CONSTITUENTSOLICITCODE] csc on csc.[CONSTITUENTSOLICITCODESYSTEMID] = cmp.[CONSTITUENTSOLICITCODESYSTEMID] and cmp.[CONSTITUENTSOLICITCODESYSTEMID] <> '00000000-0000-0000-0000-000000000000'
left join BBDW.[DIM_SOLICITCODE] sc on sc.[SOLICITCODEDIMID] = csc.[SOLICITCODEDIMID] and csc.[SOLICITCODEDIMID] <> 0