SELECT OOH.ORDER_NUMBER
, MSI.SEGMENT1
, MR.RESERVATION_QUANTITY
, MR.LOT_NUMBER
, WDD.ORGANIZATION_ID
FROM OE_ORDER_HEADERS_ALL OOH
, MTL_SYSTEM_ITEMS_B MSI
, WSH_DELIVERY_DETAILS WDD
, MTL_RESERVATIONS MR
WHERE MR.REQUIREMENT_DATE < (SYSDATE - 1)
AND WDD.SOURCE_HEADER_ID = OOH.HEADER_ID
AND MSI.ORGANIZATION_ID = <<master item organization id>>
AND MR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND WDD.SHIPPED_QUANTITY IS NOT NULL
AND WDD.SOURCE_LINE_ID = MR.DEMAND_SOURCE_LINE_ID
AND NOT EXISTS (SELECT INVENTORY_ITEM_ID FROM MTL_TRANSACTIONS_INTERFACE
WHERE
TRX_SOURCE_LINE_ID = WDD.SOURCE_LINE_ID)
GROUP BY OOH.ORDER_NUMBER
, MSI.SEGMENT1
, MR.RESERVATION_QUANTITY
, MR.LOT_NUMBER
, WDD.ORGANIZATION_ID
ORDER BY OOH.ORDER_NUMBER, MSI.SEGMENT1
-------------------------------------------------------------------------