Search Results sql_plsql_error

Overview

DPP_UIWRAPPER_PVT is a private PL/SQL package body in the APPS schema that serves as a user-interface wrapper for the Oracle EBS Advanced Pricing (DPP) module. Its primary business function is to insulate Oracle Forms-based user interfaces and other callers from the lower-level utility APIs implemented in DPP_UTILITY_PVT, presenting a simplified and consistent programming contract for common pricing and trading-community lookups. The package is classified as PVT, indicating it is intended for internal consumption by other DPP packages and by the Forms layer rather than as a published public API.

The wrapper pattern is evident in the documented source. The check_transaction procedure delegates directly to DPP_UTILITY_PVT.check_transaction, forwarding the transaction header identifier, the requested status change, and the standard output parameters (record count, message data, and return status). This delegation shields callers from the internal signature of the utility package while preserving the standard FND_API return-status convention used throughout Oracle EBS.

Key Procedures and Functions

The package exposes 24 documented procedures and functions, organized into two broad families: search routines and single-record retrieval routines.

• Search routines: SEARCH_VENDORS, SEARCH_VENDOR_SITES, SEARCH_VENDOR_CONTACTS, SEARCH_ITEMS, SEARCH_CUSTOMER_ITEMS, SEARCH_CUSTOMER_ITEMS_ALL, SEARCH_WAREHOUSES, SEARCH_CUSTOMERS, and SEARCH_CUSTOMERS_ALL. These accept caller-supplied search criteria and return tabular result sets, typically as PL/SQL collections with an accompanying record count. The search_vendors procedure illustrates the pattern: it copies the incoming search_criteria_tbl_type into an equivalent DPP_UTILITY_PVT.search_criteria_tbl_type, invokes the utility routine, and maps results back into the caller's collection type when records are found.
• Single-record getters: GET_VENDOR, GET_VENDOR_SITE, GET_VENDOR_CONTACT, GET_WAREHOUSE, GET_CUSTOMER, and GET_PRODUCT. These resolve a single entity from its identifier or key.
• Pricing retrieval: GET_LASTPRICE and GET_LISTPRICE return pricing information for validated pricing attributes.
• Inventory retrieval: GET_INVENTORYDETAILS and GET_CUSTOMERINVENTORY return on-hand or customer inventory positions.
• Approval support: GET_ALLAPPROVERS returns the set of approvers applicable to a given pricing or transaction context.

Every procedure follows the standard EBS error-handling idiom: it captures FND_API.G_EXC_ERROR and maps it to G_RET_STS_ERROR, while the WHEN OTHERS handler sets G_RET_STS_UNEXP_ERROR and, when the unexpected-error message level is enabled, raises the FND message SQL_PLSQL_ERROR with tokens for the routine name, SQLCODE, and SQLERRM.

Tables Accessed

The documented metadata identifies PLITBLM as the table referenced through APPS synonyms. PLITBLM is the pricing attribute or line-attribute interface table used within the Advanced Pricing architecture, and access to it supports the pricing retrieval and transaction-validation operations exposed by this wrapper. The bulk of the remaining data access is performed indirectly through DPP_UTILITY_PVT, which encapsulates the underlying base tables for vendors, customers, items, and warehouses.

Usage Notes

DPP_UIWRAPPER_PVT is typically invoked from Oracle Forms user interfaces within the Advanced Pricing and trading-community flows, and from other DPP packages; the metadata records that it is referenced by one other package. Because it is a private package, it is not intended for direct invocation by customer extensions. The recurring SQL_PLSQL_ERROR message raised in its exception handlers is directly relevant to the search term sql_plsql_error: when unexpected exceptions surface, lookups against FND_MESSAGE reveal this token, and the ROUTINE token identifies the specific wrapper procedure that failed, providing the starting point for diagnosis.