USP_MKTSEGMENTATION_EXCLUSIONS_SEGMENTOPTIONS

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

  declare @MAILINGTYPECODE tinyint;
  declare @HOUSEHOLDINGTYPECODE tinyint;
  declare @DONORIDDATATYPE nvarchar(128);
  declare @VALIDRECORDTYPES table ([RECORDTYPEID] uniqueidentifier primary key, [RECORDSOURCEID] uniqueidentifier not null);
  declare @CONSOLIDATEDRECORDTYPES table ([RECORDTYPEID] uniqueidentifier not null, [QUERYVIEWCATALOGID] uniqueidentifier not null, [FILTERTYPECODE] tinyint);

  select
    @MAILINGTYPECODE = [MAILINGTYPECODE],
    @HOUSEHOLDINGTYPECODE = [HOUSEHOLDINGTYPECODE],
    @DONORIDDATATYPE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_GETDONORIDDATATYPE]([ID])
  from dbo.[MKTSEGMENTATION]
  where [ID] = @SEGMENTATIONID;


  if @MAILINGTYPECODE = 0
    --Grab all the consolidated list record types that are used in the mailing's segments...

    insert into @CONSOLIDATEDRECORDTYPES ([RECORDTYPEID], [QUERYVIEWCATALOGID])
      select distinct
        [IDSETREGISTER].[RECORDTYPEID],
        [MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID]
      from dbo.[MKTSEGMENTATIONSEGMENT]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      left join dbo.[MKTSEGMENTSELECTION] on [MKTSEGMENTSELECTION].[SEGMENTID] = [MKTSEGMENT].[ID]
      inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENTSELECTION].[SELECTIONID] or [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]
      inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[RECORDTYPEID] = [IDSETREGISTER].[RECORDTYPEID]
      inner join dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC] on [MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID] = [QUERYVIEWCATALOG].[ID]
      where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID;

  --This procedure needs to only return record types that actually exist in the mailing's segments so that we don't process

  --extra exclusions that are not needed.  Do not include the consolidated list record type, instead break it out into its

  --individual list record types and house file record type.

  insert into @VALIDRECORDTYPES ([RECORDTYPEID], [RECORDSOURCEID])
    --House file and list segment record types

    select [IDSETREGISTER].[RECORDTYPEID], [MKTSEGMENT].[QUERYVIEWCATALOGID]
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    left join dbo.[MKTSEGMENTSELECTION] on [MKTSEGMENTSELECTION].[SEGMENTID] = [MKTSEGMENT].[ID]
    inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENTSELECTION].[SELECTIONID] or [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]
    left join @CONSOLIDATEDRECORDTYPES as [CRT] on [CRT].[RECORDTYPEID] = [IDSETREGISTER].[RECORDTYPEID]
    where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
    and [CRT].[RECORDTYPEID] is null

    union

    --Consolidated list "house file" record types

    select [QUERYVIEWCATALOG].[RECORDTYPEID], [QUERYVIEWCATALOG].[ID]
    from @CONSOLIDATEDRECORDTYPES as [CRT]
    inner join dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC] on [MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID] = [CRT].[QUERYVIEWCATALOGID]
    inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTCONSOLIDATEDQUERYVIEWSPEC].[ID]

    union

    --Consolidated list "list" record types

    select [MKTSEGMENTLIST].[IDSETRECORDTYPEID], [MKTLIST].[RECORDSOURCEID]
    from @CONSOLIDATEDRECORDTYPES as [CRT]
    inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[CONSOLIDATEDQUERYVIEWID] = [CRT].[QUERYVIEWCATALOGID]
    inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID];


  select
    @MAILINGTYPECODE as [MAILINGTYPECODE],
    [VRT].[RECORDSOURCEID],
    convert(bit, case when dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([VRT].[RECORDSOURCEID]) = 1 then 1 else 0 end) as [ISBBEC],
 [VRT].[RECORDTYPEID],
    @DONORIDDATATYPE as [DONORIDDATATYPE],
    @HOUSEHOLDINGTYPECODE as [HOUSEHOLDINGTYPECODE]
  from @VALIDRECORDTYPES as [VRT];

  return 0;