Search Results mth_items_d




Overview
Explore in AI Chat

Overview

MTH_ITEMS_D is a table in the MTH schema, owned by the Oracle Manufacturing Operations Center (MOC) product within Oracle E-Business Suite 12.1.1 and 12.2.2. It serves as the item dimension store for MOC — the analytic and shop-floor visibility layer that consolidates manufacturing execution data across plants, systems, and source organizations. Each row represents a single item definition as it is known to the Operations Center, carrying descriptive attributes, unit-of-measure information, physical characteristics, and a configurable block of user-defined attributes and measures.

The ETRM metadata classifies this object, heuristically, as a standalone structure with no mined foreign-key links to other documented tables. In Data Vault terms, this suggests modeling it as a standalone hub-and-satellite combination keyed on ITEM_PK, rather than as a link table. The absence of mined FK relationships does not mean the table is unreferenced in practice; it reflects that relationships are maintained through EBS identifiers (EBS_ITEM_ID, EBS_ORGANIZATION_ID) and application logic rather than enforced database constraints.

Key Information Stored

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

Common Use Cases and Queries

Typical usage centers on item master reporting, MOC dimension enrichment, and reconciliation between MOC and EBS. Because the table is the item dimension, joins to MOC fact data are performed on ITEM_PK, while joins to EBS masters use EBS_ITEM_ID and EBS_ORGANIZATION_ID.

Reconciling MOC items to EBS inventory items:

  • SELECT d.ITEM_PK, d.ITEM_NAME, d.EBS_ITEM_ID, m.SEGMENT1, m.DESCRIPTION FROM MTH.MTH_ITEMS_D d JOIN MTL_SYSTEM_ITEMS_B m ON m.INVENTORY_ITEM_ID = d.EBS_ITEM_ID AND m.ORGANIZATION_ID = d.EBS_ORGANIZATION_ID;

Resolving an item by its business key or surrogate key:

  • SELECT * FROM MTH.MTH_ITEMS_D WHERE ITEM_NAME = :item_name AND ITEM_PK_KEY = :source_key;
  • SELECT * FROM MTH.MTH_ITEMS_D WHERE ITEM_PK = :item_pk;

Incremental extraction for downstream reporting, using the audit columns and system identifiers, allows efficient delta loads. Filtering by PLANT_FK_KEY or SYSTEM_FK_KEY supports plant-scoped or source-system-scoped reporting, which is common when MOC is deployed across multiple facilities.

Related Objects

The metadata documents no enforced foreign keys, so related objects are identified through shared business identifiers and MOC application wiring:

  • MTL_SYSTEM_ITEMS_B — joined on INVENTORY_ITEM_ID = EBS_ITEM_ID and ORGANIZATION_ID = EBS_ORGANIZATION_ID.
  • MTL_ITEM_REVISIONS_B — item revision detail, joined via EBS_ITEM_ID.
  • MTL_UNITS_OF_MEASURE — resolves PRIMARY_UOM, SECONDARY_UOM, WEIGHT_UOM, and VOLUME_UOM codes.
  • MTH plant and system dimension tables — referenced through PLANT_FK_KEY and SYSTEM_FK_KEY.
  • MOC fact and staging tables — consume ITEM_PK as the item dimension key in shop-floor and execution reporting.
  • MTL_ITEM_CATEGORIES / MTL_CATEGORY_SETS_B — category assignment context, reachable via EBS_ITEM_ID.

Because referential integrity is managed by the application rather than the database, any custom query joining this table should validate key assumptions against the MOC data load and refresh procedures before relying on them for production reporting.