Query to get list of Data Accesses of users – Oracle Fusion

The below query extracts the list of data accesses given for any user in Oracle Cloud Application

————————————————–***************——————————————–

SELECT gl.name "Security Context Value", pr.role_name"Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"
,'DATA ACCESS SET' "Security Context"
FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role
,fusion.gl_access_sets gl
,fusion.per_users pu
,fusion.per_roles_dn_vl pr
WHERE gl.ACCESS_SET_ID = role.ACCESS_SET_ID
AND pu.USER_GUID = role.USER_GUID
AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT bu.bu_name "Security Context Value", pr.role_name"Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"
,'BUSINESS UNIT' "Security Context"
FROM fusion.FUN_ALL_BUSINESS_UNITS_V bu
,fusion.FUN_USER_ROLE_DATA_ASGNMNTS role
,fusion.per_users pu
,fusion.per_roles_dn_vl pr
WHERE role.org_id = bu.bu_id
AND pu.USER_GUID = role.USER_GUID
AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT led.NAME "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"
,'LEDGERS' "Security Context"
FROM fusion.GL_LEDGERS led,
fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.per_users pu,
fusion.per_roles_dn_vl pr
WHERE role.LEDGER_ID = led.LEDGER_ID
AND pu.USER_GUID = role.USER_GUID
AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT book.book_type_name "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"
,'ASSET BOOK' "Security Context"
FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.FA_BOOK_CONTROLS book,
fusion.per_users pu,
fusion.per_roles_dn_vl pr
WHERE book.BOOK_CONTROL_ID = role.book_id
AND pu.USER_GUID = role.USER_GUID
AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT interco.INTERCO_ORG_NAME "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"
,'INTERCOMPANY ORGANIZATION' "Security Context"
FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.FUN_INTERCO_ORGANIZATIONS interco,
fusion.per_users pu,
fusion.per_roles_dn_vl pr
WHERE interco.INTERCO_ORG_ID= role.INTERCO_ORG_ID
AND pu.USER_GUID = role.USER_GUID
AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT cost.COST_ORG_NAME "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"
,'COST ORGANIZATION' "Security Context"
FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.CST_COST_ORGS_V cost,
fusion.per_users pu,
fusion.per_roles_dn_vl pr
WHERE cost.COST_ORG_ID= role.CST_ORGANIZATION_ID
AND pu.USER_GUID = role.USER_GUID
AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT mfg.DEF_SUPPLY_SUBINV "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"
,'MANUFACTURING PLANT' "Security Context"
FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.RCS_MFG_PARAMETERS mfg,
fusion.per_users pu,
fusion.per_roles_dn_vl pr
WHERE mfg.ORGANIZATION_ID= role.MFG_ORGANIZATION_ID
AND pu.USER_GUID = role.USER_GUID
AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT budget.NAME "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"
,'CONTROL BUDGET' "Security Context"
FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.XCC_CONTROL_BUDGETS budget,
fusion.per_users pu,
fusion.per_roles_dn_vl pr
WHERE budget.CONTROL_BUDGET_ID = role.CONTROL_BUDGET_ID
AND pu.USER_GUID = role.USER_GUID
AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT st.SET_NAME "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"
,'REFERENCE DATA SET' "Security Context"
FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.FND_SETID_SETS_VL st,
fusion.per_users pu,
fusion.per_roles_dn_vl pr
WHERE st.SET_ID = role.SET_ID
AND pu.USER_GUID = role.USER_GUID
AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT inv.ORGANIZATION_CODE "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"
,'INVENTORY ORGANIZATION' "Security Context"
FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.INV_ORG_PARAMETERS inv,
fusion.per_users pu,
fusion.per_roles_dn_vl pr
WHERE inv.ORGANIZATION_ID = role.INV_ORGANIZATION_ID
AND pu.USER_GUID = role.USER_GUID
AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT hr.CLASSIFICATION_CODE "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"
,'PROJECT ORGANIZATION CLASSIFICATION' "Security Context"
FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,
fusion.HR_ORG_UNIT_CLASSIFICATIONS_F hr,
fusion.per_users pu,
fusion.per_roles_dn_vl pr
WHERE hr.ORG_UNIT_CLASSIFICATION_ID = role.ORG_ID
AND pu.USER_GUID = role.USER_GUID
AND pr.ROLE_COMMON_NAME = role.ROLE_NAME