Search Results mth_items_d_u2

Overview

The MTH.MTH_ITEMS_D table is a core master data object in the Oracle EBS 12.1.1 / 12.2.2 environment, owned by the MTH schema and registered under FND Design Data as MTH.MTH_ITEMS_D. It stores item master information together with a broad set of descriptive and attribute fields, including item name, item description, base/model item, primary and secondary units of measure, unit weight and its UOM, and unit volume and its UOM. The table resides in tablespace APPS_TS_TX_DATA with a PCT Free of 10, and its indexes are held in APPS_TS_TX_IDX. The object is documented as VALID and contains 58 columns.

From a Data Vault modeling perspective, the heuristic classification derived from the foreign-key structure is standalone. This suggests the table functions as an independent hub-like reference for item identity, rather than participating in a traditional hub-and-satellite or link network within the documented ETRM schema. Item identity is anchored by the primary key constraint, while descriptive measures and user-defined attributes remain physically co-located in the same table.

Key Information Stored

The most significant columns fall into three categories: identity, item attributes, and administrative metadata.

• ITEM_PK_KEY (NUMBER) — System-generated surrogate key; the physical primary key anchor for the row.
• ITEM_PK (VARCHAR2 120) — Business item identifier; the column enforced by unique index MTH_ITEMS_D_U1.
• ITEM_NAME (VARCHAR2 240) — Item name; participates with ITEM_PK_KEY in unique index MTH_ITEMS_D_U2 and again in MTH_ITEMS_D_U3, making it a strong business-key candidate.
• DESCRIPTION (VARCHAR2 240) — Free-text item description.
• BASE_ITEM (VARCHAR2 240) — Model or base item reference.
• PRIMARY_UOM and SECONDARY_UOM (VARCHAR2) — Primary and secondary units of measure.
• UNIT_WEIGHT and WEIGHT_UOM — Physical weight value and its unit of measure.
• UNIT_VOLUME and VOLUME_UOM — Physical volume value and its unit of measure.
• EBS_ITEM_ID and EBS_ORGANIZATION_ID (NUMBER) — Cross-reference keys tying the record to the Oracle EBS inventory item and organization.
• PLANT_FK_KEY and SYSTEM_FK_KEY (NUMBER) — References to the originating plant and source system.
• SOURCE_ORG_CODE (VARCHAR2 120) — Organization code in the source system, supporting multi-source integration.
• USER_ATTR1 … USER_ATTR30 and USER_MEASURE1 … USER_MEASURE5 — Extensible user-defined attributes and measures used for customer-specific enrichment.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard audit columns.

Common Use Cases and Queries

The table supports item master reporting, UOM and physical-attribute validation, and EBS-to-ETRM reconciliation. Typical patterns include:

• Business-key lookup: SELECT * FROM MTH.MTH_ITEMS_D WHERE ITEM_NAME = :name; — leverages unique index MTH_ITEMS_D_U2.
• Surrogate key fetch: SELECT ITEM_PK_KEY FROM MTH.MTH_ITEMS_D WHERE ITEM_PK = :pk; — uses MTH_ITEMS_D_U1.
• EBS reconciliation: join on EBS_ITEM_ID and EBS_ORGANIZATION_ID against EBS inventory item views.
• Physical attribute audit: report rows where UNIT_WEIGHT or UNIT_VOLUME is null, grouped by WEIGHT_UOM / VOLUME_UOM.
• Plant-level rollup: aggregate item counts grouped by PLANT_FK_KEY or SOURCE_ORG_CODE.
• Extensibility reporting: query USER_ATTR1..USER_ATTR30 and USER_MEASURE1..USER_MEASURE5 for customer-specific segment analysis.

Related Objects

The heuristic Data Vault classification is standalone, and no explicit foreign-key relationships from this table are documented in the provided metadata. Relationships are inferred through the cross-reference columns EBS_ITEM_ID, EBS_ORGANIZATION_ID, PLANT_FK_KEY, and SYSTEM_FK_KEY. Practically, the following objects are commonly referenced alongside MTH_ITEMS_D:

• MTL_SYSTEM_ITEMS_B — EBS item master; join on INVENTORY_ITEM_ID = MTH_ITEMS_D.EBS_ITEM_ID.
• MTL_ITEM_REVISIONS_B — Item revision history; join on INVENTORY_ITEM_ID and ORGANIZATION_ID.
• MTL_UNITS_OF_MEASURE — UOM validation for PRIMARY_UOM, SECONDARY_UOM, WEIGHT_UOM, and VOLUME_UOM.
• HR_ORGANIZATION_UNITS / ORG_ORGANIZATION_DEFINITIONS — Organization and plant resolution via EBS_ORGANIZATION_ID and PLANT_FK_KEY.
• MTH system reference tables — Source-system resolution driven by SYSTEM_FK_KEY.
• Downstream ETRM transaction and mapping tables that consume ITEM_PK_KEY as a surrogate reference for item-level activity.