How to Query Worker Data Using PER_ALL_PEOPLE_F Table: The Complete Oracle HCM Developer Guide

Key Takeaways

Question	Answer
What is PER_ALL_PEOPLE_F?	A date-tracked Oracle Fusion HCM table storing core person-level demographic and identification data for every person record in the system.
Why does it return duplicate rows?	Because it is date-tracked, every change creates a new row. You must filter by EFFECTIVE_START_DATE and EFFECTIVE_END_DATE to get current data only.
What is the primary key?	PERSON_ID combined with EFFECTIVE_START_DATE and EFFECTIVE_END_DATE forms the unique key for each row.
How do I get current worker records only?	Use WHERE SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE in every query targeting this table.
Which tables join to PER_ALL_PEOPLE_F?	Key joins include PER_ALL_ASSIGNMENTS_M, PER_PERIODS_OF_SERVICE, PER_PERSON_NAMES_F, and HR_ALL_ORGANIZATION_UNITS_F.
Where is the official Oracle documentation?	The official column reference is published on the Oracle Fusion HCM Data Model reference for PER_ALL_PEOPLE_F.
Can I use PER_ALL_PEOPLE_F in OTBI?	OTBI abstracts this table through subject areas. For direct SQL access, use BI Publisher or HCM Extracts pointing to the underlying table.

What Is the PER_ALL_PEOPLE_F Table in Oracle Fusion HCM?

The PER_ALL_PEOPLE_F table is the core person repository within the oracle fusion hcm data architecture. It holds person-level data including date of birth, national identifier, person type, and system person type for every individual stored in Oracle Cloud HCM, whether they are employees, contractors, or contingent workers.

The "F" suffix is the first detail that tells you everything: it signals a date-tracked (or "DateEffective") table. This means the table maintains a full history of every change made to a person record by storing each version as a separate row, bounded by EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.

Understanding this structure is not optional for HCM developers. It is the grain of the table, and every SQL query you write against PER_ALL_PEOPLE_F must respect it.

"Every row in PER_ALL_PEOPLE_F tells the story of who a person was between two specific dates. Query without those bounds, and you are reading every chapter at once."

Understanding the HCM Data Model: Where PER_ALL_PEOPLE_F Fits

The hcm data model in Oracle Fusion Cloud separates person data from employment data deliberately. PER_ALL_PEOPLE_F sits at the top of the person hierarchy, holding biographical and identity attributes that are independent of any specific assignment or job.

Below it, tables like PER_ALL_ASSIGNMENTS_M and PER_PERIODS_OF_SERVICE carry the employment context, such as job, position, business unit, and employment dates. This separation is intentional: a person record can exist in oracle cloud hcm even before an assignment is created, for example during pre-hire processing.

For report writers building anything from headcount to diversity analytics, the relationship between PER_ALL_PEOPLE_F and the assignment tables is the backbone of every meaningful workforce query. You can explore the broader set of interconnected Oracle HCM tables to understand how this table fits across modules.

The key position of PER_ALL_PEOPLE_F in the oracle hr tables hierarchy makes it the universal join point. Nearly every person-related report starts here, then branches outward to assignment, names, addresses, and organization data.

How to Query Worker Data Using PER_ALL_PEOPLE_F: Essential Column Reference

Before you write your first query, you need to know which columns carry which data. The HR PER_ALL_PEOPLE_F column reference on ETRM provides a detailed listing, but here are the columns every developer queries against most frequently.

Column Name	Data Type	Description
PERSON_ID	NUMBER	Surrogate primary key. Unique identifier for the person across all HCM tables.
EFFECTIVE_START_DATE	DATE	Start of the row's effective period. Critical for date-filtering queries.
EFFECTIVE_END_DATE	DATE	End of the row's effective period. Current rows carry 31-DEC-4712.
PERSON_TYPE_ID	NUMBER	Links to PER_PERSON_TYPES for employee, contingent worker, etc.
DATE_OF_BIRTH	DATE	Worker date of birth. Used for age-band analytics.
NATIONAL_IDENTIFIER	VARCHAR2	National ID or social security number depending on legislative context.
GENDER	VARCHAR2	Worker gender code. Commonly used in diversity reports.
CREATED_BY	VARCHAR2	Audit column tracking which user or process created the row.
LAST_UPDATE_DATE	DATE	Timestamp of the most recent update. Useful for delta extracts.

