UFN_QUERY_MKTEXPORTDEFINITION

CREATE function dbo.[UFN_QUERY_MKTEXPORTDEFINITION]
(
  @CURRENTAPPUSERID uniqueidentifier,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null
)
returns table as
return 
(
  with xmlnamespaces ('bb_appfx_queryview' as tns, 'bb_appfx_commontypes' as common),
  [GRANTEDVIEWS_CTE] ([OBJECTNAME]) as
  (
    select [QUERYVIEWCATALOG].[OBJECTNAME]
    from dbo.[QUERYVIEWCATALOG]
    where
      [QUERYVIEWCATALOG].[ID] in
      (
        select [GRANTEDVIEWS].[QUERYVIEWCATALOGID]
        from dbo.[UFN_SECURITY_GETGRANTEDQUERYVIEWSFORUSER](@CURRENTAPPUSERID) as [GRANTEDVIEWS]
      )
      or
      (
        coalesce(QUERYVIEWCATALOG.QUERYVIEWSPEC.value('(tns:QueryViewSpec/@SecurityUIDisplayFeature)[1]', 'bit'), 1) = 0 and
        coalesce(QUERYVIEWCATALOG.QUERYVIEWSPEC.value('(tns:QueryViewSpec/@UseForExportDefinitionsOnly)[1]', 'bit'), 0) = 1
      )
  )
  select
    [EXPORTDEFINITION].[ID],
    [EXPORTDEFINITION].[NAME],
    case [RECORDTYPE].[NAME]
      when 'Direct Marketing Effort Segment Member Export' then 'Direct marketing'
      when 'Marketing Acknowledgement Segment Member Export' then 'Marketing acknowledgement'
      when 'Membership Effort Segment Member Export' then 'Membership renewal'
      when 'Sponsorship Effort Segment Member Export' then 'Sponsorship'
      when 'Acknowledgement Segment Member Export' then 'Marketing acknowledgement'
    end as [MAILINGTYPE],
    cast
    (
      case
        when exists (select top 1 1 from dbo.[MKTSEGMENTATIONEXPORTPROCESS] where [MAILEXPORTDEFINITIONID] = [EXPORTDEFINITION].[ID]) then 1
        when exists (select top 1 1 from dbo.[MKTSEGMENTATIONEXPORTPROCESS] where [EMAILEXPORTDEFINITIONID] = [EXPORTDEFINITION].[ID]) then 1
        when exists (select top 1 1 from dbo.[MKTSEGMENTATIONEXPORTPROCESS] where [PHONEEXPORTDEFINITIONID] = [EXPORTDEFINITION].[ID]) then 1
        when exists (select top 1 1 from dbo.[LETTERCODE] where [EXPORTDEFINITIONID] = [EXPORTDEFINITION].[ID]) then 1
        when exists (select top 1 1 from dbo.[MKTPACKAGE] where [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] = [MKTEXPORTDEFINITION].[NETCOMMUNITYDATASOURCEID] and [MKTEXPORTDEFINITION].[NETCOMMUNITYDATASOURCEID] > 0) then 1
        when exists (select top 1 1 from dbo.[MKTPACKAGE] where [EXPORTDEFINITIONID] = [EXPORTDEFINITION].[ID]) then 1
        when exists (select top 1 1 from dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULT] where [MAILEXPORTDEFINITIONID] = [EXPORTDEFINITION].[ID] or [EMAILEXPORTDEFINITIONID] = [EXPORTDEFINITION].[ID] or [PHONEEXPORTDEFINITIONID] = [EXPORTDEFINITION].[ID])
               then 1
        else 0
      end
    as bit) as [INUSE],
    (
      select coalesce([NAME],'')
      from dbo.[MKTPOSTALTEMPLATE]
      where [ID] = [MKTEXPORTDEFINITION].[POSTALTEMPLATEID]
    ) as [POSTALTEMPLATE],
    [EXPORTDEFINITION].[DESCRIPTION],
    case [RECORDTYPE].[NAME]
      when 'Direct Marketing Effort Segment Member Export' then 0
      when 'Marketing Acknowledgement Segment Member Export' then 1
      when 'Membership Effort Segment Member Export' then 2
      when 'Sponsorship Effort Segment Member Export' then 3
      when 'Reminder Segment Member Export' then 5
      when 'Acknowledgement Segment Member Export' then 1
      else 255
    end as [MAILINGTYPECODE],
    cast
    (
      case
        when exists (select top 1 1 from dbo.[TASKCATALOG] where [ID] = '17d3a2c1-8645-4c10-bc00-f9aac5760c38') then
          case when (select top 1 len([URL]) from dbo.[NETCOMMUNITYINTEGRATION]) > 0 then 1
          else 0
        end
      else 0
    end as bit) as [NETCOMMUNITYLINKESTABLISHED],
    cast (1 as bit) as [USERCANEDIT],
    [SITE].[NAME] as [SITE],
    [EXPORTDEFINITION].[LOCKFIELDS] as [LOCKED],
    [CREATEDBY].[USERNAME] as [CREATEDBY],
    [MKTEXPORTDEFINITION].[DATEADDED] as [CREATEDON],
    [CHANGEDBY].[USERNAME] as [CHANGEDBY],
    [MKTEXPORTDEFINITION].[DATECHANGED] as [CHANGEDON]
 from dbo.[EXPORTDEFINITION]
    inner join dbo.[MKTEXPORTDEFINITION] on [MKTEXPORTDEFINITION].[EXPORTDEFINITIONID] = [EXPORTDEFINITION].[ID]
    inner join dbo.[RECORDTYPE] on [RECORDTYPE].[ID] = [EXPORTDEFINITION].[RECORDTYPEID]
    left outer join dbo.[SITE] on [SITE].[ID] = [EXPORTDEFINITION].[SITEID]
    left join dbo.[CHANGEAGENT] as [CREATEDBY] on [MKTEXPORTDEFINITION].[ADDEDBYID] = [CREATEDBY].[ID]
    left join dbo.[CHANGEAGENT] as [CHANGEDBY] on [MKTEXPORTDEFINITION].[CHANGEDBYID] = [CHANGEDBY].[ID]
  where
    (
      case [RECORDTYPE].[NAME]
        when 'Direct Marketing Effort Segment Member Export' then 1
        when 'Marketing Acknowledgement Segment Member Export' then 1
        when 'Membership Effort Segment Member Export' then 1
        when 'Sponsorship Effort Segment Member Export' then 1
        else 0
      end
    ) = 1 and
    [EXPORTDEFINITION].[ISSYSTEM] = 0 and
    (
      (
        select [ISSYSADMIN]
        from dbo.[APPUSER]
        where [ID] = @CURRENTAPPUSERID
      ) = 1 or
      -- filter out restricted query views

      not exists
      (
        select [QUERYVIEWSINUSE].[OBJECTNAME]
        from dbo.[UFN_EXPORTDEFINITION_QUERYVIEWSINUSE]([EXPORTDEFINITION].[EXPORTDEFINITIONXML]) as [QUERYVIEWSINUSE]
        where
          not exists
          (
            select [GRANTEDVIEWS].[OBJECTNAME]
            from [GRANTEDVIEWS_CTE] as [GRANTEDVIEWS]
            where [GRANTEDVIEWS].[OBJECTNAME] = [QUERYVIEWSINUSE].[OBJECTNAME]
          )
      )
    ) and
    (
      (
        select [ISSYSADMIN]
        from dbo.[APPUSER]
        where [ID] = @CURRENTAPPUSERID
      ) = 1 or
      exists
      (
        select 1
        from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID, '43db8afc-cb52-425f-9241-581dcac39d44', 10)
        where [SITEID] = [EXPORTDEFINITION].[SITEID] or ([SITEID] is null and [EXPORTDEFINITION].[SITEID] is null)
      )
    ) and
    (
      @SITEFILTERMODE = 0 or
      [EXPORTDEFINITION].[SITEID] in
      (
        select [SITEID]
        from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER](@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
      )
    )
)