Search Results mth_res_req_lt_mv




Overview
Explore in AI Chat

Overview

MTH_RES_REQ_LT_MV is a materialized view registered in the APPS schema of Oracle E-Business Suite, catalogued under the FND – Application Object Library product family. Although it is exposed as a TABLE object type in the data dictionary, its naming convention (the _MV suffix) and the presence of a system-generated snapshot index (I_SNAP$_MTH_RES_REQ_LT_MV) confirm that it is a materialized view used to pre-aggregate and persist summarized resource requirement and planning data. It is present and valid in both the 12.1.1 and 12.2.2 releases, with the 12.2.2 physical schema documenting 14 columns.

The object carries a heuristic Data Vault classification of standalone, derived from its foreign key structure. In Data Vault modeling terms this suggests the object behaves as a self-contained reporting artifact rather than participating in a normalized hub-and-link topology. It consolidates dimensional keys (product, item, time) alongside measures (planned usage quantity, value, and their associated counts), making it a satellite-like reporting snapshot rather than a transactional source of record. The single documented foreign key — MTH_RES_REQ_LT_MV.GROUPING_ID referencing HR_DM_TABLE_GROUPINGS — links the aggregated measures to a grouping definition, tying the summarized rows back to a configured grouping context.

Key Information Stored

The 14 documented columns fall into three functional groupings: dimensional keys, grouping metadata, and aggregated measures.

  • PRODUCT_FK_KEY — Foreign-key surrogate identifying the product dimension associated with the requirement row.
  • ITEM_DIM_KEY / ITEM_DIM_LEVEL — Surrogate key and level indicator for the item dimension, enabling drill-down or roll-up across item hierarchies.
  • TIME_DIM_KEY / TIME_DIM_LEVEL — Surrogate key and level indicator for the time dimension.
  • DAY_ID — Day-level identifier anchoring the row to a specific date within the calendar.
  • CALENDAR_MONTH_ID — Calendar month identifier supporting monthly-level aggregation.
  • LEVEL9_FK_KEY — Foreign-key key representing a level-9 hierarchy node (the deepest documented level in the snapshot key).
  • GROUPING_ID — Links the row to HR_DM_TABLE_GROUPINGS; a grouping definition context.
  • PLANNED_USAGE_QTY / PLANNED_USAGE_QTY_COUNT — Planned usage quantity measure and its record count.
  • PLANNED_USAGE_VAL / PLANNED_USAGE_VAL_COUNT — Planned usage value measure and its record count.
  • TOTAL_COUNT — Aggregate record count for the grouped row.

No dedicated single-column surrogate primary key is documented. The unique index I_SNAP$_MTH_RES_REQ_LT_MV functions as the effective business key over the composite of PRODUCT_FK_KEY, CALENDAR_MONTH_ID, LEVEL9_FK_KEY, DAY_ID, and GROUPING_ID. This composite uniquely identifies each aggregated snapshot row, and the presence of SYS_OP_MAP_NONNULL wrappers indicates many of these keys are nullable but treated as non-null for uniqueness enforcement.

Common Use Cases and Queries

The materialized view is typically queried for planning analytics, resource requirement reporting, and period-over-period comparisons. Because the measures are already summarized, queries avoid the cost of joining back to base transaction tables.

  • Monthly planned usage reporting: Aggregate PLANNED_USAGE_QTY or PLANNED_USAGE_VAL grouped by CALENDAR_MONTH_ID and PRODUCT_FK_KEY.
  • Item-level drill-down: Filter on ITEM_DIM_KEY and ITEM_DIM_LEVEL to traverse item hierarchy levels.
  • Grouping-scoped analysis: Restrict by GROUPING_ID to analyze a specific grouping definition configured in HR_DM_TABLE_GROUPINGS.
  • Day-level trending: Use DAY_ID together with TIME_DIM_LEVEL to chart daily resource consumption.

A representative pattern: SELECT CALENDAR_MONTH_ID, PRODUCT_FK_KEY, SUM(PLANNED_USAGE_QTY), SUM(PLANNED_USAGE_VAL) FROM APPS.MTH_RES_REQ_LT_MV WHERE GROUPING_ID = :p_grouping_id GROUP BY CALENDAR_MONTH_ID, PRODUCT_FK_KEY; — leveraging the covered unique index. Count columns (PLANNED_USAGE_QTY_COUNT, TOTAL_COUNT) support density and data-quality validation alongside the summed measures.

Related Objects

  • HR_DM_TABLE_GROUPINGS — Referenced through MTH_RES_REQ_LT_MV.GROUPING_ID; the only documented foreign key target, providing grouping context.
  • I_SNAP$_MTH_RES_REQ_LT_MV — The unique system-generated snapshot index enforcing row uniqueness over the composite business key.
  • Product dimension tables — Joined via PRODUCT_FK_KEY for product attribute resolution.
  • Item dimension tables — Joined via ITEM_DIM_KEY / ITEM_DIM_LEVEL for hierarchy traversal.
  • Time dimension tables — Joined via TIME_DIM_KEY / TIME_DIM_LEVEL for calendar roll-ups.
  • Calendar / day tables — Joined via DAY_ID and CALENDAR_MONTH_ID for date attribution.

Because the underlying refresh source tables are not documented in the supplied metadata, dependent objects should be confirmed against the actual materialized view definition in the target instance before building downstream integrations or reports.