Oracle form attached to which responsibility

Oracle form attached to which responsibility
Oracle form navigation
oracle form responsibility query

For this we should have the form function_ id first. If you already have the form function_id, skip to step 3. Else follow steps 1 and 2.

Step 1: Find the form name

If you know some navigation of the form, open the form and go to
Help>>About Oracle applications
Scroll down to Current form section and copy the form name as shown in the screenshot below.

Step 2: Get the form function id

Use the form name from step 1 in the below query and get the appropriate function_id.

SQL Querysql

1SELECT function_id,USER_FUNCTION_NAME, FUNCTION_NAME, form_name
2  FROM fnd_form_functions_vl fff, fnd_form ff
3 WHERE fff.form_id = ff.form_id
4 and form_name='OEXOEORD'

Step 3: Get the responsibility and navigation

Use the function_id obtained in the step2 and run the below query. It gives all the responsibility name along with navigation as shown in screenshot.

SQL Querysql

1SELECT responsibility_name, menu_structure.PATH navigation
2  FROM (           SELECT LEVEL padding,
3                          menu_id,
4                          RTRIM (reverse (SYS_CONNECT_BY_PATH (reverse (prompt), '>')),
5                                 '>')
6                             PATH,
7                          (SELECT menu_name
8                             FROM fnd_menus fm
9                            WHERE fm.menu_id = fme.menu_id)
10                             menu_name,
11                          entry_sequence,
12                          sub_menu_id,
13                          (SELECT menu_name
14                             FROM fnd_menus fm
15                            WHERE fm.menu_id = fme.sub_menu_id)
16                             submenu_name,
17                          function_id
18                     FROM fnd_menu_entries_vl fme
19               CONNECT BY sub_menu_id = PRIOR menu_id 
20               START WITH function_id = :function_id 
21        ORDER SIBLINGS BY entry_sequence) menu_structure,
22       fnd_responsibility_vl fr
23 WHERE menu_structure.menu_id = fr.menu_id
24 order by 1;