Navigation Path for Oracle Form/OAF Page (Query#2)

This is another query, probably slightly better query to find out responsibility and navigation path for a particular Form or OAF Page. This query takes advantage of CONNECT_BY_ROOT to get responsibility associated with top level menu. The other query uses SYS_CONNECT_BY_PATH(menu_id,' --> ') to join with fnd_responsibilities_vl. Although that worked but i am little apprehensive of that kind of trick.

SELECT * FROM ( SELECT CONNECT_BY_ROOT responsibility_name responsibility_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 , user_menu_name , prompt , Sub_Menu_Name , user_function_name , function_type , form_name , 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 CONNECT BY PRIOR sub_menu_id = menu.menu_id ORDER SIBLINGS BY entry_sequence ) WHERE ( form_name = 'GLXSTPRG' OR form_name = 'XXFAEXTD' OR prompt = 'Program' ) AND responsibility_name IS NOT NULL ORDER BY prompt , form_name ;