Results for “mtl_categories_b”

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

Overview

MTL_CATEGORIES_B is the base table that stores item category code combinations within Oracle E-Business Suite Inventory (INV). It is owned by the INV schema and serves as the master definition of every item category value that can be assigned to items, commodities, and planning entities across the EBS data model. Each row represents a single code combination within a category structure, with the flexible segment columns (SEGMENT1 through SEGMENT20) holding the concatenated segments that constitute the key flexfield combination for that category.

The table plays a central role as a reference hub: a large number of downstream tables across Purchasing, Order Management, Costing, Service, and Advanced Pricing depend on it through foreign keys. For the search term po_commodity_categories, MTL_CATEGORIES_B is the parent reference table — PO_COMMODITY_CATEGORIES.CATEGORY_ID points directly to MTL_CATEGORIES_B.CATEGORY_ID, meaning purchasing commodity categories are defined as item categories in this table.

In Data Vault modeling terms, the FK topology strongly suggests a hub-leaning classification: MTL_CATEGORIES_B is referenced by dozens of satellites and link tables but does not itself depend on many parents through foreign keys. This is a modeling suggestion rather than a documented design intent; the physical implementation is a classic EBS code-combination table with a surrogate key and descriptive flexfield columns.

Key Information Stored

The physical schema documents 56 columns. The most operationally significant are:

• CATEGORY_ID — the surrogate primary key and the join column used by virtually every dependent table. It is also part of the unique index MTL_CATEGORIES_B_U1 (CATEGORY_ID, ZD_EDITION_NAME).
• STRUCTURE_ID — identifies the category structure (key flexfield) to which this code combination belongs, separating, for example, Purchasing categories from Inventory categories.
• SEGMENT1 … SEGMENT20 — the flexfield segment values that together form the concatenated category code. Only the segments enabled in the associated structure are meaningful; the remainder are typically null.
• DESCRIPTION — the base-language description of the category code combination (translated text resides in MTL_CATEGORIES_TL).
• SUMMARY_FLAG and ENABLED_FLAG — indicate whether the row is a summary-level node and whether it is currently active for assignment.
• START_DATE_ACTIVE and END_DATE_ACTIVE — the effective date range during which the category is assignable.
• DISABLE_DATE — the date the category was disabled.
• SUPPLIER_ENABLED_FLAG and WEB_STATUS — flags relevant to supplier and web-facing usage.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 … ATTRIBUTE15 — the descriptive flexfield columns that hold customer-extensible attributes.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — standard EBS WHO columns and concurrent-program audit columns.
• ZD_EDITION_NAME — the edition column associated with the EBS online patching (editioning) architecture used in 12.2.x.

Note that CATEGORY_ID alone is the database unique constraint, while the business unique index is composite on CATEGORY_ID and ZD_EDITION_NAME, reflecting the 12.2 editioning model.

Common Use Cases and Queries

Typical reporting and integration scenarios include validating category assignments on purchasing documents, listing active categories in a purchasing structure, and reconciling item category assignments against category sets.

A common query lists the available purchasing categories and their descriptions:

• SELECT c.category_id, c.segment1, c.description, c.enabled_flag FROM mtl_categories_b c WHERE c.structure_id = :purchasing_structure_id AND c.enabled_flag = 'Y';

To resolve the commodity categories associated with purchasing, join through PO_COMMODITY_CATEGORIES:

• SELECT pcc.category_id, mcb.segment1, mcb.description FROM po_commodity_categories pcc JOIN mtl_categories_b mcb ON pcc.category_id = mcb.category_id;

To report category assignments on purchase order lines, join PO_LINES_ALL.CATEGORY_ID to MTL_CATEGORIES_B.CATEGORY_ID. For active-date filtering, include START_DATE_ACTIVE, END_DATE_ACTIVE, and DISABLE_DATE predicates. Translation-aware reporting should join MTL_CATEGORIES_TL on CATEGORY_ID to obtain language-specific descriptions. Always filter by STRUCTURE_ID when narrowing to a specific category structure, since SEGMENT columns are only meaningful within the owning structure.

Related Objects

The following are among the most significant objects that reference or depend on MTL_CATEGORIES_B through documented foreign keys:

• PO_COMMODITY_CATEGORIES — joined via PO_COMMODITY_CATEGORIES.CATEGORY_ID = MTL_CATEGORIES_B.CATEGORY_ID; defines purchasing commodity categories.
• PO_LINES_ALL — joined via PO_LINES_ALL.CATEGORY_ID = MTL_CATEGORIES_B.CATEGORY_ID; stores the category on each purchase order line.
• PO_REQUISITION_LINES_ALL — joined via CATEGORY_ID; category on requisition lines.
• MTL_CATEGORIES_TL — the translation table joined via CATEGORY_ID for language-specific descriptions.
• MTL_CATEGORY_SETS_B — the parent category set definition, referenced through DEFAULT_CATEGORY_ID.
• MTL_CATEGORY_SET_VALID_CATS — the valid-category assignments within a category set, joined via CATEGORY_ID.
• MTL_SYSTEM_ITEMS — referenced via MRP_SYSTEM_ITEMS.CATEGORY_ID, linking items to their categories.
• MRP_SR_ASSIGNMENTS — joined via CATEGORY_ID for sourcing rules that operate at category level.
• MTL_CATEGORY_ACCOUNTS — joined via CATEGORY_ID; category-level accounting defaults.
• RCV_SHIPMENT_LINES — joined via CATEGORY_ID; category captured on receiving lines.
• PA_RESOURCE_ASSIGNMENTS and PA_RESOURCE_LIST_MEMBERS — joined via ITEM_CATEGORY_ID for project resource categorizations.
• FTE_LANE_COMMODITIES — joined via COMMODITY_CATG_ID, tying transportation lanes to commodity categories.

Together these relationships establish MTL_CATEGORIES_B as the central reference for item categorization across the EBS suite, and explain its appearance in searches such as po_commodity_categories.