USP_MKTSEGMENTATION_GETCONTACTPREFOPTIONS

CREATE procedure dbo.[USP_MKTSEGMENTATION_GETCONTACTPREFOPTIONS]
(
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier,
  @NAMEFORMATPARAMETERID uniqueidentifier
)
as
  set nocount on;

  declare @INDUSESEASONALADDRESS bit;
  declare @ORGMAILINGPREFERENCE tinyint;
  declare @INDALTADDRESS1TYPECODEID uniqueidentifier;
  declare @INDALTADDRESS1ISPRIMARY bit;
  declare @INDALTADDRESS2TYPECODEID uniqueidentifier;
  declare @INDALTADDRESS2ISPRIMARY bit; 
  declare @ORGALTADDRESS1TYPECODEID uniqueidentifier;
  declare @ORGALTADDRESS1ISPRIMARY bit;
  declare @ORGALTADDRESS2TYPECODEID uniqueidentifier;
  declare @ORGALTADDRESS2ISPRIMARY bit;
  declare @INDINCLUDEWITHNOADDRESS bit;
  declare @ORGINCLUDEWITHNOADDRESS bit;
  declare @ORGINCLUDEWITHNOCONTACT bit;
  declare @ORGSENDTOALLCONTACTS bit;
  declare @INDUSECONSTITUENTPREFS bit;
  declare @ORGUSECONSTITUENTPREFS bit;
  declare @GROUPALTADDRESS1TYPECODEID uniqueidentifier;
  declare @GROUPALTADDRESS1ISPRIMARY bit;
  declare @GROUPALTADDRESS2TYPECODEID uniqueidentifier;
  declare @GROUPALTADDRESS2ISPRIMARY bit;
  declare @GROUPINCLUDEWITHNOADDRESS bit;
  declare @GROUPUSECONSTITUENTPREFS tinyint;

  declare @INDIVIDUALADDRESSEES xml;
  declare @ADDRESSEEFUNCTIONID uniqueidentifier;
  declare @INDIVIDUALSALUTATIONS xml;
  declare @SALUTATIONFUNCTIONID uniqueidentifier;
  declare @ORGADDRESSEES xml;
  declare @CONTACTADDRESSEEFUNCTIONID uniqueidentifier;
  declare @ORGSALUTATIONCODE tinyint;
  declare @ORGSALUTATIONS xml;
  declare @CONTACTSALUTATIONFUNCTIONID uniqueidentifier;
  declare @CONTACTSALUTATIONOPTIONCODE tinyint;
  declare @CUSTOMNAME nvarchar(100);
  declare @GROUPADDRESSEES xml;
  declare @GROUPADDRESSEEFUNCTIONID uniqueidentifier;
  declare @GROUPSALUTATIONCODE tinyint;
  declare @GROUPSALUTATIONS xml;
  declare @GROUPSALUTATIONFUNCTIONID uniqueidentifier; 
  declare @GROUPNOCONTACTOPTIONCODE bit; 
  declare @GROUPNOCONTACTCUSTOMNAME nvarchar(100);
  declare @JOINTRULETYPECODE tinyint; 
  declare @JOINTSELECTIONID uniqueidentifier;
  declare @JOINTSELECTIONBOTHRULETYPECODE tinyint;
  declare @JOINTSELECTIONNEITHERRULETYPECODE tinyint;

  -- get the name format options

  select
    @INDIVIDUALADDRESSEES = dbo.[UFN_NAMEFORMATPARAMETER_GETINDADDRESSEES_TOITEMLISTXML]([NAMEFORMATPARAMETER].[ID]),
    @ADDRESSEEFUNCTIONID = [ADDRESSEEFUNCTIONID],
    @INDIVIDUALSALUTATIONS = dbo.[UFN_NAMEFORMATPARAMETER_GETINDSALUTATIONS_TOITEMLISTXML]([NAMEFORMATPARAMETER].[ID]),
    @SALUTATIONFUNCTIONID = [SALUTATIONFUNCTIONID],
    @ORGADDRESSEES = dbo.[UFN_NAMEFORMATPARAMETER_GETORGADDRESSEES_TOITEMLISTXML]([NAMEFORMATPARAMETER].[ID]),
    @CONTACTADDRESSEEFUNCTIONID = [CONTACTADDRESSEEFUNCTIONID],
    @ORGSALUTATIONCODE = [ORGSALUTATIONCODE],
    @ORGSALUTATIONS = dbo.[UFN_NAMEFORMATPARAMETER_GETORGSALUTATIONS_TOITEMLISTXML]([NAMEFORMATPARAMETER].[ID]),
    @CONTACTSALUTATIONFUNCTIONID = [CONTACTSALUTATIONFUNCTIONID],
    @CONTACTSALUTATIONOPTIONCODE = [CONTACTSALUTATIONOPTIONCODE],
    @CUSTOMNAME = [CUSTOMNAME],
    @GROUPADDRESSEES = dbo.[UFN_NAMEFORMATPARAMETER_GETGRPADDRESSEES_TOITEMLISTXML]([NAMEFORMATPARAMETER].[ID]),
    @GROUPADDRESSEEFUNCTIONID = [GROUPADDRESSEEFUNCTIONID],
    @GROUPSALUTATIONCODE = [GROUPSALUTATIONCODE],
    @GROUPSALUTATIONS = dbo.[UFN_NAMEFORMATPARAMETER_GETGRPSALUTATIONS_TOITEMLISTXML]([NAMEFORMATPARAMETER].[ID]),
    @GROUPSALUTATIONFUNCTIONID = [GROUPSALUTATIONFUNCTIONID],
    @GROUPNOCONTACTOPTIONCODE = [GROUPNOCONTACTOPTIONCODE],
    @GROUPNOCONTACTCUSTOMNAME = [GROUPNOCONTACTCUSTOMNAME],
    @JOINTRULETYPECODE = [JOINTRULETYPECODE],
    @JOINTSELECTIONID = [JOINTSELECTIONID],
    @JOINTSELECTIONBOTHRULETYPECODE = [JOINTSELECTIONBOTHRULETYPECODE],
    @JOINTSELECTIONNEITHERRULETYPECODE = [JOINTSELECTIONNEITHERRULETYPECODE]
  from dbo.[NAMEFORMATPARAMETER]
  where (([ID] = @NAMEFORMATPARAMETERID) or (@NAMEFORMATPARAMETERID is null and [ISDEFAULT] = 1))

  -- get the address format options

  select
    @INDUSESEASONALADDRESS = [INDUSESEASONALADDRESS],  
    @ORGMAILINGPREFERENCE = [ORGMAILINGPREFERENCE],  
    @INDALTADDRESS1TYPECODEID = [INDALTADDRESS1TYPECODEID],  
    @INDALTADDRESS1ISPRIMARY = [INDALTADDRESS1ISPRIMARY],  
    @INDALTADDRESS2TYPECODEID = [INDALTADDRESS2TYPECODEID],  
    @INDALTADDRESS2ISPRIMARY = [INDALTADDRESS2ISPRIMARY],  
    @ORGALTADDRESS1TYPECODEID = [ORGALTADDRESS1TYPECODEID],  
    @ORGALTADDRESS1ISPRIMARY = [ORGALTADDRESS1ISPRIMARY],  
    @ORGALTADDRESS2TYPECODEID = [ORGALTADDRESS2TYPECODEID],  
    @ORGALTADDRESS2ISPRIMARY = [ORGALTADDRESS2ISPRIMARY],  
    @INDINCLUDEWITHNOADDRESS = [INDINCLUDEWITHNOADDRESS],  
    @ORGINCLUDEWITHNOADDRESS = [ORGINCLUDEWITHNOADDRESS],  
    @ORGINCLUDEWITHNOCONTACT = [ORGINCLUDEWITHNOCONTACT], 
    @ORGSENDTOALLCONTACTS = [ORGSENDTOALLCONTACTS], 
    @INDUSECONSTITUENTPREFS = [INDUSECONSTITUENTPREFS],  
    @ORGUSECONSTITUENTPREFS = [ORGUSECONSTITUENTPREFS],  
    @GROUPALTADDRESS1TYPECODEID = [GROUPALTADDRESS1TYPECODEID],  
    @GROUPALTADDRESS1ISPRIMARY = [GROUPALTADDRESS1ISPRIMARY],  
    @GROUPALTADDRESS2TYPECODEID = [GROUPALTADDRESS2TYPECODEID],  
    @GROUPALTADDRESS2ISPRIMARY = [GROUPALTADDRESS2ISPRIMARY],  
    @GROUPINCLUDEWITHNOADDRESS = [GROUPINCLUDEWITHNOADDRESS],  
    @GROUPUSECONSTITUENTPREFS = [GROUPUSECONSTITUENTPREFS]
  from dbo.[ADDRESSPROCESSINGOPTION]
  where ([ID] = @ADDRESSPROCESSINGOPTIONID or (@ADDRESSPROCESSINGOPTIONID is null and [ISDEFAULT] = 1));

  -- return the results

  select
    @INDUSESEASONALADDRESS as [INDUSESEASONALADDRESS],
    @ORGMAILINGPREFERENCE as [ORGMAILINGPREFERENCE],
    @INDALTADDRESS1TYPECODEID as [INDALTADDRESS1TYPECODEID],  
    @INDALTADDRESS1ISPRIMARY as [INDALTADDRESS1ISPRIMARY],  
    @INDALTADDRESS2TYPECODEID as [INDALTADDRESS2TYPECODEID],  
    @INDALTADDRESS2ISPRIMARY as [INDALTADDRESS2ISPRIMARY], 
    @ORGALTADDRESS1TYPECODEID as [ORGALTADDRESS1TYPECODEID],  
    @ORGALTADDRESS1ISPRIMARY as [ORGALTADDRESS1ISPRIMARY],
    @ORGALTADDRESS2TYPECODEID as [ORGALTADDRESS2TYPECODEID],  
    @ORGALTADDRESS2ISPRIMARY as [ORGALTADDRESS2ISPRIMARY],
    @INDINCLUDEWITHNOADDRESS as [INDINCLUDEWITHNOADDRESS],
    @ORGINCLUDEWITHNOADDRESS as [ORGINCLUDEWITHNOADDRESS],  
    @ORGINCLUDEWITHNOCONTACT as [ORGINCLUDEWITHNOCONTACT], 
    @ORGSENDTOALLCONTACTS as [ORGSENDTOALLCONTACTS],
    @INDUSECONSTITUENTPREFS as [INDUSECONSTITUENTPREFS],  
    @ORGUSECONSTITUENTPREFS as [ORGUSECONSTITUENTPREFS],  
    @GROUPALTADDRESS1TYPECODEID as [GROUPALTADDRESS1TYPECODEID],  
    @GROUPALTADDRESS1ISPRIMARY as [GROUPALTADDRESS1ISPRIMARY],  
    @GROUPALTADDRESS2TYPECODEID as [GROUPALTADDRESS2TYPECODEID],  
    @GROUPALTADDRESS2ISPRIMARY as [GROUPALTADDRESS2ISPRIMARY],  
    @GROUPINCLUDEWITHNOADDRESS as [GROUPINCLUDEWITHNOADDRESS],  
    @GROUPUSECONSTITUENTPREFS as [GROUPUSECONSTITUENTPREFS],
    @INDIVIDUALADDRESSEES as [INDIVIDUALADDRESSEES],
    @ADDRESSEEFUNCTIONID as [ADDRESSEEFUNCTIONID],
    @INDIVIDUALSALUTATIONS as [INDIVIDUALSALUTATIONS],
    @SALUTATIONFUNCTIONID as [SALUTATIONFUNCTIONID],
    @ORGADDRESSEES as [ORGADDRESSEES],
    @CONTACTADDRESSEEFUNCTIONID as [CONTACTADDRESSEEFUNCTIONID],
    @ORGSALUTATIONCODE as [ORGSALUTATIONCODE],
    @ORGSALUTATIONS as [ORGSALUTATIONS],
    @CONTACTSALUTATIONFUNCTIONID as [CONTACTSALUTATIONFUNCTIONID],
    @CONTACTSALUTATIONOPTIONCODE as [CONTACTSALUTATIONOPTIONCODE],
    @CUSTOMNAME as [CUSTOMNAME],
    @GROUPADDRESSEES as [GROUPADDRESSEES],
    @GROUPADDRESSEEFUNCTIONID as [GROUPADDRESSEEFUNCTIONID],
    @GROUPSALUTATIONCODE as [GROUPSALUTATIONCODE],
    @GROUPSALUTATIONS as [GROUPSALUTATIONS],
    @GROUPSALUTATIONFUNCTIONID as [GROUPSALUTATIONFUNCTIONID], 
    @GROUPNOCONTACTOPTIONCODE as [GROUPNOCONTACTOPTIONCODE], 
    @GROUPNOCONTACTCUSTOMNAME as [GROUPNOCONTACTCUSTOMNAME], 
    @JOINTRULETYPECODE as [JOINTRULETYPECODE], 
    @JOINTSELECTIONID as [JOINTSELECTIONID], 
    @JOINTSELECTIONBOTHRULETYPECODE as [JOINTSELECTIONBOTHRULETYPECODE], 
    @JOINTSELECTIONNEITHERRULETYPECODE as [JOINTSELECTIONNEITHERRULETYPECODE];

  return 0;