Oracle EBS-SQL (SYS-9):职责使用菜单.sql

查询FND Menus VLFME、VLFMV和VLSUB_FMV表关联数据

最新推荐文章于 2025-02-14 23:52:57 发布

weixin_33790053

最新推荐文章于 2025-02-14 23:52:57 发布

阅读量238

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/st-sun/p/3777561.html

本文详细介绍了如何从FND Menus VLFME、VLFMV和VLSUB_FMV表中查询特定菜单名称、用户菜单名、类型、描述、序列号、提示、子菜单名、用户函数名和描述1的数据，通过条件筛选和聚合操作实现数据的高效获取。

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