select frcst.MFG_ORG_CD
, frcst.RTL_ORG_CD
, frcst.ORG_BSNS_UNIT_ID
, frcst.MFG_SKU_ITEM_ID
, frcst.RTL_SKU_ITEM_ID
, frcst.DAY_CD
, frcst.FRCST_PURP
, frcst.FRCST_TYP
, maxvrsn.MAX_FRCST_VRSN
, decode(sign(nvl(sls.SLS_AMT_RPT, 0)), 1, frcst.FRCST_SLS_AMT_RPT, 0) as ACT_FRCST_SLS_AMT_RPT
, decode(sign(nvl(sls.SLS_QTY_PRMRY, 0)), 1, frcst.FRCST_SLS_QTY_PRMRY, 0) as ACT_FRCST_SLS_QTY_PRMRY
, frcst.ROWID as FRCST_ROWID
, maxvrsn.ROWID as MAXVRSN_ROWID
, sls.ROWID as SLS_ROWID
from ddr_b_sls_frcst_item_day frcst
, ddr_bs_frcst_vrsn_mv maxvrsn
, ddr_b_rtl_sl_rtn_itm_day sls
where 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 frcst.day_cd = sls.day_cd (+)
and frcst.mfg_sku_item_id = sls.mfg_sku_item_id (+)
and frcst.org_bsns_unit_id = sls.org_bsns_unit_id (+)
and frcst.rtl_sku_item_id = sls.rtl_sku_item_id (+)
SELECT FRCST.MFG_ORG_CD
, FRCST.RTL_ORG_CD
, FRCST.ORG_BSNS_UNIT_ID
, FRCST.MFG_SKU_ITEM_ID
, FRCST.RTL_SKU_ITEM_ID
, FRCST.DAY_CD
, FRCST.FRCST_PURP
, FRCST.FRCST_TYP
, MAXVRSN.MAX_FRCST_VRSN
, DECODE(SIGN(NVL(SLS.SLS_AMT_RPT
, 0))
, 1
, FRCST.FRCST_SLS_AMT_RPT
, 0) AS ACT_FRCST_SLS_AMT_RPT
, DECODE(SIGN(NVL(SLS.SLS_QTY_PRMRY
, 0))
, 1
, FRCST.FRCST_SLS_QTY_PRMRY
, 0) AS ACT_FRCST_SLS_QTY_PRMRY
, FRCST.ROWID AS FRCST_ROWID
, MAXVRSN.ROWID AS MAXVRSN_ROWID
, SLS.ROWID AS SLS_ROWID
FROM DDR_B_SLS_FRCST_ITEM_DAY FRCST
, DDR_BS_FRCST_VRSN_MV MAXVRSN
, DDR_B_RTL_SL_RTN_ITM_DAY SLS
WHERE 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 FRCST.DAY_CD = SLS.DAY_CD (+)
AND FRCST.MFG_SKU_ITEM_ID = SLS.MFG_SKU_ITEM_ID (+)
AND FRCST.ORG_BSNS_UNIT_ID = SLS.ORG_BSNS_UNIT_ID (+)
AND FRCST.RTL_SKU_ITEM_ID = SLS.RTL_SKU_ITEM_ID (+)
|
|
|