Accessing and querying HCM data is perhaps the most common requirement for both BI report developers and IT Support teams working with Oracle Fusion. Companies always need data to be used for reports, dashboards, audits, and custom integrations. Whether it’s for creating headcount reports, payroll verification, or reconciliation after a migration, knowing how to query and extract HCM data using Oracle Fusion HCM tables SQL is crucial.
With Fusion Cloud, data is treated differently than with on-premises systems. In Oracle E-Business Suite (EBS) in the past, developers could query the database tables directly. With Fusion, though you’ll recognize table names such as PER_ALL_PEOPLE_F, the form and way to access the data are not the same.
Note: Like E-Business Suite, you’ll find a lot of familiar table names. But internal structure and relationships may be different, so queries using Oracle Fusion HCM tables SQL will need to be crafted with caution.
Some common challenges include understanding how tables relate to each other, working with date-effective records, and ensuring that multiple versions of employee data don’t cause duplicates in your results.
By the end of this article, you’ll learn how to identify key HCM tables, write correct joins, and extract data safely and efficiently using Oracle Fusion HCM tables SQL and other Oracle-supported methods.
| Pro tip: Tools like CloudSQL make exploring and querying HCM tables faster and simpler by providing database browser, table auto suggestion and describe table option. |
Understanding the Oracle Fusion HCM Data Model
Oracle Fusion HCM’s data model is designed based on entities like person, assignment, job, location, and department. Each entity is implemented in one or more tables storing data over time using date-effective storage.
You can refer to the official Oracle documentation on date-effective tables here for more details.
This means that every record in an HCM table has an EFFECTIVE_START_DATE and an EFFECTIVE_END_DATE indicating when that data is valid. The system does not allow overlapping date ranges for the same record, which ensures historical accuracy and supports “as-of-date” reporting.
The person object sits at the heart of the HCM data model. Nearly every other table, assignment, job, salary, or location, ultimately relates back to the person via a key like PERSON_ID.
Fusion HCM data can be broadly grouped into three categories:
| Category | Examples | Description |
| Work Structure Data | Locations, Positions, Jobs, Grades, Departments | Defines the organizational framework such as where employees work, their positions, and hierarchical structures. |
| Worker Data | Person, Addresses, Phones, Email Addresses, National Identifiers, Citizenship, Employment Data (Assignments, Work Relationships) | Stores all personal and employment-related information for individuals. |
| Payroll Data | Salary, Elements, Payroll Relationships | Contains financial and payroll calculation details. |
Fusion follows specific naming conventions:
- Tables ending with _F (e.g., PER_ALL_PEOPLE_F) are date-effective tables.
- Tables ending with _TL store translatable text, meaning you can retrieve labels and descriptions in multiple languages.
Tip: When querying data, always check whether a table is date-effective or translation-enabled. This determines how you filter records and join them with others.
Below is a simplified visualization of the core HCM relationships:

