Is PER_ALL_ASSIGNMENTS_F deprecated in Oracle 24B?

PER_ALL_ASSIGNMENTS_F is not officially deprecated in Oracle 24B, but Oracle's recommended reporting approach has shifted to PER_ALL_ASSIGNMENTS_M. The _M table exposes EFFECTIVE_LATEST_CHANGE and EFFECTIVE_SEQUENCE columns that make current-state queries far simpler and more reliable. New OTBI subject areas and Oracle-delivered reports are built on the _M view, and Oracle documentation now directs developers to use _M for all new development.

What is the difference between PER_ALL_ASSIGNMENTS_F and PER_ALL_ASSIGNMENTS_M?

PER_ALL_ASSIGNMENTS_F (the _F suffix = date-effective/fused) requires SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE to filter current rows. PER_ALL_ASSIGNMENTS_M (the _M suffix = merged) adds the EFFECTIVE_LATEST_CHANGE column, which Oracle populates automatically to flag the current row. _M also includes EFFECTIVE_SEQUENCE for ordering. For current-state queries, EFFECTIVE_LATEST_CHANGE = 'Y' is more reliable than date-range filtering.

Why do OTBI assignment reports break after upgrading to Oracle 24B?

Oracle 24B updated several OTBI subject area view objects to reference _M tables instead of _F tables. Reports that used custom SQL overrides or direct table references to PER_ALL_ASSIGNMENTS_F may return incorrect row counts or miss current-state data. The fix is to replace PER_ALL_ASSIGNMENTS_F references with PER_ALL_ASSIGNMENTS_M and update the WHERE clause to use EFFECTIVE_LATEST_CHANGE = 'Y' instead of date-range filtering.

What does EFFECTIVE_LATEST_CHANGE mean in PER_ALL_ASSIGNMENTS_M?

EFFECTIVE_LATEST_CHANGE is an Oracle-managed column in _M tables that flags the most recently entered row for each assignment with 'Y'. Unlike date-range filtering, EFFECTIVE_LATEST_CHANGE correctly handles future-dated changes — if a promotion is entered for next week, Oracle marks it as EFFECTIVE_LATEST_CHANGE = 'Y' while the current row gets 'N'. For point-in-time historical queries, use date-range filtering instead.

PER_ALL_ASSIGNMENTS_F in Oracle 24B: What Changed and How to Migrate

If you've been writing Oracle Fusion HCM SQL since before Release 13, your queries probably reference PER_ALL_ASSIGNMENTS_F. It's been the go-to assignments table for years. But starting with Oracle's 24A and 24B quarterly releases, the recommended pattern has shifted — and if you're still writing new reports against _F, you're doing it the hard way.

This guide covers what changed, why PER_ALL_ASSIGNMENTS_M is now Oracle's preferred table for most reporting scenarios, and the exact patterns to migrate your existing SQL and OTBI reports.

Before you start: PER_ALL_ASSIGNMENTS_F is not removed or officially deprecated in Oracle 24B. Existing queries that work correctly will continue to work. This is about best practices for new development and fixing the edge cases where _F queries return incorrect results.

The _F vs _M Difference (Quick Recap)

In Oracle Fusion HCM, most core tables come in two variants:

Suffix	Name	How to Filter for Current Row
`_F`	Date-effective (Fused)	`TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE`
`_M`	Merged (with metadata)	`EFFECTIVE_LATEST_CHANGE = 'Y'`

The _F table stores every date-effective version of a record. To get today's row you filter by date range. The _M table contains the same data but adds EFFECTIVE_LATEST_CHANGE — an Oracle-managed flag that marks the most recently entered row as 'Y'.

That one column change has significant implications for how queries behave, especially when future-dated changes are involved.

Why _F Queries Break with Future-Dated Changes

This is the root cause of most assignment query bugs reported after 24B upgrades.

When an HR administrator enters a change with a future effective date — a promotion that starts next quarter, a grade change effective next month — Oracle creates a new row in the assignments table immediately. That row has a future EFFECTIVE_START_DATE.

Here's what happens with each filter approach:

⚠️ PER_ALL_ASSIGNMENTS_F with date-range filter — breaks on future-dated changes:

-- PER_ALL_ASSIGNMENTS_F: future-dated change causes duplicate
SELECT PERSON_ID, GRADE_ID, EFFECTIVE_START_DATE
FROM PER_ALL_ASSIGNMENTS_F
WHERE PERSON_ID = 123456
  AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
  AND ASSIGNMENT_TYPE = 'E';

