Beginner Guide

New to Oracle HCM?
Here's Your First Headcount Query

The two tables you need, the one join that trips everyone up, and five mistakes that make your headcount wrong — step by step from zero.

Jun 13, 2026·16 min read·Search 35K+ Tables →

Table of Contents

  1. Why Oracle HCM SQL Is Different
  2. The Two Tables You Need
  3. Understanding Date-Effective Joins
  4. Step 1: The Minimal Headcount Query
  5. Step 2: Add the Required Filters
  6. Step 3: Break Out by Department
  7. The Complete Production Query
  8. 5 Mistakes Every New Analyst Makes
  9. What to Learn Next

1. Why Oracle HCM SQL Is Different

If you're coming from a regular transactional database — or from Workday, SAP, or any other HR system — Oracle Fusion HCM's data model will surprise you. Most HR systems store the current state of an employee record and overwrite it when something changes. Oracle HCM does not.

Instead, Oracle HCM uses a pattern called date-effective history: every change to a person, assignment, or position creates a new row with a start date and end date, while the old row is kept unchanged. This means:

⚠️

The most common mistake new HCM analysts make: they run a headcount query, get a number that's 3x too high, and assume something is wrong with the data. The data is correct — the query is missing the date-effective filter.

2. The Two Tables You Need

For a basic headcount query you need exactly two tables. Everything else is optional enrichment.

TableWhat It HoldsKey Columns
PER_ALL_PEOPLE_F One row per person per date range. Holds name, person number, date of birth. The _F suffix means date-effective. PERSON_ID, PERSON_NUMBER, DISPLAY_NAME, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE
PER_ALL_ASSIGNMENTS_M One row per assignment per date range. Holds job, department, status, payroll. The _M suffix is the modern multi-assignment view (post-24B). PERSON_ID, ASSIGNMENT_TYPE, ASSIGNMENT_STATUS_TYPE, EFFECTIVE_LATEST_CHANGE, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE
ℹ️

Why not just use PER_ALL_ASSIGNMENTS_M alone? You can for a headcount count, since it contains PERSON_ID. But you'll almost always want the person's name and number, which live in PER_ALL_PEOPLE_F. Get in the habit of joining both from the start.

The Suffix Cheat Sheet

SuffixMeaningExample
_FDate-effective — has EFFECTIVE_START_DATE and EFFECTIVE_END_DATEPER_ALL_PEOPLE_F
_MModern date-effective — replaces old _F assignment tables post-24B, adds EFFECTIVE_LATEST_CHANGEPER_ALL_ASSIGNMENTS_M
_VLView with language translation — use instead of _TL for multi-language orgsHR_ALL_ORGANIZATION_UNITS_VL
_VView — often a simplified read-only version of the underlying _F tablePER_EMPLOYEES_X

3. Understanding Date-Effective Joins

The core pattern for joining two date-effective tables is BETWEEN effective_start_date AND effective_end_date on both sides of the join, using the same "as of" date. Think of it as asking: "give me the row that was current on this date."

