Search Results ddr_sv_slsr_mv




Overview
Explore in AI Chat

Overview

DDR_SV_SLSR_MV is a materialized view object registered in the APPS schema under the FND – Application Object Library product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It is catalogued in ETRM as a TABLE object with VALID status and is owned by the APPS schema. The object supports analytical and summarization workloads tied to retail sales and return activity, aggregating transactional facts across business calendar, organization, market, channel, and item dimensions. Its scope spans sales quantity, amount, cost, and corresponding return metrics, alongside count-based measures used for averaging and frequency analysis.

From a Data Vault modeling perspective, the object is heuristically classified as standalone, indicating that the FK structure does not resolve it into a conventional hub, link, or satellite role. This classification is a modeling suggestion only: the object itself behaves as a denormalized aggregation surface rather than a normalized integration-layer artifact. The single documented foreign key, GROUPING_ID → HR_DM_TABLE_GROUPINGS, indicates a metadata linkage to a table grouping registry rather than a dimensional relationship, reinforcing its standalone grouping classification.

Key Information Stored

The object documents 46 physical columns. The most consequential are the dimensional keys and the aggregated measures they slice:

The unique index I_SNAP$_DDR_SV_SLSR_MV defines the business-key candidate set: BSNS_MNTH_ID, ORG_RGN_ID, MFG_ITEM_CLASS_ID, RTL_ITEM_CLASS_ID, MKT_AREA_ID, CHNL_TYP_CD, ITM_TYP, SLS_TYP_CD, ORG_CHAIN_ID, BSNS_WK_ID, and GROUPING_ID, combined through SYS_OP_MAP_NONNULL to preserve uniqueness across null-bearing dimension columns. No separate surrogate primary key is documented beyond this composite unique index.

Common Use Cases and Queries

This object is typically queried for retail sales performance reporting, return-rate analysis, and cube-style aggregation. A representative query aggregates sales amount by month and region:

SELECT BSNS_MNTH_ID, ORG_RGN_ID, SUM(SLS_AMT) SLS_AMT, SUM(RTRN_AMT) RTRN_AMT FROM APPS.DDR_SV_SLSR_MV GROUP BY BSNS_MNTH_ID, ORG_RGN_ID;

A return-rate query compares returns against sales at the item class and channel grain:

SELECT MFG_ITEM_CLASS_ID, CHNL_TYP_CD, SUM(RTRN_QTY_PRMRY)/NULLIF(SUM(SLS_QTY_PRMRY),0) RTRN_RATE FROM APPS.DDR_SV_SLSR_MV GROUP BY MFG_ITEM_CLASS_ID, CHNL_TYP_CD;

Cost-to-sales margin analysis uses SLS_COST_AMT_RPT and SLS_AMT_RPT. Joining back to HR_DM_TABLE_GROUPINGS on GROUPING_ID allows consumers to filter to a named grouping, which is useful for snapshot-based reporting where multiple groupings coexist in the same physical object.

Related Objects

  • HR_DM_TABLE_GROUPINGS — referenced by the FK DDR_SV_SLSR_MV.GROUPING_ID → HR_DM_TABLE_GROUPINGS, providing grouping metadata for the snapshot rows.
  • HR_DM_TABLE_GROUPINGS.GROUPING_ID — the join key for grouping-aware filters and reporting.
  • I_SNAP$_DDR_SV_SLSR_MV — the unique index enforcing the composite business-key candidate set.
  • APPS.DDR_SV_SLSR_MV — the registered APPS-schema object that reporting and ETL consumers query directly.

The metadata does not document outbound child references from this object, consistent with its classification as a standalone aggregation surface within the FND application object library.