View: OPI_EDW_OPM_JOB_DETAIL_FCV

Product:	OPI - Operations Intelligence
Description:	OPM Source View for Job Detail Fact. This Source View is based on OPM Batch and Material Details. The Source view Included the Products and Co-Products data alson with Material inputs , By product Yields etc
Implementation/DBA Data:	APPS.OPI_EDW_OPM_JOB_DETAIL_FCV

View Text

SELECT B.PLANT_CODE||'-'||B.BATCH_ID||'-'||B.ITEM_ID||'-'||INST.INSTANCE_CODE||'-'||'OPM'
, B.PLANT_CODE||'-'||INST.INSTANCE_CODE||'-'||'OPM'||'-'||'PORG'
, EDW_ITEMS_PKG.ITEM_ORG_FK(ITEM_FK_V.INVENTORY_ITEM_ID
, IW.MTL_ORGANIZATION_ID
, NULL
, TO_NUMBER(NULL)
, NULL)
, 'NA_EDW'
, SUBSTR(EDW_TIME_PKG.CAL_DAY_FK(DECODE(B.BATCH_STATUS
, -1
, B.LAST_UPDATE_DATE
, 0
, B.LAST_UPDATE_DATE
, 1
, B.LAST_UPDATE_DATE
, 2
, B.ACTUAL_START_DATE
, B.ACTUAL_CMPLT_DATE)
, SOB.SET_OF_BOOKS_ID)
, 1
, 120)
, PM.BASE_CURRENCY_CODE SOB_CURRENCY_FK
, EDW_UTIL.GET_EDW_UOM(UOM.UOM_CODE
, ITEM_FK_V.INVENTORY_ITEM_ID) BASE_UOM_FK
, INST.INSTANCE_CODE
, 'NA_EDW'
, 'NA_EDW'
, 'NA_EDW'
, 'NA_EDW'
, 'NA_EDW'
, DECODE(B.LINE_NO
, 1
, OPI_EDW_OPM_PRD_PKG.BYPRODUCT_VALUE(B.BATCH_ID
, 'ACTUAL')
, 0) ACT_BPR_VAL_B
, DECODE(B.LINE_NO
, 1
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( TO_NUMBER(NULL)
, OPI_EDW_OPM_PRD_PKG.BYPRODUCT_VALUE(B.BATCH_ID
, 'ACTUAL')
, NULL
, PM.BASE_CURRENCY_CODE
, B.ACTUAL_CMPLT_DATE
, NULL)
, 0) ACT_BPR_VAL_G
, DECODE(B.BATCH_STATUS
, 3
, TRUNC(B.ACTUAL_CMPLT_DATE)
, 4
, TRUNC(B.ACTUAL_CMPLT_DATE)
, TRUNC(B.EXPCT_CMPLT_DATE))
, DECODE(B.BATCH_STATUS
, -1
, B.LAST_UPDATE_DATE
, NULL)
, B.ACTUAL_QTY*OPI_OPM_COMMON_PKG.OPMCO_GET_COST(B.ITEM_ID
, B.WIP_WHSE_CODE
, NULL
, B.ACTUAL_CMPLT_DATE) ACT_INP_VAL_B
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( TO_NUMBER(NULL)
, B.ACTUAL_QTY*OPI_OPM_COMMON_PKG.OPMCO_GET_COST(B.ITEM_ID
, B.WIP_WHSE_CODE
, NULL
, B.ACTUAL_CMPLT_DATE)
, NULL
, PM.BASE_CURRENCY_CODE
, B.ACTUAL_CMPLT_DATE
, NULL) ACT_INP_VAL_G
, DECODE(B.LINE_NO
, 1
, OPI_EDW_OPM_PRD_PKG.SCHD_WORK_DAYS(B.BATCH_ID
, B.ACTUAL_START_DATE
, DECODE(B.BATCH_STATUS
, 3
, B.ACTUAL_CMPLT_DATE
, 4
, B.ACTUAL_CMPLT_DATE
, B.EXPCT_CMPLT_DATE))
, 0) ACT_JOB_TIME
, OPI_EDW_OPM_PRD_PKG.INGREDIENT_VALUE(B.BATCH_ID
, B.ITEM_ID
, B.LINE_NO
, 'ACTUAL') ACT_MTL_INP_VAL_B
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( TO_NUMBER(NULL)
, OPI_EDW_OPM_PRD_PKG.INGREDIENT_VALUE(B.BATCH_ID
, B.ITEM_ID
, B.LINE_NO
, 'ACTUAL')
, NULL
, PM.BASE_CURRENCY_CODE
, DECODE(B.BATCH_STATUS
, 3
, B.ACTUAL_CMPLT_DATE
, 4
, B.ACTUAL_CMPLT_DATE
, B.EXPCT_CMPLT_DATE)
, NULL) ACT_MTL_INP_VAL_G
, B.ACTUAL_QTY
, GMICUOM.I2UOM_CV(B.ITEM_ID
, 0
, B.ITEM_UM
, B.ACTUAL_QTY
, IM.ITEM_UM)*OPI_OPM_COMMON_PKG.OPMCO_GET_COST( B.ITEM_ID
, B.WIP_WHSE_CODE
, NULL
, DECODE(B.BATCH_STATUS
, 3
, B.ACTUAL_CMPLT_DATE
, 4
, B.ACTUAL_CMPLT_DATE
, B.EXPCT_CMPLT_DATE)) ACT_OUT_VAL_B
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( TO_NUMBER(NULL)
, B.ACTUAL_QTY*OPI_OPM_COMMON_PKG.OPMCO_GET_COST(B.ITEM_ID
, B.WIP_WHSE_CODE
, NULL
, DECODE(B.BATCH_STATUS
, 3
, B.ACTUAL_CMPLT_DATE
, 4
, B.ACTUAL_CMPLT_DATE
, B.EXPCT_CMPLT_DATE))
, NULL
, PM.BASE_CURRENCY_CODE
, DECODE(B.BATCH_STATUS
, 3
, B.ACTUAL_CMPLT_DATE
, 4
, B.ACTUAL_CMPLT_DATE
, B.EXPCT_CMPLT_DATE)
, NULL) ACT_OUT_VAL_G
, 0 ACT_SCR_VAL_B
, 0 ACT_SCR_VAL_G
, DECODE(B.BATCH_STATUS
, 2
, TRUNC(B.ACTUAL_START_DATE)
, 3
, TRUNC(B.ACTUAL_START_DATE)
, 4
, TRUNC(ACTUAL_START_DATE)
, NULL)
, B.CREATION_DATE
, 0 FST_PASS_YLD
, B.BATCH_NO
, LKUP.MEANING
, B.LAST_UPDATE_DATE
, 'PROCESS MANUFACTURING'
, NULL MOVE_TIME
, DECODE(B.LINE_NO
, 1
, OPI_EDW_OPM_PRD_PKG.NO_OF_TIMES_ADJUSTED(B.BATCH_ID)
, 0) NO_ADJ
, NULL NO_TIME_RESH
, DECODE(B.LINE_NO
, 1
, OPI_EDW_OPM_PRD_PKG.BYPRODUCT_VALUE(B.BATCH_ID
, 'PLAN')
, 0) PLN_BPR_VAL_B
, DECODE(B.LINE_NO
, 1
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( TO_NUMBER(NULL)
, OPI_EDW_OPM_PRD_PKG.BYPRODUCT_VALUE(B.BATCH_ID
, 'PLAN')
, NULL
, PM.BASE_CURRENCY_CODE
, DECODE(B.BATCH_STATUS
, 3
, B.ACTUAL_CMPLT_DATE
, 4
, B.ACTUAL_CMPLT_DATE
, B.EXPCT_CMPLT_DATE)
, NULL)
, 0) PLN_BPR_VAL_G
, TRUNC(B.EXPCT_CMPLT_DATE)
, B.PLAN_QTY*OPI_OPM_COMMON_PKG.OPMCO_GET_COST(B.ITEM_ID
, B.WIP_WHSE_CODE
, NULL
, DECODE(B.BATCH_STATUS
, 3
, B.ACTUAL_CMPLT_DATE
, 4
, B.ACTUAL_CMPLT_DATE
, B.EXPCT_CMPLT_DATE)) PLN_INP_VAL_B
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( TO_NUMBER(NULL)
, B.PLAN_QTY*OPI_OPM_COMMON_PKG.OPMCO_GET_COST(B.ITEM_ID
, B.WIP_WHSE_CODE
, NULL
, DECODE(B.BATCH_STATUS
, 3
, B.ACTUAL_CMPLT_DATE
, 4
, B.ACTUAL_CMPLT_DATE
, B.EXPCT_CMPLT_DATE))
, NULL
, PM.BASE_CURRENCY_CODE
, DECODE(B.BATCH_STATUS
, 3
, B.ACTUAL_CMPLT_DATE
, 4
, B.ACTUAL_CMPLT_DATE
, B.EXPCT_CMPLT_DATE)
, NULL) PLN_INP_VAL_G
, DECODE(B.LINE_NO
, 1
, OPI_EDW_OPM_PRD_PKG.SCHD_WORK_DAYS (B.BATCH_ID
, B.PLAN_START_DATE
, B.EXPCT_CMPLT_DATE)
, 0) PLN_JOB_TIME
, NVL(B.COST_ALLOC
, 0)*OPI_EDW_OPM_PRD_PKG.INGREDIENT_VALUE(B.BATCH_ID
, B.ITEM_ID
, B.LINE_NO
, 'PLAN') PLN_MTL_INP_VAL_B
, NVL(B.COST_ALLOC
, 0)*EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( TO_NUMBER(NULL)
, OPI_EDW_OPM_PRD_PKG.INGREDIENT_VALUE(B.BATCH_ID
, B.ITEM_ID
, B.LINE_NO
, 'PLAN')
, NULL
, PM.BASE_CURRENCY_CODE
, DECODE(B.BATCH_STATUS
, 3
, B.ACTUAL_CMPLT_DATE
, 4
, B.ACTUAL_CMPLT_DATE
, B.EXPCT_CMPLT_DATE)
, NULL) PLN_MTL_INP_VAL_G
, B.PLAN_QTY
, GMICUOM.I2UOM_CV(B.ITEM_ID
, 0
, B.ITEM_UM
, B.PLAN_QTY
, IM.ITEM_UM)*OPI_OPM_COMMON_PKG.OPMCO_GET_COST( B.ITEM_ID
, B.WIP_WHSE_CODE
, NULL
, DECODE(B.BATCH_STATUS
, 3
, B.ACTUAL_CMPLT_DATE
, 4
, B.ACTUAL_CMPLT_DATE
, B.EXPCT_CMPLT_DATE)) PLN_OUT_VAL_B
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( TO_NUMBER(NULL)
, B.PLAN_QTY*OPI_OPM_COMMON_PKG.OPMCO_GET_COST(B.ITEM_ID
, B.WIP_WHSE_CODE
, NULL
, DECODE(B.BATCH_STATUS
, 3
, B.ACTUAL_CMPLT_DATE
, 4
, B.ACTUAL_CMPLT_DATE
, B.EXPCT_CMPLT_DATE))
, NULL
, PM.BASE_CURRENCY_CODE
, DECODE(B.BATCH_STATUS
, 3
, B.ACTUAL_CMPLT_DATE
, 4
, B.ACTUAL_CMPLT_DATE
, B.EXPCT_CMPLT_DATE)
, NULL) PLN_OUT_VAL_G
, NULL PLN_SCR_VAL_B
, NULL PLN_SCR_VAL_G
, TRUNC(B.PLAN_START_DATE)
, NULL QC_FAIL_QTY
, DECODE(OPI_EDW_OPM_PRD_PKG.NO_OF_SAMPLES_TAKEN(B.BATCH_ID)
, 0
, 'N'
, 'Y')
, NULL QUEUE_TIME
, NULL RESH_REASON_CODE
, 'NA_EDW'
, NULL REWORK_QTY
, B.ROUTING_NO
, B.ROUTING_VERS
, NULL RUNTIME
, NULL SETUP_TIME
, DECODE(B.LINE_NO
, 1
, OPI_EDW_OPM_PRD_PKG.NO_OF_SAMPLES_TAKEN(B.BATCH_ID)
, 0)
, PI.STD_QTY
, (PI.FIXED_LEADTIME + PI.VARIABLE_LEADTIME)/24
, GMICUOM.I2UOM_CV(B.ITEM_ID
, 0
, B.ITEM_UM
, PI.STD_QTY
, IM.ITEM_UM)*OPI_OPM_COMMON_PKG.OPMCO_GET_COST( B.ITEM_ID
, B.WIP_WHSE_CODE
, NULL
, DECODE(B.BATCH_STATUS
, 3
, B.ACTUAL_CMPLT_DATE
, 4
, B.ACTUAL_CMPLT_DATE
, B.EXPCT_CMPLT_DATE)) STD_VAL_B
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( TO_NUMBER(NULL)
, PI.STD_QTY*OPI_OPM_COMMON_PKG.OPMCO_GET_COST(B.ITEM_ID
, B.WIP_WHSE_CODE
, NULL
, DECODE(B.BATCH_STATUS
, 3
, B.ACTUAL_CMPLT_DATE
, 4
, B.ACTUAL_CMPLT_DATE
, B.EXPCT_CMPLT_DATE))
, NULL
, PM.BASE_CURRENCY_CODE
, DECODE(B.BATCH_STATUS
, 3
, B.ACTUAL_CMPLT_DATE
, 4
, B.ACTUAL_CMPLT_DATE
, B.EXPCT_CMPLT_DATE)
, NULL) STD_VAL_G
, TO_CHAR(B.BATCH_STATUS)||'-'||'BATCH_STATUS'||'-'||'OPM'
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, B.SEQ_ID
, B.VIEW_ID
, (((PI.FIXED_LEADTIME + PI.VARIABLE_LEADTIME)/24)/PI.STD_QTY)*B.ACTUAL_QTY STND_HRS_EARNED
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
, RT.ROUTING_NO
, TO_CHAR(RT.ROUTING_VERS) ROUTING_VERS
, BD.ITEM_ID
, BD.PLAN_QTY
, BD.ACTUAL_QTY
, BD.ITEM_UM
, BD.COST_ALLOC
, BD.LINE_NO
, BH.CREATION_DATE
, INC.VIEW_ID
, INC.SEQ_ID
, GREATEST(BH.LAST_UPDATE_DATE
, BD.LAST_UPDATE_DATE) LAST_UPDATE_DATE
FROM PM_BTCH_HDR BH
, PM_MATL_DTL BD
, FM_ROUT_HDR RT
, OPI_EDW_OPM_JOB_DETAIL_INC INC
WHERE BH.BATCH_ID = BD.BATCH_ID
AND BH.ROUTING_ID=RT.ROUTING_ID(+)
AND BH.BATCH_STATUS IN (-1
, 0
, 1
, 2
, 3
, 4)
AND BD.LINE_TYPE=1
AND INC.PRIMARY_KEY=BD.LINE_ID ) B
, SY_ORGN_MST OM
, IC_ITEM_MST IM
, IC_PLNT_INV PI
, GL_SETS_OF_BOOKS SOB
, GL_PLCY_MST PM
, MTL_SYSTEM_ITEMS ITEM_FK_V
, IC_WHSE_MST IW
, EDW_LOCAL_INSTANCE INST
, OPI_PMI_UOMS_MST UOM
, GEM_LOOKUPS LKUP
WHERE B.PLANT_CODE = OM.ORGN_CODE
AND B.PLANT_CODE = PI.ORGN_CODE(+)
AND B.ITEM_ID = PI.ITEM_ID(+)
AND OM.CO_CODE = PM.CO_CODE
AND PM.SET_OF_BOOKS_NAME =SOB.NAME
AND B.ITEM_ID = IM.ITEM_ID
AND ITEM_FK_V.SEGMENT1= IM.ITEM_NO
AND ITEM_FK_V.ORGANIZATION_ID = IW.MTL_ORGANIZATION_ID
AND IW.WHSE_CODE = B.WIP_WHSE_CODE
AND UOM.UM_CODE = IM.ITEM_UM
AND LKUP.LOOKUP_TYPE='BATCH_STATUS'
AND LKUP.LOOKUP_CODE=B.BATCH_STATUS

Columns