APPS Multiple Rows Into Single row single column

The query returns the cross reference for an item in oracle inventory.Modify the table name and column name where required to concatenate the multiple rows into single row and single column.

select substr(cross_reference,2) cross_reference
from (
select inventory_item_id, max(sys_connect_by_path(cross_reference,',')) cross_reference
from (
select inventory_item_id, cross_reference, row_number() over (partition by inventory_item_id order by inventory_item_id ) rnum
from mtl_cross_references_b where inventory_item_id=iid
)
start with rnum = 1
connect by prior rnum = rnum-1 and prior inventory_item_id = inventory_item_id
group by inventory_item_id
order by inventory_item_id
)order by inventory_item_id;


Here iid is inventory item id for an items.