SELECT /*+ FIRST_ROWS */ pl.po_header_id purchase_order_id, pl.po_line_id, pl.line_num line_number, pl.item_description item_description, pl.unit_price unit_price, pl.closed_code closure_status, msi.segment1 item_number, DECODE (mc.structure_id, 2, mc.segment1, 201, mc.segment1, 50136, mc.segment1 || '.' || mc.segment2 || '.' || mc.segment3, 50165, mc.segment1, 50186, mc.segment1, 50187, mc.segment1 || '.' || mc.segment2, 50188, mc.segment1, 50210, mc.segment1 || '.' || mc.segment2 || '.' || mc.segment3 || '.' || mc.segment4, 50211, mc.segment1 || '.' || mc.segment2 || '.' || mc.segment3 || '.' || mc.segment4, 50270, mc.segment1 || '.' || mc.segment2 || '.' || mc.segment3, NULL ) CATEGORY, pl.unit_meas_lookup_code unit_of_measure, can.full_name cancelled_by_employee_name, clo.full_name closed_by_employee_name, pl.creation_date created_date FROM apps.po_lines_all pl, apps.mtl_system_items_b msi, apps.mtl_categories_b mc, apps.per_people_f can, apps.per_people_f clo WHERE EXISTS /*snipped starts*/ (select * from ( Select ms.segment1,max(ms.Creation_date) as MaxDate from apps.mtl_system_items_b ms, apps.mtl_system_items_b msi, apps.po_lines_all pl, apps.mtl_categories_b mc, apps.per_people_f can, apps.per_people_f clo where pl.item_id = msi.inventory_item_id(+) AND msi.organization_id(+) = 28 AND pl.category_id = mc.category_id(+) AND pl.cancelled_by = can.person_id(+) AND can.effective_start_date(+) <= TRUNC (SYSDATE) AND can.effective_end_date(+) >= TRUNC (SYSDATE) AND pl.closed_by = clo.person_id(+) AND clo.effective_start_date(+) <= TRUNC (SYSDATE) AND clo.effective_end_date(+) >= TRUNC (SYSDATE) group by ms.segment1) mss where mss.segment1= msi.segment1 and mss.Maxdate = msi.Creation_date )/*snipped ends*/ and pl.item_id = msi.inventory_item_id(+) AND msi.organization_id(+) = 28 AND pl.category_id = mc.category_id(+) AND pl.cancelled_by = can.person_id(+) AND can.effective_start_date(+) <= TRUNC (SYSDATE) AND can.effective_end_date(+) >= TRUNC (SYSDATE) AND pl.closed_by = clo.person_id(+) AND clo.effective_start_date(+) <= TRUNC (SYSDATE) AND clo.effective_end_date(+) >= TRUNC (SYSDATE) and msi.segment1= '1008176'