Oracle Application dicussion and support forum

Oracle Apps Query to list the functions, responsibilities and the users assigned to these responsibilities. Number of times, we need to find out the list of users having access to a perticular user function in Oracle applications. The following query can be used to produce this report.

SELECT DISTINCT
u.user_name, rtl.RESPONSIBILITY_NAME, ff.function_name, ffl.user_function_name
FROM fnd_compiled_menu_functions cmf
, fnd_form_functions ff
, fnd_form_functions_tl ffl
, fnd_responsibility r
, fnd_responsibility_TL rtl
, fnd_user_resp_groups urg
, fnd_user u
WHERE cmf.function_id = ff.function_id
AND r.menu_id = cmf.menu_id
AND urg.responsibility_id = r.responsibility_id
AND rtl.responsibility_id = r.responsibility_id
AND cmf.GRANT_FLAG=’Y’
and r.APPLICATION_ID=urg.RESPONSIBILITY_APPLICATION_ID
AND u.user_id = urg.user_id
–and ff.function_id=19438
and upper(ffl.user_function_name) like upper(‘Agent%Dashboard’)
and ff.function_id=ffl.function_id
order by u.user_name