select sls.BSNS_WK_ID
, sls.BSNS_MNTH_ID
, sls.ORG_RGN_ID
, sls.ORG_CHAIN_ID
, sls.MKT_AREA_ID
, sls.CHNL_TYP_CD
, sls.ITM_TYP
, sls.MFG_ITEM_CLASS_ID
, sls.RTL_ITEM_CLASS_ID
, sls.SLS_TYP_CD
, decode (grouping_id (sls.BSNS_MNTH_ID, sls.BSNS_WK_ID),
2, sls.BSNS_WK_ID,
1, sls.BSNS_MNTH_ID) as BSNS_CLNDR_DIM_ID
, decode (grouping_id (sls.BSNS_MNTH_ID, sls.BSNS_WK_ID),
2, 'WK',
1, 'MNTH') as BSNS_CLNDR_DIM_LVL
, decode (grouping_id (sls.ORG_CHAIN_ID, sls.ORG_RGN_ID),
2, sls.ORG_RGN_ID,
1, sls.ORG_CHAIN_ID) as ORG_DIM_ID
, decode (grouping_id (sls.ORG_CHAIN_ID, sls.ORG_RGN_ID),
2, 'RGN',
1, 'CHN') as ORG_DIM_LVL
, sls.MFG_ITEM_CLASS_ID as MFG_ITEM_DIM_ID
, decode (sls.MFG_ITEM_CLASS_ID,
NULL, NULL,
'CLS') as MFG_ITEM_DIM_LVL
, sls.RTL_ITEM_CLASS_ID as RTL_ITEM_DIM_ID
, decode (sls.RTL_ITEM_CLASS_ID,
NULL, NULL,
'CLS') as RTL_ITEM_DIM_LVL
, count(*) as CNT_TOTAL
, sum(sls.ACT_BU_ITM_DAYS_SCANNED) as ACT_BU_ITM_DAYS_SCANNED
, count(sls.ACT_BU_ITM_DAYS_SCANNED) as CNT_ACT_BU_ITM_DAYS_SCANNED
, sum(sls.SLS_QTY_PRMRY) as SLS_QTY_PRMRY
, sum(sls.SLS_QTY_ALT) as SLS_QTY_ALT
, sum(sls.SLS_AMT) as SLS_AMT
, sum(sls.SLS_AMT_RPT) as SLS_AMT_RPT
, sum(sls.SLS_COST_AMT) as SLS_COST_AMT
, sum(sls.SLS_COST_AMT_RPT) as SLS_COST_AMT_RPT
, sum(sls.RTRN_QTY_PRMRY) as RTRN_QTY_PRMRY
, sum(sls.RTRN_QTY_ALT) as RTRN_QTY_ALT
, sum(sls.RTRN_AMT) as RTRN_AMT
, sum(sls.RTRN_AMT_RPT) as RTRN_AMT_RPT
, sum(sls.RTRN_COST_AMT) as RTRN_COST_AMT
, sum(sls.RTRN_COST_AMT_RPT) as RTRN_COST_AMT_RPT
, count(sls.SLS_QTY_PRMRY) as CNT_SLS_QTY_PRMRY
, count(sls.SLS_QTY_ALT) as CNT_SLS_QTY_ALT
, count(sls.SLS_AMT) as CNT_SLS_AMT
, count(sls.SLS_AMT_RPT) as CNT_SLS_AMT_RPT
, count(sls.SLS_COST_AMT) as CNT_SLS_COST_AMT
, count(sls.SLS_COST_AMT_RPT) as CNT_SLS_COST_AMT_RPT
, count(sls.RTRN_QTY_PRMRY) as CNT_RTRN_QTY_PRMRY
, count(sls.RTRN_QTY_ALT) as CNT_RTRN_QTY_ALT
, count(sls.RTRN_AMT) as CNT_RTRN_AMT
, count(sls.RTRN_AMT_RPT) as CNT_RTRN_AMT_RPT
, count(sls.RTRN_COST_AMT) as CNT_RTRN_COST_AMT
, count(sls.RTRN_COST_AMT_RPT) as CNT_RTRN_COST_AMT_RPT
, grouping_id (sls.MFG_ITEM_CLASS_ID, sls.RTL_ITEM_CLASS_ID,
sls.ORG_RGN_ID, sls.ORG_CHAIN_ID,
sls.BSNS_WK_ID, sls.BSNS_MNTH_ID,
sls.MKT_AREA_ID, sls.CHNL_TYP_CD,
sls.ITM_TYP, sls.SLS_TYP_CD) GROUPING_ID
from ddr_bs_slsr_itm_bu_wk_mv sls
group by
Grouping sets((sls.BSNS_MNTH_ID, sls.ORG_RGN_ID, sls.MFG_ITEM_CLASS_ID, sls.RTL_ITEM_CLASS_ID, sls.MKT_AREA_ID, sls.CHNL_TYP_CD, sls.ITM_TYP, sls.SLS_TYP_CD),
(sls.BSNS_MNTH_ID, sls.ORG_CHAIN_ID, sls.MFG_ITEM_CLASS_ID, sls.RTL_ITEM_CLASS_ID, sls.MKT_AREA_ID, sls.CHNL_TYP_CD, sls.ITM_TYP, sls.SLS_TYP_CD),
(sls.BSNS_WK_ID, sls.ORG_RGN_ID, sls.ITM_TYP),
(sls.BSNS_WK_ID, sls.ORG_CHAIN_ID, sls.ITM_TYP),
(sls.BSNS_WK_ID, sls.ORG_CHAIN_ID, sls.RTL_ITEM_CLASS_ID, sls.ITM_TYP))
SELECT SLS.BSNS_WK_ID
, SLS.BSNS_MNTH_ID
, SLS.ORG_RGN_ID
, SLS.ORG_CHAIN_ID
, SLS.MKT_AREA_ID
, SLS.CHNL_TYP_CD
, SLS.ITM_TYP
, SLS.MFG_ITEM_CLASS_ID
, SLS.RTL_ITEM_CLASS_ID
, SLS.SLS_TYP_CD
, DECODE (GROUPING_ID (SLS.BSNS_MNTH_ID
, SLS.BSNS_WK_ID)
,
2
, SLS.BSNS_WK_ID
,
1
, SLS.BSNS_MNTH_ID) AS BSNS_CLNDR_DIM_ID
, DECODE (GROUPING_ID (SLS.BSNS_MNTH_ID
, SLS.BSNS_WK_ID)
,
2
, 'WK'
,
1
, 'MNTH') AS BSNS_CLNDR_DIM_LVL
, DECODE (GROUPING_ID (SLS.ORG_CHAIN_ID
, SLS.ORG_RGN_ID)
,
2
, SLS.ORG_RGN_ID
,
1
, SLS.ORG_CHAIN_ID) AS ORG_DIM_ID
, DECODE (GROUPING_ID (SLS.ORG_CHAIN_ID
, SLS.ORG_RGN_ID)
,
2
, 'RGN'
,
1
, 'CHN') AS ORG_DIM_LVL
, SLS.MFG_ITEM_CLASS_ID AS MFG_ITEM_DIM_ID
, DECODE (SLS.MFG_ITEM_CLASS_ID
,
NULL
, NULL
,
'CLS') AS MFG_ITEM_DIM_LVL
, SLS.RTL_ITEM_CLASS_ID AS RTL_ITEM_DIM_ID
, DECODE (SLS.RTL_ITEM_CLASS_ID
,
NULL
, NULL
,
'CLS') AS RTL_ITEM_DIM_LVL
, COUNT(*) AS CNT_TOTAL
, SUM(SLS.ACT_BU_ITM_DAYS_SCANNED) AS ACT_BU_ITM_DAYS_SCANNED
, COUNT(SLS.ACT_BU_ITM_DAYS_SCANNED) AS CNT_ACT_BU_ITM_DAYS_SCANNED
, SUM(SLS.SLS_QTY_PRMRY) AS SLS_QTY_PRMRY
, SUM(SLS.SLS_QTY_ALT) AS SLS_QTY_ALT
, SUM(SLS.SLS_AMT) AS SLS_AMT
, SUM(SLS.SLS_AMT_RPT) AS SLS_AMT_RPT
, SUM(SLS.SLS_COST_AMT) AS SLS_COST_AMT
, SUM(SLS.SLS_COST_AMT_RPT) AS SLS_COST_AMT_RPT
, SUM(SLS.RTRN_QTY_PRMRY) AS RTRN_QTY_PRMRY
, SUM(SLS.RTRN_QTY_ALT) AS RTRN_QTY_ALT
, SUM(SLS.RTRN_AMT) AS RTRN_AMT
, SUM(SLS.RTRN_AMT_RPT) AS RTRN_AMT_RPT
, SUM(SLS.RTRN_COST_AMT) AS RTRN_COST_AMT
, SUM(SLS.RTRN_COST_AMT_RPT) AS RTRN_COST_AMT_RPT
, COUNT(SLS.SLS_QTY_PRMRY) AS CNT_SLS_QTY_PRMRY
, COUNT(SLS.SLS_QTY_ALT) AS CNT_SLS_QTY_ALT
, COUNT(SLS.SLS_AMT) AS CNT_SLS_AMT
, COUNT(SLS.SLS_AMT_RPT) AS CNT_SLS_AMT_RPT
, COUNT(SLS.SLS_COST_AMT) AS CNT_SLS_COST_AMT
, COUNT(SLS.SLS_COST_AMT_RPT) AS CNT_SLS_COST_AMT_RPT
, COUNT(SLS.RTRN_QTY_PRMRY) AS CNT_RTRN_QTY_PRMRY
, COUNT(SLS.RTRN_QTY_ALT) AS CNT_RTRN_QTY_ALT
, COUNT(SLS.RTRN_AMT) AS CNT_RTRN_AMT
, COUNT(SLS.RTRN_AMT_RPT) AS CNT_RTRN_AMT_RPT
, COUNT(SLS.RTRN_COST_AMT) AS CNT_RTRN_COST_AMT
, COUNT(SLS.RTRN_COST_AMT_RPT) AS CNT_RTRN_COST_AMT_RPT
, GROUPING_ID (SLS.MFG_ITEM_CLASS_ID
, SLS.RTL_ITEM_CLASS_ID
,
SLS.ORG_RGN_ID
, SLS.ORG_CHAIN_ID
,
SLS.BSNS_WK_ID
, SLS.BSNS_MNTH_ID
,
SLS.MKT_AREA_ID
, SLS.CHNL_TYP_CD
,
SLS.ITM_TYP
, SLS.SLS_TYP_CD) GROUPING_ID
FROM DDR_BS_SLSR_ITM_BU_WK_MV SLS
GROUP BY
GROUPING SETS((SLS.BSNS_MNTH_ID
, SLS.ORG_RGN_ID
, SLS.MFG_ITEM_CLASS_ID
, SLS.RTL_ITEM_CLASS_ID
, SLS.MKT_AREA_ID
, SLS.CHNL_TYP_CD
, SLS.ITM_TYP
, SLS.SLS_TYP_CD)
,
(SLS.BSNS_MNTH_ID
, SLS.ORG_CHAIN_ID
, SLS.MFG_ITEM_CLASS_ID
, SLS.RTL_ITEM_CLASS_ID
, SLS.MKT_AREA_ID
, SLS.CHNL_TYP_CD
, SLS.ITM_TYP
, SLS.SLS_TYP_CD)
,
(SLS.BSNS_WK_ID
, SLS.ORG_RGN_ID
, SLS.ITM_TYP)
,
(SLS.BSNS_WK_ID
, SLS.ORG_CHAIN_ID
, SLS.ITM_TYP)
,
(SLS.BSNS_WK_ID
, SLS.ORG_CHAIN_ID
, SLS.RTL_ITEM_CLASS_ID
, SLS.ITM_TYP))
|
|
|