Search Results sales_categories




Overview
Explore in AI Chat

Overview

APPS.AS_INV_ITEM_LOV_V is a reporting view in Oracle E-Business Suite (documented for 12.1.1 and 12.2.2) that presents inventory items together with their Sales category assignment and the associated interest type and interest code identifiers. It is used primarily to support list-of-values (LOV) and inquiry functionality in the Oracle Advanced Collections / Trade Management (AS) product family, where items must be qualified by their Sales category structure before being presented to the user.

The view is significant because it bridges Oracle Inventory master data (MTL_ITEM_CATEGORIES, MTL_CATEGORIES_B, MTL_SYSTEM_ITEMS_VL) with the Collections interest configuration tables (AS_INTEREST_TYPES_B, AS_INTEREST_CODES_B). Its role in reporting and integration is to expose a single, flattened, ready-to-join row per item/category/organization combination so that interest-related logic can be resolved without the caller having to reconstruct the category key-to-segment mapping.

Underlying Base Objects

The view is defined over the following documented base objects, all accessed through APPS synonyms unless noted:

  • MTL_ITEM_CATEGORIES (SYNONYM) — the item-to-category assignment fact table, aliased MIC.
  • MTL_CATEGORIES_B (SYNONYM) — the category definition table holding the segment values, aliased MC.
  • FND_ID_FLEX_STRUCTURES (SYNONYM) — the key flexfield structure definition, aliased FIFS, used to resolve the Sales category structure.
  • MTL_SYSTEM_ITEMS_VL (VIEW) — the item master view supplying concatenated segments and description, aliased ITEMS.
  • AS_INTEREST_TYPES_B (SYNONYM) — interest type definitions, aliased IT.
  • AS_INTEREST_CODES_B (SYNONYM) — interest code definitions, referenced twice as IC and IC2.

The joins are constrained tightly to the Sales category context: FIFS.ID_FLEX_CODE = 'MCAT', FIFS.APPLICATION_ID = 401, and FIFS.ID_FLEX_STRUCTURE_CODE = 'SALES_CATEGORIES', with MIC.CATEGORY_SET_ID = 5. The category segment values are converted to text and matched against the interest type and interest code identifiers, with the two AS_INTEREST_CODES_B joins expressed as outer joins.

Key Columns

  • INVENTORY_ITEM_ID — inventory item identifier from the category assignment.
  • CATEGORY_ID — the Sales category identifier assigned to the item.
  • ORGANIZATION_ID — the inventory organization context of the assignment.
  • INTEREST_TYPE_ID — resolved interest type, derived by matching category segment 1.
  • INTEREST_CODE_ID — the primary interest code (from IC), matched against category segment 2 via outer join.
  • The second INTEREST_CODE_ID — the secondary interest code (from IC2), matched against category segment 3 via outer join.
  • ITEM_CONCAT_SEGS — concatenated item segments for display in LOVs.
  • ITEM_DESCRIPTION — item description for display in LOVs.

Common Use Cases and Queries

The view supports LOV population, item qualification by Sales category, and reconciliation of item category segments to interest configuration. A typical query retrieves items with their resolved interest attributes for a given organization:

  • Populating an item LOV restricted to Sales categories: SELECT INVENTORY_ITEM_ID, ITEM_CONCAT_SEGS, ITEM_DESCRIPTION FROM APPS.AS_INV_ITEM_LOV_V WHERE ORGANIZATION_ID = :org_id.
  • Resolving interest type and code for an item: SELECT INVENTORY_ITEM_ID, INTEREST_TYPE_ID, INTEREST_CODE_ID FROM APPS.AS_INV_ITEM_LOV_V WHERE INVENTORY_ITEM_ID = :item_id.
  • Auditing category-to-interest mapping across an organization by joining to AS_INTEREST_TYPES_B and AS_INTEREST_CODES_B on the exposed IDs.

Because the view enforces the Sales category structure and category set 5 internally, queries against it are pre-filtered and do not require the caller to re-specify those conditions.