When using Oracle Fusion HCM, having knowledge of the data model is key to correct reporting, analytics, and integrations. One of the most basic tables in the HCM schema is PER_ALL_PEOPLE_F, the basis for all person-related information.
This table holds data for persons, which includes employees, contingent workers, applicants, and dependents. The “_F” in the table name represents Date-Effective (From–To), and that is why each record in this table is associated with a particular date range. This design enables Oracle Fusion to keep a complete history of changes to an individual’s data over time.
If you have ever created an HR or user-access report in Oracle Fusion, you’ve likely used this table. At the end of this article, you’ll know what it contains, how to join with other tables, and how to effectively use it in SQL queries.
What is PER_ALL_PEOPLE_F?
The PER_ALL_PEOPLE_F table serves as the master repository for all personal data within Oracle Fusion’s HCM module. It contains one record per person per date range, capturing every change in personal information such as name, status, or employment type.
Each person in Oracle Fusion is identified by a unique person number stored in the PERSON_NUMBER column. This number remains constant throughout the person’s lifecycle in the organization, regardless of role or employment status.
You can learn more about automatic and manual person number generation in Oracle Fusion here.
The “ALL” in PER_ALL_PEOPLE_F indicates that it includes all person types, such as employees, ex-employees, contacts, and applicants. These person types are defined in the PER_PERSON_TYPES table and linked through PER_PERSON_TYPE_USAGES_F.
Key Characteristics
| Attribute | Description |
| Date-effective | Each person can have multiple rows for different time periods. |
| Schema | Stored under the HCM schema in the Fusion database. |
| Purpose | Acts as the foundation for HR, Payroll, and Security data. |
Common Use Cases
| Scenario | Description |
| HR Reporting | Generate lists of active employees and their addresses. |
| Payroll Extract | Retrieve employee and assignment information for payroll processing. |
| Access Validation | Join with PER_USER to audit employee login access. |
Key Columns in PER_ALL_PEOPLE_F
Understanding the structure of this table is crucial before writing queries, as it helps you identify how person-related data is stored, how effective dates are managed, and how this table connects with other HCM tables such as PER_ALL_ASSIGNMENTS_F and PER_PERSON_TYPES.
| Column Name | Description |
| PERSON_ID | The primary key that uniquely identifies each person record. |
| PERSON_NUMBER | A unique identifier visible to users across the system. |
| EFFECTIVE_START_DATE / EFFECTIVE_END_DATE | Defines when a record is valid. |
| PERSON_TYPE_ID | Indicates the type of person (linked via PER_PERSON_TYPE_USAGES_F). |
| BUSINESS_GROUP_ID | Identifier of Enterprise, used for multi-tenancy partitioning. Foreign key to HR_ORGANIZATION_UNITS. |
| CREATION_DATE / LAST_UPDATE_DATE | Timestamps for record creation and updates |
Tip: When writing queries, always include both effective date columns to ensure you retrieve only the current or relevant historical data.
To know more about the structure, please refer to the oracle documentation of PER_ALL_PEOPLE_F
Date-Effectiveness in PER_ALL_PEOPLE_F
The _F suffix means the table is date-tracked, allowing multiple records for the same person over time. Each change, such as address updates or transfers, creates a new record with updated effective dates.
A simple best practice query to get the current active record looks like this:
SELECT
ROWNUM,
PAPF.PERSON_ID,
PAPF.EFFECTIVE_START_DATE,
PAPF.EFFECTIVE_END_DATE,
PAPF.ATTRIBUTE1
FROM
PER_ALL_PEOPLE_F PAPF
WHERE
SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
;
The result of the above SQL query is shown below
| ROWNUM | PERSON_ID | EFFECTIVE_START_DATE | EFFECTIVE_END_DATE | ATTRIBUTE1 |
| 1 | 300000107920785 | 04-04-2010 | 23-07-2015 | London |
| 2 | 300000107920785 | 24-07-2015 | 12-08-2015 | Paris |
| 3 | 300000107920785 | 13-08-2015 | 18-08-2015 | New York |
| 4 | 300000107920785 | 19-08-2015 | 31-12-4712 | London |
In Oracle Fusion, the end date “31-DEC-4712” virtually represents infinity, meaning the record is currently active without an expiry.
Fun fact: If Fusion still runs in the year 4712, it will face a new “Y2K-like” issue!
Also Read: How to Query Oracle Fusion HCM Tables SQL?
Join Between PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F
While PER_ALL_PEOPLE_F stores information about who a person is, the PER_ALL_ASSIGNMENTS_F table contains information about what that person does, including job, position, department, and assignment details.
These two tables can be joined using the PERSON_ID column and filtered by effective dates to ensure only valid records are retrieved:
SELECT
PAPF.PERSON_NUMBER,
PAAF.ASSIGNMENT_NUMBER,
PAAF.JOB_ID,
PAAF.ORGANIZATION_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
;
This join is commonly used to generate active employee lists with job and department details or to track assignments across business units.
Joining PER_ALL_PEOPLE_F with PER_USER
In Oracle Fusion, user login information is stored in the PER_USERS table. By joining PER_USERS with PER_ALL_PEOPLE_F and PER_PERSON_NAMES_F, you can map system users to employees and retrieve their full names in a specific language. For example:
SELECT
PU.USERNAME,
PPNF.FULL_NAME,
PAPF.PERSON_NUMBER
FROM
PER_USERS PU,
PER_ALL_PEOPLE_F PAPF,
PER_PERSON_NAMES_F PPNF
WHERE
PU.PERSON_ID = PAPF.PERSON_ID
AND PAPF.PERSON_ID = PPNF.PERSON_ID
AND PPNF.NAME_TYPE = 'GLOBAL'
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
;
This join is commonly used to audit user access against employment status and to identify users without active employee records
Joining PER_ALL_PEOPLE_F with PER_PERSON_NAMES_F
In Oracle Fusion, a person’s name information is stored in the PER_PERSON_NAMES_F table, which supports multiple languages. Each record in this table includes a NAME_TYPE field that identifies the type of name, such as “GLOBAL” for the default name used across the application. The following query demonstrates how to join this table with PER_ALL_PEOPLE_F using Oracle-style syntax
SELECT
PAPF.PERSON_NUMBER,
PPNF.FULL_NAME,
PPNF.NAME_TYPE
FROM
PER_ALL_PEOPLE_F PAPF,
PER_PERSON_NAMES_F PPNF
WHERE
PAPF.PERSON_ID = PPNF.PERSON_ID
AND PPNF.NAME_TYPE = 'GLOBAL'
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
Joining PER_ALL_PEOPLE_F with PER_PEOPLE_LEGISLATIVE_F
In Oracle Fusion, personal details such as marital status, gender, and other country-specific information are stored in the PER_PEOPLE_LEGISLATIVE_F table. This table is designed to manage person-related information that varies based on country-specific legislation.
Each record in this table includes a LEGISLATION_CODE, which identifies the applicable country or region, for example, ‘US’ for the United States or ‘IN’ for India.
The separation of this data into a legislative-specific table ensures that Oracle Fusion remains compliant with regional requirements. For instance, while some countries may only recognize “Single” and “Married” as valid marital statuses, others may include additional values such as “Civil Partnership” or “Separated.”
By storing this information in PER_PEOPLE_LEGISLATIVE_F, Fusion can adapt to these differences without affecting the core person data stored in PER_ALL_PEOPLE_F.
When writing SQL queries that retrieve marital status or gender, it is essential to include both date-effective filters and legislation code filters. This ensures that the query returns only the current, valid records. The following example shows how to join PER_ALL_PEOPLE_F with PER_PEOPLE_LEGISLATIVE_F using Oracle-style syntax:
SELECT
PAPF.PERSON_NUMBER,
PPLF.MARITAL_STATUS,
PPLF.SEX,
PPLF.LEGISLATION_CODE
FROM
PER_ALL_PEOPLE_F PAPF,
PER_PEOPLE_LEGISLATIVE_F PPLF
WHERE
PAPF.PERSON_ID = PPLF.PERSON_ID
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PPLF.EFFECTIVE_START_DATE AND PPLF.EFFECTIVE_END_DATE
AND PPLF.LEGISLATION_CODE = 'US'
Understanding PERSON_TYPE_ID
In Oracle Fusion, every record in the PER_ALL_PEOPLE_F table includes a column named PERSON_TYPE_ID. This column defines what kind of person the record represents, for example, an Employee, Contingent Worker, Ex-Employee, or Applicant.
The value stored in PERSON_TYPE_ID is a numeric reference (foreign key) that links to the PER_PERSON_TYPES table, where the technical definitions of each person type are maintained.
However, in order to retrieve the human-readable name of a person type, such as “Employee” or “Contingent Worker,” you must join the PER_PERSON_TYPES table with its translation table, PER_PERSON_TYPES_TL, using the common column PERSON_TYPE_ID.
The translation table (_TL) stores display names in different languages, allowing Oracle Fusion to support global deployments.
To determine a person’s current type at any point in time, it is recommended to join PER_ALL_PEOPLE_F with the PER_PERSON_TYPE_USAGES_F table. This table provides a date-effective relationship between a person and their applicable person type. It ensures that historical changes, such as an applicant being hired as an employee, are correctly tracked across time.
The following SQL demonstrates how to retrieve each person’s current user person type using Oracle-style join syntax with appropriate date-effective filters.
SELECT
PAPF.PERSON_NUMBER,
PPTTL.USER_PERSON_TYPE,
PPT.SYSTEM_PERSON_TYPE
FROM
PER_ALL_PEOPLE_F PAPF,
PER_PERSON_TYPE_USAGES_F PPTUF,
PER_PERSON_TYPES PPT,
PER_PERSON_TYPES_TL PPTTL
WHERE
PAPF.PERSON_ID = PPTUF.PERSON_ID
AND PPTUF.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPT.PERSON_TYPE_ID = PPTTL.PERSON_TYPE_ID
AND PPTTL.LANGUAGE = 'US'
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PPTUF.EFFECTIVE_START_DATE AND PPTUF.EFFECTIVE_END_DATE
Difference Between PER_PERSONS and PER_ALL_PEOPLE_F
In Oracle Fusion HCM, it is essential to understand the relationship between the PER_ALL_PEOPLE_F and PER_PERSONS tables. Although both store information about individuals, their design and purpose are quite different.
The table PER_ALL_PEOPLE_F is the primary source of person information in Fusion and is date-effective. This means it maintains a history of changes for each person, such as name updates, marital status changes, or address modifications, along with effective start and end dates. Each record represents a version of the person’s data that was valid for a specific period.
On the other hand, PER_PERSONS is a non–date-effective table that serves as the parent entity in the person model. It contains a single record for each unique individual in the system, regardless of how many effective-dated records exist in PER_ALL_PEOPLE_F.
This table provides the core identifiers such as PERSON_ID, PERSON_GUID, and other static attributes that do not change over time.
In simpler terms, you can think of PER_PERSONS as the “who” (the permanent person identity), and PER_ALL_PEOPLE_F as the “when” (the person’s data across time). The following table highlights the key differences between these two tables:
| Feature | PER_ALL_PEOPLE_F | PER_PERSONS |
| Purpose | Stores date-effective details of all persons (employees, applicants, contacts, dependents, etc.) | Stores a single, non–date-tracked record per person |
| Data Type | Date-effective (historical and current) | Non–date-effective (static) |
| Primary Key | PERSON_ID + EFFECTIVE_START_DATE + EFFECTIVE_END_DATE | PERSON_ID |
| History Tracking | Yes — stores all changes over time | No — holds only one record per person |
| Typical Use Case | HR and reporting queries that need current or historical data | Joining to other entities or identifying a unique person |
| Schema | HCM Schema | HCM Schema |
To illustrate the relationship between these tables, consider the following Oracle-style SQL query, which retrieves each person’s most recent effective record:
SELECT
PPER.PERSON_ID,
PAPF.PERSON_NUMBER,
PAPF.EFFECTIVE_START_DATE,
PAPF.EFFECTIVE_END_DATE
FROM
PER_PERSONS PPER,
PER_ALL_PEOPLE_F PAPF
WHERE
PPER.PERSON_ID = PAPF.PERSON_ID
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
This query links PER_PERSONS with PER_ALL_PEOPLE_F to return the current version of each person’s record. Since PER_ALL_PEOPLE_F is date-effective, it is crucial to include date filters like SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE to ensure that only valid records are returned
Tip: When creating person-based joins in Fusion HCM, always start with PER_PERSONS as the parent and connect it to PER_ALL_PEOPLE_F for time-sensitive information. This approach ensures better query performance and accurate reporting.
Must Read: Top 10 Oracle Fusion SQL Mistakes Developers Make
Common Queries Using PER_ALL_PEOPLE_F
The PER_ALL_PEOPLE_F table is one of the most frequently queried tables in Oracle Fusion HCM. It stores core, date-effective information for all persons in the system including employees, applicants, contingent workers, dependents, and contacts.
However, most business reports and extracts require additional details, such as a person’s name, email address, assignment, or person type, which are stored in related tables.
List of Active Persons
To retrieve a list of currently active persons, you need to filter the date-effective records in PER_ALL_PEOPLE_F and join to the PER_PERSON_NAMES_F table to fetch the employee’s name.
SELECT
PAPF.PERSON_NUMBER,
PPNF.FULL_NAME,
PEA.EMAIL_ADDRESS
FROM
PER_ALL_PEOPLE_F PAPF,
PER_PERSON_NAMES_F PPNF,
PER_EMAIL_ADDRESSES PEA
WHERE
PAPF.PERSON_ID = PPNF.PERSON_ID
AND PPNF.NAME_TYPE = 'GLOBAL'
AND PAPF.PERSON_ID = PEA.PERSON_ID
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
Employees and Their Assignments
Employee assignments, such as job title or department, are stored in the PER_ALL_ASSIGNMENTS_F table. You can join this table with PER_ALL_PEOPLE_F to link each person to their corresponding assignment number.
SELECT PAPF.PERSON_NUMBER,
PPNF.FULL_NAME,
PAAF.ASSIGNMENT_NUMBER
FROM
PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
PER_PERSON_NAMES_F PPNF
WHERE
PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAPF.PERSON_ID = PPNF.PERSON_ID
AND PPNF.NAME_TYPE = 'GLOBAL'
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
User Accounts and Person Type Information
In Oracle Fusion, user accounts and their related person types are stored across several tables. The PER_PERSON_TYPES_TL table contains the translatable user-facing label for each person type, while the join between PER_ALL_PEOPLE_F and PER_PERSON_TYPE_USAGES_F determines which person type a user belongs to.
The following query retrieves each person’s number and user person type in English (LANGUAGE = ‘US’):
SELECT
PAPF.PERSON_NUMBER,
PPTTL.USER_PERSON_TYPE,
PPT.SYSTEM_PERSON_TYPE
FROM
PER_ALL_PEOPLE_F PAPF,
PER_PERSON_TYPE_USAGES_F PPTUF,
PER_PERSON_TYPES PPT,
PER_PERSON_TYPES_TL PPTTL
WHERE
PAPF.PERSON_ID = PPTUF.PERSON_ID
AND PPTUF.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPT.PERSON_TYPE_ID = PPTTL.PERSON_TYPE_ID
AND PPTTL.LANGUAGE = 'US'
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PPTUF.EFFECTIVE_START_DATE AND PPTUF.EFFECTIVE_END_DATE
Employee Legislative Information
Country-specific information, such as gender or marital status, is stored in PER_PEOPLE_LEGISLATIVE_F. The query below joins this table with PER_ALL_PEOPLE_F to retrieve legislative details for each active person.
SELECT
PAPF.PERSON_NUMBER,
PPLF.MARITAL_STATUS,
PPLF.SEX,
PPLF.LEGISLATION_CODE
FROM
PER_ALL_PEOPLE_F PAPF,
PER_PEOPLE_LEGISLATIVE_F PPLF
WHERE
PAPF.PERSON_ID = PPLF.PERSON_ID
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PPLF.EFFECTIVE_START_DATE AND PPLF.EFFECTIVE_END_DATE
AND PPLF.LEGISLATION_CODE = 'US'
Best Practices When Working with PER_ALL_PEOPLE_F
Working with PER_ALL_PEOPLE_F requires precision, especially because it is a date-effective table that serves as the foundation of most HCM reports. Following a few standard practices can significantly improve both the accuracy and performance of your queries
Always Use Date Filters
Always include date-effective filters such as:
SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
This ensures that your query retrieves only the current active record and prevents duplicates from overlapping date ranges.
Use Parameters for “As Of” Dates
Instead of hardcoding SYSDATE, use a bind parameter (for example, :as_of_date) to run your query as of a specific date. This is particularly helpful in point-in-time reporting.
Include Person Type Joins
To filter by person category, always join PER_PERSON_TYPE_USAGES_F with PER_ALL_PEOPLE_F. This helps you distinguish between employees, contingent workers, and other categories.
PAPF.PERSON_ID = PPTUF.PERSON_ID
Avoid Using SELECT *
Selecting all columns can slow down your queries and return unnecessary data. Always select only the required fields to keep your query optimized and easier to read.
Use Consistent Aliases
Use clear aliases such as PAPF (for PER_ALL_PEOPLE_F) or PERSON (if working with multiple related tables). This improves readability and consistency across your SQL scripts.
Filter Correctly When Joining Name or Legislative Tables
- When joining with PER_PERSON_NAMES_F, always include:
- NAME_TYPE = ‘GLOBAL’
- When joining with PER_PEOPLE_LEGISLATIVE_F, use:
- LEGISLATION_CODE = ‘IN’ — Replace with country code
These filters ensure that you only retrieve valid and region-specific data.
Common Issues & Troubleshooting
Even experienced developers face issues when working with PER_ALL_PEOPLE_F. The following table lists common problems, their likely causes, and recommended solutions.
| Issue | Cause | Solution |
| Duplicate Rows | Missing or incorrect effective date filters; wrong table alias in date condition | Add SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE filters to every date-effective table used in the join. |
| Missing Users | Incorrect join or no matching record in PER_USERS | Ensure that EMPLOYEE_ID in PER_USERS matches PERSON_ID in PER_ALL_PEOPLE_F. |
| Wrong or Multiple Person Types | Incorrect or missing join with PER_PERSON_TYPE_USAGES_F | Join PER_PERSON_TYPE_USAGES_F, PER_PERSON_TYPES, and PER_PERSON_TYPES_TL, and filter for required types such as ‘EMP’ for employees. |
| Data Mismatch Across Modules | Assignment data is not synchronized or missing date filters | Verify joins with PER_ALL_ASSIGNMENTS_F and ensure that date-effective conditions are included. |
Using CloudSQL to Explore PER_ALL_PEOPLE_F
Exploring Oracle Fusion data can be challenging due to its complex table relationships and date-effective logic. CloudSQL simplifies this process by providing a developer-friendly interface for Fusion queries. With CloudSQL, you can:
- Browse Tables Easily: Use the built-in database browser to view available Fusion HCM tables, their columns, and relationships.
- Get Column Auto-Suggestions: Instantly access relevant column names while typing queries, reducing syntax errors.
- Use AI-Powered Fix Suggestions: Automatically detect and correct missing joins, date filters, or invalid conditions.
“With CloudSQL, you can explore Oracle Fusion HCM tables without worrying about syntax errors or remembering complex joins.”
Conclusion
PER_ALL_PEOPLE_F is one of the most important tables in Oracle Fusion HCM. It stores all core person records and acts as the foundation for many HR and payroll processes. To work effectively with this table:
- Understand that PER_ALL_PEOPLE_F is date-effective and use the correct filters to get accurate results.
- Always join it with related tables such as PER_PERSON_NAMES_F, PER_PERSON_TYPE_USAGES_F, and PER_ALL_ASSIGNMENTS_F to retrieve complete data.
- Follow the best practices outlined earlier to avoid duplication and data mismatch issues.
You Can Read: Guide to Migrating Data from Oracle eBusiness Suite to Oracle Fusion Cloud ERP
Frequently Asked Questions (FAQs)
What does PER_ALL_PEOPLE_F store in Oracle Fusion?
It stores date-effective person-level information such as person number, name, and system identifiers for all person types, employees, applicants, contacts, and dependents.
How to join PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F?
PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F are joined by join on the PERSON_ID column and apply date-effective filters for both tables:
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
What is the difference between PER_PEOPLE_F and PER_ALL_PEOPLE_F?
The main difference is that PER_ALL_PEOPLE_F is a base table with all person records, while PER_PEOPLE_F is a secured view of that table that filters the records based on the logged-in user’s security profile.
How to link PER_USER and PER_ALL_PEOPLE_F?
Use the join PU.EMPLOYEE_ID = PAPF.PERSON_ID to associate Fusion users with person records.