BI users: SQL to find them in Oracle Fusion

well… we went live couple of years ago and things had settled down considerably. Till one day we got a notification that few licensing conditions have been violated. so we opened our Purchase Order with Oracle and started finding the various limitations of our license agreement. Apparently there are only a finite number of users who can have BI Access. Now we needed a query to find out the BI Users or users having BI access and the role which is granting them the privilege of BI Access. so here is the query:

BI Users Query:

The below query will fetch you following details:
1. Top Role Code
2. Top Role Name
3. Top Role Id
4. Top Role GUID
5. Role Code
6. Role Name
7. Role Id
8. Role GUID

Select upper(uSername),A.* from
(
Select * from
(
SELECT CONNECT_BY_ROOT parent_role_code top_role_code
, CONNECT_BY_ROOT parent_role_name top_role_name
, CONNECT_BY_ROOT parent_role_id top_role_id
, CONNECT_BY_ROOT parent_role_guid top_role_guid
, child_role_code role_code
, child_role_name role_name
, child_role_id role_id
, child_role_guid role_guid
FROM ( SELECT pr.code parent_role_code
, pr.role_name parent_role_name
, pr.role_id parent_role_id
, pr.guid parent_role_guid
, cr.code child_role_code
, cr.role_name child_role_name
, cr.role_id child_role_id
, cr.guid child_role_guid
FROM fusion.ase_role_vl pr
, fusion.ase_role_vl cr
, fusion.ase_role_role_mbr rr
WHERE rr.effective_end_date IS NULL
AND cr.role_id = rr.parent_role_id
AND pr.role_id = rr.child_role_id ) role_hierarchy
CONNECT BY NOCYCLE PRIOR child_role_id = parent_role_id )
WHERE role_name like ‘BI%’) A,
per_roles_dn rd,
per_uSer_roleS ur,
per_uSerS u
where rd.role_id = ur.role_id
and u.uSer_id = ur.uSer_id
and rd.role_common_name = A.TOP_ROLE_CODE

Related Posts:
Query to find users having access to a module

Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.

Reference:
support.oracle.com