Work Orders with Resources

  SELECT DISTINCT

         we.wip_entity_name wo_num

       , we.creation_date

       , wo_status.meaning status

       , DECODE (wdj.firm_planned_flag,  1, 'Yes',  2, 'No') firm

       , fu.description cr_by

       , wdj.asset_number || ' (' || msn.descriptive_text || ')' asset

       , wov.operation_seq_num op

       , wov.description op_descr

       , worv.resource_code rsc

       , worv.description rsc_descr

       , worv.usage_rate_or_amount rsc_amt

       , worv.assigned_units rsc_units

       , wdj.scheduled_start_date start_

       , wdj.scheduled_completion_date end_

       , we.description

    FROM wip.wip_entities we

       , wip.wip_discrete_jobs wdj

       , 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

       , apps.wip_operation_resources_v worv

   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 = worv.wip_entity_id(+)

     AND NVL (wov.operation_seq_num, 0) = NVL (worv.operation_seq_num, 0)

--        AND wo_status.meaning = 'Complete'

     AND we.creation_date > '17-JUL-2013'

ORDER BY we.creation_date DESC

       , wov.operation_seq_num;