[Home] [Help]
401:
402: CURSOR c_dels_for_trip(p_tripid NUMBER) IS
403: SELECT distinct wnd.delivery_id, wnd.name
404: FROM wsh_trips wt,
405: wsh_trip_stops wts,
406: wsh_delivery_legs wdl,
407: wsh_new_deliveries wnd
408: WHERE wt.trip_id = wts.trip_id
409: AND wts.stop_id = wdl.pick_up_stop_id
411: AND wts.trip_id = p_tripid;
412:
413: CURSOR c_stops_for_trip(p_tripid NUMBER) IS
414: SELECT stop_id
415: FROM wsh_trip_stops
416: WHERE trip_id= p_tripid;
417:
418: CURSOR c_legs_for_del(p_deliveryid NUMBER) IS
419: SELECT delivery_leg_id
709:
710: --construct SQL for empty trips
711: empty_trip_sql := ' SELECT distinct wt.trip_id, wt.name
712: FROM wsh_trips wt,
713: wsh_trip_stops wts,
714: wsh_delivery_legs wdl1,
715: wsh_delivery_legs wdl2
716: WHERE ';
717:
735: AND wdl1.delivery_leg_id IS NULL
736: AND wdl2.delivery_leg_id IS NULL
737: AND NOT EXISTS
738: ( SELECT 1
739: FROM wsh_trip_stops wtss,
740: wsh_delivery_legs wdl1s
741: WHERE wtss.trip_id = wt.trip_id
742: AND wdl1s.pick_up_stop_id = wtss.stop_id
743: )' ;
1595: Input: Table of Record Types for Trip Stops
1596: Output: Return Status - success or failure
1597: ==============================================================================
1598: Logic: i) Delete records from the following tables:
1599: WSH_EXCEPTIONS, WSH_FREIGHT_COSTS, WSH_TRIP_STOPS
1600: -----------------------------------------------------------------------------*/
1601:
1602: PROCEDURE Purge_Trip_Stops( p_tbl_trip_stop_purge_set Trip_Stop_ID_Tbl_Type,
1603: x_return_status OUT NOCOPY VARCHAR2
1647: END IF;
1648: END IF;
1649:
1650: DELETE
1651: FROM wsh_trip_stops
1652: WHERE stop_id = l_stop_id;
1653:
1654: IF SQL%FOUND THEN
1655: IF l_debug_on THEN
1652: WHERE stop_id = l_stop_id;
1653:
1654: IF SQL%FOUND THEN
1655: IF l_debug_on THEN
1656: WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_trip_stops: STOP_ID=' || l_stop_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1657: END IF;
1658: END IF;
1659:
1660: END LOOP;
2853:
2854: CURSOR c_lpns_for_trip(p_tripid NUMBER) IS
2855: SELECT wdd.lpn_id
2856: FROM wsh_trips wt,
2857: wsh_trip_stops wts,
2858: wsh_delivery_legs wdl,
2859: wsh_new_deliveries wnd,
2860: wsh_delivery_assignments_v wda,
2861: wsh_delivery_details wdd
2879: SELECT
2880: DISTINCT wt1.trip_id
2881: FROM
2882: wsh_trips wt1,
2883: wsh_trip_stops pickup_stop1,
2884: wsh_trip_stops dropoff_stop1,
2885: wsh_delivery_legs wdl1
2886: WHERE
2887: wdl1.pick_up_stop_id = pickup_stop1.stop_id AND
2880: DISTINCT wt1.trip_id
2881: FROM
2882: wsh_trips wt1,
2883: wsh_trip_stops pickup_stop1,
2884: wsh_trip_stops dropoff_stop1,
2885: wsh_delivery_legs wdl1
2886: WHERE
2887: wdl1.pick_up_stop_id = pickup_stop1.stop_id AND
2888: wdl1.drop_off_stop_id = dropoff_stop1.stop_id AND
2896: START WITH delivery_id IN (SELECT wdl.delivery_id
2897: FROM
2898: wsh_new_deliveries wnd,
2899: wsh_delivery_legs wdl,
2900: wsh_trip_stops pickup_stop,
2901: wsh_trip_stops dropoff_stop,
2902: wsh_trips wt
2903: WHERE
2904: wnd.delivery_id = wdl.delivery_id AND
2897: FROM
2898: wsh_new_deliveries wnd,
2899: wsh_delivery_legs wdl,
2900: wsh_trip_stops pickup_stop,
2901: wsh_trip_stops dropoff_stop,
2902: wsh_trips wt
2903: WHERE
2904: wnd.delivery_id = wdl.delivery_id AND
2905: wdl.pick_up_stop_id = pickup_stop.stop_id AND
2920: SELECT
2921: DISTINCT wt1.trip_id
2922: FROM
2923: wsh_trips wt1,
2924: wsh_trip_stops wts,
2925: wsh_delivery_legs wdl1
2926: WHERE
2927: (wdl1.pick_up_stop_id = wts.stop_id OR
2928: wdl1.drop_off_stop_id = wts.stop_id) AND
2936: (
2937: SELECT wdl.delivery_leg_id
2938: FROM
2939: wsh_delivery_legs wdl,
2940: wsh_trip_stops wts,
2941: wsh_trips wt
2942: WHERE
2943: (wdl.pick_up_stop_id = wts.stop_id OR
2944: wdl.drop_off_stop_id = wts.stop_id) AND