View: PMI_EDW_BTCH_DTL_F_FCV

View Text

SELECT B.PLANT_CODE||'-'||B.BATCH_ID||'-'||B.ITEM_ID||'-'||INST.INSTANCE_CODE||'-'|| 'OPM'
, B.PLANT_CODE||'-'||B.PLANT_CODE||'-'||'PORG'||'-'||INST.INSTANCE_CODE||'-'||'OPM'
, '0'||'-'||IM.ITEM_NO||'-'||B.WIP_WHSE_CODE
, FM.FORMULA_NO||'-'||FM.FORMULA_VERS||'-'||INST.INSTANCE_CODE||'-'||'OPM'
, RH.ROUTING_NO||'-'||RH.ROUTING_VERS||'-'||INST.INSTANCE_CODE||'-'||'OPM'
, SUBSTR(EDW_TIME_PKG.CAL_DAY_FK(B.ACTUAL_CMPLT_DATE
, SOB.SET_OF_BOOKS_ID)
, 1
, 120)
, TO_CHAR(B.BATCH_STATUS)||'-'||'BATCH_STATUS'||'-'||'OPM'
, 'NA_EDW'
, 'NA_EDW'
, 'NA_EDW'
, 'NA_EDW'
, 'NA_EDW'
, B.PLAN_QTY
, GMICUOM.I2UOM_CV(B.ITEM_ID
, 0
, B.ITEM_UM
, B.PLAN_QTY
, IM.ITEM_UM)*PMI_COMMON_PKG.PMICO_GET_COST( B.ITEM_ID
, B.WIP_WHSE_CODE
, NULL
, B.ACTUAL_CMPLT_DATE)
, B.ACTUAL_QTY
, GMICUOM.I2UOM_CV(B.ITEM_ID
, 0
, B.ITEM_UM
, B.ACTUAL_QTY
, IM.ITEM_UM)*PMI_COMMON_PKG.PMICO_GET_COST( B.ITEM_ID
, B.WIP_WHSE_CODE
, NULL
, B.ACTUAL_CMPLT_DATE)
, PMI_PRODUCTION_SUM.PLAN_ING_VALUE(B.BATCH_ID)
, PMI_PRODUCTION_SUM.ACTUAL_ING_VALUE(B.BATCH_ID)
, PMI_PRODUCTION_SUM.NO_OF_SAMPLES_TAKEN(B.BATCH_ID)
, PMI_PRODUCTION_SUM.NO_OF_SAMPLES_COMPLET(B.BATCH_ID)
, PMI_PRODUCTION_SUM.NO_OF_SAMPLES_PASSED(B.BATCH_ID)
, PMI_PRODUCTION_SUM.NO_OF_TIMES_ADJUSTED(B.BATCH_ID)
, INST.INSTANCE_CODE
, INST.INSTANCE_CODE
, NULL
, NULL
, NULL
, NULL
, NULL
, B.BATCH_NO
, B.ITEM_UM
, B.PLAN_START_DATE
, B.ACTUAL_START_DATE
, B.EXPCT_CMPLT_DATE
, B.ACTUAL_CMPLT_DATE
, PM.BASE_CURRENCY_CODE
, PM.GL_COST_MTHD
, PI.STD_QTY
, (PI.FIXED_LEADTIME + PI.VARIABLE_LEADTIME)
, GREATEST(B.LAST_UPDATE_DATE
, OM.LAST_UPDATE_DATE
, IM.LAST_UPDATE_DATE
, FM.LAST_UPDATE_DATE
, RH.LAST_UPDATE_DATE
, PI.LAST_UPDATE_DATE
, SOB.LAST_UPDATE_DATE
, PM.LAST_UPDATE_DATE) LAST_UPDATE_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM ( SELECT BH.BATCH_ID
, BH.BATCH_NO
, BH.BATCH_STATUS
, BH.WIP_WHSE_CODE
, BH.PLAN_START_DATE
, BH.ACTUAL_START_DATE
, BH.EXPCT_CMPLT_DATE
, BH.ACTUAL_CMPLT_DATE
, BH.PLANT_CODE
, BH.FORMULA_ID
, BH.ROUTING_ID
, BD.ITEM_ID
, BD.PLAN_QTY
, BD.ACTUAL_QTY
, BD.ITEM_UM
, GREATEST(BH.LAST_UPDATE_DATE
, BD.LAST_UPDATE_DATE) LAST_UPDATE_DATE FROM PM_BTCH_HDR BH
, PM_MATL_DTL BD
WHERE BH.BATCH_ID = BD.BATCH_ID AND BH.BATCH_STATUS IN (3
, 4) AND BD.LINE_TYPE=1 ) B
, SY_ORGN_MST OM
, IC_ITEM_MST IM
, FM_FORM_MST FM
, FM_ROUT_HDR RH
, IC_PLNT_INV PI
, GL_SETS_OF_BOOKS SOB
, GL_PLCY_MST PM
, EDW_LOCAL_INSTANCE INST
WHERE B.PLANT_CODE = OM.ORGN_CODE
AND B.PLANT_CODE = PI.ORGN_CODE(+)
AND B.ITEM_ID = PI.ITEM_ID(+)
AND B.FORMULA_ID = FM.FORMULA_ID
AND B.ROUTING_ID = RH.ROUTING_ID(+)
AND OM.CO_CODE = PM.CO_CODE
AND PM.SET_OF_BOOKS_NAME =SOB.NAME
AND B.ITEM_ID = IM.ITEM_ID

Columns