[Home] [Help]
1: PACKAGE BODY WSH_TRIPS_ACTIONS as
2: /* $Header: WSHTRACB.pls 120.21.12000000.7 2007/03/30 09:34:27 nchellam ship $ */
3:
4: --
5: G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_TRIPS_ACTIONS';
1: PACKAGE BODY WSH_TRIPS_ACTIONS as
2: /* $Header: WSHTRACB.pls 120.21.12000000.7 2007/03/30 09:34:27 nchellam ship $ */
3:
4: --
5: G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_TRIPS_ACTIONS';
6: --
7:
8: g_int_mask VARCHAR2(12) := 'S00000000000';
9: type numtabvc2 is table of number index by varchar2(2000);
86: END get_stop_seq_mode;
87:
88: PROCEDURE Get_Trip_Defaults(p_trip_id in NUMBER,
89: p_trip_name in VARCHAR2 DEFAULT NULL,
90: x_def_rec IN OUT NOCOPY WSH_TRIPS_GRP.default_parameters_rectype,
91: x_return_Status OUT NOCOPY varchar2 ) IS
92:
93: cursor get_pickup_stop (c_trip_id number) is
94: select distinct st.stop_id, st.stop_location_id
255: END IF;
256:
257: WHEN OTHERS THEN
258:
259: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.Get_Trip_Defaults');
260:
261: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
262: IF l_debug_on THEN
263: wsh_debug_sv.log (l_module_name,'Others',substr(sqlerrm,1,200));
312: SELECT 1
313: FROM wsh_new_deliveries dl,
314: wsh_delivery_legs dg,
315: wsh_trip_stops st,
316: wsh_trips t
317: WHERE dl.delivery_id = dg.delivery_id AND
318: (dg.pick_up_stop_id = st.stop_id OR dg.drop_off_stop_id = st.stop_id) AND
319: st.trip_id = t.trip_id AND
320: dl.STATUS_CODE <> 'CO' and
336: x_msg_count NUMBER;
337:
338: CURSOR c_trip_status IS
339: SELECT status_code
340: FROM wsh_trips
341: WHERE trip_id = p_trip_id ;
342:
343: l_trip_status VARCHAR2(2);
344:
673:
674: WHEN others THEN
675: rollback to sp_confirm_trip;
676: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
677: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.CONFIRM_TRIP');
678: --
679: IF l_debug_on THEN
680: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
681: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
710:
711: cursor get_status(c_trip_id IN NUMBER) is
712: select status_code,
713: NVL(shipments_type_flag,'O') -- J-IB-NPARIKH
714: from wsh_trips
715: where trip_id = c_trip_id;
716:
717: cursor get_stops(c_trip_id IN NUMBER) is
718: select 'Y'
722: and rownum = 1;
723:
724: CURSOR c_istripfirm(p_tripid IN NUMBER) IS
725: select 'Y'
726: from wsh_trips
727: where trip_id=p_tripid AND
728: planned_flag='F';
729:
730: l_tripfirm VARCHAR2(1);
840: --
841: -- Debug Statements
842: --
843: IF l_debug_on THEN
844: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
845: END IF;
846: --
847: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
848: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
843: IF l_debug_on THEN
844: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
845: END IF;
846: --
847: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
848: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
849: wsh_util_core.add_message(x_return_status);
850: l_num_error := l_num_error + 1;
851:
893: END IF;
894: --
895: EXCEPTION
896: WHEN others THEN
897: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.PLAN');
898: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
899:
900: --
901: -- Debug Statements
915: i BINARY_INTEGER;
916: /* H integration for Multi Leg */
917: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
918: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
919: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
920: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
921: l_return_status VARCHAR2(30);
922: l_num_warn NUMBER := 0;
923:
916: /* H integration for Multi Leg */
917: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
918: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
919: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
920: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
921: l_return_status VARCHAR2(30);
922: l_num_warn NUMBER := 0;
923:
924: -- Exceptions Project
1028: --
1029: -- Debug Statements
1030: --
1031: IF l_debug_on THEN
1032: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_GRP.GET_TRIP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
1033: END IF;
1034: --
1035: wsh_trips_grp.get_trip_details_pvt
1036: (p_trip_id => p_trip_id,
1031: IF l_debug_on THEN
1032: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_GRP.GET_TRIP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
1033: END IF;
1034: --
1035: wsh_trips_grp.get_trip_details_pvt
1036: (p_trip_id => p_trip_id,
1037: x_trip_rec => l_trip_rec,
1038: x_return_status => l_return_status);
1039:
1083: /* End of H integration */
1084:
1085: --Bug Fix 2993711 added last_update_date,last_updated_by,last_update_login --
1086:
1087: UPDATE wsh_trips
1088: SET status_code = p_status_code,
1089: last_update_date = SYSDATE,
1090: last_updated_by = fnd_global.user_id,
1091: last_update_login = fnd_global.login_id
1150: END IF;
1151: --
1152:
1153: WHEN others THEN
1154: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.CHANGE_STATUS');
1155: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1156: --
1157: IF l_debug_on THEN
1158: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1201:
1202:
1203: /* H integration for Multi Leg */
1204: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
1205: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
1206: --
1207: --
1208: l_num_warnings NUMBER;
1209: l_num_errors NUMBER;
1274: --
1275: -- Debug Statements
1276: --
1277: IF l_debug_on THEN
1278: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_GRP.GET_TRIP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
1279: END IF;
1280: --
1281: wsh_trips_grp.get_trip_details_pvt
1282: (p_trip_id => p_in_rec.trip_id,
1277: IF l_debug_on THEN
1278: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_GRP.GET_TRIP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
1279: END IF;
1280: --
1281: wsh_trips_grp.get_trip_details_pvt
1282: (p_trip_id => p_in_rec.trip_id,
1283: x_trip_rec => l_trip_rec,
1284: x_return_status => l_return_status);
1285: --
1317: /* End of H integration */
1318:
1319: --Bug Fix 2993711 added last_update_date,last_updated_by,last_update_login --
1320:
1321: UPDATE wsh_trips
1322: SET status_code = p_in_rec.new_status_code,
1323: last_update_date = SYSDATE,
1324: last_updated_by = fnd_global.user_id,
1325: last_update_login = fnd_global.login_id
1519: --
1520: --========================================================================
1521: PROCEDURE generateRoutingResponse
1522: (
1523: p_action_prms IN WSH_TRIPS_GRP.action_parameters_rectype,
1524: p_rec_attr_tab IN WSH_TRIPS_PVT.Trip_Attr_Tbl_Type,
1525: x_return_status OUT NOCOPY VARCHAR2
1526: )
1527: IS
1520: --========================================================================
1521: PROCEDURE generateRoutingResponse
1522: (
1523: p_action_prms IN WSH_TRIPS_GRP.action_parameters_rectype,
1524: p_rec_attr_tab IN WSH_TRIPS_PVT.Trip_Attr_Tbl_Type,
1525: x_return_status OUT NOCOPY VARCHAR2
1526: )
1527: IS
1528: --{
1541: SELECT wdl.delivery_id, wt.name
1542: FROM wsh_trip_stops wts,
1543: wsh_Delivery_legs wdl,
1544: wsh_new_deliveries wnd,
1545: wsh_trips wt
1546: WHERE wt.trip_id = p_trip_id
1547: AND wts.trip_id = p_trip_id
1548: AND wdl.pick_up_stop_id = wts.stop_id
1549: AND wnd.delivery_id = wdl.delivery_id
1661: FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_TRIP_NAME');
1662: ELSE
1663: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NO_PICKUP_ERROR');
1664: --FND_MESSAGE.SET_TOKEN('TRIP_NAME',NVL(l_trip_name,p_rec_attr_tab(l_index).trip_id));
1665: FND_MESSAGE.SET_TOKEN('TRIP_NAME',NVL(l_trip_name, wsh_trips_pvt.get_name( p_rec_attr_tab(l_index).trip_id)));
1666: END IF;
1667: --
1668: wsh_util_core.add_message(wsh_util_core.g_ret_sts_warning,l_module_name);
1669: l_num_warnings := NVL(l_num_warnings,0) + 1;
1810: END IF;
1811: --
1812: WHEN OTHERS THEN
1813: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1814: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.generateRoutingResponse');
1815: --
1816: -- Debug Statements
1817: --
1818: IF l_debug_on THEN
1974: EXCEPTION
1975: --
1976: WHEN others THEN
1977: --
1978: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.GET_NEXT_DEL_ROWS');
1979: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1980: --
1981: IF l_debug_on THEN
1982: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2092: END IF;
2093: --
2094: WHEN others THEN
2095: --
2096: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.SORT_DEL');
2097: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2098: --
2099: IF l_debug_on THEN
2100: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2145: l_del_tbl_not_sorted Del_Rec_Type;
2146: l_return_status varchar2(1);
2147: l_del_rows wsh_util_core.id_tab_type;
2148: l_trip_id NUMBER;
2149: l_trip_name wsh_trips.name%TYPE;
2150: l_initial_pickup_location_id NUMBER;
2151: l_ultimate_dropoff_location_id NUMBER;
2152: l_num_warnings NUMBER := 0;
2153: l_num_errors NUMBER := 0;
2260: EXCEPTION
2261: --
2262: WHEN others THEN
2263: --
2264: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.AUTOCREATE_TRIP_WRP');
2265: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2266: --
2267: IF l_debug_on THEN
2268: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2299: l_ignore_ids wsh_util_core.id_tab_type;
2300: l_include_ids wsh_util_core.id_tab_type;
2301: l_return_status VARCHAR2(1);
2302: l_trip_id NUMBER;
2303: l_trip_name wsh_trips.name%TYPE;
2304: l_index NUMBER;
2305: l_trip_ids wsh_util_core.id_tab_type;
2306: l_trip_names wsh_util_core.Column_Tab_Type;
2307: l_gc3_is_installed VARCHAR2(1); -- OTM R12, glog proj
2304: l_index NUMBER;
2305: l_trip_ids wsh_util_core.id_tab_type;
2306: l_trip_names wsh_util_core.Column_Tab_Type;
2307: l_gc3_is_installed VARCHAR2(1); -- OTM R12, glog proj
2308: l_ignore_flag WSH_TRIPS.IGNORE_FOR_PLANNING%TYPE; -- OTM R12, glog proj
2309: l_count_error NUMBER;
2310:
2311: --
2312: l_debug_on BOOLEAN;
2472: --
2473:
2474: EXCEPTION
2475: WHEN others THEN
2476: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.AUTOCREATE_TRIP_MULTI');
2477: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2478:
2479: --
2480: -- Debug Statements
2530:
2531: l_delivery_id NUMBER;
2532: l_trip_id NUMBER;
2533:
2534: l_ship_method wsh_trips.ship_method_code%TYPE;
2535: l_ship_method_old wsh_trips.ship_method_code%TYPE;
2536: l_carrier_id wsh_trips.carrier_id%TYPE;
2537: l_mode wsh_trips.mode_of_transport%TYPE;
2538: l_service_level wsh_trips.service_level%TYPE;
2531: l_delivery_id NUMBER;
2532: l_trip_id NUMBER;
2533:
2534: l_ship_method wsh_trips.ship_method_code%TYPE;
2535: l_ship_method_old wsh_trips.ship_method_code%TYPE;
2536: l_carrier_id wsh_trips.carrier_id%TYPE;
2537: l_mode wsh_trips.mode_of_transport%TYPE;
2538: l_service_level wsh_trips.service_level%TYPE;
2539: l_carrier_id_old wsh_trips.carrier_id%TYPE;
2532: l_trip_id NUMBER;
2533:
2534: l_ship_method wsh_trips.ship_method_code%TYPE;
2535: l_ship_method_old wsh_trips.ship_method_code%TYPE;
2536: l_carrier_id wsh_trips.carrier_id%TYPE;
2537: l_mode wsh_trips.mode_of_transport%TYPE;
2538: l_service_level wsh_trips.service_level%TYPE;
2539: l_carrier_id_old wsh_trips.carrier_id%TYPE;
2540: l_mode_old wsh_trips.mode_of_transport%TYPE;
2533:
2534: l_ship_method wsh_trips.ship_method_code%TYPE;
2535: l_ship_method_old wsh_trips.ship_method_code%TYPE;
2536: l_carrier_id wsh_trips.carrier_id%TYPE;
2537: l_mode wsh_trips.mode_of_transport%TYPE;
2538: l_service_level wsh_trips.service_level%TYPE;
2539: l_carrier_id_old wsh_trips.carrier_id%TYPE;
2540: l_mode_old wsh_trips.mode_of_transport%TYPE;
2541: l_service_level_old wsh_trips.service_level%TYPE;
2534: l_ship_method wsh_trips.ship_method_code%TYPE;
2535: l_ship_method_old wsh_trips.ship_method_code%TYPE;
2536: l_carrier_id wsh_trips.carrier_id%TYPE;
2537: l_mode wsh_trips.mode_of_transport%TYPE;
2538: l_service_level wsh_trips.service_level%TYPE;
2539: l_carrier_id_old wsh_trips.carrier_id%TYPE;
2540: l_mode_old wsh_trips.mode_of_transport%TYPE;
2541: l_service_level_old wsh_trips.service_level%TYPE;
2542: l_ignore_for_planning wsh_trips.ignore_for_planning%TYPE;
2535: l_ship_method_old wsh_trips.ship_method_code%TYPE;
2536: l_carrier_id wsh_trips.carrier_id%TYPE;
2537: l_mode wsh_trips.mode_of_transport%TYPE;
2538: l_service_level wsh_trips.service_level%TYPE;
2539: l_carrier_id_old wsh_trips.carrier_id%TYPE;
2540: l_mode_old wsh_trips.mode_of_transport%TYPE;
2541: l_service_level_old wsh_trips.service_level%TYPE;
2542: l_ignore_for_planning wsh_trips.ignore_for_planning%TYPE;
2543:
2536: l_carrier_id wsh_trips.carrier_id%TYPE;
2537: l_mode wsh_trips.mode_of_transport%TYPE;
2538: l_service_level wsh_trips.service_level%TYPE;
2539: l_carrier_id_old wsh_trips.carrier_id%TYPE;
2540: l_mode_old wsh_trips.mode_of_transport%TYPE;
2541: l_service_level_old wsh_trips.service_level%TYPE;
2542: l_ignore_for_planning wsh_trips.ignore_for_planning%TYPE;
2543:
2544: l_status_code VARCHAR2(30);
2537: l_mode wsh_trips.mode_of_transport%TYPE;
2538: l_service_level wsh_trips.service_level%TYPE;
2539: l_carrier_id_old wsh_trips.carrier_id%TYPE;
2540: l_mode_old wsh_trips.mode_of_transport%TYPE;
2541: l_service_level_old wsh_trips.service_level%TYPE;
2542: l_ignore_for_planning wsh_trips.ignore_for_planning%TYPE;
2543:
2544: l_status_code VARCHAR2(30);
2545:
2538: l_service_level wsh_trips.service_level%TYPE;
2539: l_carrier_id_old wsh_trips.carrier_id%TYPE;
2540: l_mode_old wsh_trips.mode_of_transport%TYPE;
2541: l_service_level_old wsh_trips.service_level%TYPE;
2542: l_ignore_for_planning wsh_trips.ignore_for_planning%TYPE;
2543:
2544: l_status_code VARCHAR2(30);
2545:
2546: l_ship_method_flag BOOLEAN := TRUE;
2545:
2546: l_ship_method_flag BOOLEAN := TRUE;
2547:
2548: l_rowid VARCHAR2(30);
2549: l_trip_info WSH_TRIPS_PVT.TRIP_REC_TYPE;
2550: assigned_del_error EXCEPTION;
2551: l_return_status VARCHAR2(1);
2552: l_del_legs WSH_UTIL_CORE.ID_TAB_TYPE;
2553: l_good_dels WSH_UTIL_CORE.ID_TAB_TYPE;
2555: /* H integration for Multi Leg */
2556: l_num_warn NUMBER := 0;
2557: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
2558: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
2559: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
2560: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
2561: l_grp_trip_rec WSH_TRIPS_GRP.TRIP_PUB_REC_TYPE;
2562: --l_return_status VARCHAR2(30);
2563: l_fte_flag VARCHAR2(1):= 'N';
2556: l_num_warn NUMBER := 0;
2557: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
2558: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
2559: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
2560: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
2561: l_grp_trip_rec WSH_TRIPS_GRP.TRIP_PUB_REC_TYPE;
2562: --l_return_status VARCHAR2(30);
2563: l_fte_flag VARCHAR2(1):= 'N';
2564: --
2557: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
2558: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
2559: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
2560: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
2561: l_grp_trip_rec WSH_TRIPS_GRP.TRIP_PUB_REC_TYPE;
2562: --l_return_status VARCHAR2(30);
2563: l_fte_flag VARCHAR2(1):= 'N';
2564: --
2565: l_debug_on BOOLEAN;
2577: l_trip_ids WSH_UTIL_CORE.ID_TAB_TYPE;
2578: l_success_trip_ids WSH_UTIL_CORE.ID_TAB_TYPE;
2579:
2580: l_gc3_is_installed VARCHAR2(1); -- OTM R12, glog proj
2581: l_ignore_flag WSH_TRIPS.IGNORE_FOR_PLANNING%TYPE; -- OTM R12, glog proj
2582: l_count_error NUMBER;
2583:
2584: --
2585: BEGIN
2774: /* H integration */
2775: /* The trip level call should have been made at parent level */
2776: --
2777: IF l_debug_on THEN
2778: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.CREATE_TRIP',WSH_DEBUG_SV.C_PROC_LEVEL);
2779: END IF;
2780: --
2781: wsh_trips_pvt.create_trip(l_trip_info, l_rowid, x_trip_id, x_trip_name,x_return_status);
2782: --
2777: IF l_debug_on THEN
2778: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.CREATE_TRIP',WSH_DEBUG_SV.C_PROC_LEVEL);
2779: END IF;
2780: --
2781: wsh_trips_pvt.create_trip(l_trip_info, l_rowid, x_trip_id, x_trip_name,x_return_status);
2782: --
2783: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2784: --
2785: IF l_debug_on THEN
2996:
2997: -- SSN change
2998: -- Call to reset_stop_planned_dates API should be made
2999: -- only for profile = PAD
3000: IF WSH_TRIPS_ACTIONS.GET_STOP_SEQ_MODE = WSH_INTERFACE_GRP.G_STOP_SEQ_MODE_PAD THEN
3001: -- re-set the dates according to sequence number
3002: -- bug 3516052
3003: l_trip_id := x_trip_id;
3004:
3001: -- re-set the dates according to sequence number
3002: -- bug 3516052
3003: l_trip_id := x_trip_id;
3004:
3005: WSH_TRIPS_ACTIONS.reset_stop_planned_dates( p_trip_id => l_trip_id,
3006: p_caller => 'WSH_AUTOCREATE_TRIP',
3007: x_return_status => l_return_status);
3008: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3009: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3046: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FLEXFIELD_UTILS.WRITE_DFF_ATTRIBUTES',WSH_DEBUG_SV.C_PROC_LEVEL);
3047: END IF;
3048: -- for bug 5948562, to populate values in additional trip information flexfield, when auto creating a trip
3049: wsh_flexfield_utils.WRITE_DFF_ATTRIBUTES
3050: (p_table_name => 'WSH_TRIPS',
3051: p_primary_id => x_trip_id,
3052: x_return_status => x_return_status);
3053:
3054: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3083: EXCEPTION
3084: --
3085: WHEN others THEN
3086: --
3087: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.AUTOCREATE_TRIP');
3088: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3089: --
3090: IF l_debug_on THEN
3091: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3149: l_msg_data VARCHAR2(2000);
3150: --dummy tables for calling validate_constraint_mainper
3151: l_cc_del_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
3152: l_cc_det_attr_tab WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type;
3153: l_cc_trip_attr_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
3154: l_cc_stop_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
3155: l_cc_in_ids wsh_util_core.id_tab_type;
3156: l_cc_fail_ids wsh_util_core.id_tab_type;
3157:
3162: --and (SHIP_METHOD_CODE is not null OR INTMED_SHIP_TO_LOCATION_ID is not null);
3163:
3164: CURSOR trip_cur(p_trip_id NUMBER) IS
3165: SELECT SHIP_METHOD_CODE
3166: FROM wsh_trips
3167: WHERE trip_id = p_trip_id;
3168: --and SHIP_METHOD_CODE is not null;
3169:
3170: l_line_rows wsh_util_core.id_tab_type:=p_line_rows;
3647: IF l_trip_ids is not null AND l_trip_ids.COUNT>0 THEN--{
3648: FOR l_tripindex IN l_trip_ids.FIRST..l_trip_ids.LAST LOOP--{
3649: IF l_cc_upd_dlvy_ship_method='N' THEN--{
3650: FOR tripcurtemp in trip_cur(l_trip_ids(l_tripindex)) LOOP--{
3651: update wsh_trips
3652: set SHIP_METHOD_CODE=null,
3653: CARRIER_ID = null,
3654: MODE_OF_TRANSPORT = null,
3655: SERVICE_LEVEL = null
3724:
3725: IF l_cc_upd_dlvy_ship_method='N' and l_trip_id_tab is not null and l_trip_id_tab.COUNT>0 THEN--{
3726: FOR l_tripindex IN l_trip_id_tab.FIRST..l_trip_id_tab.LAST LOOP--{
3727: FOR tripcurtemp in trip_cur(l_trip_id_tab(l_tripindex)) LOOP--{
3728: update wsh_trips
3729: set SHIP_METHOD_CODE=null,
3730: CARRIER_ID = null,
3731: MODE_OF_TRANSPORT = null,
3732: SERVICE_LEVEL = null
3809: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:Adjust_Planned_Flag_Err');
3810: END IF;--}
3811:
3812: WHEN others THEN
3813: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.AUTOCREATE_DEL_TRIP');
3814: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3815:
3816:
3817: --
3938: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:REPRICE_REQUIRED_ERR');
3939: END IF;
3940: --
3941: WHEN others THEN
3942: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.ASSIGN_TRIP');
3943: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3944:
3945: --
3946: -- Debug Statements
4169: END IF;
4170: --
4171: EXCEPTION
4172: WHEN others THEN
4173: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.CHECK_ASSIGN_TRIP');
4174: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4175:
4176: --
4177: -- Debug Statements
4644: IF l_debug_on THEN
4645: WSH_DEBUG_SV.log(l_module_name,'Have to create new internal stop as del with physical dropoff has been passed',l_dropoff_location_id);
4646: END IF;
4647:
4648: IF WSH_TRIPS_ACTIONS.GET_STOP_SEQ_MODE = WSH_INTERFACE_GRP.G_STOP_SEQ_MODE_SSN THEN
4649: x_dropoff_seq_num := derive_next_ssn(p_trip_id, x_dropoff_stop_id);
4650: IF l_debug_on THEN
4651: WSH_DEBUG_SV.log(l_module_name,'x_dropoff_seq_num adjusted', x_dropoff_seq_num);
4652: END IF;
4673: IF l_debug_on THEN
4674: WSH_DEBUG_SV.log(l_module_name,'Have to create new internal stop as existing physical stop has activities',l_dropoff_location_id);
4675: END IF;
4676:
4677: IF WSH_TRIPS_ACTIONS.GET_STOP_SEQ_MODE = WSH_INTERFACE_GRP.G_STOP_SEQ_MODE_SSN THEN
4678: x_dropoff_seq_num := derive_next_ssn(p_trip_id, x_dropoff_stop_id);
4679: IF l_debug_on THEN
4680: WSH_DEBUG_SV.log(l_module_name,'x_dropoff_seq_num adjusted', x_dropoff_seq_num);
4681: END IF;
4738: --
4739: WHEN OTHERS THEN
4740: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4741: WSH_UTIL_CORE.DEFAULT_HANDLER(
4742: 'WSH_TRIPS_ACTIONS.process_dels_for_internal_locs',
4743: l_module_name);
4744:
4745: IF l_debug_on THEN
4746: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4790: others EXCEPTION;
4791: /* H integration for Multi Leg */
4792: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
4793: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
4794: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
4795: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
4796: l_num_warn NUMBER := 0;
4797: l_num_err NUMBER := 0;
4798: --l_return_status VARCHAR2(30);
4791: /* H integration for Multi Leg */
4792: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
4793: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
4794: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
4795: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
4796: l_num_warn NUMBER := 0;
4797: l_num_err NUMBER := 0;
4798: --l_return_status VARCHAR2(30);
4799:
4835: THEN
4836: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4837: END IF;
4838: --
4839: l_stop_seq_mode := WSH_TRIPS_ACTIONS.GET_STOP_SEQ_MODE;
4840:
4841: IF l_debug_on THEN
4842: WSH_DEBUG_SV.push(l_module_name);
4843: --
5151: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:STOP_NOT_FOUND');
5152: END IF;
5153: --
5154: WHEN others THEN
5155: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.ASSIGN_TRIP');
5156: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5157:
5158: --
5159: -- Debug Statements
5204:
5205: CURSOR trip_vehicle_info (c_trip_id NUMBER) IS
5206: SELECT vehicle_item_id,
5207: vehicle_organization_id
5208: FROM wsh_trips
5209: WHERE trip_id = c_trip_id;
5210:
5211: CURSOR trip_info (c_trip_id NUMBER) IS
5212: SELECT msi.maximum_load_weight,
5215: msi.weight_uom_code,
5216: msi.volume_uom_code,
5217: msi.organization_id
5218: FROM mtl_system_items msi,
5219: wsh_trips t
5220: WHERE msi.organization_id = t.vehicle_organization_id AND
5221: t.vehicle_item_id = msi.inventory_item_id AND
5222: t.trip_id = c_trip_id;
5223:
5223:
5224: CURSOR org_info (c_trip_id NUMBER) IS
5225: SELECT wsp.percent_fill_basis_flag
5226: FROM wsh_shipping_parameters wsp,
5227: wsh_trips t
5228: WHERE wsp.organization_id = t.vehicle_organization_id AND
5229: t.trip_id = c_trip_id;
5230:
5231: l_trip_id NUMBER;
5374: END IF;
5375: IF org_info%ISOPEN THEN
5376: CLOSE org_info;
5377: END IF;
5378: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.CALC_STOP_FILL_PERCENT');
5379: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5380:
5381: IF l_debug_on THEN
5382: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5472: msi.weight_uom_code,
5473: msi.volume_uom_code,
5474: msi.organization_id
5475: FROM mtl_system_items msi,
5476: wsh_trips t
5477: WHERE msi.organization_id = t.vehicle_organization_id AND
5478: t.vehicle_item_id = msi.inventory_item_id AND
5479: t.trip_id = l_trip_id;
5480:
5480:
5481: CURSOR org_info (l_trip_id NUMBER) IS
5482: SELECT wsp.percent_fill_basis_flag
5483: FROM wsh_shipping_parameters wsp,
5484: wsh_trips t
5485: WHERE wsp.organization_id = t.vehicle_organization_id AND
5486: t.trip_id = l_trip_id;
5487:
5488: CURSOR trip_vehicle_info (l_trip_id NUMBER) IS
5487:
5488: CURSOR trip_vehicle_info (l_trip_id NUMBER) IS
5489: SELECT vehicle_item_id,
5490: vehicle_organization_id
5491: FROM wsh_trips
5492: WHERE trip_id = l_trip_id;
5493:
5494:
5495: l_net_weight NUMBER;
5543: l_locked_stop_id NUMBER := 0;
5544: others EXCEPTION;
5545: /* H integration for Multi Leg */
5546: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
5547: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
5548:
5549: --R12 MDC
5550: l_total_pick_up_weight NUMBER := 0;
5551: l_total_drop_off_weight NUMBER := 0;
5655: --
5656: -- Debug Statements
5657: --
5658: IF l_debug_on THEN
5659: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5660: END IF;
5661: --
5662: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
5663: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5658: IF l_debug_on THEN
5659: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5660: END IF;
5661: --
5662: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
5663: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5664: wsh_util_core.add_message(x_return_status);
5665: goto wt_vol_error;
5666: END IF;
5679: --
5680: -- Debug Statements
5681: --
5682: IF l_debug_on THEN
5683: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5684: END IF;
5685: --
5686: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
5687: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5682: IF l_debug_on THEN
5683: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5684: END IF;
5685: --
5686: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
5687: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5688: wsh_util_core.add_message(x_return_status);
5689: goto wt_vol_error;
5690: END IF;
6229: --
6230: -- Debug Statements
6231: --
6232: IF l_debug_on THEN
6233: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6234: END IF;
6235: --
6236: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
6237: wsh_util_core.add_message(x_return_status);
6232: IF l_debug_on THEN
6233: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6234: END IF;
6235: --
6236: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
6237: wsh_util_core.add_message(x_return_status);
6238: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
6239: l_num_error := l_num_error + 1;
6240: ELSIF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
6263: IF (l_num_error > 0) OR (l_num_warn > 0) THEN
6264: -- Bug 3688384 Display separate message for one trip .
6265: IF (p_trip_rows.count = 1) and (l_num_error = 0) and (l_num_warn > 0) THEN
6266: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_WT_VOL_WARN');
6267: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(1)));
6268: ELSIF (p_trip_rows.count = 1) and (l_num_error > 0) THEN
6269: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_WT_VOL_ERR');
6270: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(1)));
6271: ELSIF (p_trip_rows.count > 1) then
6266: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_WT_VOL_WARN');
6267: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(1)));
6268: ELSIF (p_trip_rows.count = 1) and (l_num_error > 0) THEN
6269: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_WT_VOL_ERR');
6270: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(1)));
6271: ELSIF (p_trip_rows.count > 1) then
6272: -- Bug 3688384 .
6273: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_WT_VOL_SUMMARY');
6274: FND_MESSAGE.SET_TOKEN('NUM_WARN',l_num_warn);
6303: WHEN others THEN
6304: IF lock_stop%ISOPEN THEN
6305: CLOSE lock_stop;
6306: END IF;
6307: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.TRIP_WEIGHT_VOLUME');
6308: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6309:
6310: --
6311: -- Debug Statements
6403: --
6404: -- Debug Statements
6405: --
6406: IF l_debug_on THEN
6407: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6408: END IF;
6409: --
6410: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_id));
6411: wsh_util_core.add_message(x_return_status);
6406: IF l_debug_on THEN
6407: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6408: END IF;
6409: --
6410: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_id));
6411: wsh_util_core.add_message(x_return_status);
6412: --
6413: -- Debug Statements
6414: --
6417: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INVALID_SEQUENCE');
6418: END IF;
6419: --
6420: WHEN others THEN
6421: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.VALIDATE_STOP_SEQUENCE');
6422: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6423:
6424: --
6425: -- Debug Statements
6484:
6485: -- bug 2374603: we cannot unassign from PLANNED trips
6486: --J can assign from planned trip
6487: stmt_str := 'select st.trip_id, count(*) '||
6488: 'from wsh_trip_stops st, wsh_delivery_legs dg, wsh_trips tr '||
6489: 'where dg.pick_up_stop_id = st.stop_id AND '||
6490: 'tr.trip_id = st.trip_id AND tr.planned_flag IN (''N'',''Y'') AND ' ||
6491: 'dg.delivery_id IN (' || del_str || ') ' ||
6492: 'group by st.trip_id '||
6516: END IF;
6517: --
6518: EXCEPTION
6519: WHEN others THEN
6520: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.CHECK_UNASSIGN_TRIP');
6521: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6522:
6523: --
6524: -- Debug Statements
6537: x_return_status OUT NOCOPY VARCHAR2) IS
6538:
6539: cursor get_trip_info(p_trip_id in number) is
6540: SELECT name, planned_flag
6541: FROM wsh_trips
6542: WHERE trip_id = p_trip_id;
6543:
6544:
6545: cursor get_consol_child_deliveries(p_trip_id in number, p_delivery_id in number) is
6552: l_trip_tab WSH_UTIL_CORE.id_tab_type;
6553: l_mdc_del_tab WSH_UTIL_CORE.id_tab_type;
6554: l_return_status VARCHAR2(10);
6555: l_trip_name VARCHAR2(30);
6556: l_planned_flag wsh_trips.planned_flag%TYPE;
6557: j NUMBER := 0;
6558: unassign_deliveries_err EXCEPTION;
6559: reprice_required_err EXCEPTION;
6560: trip_not_found EXCEPTION;
6752: WHEN others THEN
6753: IF get_trip_info%ISOPEN THEN
6754: CLOSE get_trip_info;
6755: END IF;
6756: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.UNASSIGN_TRIP');
6757: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6758:
6759: --
6760: -- Debug Statements
6796: p_fill_percent IN NUMBER,
6797: x_return_status OUT NOCOPY VARCHAR2) IS
6798:
6799: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
6800: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
6801: l_return_status VARCHAR2(1);
6802:
6803: l_debug_on BOOLEAN;
6804: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'FTE_LOAD_TENDER';
6863: END IF;
6864:
6865: EXCEPTION
6866: WHEN others THEN
6867: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.Fte_Load_Tender');
6868: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6869:
6870: IF l_debug_on THEN
6871: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6916: wst.planned_departure_date,
6917: wst.actual_arrival_date,
6918: wst.actual_departure_date
6919: FROM wsh_trip_stops wst,
6920: wsh_trips wtp
6921: WHERE wtp.status_code in ('OP', 'IT')
6922: AND wtp.trip_id = c_trip_id
6923: AND wtp.trip_id = wst.trip_id
6924: ORDER BY wst.stop_sequence_number,wst.stop_id;
7016: NVL(l_last_stop_rec.actual_departure_date, l_last_stop_rec.actual_arrival_date));
7017: END IF;
7018:
7019: IF l_stop_rec.planned_arrival_date <= l_base_date THEN
7020: l_stop_rec.planned_arrival_date := l_base_date + WSH_TRIPS_ACTIONS.C_TEN_MINUTES;
7021: l_update_flag := TRUE;
7022: END IF;
7023:
7024: IF l_update_flag THEN
7145: END IF;
7146: IF c_trip_stops%ISOPEN THEN
7147: close c_trip_stops;
7148: END IF;
7149: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.reset_stop_planned_dates');
7150: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7151:
7152: IF l_debug_on THEN
7153: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
7209: END IF;
7210: return;
7211: END IF;
7212: l_trip_ids(1) := p_trip_id;
7213: WSH_TRIPS_ACTIONS.reset_stop_planned_dates
7214: ( p_trip_ids => l_trip_ids,
7215: p_caller => p_caller,
7216: x_success_trip_ids => l_success_trip_ids,
7217: x_return_status => l_return_status);
7228:
7229: EXCEPTION
7230:
7231: WHEN others THEN
7232: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.reset_stop_planned_dates');
7233: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7234:
7235: IF l_debug_on THEN
7236: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
7596: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
7597: l_warn_num := 0;
7598: x_success_trip_ids.delete;
7599:
7600: l_stop_seq_mode := WSH_TRIPS_ACTIONS.GET_STOP_SEQ_MODE;
7601:
7602: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
7603: IF l_debug_on IS NULL THEN
7604: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
7716: IF l_debug_on THEN
7717: WSH_DEBUG_SV.log(l_module_name,'link to next stop at stop', l_getstops_stop_id(j));
7718: END IF;
7719: l_getstops_phys_stop_id(j) := l_getstops_stop_id(j+1);
7720: l_getstops_pl_arr_date(j) := l_getstops_pl_arr_date(j+1) - WSH_TRIPS_ACTIONS.C_TEN_SECONDS;
7721: l_getstops_pl_dep_date(j) := l_getstops_pl_arr_date(j);
7722: IF l_stop_seq_mode = WSH_INTERFACE_GRP.G_STOP_SEQ_MODE_SSN THEN
7723: l_getstops_stop_seq_num(j) := l_getstops_stop_seq_num(j+1) - 1;
7724: END IF;
7757: l_getstops_stop_loc_id(j-1) := l_getstops_tmp.stop_location_id;
7758: l_getstops_phys_loc_id(j-1) := l_getstops_tmp.physical_location_id;
7759: l_getstops_status_code(j-1) := l_getstops_tmp.status_code;
7760: l_getstops_phys_stop_id(j-1) := l_getstops_stop_id(j); -- link
7761: l_getstops_pl_arr_date(j-1) := l_getstops_pl_arr_date(j) - WSH_TRIPS_ACTIONS.C_TEN_SECONDS;
7762: l_getstops_pl_dep_date(j-1) := l_getstops_pl_arr_date(j-1);
7763:
7764: IF l_stop_seq_mode = WSH_INTERFACE_GRP.G_STOP_SEQ_MODE_SSN THEN
7765: -- we need to adjust the sequence numbers of these stops flipped:
7826: (l_getstops_pl_arr_date(j) <> l_getstops_org_pl_arr_date(j) and
7827: l_getstops_phys_stop_id(j) is not NULL ) THEN
7828: -- the linked physical stop has been modified, it is ok to push
7829: -- other stops to avoid the time conflict
7830: l_getstops_pl_arr_date(j-1) := l_getstops_pl_arr_date(j) - WSH_TRIPS_ACTIONS.C_TEN_SECONDS;
7831: l_getstops_pl_dep_date(j-1) := l_getstops_pl_arr_date(j-1);
7832: l_update_flag := TRUE;
7833: l_push_date_allowed := TRUE;
7834: ELSE
8067: IF c_get_stops_SSN%ISOPEN THEN
8068: close c_get_stops_SSN;
8069: END IF;
8070:
8071: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.LINK_TO_REGULAR_STOPS');
8072: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8073:
8074: IF l_debug_on THEN
8075: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
8105: , freight_terms_code
8106: , Load_tender_status
8107: , lane_id
8108: , rank_id
8109: FROM WSH_TRIPS
8110: WHERE trip_id = c_tripid;
8111:
8112: -- Cursor to get stop level information
8113: CURSOR c_get_trip_stops(c_tripid IN NUMBER) is
8217:
8218: l_commit VARCHAR2(100) := FND_API.G_FALSE;
8219: l_init_msg_list VARCHAR2(100) := FND_API.G_FALSE;
8220:
8221: l_trip_in_rec WSH_TRIPS_GRP.tripInRecType;
8222: l_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
8223: l_trip_out_rec_tab WSH_TRIPS_GRP.Trip_Out_Tab_Type;
8224:
8225: l_msg_count NUMBER;
8218: l_commit VARCHAR2(100) := FND_API.G_FALSE;
8219: l_init_msg_list VARCHAR2(100) := FND_API.G_FALSE;
8220:
8221: l_trip_in_rec WSH_TRIPS_GRP.tripInRecType;
8222: l_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
8223: l_trip_out_rec_tab WSH_TRIPS_GRP.Trip_Out_Tab_Type;
8224:
8225: l_msg_count NUMBER;
8226: l_msg_data VARCHAR2(2000);
8219: l_init_msg_list VARCHAR2(100) := FND_API.G_FALSE;
8220:
8221: l_trip_in_rec WSH_TRIPS_GRP.tripInRecType;
8222: l_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
8223: l_trip_out_rec_tab WSH_TRIPS_GRP.Trip_Out_Tab_Type;
8224:
8225: l_msg_count NUMBER;
8226: l_msg_data VARCHAR2(2000);
8227: l_return_message VARCHAR2(2000);
8639: END IF;
8640:
8641: --IF (l_cs_result_tab(rec_cnt).rank = 1) THEN
8642: -- Has to update trip
8643: -- Call WSH_TRIPS_GRP.create_update_trip to update trip if required
8644:
8645: -- AG
8646: l_trip_info_tab.DELETE;
8647: --l_trip_in_rec.caller := 'FTE_CARRIER_SELECTION_FORM';
8649: l_trip_in_rec.caller := 'FTE_ROUTING_GUIDE';
8650: l_trip_in_rec.phase := null;
8651: l_trip_in_rec.action_code := 'UPDATE';
8652:
8653: WSH_TRIPS_PVT.populate_record(
8654: p_trip_id => l_trip_id,
8655: x_trip_info => l_trip_info_tab(1),
8656: x_return_status => l_return_status);
8657:
8673: -- AG
8674: l_trip_info_tab(1).routing_rule_id := l_cs_result_tab(rec_cnt).rule_id;
8675: l_trip_info_tab(1).append_flag := 'Y';
8676:
8677: WSH_TRIPS_GRP.Create_Update_Trip(
8678: p_api_version_number => 1.0,
8679: p_init_msg_list => l_init_msg_list,
8680: p_commit => l_commit,
8681: x_return_status => l_return_status,
8858: CLOSE c_get_trip_cmove;
8859: END IF;
8860: */
8861:
8862: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.PROCESS_CARRIER_SELECTION');
8863: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8864:
8865: IF l_debug_on THEN
8866: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
8987: IF l_delivery_tab.count = 0 THEN
8988: RAISE WSH_INVALID_ACTION;
8989: END IF;
8990: IF l_debug_on THEN
8991: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_Trips_Actions.Unassign_Trip',WSH_DEBUG_SV.C_PROC_LEVEL);
8992: END IF;
8993:
8994: WSH_Trips_Actions.Unassign_Trip(
8995: p_del_rows => l_delivery_tab,
8990: IF l_debug_on THEN
8991: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_Trips_Actions.Unassign_Trip',WSH_DEBUG_SV.C_PROC_LEVEL);
8992: END IF;
8993:
8994: WSH_Trips_Actions.Unassign_Trip(
8995: p_del_rows => l_delivery_tab,
8996: p_trip_id => p_trip_id_tab(i),
8997: x_return_status => l_return_status);
8998:
9042: WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
9043: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
9044: END IF;
9045: WHEN OTHERS THEN
9046: wsh_util_core.default_handler('wsh_trips_actions',l_module_name);
9047: --
9048: IF l_debug_on THEN
9049: wsh_debug_sv.pop(l_module_name, 'EXCEPTION:OTHERS');
9050: END IF;
9051:
9052: END Remove_Consolidation;
9053: --
9054:
9055: END WSH_TRIPS_ACTIONS;