BI Report, Business Intelligence Report,, Oracle Fusion, Oracle Fusion Finance

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

By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
https://www.amazon.in/gp/product/B093CC1CLD?ie=UTF8&tag=oraclebytes-21&camp=3638&linkCode=xm2&creativeASIN=B093CC1CLD

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

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?