PER_ALL_ASSIGNMENTS_M Duplicate Rows — The Definitive Fix

Why PER_ALL_ASSIGNMENTS_M Returns Duplicate Rows

PER_ALL_ASSIGNMENTS_M is a date-effective table — the _M suffix stands for "merged," which is Oracle's term for the view that combines all effective-dated rows into a single queryable surface. Every time an employee's assignment changes — a salary increase, a department transfer, a location change — Oracle writes a new row with new EFFECTIVE_START_DATE and EFFECTIVE_END_DATE values.

An employee hired in 2021 with three subsequent job changes has four rows in PER_ALL_ASSIGNMENTS_M: one for the original hire and one for each change. A query with no effective date filter returns all four.

This is not a bug. It's the architecture. Oracle stores history so you can query headcount as of any past date. The problem is that most queries only want current state, and forgetting to filter for it is the #1 cause of inflated headcount numbers in Oracle HCM reporting.

The Fix: EFFECTIVE_LATEST_CHANGE = 'Y'

EFFECTIVE_LATEST_CHANGE is an Oracle-managed flag column in all _M suffix tables. Oracle sets it to 'Y' on the most recently processed effective-dated row for each assignment. For current-state queries, filtering on this column is the simplest and most reliable approach.

SELECT
  a.person_id,
  a.assignment_number,
  a.job_id,
  a.department_id
FROM per_all_assignments_m a
WHERE a.assignment_type = 'E';

SELECT
  a.person_id,
  a.assignment_number,
  a.job_id,
  a.department_id
FROM per_all_assignments_m a
WHERE a.assignment_type = 'E'
  AND a.effective_latest_change = 'Y';

That's the whole fix for most queries. One predicate. One column. Done.

4 Root Causes of Duplicate Rows

Understanding why duplicates appear helps you write queries that stay correct as data changes. There are four main causes:

SYSDATE Filter vs EFFECTIVE_LATEST_CHANGE — Which to Use?

This is the most common debate in Oracle HCM SQL communities. Here's the full comparison:

Production SQL Patterns

Pattern 1: Current active headcount

-- Current active employees: one row per person, primary assignment only
SELECT
  p.person_number,
  p.full_name,
  a.assignment_number,
  a.job_id,
  a.department_id,
  a.location_id,
  a.assignment_status_type,
  a.effective_start_date
FROM   per_all_assignments_m a
JOIN   per_all_people_f p
  ON   p.person_id = a.person_id
  AND  p.effective_start_date <= SYSDATE
  AND  p.effective_end_date   >= SYSDATE
WHERE  a.effective_latest_change   = 'Y'
  AND  a.assignment_type          = 'E'
  AND  a.primary_flag             = 'Y'
  AND  a.assignment_status_type   = 'ACTIVE_ASSIGN'
ORDER BY p.full_name;

Pattern 2: Headcount by department

SELECT
  d.department_name,
  COUNT(DISTINCT a.person_id)  AS headcount
FROM   per_all_assignments_m a
JOIN   hr_all_organization_units_vl d
  ON   d.organization_id = a.department_id
WHERE  a.effective_latest_change   = 'Y'
  AND  a.assignment_type          = 'E'
  AND  a.primary_flag             = 'Y'
  AND  a.assignment_status_type   = 'ACTIVE_ASSIGN'
GROUP BY d.department_name
ORDER BY headcount DESC;

Pattern 3: As-of-date headcount (historical)

-- Replace :as_of_date with your target date (e.g., DATE '2024-12-31')
SELECT
  p.person_number,
  p.full_name,
  a.job_id,
  a.department_id,
  a.effective_start_date,
  a.effective_end_date
FROM   per_all_assignments_m a
JOIN   per_all_people_f p
  ON   p.person_id = a.person_id
  AND  p.effective_start_date <= :as_of_date
  AND  p.effective_end_date   >= :as_of_date
WHERE  a.effective_start_date  <= :as_of_date
  AND  a.effective_end_date    >= :as_of_date
  AND  a.assignment_type       = 'E'
  AND  a.primary_flag          = 'Y'
  AND  a.assignment_status_type = 'ACTIVE_ASSIGN'
ORDER BY p.full_name;

Pattern 4: Employee assignment history (intentionally all rows)

-- No EFFECTIVE_LATEST_CHANGE filter — we want all historical rows
SELECT
  a.assignment_number,
  a.effective_start_date,
  a.effective_end_date,
  a.job_id,
  a.department_id,
  a.location_id,
  a.assignment_status_type,
  a.effective_sequence
FROM   per_all_assignments_m a
WHERE  a.person_id    = :person_id
  AND  a.assignment_type = 'E'
  AND  a.primary_flag   = 'Y'
ORDER BY a.effective_start_date ASC, a.effective_sequence ASC;

Fixing Duplicate Rows in OTBI Reports

OTBI headcount reports that return 2x or 3x the expected row count are almost always caused by one of two things:

Cause A: Comma join between subject areas

When you drag columns from two subject areas onto the same OTBI report without a shared dimension, Oracle BI generates a comma join — a cross join that multiplies rows. If one subject area joins to PER_ALL_ASSIGNMENTS_M without an effective date constraint, you get one OTBI row per historical assignment change per matched row.

The fix: use a single subject area per report where possible. If you must join two, ensure they share the same "Employee" or "Assignment" dimension object — not just similar-looking columns.

Cause B: Custom SQL override missing effective date filter

