Search Results pqh_transaction_templates_pk




Overview
Explore in AI Chat

Overview

The HR.PQH_TRANSACTION_TEMPLATES table is a core data object within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically for the Human Resources (HR) module. Its primary function is to maintain the association between specific business transactions and the approval templates applied to them. As documented, it "keeps the reference of each template applied to a transaction. Based on this information approvers are determined." This table is fundamental to the approval workflow engine, enabling the system to identify and route transactions, such as position or budget changes, through the appropriate approval hierarchy based on the linked template. Its status is VALID, and it resides in the APPS_TS_TX_DATA tablespace.

Key Information Stored

The table stores the critical linkage between a transaction instance, its category, and the governing approval template. Key columns include:

The unique key constraint PQH_TRANSACTION_TEMPLATE_UK ensures that a given combination of Template, Transaction Category, and specific Transaction ID is not duplicated.

Common Use Cases and Queries

This table is central to troubleshooting approval routing, auditing template usage, and generating reports on transaction approvals. A common scenario involves identifying which template is governing a specific transaction to understand its approval path.

Sample Query 1: Find the active template for a specific transaction.
This query is essential for support and analysis, filtering by the transaction's category and ID.

SELECT template_id, enable_flag
FROM hr.pqh_transaction_templates
WHERE transaction_category_id = &category_id
  AND transaction_id = &specific_transaction_id
  AND enable_flag = 'Y';

Sample Query 2: List all transactions using a particular template.
Useful for impact analysis before modifying or retiring an approval template.

SELECT transaction_category_id, transaction_id, creation_date
FROM hr.pqh_transaction_templates
WHERE template_id = &template_id
ORDER BY creation_date DESC;

Sample Query 3: Standard data extraction.
The foundational query for most reporting needs, as provided in the ETRM documentation.

SELECT transaction_template_id,
       enable_flag,
       template_id,
       transaction_id,
       transaction_category_id,
       object_version_number,
       last_update_date,
       last_updated_by,
       last_update_login,
       created_by,
       creation_date
FROM hr.pqh_transaction_templates;

Related Objects

Based on the metadata and standard EBS design patterns, this table interacts with several key objects:

  • Primary & Unique Keys: It is enforced by the PQH_TRANSACTION_TEMPLATES_PK (on TRANSACTION_TEMPLATE_ID) and the PQH_TRANSACTION_TEMPLATE_UK (on TEMPLATE_ID, TRANSACTION_CATEGORY_ID, TRANSACTION_ID) indexes.
  • Foreign Key Relationships: The non-unique index PQH_TRANSACTION_TEMPLATE_FK1 on TRANSACTION_CATEGORY_ID suggests a foreign key dependency on a transaction category lookup table (e.g., PQH_TRANSACTION_CATEGORIES). The TEMPLATE_ID column typically references a master template table (e.g., PQH_TEMPLATES).
  • Dependent Objects: While the provided dependency list is truncated, this table is a critical reference point. It is heavily referenced by the approval workflow engine APIs and underlying business logic to determine approvers. Views or public APIs for transaction approvals likely join to this table.