Results for “ddr_sv_ship_mv”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

DDR_SV_SHIP_MV is an APPS-owned materialized view (despite its registration as a TABLE object type in the ETRM repository) that resides in the FND – Application Object Library product schema. It serves as a pre-aggregated shipment fact table within Oracle EBS supply chain analytics, storing shipment quantities, amounts, and costs across multiple business dimensions including organization, ship-to location, manufacturing item class, retail item class, and business calendar. The "SV" prefix and "_MV" suffix indicate a reporting-oriented summary view, typically refreshed on a scheduled basis from underlying transactional shipment data.

From a Data Vault modeling perspective, the heuristic classification is standalone, derived from its limited foreign key footprint. Only one declared FK relationship exists (GROUPING_ID → HR_DM_TABLE_GROUPINGS), suggesting this object functions primarily as a denormalized dimensional aggregate rather than a normalized hub, link, or satellite. In practice, the multi-column unique index I_SNAP$_DDR_SV_SHIP_MV behaves like a snapshot grain definition, making this object most analogous to a satellite positioned at the intersection of business calendar, organization chain, and item classification dimensions.

Key Information Stored

The table contains 27 documented columns. Its effective grain is defined by the unique index I_SNAP$_DDR_SV_SHIP_MV, which combines ORG_CHAIN_ID, SHIP_TO_CHAIN_ID, BSNS_MNTH_ID, MFG_ITEM_CLASS_ID, RTL_ITEM_CLASS_ID, and GROUPING_ID using SYS_OP_MAP_NONNULL wrappers to handle nullable key components. These six columns constitute the business-key candidates.

The most significant measure columns are:

• ORG_CHAIN_ID / SHIP_TO_CHAIN_ID – organization hierarchy and ship-to hierarchy chain identifiers.
• BSNS_MNTH_ID / BSNS_CLNDR_DIM_ID / BSNS_CLNDR_DIM_LVL – business month and calendar dimension references and their level indicators.
• MFG_ITEM_CLASS_ID / RTL_ITEM_CLASS_ID – manufacturing and retail item classification keys.
• ORG_DIM_ID / ORG_DIM_LVL, MFG_ITEM_DIM_ID / MFG_ITEM_DIM_LVL, RTL_ITEM_DIM_ID / RTL_ITEM_DIM_LVL – dimensional surrogate keys and level codes supporting drill-down.
• SHIP_QTY_PRMRY, SHIP_QTY_ALT – shipped quantity in primary and alternate units of measure.
• SHIP_AMT, SHIP_AMT_RPT, SHIP_COST, SHIP_COST_RPT – shipment amounts and costs in transactional and reporting currencies.
• CNT_TOTAL – total count of shipment records aggregated into the row.
• CNT_SHIP_QTY_PRMRY, CNT_SHIP_QTY_ALT, CNT_SHIP_AMT, CNT_SHIP_COST – supporting count metrics paired with each core measure for weighted averaging and distinct-record analysis.
• GROUPING_ID – the sole foreign key, referencing HR_DM_TABLE_GROUPINGS to identify the grouping definition applied.

Common Use Cases and Queries

This materialized view supports shipment performance reporting and dimensional analysis. Typical patterns include aggregation by business month and organization chain, or comparison of primary versus alternate unit quantities across item classes.

• Monthly shipment trending: SELECT bsns_mnth_id, SUM(ship_amt_rpt) FROM ddr_sv_ship_mv GROUP BY bsns_mnth_id.
• Organization chain performance: aggregate SHIP_COST_RPT and CNT_SHIP_COST by ORG_CHAIN_ID and ORG_DIM_LVL.
• Item class mix analysis: group by MFG_ITEM_CLASS_ID and RTL_ITEM_CLASS_ID to evaluate shipment value distribution.
• Ship-to analysis: rank SHIP_TO_CHAIN_ID by SHIP_QTY_PRMRY.
• Currency-normalized reporting using the _RPT columns for consolidated multi-currency rollups.

Because the object is a snapshot materialized view, queries should account for refresh lag relative to base transactional tables.

Related Objects

• HR_DM_TABLE_GROUPINGS – joined via GROUPING_ID; defines the grouping context for each snapshot row.
• APPS.DDR_SV_SHIP (base transactional counterpart) – presumed source for the materialized view refresh.
• Business calendar dimension tables referenced by BSNS_CLNDR_DIM_ID for period decoding.
• Organization hierarchy chain tables backing ORG_CHAIN_ID and ORG_DIM_ID.
• Item classification tables behind MFG_ITEM_CLASS_ID and RTL_ITEM_CLASS_ID.
• Standard EBS supply chain reporting views (e.g., shipment summary dashboards) that consume this aggregate.

Join key discipline should follow the unique index columns to preserve the intended grain and avoid fan-out duplication in downstream reports.