Search Results schedule_reference_num

Overview

WSH_OPSM_ASN_ITEMS_SPECIFIC_V is an Oracle E-Business Suite dictionary view owned by the APPS schema within the Shipping Execution (WSH) product family. In the context of the Oracle Order Management and Shipping Execution integration — commonly referred to as OPSM (Order Processing and Shipping Management) — the view consolidates order line, schedule, and customer cumulative key information into a single denormalized result set that can be consumed by downstream shipping and Advanced Shipment Notice (ASN) processing logic.

The view is defined with a status of VALID in both EBS 12.1.1 and 12.2.2. Its primary utility is to present order line attributes alongside associated RLM (Release Management) schedule header, schedule line, and cumulative key attributes, projecting an integrated picture of a specific order line for ASN item construction. The view is not a base table; it is a read-only projection over several transactional tables and is typically referenced by OPSM integration code, concurrent programs, or shipping-related queries that need to correlate order management lines with release management scheduling data.

Underlying Base Objects

The documented ETRM metadata for release 12.2.2 identifies five referenced base objects, all resolved through SYNONYM objects in the APPS schema:

• OE_ORDER_HEADERS_ALL — the Order Management order header, joined on HEADER_ID to the order line, supplying order-level attributes such as ORDER_DATE_TYPE_CODE.
• OE_ORDER_LINES_ALL — the Order Management order line, which is the driver table (outer-joined source) of the view and the source of the much of the projected data, including SPLIT_FROM_LINE_ID, LINE_ID, LINE_NUMBER, PROMISE_DATE, and UNIT_SELLING_PRICE.
• RLM_SCHEDULE_HEADERS_ALL — the release management schedule header, joined conditionally via DECODE on SOURCE_DOCUMENT_TYPE_ID and SOURCE_DOCUMENT_ID, supplying SCHED_GENERATION_DATE and SCHEDULE_REFERENCE_NUM.
• RLM_SCHEDULE_LINES_ALL — the release management schedule line, joined via DECODE on SOURCE_DOCUMENT_TYPE_ID and SOURCE_DOCUMENT_LINE_ID, supplying customer item revision, packaging, industry attribute (pull signal), and customer PO release data.
• RLM_CUST_ITEM_CUM_KEYS_ALL — the customer item cumulative key table, joined through VEH_CUS_ITEM_CUM_KEY_ID, supplying CUM_START_DATE.

The join predicates are largely outer joins (denoted by the (+) syntax), with the exception of the mandatory OE_ORDER_HEADERS_ALL to OE_ORDER_LINES_ALL relationship. The DECODE constructs restrict the RLM joins to lines whose SOURCE_DOCUMENT_TYPE_ID equals 5, ensuring that schedule information is only attached to those order lines that originate from an RLM schedule document.

Key Columns

• SOURCE_CODE — literal value 'OE', identifying the source system as Order Management.
• ORDER_HEADER_ID / ORDER_LINE_ID — identifiers linking back to OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL.
• SPLIT_FROM_LINE_ID — the line from which this order line was split; central to split-line traceability and to the search term that produced this article.
• LINK_TO_LINE_ID — the line to which this line is linked, supporting line association logic.
• ORIGINAL_SYSTEM_LINE_REFERENCE — the originating system's reference for the line.
• ORDER_LINE_NUMBER — the display line number, converted to character.
• PROMISE_DATE, UNIT_SELLING_PRICE, ITEM_TYPE_CODE_INT, OPTION_FLAG, COMPONENT_CODE — standard order line attributes used in pricing, configuration, and fulfillment logic.
• SCHED_GENERATION_DATE, SCHEDULE_REFERENCE_NUM — schedule-level identifiers from the RLM schedule header.
• CUSTOMER_ITEM_REVISION, PACKAGING_CODE_EXT, PRIME_CONTRCTR_PART_EXT — customer-facing item and packaging attributes from the schedule line.
• PULLSIGNAL_REF_NUM, PULLSIGNAL_START_SERIAL, PULLSIGNAL_END_SERIAL — pull signal attributes mapped from INDUSTRY_ATTRIBUTE4 through 6, relevant to demand-driven replenishment.
• CUST_PO_RELEASE_NUM, CUST_PO_DATE, CUST_PO_LINE_NUM — customer purchase order referencing data; CUST_PO_LINE_NUM uses NVL to fall back between order line and schedule line values.
• SUPPLIER_ITEM_EXT, CUM_START_DATE — supplier item identifier and cumulative start date from the cumulative key table.

Common Use Cases and Queries

Because SPLIT_FROM_LINE_ID is exposed directly, this view is frequently queried to trace split line lineage in an ASN or shipping context. A representative query follows:

• SELECT order_line_id, split_from_line_id, link_to_line_id, order_line_number FROM wsh_opsm_asn_items_specific_v WHERE split_from_line_id IS NOT NULL;
• SELECT order_header_id, order_line_number, sched_reference_num, cust_po_line_num FROM wsh_opsm_asn_items_specific_v WHERE order_header_id = :p_header_id;
• SELECT v.order_line_id, v.promise_date, v.unit_selling_price FROM wsh_opsm_asn_items_specific_v v WHERE v.source_code = 'OE' AND v.schedule_reference_num IS NOT NULL;

Typical scenarios include reconciling ASN item lines back to their originating order lines, identifying the parent line of a split shipment, and reporting on pull signal and cumulative key data associated with scheduled releases. Because the RLM joins are outer and conditioned on SOURCE_DOCUMENT_TYPE_ID = 5, consumers must expect NULL values in schedule and cumulative columns for order lines not sourced from an RLM schedule, and should apply appropriate NVL or outer-join semantics in their reporting logic.