USP_MKTSEGMENTATIONEXPORTPROCESS_GETOPTIONS

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

  declare @APPEALID uniqueidentifier;
  declare @INVITATIONID uniqueidentifier;
  declare @SEGMENTATIONID uniqueidentifier;
  declare @ADDRESSPROCESSINGREQUIRED bit;

  if dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0
    begin
      --Grab the appeal ID to use for address processing, but only for BBEC...

      select
        @APPEALID = isnull([APPEALMAILING].[APPEALID], [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID]),
        @INVITATIONID = [INVITATION].[ID]
      from dbo.[MKTSEGMENTATIONEXPORTPROCESS]
      left join dbo.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID] and dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID]) = 1 and [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] <> ''
      left join dbo.[APPEALMAILING] on [APPEALMAILING].[ID] = [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID]
      left join dbo.[INVITATION] on [INVITATION].[ID] = [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID]
      where [MKTSEGMENTATIONEXPORTPROCESS].[ID] = @ID;

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

      if exists (select top 1 1 
                 from dbo.[MKTSEGMENTATIONSEGMENT]
                 inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
                 where [MKTSEGMENTATION].[ID] = @SEGMENTATIONID
                 and ([MKTSEGMENTATION].[USEADDRESSPROCESSING] = 1
                  or ([MKTSEGMENTATIONSEGMENT].[OVERRIDEADDRESSPROCESSING] = 1 and [MKTSEGMENTATIONSEGMENT].[USEADDRESSPROCESSING] = 1)))
        set @ADDRESSPROCESSINGREQUIRED = 1
      else
        set @ADDRESSPROCESSINGREQUIRED = 0;
    end
  else
    set @ADDRESSPROCESSINGREQUIRED = 0;

  select
    [MKTSEGMENTATION].[ID] as [SEGMENTATIONID],
    [MKTSEGMENTATION].[MAILINGTYPECODE],
    dbo.[UFN_MKTSEGMENTATION_ISAPPEALMAILING]([MKTSEGMENTATION].[ID]) as [ISAPPEALMAILING],
    dbo.[UFN_MKTSEGMENTATION_ISCALCULATING]([MKTSEGMENTATION].[ID]) as [ISCALCULATING],
    dbo.[UFN_MKTSEGMENTATION_ISACTIVATING]([MKTSEGMENTATION].[ID]) as [ISACTIVATING],
    dbo.[UFN_MKTSEGMENTATION_ISRECORDCOUNTCACHECURRENT]([MKTSEGMENTATION].[ID], 0) as [RECORDCOUNTCACHEISCURRENT],
    dbo.[UFN_MKTSEGMENTATION_ISACTIVE]([MKTSEGMENTATION].[ID]) as [ISACTIVE],
    (select isnull(count([ID]), 0) from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID]) as [SEGMENTCOUNT],
    dbo.[UFN_MKTSEGMENTATIONSEED_GETSEEDCOUNT]([MKTSEGMENTATION].[ID]) as [SEEDCOUNT],
    convert(bit, case when exists (select top 1 1 from INFORMATION_SCHEMA.TABLES where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([MKTSEGMENTATION].[ID])) then 1 else 0 end) as [DATATABLEEXISTS],
    dbo.[UFN_MKTSEGMENTATION_GETMAILPREFERENCEMAILTYPECODE]([MKTSEGMENTATION].[ID]) as [ADDRESSPROCESSINGMAILTYPECODE],
    @APPEALID as [APPEALID],
    @ADDRESSPROCESSINGREQUIRED as [ADDRESSPROCESSINGREQUIRED],
    [MKTSEGMENTATION].[BASECURRENCYID],
    dbo.[UFN_MKTFINDERNUMBER_GETFIXEDWIDTH](1, [MKTSEGMENTATION].[ID]) as [FINDERNUMBERWIDTH],
    [MKTSEGMENTATIONEXPORTPROCESS].[EXPORTACTIVESEGMENTS],
    [MKTSEGMENTATIONEXPORTPROCESS].[EXPORTINLINEEXCLUSIONS],
    [MKTSEGMENTATIONEXPORTPROCESS].[INLINEEXCLUSIONEXPORTDEFINITIONID],
    [MKTSEGMENTATION].[COMMUNICATIONTYPECODE],
    @INVITATIONID as [INVITATIONID],
    [MKTSEGMENTATION].[ISHISTORICAL]
  from dbo.[MKTSEGMENTATIONEXPORTPROCESS]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID]
  where [MKTSEGMENTATIONEXPORTPROCESS].[ID] = @ID;

  return 0;