VIEW: APPS.DDR_SV_FRCST_MV#

Source

View Text - Preformatted

select                   
  frcst.BSNS_WK_ID,
  frcst.BSNS_MNTH_ID,
  frcst.ORG_RGN_ID,
  frcst.ORG_CHAIN_ID,
  frcst.MFG_ITEM_CLASS_ID,
  frcst.RTL_ITEM_CLASS_ID,
  frcst.ITM_TYP,
  frcst.FRCST_PURP,
  frcst.FRCST_TYP,
  decode (grouping_id (frcst.BSNS_MNTH_ID, frcst.BSNS_WK_ID),
          2, frcst.BSNS_WK_ID,
          1, frcst.BSNS_MNTH_ID) as BSNS_CLNDR_DIM_ID,
  decode (grouping_id (frcst.BSNS_MNTH_ID, frcst.BSNS_WK_ID), 
          2, 'WK',
          1, 'MNTH') as BSNS_CLNDR_DIM_LVL,
  decode (grouping_id (frcst.ORG_CHAIN_ID, frcst.ORG_RGN_ID),
          2, frcst.ORG_RGN_ID,
          1, frcst.ORG_CHAIN_ID) as ORG_DIM_ID,
  decode (grouping_id (frcst.ORG_CHAIN_ID, frcst.ORG_RGN_ID),
          2, 'RGN',
          1, 'CHN') as ORG_DIM_LVL,
  frcst.MFG_ITEM_CLASS_ID as MFG_ITEM_DIM_ID,
  decode (frcst.MFG_ITEM_CLASS_ID, 
          NULL, NULL,
          'CLS') as MFG_ITEM_DIM_LVL,
  frcst.RTL_ITEM_CLASS_ID as RTL_ITEM_DIM_ID,
  decode (frcst.RTL_ITEM_CLASS_ID, 
          NULL, NULL,
          'CLS') as RTL_ITEM_DIM_LVL,
  count(*) as CNT_TOTAL,
  sum(frcst.ACT_FRCST_SLS_AMT_RPT) as ACT_FRCST_SLS_AMT_RPT,
  sum(frcst.ACT_FRCST_SLS_QTY_PRMRY) as ACT_FRCST_SLS_QTY_PRMRY,
  sum(frcst.FRCST_SLS_QTY_PRMRY) as FRCST_SLS_QTY_PRMRY,
  sum(frcst.FRCST_SLS_QTY_ALT) as FRCST_SLS_QTY_ALT,
  sum(frcst.FRCST_SLS_AMT) as FRCST_SLS_AMT,
  sum(frcst.FRCST_SLS_AMT_RPT) as FRCST_SLS_AMT_RPT,
  count(frcst.ACT_FRCST_SLS_AMT_RPT) as CNT_ACT_FRCST_SLS_AMT_RPT,
  count(frcst.ACT_FRCST_SLS_QTY_PRMRY) as CNT_ACT_FRCST_SLS_QTY_PRMRY,
  count(frcst.FRCST_SLS_QTY_PRMRY) as CNT_FRCST_SLS_QTY_PRMRY,
  count(frcst.FRCST_SLS_QTY_ALT) as CNT_FRCST_SLS_QTY_ALT,
  count(frcst.FRCST_SLS_AMT) as CNT_FRCST_SLS_AMT,
  count(frcst.FRCST_SLS_AMT_RPT) as CNT_FRCST_SLS_AMT_RPT,
  grouping_id (frcst.MFG_ITEM_CLASS_ID, frcst.RTL_ITEM_CLASS_ID,
               frcst.BSNS_WK_ID, frcst.BSNS_MNTH_ID, 
               frcst.ORG_RGN_ID, frcst.ORG_CHAIN_ID,
               frcst.ITM_TYP, frcst.FRCST_PURP, frcst.FRCST_TYP) GROUPING_ID
from
  ddr_bs_frcst_itm_bu_wk_mv      frcst
group by
   Grouping sets((frcst.BSNS_MNTH_ID, frcst.ORG_RGN_ID,   frcst.MFG_ITEM_CLASS_ID, frcst.RTL_ITEM_CLASS_ID, frcst.ITM_TYP, frcst.FRCST_PURP, frcst.FRCST_TYP),
                (frcst.BSNS_MNTH_ID, frcst.ORG_CHAIN_ID, frcst.MFG_ITEM_CLASS_ID, frcst.RTL_ITEM_CLASS_ID, frcst.ITM_TYP, frcst.FRCST_PURP, frcst.FRCST_TYP),
                (frcst.BSNS_WK_ID,   frcst.ORG_RGN_ID,   frcst.ITM_TYP, frcst.FRCST_PURP, frcst.FRCST_TYP),
                (frcst.BSNS_WK_ID,   frcst.ORG_CHAIN_ID, frcst.ITM_TYP, frcst.FRCST_PURP, frcst.FRCST_TYP),
                (frcst.BSNS_WK_ID,   frcst.ORG_CHAIN_ID, frcst.RTL_ITEM_CLASS_ID, frcst.ITM_TYP, frcst.FRCST_PURP, frcst.FRCST_TYP))

View Text - HTML Formatted

