SQL Query to Extract Data Access for all Security Context Types

The following SQL queries can be used to get data from the Manage Data Access for Users task. (i.e) Security Context Types & Values and User names and the roles assigned to them.

By running the below-mentioned queries data can be extracted from the Oracle application for each of the security context types.

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, per.username

rl.ROLE_NAME

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, per.username,

rl.ROLE_NAME

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= role.INTERCO_ORG_ID

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.rl_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

Run these SQL queries separately to get better results.

Leave a Reply

Your email address will not be published.