select
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.BSNS_MNTH_ID as BSNS_CLNDR_DIM_ID,
'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,
'CLS' as MFG_ITEM_DIM_LVL,
frcst.RTL_ITEM_CLASS_ID as RTL_ITEM_DIM_ID,
'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_MNTH_ID, frcst.ORG_RGN_ID, frcst.ORG_CHAIN_ID,
frcst.ITM_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.BSNS_MNTH_ID, frcst.ORG_CHAIN_ID, frcst.MFG_ITEM_CLASS_ID, frcst.RTL_ITEM_CLASS_ID, frcst.ITM_TYP))
[Home] [Help]