ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 31BE452F60710AD7E06362FB0905BA10 -- WIP - WIP Production Job Details --This program extracts job details with release and operation dates and elapsed times according to when the transactions were entered in Oracle. --Author: Ion Yilmaz select distinct mp.organization_code "ORG", We.Wip_Entity_Name JOB, msib.segment1 ASSEMBLY, msib.description "DESCRIPTION", (SELECT distinct mc1.CONCATENATED_SEGMENTS FROM apps.mtl_item_categories mic, apps.mtl_category_sets_tl mcst1, apps.mtl_category_sets_b mcs1, apps.mtl_categories_b_kfv mc1, apps.mtl_system_items_b msiv1 WHERE mic.category_set_id = mcs1.category_set_id AND mcs1.category_set_id = mcst1.category_set_id AND mcst1.LANGUAGE = USERENV ('LANG') AND mic.category_id = mc1.category_id AND msiv1.organization_id = mic.organization_id AND msiv1.inventory_item_id = mic.inventory_item_id and mic.category_set_id=3 and msiv1.segment1=msib.segment1) "PRODUCT FAMILY", (SELECT Meaning FROM apps.fnd_lookup_values WHERE lookup_type LIKE 'WIP_ENTITY' and lookup_code = we.entity_type and language = 'US') TYPE, Wdj.Class_Code CLASS, wdj.net_quantity QUANTITY, (SELECT NVL(msib1.segment1, ' ') from apps.mtl_system_items_b msib1 where msib1.inventory_item_id = wdj.bom_reference_id and msib1.ORGANIZATION_ID = wdj.ORGANIZATION_ID) "BOM REFERENCE", wdj.bom_revision "BOM REV", NVL(Wdj.Alternate_BOM_Designator, ' ') "ALTERNATE BOM", (SELECT NVL(msib2.segment1, ' ') from apps.mtl_system_items_b msib2 where msib2.inventory_item_id = wdj.routing_reference_id and msib2.ORGANIZATION_ID = wdj.ORGANIZATION_ID) "ROUTING REFERENCE", wdj.routing_revision "ROUTING REV", NVL(Wdj.Alternate_Routing_Designator, ' ') "ALTERNATE ROUTING", (SELECT Meaning FROM apps.fnd_lookup_values WHERE lookup_type LIKE 'WIP_CLASS_TYPE' and lookup_code = wdj.job_type and language = 'US') "WO TYPE", (SELECT Meaning FROM apps.fnd_lookup_values WHERE lookup_type LIKE 'WIP_JOB_STATUS' and lookup_code = wdj.status_type and language = 'US') "WO STATUS", NVL(We.Description, ' ') "WO DESCRIPTION", -- to_date(Wdj.scheduled_start_date,'dd-mon-yyyy hh24:mi:ss') Wdj.scheduled_start_date "SCHEDULED_START_DATE", to_date(Wdj.date_released,'dd-mon-yyyy hh24:mi:ss') "WO RELEASE DATE", to_date(Wdj.scheduled_completion_date,'dd-mon-yyyy hh24:mi:ss') "SCHEDULED_COMPLETION_DATE", to_date(Wdj.date_completed,'dd-mon-yyyy hh24:mi:ss') "WO COMPLETION DATE", trunc(Wdj.date_completed)-trunc(Wdj.scheduled_completion_date) "DAYS LATE", wdj.date_closed "WO CLOSED DATE", bosv.department_code "DEPARTMENT", wo.Operation_Seq_Num "OPERATION SEQ NUM", wo.attribute1 LABEL, wo.description OPERATION_DESCRIPTION, to_date(Wo.First_Unit_Start_Date,'dd-mon-yyyy hh24:mi:ss') OPN_START_DATE, to_date(Wo.Last_Unit_Completion_Date,'dd-mon-yyyy hh24:mi:ss') OPN_COMPLETION_DATE, NVL(Bso.Operation_Code, ' ') OPERATION_CODE, wdj.start_quantity "ORDER QTY", wdj.quantity_completed "QTY COMPLETE", wdj.quantity_scrapped "QTE SCRAPPED", ((wdj.quantity_scrapped/wdj.start_quantity)*100) "SCRAP %", (Select Round((wo.Last_Unit_Completion_Date - wo.First_Unit_Start_Date)* 24*60,2)"Round" from DUAL) "PLANNED RUN TIME (MINS)", (select round ((Wdj.date_completed - Wdj.date_released)* 24*60,2) "Round" from DUAL) "ACTUAL ELAPSED TIME (MINS)", (select Round(((wo.Last_Unit_Completion_Date - wo.First_Unit_Start_Date)-(Wdj.date_completed - Wdj.date_released))* 24*60,2) "Round" from DUAL)"OVER/UNDER RUN" from apps.wip_operations wo ,apps.wip_entities we ,apps.wip_discrete_jobs wdj ,apps.mtl_system_items_b msib ,apps.bom_operational_routings bor ,apps.bom_operation_sequences bos ,apps.bom_standard_operations bso ,apps.BOM_OPERATION_SEQUENCES_V bosv ,apps.mfg_lookups ml ,apps.mtl_parameters mp where 1=1 and wo.wip_entity_id = we.wip_entity_id and wo.operation_sequence_id = bos.operation_sequence_id and wo.operation_sequence_id = bosv.operation_sequence_id and wdj.wip_entity_id = we.wip_entity_id and msib.inventory_item_id = wdj.primary_item_id and msib.ORGANIZATION_ID = wdj.ORGANIZATION_ID and msib.inventory_item_id = bor.assembly_item_id and bos.routing_sequence_id = bor.routing_sequence_id and bso.standard_operation_id(+) = bos.standard_operation_id and wdj.organization_id=mp.organization_id --and ml.lookup_code (+) = to_number(bos.shutdown_type) --and ml.lookup_type (+) = 'BOM_EAM_SHUTDOWN_TYPE' and we.entity_type not in (6,7) --and Wdj.Class_Code not in ('BLDGMAINT', 'CALIB', 'ENGMAINT', 'MAINT') and wdj.status_type = 4 and mp.organization_code='M1' --and we.wip_entity_name = 'MC31749' and wdj.creation_date > '01-jan-2009' Order by to_date(Wdj.date_released,'dd-mon-yyyy hh24:mi:ss'), We.Wip_Entity_Name, Wo.Operation_Seq_Num Y US WIP - Senfor Intl Production Job Details 2May2025 1=1