Oracle HCM Table Suffixes Explained (_F, _TL, _VL, _ALL)

If you have ever browsed the Oracle Fusion Cloud HCM schema, you have seen table names like PER_ALL_PEOPLE_F, PER_ALL_PEOPLE_TL, and PER_ALL_PEOPLE_VL. The suffix at the end is not random. It encodes critical information about what the table stores and how it behaves.

Understanding these suffixes is essential for writing correct queries, building OTBI reports, and setting up HCM Extract or BIP data models. This guide covers every suffix you will encounter.

Quick Reference: All Table Suffixes

_F: Date-Effective Tables

The _F suffix is the most important suffix in Oracle Fusion HCM. It stands for "Fusion" (historically debated, but functionally it means date-effective). These tables track how data changes over time using two key columns:

• EFFECTIVE_START_DATE — when this row's version becomes active
• EFFECTIVE_END_DATE — when this row's version expires (default: 4712-12-31)

A single person in PER_ALL_PEOPLE_F may have dozens of rows, each representing a different time period. For example, when someone changes their last name, Oracle does not overwrite the old row. Instead, it end-dates the current row and inserts a new one.

Getting the Current Row

To get only the currently-active row, filter like this:

SELECT *
FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = :person_id
  AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE
                          AND EFFECTIVE_END_DATE;

Key _F Tables

_TL: Translation Tables

The _TL suffix means Translation. Oracle Fusion supports multi-language deployments, so any text that could be translated (names, descriptions, job titles) is stored in a separate _TL table. These tables always have a LANGUAGE column and a SOURCE_LANG column.

SELECT first_name, last_name, language
FROM PER_ALL_PEOPLE_TL
WHERE PERSON_ID = :person_id
  AND LANGUAGE = 'US';

For every row in the base table, there will be one row per installed language in the _TL table. If your instance supports English and French, each person will have two _TL rows.

_VL: View + Language (Convenience View)

The _VL suffix stands for View Language. These are database views (not tables) that join the base _F table with its corresponding _TL table, automatically filtering to the user's session language.

Instead of writing the join yourself:

-- Manual join (what _VL does internally)
SELECT f.*, tl.first_name, tl.last_name
FROM PER_ALL_PEOPLE_F f
JOIN PER_ALL_PEOPLE_TL tl
  ON f.PERSON_ID = tl.PERSON_ID
  AND tl.LANGUAGE = USERENV('LANG')
WHERE TRUNC(SYSDATE) BETWEEN f.EFFECTIVE_START_DATE
                          AND f.EFFECTIVE_END_DATE;

You can just query the _VL view:

-- Equivalent using _VL view
SELECT *
FROM PER_ALL_PEOPLE_VL
WHERE PERSON_ID = :person_id;

_ALL: All Records (No Security)

Tables ending in _ALL return all rows without any business-unit or data-security filtering. The most common example is HR_LOCATIONS_ALL, which shows every location in the system regardless of which business unit owns it.

In Oracle E-Business Suite (the predecessor to Fusion), the _ALL suffix was extremely common (e.g., HR_ALL_ORGANIZATION_UNITS). In Fusion, it is less prevalent but still appears in legacy-style tables and cross-module reference tables.

Some _ALL tables you will encounter:

Note that PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F use "ALL" as part of the table name (not a suffix). These tables still contain all persons/assignments without security filtering, plus they are date-effective (_F).

_B: Base Tables

The _B suffix denotes a base table, the primary storage table without date-effective behavior. Where _F tables have EFFECTIVE_START_DATE and EFFECTIVE_END_DATE, _B tables simply have the current state of the data.

Example: PER_PERIODS_OF_SERVICE_B stores each person's employment period (hire date, termination date) as a single row, not multiple date-effective rows.

_PEO and _EO: Entity Objects (Internal)

Tables and views ending in _PEO or _EO are ADF Entity Object artifacts. These are used internally by Oracle's Application Development Framework. You should generally avoid querying these directly because:

• They may include internal framework columns not meant for reporting
• They can change between patch levels without notice
• The base _F or _B tables contain the same data in a more stable format

_S: Secured Views

Views ending in _S have Oracle Data Security (FND Data Security / FNDDS) applied. They automatically filter rows based on the current user's security profile, business unit access, and data role.

When building reports for end users, _S views ensure users only see data they are authorized to access. When building system-level integrations (running as a service account), you typically use the _F or _ALL table instead.

How to Choose the Right Table

Use this decision tree:

1. Need translated text? Use the _VL view (joins base + translation automatically)
2. Need historical data? Use the _F table with date filters
3. Need security-filtered results? Use the _S view
4. Need all records regardless of business unit? Use the _ALL table
5. Writing an integration or extract? Use the _F table directly
6. Building an OTBI report? Use the _VL view when available

Related Articles

• Complete Guide to PER_ALL_PEOPLE_F
• Top 50 Most Used Oracle Fusion HCM Tables