query gives all function names under a menu considering submenus's also.

-- Below query gives all function names under a menu considering submenus's also.
SELECT DISTINCT fmep.menu_id,
DECODE (fmep.function_id,
NULL, DECODE (fmec.function_id,
NULL, DECODE (fmec1.function_id,
NULL, 'No Func',
fmec1.function_id
),
fmec.function_id
),
fmep.function_id
) funcid,
fff.user_function_name, fff.description
FROM fnd_form_functions_tl fff,
fnd_menu_entries fmec1,
fnd_menu_entries fmec,
fnd_menu_entries fmep
WHERE fmep.menu_id =
(SELECT menu_id
FROM fnd_menus
WHERE menu_name =
'INV_NAVIGATE'
--Change the menu according to your requirement
AND ROWNUM = 1)
AND fmep.sub_menu_id = fmec.menu_id(+)
AND fmec.sub_menu_id = fmec1.menu_id(+)
AND fff.function_id =
DECODE (fmep.function_id,
NULL, DECODE (fmec.function_id,
NULL, DECODE (fmec1.function_id,
NULL, -999,
fmec1.function_id
),
fmec.function_id
),
fmep.function_id
)
ORDER BY DECODE (fmep.function_id,
NULL, DECODE (fmec.function_id,
NULL, DECODE (fmec1.function_id,
NULL, 'No Func',
fmec1.function_id
),
fmec.function_id
),
fmep.function_id
)