USP_MKTSEGMENTATIONEXPORTPROCESS_GETEXCLUSIONRECORDSOURCES

create procedure dbo.[USP_MKTSEGMENTATIONEXPORTPROCESS_GETEXCLUSIONRECORDSOURCES]
(
  @SEGMENTATIONID uniqueidentifier
)
as
  set nocount on;

  with [RECORDSOURCES] ([RECORDSOURCEID]) as
  (
    select
      [MKTSEGMENT].[QUERYVIEWCATALOGID] as [RECORDSOURCEID]
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
    and [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 1
  ),
  [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
    and [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 1
    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;