Searching for the navigation of a form in Oracle Application

Many times it happened to me that I know that there is some custom or standard form out there in some navigation under some responsibility of Oracle Applications. There is no direct way of finding this from front end or through a simple back end query. I was working with a new project where there were a lot of custom Menu entries which I did not know the navigation. So finally I wrote the following query.





SELECT LPAD ('*', (LEVEL - 1) * 2, '*') || prompt NAVIGATION,

DECODE (menu_type,'R','Responsibility',NULL) Type

FROM (SELECT prompt,

LEVEL menu_level,

TYPE menu_type,

menu_id,

sub_menu_id

FROM ( SELECT prompt,

menu_id,

sub_menu_id,

function_id,

'M' TYPE

FROM fnd_menu_entries_vl

WHERE prompt IS NOT NULL

UNION

SELECT responsibility_name,

0,

menu_id,

-1,

'R'

FROM fnd_responsibility_vl

WHERE NVL (end_date, SYSDATE + 1) > SYSDATE) fme

START WITH UPPER (prompt) LIKE Upper('&Menu_Name')

CONNECT BY PRIOR menu_id = sub_menu_id)

START WITH menu_type = 'R'

CONNECT BY PRIOR sub_menu_id = menu_id




Just give the approximate name for the menu item as input parameter and the query will output all available navigations to this menu item.