Search Results time_dim_level




Overview
Explore in AI Chat

Overview

APPS.MTH_ENTITY_PLANNED_USAGE_SM_MV is a materialized view object in the Oracle E-Business Suite 12.1.1 and 12.2.2 Enterprise Territory and Resource Management (ETRM) product family, registered under the FND Design Data namespace MTH.MTH_ENTITY_PLANNED_USAGE_SM_MV. In the database catalog the object is exposed as a TABLE with owner APPS and status VALID, and it resides in the APPS_TS_SUMMARY tablespace — a designation that confirms its role as a pre-aggregated summary structure rather than a transactional base table. The object consolidates planned sustainability or resource usage for a given entity, plant, and time dimension, together with the allocated portion of that planned usage.

Functionally, the materialized view aggregates planned usage figures across multiple time dimension levels — day, fiscal week, fiscal quarter, and fiscal year — so that downstream reporting and analytics do not have to recompute detail-level aggregations on demand. The inclusion of PERIOD_PROFILE, EFFECTIVE_DATE, and EXPIRATION_DATE indicates a slowly changing, date-ranged model in which the same entity/plant/aspect combination may be represented by multiple effective periods.

The Data Vault classification mined from the foreign key structure is "standalone." In modeling terms this is a suggestion that the object should be treated as a self-contained derived/aggregate structure rather than a hub, link, or satellite within a normalized Data Vault model. Its single documented foreign key — GROUPING_ID referencing HR_DM_TABLE_GROUPINGS — is consistent with that characterization.

Key Information Stored

The materialized view contains 25 documented columns. The most significant are summarized below.

The unique key is the function-based index I_SNAP$_MTH_ENTITY_PLANNED, which wraps SYS_OP_MAP_NONNULL over DAY_ID, PLANT_FK_KEY, ENTITY_FK_KEY, PERIOD_PROFILE, EFFECTIVE_DATE, EXPIRATION_DATE, SUSTAIN_ASPECT_FK_KEY, SITE_SUSTAIN_FK_KEY, AVERAGE_PLANNED_COST, USAGE_UOM, FISCAL_WEEK_ID, FISCAL_QUARTER_ID, FISCAL_YEAR_ID, and GROUPING_ID. That composite is the strongest, documented business-key candidate; no separate surrogate primary key is documented. The non-unique index MTH_ENTITY_PLANNED_USAGE_SM_N1 — the object the user searched for — covers TIME_DIM_LEVEL, ENTITY_FK_KEY, SUSTAIN_ASPECT_FK_KEY, TIME_DIM_KEY, and ALLOCATED_PLANNED_USAGE, and is the index Oracle would typically choose for entity- and sustainability-filtered aggregate queries.

Common Use Cases and Queries

Typical usage is reporting on planned sustainability usage by entity, plant, and fiscal period. A query filtered on the MTH_ENTITY_PLANNED_USAGE_SM_N1 leading columns and returning the aggregate measures follows the index design:

  • Fiscal trend analysis: select FISCAL_QUARTER_NAME, SUM(PLANNED_USAGE), SUM(ALLOCATED_PLANNED_USAGE) grouped by fiscal period for a fixed ENTITY_FK_KEY.
  • Plant-level utilization: filter on PLANT_FK_KEY and range over DAY, comparing PLANNED_USAGE against ALLOCATED_PLANNED_USAGE.
  • Cost and UOM analysis: aggregate AVERAGE_PLANNED_COST by USAGE_UOM across a fiscal year.
  • Effective-date lookups: apply SYSDATE BETWEEN EFFECTIVE_DATE AND EXPIRATION_DATE to retrieve the current valid row for an entity and plant.
  • Grouping analysis: join GROUPING_ID to HR_DM_TABLE_GROUPINGS to resolve grouping definitions.

Because the object is a materialized view, it should be refreshed as a dependency of its underlying MTH base tables; querying it directly is preferred over reconstructing the same aggregation from detail tables.

Related Objects

The following objects are documented or structurally related to this materialized view:

  • APPS.HR_DM_TABLE_GROUPINGS — Referenced through the foreign key on GROUPING_ID; the principal documented FK relationship.
  • MTH_ENTITY_PLANNED_USAGE_SM_N1 — The non-unique reporting index on the materialized view, covering TIME_DIM_LEVEL, ENTITY_FK_KEY, SUSTAIN_ASPECT_FK_KEY, TIME_DIM_KEY, and ALLOCATED_PLANNED_USAGE.
  • I_SNAP$_MTH_ENTITY_PLANNED — The unique function-based index enforcing the composite business key.
  • Time dimension tables referenced by TIME_DIM_KEY / TIME_DIM_LEVEL — Supplying calendar and fiscal hierarchies (DAY, FISCAL_WEEK_ID, FISCAL_QUARTER_ID, FISCAL_YEAR_ID).
  • Entity and plant dimension tables referenced by ENTITY_FK_KEY and PLANT_FK_KEY — Supplying the organizational context of each planned usage row.
  • Sustainability aspect and site sustainability dimensions referenced by SUSTAIN_ASPECT_FK_KEY and SITE_SUSTAIN_FK_KEY — Defining the environmental or sustainability aspect being planned.
  • MTH base planned-usage tables — The underlying sources refreshed into this APPS_TS_SUMMARY materialized view.

The APPS_TS_SUMMARY tablespace placement, combined with the snapshot-style unique index naming (I_SNAP$), confirms this object is intended for query performance rather than transactional maintenance, and should be accessed primarily from reporting and analytics workloads within the ETRM schema.