Wednesday, April 8, 2009

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
)

2 comments:

Siva T V said...

Its not working any of the following menus

India Localization Payables Menu

India Localization Receivables Menu

or any menu name

Unknown said...

hi all,this is the wonderful blog about oracle....
Oracle Apps Technical Online Training