WSH Shipping/Delivery Transactions – Oracle EBS SQL Report

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

Overview

This report provides details of Warehouse Shipping Transactions and Deliveries.

To review details of deliveries only, set the Assigned to Delivery Parameter = ‘Yes’ To review details of shipping transaction not yet assigned to a delivery, set the Assigned to Delivery Parameter = ‘No’ Set the parameter to null to review all shipping transations regardless of delivery assignment status.

Report Parameters

Selling Operating Unit, Shipping Operating Unit, Organization Code, Assigned to Delivery, Delivery Status, Exclude Delivery Status, Release status, Exclude Release Status, Exception Exists, Customer Name, Customer Number, Delivery Note From, Delivery Note To, Item, Item From, Item To, Lot Number, Lot Number From, Lot Number To, Order Number From, Order Number To, Scheduled Date From, Scheduled Date To, Released Date From, Released Date To, Actual Shipment Date From, Actual Shipment Date To, Invoiced Date From, Invoiced Date To, Initial Pick Up Date From, Initial Pick Up Date To, Ultimate Drop Off Date From, Ultimate Drop Off Date To, Location Format

Oracle EBS Tables Used

Report Categories

Enginatics

Running This SQL Without Blitz Report

Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.

Download & Import Options

Resource	Link
Excel Example Output	None
Blitz Report™ XML Import	WSH_Shipping_Delivery_Transactions.xml
Full SQL on Enginatics	www.enginatics.com/reports/wsh-shipping-delivery-transactions/

Case Study & Technical Analysis: WSH Shipping/Delivery Transactions Report

Executive Summary

The WSH Shipping/Delivery Transactions report is a crucial operational and customer service tool for Oracle Shipping Execution. It provides a comprehensive, detailed view of all sales order shipping transactions and their associated deliveries, encompassing various statuses, customer information, and item details. This report is indispensable for logistics managers, customer service representatives, and order fulfillment teams to track the entire shipping lifecycle, monitor delivery progress, identify exceptions, and ensure timely and accurate fulfillment of customer orders, thereby enhancing customer satisfaction and optimizing logistical efficiency.

Business Challenge

Managing the shipping and delivery of customer orders is a complex logistical challenge, with numerous touchpoints and potential for delays or errors. Organizations often struggle with:

Lack of End-to-End Visibility: Tracking a sales order from its initial release for shipping, through picking, packing, staging, actual shipment, and final delivery, is often fragmented across multiple Oracle modules (Order Management, Inventory, Shipping).
Monitoring Delivery Progress: Getting real-time status updates on individual shipments and deliveries, especially in transit, is crucial for proactive customer communication and managing delivery expectations.
Identifying Bottlenecks and Exceptions: Delays in picking, packing, or carrier handoff, or discrepancies in delivered quantities, can lead to customer complaints and increased costs. Proactively identifying these exceptions is critical.
Customer Service Inquiries: Responding to customer inquiries about their order’s shipping status requires immediate access to accurate and comprehensive delivery information.
Audit and Compliance: For high-value shipments or regulated goods, an auditable record of all shipping and delivery events is necessary for compliance and dispute resolution.

The Solution

This report offers a powerful, integrated, and actionable solution for managing shipping and delivery transactions, bringing transparency and control to the order fulfillment process.

Comprehensive Shipping Lifecycle View: It consolidates detailed information from shipping transactions and deliveries, linking them to sales orders, customers, items, and various statuses (e.g., released, shipped, delivered). This provides an unparalleled end-to-end perspective.
Flexible Status Filtering: Crucial parameters like Delivery Status, Release status, and Assigned to Delivery (Yes/No/Null) allow users to precisely focus on specific stages of the shipping process, such as orders awaiting delivery assignment or those in transit.
Proactive Exception Management: The Exception Exists flag (if applicable) highlights problematic deliveries, enabling logistics teams to proactively investigate and resolve issues before they escalate into customer complaints.
Enhanced Customer Communication: Customer service representatives can leverage this report to provide accurate, real-time updates to customers on their order’s shipping status, improving transparency and satisfaction.

