UFN_MKTRECORDSOURCE_GETRECORDSOURCES

CREATE function dbo.[UFN_MKTRECORDSOURCE_GETRECORDSOURCES]()
returns table
as
  return (select
            [QUERYVIEWCATALOG].[ID],
            (case when [MKTRECORDSOURCE].[ID] is null then 0 else 1 end) as [SELECTED],
            [QUERYVIEWCATALOG].[DISPLAYNAME] as [NAME],
            [RECORDTYPE].[NAME] as [RECORDTYPE],
            [QUERYVIEWCATALOG].[DESCRIPTION],
            [QUERYVIEWCATALOG].[PRIMARYKEYTYPENAME],
            [STATS].[SEGMENTCOUNT],
            [STATS].[LISTCOUNT],
            [STATS].[MAILINGCOUNT],
            [STATS].[EXPORTCOUNT],
            [STATS].[QUERYCOUNT],
            [RECORDTYPE].[ID] as [RECORDTYPEID]
          from dbo.[QUERYVIEWCATALOG]
          inner join dbo.[RECORDTYPE] on [RECORDTYPE].[ID] = [QUERYVIEWCATALOG].[RECORDTYPEID]
          left join dbo.[MKTRECORDSOURCE] on [MKTRECORDSOURCE].[ID] = [QUERYVIEWCATALOG].[ID]
          left join dbo.[UFN_MKTRECORDSOURCE_GETUSAGESTATISTICS]() as [STATS] on [MKTRECORDSOURCE].[ID] = [STATS].[ID]
          where ([QUERYVIEWCATALOG].[ROOTOBJECT] = 1)
          and ([QUERYVIEWCATALOG].[ID] not in (select [QUERYVIEWCATALOGID]
                                              from dbo.[MKTSEGMENTLIST]
                                              where [QUERYVIEWCATALOGID] is not null
                                              union
                                              select [CONSOLIDATEDQUERYVIEWCATALOGID]
                                              from dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC]))
          and ([QUERYVIEWCATALOG].[QUERYVIEWSPEC].value('declare namespace bbafx="bb_appfx_queryview";
                                                        declare namespace c="bb_appfx_commontypes";
                                                        (bbafx:QueryViewSpec/c:MetaTags/@MarketingConstituentRecordSource)[1]','char(1)') = '1')
          and (dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([QUERYVIEWCATALOG].[ID]) = 1)
         )