USP_MKTEXPORTDEFINITION_GETAVAILABLERECORDSOURCES

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

  /* when retrieving record sources for an appeal export definition:                                  */
  /* get all available record source query views: if a consolidated query view exists for a           */
  /* record source, then return it, else return the original query view; if a mailingID is specified  */
  /* only return distinct sources against which the mailing was activated                             */

  /* when retrieving record sources for an acknowledgement/membership export definition:              */
  /* do the same, but do not take the consolidated query view into account                            */

  declare @MAILINGRECORDSOURCES table ([ID] uniqueidentifier primary key);

  if @SEGMENTATIONID is not null
  begin
    insert into @MAILINGRECORDSOURCES
    select
      case when @MAILINGTYPECODE = 0 -- Appeal

           then isnull([MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID], [RS].[QUERYVIEWCATALOGID]) 
           else [RS].[QUERYVIEWCATALOGID]
      end as [QUERYVIEWCATALOGID]
    from
      dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID) [RS]
    left outer join
      dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC]
    on
      [MKTCONSOLIDATEDQUERYVIEWSPEC].[ID] = [RS].[QUERYVIEWCATALOGID]
  end

  select
    [DQVC].[ID] as [DONORQUERYVIEWID],
    [DQVC].[DISPLAYNAME] as [DONORQUERYVIEWNAME],
    [GQVC].[ID] as [REVENUEQUERYVIEWID],
    [GQVC].[DISPLAYNAME] as [REVENUEQUERYVIEWNAME],
    [MQVC].[ID] as [MEMBERSHIPQUERYVIEWID],
    [MQVC].[DISPLAYNAME] as [MEMBERSHIPQUERYVIEWNAME],
    [SQVC].[ID] as [SPONSORSHIPQUERYVIEWID],
    [SQVC].[DISPLAYNAME] as [SPONSORSHIPQUERYVIEWNAME]
  from
    dbo.[MKTRECORDSOURCE] [DRS]
  left outer join
    dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC]
  on
    [MKTCONSOLIDATEDQUERYVIEWSPEC].[ID] = [DRS].[ID]
  inner join
    dbo.[QUERYVIEWCATALOG] [DQVC]
  on
    [DQVC].[ID] = case when @MAILINGTYPECODE = 0 -- Appeal

                       then isnull([MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID], [DRS].[ID])
                       else [DRS].[ID]
                  end
  inner join
    dbo.[MKTGIFTRECORDSOURCE] [GRS]
  on 
    [GRS].[ID] = [DRS].[ID]
  inner join
    dbo.[QUERYVIEWCATALOG] [GQVC]
  on
    [GQVC].[ID] = [GRS].[QUERYVIEWCATALOGID]
  left outer join
    dbo.[MKTMEMBERSHIPRECORDSOURCE] [MRS]
  on 
    [MRS].[ID] = [DRS].[ID]
  left outer join
    dbo.[QUERYVIEWCATALOG] [MQVC]
  on
    [MQVC].[ID] = [MRS].[QUERYVIEWCATALOGID]  
  left outer join
    dbo.[MKTSPONSORSHIPRECORDSOURCE] [SRS]
  on 
    [SRS].[ID] = [DRS].[ID]  
  left outer join
    dbo.[QUERYVIEWCATALOG] [SQVC]
  on
    [SQVC].[ID] = [SRS].[QUERYVIEWCATALOGID]
  where (
    (@SEGMENTATIONID is null
  or
    [DQVC].[ID] in (select [ID] from @MAILINGRECORDSOURCES))
  and
  (dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([DQVC].[ID]) = 1)
        )
  order by
    [DQVC].[DISPLAYNAME];

  return 0;