For a full breakdown of every column, the complete PER_ALL_PEOPLE_F reference on DataFusing covers all attributes with additional usage context for report developers.

The Most Critical Rule When Querying PER_ALL_PEOPLE_F: Effective Date Filtering

This is the rule that separates clean reports from broken ones. Because PER_ALL_PEOPLE_F is date-tracked, every historical change to a person record (a name correction, a date of birth update, a person type change) writes a new row. Without a date filter, your query returns all versions of every worker.

The standard pattern to retrieve currently active person records is:

SELECT
    p.PERSON_ID,
    p.DATE_OF_BIRTH,
    p.GENDER,
    p.NATIONAL_IDENTIFIER,
    p.EFFECTIVE_START_DATE,
    p.EFFECTIVE_END_DATE
FROM
    PER_ALL_PEOPLE_F p
WHERE
    SYSDATE BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE;

This pattern is non-negotiable. Every query you build against PER_ALL_PEOPLE_F in oracle cloud hcm must carry this WHERE clause unless you are explicitly reporting on historical data, in which case you substitute SYSDATE with your target date.

For point-in-time historical reporting, the pattern shifts to:

WHERE :as_of_date BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE

This binds a parameter, letting report consumers pass any historical date and see the state of worker data as it existed at that moment. This is one of the most powerful patterns in oracle hcm tables reporting.

Basic SQL Query Examples to Get Started with PER_ALL_PEOPLE_F Worker Data

Once your effective date filter is in place, you can start building useful queries. Below are the patterns that cover the majority of what HCM developers and report writers need when first working against PER_ALL_PEOPLE_F.

Query 1: Active Worker Count by Gender

SELECT
    p.GENDER,
    COUNT(p.PERSON_ID) AS worker_count
FROM
    PER_ALL_PEOPLE_F p
WHERE
    SYSDATE BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE
GROUP BY
    p.GENDER
ORDER BY
    worker_count DESC;

Query 2: Workers with National Identifier (Active Records Only)

SELECT
    p.PERSON_ID,
    p.NATIONAL_IDENTIFIER,
    p.DATE_OF_BIRTH,
    p.GENDER,
    p.EFFECTIVE_START_DATE
FROM
    PER_ALL_PEOPLE_F p
WHERE
    SYSDATE BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE
    AND p.NATIONAL_IDENTIFIER IS NOT NULL
ORDER BY
    p.EFFECTIVE_START_DATE DESC;

Query 3: Recently Created Person Records (2026 Hires)

SELECT
    p.PERSON_ID,
    p.CREATED_BY,
    p.CREATION_DATE,
    p.GENDER
FROM
    PER_ALL_PEOPLE_F p
WHERE
    SYSDATE BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE
    AND EXTRACT(YEAR FROM p.CREATION_DATE) = 2026
ORDER BY
    p.CREATION_DATE DESC;

These three patterns are the building blocks. Every more complex query you write to get worker data from PER_ALL_PEOPLE_F extends from them.

How to Query Worker Data Using PER_ALL_PEOPLE_F with Joins to Other Oracle HR Tables

Person-level data alone is rarely enough. Most reports require joining PER_ALL_PEOPLE_F to assignment, name, and organization tables to produce readable, business-relevant output. Here is the standard multi-table join pattern used across oracle hr tables reporting in 2026.

Joining PER_ALL_PEOPLE_F to PER_PERSON_NAMES_F (Worker Full Name)

SELECT
    p.PERSON_ID,
    pn.FULL_NAME,
    pn.FIRST_NAME,
    pn.LAST_NAME,
    p.GENDER,
    p.DATE_OF_BIRTH
FROM
    PER_ALL_PEOPLE_F p
    INNER JOIN PER_PERSON_NAMES_F pn
        ON p.PERSON_ID = pn.PERSON_ID
        AND pn.NAME_TYPE = 'GLOBAL'
        AND SYSDATE BETWEEN pn.EFFECTIVE_START_DATE AND pn.EFFECTIVE_END_DATE
WHERE
    SYSDATE BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE;

Joining PER_ALL_PEOPLE_F to PER_ALL_ASSIGNMENTS_M (Employment Data)

SELECT
    p.PERSON_ID,
    pn.FULL_NAME,
    a.ASSIGNMENT_NUMBER,
    a.ASSIGNMENT_STATUS_TYPE_ID,
    a.BUSINESS_UNIT_ID,
    a.JOB_ID
