Search Results inv_item_definition_level




Overview
Explore in AI Chat

Overview

APPS.OE_ITEMS_V is a reporting and integration view in Oracle E-Business Suite (validated across 12.1.1 and 12.2.2) that presents the relationship between customer-defined item numbers and the corresponding internal Oracle Inventory items. It consolidates customer item definitions, customer item cross-references, inventory item master attributes, shipping site context, and customer address information into a single flattened result set. The view is central to order management and order-to-cash reporting because it allows users to trace how a customer's own item number (and description) maps to an internal inventory item and its cross-reference rank.

The view is particularly significant for the search term inv_item_definition_level, which appears as the column ITEM_DEFINITION_LEVEL. This value is derived from the FND_LOOKUP_VALUES_VL lookup type INV_ITEM_DEFINITION_LEVEL, joined to MTL_CUSTOMER_ITEMS.ITEM_DEFINITION_LEVEL. It indicates the granularity at which a customer item is defined—for example, at the customer level, customer site level, or inventory item level—and is therefore critical for interpreting how a customer item number is scoped.

Underlying Base Objects

The view is owned by APPS and is defined over a UNION ALL of at least two query branches. The documented base objects include:

Key Columns

  • ITEM / ITEM_ID — the customer item number and customer item ID from MTL_CUSTOMER_ITEMS.
  • ITEM_DESCRIPTION — customer item description; the view filters out rows where this is null.
  • INVENTORY_ITEM_ID / INVENTORY_ITEM — the internal inventory item ID and concatenated segments.
  • ITEM_IDENTIFIER_TYPE — literal 'CUST', indicating a customer item identifier.
  • SOLD_TO_ORG_ID — customer identifier from the customer item record.
  • ORGANIZATION_ID — inventory organization context for the internal item.
  • ITEM_STATUS / CROSS_REF_STATUS — decoded ACTIVE or INACTIVE status for the customer item and its cross-reference.
  • Address / CUST_ADDRESS — assembled ship-to address, with formatting controlled by lookup codes.
  • ITEM_DEFINITION_LEVEL — the decoded meaning of the customer item definition level lookup (INV_ITEM_DEFINITION_LEVEL).
  • RANK — the cross-reference preference number from MTL_CUSTOMER_ITEM_XREFS.

Common Use Cases and Queries

Typical scenarios include customer item cross-reference reporting, order entry validation of customer-to-inventory item mappings, and integration feeds that must resolve a customer part number to an internal item. The view is also used to audit items by definition level and ship-to context.

Sample query to identify inventory items by item definition level:

SELECT item,
       item_description,
       inventory_item,
       item_definition_level,
       item_status,
       rank
FROM   apps.oe_items_v
WHERE  item_definition_level = 'CUSTOMER'
ORDER BY inventory_item, rank;

Sample query to find active cross-references for a sold-to organization:

SELECT inventory_item,
       item,
       cross_ref_status,
       rank
FROM   apps.oe_items_v
WHERE  sold_to_org_id = :p_customer_id
AND    cross_ref_status = 'ACTIVE'
ORDER BY inventory_item, rank;