SQL Query to get Oracle Menus & Functions .

Script that queries the Menu structure

[sql] view plaincopyprint?

1. SELECT     LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.entry_sequence SEQUENCE,  
2.            LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.prompt prompt,  
3.            menu_entry.grant_flag grant_flag,  
4.            DECODE (menu_entry.sub_menu_id,  
5.                    NULL, 'FUNCTION',  
6.                    DECODE (menu_entry.function_id, NULL, 'SUBMENU', 'BOTH')  
7.                   ) TYPE,  
8.            menu2.user_menu_name, func2.user_function_name  
9.       FROM fnd_menu_entries_vl menu_entry,  
10.            fnd_menus_tl menu,  
11.            fnd_form_functions_tl func,  
12.            fnd_form_functions_tl func2,  
13.            fnd_menus_tl menu2  
14.      WHERE menu_entry.sub_menu_id = menu.menu_id(+)  
15.        AND menu_entry.function_id = func.function_id(+)  
16.        AND menu_entry.sub_menu_id = menu2.menu_id(+)  
17.        AND menu_entry.function_id = func2.function_id(+)  
18.        AND grant_flag = 'Y'  
19. START WITH menu_entry.menu_id =  
20.                      (SELECT menu_id  
21.                         FROM fnd_menus_tl menu2  
22.                        WHERE menu2.user_menu_name = 'INV_TRANSACTIONS')    --Your Menu Name Here(Parent_Menu_User_Name)  
23. CONNECT BY menu_entry.menu_id = PRIOR menu_entry.sub_menu_id  
24.   ORDER SIBLINGS BY menu_entry.entry_sequence;  

SELECT     LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.entry_sequence SEQUENCE,           LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.prompt prompt,           menu_entry.grant_flag grant_flag,           DECODE (menu_entry.sub_menu_id,                   NULL, 'FUNCTION',                   DECODE (menu_entry.function_id, NULL, 'SUBMENU', 'BOTH')                  ) TYPE,           menu2.user_menu_name, func2.user_function_name      FROM fnd_menu_entries_vl menu_entry,           fnd_menus_tl menu,           fnd_form_functions_tl func,           fnd_form_functions_tl func2,           fnd_menus_tl menu2     WHERE menu_entry.sub_menu_id = menu.menu_id(+)       AND menu_entry.function_id = func.function_id(+)       AND menu_entry.sub_menu_id = menu2.menu_id(+)       AND menu_entry.function_id = func2.function_id(+)       AND grant_flag = 'Y'START WITH menu_entry.menu_id =                     (SELECT menu_id                        FROM fnd_menus_tl menu2                       WHERE menu2.user_menu_name = 'INV_TRANSACTIONS')    --Your Menu Name Here(Parent_Menu_User_Name)CONNECT BY menu_entry.menu_id = PRIOR menu_entry.sub_menu_id  ORDER SIBLINGS BY menu_entry.entry_sequence;

Output:

The query gives you one level of sub menus for any responsibility specified

[sql] view plaincopyprint?

1. SELECT  FMEV.ENTRY_SEQUENCE,  
2.        FMEV.PROMPT,  
3.        FMEV.DESCRIPTION,  
4.        SUB_MENU_FMEV.USER_MENU_NAME SUB_MENU_NAME,  
5.        SUB_MENU_FMEV.DESCRIPTION SUB_MENU_DESCRIPTION,  
6.         FFFT.USER_FUNCTION_NAME,  
7.         FMEV.GRANT_FLAG  
8. FROM apps.FND_MENU_ENTRIES_VL FMEV,  
9.       apps.FND_MENUS_TL SUB_MENU_FMEV,  
10.       APPS.FND_FORM_FUNCTIONS_TL FFFT,  
11.       apps.FND_RESPONSIBILITY_VL FRV  
12. WHERE FRV.MENU_ID = FMEV.menu_id  
13. AND   SUB_MENU_FMEV.MENU_ID(+) = FMEV.SUB_menu_id  
14. AND   SUB_MENU_FMEV.LANGUAGE(+) = 'US'  
15. AND   FFFT.FUNCTION_ID(+) = FMEV.FUNCTION_ID  
16. AND   FFFT.LANGUAGE(+) = 'US'  
17. AND   FRV.responsibility_name = 'Manufacturing and Distribution Manager' --Your Responsibility Name  
18. ORDER BY FMEV.entry_sequence;  

SELECT  FMEV.ENTRY_SEQUENCE,       FMEV.PROMPT,       FMEV.DESCRIPTION,       SUB_MENU_FMEV.USER_MENU_NAME SUB_MENU_NAME,       SUB_MENU_FMEV.DESCRIPTION SUB_MENU_DESCRIPTION,        FFFT.USER_FUNCTION_NAME,        FMEV.GRANT_FLAGFROM apps.FND_MENU_ENTRIES_VL FMEV,      apps.FND_MENUS_TL SUB_MENU_FMEV,      APPS.FND_FORM_FUNCTIONS_TL FFFT,      apps.FND_RESPONSIBILITY_VL FRVWHERE FRV.MENU_ID = FMEV.menu_idAND   SUB_MENU_FMEV.MENU_ID(+) = FMEV.SUB_menu_idAND   SUB_MENU_FMEV.LANGUAGE(+) = 'US'AND   FFFT.FUNCTION_ID(+) = FMEV.FUNCTION_IDAND   FFFT.LANGUAGE(+) = 'US'AND   FRV.responsibility_name = 'Manufacturing and Distribution Manager' --Your Responsibility NameORDER BY FMEV.entry_sequence;

Output:

This QUERY will retrieve the Function(s) and Submenu(s) which those all are attached to the given input responsibility.

[sql] view plaincopyprint?

