Search Results category_concat_segs




Overview
Explore in AI Chat

Overview

The APPS.MTL_ITEM_CATEGORIES_V view exposes the relationship between inventory items and the categories to which they are assigned, joining that assignment data to its parent category set and category definitions. It is owned by the APPS schema and belongs to the INV (Inventory) product family. The EBS Reference Manual metadata flags this object with the description "10SC ONLY," indicating that its use is scoped to a specific configuration or localization context rather than being a general-purpose global view. Its status is VALID in both 12.1.1 and 12.2.2.

Functionally, the view answers the question "which items belong to which categories, and within which category set?" while enriching the raw assignment with descriptive attributes such as the category set name, the display meaning of the control level, and the concatenated category segment value. This makes it suitable for reporting and integration scenarios where a flat, denormalized view of item-to-category assignments is preferable to joining the underlying normalized tables manually. Typical consumers include custom concurrent programs, Discoverer or BI Publisher reports, inbound/outbound interfaces, and ad hoc SQL used by functional and technical analysts.

Underlying Base Objects

The view is defined over five referenced objects as documented in the ETRM 12.2.2 metadata:

  • MTL_ITEM_CATEGORIES (SYNONYM) — the primary driver table, holding the item-to-category assignment rows (inventory item, organization, category set, and category identifiers).
  • MTL_CATEGORY_SETS_B (SYNONYM) — the base table for category set definitions, supplying attributes such as structure, validation flag, control level, and the multiple-item-assignment flag.
  • MTL_CATEGORY_SETS_TL (SYNONYM) — the translated (language) table for category sets, providing CATEGORY_SET_NAME filtered by the user's session language.
  • MFG_LOOKUPS (VIEW) — the lookup view used to resolve the CONTROL_LEVEL code into a display meaning via the lookup type ITEM_CONTROL_LEVEL_GUI.
  • MTL_CATEGORIES_B_KFV (VIEW) — the key flexfield view over categories, supplying the concatenated segments and the individual SEGMENT1–SEGMENT20 columns.

Joins connect MIC to MCS and MCST on CATEGORY_SET_ID, MCST filtered by USERENV('LANG'); MIC to MC on CATEGORY_ID; and MCS.CONTROL_LEVEL to ML.LOOKUP_CODE on lookup type ITEM_CONTROL_LEVEL_GUI. Because several sources are synonyms and views, the definition transparently inherits whatever the current operating unit or language context dictates.

Key Columns

Common Use Cases and Queries

A frequent use case is reporting all items assigned within a given category set for an organization. For example:

SELECT inventory_item_id, category_id, category_concat_segs
FROM   apps.mtl_item_categories_v
WHERE  organization_id = :org_id
AND    category_set_id = :set_id;

Another common pattern lists distinct category values for an item across organizations:

SELECT category_set_name, category_concat_segs, organization_id
FROM   apps.mtl_item_categories_v
WHERE  inventory_item_id = :item_id;

The view is also used to feed integration or validation routines, where CONTROL_LEVEL_DISP and VALIDATE_FLAG determine whether an assignment must reference a valid category. Because the metadata restricts this view to "10SC ONLY," developers should confirm applicability in their specific instance before relying on it in production customizations, and should prefer the documented base objects when a narrower, join-controlled dataset is required.