USP_DATALIST_SELECTIONSBYQUERYFOLDER

CREATE procedure dbo.USP_DATALIST_SELECTIONSBYQUERYFOLDER
(
    @FOLDERID uniqueidentifier,    
  @RECORDTYPEID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @IDSETTOEXCLUDE uniqueidentifier = null
)
as
    set nocount on;    

  set @FOLDERID = isnull(@FOLDERID,'00000000-0000-0000-0000-000000000000')

  select
      ID,
      NAME,
      0 as TYPE,
      DISPLAYORDER
  from 
      dbo.UFN_ADHOCQUERYFOLDERS_GETUSERSFOLDERS(@CURRENTAPPUSERID)    
  where
      isnull(PARENTFOLDERID,'00000000-0000-0000-0000-000000000000') = @FOLDERID
  union
  select     
      IDSETREGISTER.ID,
      IDSETREGISTER.NAME,
      1 as TYPE,
      ROW_NUMBER() over (order by IDSETREGISTER.NAME) AS 'DISPLAYORDER'
  from 
      dbo.IDSETREGISTER 
      left outer join dbo.IDSETREGISTERADHOCQUERY on IDSETREGISTER.ID = IDSETREGISTERADHOCQUERY.IDSETREGISTERID
      left outer join dbo.ADHOCQUERY on IDSETREGISTERADHOCQUERY.ADHOCQUERYID = ADHOCQUERY.ID
      left outer join dbo.IDSETREGISTERSMARTQUERYINSTANCE on IDSETREGISTER.ID = IDSETREGISTERSMARTQUERYINSTANCE.IDSETREGISTERID
      left outer join dbo.SMARTQUERYINSTANCE on IDSETREGISTERSMARTQUERYINSTANCE.SMARTQUERYINSTANCEID = SMARTQUERYINSTANCE.ID
  where
    ACTIVE = 1 and
      IDSETREGISTER.DBOBJECTNAME not in(select SQLFUNCTIONCATALOG.FUNCTIONNAME from dbo.SQLFUNCTIONCATALOG) and
    GROUPNAME is null and  
    coalesce(ADHOCQUERY.FOLDERID, SMARTQUERYINSTANCE.FOLDERID,'00000000-0000-0000-0000-000000000000') = @FOLDERID and
    IDSETREGISTER.RECORDTYPEID = @RECORDTYPEID and
    IDSETREGISTER.ID not in (select IDSETREGISTERID from dbo.IDSETREGISTERMERGE) and
    IDSETREGISTER.ID <> isnull(@IDSETTOEXCLUDE,'00000000-0000-0000-0000-000000000000') and
    IDSETREGISTER.ISSYSTEM = 0
  ORDER BY
      3,4