Below is the query to get all the responsibilities which are associated to the form function :-
SELECT DISTINCT responsibility_id, responsibility_name
FROM apps.fnd_responsibility_vl a
WHERE a.end_date IS NULL
AND a.menu_id IN
( SELECT menu_id
FROM apps.fnd_menu_entries_vl
START WITH menu_id IN
(SELECT menu_id
FROM apps.fnd_menu_entries_vl
WHERE function_id IN
(SELECT function_id
FROM applsys.fnd_form_functions a
WHERE function_name =
:pc_function_name))
CONNECT BY PRIOR menu_id = sub_menu_id)
AND a.responsibility_id NOT IN
(SELECT responsibility_id
FROM apps.fnd_responsibility_vl
WHERE responsibility_id IN
(SELECT responsibility_id
FROM applsys.fnd_resp_functions resp
WHERE action_id IN
(SELECT function_id
FROM applsys.fnd_form_functions a
WHERE function_name = :pc_function_name)))
AND a.responsibility_id NOT IN
(SELECT responsibility_id
FROM apps.fnd_responsibility_vl
WHERE responsibility_id IN
(SELECT responsibility_id
FROM applsys.fnd_resp_functions resp
WHERE action_id IN
( SELECT menu_id
FROM apps.fnd_menu_entries_vl
START WITH menu_id IN
(SELECT menu_id
FROM apps.fnd_menu_entries_vl
WHERE function_id IN
(SELECT function_id
FROM applsys.fnd_form_functions a
WHERE function_name =
:pc_function_name))
CONNECT BY PRIOR menu_id = sub_menu_id)))
SELECT DISTINCT responsibility_id, responsibility_name
FROM apps.fnd_responsibility_vl a
WHERE a.end_date IS NULL
AND a.menu_id IN
( SELECT menu_id
FROM apps.fnd_menu_entries_vl
START WITH menu_id IN
(SELECT menu_id
FROM apps.fnd_menu_entries_vl
WHERE function_id IN
(SELECT function_id
FROM applsys.fnd_form_functions a
WHERE function_name =
:pc_function_name))
CONNECT BY PRIOR menu_id = sub_menu_id)
AND a.responsibility_id NOT IN
(SELECT responsibility_id
FROM apps.fnd_responsibility_vl
WHERE responsibility_id IN
(SELECT responsibility_id
FROM applsys.fnd_resp_functions resp
WHERE action_id IN
(SELECT function_id
FROM applsys.fnd_form_functions a
WHERE function_name = :pc_function_name)))
AND a.responsibility_id NOT IN
(SELECT responsibility_id
FROM apps.fnd_responsibility_vl
WHERE responsibility_id IN
(SELECT responsibility_id
FROM applsys.fnd_resp_functions resp
WHERE action_id IN
( SELECT menu_id
FROM apps.fnd_menu_entries_vl
START WITH menu_id IN
(SELECT menu_id
FROM apps.fnd_menu_entries_vl
WHERE function_id IN
(SELECT function_id
FROM applsys.fnd_form_functions a
WHERE function_name =
:pc_function_name))
CONNECT BY PRIOR menu_id = sub_menu_id)))
No comments:
Post a Comment