Also Read: Top 10 Oracle Fusion SQL Mistakes Developers Make
Key HCM Tables Every Developer Should Know
Understanding which tables hold the data you need is the first step to writing meaningful queries. The table below lists some of the most frequently used HCM tables in Oracle Fusion:
| Area | Common Tables | Description / Use Case |
| Person | PER_ALL_PEOPLE_F, PER_PERSON_NAMES_F, PER_LEGISLATIVE_DATA_GROUPS_F | Store core employee demographic data such as names, personal details, and legislative grouping. |
| Assignments | PER_ALL_ASSIGNMENTS_F | Contains employment details like job, position, department, and business unit. |
| PER_EMAIL_ADDRESSES | Holds employee email addresses. | |
| National Identifier | PER_NATIONAL_IDENTIFIERS | Stores national IDs such as PAN or SSN. |
| Addresses | PER_ADDRESSES | Contains employee address details. |
| Jobs & Positions | PER_JOBS, HR_ALL_POSITIONS_F | Maintain job codes, position hierarchy, and job descriptions. |
| Departments | HR_ALL_ORGANIZATION_UNITS_F | Define organization structures and cost centers. |
| Grades | PER_GRADES | Represent pay grades and levels. |
| Locations | HR_LOCATIONS_ALL_F | Store location and work address information. |
| Supervisors / Managers | PER_ASSIGNMENT_SUPERVISORS_F | Define reporting hierarchies between employees and managers. |
These tables are views exposed via the Fusion schema, not physical base tables.
They are designed to simplify access for reporting and ensure compliance with Oracle’s SaaS model.
Tip: If you are developing reusable SQL reports or integrations, take time to understand the underlying database structure. This helps in creating consistent joins and improves performance.
Views are there. Can be used for easy access. But if you are writing an SQL that is to be reused then better to understand underlying database structure and write a view
- Reference Oracle documentation for HCM Tables and views
HCM table and views link
How to Join Oracle Fusion HCM Tables SQL?
In Oracle Fusion HCM, the majority of reports involve joining data from multiple tables, for instance, joining an individual’s details with his/her job or department. The most significant relationship begins between the PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F tables, joined by the PERSON_ID column.
Fusion HCM uses date-effective tables, meaning each record has start and end dates that define when the data is valid. So, every _F table should include effective date filters to make sure you’re retrieving the current record and not outdated history.
SELECT
papf.person_id,
papf.Person_number,
paaf.assignment_id,
paaf.job_id,
paaf.position_id
FROM
PER_ALL_PEOPLE_F papf,
PER_ALL_ASSIGNMENTS_F paaf
WHERE
papf.person_id = paaf.person_id
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
Tip: Always use clear aliases like papf (for PER_ALL_PEOPLE_F) and paaf (for PER_ALL_ASSIGNMENTS_F), this is an Oracle-recommended convention that keeps your SQL readable.
Also, never skip date filters on date-effective tables; omitting them often returns duplicate or outdated records.
Tools like CloudSQL can also show table descriptions and structures visually, so you can confirm the joins before writing the SQL.
Common Pitfalls When Querying Oracle Fusion HCM Tables
Even experienced Fusion developers make small mistakes that lead to incorrect or duplicated results when querying Oracle Fusion HCM tables. Below are some of the most frequent pitfalls and how to avoid them.
Forgetting to Apply Date Filters
Date-effective tables (those ending with _F) store multiple records for the same entity across time. If you don’t filter by effective dates, your query may return duplicate or outdated records.
When you join multiple _F tables, you must apply a date condition for each table using its alias, for example:
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
This ensures your result set reflects only current and valid records.
Using SELECT * Instead of Specific Columns
Avoid SELECT *. Fetching all columns consumes unnecessary resources and can slow down queries significantly.
Instead, list only the columns you need, it’s faster, cleaner, and easier to maintain.
Not Understanding One-to-Many Relationships
Certain tables hold multiple records for a single entity. For example, a person can have multiple names in different languages, all stored in PER_PERSON_NAMES_F.
If you directly join it with PER_ALL_PEOPLE_F, you will get duplicate rows unless you add a condition like:
AND PPNF.NAME_TYPE = ‘GLOBAL’
This ensures you only fetch one record per person.
Not Understanding _USAGES_F Tables
A common misconception is that a person’s type (Employee, Contingent Worker, Ex-Employee, etc.) is stored directly in PER_PERSON_TYPES.
However, the actual relationship goes through PER_PERSON_TYPE_USAGES_F, which maps a person to their current and historical types.
A single person can have multiple types, for example, someone might have been a Contingent Worker earlier and later became a Permanent Employee.
Oracle handles this by linking person data through the USAGES_F table. Here’s a simple diagram of this relationship:

