SELECT DISTINCT a.delivery_id, 
                To_number (c.source_header_number)order_number 
FROM   wsh_new_deliveries a, 
       wsh_delivery_assignments b, 
       wsh_delivery_details c 
WHERE  a.delivery_id = b.delivery_id 
       AND b.delivery_detail_id = c.delivery_detail_id 
       AND To_number(c.source_header_number) BETWEEN 
           Nvl(:from_order_number, To_number(c.source_header_number)) AND 
           Nvl( :to_order_number, To_number(c.source_header_number)) 
       AND a.customer_id = Nvl(:customer_id, a.customer_id) 
       AND c.source_header_type_id = 
           Nvl (:order_type_id, c.source_header_type_id) 
       AND b.parent_delivery_detail_id IS NULL 
       AND c.source_code = 'OE' 
       AND a.status_code <> 'CL' 
ORDER  BY 1 ASC