PER_ALL_PEOPLE_F: All You Need to Know 

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)