Oracle Fusion HCM Table Suffixes: What F, TL, and V Signal

Oracle Fusion HCM Table Suffixes: What F, TL, and V Signal comes down to three clues: effective-dated data, translated rows, and views. Read those clues correctly and you can avoid the duplicate rows, missing labels, and incorrect as-of results that derail Oracle HCM reporting.

Key Takeaways

Suffix	What it commonly signals	What to verify before querying
_F	A date-effective table with records that can represent different effective periods.	Which as-of date, boundary rules, and effective-change filters your report requires.
_TL	A translation table related to a base table, often with a row for each language.	The shared key and the LANGUAGE value that should appear in the result.
_V	A view. Its name alone does not establish whether it filters dates or languages.	The view definition, exposed columns, and applicable data security.
_VL	Commonly a language-aware view of translated data.	How it selects language rows, including whether behavior depends on session language.
_B with _TL	A base-and-translation table pattern for storing core data and translated labels separately.	The join key and whether your query needs an inner join, left join, or language fallback.
_ALL	In some Oracle table families, a broader set of organization-related rows.	The actual organization columns, filtering requirements, and security rules.

The Short Answer: Suffixes Are Naming Clues, Not Contracts

Oracle Fusion HCM tables store application data such as people, assignments, benefits, and configuration. The suffix often points to a table’s role in the HCM data model: _F commonly signals effective dating, _TL commonly signals translation rows, and _V signals a view.

But a suffix is not a guarantee that every table follows identical behavior across Oracle Fusion HCM modules. Treat the name as a starting clue, then verify the table’s columns, keys, view definition, and documented purpose in the metadata available to your reporting environment.

This distinction matters in OTBI reporting and BI Publisher. OTBI presents a logical model through subject areas, while BI Publisher SQL works with the physical reporting objects available to your role. A suffix can guide your investigation, but it does not tell you which object your reporting tool exposes or which rows your user is authorized to see.

What an _F Table Means for Effective Dating

An _F table commonly stores date-effective history. Instead of overwriting a person or configuration row each time it changes, the table can retain records associated with different effective periods, usually represented by EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.

Your first decision is the report’s as-of date. A current-state report, a historical snapshot, and an audit report need different date logic. Querying an _F table without making that decision is the #1 cause of returning multiple periods for the same business entity.

For a timestamp-based as-of query, the basic pattern is:

SELECT f.*
FROM <your_table>_F f
WHERE f.effective_start_date <= :as_of_timestamp
  AND f.effective_end_date >= :as_of_timestamp;

This assumes the start and end boundaries are inclusive and that your bind value uses the same time convention as the stored dates. For a date-only report, define the beginning and end of the requested day explicitly; avoid wrapping indexed date columns in functions unless your query plan supports it.

Effective dating can also include multiple changes within an effective period. If the table exposes a field such as EFFECTIVE_LATEST_CHANGE, apply it only when its documented meaning matches the report. It is not a universal filter for every _F table.

A current-row view and a date-effective table are not interchangeable. A view may select or derive a particular row set, but _V does not itself mean “current row.” Read the view definition and test its date logic rather than inferring it from the suffix.

How _TL Translation Tables Join to _B Base Tables

A _B base table commonly stores the core record, while its corresponding _TL translation table stores translated text associated with that record. A useful example family is HCM_EXTENDED_LOOKUP_CODES_B, HCM_EXTENDED_LOOKUP_CODES_TL, and HCM_EXTENDED_LOOKUP_CODES_VL.

To join a base table to its translation table, match the documented shared key and filter the translation rows by language. Do not guess the key from the table names; inspect the table metadata and use every key column required by that table family.

SELECT b.*, tl.*
FROM <base_table> b
JOIN <translation_table> tl
  ON tl.<shared_key> = b.<shared_key>
 AND tl.language = :language;

The placeholders are intentional. Replace them with the actual table names, join columns, and language code for your table. Filtering by LANGUAGE prevents one base row from multiplying across several translation rows.

Use an inner join when a translated row is required for the result. Use a left join when you need to retain the base record even if the requested language row is unavailable, and put the language condition in the join clause so it does not discard unmatched base rows.