SELECT FRCST.BSNS_WK_ID
, FRCST.BSNS_MNTH_ID
, FRCST.ORG_RGN_ID
, FRCST.ORG_CHAIN_ID
, FRCST.MFG_ITEM_CLASS_ID
, FRCST.RTL_ITEM_CLASS_ID
, FRCST.ITM_TYP
, FRCST.FRCST_PURP
, FRCST.FRCST_TYP
, DECODE (GROUPING_ID (FRCST.BSNS_MNTH_ID
, FRCST.BSNS_WK_ID)
, 2
, FRCST.BSNS_WK_ID
, 1
, FRCST.BSNS_MNTH_ID) AS BSNS_CLNDR_DIM_ID
, DECODE (GROUPING_ID (FRCST.BSNS_MNTH_ID
, FRCST.BSNS_WK_ID)
, 2
, 'WK'
, 1
, 'MNTH') AS BSNS_CLNDR_DIM_LVL
, DECODE (GROUPING_ID (FRCST.ORG_CHAIN_ID
, FRCST.ORG_RGN_ID)
, 2
, FRCST.ORG_RGN_ID
, 1
, FRCST.ORG_CHAIN_ID) AS ORG_DIM_ID
, DECODE (GROUPING_ID (FRCST.ORG_CHAIN_ID
, FRCST.ORG_RGN_ID)
, 2
, 'RGN'
, 1
, 'CHN') AS ORG_DIM_LVL
, FRCST.MFG_ITEM_CLASS_ID AS MFG_ITEM_DIM_ID
, DECODE (FRCST.MFG_ITEM_CLASS_ID
, NULL
, NULL
, 'CLS') AS MFG_ITEM_DIM_LVL
, FRCST.RTL_ITEM_CLASS_ID AS RTL_ITEM_DIM_ID
, DECODE (FRCST.RTL_ITEM_CLASS_ID
, NULL
, NULL
, 'CLS') AS RTL_ITEM_DIM_LVL
, COUNT(*) AS CNT_TOTAL
, SUM(FRCST.ACT_FRCST_SLS_AMT_RPT) AS ACT_FRCST_SLS_AMT_RPT
, SUM(FRCST.ACT_FRCST_SLS_QTY_PRMRY) AS ACT_FRCST_SLS_QTY_PRMRY
, SUM(FRCST.FRCST_SLS_QTY_PRMRY) AS FRCST_SLS_QTY_PRMRY
, SUM(FRCST.FRCST_SLS_QTY_ALT) AS FRCST_SLS_QTY_ALT
, SUM(FRCST.FRCST_SLS_AMT) AS FRCST_SLS_AMT
, SUM(FRCST.FRCST_SLS_AMT_RPT) AS FRCST_SLS_AMT_RPT
, COUNT(FRCST.ACT_FRCST_SLS_AMT_RPT) AS CNT_ACT_FRCST_SLS_AMT_RPT
, COUNT(FRCST.ACT_FRCST_SLS_QTY_PRMRY) AS CNT_ACT_FRCST_SLS_QTY_PRMRY
, COUNT(FRCST.FRCST_SLS_QTY_PRMRY) AS CNT_FRCST_SLS_QTY_PRMRY
, COUNT(FRCST.FRCST_SLS_QTY_ALT) AS CNT_FRCST_SLS_QTY_ALT
, COUNT(FRCST.FRCST_SLS_AMT) AS CNT_FRCST_SLS_AMT
, COUNT(FRCST.FRCST_SLS_AMT_RPT) AS CNT_FRCST_SLS_AMT_RPT
, GROUPING_ID (FRCST.MFG_ITEM_CLASS_ID
, FRCST.RTL_ITEM_CLASS_ID
, FRCST.BSNS_WK_ID
, FRCST.BSNS_MNTH_ID
, FRCST.ORG_RGN_ID
, FRCST.ORG_CHAIN_ID
, FRCST.ITM_TYP
, FRCST.FRCST_PURP
, FRCST.FRCST_TYP) GROUPING_ID FROM DDR_BS_FRCST_ITM_BU_WK_MV FRCST GROUP BY GROUPING SETS((FRCST.BSNS_MNTH_ID
, FRCST.ORG_RGN_ID
, FRCST.MFG_ITEM_CLASS_ID
, FRCST.RTL_ITEM_CLASS_ID
, FRCST.ITM_TYP
, FRCST.FRCST_PURP
, FRCST.FRCST_TYP)
, (FRCST.BSNS_MNTH_ID
, FRCST.ORG_CHAIN_ID
, FRCST.MFG_ITEM_CLASS_ID
, FRCST.RTL_ITEM_CLASS_ID
, FRCST.ITM_TYP
, FRCST.FRCST_PURP
, FRCST.FRCST_TYP)
, (FRCST.BSNS_WK_ID
, FRCST.ORG_RGN_ID
, FRCST.ITM_TYP
, FRCST.FRCST_PURP
, FRCST.FRCST_TYP)
, (FRCST.BSNS_WK_ID
, FRCST.ORG_CHAIN_ID
, FRCST.ITM_TYP
, FRCST.FRCST_PURP
, FRCST.FRCST_TYP)
, (FRCST.BSNS_WK_ID
, FRCST.ORG_CHAIN_ID
, FRCST.RTL_ITEM_CLASS_ID
, FRCST.ITM_TYP
, FRCST.FRCST_PURP
, FRCST.FRCST_TYP))