USP_SIMPLEDATALIST_MKTEXPORTDEFINITION

CREATE procedure dbo.[USP_SIMPLEDATALIST_MKTEXPORTDEFINITION]
(
  @MAILINGTYPECODE tinyint = null,
  @SEGMENTATIONID uniqueidentifier = null
)
as
  set nocount on;

  select distinct
    [MKTEXPORTDEFINITION].[ID] as [VALUE],
    [MKTEXPORTDEFINITION].[NAME] as [LABEL]
  from dbo.[MKTEXPORTDEFINITIONOUTPUTFIELD]
  inner join dbo.[MKTEXPORTDEFINITION] on [MKTEXPORTDEFINITION].[ID] = [MKTEXPORTDEFINITIONOUTPUTFIELD].[EXPORTDEFINITIONID]
  where [MKTEXPORTDEFINITION].[EXPORTDEFINITIONID] is null
  and [MKTEXPORTDEFINITION].[ISSYSTEM] = 0
  and (@MAILINGTYPECODE is null or @MAILINGTYPECODE = 255 or [MKTEXPORTDEFINITION].[MAILINGTYPECODE] = @MAILINGTYPECODE)
  and
  (@SEGMENTATIONID is null or (
    [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYVIEWCATALOGID] in (select [ID] from dbo.[UFN_MKTEXPORTDEFINITION_GETSPECIALQUERYVIEWIDS]())
    or [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYVIEWCATALOGID] in 
    (
      select
        case when @MAILINGTYPECODE = 0 -- appeal

             then isnull([MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID], [RS].[QUERYVIEWCATALOGID])
             else [RS].[QUERYVIEWCATALOGID] -- acknowledgement, membership

        end
      from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID) as [RS]
      left outer join dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC] on [MKTCONSOLIDATEDQUERYVIEWSPEC].[ID] = [RS].[QUERYVIEWCATALOGID]
    )
    or (@MAILINGTYPECODE = 1 and [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYVIEWCATALOGID] in 
    (
      select 
        [GRS].[QUERYVIEWCATALOGID]
      from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID) as [RS]
      inner join dbo.[MKTGIFTRECORDSOURCE] as [GRS] on [GRS].[ID] = [RS].[QUERYVIEWCATALOGID]
    ))
    or (@MAILINGTYPECODE = 2 and [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYVIEWCATALOGID] in 
    (
      select 
        [MRS].[QUERYVIEWCATALOGID]
      from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID) as [RS]
      inner join dbo.[MKTMEMBERSHIPRECORDSOURCE] as [MRS] on [MRS].[ID] = [RS].[QUERYVIEWCATALOGID]
    ))
  ))  
  order by [MKTEXPORTDEFINITION].[NAME];

  return 0;