Search Results sql_to_pl20




Overview
Explore in AI Chat

Overview

XX_BPEL_CALLPROCESSORDEREBS11I is a custom APPS-owned PL/SQL package classified under the OTHER API category in Oracle E-Business Suite. It is delivered as part of a BPEL (Business Process Execution Language) integration layer that allows an external BPEL process to invoke the Oracle Order Management order processing API — specifically OE_ORDER_PUB — from outside the EBS database session. The package name and its source header reference (OEXCGNIS.pls, version 120.2, dated 2008) indicate it was built for the 11i Order Capture integration, and the same code line remains resident in 12.1.1 and 12.2.2 instances where BPEL-based order sourcing is deployed.

The central problem this package solves is type impedance between the PL/SQL record structures declared in OE_ORDER_PUB and the equivalent SQL-level object and collection types declared in OE_ORDER_PUB_HEADER_* and OE_ORDER_PUB_HEADER_VAL_* type families. PL/SQL records cannot be passed across a JDBC/BPEL call boundary, so the package publishes paired conversion functions that map each PL/SQL RECORD to its SQL object counterpart, and back again. A global package state variable, G_check_action_ret_status, initialized to 'S', tracks the last action return status across the conversion calls within a session.

Key Procedures and Functions

The 99 documented functions follow a strict naming convention. Functions prefixed PL_TO_SQL accept a PL/SQL record or table type and return the corresponding SQL object or collection type; functions prefixed SQL_TO_PL perform the inverse conversion.

Each function is a pure transformation: no DML is executed, no OE_ORDER_PUB API is called within the conversion routines themselves. This design keeps the marshalling layer side-effect free so the calling BPEL process controls transaction boundaries explicitly.

Tables Accessed

The documented metadata records no direct table references through APPS synonyms. This is consistent with the package's design as a marshalling utility: it operates exclusively on PL/SQL and SQL types defined in the OE_ORDER_PUB specification. Consequently, XX_BPEL_CALLPROCESSORDEREBS11I reads no base tables directly and writes none. Any persistence to OE_ORDER_HEADERS_ALL, OE_ORDER_LINES_ALL, or their interface tables occurs when the caller subsequently invokes the OE_ORDER_PUB processing procedures using the converted structures.

Usage Notes

The package is referenced by zero other PL/SQL packages, confirming it is a leaf-layer integration adapter rather than shared internal infrastructure. It is normally invoked from a BPEL partner link through a database adapter, or from custom Java/JDBC code that first populates SQL object types from the inbound message, then calls SQL_TO_PL to obtain native OE_ORDER_PUB records for submission to the order API. The reverse PL_TO_SQL direction is used to return order processing results — statuses, messages, and populated header structures — to the BPEL layer.

Because it uses AUTHID CURRENT_USER, privileges are evaluated against the invoking schema; deployments should grant the calling account EXECUTE on the package and access to the underlying OE_ORDER_PUB types. The 11i-named object is supported in 12.1.1 and 12.2.2 through compatibility, but the OE_ORDER_PUB type hierarchy must exist unchanged for the conversions to compile and execute correctly.