UFN_CONTACTPREFERENCES_GETFORMATS_4_WORKER

Returns constituents with address and name format processing rules applied.

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

Definition

Copy



CREATE function dbo.[UFN_CONTACTPREFERENCES_GETFORMATS_4_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
)
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_BULK(@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 
  inner 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
);