View: OPI_EDW_OPI_JOB_DETAIL_FCV

Product:	OPI - Operations Intelligence
Description:	OPI Source View for Job Detail Fact
Implementation/DBA Data:	APPS.OPI_EDW_OPI_JOB_DETAIL_FCV

View Text

SELECT 1 VIEW_ID
, SEQ_ID
, ORG_ID||'-'||JOBS.JOB_ID||'-'||ITEM_ORG||'-'||INST.INSTANCE_CODE||'-OPI' JOB_DETAIL_PK
, EDW_UTIL.GET_UOM_CONV_RATE(MSI.PRIMARY_UOM_CODE
, ITEM_ORG) * ACT_OUT_QTY ACT_OUT_QTY
, EDW_UTIL.GET_UOM_CONV_RATE(MSI.PRIMARY_UOM_CODE
, ITEM_ORG) * PLN_OUT_QTY PLN_OUT_QTY
, ACT_OUT_VAL ACT_OUT_VAL_B
, DECODE(ACT_OUT_VAL
, NULL
, NULL
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT(ACT_OUT_VAL
, ACT_OUT_VAL
, CURRENCY_CODE
, CURRENCY_CODE
, ACT_CMPL_DATE
, NULL)) ACT_OUT_VAL_G
, AVG_ACT_UNIT_CMPL_CST * PLN_OUT_QTY PLN_OUT_VAL_B
, DECODE(AVG_ACT_UNIT_CMPL_CST * PLN_OUT_QTY
, NULL
, NULL
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT(AVG_ACT_UNIT_CMPL_CST * PLN_OUT_QTY
, AVG_ACT_UNIT_CMPL_CST * PLN_OUT_QTY
, CURRENCY_CODE
, CURRENCY_CODE
, ACT_CMPL_DATE
, NULL)) PLN_OUT_VAL_G
, ACT_INP_VAL ACT_INP_VAL_B
, DECODE(ACT_INP_VAL
, NULL
, NULL
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT(ACT_INP_VAL
, ACT_INP_VAL
, CURRENCY_CODE
, CURRENCY_CODE
, ACT_CMPL_DATE
, NULL)) ACT_INP_VAL_G
, TO_NUMBER(NULL) PLN_INP_VAL_B
, TO_NUMBER(NULL) PLN_INP_VAL_G
, ACT_MTL_INP_VAL ACT_MTL_INP_VAL_B
, DECODE(ACT_MTL_INP_VAL
, NULL
, NULL
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT(ACT_MTL_INP_VAL
, ACT_MTL_INP_VAL
, CURRENCY_CODE
, CURRENCY_CODE
, ACT_CMPL_DATE
, NULL)) ACT_MTL_INP_VAL_G
, DECODE(ENTITY_TYPE
, 4
, ACT_MTL_INP_VAL
, PLN_MTL_INP_VAL) PLN_MTL_INP_VAL_B
, DECODE(DECODE(ENTITY_TYPE
, 4
, ACT_MTL_INP_VAL
, PLN_MTL_INP_VAL)
, NULL
, NULL
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT(DECODE(ENTITY_TYPE
, 4
, ACT_MTL_INP_VAL
, PLN_MTL_INP_VAL)
, DECODE(ENTITY_TYPE
, 4
, ACT_MTL_INP_VAL
, PLN_MTL_INP_VAL)
, CURRENCY_CODE
, CURRENCY_CODE
, ACT_CMPL_DATE
, NULL)) PLN_MTL_INP_VAL_G
, ACT_SCR_VAL ACT_SCR_VAL_B
, DECODE(ACT_SCR_VAL
, NULL
, NULL
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT(ACT_SCR_VAL
, ACT_SCR_VAL
, CURRENCY_CODE
, CURRENCY_CODE
, ACT_CMPL_DATE
, NULL)) ACT_SCR_VAL_G
, TO_NUMBER(NULL) PLN_SCR_VAL_B
, TO_NUMBER(NULL) PLN_SCR_VAL_G
, ACT_BPR_VAL ACT_BPR_VAL_B
, DECODE(ACT_BPR_VAL
, NULL
, NULL
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT(ACT_BPR_VAL
, ACT_BPR_VAL
, CURRENCY_CODE
, CURRENCY_CODE
, ACT_CMPL_DATE
, NULL)) ACT_BPR_VAL_G
, DECODE(ENTITY_TYPE
, 4
, ACT_BPR_VAL
, PLN_BPR_VAL) PLN_BPR_VAL_B
, DECODE(DECODE(ENTITY_TYPE
, 4
, ACT_BPR_VAL
, PLN_BPR_VAL)
, NULL
, NULL
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT(DECODE(ENTITY_TYPE
, 4
, ACT_BPR_VAL
, PLN_BPR_VAL)
, DECODE(ENTITY_TYPE
, 4
, ACT_BPR_VAL
, PLN_BPR_VAL)
, CURRENCY_CODE
, CURRENCY_CODE
, ACT_CMPL_DATE
, NULL)) PLN_BPR_VAL_G
, DECODE(MSI.LEAD_TIME_LOT_SIZE
, NULL
, DECODE(MSI.STD_LOT_SIZE
, NULL
, 1
, DECODE(MSI.STD_LOT_SIZE
, 0
, 1
, MSI.STD_LOT_SIZE))
, DECODE(MSI.LEAD_TIME_LOT_SIZE
, 0
, DECODE(MSI.STD_LOT_SIZE
, NULL
, 1
, DECODE(MSI.STD_LOT_SIZE
, 0
, 1
, MSI.STD_LOT_SIZE))
, MSI.LEAD_TIME_LOT_SIZE)) * EDW_UTIL.GET_UOM_CONV_RATE(MSI.PRIMARY_UOM_CODE
, ITEM_ORG) STANDARD_QTY
, DECODE(MSI.LEAD_TIME_LOT_SIZE
, NULL
, DECODE(MSI.STD_LOT_SIZE
, NULL
, 1
, DECODE(MSI.STD_LOT_SIZE
, 0
, 1
, MSI.STD_LOT_SIZE))
, DECODE(MSI.LEAD_TIME_LOT_SIZE
, 0
, DECODE(MSI.STD_LOT_SIZE
, NULL
, 1
, DECODE(MSI.STD_LOT_SIZE
, 0
, 1
, MSI.STD_LOT_SIZE))
, MSI.LEAD_TIME_LOT_SIZE)) * AVG_ACT_UNIT_CMPL_CST STD_VAL_B
, DECODE( DECODE(MSI.LEAD_TIME_LOT_SIZE
, NULL
, DECODE(MSI.STD_LOT_SIZE
, NULL
, 1
, DECODE(MSI.STD_LOT_SIZE
, 0
, 1
, MSI.STD_LOT_SIZE))
, DECODE(MSI.LEAD_TIME_LOT_SIZE
, 0
, DECODE(MSI.STD_LOT_SIZE
, NULL
, 1
, DECODE(MSI.STD_LOT_SIZE
, 0
, 1
, MSI.STD_LOT_SIZE))
, MSI.LEAD_TIME_LOT_SIZE)) * AVG_ACT_UNIT_CMPL_CST
, NULL
, NULL
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( DECODE(MSI.LEAD_TIME_LOT_SIZE
, NULL
, DECODE(MSI.STD_LOT_SIZE
, NULL
, 1
, DECODE(MSI.STD_LOT_SIZE
, 0
, 1
, MSI.STD_LOT_SIZE))
, DECODE(MSI.LEAD_TIME_LOT_SIZE
, 0
, DECODE(MSI.STD_LOT_SIZE
, NULL
, 1
, DECODE(MSI.STD_LOT_SIZE
, 0
, 1
, MSI.STD_LOT_SIZE))
, MSI.LEAD_TIME_LOT_SIZE)) * AVG_ACT_UNIT_CMPL_CST
, DECODE(MSI.LEAD_TIME_LOT_SIZE
, NULL
, DECODE(MSI.STD_LOT_SIZE
, NULL
, 1
, DECODE(MSI.STD_LOT_SIZE
, 0
, 1
, MSI.STD_LOT_SIZE))
, DECODE(MSI.LEAD_TIME_LOT_SIZE
, 0
, DECODE(MSI.STD_LOT_SIZE
, NULL
, 1
, DECODE(MSI.STD_LOT_SIZE
, 0
, 1
, MSI.STD_LOT_SIZE))
, MSI.LEAD_TIME_LOT_SIZE)) * AVG_ACT_UNIT_CMPL_CST
, CURRENCY_CODE
, CURRENCY_CODE
, ACT_CMPL_DATE
, NULL)) STD_VAL_G
, MSI.FULL_LEAD_TIME STD_TIME
, ACT_JOB_TIME
, PLN_JOB_TIME
, JOB_NO
, JOB_STATUS
, ACT_STRT_DATE
, PLN_STRT_DATE
, ACT_CMPL_DATE
, PLN_CMPL_DATE
, ACT_CNCL_DATE
, MFG_MODE
, DECODE(PRD_LINE_FK
, NULL
, 'NA_EDW'
, PRD_LINE_FK ||'-'|| ORG_ID ||'-'|| INST.INSTANCE_CODE ||'-OPI') PRD_LINE_FK
, ROUTING
, ROUTING_REVISION
, EDW_ITEMS_PKG.ITEM_ORG_FK(ITEM_ORG
, ORG_ID
, NULL
, TO_NUMBER(NULL)
, NULL) ITEM_FK
, EDW_UTIL.GET_EDW_BASE_UOM(MSI.PRIMARY_UOM_CODE
, ITEM_ORG) BASE_UOM_FK
, EDW_TIME_PKG.CAL_DAY_FK(ACT_CMPL_DATE
, SET_OF_BOOKS_ID) TRX_DATE_FK
, MP.ORGANIZATION_CODE ||'-'|| INST.INSTANCE_CODE ||'-PLNT' LOCATOR_FK
, EDW_UTIL.GET_BASE_CURRENCY(ORG_ID) SOB_CURRENCY_FK
, INST.INSTANCE_CODE INSTANCE_FK
, JOBS.LAST_UPDATE_DATE LAST_UPDATE_DATE
, JOBS.CREATION_DATE CREATION_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE10
, NULL USER_ATTRIBUTE11
, NULL USER_ATTRIBUTE12
, NULL USER_ATTRIBUTE13
, NULL USER_ATTRIBUTE14
, NULL USER_ATTRIBUTE15
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
, NULL USER_ATTRIBUTE6
, NULL USER_ATTRIBUTE7
, NULL USER_ATTRIBUTE8
, NULL USER_ATTRIBUTE9
, NULL USER_FK1
, NULL USER_FK2
, NULL USER_FK3
, NULL USER_FK4
, NULL USER_FK5
, NULL USER_MEASURE1
, NULL USER_MEASURE2
, NULL USER_MEASURE3
, NULL USER_MEASURE4
, NULL USER_MEASURE5
FROM ( SELECT INC.SEQ_ID SEQ_ID
, EN.WIP_ENTITY_NAME JOB_NO
, DI.QUANTITY_COMPLETED ACT_OUT_QTY
, DI.SCHEDULED_COMPLETION_DATE PLN_CMPL_DATE
, EN.WIP_ENTITY_ID || '-' JOB_ID
, EN.ORGANIZATION_ID ORG_ID
, EN.PRIMARY_ITEM_ID ITEM_ORG
, DI.START_QUANTITY PLN_OUT_QTY
, ML1.MEANING JOB_STATUS
, DI.SCHEDULED_START_DATE ACT_STRT_DATE
, DI.SCHEDULED_START_DATE PLN_STRT_DATE
, NVL(DI.DATE_COMPLETED
, DI.DATE_CLOSED) ACT_CMPL_DATE
, DECODE(DI.STATUS_TYPE
, 7
, DI.DATE_COMPLETED
, NULL) ACT_CNCL_DATE
, ML2.MEANING MFG_MODE
, EN.ENTITY_TYPE ENTITY_TYPE
, DI.LINE_ID PRD_LINE_FK
, EN.CREATION_DATE CREATION_DATE
, EN.CREATED_BY
, EN.LAST_UPDATE_DATE LAST_UPDATE_DATE
, EN.LAST_UPDATED_BY
, DECODE(DI.ALTERNATE_ROUTING_DESIGNATOR
, TO_CHAR(NULL)
, TO_CHAR(EN.PRIMARY_ITEM_ID)
, DI.ALTERNATE_ROUTING_DESIGNATOR) ROUTING
, DI.ROUTING_REVISION ROUTING_REVISION
, SUM(NVL(WPB.TL_RESOURCE_IN
, 0) + NVL(WPB.TL_OVERHEAD_IN
, 0) + NVL(WPB.TL_OUTSIDE_PROCESSING_IN
, 0) + NVL(WPB.PL_MATERIAL_IN
, 0) + NVL(WPB.PL_MATERIAL_OVERHEAD_IN
, 0) + NVL(WPB.PL_RESOURCE_IN
, 0) + NVL(WPB.PL_OVERHEAD_IN
, 0) + NVL(WPB.PL_OUTSIDE_PROCESSING_IN
, 0) + NVL(WPB.TL_SCRAP_IN
, 0)) ACT_INP_VAL
FROM WIP_ENTITIES EN
, OPI_EDW_OPI_JOB_DETAIL_INC INC
, WIP_DISCRETE_JOBS DI
, WIP_PERIOD_BALANCES WPB
, MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
WHERE DI.WIP_ENTITY_ID = INC.PRIMARY_KEY1
AND DI.STATUS_TYPE IN (4
, 5
, 7
, 12)
AND '_SEC:DI.ORGANIZATION_ID' IS NOT NULL
AND DI.WIP_ENTITY_ID = EN.WIP_ENTITY_ID
AND DI.ORGANIZATION_ID = EN.ORGANIZATION_ID
AND DI.WIP_ENTITY_ID = WPB.WIP_ENTITY_ID
AND ML1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND ML1.LOOKUP_CODE = DI.STATUS_TYPE
AND ML2.LOOKUP_TYPE = 'WIP_ENTITY'
AND ML2.LOOKUP_CODE = EN.ENTITY_TYPE GROUP BY INC.SEQ_ID
, EN.WIP_ENTITY_NAME
, DI.QUANTITY_COMPLETED
, DI.SCHEDULED_COMPLETION_DATE
, EN.WIP_ENTITY_ID
, EN.ORGANIZATION_ID
, EN.PRIMARY_ITEM_ID
, DI.START_QUANTITY
, DI.SCHEDULED_COMPLETION_DATE - DI.SCHEDULED_START_DATE
, ML1.MEANING
, DI.SCHEDULED_START_DATE
, DI.SCHEDULED_START_DATE
, DI.DATE_COMPLETED
, DECODE(DI.STATUS_TYPE
, 7
, DI.DATE_COMPLETED
, NULL)
, ML2.MEANING
, EN.ENTITY_TYPE
, DI.LINE_ID
, EN.CREATION_DATE
, EN.CREATED_BY
, EN.LAST_UPDATE_DATE
, EN.LAST_UPDATED_BY
, DECODE(DI.ALTERNATE_ROUTING_DESIGNATOR
, TO_CHAR(NULL)
, TO_CHAR(EN.PRIMARY_ITEM_ID)
, DI.ALTERNATE_ROUTING_DESIGNATOR)
, DI.ROUTING_REVISION
, DI.DATE_CLOSED UNION SELECT INC.SEQ_ID SEQ_ID
, EN.WIP_ENTITY_NAME JOB_NO
, RE.QUANTITY_COMPLETED ACT_OUT_QTY
, RE.LAST_UNIT_COMPLETION_DATE PLN_CMPL_DATE
, EN.WIP_ENTITY_ID || '-' || RE.REPETITIVE_SCHEDULE_ID JOB_ID
, EN.ORGANIZATION_ID ORG_ID
, EN.PRIMARY_ITEM_ID ITEM_ORG
, RE.DAILY_PRODUCTION_RATE * RE.PROCESSING_WORK_DAYS PLN_OUT_QTY
, ML1.MEANING JOB_STATUS
, RE.FIRST_UNIT_START_DATE ACT_STRT_DATE
, RE.FIRST_UNIT_START_DATE PLN_STRT_DATE
, NVL(RE.DATE_CLOSED
, RE.LAST_UNIT_COMPLETION_DATE) ACT_CMPL_DATE
, DECODE(RE.STATUS_TYPE
, 7
, RE.DATE_CLOSED
, NULL) ACT_CNCL_DATE
, ML2.MEANING MFG_MODE
, EN.ENTITY_TYPE ENTITY_TYPE
, RE.LINE_ID PRD_LINE_FK
, EN.CREATION_DATE CREATION_DATE
, EN.CREATED_BY
, EN.LAST_UPDATE_DATE LAST_UPDATE_DATE
, EN.LAST_UPDATED_BY
, DECODE(RE.ALTERNATE_ROUTING_DESIGNATOR
, TO_CHAR(NULL)
, TO_CHAR(EN.PRIMARY_ITEM_ID)
, RE.ALTERNATE_ROUTING_DESIGNATOR) ROUTING
, RE.ROUTING_REVISION ROUTING_REVISION
, SUM(NVL(WPB.TL_RESOURCE_IN
, 0) + NVL(WPB.TL_OVERHEAD_IN
, 0) + NVL(WPB.TL_OUTSIDE_PROCESSING_IN
, 0) + NVL(WPB.PL_MATERIAL_IN
, 0) + NVL(WPB.PL_MATERIAL_OVERHEAD_IN
, 0) + NVL(WPB.PL_RESOURCE_IN
, 0) + NVL(WPB.PL_OVERHEAD_IN
, 0) + NVL(WPB.PL_OUTSIDE_PROCESSING_IN
, 0) + NVL(WPB.TL_SCRAP_IN
, 0)) ACT_INP_VAL
FROM WIP_ENTITIES EN
, OPI_EDW_OPI_JOB_DETAIL_INC INC
, WIP_REPETITIVE_SCHEDULES RE
, WIP_PERIOD_BALANCES WPB
, MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
WHERE RE.WIP_ENTITY_ID = INC.PRIMARY_KEY1
AND RE.REPETITIVE_SCHEDULE_ID = INC.PRIMARY_KEY2
AND RE.STATUS_TYPE IN (4
, 5
, 7
, 12)
AND '_SEC:RE.ORGANIZATION_ID' IS NOT NULL
AND RE.WIP_ENTITY_ID = EN.WIP_ENTITY_ID
AND RE.ORGANIZATION_ID = EN.ORGANIZATION_ID
AND RE.WIP_ENTITY_ID = WPB.WIP_ENTITY_ID
AND RE.REPETITIVE_SCHEDULE_ID = WPB.REPETITIVE_SCHEDULE_ID
AND ML1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND ML1.LOOKUP_CODE = RE.STATUS_TYPE
AND ML2.LOOKUP_TYPE = 'WIP_ENTITY'
AND ML2.LOOKUP_CODE = EN.ENTITY_TYPE GROUP BY INC.SEQ_ID
, EN.WIP_ENTITY_NAME
, RE.QUANTITY_COMPLETED
, RE.LAST_UNIT_COMPLETION_DATE
, EN.WIP_ENTITY_ID || '-' || RE.REPETITIVE_SCHEDULE_ID
, EN.ORGANIZATION_ID
, EN.PRIMARY_ITEM_ID
, RE.DAILY_PRODUCTION_RATE * RE.PROCESSING_WORK_DAYS
, RE.PROCESSING_WORK_DAYS
, ML1.MEANING
, RE.FIRST_UNIT_START_DATE
, RE.FIRST_UNIT_START_DATE
, RE.DATE_CLOSED
, DECODE(RE.STATUS_TYPE
, 7
, RE.DATE_CLOSED
, NULL)
, ML2.MEANING
, EN.ENTITY_TYPE
, RE.LINE_ID
, EN.CREATION_DATE
, EN.CREATED_BY
, EN.LAST_UPDATE_DATE
, EN.LAST_UPDATED_BY
, DECODE(RE.ALTERNATE_ROUTING_DESIGNATOR
, TO_CHAR(NULL)
, TO_CHAR(EN.PRIMARY_ITEM_ID)
, RE.ALTERNATE_ROUTING_DESIGNATOR)
, RE.ROUTING_REVISION
, RE.LAST_UNIT_COMPLETION_DATE UNION SELECT INC.SEQ_ID SEQ_ID
, EN.WIP_ENTITY_NAME JOB_NO
, FL.QUANTITY_COMPLETED ACT_OUT_QTY
, FL.SCHEDULED_COMPLETION_DATE PLN_CMPL_DATE
, EN.WIP_ENTITY_ID || '-' JOB_ID
, EN.ORGANIZATION_ID ORG_ID
, EN.PRIMARY_ITEM_ID ITEM_ORG
, FL.PLANNED_QUANTITY PLN_OUT_QTY
, ML1.MEANING JOB_STATUS
, FL.SCHEDULED_START_DATE ACT_STRT_DATE
, FL.SCHEDULED_START_DATE PLN_STRT_DATE
, NVL(FL.DATE_CLOSED
, FL.SCHEDULED_COMPLETION_DATE) ACT_CMPL_DATE
, TO_DATE(NULL) ACT_CNCL_DATE
, ML2.MEANING MFG_MODE
, EN.ENTITY_TYPE ENTITY_TYPE
, FL.LINE_ID PRD_LINE_FK
, EN.CREATION_DATE CREATION_DATE
, EN.CREATED_BY
, EN.LAST_UPDATE_DATE LAST_UPDATE_DATE
, EN.LAST_UPDATED_BY
, DECODE(FL.ALTERNATE_ROUTING_DESIGNATOR
, TO_CHAR(NULL)
, TO_CHAR(EN.PRIMARY_ITEM_ID)
, FL.ALTERNATE_ROUTING_DESIGNATOR) ROUTING
, FL.ROUTING_REVISION ROUTING_REVISION
, SUM(NVL(WPB.TL_RESOURCE_IN
, 0) + NVL(WPB.TL_OVERHEAD_IN
, 0) + NVL(WPB.TL_OUTSIDE_PROCESSING_IN
, 0) + NVL(WPB.PL_MATERIAL_IN
, 0) + NVL(WPB.PL_MATERIAL_OVERHEAD_IN
, 0) + NVL(WPB.PL_RESOURCE_IN
, 0) + NVL(WPB.PL_OVERHEAD_IN
, 0) + NVL(WPB.PL_OUTSIDE_PROCESSING_IN
, 0) + NVL(WPB.TL_SCRAP_IN
, 0)) ACT_INP_VAL
FROM WIP_ENTITIES EN
, OPI_EDW_OPI_JOB_DETAIL_INC INC
, WIP_FLOW_SCHEDULES FL
, WIP_PERIOD_BALANCES WPB
, MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
WHERE FL.WIP_ENTITY_ID = INC.PRIMARY_KEY1
AND FL.STATUS = 2
AND '_SEC:FL.ORGANIZATION_ID' IS NOT NULL
AND FL.WIP_ENTITY_ID = EN.WIP_ENTITY_ID
AND FL.ORGANIZATION_ID = EN.ORGANIZATION_ID
AND FL.WIP_ENTITY_ID = WPB.WIP_ENTITY_ID
AND ML1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND ML1.LOOKUP_CODE = FL.STATUS
AND ML2.LOOKUP_TYPE = 'WIP_ENTITY'
AND ML2.LOOKUP_CODE = EN.ENTITY_TYPE GROUP BY INC.SEQ_ID
, EN.WIP_ENTITY_NAME
, FL.QUANTITY_COMPLETED
, FL.SCHEDULED_COMPLETION_DATE
, EN.WIP_ENTITY_ID
, EN.ORGANIZATION_ID
, EN.PRIMARY_ITEM_ID
, FL.PLANNED_QUANTITY
, FL.SCHEDULED_COMPLETION_DATE - FL.SCHEDULED_START_DATE
, ML1.MEANING
, FL.SCHEDULED_START_DATE
, FL.SCHEDULED_START_DATE
, FL.DATE_CLOSED
, ML2.MEANING
, EN.ENTITY_TYPE
, FL.LINE_ID
, EN.CREATION_DATE
, EN.CREATED_BY
, EN.LAST_UPDATE_DATE
, EN.LAST_UPDATED_BY
, DECODE(FL.ALTERNATE_ROUTING_DESIGNATOR
, TO_CHAR(NULL)
, TO_CHAR(EN.PRIMARY_ITEM_ID)
, FL.ALTERNATE_ROUTING_DESIGNATOR)
, FL.ROUTING_REVISION
, FL.SCHEDULED_COMPLETION_DATE ) JOBS
, ( SELECT JOB_ID
, SUM(TOT_COST_MAT) ACT_MTL_INP_VAL
, SUM(DECODE(QTY_MAT
, 0
, 0
, (TOT_COST_MAT/QTY_MAT))*REQ_QTY) PLN_MTL_INP_VAL
, SUM(TOT_COST_BPR) ACT_BPR_VAL
, SUM(DECODE(QTY_BPR
, 0
, 0
, (TOT_COST_BPR/QTY_BPR))*REQ_QTY) PLN_BPR_VAL
, SUM(TOT_COST_JOBITEM_TOTAL) ACT_OUT_VAL
, SUM(DECODE(QTY_BPR
, 0
, 0
, (TOT_COST_JOBITEM_TOTAL/QTY_JOBITEM_TOTAL))) AVG_ACT_UNIT_CMPL_CST
, SUM(SV_JOB_ITEM) ACT_SCR_VAL
FROM ( SELECT TXN_SRC||'-'||NVL(RTV_SCH
, '') JOB_ID
, ITEM_ID
, SUM(DECODE(TXN_ACTION_ID
, 1
, -1*TOT_COST
, DECODE(TXN_ACTION_ID
, 27
, -1*TOT_COST
, 0))) TOT_COST_MAT
, SUM(DECODE(TXN_ACTION_ID
, 1
, -1*TXN_QTY
, DECODE(TXN_ACTION_ID
, 27
, -1*TXN_QTY
, 0))) QTY_MAT
, SUM(DECODE(TXN_ACTION_ID
, 33
, TOT_COST
, DECODE(TXN_ACTION_ID
, 34
, TOT_COST))) TOT_COST_BPR
, SUM(DECODE(TXN_ACTION_ID
, 33
, TXN_QTY
, DECODE(TXN_ACTION_ID
, 34
, TXN_QTY))) QTY_BPR
, SUM(DECODE(TXN_ACTION_ID
, 31
, TOT_COST
, DECODE(TXN_ACTION_ID
, 32
, TOT_COST))) TOT_COST_JOBITEM_TOTAL
, SUM(DECODE(TXN_ACTION_ID
, 31
, TXN_QTY
, DECODE(TXN_ACTION_ID
, 32
, TXN_QTY))) QTY_JOBITEM_TOTAL
, SUM(DECODE(TXN_ACTION_ID
, 30
, TXN_QTY)) SV_JOB_ITEM
, REQUIRED_QUANTITY REQ_QTY
FROM ( SELECT MMT.TRANSACTION_ID TXN_ID
, MMT.INVENTORY_ITEM_ID ITEM_ID
, MMT.TRANSACTION_SOURCE_ID TXN_SRC
, MMTA.REPETITIVE_SCHEDULE_ID RTV_SCH
, MMT.TRANSACTION_ACTION_ID TXN_ACTION_ID
, MMT.TRANSACTION_SOURCE_ID||'-'||NVL(MMTA.REPETITIVE_SCHEDULE_ID
, '') JOB_ID
, DECODE(MMT.TRANSACTION_ACTION_ID
, 30
, SUM(COST_ELEMENT_VALUE) * DECODE(MMTA.PRIMARY_QUANTITY
, NULL
, MMT.PRIMARY_QUANTITY
, MMTA.PRIMARY_QUANTITY)
, DECODE(MMTA.PRIMARY_QUANTITY
, NULL
, MMT.PRIMARY_QUANTITY
, MMTA.PRIMARY_QUANTITY)) TXN_QTY
, SUM_ACT_CST * DECODE(MMTA.PRIMARY_QUANTITY
, NULL
, MMT.PRIMARY_QUANTITY
, MMTA.PRIMARY_QUANTITY) TOT_COST
FROM MTL_MATERIAL_TRANSACTIONS MMT
, MTL_MATERIAL_TXN_ALLOCATIONS MMTA
, WIP_SCRAP_VALUES WSV
, (SELECT TRANSACTION_ID
, SUM(MCACD.ACTUAL_COST) SUM_ACT_CST
FROM MTL_CST_ACTUAL_COST_DETAILS MCACD GROUP BY TRANSACTION_ID ) CD
WHERE (MMT.TRANSACTION_ACTION_ID IN (1
, 27
, 33
, 34
, 31
, 32
, 30))
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 5
AND MMT.TRANSACTION_ID = MMTA.TRANSACTION_ID (+)
AND MMT.TRANSACTION_ID = WSV.TRANSACTION_ID (+)
AND MMT.TRANSACTION_ID = CD.TRANSACTION_ID GROUP BY MMT.TRANSACTION_ID
, MMT.INVENTORY_ITEM_ID
, MMT.TRANSACTION_SOURCE_ID
, MMTA.REPETITIVE_SCHEDULE_ID
, MMT.TRANSACTION_ACTION_ID
, DECODE(MMTA.PRIMARY_QUANTITY
, NULL
, MMT.PRIMARY_QUANTITY
, MMTA.PRIMARY_QUANTITY)
, SUM_ACT_CST ) T1
, (SELECT WRO.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, WRO.WIP_ENTITY_ID WIP_ENTITY_ID
, WRO.REPETITIVE_SCHEDULE_ID REPETITIVE_SCHEDULE_ID
, WRO.WIP_ENTITY_ID ||'-'|| NVL(WRO.REPETITIVE_SCHEDULE_ID
, '') JOB_ID
, SUM(WRO.REQUIRED_QUANTITY) REQUIRED_QUANTITY
FROM WIP_REQUIREMENT_OPERATIONS WRO GROUP BY WRO.INVENTORY_ITEM_ID
, WRO.WIP_ENTITY_ID
, WRO.REPETITIVE_SCHEDULE_ID ) T2
WHERE T1.ITEM_ID = T2.INVENTORY_ITEM_ID (+)
AND T1.JOB_ID = T2.JOB_ID (+) GROUP BY TXN_SRC||'-'||NVL(RTV_SCH
, '')
, ITEM_ID
, REQUIRED_QUANTITY ) GROUP BY JOB_ID ) JOBITEMTOTAL_MAT_BPR_SCRAP
, ( SELECT DI.WIP_ENTITY_ID || '-' JOB_ID
, (NVL(DI.DATE_COMPLETED
, DI.DATE_CLOSED) - DI.SCHEDULED_START_DATE) - SUM(DECODE(BCD1.SEQ_NUM
, NULL
, 1
, 0)) ACT_JOB_TIME
FROM WIP_DISCRETE_JOBS DI
, OPI_EDW_OPI_JOB_DETAIL_INC INC
, MTL_PARAMETERS MP
, BOM_CALENDAR_DATES BCD1
WHERE BCD1.CALENDAR_DATE BETWEEN DI.SCHEDULED_START_DATE
AND NVL(DI.DATE_COMPLETED
, DI.DATE_CLOSED)
AND DI.WIP_ENTITY_ID = INC.PRIMARY_KEY1
AND DI.STATUS_TYPE IN (4
, 5
, 7
, 12)
AND /* TO_CHAR(CALENDAR_DATE) >= TO_CHAR(WMT.MIN_TXN_DATE)
AND TO_CHAR(CALENDAR_DATE) <= TO_CHAR(NVL(DI.DATE_COMPLETED
, DI.DATE_CLOSED))
AND */ MP.CALENDAR_CODE = BCD1.CALENDAR_CODE
AND MP.CALENDAR_EXCEPTION_SET_ID = BCD1.EXCEPTION_SET_ID
AND DI.ORGANIZATION_ID = MP.ORGANIZATION_ID GROUP BY DI.WIP_ENTITY_ID || '-'
, DI.SCHEDULED_START_DATE
, DI.DATE_COMPLETED
, DI.DATE_CLOSED
, DI.SCHEDULED_COMPLETION_DATE UNION SELECT RE.WIP_ENTITY_ID ||'-'|| RE.REPETITIVE_SCHEDULE_ID JOB_ID
, (NVL(RE.DATE_CLOSED
, RE.LAST_UNIT_COMPLETION_DATE) - RE.FIRST_UNIT_START_DATE) - SUM(DECODE(SEQ_NUM
, NULL
, 1
, 0)) ACT_JOB_TIME
FROM WIP_REPETITIVE_SCHEDULES RE
, OPI_EDW_OPI_JOB_DETAIL_INC INC
, MTL_PARAMETERS MP
, BOM_CALENDAR_DATES BCD
WHERE CALENDAR_DATE BETWEEN RE.FIRST_UNIT_START_DATE
AND NVL(RE.DATE_CLOSED
, RE.LAST_UNIT_COMPLETION_DATE)
AND RE.WIP_ENTITY_ID = INC.PRIMARY_KEY1
AND RE.REPETITIVE_SCHEDULE_ID = INC.PRIMARY_KEY2
AND RE.STATUS_TYPE IN (4
, 5
, 7
, 12)
AND /* TO_CHAR(CALENDAR_DATE) >= TO_CHAR(RE.FIRST_UNIT_START_DATE)
AND TO_CHAR(CALENDAR_DATE) <= TO_CHAR(NVL(RE.DATE_CLOSED
, RE.LAST_UNIT_COMPLETION_DATE))
AND */ MP.CALENDAR_CODE = BCD.CALENDAR_CODE
AND MP.CALENDAR_EXCEPTION_SET_ID = BCD.EXCEPTION_SET_ID
AND RE.ORGANIZATION_ID = MP.ORGANIZATION_ID GROUP BY RE.WIP_ENTITY_ID ||'-'|| RE.REPETITIVE_SCHEDULE_ID
, RE.DATE_CLOSED
, RE.LAST_UNIT_COMPLETION_DATE
, RE.FIRST_UNIT_START_DATE
, RE.PROCESSING_WORK_DAYS UNION SELECT FL.WIP_ENTITY_ID || '-' JOB_ID
, (NVL(FL.DATE_CLOSED
, FL.SCHEDULED_COMPLETION_DATE) - FL.SCHEDULED_START_DATE) - SUM(DECODE(BCD1.SEQ_NUM
, NULL
, 1
, 0)) ACT_JOB_TIME
FROM WIP_FLOW_SCHEDULES FL
, OPI_EDW_OPI_JOB_DETAIL_INC INC
, MTL_PARAMETERS MP
, BOM_CALENDAR_DATES BCD1
WHERE BCD1.CALENDAR_DATE BETWEEN FL.SCHEDULED_START_DATE
AND NVL(FL.DATE_CLOSED
, FL.SCHEDULED_COMPLETION_DATE)
AND FL.WIP_ENTITY_ID = INC.PRIMARY_KEY1
AND FL.STATUS = 2
AND /* TO_CHAR(CALENDAR_DATE) >= TO_CHAR(FL.SCHEDULED_START_DATE)
AND TO_CHAR(CALENDAR_DATE) <= TO_CHAR(NVL(FL.DATE_CLOSED
, FL.SCHEDULED_COMPLETION_DATE))
AND */ MP.CALENDAR_CODE = BCD1.CALENDAR_CODE
AND MP.CALENDAR_EXCEPTION_SET_ID = BCD1.EXCEPTION_SET_ID
AND FL.ORGANIZATION_ID = MP.ORGANIZATION_ID GROUP BY FL.WIP_ENTITY_ID || '-'
, FL.SCHEDULED_START_DATE
, FL.DATE_CLOSED
, FL.SCHEDULED_COMPLETION_DATE ) ACT_JT
, ( SELECT DI.WIP_ENTITY_ID || '-' JOB_ID
, (DI.SCHEDULED_COMPLETION_DATE - DI.SCHEDULED_START_DATE) - SUM(DECODE(BCD2.SEQ_NUM
, NULL
, 1
, 0)) PLN_JOB_TIME
FROM WIP_DISCRETE_JOBS DI
, OPI_EDW_OPI_JOB_DETAIL_INC INC
, MTL_PARAMETERS MP
, BOM_CALENDAR_DATES BCD2
WHERE BCD2.CALENDAR_DATE BETWEEN DI.SCHEDULED_START_DATE
AND DI.SCHEDULED_COMPLETION_DATE
AND DI.WIP_ENTITY_ID = INC.PRIMARY_KEY1
AND DI.STATUS_TYPE IN (4
, 5
, 7
, 12)
AND /* TO_CHAR(CALENDAR_DATE) >= TO_CHAR(WMT.MIN_TXN_DATE)
AND TO_CHAR(CALENDAR_DATE) <= TO_CHAR(NVL(DI.DATE_COMPLETED
, DI.DATE_CLOSED))
AND */ MP.CALENDAR_CODE = BCD2.CALENDAR_CODE
AND MP.CALENDAR_EXCEPTION_SET_ID = BCD2.EXCEPTION_SET_ID
AND DI.ORGANIZATION_ID = MP.ORGANIZATION_ID GROUP BY DI.WIP_ENTITY_ID || '-'
, DI.SCHEDULED_START_DATE
, DI.DATE_COMPLETED
, DI.DATE_CLOSED
, DI.SCHEDULED_COMPLETION_DATE UNION SELECT RE.WIP_ENTITY_ID ||'-'|| RE.REPETITIVE_SCHEDULE_ID JOB_ID
, RE.PROCESSING_WORK_DAYS PLN_JOB_TIME
FROM WIP_REPETITIVE_SCHEDULES RE
, OPI_EDW_OPI_JOB_DETAIL_INC INC
WHERE RE.WIP_ENTITY_ID = INC.PRIMARY_KEY1
AND RE.REPETITIVE_SCHEDULE_ID = INC.PRIMARY_KEY2
AND RE.STATUS_TYPE IN (4
, 5
, 7
, 12) GROUP BY RE.WIP_ENTITY_ID ||'-'|| RE.REPETITIVE_SCHEDULE_ID
, RE.DATE_CLOSED
, RE.LAST_UNIT_COMPLETION_DATE
, RE.FIRST_UNIT_START_DATE
, RE.PROCESSING_WORK_DAYS UNION SELECT FL.WIP_ENTITY_ID || '-' JOB_ID
, (FL.SCHEDULED_COMPLETION_DATE - FL.SCHEDULED_START_DATE) - SUM(DECODE(BCD2.SEQ_NUM
, NULL
, 1
, 0)) PLN_JOB_TIME
FROM WIP_FLOW_SCHEDULES FL
, OPI_EDW_OPI_JOB_DETAIL_INC INC
, MTL_PARAMETERS MP
, BOM_CALENDAR_DATES BCD2
WHERE BCD2.CALENDAR_DATE BETWEEN FL.SCHEDULED_START_DATE
AND FL.SCHEDULED_COMPLETION_DATE
AND FL.WIP_ENTITY_ID = INC.PRIMARY_KEY1
AND FL.STATUS = 2
AND /* TO_CHAR(CALENDAR_DATE) >= TO_CHAR(FL.SCHEDULED_START_DATE)
AND TO_CHAR(CALENDAR_DATE) <= TO_CHAR(NVL(FL.DATE_CLOSED
, FL.SCHEDULED_COMPLETION_DATE))
AND */ MP.CALENDAR_CODE = BCD2.CALENDAR_CODE
AND MP.CALENDAR_EXCEPTION_SET_ID = BCD2.EXCEPTION_SET_ID
AND FL.ORGANIZATION_ID = MP.ORGANIZATION_ID GROUP BY FL.WIP_ENTITY_ID || '-'
, FL.SCHEDULED_START_DATE
, FL.DATE_CLOSED
, FL.SCHEDULED_COMPLETION_DATE ) PLN_JT
, MTL_SYSTEM_ITEMS MSI
, HR_ORGANIZATION_INFORMATION HOI
, GL_SETS_OF_BOOKS GSOB
, MTL_PARAMETERS MP
, EDW_LOCAL_INSTANCE INST
WHERE JOBS.JOB_ID = JOBITEMTOTAL_MAT_BPR_SCRAP.JOB_ID (+)
AND JOBS.JOB_ID = ACT_JT.JOB_ID (+)
AND JOBS.JOB_ID = PLN_JT.JOB_ID (+)
AND (MSI.INVENTORY_ITEM_ID = JOBS.ITEM_ORG
AND MSI.ORGANIZATION_ID = JOBS.ORG_ID)
AND SET_OF_BOOKS_ID = ORG_INFORMATION1
AND HOI.ORGANIZATION_ID = ORG_ID
AND ORG_INFORMATION_CONTEXT = 'ACCOUNTING INFORMATION'
AND MP.ORGANIZATION_ID = JOBS.ORG_ID

Columns