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.SLS_TYP_CD
, sls.MFG_PRMTN_ID
, 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.SLS_TYP_CD
, sls.MFG_PRMTN_ID
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.SLS_TYP_CD
, SLS.MFG_PRMTN_ID
, 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.SLS_TYP_CD
, SLS.MFG_PRMTN_ID
|
|
|