Search Results mth_equip_op_sum_pm_mv




Overview
Explore in AI Chat

Overview

MTH_EQUIP_OP_SUM_PM_MV is an APPS-owned table within the FND – Application Object Library product of Oracle E-Business Suite. Despite the _MV suffix, which in Oracle EBS nomenclature commonly denotes a materialized view, ETRM metadata classifies this object as a TABLE with a documented physical schema of 35 columns. The object appears in both 12.1.1 and 12.2.2 releases, with the ETRM physical definition recorded against 12.2.2. It functions as an equipment operations summary store, aggregating production, quality, scrap, rework, and labor-hour metrics across day, calendar month, department, customer, and product hierarchy dimensions.

The heuristic Data Vault classification mined from the foreign key structure is standalone. As a modeling suggestion, this indicates that MTH_EQUIP_OP_SUM_PM_MV behaves as an independent aggregate or summary artifact rather than a normalized hub, link, or satellite within a Data Vault model. Its single documented foreign key, from GROUPING_ID to HR_DM_TABLE_GROUPINGS, is consistent with this classification: the table references a grouping definition table but is not itself a dependent child in a broader relational chain.

Key Information Stored

The table's most significant columns fall into four functional groups:

No explicit surrogate primary key column is documented in the metadata. The unique index I_SNAP$_MTH_EQUIP_OP_SUM_P, built with SYS_OP_MAP_NONNULL wrappers, represents the business-key candidate. It comprises DEPT_KEY, CALENDAR_MONTH_ID, PRODUCT_LEVEL9_KEY, DEPT_EFF_DATE, STATUS_CODE, CUST_LEVEL9_KEY, DAY_ID, CUST_LEVEL9_EFF_DATE, and GROUPING_ID. The SYS_OP_MAP_NONNULL function indicates the index treats null values as equal, a pattern typical of Oracle's dimension or summary tables where nulls are semantically meaningful rather than absent. Practitioners should treat this composite as the logical uniqueness constraint rather than expecting a single-column identity attribute.

Common Use Cases and Queries

This table supports manufacturing and operational analytics. Typical scenarios include equipment throughput reporting, quality loss analysis, scrap and rework trending, and department-level productivity measurement. Because measures are paired with counts, reports can present both totals and averages without recomputation.

A representative query aggregating good quantity by department and month would resemble:

SELECT dept_key, calendar_month_id,
       SUM(qty_good) AS total_good,
       SUM(qty_scrap) AS total_scrap,
       SUM(qty_rework) AS total_rework
FROM   apps.mth_equip_op_sum_pm_mv
WHERE  calendar_month_id = :p_month
GROUP  BY dept_key, calendar_month_id;

Filtering by GROUPING_ID allows reports to isolate specific grouping definitions registered in HR_DM_TABLE_GROUPINGS. Trend analysis over EQUIP_HIER_KEY and EQUIP_HIER_EFF_DATE supports effective-dated equipment hierarchy reporting, while PRODUCT_LEVEL9_KEY and CUST_LEVEL9_KEY enable product and customer rollups at the level-9 grain. Joins to a time dimension via TIME_DIM_KEY and TIME_DIM_LEVEL are appropriate when calendar attributes beyond DAY_ID and CALENDAR_MONTH_ID are required.

Related Objects

  • HR_DM_TABLE_GROUPINGS — the only documented foreign key relationship, joined via MTH_EQUIP_OP_SUM_PM_MV.GROUPING_ID = HR_DM_TABLE_GROUPINGS.GROUPING_ID. This defines the grouping context for each summary row.
  • Time dimension tables — referenced through TIME_DIM_KEY and TIME_DIM_LEVEL, supplying calendar and period attributes.
  • Product hierarchy tables — represented by PRODUCT_LEVEL9_KEY, providing level-9 product attributes.
  • Customer hierarchy tables — represented by CUST_LEVEL9_KEY and CUST_LEVEL9_EFF_DATE.
  • Department and organization tables — represented by DEPT_KEY and DEPT_EFF_DATE.
  • Equipment hierarchy definitions — represented by EQUIP_HIER, EQUIP_HIER_KEY, and EQUIP_HIER_EFF_DATE.

Because the metadata documents only one foreign key, the remaining relationships are inferred from column naming conventions. Implementers should validate join paths against the actual ETRM or data-model documentation for their specific release before building dependent reports or extractions.