View: OPI_EDW_OPM_JOB_RSRC_FCV

Product:	OPI - Operations Intelligence
Description:	This is the source view for extracting job-resource data from the Process Manufacturing source tables.
Implementation/DBA Data:	APPS.OPI_EDW_OPM_JOB_RSRC_FCV

View Text

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
, SUBSTR(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

Columns