Search Results wsh_pick_custom

Overview

WSH_PICK_CUSTOM is a user-customizable PL/SQL package in the Oracle E-Business Suite Shipping (WSH) module. Its documented purpose is to provide an extension point for Oracle Warehouse Management's Pick Release process, specifically exposing a mechanism through which customers can compute order value. Pick Release selects eligible sales order lines from inventory and generates move orders and picking instructions; the sequence in which lines are released can be influenced by a custom valuation routine. Because the package is defined with AUTHID CURRENT_USER and is intended for customer modification, Oracle ships it as a stub-style container that implementers may extend to insert site-specific business logic without altering Oracle-owned code. The package is owned by the APPS schema and is classified in the ETRM repository as an "OTHER" API type rather than a standard public interface. In Oracle EBS 12.1.1 and 12.2.2 the package remains present with the same single function signature, and the 12.2 online patching architecture treats it as an ordinary PL/SQL unit within the APPS edition.

Key Procedures and Functions

The ETRM metadata documents exactly one callable unit:

• OUTSTANDING_ORDER_VALUE — A function that calculates the value of an order and returns that value as a BINARY_INTEGER. Per the source header, this value is intended for use in the ORDER BY clause that determines the sequence in which order lines are released during Pick Release. The function accepts a single input, identified in the specification as a header identifier (p_header_id), representing the sales order header being evaluated. Documented return behavior is the computed order value on success and 0 on failure. The function carries a PRAGMA RESTRICT_REFERENCES declaration (WNDS, WNPS), meaning it is asserted not to write database state or package state — a purity guarantee that permits it to be called from SQL contexts such as the ORDER BY clause referenced in the header comment. Implementers who add logic to this function must preserve that purity contract, because violating it causes runtime errors when the function is invoked from SQL.

Tables Accessed

The package's documented table references, resolved through APPS synonyms, are:

• OE_ORDER_HEADERS_ALL — The Order Management order header table. It supplies header-level attributes such as the order number, order type, pricing and currency context, and total order value inputs needed to derive a comparable value for release sequencing.
• OE_ORDER_LINES_ALL — The Order Management order line table. It provides line-level pricing, quantity, and ordered-item detail that the valuation calculation aggregates when a header identifier is passed in.

The source header does not document writes to any table, consistent with the WNDS restriction on the function. Any customer extension should therefore read order data only and return a numeric ranking value.

Usage Notes

WSH_PICK_CUSTOM is not invoked directly by end users. It is called from within the Pick Release processing flow, where the returning value of OUTSTANDING_ORDER_VALUE is consumed by the ordering logic that prioritizes which orders or lines are released first. Typical customization scenarios include prioritizing high-value orders, orders with aging or credit exposure, or orders whose outstanding value exceeds a threshold, so that fulfillment resources are directed to strategically important demand. Because the ETRM metadata records that the package is referenced by one other package, the calling site is an Oracle Shipping program rather than a form or concurrent program; implementations confirm this by searching the Shipping module's Pick Release package for the call prior to modifying behavior. The standard approach is to copy the delivered stub into a custom package or augment it in place within the APPS schema, then recompile and regression-test Pick Release to confirm the ORDER BY effect. Implementers should note that object changes of this kind require the usual 12.1.1 and 12.2.2 deployment disciplines, and that patching or upgrading Shipping may overwrite customizations, so change control and reapplication scripts are recommended.