Results for “mtl_item_categories”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

MTL_ITEM_CATEGORIES is a core Inventory (INV) table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores the assignment of inventory items to categories. Each row represents a unique combination of an item, an organization, and a category set, allowing the same item to be classified under multiple category sets (for example, Product Line, Buyer, Planning, or Costing) simultaneously. This flexible many-to-many structure is fundamental to how Oracle EBS drives reporting, planning, pricing, and order management logic based on category membership.

Under the heuristic Data Vault classification mined from the foreign key structure, this table is best modeled as a hub. It acts as a central intersection point between items, organizations, and category sets, with a composite primary key (MTL_ITEM_CATEGORIES_PK) defined on INVENTORY_ITEM_ID, ORGANIZATION_ID, and CATEGORY_SET_ID. A second unique index, MTL_ITEM_CATEGORIES_U1, extends this key to include CATEGORY_ID and ZD_EDITION_NAME, reflecting the multi-tenant editioning model introduced in Release 12.2.

Key Information Stored

The table is composed of 15 documented columns. The primary key columns form the business identity of each category assignment:

• INVENTORY_ITEM_ID — Identifies the item being categorized; foreign key to MTL_SYSTEM_ITEMS_B.
• ORGANIZATION_ID — The inventory organization context for the assignment; foreign key to MTL_PARAMETERS.
• CATEGORY_SET_ID — The category set under which the assignment is made; foreign key to MTL_CATEGORY_SET_VALID_CATS.
• CATEGORY_ID — The specific category selected within the category set.

Surrogate and audit columns include LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN, which support concurrency and change tracking. Concurrency program columns (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) identify the concurrent request that last modified the row, while WH_UPDATE_DATE supports warehouse-related refresh logic. ZD_EDITION_NAME is the editioning column required in 12.2 multi-tenant deployments.

Common Use Cases and Queries

Typical applications include category-driven reporting, item onboarding validation, and planning or pricing rules that depend on category membership. A common query retrieves all category sets assigned to a given item within an organization:

• SELECT mic.organization_id, mic.category_set_id, mic.category_id FROM mtl_item_categories mic WHERE mic.inventory_item_id = :item_id AND mic.organization_id = :org_id;
• Joining to MTL_CATEGORIES_B/_TL to resolve category names for reporting.
• Checking for duplicate or missing assignments before running item import (Open Interface) processes.

Because the table is frequently joined to MTL_SYSTEM_ITEMS_B, performance is best served by filters on ORGANIZATION_ID and INVENTORY_ITEM_ID. Reports across all organizations should restrict on CATEGORY_SET_ID to limit row counts.

Related Objects

The table participates in several important relationships:

• MTL_SYSTEM_ITEMS_B — parent of the item/organization key.
• MTL_CATEGORY_SET_VALID_CATS — validates the category set and category combination.
• MTL_PARAMETERS — validates the organization.
• AMS_ACT_PRODUCTS — marketing activity products reference this table via item, organization, and category set.
• MRP_SR_ASSIGNMENTS — planning sourcing assignments depend on item category assignments.
• OKL_FE_EO_TERM_OBJECTS, OKL_FE_ITEM_RESIDUAL_ALL, and OKL_FE_RESI_CAT_OBJECTS — Lease Management term and residual objects reference this table's key.

These dependencies confirm that MTL_ITEM_CATEGORIES is a foundational reference point across Inventory, Planning, Marketing, and Lease Management modules.