• Reports Conversion
  • Oracle HCM Analytics
  • Oracle Health Analytics
  • Services
    • ETL SolutionsETL Solutions
    • Performed multiple ETL pipeline building and integrations.

    • Data Lake IconData Lake
    • Experienced in building Data Lakes with Billions of records.

    • e-flyer migration solutions menuMigration Solutions
    • Modernize BI reporting with automated migration.

    • BI Products MenuBI products
    • Successfully delivered multiple BI product-based projects.

    • Legacy Scripts MenuLegacy scripts
    • Successfully transitioned legacy scripts from Mainframes to Cloud.

    • AI/ML Solutions MenuAI ML Consulting
    • Expertise in building innovative AI/ML-based projects.

  • Contact Us
  • Blogs
  • Oracle HCM Insights
  • Suffixes Help Identify the Purpose of oracle HCM Tables

Contents

1/10
What Are Oracle HCM Fusion Table Suffixes Oracle HCM Table Suffixes: Reference Table Understanding Common Oracle HCM Table Suffixes How to Choose the Right Oracle HCM Table for Reporting How to Join Oracle HCM Tables: SQL Examples Common Mistakes When Querying Oracle HCM Tables Oracle HCM Tables and Views: Where to Find Official Definitions How Correct Table Selection Improves Oracle HCM Reporting Frequently Asked Questions Related Resources
  • 11 Nov 2026

Oracle HCM Fusion Table Suffixes Explained with Examples

Quick Summary

Oracle HCM Fusion table suffixes indicate common database object patterns and help users understand how HCM information is organized. For example, _B commonly identifies base tables, _TL identifies translation tables, _F indicates date-effective data, and _VL commonly identifies language-dependent views. Understanding these conventions helps developers choose the right tables, manage joins, apply effective-date conditions, and build more reliable Oracle HCM reports.

Oracle HCM Fusion table suffixes help identify the type of information stored in a database object and how to query it. Suffixes such as _B, _TL, _F, _VL, and _ALL provide useful clues about base records, translated information, date-effective data, and multi-organization structures.

Understanding these naming conventions is important when developing Oracle HCM reports, writing SQL queries, and working with HCM data models. Choosing an unsuitable table or overlooking its underlying structure can cause duplicate records, missing historical information, or incorrect reporting results.

This guide explains common Oracle HCM Fusion table suffixes, their purposes, practical examples, SQL query patterns, and considerations for selecting the right tables for reporting.

Oracle HCM Fusion table suffixes
  • Share Post:
  • LinkedIn Icon
  • Twitter Icon

What Are Oracle HCM Fusion Table Suffixes?

Oracle Fusion HCM uses database tables and views to organize information across modules such as Core HR, Payroll, Workforce Management, and Talent Management.

The names of many Oracle HCM database objects follow recognizable naming conventions. Their suffixes indicate the type of information they contain or how they are intended for use.

For example:

  • PER_JOBS_B is a base-table example associated with job information.
  • PER_JOBS_TL is a translation-table example associated with job information.
  • PER_JOBS_VL is a language-dependent view example.
  • PER_ALL_PEOPLE_F is a date-effective table example.

These naming patterns help developers understand HCM data structure before building queries.

Important: Suffixes are naming conventions, not a substitute for checking the actual database object definition. Object availability, columns, relationships, and behavior can vary by Oracle Cloud release and module.

Oracle HCM Table Suffixes: Reference Table

Suffix Meaning Example Table What Goes Wrong If You Ignore It
_ALLMulti-org data, no security filter appliedPAY_ELEMENT_TYPES_ALLCross-org data leaks into single-org reports
_BBase table, structural attributes onlyPER_JOBS_BIDs with no readable name or label
_TLTranslation table, one row per languagePER_JOBS_TLOne row per installed language returned per record
_VLView joining a _B table with its _TL tablePER_JOBS_VLNone if used correctly; manual joins risk the _TL row-multiplication bug
_FDate-tracked history (EFFECTIVE_START_DATE / EFFECTIVE_END_DATE)PER_ALL_PEOPLE_FEvery historical version returned instead of the current one
_XView showing only the currently effective row of a _F tablePER_PEOPLE_XManually filtering _F tables by SYSDATE is error-prone; _X does it for you
_VGeneric view over a base tableVaries by moduleQuery inefficiency if the underlying base table is queried directly instead
_SSequence-generated primary key valuesVaries by moduleNot typically queried directly; relevant mainly for custom extensions
_A, _AVN, _ACNAudit tables tracking changes over timeVaries by moduleMissed audit trail for compliance and change tracking
_KFVView containing Key Flexfield (KFF) segment valuesVaries by moduleKFF values queried by raw attribute column instead of by name
_DFVView containing Descriptive Flexfield (DFF) segment valuesVaries by moduleSame issue as _KFV, but for DFF attributes
_EFCEuro Financial Currency configuration tablesVaries by moduleRelevant mainly in EU-currency financial configurations

