Search Results mtl_item_categories_interface




Overview
Explore in AI Chat

Overview

MTL_ITEM_CATEGORIES_INTERFACE is the Item Category Open Interface table in the Oracle Inventory (INV) module. It serves as the staging area through which external systems, legacy conversions, and batch programs load item-to-category assignments into Oracle E-Business Suite. Each row represents a pending assignment of an inventory item to a category within a category set, together with the descriptive context and processing controls required by the Item Category Open Interface concurrent program to validate and import that assignment into the base Inventory tables.

The table is owned by the INV schema and is documented as a standalone object in the ETRM 12.2.2 physical schema, containing 28 columns. Because it carries no foreign key dependencies to other tables in the documented relationship model, a heuristic Data Vault classification would model it as a standalone structure; functionally, however, it behaves as a staging satellite for item-category assignment transactions, with the natural business key formed by the item, organization, category set, and category combination.

Key Information Stored

The primary key of the table is MTL_ITEM_CAT_INTERFACE_PK, defined over the columns INVENTORY_ITEM_ID, ORGANIZATION_ID, CATEGORY_SET_ID, and CATEGORY_ID. This composite key is the surrogate identifier for interface rows and simultaneously represents the business-key candidate: the quartet uniquely identifies which item is being assigned to which category, in which category set, for which organization.

Common Use Cases and Queries

The most common use case is data conversion and integration: populating MTL_ITEM_CATEGORIES_INTERFACE from a legacy system or external application, then running the Item Category Open Interface concurrent program to validate and create the corresponding MTL_ITEM_CATEGORIES records. A second frequent use case is incremental maintenance, such as bulk reclassification of items between categories, where TRANSACTION_TYPE and OLD_CATEGORY_ID drive updates and deletions.

Typical interrogations of the table include:

  • Identifying rows still pending processing by selecting where PROCESS_FLAG is null or indicates an unprocessed state.
  • Reconciling loaded volumes by grouping on REQUEST_ID or SET_PROCESS_ID to confirm that all rows from a given run were processed.
  • Comparing the interface to the base tables by joining on INVENTORY_ITEM_ID and ORGANIZATION_ID to detect assignments that failed validation.
  • Auditing the source of a change using SOURCE_SYSTEM_ID and SOURCE_SYSTEM_REFERENCE, or reconstructing change history through CHANGE_ID and CHANGE_LINE_ID.
  • Reporting on workload by counting rows per CATEGORY_SET_ID or ORGANIZATION_ID to size category management initiatives.

Related Objects

Although the documented relationship model classifies the table as standalone, its purpose is to feed the base item-category assignment table and to reference the Inventory master data structures. The most significant related objects and their join columns are:

  • MTL_ITEM_CATEGORIES — the base assignment table populated by the interface; joined on INVENTORY_ITEM_ID, ORGANIZATION_ID, CATEGORY_SET_ID, and CATEGORY_ID.
  • MTL_CATEGORY_SETS — the category set definition; joined on CATEGORY_SET_ID.
  • MTL_CATEGORIES_B and MTL_CATEGORIES_TL — category definitions and their translations; joined on CATEGORY_ID.
  • MTL_SYSTEM_ITEMS_B — item master data; joined on INVENTORY_ITEM_ID and ORGANIZATION_ID.
  • MTL_PARAMETERS — inventory organization definitions; joined on ORGANIZATION_ID.
  • FND_CONCURRENT_REQUESTS — concurrent request audit; joined on REQUEST_ID.
  • Item Category Open Interface concurrent program — the program that reads, validates, and imports rows from this table into the base Inventory tables.