-- Returns 2 rows if today falls within overlapping date ranges:
-- Row 1: Grade G5, effective 2024-01-01 to 2026-06-29  ← current
-- Row 2: Grade G6, effective 2026-06-30 to 4712-12-31  ← future
-- SYSDATE is 2026-04-04 — only Row 1 should match, but edge cases exist

✅ PER_ALL_ASSIGNMENTS_M with EFFECTIVE_LATEST_CHANGE — handles future changes correctly:

-- PER_ALL_ASSIGNMENTS_M: always returns the most recently entered row
SELECT PERSON_ID, GRADE_ID, EFFECTIVE_START_DATE
FROM PER_ALL_ASSIGNMENTS_M
WHERE PERSON_ID = 123456
  AND EFFECTIVE_LATEST_CHANGE = 'Y'
  AND ASSIGNMENT_TYPE = 'E';

-- Returns 1 row: Grade G6 (the most recently entered change)
-- EFFECTIVE_LATEST_CHANGE flags the future change as the "latest" entry

The behavior difference matters: _F with date-range gives you "what's active today." _M with EFFECTIVE_LATEST_CHANGE gives you "what is the most recently entered state." For current-state headcount reports, _M is the correct choice in almost every case.

What Oracle Changed in 24A/24B

Oracle's quarterly releases don't always announce schema changes in headlines — they show up in the OTBI subject area updates, the Oracle-delivered report redesigns, and the technical documentation revisions.

In the 24A and 24B cycles, Oracle updated:

OTBI Worker Real Time subject area — underlying view objects now reference _M tables for assignment data. Custom reports using direct SQL overrides to PER_ALL_ASSIGNMENTS_F may produce different row counts than Oracle-delivered reports.
Worker Assignment Real Time subject area — assignment history dimensions now expose EFFECTIVE_LATEST_CHANGE as a filterable attribute. Historical queries should use date-range; current-state queries should use this flag.
Oracle-delivered headcount analytics — rebuilt on _M tables. If your custom reports produce different headcount numbers than Oracle's delivered workforce analytics, this is the likely cause.
HCM Data Loader validations — 24B tightened assignment data validation to align with the _M data model. Some HDL loads that previously succeeded now require explicit effective dating.

The Full Migration Pattern

Replacing PER_ALL_ASSIGNMENTS_F with PER_ALL_ASSIGNMENTS_M requires three changes:

1. Replace the table reference

-- Before (PER_ALL_ASSIGNMENTS_F)
FROM PER_ALL_ASSIGNMENTS_F paf

-- After (PER_ALL_ASSIGNMENTS_M)
FROM PER_ALL_ASSIGNMENTS_M pam

2. Replace the date-range filter with EFFECTIVE_LATEST_CHANGE

-- Before: date-range filter
WHERE TRUNC(SYSDATE) BETWEEN paf.EFFECTIVE_START_DATE AND paf.EFFECTIVE_END_DATE

-- After: flag-based filter
WHERE pam.EFFECTIVE_LATEST_CHANGE = 'Y'

3. Keep (or add) the assignment type and status filters

-- These stay the same regardless of _F or _M
AND pam.ASSIGNMENT_TYPE = 'E'                    -- employee assignments only
AND pam.ASSIGNMENT_STATUS_TYPE = 'ACTIVE_ASSIGN' -- active employees only

Complete Before/After Example

Before (PER_ALL_ASSIGNMENTS_F pattern):

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

After (PER_ALL_ASSIGNMENTS_M pattern — Oracle 24B recommended):

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

Both queries return active employees, but the _M version is more reliable when future-dated changes exist in the system — which in any active Oracle HCM implementation, they almost certainly do.

When to Keep Using PER_ALL_ASSIGNMENTS_F

The _F table is still the right choice for point-in-time historical queries. If you need to answer "what was this person's assignment as of December 31, 2024?", the date-range filter on _F is correct:

-- Historical as-of query: use _F with explicit date range
SELECT
  p.PERSON_NUMBER,
  paf.GRADE_ID,
  paf.DEPARTMENT_ID,
  paf.EFFECTIVE_START_DATE
FROM PER_ALL_PEOPLE_F p
JOIN PER_ALL_ASSIGNMENTS_F paf
  ON p.PERSON_ID = paf.PERSON_ID
WHERE DATE '2024-12-31' BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE
  AND DATE '2024-12-31' BETWEEN paf.EFFECTIVE_START_DATE AND paf.EFFECTIVE_END_DATE
  AND paf.ASSIGNMENT_TYPE = 'E';

The _M table's EFFECTIVE_LATEST_CHANGE flag reflects the most recently entered row, not the row active on a specific historical date. For historical as-of queries, _F with explicit date filtering is still the correct approach.