Note: These are common naming patterns and illustrative examples, not universal rules for every Oracle HCM object. Verify individual object definitions, columns, joins, and behavior against the applicable Oracle Fusion Cloud HCM release.

Understanding Common Oracle HCM Table Suffixes

1. _B – Base Tables

The _B suffix commonly identifies a base table that stores core information and structural attributes associated with a business object.

For example, PER_JOBS_B is associated with job information.

Base tables may contain identifiers, codes, and other attributes needed to define a record. However, descriptive or translated information may be stored in a related translation table.

When to investigate _B tables:

  • When retrieving base attributes.
  • When identifying business-object relationships.
  • When joining a base object to its related translation information.
  • When developing queries that require structural information.

Reporting consideration: Querying a base table alone may not provide all the readable descriptions required in a report.

2. _TL – Translation Tables

The _TL suffix commonly identifies a translation table that stores language-dependent information associated with a business object.

For example, PER_JOBS_TL is associated with translated job information.

A translation table can contain multiple language records for the same underlying business object. The LANGUAGE column is therefore important when retrieving descriptions in a particular language.

Why does the language condition matter?

Suppose a job title is available in several installed languages. A query that retrieves all translation records without an appropriate language condition may return multiple rows for the same job.

This can increase row counts and affect report totals.

When to investigate _TL tables:

  • When retrieving translated descriptions.
  • When building multilingual reports.
  • When joining descriptive information to a base table.
  • When investigating unexpected duplicate rows.

3. _VL – Language-Dependent Views

The _VL suffix commonly identifies a language-dependent view that brings together information from related database objects.

For example, PER_JOBS_VL is a commonly referenced view associated with job information.

A suitable _VL view can simplify reporting by providing descriptive information without requiring developers to manually recreate every underlying join.

Why use a _VL view?

Consider a report that needs to display job codes and readable job names.

Instead of manually joining the base and translation objects, a suitable view may provide the required information through a single reporting object.

Important: Do not assume that every _VL view automatically applies all required security, effective-date, or business-context filters. Review the individual view definition and validate its behavior before using it.

4. _F – Date-Effective Tables

The _F suffix commonly identifies date-effective tables that maintain records across different effective periods.

For example, PER_ALL_PEOPLE_F contains date-effective person records.

A single business entity may have multiple records representing changes over time. These records include effective start and end dates.

Why are effective dates important?

Suppose an employee's information changes during their employment. The date-effective table may contain multiple versions of that employee's record.

If a query retrieves all records without considering the relevant effective dates, the report may include historical or future-effective versions instead of the intended record.

Example SQL pattern:

SELECT
    person_id,
    effective_start_date,
    effective_end_date
FROM per_all_people_f
WHERE TRUNC(SYSDATE)
      BETWEEN effective_start_date
      AND effective_end_date;

This example retrieves records effective on the current date, subject to the table's actual data and business requirements.

When to investigate _F tables:

  • When reporting employee history.
  • When retrieving effective employee assignments.
  • When analyzing changes over time.
  • When developing current-state or historical reports.

Important: Effective-date logic must reflect the reporting requirement. A historical report, for example, needs the relevant reporting date rather than automatically using the current date.

5. _ALL – Multi-Organisation Table Naming Pattern

The _ALL suffix commonly appears in objects that contain information across multiple organizational contexts.

For example, PAY_ELEMENT_TYPES_ALL is associated with payroll element information.

These objects may contain organization-related columns that help distinguish records across different organizational contexts.

Why does this matter for reporting?

When developing organization-specific reports, the query must account for the relevant organizational context.

Otherwise, the report may include records beyond the intended reporting scope.

When to investigate _ALL objects:

  • When developing cross-organization reports.
  • When analyzing payroll or other organization-related information.
  • When identifying organization-related columns.
  • When validating data access and reporting security.

Important: The _ALL suffix alone does not establish whether security restrictions are applied or bypassed. Validate the actual object's security and access behavior in the reporting environment.

