In Oracle Fusion Applications, understanding user data access across different security context types is essential for governance and compliance. The following SQL queries help retrieve information from the “Manage Data Access for Users” task, including Security Context Types, Usernames, and their Associated Roles.
Each query corresponds to a specific Security Context Type, providing insights into user-level data access configurations.
Asset Book
SELECT book.book_type_name, rl.ROLE_NAME, per.username
FROM fu.FUN_USER_ROLE_DATA_ASGNMNTS rl,
fu.FA_BOOK_CONTROLS book,
fu.per_users per
WHERE book.BOOK_CONTROL_ID = rl.book_id
AND per.USER_GUID = rl.USER_GUID;
Business Unit
SELECT bu.bu_name, rl.ROLE_NAME, per. username
FROM fu.FUN_ALL_BUSINESS_UNITS_V bu,
fu.FUN_USER_ROLE_DATA_ASGNMNTS rl,
fu.per_users per
WHERE rl.org_id = bu.bu_id
AND per.USER_GUID = rl.USER_GUID;
Control Budget
SELECT budget.NAME, rl.ROLE_NAME, per. username
FROM fu.FUN_USER_ROLE_DATA_ASGNMNTS rl,
fu.XCC_CONTROL_BUDGETS budget,
fu.per_users per
WHERE budget.CONTROL_BUDGET_ID = rl.CONTROL_BUDGET_ID
AND per.USER_GUID = rl.USER_GUID;
Cost Organization
SELECT cost.COST_ORG_NAME, rl.ROLE_NAME, per.username
FROM fu.FUN_USER_ROLE_DATA_ASGNMNTS rl,
fu.CST_COST_ORGS_V cost,
fu.per_users per
WHERE cost.COST_ORG_ID = rl.CST_ORGANIZATION_ID
AND per.USER_GUID = rl.USER_GUID;
Data Access Set
SELECT led. name, rl.ROLE_NAME, per. username
FROM fu.FUN_USER_ROLE_DATA_ASGNMNTS rl,
fu.gl_access_sets led,
fu.per_users per
WHERE led.ACCESS_SET_ID = rl.ACCESS_SET_ID
AND per.USER_GUID = rl.USER_GUID;
Intercompany Organization
SELECT ic.INTERCO_ORG_NAME, rl.ROLE_NAME, per. username
FROM fu.FUN_USER_ROLE_DATA_ASGNMNTS rl,
fu.FUN_INTERCO_ORGANIZATIONS ic,
fu.per_users per
WHERE ic.INTERCO_ORG_ID = rl.INTERCO_ORG_ID
AND per.USER_GUID = rl.USER_GUID;
Inventory Organization
SELECT inv.ORGANIZATION_CODE, rl.ROLE_NAME, per. username
FROM fu.FUN_USER_ROLE_DATA_ASGNMNTS rl,
fu.INV_ORG_PARAMETERS inv,
fu.per_users per
WHERE inv.ORGANIZATION_ID = rl.INV_ORGANIZATION_ID
AND per.USER_GUID = rl.USER_GUID;
Ledgers
SELECT led.NAME, rl.ROLE_NAME, per. username
FROM fu.GL_LEDGERS led,
fu.FUN_USER_ROLE_DATA_ASGNMNTS rl,
fu.per_users per
WHERE rl.LEDGER_ID = led.LEDGER_ID
AND per.USER_GUID = rl.USER_GUID;
Manufacturing Plant
SELECT mfg.DEF_SUPPLY_SUBINV, rl.ROLE_NAME, per. username
FROM fu.FUN_USER_ROLE_DATA_ASGNMNTS rl,
fu.RCS_MFG_PARAMETERS mfg,
fu.per_users per
WHERE mfg.ORGANIZATION_ID = rl.MFG_ORGANIZATION_ID
AND per.USER_GUID = rl.USER_GUID;
Project Organization Classification
SELECT hr.CLASSIFICATION_CODE, rl.ROLE_NAME, per. username
FROM fu.FUN_USER_ROLE_DATA_ASGNMNTS rl,
fu.HR_ORG_UNIT_CLASSIFICATIONS_F hr,
fu.per_users per
WHERE hr.ORG_UNIT_CLASSIFICATION_ID = rl.ORG_ID
AND per.USER_GUID = rl.USER_GUID;
Reference Data Set
SELECT st.SET_NAME, rl.ROLE_NAME, per. username
FROM fu.FUN_USER_ROLE_DATA_ASGNMNTS rl,
fu.FND_SETID_SETS_VL st,
fu.per_users per
WHERE st.SET_ID = rl.SET_ID
AND per.USER_GUID = rl.USER_GUID;
Note:
Execute each SQL query individually to ensure optimal performance and accurate results.