Results for “pqh_table_route”

50+ results




Overview
Explore in AI Chat

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

Overview

PQH_TABLE_ROUTE is a configuration and metadata table owned by the HR schema in Oracle E-Business Suite, delivered under the PQH (Public Sector HR) product family. Its documented purpose is to store "table information for dynamic queries." In practice, the table acts as a registry of routable table definitions — each row describes a table or view that can be dynamically referenced, joined, or consolidated by the PQH transaction and copy-entity framework. Columns such as FROM_CLAUSE, WHERE_CLAUSE, and TABLE_ALIAS confirm that the object drives runtime SQL generation rather than holding transactional business data.

Because the table is self-referencing (SHADOW_TABLE_ROUTE_ID points back to PQH_TABLE_ROUTE) and is referenced by numerous dependent tables, the heuristic Data Vault classification is hub. This is a modeling suggestion: PQH_TABLE_ROUTE functions as a central key-bearing entity whose surrogate key is broadcast to satellites and dependent structures, with the self-referencing column representing a hierarchical or shadow relationship rather than an independent link.

Key Information Stored

The table exposes 17 documented columns. The most significant are:

  • TABLE_ROUTE_ID — surrogate primary key defined by PQH_TABLE_ROUTE_PK, combined with ZD_EDITION_NAME for editioning.
  • TABLE_ALIAS — the business-key candidate enforced by unique index PQH_TABLE_ROUTE_UK1; provides the SQL alias used when the route is injected into dynamic queries.
  • DISPLAY_NAME — enforces the second unique key, PQH_TABLE_ROUTE_UK2; supplies the user-facing label for the route.
  • SHADOW_TABLE_ROUTE_ID — self-referencing foreign key linking a route to a shadow counterpart, supporting alternate or mirrored query definitions.
  • FROM_CLAUSE — the FROM fragment used to build dynamic SQL for the route.
  • WHERE_CLAUSE — the predicate fragment appended to dynamic SQL.
  • MAP_REQUIRED_FLAG — indicates whether attribute mapping must be performed for the route.
  • SELECT_ALLOWED_FLAG — controls whether the route may be selected in query-building interfaces.
  • HIDE_TABLE_FOR_VIEW_FLAG — suppresses the route from view-based presentation.
  • DISPLAY_ORDER — governs the ordering of routes in lists and pickers.
  • OBJECT_VERSION_NUMBER — optimistic locking column used by the OA Framework.
  • ZD_EDITION_NAME — the editioning column enabling online patching, part of both unique indexes.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns.

The distinction is important: TABLE_ROUTE_ID is the surrogate key, while TABLE_ALIAS and DISPLAY_NAME are the documented business-key candidates. All three appear in unique indexes alongside ZD_EDITION_NAME.

Common Use Cases and Queries

Typical scenarios include auditing which tables are exposed to the dynamic query engine, verifying alias and display-name uniqueness, and tracing how a route is consumed by transaction categories, attributes, and copy-entity processing.

Listing active routes with display ordering:

  • SELECT TABLE_ROUTE_ID, TABLE_ALIAS, DISPLAY_NAME, DISPLAY_ORDER FROM HR.PQH_TABLE_ROUTE WHERE SELECT_ALLOWED_FLAG = 'Y' ORDER BY DISPLAY_ORDER;

Resolving self-referencing shadow routes:

  • SELECT r.DISPLAY_NAME, s.DISPLAY_NAME AS shadow_name FROM HR.PQH_TABLE_ROUTE r, HR.PQH_TABLE_ROUTE s WHERE r.SHADOW_TABLE_ROUTE_ID = s.TABLE_ROUTE_ID;

Finding routes drawn into transaction category processing:

Reporting use cases center on impact analysis before changing a WHERE_CLAUSE or FROM_CLAUSE, since dependent transaction and copy-entity configurations resolve through this hub.

Related Objects

The following represent the most significant dependents and relationships:

These relationships confirm that PQH_TABLE_ROUTE is a central registry rather than a leaf table; any modification to its rows can ripple into attribute definitions, transaction category processing, and copy-entity execution and logging.