UFN_CONTACTPREFERENCES_GETFORMATS_4

Returns results from UFN_CONTACTPREFERENCES_GETFORMATS_4_WORKER and loads parameters.

Return

Return Type
table

Parameters

Parameter Parameter Type Mode Description
@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 


CREATE function dbo.UFN_CONTACTPREFERENCES_GETFORMATS_4(
  @NAMEFORMATPARAMETERID uniqueidentifier = null,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null,
  @MAILTYPE tinyint = null,
  @PARAMETERSETID uniqueidentifier = null,
  @SEASONALDATE datetime = null,
  @INCLUDEHOUSEHOLDPROCESSING bit = null,
  @INDUSESEASONALADDRESS bit = null,
  @ORGMAILINGPREFERENCE tinyint = null,
  @INDALTADDRESS1TYPECODEID uniqueidentifier = null,
  @INDALTADDRESS1ISPRIMARY bit = null,
  @INDALTADDRESS2TYPECODEID uniqueidentifier = null,
  @INDALTADDRESS2ISPRIMARY bit = null,
  @ORGALTADDRESS1TYPECODEID uniqueidentifier = null,
  @ORGALTADDRESS1ISPRIMARY bit = null,
  @ORGALTADDRESS2TYPECODEID uniqueidentifier = null,
  @ORGALTADDRESS2ISPRIMARY bit = null,
  @INDINCLUDEWITHNOADDRESS bit = null,
  @ORGINCLUDEWITHNOADDRESS bit = null,
  @ORGINCLUDEWITHNOCONTACT bit = null,
  @ORGSENDTOALLCONTACTS bit = null,
  @INDUSECONSTITUENTPREFS bit = null,
  @ORGUSECONSTITUENTPREFS bit = null,
  @GROUPALTADDRESS1TYPECODEID uniqueidentifier = null,
  @GROUPALTADDRESS1ISPRIMARY bit = null,
  @GROUPALTADDRESS2TYPECODEID uniqueidentifier = null,
  @GROUPALTADDRESS2ISPRIMARY bit = null,
  @GROUPINCLUDEWITHNOADDRESS bit = null,
  @GROUPUSECONSTITUENTPREFS bit = null,
  @INDIVIDUALADDRESSEES xml = null,
  @ADDRESSEEFUNCTIONID uniqueidentifier = null,
  @INDIVIDUALSALUTATIONS xml = null,
  @SALUTATIONFUNCTIONID uniqueidentifier = null,
  @ORGADDRESSEES xml = null,
  @CONTACTADDRESSEEFUNCTIONID uniqueidentifier = null,
  @ORGSALUTATIONCODE tinyint = null,
  @ORGSALUTATIONS xml = null,
  @CONTACTSALUTATIONFUNCTIONID uniqueidentifier = null,
  @CONTACTSALUTATIONOPTIONCODE tinyint = null,
  @CUSTOMNAME nvarchar(100) = null,
  @GROUPADDRESSEES xml = null,
  @GROUPADDRESSEEFUNCTIONID uniqueidentifier = null,
  @GROUPSALUTATIONCODE tinyint = null,
  @GROUPSALUTATIONS xml = null,
  @GROUPSALUTATIONFUNCTIONID uniqueidentifier = null,
  @GROUPNOCONTACTOPTIONCODE bit = null,
  @GROUPNOCONTACTCUSTOMNAME nvarchar(100) = null,
  @JOINTRULETYPECODE As tinyint = null,
  @JOINTSELECTIONID As uniqueidentifier = null,
  @JOINTSELECTIONBOTHRULETYPECODE As tinyint = null,
  @JOINTSELECTIONNEITHERRULETYPECODE As tinyint = null,
  @EXCLUDESPOUSE bit = null)  
