Search Results mth_res_req_lt_mv_b2

Overview

APPS.MTH_RES_REQ_LT_MV is a materialized view residing in the APPS schema and stored within the APPS_TS_SUMMARY tablespace, a location typical of pre-aggregated reporting structures in Oracle E-Business Suite. The object is registered as FND Design Data (FND.MTH_RES_REQ_LT_MV) and carries a VALID status in both EBS 12.1.1 and 12.2.2. The name follows the ETRM convention for monthly resource requirement aggregations, and the presence of dimension surrogate keys indicates that it supports the Oracle Enterprise Data Warehouse-style star schema used by ETRM dashboards and reports.

Based on the heuristic Data Vault classification mined from the FK structure, this object is best modeled as a standalone object rather than a traditional hub, link, or satellite. It exposes a single documented referential link, GROUPING_ID referencing APPS.HR_DM_TABLE_GROUPINGS, and otherwise holds no outgoing foreign keys to operational entities. This suggests the view functions as a denormalized reporting snapshot rather than a transactional integration point.

Key Information Stored

Of the fourteen documented columns, the following are most significant for reporting and analysis:

• PRODUCT_FK_KEY and LEVEL9_FK_KEY — surrogate keys to the product and level-nine dimension members represented by each aggregated row.
• PLANNED_USAGE_QTY, PLANNED_USAGE_QTY_COUNT, PLANNED_USAGE_VAL, and PLANNED_USAGE_VAL_COUNT — the core measures: planned usage quantity and monetary value, each with a companion count column used to reconstruct averages and weighted sums.
• DAY_ID and CALENDAR_MONTH_ID — date and month grain identifiers.
• TOTAL_COUNT — the aggregate row count supporting the measure set.
• GROUPING_ID — the grouping level of the aggregation; the only column with a documented foreign key, pointing to HR_DM_TABLE_GROUPINGS.
• TIME_DIM_KEY, TIME_DIM_LEVEL, ITEM_DIM_KEY, ITEM_DIM_LEVEL — dimension surrogate keys and their hierarchy levels.

The unique candidate business key is enforced by the function-based index I_SNAP$_MTH_RES_REQ_LT_MV over SYS_OP_MAP_NONNULL applied to PRODUCT_FK_KEY, CALENDAR_MONTH_ID, LEVEL9_FK_KEY, DAY_ID, and GROUPING_ID. Two bitmap indexes, MTH_RES_REQ_LT_MV_B1 (TIME_DIM_KEY) and MTH_RES_REQ_LT_MV_B2 (ITEM_DIM_KEY), accelerate dimension-level filtering. The searched term item_dim_key corresponds exactly to the column indexed by MTH_RES_REQ_LT_MV_B2.

Common Use Cases and Queries

The primary use case is downstream reporting and dashboarding on the ETRM resource requirement subject area. A canonical extraction follows the documented query text:

SELECT PRODUCT_FK_KEY, LEVEL9_FK_KEY,
       PLANNED_USAGE_QTY, PLANNED_USAGE_VAL,
       TIME_DIM_KEY, ITEM_DIM_KEY,
       CALENDAR_MONTH_ID
FROM   APPS.MTH_RES_REQ_LT_MV
WHERE  ITEM_DIM_KEY = :p_item
AND    CALENDAR_MONTH_ID = :p_month;

Bitmap index MTH_RES_REQ_LT_MV_B2 makes single-item filtering efficient, while MTH_RES_REQ_LT_MV_B1 supports time-based slicing. Analysts typically join TIME_DIM_KEY and ITEM_DIM_KEY to their dimension members for descriptive labels, and GROUPING_ID to HR_DM_TABLE_GROUPINGS to resolve the aggregation tier. Because of the companion _COUNT columns, weighted averages over items and months can be computed directly without re-scanning base transactions.

Related Objects

• APPS.HR_DM_TABLE_GROUPINGS — referenced via GROUPING_ID, the sole documented foreign key.
• Dimension members resolved through TIME_DIM_KEY and ITEM_DIM_KEY — the time and item dimensions underlying the bitmap indexes.
• I_SNAP$_MTH_RES_REQ_LT_MV — the unique function-based snapshot index.
• MTH_RES_REQ_LT_MV_B1 and MTH_RES_REQ_LT_MV_B2 — bitmap indexes on TIME_DIM_KEY and ITEM_DIM_KEY respectively.
• FND.MTH_RES_REQ_LT_MV — the design data registration controlling the object in EBS.