Why does PER_ALL_ASSIGNMENTS_M return multiple rows per employee?

PER_ALL_ASSIGNMENTS_M stores the full effective-date history of every assignment. Each time an assignment is changed (salary review, grade change, location transfer, promotion), a new row is added with a new EFFECTIVE_START_DATE. One employee with 5 years of history could have 20+ rows in this table. You need EFFECTIVE_LATEST_CHANGE = 'Y' to get only the current record.

What is EFFECTIVE_LATEST_CHANGE in PER_ALL_ASSIGNMENTS_M?

EFFECTIVE_LATEST_CHANGE = 'Y' flags the most recent effective-dated row for each assignment. When an assignment has multiple date-effective rows, only the row with EFFECTIVE_LATEST_CHANGE = 'Y' represents the current state. This is Oracle's recommended filter for 'current assignment' queries.

What is ASSIGNMENT_TYPE in PER_ALL_ASSIGNMENTS_M and which value should I use?

ASSIGNMENT_TYPE identifies the type of work relationship: 'E' = Employee, 'C' = Contingent Worker, 'N' = Non-Worker, 'B' = Benefit Relationship, 'P' = Pending Worker. For headcount reports, filter to ASSIGNMENT_TYPE = 'E'. Including all types is why many queries over-count.

What is the difference between PER_ALL_ASSIGNMENTS_M and PER_ALL_ASSIGNMENTS_F?

PER_ALL_ASSIGNMENTS_M (the _M suffix = merged) combines both current and historical assignment data in a single table with EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns. PER_ALL_ASSIGNMENTS_F (the _F suffix = future or dated) is similar but has stricter effective-date enforcement. For most reporting, use PER_ALL_ASSIGNMENTS_M with EFFECTIVE_LATEST_CHANGE = 'Y'.

Oracle HCM / SQL

PER_ALL_ASSIGNMENTS_M: Why Your Query Returns 15 Rows Per Employee

March 28, 2026 · 1,500 words

Advance Your Oracle HCM Career

Structured courses trusted by HCM professionals worldwide.

Udemy Oracle Fusion HCM Cloud — Complete Training Browse Courses → Pluralsight Oracle HCM Cloud Learning Path Start Free Trial →

You query PER_ALL_ASSIGNMENTS_M expecting one row per employee. Instead you get 12, 15, or 20 rows for a single person. Your headcount is off by an order of magnitude and you have no idea why.

This is one of the most common Oracle Fusion HCM SQL mistakes. The fix is straightforward once you understand how this table works — but Oracle's documentation doesn't explain it clearly.

Why PER_ALL_ASSIGNMENTS_M Returns Multiple Rows

PER_ALL_ASSIGNMENTS_M stores the complete date-effective history of every assignment. Every time an assignment attribute changes — a pay grade update, a position transfer, a location change, a manager change — Oracle doesn't update the existing row. It end-dates the current row and creates a new row with the change.

For an employee who has been with the company 5 years and received annual pay reviews plus a promotion, you might see 10-15 rows in this table — all for the same person and the same assignment.

There are also multiple rows per person for a second reason: assignment types. Oracle stores different types of work relationships as separate assignment rows. An employee might have an employee assignment (type E), a benefit assignment (type B), and a pending assignment (type P) all in the same table.

The Two Filters That Eliminate Duplicates

Filter 1: EFFECTIVE_LATEST_CHANGE = 'Y'

This column flags the most recent effective-dated row for each assignment. It's Oracle's standard mechanism for answering "what is this assignment's current state?" Without this filter, you get every historical version.

-- Without filter: returns all historical rows
SELECT PERSON_ID, ASSIGNMENT_ID, GRADE_ID, EFFECTIVE_START_DATE
FROM PER_ALL_ASSIGNMENTS_M
WHERE PERSON_ID = 123456;
-- Returns: 14 rows (all date-effective changes)

-- With EFFECTIVE_LATEST_CHANGE filter: returns current row only
SELECT PERSON_ID, ASSIGNMENT_ID, GRADE_ID, EFFECTIVE_START_DATE
FROM PER_ALL_ASSIGNMENTS_M
WHERE PERSON_ID = 123456
  AND EFFECTIVE_LATEST_CHANGE = 'Y';
-- Returns: 1-2 rows (current state per assignment type)
      

Filter 2: ASSIGNMENT_TYPE = 'E'

Even with EFFECTIVE_LATEST_CHANGE = 'Y', you'll still get multiple rows if an employee has multiple assignment types. Filter to ASSIGNMENT_TYPE = 'E' to get only employee assignments.

ASSIGNMENT_TYPE	Meaning	Include in headcount?
E	Employee	Yes — primary assignment type
C	Contingent Worker	Yes — if counting contractors
N	Non-Worker	Usually no — third-party non-headcount
B	Benefit Relationship	No — benefits-only record, no work relationship
P	Pending Worker	No — pre-hire, not yet active
O	Offer	No — recruiting stage only

The Correct Headcount Query

✅ One row per active employee — correct query:

SELECT
  p.PERSON_NUMBER,
  p.FULL_NAME,
  a.ASSIGNMENT_NUMBER,
  a.ASSIGNMENT_STATUS_TYPE,
  a.GRADE_ID,
  a.JOB_ID,
  a.LOCATION_ID,
  a.DEPARTMENT_ID,
  a.MANAGER_ID,
  a.EFFECTIVE_START_DATE
FROM PER_ALL_PEOPLE_F p
JOIN PER_ALL_ASSIGNMENTS_M a
  ON p.PERSON_ID = a.PERSON_ID
