Results for “mtl_item_catalog_groups_b”

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

Overview

MTL_ITEM_CATALOG_GROUPS_B is the base (language-independent) definition table for Item Catalog Groups in Oracle Inventory (INV). An item catalog group is a user-defined classification construct that assigns a descriptive structure to a set of items, allowing flexible, non-hierarchical item attributes through descriptive elements, aliases, and category assignments. The table resides in the INV schema and is documented as VALID in ETRM for Oracle EBS 12.1.1 and 12.2.2. It is a core reference object for item master configuration and is consumed by a broad set of Oracle modules, including Bills of Material, Product Information Management (EGO), and Oracle Lease and Finance Management (OKL).

From a Data Vault modeling perspective, ETRM classifies this object as hub-leaning. Practically, this means ITEM_CATALOG_GROUP_ID behaves as a durable business key or hub candidate, while descriptive and audit-type columns act as satellite attributes.

Key Information Stored

The table contains 67 documented columns. The most significant are:

• ITEM_CATALOG_GROUP_ID — Surrogate primary key; the unique identifier for each catalog group. Present in the primary key constraint MTL_ITEM_CATALOG_GROUPS_B_PK.
• PARENT_CATALOG_GROUP_ID — Self-referencing parent identifier supporting catalog group hierarchies. Together with ITEM_CATALOG_GROUP_ID and ZD_EDITION_NAME, this forms the unique index MTL_ITEM_CATALOG_GROUPS_B_U1, a strong business-key candidate.
• DESCRIPTION — Free-text description of the catalog group.
• SEGMENT1 through SEGMENT20 — Key Flexfield segments defining the descriptive structure of the group.
• ENABLED_FLAG, START_DATE_ACTIVE, END_DATE_ACTIVE, INACTIVE_DATE — Lifecycle control columns determining active status and effective dating.
• SUMMARY_FLAG — Distinguishes summary (roll-up) groups from detail groups.
• AUDIT COLUMNS — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN.
• CONCURRENT PROGRAM COLUMNS — REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE.
• ITEM NUMBERING COLUMNS — ITEM_NUM_GEN_METHOD, ITEM_NUM_SEQ_NAME, PREFIX, STARTING_NUMBER, INCREMENT_BY, SUFFIX, CURRENT_NUMBER.
• ITEM CREATION CONTROL — ITEM_CREATION_ALLOWED_FLAG, NEW_ITEM_REQUEST_REQD, ITEM_DESC_GEN_METHOD.
• ZD_EDITION_NAME — Edition tracking column consistent with the 12.2.2 data model.

Because MTL_ITEM_CATALOG_GROUPS_B is the base table, translated names and display values reside in MTL_ITEM_CATALOG_GROUPS_TL, which joins on ITEM_CATALOG_GROUP_ID.

Common Use Cases and Queries

Catalog groups are frequently queried when building item master reports, validating item setup against a descriptive structure, or extracting category-to-item mappings for downstream analytics. Typical query patterns include:

• Retrieving active catalog groups:

SELECT ITEM_CATALOG_GROUP_ID, DESCRIPTION, SEGMENT1, ENABLED_FLAG FROM INV.MTL_ITEM_CATALOG_GROUPS_B WHERE ENABLED_FLAG='Y' AND (END_DATE_ACTIVE IS NULL OR END_DATE_ACTIVE > SYSDATE);

• Joining to the translation table to obtain the localized description (in 12.2.2 the language joins on the TL table).
• Extracting items assigned to a catalog group via MTL_SYSTEM_ITEMS_B.ITEM_CATALOG_GROUP_ID.
• Reporting on hierarchical catalog groups using PARENT_CATALOG_GROUP_ID.
• Auditing recent changes with LAST_UPDATE_DATE and LAST_UPDATED_BY.

Related Objects

MTL_ITEM_CATALOG_GROUPS_B sits at the center of a wide FK constellation. The most significant related objects are:

• MTL_ITEM_CATALOG_GROUPS_TL — Translation table keyed on ITEM_CATALOG_GROUP_ID.
• MTL_SYSTEM_ITEMS_B — Item master; items reference a catalog group via ITEM_CATALOG_GROUP_ID.
• MTL_DESCRIPTIVE_ELEMENTS — Defines descriptive attributes attached to each catalog group.
• MTL_ICG_CATEGORIES and MTL_ICG_ALIASES — Category and alias mappings for the group.
• BOM_STRUCTURE_TYPES_B — References the catalog group in the BOM structure type definition, which matches the user search term.
• EGO_* tables (EGO_MTL_SY_ITEMS_EXT_B, EGO_ITEM_GTN_ATTRS_B, EGO_ITM_GTN_MUL_ATTRS_B) — Product Information Management extensions.
• OKL_DF_CTGY_RMK_TMS_ALL — Oracle Lease Management remarketing linkage via ICO_ID.
• MTL_SYSTEM_ITEMS_INTERFACE and INV_ITEM_TEMP — Interface/staging tables that reference the group during item loading.
• BOM_BILL_OF_MTLS_INTERFACE and BOM_INVENTORY_COMPS_INTERFACE — Bill of Material interfaces that carry the catalog group reference.