How to get all Documents Types assigned to a User Group?

[Harshal_Shravgi]

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?

[William_Martin1]

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)

[A_bhi]

@William Martin  Thank you so much. I was just looking to get a report for the Usergroup assigned to Document types. Find the below mentioned query I used:

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

[Roger_Linhart]

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]