[Home] [Help]
MATERIALIZED VIEW: APPS.DDR_SV_SLSR_MV
Source
select 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.PRMTN_FLAG
, sls.BSNS_MNTH_ID as BSNS_CLNDR_DIM_ID
, '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
, 'CLS' as MFG_ITEM_DIM_LVL
, sls.RTL_ITEM_CLASS_ID as RTL_ITEM_DIM_ID
, '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.BSNS_MNTH_ID, sls.ORG_CHAIN_ID,
sls.MKT_AREA_ID, sls.CHNL_TYP_CD,
sls.ITM_TYP, sls.PRMTN_FLAG) 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.PRMTN_FLAG),
(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.PRMTN_FLAG))