Search Results mtl_item_cat_grps_interface

Overview

MTL_ITEM_CAT_GRPS_INTERFACE is an interface (staging) table owned by the EGO schema in Oracle E-Business Suite, introduced with the Advanced Product Catalog module. It serves as the inbound landing zone for Item Catalog Category group header information that is ultimately validated and imported into the base catalog structures. In Oracle EBS 12.1.1 and 12.2.2, this table carries the transient or batch-loaded records generated by item catalog category import processes, new item request workflows, and bulk catalog maintenance utilities. Rows are written here by external systems, concurrent programs, or the new item request framework, then processed into their final destinations by the catalog category import concurrent program and removed upon successful completion.

Because the table is a standalone structure with only one outbound foreign key reference — ITEM_CATALOG_GROUP_ID pointing to MTL_ITEM_CATALOG_GROUPS_B — a heuristic Data Vault classification suggests modeling it as a satellite rather than a hub or link. The records are descriptive, keyed to a parent catalog group, and carry process/audit attributes rather than representing an independent business entity. The physical schema in 12.2.2 documents 78 columns, which reflects the wide, denormalized structure typical of EBS interface staging tables.

Key Information Stored

The table is organized around a small set of identifying and controlling columns, surrounded by the many descriptor and control attributes required for bulk processing.

• ITEM_CATALOG_GROUP_ID — the foreign key to MTL_ITEM_CATALOG_GROUPS_B; the surrogate link to the target catalog group.
• ITEM_CATALOG_NAME — the business name of the catalog category group being loaded.
• PARENT_CATALOG_GROUP_ID / PARENT_CATALOG_GROUP_NAME — establish hierarchy so child categories attach to the correct parent node.
• SEGMENT1 through SEGMENT20 — the key flexfield segments that define the category's descriptive and key attributes.
• DESCRIPTION, SUMMARY_FLAG, ENABLED_FLAG — core descriptive state of the group.
• START_DATE_ACTIVE, END_DATE_ACTIVE, INACTIVE_DATE — effective-dating controls for the catalog group.
• ITEM_CREATION_ALLOWED_FLAG, ITEM_NUM_GEN_METHOD_TYPE, PREFIX, STARTING_NUMBER, INCREMENT_BY, SUFFIX, ITEM_NUM_SEQ_NAME — control item number generation for categories that permit item creation.
• ITEM_DESC_GEN_METHOD_TYPE, ITEM_DESC_GENERATION_FUNCTION — control automatic item description generation.
• TRANSACTION_ID, PROCESS_STATUS, TRANSACTION_TYPE, REQUEST_ID — the processing envelope that links each row to a batch and tracks import status.
• PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — concurrent program and audit stamp columns.

The surrogate primary key is ITEM_CATALOG_GROUP_ID; business-key candidates are defined by the combination of ITEM_CATALOG_NAME plus the SEGMENT flexfield values, which uniquely identify a catalog category group in the target table.

Common Use Cases and Queries

The principal use case is monitoring the catalog category import batch. A common query joins the interface to the base table to detect rows that failed to import:

• Identify pending failures: SELECT i.ITEM_CATALOG_NAME, i.PROCESS_STATUS, i.REQUEST_ID FROM MTL_ITEM_CAT_GRPS_INTERFACE i WHERE i.PROCESS_STATUS = 'ERROR';
• Verify successful import of a specific catalog group by joining on ITEM_CATALOG_GROUP_ID to MTL_ITEM_CATALOG_GROUPS_B.
• Inspect orphan parent references where PARENT_CATALOG_GROUP_ID does not resolve to an existing group.
• Audit item-number generation settings before import by selecting PREFIX, STARTING_NUMBER, INCREMENT_BY, and SUFFIX.
• Report rows still unprocessed after a concurrent request run using PROGRAM_UPDATE_DATE and TRANSACTION_ID.

Related Objects

• MTL_ITEM_CATALOG_GROUPS_B — the target base table; joined on ITEM_CATALOG_GROUP_ID.
• MTL_ITEM_CATALOG_GROUPS_TL — translation table holding catalog group names in multiple languages.
• MTL_CATEGORIES_B / MTL_CATEGORIES_TL — the category definitions created from imported catalog groups.
• MTL_ITEM_CAT_ASSIGNMENTS_INTERFACE — the related interface for category-to-item assignments.
• EGO_ITEM_CAT_GRPS_IMPORT_PKG — the import package that processes rows from this table.
• MTL_SYSTEM_ITEMS_B — referenced when item generation settings produce new items.
• FND_CONCURRENT_REQUESTS — joined on REQUEST_ID to trace the import batch.