The same concept applies to tables like PER_ADDRESS_USAGES_F, which track the different usages (e.g., Home, Work) of a person’s address.
Not Understanding Supervisor or Position Hierarchies
In HCM, hierarchies like supervisor or position reporting are recursive — meaning an employee can report to a manager who is also part of the same structure.
Writing queries for these hierarchies requires CONNECT BY or recursive CTEs to traverse levels properly.
Missing Language Joins with _TL Tables
When you want translated labels (for example, job titles or department names), you need to join the corresponding translation tables (those ending with _TL) using both the key column and LANGUAGE filter:
PER_JOBS_TL PJTL
WHERE PJTL.JOB_ID = PJ.JOB_ID
AND PJTL.LANGUAGE = ‘US’
Without this, your output might show internal codes instead of readable descriptions.
| Pro Tip: Always review your join logic carefully. Use table aliases, apply date filters for every _F table, and filter translation or usage tables correctly, these small steps ensure your HCM queries are clean, accurate, and performant. |
Best Practices for Oracle Fusion HCM Tables SQL Queries
Querying Oracle Fusion HCM tables can be tricky due to their date-effective nature and complex relationships. Following best practices ensures your queries are efficient, accurate, and maintainable.
Filter Early
Always apply WHERE conditions to narrow down results as soon as possible.
For example:
- Filter by person type (Employee, Contingent Worker, etc.)
- Filter by effective dates to fetch only active or relevant records
This reduces unnecessary data processing and avoids duplicates.
Use Meaningful Aliases
Short aliases like papf for PER_ALL_PEOPLE_F and paaf for PER_ALL_ASSIGNMENTS_F make queries easier to read.
If your team has a convention for aliases, stick to it — consistency improves maintainability, especially in shared SQL scripts.
Select Only Required Columns
Avoid SELECT *. Fetching all columns can slow down queries and make debugging harder. Pick only the columns you need for your report or integration.
Leverage Key Joins and Indexes
Columns like PERSON_ID and ASSIGNMENT_ID are primary keys and commonly indexed. Using them in joins improves query performance and ensures accurate relationships.
Validate Results
Always double-check your results:
- Compare employee counts with functional HR teams.
- Ensure there are no duplicate records from joins with date-effective tables.
Use Tools to Explore Relationships
Tools like CloudSQL can:
- Auto-suggest joins
- Describe table structure
This helps you quickly understand the schema and reduces errors.
Pro Tip: In HCM queries, even small mistakes like missing a date filter or ignoring one-to-many relationships can produce incorrect results. Following these best practices ensures that your reports are reliable and ready for production.
Example: Building an HR Report
To put everything into practice, let’s build a simple HR report. We’ll retrieve active employees along with their job title and department, using the best practices discussed earlier
Example 1: Active Employees with Job and Department
SQL snippet:
SELECT
papf.person_id,
papf.person_number,
pj.name job_name,
haouf.name organization_name
FROM
PER_ALL_PEOPLE_F papf,
PER_ALL_ASSIGNMENTS_F paaf,
PER_JOBS_f_vl pj,
HR_ALL_ORGANIZATION_UNITS_F_vl haouf
WHERE
papf.person_id = paaf.person_id
AND paaf.job_id = pj.job_id
AND paaf.organization_id = haouf.organization_id
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
Explanation:
- papf is the alias for the person table; paaf for assignment table.
- Date filters on both tables ensure only current records are included.
- Joining PER_JOBS (pj) and HR_ALL_ORGANIZATION_UNITS_F (haouf) brings in job titles and department names.
2. all persons with their addresses.
SELECT
papf.person_id,
papf.person_number,
paad.address_line_1,
paad.country,
paad.postal_code
FROM
PER_ALL_PEOPLE_F papf,
PER_PERSON_ADDR_USAGES_F ppauf,
PER_ADDRESSES_F paad
WHERE
papf.person_id = ppauf.person_id
AND ppauf.address_id = paad.address_id
AND SYSDATE BETWEEN ppauf.effective_start_date AND ppauf.effective_end_date
AND SYSDATE BETWEEN paad.effective_start_date AND paad.effective_end_date
Explanation:
- Addresses are stored in two tables: PER_PERSON_ADDR_USAGES_F maps address to people, and PER_ADDRESSES contains the address details.
- Date-effective filters are applied to the usages table to ensure only current addresses are returned.
Tip: Always verify the results with functional teams, especially when joining tables with one-to-many relationships like addresses or person names, to avoid duplicates.
Must Read: Migrating Data from Oracle eBusiness Suite to Oracle Fusion Cloud ERP
Advanced Scenarios
Once you are comfortable with basic HCM queries, you may encounter scenarios that require more advanced techniques. Here are some common cases:
Historical Reporting
Often, HR needs reports as of a particular date rather than current data. Instead of using SYSDATE, use a bind variable (e.g., :P_AS_OF_DATE) in your date filters:
AND :P_AS_OF_DATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND :P_AS_OF_DATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
This retrieves the exact snapshot of employee data for the chosen date.
Supervisory Hierarchy Reports
In Fusion HCM, PER_ASSIGNMENT_SUPERVISORS_F tracks multiple supervisor relationships, but it does not have a direct “supervisor assignment” column. Instead, supervisor details are usually available via:
- PER_ALL_ASSIGNMENTS_F.manager_id (or your instance-specific column for reporting manager)
- PER_ASSIGNMENT_SUPERVISORS_F for validating multiple supervisor relationships
A practical query to get employees with their supervisors looks like this
SELECT
emp.assignment_id AS employee_assignment,
emp.person_id AS employee_id,
sup.assignment_id AS supervisor_assignment,
sup.person_id AS supervisor_id,
emp.job_id,
emp.position_id
FROM
PER_ASSIGNMENT_SUPERVISORS_F pasf,
PER_ALL_ASSIGNMENTS_F emp,
PER_ALL_ASSIGNMENTS_F sup
WHERE
pasf.assignment_id = emp.assignment_id
AND pasf.MANAGER_ASSIGNMENT_ID = sup.assignment_id
AND SYSDATE BETWEEN emp.effective_start_date AND emp.effective_end_date
AND SYSDATE BETWEEN sup.effective_start_date AND sup.effective_end_date
Explanation:
- PER_ASSIGNMENT_SUPERVISORS_F maps employee assignments to supervisor assignments.
- PER_ALL_ASSIGNMENTS_F is joined twice: once for the employee, once for the supervisor.
- Date-effective filters are applied using SYSDATE.
- Use MANAGER_FLAG = ‘Y’ in the sup table if you want to filter only supervisors.
Tip: Always test your query for duplicates because one employee may have multiple supervisor assignments.
Multi-Language Reports
Fusion stores labels and descriptions in _TL tables (translation tables). To get job titles or department names in a specific language, join the translation table and filter by language:
PER_JOBS_TL pjtl
WHERE pj.job_id = pjtl.job_id
AND pjtl.language = ‘US’;
This ensures that your reports show readable labels instead of internal codes.
Cross-Module Reporting
You may need to combine HCM data with Payroll or Financials. For example:
- Linking employee records with payroll elements
- Fetching expense reports or invoices for specific employees
When doing cross-module queries, always:
- Apply date filters for all date-effective tables
- Use aliases consistently to avoid confusion
- Validate results with functional teams
Pro Tip: Start with the main entity (usually PER_ALL_PEOPLE_F), then join related tables step by step. Test your query at each stage to ensure accuracy before adding more tables
Troubleshooting SQL Issues
Even experienced developers encounter issues when querying Fusion HCM tables. Understanding common errors and their solutions can save a lot of time.
“ORA-00918: Column ambiguously defined”
This error occurs when two or more tables have columns with the same name (e.g., PERSON_ID, EFFECTIVE_START_DATE) and the query does not use table aliases.
Solution:
- Always assign aliases to tables (papf for PER_ALL_PEOPLE_F, paaf for PER_ALL_ASSIGNMENTS_F, etc.).
- Prefix column names with the alias in SELECT and WHERE clauses.
SELECT
papf.person_id,
paaf.assignment_id
FROM
PER_ALL_PEOPLE_F papf,
PER_ALL_ASSIGNMENTS_F paaf
WHERE
papf.person_id = paaf.person_id
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
“ORA-01427: single-row subquery returns more than one row”
This happens when a subquery is expected to return one value but returns multiple rows — for example, querying national identifiers or multiple person names.
Solution:
- Ensure filters are applied to get a single row, such as NAME_TYPE = ‘GLOBAL’ or ROWNUM = 1.
- When expecting multiple rows, avoid using a scalar subquery; instead, use a JOIN
SELECT
papf.person_id,
nid.national_identifier_number
FROM
PER_ALL_PEOPLE_F papf,
PER_NATIONAL_IDENTIFIERS nid
WHERE
papf.person_id = nid.person_id
AND nid.NATIONAL_IDENTIFIER_TYPE = 'SSN'
AND ROWNUM = 1
Date-effective issues
- Forgetting to apply EFFECTIVE_START_DATE / EFFECTIVE_END_DATE filters may include outdated or future records, causing duplicates or incorrect results.
- When joining multiple _F tables, apply date filters for each table individually.
One-to-many relationship misunderstandings
- Some tables store multiple rows per employee, e.g., PER_PERSON_NAMES_F for different languages.
- Always filter using the appropriate column (NAME_TYPE) to avoid duplicates.
Using CloudSQL for validation
Tools like CloudSQL can help identify and prevent errors:
- Auto-suggests tables and columns
- Shows table columns and primary keys
Tip: Always test queries step by step. Validate counts and sample data with functional HR teams to ensure accuracy.
Conclusion
Querying Oracle Fusion HCM tables SQL requires more than just knowing the table names, it’s about understanding date effectiveness, relationships, and business context.
By mastering how to join key tables like PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_F, and PER_ASSIGNMENT_SUPERVISORS_F, you can extract accurate, real-time workforce data that supports analytics and decision-making.
Always remember:
- Apply effective date filters to every _F table
- Use table aliases for readability and to avoid ambiguity
- Validate joins based on person_id, assignment_id, or usage relationships depending on the scenario
- Watch out for one-to-many joins that may cause duplicates
Once you understand these fundamentals, you will find querying HCM data in Fusion as powerful as it is flexible.
You May Read: Oracle Fusion BI Report Development Tool
Frequently Asked Questions (FAQs)
What are the main Oracle Fusion HCM tables?
The most frequently used Oracle Fusion HCM tables include PER_ALL_PEOPLE_F, which stores core person and employee details; PER_ALL_ASSIGNMENTS_F, capturing job, position, and department assignments; PER_PERSON_NAMES_F, containing employee names and language-specific entries; HR_ALL_ORGANIZATION_UNITS_F, defining departments and business units; and PER_ASSIGNMENT_SUPERVISORS_F, which tracks supervisor relationships and reporting hierarchies.
Each of these tables follows a date-effective design, meaning they store multiple records over time for the same person or assignment. To ensure accurate results, it is important to always apply date filters when joining these tables.
Why do I see duplicate rows in my HCM queries?
Duplicate rows often occur in HCM queries due to missing or incorrect date filters on _F tables, not applying conditions like NAME_TYPE = ‘GLOBAL’ when joining name or address tables, or because of one-to-many relationships, such as multiple phone numbers, email addresses, or name records for a single person.
To resolve this, review your joins carefully and ensure that each table includes the proper date conditions and any unique identifiers necessary to return a single, accurate record.
How can I improve performance and accuracy when querying Oracle Fusion HCM tables?
To improve both performance and accuracy, always filter by effective dates and person type (e.g., Employee or Contingent Worker), select only the required columns instead of using SELECT, and use meaningful table aliases for readability.
Additionally, understanding one-to-many relationships and joining translation tables (_TL) properly ensures that your queries return accurate, clean results. Leveraging tools like CloudSQL to explore table structures and relationships can further reduce errors and make writing complex joins easier.