Pages

Thursday, February 14, 2013

Employees and users script

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;