Key Points to Note:
- PER_USERS is the primary table that stores user account information in Oracle Fusion.
- It links to employee records through the PERSON_ID column, which exists in both PER_USERS and PER_ALL_PEOPLE_F.
- Not all entries in PER_USERS will have a corresponding record in PER_ALL_PEOPLE_F.
These unmatched records usually represent:
- System accounts
- Integration or service users
Such users do not have associated employee profiles.
This is important to consider if you’re filtering only for named individuals (employees or contingent workers) versus all system-level users.
SELECT
usr.username,
name.display_name,
person.person_number,
usr.active_flag,
usr.start_date,
usr.end_date
FROM
per_users usr,
per_person_names_f name,
per_all_people_f person
WHERE
usr.person_id = name.person_id (+)
AND usr.person_id = person.person_id(+)
AND trunc(sysdate) BETWEEN name.effective_start_date(+) AND name.effective_end_date(+)
AND trunc(sysdate) BETWEEN person.effective_start_date(+) AND person.effective_end_date(+)
AND name.name_type (+)= 'GLOBAL'
