What is the difference between a _B table and a _TL table?

A _B table stores the base structural attributes and identifiers of an object, while the corresponding _TL table stores translated, user-friendly text values for that object in multiple languages.

Why do _F tables require a date filter?

_F tables are date-effective tables that store every historical version of a record. Queries should filter using EFFECTIVE_START_DATE and EFFECTIVE_END_DATE to return only the correct version for a specific date.

What does the _X suffix mean in Oracle HCM?

The _X suffix represents a view that returns only the currently effective row from a date-tracked (_F) table, eliminating the need for manual SYSDATE filtering.

What are _KFV and _DFV views used for?

_KFV views expose Key Flexfield values, while _DFV views expose Descriptive Flexfield values. These views make it easier to query business-friendly flexfield data instead of raw attribute columns.

Why don't _ALL tables apply organization-level security?

_ALL tables are designed to store data across multiple business units and organizations. Because they intentionally include all organizations, report developers must explicitly apply organization-level filters when needed.

30 July 2026

Oracle HCM Fusion Table Suffixes: _ALL, _TL, _B, _F, _VL Explained

Q: What does the _TL suffix mean in Oracle HCM tables?

The _TL suffix stands for Translation Layer. A _TL table stores one row per business object for each installed language, allowing translated labels such as job titles and organization names to be displayed in different languages.

Executive Summary

Every table in Oracle Fusion HCM follows a naming convention, and the suffix at the end tells you exactly what the table does: whether it holds translations, current values only, or a full date-tracked history. Get the suffix wrong in a query, and you don't get an error; you get silently wrong data: duplicate rows, missing history, or results in the wrong language. This piece explains each suffix with real table examples and the specific reporting bug each one causes when it's misunderstood. DataTerrain's Oracle HCM Analytics team builds these conventions correctly into every reporting engagement.

Key Takeaways

_B and _TL are a pair. A base table like PER_JOBS_B almost always has a matching PER_JOBS_TL holding the translated name. Query the base table alone, and you get IDs with no readable labels.
_F tables store full history, not current state. Querying PER_ALL_PEOPLE_F without a date filter on EFFECTIVE_START_DATE/EFFECTIVE_END_DATE returns every historical version of a person's record, not just today's.
_VL views already do the _TL join for you. If a _VL view exists, use it instead of manually joining base and translation tables.
Missing a LANGUAGE filter on a _TL table is a classic row-multiplication bug; one row per installed language comes back instead of one.
_ALL means no organization-level security filter. These tables span every operating unit, useful for cross-org reporting, dangerous if you assumed row-level security was already applied.

What Are Oracle HCM Table Suffixes?

Oracle Fusion HCM Cloud has roughly 15,000 tables, and each one has a suffix that tells you what kind of data it holds and how it should be queried. Getting the suffix wrong doesn't throw an error; it just returns incorrect data that looks plausible until someone checks the numbers.

Oracle HCM Table Suffix Reference

Suffix	Meaning	Example Table	What Goes Wrong If You Ignore It
_ALL	Multi-org data, no security filter applied	PAY_ELEMENT_TYPES_ALL	Cross-org data leaks into single-org reports
_B	Base table, structural attributes only	PER_JOBS_B	IDs with no readable name or label
_TL	Translation table, one row per language	PER_JOBS_TL	One row per installed language returned per record
_VL	View joining a _B table with its _TL table	PER_JOBS_VL	None if used correctly; manual joins risk the _TL row-multiplication bug
_F	Date-tracked history (EFFECTIVE_START_DATE / EFFECTIVE_END_DATE)	PER_ALL_PEOPLE_F	Every historical version returned instead of the current one
_X	View showing only the currently effective row of a _F table	PER_PEOPLE_X	Manually filtering _F tables by SYSDATE is error-prone; _X does it for you
_V	Generic view over a base table	Varies by module	Query inefficiency if the underlying base table is queried directly instead
_S	Sequence-generated primary key values	Varies by module	Not typically queried directly; relevant mainly for custom extensions
_A, _AVN, _ACN	Audit tables tracking changes over time	Varies by module	Missed audit trail for compliance and change tracking
_KFV	View containing Key Flexfield (KFF) segment values	Varies by module	KFF values queried by raw attribute column instead of by name
_DFV	View containing Descriptive Flexfield (DFF) segment values	Varies by module	Same issue as _KFV, but for DFF attributes
_EFC	Euro Financial Currency configuration tables	Varies by module	Relevant mainly in EU-currency financial configurations

