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;