USP_SIMPLEDATALIST_MKTSEGMENTATIONEXPORTDEFINITION

CREATE procedure dbo.[USP_SIMPLEDATALIST_MKTSEGMENTATIONEXPORTDEFINITION]
(
  @SEGMENTATIONID uniqueidentifier
)
as
  set nocount on;

  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]
  )
  select distinct
    [EXPORTDEFINITION].[ID] as [VALUE],
    [EXPORTDEFINITION].[NAME] as [LABEL]
  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];

  return 0;