[Home] [Help]
1: PACKAGE BODY WSH_DELIVERY_LEGS_ACTIONS as
2: /* $Header: WSHDGACB.pls 120.13 2008/03/18 11:53:24 jnpinto noship $ */
3:
4: --
5: G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_DELIVERY_LEGS_ACTIONS';
1: PACKAGE BODY WSH_DELIVERY_LEGS_ACTIONS as
2: /* $Header: WSHDGACB.pls 120.13 2008/03/18 11:53:24 jnpinto noship $ */
3:
4: --
5: G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_DELIVERY_LEGS_ACTIONS';
6: --
7: g_wms_installed WSH_UTIL_CORE.Column_Tab_Type;
8:
9: CURSOR C_IS_FIRST_LEG(p_trip_id in number, p_delivery_id in number, p_pickup_loc_id in number) is
7: g_wms_installed WSH_UTIL_CORE.Column_Tab_Type;
8:
9: CURSOR C_IS_FIRST_LEG(p_trip_id in number, p_delivery_id in number, p_pickup_loc_id in number) is
10: select delivery_leg_id
11: from wsh_delivery_legs l, wsh_trip_stops s
12: where s.trip_id = p_trip_id
13: and l.delivery_id = p_delivery_id
14: and s.stop_id = l.pick_up_stop_id
15: and s.stop_location_id = p_pickup_loc_id;
83: st1.stop_location_id,
84: st2.stop_location_id
85: FROM wsh_trip_stops st1,
86: wsh_trip_stops st2,
87: wsh_delivery_legs dg
88: WHERE st1.stop_id = dg.pick_up_stop_id AND
89: st2.stop_id = dg.drop_off_stop_id AND
90: st1.trip_id = p_trip_id AND
91: st2.trip_id = p_trip_id AND
131: FOR UPDATE NOWAIT;
132:
133: CURSOR max_leg_seq_number ( del_id IN NUMBER) IS
134: SELECT max(dg.sequence_number)
135: FROM wsh_delivery_legs dg
136: WHERE dg.delivery_id = del_id;
137:
138: CURSOR get_sequence (l_stop_id NUMBER) IS
139: SELECT stop_sequence_number
210:
211:
212: --2709662
213: l_trip_stop_info wsh_trip_stops_pvt.trip_stop_rec_type;
214: l_delivery_leg_info wsh_delivery_legs_pvt.delivery_leg_rec_type;
215: l_rowid VARCHAR2(30);
216: l_pickup_stop_id NUMBER;
217: l_dropoff_stop_id NUMBER;
218: l_pickup_location_id NUMBER;
1611: --TD: and assigned to new trip stops
1612:
1613: IF (l_pickup_stop_id IS NOT NULL) THEN
1614:
1615: update wsh_delivery_legs
1616: set pick_up_stop_id = l_pickup_stop_id
1617: where delivery_leg_id = l_leg_id;
1618:
1619: IF (SQL%NOTFOUND) THEN
1659: END IF;
1660:
1661: IF (l_dropoff_stop_id IS NOT NULL) THEN
1662:
1663: update wsh_delivery_legs
1664: set drop_off_stop_id = l_dropoff_stop_id
1665: where delivery_leg_id = l_leg_id;
1666:
1667: IF (SQL%NOTFOUND) THEN
1729: --
1730: -- Debug Statements
1731: --
1732: IF l_debug_on THEN
1733: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_PVT.CREATE_DELIVERY_LEG',WSH_DEBUG_SV.C_PROC_LEVEL);
1734: END IF;
1735: --
1736: wsh_delivery_legs_pvt.create_delivery_leg(l_delivery_leg_info, l_rowid, l_leg_id, x_return_status);
1737:
1732: IF l_debug_on THEN
1733: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_PVT.CREATE_DELIVERY_LEG',WSH_DEBUG_SV.C_PROC_LEVEL);
1734: END IF;
1735: --
1736: wsh_delivery_legs_pvt.create_delivery_leg(l_delivery_leg_info, l_rowid, l_leg_id, x_return_status);
1737:
1738: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1739: RAISE e_InvalidDelExcep; -- Bug 5336308
1740: END IF;
2812: END IF;
2813: IF c_next_seq_exists%ISOPEN THEN
2814: close c_next_seq_exists;
2815: END IF;
2816: wsh_util_core.default_handler('WSH_DELIVERY_LEGS_ACTIONS.ASSIGN_DELIVERIES');
2817: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2818:
2819: IF l_debug_on THEN
2820: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2868: -- Note: dont merge next two cursors due to full table scan problem
2869:
2870: CURSOR del_pickup_exists(p_del_id IN NUMBER) IS
2871: SELECT delivery_leg_id
2872: FROM wsh_delivery_legs
2873: WHERE pick_up_stop_id = p_pickup_stop_id
2874: FOR UPDATE NOWAIT;
2875:
2876: CURSOR del_dropoff_exists(p_del_id IN NUMBER) IS
2874: FOR UPDATE NOWAIT;
2875:
2876: CURSOR del_dropoff_exists(p_del_id IN NUMBER) IS
2877: SELECT delivery_leg_id
2878: FROM wsh_delivery_legs
2879: WHERE drop_off_stop_id = p_dropoff_stop_id
2880: FOR UPDATE NOWAIT;
2881:
2882: CURSOR del_trip_exists( p_del_id IN NUMBER) IS
2882: CURSOR del_trip_exists( p_del_id IN NUMBER) IS
2883: SELECT dg.delivery_leg_id, st1.stop_location_id
2884: FROM wsh_trip_stops st1,
2885: wsh_trip_stops st2,
2886: wsh_delivery_legs dg
2887: WHERE st1.stop_id = dg.pick_up_stop_id AND
2888: st2.stop_id = dg.drop_off_stop_id AND
2889: st1.trip_id = p_trip_id AND
2890: st2.trip_id = p_trip_id AND
2923:
2924:
2925: CURSOR c_trip_empty IS
2926: SELECT count(*)
2927: FROM wsh_trip_stops a,wsh_delivery_legs b
2928: WHERE a.stop_id = b.pick_up_stop_id
2929: AND a.trip_id = p_trip_id
2930: AND rownum = 1 ;
2931:
3012: BEGIN
3013:
3014: SELECT 'Y'
3015: INTO l_del_for_update_load_seq
3016: FROM wsh_delivery_legs wdl,
3017: wsh_trip_stops wts,
3018: wsh_trips wt
3019: WHERE wdl.delivery_id = p_del_rows(i)
3020: AND wdl.drop_off_stop_id = wts.stop_id
3175: --
3176: -- Debug Statements
3177: --
3178: IF l_debug_on THEN
3179: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_PVT.DELETE_DELIVERY_LEG',WSH_DEBUG_SV.C_PROC_LEVEL);
3180: END IF;
3181: --
3182: wsh_delivery_legs_pvt.delete_delivery_leg(NULL,
3183: l_del_leg_id, l_return_status);
3178: IF l_debug_on THEN
3179: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_PVT.DELETE_DELIVERY_LEG',WSH_DEBUG_SV.C_PROC_LEVEL);
3180: END IF;
3181: --
3182: wsh_delivery_legs_pvt.delete_delivery_leg(NULL,
3183: l_del_leg_id, l_return_status);
3184:
3185: END IF;
3186:
3519: WHEN others THEN
3520: iF c_is_first_leg%isopen THEN
3521: close c_is_first_leg;
3522: END IF;
3523: wsh_util_core.default_handler('WSH_DELIVERY_LEGS_ACTIONS.UNASSIGN_DELIVERIES');
3524: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3525: IF l_debug_on THEN
3526: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3527: END IF;
3588:
3589: cursor get_trip_from_det( c_delivery_detail_id NUMBER) is
3590: SELECT dlg.delivery_leg_id, trip.trip_id, trip.consolidation_allowed,
3591: trip.lane_id, trip.mode_of_transport
3592: FROM wsh_delivery_legs dlg,
3593: wsh_delivery_assignments_v da,
3594: wsh_trips trip,
3595: wsh_trip_stops st
3596: WHERE da.delivery_detail_id = c_delivery_detail_id AND
3600:
3601: cursor get_trip_from_del( c_delivery_id NUMBER) is
3602: SELECT dlg.delivery_leg_id, trip.trip_id, trip.consolidation_allowed,
3603: trip.lane_id, trip.mode_of_transport
3604: FROM wsh_delivery_legs dlg,
3605: wsh_trip_stops stop,
3606: wsh_trips trip
3607: WHERE dlg.delivery_id = c_delivery_id and
3608: dlg.pick_up_stop_id = stop.stop_id and
3610:
3611: cursor get_trip_from_leg( c_leg_id NUMBER) is
3612: SELECT dlg.delivery_leg_id, trip.trip_id, trip.consolidation_allowed,
3613: trip.lane_id, trip.mode_of_transport
3614: FROM wsh_delivery_legs dlg,
3615: wsh_trip_stops stop,
3616: wsh_trips trip
3617: WHERE dlg.delivery_leg_id = c_leg_id AND
3618: dlg.pick_up_stop_id = stop.stop_id AND
3623: SELECT dlg.delivery_leg_id, trip.trip_id, trip.consolidation_allowed,
3624: trip.lane_id, trip.mode_of_transport
3625: FROM wsh_trip_stops stop,
3626: wsh_trips trip,
3627: wsh_delivery_legs dlg,
3628: wsh_trip_stops ts
3629: WHERE stop.stop_id = c_stop_id AND
3630: stop.trip_id = trip.trip_id AND
3631: ts.trip_id=trip.trip_id AND
3634: cursor get_legs_from_trip( c_trip_id NUMBER) is
3635: SELECT dlg.delivery_leg_id, trip.trip_id, trip.consolidation_allowed,
3636: trip.lane_id, trip.mode_of_transport
3637: FROM wsh_trip_stops ts,
3638: wsh_delivery_legs dlg,
3639: wsh_trips trip
3640: WHERE ts.trip_id = c_trip_id AND
3641: dlg.pick_up_stop_id = ts.stop_id AND
3642: ts.trip_id = trip.trip_id;
3644: cursor legs_priced_cur( c_trip_id NUMBER) is
3645: select 1
3646: from wsh_freight_costs wfc,
3647: wsh_trip_stops wts,
3648: wsh_delivery_legs wdl
3649: where wts.trip_id = c_trip_id
3650: and wdl.pick_up_stop_id = wts.stop_id
3651: and wfc.delivery_leg_id = wdl.delivery_leg_id
3652: and wfc.line_type_code = 'SUMMARY'
3663: and NVL(wfc.total_amount,0) > 0;
3664:
3665: cursor c_lock_delivery_leg(c_delivery_leg_id NUMBER) is
3666: SELECT delivery_leg_id, status_code, reprice_required, parent_delivery_leg_id
3667: FROM wsh_delivery_legs
3668: WHERE delivery_leg_id = c_delivery_leg_id FOR UPDATE OF reprice_required NOWAIT;
3669:
3670: -- Bug 4451383
3671: cursor get_trip_details ( c_trip_id NUMBER) is
3935: OPEN c_lock_delivery_leg(l_final_leg_tab(i));
3936: FETCH c_lock_delivery_leg INTO l_lock_delivery_leg_rec;
3937: IF c_lock_delivery_leg%FOUND THEN
3938: IF l_lock_delivery_leg_rec.reprice_required <> 'Y' THEN
3939: UPDATE wsh_delivery_legs
3940: SET reprice_required = 'Y'
3941: WHERE CURRENT OF c_lock_delivery_leg;
3942: IF l_lock_delivery_leg_rec.parent_delivery_leg_id IS NOT NULL THEN
3943: BEGIN
3940: SET reprice_required = 'Y'
3941: WHERE CURRENT OF c_lock_delivery_leg;
3942: IF l_lock_delivery_leg_rec.parent_delivery_leg_id IS NOT NULL THEN
3943: BEGIN
3944: WSH_DELIVERY_LEGS_PVT.lock_dlvy_leg_no_compare(p_dlvy_leg_id => l_lock_delivery_leg_rec.parent_delivery_leg_id);
3945: EXCEPTION
3946: WHEN OTHERS THEN
3947: IF c_lock_delivery_leg%ISOPEN THEN
3948: CLOSE c_lock_delivery_leg;
3955: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:DELIVERY_LEG_LOCKED');
3956: END IF;
3957: RETURN;
3958: END;
3959: UPDATE wsh_delivery_legs
3960: SET reprice_required = 'Y'
3961: WHERE delivery_leg_id = l_lock_delivery_leg_rec.parent_delivery_leg_id
3962: AND NVL(reprice_required, 'N') <> 'Y';
3963: END IF;
4005: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:DELIVERY_LEG_LOCKED');
4006: END IF;
4007: --
4008: WHEN others THEN
4009: wsh_util_core.default_handler('WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required');
4010: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4011:
4012: --
4013: -- Debug Statements
4186: p_freight_terms_code_i IN VARCHAR2, p_freight_terms_code_d VARCHAR2) IS
4187: select wnd.delivery_id
4188: FROM wsh_new_deliveries wnd,
4189: wsh_trip_stops wts,
4190: wsh_delivery_legs wdl
4191: WHERE wts.stop_id = wdl.pick_up_stop_id AND
4192: wts.trip_id = p_trip_id AND
4193: wdl.delivery_id = wnd.delivery_id AND
4194: wnd.freight_terms_code <> p_freight_terms_code_o and
4197: UNION
4198: select wnd.delivery_id
4199: FROM wsh_new_deliveries wnd,
4200: wsh_trip_stops wts,
4201: wsh_delivery_legs wdl
4202: WHERE wts.stop_id = wdl.pick_up_stop_id AND
4203: wts.trip_id = p_trip_id AND
4204: wdl.delivery_id = wnd.delivery_id AND
4205: wnd.freight_terms_code <> p_freight_terms_code_i and
4208: UNION
4209: select wnd.delivery_id
4210: FROM wsh_new_deliveries wnd,
4211: wsh_trip_stops wts,
4212: wsh_delivery_legs wdl
4213: WHERE wts.stop_id = wdl.pick_up_stop_id AND
4214: wts.trip_id = p_trip_id AND
4215: wdl.delivery_id = wnd.delivery_id AND
4216: wnd.freight_terms_code <> p_freight_terms_code_d and
4221: p_freight_terms_code_i IN VARCHAR2, p_freight_terms_code_d VARCHAR2) IS
4222: select wnd.delivery_id
4223: FROM wsh_new_deliveries wnd,
4224: wsh_trip_stops wts,
4225: wsh_delivery_legs wdl
4226: WHERE wts.stop_id = wdl.pick_up_stop_id AND
4227: wts.trip_id = p_trip_id AND
4228: wdl.delivery_id = wnd.delivery_id AND
4229: wnd.freight_terms_code = p_freight_terms_code_o and
4232: UNION
4233: select wnd.delivery_id
4234: FROM wsh_new_deliveries wnd,
4235: wsh_trip_stops wts,
4236: wsh_delivery_legs wdl
4237: WHERE wts.stop_id = wdl.pick_up_stop_id AND
4238: wts.trip_id = p_trip_id AND
4239: wdl.delivery_id = wnd.delivery_id AND
4240: wnd.freight_terms_code <> p_freight_terms_code_i and
4243: UNION
4244: select wnd.delivery_id
4245: FROM wsh_new_deliveries wnd,
4246: wsh_trip_stops wts,
4247: wsh_delivery_legs wdl
4248: WHERE wts.stop_id = wdl.pick_up_stop_id AND
4249: wts.trip_id = p_trip_id AND
4250: wdl.delivery_id = wnd.delivery_id AND
4251: wnd.freight_terms_code <> p_freight_terms_code_d and
4374: END Check_Rate_Trip_Contents;
4375:
4376:
4377:
4378: END WSH_DELIVERY_LEGS_ACTIONS;