Query: Menus and Function Name for a Particular Responsibility

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
FROM (SELECT LEVEL lvl,
ROW_NUMBER () OVER (PARTITION BY LEVEL, menu_id, entry_sequence ORDER BY entry_sequence)
AS rownumber,
entry_sequence,
(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.

Post a Comment

Your email is kept private. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>