WSH Pick Confirm Upload – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

WSH Pick Confirm Upload ======================= Confirms released sales order pick tasks in bulk from Excel: the picked quantity, the subinventory, locator and lot actually picked, the staging location and, in WMS enabled organizations, the LPN the material was packed into.

Download the released to warehouse lines of an organization, optionally restricted to an order, delivery, trip, move order (pick slip), customer or item. One row is created per pick allocation, and per allocated lot for lot controlled items.

Overwrite the pre-filled allocation where the pick differs, enter the LPN and upload. Each pick task is confirmed through the standard pick confirm API, which moves the material to the staging subinventory and sets the delivery line to Staged/Pick Confirmed. Unknown LPN names are created as new LPNs in the organization.

Parameter Confirm All Downloaded Lines pre-flags every downloaded row, so an unchanged pick list can be uploaded as it is.

Parameter Ship Confirm additionally ship confirms a delivery as soon as its last released line has been confirmed by the upload. Unpicked quantities of the delivery are backordered.

Serial controlled items are not supported.

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

Report Parameters

Organization Code, Order Number, Delivery, Trip, Move Order, Customer, Item, Confirm All Downloaded Lines, Ship Confirm

Oracle EBS Tables Used

wsh_delivery_legs, wsh_trip_stops, wsh_trips, mtl_parameters, mtl_material_transactions_temp, wsh_delivery_details, wsh_delivery_assignments, wsh_new_deliveries, oe_order_lines_all, oe_order_headers_all, hz_cust_accounts, hz_parties, hz_cust_site_uses_all, mtl_transaction_lots_temp, mtl_txn_request_lines, mtl_txn_request_headers, wms_dispatched_tasks, mtl_system_items_vl, mtl_item_locations_kfv, wms_license_plate_numbers

Report Categories

Enginatics, R12 only, Upload

WSH Shipping/Delivery Transactions, CAC Interface Error Summary, INV Period Close Pending Transactions

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource Link
Excel Example Output Example
Blitz Report™ XML Import WSH_Pick_Confirm_Upload.xml
SQL Source WSH Pick Confirm Upload.sql
Full Report on Enginatics www.enginatics.com/reports/wsh-pick-confirm-upload/

Case Study & Technical Analysis: WSH Pick Confirm Upload

Executive Summary

The WSH Pick Confirm Upload confirms released sales order pick tasks in bulk from Excel in Oracle Shipping Execution. Warehouse operators download the released-to-warehouse lines of an organization, correct the picked quantity, subinventory, locator and lot where the pick differs, enter the license plate number in WMS-enabled organizations, and upload the result. Each pick task is confirmed through the standard pick confirm API, which moves the material to the staging subinventory and sets the delivery line to Staged/Pick Confirmed. The upload can also ship confirm a delivery once its last released line has been confirmed. The report runs in Excel via Blitz Report, and serial controlled items are not supported.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. MTL_PARAMETERS is joined to MTL_MATERIAL_TRANSACTIONS_TEMP on organization, restricted to sales order transactions by transaction source type.
  2. MTL_MATERIAL_TRANSACTIONS_TEMP is joined to WSH_DELIVERY_DETAILS on move order line, with the line in released status.
  3. WSH_DELIVERY_DETAILS is joined to OE_ORDER_LINES_ALL, then to OE_ORDER_HEADERS_ALL, the customer account, the party and the ship-to site.
  4. WSH_DELIVERY_DETAILS is outer joined to WSH_DELIVERY_ASSIGNMENTS and WSH_NEW_DELIVERIES to find the delivery.
  5. The lot allocations, move order lines and WMS dispatched tasks are outer joined on the transaction temporary identifier.
  6. The pick and staging locators and the transfer license plate are outer joined on their identifiers.
  7. The trip name is a listagg over distinct trip names reached through the delivery legs and trip stops.

Parameters & Filtering

Performance & Optimization

FAQ

Q: Why does one order line appear on several rows? A: One row is created per pick allocation, and per allocated lot for lot controlled items, so a line picked from several locations or lots appears several times.

Q: How do I pick an unchanged pick list quickly? A: Set Confirm All Downloaded Lines so every row is pre-flagged, then upload the file without changes.

Q: Are serial controlled items supported? A: No. The description states that serial controlled items are not supported by this upload.

© Enginatics GmbH