[转自]https://blog.csdn.net/AlexLiu_2019/article/details/125168835
1. 菜单查询
–菜单查询
SELECT fm.MENU_NAME 菜单名,
fm.TYPE 菜单类型,
fmev.ENTRY_SEQUENCE 序号,
fmev.PROMPT 显示名称,
fmev.DESCRIPTION 描述,
fffv.function_name 功能名,
fffv.USER_FUNCTION_NAME 用户功能名
FROM fnd_menu_entries_vl fmev,
fnd_form_functions_vl fffv,
fnd_menus fm
WHERE 1 = 1
AND fmev.menu_id = fm.menu_id
AND fffv.function_id(+) = fmev.function_id
AND fm.MENU_NAME like ‘%gl_inquiry%’;
2. 请求组查询
–请求组查询
SELECT frg.REQUEST_GROUP_NAME 请求组名字,
frg.REQUEST_GROUP_CODE 请求组代码,
fa1.application_name 请求组应用产品,
frg.DESCRIPTION 请求组描述,
frgu.REQUEST_UNIT_TYPE 请求类型,
–此代码必然为P（请求），没有写查请求集的方法，后续补充
fcp.USER_CONCURRENT_PROGRAM_NAME 请求名字,
fa2.application_name 请求应用产品
FROM FND_REQUEST_GROUPS frg,
FND_REQUEST_GROUP_UNITS frgu,
fnd_application_vl fa1,
fnd_application_vl fa2,
FND_CONCURRENT_PROGRAMS_VL fcp
WHERE 1=1
AND frg.REQUEST_GROUP_NAME like ‘%%’
–请求组名字，不填查出全部
AND frgu.REQUEST_GROUP_ID = frg.REQUEST_GROUP_ID
AND fa1.application_id = frg.application_id
AND fa2.application_id = frgu.application_id
AND frgu.REQUEST_UNIT_ID = fcp.CONCURRENT_PROGRAM_ID;
3. 配置文件查询：
–查询系统中配置文件的创建情况
SELECT PROFILE_OPTION_NAME 配置文件名,
USER_PROFILE_OPTION_NAME 用户配置文件名,
DESCRIPTION 说明,
hierarchy_type 层次结构类型,
SITE_ENABLED_FLAG 地点可见,
SITE_UPDATE_ALLOWED_FLAG 地点可更新,
app_enabled_flag 应用产品可见,
app_update_allowed_flag 应用产品可更新,
RESP_ENABLED_FLAG 责任可见,
RESP_UPDATE_ALLOWED_FLAG 责任可更新,
SERVER_ENABLED_FLAG 服务器可见,
SERVER_UPDATE_ALLOWED_FLAG 服务器可更新,
SERVERRESP_ENABLED_FLAG 服务器职责可见,
SERVERRESP_UPDATE_ALLOWED_FLAG 服务器职责可更新,
ORG_ENABLED_FLAG 组织可见,
ORG_UPDATE_ALLOWED_FLAG 组织可更新,
USER_ENABLED_FLAG 用户可见,
USER_UPDATE_ALLOWED_FLAG 用户可更新,
start_date_active 有效起始日期,
END_DATE_ACTIVE 有效截止日期,
USER_VISIBLE_FLAG 用户访问可查看,
USER_CHANGEABLE_FLAG 用户访问可更新,
READ_ALLOWED_FLAG 可读,
WRITE_ALLOWED_FLAG 可写,
SQL_VALIDATION SQL验证,
PROFILE_OPTION_ID 配置文件配置情况ID
FROM FND_PROFILE_OPTIONS_VL
WHERE PROFILE_OPTION_NAME LIKE ‘%%’
4. 菜单、请求组与职责关联查询：
SELECT frv.responsibility_name 职责名,
frv.responsibility_key 职责代码,
fa.application_name 应用产品,
fm.MENU_NAME 菜单名,
frg.REQUEST_GROUP_NAME 请求组名
FROM FND_RESPONSIBILITY_VL frv,
fnd_application_vl fa,
fnd_menus fm,
FND_REQUEST_GROUPS frg
WHERE 1 = 1
AND frg.REQUEST_GROUP_ID(+) = frv.REQUEST_GROUP_ID
AND fm.MENU_ID = frv.MENU_ID
AND fa.application_id = frv.application_id;
5. 配置文件与职责关联查询：（不同配置文件需要单独写的，否则只能取到value，不能看见对应的值）
–配置文件与职责
SELECT fst.responsibility_name 职责名,
fpo.profile_option_name 配置文件名,
tl.user_profile_option_name 用户配置文件名,
lv.文件安全性 配置文件层级,
fpv.level_value 配置文件值,
gas.NAME 配置文件值对应含义
FROM fnd_profile_options fpo,
fnd_profile_option_values fpv,
fnd_profile_options_tl tl,
fnd_responsibility_tl fst,
gl_access_sets gas,
(SELECT 10001 level_id, ‘地点’ 文件安全性
FROM dual
UNION
SELECT 10002 level_id, ‘应用产品’ 文件安全性
FROM dual
UNION
SELECT 10003 level_id, ‘责任’ 文件安全性
FROM dual
UNION
SELECT 10004 level_id, ‘用户’ 文件安全性
FROM dual
UNION
SELECT 10005 level_id, ‘服务器’ 文件安全性
FROM dual
UNION
SELECT 10006 level_id, ‘组织’ 文件安全性
FROM dual) lv
WHERE 1 = 1
AND fpv.profile_option_id = fpo.profile_option_id
AND tl.language = ‘ZHS’
AND tl.profile_option_name = fpo.profile_option_name
AND tl.user_profile_option_name LIKE ‘%数据访问权限集%’
AND gas.ACCESS_SET_ID = fpv.PROFILE_OPTION_VALUE --数据访问权限集ID
AND lv.level_id = fpv.LEVEL_ID
AND fpv.level_value = fst.responsibility_id
AND fpv.application_id = fst.application_id
AND fst.language = ‘ZHS’;
6. 职责
SELECT distinct fst.responsibility_name 职责名
FROM fnd_responsibility_tl fst
WHERE 1 = 1
AND fst.language = ‘ZHS’
AND fst.RESPONSIBILITY_NAME like ‘%%’;
7. 用户与职责
SELECT distinct wur.user_name 用户名,fst.responsibility_name 职责名
FROM fnd_responsibility_tl fst,wf_all_user_roles wur
WHERE 1 = 1
AND fst.language = ‘ZHS’
– AND fst.RESPONSIBILITY_NAME like ‘%%’
–职责名字范围
AND fst.RESPONSIBILITY_ID = wur.role_orig_system_id
– AND wur.user_name in (’’,’’)
–用户名范围
order by wur.user_name;
