Oracle EBS菜单、请求组、配置文件与职责的查询和分配SQL
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;
--刘轶鹤转自网络