6. _V – Database Views

The _V suffix commonly appears in database view names.

A view can provide an organized way to access information from underlying database objects.

Views may simplify reporting by exposing selected columns or combining information from multiple objects.

When to investigate _V views:

  • When looking for a reporting-ready representation of information.
  • When checking whether an existing view already provides the required fields.
  • When you need to understand how information is retrieved from underlying objects.

Do not assume that all _V views follow identical structures. Review their definitions before using them.

7. _X – Current-Effective View Naming Pattern

The _X suffix appears in certain Oracle HCM view names associated with current-effective information.

For example, PER_PEOPLE_X is a commonly referenced object name.

Such views may simplify access to information that is effective for a particular date or reporting context.

However, the precise effective-date logic depends on the individual view.

Before using an _X view:

  • Verify its definition.
  • Check which effective-date conditions it applies.
  • Confirm whether it returns the records required for the reporting date.
  • Validate whether additional filters are needed.

Do not assume every _X view behaves identically.

8. _A, _AVN, and _ACN – Audit-Related Objects

Oracle HCM environments include audit-related objects that track and examine changes.

Objects with _A, _AVN, and _ACN naming patterns may be associated with audit information.

These objects should not automatically be treated as current transactional data.

When to investigate audit-related objects:

  • When reviewing historical changes.
  • When investigating modifications to business information.
  • When developing audit-related reporting.
  • When validating the history of a particular record.

Check the specific object's definition to understand the information it exposes and how it relates to the underlying business object.

9. _KFV and _DFV – Flexfield Views

Oracle HCM supports flexfields to accommodate additional business information and configurable attributes.

Two commonly encountered naming patterns are:

_KFV – Key Flexfield View

Key Flexfield views are associated with Key Flexfield structures and may expose concatenated segment information.

_DFV – Descriptive Flexfield View

Descriptive Flexfield views are associated with Descriptive Flexfield information and configured attributes.

These views can help reporting developers retrieve flexfield-related information without manually reconstructing every segment.

The actual columns and available values depend on the specific flexfield configuration and database object.

How to Choose the Right Oracle HCM Table for Reporting

Choosing a table based only on its suffix can lead to incorrect assumptions. Instead, begin with the information your report needs and identify the object that provides it.

Use the following guide:

Reporting requirement What to investigate
Retrieve base attributesRelevant _B object
Retrieve translated descriptionsRelated _TL object or suitable _VL view
Retrieve current-effective informationAppropriate current-effective view or date-filtered table
Retrieve employee historyRelevant _F object
Analyze multiple organizational recordsRelevant _ALL object and organization context
Retrieve database-view informationRelevant _V object
Retrieve audit historyRelevant audit-related objects
Retrieve flexfield informationAppropriate _KFV or _DFV view

Practical tip: Before writing a SQL query, confirm the table name, available columns, primary and foreign key relationships, and relevant security requirements in the applicable Oracle HCM documentation.

How to Join Oracle HCM Tables: SQL Examples

Understanding suffixes becomes more useful when you apply them to real reporting scenarios.

The following examples demonstrate common SQL patterns. They are illustrative starting points, not guaranteed production-ready queries. Validate object names, column names, relationships, language values, and access permissions against your Oracle HCM environment.

Example 1: Joining _B and _TL Tables

Suppose a report needs to retrieve job identifiers and their corresponding descriptive names.

The base table contains core job information, while the translation table provides language-dependent descriptions.

Illustrative SQL:

SELECT
    b.job_id,
    b.job_code,
    tl.name,
    tl.language
FROM per_jobs_b b
JOIN per_jobs_tl tl
    ON b.job_id = tl.job_id
WHERE tl.language = 'US';

How this query works:

  • PER_JOBS_B provides the base job information.
  • PER_JOBS_TL provides the corresponding language-dependent information.
  • The join connects records using the job identifier.
  • The language condition limits the retrieved translation records to the selected language.

Reporting benefit: Applying the appropriate language condition helps prevent multiple translation records from appearing for the same business object.

Example 2: Filtering Date-Effective _F Tables

Suppose a report needs to retrieve employee records effective on the current date.

Illustrative SQL:

SELECT
    person_id,
    effective_start_date,
    effective_end_date
FROM per_all_people_f
WHERE TRUNC(SYSDATE)
      BETWEEN effective_start_date
      AND effective_end_date;

