UFN_QUERY_IMPORTSELECTIONS

CREATE function dbo.[UFN_QUERY_IMPORTSELECTIONS]
(
    @CURRENTAPPUSERID uniqueidentifier = null,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null
)
returns @SELECTIONS table 
(
  [ID] uniqueidentifier,
  [NAME] nvarchar(255),
    [RECORDTYPEID] uniqueidentifier,
  [RECORDTYPE] nvarchar(50),
    [CATEGORYID] uniqueidentifier,
  [CATEGORY] nvarchar(100),
  [USEINQUERYDESIGNER] bit,
  [STATUSCODE] tinyint,
  [STATUS] nvarchar(50),  
  [DESCRIPTION] nvarchar(1024),
  [OWNERID] uniqueidentifier,
  [OWNER] nvarchar(128),
  [SITE] nvarchar(255),
  [IDSETREGISTERID] uniqueidentifier
)
with execute as caller as
begin

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

  insert into @SELECTIONS
  (
    [ID],
    [NAME],
        [RECORDTYPEID],
    [RECORDTYPE],
        [CATEGORYID],
    [CATEGORY],
    [USEINQUERYDESIGNER],
    [STATUSCODE],
    [STATUS],
    [DESCRIPTION],
    [OWNERID],
    [OWNER],
    [SITE],
    [IDSETREGISTERID]
  )
  select
    [IMPORTSELECTIONPROCESS].[ID],
    [IMPORTSELECTIONPROCESS].[NAME],
        [RECORDTYPE].[ID] as [RECORDTYPEID],
    isnull([RECORDTYPE].[NAME], '') as [RECORDTYPE],
        isnull([QUERYCATEGORYCODE].[ID], '00000000-0000-0000-0000-000000000001') as [CATEGORYID],
    isnull([QUERYCATEGORYCODE].[DESCRIPTION], '') as [CATEGORY],
    [IMPORTSELECTIONPROCESS].[USEINQUERYDESIGNER],
    [IMPORTSELECTIONPROCESS].[STATUSCODE],
    [IMPORTSELECTIONPROCESS].[STATUS],
    [IMPORTSELECTIONPROCESS].[DESCRIPTION],
    [IMPORTSELECTIONPROCESS].[OWNERID],
    [APPUSER].[USERNAME] as [OWNER],
    isnull([SITE].[NAME], 'All sites') as [SITE],
    [IMPORTSELECTIONPROCESS].[IDSETREGISTERID]
  from dbo.[IMPORTSELECTIONPROCESS]
  left join dbo.[RECORDTYPE] on [RECORDTYPE].[ID] = [IMPORTSELECTIONPROCESS].[RECORDTYPEID]
  left join dbo.[QUERYCATEGORYCODE] on [QUERYCATEGORYCODE].[ID] = [IMPORTSELECTIONPROCESS].[QUERYCATEGORYCODEID]
  left join dbo.[SITE] on [SITE].[ID] = [IMPORTSELECTIONPROCESS].[SITEID]
  left join dbo.[APPUSER] on [APPUSER].[ID] = [IMPORTSELECTIONPROCESS].[OWNERID]
  where 
    (
      (@ISSYSADMIN = 1) or 
      (@CURRENTAPPUSERID is null) or 
      ([IMPORTSELECTIONPROCESS].[IDSETREGISTERID] is null) or 
      (dbo.[UFN_SECURITY_APPUSER_GRANTED_SELECTION_IN_SYSTEMROLE](@CURRENTAPPUSERID, [IMPORTSELECTIONPROCESS].[IDSETREGISTERID]) = 1)
    )
    and
    (
      [IMPORTSELECTIONPROCESS].[SITEID] is null 
      or
      (dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where [SITEID]=[IMPORTSELECTIONPROCESS].[SITEID] or ([SITEID] is null and [IMPORTSELECTIONPROCESS].[SITEID] is null)))
    )
    and 
    (
      @SITEFILTERMODE = 0
      or 
      [IMPORTSELECTIONPROCESS].[SITEID] in (select [SITEID] from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER](@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
    );

  return;
end