Oracle sql script menu submenu subprogram
這個Script可以讓各位指定Menu無限展開其底下的Submenu or Subprogram，用來確認各項事務流程的完整度，及轉換DB時的Menu確認
SELECT LPAD(' ', 4 *(LEVEL - 1)) || TO_CHAR(a.menu_name) u_menu_name,
LPAD(' ', 4 *(LEVEL - 1)) || a.entry_sequence ss,
LPAD(' ', 4 *(LEVEL - 1)) || a.prompt,
-- A.SUB_MENU_ID,
-- A.FUNCTION_ID,
LPAD(' ', 4 *(LEVEL - 1)) || a.function_name fn,
LPAD(' ', 4 *(LEVEL - 1)) || a.user_function_name ufn,
LPAD(' ', 4 *(LEVEL - 1)) || a.description de,
a.entry_sequence seq
FROM (SELECT fmv.menu_id,
fmv.menu_name,
fmv.user_menu_name,
fmev.entry_sequence,
fmev.prompt,
fmev.sub_menu_id,
fmev.function_id,
fffv.function_name,
fffv.user_function_name,
fmev.description
FROM fnd_menus_vl fmv,
fnd_menu_entries_vl fmev,
fnd_form_functions_vl fffv
WHERE 1 = 1
AND fmv.menu_id = fmev.menu_id
AND fmev.function_id = fffv.function_id(+)) a
START WITH UPPER(a.user_menu_name) = '&MENU_NAME'
CONNECT BY PRIOR a.sub_menu_id = a.menu_id
ORDER SIBLINGS BY seq