USP_MKTSEGMENTATIONSEGMENTCALCULATEPROCESS_GETOPTIONS

CREATE procedure dbo.[USP_MKTSEGMENTATIONSEGMENTCALCULATEPROCESS_GETOPTIONS]
(
  @ID uniqueidentifier
)
as
  set nocount on;

  declare @SEGMENTATIONID uniqueidentifier;
  declare @SOURCEANALYSISRULEDATAEXISTS bit;

  select
    @SEGMENTATIONID = [SEGMENTATIONID]
  from dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS]
  where [ID] = @ID;

  exec dbo.[USP_MKTSEGMENTATION_SOURCEANALYSISRULEDATAEXISTS] @SEGMENTATIONID, @SOURCEANALYSISRULEDATAEXISTS output;

  -- See if any segments use a name format containing an advanced spouse option.

  declare @ADVANCEDNAMEFORMATOPTIONUSED bit = 0;
  if exists (select top 1 1
              from dbo.[MKTSEGMENTATIONSEGMENT]
              inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
              left join dbo.[NAMEFORMATPARAMETER] as [NAMEFORMATPARAMETEREFFORT] on [NAMEFORMATPARAMETEREFFORT].[ID] = [MKTSEGMENTATION].[NAMEFORMATPARAMETERID]
              left join dbo.[NAMEFORMATPARAMETER] as [NAMEFORMATPARAMETERSEGMENT] on [NAMEFORMATPARAMETERSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[NAMEFORMATPARAMETERID]
              where 
                [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
                -- advanced name format on the segment level

                and (([MKTSEGMENTATIONSEGMENT].[OVERRIDEADDRESSPROCESSING] = 1 
                      and [MKTSEGMENTATIONSEGMENT].[USEADDRESSPROCESSING] = 1 
                      and ([NAMEFORMATPARAMETERSEGMENT].[JOINTEXCLUDESPOUSE] = 1 or [NAMEFORMATPARAMETERSEGMENT].[JOINTSPOUSESMAILEDSEPARATELY] = 1)) 
                -- advanced name format on the mailing level

                or ([MKTSEGMENTATIONSEGMENT].[OVERRIDEADDRESSPROCESSING] = 0 
                    and [MKTSEGMENTATION].[USEADDRESSPROCESSING] = 1
                    and ([NAMEFORMATPARAMETEREFFORT].[JOINTEXCLUDESPOUSE] = 1 or [NAMEFORMATPARAMETEREFFORT].[JOINTSPOUSESMAILEDSEPARATELY] = 1)))
            )

    set @ADVANCEDNAMEFORMATOPTIONUSED = 1;
  else
    set @ADVANCEDNAMEFORMATOPTIONUSED = 0;

  select
    @SEGMENTATIONID as [SEGMENTATIONID],
    (select count([ID]) from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @SEGMENTATIONID) as [SEGMENTCOUNT],
    cast([MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[RUNSEGMENTATIONSEGMENTREFRESHPROCESS] as bit) as [RUNSEGMENTATIONSEGMENTREFRESHPROCESS],
    cast([MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[CACHESOURCEANALYSISRULEDATA] as bit) as [CACHESOURCEANALYSISRULEDATA],
    @SOURCEANALYSISRULEDATAEXISTS as [SOURCEANALYSISRULEDATAEXISTS],
    [MKTSEGMENTATION].[MAILINGTYPECODE],
    [MKTSEGMENTATIONSEGMENTREFRESHPROCESS].[ID] as [SEGMENTATIONSEGMENTREFRESHPROCESSID],
    [MKTMAILINGPREACTIVATIONPROCESS].[ID] as [MAILINGPREACTIVATIONPROCESSID],
    [MKTSEGMENTATION].[ACTIVE],
    [MKTSEGMENTATION].[HOUSEHOLDINGTYPECODE],
    [MKTSEGMENTATIONEXCLUSIONSPROCESS].[ID] as [SEGMENTATIONEXCLUSIONSPROCESSID],
    [MKTSEGMENTATION].[COMMUNICATIONTYPECODE],
    [MKTSEGMENTATION].[ISHISTORICAL],
    [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[RUNMARKETINGEXCLUSIONSREPORT],
    @ADVANCEDNAMEFORMATOPTIONUSED as [ADVANCEDNAMEFORMATOPTIONUSED]
  from dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[SEGMENTATIONID]
  left join dbo.[MKTSEGMENTATIONSEGMENTREFRESHPROCESS] on [MKTSEGMENTATIONSEGMENTREFRESHPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[SEGMENTATIONID]
  left join dbo.[MKTMAILINGPREACTIVATIONPROCESS] on [MKTMAILINGPREACTIVATIONPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[SEGMENTATIONID]
  left join dbo.[MKTSEGMENTATIONEXCLUSIONSPROCESS] on [MKTSEGMENTATIONEXCLUSIONSPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[SEGMENTATIONID]
  where [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[ID] = @ID;

  return 0;