FROM
    PER_ALL_PEOPLE_F p
    INNER JOIN PER_PERSON_NAMES_F pn
        ON p.PERSON_ID = pn.PERSON_ID
        AND pn.NAME_TYPE = 'GLOBAL'
        AND SYSDATE BETWEEN pn.EFFECTIVE_START_DATE AND pn.EFFECTIVE_END_DATE
    INNER JOIN PER_ALL_ASSIGNMENTS_M a
        ON p.PERSON_ID = a.PERSON_ID
        AND SYSDATE BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
        AND a.PRIMARY_FLAG = 'Y'
        AND a.ASSIGNMENT_TYPE = 'E'
WHERE
    SYSDATE BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE;

Notice that every joined date-tracked table carries its own effective date filter. This is the rule across the entire oracle fusion hcm schema: each "F" or "M" suffixed table requires its own SYSDATE BETWEEN clause. Forgetting one is enough to multiply your result set.

For a broader overview of which tables connect to PER_ALL_PEOPLE_F across modules, the guide on important tables in Oracle Fusion HCM provides a solid map of the core schema relationships.

Common Mistakes Developers Make When Querying Oracle HCM Tables

Across the oracle hcm tables landscape, the same mistakes appear repeatedly. Understanding them before they appear in your queries saves hours of debugging downstream.

• No effective date filter: The single most common error. Always include SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE on every date-tracked table in the join chain.
• Missing PRIMARY_FLAG filter on assignments: Workers can have multiple assignments. Without PRIMARY_FLAG = 'Y', you get one row per assignment, not one row per worker.
• Wrong ASSIGNMENT_TYPE value: Use 'E' for employees and 'C' for contingent workers in PER_ALL_ASSIGNMENTS_M. Mixing them gives you both populations unintentionally.
• Joining on PERSON_ID alone across non-date-tracked tables: Some lookup tables are not date-tracked. Verify before assuming a BETWEEN filter is required.
• Using SELECT * in production queries: PER_ALL_PEOPLE_F carries sensitive columns including national identifiers. Always select only the columns your report needs.
• Not aliasing tables in multi-table joins: Once you have three or more joins in oracle fusion hcm queries, ambiguous column references cause ORA errors quickly.
• Ignoring LEGISLTIVE_DATA_GROUP_ID: In multi-country implementations, this column scopes data to a specific legislative context. Omitting it can return data across countries unintentionally.

How to Query Worker Data Using PER_ALL_PEOPLE_F for OTBI and BI Publisher Reports

Not all access to PER_ALL_PEOPLE_F is through direct SQL. In oracle cloud hcm, report writers frequently work through OTBI subject areas or BI Publisher data models, both of which ultimately read from the same underlying table structure.

In OTBI, the "Workforce Management - Worker Assignment Real Time" subject area abstracts PER_ALL_PEOPLE_F through its presentation layer. The effective date handling is done automatically, which removes the risk of the duplicate-row mistake. However, OTBI limits what you can express in complex join logic.

BI Publisher gives you full SQL control. When writing data models in BI Publisher against oracle hr tables, you write raw SQL directly. This means the effective date filter responsibility falls back to you as the developer.

HCM Extracts are the third path. They are Oracle-managed extract definitions that reference date-tracked tables correctly by design. For high-volume data extractions from PER_ALL_PEOPLE_F in 2026 implementations, HCM Extracts remain the recommended approach for payroll and integration interfaces.

Performance Optimization Tips for PER_ALL_PEOPLE_F Queries in Oracle Cloud HCM

Query performance against PER_ALL_PEOPLE_F in large-scale oracle cloud hcm implementations follows predictable patterns. Apply these optimizations before your queries go into production report definitions.

1. Always filter on indexed columns first. PERSON_ID and EFFECTIVE_START_DATE are indexed. Lead your WHERE clause with these conditions to leverage index scans rather than full table scans.
2. Avoid functions on indexed columns in WHERE clauses. TRUNC(EFFECTIVE_START_DATE) in a filter condition disables the index. Use date literals or bind variables instead.
3. Use bind variables in BI Publisher data models. Parameterized queries allow Oracle's optimizer to cache execution plans and reuse them across report runs.
4. Limit your column selection. Wide SELECT lists on large fact tables increase I/O. Only pull the columns your report renders.
5. Partition pruning awareness. In Oracle Fusion Cloud's managed database environment, PER_ALL_PEOPLE_F may benefit from partition pruning on effective date ranges. Date-bounded queries allow the optimizer to skip historical partitions entirely.
6. Test with EXPLAIN PLAN before deployment. A full table scan on PER_ALL_PEOPLE_F in an enterprise with 100,000+ workers is a report that fails in production. Validate execution plans in development first.