Custom SQL overrides in OTBI bypass the subject area's built-in date-effective join logic. If your custom SQL selects from PER_ALL_ASSIGNMENTS_M without EFFECTIVE_LATEST_CHANGE = 'Y', you'll get multiple rows per assignment.

-- Wrong: no effective date filter
SELECT
  "Workforce Management - Worker Assignment Real Time"."Assignment Details"."Assignment Number",
  "Workforce Management - Worker Assignment Real Time"."Worker"."Full Name"
FROM "Workforce Management - Worker Assignment Real Time"

-- Use a filter in the OTBI Criteria tab instead:
-- "Assignment Details"."Effective Latest Change" is equal to / is in Y

In OTBI's standard subject areas, look for a filter column called "Effective Latest Change" under the Assignment Details folder. Adding a filter Effective Latest Change = Y in the Criteria tab is equivalent to WHERE effective_latest_change = 'Y' in SQL.

When to Use EFFECTIVE_SEQUENCE

EFFECTIVE_SEQUENCE is a secondary sequencing column that handles same-day multiple changes. When two assignment changes share the same EFFECTIVE_START_DATE, Oracle differentiates them with EFFECTIVE_SEQUENCE — the highest sequence number is the most recent version of that day's changes.

You need EFFECTIVE_SEQUENCE in two scenarios:

1. Ordering history queries: When displaying all changes sorted chronologically, order by EFFECTIVE_START_DATE ASC, EFFECTIVE_SEQUENCE ASC.
2. Deduplicating same-day changes manually: If you cannot use EFFECTIVE_LATEST_CHANGE (rare edge case), you can use a window function to pick the highest sequence per date:

SELECT *
FROM (
  SELECT
    a.*,
    ROW_NUMBER() OVER (
      PARTITION BY a.person_id, a.assignment_id
      ORDER BY a.effective_start_date DESC, a.effective_sequence DESC
    ) AS rn
  FROM   per_all_assignments_m a
  WHERE  a.assignment_type = 'E'
    AND  a.primary_flag   = 'Y'
)
WHERE rn = 1;

Common Mistakes That Reintroduce Duplicates

Even after adding EFFECTIVE_LATEST_CHANGE = 'Y', some queries still return multiples. Here's why:

Mistake 1: Forgetting PRIMARY_FLAG

An employee with a primary and secondary assignment has two assignment records, both with EFFECTIVE_LATEST_CHANGE = 'Y' on their respective current rows. The result is two rows per person. Fix: add AND primary_flag = 'Y' when you want exactly one row per employee.

Mistake 2: Filtering on ASSIGNMENT_STATUS_TYPE without understanding all valid values

Some queries filter on ASSIGNMENT_STATUS_TYPE = 'ACTIVE_ASSIGN' to get active employees. But an employee on leave has a different status code (e.g., 'SUSP_ASSIGN') and won't appear. If your headcount should include employees on leave, extend your filter: AND assignment_status_type IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN').

Mistake 3: Joining PER_ALL_PEOPLE_F without effective date

Joining PER_ALL_ASSIGNMENTS_M (filtered to current row) to PER_ALL_PEOPLE_F without an effective date filter on PER_ALL_PEOPLE_F reintroduces duplicates — now from the person table. Always add:

JOIN per_all_people_f p
  ON  p.person_id = a.person_id
  AND p.effective_start_date <= SYSDATE
  AND p.effective_end_date   >= SYSDATE

Mistake 4: Using DISTINCT as a band-aid

SELECT DISTINCT masks duplicate rows without fixing the underlying join problem. If your query needs DISTINCT to return the right count, you have an implicit Cartesian join somewhere. Fix the join, don't hide the symptom.

Frequently Asked Questions

Does EFFECTIVE_LATEST_CHANGE work on all Oracle HCM versions?

Yes. EFFECTIVE_LATEST_CHANGE is present in all Oracle Fusion Cloud HCM versions and was introduced with the _M table architecture. If you're on Oracle HCM Cloud (Fusion), you have it. The column is not available on Oracle E-Business Suite (EBS) — that's a completely different architecture.

Can I use EFFECTIVE_LATEST_CHANGE on other _M tables?

Yes — it's available on all Oracle HCM _M suffix tables: PER_ALL_ASSIGNMENTS_M, PER_ALL_PEOPLE_M, PAY_ALL_PAYROLLS_M, and others. The column functions identically across all of them.

My query still returns duplicates after adding EFFECTIVE_LATEST_CHANGE = 'Y'. Why?

Check for: (1) missing PRIMARY_FLAG = 'Y' — the employee has multiple assignments; (2) a join to another date-effective table without an effective date filter; (3) a comma join in OTBI — two subject areas that don't share a properly constrained dimension.

Is PER_ALL_ASSIGNMENTS_M replacing PER_ALL_ASSIGNMENTS_F?

PER_ALL_ASSIGNMENTS_F is not officially deprecated, but Oracle's recommended approach since 23D/24B is to use PER_ALL_ASSIGNMENTS_M for all new development. New Oracle-delivered OTBI subject areas are built on _M tables. See the 24B migration guide for full detail.

What's the performance impact of EFFECTIVE_LATEST_CHANGE = 'Y'?

Oracle indexes EFFECTIVE_LATEST_CHANGE on all _M tables. A filter on this column is typically faster than a range predicate on EFFECTIVE_START_DATE/EFFECTIVE_END_DATE because it's a single-value equality check. On large instances (500K+ assignments), this difference is material.