Search Results mth_res_txn_lt_mv




Overview
Explore in AI Chat

Overview

MTH_RES_TXN_LT_MV is a materialized view owned by the APPS schema within the FND – Application Object Library product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Despite the TABLE object designation recorded in the ETRM repository, the _MV suffix and the presence of a system-generated snapshot index (I_SNAP$_MTH_RES_TXN_LT_MV) confirm that this object is a materialized view rather than a base table. It stores pre-aggregated resource transaction metrics spanning a product dimension, an item or resource dimension, and a time dimension, organized by a calendar month and a grouping identifier. In the EBS data model the object functions as a reporting and analytical staging surface, allowing cost, usage, and quantity rollups to be queried without aggregating the underlying transactional detail at runtime.

The ETRM metadata classifies this object heuristically as standalone under the data vault taxonomy. This classification is derived from an outer-join style foreign key to HR_DM_TABLE_GROUPINGS and should be read as a modeling suggestion only: the object holds no strictly enforced parent hub relationship for every row, so it is best treated as a self-contained aggregate or snapshot. Practitioners modeling a data vault around ETRM would typically represent it as a satellite-like snapshot rather than a link table, because its grain is a combination of dimension keys plus measures rather than a pure association of two business entities.

Key Information Stored

The materialized view exposes 14 documented columns. The most significant are:

  • GROUPING_ID — the foreign key to HR_DM_TABLE_GROUPINGS and the primary organizing attribute for each aggregate grouping. It is also part of the unique index.
  • PRODUCT_FK_KEY — the product dimension foreign key identifying which product the usage metrics belong to; part of the unique business key.
  • LEVEL9_FK_KEY — a hierarchical dimension key (the ninth level of the source dimension) used to slice usage by a specific level of the item or resource hierarchy; part of the unique business key.
  • DAY_ID — the day-level time key participating in the snapshot grain and unique index.
  • CALENDAR_MONTH_ID — the calendar month the aggregate belongs to; part of the unique business key and the primary fiscal/period filter.
  • TIME_DIM_KEY and TIME_DIM_LEVEL — the surrogate key and level indicator for the time dimension.
  • ITEM_DIM_KEY and ITEM_DIM_LEVEL — the surrogate key and level indicator for the item dimension.
  • USAGE_QTY and USAGE_QTY_COUNT — the aggregated usage quantity and the number of source transactions contributing to it.
  • USAGE_VAL and USAGE_VAL_COUNT — the aggregated usage value (typically currency or cost) and its contributing transaction count.
  • TOTAL_COUNT — the total number of records rolled into the row.

The unique index I_SNAP$_MTH_RES_TXN_LT_MV, built over PRODUCT_FK_KEY, CALENDAR_MONTH_ID, LEVEL9_FK_KEY, DAY_ID, and GROUPING_ID, defines the effective business key. No separate surrogate primary key column is documented, so the composite of these dimension keys serves as the row identifier.

Common Use Cases and Queries

The object is typically accessed for period-based usage and cost reporting where the caller needs aggregated quantities and values rather than transaction-level detail. A common pattern filters by calendar month and grouping, then sums across products:

  • Monthly usage value rollup by product: SELECT PRODUCT_FK_KEY, SUM(USAGE_VAL) FROM MTH_RES_TXN_LT_MV WHERE CALENDAR_MONTH_ID = :month GROUP BY PRODUCT_FK_KEY;
  • Drill-down by grouping: SELECT GROUPING_ID, SUM(USAGE_QTY), SUM(TOTAL_COUNT) FROM MTH_RES_TXN_LT_MV WHERE CALENDAR_MONTH_ID = :month GROUP BY GROUPING_ID;
  • Trend analysis comparing month-over-month usage: join the view to itself on DAY_ID or compare CALENDAR_MONTH_ID partitions.

Because the object is a snapshot, freshness depends on the refresh schedule defined for the materialized view; queries used for period-close reporting should account for this. Count columns (USAGE_QTY_COUNT, USAGE_VAL_COUNT, TOTAL_COUNT) support averaging and anomaly detection where raw totals would be misleading.

Related Objects

The documented relationship connects this object to the grouping repository; additional dependencies follow standard EBS dimensional conventions:

  • HR_DM_TABLE_GROUPINGS — referenced via MTH_RES_TXN_LT_MV.GROUPING_ID; defines the grouping definition applied to the aggregate.
  • MRC / GL calendar tables — CALENDAR_MONTH_ID and DAY_ID resolve against the accounting calendar hierarchy used for period reporting.
  • Item and product dimension views — ITEM_DIM_KEY and PRODUCT_FK_KEY resolve to the corresponding item and product dimension definitions.
  • FND materialized view definition and refresh log objects — govern the snapshot's refresh behavior.
  • Depending reports and concurrent programs — external reports and FND concurrent programs that query the aggregate rather than the base transaction tables.