UFN_QUERY_EXPORTPROCESSLISTNOCONTEXT

CREATE function dbo.[UFN_QUERY_EXPORTPROCESSLISTNOCONTEXT]
(
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @CURRENTAPPUSERID uniqueidentifier,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null
)
returns table
as return (
  with [ISSYSADMIN] ([ISSYSADMIN]) as (
    select [ISSYSADMIN] from dbo.[APPUSER] where [ID] = @CURRENTAPPUSERID
  )
  select
    [EXPORTPROCESS].[ID],
    [EXPORTPROCESS].[NAME],
    [EXPORTPROCESS].[DESCRIPTION],
    case when [ADHOCQUERY].[ID] is not null then 'Ad-hoc query'
         when [SMARTQUERYINSTANCE].[ID] is not null then 'Smart query instance'
         else 'Export definition' end as [EXPORTTYPE],
    case when [ADHOCQUERY].[NAME] is null then case when [SMARTQUERYINSTANCE].[NAME] is null then [IDSETREGISTER].[NAME] else [SMARTQUERYINSTANCE].[NAME] end else [ADHOCQUERY].[NAME] end as [QUERYSELECTION],
    [EXPORTDEFINITION].[NAME] as [EXPORTDEFINITION],
    [BUSINESSPROCESSSTATUS].[STARTEDON] as [LASTRUNDATE],
    cast([BUSINESSPROCESSSTATUS].[ENDEDON] - [BUSINESSPROCESSSTATUS].[STARTEDON] as time) as [LASTRUNDURATION],
    case when [APPUSER].[DISPLAYNAME] = '' then [APPUSER].[USERNAME] else [DISPLAYNAME] end  as [LASTRUNBY_USERNAME],
    [BUSINESSPROCESSSTATUS].[NUMBERPROCESSED] as [LASTRUNRESULTCOUNT],
    isnull([BUSINESSPROCESSSTATUS].[STATUS], 'Not started') as [LASTRUNSTATUS],
    [BUSINESSPROCESSINSTANCE].[OWNERID] as [OWNERID],
    coalesce([SITE].[NAME], 'All sites') as [SITES],
    [EXPORTPROCESS].[SMARTQUERYINSTANCEID],
    [EXPORTPROCESS].[ADHOCQUERYID]
  from dbo.[EXPORTPROCESS]
  left outer join dbo.[ADHOCQUERY] on [EXPORTPROCESS].[ADHOCQUERYID] = [ADHOCQUERY].[ID]
  left outer join dbo.[UFN_ADHOCQUERY_APPLYQUERYVIEWSECURITY](@CURRENTAPPUSERID) as [GRANTEDADHOCQUERIES] on [GRANTEDADHOCQUERIES].[ID] = [ADHOCQUERY].[ID]
  left outer join dbo.[IDSETREGISTERADHOCQUERY] on [EXPORTPROCESS].[ADHOCQUERYID] = [IDSETREGISTERADHOCQUERY].[ADHOCQUERYID]
  left outer join dbo.[SMARTQUERYINSTANCE] on [EXPORTPROCESS].[SMARTQUERYINSTANCEID] = [SMARTQUERYINSTANCE].[ID]
  left outer join dbo.[UFN_SECURITY_GETGRANTEDSMARTQUERIESFORUSER](@CURRENTAPPUSERID) as [GRANTEDSMARTQUERIES] on [GRANTEDSMARTQUERIES].[SMARTQUERYCATALOGID] = [SMARTQUERYINSTANCE].[SMARTQUERYCATALOGID]
  left outer join dbo.[IDSETREGISTERSMARTQUERYINSTANCE] on [EXPORTPROCESS].[SMARTQUERYINSTANCEID] = [IDSETREGISTERSMARTQUERYINSTANCE].[SMARTQUERYINSTANCEID]
  left outer join dbo.[BUSINESSPROCESSINSTANCE] on [EXPORTPROCESS].[ID] = [BUSINESSPROCESSINSTANCE].[BUSINESSPROCESSPARAMETERSETID]
  left outer join dbo.[SITE] on [SITE].[ID] = [BUSINESSPROCESSINSTANCE].[SITEID]
  left outer join dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE) as [GRANTEDSITES] on [GRANTEDSITES].[SITEID] = [BUSINESSPROCESSINSTANCE].[SITEID]
  left outer join dbo.[EXPORTDEFINITION] on [EXPORTDEFINITION].[ID] = [EXPORTPROCESS].[EXPORTDEFINITIONID]
  left outer join dbo.[UFN_EXPORTDEFINITION_APPLYQUERYVIEWSECURITY](@CURRENTAPPUSERID) as [GRANTEDEXPORTDEFINITIONS] on [GRANTEDEXPORTDEFINITIONS].[ID] = [EXPORTDEFINITION].[ID]
  left outer join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [EXPORTPROCESS].[SELECTIONID]
  left outer join dbo.[IDSETREGISTERADHOCQUERY] as [IDSETREGISTERADHOCQUERY2] on [IDSETREGISTER].[ID] = [IDSETREGISTERADHOCQUERY2].[IDSETREGISTERID]
  left outer join dbo.[IDSETREGISTERSMARTQUERYINSTANCE] as [IDSETREGISTERSMARTQUERYINSTANCE2] on [IDSETREGISTER].[ID] = [IDSETREGISTERSMARTQUERYINSTANCE2].[IDSETREGISTERID]
  outer apply (
    select
      top 1
      [STARTEDON],
      [STATUS],
      [STARTEDBYUSERID],
      [ENDEDON],
      [NUMBERPROCESSED],
      [BUSINESSPROCESSPARAMETERSETID]
    from dbo.[BUSINESSPROCESSSTATUS]
    where [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSPARAMETERSETID] = [BUSINESSPROCESSINSTANCE].[BUSINESSPROCESSPARAMETERSETID]
    order by [STARTEDON] desc
  ) as [BUSINESSPROCESSSTATUS]
  left outer join dbo.[APPUSER] on [APPUSER].[ID] = [BUSINESSPROCESSSTATUS].[STARTEDBYUSERID]
  cross apply [ISSYSADMIN]
  where
  -- start security check

  (
    [ISSYSADMIN].[ISSYSADMIN] = 1
    or
    (
      case when [ADHOCQUERY].[ID] is not null then case when [GRANTEDADHOCQUERIES].[ID] is not null then 1 else 0 end else 1 end = 1
      and
      case when [SMARTQUERYINSTANCE].[ID] is not null then case when [GRANTEDSMARTQUERIES].[SMARTQUERYCATALOGID] is not null then 1 else 0 end else 1 end = 1
      and
      case when [EXPORTDEFINITION].[ID] is not null then case when [GRANTEDEXPORTDEFINITIONS].[ID] is not null then 1 else 0 end else 1 end = 1
      and
      dbo.[UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESSINSTANCE_IN_SYSTEMROLE](@CURRENTAPPUSERID, [EXPORTPROCESS].[ID]) = 1
      and
      -- check site security

      ([GRANTEDSITES].[SITEID] is not null or [BUSINESSPROCESSINSTANCE].[SITEID] is null)
    )
  )
  -- end security check

  and
  (
    @SITEFILTERMODE = 0
    or
    [BUSINESSPROCESSINSTANCE].[SITEID] in
    (
      select [SITEID]
      from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER](@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
    )
  )
)