SQL Query to get Oracle Menus & Functions

nghhuhpncs

　　Script that queries the Menu structure

　　

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


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_FLAG
FROM apps.FND_MENU_ENTRIES_VL FMEV,
apps.FND_MENUS_TL SUB_MENU_FMEV,
APPS.FND_FORM_FUNCTIONS_TL FFFT,
apps.FND_RESPONSIBILITY_VL FRV
WHERE FRV.MENU_ID = FMEV.menu_id
AND   SUB_MENU_FMEV.MENU_ID(+) = FMEV.SUB_menu_id
AND   SUB_MENU_FMEV.LANGUAGE(+) = 'US'
AND   FFFT.FUNCTION_ID(+) = FMEV.FUNCTION_ID
AND   FFFT.LANGUAGE(+) = 'US'
AND   FRV.responsibility_name = 'Manufacturing and Distribution Manager' --Your Responsibility Name
ORDER 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.

　　

SELECT NVL2 (fme.sub_menu_id, '+', '-')
|| LPAD (NVL ((SELECT prompt
FROM apps.fnd_menu_entries_vl
WHERE menu_id = fme.menu_id
AND sub_menu_id = fme.sub_menu_id
AND fme.function_id IS NULL),
(SELECT prompt
FROM apps.fnd_menu_entries_vl
WHERE menu_id = fme.menu_id
AND function_id = fme.function_id
AND fme.sub_menu_id IS NULL)
),
LENGTH (NVL ((SELECT prompt
FROM apps.fnd_menu_entries_vl
WHERE menu_id = fme.menu_id
AND sub_menu_id = fme.sub_menu_id
AND fme.function_id IS NULL),
(SELECT prompt
FROM apps.fnd_menu_entries_vl
WHERE menu_id = fme.menu_id
AND function_id = fme.function_id
AND fme.sub_menu_id IS NULL)
)
)
+ (LEVEL * 5),
'-'
) tree_structure
FROM apps.fnd_menu_entries fme
START WITH fme.menu_id =
(SELECT menu_id
FROM apps.fnd_responsibility fr, apps.fnd_responsibility_tl frt
WHERE fr.responsibility_id = frt.responsibility_id
AND 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%'))