select mtl_lot.inventory_item_id,mtl_lot.organization_id,mtl_lot.locator_id
,ltrim( max(sys_connect_by_path(mtl_lot.LOT_NUMBER, ',') ),',') lot_number
from
(SELECT moq.inventory_item_id
,moq.organization_id
,moq.locator_id
,moq.LOT_NUMBER
, row_number() over(PARTITION BY moq.inventory_item_id,moq.organization_id,moq.locator_id
ORDER BY moq.inventory_item_id,moq.organization_id,moq.locator_id ) rn
FROM mtl_onhand_quantities moq,
mtl_system_items_b msi,
mtl_item_locations_kfv mil,
org_organization_definitions ood,
hr_operating_units hou
WHERE moq.organization_id = msi.organization_id
AND moq.inventory_item_id = msi.inventory_item_id
AND moq.locator_id = mil.inventory_location_id(+)
AND moq.organization_id = mil.organization_id(+)
AND moq.organization_id = ood.organization_id
AND ood.operating_unit = hou.organization_id
and moq.SUBINVENTORY_CODE='LENDING'
group by moq.inventory_item_id,moq.organization_id,moq.locator_id,LOT_NUMBER
) mtl_lot
START WITH rn = 1
CONNECT BY PRIOR rn = rn - 1
AND mtl_lot.inventory_item_id = PRIOR mtl_lot.inventory_item_id
AND mtl_lot.organization_id = PRIOR mtl_lot.organization_id
AND mtl_lot.locator_id = PRIOR mtl_lot.locator_id
group by mtl_lot.inventory_item_id,mtl_lot.organization_id,mtl_lot.locator_id
