Work Orders with Materials

  SELECT we.wip_entity_name wo_num

       , we.creation_date

       , wdj.asset_number

       , wo_status.meaning status

       , we.description

       , fu.description cr_by

       , wdj.class_code

       , wdj.date_released

       , wdj.date_completed

       , wdj.asset_number

       , msn.descriptive_text

       , worv.operation_seq_num op

       , worv.resource_code rsc

       , msib.segment1 item

       , msib.description item_descr

       , wro.suggested_vendor_name suppl

    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

       , wip.wip_requirement_operations wro

       , inv.mtl_system_items_b msib

   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 wro.wip_entity_id = we.wip_entity_id

     AND wro.inventory_item_id = msib.inventory_item_id

     AND we.creation_date > '01-JAN-2013'

     AND wo_status.meaning = 'Complete'

     AND msib.segment1 = 'MY_ITEM'

     AND worv.resource_code = 'MY_RSC_CODE'

ORDER BY we.wip_entity_name DESC;