A simple query to get all the menu listings attached to a responsibility. Particularly useful when you want to know in which responsibility a particular menu is attached.You can alter the below query to take the menu name as the where condition instead of the Responsibility name:
SELECT
a.responsibility_name,c.prompt,c.description,d.menu_name
FROM apps.fnd_responsibility_tl a,
apps.fnd_responsibility b,
apps.fnd_menu_entries_tl c,
apps.fnd_menus_tl g,
apps.fnd_menus d,
apps.fnd_application_tl e,
apps.fnd_application f
WHERE
a.responsibility_id(+) = b.responsibility_id
AND a.responsibility_name like '%ITD%AR%MANAGER%'
AND g.menu_id = d.menu_id
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE = 'US'
AND c.LANGUAGE = 'US'
AND g.LANGUAGE = 'US'
and e.LANGUAGE = 'US';
This query does not offer enough info.
ReplyDeleteThe "prompt" column shows only how you type the function in the navigator for the users, it doesn't actually show the existing functions inside the resp.
The one below shows all the responsibilities where "View Suppliers" exist in the menu, attention, it does not exclude the menu exclusions in the responsibiliy :
SELECT A.menu_id,
a.menu_name "Menu name",
vl.user_menu_name "Menu Responsibility",
B.sub_menu_id,
C.prompt,
B.function_id,
D.user_function_name,
B.entry_sequence
FROM APPS.Fnd_menus A,
APPS.Fnd_menu_entries B,
APPS.Fnd_menu_entries_tl C,
APPS.FND_FORM_FUNCTIONS_TL D,
apps.FND_MENUS_VL vl
WHERE A.menu_id = B.menu_id
AND B.menu_id = C.menu_id
AND B.entry_sequence = C.entry_sequence
AND B.function_id = D.function_id
AND C.LANGUAGE = 'US'
AND D.LANGUAGE = 'US'
AND D.user_function_name LIKE 'View Suppliers'
AND VL.MENU_ID=A.MENU_ID