Results for “routing_category_id”

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

Overview

PQH_ROUTING_CATEGORIES is a transaction routing configuration table in the Oracle E-Business Suite Public Sector HR (PQH) module. It defines which routing list applies to a given transaction category, optionally scoped by position structure, and supplies override targets (position, assignment, role, or user) plus enable/default/delete flags. In practice it is the governing intersection between a workflow-style routing definition (PQH_ROUTING_LISTS) and the classification of a transaction (PQH_TRANSACTION_CATEGORIES), allowing the routing engine to select the correct approval or distribution path at runtime.

The table is owned by the HR schema and is documented as VALID in both the 12.1.1 and 12.2.2 ETRM data models, with 17 columns in the documented 12.2.2 physical schema. Based on the foreign key topology, the heuristic Data Vault classification is satellite-leaning: the table carries descriptive routing attributes and a natural business key (TRANSACTION_CATEGORY_ID, DEFAULT_FLAG, ROUTING_LIST_ID, POSITION_STRUCTURE_ID) around the surrogate ROUTING_CATEGORY_ID, rather than acting as a pure hub or link.

Key Information Stored

• ROUTING_CATEGORY_ID — Surrogate primary key defined by index PQH_ROUTING_CATEGORIES_PK. This is an internal identifier and should not be exposed to end users as a business key.
• TRANSACTION_CATEGORY_ID — Foreign key to PQH_TRANSACTION_CATEGORIES. Identifies the transaction type (for example, hire, promotion, termination) whose routing is being configured.
• ROUTING_LIST_ID — Foreign key to PQH_ROUTING_LISTS. Determines the routing list that will be applied for this category.
• POSITION_STRUCTURE_ID — Foreign key to PER_POSITION_STRUCTURES. Scopes the routing rule to a specific position hierarchy, enabling structure-specific routing.
• OVERRIDE_ROLE_ID — Foreign key to PQH_ROLES. Suppresses the default routings and directs the transaction to a specific role.
• OVERRIDE_POSITION_ID and OVERRIDE_ASSIGNMENT_ID — Override targets that redirect routing to a specific position or assignment rather than the standard list.
• OVERRIDE_USER_ID — Override target that directs routing to a specific user when a named recipient is required.
• DEFAULT_FLAG — Part of the business-key candidate index PQH_ROUTING_CATEGORIES_UK1. Marks the row as the default routing rule for the transaction category when no more specific scope applies.
• ENABLE_FLAG and DELETE_FLAG — Control whether the routing rule is currently active and whether it has been logically deleted.
• OBJECT_VERSION_NUMBER and the audit columns (LAST_UPDATE_DATE, CREATION_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) — Standard Oracle EBS concurrency and audit tracking.

The unique index PQH_ROUTING_CATEGORIES_UK1 covers TRANSACTION_CATEGORY_ID, DEFAULT_FLAG, ROUTING_LIST_ID, and POSITION_STRUCTURE_ID, forming the business-key candidate. The surrogate PK (ROUTING_CATEGORY_ID) remains the physical join key to child tables.

Common Use Cases and Queries

Typical usage centers on the routing engine resolving a transaction to a routing list at submission time, and on administrators maintaining routing configuration.

• Resolving the applicable routing rule:

  SELECT rc.routing_list_id, rc.default_flag
  FROM   pqh_routing_categories rc
  WHERE  rc.transaction_category_id = :category_id
  AND    rc.enable_flag = 'Y'
  AND    rc.delete_flag = 'N'
  AND    rc.position_structure_id = :structure_id;

• Finding the default rule for a category: filter on DEFAULT_FLAG = 'Y' combined with TRANSACTION_CATEGORY_ID.
• Listing routes with override destinations: join to PQH_ROLES, PER_POSITION_STRUCTURES, or PER_ASSIGNMENTS on the OVERRIDE_* columns to report which transactions bypass standard routing.
• Auditing historical routing: join PQH_ROUTING_HISTORY on ROUTING_CATEGORY_ID to trace which routing rule applied to past transactions.
• Validating attribute ranges: join PQH_ATTRIBUTE_RANGES on ROUTING_CATEGORY_ID to inspect the value ranges that further qualify the routing rule.

Reporting queries should always filter DELETE_FLAG and ENABLE_FLAG, and should project the business-key columns rather than the surrogate ID.

Related Objects

• PQH_ROUTING_LISTS — referenced via ROUTING_LIST_ID; the routing list applied by the rule.
• PQH_TRANSACTION_CATEGORIES — referenced via TRANSACTION_CATEGORY_ID; the transaction type being routed.
• PER_POSITION_STRUCTURES — referenced via POSITION_STRUCTURE_ID; scopes routing by position hierarchy.
• PQH_ROLES — referenced via OVERRIDE_ROLE_ID; supplies the role override target.
• PQH_ATTRIBUTE_RANGES — child table referencing PQH_ROUTING_CATEGORIES via ROUTING_CATEGORY_ID; holds qualifying attribute ranges.
• PQH_ROUTING_HISTORY — child/audit table referencing PQH_ROUTING_CATEGORIES via ROUTING_CATEGORY_ID; records routing decisions applied to transactions.
• Position and assignment entities (PER_POSITIONS, PER_ASSIGNMENTS) associated with OVERRIDE_POSITION_ID and OVERRIDE_ASSIGNMENT_ID, used to resolve override routing targets.