As I mentioned in my last post, here in this post I would like to share some SQL queries which will/might be useful for the sysadmins.
1. SQL Statement to find the list of responsibilities, which got the access to a particular function.
SELECT *
FROM fnd_responsibility_vl
WHERE menu_id IN
(SELECT menu_id
FROM fnd_menus_vl
WHERE menu_id IN
(SELECT menu_id
FROM fnd_menu_entries_vl START
WITH function_id =
(SELECT function_id FROM fnd_form_functions WHERE function_name=’FUNCTION_NAME‘
) CONNECT BY PRIOR menu_id = sub_menu_id
)
)
2. SQL Statement to find the list of responsibilities, which got the access to any of the function(s) of a menu.
SELECT *
FROM fnd_responsibility_vl
WHERE menu_id IN
(SELECT menu_id
FROM fnd_menus_vl
WHERE menu_id IN
(SELECT menu_id
FROM fnd_menu_entries_vl START
WITH function_id IN
(SELECT function_id
FROM fnd_form_functions
WHERE function_id IN
(SELECT function_id
FROM fnd_menu_entries_vl START
WITH menu_id = YOUR_MENU_ID CONNECT BY PRIOR sub_menu_id = menu_id
)
) CONNECT BY PRIOR menu_id = sub_menu_id
)
)
3. SQL Statement to find the list of users who got the access to any of the function(s) of a menu.
SELECT user_name ,
USER_ORIG_SYSTEM_ID responsibility_id,
start_date ,
expiration_date
FROM wf_all_user_roles
WHERE USER_ORIG_SYSTEM_ID IN
(SELECT responsibility_id
FROM fnd_responsibility
WHERE menu_id IN
(SELECT menu_id
FROM fnd_menus
WHERE menu_id IN
(SELECT menu_id
FROM fnd_menu_entries START
WITH function_id IN
(SELECT function_id
FROM fnd_form_functions
WHERE function_id IN
(SELECT function_id
FROM fnd_menu_entries_vl START
WITH menu_id = YOUR_MENU_ID CONNECT BY PRIOR sub_menu_id = menu_id
)
) CONNECT BY PRIOR menu_id = sub_menu_id
)
)
)
AND sysdate BETWEEN start_date AND NVL(expiration_date,sysdate)
Ok here the above mentioned statements were structured based on the 11.5.10 application. Since there is no data model chance in the sysadmin side for R12, these queries should work on the R12 application. You can cross verify the results of these statements using the Functional Administrator responsibility.
Readers if you see any of the statement(s) mentioned above is not giving the expected result then do suggest your changes in the comments section and obviously if you have a better query you can post them in the comments section.
About the Author | |
 |
AK Lakshmanan
Lakshmanan Ashok Kumar is an oracle EBS technical enthusiastic. He has hands full of experience on Oracle forms, reports, and BI Desktop Publisher. Otherwise Lakshmanan Ashok Kumar is also a Techno Functional person in Order 2 Cash and Manufacturing modules. We are glad to have Lakshmanan's thoughts and views of various oracle EBS stand and hope you enjoy his posts. |
Tags: find responsibilities have access to a function, sysadmin sql query
The latest Doodle:
|
