Search Results pqh_transaction_categories

Overview

The PQH_TRANSACTION_CATEGORIES table is a core setup table within the Oracle E-Business Suite Public Sector HR (PQH) module. It functions as the central repository for defining and categorizing different types of HR transactions. Its primary role is to enable the selective routing and workflow processing of transactions by grouping them into logical categories. This categorization is fundamental to the configuration of approval hierarchies, notification rules, and template assignments, ensuring that transactions follow the correct organizational and business process paths in both Oracle EBS 12.1.1 and 12.2.2.

Key Information Stored

The table stores configuration data that defines each transaction category. The primary identifier is the TRANSACTION_CATEGORY_ID (primary key). Each category is uniquely identified within a business group by the combination of SHORT_NAME and BUSINESS_GROUP_ID (unique key). Critical setup columns include MASTER_TABLE_ROUTE_ID and CONSOLIDATED_TABLE_ROUTE_ID, which are foreign keys to the PQH_TABLE_ROUTE table. These columns define the specific routing rules—master and consolidated—that apply to transactions within this category, directly controlling the approval workflow. Other typical columns (implied by standard design patterns) would include NAME, DESCRIPTION, and ENABLED_FLAG.

Common Use Cases and Queries

This table is primarily queried for setup, audit, and troubleshooting of transaction routing. Common scenarios include identifying all transaction categories defined for a business group, or finding the routing rules applied to a specific category. For example, to list all categories with their assigned routing, a query might join to PQH_TABLE_ROUTE:

• SELECT ptc.short_name, ptc.name, ptr_master.name master_route, ptr_consolidated.name consolidated_route FROM pqh_transaction_categories ptc, pqh_table_route ptr_master, pqh_table_route ptr_consolidated WHERE ptc.master_table_route_id = ptr_master.table_route_id(+) AND ptc.consolidated_table_route_id = ptr_consolidated.table_route_id(+) AND ptc.business_group_id = &business_group_id ORDER BY ptc.short_name;

Another key use case is diagnosing workflow issues by verifying the transaction category assigned to a specific position transaction or worksheet, which involves joining to tables like PQH_POSITION_TRANSACTIONS or PQH_WORKSHEETS.

Related Objects

As indicated by the foreign key relationships, PQH_TRANSACTION_CATEGORIES is a pivotal parent table referenced by numerous other PQH objects. Key child tables include:

• PQH_TXN_CATEGORY_ATTRIBUTES: Stores additional attributes specific to a category.
• PQH_ROUTING_CATEGORIES & PQH_ROUTING_HISTORY: Directly support the routing and approval history functionality.
• PQH_TEMPLATES & PQH_TRANSACTION_TEMPLATES: Link document and transaction templates to categories.
• PQH_POSITION_TRANSACTIONS & PQH_WORKSHEETS: Use the category to determine workflow behavior (via WF_TRANSACTION_CATEGORY_ID).
• PQH_COPY_ENTITY_TXNS, PQH_FYI_NOTIFY, PQH_ROLE_TEMPLATES: Further extend the category's use in copying, notification, and role assignment processes.

The table's relationship with PQH_TABLE_ROUTE (for MASTER_TABLE_ROUTE_ID and CONSOLIDATED_TABLE_ROUTE_ID) is critical for defining the actual approval path.