eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.OPI_EDW_OPM_JOB_DETAIL_FCV

Source

View Text - Preformatted


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', substrb(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),NULL), 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,DECODE(B.BATCH_STATUS,3,B.ACTUAL_START_DATE,4,B.ACTUAL_START_DATE,B.PLAN_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, DECODE(PI.STD_QTY,0,0,NULL,0,(((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

View Text - HTML Formatted

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'
, SUBSTRB(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)
, NULL)
, 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
, DECODE(B.BATCH_STATUS
, 3
, B.ACTUAL_START_DATE
, 4
, B.ACTUAL_START_DATE
, B.PLAN_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
, DECODE(PI.STD_QTY
, 0
, 0
, NULL
, 0
, (((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