SELECT WIP_DJ.wip_entity_id,
      wip_dj.lot_number INPUT_LOT,
      WIP_DJ.organization_id,
      WIP_DJ.date_released,
      WIP_DJ.last_update_date,
      WIP_DJ.start_quantity,
      WE.wip_entity_name,
      WIP_DJ.common_routing_sequence_id,
      wo.operation_seq_num,
     bop.operation_code
FROM   --mtl_lot_numbers MTLL,
      wip.wip_discrete_jobs WIP_DJ,
      wip_entities WE,
    --  mtl_object_genealogy GEN,
      wip_operations WO,
      bom_standard_operations bop
WHERE  WIP_DJ.wip_entity_id = WE.wip_entity_id
      AND WIP_DJ.organization_id = WE.organization_id
       AND WIP_DJ.PRIMARY_ITEM_ID = WE.PRIMARY_ITEM_ID
     -- AND WE.gen_object_id = GEN.parent_object_id
    --  AND (WIP_DJ.last_update_date BETWEEN To_date (:last_run, 'YYYY-MM-DD HH24:MI:SS') AND
    --                                   To_date (:now, 'YYYY-MM-DD HH24:MI:SS') )
    --  AND GEN.object_id = MTLL.gen_object_id
    --  AND mtll.organization_id = wip_dj.organization_id
      AND wo.organization_id = we.organization_id
      AND wo.wip_entity_id  = we.wip_entity_id
      AND bop.standard_operation_id = wo.standard_operation_id
     -- and we.wip_entity_name = 'ERT4'
      --AND WIP_DJ.organization_id = :orgid
UNION
SELECT srj.wip_entity_id,
      NVL(ssj.wip_entity_name,wip_dj.lot_number) INPUT_LOT,
      srj.organization_id,
      WIP_DJ.date_released,
      WIP_DJ.last_update_date,
      srj.start_quantity,
      srj.wip_entity_name,
      srj.common_routing_sequence_id,
      wo.operation_seq_num,
     bop.operation_code
FROM  wip_operations WO,
      bom_standard_operations bop,
      wsm_sm_starting_jobs ssj,
      wsm_sm_resulting_jobs srj,
      wip_discrete_jobs wip_dj,
      wip_entities we
WHERE
    --  AND (WIP_DJ.last_update_date BETWEEN To_date (:last_run, 'YYYY-MM-DD HH24:MI:SS') AND
    --                                   To_date (:now, 'YYYY-MM-DD HH24:MI:SS') )
       srj.transaction_id = (SELECT MAX(transaction_id) FROM wsm_sm_resulting_jobs srj2 WHERE srj2.wip_entity_id = WIP_DJ.wip_entity_id)
      AND ssj.transaction_id = srj.transaction_id
      AND ssj.wip_entity_id = srj.wip_entity_id
      AND wo.organization_id = srj.organization_id
      AND wo.wip_entity_id  = srj.wip_entity_id
      AND bop.standard_operation_id = wo.standard_operation_id
      AND WIP_DJ.wip_entity_id = srj.wip_entity_id
      AND WIP_DJ.organization_id = srj.organization_id
      AND WIP_DJ.wip_entity_id = WE.wip_entity_id
    --  --AND WIP_DJ.organization_id = :orgid