Key Columns Compared

Column	PER_ALL_ASSIGNMENTS_F	PER_ALL_ASSIGNMENTS_M
EFFECTIVE_START_DATE	Present — use for historical queries	Present — use for historical queries
EFFECTIVE_END_DATE	Present — 4712-12-31 = open-ended	Present — same semantics
EFFECTIVE_LATEST_CHANGE	Not available	'Y' = most recently entered row
EFFECTIVE_SEQUENCE	Not available	Ordering number for same-date changes
ASSIGNMENT_TYPE	E/C/N/B/P/O	Same values, same semantics
ASSIGNMENT_STATUS_TYPE	ACTIVE_ASSIGN, INACTIVE, etc.	Same values, same semantics
PRIMARY_FLAG	Y/N — primary assignment indicator	Same values, same semantics

OTBI Report Migration

If you have custom OTBI reports that use logical SQL overrides or direct database joins referencing PER_ALL_ASSIGNMENTS_F, you'll need to update these manually.

For reports using standard OTBI subject area columns (no custom SQL), the underlying view objects were updated by Oracle. You may still see behavioral differences if:

Your report uses an assignment date filter built before 24B — check if the filter logic was date-range based and whether it now needs to reference the Effective Latest Change attribute
Your headcount totals differ from Oracle's delivered analytics — run the same query on both _F (date-range) and _M (EFFECTIVE_LATEST_CHANGE = 'Y') and compare; the difference is future-dated rows
You use the Worker Assignment Real Time subject area with custom assignment date dimensions — verify these dimensions resolve correctly after the 24B update

Migration Checklist

Audit all custom SQL reports for PER_ALL_ASSIGNMENTS_F references
Replace date-range filters with EFFECTIVE_LATEST_CHANGE = 'Y' for current-state queries
Keep date-range filters for historical point-in-time queries
Verify headcount numbers match Oracle-delivered analytics after migration
Test against employees with pending future-dated assignment changes
Check HDL load templates — update effective date logic if loads are failing validation
Review OTBI report custom SQL overrides for direct table references

Related Changes: EFFECTIVE_SEQUENCE

The _M table also exposes EFFECTIVE_SEQUENCE, which becomes important when multiple assignment changes are entered on the same calendar date. In _F, same-day changes share the same EFFECTIVE_START_DATE, which can produce ambiguous results. In _M, EFFECTIVE_SEQUENCE provides an ordering number to break ties:

-- When multiple changes land on the same date, EFFECTIVE_SEQUENCE determines order
SELECT PERSON_ID, GRADE_ID, EFFECTIVE_START_DATE, EFFECTIVE_SEQUENCE, EFFECTIVE_LATEST_CHANGE
FROM PER_ALL_ASSIGNMENTS_M
WHERE PERSON_ID = 123456
  AND ASSIGNMENT_TYPE = 'E'
ORDER BY EFFECTIVE_START_DATE, EFFECTIVE_SEQUENCE;

-- EFFECTIVE_LATEST_CHANGE = 'Y' on the highest EFFECTIVE_SEQUENCE row for each date

Explore PER_ALL_ASSIGNMENTS_M and Every Oracle HCM Table

Search all columns for PER_ALL_ASSIGNMENTS_M, PER_ALL_ASSIGNMENTS_F, and 14,950+ Oracle Fusion HCM tables. Find join paths, data types, and FK relationships between assignment, person, and payroll tables.

Search Oracle HCM Tables →

Summary

Oracle's 24A/24B releases formalized the shift toward _M tables for assignment reporting. The practical impact:

New reports — always use PER_ALL_ASSIGNMENTS_M with EFFECTIVE_LATEST_CHANGE = 'Y' for current-state queries
Existing reports that work — no urgency to migrate unless you're seeing headcount discrepancies or future-dated row issues
Historical queries — keep using PER_ALL_ASSIGNMENTS_F with explicit date-range filters; _M's EFFECTIVE_LATEST_CHANGE does not apply to historical as-of queries
OTBI reports — audit custom SQL overrides; Oracle-delivered reports were updated but custom logical SQL may not have been

The easiest way to validate your migration: run both the old query (PER_ALL_ASSIGNMENTS_F + date-range) and the new query (PER_ALL_ASSIGNMENTS_M + EFFECTIVE_LATEST_CHANGE) against the same dataset. If results differ, you have future-dated assignment changes in the system — the _M version is correct.

PER_ALL_ASSIGNMENTS_F in Oracle 24B: What Changed and How to Migrate Your Reports

Stay Current with Oracle HCM Updates