Results for “mtl_default_category_sets”

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

Overview

MTL_DEFAULT_CATEGORY_SETS is an Inventory (INV) module table that stores the default category set assigned to each functional area in Oracle E-Business Suite. A functional area represents a distinct business context that consumes item categories — Purchasing, Inventory, Order Entry, Costing, and similar — and each context requires exactly one designated category set to drive item classification, validation, and defaulting during transactions. This table is the single mapping that answers the question "which category set applies when the system performs a category-driven operation in this area?"

Because the table is narrow in scope, its role is configuration rather than transaction capture. It is populated during implementation setup and modified infrequently. From a Data Vault modeling perspective, the mined relationship structure classifies this object as satellite-leaning: the primary key is a composite of functional area and category set, and the only outbound foreign key points to a descriptive reference table, MTL_CATEGORY_SETS_B. The recommended modeling treatment is therefore a satellite attached to the functional-area hub, recording the currently effective default category set assignment.

Key Information Stored

The documented physical schema for ETRM 12.2.2 contains twelve columns in the INV schema. The most significant are:

• FUNCTIONAL_AREA_ID — Identifies the functional area for which the default category set applies. Part of the composite primary key.
• CATEGORY_SET_ID — Foreign key to MTL_CATEGORY_SETS_B, identifying the category set designated as default. Part of the composite primary key.
• LAST_UPDATE_DATE — Standard audit column recording the most recent modification timestamp.
• LAST_UPDATED_BY — User who performed the most recent update.
• CREATION_DATE — Timestamp of row creation.
• CREATED_BY — User who created the row.
• LAST_UPDATE_LOGIN — Login session associated with the last update.
• REQUEST_ID — Concurrent request identifier when the row was created or updated by a program.
• PROGRAM_APPLICATION_ID — Application owning the concurrent program that touched the row.
• PROGRAM_ID — Concurrent program identifier.
• PROGRAM_UPDATE_DATE — Date the concurrent program performed the update.
• ZD_EDITION_NAME — Editioning column used by the EBS 12.2 online patching (Edition-Based Redefinition) model.

The surrogate primary key is MTL_DEFAULT_CATEGORY_SETS_PK, defined over (FUNCTIONAL_AREA_ID, CATEGORY_SET_ID). A separate unique index, MTL_DEFAULT_CATEGORY_SETS_U1, covers (FUNCTIONAL_AREA_ID, ZD_EDITION_NAME) and acts as the business-key candidate in the edition-aware model, enforcing that only one default category set exists per functional area per edition.

Common Use Cases and Queries

Typical uses include confirming which category set drives item category assignment in a given functional area, validating configuration after a clone or upgrade, and reporting category structure coverage. A representative query resolves the default category set for Inventory:

• SELECT mdcs.functional_area_id, mcsb.category_set_name FROM mtl_default_category_sets mdcs, mtl_category_sets_b mcsb WHERE mdcs.category_set_id = mcsb.category_set_id;
• Joining to MTL_CATEGORY_SETS_TL (or the relevant translation view) for localized category set names in multi-language environments.
• Comparing functional area assignments across editions by filtering on ZD_EDITION_NAME, useful when diagnosing behavior differences before and after an online patch cycle.
• Auditing setup changes using LAST_UPDATE_DATE, LAST_UPDATED_BY, and the PROGRAM_* columns to determine whether a default was altered manually or by a concurrent program.
• Feeding downstream reporting on item classification, since category-driven reports and interfaces often depend on the functional area default defined here.

Related Objects

The following objects are most significant in relation to MTL_DEFAULT_CATEGORY_SETS:

• MTL_CATEGORY_SETS_B — Referenced via CATEGORY_SET_ID; holds the category set definition itself.
• MTL_CATEGORY_SETS_TL — Translation table supplying category set names and descriptions.
• MTL_CATEGORIES_B / MTL_CATEGORIES_TL — Categories belonging to the category sets referenced here.
• MTL_ITEM_CATEGORIES — Stores item-to-category assignments that ultimately rely on the default category set for the relevant functional area.
• MTL_CATEGORY_SETS_VL — The view interface commonly used to query category set metadata alongside default assignments.
• MTL_DEFAULT_CATEGORY_SETS_PK / MTL_DEFAULT_CATEGORY_SETS_U1 — Primary key and unique index enforcing assignment integrity.