Search Results ddr_sv_frcst_mv




Overview
Explore in AI Chat

Overview

DDR_SV_FRCST_MV is a materialized view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2, catalogued under the FND – Application Object Library product. Its name indicates a forecast summary object ("DDR" denoting a data repository or demand planning construct, "SV" indicating a summary view, and "FRCST" referencing forecast data). Within the EBS architecture, this object serves as a pre-aggregated analytical structure that consolidates sales and forecast figures across business calendar, organization, manufacturing item, and retail item dimensions. Because it is materialized, it stores physically persisted rows rather than resolving a query at runtime, which supports performance-intensive planning, demand review, and retail forecasting reporting.

From a Data Vault modeling perspective, the heuristic classification mined from the foreign key structure is standalone. This suggests the object functions independently of a strict hub/link/satellite hierarchy and is best modeled as a reporting satellite or aggregate fact structure. Practitioners designing downstream data vaults should treat it as a pre-joined aggregate rather than a normalized business entity.

Key Information Stored

The object contains 31 physical columns in the documented 12.2.2 schema. The most significant include the business-key dimension columns and the forecast measures:

The unique index I_SNAP$_DDR_SV_FRCST_MV, built with SYS_OP_MAP_NONNULL on the ten business-key columns, confirms that the table's grain is defined by the combination of business week, region, chain, item classes, item type, forecast purpose, and forecast type.

Common Use Cases and Queries

Typical usage involves aggregating forecast accuracy and variance across organizational and item hierarchies. A representative query pattern joins measures by business period:

  • Forecast-versus-actual variance: SELECT BSNS_MNTH_ID, SUM(FRCST_SLS_AMT_RPT), SUM(ACT_FRCST_SLS_AMT_RPT) FROM DDR_SV_FRCST_MV GROUP BY BSNS_MNTH_ID;
  • Organization chain performance: filtering on ORG_CHAIN_ID and FRCST_TYP to compare primary forecast quantities against alternate units.
  • Item class roll-ups: grouping on MFG_ITEM_CLASS_ID or RTL_ITEM_CLASS_ID for category-level demand review.
  • Grouping-based slicing: joining GROUPING_ID to HR_DM_TABLE_GROUPINGS to apply user-defined grouping hierarchies.

Related Objects

  • HR_DM_TABLE_GROUPINGS — referenced via GROUPING_ID; supplies grouping definitions used to slice the forecast data.
  • DDR_SV_FRCST_MV underlying base tables — forecast and sales transaction sources that populate the materialized view.
  • Business calendar and organization dimension tables — joined through BSNS_CLNDR_DIM_ID and ORG_DIM_ID.
  • Manufacturing and retail item dimension tables — joined through MFG_ITEM_DIM_ID and RTL_ITEM_DIM_ID.
  • HR_DM_* data mining and grouping infrastructure — provides the dimensional grouping framework referenced by GROUPING_ID.