How to Query Oracle Fusion HCM Tables SQL?

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.
Email	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)