Results for “pqh_transaction_categories”

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

Overview

HR.PQH_TRANSACTION_CATEGORIES is a setup (configuration) table within the Oracle E-Business Suite PQH — Public Sector HR product family. Its documented purpose is to store setup information that allows selective routing of transactions. In practice, each row defines a named transaction category that the routing framework uses to determine how a public-sector HR transaction — such as a position action, establishment change, or entity copy operation — is validated, approved, and dispatched through Oracle Workflow.

The table sits at the center of the PQH routing model. Two of its columns, CONSOLIDATED_TABLE_ROUTE_ID and MASTER_TABLE_ROUTE_ID, are foreign keys into PQH_TABLE_ROUTE, meaning a category carries its own routing definitions for both master and consolidated processing paths. The table also holds workflow customisation attributes, posting behaviour flags, and time-out thresholds, making it the primary control point for how a transaction class behaves end to end.

The heuristic Data Vault classification mined from the foreign-key structure is hub. As a modeling suggestion, this reflects that PQH_TRANSACTION_CATEGORIES functions as a central business-key repository: it owns the identity of each transaction category, and numerous dependent tables reference that identity rather than defining it. In a Data Vault design it would therefore be modelled as a hub, with the referencing tables treated as links or satellites.

Key Information Stored

The documented physical schema contains 27 columns. The most significant are:

• TRANSACTION_CATEGORY_ID — surrogate primary key, defined by the PQH_TRANSACTION_CATEGORIES_PK unique index (together with ZD_EDITION_NAME in 12.2.2).
• SHORT_NAME and BUSINESS_GROUP_ID — the business-key candidates, forming the PQH_TRANSACTION_CATEGORIES_UK unique index. This is the constraint users typically search for as "pqh_transaction_categories_uk"; it enforces uniqueness of the category short name within a business group and edition.
• NAME — the descriptive, user-facing name of the transaction category.
• CONSOLIDATED_TABLE_ROUTE_ID and MASTER_TABLE_ROUTE_ID — foreign keys to PQH_TABLE_ROUTE, defining the routing configuration applied to consolidated and master-level transactions respectively.
• WORKFLOW_ENABLE_FLAG, CUSTOM_WORKFLOW_NAME, and CUSTOM_WF_PROCESS_NAME — control whether Oracle Workflow is invoked and which custom process is used.
• TIMEOUT_DAYS — the elapsed-time threshold applied to the routing or approval cycle.
• FREEZE_STATUS_CD, FUTURE_ACTION_CD, MEMBER_CD, and POST_STYLE_CD — status, timing, membership, and posting-style codes governing transaction handling.
• POST_TXN_FUNCTION — identifies the function invoked when the transaction is posted.
• ROUTE_VALIDATED_TXN_FLAG and PREVENT_APPROVER_SKIP — routing controls determining whether validated transactions are routed and whether approvers may be bypassed.
• ENABLE_FLAG and SETUP_TYPE_CD — availability and setup classification of the category.
• Audit columns: OBJECT_VERSION_NUMBER, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN.

In 12.2.2 the presence of ZD_EDITION_NAME indicates the table participates in the edition-based redefinition (EBR) model, so both unique indexes include that column.

Common Use Cases and Queries

Typical scenarios include validating routing setup, reporting on enabled categories, and tracing which dependent records belong to a category.

• Locating a category by its business key — the query most often associated with the UK search:

SELECT transaction_category_id, name, short_name, enable_flag
FROM   hr.pqh_transaction_categories
WHERE  short_name = :short_name
AND    business_group_id = :business_group_id
AND    zd_edition_name = :edition;

• Routing review — join to PQH_TABLE_ROUTE to inspect master and consolidated routing:

SELECT c.short_name, m.route_name master_route, t.route_name consolidated_route
FROM   hr.pqh_transaction_categories c,
       hr.pqh_table_route m,
       hr.pqh_table_route t
WHERE  c.master_table_route_id = m.route_id
AND    c.consolidated_table_route_id = t.route_id;

• Workflow enablement audit — filtering on WORKFLOW_ENABLE_FLAG and CUSTOM_WORKFLOW_NAME to find categories using custom workflow processes.
• Dependency reporting — counting referencing rows in dependent tables such as PQH_POSITION_TRANSACTIONS or PQH_ROUTING_HISTORY by TRANSACTION_CATEGORY_ID to assess impact before disabling a category.
• Setup replication — comparing ENABLE_FLAG, TIMEOUT_DAYS, and PREVENT_APPROVER_SKIP across business groups to ensure consistent configuration.

Related Objects

The foreign-key metadata identifies the most significant related objects:

• PQH_TABLE_ROUTE — referenced twice by this table via MASTER_TABLE_ROUTE_ID and CONSOLIDATED_TABLE_ROUTE_ID; defines the routing rules applied to each category.
• PQH_POSITION_TRANSACTIONS — references this table through WF_TRANSACTION_CATEGORY_ID; records position transactions subject to category routing.
• PQH_ROUTING_HISTORY and PQH_ROUTING_CATEGORIES — both join on TRANSACTION_CATEGORY_ID; provide routing categorisation and historical routing records.
• PQH_TXN_CATEGORY_ATTRIBUTES — stores per-category attribute configuration keyed by TRANSACTION_CATEGORY_ID.
• PQH_TEMPLATES, PQH_TRANSACTION_TEMPLATES, and PQH_ROLE_TEMPLATES — each reference TRANSACTION_CATEGORY_ID, tying templates to a transaction category.
• PQH_WORKSHEETS and PQH_FYI_NOTIFY — reference the category to drive worksheet processing and FYI notifications.
• PQH_COPY_ENTITY_TXNS — references the table twice, through TRANSACTION_CATEGORY_ID and WF_TRANSACTION_CATEGORY_ID, supporting entity copy operations.

Because eleven documented tables depend on this hub, changes to a transaction category — particularly to SHORT_NAME, BUSINESS_GROUP_ID, routing identifiers, or the enable flag — should be assessed for downstream impact before being applied in a production environment.