Search Results sql_to_pl12

Overview

DPP_BPEL_POPCUSTCLAIM is an Oracle E-Business Suite PL/SQL package body owned by APPS that supports the Oracle Demand Planning / Advanced Planning (DPP) module's interaction with Oracle BPEL process flows for customer claims processing. The package provides bidirectional data conversion routines that translate between PL/SQL record types and SQL object types used by the DPP Customer Claims private API (DPP_CUSTOMERCLAIMS_PVT). This pattern reflects the standard EBS design of bridging PL/SQL collections and SQL object types so that the same business data structure can be passed between PL/SQL code and SQL statements in a consistent, strongly-typed manner.

The package is classified as OTHER in ETRM metadata and was last modified per its revision header in February 2008, corresponding to early Release 12 development. It is designed for Oracle EBS 12.1.1 and 12.2.2 deployment, where it remains a supporting component of the customer claims execution flow within the Demand Planning architecture.

Key Procedures and Functions

ETRM documents eleven procedures and functions in this package. The core pattern is a paired set of conversion routines:

• PL_TO_SQL10 through PL_TO_SQL14 — These functions convert a PL/SQL record (of type DPP_CUSTOMERCLAIMS_PVT.DPP_CUST_HDR_REC_TYPE) into a corresponding SQL object type (DPP_CUSTOMERCLAIMS_PVT_DPP_C10 through _C14). Each function initializes the object, copies field values such as TRANSACTION_HEADER_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, ORG_ID, EXECUTION_DETAIL_ID, OUTPUT_XML, PROVIDER_PROCESS_ID, PROVIDER_PROCESS_INSTANCE_ID, LAST_UPDATED_BY, and CURRENCY_CODE, and returns the populated SQL object.
• SQL_TO_PL10 through SQL_TO_PL14 — These functions perform the reverse conversion, mapping a SQL object type back into the PL/SQL record type so that records retrieved by SQL can be processed by PL/SQL logic. The documented source excerpt shows SQL_TO_PL10 mirroring PL_TO_SQL10 exactly, copying each of the ten attributes in turn.
• DPP_CUSTOMERCLAIMS_PVT$POPULA — A supporting routine whose name suggests it populates the private API's record structures, likely serving as an internal population helper invoked during conversion or persistence operations.

The numbered suffixes (10 through 14) correspond to successive versions of the underlying customer claims header record structure, allowing EBS to support multiple concurrent schema versions across release upgrades without breaking existing code paths.

Tables Accessed

ETRM metadata lists a single referenced table accessed through an APPS synonym: PLITBLM. This is an EBS-standard temporary/interface table used in planning and manufacturing flows for staging and bulk data movement. In the context of this package, PLITBLM likely serves as the staging surface through which customer claim header data is passed between the BPEL process layer and the DPP private API persister. The bulk of the package's logic operates in memory on records and object types; the table access is comparatively narrow.

Usage Notes

This package is invoked internally rather than by end users directly. Typical invocation paths include:

• Oracle BPEL Process Manager workflows for customer claims that call the DPP_CUSTOMERCLAIMS_PVT API; the conversion routines translate records between the BPEL-facing PL/SQL structures and the SQL object types used by persistence queries.
• Concurrent programs and internal planning jobs that serialize DPP customer claim headers to or from the database via bulk SQL operations.
• Custom integrations extending the standard claims flow, which may reuse these conversion functions to construct valid SQL objects for insertion or update.

Because the package is documented as referenced by zero other packages, its callers are external — BPEL flows, the private API's own code base, or ad-hoc SQL — rather than sibling PL/SQL packages. The header note indicates it was formerly a non-shipped (noship) component that was later blessed for production, so integrators should treat it as supported but internal and avoid direct modification.