How to Query Worker Data Using PER_ALL_PEOPLE_F for Advanced Use Cases

Beyond basic headcount and demographic queries, the PER_ALL_PEOPLE_F table serves as the anchor for more complex analytical requirements. Here are the advanced patterns that senior HCM developers build against this table in the hcm data model.

Historical Headcount at a Point in Time

SELECT
    COUNT(DISTINCT p.PERSON_ID) AS headcount_at_period_end
FROM
    PER_ALL_PEOPLE_F p
    INNER JOIN PER_ALL_ASSIGNMENTS_M a
        ON p.PERSON_ID = a.PERSON_ID
        AND DATE '2025-12-31' BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
        AND a.PRIMARY_FLAG = 'Y'
        AND a.ASSIGNMENT_TYPE = 'E'
WHERE
    DATE '2025-12-31' BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE;

Worker Age Band Distribution for Workforce Planning

SELECT
    CASE
        WHEN FLOOR(MONTHS_BETWEEN(SYSDATE, p.DATE_OF_BIRTH) / 12) BETWEEN 18 AND 29 THEN '18-29'
        WHEN FLOOR(MONTHS_BETWEEN(SYSDATE, p.DATE_OF_BIRTH) / 12) BETWEEN 30 AND 39 THEN '30-39'
        WHEN FLOOR(MONTHS_BETWEEN(SYSDATE, p.DATE_OF_BIRTH) / 12) BETWEEN 40 AND 49 THEN '40-49'
        WHEN FLOOR(MONTHS_BETWEEN(SYSDATE, p.DATE_OF_BIRTH) / 12) BETWEEN 50 AND 59 THEN '50-59'
        ELSE '60+'
    END AS age_band,
    COUNT(p.PERSON_ID) AS worker_count
FROM
    PER_ALL_PEOPLE_F p
WHERE
    SYSDATE BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE
    AND p.DATE_OF_BIRTH IS NOT NULL
GROUP BY
    CASE
        WHEN FLOOR(MONTHS_BETWEEN(SYSDATE, p.DATE_OF_BIRTH) / 12) BETWEEN 18 AND 29 THEN '18-29'
        WHEN FLOOR(MONTHS_BETWEEN(SYSDATE, p.DATE_OF_BIRTH) / 12) BETWEEN 30 AND 39 THEN '30-39'
        WHEN FLOOR(MONTHS_BETWEEN(SYSDATE, p.DATE_OF_BIRTH) / 12) BETWEEN 40 AND 49 THEN '40-49'
        WHEN FLOOR(MONTHS_BETWEEN(SYSDATE, p.DATE_OF_BIRTH) / 12) BETWEEN 50 AND 59 THEN '50-59'
        ELSE '60+'
    END
ORDER BY age_band;

These patterns represent real workforce planning use cases that organizations running oracle fusion hcm in 2026 build into their regular reporting cycles.

Security and Data Governance Considerations for PER_ALL_PEOPLE_F Queries

In oracle cloud hcm, direct SQL access to PER_ALL_PEOPLE_F bypasses the row-level security enforced by Oracle's LDAP-based data security policies. This is a critical consideration for any developer or analyst working outside of OTBI subject areas.

In BI Publisher reports pointing directly to the table, your SQL runs with the database credentials of the data source connection, not the viewing user's HCM security profile. This means a report running under a service account with broad access will return records the end user would not normally see through the UI.

Best practices for data governance in direct PER_ALL_PEOPLE_F queries include:

• Use HCM BI data roles to scope report access rather than granting direct table access.
• Avoid exposing NATIONAL_IDENTIFIER in reports unless explicitly required and masked.
• Apply Business Unit or Legal Employer filters as parameters in all BI Publisher data models to scope results to the viewer's organizational access.
• Log all direct database access to audit tables where policy requires it in your organization's oracle hcm tables governance framework.
• Review Oracle's VPD (Virtual Private Database) policies if your implementation uses them for automatic row-level filtering on person data tables.

