Results for “cross_reference”

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

Overview

The WSH_OPSM_ASN_ITEMS_COMM_V view is a Shipping Execution (WSH) public view owned by the APPS schema. Its name indicates an interface intended for the Oracle Product Information Management / Order Promising and Shipping integration layer — specifically the delivery of Advance Shipment Notice (ASN) item-level information to external or downstream commerce applications. The OPSM segment references the Oracle Product Support/Shipping messaging context, ASN denotes Advance Shipment Notice content, and ITEMS_COMM indicates the item and commercial (customer-facing) attributes aligned to the ASN structure. It is a denormalized read-only projection that flattens delivery, order, item, customer, and hazard information into a single row per delivery detail line, suitable for outbound message generation, integration staging, and operational reporting.

Underlying Base Objects

The view is defined over eleven documented base objects spanning the Shipping, Inventory, Order Management, and Purchasing schemas within the APPS owner. Shipping objects include WSH_DELIVERY_DETAILS, WSH_DELIVERY_ASSIGNMENTS, and WSH_NEW_DELIVERIES, which supply delivery detail identifiers, container parent relationships, delivery numbers, and shipment confirmation dates. Inventory and item master objects include MTL_SYSTEM_ITEMS, MTL_SYSTEM_ITEMS_B_KFV, MTL_ITEM_FLEXFIELDS, and MTL_PARAMETERS, providing container flags, container type codes, customer order enabled flags, and item descriptions keyed by organization. Customer and cross-reference objects include MTL_CUSTOMER_ITEMS and MTL_CROSS_REFERENCES_VL, which resolve customer part numbers and descriptions. Commercial and hazard attributes come from MTL_COMMODITY_CODES and PO_HAZARD_CLASSES. The view performs outer-join style resolution against these referenced objects to produce a consolidated ASN commercial record.

Key Columns

• DELIVERY_ID, DELIVERY_DETAIL_ID, CONTAINER_INSTANCE_ID — delivery header, detail line, and container parent identifiers from WSH_DELIVERY_DETAILS and WSH_DELIVERY_ASSIGNMENTS.
• ITEM_ID, CUSTOMER_ITEM_ID, SUPPLIER_PART_NUMBER, CUSTOMER_PART_NUMBER — internal item and customer-facing identifiers resolved via MTL_CUSTOMER_ITEMS and MTL_CROSS_REFERENCES_VL.
• SOURCE_CODE, SALES_ORDER_NUMBER, ORDER_HEADER_ID, ORDER_LINE_ID, ORDERTYPE — order-line origin and source document references.
• SOLDTO_CUSTOMER_ID, SOLDTO_CONTACT_ID, DESTINATION_CONT_ID, SHIP_TO_CONTACT_ID — customer and contact role identifiers for the ASN parties.
• SHIPPED_QUANTITY, ORDERED_QUANTITY, CANCELLED_QUANTITY, SHIPMENT_CONFIRMED_DATE — shipment quantity and confirmation data used for ASN reconciliation.
• REQUESTED_DATE, CUSTOMER_DOCK_CODE, CUST_PO_NUMBER, SHIPMENT_PRIORITY_CODE — scheduling, dock, and priority attributes.
• HAZARDOUS_MATERIAL_CODE_INT, HAZARD_CLASS_INT — hazard classification populated from PO_HAZARD_CLASSES, with a literal 'UN' code convention.
• CONTAINER_ITEM_FLAG, CONTAINER_TYPE_CODE_INT, COMMODITY_CODE_INT, CUSTOMER_ORDER_FLAG — flag and code attributes from MTL_SYSTEM_ITEMS and MTL_COMMODITY_CODES, suffixed _INT for integration.

Common Use Cases and Queries

The view is typically queried to extract ASN content for a specific delivery or set of delivery details when integrating with external commerce or messaging systems.

SELECT SALES_ORDER_NUMBER, DELIVERY_DETAIL_ID, ITEM_ID,
       CUSTOMER_PART_NUMBER, SHIPPED_QUANTITY, ORDERED_QUANTITY,
       SHIPMENT_CONFIRMED_DATE, HAZARD_CLASS_INT
FROM   APPS.WSH_OPSM_ASN_ITEMS_COMM_V
WHERE  DELIVERY_ID = :p_delivery_id
ORDER BY CUST_PRODUCTION_SEQ_NUM;

Integration developers join the view back to WSH_DELIVERY_DETAILS or WSH_NEW_DELIVERIES for additional shipment context, and filter on ORGANIZATION_ID to scope results to the shipping warehouse. Because the view is read-only, all consumption is limited to SELECT statements; no DML is permitted against it.