Search Results g_internal_query




Overview
Explore in AI Chat

Overview

OE_VERSION_HISTORY_UTIL is a utility PL/SQL package in the Oracle Order Management module of Oracle E-Business Suite, owned by the APPS schema and classified as a utility API (UTIL). Its primary business function is to create and maintain version history records for Order Management entities, providing the audit trail that supports version comparison and historical inquiry in the Order Organizer and related order inquiry forms. The package writes history rows into four distinct history tables: oe_order_header_history, oe_order_lines_history, oe_price_adjs_history, and oe_sales_credit_history. A defining design characteristic is its idempotent behavior: the API skips any record that already exists for the specified version, so repeated invocations do not produce duplicate history entries. The package also supports a dedicated phase-change mode, in which the caller passes a NULL version number together with the phase change flag set to 'Y', causing only phase transition information to be captured rather than a full version snapshot. The header comment shows a maintenance lineage extending through version 120.8 with a 2013 patch level, and the package cites bug fix 9503990 as the origin of the line-status derivation logic described below.

Key Procedures and Functions

Fourteen documented units make up the package, with the core public units and the internal query helpers as follows:

  • CREATE_VERSION_HISTORY — The principal entry point. It creates history records for the supported entities, skipping rows that already exist for the requested version, and accepts the header identifier along with version and phase-change parameters.
  • GET_STATUS — A function returning a line's flow status code. Introduced by bug 9503990, it accepts a line identifier and an optional flow status code. When the supplied status is NULL it queries oe_order_lines_all for the current value; when the status falls into the shipping-completion set ('AWAITING_SHIPPING', 'PRODUCTION_COMPLETE', 'PICKED', 'PICKED_PARTIAL', 'PO_RECEIVED') it re-derives the status from wsh_delivery_details by aggregating released-status counts, returning 'PICKED' when all details are released, 'PICKED_PARTIAL' when only some are, and otherwise the original status. The function emits diagnostic messages through oe_debug_pub.
  • GET_TRANSACTION_VERSION — Retrieves the current transaction version identifier used when stamping history rows.
  • QUERY_ROW and QUERY_ROWS — Internal query helpers that fetch single history rows and result sets respectively, encapsulating the cursor logic used by the higher-level history creation and retrieval routines.

The remaining documented units are internal helpers supporting these public interfaces; they are not intended for direct external invocation. Standard order-management development practice treats the entire package as an internal utility rather than a supported public API.

Tables Accessed

The package spans order capture, pricing, credit, and shipping data. Source tables include OE_ORDER_HEADERS and OE_ORDER_HEADERS_ALL (header attributes at the parent level), OE_ORDER_LINES_ALL (line attributes and current flow status), OE_PRICE_ADJUSTMENTS (pricing adjustment detail), OE_SALES_CREDITS (sales credit assignments), WSH_DELIVERY_DETAILS (shipping release status used by GET_STATUS), OE_PAYMENTS and PLITBLM (payment and internal LOB storage), and the payment instrument tables IBY_CREDITCARD_H, IBY_FNDCPT_TX_EXTENSIONS, and IBY_PMT_INSTR_USES_ALL for credit-card and funds-capture information reflected in the sales credit and payment history. Target history tables written by the package are OE_ORDER_HEADER_HISTORY, OE_ORDER_LINES_HISTORY, OE_PRICE_ADJS_HISTORY, and OE_SALES_CREDIT_HISTORY, all accessed through APPS synonyms.

Usage Notes

OE_VERSION_HISTORY_UTIL is invoked indirectly rather than directly by end users. It is called from Order Management forms and business flows at points where a version snapshot or phase transition must be recorded, and the ETRM repository shows it is referenced by four other packages, indicating it is embedded in the order versioning infrastructure. In EBS 12.1.1 and 12.2.2, customizations should not call this package directly, because the supported interface for retrieving version history is through the Order Management inquiry forms and the documented version history APIs. When extending version-related functionality, developers should instead rely on the standard Order Management public APIs and treat this package as an internal implementation detail subject to change between patch levels — as evidenced by the bug-driven modification to GET_STATUS.