Menu Structure

This query can give all function available to a menu.

SELECT connect_by_root responsibility_name resp , user_menu_name , prompt , Sub_Menu_Name , user_function_name , function_type , form_name , SYS_CONNECT_BY_PATH( decode( prompt,null, decode(sub_menu_name,null,'(Hidden User Function ->'|| user_function_name|| ')' ,'(Hidden Sub Menu ->' || sub_menu_name || ')' ), prompt ) , ' --> ' ) Navigation_Path , ROWNUM seq , menu.menu_id , LEVEL lvl , entry_sequence FROM (SELECT fm.menu_id , fme.sub_menu_id , (SELECT user_menu_name FROM apps.fnd_menus_vl WHERE menu_id = fme.sub_menu_id ) Sub_Menu_Name , fme.entry_sequence , fm.user_menu_name , fme.function_id , fme.prompt , func.user_function_name , func.type function_type , DECODE(func.type, 'FORM', frm.form_name , 'JSP' , WEB_HTML_CALL , 'WWW' , WEB_HTML_CALL , NULL ) Form_name FROM apps.fnd_menu_entries_vl fme , apps.fnd_menus_vl fm , apps.fnd_form_functions_vl func , applsys.fnd_form frm WHERE fm.menu_id = fme.menu_id AND fme.menu_id <> NVL(fme.sub_menu_id,-1) -- -- Above condition is needed to avoid infinite loop -- Two Oracle contracts menu have a menu as child of itself -- AND fme.function_id = func.function_id (+) AND func.form_id = frm.form_id (+) ) menu , apps.fnd_responsibility_vl resp WHERE resp.menu_id(+) = menu.menu_id -- -- Add additional conditions to get desired results -- Replace % below to select desired menu structures -- START WITH user_menu_name like '%' CONNECT BY PRIOR sub_menu_id = menu.menu_id ORDER SIBLINGS BY entry_sequence