[Home] [Help]
5975: SELECT NVL(wts.planned_departure_date,
5976: NVL(wdd.date_scheduled,
5977: NVL(ool.schedule_ship_date, ool.promise_date))) AS expected_ship_date
5978: FROM wsh_delivery_details wdd, oe_order_lines_all ool,
5979: wsh_delivery_assignments_v wda, wsh_new_deliveries wnd, wsh_delivery_legs wdl,
5980: wsh_trip_stops wts, wsh_trips wt
5981: WHERE wdd.delivery_detail_id = p_delivery_line_id
5982: AND ool.line_id = p_source_line_id
5983: AND wdd.source_line_id = ool.line_id
5986: AND wda.delivery_id = wnd.delivery_id (+)
5987: AND wnd.delivery_id = wdl.delivery_id (+)
5988: AND (wdl.sequence_number IS NULL OR
5989: wdl.sequence_number = (SELECT MIN(sequence_number)
5990: FROM wsh_delivery_legs wdl_first_leg
5991: WHERE wdl_first_leg.delivery_id = wdl.delivery_id))
5992: AND wdl.pick_up_stop_id = wts.stop_id (+)
5993: AND wts.trip_id = wt.trip_id (+);
5994:
6047: -- MIN(NVL(wts.planned_departure_date,
6048: -- NVL(wdd.date_scheduled,
6049: -- NVL(ool.schedule_ship_date, ool.promise_date)))) AS min_expected_ship_date,
6050: FROM wsh_new_deliveries wnd, wsh_delivery_details wdd, wsh_delivery_assignments_v wda,
6051: wsh_delivery_legs wdl, wsh_trip_stops wts
6052: -- oe_order_lines_all ool
6053:
6054: WHERE wdd.delivery_detail_id = p_delivery_detail_id
6055: -- AND wdd.source_line_id = ool.line_id (+)
6058: AND wda.delivery_detail_id = wdd.delivery_detail_id (+)
6059: AND wnd.delivery_id = wdl.delivery_id (+)
6060: AND (wdl.sequence_number IS NULL OR
6061: wdl.sequence_number = (SELECT MIN(sequence_number)
6062: FROM wsh_delivery_legs wdl_first_leg
6063: WHERE wdl_first_leg.delivery_id = wdl.delivery_id))
6064: AND wdl.pick_up_stop_id = wts.stop_id (+)
6065: GROUP BY wnd.organization_id, wnd.delivery_id, wts.stop_id;
6066: