Useful Sysadmin SQL statements

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.