Technical Architecture (High Level)

The report queries core Oracle Shipping Execution, Order Management, and customer master tables to provide its detailed output.

Primary Tables Involved:
- wsh_delivery_details (the central table for individual items released for shipment and their status).
- wsh_new_deliveries (for delivery headers, grouping delivery details).
- wsh_delivery_assignments (links delivery details to deliveries).
- oe_order_lines_all and oe_order_headers_all (for sales order context).
- hz_cust_accounts, hz_parties, hz_cust_site_uses_all (for customer and ship-to site details).
- mtl_system_items_vl (for item master details).
Logical Relationships: The report typically starts with wsh_delivery_details to identify all shipping lines. It then joins to wsh_new_deliveries (via wsh_delivery_assignments) to get delivery-level information. Further joins to Order Management tables link these deliveries back to the original sales order, and to customer master tables to provide full customer context. The various date parameters and status flags are applied to filter and present the data at different stages of the shipping lifecycle.

Parameters & Filtering

The report offers an extensive set of parameters for precise filtering and detailed data inclusion:

Organizational Context: Selling Operating Unit, Shipping Operating Unit, Organization Code define the business and inventory scope.
Status Filters: Assigned to Delivery (Yes/No/Null), Delivery Status, Exclude Delivery Status, Release status, Exclude Release Status, Exception Exists are crucial for pinpointing specific operational states or problems.
Customer and Order Identification: Customer Name, Customer Number, Order Number From/To allow for granular targeting of specific customer orders.
Item and Lot Filters: Item, Item From/To, Lot Number, Lot Number From/To enable tracking of specific products or batches.
Date Ranges: A wide array of date parameters (Scheduled Date From/To, Released Date From/To, Actual Shipment Date From/To, Invoiced Date From/To, Initial Pick Up Date From/To, Ultimate Drop Off Date From/To) support detailed chronological analysis of the shipping process.
Location Format: Customizes the display of location information.

Performance & Optimization

As a detailed transactional report integrating data across multiple modules (Shipping, OM, Inventory), it is optimized through strong filtering and date-driven processing.

Parameter-Driven Efficiency: The extensive filtering capabilities (by status, customer, order, dates) are critical for performance, allowing the database to efficiently narrow down the large transactional datasets to relevant shipments and deliveries using existing indexes.
Date Range Filters: The various date parameters (Actual Shipment Date From/To, Scheduled Date From/To) are particularly effective in limiting the data volume to a manageable timeframe.
Conditional Joins: The Assigned to Delivery parameter effectively acts as a filter that can simplify the query by focusing only on delivery details that are or are not assigned to a delivery, preventing unnecessary complex joins when only a subset is required.

FAQ

1. What is the difference between a ‘Shipping Transaction’ and a ‘Delivery’ in Oracle Shipping? A ‘Shipping Transaction’ (represented by a wsh_delivery_detail) refers to an individual item line that has been released for shipment. A ‘Delivery’ (represented by a wsh_new_delivery) is a grouping of one or more shipping transactions that are to be shipped together to a single customer location. A single delivery can contain multiple items from one or more sales order lines.

2. How does the ‘Assigned to Delivery’ parameter help operations? Setting Assigned to Delivery to ‘No’ allows operations teams to quickly identify all shipping transaction lines that have been picked or staged but have not yet been assigned to a delivery. This is crucial for completing the packing and shipping process and preventing items from being left unshipped.

3. Can this report help identify reasons for delayed shipments? Yes. By filtering for shipments with a Delivery Status indicating delay or exception, and then comparing the Scheduled Date to the Actual Shipment Date, logistics managers can pinpoint delayed shipments. Further investigation into associated exceptions (Exception Exists) can help determine the root cause (e.g., inventory shortage, carrier issue, documentation hold).

WSH Shipping/Delivery Transactions | Oracle EBS SQL Report

This report provides details of Warehouse Shipping Transactions and Deliveries. To review details of deliveries only, set the Assigned to Delivery…