UFN_CONTACTPREFERENCES_GETFORMATS_5_WORKER

Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@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
@CONSTITUENTID	uniqueidentifier	IN
Definition

Copy


--New overload function that takes CONSTITUENTID as parameter

--This calls UFN_ADDRESSPROCESS_ADDRESSES_CONSTITUENT instead of UFN_ADDRESSPROCESS_ADDRESSES_CONSTITUENT_BULK

CREATE function dbo.[UFN_CONTACTPREFERENCES_GETFORMATS_5_WORKER]
(
    @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,
  @CONSTITUENTID uniqueidentifier
)
returns table
as
return(
    select
        ADDRESSPROCESS.CONSTITUENTID CONSTITUENTID,

        case when CONSTITUENT.ISORGANIZATION = 1 or CONSTITUENT.ISGROUP = 1 then
            CONSTITUENT.NAME
        else
            case when len(INDADDRESSEE.CUSTOMNAME) > 0 then
                INDADDRESSEE.CUSTOMNAME
            else
                dbo.UFN_NAMEFORMAT_FROMID_INTERNAL(
                    isnull(INDADDRESSEE.NAMEFORMATFUNCTIONID, @ADDRESSEEFUNCTIONID),
                    case when dbo.UFN_NAMEFORMATFUNCTION_ISJOINT(isnull(INDADDRESSEE.NAMEFORMATFUNCTIONID, @ADDRESSEEFUNCTIONID)) = 1 and @EXCLUDESPOUSE = 0 and dbo.[UFN_NAMEFORMATFUNCTION_ISJOINTSPOUSEFIELDFIRST](isnull(INDADDRESSEE.NAMEFORMATFUNCTIONID, @ADDRESSEEFUNCTIONID)) = 0 then
                        dbo.UFN_MAILING_GETNAMEFORMATS_JOINTPROCESSOR(CONSTITUENT.ID, SPOUSECONSTIT.ID, CONSTITUENT.GENDERCODE, SPOUSECONSTIT.GENDERCODE,@JOINTRULETYPECODE, @JOINTSELECTIONID, @JOINTSELECTIONBOTHRULETYPECODE, @JOINTSELECTIONNEITHERRULETYPECODE, CONSTITUENT.GENDERCODEID, SPOUSECONSTIT.GENDERCODEID)
                    else
                        CONSTITUENT.ID 
                    end,
                    @EXCLUDESPOUSE
                )
            end
        end ADDRESSEE,

        case when CONSTITUENT.ISORGANIZATION = 1 then
            case @ORGSALUTATIONCODE
            when 1 then
                case when ADDRESSPROCESS.CONTACTID is null then
                    case when @CONTACTSALUTATIONOPTIONCODE = 1 then
                        @CUSTOMNAME
                    else
                        CONSTITUENT.NAME
                    end
                else
                    case when len(CONTACTSALUTATION.CUSTOMNAME) > 0 then
                        CONTACTSALUTATION.CUSTOMNAME
                    else
                        dbo.UFN_NAMEFORMAT_FROMID_INTERNAL(
                            isnull(CONTACTSALUTATION.NAMEFORMATFUNCTIONID, @CONTACTSALUTATIONFUNCTIONID),
                            case when dbo.UFN_NAMEFORMATFUNCTION_ISJOINT(isnull(CONTACTSALUTATION.NAMEFORMATFUNCTIONID, @CONTACTSALUTATIONFUNCTIONID)) = 1 and @EXCLUDESPOUSE = 0 and dbo.[UFN_NAMEFORMATFUNCTION_ISJOINTSPOUSEFIELDFIRST](isnull(CONTACTSALUTATION.NAMEFORMATFUNCTIONID, @CONTACTSALUTATIONFUNCTIONID)) = 0 then
                                dbo.UFN_MAILING_GETNAMEFORMATS_JOINTPROCESSOR(ADDRESSPROCESS.CONTACTID, SPOUSECONTACT.ID, CONSTITUENT.GENDERCODE, SPOUSECONTACT.GENDERCODE, @JOINTRULETYPECODE, @JOINTSELECTIONID, @JOINTSELECTIONBOTHRULETYPECODE, @JOINTSELECTIONNEITHERRULETYPECODE, CONSTITUENT.GENDERCODEID, SPOUSECONTACT.GENDERCODEID)
                            else
                                ADDRESSPROCESS.CONTACTID
                            end,
                            @EXCLUDESPOUSE
                        )
                    end
                end
            when 2 then
                @CUSTOMNAME
            else
                CONSTITUENT.NAME
            end
        when CONSTITUENT.ISGROUP = 1 then
            case @GROUPSALUTATIONCODE
            when 1 then
                case when GROUPMEMBER.ID is null then
                    case when @GROUPNOCONTACTOPTIONCODE = 1 then
                        @GROUPNOCONTACTCUSTOMNAME
                    else
                        CONSTITUENT.NAME
                    end
                else
                    case when LEN(MEMBERSALUTATION.CUSTOMNAME) > 0 then
                        MEMBERSALUTATION.CUSTOMNAME
                    else
                        dbo.UFN_NAMEFORMAT_FROMID_INTERNAL(
                            isnull(MEMBERSALUTATION.NAMEFORMATFUNCTIONID, @GROUPSALUTATIONFUNCTIONID),
                            case when dbo.UFN_NAMEFORMATFUNCTION_ISJOINT(isnull(MEMBERSALUTATION.NAMEFORMATFUNCTIONID, @GROUPSALUTATIONFUNCTIONID)) = 1 and @EXCLUDESPOUSE = 0 and dbo.[UFN_NAMEFORMATFUNCTION_ISJOINTSPOUSEFIELDFIRST](isnull(MEMBERSALUTATION.NAMEFORMATFUNCTIONID, @GROUPSALUTATIONFUNCTIONID)) = 0 then
                                dbo.UFN_MAILING_GETNAMEFORMATS_JOINTPROCESSOR(GROUPMEMBER.MEMBERID, SPOUSEMEMBER.ID, CONSTITUENT.GENDERCODE, SPOUSEMEMBER.GENDERCODE, @JOINTRULETYPECODE, @JOINTSELECTIONID, @JOINTSELECTIONBOTHRULETYPECODE, @JOINTSELECTIONNEITHERRULETYPECODE, CONSTITUENT.GENDERCODEID, SPOUSEMEMBER.GENDERCODEID)
                            else
                                GROUPMEMBER.MEMBERID
                            end,
                            @EXCLUDESPOUSE
                        )
                    end
                end
            when 2 then
                @GROUPNOCONTACTCUSTOMNAME
            else
                CONSTITUENT.NAME
            end
        else
            case when len(INDSALUTATION.CUSTOMNAME) > 0 then
                INDSALUTATION.CUSTOMNAME
            else
                dbo.UFN_NAMEFORMAT_FROMID_INTERNAL(
                    isnull(INDSALUTATION.NAMEFORMATFUNCTIONID, @SALUTATIONFUNCTIONID),
                    case when dbo.UFN_NAMEFORMATFUNCTION_ISJOINT(isnull(INDSALUTATION.NAMEFORMATFUNCTIONID, @SALUTATIONFUNCTIONID)) = 1 and @EXCLUDESPOUSE = 0 and dbo.[UFN_NAMEFORMATFUNCTION_ISJOINTSPOUSEFIELDFIRST](isnull(INDSALUTATION.NAMEFORMATFUNCTIONID, @SALUTATIONFUNCTIONID)) = 0 then
                        dbo.UFN_MAILING_GETNAMEFORMATS_JOINTPROCESSOR(CONSTITUENT.ID, SPOUSECONSTIT.ID, CONSTITUENT.GENDERCODE, SPOUSECONSTIT.GENDERCODE, @JOINTRULETYPECODE, @JOINTSELECTIONID, @JOINTSELECTIONBOTHRULETYPECODE, @JOINTSELECTIONNEITHERRULETYPECODE, CONSTITUENT.GENDERCODEID, SPOUSECONSTIT.GENDERCODEID)
                    else
                        CONSTITUENT.ID 
                    end,
                    @EXCLUDESPOUSE
                )
            end
        end SALUTATION,

        case when CONSTITUENT.ISORGANIZATION = 1 then
            case when ADDRESSPROCESS.CONTACTID is null then --No contact found

                null
            else
                case when len(CONTACTADDRESSEE.CUSTOMNAME) > 0 then
                    CONTACTADDRESSEE.CUSTOMNAME
                else
                    dbo.UFN_NAMEFORMAT_FROMID_INTERNAL(
                        isnull(CONTACTADDRESSEE.NAMEFORMATFUNCTIONID, @CONTACTADDRESSEEFUNCTIONID),
                        case when dbo.UFN_NAMEFORMATFUNCTION_ISJOINT(isnull(CONTACTADDRESSEE.NAMEFORMATFUNCTIONID, @CONTACTADDRESSEEFUNCTIONID)) = 1 and @EXCLUDESPOUSE = 0 and dbo.[UFN_NAMEFORMATFUNCTION_ISJOINTSPOUSEFIELDFIRST](isnull(CONTACTADDRESSEE.NAMEFORMATFUNCTIONID, @CONTACTADDRESSEEFUNCTIONID)) = 0 then
                            dbo.UFN_MAILING_GETNAMEFORMATS_JOINTPROCESSOR(ADDRESSPROCESS.CONTACTID, SPOUSECONTACT.ID, CONSTITUENT.GENDERCODE, SPOUSECONTACT.GENDERCODE, @JOINTRULETYPECODE, @JOINTSELECTIONID, @JOINTSELECTIONBOTHRULETYPECODE, @JOINTSELECTIONNEITHERRULETYPECODE, CONSTITUENT.GENDERCODEID, SPOUSECONTACT.GENDERCODEID)
                        else
                            ADDRESSPROCESS.CONTACTID
                        end,
                        @EXCLUDESPOUSE
                    )
                end
            end
        when CONSTITUENT.ISGROUP = 1 then
            case when GROUPMEMBER.ID is null then --No group members found

                null
            else
                case when len(MEMBERADDRESSEE.CUSTOMNAME) > 0 then
                    MEMBERADDRESSEE.CUSTOMNAME
                else
                    dbo.UFN_NAMEFORMAT_FROMID_INTERNAL(
                        isnull(MEMBERADDRESSEE.NAMEFORMATFUNCTIONID, @GROUPADDRESSEEFUNCTIONID),
                        case when dbo.UFN_NAMEFORMATFUNCTION_ISJOINT(isnull(MEMBERADDRESSEE.NAMEFORMATFUNCTIONID, @GROUPADDRESSEEFUNCTIONID)) = 1 and @EXCLUDESPOUSE = 0 and dbo.[UFN_NAMEFORMATFUNCTION_ISJOINTSPOUSEFIELDFIRST](isnull(MEMBERADDRESSEE.NAMEFORMATFUNCTIONID, @GROUPADDRESSEEFUNCTIONID)) = 0 then
                            dbo.UFN_MAILING_GETNAMEFORMATS_JOINTPROCESSOR(GROUPMEMBER.MEMBERID, SPOUSEMEMBER.ID, CONSTITUENT.GENDERCODE, SPOUSEMEMBER.GENDERCODE, @JOINTRULETYPECODE, @JOINTSELECTIONID, @JOINTSELECTIONBOTHRULETYPECODE, @JOINTSELECTIONNEITHERRULETYPECODE, CONSTITUENT.GENDERCODEID, SPOUSEMEMBER.GENDERCODEID)

                        else
                            GROUPMEMBER.MEMBERID
                        end,
                        @EXCLUDESPOUSE
                    )
                end
            end
        else
            null
        end CONTACT,

        ADDRESSPROCESS.ADDRESSID,
        ADDRESSPROCESS.HOUSEHOLDID,
        ADDRESSPROCESS.RETURNEDASHOUSEHOLDMEMBER,
        ADDRESSPROCESS.GROUPCONTACTID,
        ADDRESSPROCESS.POSITION,
        coalesce(ADDRESSPROCESS.CONTACTID, GROUPMEMBER.MEMBERID, CONSTITUENT.ID) MAILTOCONSTITUENTID,
        ADDRESSPROCESS.CONTACTID,

        case when CONSTITUENT.ISORGANIZATION = 1 then
            SPOUSECONTACT.ID
        when CONSTITUENT.ISGROUP = 1 then
            SPOUSEMEMBER.ID
        else
            INDFORMATS.SPOUSEID
        end as SPOUSEID,

        CONSTITUENT.ISORGANIZATION,
        CONSTITUENT.ISGROUP

    from  dbo.UFN_ADDRESSPROCESS_ADDRESSES_CONSTITUENT(@CONSTITUENTID,@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) as ADDRESSPROCESS 
    left outer join dbo.CONSTITUENT on CONSTITUENT.ID = isnull(ADDRESSPROCESS.GROUPCONTACTID, ADDRESSPROCESS.CONSTITUENTID)
    left outer join dbo.GROUPMEMBER on CONSTITUENT.ID = GROUPMEMBER.GROUPID and GROUPMEMBER.ISPRIMARY = 1

    cross apply dbo.UFN_MAILING_GETNAMEFORMATS(CONSTITUENT.ID, @INDIVIDUALADDRESSEES, @INDIVIDUALSALUTATIONS) INDFORMATS
    outer apply dbo.UFN_MAILING_GETCONTACTNAMEFORMATS(ADDRESSPROCESS.CONTACTID, @ORGADDRESSEES, @ORGSALUTATIONS) CONTACTFORMATS
    outer apply dbo.UFN_MAILING_GETMEMBERNAMEFORMATS(GROUPMEMBER.MEMBERID, @GROUPADDRESSEES, @GROUPSALUTATIONS) MEMBERFORMATS

    left outer join dbo.CONSTITUENT SPOUSECONSTIT on INDFORMATS.SPOUSEID = SPOUSECONSTIT.ID

    left outer join dbo.RELATIONSHIP RELATIONSHIPCONTACT on ADDRESSPROCESS.CONTACTID = RELATIONSHIPCONTACT.RELATIONSHIPCONSTITUENTID and RELATIONSHIPCONTACT.ISSPOUSE = 1 and CONSTITUENT.ISORGANIZATION = 1
    left outer join dbo.CONSTITUENT SPOUSECONTACT on RELATIONSHIPCONTACT.RECIPROCALCONSTITUENTID = SPOUSECONTACT.ID

    left outer join dbo.RELATIONSHIP RELATIONSHIPMEMBER on GROUPMEMBER.MEMBERID = RELATIONSHIPMEMBER.RELATIONSHIPCONSTITUENTID and RELATIONSHIPMEMBER.ISSPOUSE = 1 and CONSTITUENT.ISGROUP = 1
    left outer join dbo.CONSTITUENT SPOUSEMEMBER on RELATIONSHIPMEMBER.RECIPROCALCONSTITUENTID = SPOUSEMEMBER.ID

    left outer join dbo.NAMEFORMAT INDADDRESSEE on INDADDRESSEE.ID = INDFORMATS.ADDRESSEE_NAMEFORMATID
    left outer join dbo.NAMEFORMAT INDSALUTATION on INDSALUTATION.ID = INDFORMATS.SALUTATION_NAMEFORMATID
    left outer join dbo.NAMEFORMAT CONTACTADDRESSEE on CONTACTADDRESSEE.ID = CONTACTFORMATS.ADDRESSEE_NAMEFORMATID
    left outer join dbo.NAMEFORMAT CONTACTSALUTATION on CONTACTSALUTATION.ID = CONTACTFORMATS.SALUTATION_NAMEFORMATID
    left outer join dbo.NAMEFORMAT MEMBERADDRESSEE on MEMBERADDRESSEE.ID = MEMBERFORMATS.ADDRESSEE_NAMEFORMATID
    left outer join dbo.NAMEFORMAT MEMBERSALUTATION on MEMBERSALUTATION.ID = MEMBERFORMATS.SALUTATION_NAMEFORMATID
);