Results for “hr_all_positions_f_tl”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

HR_ALL_POSITIONS_F_TL is the translation (TL) table for the HR_ALL_POSITIONS_F base table in the Oracle E-Business Suite Human Resources (PER) module. In Oracle EBS, every translatable entity is stored using a pair of tables: a base table (suffix _F) that holds language-independent attributes and a translation table (suffix _TL) that stores the language-dependent, user-facing descriptive text. HR_ALL_POSITIONS_F_TL holds the translated name of each position defined in the enterprise, keyed by position identifier and language.

From a Data Vault modeling perspective, the heuristic classification mined from the foreign-key structure identifies this object as standalone. This is a natural consequence of the TL pattern: the table's primary key is fully self-contained (POSITION_ID plus LANGUAGE) and it does not participate in the FK relationships that link HR_ALL_POSITIONS_F to the wider PER schema. As a modeling suggestion rather than a documented fact, a Data Vault practitioner would typically represent the translatable position description as a satellite hanging off the position hub (HR_ALL_POSITIONS_F, keyed by POSITION_ID), with LANGUAGE acting as a driving key or a descriptive attribute of the multi-active satellite. The standalone heuristic classification reflects only the absence of enforceable foreign keys on this table, not a true independence from the parent position entity.

Key Information Stored

The documented physical schema in ETRM 12.2.2 lists nine columns. The most significant are:

• POSITION_ID — Surrogate numeric identifier of the position. Together with LANGUAGE this forms the composite primary key HR_ALL_POSITIONS_F_TL_PK, which is also the only documented unique index and therefore the sole business-key candidate.
• LANGUAGE — The language code of the translation row (for example US for American English). Participates in the primary key and enables multiple language rows per position.
• SOURCE_LANG — The language of the source row from which the translation was created; used by the translation framework to track the origin language.
• NAME — The translated position name, the principal descriptive attribute of this table and the value most commonly exposed in reports and list-of-values queries.
• CREATED_BY, CREATION_DATE — Standard WHO audit columns recording the user and timestamp of row creation.
• LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO audit columns recording the last modification user, timestamp, and login session.

Common Use Cases and Queries

Because positions are referenced across recruiting, payroll, and reporting, retrieving the translated name is a frequent requirement. A typical join retrieves the current-session translation of a position:

• Joining HR_ALL_POSITIONS_F to HR_ALL_POSITIONS_F_TL on POSITION_ID to obtain NAME for display, filtered by LANGUAGE.
• Reporting positions in multiple languages by grouping on POSITION_ID and pivoting LANGUAGE.
• Data-conversion or interface validation to confirm that every position has a translation row in the required language.

A representative SQL pattern is:

SELECT p.position_id, t.name FROM hr.hr_all_positions_f p, hr.hr_all_positions_f_tl t WHERE p.position_id = t.position_id AND t.language = USERENV('LANG') AND SYSDATE BETWEEN p.effective_start_date AND p.effective_end_date;

Note that while the TL table itself carries no date-effective columns, the base table HR_ALL_POSITIONS_F is date-tracked, so reporting queries must also constrain the base table's effective dates to avoid duplicate position versions.

Related Objects

• HR_ALL_POSITIONS_F — The base table holding date-effective, language-independent position attributes; joined on POSITION_ID.
• HR_ALL_POSITIONS_F_TL_PK — The composite unique index on (POSITION_ID, LANGUAGE) enforcing one translation per position per language.
• HR_ALL_POSITION_DEFINITIONS — Stores position definition hierarchy and parent-child relationships.
• PER_ALL_POSITIONS — Date-tracked view over position data commonly used in Fast Formulas and reports.
• HR_ALL_ORGANIZATION_UNITS_F_TL — Parallel translation table for organizations, useful when reporting position within organization contexts.
• FND_LANGUAGES — Reference table for valid LANGUAGE and SOURCE_LANG values.

Direct DML against HR_ALL_POSITIONS_F_TL should be avoided; the supported route for maintaining position names is the Position form or the corresponding PER APIs, which keep the base and translation tables synchronized.