Search Results shadow_table_route_id




Overview
Explore in AI Chat

Overview

The HR.PQH_TABLE_ROUTE table is a core metadata repository within Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically for the Oracle HRMS (Human Resources) module. It functions as a central registry that defines how to query and access various application tables, primarily to support complex data operations in the Advanced Benefits and Plan Copy features. The table stores SQL fragments and configuration flags that enable the dynamic construction of queries, allowing for flexible data retrieval, mapping, and copying of benefit plan components across different contexts. Its self-referencing foreign key on SHADOW_TABLE_ROUTE_ID indicates a hierarchical or versioning relationship between different table route definitions.

Key Information Stored

The table's columns are categorized into identifiers, SQL definitions, configuration flags, and standard audit columns. The primary identifier is TABLE_ROUTE_ID. A critical column is SHADOW_TABLE_ROUTE_ID, which creates a recursive relationship pointing to another record in the same table, likely used to link a primary table definition to a secondary or "shadow" version for specialized processing. The SQL construction elements include DISPLAY_NAME (a user-friendly label), FROM_CLAUSE (the core table or join for the query), TABLE_ALIAS (a short name used in queries), and WHERE_CLAUSE (an optional filter). Operational behavior is controlled by flags: MAP_REQUIRED_FLAG, SELECT_ALLOWED_FLAG, and HIDE_TABLE_FOR_VIEW_FLAG, which govern visibility and functionality during plan copy operations. DISPLAY_ORDER dictates the sequence of table presentation, and ZD_EDITION_NAME supports Edition-Based Redefinition (EBR).

Common Use Cases and Queries

The primary use case is the dynamic generation of SQL for copying and configuring benefit plans. For instance, when a user initiates a plan copy, the application queries PQH_TABLE_ROUTE to determine which source tables are involved, how to join them, and what filters to apply. A common query retrieves all configured table routes for a specific operation, ordered for display. To investigate the recursive shadow relationship, a self-join is often performed. The following query pattern is fundamental for examining the table's metadata and relationships:

  • SELECT t1.TABLE_ALIAS, t1.DISPLAY_NAME, t1.FROM_CLAUSE, t1.SHADOW_TABLE_ROUTE_ID, t2.TABLE_ALIAS AS SHADOW_ALIAS FROM HR.PQH_TABLE_ROUTE t1 LEFT JOIN HR.PQH_TABLE_ROUTE t2 ON t1.SHADOW_TABLE_ROUTE_ID = t2.TABLE_ROUTE_ID WHERE t1.SELECT_ALLOWED_FLAG = 'Y' ORDER BY t1.DISPLAY_ORDER;

This is essential for debugging plan copy issues or understanding the data model for custom reporting on benefit structures.

Related Objects

As a central metadata table, PQH_TABLE_ROUTE has extensive relationships within the PQH (Advanced Benefits) schema. It is referenced by numerous key transactional and configuration tables, as documented in the provided dependency information. The primary key (TABLE_ROUTE_ID) is foreign keyed by the following objects:

These relationships demonstrate its role in defining attributes, copy functions, user preferences, process logging, transaction categories, and category attributes, forming the backbone of the Advanced Benefits data manipulation framework.