SQL Query to Extract User Data Access Details in Oracle fusion

SQL Query to Extract User Data Access Details in Oracle fusion

Hi Friends, we are going to discuss about the sql  Query to Extract User Data Access Details in Oracle fusion. We will share the detail sql query which helps to extract the Data access details for the user accounts in oracle fusion. Using this sql query we can develop the User and its data access report in oracle fusion. In Oracle fusion, We do provide the User data access in multiple levels. We do provide the data access for Ledgers , For FA Books and For Business Units and some other levels too. So in this post , We will share many sql queries which helps to extract the user data access for multiple levels. This is one of the most important sql query about user and its data access in the oracle fusion application.

As below Select the User Name and Role and Security Type like ‘Business Unit’ & ‘Ledger’ many more.

SQL Query to Extract User Data Access Details in Oracle fusion

11 Important Tables about User Data Access Details in Oracle Fusion

1.FUN_ROLE_DATA_SECURITY_MAPPING

2.FUN_USER_ROLE_DATA_ASGNMNTS

3.GL_ACCESS_SETS

4.PER_USERS

5.FUN_ALL_BUSINESS_UNITS_V

6.FUN_USER_ROLE_DATA_ASGNMNTS

7.FA_BOOK_CONTROLS

8.FUN_INTERCO_ORGANIZATIONS

9.CST_COST_ORGS_V

10.RCS_MFG_PARAMETERS

11.XCC_CONTROL_BUDGETS

The table for all the security context type is

select * from fusion.FUN_ROLE_DATA_SECURITY_MAPPING             -->IN this table there is security context which is null and I checked the forums and it says it is expected behavior however the object_name gives all the security_context types we have

select distinct object_name from fusion.FUN_ROLE_DATA_SECURITY_MAPPING

select  gl.name,
role.ROLE_NAME, pu.username
from fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.gl_access_sets gl,
fusion.per_users pu
where gl.ACCESS_SET_ID = role.ACCESS_SET_ID
and pu.USER_GUID = role.USER_GUID

select led.NAME, role.role_name, pu.username
from fusion.GL_LEDGERS led,
fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.per_users pu
where role.LEDGER_ID = led.LEDGER_ID
and pu.USER_GUID = role.USER_GUID

select book.book_type_name , role.ROLE_NAME, pu.username
from fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.FA_BOOK_CONTROLS book,
fusion.per_users pu
where book.BOOK_CONTROL_ID = role.book_id
and pu.USER_GUID = role.USER_GUID

select bu.bu_name,
role.ROLE_NAME, pu.username
from fusion.FUN_ALL_BUSINESS_UNITS_V bu,
fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.per_users pu
where role.org_id = bu.bu_id
and pu.USER_GUID = role.USER_GUID

select interco.INTERCO_ORG_NAME , role.ROLE_NAME, pu.username
from fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.FUN_INTERCO_ORGANIZATIONS interco,
fusion.per_users pu
where interco.INTERCO_ORG_ID= role.INTERCO_ORG_ID
and pu.USER_GUID = role.USER_GUID

select cost.COST_ORG_NAME, role.ROLE_NAME, pu.username
from fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.CST_COST_ORGS_V cost,
fusion.per_users pu
where cost.COST_ORG_ID= role.CST_ORGANIZATION_ID
and pu.USER_GUID = role.USER_GUID

select mfg.DEF_SUPPLY_SUBINV, role.ROLE_NAME, pu.username
from fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.RCS_MFG_PARAMETERS mfg,
fusion.per_users pu
where mfg.ORGANIZATION_ID= role.MFG_ORGANIZATION_ID
and pu.USER_GUID = role.USER_GUID

select inv.ORGANIZATION_CODE, role.ROLE_NAME, pu.username
from fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.INV_ORG_PARAMETERS inv,
fusion.per_users pu
where inv.ORGANIZATION_ID = role.INV_ORGANIZATION_ID
and pu.USER_GUID = role.USER_GUID

select hr.CLASSIFICATION_CODE, role.ROLE_NAME, pu.username
from fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.HR_ORG_UNIT_CLASSIFICATIONS_F hr,
fusion.per_users pu
where hr.ORG_UNIT_CLASSIFICATION_ID = role.ORG_ID
and pu.USER_GUID = role.USER_GUID

select budget.NAME, role.ROLE_NAME, pu.username
from fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.XCC_CONTROL_BUDGETS budget,
fusion.per_users pu
where budget.CONTROL_BUDGET_ID = role.CONTROL_BUDGET_ID
and pu.USER_GUID = role.USER_GUID

select st.SET_NAME, role.ROLE_NAME, pu.username
from fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.FND_SETID_SETS_VL st,
fusion.per_users pu
where st.SET_ID = role.SET_ID
and pu.USER_GUID = role.USER_GUID