Below is the query for getting expected shipments in Oracle Purchasing.
SELECT reqh.type_lookup_code,
reqh.requisition_header_id po_header_id,
rsl.requisition_line_id po_line_id,
rsl.req_distribution_id po_distribution_id,
reqh.segment1 po_number,
msi.segment1 AS item_code,
rsl.item_description,
rsh.shipment_num,
rsl.line_num shipment_line_number,
rsl.quantity_shipped expected_receipt_qty,
rss.lot_num,
rss.serial_num,
msn.attribute2 engine_number
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
rcv_serials_supply rss,
po_requisition_lines_all reql,
po_requisition_headers_all reqh,
mtl_system_items_b msi,
mtl_serial_numbers msn
WHERE 1 = 1
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.shipment_line_id = rss.shipment_line_id(+)
AND rsl.to_organization_id IN
(SELECT ood.organization_id
FROM org_organization_definitions ood, hr_operating_units hou
WHERE hou.organization_id = ood.operating_unit
AND hou.organization_id = lv_org_id_list2)
AND rsl.shipment_line_status_code = ‘EXPECTED’
AND reql.requisition_line_id(+) = rsl.requisition_line_id
AND reqh.requisition_header_id(+) = reql.requisition_header_id
AND reql.org_id = reqh.org_id
AND rsl.item_id = msi.inventory_item_id
AND rsh.organization_id = msi.organization_id
AND msn.serial_number(+) = rss.serial_num
AND rsh.shipment_num = nvl(p_shipment_number, rsh.shipment_num)
AND greatest(rsh.last_update_date,
rsl.last_update_date
) BETWEEN ld_from_date AND ld_to_date;
