Complete Guide to PER_ALL_PEOPLE_F

PER_ALL_PEOPLE_F is the single most important table in Oracle HCM Cloud. It is the master person record: every employee, contingent worker, contact, and applicant has at least one row here. If you are building reports, integrations, or HCM Extracts, you will use this table constantly.

This guide covers its structure, key columns, date-effective behavior, common join patterns, and practical SQL examples.

Table Overview

• Full Name: PER_ALL_PEOPLE_F
• Module: Core HR (Human Resources)
• Type: Date-Effective Table (_F suffix)
• Primary Key: PERSON_ID + EFFECTIVE_START_DATE
• Translation Table: PER_ALL_PEOPLE_TL
• Convenience View: PER_ALL_PEOPLE_VL

Key Columns Reference

For the full column listing (60+ columns), view PER_ALL_PEOPLE_F in our table browser.

Date-Effective Behavior

Every change to a person's record creates a new date-effective row. Suppose an employee's record is first created on January 1, 2025:

PERSON_ID | EFFECTIVE_START_DATE | EFFECTIVE_END_DATE | PERSON_NUMBER
----------|---------------------|--------------------|---------------
    10001 | 2025-01-01          | 4712-12-31         | EMP001

When a correction is made on March 15, Oracle end-dates the old row and creates a new one:

PERSON_ID | EFFECTIVE_START_DATE | EFFECTIVE_END_DATE | PERSON_NUMBER
----------|---------------------|--------------------|---------------
    10001 | 2025-01-01          | 2025-03-14         | EMP001
    10001 | 2025-03-15          | 4712-12-31         | EMP001

This is why you must always filter on dates. Without the filter, a SELECT COUNT(*) on PER_ALL_PEOPLE_F will return a much larger number than your actual headcount.

Getting Current Records

-- Current person record
SELECT p.PERSON_ID, p.PERSON_NUMBER, p.DATE_OF_BIRTH
FROM PER_ALL_PEOPLE_F p
WHERE TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE
                          AND p.EFFECTIVE_END_DATE;

Getting Records as of a Specific Date

-- Person records as of January 1, 2025
SELECT p.PERSON_ID, p.PERSON_NUMBER
FROM PER_ALL_PEOPLE_F p
WHERE DATE '2025-01-01' BETWEEN p.EFFECTIVE_START_DATE
                              AND p.EFFECTIVE_END_DATE;

Common Join Patterns

Person + Name (Most Common Query)

Since names are stored separately, the most common query pattern joins PER_ALL_PEOPLE_F with PER_PERSON_NAMES_F:

SELECT
  p.PERSON_ID,
  p.PERSON_NUMBER,
  n.FIRST_NAME,
  n.LAST_NAME,
  n.DISPLAY_NAME
FROM PER_ALL_PEOPLE_F p
JOIN PER_PERSON_NAMES_F n
  ON p.PERSON_ID = n.PERSON_ID
  AND n.NAME_TYPE = 'GLOBAL'
  AND TRUNC(SYSDATE) BETWEEN n.EFFECTIVE_START_DATE
                          AND n.EFFECTIVE_END_DATE
WHERE TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE
                          AND p.EFFECTIVE_END_DATE;

Person + Assignment (Employee Details)

To get employee-level details like job, position, department, and business unit, join to PER_ALL_ASSIGNMENTS_F:

SELECT
  p.PERSON_ID,
  p.PERSON_NUMBER,
  n.DISPLAY_NAME,
  a.ASSIGNMENT_NUMBER,
  a.ASSIGNMENT_STATUS_TYPE,
  a.BUSINESS_UNIT_ID,
  a.JOB_ID,
  a.POSITION_ID,
  a.DEPARTMENT_ID,
  a.LOCATION_ID,
  a.MANAGER_ID
FROM PER_ALL_PEOPLE_F p
JOIN PER_PERSON_NAMES_F n
  ON p.PERSON_ID = n.PERSON_ID
  AND n.NAME_TYPE = 'GLOBAL'
  AND TRUNC(SYSDATE) BETWEEN n.EFFECTIVE_START_DATE AND n.EFFECTIVE_END_DATE
JOIN PER_ALL_ASSIGNMENTS_F a
  ON p.PERSON_ID = a.PERSON_ID
  AND a.PRIMARY_FLAG = 'Y'
  AND a.ASSIGNMENT_TYPE = 'E'
  AND TRUNC(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
WHERE TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE;

Person + Address

SELECT
  p.PERSON_ID,
  p.PERSON_NUMBER,
  addr.ADDRESS_LINE_1,
  addr.TOWN_OR_CITY,
  addr.REGION_2 AS state,
  addr.POSTAL_CODE,
  addr.COUNTRY
FROM PER_ALL_PEOPLE_F p
JOIN PER_ADDRESSES_F addr
  ON p.PERSON_ID = addr.PERSON_ID
  AND addr.ADDRESS_TYPE = 'HOME'
  AND TRUNC(SYSDATE) BETWEEN addr.EFFECTIVE_START_DATE AND addr.EFFECTIVE_END_DATE
WHERE TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE;

Person + Period of Service (Hire/Termination Dates)

SELECT
  p.PERSON_ID,
  p.PERSON_NUMBER,
  pos.DATE_START AS hire_date,
  pos.ACTUAL_TERMINATION_DATE,
  pos.PERIOD_TYPE
FROM PER_ALL_PEOPLE_F p
JOIN PER_PERIODS_OF_SERVICE pos
  ON p.PERSON_ID = pos.PERSON_ID
  AND pos.PRIMARY_FLAG = 'Y'
WHERE TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE;

Related Tables

PER_ALL_PEOPLE_F is the hub that connects to these key tables via PERSON_ID:

Common Pitfalls

1. Forgetting the date filter — You will get inflated row counts and duplicate records. Always filter on EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
2. Looking for names in PER_ALL_PEOPLE_F — Names are in PER_PERSON_NAMES_F, not here.
3. Not filtering by NAME_TYPE — When joining to PER_PERSON_NAMES_F, always include NAME_TYPE = 'GLOBAL' (or the specific type you need).
4. Not filtering by ASSIGNMENT_TYPE — PER_ALL_ASSIGNMENTS_F contains multiple assignment types (E=Employee, C=Contingent Worker, O=Offer, etc.). Filter to avoid cross-type duplicates.
5. Using PERSON_ID as a unique key — It is unique per person but not per row. The true primary key is PERSON_ID + EFFECTIVE_START_DATE.

OTBI / BIP Report Tips

When building Oracle Transactional Business Intelligence (OTBI) or BI Publisher (BIP) reports:

• Prefer the subject areas (e.g., "Workforce Management - Person Real Time") over direct table queries when possible
• Use PER_ALL_PEOPLE_VL for views that automatically join translation data
• In BIP data models, always include the date-effective filter as a bind parameter
• For headcount reports, use the assignment table's ASSIGNMENT_STATUS_TYPE = 'ACTIVE' rather than trying to derive it from PER_ALL_PEOPLE_F

Related Articles

• Oracle HCM Table Suffixes Explained (_F, _TL, _VL, _ALL)
• Top 50 Most Used Oracle Fusion HCM Tables