Find below the SQL statement to generate an extract with active users together with their active responsibilities.
The statement extracts all the active assignments of responsibilities by using the FND_USER_RESP_GROUPS table which combines DIRECT and INDIRECT responsibilities.
If you need only the DIRECT responsibilities than you also have the option to use the seeded view FND_USER_RESP_GROUPS_DIRECT. If you need only the INDIRECT responsibilities (added by roles – like Application Diagnostics and others) than use the seeded view FND_USER_RESP_GROUPS_INDIRECT.
As always adjust the SQL to cover your needs.
, per.FULL_NAME FULL_NAME
, per.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, frt.RESPONSIBILITY_NAME RESPONSIBILITY
, PER_PEOPLE_F per
, FND_USER_RESP_GROUPS furg
, FND_RESPONSIBILITY_TL frt
fuser.EMPLOYEE_ID = per.PERSON_ID
AND fuser.USER_ID = furg.USER_ID
AND (to_char(fuser.END_DATE) is null
OR fuser.END_DATE > sysdate)
AND frt.RESPONSIBILITY_ID = furg.RESPONSIBILITY_ID
AND (to_char(furg.END_DATE) is null
OR furg.END_DATE > sysdate)
AND frt.LANGUAGE = ‘US’
I hope this will be useful.