Code Snippet for querying responsibilities and users in 11i Applications

1. During a migration project we were required to copy the responsibilities created in test instances to production. For this scenario, I have used the below query to find out the request groups and menus used by these responsibilities.

SELECT DISTINCT rtl.responsibility_name,
(SELECT DISTINCT application_name
FROM fnd_application_tl
WHERE application_id = r.application_id
AND source_lang = 'US') application,
(SELECT DISTINCT user_menu_name FROM fnd_menus_tl
WHERE menu_id = r.menu_id AND source_lang = 'US') menu, rg.request_group_name, DECODE (rule_type,'F', 'FUNCTION', 'M', 'MENU' ) exclusion_type, DECODE (rule_type, 'F', (SELECT DISTINCT user_function_name FROM applsys.fnd_form_functions_tl
WHERE function_id = fff.action_id AND source_lang = 'US'),
'M', (SELECT DISTINCT menu_name
FROM fnd_menus
WHERE menu_id = fff.action_id) )
exclusion_name
FROM fnd_responsibility_tl rtl,fnd_responsibility r, fnd_request_groups rg, applsys.fnd_resp_functions fff
WHERE rtl.responsibility_id = r.responsibility_id
AND (r.request_group_id = rg.request_group_id(+)
AND r.group_application_id = rg.application_id(+))
AND r.responsibility_id = fff.responsibility_id(+)
AND UPPER (rtl.responsibility_name) = UPPER ('&responsibility_name')
AND rtl.source_lang = 'US';

2. I use the below query to find out the responsibilities assigned to user in 11i with start and end dates.

SELECT fu.user_name, fr.responsibility_key, fur.start_date, fur.end_date
FROM apps.fnd_responsibility fr, apps.fnd_user fu, apps.fnd_user_resp_groups_direct fur WHERE fur.responsibility_id = fr.responsibility_id
AND fur.responsibility_application_id = fr.application_id
AND fur.user_id = fu.user_idORDER BY user_name