Search Results wsh_itm_reference_lines_v




Overview
Explore in AI Chat

Overview

WSH_ITM_REFERENCE_LINES_V is an APPS-owned database view in Oracle E-Business Suite, assigned to the WSH (Shipping Execution) product module. It is a conventional relational view, not a materialized one, and is marked VALID in the ETRM repository for release 12.2.2 with continuity back to 12.1.1. The view serves as a bridge between Oracle Order Management (OE) order lines and the shipping integration control records held in WSH_ITM_REQUEST_CONTROL, exposing order line identifiers and status information under the naming conventions expected by item/shipping integration consumers.

Its central purpose is to resolve a shipping-side reference — the original system header and line reference stored on a WSH integration request — into a readable order line, including the concatenated order line number and the order line's flow status. Because the view is constrained to APPLICATION_ID = 660, it is effectively scoped to Oracle Order Management as the source application, meaning consumers querying it obtain only order-line-originated references rather than references originating from other source applications. The view is therefore principally useful to parties that must reconcile shipped or transmitted items back to their originating sales order lines and need to observe the current lifecycle state of those lines.

Underlying Base Objects

The documented view text defines the query over three base objects joined together, with one package-based function:

  • OE_ORDER_HEADERS_ALL — the order header, joined on HEADER_ID. It links the order line to its parent order and supplies the header identifier that must match the integration control record.
  • OE_ORDER_LINES_ALL — the order line, supplied under the alias OL. It contributes the line number, shipment number, option, component, and service number attributes, as well as the flow status code.
  • WSH_ITM_REQUEST_CONTROL — the shipping item reference control record, supplied under the alias WRC. It holds ORIGINAL_SYSTEM_REFERENCE and ORIGINAL_SYSTEM_LINE_REFERENCE, which correspond to the order header and line identifiers, and the APPLICATION_ID that constrains the view.
  • OE_FLEX_UTIL — a package whose GET_CONCAT_VALUE function assembles the multi-segment reference line number from the individual order line key attributes.

The join predicates are: OH.HEADER_ID = OL.HEADER_ID; OH.HEADER_ID = WRC.ORIGINAL_SYSTEM_REFERENCE; 660 = WRC.APPLICATION_ID; and WRC.ORIGINAL_SYSTEM_LINE_REFERENCE = OL.LINE_ID. A DISTINCT operator is applied to the result set, indicating that the underlying join can produce duplicate rows that the view intentionally collapses.

Key Columns

  • APPLICATION_ID — the source application identifier, fixed at 660 (Order Management) by the view predicate.
  • REFERENCE_HEADER_ID — sourced from WRC.ORIGINAL_SYSTEM_REFERENCE; corresponds to the order header identifier.
  • REFERENCE_LINE_ID — sourced from WRC.ORIGINAL_SYSTEM_LINE_REFERENCE; corresponds to the order line identifier.
  • REFERENCE_LINE_NUMBER — the derived, concatenated line identifier produced by OE_FLEX_UTIL.GET_CONCAT_VALUE from the line, shipment, option, component, and service numbers.
  • FLOW_STATUS_CODE — taken directly from OL.FLOW_STATUS_CODE on OE_ORDER_LINES_ALL, representing the current flow (lifecycle) status of the order line. This is the column returned for the search term "FLOW_STATUS_CODE" and is the only status attribute the view exposes.

Common Use Cases and Queries

The view is typically used to report the order line identity and flow status for shipping integration references. A representative query lists references with their line numbers and status:

  • Status reconciliation: SELECT reference_header_id, reference_line_id, reference_line_number, flow_status_code FROM wsh_itm_reference_lines_v WHERE flow_status_code = 'CLOSED'; — identifies integration references whose order lines have reached a terminal flow state.
  • Line lookup: SELECT reference_line_number, flow_status_code FROM wsh_itm_reference_lines_v WHERE reference_line_id = :p_line_id; — resolves a known order line to its readable number and status.
  • Order-level roll-up: SELECT reference_header_id, COUNT(*) FROM wsh_itm_reference_lines_v GROUP BY reference_header_id; — counts integration references per originating order header.

Because the view is read-only and internally constrained to application 660, it cannot be used to retrieve references from other source applications without modification. Reports and integrations should treat FLOW_STATUS_CODE as the authoritative line-state indicator and note that DISTINCT may suppress duplicate rows where a single line has multiple matching control records.