Date-Effective Join Pattern (the template you'll use everywhere)
-- The universal HCM join pattern
-- :as_of_date is the date you care about (e.g. DATE '2026-03-31')
FROM
    table_a  a
    JOIN table_b b
         ON  b.join_key          = a.join_key
         AND :as_of_date         BETWEEN b.effective_start_date
                                         AND b.effective_end_date
WHERE
    :as_of_date BETWEEN a.effective_start_date
                        AND a.effective_end_date

A few things to notice:

4. Step 1: The Minimal Headcount Query

Here is the smallest possible correct headcount query. It will give you a number close to your real headcount — but it'll be slightly too high until we add the required filters in Step 2.

Step 1 — Minimal Query (intentionally incomplete)
-- Minimal query — will over-count without the filters in Step 2
SELECT
    COUNT(DISTINCT paam.person_id)   AS headcount
FROM
    per_all_assignments_m  paam
WHERE
    :as_of_date BETWEEN paam.effective_start_date AND paam.effective_end_date;
⚠️

This will still over-count because PER_ALL_ASSIGNMENTS_M contains rows for employees, contractors, applicants, and contingent workers — all mixed together. Step 2 adds the filters to isolate employees only.

5. Step 2: Add the Required Filters

Three filters are non-negotiable for a correct headcount. Missing any one of them inflates the result.

1

ASSIGNMENT_TYPE = 'E'

PER_ALL_ASSIGNMENTS_M stores employees (E), contingent workers (C), applicants (A), and non-workers (N) in the same table. Without this filter you count all of them.

2

ASSIGNMENT_STATUS_TYPE = 'ACTIVE_ASSIGN'

An employee who has been terminated still has rows in this table — their last row just has an end date in the past. The status filter ensures you only count currently active assignments.

3

EFFECTIVE_LATEST_CHANGE = 'Y'

This is the _M-specific column. When multiple rows exist for the same assignment and overlapping date range (due to change history), this flag marks only the most recent one. Without it you get duplicates for employees who had mid-period changes.

Step 2 — Add the Three Required Filters
-- Headcount with all required filters — now accurate
SELECT
    COUNT(DISTINCT paam.person_id)   AS headcount
FROM
    per_all_assignments_m  paam
WHERE
    :as_of_date BETWEEN paam.effective_start_date AND paam.effective_end_date
    AND paam.assignment_type         = 'E'           -- employees only
    AND paam.assignment_status_type  = 'ACTIVE_ASSIGN'
    AND paam.effective_latest_change = 'Y';          -- deduplicate mid-period changes

This should now match your HR system's headcount report within a small margin. If it's still off, check: are you including primary assignments only? Some orgs allow employees to have secondary assignments. Add paam.primary_flag = 'Y' to count heads, not assignment rows.

6. Step 3: Break Out by Department

A total headcount number is rarely useful on its own. Let's add a department breakdown, which requires joining to the organization table.

Step 3 — Headcount by Department
-- Headcount broken out by department
SELECT
    dept.name                                    AS department,
    COUNT(DISTINCT paam.person_id)              AS headcount
FROM
    per_all_assignments_m           paam
    JOIN hr_all_organization_units_f dept
         ON  dept.organization_id    = paam.organization_id
         AND :as_of_date             BETWEEN dept.effective_start_date
                                             AND dept.effective_end_date
WHERE
    :as_of_date BETWEEN paam.effective_start_date AND paam.effective_end_date
    AND paam.assignment_type         = 'E'
    AND paam.assignment_status_type  = 'ACTIVE_ASSIGN'
    AND paam.effective_latest_change = 'Y'
    AND paam.primary_flag            = 'Y'
GROUP BY
    dept.name
ORDER BY
    dept.name;
ℹ️

HR_ALL_ORGANIZATION_UNITS_F is also date-effective — notice the date join appears in the JOIN condition, not just the WHERE clause. This is the pattern you'll repeat for every lookup table you add.

7. The Complete Production Query

Here is the full headcount query with person name, department, legal employer, job, and manager — ready to copy into Oracle SQL Developer, HCM Data Loader preview, or OTBI Logical SQL.

Complete Headcount Query — Production Ready
-- Production headcount query: active employees as of any date
-- Replace DATE '2026-06-30' with your target date
SELECT
    papf.person_number,
    papf.display_name,
    paam.assignment_number,
    job.name                                        AS job_name,
    dept.name                                       AS department,
    le.name                                         AS legal_employer,
    mgr.display_name                                AS manager_name,
    paam.effective_start_date                       AS assignment_start
FROM
    per_all_assignments_m           paam
    JOIN per_all_people_f           papf
         ON  papf.person_id          = paam.person_id
         AND :as_of_date             BETWEEN papf.effective_start_date
                                             AND papf.effective_end_date
    JOIN hr_all_organization_units_f dept
         ON  dept.organization_id    = paam.organization_id
         AND :as_of_date             BETWEEN dept.effective_start_date
                                             AND dept.effective_end_date
    JOIN hr_all_organization_units_f le
         ON  le.organization_id      = paam.legal_entity_id
         AND :as_of_date             BETWEEN le.effective_start_date
                                             AND le.effective_end_date
    LEFT JOIN per_jobs_f            job
         ON  job.job_id              = paam.job_id
         AND :as_of_date             BETWEEN job.effective_start_date
                                             AND job.effective_end_date
    LEFT JOIN per_all_people_f      mgr
         ON  mgr.person_id           = paam.manager_id
         AND :as_of_date             BETWEEN mgr.effective_start_date
                                             AND mgr.effective_end_date
WHERE
    :as_of_date BETWEEN paam.effective_start_date AND paam.effective_end_date
    AND paam.assignment_type         = 'E'
    AND paam.assignment_status_type  = 'ACTIVE_ASSIGN'
    AND paam.effective_latest_change = 'Y'
    AND paam.primary_flag            = 'Y'
ORDER BY
    le.name,
    dept.name,
    papf.display_name;

8. Five Mistakes Every New Analyst Makes

Mistake 1: No date filter

Running SELECT COUNT(*) FROM PER_ALL_ASSIGNMENTS_M without a BETWEEN filter returns the total number of assignment rows across all time — often 5–20x your real headcount. Always bind :as_of_date.

Mistake 2: Missing ASSIGNMENT_TYPE = 'E'

The assignments table has E, C, A, and N rows. If your org has contractors or open requisitions, you'll count them as employees. This filter is mandatory for headcount.

Mistake 3: Forgetting EFFECTIVE_LATEST_CHANGE on _M tables

The _M suffix tables use a different deduplication mechanism than the older _F tables. Without EFFECTIVE_LATEST_CHANGE = 'Y', employees with mid-period changes (a job change on the 15th of the month) appear as two rows.

Mistake 4: Using SYSDATE instead of a bound date variable

SYSDATE-based queries are not reproducible — the same query returns different results depending on when you run it. Use :as_of_date and log the value you used. Auditors will ask for it.

Mistake 5: Joining on PERSON_ID without the date filter on both tables

If you join PER_ALL_PEOPLE_F on just person_id = person_id without adding the date filter on PER_ALL_PEOPLE_F, you get a row for every historical version of the person's name — multiplying your result set by however many times they changed their legal name.

9. What to Learn Next

Once the headcount query clicks, the same pattern extends to every other HCM query you'll ever write. Here's a suggested order:

  1. Add hire and termination dates — join PER_PERIODS_OF_SERVICE for employment start dates and termination history.
  2. Add salary — join CMP_SALARY on person_id + effective date to pull current annual salary.
  3. Filter by legal employer — add a WHERE on le.name = 'Your Legal Employer Name' to scope to one entity.
  4. Learn OTBI Logical SQL — once you know the physical tables, OTBI's subject areas will make sense as a friendlier layer over the same data.
  5. Master PER_ALL_ASSIGNMENTS_M columns — it holds job, grade, position, location, payroll, and 80+ other columns. One table, most of what you need.

Explore All 14,950 Oracle HCM Tables

Search every table and column in the Oracle Fusion HCM schema — find the exact column you need, see sample values, and trace relationships across the data model.

Search HCM Tables →

Need a Headcount Report Built for Your Organization?

Our Oracle HCM consultant network can build custom headcount dashboards, fix OTBI subject area issues, and optimize your workforce reporting setup. Get a free scoping call.

Find an Oracle HCM Consultant →

Related Articles