Today I got the request to generate an extract with active users together with their active responsibilities.
Find below the SQL statement I created to deliver the output. 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) than use the seeded view FND_USER_RESP_GROUPS_INDIRECT.
As always adjust the SQL to cover your needs.
SELECT
fuser.USER_NAME USER_NAME
, per.FULL_NAME FULL_NAME
, per.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, frt.RESPONSIBILITY_NAME RESPONSIBILITY
FROM
FND_USER fuser
, PER_PEOPLE_F per
, FND_USER_RESP_GROUPS furg
, FND_RESPONSIBILITY_TL frt
WHERE
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'
ORDER BY
fuser.USER_NAME;
how to know what the end user is doing on particular day
Hi,
Can you be a little bit more specific? Oracle is logging in the tables which user is creating and updating etc or you enable Audit Trails. What are you trying to accomplish?
Regards,
Cyriel
Thanks a lot….was struggling to find the right tables to link all these together 🙂
, fuser.USER_NAME;
should read
fuser.USER_NAME;
You’re right. Changed!
Thanks for providing a nice information about SQL to extract active user list with responsibilities.