SELECT
 operation_code,
 operation_seq_num,
 test_condition
FROM
 (
     SELECT
         msi.inventory_item_id,
         msi.organization_id,
         msi.segment1,
         we.wip_entity_name,
         bop.operation_code,
         wo.operation_seq_num,
         wo.description,
         wo.quantity_in_queue,
         wo.quantity_running,
         wo.quantity_waiting_to_move,
         'Test Condition '
         || ROW_NUMBER() OVER(
             PARTITION BY msi.segment1,
             we.wip_entity_name,
             bop.operation_code
             ORDER BY
                 wo.operation_seq_num
         ) AS test_specification_name
     FROM
         apps.mtl_system_items msi,
         apps.wip_entities we,
         apps.wip_operations wo,
         apps.wip_discrete_jobs wdj,
         apps.bom_standard_operations bop,
         apps.wip_operation_resources wor,
         apps.bom_resources res
     WHERE
         msi.inventory_item_id = we.primary_item_id
         AND   msi.organization_id = we.organization_id
         AND   we.wip_entity_id = wo.wip_entity_id
         AND   we.organization_id = wo.organization_id
         AND   we.wip_entity_id = wdj.wip_entity_id
         AND   we.organization_id = wdj.organization_id
         AND   bop.standard_operation_id = wo.standard_operation_id
         AND   wo.wip_entity_id = wdj.wip_entity_id
         AND   wo.organization_id = wdj.organization_id
         AND   wor.wip_entity_id = wdj.wip_entity_id
         AND   wor.operation_seq_num = wo.operation_seq_num
         AND   res.resource_id = wor.resource_id
         AND   res.resource_code LIKE 'TP%'
--         AND   wo.operation_seq_num not in (Select operation_seq_num
--                                              From wip_transactions wt
--                                             Where wt.wip_entity_id =  wo.wip_entity_id
--                                               and wt.organization_id =  wo.organization_id
--                                               and wt.transaction_type = 14)
         AND   we.wip_entity_name = 'TEST16'
 ) job,
 apps.xxmlx_item_testprog_details_v tp
WHERE
 tp.test_specification_name = job.test_specification_name
 AND   tp.item_id = job.inventory_item_id
 AND   tp.organization_id = job.organization_id;