VIEW: APPS.OPI_EDW_OPM_JOB_RSRC_FCV

Source

View Text - Preformatted

SELECT BR.Batch_id||'-'||BD.line_id||'-'||BR.Batchstep_no||'-'||BR.Resources||'-'||BR.Activity||'-' ||BR.BATCHSTEPLINE_ID||'-'||inst.instance_code||'-'||'OPM' JOB_RSRC_PK, BR.ACTIVITY||'-'||inst.INSTANCE_CODE||'-OPM' ACTIVITY_FK, BH.plant_code||'-'||inst.instance_code||'-'||'OPM'||'-'||'PORG' LOCATOR_FK, EDW_ITEMS_PKG.ITEM_ORG_FK(ITEM_FK_V.INVENTORY_ITEM_ID,IW.MTL_ORGANIZATION_ID,NULL,TO_NUMBER(NULL),NULL) ITEM_FK, decode(BR.resources,NULL,'NA_EDW',BR.resources||'-'||inst.instance_code||'-'||'OPM') RSRC_FK, substrb(edw_time_pkg.cal_day_fk(BH.ACTUAL_CMPLT_DATE,SOB.SET_OF_BOOKS_ID),1,120) TRX_DATE_FK, 'NA_EDW' QTY_UOM_FK, pm.BASE_CURRENCY_CODE SOB_CURRENCY_FK, EDW_UTIL.GET_EDW_UOM(UOM.UOM_CODE, 0) USAGE_UOM_FK, inst.instance_code INSTANCE_FK, OPRM.OPRN_ID||'-'||INST.INSTANCE_CODE||'-OPM' OPRN_FK, 'NA_EDW', 'NA_EDW', 'NA_EDW', 'NA_EDW', 'NA_EDW', BR.ACTUAL_CMPLT_DATE, BR.ACTUAL_RSRC_COUNT, nvl(BR.ACTUAL_RSRC_QTY,0), nvl(BR.ACTUAL_RSRC_USAGE,0), nvl(BR.ACTUAL_RSRC_USAGE,0)* OPI_OPM_COMMON_PKG.OPMCO_GET_RSRC_COST(BH.PLANT_CODE,BR.RESOURCES, NULL,UOM.UOM_CODE,BR.ACTUAL_CMPLT_DATE) ACT_RSRC_USAGE_VAL_B, EDW_CURRENCY.convert_global_amount(to_number(NULL), nvl(BR.ACTUAL_RSRC_USAGE,0)* OPI_OPM_COMMON_PKG.OPMCO_GET_RSRC_COST(BH.PLANT_CODE,BR.RESOURCES, NULL,UOM.UOM_CODE,BR.ACTUAL_CMPLT_DATE), NULL,pm.BASE_CURRENCY_CODE, BH.ACTUAL_CMPLT_DATE, NULL) ACT_RSRC_USAGE_VAL_G, 0 ACT_RSRC_VAL_B, 0 ACT_RSRC_VAL_G, NVL(CR.DAILY_AVAIL_USE,0), BR.ACTUAL_START_DATE, NULL DEPARTMENT, 0, BR.BATCHSTEP_NO, BR.PLAN_CMPLT_DATE, BR.PLAN_RSRC_COUNT, nvl(BR.PLAN_RSRC_QTY,0), nvl(BR.PLAN_RSRC_USAGE,0), nvl(BR.PLAN_RSRC_USAGE,0)* OPI_OPM_COMMON_PKG.OPMCO_GET_RSRC_COST(BH.PLANT_CODE,BR.RESOURCES, NULL,UOM.UOM_CODE,BR.ACTUAL_CMPLT_DATE) PLN_RSRC_USAGE_VAL_B, EDW_CURRENCY.convert_global_amount(to_number(NULL), nvl(BR.PLAN_RSRC_USAGE,0)* OPI_OPM_COMMON_PKG.OPMCO_GET_RSRC_COST(BH.PLANT_CODE,BR.RESOURCES, NULL,UOM.UOM_CODE,BR.ACTUAL_CMPLT_DATE), NULL,pm.BASE_CURRENCY_CODE, BH.ACTUAL_CMPLT_DATE, NULL) PLN_RSRC_USAGE_VAL_G, 0 PLN_RSRC_VAL_B, 0 PLN_RSRC_VAL_G, BR.PLAN_START_DATE, to_number(NULL), to_number(NULL), to_number(NULL), to_number(NULL), to_number(NULL), BH.BATCH_NO, br.last_update_date last_update_date, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, BR.SEQ_ID, BR.VIEW_ID, nvl(((BR.PLAN_RSRC_USAGE/BR.PLAN_RSRC_QTY)*BR.ACTUAL_RSRC_QTY),0) FROM PM_BTCH_HDR BH, PM_MATL_DTL BD, IC_ITEM_MST IM, (SELECT POD.BATCH_ID, PBH.PLANT_CODE, POD.ACTIVITY, POD.RESOURCES, POD.OPRN_LINE_ID, POD.BATCHSTEP_NO, POD.BATCHSTEPLINE_ID, POD.USAGE_UM, POD.ACTUAL_CMPLT_DATE, POD.ACTUAL_RSRC_COUNT, POD.ACTUAL_RSRC_QTY, POD.ACTUAL_RSRC_USAGE, POD.ACTUAL_START_DATE, POD.PLAN_CMPLT_DATE, POD.PLAN_RSRC_COUNT, POD.PLAN_RSRC_QTY, POD.PLAN_RSRC_USAGE, POD.PLAN_START_DATE, POD.LAST_UPDATE_DATE, INC.SEQ_ID, INC.VIEW_ID FROM PM_OPRN_DTL POD, PM_BTCH_HDR PBH, OPI_EDW_OPM_JOB_RSRC_INC INC WHERE POD.BATCH_ID=PBH.BATCH_ID AND INC.PRIMARY_KEY=POD.BATCHSTEPLINE_ID) BR, CR_RSRC_DTL CR, SY_ORGN_MST OM, GL_PLCY_MST PM, FM_OPRN_MST OPRM, FM_OPRN_DTL OPRD, MTL_SYSTEM_ITEMS ITEM_FK_V, IC_WHSE_MST IW, GL_SETS_OF_BOOKS SOB, EDW_LOCAL_INSTANCE inst, OPI_PMI_UOMS_MST UOM WHERE BH.BATCH_ID = BR.BATCH_ID AND BH.BATCH_ID = BD.BATCH_ID AND BH.PLANT_CODE = OM.ORGN_CODE AND BR.PLANT_CODE = CR.ORGN_CODE(+) AND BR.RESOURCES = CR.RESOURCES(+) AND BR.OPRN_LINE_ID = OPRD.OPRN_LINE_ID AND OPRD.OPRN_ID = OPRM.OPRN_ID 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 and BD.LINE_NO=1 AND ITEM_FK_V.SEGMENT1= IM.ITEM_NO AND ITEM_FK_V.ORGANIZATION_ID = IW.MTL_ORGANIZATION_ID AND IW.WHSE_CODE = BH.WIP_WHSE_CODE AND UOM.UM_CODE = BR.USAGE_UM

