UFN_CONTACTPREFERENCES_GETFORMATS_EXT_2

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTID	uniqueidentifier	IN
@NAMEFORMATPARAMETERID	uniqueidentifier	IN
@ADDRESSPROCESSINGOPTIONID	uniqueidentifier	IN
@MAILTYPE	tinyint	IN
@PARAMETERSETID	uniqueidentifier	IN
@SEASONALDATE	datetime	IN
@INCLUDEHOUSEHOLDPROCESSING	bit	IN
@INDUSESEASONALADDRESS	bit	IN
@ORGMAILINGPREFERENCE	tinyint	IN
@INDALTADDRESS1TYPECODEID	uniqueidentifier	IN
@INDALTADDRESS1ISPRIMARY	bit	IN
@INDALTADDRESS2TYPECODEID	uniqueidentifier	IN
@INDALTADDRESS2ISPRIMARY	bit	IN
@ORGALTADDRESS1TYPECODEID	uniqueidentifier	IN
@ORGALTADDRESS1ISPRIMARY	bit	IN
@ORGALTADDRESS2TYPECODEID	uniqueidentifier	IN
@ORGALTADDRESS2ISPRIMARY	bit	IN
@INDINCLUDEWITHNOADDRESS	bit	IN
@ORGINCLUDEWITHNOADDRESS	bit	IN
@ORGINCLUDEWITHNOCONTACT	bit	IN
@ORGSENDTOALLCONTACTS	bit	IN
@INDUSECONSTITUENTPREFS	bit	IN
@ORGUSECONSTITUENTPREFS	bit	IN
@GROUPALTADDRESS1TYPECODEID	uniqueidentifier	IN
@GROUPALTADDRESS1ISPRIMARY	bit	IN
@GROUPALTADDRESS2TYPECODEID	uniqueidentifier	IN
@GROUPALTADDRESS2ISPRIMARY	bit	IN
@GROUPINCLUDEWITHNOADDRESS	bit	IN
@GROUPUSECONSTITUENTPREFS	bit	IN
@INDIVIDUALADDRESSEES	xml	IN
@ADDRESSEEFUNCTIONID	uniqueidentifier	IN
@INDIVIDUALSALUTATIONS	xml	IN
@SALUTATIONFUNCTIONID	uniqueidentifier	IN
@ORGADDRESSEES	xml	IN
@CONTACTADDRESSEEFUNCTIONID	uniqueidentifier	IN
@ORGSALUTATIONCODE	tinyint	IN
@ORGSALUTATIONS	xml	IN
@CONTACTSALUTATIONFUNCTIONID	uniqueidentifier	IN
@CONTACTSALUTATIONOPTIONCODE	tinyint	IN
@CUSTOMNAME	nvarchar(100)	IN
@GROUPADDRESSEES	xml	IN
@GROUPADDRESSEEFUNCTIONID	uniqueidentifier	IN
@GROUPSALUTATIONCODE	tinyint	IN
@GROUPSALUTATIONS	xml	IN
@GROUPSALUTATIONFUNCTIONID	uniqueidentifier	IN
@GROUPNOCONTACTOPTIONCODE	bit	IN
@GROUPNOCONTACTCUSTOMNAME	nvarchar(100)	IN
@JOINTRULETYPECODE	tinyint	IN
@JOINTSELECTIONID	uniqueidentifier	IN
@JOINTSELECTIONBOTHRULETYPECODE	tinyint	IN
@JOINTSELECTIONNEITHERRULETYPECODE	tinyint	IN
@EXCLUDESPOUSE	bit	IN

Definition

Copy



--New overload function that takes CONSTITUENTID as parameter

--This is calling the new function(UFN_CONTACTPREFERENCES_GETFORMATS_5_WORKER) instead of UFN_CONTACTPREFERENCES_GETFORMATS_4_WORKER

