WIP - WIP Production Job Details

select distinct 
 
       We.Wip_Entity_Name    JOB,
       msib.segment1         ASSEMBLY,
       msib.description     "DESCRIPTION",  
       (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,  
        NVL(Wdj.Alternate_BOM_Designator, ' ') ALTERNATE_BILL, 
       (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,   
        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') STATUS,
       NVL(We.Description, ' ')        "WO DESCRIPTION",  
       Wdj.date_released WO_RELEASE_DATE,
       Wdj.date_completed WO_COMPLETION_DATE,
       wdj.date_closed WO_CLOSED_DATE,
       wo.Operation_Seq_Num OPERATION_SEQ_NUM,       
       to_char(Wo.First_Unit_Start_Date,'dd-mon-yyyy hh24:mi:ss') OPN_START_DATE, 
       to_char(Wo.Last_Unit_Completion_Date,'dd-mon-yyyy hh24:mi:ss') OPN_COMPLETION_DATE, 
       NVL(Bso.Operation_Code, ' ') OPERATION_CODE, 
       (Select Round((wo.Last_Unit_Completion_Date - wo.First_Unit_Start_Date)* 24*60,0)"Round" from DUAL) RUN_TIME_MINS 
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.mfg_lookups ml
where  1=1
and    wo.wip_entity_id   = we.wip_entity_id 
and    wo.operation_sequence_id = bos.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    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') 
Order by to_char(Wdj.date_released,'dd-mon-yyyy hh24:mi:ss'), We.Wip_Entity_Name, Wo.Operation_Seq_Num