Script to Extract FND Menu Hierarchy

/********************************************************************************************************

*

* Purpose:           Script to Extract AOL Menu Hierarchy

* Created by:        Faraz Anwar

* Creation Date:     25/03/2010

*

* 

* Description:      Simply replace the root node (master menu id) with the required

*                   value to retrive the list of underlying menus and functions

********************************************************************************************************/

SELECT rownum row_num,
       LEVEL lev,
       fme.entry_sequence seq,
       fme.prompt,
       fm.user_menu_name "MASTER_MENU",
       fms.user_menu_name "SUB_MENU",
       fff.function_id,
       fff.user_function_name,
       grant_flag,
       sys_connect_by_path(fm.user_menu_name, '/') full_path
  FROM fnd_menu_entries_vl fme,
       fnd_menus_tl        fm,
       fnd_menus_tl        fms,
       fnd_form_functions_tl  fff
 WHERE fme.menu_id = fm.menu_id
   AND fme.sub_menu_id = fms.menu_id(+)
   AND fme.function_id = fff.function_id(+)
 START WITH fme.menu_id = ( SELECT m.menu_id
                            --, M.menu_name, mt.user_menu_name 
                           FROM fnd_menus m, fnd_menus_tl mt 
                           WHERE m.menu_id = mt.menu_id
                           AND mt.user_menu_name = 'OTA Navigator'
                           )-- GLB HRMS Navigator
CONNECT BY PRIOR fme.sub_menu_id = fme.menu_id
 ORDER SIBLINGS BY entry_sequence;