USP_MKTSEGMENTATIONEXPORTPROCESS_GETRECORDSOURCES

CREATE procedure dbo.[USP_MKTSEGMENTATIONEXPORTPROCESS_GETRECORDSOURCES]
(
  @SEGMENTATIONID uniqueidentifier,
  @EXPORTDEFINITIONID uniqueidentifier,
  @PREVIEWDEFAULTMAILEXPORTDEFINITION bit = 0,
  @PREVIEWDEFAULTEMAILEXPORTDEFINITION bit = 0,
  @PREVIEWDEFAULTPHONEEXPORTDEFINITION bit = 0
)
as
  set nocount on;

  with [RECORDSOURCES] ([RECORDSOURCEID]) as
  (
    select
      [MKTSEGMENT].[QUERYVIEWCATALOGID] as [RECORDSOURCEID]
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[MKTSEGMENTATIONEXPORTPROCESS] on [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    left outer join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
    inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
    left outer join dbo.[LETTERCODE] as [MAILEXPORTDEFINITION] on [MAILEXPORTDEFINITION].[ID] = [MKTPACKAGE].[LETTERCODEID]
    left outer join dbo.[MKTEXPORTDEFINITION] on ([MKTEXPORTDEFINITION].[NETCOMMUNITYDATASOURCEID] = [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] and [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] > 0)
    left outer join dbo.[EXPORTDEFINITION] as [EMAILEXPORTDEFINITION] on [EMAILEXPORTDEFINITION].[ID] = [MKTEXPORTDEFINITION].[EXPORTDEFINITIONID]
    where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
    and [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 0
    and ([MKTSEGMENTLIST].[TYPECODE] is null or [MKTSEGMENTLIST].[TYPECODE] <> 1) -- exclude vendor-managed segments

    and case [MKTPACKAGE].[CHANNELCODE]
          when 0 then case when @PREVIEWDEFAULTMAILEXPORTDEFINITION = 1 then @EXPORTDEFINITIONID
                      else coalesce([MAILEXPORTDEFINITION].[EXPORTDEFINITIONID], [MKTPACKAGE].[EXPORTDEFINITIONID], [MKTSEGMENTATIONEXPORTPROCESS].[MAILEXPORTDEFINITIONID]) end
          when 1 then case when @PREVIEWDEFAULTEMAILEXPORTDEFINITION = 1 then @EXPORTDEFINITIONID
                      else coalesce([EMAILEXPORTDEFINITION].[ID], [MKTPACKAGE].[EXPORTDEFINITIONID], [MKTSEGMENTATIONEXPORTPROCESS].[EMAILEXPORTDEFINITIONID]) end
          when 2 then case when @PREVIEWDEFAULTPHONEEXPORTDEFINITION = 1 then @EXPORTDEFINITIONID
                      else coalesce([MKTPACKAGE].[EXPORTDEFINITIONID], [MKTSEGMENTATIONEXPORTPROCESS].[PHONEEXPORTDEFINITIONID]) end
        end = @EXPORTDEFINITIONID

    union

    select
      [MKTSEGMENT].[QUERYVIEWCATALOGID] as [RECORDSOURCEID]
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
    inner join dbo.[MKTSEGMENTATIONEXPORTPROCESS] on [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    left outer join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
    inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]
    left outer join dbo.[LETTERCODE] as [MAILEXPORTDEFINITION] on [MAILEXPORTDEFINITION].[ID] = [MKTPACKAGE].[LETTERCODEID]
    left outer join dbo.[MKTEXPORTDEFINITION] on ([MKTEXPORTDEFINITION].[NETCOMMUNITYDATASOURCEID] = [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] and [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] > 0)
    left outer join dbo.[EXPORTDEFINITION] as [EMAILEXPORTDEFINITION] on [EMAILEXPORTDEFINITION].[ID] = [MKTEXPORTDEFINITION].[EXPORTDEFINITIONID]
    where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
    and [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 0
    and ([MKTSEGMENTLIST].[TYPECODE] is null or [MKTSEGMENTLIST].[TYPECODE] <> 1) -- exclude vendor-managed segments

    and case [MKTPACKAGE].[CHANNELCODE]
          when 0 then case when @PREVIEWDEFAULTMAILEXPORTDEFINITION = 1 then @EXPORTDEFINITIONID
                      else coalesce([MAILEXPORTDEFINITION].[EXPORTDEFINITIONID], [MKTPACKAGE].[EXPORTDEFINITIONID], [MKTSEGMENTATIONEXPORTPROCESS].[MAILEXPORTDEFINITIONID]) end
          when 1 then case when @PREVIEWDEFAULTEMAILEXPORTDEFINITION = 1 then @EXPORTDEFINITIONID
                      else coalesce([EMAILEXPORTDEFINITION].[ID], [MKTPACKAGE].[EXPORTDEFINITIONID], [MKTSEGMENTATIONEXPORTPROCESS].[EMAILEXPORTDEFINITIONID]) end
          when 2 then case when @PREVIEWDEFAULTPHONEEXPORTDEFINITION = 1 then @EXPORTDEFINITIONID
                      else coalesce([MKTPACKAGE].[EXPORTDEFINITIONID], [MKTSEGMENTATIONEXPORTPROCESS].[PHONEEXPORTDEFINITIONID]) end
        end = @EXPORTDEFINITIONID
  ),
  [RECORDSOURCES_WITH_SEQUENCE] ([RECORDSOURCEID], [SEQUENCE]) as
  (
    select
      [RECORDSOURCES].[RECORDSOURCEID],
      min([MKTSEGMENTATIONSEGMENT].[SEQUENCE])
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    inner join [RECORDSOURCES] on [RECORDSOURCES].[RECORDSOURCEID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
    where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
    group by [RECORDSOURCES].[RECORDSOURCEID]
  )
  select
    [RS].[RECORDSOURCEID],
    [QUERYVIEWCATALOG].[DISPLAYNAME] as [RECORDSOURCENAME],
    dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([RS].[RECORDSOURCEID]) as [RECORDSOURCEISBBEC],
    [QUERYVIEWCATALOG].[OBJECTNAME] as [RECORDSOURCEOBJECTNAME],
    [QUERYVIEWCATALOG].[PRIMARYKEYTYPENAME] as [RECORDSOURCEPRIMARYKEYTYPENAME],
    isnull([CONSOLIDATEDQUERYVIEWCATALOG].[OBJECTNAME], '') as [CONSOLIDATEDOBJECTNAME],
    [QUERYVIEWCATALOG].[QUERYVIEWSPEC] as [RECORDSOURCEQUERYVIEWXML],
    isnull([CONSOLIDATEDQUERYVIEWCATALOG].[QUERYVIEWSPEC], '') as [CONSOLIDATEDQUERYVIEWXML]
  from [RECORDSOURCES_WITH_SEQUENCE] as [RS]
  inner join dbo.[MKTRECORDSOURCE] on [MKTRECORDSOURCE].[ID] = [RS].[RECORDSOURCEID]
  inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTRECORDSOURCE].[ID]
  left outer join dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC] on [MKTCONSOLIDATEDQUERYVIEWSPEC].[ID] = [RS].[RECORDSOURCEID]
  left outer join dbo.[QUERYVIEWCATALOG] as [CONSOLIDATEDQUERYVIEWCATALOG] on [CONSOLIDATEDQUERYVIEWCATALOG].[ID] = [MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID]
  order by [RS].[SEQUENCE];

  return 0;