Search Results exec_dynamic_sql

Overview

APPS.RCV_RECEIPTS_QUERY_SV is a server-side PL/SQL package within the Oracle E-Business Suite Receiving (RCV) module. Its name follows the EBS convention for "server-side view" packages that encapsulate query and lookup logic used by receiving-related user interfaces and APIs. The package provides a controlled, reusable layer for retrieving receipt information from the Receiving open interface and purchasing distribution tables, thereby shielding callers from the underlying table joins and filter logic required to display and validate receipt data.

The package is classified as OTHER in the ETRM API registry, indicating it is not a public, customer-facing API in the sense of a documented business interface, but rather an internal utility that supports higher-level receiving programs. It resides in the APPS schema and is valid in both 12.1.1 and 12.2.2. Its primary role is to expose a query model over receipts, shipments, and receiving transactions so that dependent packages can obtain consistent result sets without re-implementing the same SQL. According to the documented dependency metadata, RCV_RECEIPTS_QUERY_SV is referenced by the packages CSP_RECEIVE_PVT, INV_RCV_STD_RCPT_APIS, and itself, confirming its role as a shared internal component of the receiving stack.

Key Procedures and Functions

The ETRM metadata documents four procedures or functions within the package. Because the metadata does not expose parameter signatures, only the documented names and their apparent purposes are described.

• NAME — A functional element associated with the package, consistent with the EBS pattern of exposing an object or query name identifier used by callers to reference the receipt query model.
• POST_QUERY — The post-query processing routine. In EBS server-side view packages, POST_QUERY typically performs row-level enrichment or validation after the base query executes, such as populating derived columns or applying receiving-specific business rules to the returned receipt rows.
• EXEC_DYNAMIC_SQL — Provides dynamic SQL execution for the package, allowing query clauses or predicates to be assembled at runtime. This supports the flexible filtering required when receipts are queried by varying criteria such as receipt number, shipment, or organization.
• Additional functional member — The fourth documented procedure or function participates in the query lifecycle alongside the elements above, completing the retrieval and processing sequence.

Tables Accessed

The documented table references reflect the receiving, purchasing, inventory, and order management domains. Access to RCV_SHIPMENT_LINES and RCV_TRANSACTIONS_INTERFACE underlies receipt and shipment querying and open interface processing. PO_LINE_LOCATIONS, PO_DISTRIBUTIONS, PO_REQUISITION_LINES, and PO_REQ_DISTRIBUTIONS supply purchasing document context. Inventory master and control tables — MTL_SYSTEM_ITEMS, MTL_SECONDARY_INVENTORIES, MTL_UNITS_OF_MEASURE, MTL_INTERORG_PARAMETERS, and MTL_KANBAN_CARDS — provide item, subinventory, UOM, and organization validation data. OE_ORDER_LINES_ALL links receipts to sales order demand, RCV_PARAMETERS supplies receiving setup defaults, and PLITBLM is the standard EBS PL/SQL table type used by the dynamic SQL and post-query mechanisms.

Usage Notes

RCV_RECEIPTS_QUERY_SV is an internal package and is not intended for direct customer invocation. It is consumed by CSP_RECEIVE_PVT and INV_RCV_STD_RCPT_APIS, meaning it supports receiving execution and the standard receiving APIs. Customizations should not call this package directly; instead, developers should use the documented public receiving APIs. Any extension that references this package risks breakage during patching or upgrade, particularly between 12.1.1 and 12.2.2 where Receiving internals were revised. Where custom receipt querying is required, the supported approach is to query the RCV_SHIPMENT_LINES and RCV_TRANSACTIONS tables through supported views, or to extend the standard receiving forms, rather than depending on this server-side view package.