SELECT
ooha.order_number,
wdd.DELIVERY_DETAIL_ID ,
wda.delivery_detail_id,
wnd.delivery_id
from
oe_order_headers_all ooha,
oe_order_lines_all oola
,wsh_new_deliveries wnd
,wsh_delivery_assignments wda
,wsh_delivery_details wdd
where wdd.SOURCE_HEADER_ID=ooha.HEADER_ID
AND wdd.source_line_id = ool.line_id
and ooha.header_id=oola.HEADER_ID
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wnd.delivery_id = wda.delivery_id
and ooha.ORDER_NUMBER=:ORDER_NUMBER