UFN_CONTACTPREFERENCES_GETFORMATS_2

Returns address and name formats according to contact preference settings.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode
@NAMEFORMATPARAMETERID	uniqueidentifier	IN
@ADDRESSPROCESSINGOPTIONID	uniqueidentifier	IN
@MAILTYPE	tinyint	IN
@PARAMETERSETID	uniqueidentifier	IN
@SEASONALDATE	datetime	IN
@INCLUDEHOUSEHOLDPROCESSING	bit	IN
@USEADDRESSEEFORMAT	bit	IN
@ADDRESSEEFORMATID	uniqueidentifier	IN
@ADDRESSEEFORMATISPRIMARY	bit	IN
@ALTADDRESSEEFORMATID	uniqueidentifier	IN
@ALTADDRESSEEFORMATISPRIMARY	bit	IN
@ADDRESSEEFUNCTIONID	uniqueidentifier	IN
@USESALUTATIONFORMAT	bit	IN
@SALUTATIONFORMATID	uniqueidentifier	IN
@SALUTATIONFORMATISPRIMARY	bit	IN
@ALTSALUTATIONFORMATID	uniqueidentifier	IN
@ALTSALUTATIONFORMATISPRIMARY	bit	IN
@SALUTATIONFUNCTIONID	uniqueidentifier	IN
@USECONTACTADDRESSEEFORMAT	bit	IN
@CONTACTADDRESSEEFORMATID	uniqueidentifier	IN
@CONTACTADDRESSEEFORMATISPRIMARY	bit	IN
@ALTCONTACTADDRESSEEFORMATID	uniqueidentifier	IN
@ALTCONTACTADDRESSEEFORMATISPRIMARY	bit	IN
@CONTACTADDRESSEEFUNCTIONID	uniqueidentifier	IN
@ORGSALUTATIONCODE	tinyint	IN
@USECONTACTSALUTATIONFORMAT	bit	IN
@CONTACTSALUTATIONFORMATID	uniqueidentifier	IN
@CONTACTSALUTATIONFORMATISPRIMARY	bit	IN
@ALTCONTACTSALUTATIONFORMATID	uniqueidentifier	IN
@ALTCONTACTSALUTATIONFORMATISPRIMARY	bit	IN
@CONTACTSALUTATIONFUNCTIONID	uniqueidentifier	IN
@CONTACTSALUTATIONOPTIONCODE	tinyint	IN
@CUSTOMNAME	nvarchar(100)	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	tinyint	IN

Definition

Copy



CREATE function dbo.UFN_CONTACTPREFERENCES_GETFORMATS_2 
   (@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 table  
  as  
  return  
   (  

            /*
                Note this function has been upgraded for backwards compatibility 
                but the new UFN_CONTACTPREFERENCES_GETFORMATS_3
                should be used for new development

            */

   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  
      coalesce((select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = coalesce(AP.GROUPCONTACTID, CONSTITUENT.ID)  
         and (NAMEFORMAT.NAMEFORMATTYPECODEID = @ADDRESSEEFORMATID   
          or (@ADDRESSEEFORMATISPRIMARY = 1 and NAMEFORMAT.PRIMARYADDRESSEE = 1))),  
         (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = coalesce(AP.GROUPCONTACTID, CONSTITUENT.ID)  
             and (NAMEFORMAT.NAMEFORMATTYPECODEID = @ALTADDRESSEEFORMATID   
              or (@ALTADDRESSEEFORMATISPRIMARY = 1 and NAMEFORMAT.PRIMARYADDRESSEE = 1))),  
         coalesce( (select NAME from dbo.CONSTITUENT where ID = AP.GROUPCONTACTID), CONSTITUENT.NAME))  
     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  
      coalesce((select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = coalesce(AP.GROUPCONTACTID, CONSTITUENT.ID)  
         and (NAMEFORMAT.NAMEFORMATTYPECODEID = @SALUTATIONFORMATID  
          or (@SALUTATIONFORMATISPRIMARY = 1 and NAMEFORMAT.PRIMARYSALUTATION = 1))),  
         (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = coalesce(AP.GROUPCONTACTID, CONSTITUENT.ID)  
         and (NAMEFORMAT.NAMEFORMATTYPECODEID = @ALTSALUTATIONFORMATID  
          or (@ALTSALUTATIONFORMATISPRIMARY = 1 and NAMEFORMAT.PRIMARYSALUTATION = 1))),  
         coalesce( (select NAME from dbo.CONSTITUENT where ID = AP.GROUPCONTACTID), CONSTITUENT.NAME))     
     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,
    A.CART,
    A.DPC,
    A.LOT

   from dbo.CONSTITUENT  
   inner join dbo.UFN_ADDRESSPROCESS_ADDRESSES_2(@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 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  

   )