USP_DATALIST_IMPORTSELECTIONS

List of all import selections.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@NAME	nvarchar(100)	IN	Name
@RECORDTYPEID	uniqueidentifier	IN	Type
@QUERYCATEGORYCODEID	uniqueidentifier	IN	Category
@ONLYSHOWMYSELECTIONS	bit	IN	Only show my selections
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.

Definition

Copy



CREATE procedure dbo.[USP_DATALIST_IMPORTSELECTIONS]
(
  @CURRENTAPPUSERID uniqueidentifier,
  @NAME nvarchar(100) = null, 
  @RECORDTYPEID uniqueidentifier = null,
  @QUERYCATEGORYCODEID uniqueidentifier = null,
  @ONLYSHOWMYSELECTIONS bit = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null
)
as
  set nocount on;

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

  select
    [IMPORTSELECTIONPROCESS].[ID],
    [IMPORTSELECTIONPROCESS].[NAME],
    isnull([RECORDTYPE].[NAME], '') as [RECORDTYPE],
    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 ((@RECORDTYPEID is null) or ([IMPORTSELECTIONPROCESS].[RECORDTYPEID] = @RECORDTYPEID))
  and ((@QUERYCATEGORYCODEID is null) or ([IMPORTSELECTIONPROCESS].[QUERYCATEGORYCODEID] = @QUERYCATEGORYCODEID))
  and ((@NAME is null or @NAME = '') or ([IMPORTSELECTIONPROCESS].[NAME] like '%' + @NAME + '%'))
  and ((@ONLYSHOWMYSELECTIONS is null or @ONLYSHOWMYSELECTIONS = 0) or (@ONLYSHOWMYSELECTIONS = 1 and [IMPORTSELECTIONPROCESS].[OWNERID] = @CURRENTAPPUSERID))
  and ((@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 0;