Search Results rtl_item_dim_id




Overview
Explore in AI Chat

Overview

APPS.DDR_SV_ORDR_MV is a materialized view residing in the APPS schema, classified under FND Design Data as FND.DDR_SV_ORDR_MV. It is stored in the APPS_TS_SUMMARY tablespace with a PCT Free of 10, which is consistent with its role as an aggregated reporting structure rather than a high-volume transactional table. The object forms part of the Distributed Data Repository (DDR) family of summary objects used within Oracle EBS to support demand planning, order visibility, and supply chain analytics. The "SV" prefix indicates a summary view, and the "ORDR" token denotes order-related measurements. The view consolidates order quantities and order amounts across a star-like dimensional model consisting of business calendar, organization, manufacturing item, and retail item dimensions.

The heuristic Data Vault classification mined from the foreign key structure is standalone. In Data Vault terms this suggests the object is neither a hub, link, nor satellite in a strict sense; rather it behaves as a self-contained aggregated mart. Modelers should treat it as a derived reporting aggregate that references a grouping table (HR_DM_TABLE_GROUPINGS) via GROUPING_ID, rather than as a first-class business entity.

Key Information Stored

The object contains 23 documented columns. The most significant are the dimensional keys and level indicators that drive aggregation, together with the order measures. The user search term ORG_DIM_ID is central to this table: it is the surrogate key for the organization dimension, paired with ORG_DIM_LVL, a CHAR column indicating the level of the organization hierarchy at which the row is aggregated. ORG_DIM_ID is indexed by bitmap index DDR_SV_ORDR_MV_B3, which is typical for dimensional keys in a summary fact.

Common Use Cases and Queries

This object is typically queried for order bookings and demand reporting by organization, item, and calendar hierarchies. A common pattern filters by a specific organization dimension key and rolls up by calendar level:

  • Organization-level order summary: SELECT ORG_DIM_ID, SUM(ORDR_AMT) FROM APPS.DDR_SV_ORDR_MV WHERE BSNS_CLNDR_DIM_LVL = 'MONTH' GROUP BY ORG_DIM_ID. The bitmap index DDR_SV_ORDR_MV_B3 supports the ORG_DIM_ID predicate.
  • Item class trends: aggregate ORDR_QTY_PRMRY across MFG_ITEM_CLASS_ID and RTL_ITEM_CLASS_ID to compare manufacturing versus retail item performance.
  • Currency-aware reporting: use ORDR_AMT_RPT for reporting-currency totals and ORDR_AMT for transactional-currency detail.
  • Average order value: divide ORDR_AMT by CNT_ORDR_AMT to derive per-order averages, exploiting the paired measure/count columns.
  • Grouping-aware queries: constrain or group by GROUPING_ID to distinguish subtotal rows from detail rows in the aggregate.

Reports should favour the bitmap indexes for dimensional filters and rely on the unique snap index for point lookups by the composite key. Because the object resides in APPS_TS_SUMMARY, it is refreshed rather than transactionally current, so reporting windows should align with the refresh schedule.

Related Objects

The documented foreign key relationship links GROUPING_ID to APPS.HR_DM_TABLE_GROUPINGS, which governs how grouped aggregates are labeled and consumed by downstream reporting. Beyond this, the object is a summary view whose lineage traces to the DDR dimensional model rather than to transactional order tables directly.

  • APPS.HR_DM_TABLE_GROUPINGS — joined on GROUPING_ID; defines grouping semantics for aggregate rows.
  • DDR calendar, organization, manufacturing item, and retail item dimension objects — joined via BSNS_CLNDR_DIM_ID, ORG_DIM_ID, MFG_ITEM_DIM_ID, and RTL_ITEM_DIM_ID respectively, each keyed by matching _DIM_LVL columns.
  • FND.DDR_SV_ORDR_MV — the FND design-data registration that manages the view definition and refresh.
  • Sibling DDR_SV summary views — companion summary objects covering adjacent subject areas, typically consumed together in EBS supply chain dashboards.

Because the object is classified as standalone, it does not act as a hub or satellite feeding other Data Vault structures; it should be treated as a terminal reporting aggregate within the ETRM 12.1.1 and 12.2.2 environments.