UFN_QUERY_GLOBALCHANGELIST

CREATE function dbo.[UFN_QUERY_GLOBALCHANGELIST]
(
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null
)
returns table
as return (
  with [ISSYSADMIN] ([ISSYSADMIN]) as (
    select [ISSYSADMIN] from dbo.[APPUSER] where [ID] = @CURRENTAPPUSERID
  )
  select
    [GLOBALCHANGE].[ID],
    [GLOBALCHANGE].[NAME],
    [GLOBALCHANGECATALOG].[DISPLAYNAME] as [TYPE],
    [GLOBALCHANGE].[LASTRUNON],
    isnull([SITE].[NAME], 'All sites') as [SITE],
    case when [OWNER].[DISPLAYNAME] = '' then [OWNER].[USERNAME] else [OWNER].[DISPLAYNAME] end as [OWNER],
    [GLOBALCHANGECATALOG].[ID] as [GLOBALCHANGECATALOGID],
    isnull([GLOBALCHANGE].[GLOBALCHANGEDATAFORMITEM], cast('' as xml)) as [DATAFORMITEM],
    [BUSINESSPROCESSINSTANCE].[OWNERID] as [OWNERID]
  from
    dbo.[GLOBALCHANGE]
    inner join dbo.[GLOBALCHANGECATALOG] on [GLOBALCHANGECATALOG].[ID] = [GLOBALCHANGE].[GLOBALCHANGECATALOGID]
    left outer join dbo.[BUSINESSPROCESSINSTANCE] on [BUSINESSPROCESSINSTANCE].[BUSINESSPROCESSPARAMETERSETID] = [GLOBALCHANGE].[ID]
    left outer join dbo.[APPUSER] as [OWNER] on [OWNER].[ID] = [BUSINESSPROCESSINSTANCE].[OWNERID]
    left outer join dbo.[SITE] on [SITE].[ID] = [BUSINESSPROCESSINSTANCE].[SITEID]
    cross apply [ISSYSADMIN]
  where (
      [ISSYSADMIN].[ISSYSADMIN] = 1
      or (
        -- check feature security

        dbo.[UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESSINSTANCE_IN_SYSTEMROLE](@CURRENTAPPUSERID, [GLOBALCHANGE].[ID]) = 1
        -- check site security

        and (
          select
            count(1)
          from
            dbo.[UFN_SITEID_MAPFROM_GLOBALCHANGEPROCESSID]([GLOBALCHANGE].[ID]) as [GLOBALCHANGESITE]
          where
            exists (select top 1 1 from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID, 'a146482f-02bc-4558-825a-6678556468df', 10) where [SITEID] = [GLOBALCHANGESITE].[SITEID] or ([SITEID] is null and [GLOBALCHANGESITE].[SITEID] is null))
        ) > 0
      )
    )
    -- apply site filter

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