[Home] [Help]
394:
395: CURSOR c_dels_for_trip(p_tripid NUMBER) IS
396: SELECT distinct wnd.delivery_id, wnd.name
397: FROM wsh_trips wt,
398: wsh_trip_stops wts,
399: wsh_delivery_legs wdl,
400: wsh_new_deliveries wnd
401: WHERE wt.trip_id = wts.trip_id
402: AND wts.stop_id = wdl.pick_up_stop_id
404: AND wts.trip_id = p_tripid;
405:
406: CURSOR c_stops_for_trip(p_tripid NUMBER) IS
407: SELECT stop_id
408: FROM wsh_trip_stops
409: WHERE trip_id= p_tripid;
410:
411: CURSOR c_legs_for_del(p_deliveryid NUMBER) IS
412: SELECT delivery_leg_id
634:
635: --construct SQL for empty trips
636: empty_trip_sql := ' SELECT distinct wt.trip_id, wt.name
637: FROM wsh_trips wt,
638: wsh_trip_stops wts,
639: wsh_delivery_legs wdl1,
640: wsh_delivery_legs wdl2
641: WHERE ';
642:
659: AND wdl1.delivery_leg_id IS NULL
660: AND wdl2.delivery_leg_id IS NULL
661: AND NOT EXISTS
662: ( SELECT 1
663: FROM wsh_trip_stops wtss,
664: wsh_delivery_legs wdl1s
665: WHERE wtss.trip_id = wt.trip_id
666: AND wdl1s.pick_up_stop_id = wtss.stop_id
667: )
1470: Input: Table of Record Types for Trip Stops
1471: Output: Return Status - success or failure
1472: ==============================================================================
1473: Logic: i) Delete records from the following tables:
1474: WSH_EXCEPTIONS, WSH_FREIGHT_COSTS, WSH_TRIP_STOPS
1475: -----------------------------------------------------------------------------*/
1476:
1477: PROCEDURE Purge_Trip_Stops( p_tbl_trip_stop_purge_set Trip_Stop_ID_Tbl_Type,
1478: x_return_status OUT NOCOPY VARCHAR2
1522: END IF;
1523: END IF;
1524:
1525: DELETE
1526: FROM wsh_trip_stops
1527: WHERE stop_id = l_stop_id;
1528:
1529: IF SQL%FOUND THEN
1530: IF l_debug_on THEN
1527: WHERE stop_id = l_stop_id;
1528:
1529: IF SQL%FOUND THEN
1530: IF l_debug_on THEN
1531: WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_trip_stops: STOP_ID=' || l_stop_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1532: END IF;
1533: END IF;
1534:
1535: END LOOP;
2666:
2667: CURSOR c_lpns_for_trip(p_tripid NUMBER) IS
2668: SELECT wdd.lpn_id
2669: FROM wsh_trips wt,
2670: wsh_trip_stops wts,
2671: wsh_delivery_legs wdl,
2672: wsh_new_deliveries wnd,
2673: wsh_delivery_assignments_v wda,
2674: wsh_delivery_details wdd
2692: SELECT
2693: DISTINCT wt1.trip_id
2694: FROM
2695: wsh_trips wt1,
2696: wsh_trip_stops pickup_stop1,
2697: wsh_trip_stops dropoff_stop1,
2698: wsh_delivery_legs wdl1
2699: WHERE
2700: wdl1.pick_up_stop_id = pickup_stop1.stop_id AND
2693: DISTINCT wt1.trip_id
2694: FROM
2695: wsh_trips wt1,
2696: wsh_trip_stops pickup_stop1,
2697: wsh_trip_stops dropoff_stop1,
2698: wsh_delivery_legs wdl1
2699: WHERE
2700: wdl1.pick_up_stop_id = pickup_stop1.stop_id AND
2701: wdl1.drop_off_stop_id = dropoff_stop1.stop_id AND
2709: START WITH delivery_id IN (SELECT wdl.delivery_id
2710: FROM
2711: wsh_new_deliveries wnd,
2712: wsh_delivery_legs wdl,
2713: wsh_trip_stops pickup_stop,
2714: wsh_trip_stops dropoff_stop,
2715: wsh_trips wt
2716: WHERE
2717: wnd.delivery_id = wdl.delivery_id AND
2710: FROM
2711: wsh_new_deliveries wnd,
2712: wsh_delivery_legs wdl,
2713: wsh_trip_stops pickup_stop,
2714: wsh_trip_stops dropoff_stop,
2715: wsh_trips wt
2716: WHERE
2717: wnd.delivery_id = wdl.delivery_id AND
2718: wdl.pick_up_stop_id = pickup_stop.stop_id AND
2733: SELECT
2734: DISTINCT wt1.trip_id
2735: FROM
2736: wsh_trips wt1,
2737: wsh_trip_stops wts,
2738: wsh_delivery_legs wdl1
2739: WHERE
2740: (wdl1.pick_up_stop_id = wts.stop_id OR
2741: wdl1.drop_off_stop_id = wts.stop_id) AND
2749: (
2750: SELECT wdl.delivery_leg_id
2751: FROM
2752: wsh_delivery_legs wdl,
2753: wsh_trip_stops wts,
2754: wsh_trips wt
2755: WHERE
2756: (wdl.pick_up_stop_id = wts.stop_id OR
2757: wdl.drop_off_stop_id = wts.stop_id) AND