Query: Menus and Function Name for a Particular Responsibility

Posted: April 26, 2013 in Oracle Apps, Queries

This Query provides the details abouts menus, submenus and function attached to a particular responsibility. This will drill down to all the submenus and provide the details.

SELECT lvl r_lvl, rownumber rw_num, entry_sequence seq,
 (lvl || '.' || rownumber || '.' || entry_sequence) menu_seq,
 menu_name, sub_menu_name, prompt, fm.description, TYPE,
 function_name, user_function_name, fff.description form_description
 ROW_NUMBER () OVER (PARTITION BY LEVEL, menu_id, entry_sequence ORDER BY entry_sequence)
 AS rownumber,
 (SELECT user_menu_name
 FROM fnd_menus_vl fmvl
 WHERE 1 = 1 AND fmvl.menu_id = fmv.menu_id) menu_name,
 (SELECT user_menu_name
 FROM fnd_menus_vl fmvl
 WHERE 1 = 1
 AND fmvl.menu_id = fmv.sub_menu_id) sub_menu_name,
 function_id, prompt, description
 FROM apps.fnd_menu_entries_vl fmv
 START WITH menu_id =
 (SELECT menu_id
 FROM apps.fnd_responsibility_vl
 WHERE UPPER (responsibility_name) =
 UPPER (:resp_name))
 CONNECT BY PRIOR sub_menu_id = menu_id) fm,
 apps.fnd_form_functions_vl fff
 WHERE fff.function_id(+) = fm.function_id
ORDER BY lvl, entry_sequence

1) R_lvl is a sequence number which starts with Parent Menu Level and drills down to child menu 2) RW_NUM is sequence number for submenus. 3) Seq is a sequence number as per the Oracle Apps.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s