MATERIALIZED VIEW: APPS.DDR_SV_ORDR_MV

Source

select                   
  ordr.BSNS_MNTH_ID,
  ordr.ORG_CHAIN_ID,
  ordr.MFG_ITEM_CLASS_ID,
  ordr.RTL_ITEM_CLASS_ID,
  ordr.ITM_TYP,
  ordr.BSNS_MNTH_ID as BSNS_CLNDR_DIM_ID,
  'MNTH' as BSNS_CLNDR_DIM_LVL,
  ordr.ORG_CHAIN_ID as ORG_DIM_ID,
  'CHN' as ORG_DIM_LVL,
  ordr.MFG_ITEM_CLASS_ID as MFG_ITEM_DIM_ID,
  'CLS' as MFG_ITEM_DIM_LVL,
  ordr.RTL_ITEM_CLASS_ID as RTL_ITEM_DIM_ID,
  'CLS' as RTL_ITEM_DIM_LVL,
  count(*) as CNT_TOTAL,
  sum(ordr.ORDR_QTY_PRMRY) as ORDR_QTY_PRMRY,
  sum(ordr.ORDR_QTY_ALT) as ORDR_QTY_ALT,
  sum(ordr.ORDR_AMT) as ORDR_AMT,
  sum(ordr.ORDR_AMT_RPT) as ORDR_AMT_RPT,
  count(ordr.ORDR_QTY_PRMRY) as CNT_ORDR_QTY_PRMRY,
  count(ordr.ORDR_QTY_ALT) as CNT_ORDR_QTY_ALT,
  count(ordr.ORDR_AMT) as CNT_ORDR_AMT,
  count(ordr.ORDR_AMT_RPT) as CNT_ORDR_AMT_RPT,
  grouping_id (ordr.MFG_ITEM_CLASS_ID, ordr.RTL_ITEM_CLASS_ID,
               ordr.BSNS_MNTH_ID, ordr.ORG_CHAIN_ID, ordr.ITM_TYP) GROUPING_ID
from
  ddr_bs_ordr_itm_bu_wk_mv       ordr
group by
  Grouping sets((ordr.BSNS_MNTH_ID, ordr.ORG_CHAIN_ID, ordr.MFG_ITEM_CLASS_ID, ordr.RTL_ITEM_CLASS_ID, ordr.ITM_TYP))