UFN_CONTACTPREFERENCES_GETFORMATS_CONSTITUENT_WORKER

CREATE function dbo.UFN_CONTACTPREFERENCES_GETFORMATS_CONSTITUENT_WORKER
(
  @CONSTITUENTID uniqueidentifier,
  @NAMEFORMATPARAMETERID uniqueidentifier,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier,
  @MAILTYPE tinyint,
  @PARAMETERSETID uniqueidentifier,
  @SEASONALDATE datetime,
  @INCLUDEHOUSEHOLDPROCESSING bit,
  @USEADDRESSEEFORMAT bit,
  @ADDRESSEEFORMATID uniqueidentifier,
  @ADDRESSEEFORMATISPRIMARY bit,
  @ALTADDRESSEEFORMATID uniqueidentifier,
  @ALTADDRESSEEFORMATISPRIMARY bit,
  @ADDRESSEEFUNCTIONID uniqueidentifier,
  @USESALUTATIONFORMAT bit,
  @SALUTATIONFORMATID uniqueidentifier,
  @SALUTATIONFORMATISPRIMARY bit,
  @ALTSALUTATIONFORMATID uniqueidentifier,
  @ALTSALUTATIONFORMATISPRIMARY bit,
  @SALUTATIONFUNCTIONID uniqueidentifier,
  @USECONTACTADDRESSEEFORMAT bit,
  @CONTACTADDRESSEEFORMATID uniqueidentifier,
  @CONTACTADDRESSEEFORMATISPRIMARY bit,
  @ALTCONTACTADDRESSEEFORMATID uniqueidentifier,
  @ALTCONTACTADDRESSEEFORMATISPRIMARY bit,
  @CONTACTADDRESSEEFUNCTIONID uniqueidentifier,
  @ORGSALUTATIONCODE tinyint,
  @USECONTACTSALUTATIONFORMAT bit,
  @CONTACTSALUTATIONFORMATID uniqueidentifier,
  @CONTACTSALUTATIONFORMATISPRIMARY bit,
  @ALTCONTACTSALUTATIONFORMATID uniqueidentifier,
  @ALTCONTACTSALUTATIONFORMATISPRIMARY bit,
  @CONTACTSALUTATIONFUNCTIONID uniqueidentifier,
  @CONTACTSALUTATIONOPTIONCODE tinyint,
  @CUSTOMNAME nvarchar(100),
  @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 tinyint
)
returns @R table
(
  CONSTITUENTID uniqueidentifier,
  ADDRESSEE nvarchar(700),
  SALUTATION nvarchar(700),
  CONTACT nvarchar(700),
  POSITION nvarchar(50),
  ADDRESSBLOCK nvarchar(150),
  CITY nvarchar(50),
  STATE nvarchar(100),
  POSTCODE nvarchar(12),
  COUNTRY nvarchar(100),
  CONTACTID uniqueidentifier,
  HOUSEHOLDID uniqueidentifier,
  RETURNEDASHOUSEHOLDMEMBER bit,
  GROUPCONTACTID uniqueidentifier,
  CONTACTORCONSTITUENTID uniqueidentifier
)
as
begin

  --CAMERONBO WI 44493 If the letter comes in with its own name format parameters, use those instead of the default ones. 

  if @NAMEFORMATPARAMETERID is not null
    with NAMEFORMAT_CTE as
    (
      select 
        case when ADDRESSEEFUNCTIONID is null then 0 else 1 end USEADDRESSEEFORMAT,
        ADDRESSEE.NAMEFORMATTYPECODEID ADDRESSEEFORMATID,
        ADDRESSEE.ADDRESSEEFORMATISPRIMARY ADDRESSEEFORMATISPRIMARY,
        ALTADDRESSEE.NAMEFORMATTYPECODEID ALTADDRESSEEFORMATID,
        ALTADDRESSEE.ADDRESSEEFORMATISPRIMARY ALTADDRESSEEFORMATISPRIMARY,
        ADDRESSEEFUNCTIONID,
        case when SALUTATIONFUNCTIONID is null then 0 else 1 end USESALUTATIONFORMAT,
        SALUTATION.NAMEFORMATTYPECODEID SALUTATIONFORMATID,
        SALUTATION.SALUTATIONFORMATISPRIMARY SALUTATIONFORMATISPRIMARY,
        ALTSALUTATION.NAMEFORMATTYPECODEID ALTSALUTATIONFORMATID,
        ALTSALUTATION.SALUTATIONFORMATISPRIMARY ALTSALUTATIONFORMATISPRIMARY,
        SALUTATIONFUNCTIONID,
        case when CONTACTADDRESSEEFUNCTIONID is null then 0 else 1 end USECONTACTADDRESSEEFORMAT,
        ORGADDRESSEE.NAMEFORMATTYPECODEID CONTACTADDRESSEEFORMATID,
        ORGADDRESSEE.ADDRESSEEFORMATISPRIMARY CONTACTADDRESSEEFORMATISPRIMARY,
        ALTORGADDRESSEE.NAMEFORMATTYPECODEID ALTCONTACTADDRESSEEFORMATID,
        ALTORGADDRESSEE.ADDRESSEEFORMATISPRIMARY ALTCONTACTADDRESSEEFORMATISPRIMARY,
        CONTACTADDRESSEEFUNCTIONID,
        ORGSALUTATIONCODE as ORGSALUTATIONCODE,
        case when CONTACTSALUTATIONFUNCTIONID is null then 0 else 1 end USECONTACTSALUTATIONFORMAT,
        ORGSALUTATION.NAMEFORMATTYPECODEID CONTACTSALUTATIONFORMATID,
        ORGSALUTATION.SALUTATIONFORMATISPRIMARY CONTACTSALUTATIONFORMATISPRIMARY,
        ALTORGSALUTATION.NAMEFORMATTYPECODEID ALTCONTACTSALUTATIONFORMATID,
        ALTORGSALUTATION.SALUTATIONFORMATISPRIMARY ALTCONTACTSALUTATIONFORMATISPRIMARY,
        CONTACTSALUTATIONFUNCTIONID,
        CONTACTSALUTATIONOPTIONCODE,
        CUSTOMNAME
      from dbo.NAMEFORMATPARAMETER
      outer apply (select top 1 ID, NAMEFORMATTYPECODEID, ADDRESSEEFORMATISPRIMARY
                from dbo.NAMEFORMATPARAMETERINDADDRESSEE 
                where NAMEFORMATPARAMETERID = NAMEFORMATPARAMETER.ID 
                order by SEQUENCE) ADDRESSEE
      outer apply (select top 1 ID, NAMEFORMATTYPECODEID, ADDRESSEEFORMATISPRIMARY 
                from dbo.NAMEFORMATPARAMETERINDADDRESSEE 
                where NAMEFORMATPARAMETERID = NAMEFORMATPARAMETER.ID 
                  and NAMEFORMATPARAMETERINDADDRESSEE.ID <> ADDRESSEE.ID
                order by SEQUENCE) ALTADDRESSEE
      outer apply (select top 1 ID, NAMEFORMATTYPECODEID, SALUTATIONFORMATISPRIMARY
                from dbo.NAMEFORMATPARAMETERINDSALUTATION 
                where NAMEFORMATPARAMETERID = NAMEFORMATPARAMETER.ID 
                order by SEQUENCE) SALUTATION
      outer apply (select top 1 ID, NAMEFORMATTYPECODEID, SALUTATIONFORMATISPRIMARY 
                from dbo.NAMEFORMATPARAMETERINDSALUTATION 
                where NAMEFORMATPARAMETERID = NAMEFORMATPARAMETER.ID 
                  and NAMEFORMATPARAMETERINDSALUTATION.ID <> SALUTATION.ID
                order by SEQUENCE) ALTSALUTATION
      outer apply (select top 1 ID, NAMEFORMATTYPECODEID, ADDRESSEEFORMATISPRIMARY
                from dbo.NAMEFORMATPARAMETERORGADDRESSEE 
                where NAMEFORMATPARAMETERID = NAMEFORMATPARAMETER.ID 
                order by SEQUENCE) ORGADDRESSEE
      outer apply (select top 1 ID, NAMEFORMATTYPECODEID, ADDRESSEEFORMATISPRIMARY 
                from dbo.NAMEFORMATPARAMETERORGADDRESSEE 
                where NAMEFORMATPARAMETERID = NAMEFORMATPARAMETER.ID 
                  and NAMEFORMATPARAMETERORGADDRESSEE.ID <> ORGADDRESSEE.ID
                order by SEQUENCE) ALTORGADDRESSEE
      outer apply (select top 1 ID, NAMEFORMATTYPECODEID, SALUTATIONFORMATISPRIMARY
                from dbo.NAMEFORMATPARAMETERORGSALUTATION 
                where NAMEFORMATPARAMETERID = NAMEFORMATPARAMETER.ID 
                order by SEQUENCE) ORGSALUTATION
      outer apply (select top 1 ID, NAMEFORMATTYPECODEID, SALUTATIONFORMATISPRIMARY 
                from dbo.NAMEFORMATPARAMETERORGSALUTATION 
                where NAMEFORMATPARAMETERID = NAMEFORMATPARAMETER.ID 
                  and NAMEFORMATPARAMETERORGSALUTATION.ID <> ORGSALUTATION.ID
                order by SEQUENCE) ALTORGSALUTATION
      where NAMEFORMATPARAMETER.ID = @NAMEFORMATPARAMETERID
    )
    select
      @USEADDRESSEEFORMAT = coalesce(USEADDRESSEEFORMAT, @USEADDRESSEEFORMAT),
      @ADDRESSEEFORMATID = coalesce(ADDRESSEEFORMATID, @ADDRESSEEFORMATID),
      @ADDRESSEEFORMATISPRIMARY = coalesce(ADDRESSEEFORMATISPRIMARY, @ADDRESSEEFORMATISPRIMARY),
      @ALTADDRESSEEFORMATID = coalesce(ALTADDRESSEEFORMATID, @ALTADDRESSEEFORMATID),
      @ALTADDRESSEEFORMATISPRIMARY = coalesce(ALTADDRESSEEFORMATISPRIMARY, @ALTADDRESSEEFORMATISPRIMARY),
      @ADDRESSEEFUNCTIONID = coalesce(ADDRESSEEFUNCTIONID, @ADDRESSEEFUNCTIONID),
      @USESALUTATIONFORMAT = coalesce(USESALUTATIONFORMAT, @USESALUTATIONFORMAT),
      @SALUTATIONFORMATID = coalesce(SALUTATIONFORMATID, @SALUTATIONFORMATID),
      @SALUTATIONFORMATISPRIMARY = coalesce(SALUTATIONFORMATISPRIMARY, @SALUTATIONFORMATISPRIMARY),
      @ALTSALUTATIONFORMATID = coalesce(ALTSALUTATIONFORMATID, @ALTSALUTATIONFORMATID),
      @ALTSALUTATIONFORMATISPRIMARY = coalesce(ALTSALUTATIONFORMATISPRIMARY, @ALTSALUTATIONFORMATISPRIMARY),
      @SALUTATIONFUNCTIONID = coalesce(SALUTATIONFUNCTIONID, @SALUTATIONFUNCTIONID),
      @USECONTACTADDRESSEEFORMAT = coalesce(USECONTACTADDRESSEEFORMAT, @USECONTACTADDRESSEEFORMAT),
      @CONTACTADDRESSEEFORMATID = coalesce(CONTACTADDRESSEEFORMATID, @CONTACTADDRESSEEFORMATID),
      @CONTACTADDRESSEEFORMATISPRIMARY = coalesce(CONTACTADDRESSEEFORMATISPRIMARY, @CONTACTADDRESSEEFORMATISPRIMARY),
      @ALTCONTACTADDRESSEEFORMATID = coalesce(ALTCONTACTADDRESSEEFORMATID, @ALTCONTACTADDRESSEEFORMATID),
      @ALTCONTACTADDRESSEEFORMATISPRIMARY = coalesce(ALTCONTACTADDRESSEEFORMATISPRIMARY, @ALTCONTACTADDRESSEEFORMATISPRIMARY),
      @CONTACTADDRESSEEFUNCTIONID = coalesce(CONTACTADDRESSEEFUNCTIONID, @CONTACTADDRESSEEFUNCTIONID),
      @ORGSALUTATIONCODE = coalesce(ORGSALUTATIONCODE, @ORGSALUTATIONCODE),
      @USECONTACTSALUTATIONFORMAT = coalesce(USECONTACTSALUTATIONFORMAT, @USECONTACTSALUTATIONFORMAT),
      @CONTACTSALUTATIONFORMATID = coalesce(CONTACTSALUTATIONFORMATID, @CONTACTSALUTATIONFORMATID),
      @CONTACTSALUTATIONFORMATISPRIMARY = coalesce(CONTACTSALUTATIONFORMATISPRIMARY, @CONTACTSALUTATIONFORMATISPRIMARY),
      @ALTCONTACTSALUTATIONFORMATID = coalesce(ALTCONTACTSALUTATIONFORMATID, @ALTCONTACTSALUTATIONFORMATID),
      @ALTCONTACTSALUTATIONFORMATISPRIMARY = coalesce(ALTCONTACTSALUTATIONFORMATISPRIMARY, @ALTCONTACTSALUTATIONFORMATISPRIMARY),
      @CONTACTSALUTATIONFUNCTIONID = coalesce(CONTACTSALUTATIONFUNCTIONID, @CONTACTSALUTATIONFUNCTIONID),
      @CONTACTSALUTATIONOPTIONCODE = coalesce(CONTACTSALUTATIONOPTIONCODE, @CONTACTSALUTATIONOPTIONCODE),
      @CUSTOMNAME = coalesce(CUSTOMNAME, @CUSTOMNAME)
    from NAMEFORMAT_CTE;

  --JamesWill WI 40150 If the letter comes in with its own set of comm prefs, use those instead of the default ones. 

  if @ADDRESSPROCESSINGOPTIONID is not null
    select
      @INDUSECONSTITUENTPREFS = coalesce(INDUSECONSTITUENTPREFS, @INDUSECONSTITUENTPREFS),
      @INDALTADDRESS1TYPECODEID = coalesce(INDALTADDRESS1TYPECODEID, @INDALTADDRESS1TYPECODEID),
      @INDALTADDRESS1ISPRIMARY = coalesce(INDALTADDRESS1ISPRIMARY, @INDALTADDRESS1ISPRIMARY),
      @INDALTADDRESS2TYPECODEID = coalesce(INDALTADDRESS2TYPECODEID, @INDALTADDRESS2TYPECODEID),
      @INDALTADDRESS2ISPRIMARY = coalesce(INDALTADDRESS2ISPRIMARY, @INDALTADDRESS2ISPRIMARY),
      @INDUSESEASONALADDRESS = coalesce(INDUSESEASONALADDRESS, @INDUSESEASONALADDRESS),
      @INDINCLUDEWITHNOADDRESS = coalesce(INDINCLUDEWITHNOADDRESS, @INDINCLUDEWITHNOADDRESS),
      @ORGMAILINGPREFERENCE = coalesce(ORGMAILINGPREFERENCE, @ORGMAILINGPREFERENCE),
      @ORGALTADDRESS1TYPECODEID = coalesce(ORGALTADDRESS1TYPECODEID, @ORGALTADDRESS1TYPECODEID),
      @ORGALTADDRESS1ISPRIMARY = coalesce(ORGALTADDRESS1ISPRIMARY, @ORGALTADDRESS1ISPRIMARY),
      @ORGALTADDRESS2TYPECODEID = coalesce(ORGALTADDRESS2TYPECODEID, @ORGALTADDRESS2TYPECODEID),
      @ORGALTADDRESS2ISPRIMARY = coalesce(ORGALTADDRESS2ISPRIMARY, @ORGALTADDRESS2ISPRIMARY),
      @ORGINCLUDEWITHNOADDRESS= coalesce(ORGINCLUDEWITHNOADDRESS, @ORGINCLUDEWITHNOADDRESS),
      @ORGSENDTOALLCONTACTS = coalesce(ORGSENDTOALLCONTACTS, @ORGSENDTOALLCONTACTS),
      @ORGINCLUDEWITHNOCONTACT = coalesce(ORGINCLUDEWITHNOCONTACT, @ORGINCLUDEWITHNOCONTACT)
    from dbo.ADDRESSPROCESSINGOPTION
    where ID = @ADDRESSPROCESSINGOPTIONID;

   insert into @R(CONSTITUENTID, ADDRESSEE, SALUTATION, CONTACT, POSITION, ADDRESSBLOCK, CITY, STATE, POSTCODE, COUNTRY, CONTACTID, HOUSEHOLDID, RETURNEDASHOUSEHOLDMEMBER, GROUPCONTACTID, CONTACTORCONSTITUENTID)
    select 
      CONSTITUENT.ID as CONSTITUENTID,
      case when CONSTITUENT.ISORGANIZATION = 1 then
        CONSTITUENT.NAME
      else
 case when @USEADDRESSEEFORMAT = 0 then
          dbo.UFN_NAMEFORMAT_FROMID(@ADDRESSEEFUNCTIONID, coalesce(AP.GROUPCONTACTID, CONSTITUENT.ID))
        else
          case when AP.GROUPCONTACTID is not null then
            coalesce((select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = AP.GROUPCONTACTID
                  and (NAMEFORMAT.NAMEFORMATTYPECODEID = @ADDRESSEEFORMATID
                  or (@ADDRESSEEFORMATISPRIMARY = 1 and NAMEFORMAT.PRIMARYADDRESSEE = 1))),
                (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = AP.GROUPCONTACTID
                  and (NAMEFORMAT.NAMEFORMATTYPECODEID = @ALTADDRESSEEFORMATID
                  or (@ALTADDRESSEEFORMATISPRIMARY = 1 and NAMEFORMAT.PRIMARYADDRESSEE = 1))),
                (select NAME from dbo.CONSTITUENT where ID = AP.GROUPCONTACTID))
          else
            coalesce((select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID
                  and (NAMEFORMAT.NAMEFORMATTYPECODEID = @ADDRESSEEFORMATID
                  or (@ADDRESSEEFORMATISPRIMARY = 1 and NAMEFORMAT.PRIMARYADDRESSEE = 1))),
                (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID
                  and (NAMEFORMAT.NAMEFORMATTYPECODEID = @ALTADDRESSEEFORMATID
                  or (@ALTADDRESSEEFORMATISPRIMARY = 1 and NAMEFORMAT.PRIMARYADDRESSEE = 1))),
                CONSTITUENT.NAME)
          end
        end
      end as ADDRESSEE,
      case when CONSTITUENT.ISORGANIZATION = 1 then
        --process salutation option

        case when @ORGSALUTATIONCODE = 0 then --use organization name

          CONSTITUENT.NAME
        when @ORGSALUTATIONCODE = 1 then -- use contact name

          coalesce((select top (1)
                case when @USECONTACTSALUTATIONFORMAT = 0 then
                  dbo.UFN_NAMEFORMAT_FROMID(@CONTACTSALUTATIONFUNCTIONID, CONTACT.ID)
                else
                  coalesce((select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONTACT.ID
                        and (NAMEFORMAT.NAMEFORMATTYPECODEID = @CONTACTSALUTATIONFORMATID
                        or (@CONTACTSALUTATIONFORMATISPRIMARY = 1 and NAMEFORMAT.PRIMARYSALUTATION = 1))),
                      (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONTACT.ID
                        and (NAMEFORMAT.NAMEFORMATTYPECODEID = @ALTCONTACTSALUTATIONFORMATID
                        or (@ALTCONTACTSALUTATIONFORMATISPRIMARY = 1 and NAMEFORMAT.PRIMARYSALUTATION = 1))),
                      CONTACT.NAME)
                end
              from dbo.CONSTITUENT CONTACT
              where CONTACT.ID = AP.CONTACTID),
              (case when @CUSTOMNAME <> '' then @CUSTOMNAME else null end), 
              CONSTITUENT.NAME)
      else --use custom name

        @CUSTOMNAME
      end
    else
      case when @USESALUTATIONFORMAT = 0 then
        dbo.UFN_NAMEFORMAT_FROMID(@SALUTATIONFUNCTIONID, coalesce(AP.GROUPCONTACTID, CONSTITUENT.ID))
      else
        case when AP.GROUPCONTACTID is not null then
          coalesce((select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = AP.GROUPCONTACTID
                and (NAMEFORMAT.NAMEFORMATTYPECODEID = @SALUTATIONFORMATID
                or (@SALUTATIONFORMATISPRIMARY = 1 and NAMEFORMAT.PRIMARYSALUTATION = 1))),
              (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = AP.GROUPCONTACTID
                and (NAMEFORMAT.NAMEFORMATTYPECODEID = @ALTSALUTATIONFORMATID
                or (@ALTSALUTATIONFORMATISPRIMARY = 1 and NAMEFORMAT.PRIMARYSALUTATION = 1))),
              (select NAME from dbo.CONSTITUENT where ID = AP.GROUPCONTACTID))
        else
          coalesce((select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID
                and (NAMEFORMAT.NAMEFORMATTYPECODEID = @SALUTATIONFORMATID
                or (@SALUTATIONFORMATISPRIMARY = 1 and NAMEFORMAT.PRIMARYSALUTATION = 1))),
              (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID
                and (NAMEFORMAT.NAMEFORMATTYPECODEID = @ALTSALUTATIONFORMATID
                or (@ALTSALUTATIONFORMATISPRIMARY = 1 and NAMEFORMAT.PRIMARYSALUTATION = 1))),
              CONSTITUENT.NAME)
        end
      end
    end as SALUTATION,
    case when CONSTITUENT.ISORGANIZATION = 1 then
      (select top (1)
        case when @USECONTACTADDRESSEEFORMAT = 0 then
          dbo.UFN_NAMEFORMAT_FROMID(@CONTACTADDRESSEEFUNCTIONID, CONTACT.ID)
        else
          coalesce((select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONTACT.ID
                and (NAMEFORMAT.NAMEFORMATTYPECODEID = @CONTACTADDRESSEEFORMATID
                or (@CONTACTADDRESSEEFORMATISPRIMARY = 1 and NAMEFORMAT.PRIMARYADDRESSEE = 1))),
              (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONTACT.ID
                and (NAMEFORMAT.NAMEFORMATTYPECODEID = @ALTCONTACTADDRESSEEFORMATID
                or (@ALTCONTACTADDRESSEEFORMATISPRIMARY = 1 and NAMEFORMAT.PRIMARYADDRESSEE = 1))),
              CONTACT.NAME)
        end
      from dbo.CONSTITUENT CONTACT
      where CONTACT.ID = AP.CONTACTID)
    else
      null
    end as CONTACT,
    AP.POSITION,
    A.ADDRESSBLOCK,
    A.CITY,
    STATE.ABBREVIATION as [STATE],
    A.POSTCODE,
    COUNTRY.DESCRIPTION as COUNTRY,
    AP.CONTACTID,
    AP.HOUSEHOLDID,
    AP.RETURNEDASHOUSEHOLDMEMBER,
    AP.GROUPCONTACTID,
    coalesce(AP.GROUPCONTACTID, CONSTITUENT.ID) as CONTACTORCONSTITUENTID
  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 AP
  inner join CONSTITUENT on AP.CONSTITUENTID = CONSTITUENT.ID
  left join dbo.ADDRESS A on AP.ADDRESSID = A.ID
  left join dbo.STATE on A.STATEID = STATE.ID
  left join dbo.COUNTRY on A.COUNTRYID = COUNTRY.ID;

  return;
end