Thursday, 26 December 2013

Operation Count per Work Order

  SELECT we.wip_entity_name wo_num
       , we.creation_date
       , COUNT (DISTINCT worv.operation_seq_num) op_count
    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 wo_status.meaning = 'Released'
GROUP BY we.wip_entity_name
       , we.creation_date
  HAVING COUNT (DISTINCT worv.operation_seq_num) > 0
ORDER BY we.wip_entity_name DESC;

1 comment:

  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