Results for “load_sequence_number”

50+ results




Overview
Explore in AI Chat

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

Overview

WSH_OPSM_ASN_ITEM_DETAILS_V is a seed data dictionary view owned by the APPS schema in Oracle E-Business Suite, registered as VALID and delivered under the WSH — Shipping Execution product family. The view exposes Advance Shipping Notice (ASN) item-level detail for outbound deliveries processed through Oracle Shipping Execution. It is one of the supporting objects used by the Order Processing and Shipping Management (OPSM) inbound/outbound messaging and integration layer, providing a flattened, reporting-friendly projection of delivery assignment, delivery detail, lot, and serial information that downstream consumers can query without reconstructing the underlying multi-table joins. Because it is defined over transactional shipping tables rather than storing data itself, its content reflects the live state of delivery details at the moment of query, making it suitable for operational reporting, interface extraction, and reconciliation of shipped quantities against lot and serial attributes.

Underlying Base Objects

The view is defined over six documented base objects, all referenced through APPS synonyms: WSH_DELIVERY_ASSIGNMENTS, WSH_DELIVERY_DETAILS, WSH_SERIAL_NUMBERS, MTL_SERIAL_NUMBERS_TEMP, MTL_LOT_NUMBERS, and MTL_PARAMETERS. WSH_DELIVERY_ASSIGNMENTS and WSH_DELIVERY_DETAILS form the driving join, with delivery detail joined to delivery assignment on DELIVERY_DETAIL_ID, and delivery detail also acting as the mandatory join to MTL_PARAMETERS on ORGANIZATION_ID to obtain the organization code. The joins to WSH_SERIAL_NUMBERS, MTL_SERIAL_NUMBERS_TEMP, and MTL_LOT_NUMBERS are outer joins, so item detail rows are retained even when serial or lot data is absent. The view text includes an explicit RULE hint and arithmetic coercions (for example, WDD.DELIVERY_DETAIL_ID+0) that suppress index usage and enforce the legacy rule-based optimizer path; this is characteristic of older OPSM integration views and should be noted when assessing performance on 12.1.1 or 12.2.2. Rows are restricted to non-container delivery details (CONTAINER_FLAG = 'N') with a shipped quantity greater than zero.

Key Columns

Common Use Cases and Queries

Typical uses include ASN and shipment notification extraction, reconciliation of shipped quantities against lot and serial attributes, and lot genealogy reporting for regulated or serialized items. Because the view filters to non-container lines with positive shipped quantity, it is well suited to item-level ASN feeds.

For example, to retrieve shipped item details for a specific delivery:

  • SELECT delivery_id, delivery_detail_id, organization_code, lot_number, shipped_quantity, shipping_quantity_uom, from_serial_number, to_serial_number FROM wsh_opsm_asn_item_details_v WHERE delivery_id = :p_delivery_id;

To summarize shipped quantities by organization and lot for interface reconciliation:

  • SELECT organization_code, lot_number, SUM(shipped_quantity) total_shipped FROM wsh_opsm_asn_item_details_v GROUP BY organization_code, lot_number;

To identify lots approaching expiration for a shipment population:

  • SELECT delivery_id, lot_number, expiration_date, shipped_quantity FROM wsh_opsm_asn_item_details_v WHERE expiration_date IS NOT NULL ORDER BY expiration_date;

Given the RULE hint and outer joins, queries should be filtered narrowly and performance validated against the target release before being embedded in high-volume integrations.