Results for “mtl_category_sets_b”

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

Overview

MTL_CATEGORY_SETS_B is the base table that defines Category Sets within the Oracle E-Business Suite Inventory (INV) module. A category set is a named grouping of categories that share a common structure (hierarchy), validation policy, and control level, and it governs how items are classified for reporting, planning, purchasing, and web-store merchandising. The table is owned by the INV schema and is classified as VALID in the ETRM 12.2.2 repository. It functions as a foundational reference object: nearly every functional area that organizes items — costing, planning, order management, iProcurement, and iStore — resolves its category behavior through a row in this table.

From a Data Vault modeling perspective, the FK topology classified heuristically as hub-leaning confirms that MTL_CATEGORY_SETS_B behaves as a business hub: it holds a single surrogate identifier (CATEGORY_SET_ID) that is referenced by a broad fan of downstream tables, with its descriptive and multilingual attributes pushed to the companion table MTL_CATEGORY_SETS_TL. Modeling it as a hub with a satellite is therefore a reasonable suggestion when designing an analytical vault.

Key Information Stored

The documented physical schema contains 24 columns. The most significant are:

• CATEGORY_SET_ID — the surrogate primary key (MTL_CATEGORY_SETS_B_PK) and the column propagated to every referencing table.
• STRUCTURE_ID — identifies the category structure/hierarchy that the set is built upon.
• VALIDATE_FLAG — determines whether item-category assignments are validated against valid categories.
• CONTROL_LEVEL — defines the level at which category assignment is controlled (for example, item master or item level).
• DEFAULT_CATEGORY_ID — the default category applied when none is specified; this is the table's only outbound foreign key, pointing to MTL_CATEGORIES_B.
• MULT_ITEM_CAT_ASSIGN_FLAG — allows an item to belong to multiple categories within the set.
• HIERARCHY_ENABLED — indicates whether the set supports a hierarchical category structure.
• USER_CREATION_ALLOWED_FLAG — controls whether users may add categories to the set at runtime.
• CONTROL_LEVEL_UPDATEABLE_FLAG and MULT_ITEM_CAT_UPDATEABLE_FLAG — govern whether the control level and multi-assignment settings can be changed after creation.
• VALIDATE_FLAG_UPDATEABLE_FLAG — governs whether the validation policy may be modified.
• RAISE_ITEM_CAT_ASSIGN_EVENT, RAISE_ALT_CAT_HIER_CHG_EVENT, RAISE_CATALOG_CAT_CHG_EVENT — business-event flags that trigger workflow or subscription notifications on assignment or hierarchy/catalog changes.
• ZD_EDITION_NAME — the editioning column introduced in 12.2 online patching; it participates with CATEGORY_SET_ID in the unique index MTL_CATEGORY_SETS_B_U1.

Common Use Cases and Queries

The most frequent reporting need is to resolve the category set used by a particular store or functional configuration. Because the user searched on ICX_WEB_STORE_OPTIONS, the canonical query joins that table to the base table and its translation table to return the store's category set name:

• SELECT o.store_id, o.category_set_id, t.category_set_name FROM icx_web_store_options o, mtl_category_sets_b b, mtl_category_sets_tl t WHERE o.category_set_id = b.category_set_id AND b.category_set_id = t.category_set_id AND t.language = USERENV('LANG');
• Inventory analysts query DEFAULT_CATEGORY_ID against MTL_CATEGORIES_B to confirm the default classification applied during item creation.
• Costing and planning reports join CST_COST_UPDATES and CSP_PLANNING_PARAMETERS by CATEGORY_SET_ID to group items by the costing or planning category set.
• Setup validation scripts check VALIDATE_FLAG, CONTROL_LEVEL, and MULT_ITEM_CAT_ASSIGN_FLAG to ensure item-assignment behavior matches configuration intent before a go-live.

Related Objects

MTL_CATEGORY_SETS_B is referenced by an extensive set of tables spanning multiple modules. The most significant include:

• MTL_CATEGORY_SETS_TL — the translation table holding the category set name and description, joined on CATEGORY_SET_ID and LANGUAGE.
• MTL_CATEGORIES_B — referenced by DEFAULT_CATEGORY_ID, the only outbound FK from this table.
• MTL_CATEGORY_SET_VALID_CATS and MTL_DEFAULT_CATEGORY_SETS — define valid categories and default category sets for functional areas.
• ICX_WEB_STORE_OPTIONS — links iStore web stores to their merchandising category set via CATEGORY_SET_ID.
• ICX_RELATED_CATEGORIES — drives cross-sell/up-sell category relationships in iStore.
• CSP_PLANNING_PARAMETERS and CSP_EXCESS_RULES_B — planning category set assignments.
• CST_COST_UPDATES and CST_COST_TYPE_HISTORY — costing category set usage over time.
• PA_PLAN_RES_DEFAULTS — references ITEM_CATEGORY_SET_ID for project planning resource defaults.
• ZX_FC_TYPES_B — fiscal classification types referencing OWNER_ID_NUM.