returns table 
as
return
(
with PARAM_CTE as
(
select top 1
    ADDRESSPROCESSINGOPTION.ID as ADDRESSPROCESSINGOPTIONID, 
    @MAILTYPE as MAILTYPE, 
    @PARAMETERSETID as PARAMETERSETID, 
    @SEASONALDATE as SEASONALDATE, 
    @INCLUDEHOUSEHOLDPROCESSING as INCLUDEHOUSEHOLDPROCESSING, 
    coalesce(@INDUSESEASONALADDRESS, ADDRESSPROCESSINGOPTION.INDUSESEASONALADDRESS) as INDUSESEASONALADDRESS, 
    coalesce(@ORGMAILINGPREFERENCE, ADDRESSPROCESSINGOPTION.ORGMAILINGPREFERENCE) as ORGMAILINGPREFERENCE, 
    coalesce(@INDALTADDRESS1TYPECODEID, ADDRESSPROCESSINGOPTION.INDALTADDRESS1TYPECODEID) as INDALTADDRESS1TYPECODEID, 
    coalesce(@INDALTADDRESS1ISPRIMARY, ADDRESSPROCESSINGOPTION.INDALTADDRESS1ISPRIMARY) as INDALTADDRESS1ISPRIMARY, 
    coalesce(@INDALTADDRESS2TYPECODEID, ADDRESSPROCESSINGOPTION.INDALTADDRESS2TYPECODEID) as INDALTADDRESS2TYPECODEID, 
    coalesce(@INDALTADDRESS2ISPRIMARY, ADDRESSPROCESSINGOPTION.INDALTADDRESS2ISPRIMARY) as INDALTADDRESS2ISPRIMARY, 
    coalesce(@ORGALTADDRESS1TYPECODEID, ADDRESSPROCESSINGOPTION.ORGALTADDRESS1TYPECODEID) as ORGALTADDRESS1TYPECODEID, 
    coalesce(@ORGALTADDRESS1ISPRIMARY, ADDRESSPROCESSINGOPTION.ORGALTADDRESS1ISPRIMARY) as ORGALTADDRESS1ISPRIMARY, 
    coalesce(@ORGALTADDRESS2TYPECODEID, ADDRESSPROCESSINGOPTION.ORGALTADDRESS2TYPECODEID) as ORGALTADDRESS2TYPECODEID, 
    coalesce(@ORGALTADDRESS2ISPRIMARY, ADDRESSPROCESSINGOPTION.ORGALTADDRESS2ISPRIMARY) as ORGALTADDRESS2ISPRIMARY, 
    coalesce(@INDINCLUDEWITHNOADDRESS, ADDRESSPROCESSINGOPTION.INDINCLUDEWITHNOADDRESS) as INDINCLUDEWITHNOADDRESS, 
    coalesce(@ORGINCLUDEWITHNOADDRESS, ADDRESSPROCESSINGOPTION.ORGINCLUDEWITHNOADDRESS) as ORGINCLUDEWITHNOADDRESS, 
    coalesce(@ORGINCLUDEWITHNOCONTACT, ADDRESSPROCESSINGOPTION.ORGINCLUDEWITHNOCONTACT) as ORGINCLUDEWITHNOCONTACT, 
    coalesce(@ORGSENDTOALLCONTACTS, ADDRESSPROCESSINGOPTION.ORGSENDTOALLCONTACTS) as ORGSENDTOALLCONTACTS, 
    coalesce(@INDUSECONSTITUENTPREFS, ADDRESSPROCESSINGOPTION.INDUSECONSTITUENTPREFS) as INDUSECONSTITUENTPREFS, 
    coalesce(@ORGUSECONSTITUENTPREFS, ADDRESSPROCESSINGOPTION.ORGUSECONSTITUENTPREFS) as ORGUSECONSTITUENTPREFS, 
    coalesce(@GROUPALTADDRESS1TYPECODEID, ADDRESSPROCESSINGOPTION.GROUPALTADDRESS1TYPECODEID) as GROUPALTADDRESS1TYPECODEID, 
    coalesce(@GROUPALTADDRESS1ISPRIMARY, ADDRESSPROCESSINGOPTION.GROUPALTADDRESS1ISPRIMARY) as GROUPALTADDRESS1ISPRIMARY, 
    coalesce(@GROUPALTADDRESS2TYPECODEID, ADDRESSPROCESSINGOPTION.GROUPALTADDRESS2TYPECODEID) as GROUPALTADDRESS2TYPECODEID, 
    coalesce(@GROUPALTADDRESS2ISPRIMARY, ADDRESSPROCESSINGOPTION.GROUPALTADDRESS2ISPRIMARY) as GROUPALTADDRESS2ISPRIMARY, 
    coalesce(@GROUPINCLUDEWITHNOADDRESS, ADDRESSPROCESSINGOPTION.GROUPINCLUDEWITHNOADDRESS) as GROUPINCLUDEWITHNOADDRESS, 
    coalesce(@GROUPUSECONSTITUENTPREFS, ADDRESSPROCESSINGOPTION.GROUPUSECONSTITUENTPREFS) as GROUPUSECONSTITUENTPREFS, 
    coalesce(@INDIVIDUALADDRESSEES, dbo.UFN_NAMEFORMATPARAMETER_GETINDADDRESSEES_TOITEMLISTXML(NAMEFORMATPARAMETER.ID)) as INDIVIDUALADDRESSEES, 
    coalesce(@ADDRESSEEFUNCTIONID, NAMEFORMATPARAMETER.ADDRESSEEFUNCTIONID) as ADDRESSEEFUNCTIONID, 
    coalesce(@INDIVIDUALSALUTATIONS, dbo.UFN_NAMEFORMATPARAMETER_GETINDSALUTATIONS_TOITEMLISTXML(NAMEFORMATPARAMETER.ID)) as INDIVIDUALSALUTATIONS, 
    coalesce(@SALUTATIONFUNCTIONID, NAMEFORMATPARAMETER.SALUTATIONFUNCTIONID) as SALUTATIONFUNCTIONID, 
    coalesce(@ORGADDRESSEES, dbo.UFN_NAMEFORMATPARAMETER_GETORGADDRESSEES_TOITEMLISTXML(NAMEFORMATPARAMETER.ID)) as ORGADDRESSEES, 
    coalesce(@CONTACTADDRESSEEFUNCTIONID, NAMEFORMATPARAMETER.CONTACTADDRESSEEFUNCTIONID) as CONTACTADDRESSEEFUNCTIONID, 
    coalesce(@ORGSALUTATIONCODE, NAMEFORMATPARAMETER.ORGSALUTATIONCODE) as ORGSALUTATIONCODE, 
    coalesce(@ORGSALUTATIONS, dbo.UFN_NAMEFORMATPARAMETER_GETORGSALUTATIONS_TOITEMLISTXML(NAMEFORMATPARAMETER.ID)) as ORGSALUTATIONS, 
    coalesce(@CONTACTSALUTATIONFUNCTIONID, NAMEFORMATPARAMETER.CONTACTSALUTATIONFUNCTIONID) as CONTACTSALUTATIONFUNCTIONID, 
    coalesce(@CONTACTSALUTATIONOPTIONCODE, NAMEFORMATPARAMETER.CONTACTSALUTATIONOPTIONCODE) as CONTACTSALUTATIONOPTIONCODE, 
    coalesce(@CUSTOMNAME, NAMEFORMATPARAMETER.CUSTOMNAME) as CUSTOMNAME, 
    coalesce(@GROUPADDRESSEES, dbo.UFN_NAMEFORMATPARAMETER_GETGRPADDRESSEES_TOITEMLISTXML(NAMEFORMATPARAMETER.ID)) as GROUPADDRESSEES, 
    coalesce(@GROUPADDRESSEEFUNCTIONID, GROUPADDRESSEEFUNCTIONID) as GROUPADDRESSEEFUNCTIONID, 
    coalesce(@GROUPSALUTATIONCODE, NAMEFORMATPARAMETER.GROUPSALUTATIONCODE) as GROUPSALUTATIONCODE, 
    coalesce(@GROUPSALUTATIONS, dbo.UFN_NAMEFORMATPARAMETER_GETGRPSALUTATIONS_TOITEMLISTXML(NAMEFORMATPARAMETER.ID)) as GROUPSALUTATIONS, 
    coalesce(@GROUPSALUTATIONFUNCTIONID, GROUPSALUTATIONFUNCTIONID) as GROUPSALUTATIONFUNCTIONID, 
    coalesce(@GROUPNOCONTACTOPTIONCODE, GROUPNOCONTACTOPTIONCODE) as GROUPNOCONTACTOPTIONCODE, 
    coalesce(@GROUPNOCONTACTCUSTOMNAME, GROUPNOCONTACTCUSTOMNAME) as GROUPNOCONTACTCUSTOMNAME, 
    coalesce(@JOINTRULETYPECODE, JOINTRULETYPECODE) as JOINTRULETYPECODE, 
    coalesce(@JOINTSELECTIONID, JOINTSELECTIONID) as JOINTSELECTIONID, 
    coalesce(@JOINTSELECTIONBOTHRULETYPECODE, JOINTSELECTIONBOTHRULETYPECODE) as JOINTSELECTIONBOTHRULETYPECODE, 
    coalesce(@JOINTSELECTIONNEITHERRULETYPECODE, JOINTSELECTIONNEITHERRULETYPECODE) as JOINTSELECTIONNEITHERRULETYPECODE,
    @EXCLUDESPOUSE as EXCLUDESPOUSE
from dbo.ADDRESSPROCESSINGOPTION, dbo.NAMEFORMATPARAMETER
where (ADDRESSPROCESSINGOPTION.ID = @ADDRESSPROCESSINGOPTIONID or (@ADDRESSPROCESSINGOPTIONID is null and ADDRESSPROCESSINGOPTION.ISDEFAULT = 1))
    and 
    (NAMEFORMATPARAMETER.ID = @NAMEFORMATPARAMETERID or (@NAMEFORMATPARAMETERID is null and NAMEFORMATPARAMETER.ISDEFAULT = 1))
)
select NF.CONSTITUENTID,
    NF.ADDRESSEE,
    NF.SALUTATION,
    NF.CONTACT,
    NF.ADDRESSID,
    NF.HOUSEHOLDID,
    NF.RETURNEDASHOUSEHOLDMEMBER,
    NF.GROUPCONTACTID,
    NF.POSITION,
    NF.MAILTOCONSTITUENTID,
    NF.CONTACTID,
    NF.SPOUSEID,
  case when NF.ISORGANIZATION = 1 or NF.ISGROUP = 1 then NF.CONTACT else NF.ADDRESSEE end as CONTACTORADDRESSEE
from PARAM_CTE PARAMS
cross apply dbo.UFN_CONTACTPREFERENCES_GETFORMATS_4_WORKER(
            PARAMS.ADDRESSPROCESSINGOPTIONID,
            PARAMS.MAILTYPE,
            PARAMS.PARAMETERSETID,
            PARAMS.SEASONALDATE,
            PARAMS.INCLUDEHOUSEHOLDPROCESSING ,
            PARAMS.INDUSESEASONALADDRESS ,
            PARAMS.ORGMAILINGPREFERENCE ,
            PARAMS.INDALTADDRESS1TYPECODEID,
            PARAMS.INDALTADDRESS1ISPRIMARY ,
            PARAMS.INDALTADDRESS2TYPECODEID,
            PARAMS.INDALTADDRESS2ISPRIMARY ,
            PARAMS.ORGALTADDRESS1TYPECODEID,
            PARAMS.ORGALTADDRESS1ISPRIMARY ,
            PARAMS.ORGALTADDRESS2TYPECODEID,
            PARAMS.ORGALTADDRESS2ISPRIMARY ,
            PARAMS.INDINCLUDEWITHNOADDRESS ,
            PARAMS.ORGINCLUDEWITHNOADDRESS ,
            PARAMS.ORGINCLUDEWITHNOCONTACT ,
            PARAMS.ORGSENDTOALLCONTACTS ,
            PARAMS.INDUSECONSTITUENTPREFS ,
            PARAMS.ORGUSECONSTITUENTPREFS ,
            PARAMS.GROUPALTADDRESS1TYPECODEID,
            PARAMS.GROUPALTADDRESS1ISPRIMARY ,
            PARAMS.GROUPALTADDRESS2TYPECODEID,
            PARAMS.GROUPALTADDRESS2ISPRIMARY ,
            PARAMS.GROUPINCLUDEWITHNOADDRESS ,
            PARAMS.GROUPUSECONSTITUENTPREFS ,
            PARAMS.INDIVIDUALADDRESSEES ,
            PARAMS.ADDRESSEEFUNCTIONID,
            PARAMS.INDIVIDUALSALUTATIONS ,
            PARAMS.SALUTATIONFUNCTIONID,
            PARAMS.ORGADDRESSEES ,
            PARAMS.CONTACTADDRESSEEFUNCTIONID,
            PARAMS.ORGSALUTATIONCODE,
            PARAMS.ORGSALUTATIONS ,
            PARAMS.CONTACTSALUTATIONFUNCTIONID,
            PARAMS.CONTACTSALUTATIONOPTIONCODE ,
            PARAMS.CUSTOMNAME ,
            PARAMS.GROUPADDRESSEES ,
            PARAMS.GROUPADDRESSEEFUNCTIONID,
            PARAMS.GROUPSALUTATIONCODE,
            PARAMS.GROUPSALUTATIONS ,
            PARAMS.GROUPSALUTATIONFUNCTIONID,
            PARAMS.GROUPNOCONTACTOPTIONCODE ,
            PARAMS.GROUPNOCONTACTCUSTOMNAME,
            PARAMS.JOINTRULETYPECODE,
            PARAMS.JOINTSELECTIONID,
            PARAMS.JOINTSELECTIONBOTHRULETYPECODE,
            PARAMS.JOINTSELECTIONNEITHERRULETYPECODE,
            PARAMS.EXCLUDESPOUSE) NF
)