create function dbo.[UFN_CONTACTPREFERENCES_GETFORMATS_EXT_2]
(
    @CONSTITUENTID uniqueidentifier,
    @NAMEFORMATPARAMETERID uniqueidentifier,
    @ADDRESSPROCESSINGOPTIONID uniqueidentifier,
    @MAILTYPE tinyint,
    @PARAMETERSETID uniqueidentifier,
    @SEASONALDATE datetime,
    @INCLUDEHOUSEHOLDPROCESSING bit,
    @INDUSESEASONALADDRESS bit,
    @ORGMAILINGPREFERENCE tinyint,
    @INDALTADDRESS1TYPECODEID uniqueidentifier,
    @INDALTADDRESS1ISPRIMARY bit,
    @INDALTADDRESS2TYPECODEID uniqueidentifier,
    @INDALTADDRESS2ISPRIMARY bit,
    @ORGALTADDRESS1TYPECODEID uniqueidentifier,
    @ORGALTADDRESS1ISPRIMARY bit,
    @ORGALTADDRESS2TYPECODEID uniqueidentifier,
    @ORGALTADDRESS2ISPRIMARY bit,
    @INDINCLUDEWITHNOADDRESS bit,
    @ORGINCLUDEWITHNOADDRESS bit,
    @ORGINCLUDEWITHNOCONTACT bit,
    @ORGSENDTOALLCONTACTS bit,
    @INDUSECONSTITUENTPREFS bit,
    @ORGUSECONSTITUENTPREFS bit,
    @GROUPALTADDRESS1TYPECODEID uniqueidentifier,
    @GROUPALTADDRESS1ISPRIMARY bit,
    @GROUPALTADDRESS2TYPECODEID uniqueidentifier,
    @GROUPALTADDRESS2ISPRIMARY bit,
    @GROUPINCLUDEWITHNOADDRESS bit,
    @GROUPUSECONSTITUENTPREFS bit,
    @INDIVIDUALADDRESSEES xml,
    @ADDRESSEEFUNCTIONID uniqueidentifier,
    @INDIVIDUALSALUTATIONS xml,
    @SALUTATIONFUNCTIONID uniqueidentifier,
    @ORGADDRESSEES xml,
    @CONTACTADDRESSEEFUNCTIONID uniqueidentifier,
    @ORGSALUTATIONCODE tinyint,
    @ORGSALUTATIONS xml,
    @CONTACTSALUTATIONFUNCTIONID uniqueidentifier,
    @CONTACTSALUTATIONOPTIONCODE tinyint,
    @CUSTOMNAME nvarchar(100),
    @GROUPADDRESSEES xml,
    @GROUPADDRESSEEFUNCTIONID uniqueidentifier,
    @GROUPSALUTATIONCODE tinyint,
    @GROUPSALUTATIONS xml,
    @GROUPSALUTATIONFUNCTIONID uniqueidentifier,
    @GROUPNOCONTACTOPTIONCODE bit,
    @GROUPNOCONTACTCUSTOMNAME nvarchar(100),
    @JOINTRULETYPECODE As tinyint,
    @JOINTSELECTIONID As uniqueidentifier,
    @JOINTSELECTIONBOTHRULETYPECODE As tinyint,
    @JOINTSELECTIONNEITHERRULETYPECODE As tinyint,
    @EXCLUDESPOUSE as bit
)
returns table
as
return
(
    select
        NF.CONSTITUENTID,
        NF.ADDRESSEE,
        NF.SALUTATION,
        NF.CONTACT,
        NF.POSITION,
        A.ADDRESSBLOCK,
        A.CITY,
        STATE.ABBREVIATION as STATE,
        A.POSTCODE,
        COUNTRY.DESCRIPTION as COUNTRY,
        NF.CONTACTID,
        NF.HOUSEHOLDID,
        NF.RETURNEDASHOUSEHOLDMEMBER,
        NF.GROUPCONTACTID,
        coalesce(NF.CONTACTID, NF.GROUPCONTACTID, NF.CONSTITUENTID) as CONTACTORCONSTITUENTID,
        NF.ADDRESSID,
        NF.MAILTOCONSTITUENTID,
        NF.SPOUSEID
    from dbo.UFN_CONTACTPREFERENCES_GETFORMATS_5_WORKER(
        @ADDRESSPROCESSINGOPTIONID,
        @MAILTYPE,
        @PARAMETERSETID,
        @SEASONALDATE,
        @INCLUDEHOUSEHOLDPROCESSING,
        @INDUSESEASONALADDRESS,
        @ORGMAILINGPREFERENCE,
        @INDALTADDRESS1TYPECODEID,
        @INDALTADDRESS1ISPRIMARY,
        @INDALTADDRESS2TYPECODEID,
        @INDALTADDRESS2ISPRIMARY,
        @ORGALTADDRESS1TYPECODEID,
        @ORGALTADDRESS1ISPRIMARY,
        @ORGALTADDRESS2TYPECODEID,
        @ORGALTADDRESS2ISPRIMARY,
        @INDINCLUDEWITHNOADDRESS,
        @ORGINCLUDEWITHNOADDRESS,
        @ORGINCLUDEWITHNOCONTACT,
        @ORGSENDTOALLCONTACTS,
        @INDUSECONSTITUENTPREFS,
        @ORGUSECONSTITUENTPREFS,
        @GROUPALTADDRESS1TYPECODEID,
        @GROUPALTADDRESS1ISPRIMARY,
        @GROUPALTADDRESS2TYPECODEID,
        @GROUPALTADDRESS2ISPRIMARY,
        @GROUPINCLUDEWITHNOADDRESS,
        @GROUPUSECONSTITUENTPREFS,
        @INDIVIDUALADDRESSEES,
        @ADDRESSEEFUNCTIONID,
        @INDIVIDUALSALUTATIONS,
        @SALUTATIONFUNCTIONID,
        @ORGADDRESSEES,
        @CONTACTADDRESSEEFUNCTIONID,
        @ORGSALUTATIONCODE,
        @ORGSALUTATIONS,
        @CONTACTSALUTATIONFUNCTIONID,
        @CONTACTSALUTATIONOPTIONCODE,
        @CUSTOMNAME,
        @GROUPADDRESSEES,
        @GROUPADDRESSEEFUNCTIONID,
        @GROUPSALUTATIONCODE,
        @GROUPSALUTATIONS,
        @GROUPSALUTATIONFUNCTIONID,
        @GROUPNOCONTACTOPTIONCODE,
        @GROUPNOCONTACTCUSTOMNAME,
        @JOINTRULETYPECODE,
        @JOINTSELECTIONID,
        @JOINTSELECTIONBOTHRULETYPECODE,
        @JOINTSELECTIONNEITHERRULETYPECODE,
        @EXCLUDESPOUSE,
    @CONSTITUENTID
    ) NF
    inner join dbo.CONSTITUENT on NF.CONSTITUENTID = CONSTITUENT.ID
    left outer join dbo.ADDRESS A on A.ID = NF.ADDRESSID
    left outer join dbo.COUNTRY on COUNTRY.ID = A.COUNTRYID
    left outer join dbo.STATE on STATE.ID = A.STATEID
    where NF.CONSTITUENTID = @CONSTITUENTID
)