Search Results pqh_table_route_tl_pk




Overview
Explore in AI Chat

Overview

HR.PQH_TABLE_ROUTE_TL is the translation (TL) table for the routing definitions used by Oracle E-Business Suite's Public Sector/HR table routing and approval-routing framework. In Oracle EBS 12.1.1 and 12.2.2, PQH objects support configurable routing of transactions and documents through approval hierarchies, and PQH_TABLE_ROUTE_TL stores the language-specific display text associated with each route. The object resides in the HR schema and is registered under FND Design Data as PQH.PQH_TABLE_ROUTE_TL, with status VALID.

The table follows the standard EBS multilingual pattern: a single-language base row is represented by the language-independent parent table, and one translated row per installed language is stored here. The DISPLAY_NAME column carries the translated name of the route, keyed by LANGUAGE with SOURCE_LANG recording the language origin for the Translation Synchronization process.

From a Data Vault modeling perspective, the metadata classifies this object as standalone. Following that heuristic, PQH_TABLE_ROUTE_TL is best treated as a satellite — a descriptive, dependent structure that qualifies the base route entity through its language and display attributes — rather than a hub or link. This is appropriate for a TL table, which by design holds descriptive rows rather than independent business keys.

Key Information Stored

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

  • TABLE_ROUTE_ID (NUMBER 15) — the surrogate identifier linking the translated row to its base route definition; part of the primary key.
  • LANGUAGE — the language in effect for the translated display text; the second component of the primary key.
  • DISPLAY_NAME (VARCHAR2 80) — the translated route name presented to the user in the selected language.
  • SOURCE_LANG — the language source, used by the translation synchronization API to determine which language acts as the master.
  • ZD_EDITION_NAME — the edition component, supporting Edition-Based Redefinition in 12.2.2.
  • LAST_UPDATE_DATE, CREATION_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard WHO audit columns tracking insert and update context.

The primary key is PQH_TABLE_ROUTE_TL_PK, defined on (TABLE_ROUTE_ID, LANGUAGE). In the 12.2.2 schema the unique index is documented as (TABLE_ROUTE_ID, LANGUAGE, ZD_EDITION_NAME), reflecting the addition of the edition column. The combination of TABLE_ROUTE_ID and LANGUAGE is the business-key candidate that guarantees one translated row per route per language.

Common Use Cases and Queries

The most frequent use of this table is retrieving a translated route name for display in forms, OAF pages, or concurrent program reports. A typical join pairs it with its base table on TABLE_ROUTE_ID and filters by the user's session language:

SELECT b.TABLE_ROUTE_ID, t.DISPLAY_NAME
FROM   HR.PQH_TABLE_ROUTE_TL t
WHERE  t.LANGUAGE = USERENV('LANG');

Translation administrators query the table to audit coverage: rows missing a DISPLAY_NAME for a given LANGUAGE indicate untranslated routes. The Oracle Translation Synchronization (FND) process reads SOURCE_LANG to identify stale rows. Reporting against WHO columns supports audit trails of when a translated name was last changed and by which user. Multi-language reporting joins the TL table to expose the same route under several LANGUAGE codes side by side.

Related Objects

The dependency metadata shows this object does not reference any database object itself, but it is referenced by the following:

  • APPS.PQH_TABLE_ROUTE_TL — the APPS synonym used by application code.
  • APPS.PQH_TABLE_ROUTE_TL041457_WHO — the seeded WHO (Standard Who) trigger that populates the audit columns on insert and update.
  • PUBLIC.PQH_TABLE_ROUTE_TL — the PUBLIC synonym exposing the table beyond the APPS schema.

Logically, the parent routing entity and the associated base route table join on TABLE_ROUTE_ID, while LANGUAGE drives the link to FND_LANGUAGES. Together these objects form the metadata layer that drives PQH routing and approval configuration in Oracle EBS 12.1.1 and 12.2.2.