Blog

Extract User Details from Oracle Fusion involves understanding key tables and their relationships.

This guide explains how to Extract User Details from Oracle Fusion efficiently.

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';