Menus - Tree Walking

SELECT fmev.entry_sequence seq
                , LEVEL
                , LPAD (' ', (LEVEL - 1) * 3, ' ') || fmv.user_menu_name menu
                , LPAD (' ', (LEVEL - 1) * 3, ' ') || fmev.prompt prompt
                , fmv.menu_name
                , fmv.user_menu_name
                , CASE
                     WHEN fmev.function_id IS NOT NULL
                     THEN
                        (SELECT user_function_name
                           FROM apps.fnd_form_functions_vl
                          WHERE function_id = fmev.function_id)
                  END
                     user_function_name
                , CASE
                     WHEN fmev.function_id IS NOT NULL
                     THEN
                        (SELECT function_name
                           FROM apps.fnd_form_functions_vl
                          WHERE function_id = fmev.function_id)
                  END
                     function_name
                , fmev.description
             FROM apps.fnd_menus_vl fmv
                , apps.fnd_menu_entries_vl fmev
            WHERE fmev.menu_id = fmv.menu_id
       CONNECT BY fmev.menu_id = PRIOR fmev.sub_menu_id
       START WITH fmv.user_menu_name = 'INV_NAVIGATE'
ORDER SIBLINGS BY fmev.entry_sequence;

Menu Flat View

 

SELECT fmev.entry_sequence seq
     , fmev.prompt
     , fmev.description
     -- sub menu details
     , CASE WHEN fmev.sub_menu_id IS NOT NULL THEN (SELECT user_menu_name FROM apps.fnd_menus_vl e WHERE e.menu_id = fmev.sub_menu_id) END submenu
     , CASE WHEN fmev.sub_menu_id IS NOT NULL THEN (SELECT creation_date FROM apps.fnd_menus_vl e WHERE e.menu_id = fmev.sub_menu_id) END submenu_cr_date
     , CASE WHEN fmev.sub_menu_id IS NOT NULL THEN (SELECT fu.user_name FROM apps.fnd_menus_vl e, applsys.fnd_user fu WHERE e.menu_id = fmev.sub_menu_id AND e.created_by = fu.user_id) END submenu_cr_by            
     , CASE WHEN fmev.sub_menu_id IS NOT NULL THEN (SELECT last_update_date FROM apps.fnd_menus_vl e WHERE e.menu_id = fmev.sub_menu_id) END submenu_up_date
     , CASE WHEN fmev.sub_menu_id IS NOT NULL THEN (SELECT fu.user_name FROM apps.fnd_menus_vl e, applsys.fnd_user fu WHERE e.menu_id = fmev.sub_menu_id AND e.last_updated_by = fu.user_id) END submenu_up_by                  
     -- function details
     , CASE WHEN fmev.function_id IS NOT NULL THEN (SELECT function_name FROM apps.fnd_form_functions_vl WHERE function_id = fmev.function_id) END fcn
     , CASE WHEN fmev.function_id IS NOT NULL THEN (SELECT user_function_name FROM apps.fnd_form_functions_vl WHERE function_id = fmev.function_id) END user_fcn_name
     , CASE WHEN fmev.function_id IS NOT NULL THEN (SELECT fmev.creation_date FROM apps.fnd_form_functions_vl WHERE function_id = fmev.function_id) END fcn_added_to_menu_date
     , CASE WHEN fmev.function_id IS NOT NULL THEN (SELECT fu.user_name FROM apps.fnd_form_functions_vl f, applsys.fnd_user fu WHERE function_id = fmev.function_id AND fmev.created_by = fu.user_id) END fcn_added_to_menu_cr_by         
     , CASE WHEN fmev.function_id IS NOT NULL THEN (SELECT fmev.last_update_date FROM apps.fnd_form_functions_vl WHERE function_id = fmev.function_id) END fcn_added_to_menu_update_date    
     , CASE WHEN fmev.function_id IS NOT NULL THEN (SELECT fu.user_name FROM apps.fnd_form_functions_vl f, applsys.fnd_user fu WHERE function_id = fmev.function_id AND fmev.last_updated_by = fu.user_id) END fcn_added_to_menu_up_by                   
  FROM apps.fnd_menus_vl fmv
     , apps.fnd_menu_entries_vl fmev
 WHERE fmev.menu_id = fmv.menu_id
   AND user_menu_name = 'INV_NAVIGATE';