You can utilize the subsequent SQL queries to retrieve information from the “Manage Data Access for Users” task, which encompasses Security Context Types and values, User names, and their associated roles.
Executing the provided queries enables the extraction of data from the Oracle application for each specific security context type.
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
Advertisement
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