select aa.menu_name, aa.user_menu_name, aa.type,
aa.description, aa.ENTRY_SEQUENCE, aa.prompt,
aa.sub_user_menu_name, aa.user_function_name,
aa.description1 from (
SELECT DISTINCT fmv.menu_name, fmv.user_menu_name,
‘标准‘ type,
fmv.description, fme.ENTRY_SEQUENCE, fme.prompt,
sub_fmv.user_menu_name sub_user_menu_name, fff.user_function_name,
fme.description description1
FROM
fnd_menu_entries_vl fme,
fnd_menus_vl fmv,
fnd_menus_vl sub_fmv,
fnd_form_functions_vl fff
WHERE
--fmv.MENU_NAME = ‘WB_INV_WIP‘
and
fmv.menu_id
= fme.menu_id
AND
fme.sub_menu_id = sub_fmv.menu_id(+)
AND
fme.function_id = fff.function_id(+)
AND
fmv.menu_id IN (
SELECT DISTINCT b.menu_id
FROM apps.fnd_application_vl fa,
apps.fnd_responsibility_vl b,
apps.fnd_menus_vl fmv,
applsys.fnd_request_groups frg,
apps.fnd_application_vl fa3,
apps.fnd_application_vl fa2
WHERE b.responsibility_name IN (SELECT col
FROM able_test)
AND b.application_id = fa.application_id
AND b.menu_id = fmv.menu_id(+)
AND b.data_group_application_id = fa3.application_id(+)
AND b.request_group_id = frg.request_group_id(+)
AND frg.application_id = fa2.application_id(+))) aa
order by
menu_name
Oracle EBS-SQL (SYS-9):职责使用菜单.sql,布布扣,bubuko.com
Oracle EBS-SQL (SYS-9):职责使用菜单.sql
原文：http://www.cnblogs.com/st-sun/p/3777561.html