Search Results per_empdir_locations_tl




Overview
Explore in AI Chat

Overview

PER_EMPDIR_LOCATIONS_TL is a translation (TL) table in the HR schema of Oracle E-Business Suite, belonging to the PER — Human Resources product family. It stores language-specific, translatable text for employee directory location records, allowing the same underlying location entry to carry a distinct description for each installed language. In a multilingual EBS deployment, the base table holds language-neutral and installation-level attributes, while the _TL table carries the user-facing translated fields keyed by LANGUAGE.

The ETRM metadata classifies this object heuristically as standalone under the Data Vault modeling scheme. This suggests the table be treated as a reference or descriptive satellite-like structure rather than a transactional hub or link, reflecting its role as a lookup of directory location descriptions rather than a high-volume fact store.

It is important to note that the object is documented as a _TL translation table but contains no _B base table listed in the relationship data. The only outbound foreign keys reference HZ_ORIG_SYSTEMS_B (via ORIG_SYSTEM_ID) and JTF_FM_PARTITION_X_REQUEST (via PARTITION_ID). The absence of a documented base-table FK indicates the translation rows are keyed primarily by the business composite (ORIG_SYSTEM_ID, ORIG_SYSTEM, LANGUAGE) defined by the table's unique index.

Key Information Stored

The most significant columns documented for this 16-column table are:

  • ORIG_SYSTEM_ID — Surrogate identifier for the originating system record; participates in the unique business key and joins to HZ_ORIG_SYSTEMS_B.
  • ORIG_SYSTEM — Short name of the source system that owns the directory location entry; also part of the unique business key.
  • LANGUAGE — The installed language for which the translated text applies; the third component of the unique business key.
  • SOURCE_LANG — The language from which the translation was originally derived (the source language of the seed text).
  • LOCATION_CODE — Code identifying the directory location (business identifier).
  • DESCRIPTION — The translated, user-facing description of the location; this is the core translatable payload.
  • PARTITION_ID — Partition reference, joined to JTF_FM_PARTITION_X_REQUEST, supporting partition-aware processing.
  • OBJECT_VERSION_NUMBER — Optimistic concurrency control token, incremented on each update.
  • LAST_UPDATE_DATE / LAST_UPDATE_BY — Standard audit columns tracking the most recent modification.
  • CREATED_BY / CREATION_DATE — Standard audit columns for row creation.
  • REQUEST_ID / PROGRAM_APPLICATION_ID / PROGRAM_ID / PROGRAM_UPDATE_DATE — Concurrent manager / whose-column style columns recording the program that created or last updated the row.

The documented unique index PER_EMPDIR_LOCATIONS_TL_PK spans (ORIG_SYSTEM_ID, ORIG_SYSTEM, LANGUAGE), establishing these three columns as the business-key candidate for the translation, distinct from the surrogate keys implied elsewhere in the schema.

Common Use Cases and Queries

Typical reporting queries join the translation to language-neutral directory data and filter to a single language:

SELECT LOCATION_CODE, DESCRIPTION
FROM   PER_EMPDIR_LOCATIONS_TL
WHERE  LANGUAGE = USERENV('LANG');

To retrieve all available translations for a given source entry:

SELECT LANGUAGE, SOURCE_LANG, DESCRIPTION
FROM   PER_EMPDIR_LOCATIONS_TL
WHERE  ORIG_SYSTEM_ID = :p_id AND ORIG_SYSTEM = :p_system;

Joining to the originating system definition supports source attribution reporting:

SELECT t.LOCATION_CODE, t.DESCRIPTION, s.*
FROM   PER_EMPDIR_LOCATIONS_TL t,
       HZ_ORIG_SYSTEMS_B s
WHERE  t.ORIG_SYSTEM_ID = s.ORIG_SYSTEM_ID;

Common scenarios include localized employee-directory displays, translation completeness audits (comparing present languages against installed languages), and partition-aware bulk processing keyed on PARTITION_ID.

Related Objects

  • HZ_ORIG_SYSTEMS_B — Referenced via ORIG_SYSTEM_ID; source-system registry for the directory location entries.
  • JTF_FM_PARTITION_X_REQUEST — Referenced via PARTITION_ID; partition definition used for the translation records.
  • FND_LANGUAGES — Provides the valid installed-language domain for the LANGUAGE and SOURCE_LANG columns.
  • PER_EMPDIR_LOCATIONS (base equivalent) — The language-neutral directory location object supplying the non-translated attributes that the _TL rows describe.
  • FND_USER / FND_APPLICATION — Resolve CREATED_BY, LAST_UPDATE_BY, PROGRAM_APPLICATION_ID, and PROGRAM_ID audit values.

Because the ETRM relationship data documents only the two outbound foreign keys above, additional references should be validated against the actual 12.1.1 / 12.2.2 database catalog before use in production queries.