USP_BBNC_DATALIST_CONSTITUENT_COMMUNICATION_PREFERENCES

List of all mail and email preferences for a constituent

Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.

Definition

Copy



                CREATE procedure dbo.USP_BBNC_DATALIST_CONSTITUENT_COMMUNICATION_PREFERENCES(@CONSTITUENTID uniqueidentifier)
                as
                set nocount on;

                select MP.ID,
                       MP.MAILTYPECODE,
                       MP.BUSINESSUNITCODEID,
                       MP.CATEGORYCODEID,
                       MP.SITEID,
                       MP.CORRESPONDENCECODEID, 
                       MP.RECEIPTTYPECODE,
                       MP.SENDMAIL, 
                       MP.DELIVERYMETHODCODE,
                       MP.ADDRESSID,
                       MP.EMAILADDRESSID, 
                       MP.EVENTCATEGORYCODEID,
                       CASE MP.DELIVERYMETHODCODE
                       WHEN 0 THEN A.ADDRESSBLOCK
                       WHEN 1 THEN EA.EMAILADDRESS
                       END AS [ADDRESS],
             0 as ISNOPREFERENCE,
                         CASE WHEN EIR.CATEGORY = 112 and ISBLACKLISTED =1 then 1 ELSE 0 END    as ISSPAM
                from [dbo].[MAILPREFERENCE] MP
                LEFT JOIN [dbo].[ADDRESS] A on A.ID = MP.ADDRESSID
                LEFT JOIN [dbo].EMAILADDRESS EA ON EA.ID = MP.EMAILADDRESSID
                LEFT JOIN [dbo].EMAILINVALIDRECIPIENT as EIR on EIR.ADDRESS = EA.EMAILADDRESS
                where MP.CONSTITUENTID = @CONSTITUENTID