select frcst.MFG_ORG_CD
, frcst.RTL_ORG_CD
, frcst.ORG_BSNS_UNIT_ID
, frcst.ITM_TYP
, frcst.FRCST_PURP
, frcst.FRCST_TYP
, 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
, count(*) as CNT_TOTAL
, sum(lstsls.ACT_FRCST_SLS_AMT_RPT) as ACT_FRCST_SLS_AMT_RPT
, sum(lstsls.ACT_FRCST_SLS_QTY_PRMRY) as ACT_FRCST_SLS_QTY_PRMRY
, sum(nvl(frcst.FRCST_SLS_QTY_PRMRY, 0)) as FRCST_SLS_QTY_PRMRY
, sum(nvl(frcst.FRCST_SLS_QTY_ALT, 0)) as FRCST_SLS_QTY_ALT
, sum(nvl(frcst.FRCST_SLS_AMT, 0)) as FRCST_SLS_AMT
, sum(nvl(frcst.FRCST_SLS_AMT_RPT, 0)) as FRCST_SLS_AMT_RPT
, count(lstsls.ACT_FRCST_SLS_AMT_RPT) as CNT_ACT_FRCST_SLS_AMT_RPT
, count(lstsls.ACT_FRCST_SLS_QTY_PRMRY) as CNT_ACT_FRCST_SLS_QTY_PRMRY
, count(nvl(frcst.FRCST_SLS_QTY_PRMRY, 0)) as CNT_FRCST_SLS_QTY_PRMRY
, count(nvl(frcst.FRCST_SLS_QTY_ALT, 0)) as CNT_FRCST_SLS_QTY_ALT
, count(nvl(frcst.FRCST_SLS_AMT, 0)) as CNT_FRCST_SLS_AMT
, count(nvl(frcst.FRCST_SLS_AMT_RPT, 0)) as CNT_FRCST_SLS_AMT_RPT
from ddr_b_sls_frcst_item_day frcst
, ddr_bs_frcst_vrsn_mv maxvrsn
, ddr_r_base_day_dn_mv timehier
, ddr_r_mfg_item_sku_dn_mv mfghier
, ddr_r_rtl_item_sku_dn_mv rtlhier
, ddr_r_org_bu_dn_mv orghier
, ddr_bs_frcst_lst_sls_mv lstsls
where timehier.mfg_org_cd = frcst.mfg_org_cd
and timehier.day_cd = frcst.day_cd
and timehier.clndr_cd = CONCAT(frcst.mfg_org_cd, '-BSNS')
and maxvrsn.max_frcst_vrsn = frcst.frcst_vrsn
and maxvrsn.day_cd = frcst.day_cd
and maxvrsn.mfg_sku_item_id = frcst.mfg_sku_item_id
and maxvrsn.org_bsns_unit_id = frcst.org_bsns_unit_id
and maxvrsn.rtl_sku_item_id = frcst.rtl_sku_item_id
and maxvrsn.frcst_purp = frcst.frcst_purp
and maxvrsn.frcst_typ = frcst.frcst_typ
and mfghier.mfg_sku_item_id = frcst.mfg_sku_item_id
and rtlhier.rtl_sku_item_id = frcst.rtl_sku_item_id
and orghier.org_bsns_unit_id = frcst.org_bsns_unit_id
and frcst.frcst_vrsn = lstsls.max_frcst_vrsn
and frcst.day_cd = lstsls.day_cd
and frcst.mfg_sku_item_id = lstsls.mfg_sku_item_id
and frcst.org_bsns_unit_id = lstsls.org_bsns_unit_id
and frcst.rtl_sku_item_id = lstsls.rtl_sku_item_id
and frcst.frcst_purp = lstsls.frcst_purp
and frcst.frcst_typ = lstsls.frcst_typ
group by timehier.BSNS_WK_ID
, timehier.BSNS_MNTH_ID
, timehier.BSNS_YR_ID
, frcst.MFG_ORG_CD
, frcst.RTL_ORG_CD
, frcst.ORG_BSNS_UNIT_ID
, frcst.ITM_TYP
, 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
, frcst.FRCST_PURP
, frcst.FRCST_TYP
SELECT FRCST.MFG_ORG_CD
, FRCST.RTL_ORG_CD
, FRCST.ORG_BSNS_UNIT_ID
, FRCST.ITM_TYP
, FRCST.FRCST_PURP
, FRCST.FRCST_TYP
, 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
, COUNT(*) AS CNT_TOTAL
, SUM(LSTSLS.ACT_FRCST_SLS_AMT_RPT) AS ACT_FRCST_SLS_AMT_RPT
, SUM(LSTSLS.ACT_FRCST_SLS_QTY_PRMRY) AS ACT_FRCST_SLS_QTY_PRMRY
, SUM(NVL(FRCST.FRCST_SLS_QTY_PRMRY
, 0)) AS FRCST_SLS_QTY_PRMRY
, SUM(NVL(FRCST.FRCST_SLS_QTY_ALT
, 0)) AS FRCST_SLS_QTY_ALT
, SUM(NVL(FRCST.FRCST_SLS_AMT
, 0)) AS FRCST_SLS_AMT
, SUM(NVL(FRCST.FRCST_SLS_AMT_RPT
, 0)) AS FRCST_SLS_AMT_RPT
, COUNT(LSTSLS.ACT_FRCST_SLS_AMT_RPT) AS CNT_ACT_FRCST_SLS_AMT_RPT
, COUNT(LSTSLS.ACT_FRCST_SLS_QTY_PRMRY) AS CNT_ACT_FRCST_SLS_QTY_PRMRY
, COUNT(NVL(FRCST.FRCST_SLS_QTY_PRMRY
, 0)) AS CNT_FRCST_SLS_QTY_PRMRY
, COUNT(NVL(FRCST.FRCST_SLS_QTY_ALT
, 0)) AS CNT_FRCST_SLS_QTY_ALT
, COUNT(NVL(FRCST.FRCST_SLS_AMT
, 0)) AS CNT_FRCST_SLS_AMT
, COUNT(NVL(FRCST.FRCST_SLS_AMT_RPT
, 0)) AS CNT_FRCST_SLS_AMT_RPT
FROM DDR_B_SLS_FRCST_ITEM_DAY FRCST
, DDR_BS_FRCST_VRSN_MV MAXVRSN
, DDR_R_BASE_DAY_DN_MV TIMEHIER
, DDR_R_MFG_ITEM_SKU_DN_MV MFGHIER
, DDR_R_RTL_ITEM_SKU_DN_MV RTLHIER
, DDR_R_ORG_BU_DN_MV ORGHIER
, DDR_BS_FRCST_LST_SLS_MV LSTSLS
WHERE TIMEHIER.MFG_ORG_CD = FRCST.MFG_ORG_CD
AND TIMEHIER.DAY_CD = FRCST.DAY_CD
AND TIMEHIER.CLNDR_CD = CONCAT(FRCST.MFG_ORG_CD
, '-BSNS')
AND MAXVRSN.MAX_FRCST_VRSN = FRCST.FRCST_VRSN
AND MAXVRSN.DAY_CD = FRCST.DAY_CD
AND MAXVRSN.MFG_SKU_ITEM_ID = FRCST.MFG_SKU_ITEM_ID
AND MAXVRSN.ORG_BSNS_UNIT_ID = FRCST.ORG_BSNS_UNIT_ID
AND MAXVRSN.RTL_SKU_ITEM_ID = FRCST.RTL_SKU_ITEM_ID
AND MAXVRSN.FRCST_PURP = FRCST.FRCST_PURP
AND MAXVRSN.FRCST_TYP = FRCST.FRCST_TYP
AND MFGHIER.MFG_SKU_ITEM_ID = FRCST.MFG_SKU_ITEM_ID
AND RTLHIER.RTL_SKU_ITEM_ID = FRCST.RTL_SKU_ITEM_ID
AND ORGHIER.ORG_BSNS_UNIT_ID = FRCST.ORG_BSNS_UNIT_ID
AND FRCST.FRCST_VRSN = LSTSLS.MAX_FRCST_VRSN
AND FRCST.DAY_CD = LSTSLS.DAY_CD
AND FRCST.MFG_SKU_ITEM_ID = LSTSLS.MFG_SKU_ITEM_ID
AND FRCST.ORG_BSNS_UNIT_ID = LSTSLS.ORG_BSNS_UNIT_ID
AND FRCST.RTL_SKU_ITEM_ID = LSTSLS.RTL_SKU_ITEM_ID
AND FRCST.FRCST_PURP = LSTSLS.FRCST_PURP
AND FRCST.FRCST_TYP = LSTSLS.FRCST_TYP
GROUP BY TIMEHIER.BSNS_WK_ID
, TIMEHIER.BSNS_MNTH_ID
, TIMEHIER.BSNS_YR_ID
, FRCST.MFG_ORG_CD
, FRCST.RTL_ORG_CD
, FRCST.ORG_BSNS_UNIT_ID
, FRCST.ITM_TYP
, 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
, FRCST.FRCST_PURP
, FRCST.FRCST_TYP
|
|
|