---sales order against lot number
SELECT oola.ordered_quantity,
oola.order_quantity_uom,
oola.ordered_item,
ooha.order_number,
MTLN.LOT_NUMBER
FROM apps.mtl_material_transactions mmt
,apps.oe_order_lines_all oola
,apps.oe_order_headers_all ooha
,apps.mtl_transaction_lot_numbers mtln
WHERE mmt.trx_source_line_id = oola.line_id
AND mmt.organization_id= oola.ship_from_org_id
AND oola.header_id = ooha.header_id
AND mmt.transaction_id = mtln.transaction_id
-- and mmt.organization_id=3054
AND ooha.order_number = NVL(P_ORDER_NUMBER,ooha.order_number)
Order by ooha.order_number;