Why These Suffixes Matter for Reporting

The suffixes aren't just naming trivia; they directly determine whether a query is correct. Three patterns come up constantly, and getting any of them wrong is exactly the kind of issue DataTerrain's Reports Conversion team catches when migrating legacy HCM reports onto Fusion.

The _TL row-multiplication bug. PER_JOBS_TL holds one row per business object per installed language. Query it without filtering LANGUAGE = 'US' (or your session's language code), and every job title comes back once per installed language, silently multiplying your row count. This is one of the most common Oracle HCM reporting bugs, and it rarely throws an obvious error; the report just quietly has more rows than expected.

The _F missing-date-filter bug. PER_ALL_PEOPLE_F stores every version of an employee's record across their entire history, each bounded by EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. A query with no date predicate returns every historical version rather than the one true record as of today, which is why almost every query against a _F table needs a date filter, or should use the corresponding _X view where one exists.

The _B-without-_TL bug. PER_JOBS_B holds the job's ID and structural attributes, but the readable job title lives in PER_JOBS_TL. Querying _B alone gets you IDs with no labels attached; a _VL view (like PER_JOBS_VL) usually already does this join correctly and should be used instead of hand-joining the two tables.

Optimizing Data Management with _ALL, _TL, _B, and _F Tables

_ALL tables consolidate data across operating units, giving a comprehensive view for cross-organization analysis. Because there's no built-in security filter, query optimization (indexing on ORG_ID, appropriate WHERE clauses) matters more here than on single-org tables.
_TL tables need consistent data governance across language settings, standardized data entry procedures, and a LANGUAGE filter on every query to avoid the row-multiplication issue above.
_B tables enforce validation and integrity constraints at the database level. Data corruption here is serious, since these are the tables every other view and translation table depends on.
_F tables support historical trend analysis when queried correctly; effective-dated queries let you reconstruct "what did this record look like on any given date," which is exactly what audit and retroactive-correction scenarios need.

How DataTerrain Uses This in Practice

Getting table suffixes wrong is a common source of broken reports during Oracle HCM migrations and custom reporting builds. DataTerrain's Oracle HCM Analytics practice builds reporting and data models against these conventions correctly from the start, applying the right date filters on _F tables, the right language filters on _TL tables, and using _VL/_X views wherever they already exist instead of reinventing the join. If you're migrating legacy HCM reports onto Oracle Fusion, our Reports Conversion services carry this same suffix-level discipline through the conversion so migrated reports don't silently break on row-multiplication or stale-date bugs. This same table-level rigor underpins our Oracle Fast Formulas work, since formulas frequently read from these same _F and _TL tables.

Schedule a Consultation

Frequently Asked Questions

What does the _TL suffix mean in Oracle HCM tables?

_TL stands for translation. A _TL table holds one row per business object per installed language, distinguished by a LANGUAGE column, so translated labels (like job titles) live separately from the base table's structural data.

What is the difference between a _B and a _TL table?

A _B (base) table holds the structural attributes and ID of an object, with strict validation. The matching _TL table holds the translated, human-readable text for that same object. You'll rarely find one without the other.

Why do _F tables need a date filter in queries?

_F tables store the full history of a record, with every version bounded by EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. Without a date predicate, a query returns every historical version instead of just the current one.

What is a _VL view in Oracle HCM?

A _VL view combines a base (_B) table with its translation (_TL) table, filtered to the user's session language, so you get readable labels without writing the join yourself.

What does the _X suffix mean?

_X denotes a view that shows only the currently effective row of a date-tracked (_F) table, filtered as of the current system date, avoiding the need to manually filter by SYSDATE.

What are _KFV and _DFV tables used for?

_KFV views expose Key Flexfield (KFF) segment values, and _DFV views expose Descriptive Flexfield (DFF) segment values; both let you query flexfield data by name rather than by raw attribute column.

Why does an _ALL table not have organization-level security by default?

_ALL tables are designed to span multiple operating units for cross-org reporting and analysis. Because they intentionally aggregate across orgs, any org-level security filtering has to be applied explicitly in the query; it isn't built into the table itself.

Related Resources

Oracle Fast Formulas in Fusion HCM | Oracle HCM Analytics | Reports Conversion