Search Results mth_mtl_prod_lt_mv




Overview
Explore in AI Chat

Overview

MTH_MTL_PROD_LT_MV is an APPS-owned table registered under the FND – Application Object Library product in Oracle E-Business Suite 12.1.1 and 12.2.2. The _MV suffix and the presence of a unique index built on SYS_OP_MAP_NONNULL expressions strongly indicate that this object is a materialized view container used to persist pre-aggregated manufacturing production and yield metrics. The object carries 41 documented columns in the ETRM 12.2.2 physical schema, and its row grain is defined by a composite key combining an item surrogate, a status code, a calendar month, a day, a level-9 hierarchy node, and a grouping identifier. Its role is analytical: it stores pre-computed production, scrap, rework, first-time-yield, and batch cycle-time measures, keyed to item and time dimensions, so that shop-floor reporting and yield analysis do not repeatedly scan high-volume transaction tables.

From a Data Vault modeling perspective, the mined FK structure classifies this object as standalone. The heuristic classification is therefore a satellite candidate: it holds descriptive, measurable attributes attached to a business key rather than acting as a hub or a link. The single documented foreign key to HR_DM_TABLE_GROUPINGS via GROUPING_ID supports this reading, since a grouping reference is an attribute of the aggregate record, not a relationship joining two independent business entities.

Key Information Stored

The business key is enforced by the unique index I_SNAP$_MTH_MTL_PROD_LT_MV, which is defined over the null-mapped composite of ITEM_FK_KEY, STATUS_CODE, CALENDAR_MONTH_ID, LEVEL9_FK_KEY, DAY_ID, and GROUPING_ID. That index is the authoritative uniqueness constraint; there is no separately documented single-column surrogate primary key. The most significant columns fall into three groups:

Common Use Cases and Queries

Typical usage is period-over-period yield reporting, scrap and rework trending, and batch cycle-time analysis by item and month. A representative pattern joins the item dimension and filters by calendar month:

  • SELECT item_fk_key, calendar_month_id, SUM(start_quantity_g), SUM(actual_completed_qty_g), SUM(first_time_qty)/SUM(first_time_qty_count) grouped by item and month for first-time-yield reporting.
  • SELECT level9_fk_key, SUM(actual_scrap_qty_g), SUM(actual_reworked_qty_g) to rank hierarchy nodes by loss.
  • SELECT grouping_id, SUM(batch_cycle_time_sum)/SUM(batch_cycle_time_count) to compute average batch cycle time per grouping.
  • Join to HR_DM_TABLE_GROUPINGS on GROUPING_ID to resolve grouping labels for presentation.
  • Compare EXPECTED_YIELD against ACTUAL_COMP_QTY_G and divide by START_QUANTITY_G to derive realized yield percentage.

Because these are aggregate rows, queries should always group by the business-key columns and use the _COUNT companions to validate that underlying transaction volumes are non-trivial before drawing conclusions.

Related Objects

  • HR_DM_TABLE_GROUPINGS — the only documented foreign key target, joined on GROUPING_ID; provides grouping metadata for the aggregate rows.
  • MTL_SYSTEM_ITEMS_B — resolves ITEM_FK_KEY / ITEM_DIM_KEY to item numbers, descriptions, and planning attributes.
  • MTL_MATERIAL_TRANSACTIONS — the principal source of the quantity and count measures aggregated into this table.
  • WIP_DISCRETE_JOBS / WIP_ENTITIES — supply batch and job context underlying cycle-time and completion measures.
  • BOM_STRUCTURES_B / BOM_COMPONENTS_B — link item hierarchy context for level-9 rollups and component-level yield analysis.
  • FND_CALENDAR / time dimension objects referenced by TIME_DIM_KEY — resolve DAY_ID, CALENDAR_MONTH_ID, and TIME_DIM_LEVEL for period reporting.
  • MTH_MTL_PROD_LT (base or detail counterpart) — the underlying production line-transaction source that this aggregate table summaries.