SELECT 'PR', prompt, menu.menu_id, menu.description, responsibility_name,
rsp.description application, user_name, full_name full_name
FROM fnd_menu_entries_vl menu,
apps.fnd_responsibility_vl rsp,
fnd_user_resp_groups_direct ursp,
(SELECT fu.user_id, fu.user_name, full_name full_name, pp.person_id
FROM apps.fnd_user fu, apps.per_people_f pp
WHERE pp.person_id = fu.employee_id
AND NVL (fu.end_date, SYSDATE + 1) >= TRUNC (SYSDATE)) person
WHERE rsp.menu_id = menu.menu_id
AND ursp.responsibility_id = rsp.responsibility_id
AND person.user_id = ursp.user_id
AND NVL (ursp.end_date, SYSDATE + 1) >= TRUNC (SYSDATE)
AND ( menu.menu_id IN (
SELECT menu.menu_id
FROM fnd_menu_entries_vl menu
WHERE sub_menu_id IN (SELECT menu.menu_id
--, menu.description,SUB_MENU_ID
FROM fnd_menu_entries_vl menu
WHERE function_id IN (:P_function_id))
UNION ALL
SELECT menu.menu_id
FROM fnd_menu_entries_vl menu
WHERE sub_menu_id IN (
SELECT menu.menu_id
FROM fnd_menu_entries_vl menu
WHERE sub_menu_id IN (
SELECT menu.menu_id
--, menu.description,SUB_MENU_ID
FROM fnd_menu_entries_vl menu
WHERE function_id IN (:P_function_id)))
UNION ALL
SELECT menu.menu_id
FROM fnd_menu_entries_vl menu
WHERE sub_menu_id IN (
SELECT menu.menu_id
FROM fnd_menu_entries_vl menu
WHERE sub_menu_id IN (
SELECT menu.menu_id
FROM fnd_menu_entries_vl menu
WHERE sub_menu_id IN (
SELECT menu.menu_id
--, menu.description,SUB_MENU_ID
FROM fnd_menu_entries_vl menu
WHERE function_id IN (:P_function_id))))
UNION ALL
SELECT menu.menu_id
FROM fnd_menu_entries_vl menu
WHERE sub_menu_id IN (
SELECT menu.menu_id
FROM fnd_menu_entries_vl menu
WHERE sub_menu_id IN (
SELECT menu.menu_id
FROM fnd_menu_entries_vl menu
WHERE sub_menu_id IN (
SELECT menu.menu_id
FROM fnd_menu_entries_vl menu
WHERE sub_menu_id IN (
SELECT menu.menu_id
--, menu.description,SUB_MENU_ID
FROM fnd_menu_entries_vl menu
WHERE function_id IN
(:P_function_id)))))
UNION ALL
SELECT menu.menu_id
FROM fnd_menu_entries_vl menu
WHERE sub_menu_id IN (
SELECT menu.menu_id
FROM fnd_menu_entries_vl menu
WHERE sub_menu_id IN (
SELECT menu.menu_id
FROM fnd_menu_entries_vl menu
WHERE sub_menu_id IN (
SELECT menu.menu_id
FROM fnd_menu_entries_vl menu
WHERE sub_menu_id IN (
SELECT menu.menu_id
FROM fnd_menu_entries_vl menu
WHERE sub_menu_id IN (
SELECT menu.menu_id
--, menu.description,SUB_MENU_ID
FROM fnd_menu_entries_vl menu
WHERE function_id IN
(:P_function_id))))))
UNION ALL
SELECT menu.menu_id
FROM fnd_menu_entries_vl menu
WHERE sub_menu_id IN (
SELECT menu.menu_id
FROM fnd_menu_entries_vl menu
WHERE sub_menu_id IN (
SELECT menu.menu_id
FROM fnd_menu_entries_vl menu
WHERE sub_menu_id IN (
SELECT menu.menu_id
FROM fnd_menu_entries_vl menu
WHERE sub_menu_id IN (
SELECT menu.menu_id
FROM fnd_menu_entries_vl menu
WHERE sub_menu_id IN (
SELECT menu.menu_id
FROM fnd_menu_entries_vl menu
WHERE sub_menu_id IN (
SELECT menu.menu_id
--, menu.description,SUB_MENU_ID
FROM fnd_menu_entries_vl menu
WHERE function_id IN
(:P_function_id))))))))
OR menu.function_id IN (:P_function_id)
)
GROUP BY menu.menu_id,
menu.description,
responsibility_name,
rsp.description,
user_name,
full_name,
prompt
