Search Results create_routing_history

Overview

The APPS.PQH_ROUTING_HISTORY_API package body is a public PL/SQL API within the Oracle E-Business Suite HRMS (Public Sector/Advanced Benefits) module. It manages the routing history records that track the movement, approval, and forwarding of transactions and notifications through the routing hierarchy. Routing history captures the audit trail of who forwarded an item, to whom, at what role or position, and the resulting approval outcome. The package encapsulates the business logic required to insert, modify, and remove rows in the routing history table and its associated descriptive attributes, ensuring that object version numbers and attribution columns are maintained consistently. It is classified as an API in the ETRM repository, meaning it is intended for programmatic invocation by both Oracle-delivered components and customer extensions rather than direct table manipulation.

Key Procedures and Functions

The package exposes four documented entry points:

• CREATE_ROUTING_HISTORY — The primary procedure for creating a new routing history record. It accepts identifying and descriptive parameters covering the approval status, comments, forwarding source and destination (assignment, member, position, user, and role identifiers), notification date, position structure version, routing and transaction categories, transaction identifier, user action code, effective date, and range names. It returns the newly generated routing history identifier and the object version number. A validate parameter allows the procedure to perform validation only without committing data.
• CREATE_ROUTING_HISTORY_BP — A business-process wrapper variant of the create procedure, typically invoked by workflow or business process flows that require the same insertion logic under a controlled context. It follows the same conceptual purpose as CREATE_ROUTING_HISTORY while supporting integration with Oracle Workflow business events.
• UPDATE_ROUTING_HISTORY — Modifies an existing routing history record, preserving the object version number to support optimistic locking and concurrent-update detection.
• DELETE_ROUTING_HISTORY — Removes a routing history record, typically used when a routing action is reversed or a transaction is purged. Deletion is guarded by object version checks.

Tables Accessed

The package operates against three documented tables via APPS synonyms:

• PQH_ROUTING_HISTORY — The master table storing the routing history rows, including the forwarding parties, dates, categories, and version columns written and read by the create, update, and delete procedures.
• PQH_ROUTING_HIST_ATTRIBS — The descriptive flexfield attribute table that stores additional user-defined or context-specific attributes associated with each routing history record. The API maintains these attributes alongside the base row.
• PLITBLM — A PL/SQL index-by table type used as an in-memory structure for holding and processing collections of identifiers or messages during API execution, rather than a persistent business table.

Usage Notes

This API is most commonly invoked from routing and approval forms within the Public Sector/Advanced Benefits module, from Oracle Workflow business processes that route transactions for approval, and from custom PL/SQL code that needs to record routing activity programmatically. Because it is a public API, direct DML against PQH_ROUTING_HISTORY should be avoided; callers should always use these procedures so that object version numbers, WHO columns, and attribute records remain synchronized. The boolean p_validate parameter on the create routine allows callers to test the operation before committing. The package is referenced by one other package in the ETRM repository, indicating a degree of internal dependency that developers should account for when extending or wrapping the API. Callers must supply a valid effective date and at least the routing identifiers appropriate to their transaction context.