WIP - Senfor Intl Production Job Details

-- 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