Search Results shipping_status




Overview
Explore in AI Chat

Overview

APPS.SO_DELIVERIES_V is an Oracle E-Business Suite shipping and delivery reporting view that consolidates order header, order line, delivery, and departure information into a single queryable structure. Its primary purpose is to expose the shipment lifecycle of sales order lines that have been assigned to a delivery, along with the status of both the delivery and the associated departure. Because the view joins order management tables with Warehouse Management (WSH) delivery tables, it is commonly used for order-to-shipment tracking, carrier and waybill reporting, and integration extracts where shipment-level detail is required.

The view is defined with a DISTINCT clause, reflecting that a given order line may relate to multiple picking or delivery records, and the distinct projection prevents duplicated business rows from appearing in reporting output. A significant portion of the view's semantics relates to shipping status: both SO_LOOKUPS aliases (LUP1 and LUP2) are restricted to LOOKUP_TYPE = 'SHIPPING_STATUS', meaning the departure status and delivery status columns are decoded from the shipping status lookup set.

Underlying Base Objects

The view draws from the following documented base objects:

  • SO_HEADERS_ALL — order header attributes including order number, customer, and default ship method.
  • SO_LINES_ALL — order line attributes including ship method, ship-to site, ship-to contact, ATO flags, and line type.
  • SO_LINE_DETAILS — the assignment of a line to a warehouse, delivery, and departure, with shipable and DPW assigned flags.
  • WSH_DELIVERIES — delivery header records providing delivery name, status code, waybill, and expected arrival date.
  • WSH_DEPARTURES — departure records providing departure name, status code, and bill of lading.
  • SO_LOOKUPS — lookup values for shipping status assignment.
  • MTL_PARAMETERS — organization definition used to derive the warehouse organization code.

While the documented metadata also lists SO_PICKING_HEADERS_ALL, SO_PICKING_LINES_ALL, SO_PICKING_LINE_DETAILS, and FND_GLOBAL as referenced objects, the extracted view text joins the tables above. The view is owned by APPS and references synonyms over the underlying application tables.

Key Columns

  • order_number — the sales order number from the header.
  • warehouse_id / organization_code — the shipping warehouse and its organization code.
  • delivery_id / delivery_name / delivery_status_code / delivery_status — the delivery and its decoded shipping status.
  • departure_id / departure_name / departure_status_code / departure_status — the departure and its decoded shipping status.
  • departure_date — derived using DECODE on departure status: actual date when Closed, planned dates for Planned or Open.
  • waybill / bill_of_lading — carrier and transport documentation references.
  • expected_arrival_date — the projected arrival date of the delivery.
  • freight_carrier_code — ship method, resolved from the line first, then the header via NVL.
  • ship_to_site_use_id / ship_to_contact_id — resolved from the line, then the header, using NVL.
  • dpw_assigned_flag — indicates the delivery/picking wave assignment flag from line details.

Common Use Cases and Queries

Typical use cases include shipment status dashboards, carrier and waybill reporting, arrival-date forecasting, and shipment data extracts. The shipping_status lookup surfaced through delivery_status and departure_status is the primary filter for operational reporting.

For example, to list open deliveries awaiting departure:

SELECT order_number,
       organization_code,
       delivery_name,
       delivery_status,
       departure_status,
       departure_date
FROM   apps.so_deliveries_v
WHERE  delivery_status = 'Open';

To report shipments by carrier and waybill:

SELECT order_number,
       freight_carrier_code,
       waybill,
       bill_of_lading,
       expected_arrival_date
FROM   apps.so_deliveries_v
WHERE  waybill IS NOT NULL
ORDER  BY order_number;

Because the view filters on order category P and R, regular line types, and excludes service items and fully cancelled lines, queries against it return only shippable, non-service order lines assigned to a delivery or departure. This makes it well suited for reporting, but the DISTINCT projection and multi-table joins mean it should be used carefully in high-volume transactions, with appropriate indexes on the underlying tables.