VIEW: APPS.OPI_EDW_OPI_JOB_DETAIL_FCV

Source

View Text - Preformatted

SELECT 1 VIEW_ID, SEQ_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, NULL ACT_OUT_VAL_G, AVG_ACT_UNIT_CMPL_CST * PLN_OUT_QTY PLN_OUT_VAL_B, NULL PLN_OUT_VAL_G, ACT_INP_VAL ACT_INP_VAL_B, 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, NULL ACT_MTL_INP_VAL_G, DECODE(ENTITY_TYPE, 4, ACT_MTL_INP_VAL,PLN_MTL_INP_VAl) PLN_MTL_INP_VAl_B, NULL PLN_MTL_INP_VAl_G, ACT_SCR_VAL ACT_SCR_VAL_B, 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, NULL ACT_BPR_VAL_G, DECODE(ENTITY_TYPE, 4, ACT_BPR_VAL, PLN_BPR_VAL) PLN_BPR_VAL_B, 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, NULL STD_VAL_G, MSI.FULL_LEAD_TIME STD_TIME, ACT_JOB_TIME ACT_JOB_TIME, PLN_JOB_TIME PLN_JOB_TIME, JOB_NO JOB_NO, JOB_STATUS JOB_STATUS, ACT_STRT_DATE ACT_STRT_DATE, PLN_STRT_DATE PLN_STRT_DATE, ACT_CMPL_DATE ACT_CMPL_DATE, PLN_CMPL_DATE PLN_CMPL_DATE, ACT_CNCL_DATE ACT_CNCL_DATE, MFG_MODE MFG_MODE, DECODE(PRD_LINE_FK, NULL, 'NA_EDW', PRD_LINE_FK ||'-'|| ORG_ID ||'-'|| INST.INSTANCE_CODE ||'-OPI') PRD_LINE_FK, ROUTING ROUTING, ROUTING_REVISION 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, EDW_CURRENCY.GET_RATE(currency_code, NVL(ACT_CMPL_DATE, JOBS.CREATION_DATE), NULL) CONVERSION_RATE 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 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 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 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_FLOW_SCHEDULE_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_JOBITEM_TOTAL,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 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 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 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 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 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 TO_CHAR(set_of_books_id) = TRIM(ORG_INFORMATION1) and HOI.organization_id = ORG_ID and ORG_INFORMATION_CONTEXT = 'Accounting Information' and mp.organization_id = JOBS.ORG_ID

View Text - HTML Formatted

SELECT 1 VIEW_ID
, SEQ_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
, NULL ACT_OUT_VAL_G
, AVG_ACT_UNIT_CMPL_CST * PLN_OUT_QTY PLN_OUT_VAL_B
, NULL PLN_OUT_VAL_G
, ACT_INP_VAL ACT_INP_VAL_B
, 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
, NULL ACT_MTL_INP_VAL_G
, DECODE(ENTITY_TYPE
, 4
, ACT_MTL_INP_VAL
, PLN_MTL_INP_VAL) PLN_MTL_INP_VAL_B
, NULL PLN_MTL_INP_VAL_G
, ACT_SCR_VAL ACT_SCR_VAL_B
, 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
, NULL ACT_BPR_VAL_G
, DECODE(ENTITY_TYPE
, 4
, ACT_BPR_VAL
, PLN_BPR_VAL) PLN_BPR_VAL_B
, 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
, NULL STD_VAL_G
, MSI.FULL_LEAD_TIME STD_TIME
, ACT_JOB_TIME ACT_JOB_TIME
, PLN_JOB_TIME PLN_JOB_TIME
, JOB_NO JOB_NO
, JOB_STATUS JOB_STATUS
, ACT_STRT_DATE ACT_STRT_DATE
, PLN_STRT_DATE PLN_STRT_DATE
, ACT_CMPL_DATE ACT_CMPL_DATE
, PLN_CMPL_DATE PLN_CMPL_DATE
, ACT_CNCL_DATE ACT_CNCL_DATE
, MFG_MODE MFG_MODE
, DECODE(PRD_LINE_FK
, NULL
, 'NA_EDW'
, PRD_LINE_FK ||'-'|| ORG_ID ||'-'|| INST.INSTANCE_CODE ||'-OPI') PRD_LINE_FK
, ROUTING ROUTING
, ROUTING_REVISION 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
, EDW_CURRENCY.GET_RATE(CURRENCY_CODE
, NVL(ACT_CMPL_DATE
, JOBS.CREATION_DATE)
, NULL) CONVERSION_RATE
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 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 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 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_FLOW_SCHEDULE_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_JOBITEM_TOTAL
, 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 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 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 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 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 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 TO_CHAR(SET_OF_BOOKS_ID) = TRIM(ORG_INFORMATION1)
AND HOI.ORGANIZATION_ID = ORG_ID
AND ORG_INFORMATION_CONTEXT = 'ACCOUNTING INFORMATION'
AND MP.ORGANIZATION_ID = JOBS.ORG_ID