Search Results order_ref_value

Overview

XDP_OE_ORDER_DETAILS_V is a reporting and integration view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the XDP (Provisioning) product family. ETRM documents the object as VALID in both 12.1.1 and 12.2.2, with the stated purpose that it "contains the details for a particular order." The view flattens order header and order line information into a single denormalized result set, so consumers do not need to join the two base entities themselves. It is the canonical source for order-detail extracts used by provisioning workflows, service-fulfillment interfaces, and operational reporting. Users searching for telephone_number reach this view because TELEPHONE_NUMBER is one of the header-level attributes projected by the view text, making it the appropriate access point when contact data must be retrieved alongside order and line context.

Underlying Base Objects

The documented base objects referenced by this view are XDP_OE_ORDER_HEADERS and XDP_OE_ORDER_LINES, both exposed in the APPS schema as synonyms. The SELECT joins them on ORDER_NUMBER, with the version comparison expressed defensively using NVL on both sides: XOEH.ORDER_NUMBER = XOEL.ORDER_NUMBER and NVL(XOEH.ORDER_VERSION,'X') = NVL(XOEL.ORDER_VERSION,'X'). This construction permits header rows with a null ORDER_VERSION to match corresponding line rows, avoiding the row loss that a plain equality predicate would cause. Because the join is a header-to-line relationship, the view is effectively at line granularity: one row per order line, with the parent header attributes repeated on each row.

Key Columns

The view exposes header columns prefixed conceptually as order-level and line columns as line-level. Identification and versioning columns include ORDER_NUMBER, ORDER_VERSION, LINE_NUMBER, LINE_ITEM_NAME, LINE_ITEM_VERSION, and LINE_ITEM_TYPE. Flags such as IS_WORK_ITEM_FLAG (aliased from IS_WORKITEM_FLAG) and PROVISIONING_REQUIRED_FLAG indicate fulfillment behaviour, supported by ORDER_PROVISIONING_DATE, LINE_PROVISIONING_DATE, and PROVISIONING_SEQUENCE. Order classification and state are represented by ORDER_TYPE, ORDER_ACTION, LINE_ITEM_ACTION, ORDER_SOURCE, ORDER_PRIORITY, LINE_PRIORITY, ORDER_STATUS, and LINE_STATUS.

• TELEPHONE_NUMBER — header-level contact number for the order, sourced from XDP_OE_ORDER_HEADERS; the column most frequently targeted in searches against this view.
• CUSTOMER_NAME / CUSTOMER_ID — trading-party identity carried from the header.
• SERVICE_PROVIDER_ID, SP_ORDER_NUMBER, SP_USERID — service-provider correlation attributes used in provisioning handshakes.
• SDP_ORDER_ID, RELATED_ORDER_ID, ORDER_REF_NAME / ORDER_REF_VALUE — cross-references to the source order system and external order references.
• ORG_ID — operating unit, the mandatory predicate for multi-org secured queries.
• STARTING_NUMBER / ENDING_NUMBER — line-level number ranges, typical of telephony and subscription provisioning.

Common Use Cases and Queries

The view is typically queried in three ways: locating an order by contact telephone number, extracting line-level provisioning detail for a known order, and feeding interface programs that require header and line attributes in one cursor.

• Retrieve the header contact number and current status for a known order:

  SELECT ORDER_NUMBER, ORDER_VERSION, ORDER_STATUS,
         TELEPHONE_NUMBER, CUSTOMER_NAME
  FROM   APPS.XDP_OE_ORDER_DETAILS_V
  WHERE  ORDER_NUMBER = :p_order_number;

• Find orders associated with a telephone number within an operating unit:

  SELECT ORDER_NUMBER, LINE_NUMBER, LINE_STATUS,
         TELEPHONE_NUMBER, ORDER_ACTION
  FROM   APPS.XDP_OE_ORDER_DETAILS_V
  WHERE  TELEPHONE_NUMBER = :p_telephone_number
  AND    ORG_ID = :p_org_id;

• Extract all lines requiring provisioning for reconciliation:

  SELECT ORDER_NUMBER, LINE_NUMBER, LINE_ITEM_NAME,
         PROVISIONING_SEQUENCE, ORDER_PROVISIONING_DATE,
         LINE_PROVISIONING_DATE
  FROM   APPS.XDP_OE_ORDER_DETAILS_V
  WHERE  PROVISIONING_REQUIRED_FLAG = 'Y'
  AND    ORG_ID = :p_org_id
  ORDER BY ORDER_NUMBER, PROVISIONING_SEQUENCE;

Queries should always constrain ORG_ID to satisfy operating-unit security, and should treat ORDER_VERSION as optional in predicates because of the NVL handling in the view definition.