Search Results clndr_dim_lvl




Overview
Explore in AI Chat

Overview

APPS.DDR_SV_MFG_SHIP_C_MV is a materialized view owned by the APPS schema and stored in the APPS_TS_SUMMARY tablespace. It forms part of the Oracle E-Business Suite Daily Business Intelligence (DBI) and Enterprise Tax/Transaction Reporting Model (ETRM) reporting infrastructure, specifically within the manufacturing shipping subject area. The object summarizes manufacturing shipment activity across three analytic dimensions: calendar, organization, and manufacturing item. Because the table resides in a summary tablespace and carries aggregated numeric measures alongside dimension level indicators, it functions as a pre-computed rollup that supports drill-down and roll-up reporting without querying transactional tables.

In Data Vault modeling terms, the heuristic classification of this object is satellite. It stores descriptive, aggregated measures (quantities, amounts, and costs) attached to a set of dimension keys, rather than serving as a pure hub (which would hold only a business key) or a link (which would resolve many-to-many relationships). The GROUPING_ID column and the nullable dimension-level columns (CLNDR_DIM_LVL, ORG_DIM_LVL, MFG_ITEM_DIM_LVL) further reinforce its role as a satellite populated by a grouping-set or cube-style aggregation.

Key Information Stored

The table contains 20 documented columns. The most significant are:

  • CLNDR_MNTH_ID – Calendar month identifier used to slice shipments by accounting or reporting period.
  • ORG_CHAIN_ID – Organization dimension chain identifier, representing the inventory/operating organization hierarchy node.
  • SHIP_TO_CHAIN_ID – Ship-to customer or location chain identifier for the receiving party.
  • MFG_ITEM_CLASS_ID – Manufacturing item classification key, grouping shipped items by class or category.
  • CLNDR_DIM_LVL, ORG_DIM_LVL, MFG_ITEM_DIM_LVL – Level indicators that identify which hierarchy level each aggregated row represents, enabling drill-down navigation.
  • CLNDR_DIM_ID, ORG_DIM_ID, MFG_ITEM_DIM_ID – Surrogate identifiers for each of the three dimensions.
  • GROUPING_ID – Bitmap identifier produced by SQL GROUPING SETS/ROLLUP, indicating which dimension combinations are aggregated in a given row. This column participates in the unique index and references HR_DM_TABLE_GROUPINGS.
  • CNT_TOTAL, CNT_SHIP_QTY_PRMRY, CNT_SHIP_QTY_ALT, CNT_SHIP_AMT, CNT_SHIP_COST – Count measures for the number of shipments and correlated quantities, amounts, and costs.
  • SHIP_QTY_PRMRY, SHIP_QTY_ALT, SHIP_AMT, SHIP_COST – Summed shipped quantities in primary and alternate units, shipment amount, and shipment cost.

The surrogate primary key is enforced by the unique function-based index I_SNAP$_DDR_SV_MFG_SHIP_C_, which applies SYS_OP_MAP_NONNULL to SHIP_TO_CHAIN_ID, ORG_CHAIN_ID, CLNDR_MNTH_ID, MFG_ITEM_CLASS_ID, and GROUPING_ID. These five columns therefore constitute the composite business-key candidate for the aggregated grain of the table.

Common Use Cases and Queries

The primary use case is manufacturing shipment reporting with hierarchical drill-down. Analysts filter on ORG_DIM_LVL or MFG_ITEM_DIM_LVL to control the aggregation depth and use GROUPING_ID to distinguish subtotal rows from detail rows. The following pattern retrieves organization-level shipment totals for a reporting month:

  • Query columns CLNDR_MNTH_ID, ORG_CHAIN_ID, SHIP_QTY_PRMRY, SHIP_AMT, SHIP_COST filtered by CLNDR_MNTH_ID and ORG_DIM_LVL to report month-over-month manufacturing shipment performance.
  • Join to organization and item dimension views on ORG_DIM_ID and MFG_ITEM_DIM_ID to resolve descriptive names for a management dashboard.
  • Aggregate CNT_TOTAL and CNT_SHIP_COST to compute average shipment cost per shipment.

A representative query is:

SELECT CLNDR_MNTH_ID, ORG_CHAIN_ID, SUM(SHIP_QTY_PRMRY) SHIP_QTY, SUM(SHIP_AMT) AMT FROM APPS.DDR_SV_MFG_SHIP_C_MV WHERE ORG_DIM_LVL = :lvl AND GROUPING_ID = :grp GROUP BY CLNDR_MNTH_ID, ORG_CHAIN_ID;

Because the view is pre-aggregated, it is well suited to DBI dashboards, period-end manufacturing variance reporting, and freight or shipping cost trend analysis.

Related Objects

  • APPS.HR_DM_TABLE_GROUPINGS – Referenced by the GROUPING_ID foreign key; documents the grouping-set definitions that produce each aggregated row.
  • APPS.DDR_SV_MFG_SHIP_C_MV – Self-referencing entry in the dependency listing, reflecting the materialized view's own definition.
  • Calendar dimension objects – Accessed via CLNDR_DIM_ID to resolve month and period labels for CLNDR_MNTH_ID.
  • Organization dimension objects – Accessed via ORG_DIM_ID to expand the ORG_CHAIN_ID hierarchy.
  • Item dimension objects – Accessed via MFG_ITEM_DIM_ID to resolve MFG_ITEM_CLASS_ID descriptions.
  • Ship-to and customer dimension objects – Accessed via SHIP_TO_CHAIN_ID to identify receiving parties.

The documented dependencies indicate that DDR_SV_MFG_SHIP_C_MV does not reference other database objects directly; it is referenced by downstream DBI reporting views that consume its aggregated measures.