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 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:
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.
| 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 |
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.
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:
Reporting consideration: Querying a base table alone may not provide all the readable descriptions required in a report.
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:
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.
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:
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.
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:
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.
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:
Do not assume that all _V views follow identical structures. Review their definitions before using them.
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:
Do not assume every _X view behaves identically.
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:
Check the specific object's definition to understand the information it exposes and how it relates to the underlying business object.
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.
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 attributes | Relevant _B object |
| Retrieve translated descriptions | Related _TL object or suitable _VL view |
| Retrieve current-effective information | Appropriate current-effective view or date-filtered table |
| Retrieve employee history | Relevant _F object |
| Analyze multiple organizational records | Relevant _ALL object and organization context |
| Retrieve database-view information | Relevant _V object |
| Retrieve audit history | Relevant audit-related objects |
| Retrieve flexfield information | Appropriate _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.
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.
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:
Reporting benefit: Applying the appropriate language condition helps prevent multiple translation records from appearing for the same business object.
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:
Reporting benefit: Applying the appropriate effective-date condition helps prevent historical or future-effective records from being included unintentionally.
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:
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.
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 condition | Multiple language records may appear | Apply the required language condition |
| Querying _F without effective-date logic | Historical or future records may be included | Apply date conditions appropriate to the report |
| Using _B without related descriptive information | Reports may show identifiers instead of readable descriptions | Investigate related translation objects or suitable views |
| Assuming _VL handles every filter | Required security or business conditions may be overlooked | Review the specific view definition |
| Ignoring organization context in _ALL objects | Records outside the intended reporting scope may be returned | Validate organization filters and security |
| Assuming all suffixes have identical behavior | Incorrect table selection and query logic | Verify each database object independently |
| Selecting unnecessary columns and records | More data may be processed than required | Retrieve only the columns and records needed |
If an Oracle HCM report returns unexpected results, review the following:
These checks help identify whether the issue originates from table selection, joins, date conditions, or the reporting environment.
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:
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.
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:
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.
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:
Contact DataTerrain to discuss your BI reporting and migration requirements.
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.
Explore these DataTerrain resources for more information about Oracle HCM analytics, reporting, and migration:
Official Oracle reference: Oracle Fusion Cloud HCM Tables and Views.