Where to Find the Full PER_ALL_PEOPLE_F Column Reference in 2026

Oracle publishes the authoritative column-level documentation for PER_ALL_PEOPLE_F as part of the Oracle HCM Cloud data model reference. The 24C release documentation remains the most detailed publicly available reference as of 2026 and covers every column with data type, length, and descriptive context.

The official Oracle documentation for PER_ALL_PEOPLE_F is the first reference every developer should bookmark. It is the source of truth for column names, data types, and column descriptions.

For community-contributed context, practical usage examples, and how this table connects to real reporting scenarios, the additional references we link throughout this guide fill in the gaps that formal documentation leaves open.

The oracle hcm tables schema is extensive. PER_ALL_PEOPLE_F is the anchor, but understanding its relationships to person names, assignments, employment, and organization data is what makes oracle cloud hcm reporting genuinely powerful.

Conclusion

Knowing how to query worker data using the PER_ALL_PEOPLE_F table correctly is the skill that underpins all person-level reporting in Oracle Fusion HCM. The table is not complicated in structure, but the date-tracking mechanism demands discipline: every query must carry an effective date filter, and every joined table must carry its own.

The SQL patterns in this guide cover the full range from basic demographic pulls to point-in-time historical headcount and workforce age band analysis. Whether you are writing BI Publisher data models, building OTBI dashboards, or constructing HCM Extracts, PER_ALL_PEOPLE_F is where the data starts.

Master the effective date rule, understand the joins to PER_PERSON_NAMES_F and PER_ALL_ASSIGNMENTS_M, and keep security considerations front of mind. That is the complete foundation for querying worker data using PER_ALL_PEOPLE_F across any oracle cloud hcm implementation in 2026.

---

Frequently Asked Questions

What is PER_ALL_PEOPLE_F in Oracle Fusion HCM and what data does it store?

PER_ALL_PEOPLE_F is the core person table in the Oracle Fusion HCM data model. It stores biographical and identification data for every person in the system including date of birth, gender, national identifier, and person type. It is date-tracked, meaning every historical change creates a new row bounded by EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.

Why does my query against PER_ALL_PEOPLE_F return duplicate rows for the same person?

Duplicate rows occur when you query PER_ALL_PEOPLE_F without an effective date filter. Because the table is date-tracked in Oracle Fusion HCM, each historical change to a person record writes a new row. Add WHERE SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE to return only the current version of each record.

How do I get worker names from PER_ALL_PEOPLE_F in Oracle Cloud HCM?

PER_ALL_PEOPLE_F does not store the worker's display name directly. You need to join it to PER_PERSON_NAMES_F on PERSON_ID, filtering for NAME_TYPE = 'GLOBAL' and applying the effective date filter to the names table as well. Both tables require their own SYSDATE BETWEEN filter.

What is the difference between PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_M in Oracle HR tables?

PER_ALL_PEOPLE_F holds person-level biographical data that is independent of employment, such as date of birth, gender, and national identifier. PER_ALL_ASSIGNMENTS_M holds employment data including job, position, business unit, and assignment status. Every worker report that needs both person and employment context requires joining these two tables on PERSON_ID.

Is it safe to query PER_ALL_PEOPLE_F directly in BI Publisher in 2026?

Direct SQL access to PER_ALL_PEOPLE_F in BI Publisher bypasses Oracle HCM's row-level security, meaning the query returns data based on the data source credentials rather than the user's HCM security profile. Apply explicit Business Unit or Legal Employer parameters to scope results, and avoid exposing sensitive columns like NATIONAL_IDENTIFIER without masking.

How do I query PER_ALL_PEOPLE_F for historical headcount at a specific past date?

Replace SYSDATE with your target date in the effective date filter: WHERE DATE '2025-06-30' BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE. Apply the same substitution to every date-tracked table in your join chain, including PER_ALL_ASSIGNMENTS_M and PER_PERSON_NAMES_F, to get the state of all data as it existed at that historical point.

Where can I find the full column list for PER_ALL_PEOPLE_F in Oracle Cloud HCM?

The authoritative column reference for PER_ALL_PEOPLE_F is published in Oracle's official HCM Cloud data model documentation for the 24C release. Community resources like the DataFusing PER_ALL_PEOPLE_F guide and the ETRM column reference provide additional usage context that helps developers understand how each column is used in real reporting scenarios across the oracle hcm tables ecosystem.