Search Results mtl_item_categories




Overview
Explore in AI Chat

Overview

The MTL_ITEM_CATEGORIES table is a core data object within the Oracle E-Business Suite (EBS) Inventory (INV) module, specifically for releases 12.1.1 and 12.2.2. Its primary function is to define and store the assignment of inventory items to specific categories within defined category sets. This table is the operational foundation for the item categorization framework, enabling the classification of items for reporting, pricing, sourcing, planning, and various transactional controls. It serves as the junction table that resolves the many-to-many relationship between items (MTL_SYSTEM_ITEMS_B) and categories (MTL_CATEGORIES), within the context of a specific category set and organization.

Key Information Stored

The table's structure is defined by its composite primary key, which uniquely identifies a single item-category assignment. The critical columns are INVENTORY_ITEM_ID and ORGANIZATION_ID, which link to the master item definition; CATEGORY_SET_ID, which identifies the classification scheme (e.g., Inventory, Purchasing, Costing); and CATEGORY_ID, which specifies the actual category within that set. Other significant columns include LAST_UPDATE_DATE, LAST_UPDATED_BY for auditing, and PROGRAM_ID for tracking the source of the assignment. The integrity of the data is enforced through foreign key relationships to MTL_SYSTEM_ITEMS_B, MTL_CATEGORY_SET_VALID_CATS (ensuring the category is valid for the set), and MTL_PARAMETERS.

Common Use Cases and Queries

This table is central to any process requiring item classification. Common use cases include generating reports filtered by item category, configuring category-specific defaulting rules in Order Management or Purchasing, and driving category-based sourcing in Advanced Supply Chain Planning. A fundamental query pattern retrieves all category assignments for a given item and organization, often joining to descriptive tables for meaningful output:

  • SELECT mic.inventory_item_id, mic.organization_id, mcs.category_set_name, mc.segment1, mc.segment2 FROM mtl_item_categories mic, mtl_category_sets_b mcs, mtl_categories_b mc WHERE mic.category_set_id = mcs.category_set_id AND mic.category_id = mc.category_id AND mic.inventory_item_id = :item_id AND mic.organization_id = :org_id;

Another critical use is validating category assignments before bulk data loads or during interface processing to ensure data integrity with the valid categories defined for a set.

Related Objects

MTL_ITEM_CATEGORIES has extensive integration across EBS. Its primary relationships are with MTL_SYSTEM_ITEMS_B (the item master) and MTL_CATEGORY_SET_VALID_CATS. As indicated by the foreign key metadata, it is directly referenced by several other modules, including Marketing (AMS_ACT_PRODUCTS), Manufacturing (MRP_SR_ASSIGNMENTS), and Financials (OKL_FE_* tables). Key related views include MTL_ITEM_CATEGORIES_V for a descriptive join of assignments and MTL_CATEGORIES_B for category details. For programmatic operations, the INV_ITEM_CATEGORY_PUB API package is the supported interface for creating, updating, or deleting assignments, rather than direct DML on the table.