View: PMI_EDW_BTCH_RSRC_F_FCV

View Text

SELECT BR.BATCH_ID||'-'||BR.BATCHSTEP_NO||'-'||BR.RESOURCES||'-'||BR.ACTIVITY||'-' ||BR.BATCHSTEPLINE_ID||'-'||INST.INSTANCE_CODE||'-'||'OPM'
, BH.PLANT_CODE||'-'||BH.PLANT_CODE||'-'||'PORG'||'-'||INST.INSTANCE_CODE||'-'||'OPM'
, '0'||'-'||IM.ITEM_NO||'-'||BH.WIP_WHSE_CODE
, BR.RESOURCES||'-'||INST.INSTANCE_CODE||'-'||'OPM'
, SUBSTR(EDW_TIME_PKG.CAL_DAY_FK(BH.ACTUAL_CMPLT_DATE
, SOB.SET_OF_BOOKS_ID)
, 1
, 120)
, TO_CHAR(BH.BATCH_STATUS)||'-'||'BATCH_STATUS'||'-'||'OPM'
, 'NA_EDW'
, 'NA_EDW'
, 'NA_EDW'
, 'NA_EDW'
, 'NA_EDW'
, NVL(BR.PLAN_RSRC_QTY
, 0)
, NVL(BR.ACTUAL_RSRC_QTY
, 0)
, NVL(BR.PLAN_RSRC_USAGE
, 0)
, NVL(BR.ACTUAL_RSRC_USAGE
, 0)
, BR.PLAN_RSRC_COUNT
, BR.ACTUAL_RSRC_COUNT
, NULL
, NULL
, NULL
, NULL
, NULL
, BH.BATCH_NO
, BR.BATCHSTEP_NO
, BR.ACTIVITY
, BR.BATCHSTEPLINE_ID
, BR.PRIM_RSRC_IND
, BR.SCALE_TYPE
, BR.USAGE_UM
, BR.PLAN_START_DATE
, BR.ACTUAL_START_DATE
, BR.PLAN_CMPLT_DATE
, BR.ACTUAL_CMPLT_DATE
, INST.INSTANCE_CODE
, INST.INSTANCE_CODE
, GREATEST(BH.LAST_UPDATE_DATE
, BD.LAST_UPDATE_DATE
, IM.LAST_UPDATE_DATE
, BR.LAST_UPDATE_DATE
, OM.LAST_UPDATE_DATE
, PM.LAST_UPDATE_DATE
, SOB.LAST_UPDATE_DATE) LAST_UPDATE_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM PM_BTCH_HDR BH
, PM_MATL_DTL BD
, IC_ITEM_MST IM
, PM_OPRN_DTL BR
, SY_ORGN_MST OM
, GL_PLCY_MST PM
, GL_SETS_OF_BOOKS SOB
, EDW_LOCAL_INSTANCE INST
WHERE BH.BATCH_ID = BR.BATCH_ID
AND BH.BATCH_ID = BD.BATCH_ID
AND BH.PLANT_CODE = OM.ORGN_CODE
AND OM.CO_CODE = PM.CO_CODE
AND PM.SET_OF_BOOKS_NAME =SOB.NAME
AND BD.ITEM_ID = IM.ITEM_ID
AND BH.BATCH_STATUS IN (3
, 4)
AND BD.LINE_TYPE=1

Columns