1. SELECT NVL2 (fme.sub_menu_id, '+', '-')  
2. || LPAD (NVL ((SELECT prompt  
3. FROM apps.fnd_menu_entries_vl  
4. WHERE menu_id = fme.menu_id  
5. AND sub_menu_id = fme.sub_menu_id  
6. AND fme.function_id IS NULL),  
7. (SELECT prompt  
8. FROM apps.fnd_menu_entries_vl  
9. WHERE menu_id = fme.menu_id  
10. AND function_id = fme.function_id  
11. AND fme.sub_menu_id IS NULL)  
12. ),  
13. LENGTH (NVL ((SELECT prompt  
14. FROM apps.fnd_menu_entries_vl  
15. WHERE menu_id = fme.menu_id  
16. AND sub_menu_id = fme.sub_menu_id  
17. AND fme.function_id IS NULL),  
18. (SELECT prompt  
19. FROM apps.fnd_menu_entries_vl  
20. WHERE menu_id = fme.menu_id  
21. AND function_id = fme.function_id  
22. AND fme.sub_menu_id IS NULL)  
23. )  
24. )  
25. + (LEVEL * 5),  
26. '-'  
27. ) tree_structure  
28. FROM apps.fnd_menu_entries fme  
29. START WITH fme.menu_id =  
30. (SELECT menu_id  
31. FROM apps.fnd_responsibility fr, apps.fnd_responsibility_tl frt  
32. WHERE fr.responsibility_id = frt.responsibility_id  
33. AND frt.responsibility_name = '&responsibility_name') -- 'Application Developer'  
34. CONNECT BY PRIOR fme.sub_menu_id = fme.menu_id;  

SELECT NVL2 (fme.sub_menu_id, '+', '-')|| LPAD (NVL ((SELECT promptFROM apps.fnd_menu_entries_vlWHERE menu_id = fme.menu_idAND sub_menu_id = fme.sub_menu_idAND fme.function_id IS NULL),(SELECT promptFROM apps.fnd_menu_entries_vlWHERE menu_id = fme.menu_idAND function_id = fme.function_idAND fme.sub_menu_id IS NULL)),LENGTH (NVL ((SELECT promptFROM apps.fnd_menu_entries_vlWHERE menu_id = fme.menu_idAND sub_menu_id = fme.sub_menu_idAND fme.function_id IS NULL),(SELECT promptFROM apps.fnd_menu_entries_vlWHERE menu_id = fme.menu_idAND function_id = fme.function_idAND fme.sub_menu_id IS NULL)))+ (LEVEL * 5),'-') tree_structureFROM apps.fnd_menu_entries fmeSTART WITH fme.menu_id =(SELECT menu_idFROM apps.fnd_responsibility fr, apps.fnd_responsibility_tl frtWHERE fr.responsibility_id = frt.responsibility_idAND frt.responsibility_name = '&responsibility_name') -- 'Application Developer'CONNECT BY PRIOR fme.sub_menu_id = fme.menu_id;

Output:

TREE_STRUCTURE
+-----Flexfield
+----------Key
----------------Register
----------------Segments
----------------Aliases
----------------CrossValidation
----------------Values
----------------Groups
----------------Accounts
+----------Descriptive
----------------Register
----------------Segments
----------------Values
-----------Flexfield Test
+-----Concurrent
-----------Program
-----------Executable
-----------Library
+-----Application
-----------Register
-----------Form
-----------Function
-----------Menu
-----------Messages
+----------Database
----------------Table
----------------Sequence
----------------View
+----------Lookups
----------------Application Object Library
----------------Common
----------------CE lookups
----------------GCS Lookups
----------------IA Lookups
----------------igi lookups
----------------XLA Lookups
----------------XLE Lookups
----------------FUN Lookups
----------------Service Lookups
+----------Validation
----------------Set
----------------Values
------Profile
+-----Attachments
-----------Document Entities
-----------Document Categories
-----------Attachment Functions
-----------Repositories
+-----Other
+----------Requests
----------------Run
----------------View
----------------Set
-----------Profile
-----------Concurrent
-----------Change Organization
-----------Running Jobs
+----------EDI
----------------EDI Setup
----------------EDI Data

             

From Function to Menu(Basic Queries)

SELECT * FROM FND_FORM_FUNCTIONS_TL WHERE user_function_name LIKE '%Define Material Status%';                                 

SELECT * FROM FND_MENU_ENTRIES_VL WHERE function_id = 1006916; --Get Menu_id

SELECT * FROM FND_MENU_ENTRIES_VL WHERE menu_id = 67607 --同一Level都有哪些其他功能
 
SELECT * FROM FND_MENUS WHERE menu_id = 67607 --查看上层的Menu,Menu_Name:INV_SETUP_TRANSACTIONS

SELECT * FROM FND_MENU_ENTRIES WHERE sub_menu_id = 67607 --上层Menu_id:67579

SELECT * FROM FND_MENUS WHERE menu_id = 67579 --查看上层的Menu_Name:INV_SETUP

SELECT * FROM FND_MENU_ENTRIES WHERE sub_menu_id = 67579 --上层Menu_id:67579

SELECT * FROM FND_MENUS WHERE  menu_id IN  (SELECT menu_id FROM FND_MENU_ENTRIES WHERE sub_menu_id = 67579)


SELECT fmt.description,fm.*
FROM FND_MENUS fm,FND_MENUS_TL fmt WHERE fmt.LANGUAGE = 'US' AND fm.menu_id = fmt.menu_id
AND fm.menu_id IN
(SELECT menu_id FROM FND_MENU_ENTRIES_VL WHERE function_id IN
(SELECT function_id FROM FND_FORM_FUNCTIONS_TL WHERE user_function_name LIKE '%Define Material Status%'))