Oracle Apps + SQL Query to Get Oracle Menus, Sub Menus and Functions

 1. Query below will give tree structure as per menu_id, but I need to get the backend User Function Name as well (which was missing).

SELECT * FROM 
      (SELECT  menu_id, sub_menu_id, function_id,(select user_menu_name 
from fnd_menus_tl
where menu_id=fme.menu_id
AND language='US') menu_name,(select user_menu_name 
from fnd_menus_tl
where menu_id=fme.sub_menu_id
AND language='US') sub_menu,(select USER_FUNCTION_NAME from fnd_form_functions_vl 
where function_id=fme.function_id) fucntion_name,
         LPAD (' ', (LEVEL - 1) * 2)|| prompt prompt, entry_sequence
                FROM fnd_menu_entries_vl fme
                WHERE prompt IS NOT NULL
                CONNECT BY PRIOR sub_menu_id = menu_id
                START WITH menu_id = 79980
              AND menu_id =  79980
              AND prompt IS NOT NULL
              AND grant_flag = 'Y'
              ORDER BY entry_sequence) a
     CONNECT BY PRIOR sub_menu_id = menu_id
     START WITH menu_id =  79980  /*like INV_NAVIGATE*/
     AND menu_id =  79980
     AND prompt IS NOT NULL;

2).

SELECT LPAD(' ', 6*(LEVEL-1)) || menu_entry.entry_sequence sequence 
, LPAD(' ', 6*(LEVEL-1)) || menu.user_menu_name SubMenu_Descrition 
, LPAD(' ', 6*(LEVEL-1)) || func.user_function_name Function_Description 
, menu.menu_id 
, func.function_id 
, menu_entry.grant_flag Grant_Flag 
, DECODE( menu_entry.sub_menu_id 
, null, 'FUNCTION' 
, DECODE( menu_entry.function_id 
, null, 'SUBMENU' 
, 'BOTH') 
) Type 
FROM fnd_menu_entries menu_entry,
fnd_menus_tl menu,
fnd_form_functions_tl func
WHERE menu_entry.sub_menu_id = menu.menu_id(+)
AND menu_entry.function_id = func.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 = '&Parent_Menu_User_Name') 
CONNECT BY menu_entry.menu_id = PRIOR menu_entry.sub_menu_id
ORDER SIBLINGS BY menu_entry.entry_sequence

3).

SELECT * FROM
     (SELECT  menu_id, sub_menu_id, function_id,
        ,(select function_name from fnd_form_functions_vl where function_id= fme.function_id) func,
        LPAD (' ', (LEVEL - 1) * 2)|| prompt prompt, entry_sequence
               FROM fnd_menu_entries_vl fme
               WHERE prompt IS NOT NULL
               CONNECT BY PRIOR sub_menu_id = menu_id
               START WITH menu_id = 67605
             AND menu_id =  67605
             AND prompt IS NOT NULL
             AND grant_flag = 'Y'
             ORDER BY entry_sequence) a
    CONNECT BY PRIOR sub_menu_id = menu_id
    START WITH menu_id =  67605  /*like INV_NAVIGATE*/
    AND menu_id =  67605
    AND prompt IS NOT NULL;