Search Results mth_entity_planned_usage_sm_mv

Overview

MTH_ENTITY_PLANNED_USAGE_SM_MV is an APPS-owned materialized view table within the Oracle Manufacturing Operations Center (MTH) product family, valid in both Oracle EBS 12.1.1 and 12.2.2. It stores planned versus allocated usage of manufacturing entities — such as resources, equipment, or production assets — aggregated across daily, fiscal-week, fiscal-quarter, and fiscal-year reporting horizons. The object serves as the materialized snapshot layer for the Manufacturing Operations Center analytics engine, exposing pre-computed planned usage metrics and average planned cost per plant, entity, and sustainability aspect.

The ETRM documentation records 25 physical columns and one unique index, I_SNAP$_MTH_ENTITY_PLANNED, whose expression list spans day, plant, entity, period profile, effective and expiration dates, sustainability attributes, average planned cost, usage unit of measure, fiscal identifiers, and grouping. Based on the mined foreign-key structure and the absence of upstream hub or link dependencies other than HR_DM_TABLE_GROUPINGS, the heuristic Data Vault classification for this object is standalone. In Data Vault modeling terms, this suggests the view is best treated as a derived reporting structure rather than a true hub, link, or satellite, and downstream modeling should not assume referential integration into a conformed dimensional chain.

Key Information Stored

The most operationally significant columns fall into four groupings: time dimension, plant/entity identity, sustainability classification, and usage metrics.

• Time identifiers: DAY, DAY_ID, FISCAL_WEEK_ID, FISCAL_WEEK_NAME, FISCAL_QUARTER_ID, FISCAL_QUARTER_NAME, FISCAL_YEAR_ID, FISCAL_YEAR_NAME, and the generic TIME_DIM_KEY with TIME_DIM_LEVEL provide convergent calendar rollups for period-over-period reporting.
• Entity and plant keys: PLANT_FK_KEY and ENTITY_FK_KEY identify the manufacturing plant and the specific production entity whose planned usage is being tracked. PERIOD_PROFILE and the EFFECTIVE_DATE / EXPIRATION_DATE pair bound the validity window of each planned-usage record.
• Sustainability attributes: SUSTAIN_ASPECT_FK_KEY and SITE_SUSTAIN_FK_KEY link usage records to sustainability aspects and site-level sustainability definitions, supporting environmental and consumption analytics.
• Usage metrics: PLANNED_USAGE, PLANNED_USAGE_COUNT, ALLOCATED_PLANNED_USAGE, ALLOCATED_PLANNED_USAGE_COUNT, TOTAL_COUNT, AVERAGE_PLANNED_COST, and USAGE_UOM capture both the quantitative planned values and the monetary cost baseline, in the applicable unit of measure.
• Grouping key: GROUPING_ID references HR_DM_TABLE_GROUPINGS, providing the sole documented foreign-key relationship.

The unique index I_SNAP$_MTH_ENTITY_PLANNED, built using SYS_OP_MAP_NONNULL to normalize NULL values, identifies the composite business key candidates, distinguishing them from any surrogate identifier. The GROUPING_ID foreign key to HR_DM_TABLE_GROUPINGS is the only documented inbound reference.

Common Use Cases and Queries

Typical consumption centers on planned-versus-actual usage variance analysis, fiscal-period cost reporting, and sustainability consumption tracking per plant and entity.

• Period variance reporting: aggregate PLANNED_USAGE against ALLOCATED_PLANNED_USAGE grouped by FISCAL_WEEK_NAME or FISCAL_QUARTER_NAME to measure under- or over-allocation.
• Entity cost trending: trend AVERAGE_PLANNED_COST across DAY_ID for a given PLANT_FK_KEY and ENTITY_FK_KEY, filtered on EFFECTIVE_DATE and EXPIRATION_DATE to exclude expired plans.
• Sustainability analytics: join SUSTAIN_ASPECT_FK_KEY and SITE_SUSTAIN_FK_KEY to sustainability reference tables to report planned consumption per aspect.
• Grouping-based rollups: join GROUPING_ID to HR_DM_TABLE_GROUPINGS to drive grouping-based aggregation.

A representative query pattern filters on PLANT_FK_KEY and a fiscal identifier, then sums PLANNED_USAGE and ALLOCATED_PLANNED_USAGE. Because the object is a materialized view, refresh staleness should be checked before using it for near-real-time reporting.

Related Objects

• HR_DM_TABLE_GROUPINGS — referenced via MTH_ENTITY_PLANNED_USAGE_SM_MV.GROUPING_ID; the sole documented foreign key and the primary grouping lookup.
• Manufacturing plant and entity master tables — reached through PLANT_FK_KEY and ENTITY_FK_KEY to resolve plant and entity names.
• Sustainability aspect and site sustainability tables — reached through SUSTAIN_ASPECT_FK_KEY and SITE_SUSTAIN_FK_KEY.
• Fiscal calendar reference tables — reached through FISCAL_WEEK_ID, FISCAL_QUARTER_ID, and FISCAL_YEAR_ID.
• Other MTH Manufacturing Operations Center materialized views — associated SM_MV analytics objects sharing the APPS schema and period-profile conventions.

The object operates as a standalone reporting structure within the MTH analytics layer, with HR_DM_TABLE_GROUPINGS as its only documented referential anchor.