Results for “pqh_table_route_vl”

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

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

• ROW_ID — ROWID of the underlying PQH_TABLE_ROUTE row.
• TABLE_ROUTE_ID — primary identifier of the route definition.
• SHADOW_TABLE_ROUTE_ID — the identifier of the associated shadow table route; the object of the user's search.
• FROM_CLAUSE / TABLE_ALIAS / WHERE_CLAUSE — the SQL fragments used to build dynamic queries.
• DISPLAY_NAME — localized, translated name from the TL table.
• DISPLAY_ORDER — ordering of the route in user-facing lists.
• HIDE_TABLE_FOR_VIEW_FLAG, SELECT_ALLOWED_FLAG, MAP_REQUIRED_FLAG — behavioral flags controlling visibility, query permission, and mapping requirements.
• OBJECT_VERSION_NUMBER, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard audit and optimistic-locking 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.