03-15-2018 01:25 PM
We are using OnBase 15 and our DB is Oracle 10g. I want the SQL to get all document types assigned to user group. I have written below query:
SELECT distinct itmg.itemtypegroupname AS DocTypeGroup, dt.itemtypename AS DocTypeName
FROM hsi.useritemtypegrp ugig INNER JOIN
hsi.usergroup ug ON ugig.usergroupnum = ug.usergroupnum INNER JOIN
hsi.itemtypegroup itmg ON ugig.itemtypegroupnum = itmg.itemtypegroupnum INNER JOIN
hsi.doctype dt ON itmg.itemtypegroupnum = dt.itemtypegroupnum --INNER JOIN
Where ugig.usergroupnum=101
ORDER BY DocTypeGroup
This gives all document types for the document type group but in OnBase Configuration, we could select only specific document types of a document type group in assignment to User Group. Is there a table having relation between User Group and Document Type?
03-16-2018 12:18 PM
Use hsi.usergroupconfig as the linking table between Usergroup and doctype.
Here is how I found that table...
USE Master
Select *
from (SELECT TABLE_NAME
, COLUMN_NAME
FROM onbase.INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'itemtypenum') item
inner join (SELECT TABLE_NAME
, COLUMN_NAME
FROM onbase.INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like 'usergroupnum') usergroup
on item.TABLE_NAME = usergroup.TABLE_NAME
Then I found the correct table from the results. (one of those tables has 0's for the itemtype value, so it was obviously not the correct table)
10-27-2020 06:56 AM
select D.itemtypename,UA.username from hsi.doctype D
JOIN hsi.usergroupconfig UG ON D.itemtypenum = UG.itemtypenum
JOIN hsi.userxusergroup UUG ON UG.usergroupnum = UUG.usergroupnum
JOIN hsi.useraccount UA ON UUG.usernum = UA.usernum
10-27-2020 08:21 AM
Others have posted working solutions so I'm only including the script I use to show my style. This is one of the things I appreciate about this community, seeing the way other admins structure their queries. For example, we're on a Microsoft SQL Server database and op mentioned they're on Oracle. I'm going to begin adding that to the comments in my scripts since some of my queries might not work as expected with Oracle.
/* DocTypesInUserGroups.sql * * Roger D. Linhart 07/24/2015 OnBase 14.0.2.137 Microsoft SQL Server 2012 * * Show DocTypeGroups and DocTypes In UserGroup(s). * * To list all UserGroups: * --SET @myUserGroupName = 'UserGroupName'; * * To list a specific UserGroup: * SET @myUserGroupName = 'UserGroupName'; * * IMPORTANT: Remember to select the target database instance. * */DECLARE @myUserGroupName nvarchar(128) = '';-- SET @myUserGroupName = 'UserGroupName';SET @myUserGroupName += '%';SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECT RTRIM(ug.usergroupname) AS [User Group] ,RTRIM(dt.itemtypename) AS [Document Type]FROM hsi.usergroupconfig AS x WITH (NOLOCK) INNER JOIN hsi.doctype AS dt WITH (NOLOCK) ON x.itemtypenum = dt.itemtypenum INNER JOIN hsi.usergroup AS ug WITH (NOLOCK) ON x.usergroupnum = ug.usergroupnumWHERE ug.usergroupname LIKE @myUserGroupNameORDER BY [User Group] ,[Document Type]
Find what you came for
We want to make your experience in Hyland Connect as valuable as possible, so we put together some helpful links.