SELECT wdd.delivery_detail_id delivery_detail_id,
wdd.source_header_number order_number,
wdd.source_line_number line_number, wdd.source_header_id header_id,
wdd.source_header_type_id order_type_id,
wdd.source_header_type_name order_type_name,
wdd.source_line_id line_id, wdd.customer_id customer_id,
wdd.inventory_item_id inventory_item_id,
wdd.item_description item_description,
wdd.ship_from_location_id ship_from_location_id,
wdd.organization_id organization_id,
wdd.requested_quantity requested_quantity,
wdd.requested_quantity_uom requested_quantity_uom,
wnd.delivery_id delivery_id, wnd.NAME delivery_name,
wts.trip_id trip_id, wts.stop_id stop_id,
wdl.delivery_leg_id delivery_leg_id
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND wnd.initial_pickup_location_id = wts.stop_location_id
AND wnd.delivery_id = wdl.delivery_id
AND wdl.pick_up_stop_id = wts.stop_id
AND wda.delivery_id IS NOT NULL
AND wdd.released_status = 'B'
AND NVL (wdd.line_direction, 'O') IN ('O', 'IO')
AND wnd.delivery_type = 'STANDARD'
UNION
SELECT wdd.delivery_detail_id delivery_detail_id,
wdd.source_header_number order_number,
wdd.source_line_number line_number, wdd.source_header_id header_id,
wdd.source_header_type_id order_type_id,
wdd.source_header_type_name order_type_name,
wdd.source_line_id line_id, wdd.customer_id customer_id,
wdd.inventory_item_id inventory_item_id,
wdd.item_description item_description,
wdd.ship_from_location_id ship_from_location_id,
wdd.organization_id organization_id,
wdd.requested_quantity requested_quantity,
wdd.requested_quantity_uom requested_quantity_uom,
wnd.delivery_id delivery_id, wnd.NAME delivery_name,
TO_NUMBER (NULL) trip_id, TO_NUMBER (NULL) stop_id,
TO_NUMBER (NULL) delivery_leg_id
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd,
wsh_delivery_legs wdl
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND wnd.delivery_id = wdl.delivery_id(+)
AND wdd.released_status = 'B'
AND wda.delivery_id IS NOT NULL
AND wdl.delivery_id IS NULL
AND NVL (wdd.line_direction, 'O') IN ('O', 'IO')
AND wnd.delivery_type = 'STANDARD'
UNION
SELECT wdd.delivery_detail_id delivery_detail_id,
wdd.source_header_number order_number,
wdd.source_line_number line_number, wdd.source_header_id header_id,
wdd.source_header_type_id order_type_id,
wdd.source_header_type_name order_type_name,
wdd.source_line_id line_id, wdd.customer_id customer_id,
wdd.inventory_item_id inventory_item_id,
wdd.item_description item_description,
wdd.ship_from_location_id ship_from_location_id,
wdd.organization_id organization_id,
wdd.requested_quantity requested_quantity,
wdd.requested_quantity_uom requested_quantity_uom,
TO_NUMBER (NULL) delivery_id, NULL delivery_name,
TO_NUMBER (NULL) trip_id, TO_NUMBER (NULL) stop_id,
TO_NUMBER (NULL) delivery_leg_id
FROM wsh_delivery_details wdd, wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.released_status = 'B'
AND wda.delivery_id IS NULL
AND NVL (wdd.line_direction, 'O') IN ('O', 'IO');
