Search Results oe_return_items_v




Overview
Explore in AI Chat

Overview

OE_RETURN_ITEMS_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It belongs to the Order Management (ONT) product family and was introduced specifically to support the Return Items list of values (LOV) used by the Returns and Order Management windows. The view presents a flattened, query-ready projection of customer item definitions cross-referenced to internal inventory items, enriched with sold-to addressing information and item status flags.

Because the view resolves the customer item to internal inventory item relationship through the MTL_CUSTOMER_ITEM_XREFS intersection table, it allows forms, concurrent programs, and custom integrations to present a single LOV row that carries both the customer-facing item identifier and the internal inventory item identifier. This makes it a convenient integration surface for returns processing, where the user typically knows the customer item number but the application must resolve the correct inventory item and master organization context.

Underlying Base Objects

The view is defined as a multi-table join across customer item, cross-reference, item master, and party/address objects. The documented referenced base objects are:

The joins are predominantly outer joins (denoted by the (+) operator) from MTL_CUSTOMER_ITEMS outward, so customer items are not dropped when address, lookup, or description data is missing. The item master join, however, is effectively restricted to the master organization through the OE_SYS_PARAMETERS call.

Note that the documented metadata also lists MTL_CROSS_REFERENCES and MTL_CROSS_REFERENCE_TYPES as referenced objects, reflecting the cross-reference framework used by the MTL_CUSTOMER_ITEM_XREFS structure.

Key Columns

  • ITEM — the customer item number (CITEMS.CUSTOMER_ITEM_NUMBER), the value typically searched in the LOV.
  • ITEM_ID — the customer item identifier (CITEMS.CUSTOMER_ITEM_ID).
  • ITEM_DESCRIPTION — customer item description, with NVL fallback to the internal item description.
  • INVENTORY_ITEM_ID — internal inventory item identifier resolved from the cross-reference.
  • INVENTORY_ITEM — concatenated item flexfield segments (CONCATENATED_SEGMENTS) for the internal item.
  • ITEM_IDENTIFIER_TYPE — literal 'CUST', indicating the row represents a customer item identifier.
  • SOLD_TO_ORG_ID — customer identifier from the customer item record.
  • ORGANIZATION_ID — item master organization identifier.
  • ITEM_STATUS and CROSS_REF_STATUS — decoded ACTIVE/INACTIVE flags from the customer item and cross-reference records respectively.
  • ADDRESS and CUST_ADDRESS — ship-to address elements assembled from HZ_LOCATIONS and, depending on lookup code, city/state/postal or an address meaning.
  • ITEM_DEFINITION_LEVEL — the decoded lookup meaning for the item definition level.
  • RANK — preference number from the cross-reference, useful for ordering multiple cross-reference candidates.

Common Use Cases and Queries

The primary use case is populating a Returns LOV, where a user searches by customer item number and the form resolves the inventory item. A representative query follows:

SELECT item, item_id, item_description, inventory_item_id, inventory_item
FROM   apps.oe_return_items_v
WHERE  UPPER(item) LIKE UPPER(:p_search) || '%'
AND    item_status = 'ACTIVE'
ORDER  BY item;

A second common scenario resolves a known customer item to its internal inventory item for returns validation or integration staging:

SELECT customer_item_number, inventory_item_id, inventory_item,
       organization_id, cross_ref_status
FROM   apps.oe_return_items_v
WHERE  item_id = :p_customer_item_id
ORDER  BY rank;

Reporting queries may filter on organization or active status, for example listing all active customer items for a given sold-to organization with their ship-to addresses. Because the view reads from overlapping customer item and TCA structures, callers should expect that a single customer item may appear on multiple rows when multiple cross-references or address records qualify; the RANK column is the intended ordering key when picking a preferred row. The view should be treated as a query-only object, consistent with standard Oracle EBS practice for seeded views.