USP_SIMPLEDATALIST_MKTSEGMENTATIONEXPORTDEFINITION2

create procedure dbo.[USP_SIMPLEDATALIST_MKTSEGMENTATIONEXPORTDEFINITION2]
(
  @SEGMENTATIONID uniqueidentifier,
  @EXPORTACTIVESEGMENTS bit = 1,
  @MAILEXPORTDEFINITIONID uniqueidentifier = null,
  @EMAILEXPORTDEFINITIONID uniqueidentifier = null,
  @PHONEEXPORTDEFINITIONID uniqueidentifier = null,
  @EXPORTINLINEEXCLUSIONS bit = 0,
  @INLINEEXCLUSIONEXPORTDEFINITIONID uniqueidentifier = null
)
as
  set nocount on;

  declare @ITEMS table ([ID] uniqueidentifier, [VALUE] varchar(38), [LABEL] nvarchar(255), [EXCLUSION] bit);

  if @EXPORTACTIVESEGMENTS = 1
    begin
      with [PACKAGES] ([PACKAGEID]) as (
        select distinct [TEMP].[PACKAGEID]
        from (select [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
                from dbo.[MKTSEGMENTATIONSEGMENT]
                inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
                left outer join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[SEGMENTID] = [MKTSEGMENT].[ID]
                inner join dbo.[MKTPACKAGE] on [MKTSEGMENTATIONSEGMENT].[PACKAGEID] = [MKTPACKAGE].[ID] and [MKTPACKAGE].[ISSYSTEM] = 0
               where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
                 and ([MKTSEGMENTLIST].[TYPECODE] is null or [MKTSEGMENTLIST].[TYPECODE] <> 1)
              union all
              select [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]
                from dbo.[MKTSEGMENTATIONTESTSEGMENT]
                inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
                inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
                left outer join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[SEGMENTID] = [MKTSEGMENT].[ID]
                inner join dbo.[MKTPACKAGE] on [MKTSEGMENTATIONSEGMENT].[PACKAGEID] = [MKTPACKAGE].[ID] and [MKTPACKAGE].[ISSYSTEM] = 0
               where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
                 and ([MKTSEGMENTLIST].[TYPECODE] is null or [MKTSEGMENTLIST].[TYPECODE] <> 1)) as [TEMP]
      )
      insert into @ITEMS ([ID], [VALUE], [LABEL], [EXCLUSION])
      select distinct
        [EXPORTDEFINITION].[ID],
        convert(varchar(36), [EXPORTDEFINITION].[ID]) + '|0' as [VALUE],
        [EXPORTDEFINITION].[NAME] as [LABEL],
        0 as [EXCLUSION]
      from [PACKAGES]
      inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [PACKAGES].[PACKAGEID]
      left outer join dbo.[LETTERCODE] on [LETTERCODE].[ID] = [MKTPACKAGE].[LETTERCODEID]
      left outer join dbo.[MKTEXPORTDEFINITION] on ([MKTEXPORTDEFINITION].[NETCOMMUNITYDATASOURCEID] = [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] and [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] > 0)
      inner join dbo.[EXPORTDEFINITION] on ([EXPORTDEFINITION].[ID] = [LETTERCODE].[EXPORTDEFINITIONID] or [EXPORTDEFINITION].[ID] = [MKTEXPORTDEFINITION].[EXPORTDEFINITIONID] or [EXPORTDEFINITION].[ID] = [MKTPACKAGE].[EXPORTDEFINITIONID])
      order by [EXPORTDEFINITION].[NAME];

      if @MAILEXPORTDEFINITIONID is not null and not exists (select top 1 1 from @ITEMS where [ID] = @MAILEXPORTDEFINITIONID)
        insert into @ITEMS ([ID], [VALUE], [LABEL], [EXCLUSION])
        values (@MAILEXPORTDEFINITIONID, convert(varchar(36), @MAILEXPORTDEFINITIONID) + '|0', dbo.[UFN_EXPORTDEFINITION_GETNAME](@MAILEXPORTDEFINITIONID), 0);

      if @EMAILEXPORTDEFINITIONID is not null and not exists (select top 1 1 from @ITEMS where [ID] = @EMAILEXPORTDEFINITIONID)
        insert into @ITEMS ([ID], [VALUE], [LABEL], [EXCLUSION])
        values (@EMAILEXPORTDEFINITIONID, convert(varchar(36), @EMAILEXPORTDEFINITIONID) + '|0', dbo.[UFN_EXPORTDEFINITION_GETNAME](@EMAILEXPORTDEFINITIONID), 0);

      if @PHONEEXPORTDEFINITIONID is not null and not exists (select top 1 1 from @ITEMS where [ID] = @PHONEEXPORTDEFINITIONID)
        insert into @ITEMS ([ID], [VALUE], [LABEL], [EXCLUSION])
     values (@PHONEEXPORTDEFINITIONID, convert(varchar(36), @PHONEEXPORTDEFINITIONID) + '|0', dbo.[UFN_EXPORTDEFINITION_GETNAME](@PHONEEXPORTDEFINITIONID), 0);
    end

  if @EXPORTINLINEEXCLUSIONS = 1
    if @INLINEEXCLUSIONEXPORTDEFINITIONID is not null
      insert into @ITEMS ([ID], [VALUE], [LABEL], [EXCLUSION])
      values (@INLINEEXCLUSIONEXPORTDEFINITIONID, convert(varchar(36), @INLINEEXCLUSIONEXPORTDEFINITIONID) + '|1', N'Exclusions: ' + dbo.[UFN_EXPORTDEFINITION_GETNAME](@INLINEEXCLUSIONEXPORTDEFINITIONID), 1);

  select [VALUE], [LABEL] from @ITEMS order by [EXCLUSION], [LABEL];

  return 0;