The language choice can depend on session context or an explicit query filter, depending on the object. An explicit language bind makes a report’s output reproducible across sessions; a fallback to another language needs a deliberate rule, not an assumption that Oracle will supply the preferred label automatically.

When to Use a _VL View Instead of Joining _B and _TL

A _VL view commonly presents translated data through a language-aware view, so it can save you from writing the base-to-translation join yourself. A _V view is a broader category: it can expose a selected or derived data set, but the suffix alone does not tell you that it handles language.

Use a _VL view when its exposed columns and language behavior match your report. Use the _B and _TL tables directly when you need explicit control over language selection, fallback logic, or which base and translation columns you return.

Do not assume every _VL view follows the same session-language rule. Inspect its definition and confirm whether the language is selected through session context, a view predicate, or another mechanism. For comparison, the HCM_LOOKUPS object is a useful named reference point when examining lookup-related reporting objects, but its actual behavior must come from its own metadata.

The _V suffix also does not mean that a view automatically applies the correct effective-date filter. If your result requires a particular date or language, verify that the view handles it or add the needed filter in your query.

How _ALL Tables Relate to Organization Data

An _ALL name can signal a broad table family that contains rows associated with multiple organizations or business units. In some Oracle products, a corresponding secured or context-aware view applies organization filtering, but the exact relationship varies by module and object.

Do not assume that _ALL means “all rows this user may see,” or that another suffix means organization filtering is already applied. Check organization identifiers, the reporting tool’s security context, and any required business-unit or legislative filters. Naming conventions are not data security.

Other Oracle Table Suffixes to Recognize

• _S commonly identifies a sequence used to generate values, rather than a table of business records.
• _A commonly appears on audit-related tables that record changes or historical activity.
• _KFV commonly identifies a key flexfield view, often used to present flexfield values in a query-friendly form.
• _DFV commonly identifies a descriptive flexfield view.

These are useful search clues, not universal definitions. The suffix on its own does not prove which columns a flexfield view exposes, what an audit table records, or how a sequence is used by a specific application process.

Common Query Mistakes and the Fix

• Returning duplicate effective periods: choose the report’s as-of timestamp, then apply the correct start and end date predicates.
• Multiplying rows after a translation join: join on the complete documented key and include the intended LANGUAGE value.
• Returning codes without translated labels: join to the translation table or use a verified language-aware view.
• Assuming every view is language-aware: check the view definition; _V and _VL do not signal the same thing.
• Assuming a suffix applies organization security: validate the query’s security context and organization filters separately.

To find columns and relationships, inspect the table metadata exposed by your environment. Where Oracle dictionary views are available, a column lookup can start with:

SELECT column_name, data_type, nullable
FROM all_tab_columns
WHERE table_name = UPPER(:table_name)
ORDER BY column_id;

Use constraint metadata, including ALL_CONSTRAINTS and ALL_CONS_COLUMNS, to investigate declared keys where those views are accessible. In a managed reporting environment, use the supported schema reference or catalog available to your role; dictionary access is not guaranteed in every Oracle Cloud HCM reporting connection.

Frequently Asked Questions

Does _F mean the table is a fact table?

No. In Oracle Fusion HCM naming, _F commonly signals a date-effective table, not an analytics fact table. Determine whether the object stores effective-dated application records from its columns and metadata.

Can HDL load data directly into an _F or _B table?

No. HDL uses supported business objects and load definitions rather than treating physical table suffixes as import targets. Use the relevant HDL object and its required attributes instead of attempting a direct table write.

Can I query Oracle Fusion HCM tables from any external SQL client?

No; use a reporting interface and connection for which your account has been granted access.

Why does an OTBI subject area not expose a table I can find by name?

Check the subject area's logical model and available reporting paths; a physical table name alone does not establish that it is selectable in OTBI.

Does a suffix tell me whether a table is supported for custom reporting?

No; validate the specific reporting object and access path for your Fusion release, since a suffix cannot establish support.

Conclusion

Oracle Fusion HCM Table Suffixes: What F, TL, and V Signal gives you a fast way to classify likely date-effective tables, translation tables, and views. The reliable fix is to confirm the actual columns and definitions, set the correct as-of date, join translations on their real keys, and apply language, organization, and security rules explicitly.