View Text - HTML Formatted

SELECT BR.BATCH_ID||'-'||BD.LINE_ID||'-'||BR.BATCHSTEP_NO||'-'||BR.RESOURCES||'-'||BR.ACTIVITY||'-' ||BR.BATCHSTEPLINE_ID||'-'||INST.INSTANCE_CODE||'-'||'OPM' JOB_RSRC_PK
, BR.ACTIVITY||'-'||INST.INSTANCE_CODE||'-OPM' ACTIVITY_FK
, BH.PLANT_CODE||'-'||INST.INSTANCE_CODE||'-'||'OPM'||'-'||'PORG' LOCATOR_FK
, EDW_ITEMS_PKG.ITEM_ORG_FK(ITEM_FK_V.INVENTORY_ITEM_ID
, IW.MTL_ORGANIZATION_ID
, NULL
, TO_NUMBER(NULL)
, NULL) ITEM_FK
, DECODE(BR.RESOURCES
, NULL
, 'NA_EDW'
, BR.RESOURCES||'-'||INST.INSTANCE_CODE||'-'||'OPM') RSRC_FK
, SUBSTRB(EDW_TIME_PKG.CAL_DAY_FK(BH.ACTUAL_CMPLT_DATE
, SOB.SET_OF_BOOKS_ID)
, 1
, 120) TRX_DATE_FK
, 'NA_EDW' QTY_UOM_FK
, PM.BASE_CURRENCY_CODE SOB_CURRENCY_FK
, EDW_UTIL.GET_EDW_UOM(UOM.UOM_CODE
, 0) USAGE_UOM_FK
, INST.INSTANCE_CODE INSTANCE_FK
, OPRM.OPRN_ID||'-'||INST.INSTANCE_CODE||'-OPM' OPRN_FK
, 'NA_EDW'
, 'NA_EDW'
, 'NA_EDW'
, 'NA_EDW'
, 'NA_EDW'
, BR.ACTUAL_CMPLT_DATE
, BR.ACTUAL_RSRC_COUNT
, NVL(BR.ACTUAL_RSRC_QTY
, 0)
, NVL(BR.ACTUAL_RSRC_USAGE
, 0)
, NVL(BR.ACTUAL_RSRC_USAGE
, 0)* OPI_OPM_COMMON_PKG.OPMCO_GET_RSRC_COST(BH.PLANT_CODE
, BR.RESOURCES
, NULL
, UOM.UOM_CODE
, BR.ACTUAL_CMPLT_DATE) ACT_RSRC_USAGE_VAL_B
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT(TO_NUMBER(NULL)
, NVL(BR.ACTUAL_RSRC_USAGE
, 0)* OPI_OPM_COMMON_PKG.OPMCO_GET_RSRC_COST(BH.PLANT_CODE
, BR.RESOURCES
, NULL
, UOM.UOM_CODE
, BR.ACTUAL_CMPLT_DATE)
, NULL
, PM.BASE_CURRENCY_CODE
, BH.ACTUAL_CMPLT_DATE
, NULL) ACT_RSRC_USAGE_VAL_G
, 0 ACT_RSRC_VAL_B
, 0 ACT_RSRC_VAL_G
, NVL(CR.DAILY_AVAIL_USE
, 0)
, BR.ACTUAL_START_DATE
, NULL DEPARTMENT
, 0
, BR.BATCHSTEP_NO
, BR.PLAN_CMPLT_DATE
, BR.PLAN_RSRC_COUNT
, NVL(BR.PLAN_RSRC_QTY
, 0)
, NVL(BR.PLAN_RSRC_USAGE
, 0)
, NVL(BR.PLAN_RSRC_USAGE
, 0)* OPI_OPM_COMMON_PKG.OPMCO_GET_RSRC_COST(BH.PLANT_CODE
, BR.RESOURCES
, NULL
, UOM.UOM_CODE
, BR.ACTUAL_CMPLT_DATE) PLN_RSRC_USAGE_VAL_B
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT(TO_NUMBER(NULL)
, NVL(BR.PLAN_RSRC_USAGE
, 0)* OPI_OPM_COMMON_PKG.OPMCO_GET_RSRC_COST(BH.PLANT_CODE
, BR.RESOURCES
, NULL
, UOM.UOM_CODE
, BR.ACTUAL_CMPLT_DATE)
, NULL
, PM.BASE_CURRENCY_CODE
, BH.ACTUAL_CMPLT_DATE
, NULL) PLN_RSRC_USAGE_VAL_G
, 0 PLN_RSRC_VAL_B
, 0 PLN_RSRC_VAL_G
, BR.PLAN_START_DATE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, BH.BATCH_NO
, BR.LAST_UPDATE_DATE LAST_UPDATE_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, BR.SEQ_ID
, BR.VIEW_ID
, NVL(((BR.PLAN_RSRC_USAGE/BR.PLAN_RSRC_QTY)*BR.ACTUAL_RSRC_QTY)
, 0)
FROM PM_BTCH_HDR BH
, PM_MATL_DTL BD
, IC_ITEM_MST IM
, (SELECT POD.BATCH_ID
, PBH.PLANT_CODE
, POD.ACTIVITY
, POD.RESOURCES
, POD.OPRN_LINE_ID
, POD.BATCHSTEP_NO
, POD.BATCHSTEPLINE_ID
, POD.USAGE_UM
, POD.ACTUAL_CMPLT_DATE
, POD.ACTUAL_RSRC_COUNT
, POD.ACTUAL_RSRC_QTY
, POD.ACTUAL_RSRC_USAGE
, POD.ACTUAL_START_DATE
, POD.PLAN_CMPLT_DATE
, POD.PLAN_RSRC_COUNT
, POD.PLAN_RSRC_QTY
, POD.PLAN_RSRC_USAGE
, POD.PLAN_START_DATE
, POD.LAST_UPDATE_DATE
, INC.SEQ_ID
, INC.VIEW_ID
FROM PM_OPRN_DTL POD
, PM_BTCH_HDR PBH
, OPI_EDW_OPM_JOB_RSRC_INC INC
WHERE POD.BATCH_ID=PBH.BATCH_ID
AND INC.PRIMARY_KEY=POD.BATCHSTEPLINE_ID) BR
, CR_RSRC_DTL CR
, SY_ORGN_MST OM
, GL_PLCY_MST PM
, FM_OPRN_MST OPRM
, FM_OPRN_DTL OPRD
, MTL_SYSTEM_ITEMS ITEM_FK_V
, IC_WHSE_MST IW
, GL_SETS_OF_BOOKS SOB
, EDW_LOCAL_INSTANCE INST
, OPI_PMI_UOMS_MST UOM
WHERE BH.BATCH_ID = BR.BATCH_ID
AND BH.BATCH_ID = BD.BATCH_ID
AND BH.PLANT_CODE = OM.ORGN_CODE
AND BR.PLANT_CODE = CR.ORGN_CODE(+)
AND BR.RESOURCES = CR.RESOURCES(+)
AND BR.OPRN_LINE_ID = OPRD.OPRN_LINE_ID
AND OPRD.OPRN_ID = OPRM.OPRN_ID
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
AND BD.LINE_NO=1
AND ITEM_FK_V.SEGMENT1= IM.ITEM_NO
AND ITEM_FK_V.ORGANIZATION_ID = IW.MTL_ORGANIZATION_ID
AND IW.WHSE_CODE = BH.WIP_WHSE_CODE
AND UOM.UM_CODE = BR.USAGE_UM