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;