Results for “initial_pickup_location”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The APPS.WSH_DELIVERY_LINE_STATUS_V view is a reporting and integration object within the Oracle E-Business Suite Shipping Execution (WSH) module. It presents a consolidated, denormalized picture of delivery line status by joining delivery details, delivery assignments, delivery headers, pick status lookups, shipping locations, and serial number information into a single queryable structure. The view is owned by the APPS schema and carries a VALID status in both 12.1.1 and 12.2.2.

Its primary role is to expose the relationship between individual delivery detail lines and the deliveries to which they are assigned, enriched with decoded lookup meanings for delivery status and pick (released) status. This makes it suitable for operational reporting, status dashboards, and integration extracts where consumers require human-readable status values alongside source document references. By wrapping the decode logic for WSH_LOOKUPS internally, the view spares downstream developers from repeated joins and NVL handling against the lookup tables.

Underlying Base Objects

The documented base objects underlying this view are WSH_DELIVERY_ASSIGNMENTS_V (VIEW), WSH_DELIVERY_DETAILS (SYNONYM), WSH_LOCATIONS (SYNONYM), WSH_LOOKUPS (VIEW), WSH_NEW_DELIVERIES (SYNONYM), and WSH_SERIAL_NUMBERS (SYNONYM). The view text additionally references WSH_DELIVERY_ASSIGNMENTS and WSH_LOOKUPS aliased twice (WL1 for DELIVERY_STATUS, WL2 for PICK_STATUS).

• WSH_DELIVERY_DETAILS (DD) — provides the grain of the view: one row per delivery detail, including source references, quantities, UOMs, item, revision, lot, serial, weights, and organization.
• WSH_DELIVERY_ASSIGNMENTS (DA) — links each delivery detail to a delivery via DELIVERY_ID and DELIVERY_DETAIL_ID.
• WSH_NEW_DELIVERIES (DL) — supplies delivery header attributes such as name, currency, status code, and pickup/dropoff dates. The join to this table is outer (+), so details not yet assigned to a delivery still appear.
• WSH_LOOKUPS (WL1, WL2) — decodes delivery status and released (pick) status into meaningful descriptions.
• WSH_LOCATIONS (WLF, WLT) — outer-joined to resolve ship-from and ship-to location codes.
• WSH_SERIAL_NUMBERS (WSN) — outer-joined to populate quantity and serial number fields when serial-controlled tracking exists.

The view restricts output to line directions of 'O' or 'IO' (outbound and internal outbound), excluding inbound and return lines.

Key Columns

• DELIVERY_DETAIL_ID, SOURCE_LINE_ID, SOURCE_HEADER_ID — keys linking the detail back to its originating order or document line.
• SOURCE_CODE, SOURCE_HEADER_NUMBER, SOURCE_LINE_NUMBER — identify the source system and document identifiers.
• INVENTORY_ITEM_ID, REVISION, LOT_NUMBER, SERIAL_NUMBER — item and lot/serial identification, with serial resolved from WSH_SERIAL_NUMBERS or the OE-interfaced flag.
• REQUESTED_QUANTITY, SHIPPED_QUANTITY — resolved using NVL against WSN.QUANTITY so serial-driven quantities take precedence.
• DELIVERY_ID, NAME, CURRENCY_CODE, STATUS_CODE — delivery header context; the decoded delivery status meaning is also derived.
• RELEASED_STATUS and its meaning (WL2.MEANING) — the pick/release state of the line.
• SHIP_FROM / SHIP_TO location codes (WLF.UI_LOCATION_CODE, WLT.UI_LOCATION_CODE) — resolved location identifiers.
• TRACKING_NUMBER, WAYBILL, INITIAL_PICKUP_DATE, ULTIMATE_DROPOFF_DATE — logistics and transportation attributes.

Common Use Cases and Queries

Typical uses include open-delivery-line status reporting, shipped-quantity reconciliation against requested quantities, and extract feeds to warehouses or external systems requiring decoded statuses. The absence of an inner join to WSH_NEW_DELIVERIES makes the view safe for reporting on unassigned details as well.

• List all lines for a given delivery with decoded statuses:

SELECT delivery_id, name, source_header_number, source_line_number,
       inventory_item_id, requested_quantity, shipped_quantity,
       released_status, meaning AS released_status_meaning
FROM apps.wsh_delivery_line_status_v
WHERE delivery_id = :p_delivery_id;

• Reconcile shipped versus requested quantity by item across open deliveries:

SELECT inventory_item_id, SUM(requested_quantity) requested, SUM(shipped_quantity) shipped
FROM apps.wsh_delivery_line_status_v
GROUP BY inventory_item_id;

Because the view joins multiple WSH tables and includes several outer joins, performance depends on indexed access to DELIVERY_DETAIL_ID, DELIVERY_ID, and SOURCE_HEADER_ID. When querying large volumes, filtering on DELIVERY_ID or ORGANIZATION_ID is advisable to limit the result set.