Search Results pqh_table_route_tl




Overview
Explore in AI Chat

Overview

PQH_TABLE_ROUTE_TL is a translated (MLS) detail table within the HR schema, owned by the Public Sector HR (PQH) product. It exists to hold the language-specific, translatable text attributes for the parent PQH_TABLE_ROUTE entity. The "_TL" suffix is the standard Oracle E-Business Suite convention indicating that rows are keyed by LANGUAGE and hold user-facing translated columns such as display names, while the corresponding base table holds language-independent attributes.

The ETRM metadata classifies this object heuristically as standalone within a Data Vault modeling scheme. Taken as a modeling suggestion, this implies the table has no strongly mined foreign key relationships to other tables in the documented FK structure and therefore does not behave as a link table; it is best understood as a satellite-style attribute table hanging off the parent route entity. The absence of outward foreign keys does not preclude an implicit parent relationship to PQH_TABLE_ROUTE on TABLE_ROUTE_ID, which is the conventional design in EBS MLS pairs.

Key Information Stored

The table contains 10 documented columns. The most significant are:

  • TABLE_ROUTE_ID — Surrogate identifier that ties each translated row to its parent route record in the base table. This is the primary correlation column across all languages.
  • LANGUAGE — The language code identifying the translation locale for the row.
  • SOURCE_LANG — The language of the source text from which the translation was derived, used by the MLS translation framework.
  • DISPLAY_NAME — The translated, user-facing label for the route. This is the principal business payload of the table.
  • ZD_EDITION_NAME — Editioning column introduced for the EBS 12.2 online patching (Edition-Based Redefinition) architecture.
  • LAST_UPDATE_DATE, CREATION_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit and "Who" columns populated by the framework on insert and update.

The documented primary key is PQH_TABLE_ROUTE_TL_PK (TABLE_ROUTE_ID, LANGUAGE). The 12.2.2 physical schema notes an additional unique index candidate of (TABLE_ROUTE_ID, LANGUAGE, ZD_EDITION_NAME), which is the editioning-aware form of the same business key. Together, TABLE_ROUTE_ID plus LANGUAGE constitute the business-key candidates that guarantee one translation per locale per route.

Common Use Cases and Queries

Typical usage is to resolve the display label for a table route in a specific session language, and to audit translations across locales.

Resolving a translated display name for a given language:

SELECT t.table_route_id, t.display_name
FROM   pqh_table_route_tl t
WHERE  t.table_route_id = :route_id
AND    t.language = USERENV('LANG');

Reporting all translations for a route, including the source language:

SELECT t.language, t.display_name, t.source_lang
FROM   pqh_table_route_tl t
WHERE  t.table_route_id = :route_id
ORDER BY t.language;

Detecting missing translations by comparing against the set of installed languages is a common data-quality check. Because the table is keyed on LANGUAGE, joins to the base route table must restrict to a single language or aggregate carefully to avoid row multiplication in reporting queries.

Related Objects

  • PQH_TABLE_ROUTE — The base (non-translated) table. Join on TABLE_ROUTE_ID to combine language-independent attributes with the translated DISPLAY_NAME.
  • PQH_TABLE_ROUTE_TL_PK — Primary key constraint/index (TABLE_ROUTE_ID, LANGUAGE) enforcing uniqueness.
  • FND_LANGUAGES — Reference table for valid LANGUAGE and SOURCE_LANG values installed in the instance.
  • FND_TERRITORIES / FND_LANGUAGE — Supporting MLS lookup objects used when resolving locale context.
  • MLS framework views (e.g., _VL / _V variants) — Public Sector HR synonyms or views that present language-specific joins to consumers.

Because ETRM documents this object as standalone, no mined foreign-key targets are listed; dependencies shown here are inferred from the MLS naming convention and the shared TABLE_ROUTE_ID correlation column.