How this query works:

  • PER_ALL_PEOPLE_F provides date-effective person records.
  • EFFECTIVE_START_DATE identifies when a record becomes effective.
  • EFFECTIVE_END_DATE identifies when its effective period ends.
  • The date condition selects records effective on the current date.

Reporting benefit: Applying the appropriate effective-date condition helps prevent historical or future-effective records from being included unintentionally.

Example 3: Using a _VL View

Suppose a report needs job-related information with descriptive labels.

Where a suitable language-dependent view is available, it may simplify the query.

Illustrative SQL pattern:

SELECT
    job_id,
    job_code,
    name
FROM per_jobs_vl;

How this query works:

  • The query accesses the language-dependent view.
  • The view provides the columns exposed by its definition.
  • Developers can use the available descriptive fields without manually recreating the underlying joins.

Reporting benefit: Suitable views can reduce query complexity and make reporting logic easier to maintain.

Important: Confirm the actual view columns and language behavior in your Oracle HCM environment. Add any additional filters required by the reporting requirement.

Common Mistakes When Querying Oracle HCM Tables

Even when developers understand table suffixes, incorrect joins and missing conditions can affect report accuracy.

Common mistake Possible impact Recommended approach
Querying _TL without a language conditionMultiple language records may appearApply the required language condition
Querying _F without effective-date logicHistorical or future records may be includedApply date conditions appropriate to the report
Using _B without related descriptive informationReports may show identifiers instead of readable descriptionsInvestigate related translation objects or suitable views
Assuming _VL handles every filterRequired security or business conditions may be overlookedReview the specific view definition
Ignoring organization context in _ALL objectsRecords outside the intended reporting scope may be returnedValidate organization filters and security
Assuming all suffixes have identical behaviorIncorrect table selection and query logicVerify each database object independently
Selecting unnecessary columns and recordsMore data may be processed than requiredRetrieve only the columns and records needed

How to Troubleshoot Unexpected Query Results

If an Oracle HCM report returns unexpected results, review the following:

  1. Check the object definition: Confirm that the selected table or view contains the required information.
  2. Review join conditions: Validate the relationship between the objects and the join keys.
  3. Check translation records: Confirm that the correct language is selected.
  4. Review effective dates: Verify that the reporting date matches the business requirement.
  5. Validate organizational context: Check the organization-related conditions and security requirements.
  6. Compare record counts: Test each object separately and compare the results after joining.
  7. Review permissions: Confirm that the reporting user has the required access.

These checks help identify whether the issue originates from table selection, joins, date conditions, or the reporting environment.

Oracle HCM Tables and Views: Where to Find Official Definitions

Understanding table suffixes is only one part of working with Oracle HCM data.

Developers should also understand the actual database object definitions, columns, relationships, and available views.

Oracle provides official documentation to help users investigate these details.

Useful resources include:

  • Oracle Fusion Cloud HCM Tables and Views – Explore Oracle HCM database object definitions and related metadata.
  • Oracle Help Center – Access Oracle Fusion Cloud HCM product documentation.
  • Oracle Cloud Applications Documentation – Explore documentation across Oracle Cloud applications.

Recommendation: Check the documentation applicable to your Oracle Cloud HCM release. Do not assume that an object, column, or relationship documented for one release is necessarily identical in another.

How Correct Table Selection Improves Oracle HCM Reporting

Selecting the appropriate Oracle HCM tables and views is important for building reliable reports.

Different reporting requirements may require different data structures, effective-date conditions, translation handling, and organizational filters.

For example:

  • Employee reporting may require date-effective information.
  • Job reporting may require both base attributes and translated descriptions.
  • Payroll reporting may require attention to organizational context.
  • Historical reporting may require records from multiple effective periods.
  • Flexfield reporting may require appropriate flexfield views.

When teams address these considerations during report development, they can reduce common data retrieval issues and make reporting logic easier to validate and maintain.

For organizations developing or modernizing HCM reporting environments, DataTerrain's Oracle HCM Analytics solutions provide a relevant starting point for exploring HCM analytics capabilities.

How DataTerrain Supports Oracle HCM Reporting and Migration

Oracle HCM reporting requirements can become more complex when organizations work with multiple data structures, legacy reporting assets, and different analytics platforms.

Understanding table relationships, effective dates, translations, and reporting requirements is important when developing or converting reports.

