Thursday 26 December 2013

Work Orders with Operations

SELECT DISTINCT
         we.wip_entity_name wo_num
       , wewr.work_request_id work_req
       , we.creation_date
       , wo_status.meaning status
       , DECODE (wdj.firm_planned_flag,  1, 'Yes',  2, 'No') firm
       , fu.description cr_by
       , wdj.asset_number asset
       , msn.descriptive_text asset_descr
       , wov.creation_date op_ct_dt
       , wov.operation_seq_num op
       , wov.description op_descr
       , wdj.scheduled_start_date start_
       , wdj.scheduled_completion_date end_
       , we.description
    FROM wip.wip_entities we
       , wip.wip_discrete_jobs wdj
       , wip.wip_eam_work_requests wewr
       , inv.mtl_serial_numbers msn
       , applsys.fnd_user fu
       , (SELECT lookup_code, meaning FROM apps.fnd_lookup_values_vl WHERE lookup_type = 'WIP_JOB_STATUS') wo_status
       , apps.wip_operations_v wov
   WHERE we.created_by = fu.user_id(+)
     AND we.wip_entity_id = wdj.wip_entity_id
     AND wdj.asset_number = msn.serial_number(+)
     AND wdj.status_type = wo_status.lookup_code(+)
     AND we.wip_entity_id = wov.wip_entity_id(+)
     AND we.wip_entity_id = wewr.wip_entity_id
--     AND wo_status.meaning = 'Released'
ORDER BY we.creation_date DESC
       , wov.operation_seq_num;

No comments:

Post a Comment