Thursday, 26 December 2013

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;

2 comments:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete
  2. For oracle related topics visit
    http://ebiztechnics.blogspot.com

    ReplyDelete