WHERE TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE
  AND a.EFFECTIVE_LATEST_CHANGE = 'Y'
  AND a.ASSIGNMENT_TYPE = 'E'
  AND a.ASSIGNMENT_STATUS_TYPE = 'ACTIVE_ASSIGN';
      

This query returns exactly one row per active employee. Let's break down each filter:

TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE — gets the current version of the person record
a.EFFECTIVE_LATEST_CHANGE = 'Y' — gets only the most recent assignment row (eliminates date-effective history rows)
a.ASSIGNMENT_TYPE = 'E' — employee assignments only (eliminates benefit, pending, non-worker rows)
a.ASSIGNMENT_STATUS_TYPE = 'ACTIVE_ASSIGN' — active employees only (eliminates terminated, suspended, leave-of-absence)

Why Not Just Filter by Date Range?

A common mistake is filtering by TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE on the assignment table instead of using EFFECTIVE_LATEST_CHANGE. This often works but has edge cases:

⚠️ Date-range filter — works most of the time but has edge cases:

-- This can still return multiple rows for employees with future-dated changes
WHERE TRUNC(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
  AND a.ASSIGNMENT_TYPE = 'E'
      

The problem: if an assignment change is entered with a future effective date (e.g., a promotion that starts next week), Oracle creates a new row with EFFECTIVE_START_DATE in the future. For that employee, two rows overlap today — the current assignment and the future-dated one. The date range filter will return both.

EFFECTIVE_LATEST_CHANGE = 'Y' handles this correctly by only flagging the most recently entered change, regardless of effective date.

Best practice: Use EFFECTIVE_LATEST_CHANGE = 'Y' as your primary deduplication filter. Use date range as a secondary filter only if you need to report as-of a specific historical date.

As-Of Date Queries: Reporting on Past or Future State

If you need to report on assignment data as of a specific date (not today), replace EFFECTIVE_LATEST_CHANGE with a date-range filter. This is the correct pattern for point-in-time reporting:

-- Assignments as of a specific date (e.g., end of last quarter)
SELECT
  p.PERSON_NUMBER,
  p.FULL_NAME,
  a.ASSIGNMENT_NUMBER,
  a.GRADE_ID,
  a.DEPARTMENT_ID
FROM PER_ALL_PEOPLE_F p
JOIN PER_ALL_ASSIGNMENTS_M a
  ON p.PERSON_ID = a.PERSON_ID
WHERE DATE '2025-12-31' BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE
  AND DATE '2025-12-31' BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
  AND a.ASSIGNMENT_TYPE = 'E'
  AND a.ASSIGNMENT_STATUS_TYPE = 'ACTIVE_ASSIGN';
      

For historical queries, date-range filtering is correct — EFFECTIVE_LATEST_CHANGE only applies to the current state.

Key Columns Reference

Column	Purpose	Common Filter Value
EFFECTIVE_LATEST_CHANGE	Flags the most recent date-effective row per assignment	'Y' for current state
ASSIGNMENT_TYPE	Type of work relationship	'E' for employee headcount
ASSIGNMENT_STATUS_TYPE	Active vs terminated vs suspended	'ACTIVE_ASSIGN' for active employees
EFFECTIVE_START_DATE	When this version of the assignment started	Use with EFFECTIVE_END_DATE for point-in-time
EFFECTIVE_END_DATE	When this version ends (4712-12-31 = no end date)	4712-12-31 = open-ended (current)
PRIMARY_FLAG	Marks the primary assignment when a worker has multiple	'Y' if you only want the primary assignment
PERSON_ID	FK to PER_ALL_PEOPLE_F	Join key
ASSIGNMENT_ID	Unique assignment identifier	Used for joining to other assignment tables

Multiple Assignments Per Person

Some organizations allow workers to hold multiple active assignments simultaneously (e.g., a part-time worker in two departments). In this case, even with EFFECTIVE_LATEST_CHANGE = 'Y' and ASSIGNMENT_TYPE = 'E', you may still see two rows per person.

To get only the primary assignment:

-- Add PRIMARY_FLAG to get exactly one row per worker
WHERE a.EFFECTIVE_LATEST_CHANGE = 'Y'
  AND a.ASSIGNMENT_TYPE = 'E'
  AND a.ASSIGNMENT_STATUS_TYPE = 'ACTIVE_ASSIGN'
  AND a.PRIMARY_FLAG = 'Y'
      

Use PRIMARY_FLAG = 'Y' only if your organization uses primary assignment designation. If it's not populated, this filter will exclude valid employees.

Explore the Full PER_ALL_ASSIGNMENTS_M Schema

View all columns for PER_ALL_ASSIGNMENTS_M and every other Oracle Fusion HCM table. Find foreign key relationships, data types, and join paths between assignment, person, and payroll tables.

Search Oracle HCM Tables →

Summary

PER_ALL_ASSIGNMENTS_M returns multiple rows per employee because:

Date-effective history — every change creates a new row. Fix: add EFFECTIVE_LATEST_CHANGE = 'Y'
Multiple assignment types — employee, benefit, pending rows coexist. Fix: add ASSIGNMENT_TYPE = 'E'
Terminated/inactive assignments — still stored after termination. Fix: add ASSIGNMENT_STATUS_TYPE = 'ACTIVE_ASSIGN'
Multiple active assignments — workers in multiple roles. Fix: add PRIMARY_FLAG = 'Y' if needed

The standard current-state headcount query combines all four: EFFECTIVE_LATEST_CHANGE = 'Y' + ASSIGNMENT_TYPE = 'E' + ASSIGNMENT_STATUS_TYPE = 'ACTIVE_ASSIGN'. Add PRIMARY_FLAG = 'Y' only if your implementation uses it.