Search Results pqh_table_route_vl




Overview
Explore in AI Chat

Overview

PQH_TABLE_ROUTE_VL is a translation-enabled (VL) view owned by the APPS schema in Oracle E-Business Suite, residing in the Public Sector HR (PQH) product family. It exposes "Table Route" configuration data that governs how the PQH framework dynamically constructs SQL statements against mapped/shadow tables. In the EBS architecture, a "table route" defines the FROM clause, table alias, and WHERE clause fragments used when the application assembles queries against a logical (shadow) table rather than the physical base table. The "_VL" suffix indicates that the view joins a base table with its translation table to surface the language-appropriate display name for the current session.

The view is a reporting and integration access point: rather than reading the underlying base and translation tables separately, external reports, concurrent programs, and integrations query PQH_TABLE_ROUTE_VL to obtain both the technical routing metadata and the user-facing, localized display name in a single row. This is relevant to the searched term "shadow_table_route_id," which is exposed directly as a column and links a route definition to its shadow-table counterpart.

Underlying Base Objects

Per documented ETRM metadata, PQH_TABLE_ROUTE_VL is defined over two base objects, both referenced through synonyms:

  • PQH_TABLE_ROUTE (aliased B) — the base table holding the technical route definition, row identity, audit columns, and flags.
  • PQH_TABLE_ROUTE_TL (aliased T) — the translation table holding the language-specific DISPLAY_NAME.

The join condition is B.TABLE_ROUTE_ID = T.TABLE_ROUTE_ID AND T.LANGUAGE = USERENV('LANG'), ensuring each row is presented in the language of the current session. Because it is a VL view, it inherits the multi-language behavior of the TL table while exposing the ROWID of the base table as ROW_ID.

Key Columns

Common Use Cases and Queries

Typical uses include reporting the configured routes for a given shadow table, resolving the display name for administration screens, and validating the FROM/WHERE fragments used in dynamic SQL generation.

SELECT table_route_id,
       shadow_table_route_id,
       display_name,
       from_clause,
       table_alias,
       where_clause
FROM   apps.pqh_table_route_vl
WHERE  shadow_table_route_id IS NOT NULL
ORDER  BY display_order;

To inspect flags for a specific route:

SELECT table_route_id, display_name,
       hide_table_for_view_flag,
       select_allowed_flag,
       map_required_flag
FROM   apps.pqh_table_route_vl
WHERE  table_route_id = :p_route_id;

Because the view enforces LANGUAGE = USERENV('LANG'), results reflect the caller's session language; queries requiring all translations should target PQH_TABLE_ROUTE_TL directly.