查询某个职责有哪些配置文件
SELECT OP.PROFILE_OPTION_ID, TL.PROFILE_OPTION_NAME, TL.USER_PROFILE_OPTION_NAME, LV.LEVEL_ID, LV.文件安全性, VA.LEVEL_VALUE, CASE WHEN VA.LEVEL_ID = 10001 THEN '地点' WHEN VA.LEVEL_ID = 10002 THEN (SELECT FAV.APPLICATION_NAME FROM FND_APPLICATION_VL FAV WHERE FAV.APPLICATION_ID = VA.LEVEL_VALUE) WHEN VA.LEVEL_ID = 10003 THEN (SELECT /* $HEADER$ */ T.RESPONSIBILITY_NAME FROM FND_RESPONSIBILITY_TL T, FND_RESPONSIBILITY B WHERE T.RESPONSIBILITY_ID = VA.LEVEL_VALUE AND T.RESPONSIBILITY_ID = B.RESPONSIBILITY_ID -- and t.responsibility_name='SYSGHO_109_GL_二级公司总帐汇总查询岗' AND B.APPLICATION_ID = T.APPLICATION_ID AND NVL(B.END_DATE, SYSDATE + 1) > SYSDATE AND NVL(B.START_DATE, SYSDATE - 1) < SYSDATE AND T.LANGUAGE = 'ZHS') WHEN VA.LEVEL_ID = 10004 THEN (SELECT USER_NAME FROM FND_USER WHERE USER_NAME NOT IN ('*ANONYMOS*', 'CONVERSION', 'INITIAL SETUP', 'FEEDER SYSTEM', 'CONCURRENT MANAGER', 'STANDALONE BATCH PROCESS') AND USER_ID = VA.LEVEL_VALUE AND NVL(END_DATE, SYSDATE + 1) > SYSDATE AND NVL(START_DATE, SYSDATE - 1) < SYSDATE) WHEN VA.LEVEL_ID = 10005 THEN(SELECT NODE_NAME FROM FND_NODES WHERE NODE_ID = VA.LEVEL_VALUE) WHEN VA.LEVEL_ID = 10006 THEN (SELECT NAME FROM HR_OPERATING_UNITS WHERE ORGANIZATION_ID = VA.LEVEL_VALUE) ELSE '' END AS PROFILE_LEVEL_VALUE, VA.PROFILE_OPTION_VALUE FROM FND_PROFILE_OPTIONS_TL TL, FND_PROFILE_OPTIONS OP, FND_PROFILE_OPTION_VALUES VA, (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 TL.LANGUAGE = 'ZHS' AND TL.PROFILE_OPTION_NAME = OP.PROFILE_OPTION_NAME AND VA.PROFILE_OPTION_ID = OP.PROFILE_OPTION_ID AND VA.LEVEL_ID = LV.LEVEL_ID and VA.LEVEL_VALUE in ( SELECT T.RESPONSIBILITY_ID FROM FND_RESPONSIBILITY_TL T, FND_RESPONSIBILITY B WHERE T.RESPONSIBILITY_ID = B.RESPONSIBILITY_ID and t.responsibility_name = 'XXX总帐会计岗' AND B.APPLICATION_ID = T.APPLICATION_ID AND NVL(B.END_DATE, SYSDATE + 1) > SYSDATE AND NVL(B.START_DATE, SYSDATE - 1) < SYSDATE AND T.LANGUAGE = 'ZHS') --AND TL.PROFILE_OPTION_NAME like '%' --AND TL.USER_PROFILE_OPTION_NAME like '%'
以下为转载参考
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; ———————————————— 版权声明：本文为CSDN博主「跳跃生命线」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/AlexLiu_2019/article/details/125168835