DataTerrain supports organizations through its BI and data modernization services, including:

  • Oracle HCM Analytics – Explore Oracle HCM analytics capabilities.
  • BI Reports Conversion Services – Explore reporting conversion and modernization services.
  • Migration Solutions – Learn about DataTerrain's migration services.

Contact DataTerrain to discuss your BI reporting and migration requirements.

Key Takeaways

  • _B tables: Commonly contain base information and structural attributes.
  • _TL tables: Store language-dependent information associated with business objects.
  • _VL views: Commonly provide language-dependent views of related information.
  • _F tables: Contain date-effective records that require appropriate date conditions.
  • _ALL tables: Commonly contain records across multiple organizational contexts.
  • _X views: May expose currently effective records, depending on the specific object definition.
  • SQL validation matters: Correct joins, language conditions, date filters, and security context are essential for accurate reporting.

Conclusion

Understanding Oracle HCM Fusion table suffixes helps developers identify common database object patterns and make better decisions when building HCM reports.

From base tables and translation tables to date-effective tables and language-dependent views, each object may require different query conditions and reporting considerations. Applying the correct joins, effective-date filters, language conditions, and organizational context helps improve the reliability of retrieved information.

For organizations working with complex Oracle HCM reporting environments, understanding these structures is also an important part of report development, maintenance, and modernization.

Explore DataTerrain's Oracle HCM Analytics solutions and BI Reports Conversion Services to learn more about its analytics and reporting capabilities.

Have questions about your Oracle HCM reporting environment? Contact DataTerrain to discuss your requirements.

Frequently Asked Questions

1. What are table suffixes in Oracle HCM Fusion?
Oracle HCM table suffixes are naming conventions that indicate the purpose or structure of database objects. For example, _B commonly identifies base tables, _TL translation tables, _F date-effective tables, and _VL language-dependent views.
2. What is the difference between _B and _TL tables in Oracle HCM?
_B tables store core business information and structural attributes, while _TL tables store language-dependent descriptions. You can join them to retrieve both base information and translated values.
3. What does _F mean in Oracle HCM tables?
The _F suffix commonly identifies date-effective tables that maintain records across different time periods. Queries must use appropriate effective-date conditions to retrieve the correct current or historical records.
4. What is the difference between _VL and _TL tables?
_TL tables store translated information for different languages, whereas _VL views commonly combine base and translation information to simplify access to descriptive data.
5. How do I identify the correct Oracle HCM table for reporting?
Identify the information your report requires, then review the relevant table or view definitions, columns, relationships, effective dates, and security requirements. Use suffixes as guidance and validate the object against Oracle's official documentation.

Related Resources

Explore these DataTerrain resources for more information about Oracle HCM analytics, reporting, and migration:

  • Oracle HCM Analytics – Explore analytics solutions for Oracle HCM environments.
  • Oracle Fast Formulas – Learn about Oracle HCM Fast Formulas and their role in HCM processes.
  • BI Reports Conversion Services – Explore BI reporting conversion and modernization.
  • Migration Solutions – Discover DataTerrain's data and BI migration services.
  • DataTerrain Homepage – Explore DataTerrain's analytics, BI, and data modernization services.

Official Oracle reference: Oracle Fusion Cloud HCM Tables and Views.

Categories
  • All
  • BI Insights Hub
  • Data Analytics
  • ETL Tools
  • Oracle HCM Insights
  • Legacy Reports conversion
  • AI and ML Hub
Customer Stories
  • All
  • Data Analytics
  • Reports conversion
  • Jaspersoft
  • Oracle HCM
Recent posts
  • suffixes-help-identify-the-purpose-of-oracle-hcm-tables
    Oracle HCM Fusion Table Suffixes Explained....
  • oracle fusion hr
    Migrating to Oracle Fusion Cloud ERP: A....
  • Oracle HCM tables and views
    Oracle HCM Tables and Views: A Complete...
Connect with Us
  • About
  • Careers
  • Privacy Policy
  • Terms and condtions
Sources
  • Customer stories
  • Blogs
  • Tools
  • News
  • Videos
  • Events
Services
  • Reports Conversion
  • ETL Solutions
  • Migration Solutions
  • Data Lake
  • Legacy Scripts
  • Oracle HCM Analytics
  • BI Products
  • AI ML Consulting
  • Data Analytics
Get in touch
  • connect@dataterrain.com
  • +1 650-701-1100

Subscribe to newsletter

Enter your email address for receiving valuable newsletters.

logo

© 2026 Copyright by DataTerrain Inc.

  • twitter