Results for “reference_id”

50+ results




Overview
Explore in AI Chat

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

Overview

The view APPS.WSH_ITM_REFERENCE_NUMBERS_V is a Shipping Execution (WSH) reporting object that provides a unified, denormalized listing of reference numbers associated with Inbound/Outbound Tracking and Management (ITM) request control records. It consolidates references that originate from two distinct application contexts: Oracle Order Management (application ID 660) and Oracle Shipping Execution deliveries (application ID 665). The view exposes the source application, the original system reference identifier, a descriptive type or organization name, the human-readable reference number, and the current status code. It is defined with SELECT DISTINCT over a UNION of two branches, so it presents a single, de-duplicated list of trackable references suitable for lookups, reporting, and integration queries keyed on the search term "reference_number."

The object is owned by the APPS schema and is marked VALID. It relies on Row Level Security through the inclusion of the HR_SECURITY and HR_GENERAL packages in its dependency chain, meaning visible rows are filtered according to the operating user's security profile.

Underlying Base Objects

The documented base objects underlying the view are HR_GENERAL (package), HR_SECURITY (package), OE_ORDER_HEADERS_ALL (synonym), ORG_ORGANIZATION_DEFINITIONS (view), WSH_ITM_REQUEST_CONTROL (synonym), and WSH_NEW_DELIVERIES (synonym). Additional objects appear in the view text, including OE_TRANSACTION_TYPES_VL (order type descriptions) and WSH_ITM_REQUEST_CONTROL as the driving table.

  • WSH_ITM_REQUEST_CONTROL — The central driver table. Both UNION branches join from it, filtering on APPLICATION_ID to distinguish order references (660) from delivery references (665) via ORIGINAL_SYSTEM_REFERENCE.
  • OE_ORDER_HEADERS_ALL / OE_TRANSACTION_TYPES_VL — Supply the order number, flow status, and order type name for the Order Management branch.
  • WSH_NEW_DELIVERIES / ORG_ORGANIZATION_DEFINITIONS — Supply the delivery identifier, status code, and organization name for the Shipping branch.
  • HR_SECURITY / HR_GENERAL — Implement organization and user-based Row Level Security on the exposed records.

Key Columns

  • APPLICATION_ID — Identifies the source application. A value of 660 corresponds to Order Management order references; 665 corresponds to Shipping delivery references.
  • REFERENCE_ID — The original system reference. For orders this is the order header ID; for deliveries it is the delivery ID.
  • ORDER_TYPE_NAME — For order rows, the transaction type name; for delivery rows, the organization name is projected into this column by the UNION.
  • REFERENCE_NUMBER — For order rows, the order number; for delivery rows, the delivery ID is projected here. This is the primary human-readable reference value.
  • STATUS_CODE — The current flow status code for orders or the status code for deliveries.

Common Use Cases and Queries

Typical uses include resolving a scanned or externally supplied reference number into its internal identifier, listing active references for a given application, and joining ITM request control data to order or delivery status.

  • Look up a specific reference number:
    SELECT application_id, reference_id, reference_number, status_code FROM apps.wsh_itm_reference_numbers_v WHERE reference_number = :p_ref_num;
  • List all order-based references:
    SELECT reference_id, reference_number, order_type_name, status_code FROM apps.wsh_itm_reference_numbers_v WHERE application_id = 660;
  • List all delivery-based references:
    SELECT reference_id, reference_number, status_code FROM apps.wsh_itm_reference_numbers_v WHERE application_id = 665;