SQL to extract active user list with responsibilities

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;