Search Results pqh_table_route

Overview

The PQH_TABLE_ROUTE table is a core metadata repository within the Oracle E-Business Suite Public Sector Human Resources (PQH) module. It functions as a central registry for defining and managing table information used to construct dynamic queries. Its primary role is to enable flexible data access and manipulation by providing a configurable mapping between logical table aliases, their physical database names, and descriptive display information. This abstraction layer is critical for supporting complex HR processes, such as transaction consolidation and entity copying, where runtime SQL generation depends on configurable data sources.

Key Information Stored

The table's structure is designed to uniquely identify and describe database tables used in PQH processes. Based on the provided metadata, key columns include TABLE_ROUTE_ID (the primary surrogate key), TABLE_ALIAS (a unique logical identifier for the table), and DISPLAY_NAME (a unique descriptive name for user interfaces). A critical column is SHADOW_TABLE_ROUTE_ID, which establishes a recursive foreign key relationship to another record within the same table. This relationship is fundamental for distinguishing between master and shadow (or staging) tables, a common pattern in transaction processing where data is staged before final posting. The table's unique constraints on TABLE_ALIAS and DISPLAY_NAME ensure the integrity of these logical identifiers.

Common Use Cases and Queries

This table is predominantly accessed by the application's engine to resolve logical table names to physical ones during dynamic SQL execution. A common use case is during the configuration of transaction categories or copy entity functions, where the system needs to determine which master and shadow tables are involved in a specific HR process. A typical query would retrieve the physical table name for a given logical alias to build a query. For example, to find the master and shadow table pairing for a specific transaction route, one might use a self-join:

• SELECT m.TABLE_ALIAS AS MASTER_ALIAS, s.TABLE_ALIAS AS SHADOW_ALIAS FROM PQH_TABLE_ROUTE m, PQH_TABLE_ROUTE s WHERE m.SHADOW_TABLE_ROUTE_ID = s.TABLE_ROUTE_ID AND m.TABLE_ALIAS = '<ALIAS_NAME>';

Reporting use cases are generally administrative, focused on auditing the configuration of table routes and their relationships within the PQH module.

Related Objects

As indicated by the foreign key relationships, PQH_TABLE_ROUTE is a foundational object referenced by numerous other PQH entities. Key dependent tables include:

• PQH_ATTRIBUTES: References the table route via MASTER_TABLE_ROUTE_ID to define which attributes belong to a master entity.
• PQH_TRANSACTION_CATEGORIES: Uses both MASTER_TABLE_ROUTE_ID and CONSOLIDATED_TABLE_ROUTE_ID to link transaction types to specific master and consolidated data tables.
• PQH_TXN_CATEGORY_ATTRIBUTES: References TRANSACTION_TABLE_ROUTE_ID to map attributes to transaction-specific table routes.
• PQH_COPY_ENTITY_FUNCTIONS and PQH_COPY_ENTITY_PREFS: Reference TABLE_ROUTE_ID to configure data copying operations.
• PQH_PROCESS_LOG: Uses TXN_TABLE_ROUTE_ID to log the table route context for process executions.

The recursive foreign key on SHADOW_TABLE_ROUTE_ID also creates a direct relationship between records within the PQH_TABLE_ROUTE table itself, defining master-shadow table hierarchies.