USP_DATALIST_MKTEXPORTDEFINITIONS2

CREATE procedure dbo.[USP_DATALIST_MKTEXPORTDEFINITIONS2]
(
  @CURRENTAPPUSERID uniqueidentifier,
  @MAILINGTYPECODE tinyint = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null  
)
as
  declare @ISSYSADMIN bit;

  set nocount on;

  select @ISSYSADMIN = [ISSYSADMIN] from dbo.[APPUSER] where [ID] = @CURRENTAPPUSERID;

  -- filter out any export processes involving query views to which the current user does not have rights

  if @ISSYSADMIN = 0 
    begin
      declare @GRANTEDVIEWS table ([OBJECTNAME] nvarchar(128));

      with xmlnamespaces ('bb_appfx_queryview' as tns, 'bb_appfx_commontypes' as common)
      insert into @GRANTEDVIEWS
      select [QUERYVIEWCATALOG].[OBJECTNAME]
      from dbo.[QUERYVIEWCATALOG]
      where exists (
        select [GRANTEDVIEWS].[QUERYVIEWCATALOGID]
        from dbo.[UFN_SECURITY_GETGRANTEDQUERYVIEWSFORUSER](@CURRENTAPPUSERID) as [GRANTEDVIEWS]
        where [GRANTEDVIEWS].[QUERYVIEWCATALOGID] = [QUERYVIEWCATALOG].[ID])
        or (coalesce(QUERYVIEWCATALOG.QUERYVIEWSPEC.value('(tns:QueryViewSpec/@SecurityUIDisplayFeature)[1]', 'bit'), 1) = 0 and
            coalesce(QUERYVIEWCATALOG.QUERYVIEWSPEC.value('(tns:QueryViewSpec/@UseForExportDefinitionsOnly)[1]', 'bit'), 0) = 1);
    end

  select
    [EXPORTDEFINITION].[ID],
    [EXPORTDEFINITION].[NAME],
    case dbo.[UFN_MKTEXPORTDEFINITION_GETMAILINGTYPECODEFROMRECORDTYPE]([RECORDTYPE].[NAME])
      when 0 then 'Direct marketing'
      when 1 then 'Marketing acknowledgement'
      when 2 then 'Membership renewal'
      when 3 then 'Sponsorship'
    end as [MAILINGTYPE],
    convert(bit, 
      case when exists (select top 1 1 from dbo.[MKTSEGMENTATIONEXPORTPROCESS] where [MAILEXPORTDEFINITIONID] = [EXPORTDEFINITION].[ID]) or
                exists (select top 1 1 from dbo.[MKTSEGMENTATIONEXPORTPROCESS] where [EMAILEXPORTDEFINITIONID] = [EXPORTDEFINITION].[ID]) or
                exists (select top 1 1 from dbo.[MKTSEGMENTATIONEXPORTPROCESS] where [PHONEEXPORTDEFINITIONID] = [EXPORTDEFINITION].[ID]) or
                exists (select top 1 1 from dbo.[LETTERCODE] where [EXPORTDEFINITIONID] = [EXPORTDEFINITION].[ID]) or
                exists (select top 1 1 from dbo.[MKTPACKAGE] where [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] = [MKTEXPORTDEFINITION].[NETCOMMUNITYDATASOURCEID] and [MKTEXPORTDEFINITION].[NETCOMMUNITYDATASOURCEID] > 0) or 
                exists (select top 1 1 from dbo.[MKTPACKAGE] where [EXPORTDEFINITIONID] = [EXPORTDEFINITION].[ID] or
                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 [INUSE],
    dbo.[UFN_MKTPOSTALTEMPLATE_GETNAME]([MKTEXPORTDEFINITION].[POSTALTEMPLATEID]) as [POSTALTEMPLATE],
    [EXPORTDEFINITION].[DESCRIPTION],
    dbo.[UFN_MKTEXPORTDEFINITION_GETMAILINGTYPECODEFROMRECORDTYPE]([RECORDTYPE].[NAME]) as [MAILINGTYPECODE],
    dbo.[UFN_MKTNETCOMMUNITYINTEGRATION_LINKESTABLISHED]() as [NETCOMMUNITYLINKESTABLISHED],
    cast (1 as bit) as [USERCANEDIT],
    [SITE].[NAME] as [SITE],
    [EXPORTDEFINITION].[LOCKFIELDS] as [LOCKED]
  from dbo.[EXPORTDEFINITION]
  left outer join dbo.[SITE] on [SITE].[ID] = [EXPORTDEFINITION].[SITEID]
  inner join dbo.[MKTEXPORTDEFINITION] on [MKTEXPORTDEFINITION].[EXPORTDEFINITIONID] = [EXPORTDEFINITION].[ID]
  inner join dbo.[RECORDTYPE] on [RECORDTYPE].[ID] = [EXPORTDEFINITION].[RECORDTYPEID]
  where dbo.[UFN_MKTEXPORTDEFINITION_RECORDTYPEISVALIDFORMARKETING]([RECORDTYPE].[NAME]) = 1
  and [EXPORTDEFINITION].[ISSYSTEM] = 0
  and (@MAILINGTYPECODE is null or @MAILINGTYPECODE = dbo.[UFN_MKTEXPORTDEFINITION_GETMAILINGTYPECODEFROMRECORDTYPE]([RECORDTYPE].[NAME]))
  and
  (@ISSYSADMIN = 1 or
    -- filter out restricted query views

    not exists(select [QUERYVIEWSINUSE].[OBJECTNAME]
                 from dbo.[EXPORTDEFINITION] as [EXPORTDEFINITIONRIGHTSTEST]
                 outer apply dbo.[UFN_EXPORTDEFINITION_QUERYVIEWSINUSE]([EXPORTDEFINITION].[EXPORTDEFINITIONXML]) as [QUERYVIEWSINUSE]
                 where not exists (
                   select [GRANTEDVIEWS].[OBJECTNAME]
                   from @GRANTEDVIEWS as [GRANTEDVIEWS]
                   where [GRANTEDVIEWS].[OBJECTNAME] = [QUERYVIEWSINUSE].[OBJECTNAME]))
  )
  and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) 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)))

  return 0;