Featured post

General Ledger Revaluation

General Ledger Revaluation Account balances denominated in foreign currencies are adjusted through the revaluation procedure. Revaluat...

Wednesday, 25 August 2021

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

    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

No comments:

Post a Comment

Please review my topic and update your comments

Note: only a member of this blog may post a comment.