Search Results as_inv_item_lov_v




Overview
Explore in AI Chat

Overview

AS_INV_ITEM_LOV_V is a read-only database view owned by the APPS schema within the AS (Sales Foundation) product family of Oracle E-Business Suite. In EBS 12.1.1 and 12.2.2, the view is documented as VALID and is catalogued in the ETRM repository. Its stated purpose is to provide a consolidated listing of Inventory Items together with their associated Sales category identifiers and interest type/code information.

The view functions as the data source behind list-of-values (LOV) and selection mechanisms in Sales Foundation flows, where an application user or concurrent program must resolve an inventory item to its Sales category and its primary and secondary interest codes. Because it joins the item master and item category assignments to the Sales Categories key flexfield, it presents a joined, denormalized row set rather than a single base table. This makes it suitable for reporting, validation, and integration queries that need item context without manually reconstructing the flexfield logic.

Underlying Base Objects

The view text is defined over the following objects, all referenced through public synonyms in the APPS schema:

The join logic fixes APPLICATION_ID to 401 and ID_FLEX_STRUCTURE_CODE to 'SALES_CATEGORIES', and restricts MIC.CATEGORY_SET_ID to 5. Interest code joins are outer joins (+), so items without a mapped interest code row are still returned.

Key Columns

  • INVENTORY_ITEM_ID — the inventory item identifier from MTL_ITEM_CATEGORIES.
  • CATEGORY_ID — the Sales category identifier assigned to the item.
  • ORGANIZATION_ID — the inventory organization scoping the item/category assignment.
  • INTEREST_TYPE_ID — the interest type id derived from SEGMENT1 of the Sales category.
  • PRIMARY_INTEREST_CODE_ID — the primary interest code (SEGMENT2), nullable due to the outer join.
  • SECONDARY_INTEREST_CODE_ID — the secondary interest code (SEGMENT3), also nullable.
  • ITEM_CONCAT_SEGS — the concatenated flexfield segments of the item for display.
  • ITEM_DESCRIPTION — the item description text.

Common Use Cases and Queries

Typical scenarios include LOV population for item selection, validating that an item belongs to the Sales Categories structure, and reporting item-to-interest-code relationships for downstream pricing or agreement logic.

Example: list items for an organization with their Sales category and interest codes:

  • SELECT inventory_item_id, organization_id, category_id, interest_type_id, primary_interest_code_id, secondary_interest_code_id, item_concat_segs, item_description FROM as_inv_item_lov_v WHERE organization_id = :org_id;

Example: retrieve description and concatenated segments for a specific item:

  • SELECT item_concat_segs, item_description FROM as_inv_item_lov_v WHERE inventory_item_id = :item_id AND organization_id = :org_id;

Because the view is a join over transactional and flexfield structures, queries should always filter by ORGANIZATION_ID and INVENTORY_ITEM_ID to avoid full scans. As with any APPS view, columns should be qualified and the object referenced through the APPS synonym rather than the underlying tables directly.