Search Results pl_to_sql19




Overview
Explore in AI Chat

Overview

DPP_BPEL_GETPROMOS is a PL/SQL package body owned by the APPS schema in Oracle E-Business Suite, classified under Advanced Pricing (DPP) functionality. The package functions as a PL/SQL-to-SQL bridge within the pricing architecture, specifically supporting the Business Process Execution Language (BPEL) integration layer that retrieves promotion and pricing information. In EBS 12.1.1 and 12.2.2, Oracle Advanced Pricing relies on this package to translate internal PL/SQL record structures into SQL-queryable table formats and vice versa, enabling external BPEL processes and Java-based services to consume pricing data through standard SQL interfaces rather than direct PL/SQL calls. The package is documented with a status of VALID and is not referenced by any other database object, positioning it as a top-level entry point rather than a shared library component.

Key Procedures and Functions

The package exposes nine documented procedures and functions, organized into converter pairs and a notification routine:

  • PL_TO_SQL16 / SQL_TO_PL16 — a bidirectional converter pair handling the transformation of PL/SQL data structures to SQL-accessible collections and back. The numeric suffix denotes the specific structure variant the pair operates on.
  • PL_TO_SQL17 / SQL_TO_PL17 — a second bidirectional converter pair for a distinct data structure variant within the pricing promotion model.
  • PL_TO_SQL18 / SQL_TO_PL18 — a third converter pair mapping PL/SQL records to SQL table types and reconstructing original structures on return.
  • PL_TO_SQL19 / SQL_TO_PL19 — a fourth converter pair serving an additional promotion-related data structure.
  • DPP_PRICING_PVT$NOTIFY_PROMOT — a notification procedure tied to the DPP_PRICING_PVT package, responsible for firing promotion notification events into the BPEL pipeline.

The converter procedures follow a consistent naming convention where "PL_TO_SQL" supports outbound serialization and "SQL_TO_PL" supports inbound deserialization. Their sequential numbering reflects the multiple distinct record shapes used by the promotion retrieval logic.

Tables Accessed

The package references the PLITBLM table via APPS synonym, which is a standard Oracle EBS PL/SQL integer table indexed by binary integer, used here as a transient collection target for serialized data during PL/SQL-to-SQL conversion. Multiple DPP_PRICING_PVT dependencies (DPP_PRICING_PVT_DPP_PL_NOTI14 through _16) indicate that promotion notification records flow through private PL/SQL table types owned by DPP_PRICING_PVT, while DPPPRICINGPVTDPPPLNOTI16_DPP_ represents a generated table type backing one of these converters. The use of PLITBLM rather than a persistent application table underscores that this package operates primarily on in-memory structures rather than writing directly to base pricing tables.

Usage Notes

DPP_BPEL_GETPROMOS is typically invoked to service BPEL callouts and Java concurrent processing requests that need promotion data in a relational format. Because the package is not referenced by any other database object, it is called directly by external integration code, BPEL partner links, or Java deployment code rather than through internal EBS framework chaining. Developers should note that APPS.DPP_BPEL_GETPROMOS carries Oracle Proprietary, Confidential Information status and should be modified only through supported patches. The absence of dependent objects means changes to its signatures carry limited internal impact but may break external BPEL interfaces. When troubleshooting pricing promotion retrieval failures in 12.1.1 or 12.2.2, this package is a primary candidate for investigation, particularly the DPP_PRICING_PVT$NOTIFY_PROMOT notification path and the four converter pairs.