WIP - WIP Production Job Details

Description
Columns: Job, Assembly, Description, Type, Class, Quantity, Bom Reference, Alternate Bill, Routing Reference, Alternate Routing ...
This program extracts job details with release and operation dates and elapsed times according to when the transactions were entered in Oracle.

It is allows identifying job details to the operation level delays in production, for example when an operation is not completed or exceeds the time expected.

The report can be run by any combination of organization, release dates, job, assembly, job status.

License: Creative commons with Attribution by Ion Yılmaz, Senfor International, www.senfor-intl.eu ... 
This program extracts job details with release and operation dates and elapsed times according to when the transactions were entered in Oracle.

It is allows identifying job details to the operation level delays in production, for example when an operation is not completed or exceeds the time expected.

The report can be run by any combination of organization, release dates, job, assembly, job status.

License: Creative commons with Attribution by Ion Yılmaz, Senfor International, www.senfor-intl.eu
   more
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
Parameter Name SQL text Validation
Job Status
wdj.status_type=xxen_util.lookup_code(:job_status,'WIP_JOB_STATUS',700)
LOV
Assembly
msib.concatenated_segments=:item
LOV
Job
we.wip_entity_name=:job_name
LOV
Organization
wdj.organization_id=:organization_id
LOV Oracle
Release Date from
wdj.date_released >= :P_TRX_FROM
Date
Release Date to
wdj.date_released < :P_TRX_TO
Date