Search Results sql_to_pl3

Overview

DPP_BPEL_GETIPL is an Oracle E-Business Suite PL/SQL package body owned by the APPS schema that supports the Advanced Pricing (DPP) module's integration with BPEL-based business event processing. The package name and its internal structure indicate it functions as an inbound interface layer: it receives notification records generated by external BPEL workflows and marshals them into the internal data structures used by the DPP_PRICING_PVT pricing engine. The source header identifies the file as dppvbipb.pls (version 120.1, dated December 2007), consistent with the 12.1.1 code base that was subsequently carried forward into 12.2.2.

The central purpose of the package is conversion and adaptation. Oracle EBS pricing APIs frequently exchange data using two parallel representations: PL/SQL record types (nested, strongly typed, defined in DPP_PRICING_PVT) and SQL object types (schema-level types usable in SQL statements). DPP_BPEL_GETIPL provides the bridge between these two worlds, allowing records to be passed through SQL layers — including the PLITBLM table and any object-relational plumbing used by the BPEL adapter — without losing field-level fidelity. In ETRM the package is classified as an "OTHER" API, meaning it is not a documented public API but is instead an internal support unit.

Key Procedures and Functions

The ETRM metadata documents nine procedures and functions. Their naming follows a strict pattern that encodes both the direction and the "arity" of the conversion:

• PL_TO_SQL0 — Converts a scalar PL/SQL record (DPP_PRICING_PVT.DPP_PL_NOTIFY_REC_TYPE) into the corresponding SQL object type (DPP_PRICING_PVT_DPP_PL_NOTIFY). The excerpt confirms it initializes the object with nine NULL attributes and copies fields such as PP_TRANSACTION_NO, EFFECTIVITY_DATE, ORG_ID, VENDOR_ID, VENDOR_SITE_ID, VENDOR_NUMBER, VENDOR_NAME, VENDOR_SITE_CODE, and OPERATING_UNIT.
• SQL_TO_PL0 — The inverse of PL_TO_SQL0, mapping the SQL object back into the PL/SQL record type.
• PL_TO_SQL1 — First overload in the conversion family; handles a variant of the source PL/SQL type or a differently shaped object.
• SQL_TO_PL1 — Inverse of PL_TO_SQL1.
• PL_TO_SQL2 — Converts a collection/table type (DPP_OBJECT_NAME_TBL_TYPE) into an object collection (DPPPRICINGPVTDPPPLNOTIF2_DPP_). The excerpt shows it initializes an empty collection, calls EXTEND to match the source COUNT, and iterates from FIRST to LAST element.
• SQL_TO_PL2 — Inverse of PL_TO_SQL2, converting a collection of SQL objects back into a PL/SQL table type.
• PL_TO_SQL3 and SQL_TO_PL3 — The final conversion pair, extending the same pattern to a third type shape. The precise type pair is not shown in the excerpt.
• DPP_PRICING_PVT$NOTIFY_INBOUN — The entry point for inbound notifications, truncating the name at the 30-character limit. This is the procedure invoked by the BPEL inbound queue/agent to hand a notification to the pricing engine.

Tables Accessed

ETRM records a single referenced table via the APPS synonym: PLITBLM. In Advanced Pricing, PLITBLM is the Pricing Interface Table (Pricing List Item / price list line interface), used as a staging area for inbound price-list and adjustment data arriving from external systems, batch loaders, or interfaced applications. DPP_BPEL_GETIPL reads records from PLITBLM during the notify-inbound flow, converting each row's column data into PL/SQL record form or SQL object form so the pricing engine can validate and apply the incoming price-list entries. Because PLITBLM is the only documented table, the package is narrowly scoped to the price-list inbound path rather than the broader pricing schema.

Usage Notes

DPP_BPEL_GETIPL is an internal, non-public utility. ETRM records zero packages that reference it, which means it is called directly by Oracle's BPEL composite rather than through a wrapper in the EBS database. Typical invocation is therefore:

• BPEL inbound message flow — The BPEL process for price-list notifications uses the database adapter to invoke DPP_PRICING_PVT$NOTIFY_INBOUN, which in turn drives the PL_TO_SQL/SQL_TO_PL conversion functions as the payload is normalized.
• SQL-layer data migration — When PL/SQL records must be inserted or selected through SQL (for example, to leverage a collection type inside a table or view), the SQL_TO_PL* and PL_TO_SQL* pairs are used to move values across the PL/SQL–SQL boundary without type errors.

Customers and partners should not call these conversion functions directly in custom code. They are version-specific helpers bound to the DPP_PRICING_PVT object type definitions; if the underlying object types change in a patch or upgrade (particularly the 12.1.1 → 12.2.2 transition), the conversion functions may be regenerated. Any custom extension that depends on them should go through the supported DPP_PRICING_PVT public API and the documented inbound interface mechanisms rather than invoking DPP_BPEL_GETIPL directly. The package is not registered as a public API in ETRM, and no public parameters are documented, reinforcing its role as internal plumbing for the Advanced Pricing BPEL integration.