MATERIALIZED VIEW: APPS.DDR_BS_SLSR_ITM_BU_WK_MV

Source

select sls.MFG_ORG_CD
,      sls.RTL_ORG_CD
,      sls.ORG_BSNS_UNIT_ID
,      sls.ITM_TYP
,      orghier.MKT_AREA_ID
,      orghier.CHNL_TYP_CD
,      orghier.ORG_RGN_ID
,      orghier.ORG_CHAIN_ID
,      mfghier.MFG_ITEM_ID
,      mfghier.MFG_ITEM_CLASS_ID
,      rtlhier.RTL_ITEM_ID
,      rtlhier.RTL_ITEM_CLASS_ID
,      timehier.BSNS_WK_ID
,      timehier.BSNS_MNTH_ID
,      timehier.BSNS_YR_ID
,      sls.PRMTN_FLAG
,      count(*) as CNT_TOTAL
,      count(decode(sign(sls.SLS_QTY_PRMRY), 1, 1, NULL)) as ACT_BU_ITM_DAYS_SCANNED
,      sum(nvl(sls.SLS_QTY_PRMRY, 0)) as SLS_QTY_PRMRY
,      sum(nvl(sls.SLS_QTY_ALT, 0)) as SLS_QTY_ALT
,      sum(nvl(sls.SLS_AMT, 0)) as SLS_AMT
,      sum(nvl(sls.SLS_AMT_RPT, 0)) as SLS_AMT_RPT
,      sum(nvl(sls.SLS_COST_AMT, 0)) as SLS_COST_AMT
,      sum(nvl(sls.SLS_COST_AMT_RPT, 0)) as SLS_COST_AMT_RPT
,      sum(nvl(sls.RTRN_QTY_PRMRY, 0)) as RTRN_QTY_PRMRY
,      sum(nvl(sls.RTRN_QTY_ALT, 0)) as RTRN_QTY_ALT
,      sum(nvl(sls.RTRN_AMT, 0)) as RTRN_AMT
,      sum(nvl(sls.RTRN_AMT_RPT, 0)) as RTRN_AMT_RPT
,      sum(nvl(sls.RTRN_COST_AMT, 0)) as RTRN_COST_AMT
,      sum(nvl(sls.RTRN_COST_AMT_RPT, 0)) as RTRN_COST_AMT_RPT
,      count(nvl(sls.SLS_QTY_PRMRY, 0)) as CNT_SLS_QTY_PRMRY
,      count(nvl(sls.SLS_QTY_ALT, 0)) as CNT_SLS_QTY_ALT
,      count(nvl(sls.SLS_AMT, 0)) as CNT_SLS_AMT
,      count(nvl(sls.SLS_AMT_RPT, 0)) as CNT_SLS_AMT_RPT
,      count(nvl(sls.SLS_COST_AMT, 0)) as CNT_SLS_COST_AMT
,      count(nvl(sls.SLS_COST_AMT_RPT, 0)) as CNT_SLS_COST_AMT_RPT
,      count(nvl(sls.RTRN_QTY_PRMRY, 0)) as CNT_RTRN_QTY_PRMRY
,      count(nvl(sls.RTRN_QTY_ALT, 0)) as CNT_RTRN_QTY_ALT
,      count(nvl(sls.RTRN_AMT, 0)) as CNT_RTRN_AMT
,      count(nvl(sls.RTRN_AMT_RPT, 0)) as CNT_RTRN_AMT_RPT
,      count(nvl(sls.RTRN_COST_AMT, 0)) as CNT_RTRN_COST_AMT
,      count(nvl(sls.RTRN_COST_AMT_RPT, 0)) as CNT_RTRN_COST_AMT_RPT
from   ddr_b_rtl_sl_rtn_itm_day             sls
,      ddr_r_base_day_dn_mv                 timehier
,      ddr_r_org_bu_dn_mv                   orghier
,      ddr_r_mfg_item_sku_dn_mv             mfghier
,      ddr_r_rtl_item_sku_dn_mv             rtlhier
where timehier.mfg_org_cd = sls.mfg_org_cd
and   timehier.day_cd = sls.day_cd
and   timehier.clndr_cd = CONCAT(sls.mfg_org_cd, '-BSNS')
and   mfghier.mfg_sku_item_id = sls.mfg_sku_item_id
and   rtlhier.rtl_sku_item_id = sls.rtl_sku_item_id
and   orghier.org_bsns_unit_id = sls.org_bsns_unit_id
group by timehier.BSNS_WK_ID
,        timehier.BSNS_MNTH_ID
,        timehier.BSNS_YR_ID
,        sls.MFG_ORG_CD
,        sls.RTL_ORG_CD
,        sls.ORG_BSNS_UNIT_ID
,        sls.ITM_TYP
,        orghier.MKT_AREA_ID
,        orghier.CHNL_TYP_CD
,        orghier.ORG_RGN_ID
,        orghier.ORG_CHAIN_ID
,        mfghier.MFG_ITEM_ID
,        mfghier.MFG_ITEM_CLASS_ID
,        rtlhier.RTL_ITEM_ID
,        rtlhier.RTL_ITEM_CLASS_ID
,        sls.PRMTN_FLAG