[Home] [Help]
1: PACKAGE BODY WSH_TRIPS_ACTIONS as
2: /* $Header: WSHTRACB.pls 120.30 2011/02/21 09:37:09 brana 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.30 2011/02/21 09:37:09 brana 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
257: END IF;
258:
259: WHEN OTHERS THEN
260:
261: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.Get_Trip_Defaults');
262:
263: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
264: IF l_debug_on THEN
265: wsh_debug_sv.log (l_module_name,'Others',substr(sqlerrm,1,200));
314: SELECT 1
315: FROM wsh_new_deliveries dl,
316: wsh_delivery_legs dg,
317: wsh_trip_stops st,
318: wsh_trips t
319: WHERE dl.delivery_id = dg.delivery_id AND
320: (dg.pick_up_stop_id = st.stop_id OR dg.drop_off_stop_id = st.stop_id) AND
321: st.trip_id = t.trip_id AND
322: dl.STATUS_CODE <> 'CO' and
338: x_msg_count NUMBER;
339:
340: CURSOR c_trip_status IS
341: SELECT status_code
342: FROM wsh_trips
343: WHERE trip_id = p_trip_id ;
344:
345: l_trip_status VARCHAR2(2);
346:
675:
676: WHEN others THEN
677: rollback to sp_confirm_trip;
678: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
679: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.CONFIRM_TRIP');
680: --
681: IF l_debug_on THEN
682: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
683: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
712:
713: cursor get_status(c_trip_id IN NUMBER) is
714: select status_code,
715: NVL(shipments_type_flag,'O') -- J-IB-NPARIKH
716: from wsh_trips
717: where trip_id = c_trip_id;
718:
719: cursor get_stops(c_trip_id IN NUMBER) is
720: select 'Y'
724: and rownum = 1;
725:
726: CURSOR c_istripfirm(p_tripid IN NUMBER) IS
727: select 'Y'
728: from wsh_trips
729: where trip_id=p_tripid AND
730: planned_flag='F';
731:
732: l_tripfirm VARCHAR2(1);
842: --
843: -- Debug Statements
844: --
845: IF l_debug_on THEN
846: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
847: END IF;
848: --
849: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
850: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
845: IF l_debug_on THEN
846: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
847: END IF;
848: --
849: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
850: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
851: wsh_util_core.add_message(x_return_status);
852: l_num_error := l_num_error + 1;
853:
895: END IF;
896: --
897: EXCEPTION
898: WHEN others THEN
899: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.PLAN');
900: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
901:
902: --
903: -- Debug Statements
917: i BINARY_INTEGER;
918: /* H integration for Multi Leg */
919: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
920: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
921: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
922: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
923: l_return_status VARCHAR2(30);
924: l_num_warn NUMBER := 0;
925:
918: /* H integration for Multi Leg */
919: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
920: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
921: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
922: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
923: l_return_status VARCHAR2(30);
924: l_num_warn NUMBER := 0;
925:
926: -- Exceptions Project
1030: --
1031: -- Debug Statements
1032: --
1033: IF l_debug_on THEN
1034: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_GRP.GET_TRIP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
1035: END IF;
1036: --
1037: wsh_trips_grp.get_trip_details_pvt
1038: (p_trip_id => p_trip_id,
1033: IF l_debug_on THEN
1034: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_GRP.GET_TRIP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
1035: END IF;
1036: --
1037: wsh_trips_grp.get_trip_details_pvt
1038: (p_trip_id => p_trip_id,
1039: x_trip_rec => l_trip_rec,
1040: x_return_status => l_return_status);
1041:
1085: /* End of H integration */
1086:
1087: --Bug Fix 2993711 added last_update_date,last_updated_by,last_update_login --
1088:
1089: UPDATE wsh_trips
1090: SET status_code = p_status_code,
1091: last_update_date = SYSDATE,
1092: last_updated_by = fnd_global.user_id,
1093: last_update_login = fnd_global.login_id
1152: END IF;
1153: --
1154:
1155: WHEN others THEN
1156: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.CHANGE_STATUS');
1157: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1158: --
1159: IF l_debug_on THEN
1160: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1203:
1204:
1205: /* H integration for Multi Leg */
1206: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
1207: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
1208: --
1209: --
1210: l_num_warnings NUMBER;
1211: l_num_errors NUMBER;
1276: --
1277: -- Debug Statements
1278: --
1279: IF l_debug_on THEN
1280: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_GRP.GET_TRIP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
1281: END IF;
1282: --
1283: wsh_trips_grp.get_trip_details_pvt
1284: (p_trip_id => p_in_rec.trip_id,
1279: IF l_debug_on THEN
1280: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_GRP.GET_TRIP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
1281: END IF;
1282: --
1283: wsh_trips_grp.get_trip_details_pvt
1284: (p_trip_id => p_in_rec.trip_id,
1285: x_trip_rec => l_trip_rec,
1286: x_return_status => l_return_status);
1287: --
1319: /* End of H integration */
1320:
1321: --Bug Fix 2993711 added last_update_date,last_updated_by,last_update_login --
1322:
1323: UPDATE wsh_trips
1324: SET status_code = p_in_rec.new_status_code,
1325: last_update_date = SYSDATE,
1326: last_updated_by = fnd_global.user_id,
1327: last_update_login = fnd_global.login_id
1521: --
1522: --========================================================================
1523: PROCEDURE generateRoutingResponse
1524: (
1525: p_action_prms IN WSH_TRIPS_GRP.action_parameters_rectype,
1526: p_rec_attr_tab IN WSH_TRIPS_PVT.Trip_Attr_Tbl_Type,
1527: x_return_status OUT NOCOPY VARCHAR2
1528: )
1529: IS
1522: --========================================================================
1523: PROCEDURE generateRoutingResponse
1524: (
1525: p_action_prms IN WSH_TRIPS_GRP.action_parameters_rectype,
1526: p_rec_attr_tab IN WSH_TRIPS_PVT.Trip_Attr_Tbl_Type,
1527: x_return_status OUT NOCOPY VARCHAR2
1528: )
1529: IS
1530: --{
1543: SELECT wdl.delivery_id, wt.name
1544: FROM wsh_trip_stops wts,
1545: wsh_Delivery_legs wdl,
1546: wsh_new_deliveries wnd,
1547: wsh_trips wt
1548: WHERE wt.trip_id = p_trip_id
1549: AND wts.trip_id = p_trip_id
1550: AND wdl.pick_up_stop_id = wts.stop_id
1551: AND wnd.delivery_id = wdl.delivery_id
1663: FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_TRIP_NAME');
1664: ELSE
1665: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NO_PICKUP_ERROR');
1666: --FND_MESSAGE.SET_TOKEN('TRIP_NAME',NVL(l_trip_name,p_rec_attr_tab(l_index).trip_id));
1667: FND_MESSAGE.SET_TOKEN('TRIP_NAME',NVL(l_trip_name, wsh_trips_pvt.get_name( p_rec_attr_tab(l_index).trip_id)));
1668: END IF;
1669: --
1670: wsh_util_core.add_message(wsh_util_core.g_ret_sts_warning,l_module_name);
1671: l_num_warnings := NVL(l_num_warnings,0) + 1;
1812: END IF;
1813: --
1814: WHEN OTHERS THEN
1815: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1816: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.generateRoutingResponse');
1817: --
1818: -- Debug Statements
1819: --
1820: IF l_debug_on THEN
1976: EXCEPTION
1977: --
1978: WHEN others THEN
1979: --
1980: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.GET_NEXT_DEL_ROWS');
1981: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1982: --
1983: IF l_debug_on THEN
1984: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2094: END IF;
2095: --
2096: WHEN others THEN
2097: --
2098: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.SORT_DEL');
2099: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2100: --
2101: IF l_debug_on THEN
2102: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2147: l_del_tbl_not_sorted Del_Rec_Type;
2148: l_return_status varchar2(1);
2149: l_del_rows wsh_util_core.id_tab_type;
2150: l_trip_id NUMBER;
2151: l_trip_name wsh_trips.name%TYPE;
2152: l_initial_pickup_location_id NUMBER;
2153: l_ultimate_dropoff_location_id NUMBER;
2154: l_num_warnings NUMBER := 0;
2155: l_num_errors NUMBER := 0;
2262: EXCEPTION
2263: --
2264: WHEN others THEN
2265: --
2266: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.AUTOCREATE_TRIP_WRP');
2267: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2268: --
2269: IF l_debug_on THEN
2270: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2301: l_ignore_ids wsh_util_core.id_tab_type;
2302: l_include_ids wsh_util_core.id_tab_type;
2303: l_return_status VARCHAR2(1);
2304: l_trip_id NUMBER;
2305: l_trip_name wsh_trips.name%TYPE;
2306: l_index NUMBER;
2307: l_trip_ids wsh_util_core.id_tab_type;
2308: l_trip_names wsh_util_core.Column_Tab_Type;
2309: l_gc3_is_installed VARCHAR2(1); -- OTM R12, glog proj
2306: l_index NUMBER;
2307: l_trip_ids wsh_util_core.id_tab_type;
2308: l_trip_names wsh_util_core.Column_Tab_Type;
2309: l_gc3_is_installed VARCHAR2(1); -- OTM R12, glog proj
2310: l_ignore_flag WSH_TRIPS.IGNORE_FOR_PLANNING%TYPE; -- OTM R12, glog proj
2311: l_count_error NUMBER;
2312:
2313: --
2314: l_debug_on BOOLEAN;
2474: --
2475:
2476: EXCEPTION
2477: WHEN others THEN
2478: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.AUTOCREATE_TRIP_MULTI');
2479: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2480:
2481: --
2482: -- Debug Statements
2532:
2533: l_delivery_id NUMBER;
2534: l_trip_id NUMBER;
2535:
2536: l_ship_method wsh_trips.ship_method_code%TYPE;
2537: l_ship_method_old wsh_trips.ship_method_code%TYPE;
2538: l_carrier_id wsh_trips.carrier_id%TYPE;
2539: l_mode wsh_trips.mode_of_transport%TYPE;
2540: l_service_level wsh_trips.service_level%TYPE;
2533: l_delivery_id NUMBER;
2534: l_trip_id NUMBER;
2535:
2536: l_ship_method wsh_trips.ship_method_code%TYPE;
2537: l_ship_method_old wsh_trips.ship_method_code%TYPE;
2538: l_carrier_id wsh_trips.carrier_id%TYPE;
2539: l_mode wsh_trips.mode_of_transport%TYPE;
2540: l_service_level wsh_trips.service_level%TYPE;
2541: l_carrier_id_old wsh_trips.carrier_id%TYPE;
2534: l_trip_id NUMBER;
2535:
2536: l_ship_method wsh_trips.ship_method_code%TYPE;
2537: l_ship_method_old wsh_trips.ship_method_code%TYPE;
2538: l_carrier_id wsh_trips.carrier_id%TYPE;
2539: l_mode wsh_trips.mode_of_transport%TYPE;
2540: l_service_level wsh_trips.service_level%TYPE;
2541: l_carrier_id_old wsh_trips.carrier_id%TYPE;
2542: l_mode_old wsh_trips.mode_of_transport%TYPE;
2535:
2536: l_ship_method wsh_trips.ship_method_code%TYPE;
2537: l_ship_method_old wsh_trips.ship_method_code%TYPE;
2538: l_carrier_id wsh_trips.carrier_id%TYPE;
2539: l_mode wsh_trips.mode_of_transport%TYPE;
2540: l_service_level wsh_trips.service_level%TYPE;
2541: l_carrier_id_old wsh_trips.carrier_id%TYPE;
2542: l_mode_old wsh_trips.mode_of_transport%TYPE;
2543: l_service_level_old wsh_trips.service_level%TYPE;
2536: l_ship_method wsh_trips.ship_method_code%TYPE;
2537: l_ship_method_old wsh_trips.ship_method_code%TYPE;
2538: l_carrier_id wsh_trips.carrier_id%TYPE;
2539: l_mode wsh_trips.mode_of_transport%TYPE;
2540: l_service_level wsh_trips.service_level%TYPE;
2541: l_carrier_id_old wsh_trips.carrier_id%TYPE;
2542: l_mode_old wsh_trips.mode_of_transport%TYPE;
2543: l_service_level_old wsh_trips.service_level%TYPE;
2544: l_ignore_for_planning wsh_trips.ignore_for_planning%TYPE;
2537: l_ship_method_old wsh_trips.ship_method_code%TYPE;
2538: l_carrier_id wsh_trips.carrier_id%TYPE;
2539: l_mode wsh_trips.mode_of_transport%TYPE;
2540: l_service_level wsh_trips.service_level%TYPE;
2541: l_carrier_id_old wsh_trips.carrier_id%TYPE;
2542: l_mode_old wsh_trips.mode_of_transport%TYPE;
2543: l_service_level_old wsh_trips.service_level%TYPE;
2544: l_ignore_for_planning wsh_trips.ignore_for_planning%TYPE;
2545:
2538: l_carrier_id wsh_trips.carrier_id%TYPE;
2539: l_mode wsh_trips.mode_of_transport%TYPE;
2540: l_service_level wsh_trips.service_level%TYPE;
2541: l_carrier_id_old wsh_trips.carrier_id%TYPE;
2542: l_mode_old wsh_trips.mode_of_transport%TYPE;
2543: l_service_level_old wsh_trips.service_level%TYPE;
2544: l_ignore_for_planning wsh_trips.ignore_for_planning%TYPE;
2545:
2546: l_status_code VARCHAR2(30);
2539: l_mode wsh_trips.mode_of_transport%TYPE;
2540: l_service_level wsh_trips.service_level%TYPE;
2541: l_carrier_id_old wsh_trips.carrier_id%TYPE;
2542: l_mode_old wsh_trips.mode_of_transport%TYPE;
2543: l_service_level_old wsh_trips.service_level%TYPE;
2544: l_ignore_for_planning wsh_trips.ignore_for_planning%TYPE;
2545:
2546: l_status_code VARCHAR2(30);
2547:
2540: l_service_level wsh_trips.service_level%TYPE;
2541: l_carrier_id_old wsh_trips.carrier_id%TYPE;
2542: l_mode_old wsh_trips.mode_of_transport%TYPE;
2543: l_service_level_old wsh_trips.service_level%TYPE;
2544: l_ignore_for_planning wsh_trips.ignore_for_planning%TYPE;
2545:
2546: l_status_code VARCHAR2(30);
2547:
2548: l_ship_method_flag BOOLEAN := TRUE;
2547:
2548: l_ship_method_flag BOOLEAN := TRUE;
2549:
2550: l_rowid VARCHAR2(30);
2551: l_trip_info WSH_TRIPS_PVT.TRIP_REC_TYPE;
2552: assigned_del_error EXCEPTION;
2553: l_return_status VARCHAR2(1);
2554: l_del_legs WSH_UTIL_CORE.ID_TAB_TYPE;
2555: l_good_dels WSH_UTIL_CORE.ID_TAB_TYPE;
2557: /* H integration for Multi Leg */
2558: l_num_warn NUMBER := 0;
2559: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
2560: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
2561: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
2562: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
2563: l_grp_trip_rec WSH_TRIPS_GRP.TRIP_PUB_REC_TYPE;
2564: --l_return_status VARCHAR2(30);
2565: l_fte_flag VARCHAR2(1):= 'N';
2558: l_num_warn NUMBER := 0;
2559: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
2560: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
2561: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
2562: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
2563: l_grp_trip_rec WSH_TRIPS_GRP.TRIP_PUB_REC_TYPE;
2564: --l_return_status VARCHAR2(30);
2565: l_fte_flag VARCHAR2(1):= 'N';
2566: --
2559: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
2560: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
2561: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
2562: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
2563: l_grp_trip_rec WSH_TRIPS_GRP.TRIP_PUB_REC_TYPE;
2564: --l_return_status VARCHAR2(30);
2565: l_fte_flag VARCHAR2(1):= 'N';
2566: --
2567: l_debug_on BOOLEAN;
2579: l_trip_ids WSH_UTIL_CORE.ID_TAB_TYPE;
2580: l_success_trip_ids WSH_UTIL_CORE.ID_TAB_TYPE;
2581:
2582: l_gc3_is_installed VARCHAR2(1); -- OTM R12, glog proj
2583: l_ignore_flag WSH_TRIPS.IGNORE_FOR_PLANNING%TYPE; -- OTM R12, glog proj
2584: l_count_error NUMBER;
2585:
2586: --
2587: BEGIN
2776: /* H integration */
2777: /* The trip level call should have been made at parent level */
2778: --
2779: IF l_debug_on THEN
2780: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.CREATE_TRIP',WSH_DEBUG_SV.C_PROC_LEVEL);
2781: END IF;
2782: --
2783: wsh_trips_pvt.create_trip(l_trip_info, l_rowid, x_trip_id, x_trip_name,x_return_status);
2784: --
2779: IF l_debug_on THEN
2780: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.CREATE_TRIP',WSH_DEBUG_SV.C_PROC_LEVEL);
2781: END IF;
2782: --
2783: wsh_trips_pvt.create_trip(l_trip_info, l_rowid, x_trip_id, x_trip_name,x_return_status);
2784: --
2785: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2786: --
2787: IF l_debug_on THEN
2998:
2999: -- SSN change
3000: -- Call to reset_stop_planned_dates API should be made
3001: -- only for profile = PAD
3002: IF WSH_TRIPS_ACTIONS.GET_STOP_SEQ_MODE = WSH_INTERFACE_GRP.G_STOP_SEQ_MODE_PAD THEN
3003: -- re-set the dates according to sequence number
3004: -- bug 3516052
3005: l_trip_id := x_trip_id;
3006:
3003: -- re-set the dates according to sequence number
3004: -- bug 3516052
3005: l_trip_id := x_trip_id;
3006:
3007: WSH_TRIPS_ACTIONS.reset_stop_planned_dates( p_trip_id => l_trip_id,
3008: p_caller => 'WSH_AUTOCREATE_TRIP',
3009: x_return_status => l_return_status);
3010: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3011: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3048: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FLEXFIELD_UTILS.WRITE_DFF_ATTRIBUTES',WSH_DEBUG_SV.C_PROC_LEVEL);
3049: END IF;
3050: -- for bug 5948562, to populate values in additional trip information flexfield, when auto creating a trip
3051: wsh_flexfield_utils.WRITE_DFF_ATTRIBUTES
3052: (p_table_name => 'WSH_TRIPS',
3053: p_primary_id => x_trip_id,
3054: x_return_status => x_return_status);
3055:
3056: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3085: EXCEPTION
3086: --
3087: WHEN others THEN
3088: --
3089: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.AUTOCREATE_TRIP');
3090: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3091: --
3092: IF l_debug_on THEN
3093: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3151: l_msg_data VARCHAR2(2000);
3152: --dummy tables for calling validate_constraint_mainper
3153: l_cc_del_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
3154: l_cc_det_attr_tab WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type;
3155: l_cc_trip_attr_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
3156: l_cc_stop_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
3157: l_cc_in_ids wsh_util_core.id_tab_type;
3158: l_cc_fail_ids wsh_util_core.id_tab_type;
3159:
3164: --and (SHIP_METHOD_CODE is not null OR INTMED_SHIP_TO_LOCATION_ID is not null);
3165:
3166: CURSOR trip_cur(p_trip_id NUMBER) IS
3167: SELECT SHIP_METHOD_CODE
3168: FROM wsh_trips
3169: WHERE trip_id = p_trip_id;
3170: --and SHIP_METHOD_CODE is not null;
3171:
3172: l_line_rows wsh_util_core.id_tab_type:=p_line_rows;
3649: IF l_trip_ids is not null AND l_trip_ids.COUNT>0 THEN--{
3650: FOR l_tripindex IN l_trip_ids.FIRST..l_trip_ids.LAST LOOP--{
3651: IF l_cc_upd_dlvy_ship_method='N' THEN--{
3652: FOR tripcurtemp in trip_cur(l_trip_ids(l_tripindex)) LOOP--{
3653: update wsh_trips
3654: set SHIP_METHOD_CODE=null,
3655: CARRIER_ID = null,
3656: MODE_OF_TRANSPORT = null,
3657: SERVICE_LEVEL = null
3726:
3727: IF l_cc_upd_dlvy_ship_method='N' and l_trip_id_tab is not null and l_trip_id_tab.COUNT>0 THEN--{
3728: FOR l_tripindex IN l_trip_id_tab.FIRST..l_trip_id_tab.LAST LOOP--{
3729: FOR tripcurtemp in trip_cur(l_trip_id_tab(l_tripindex)) LOOP--{
3730: update wsh_trips
3731: set SHIP_METHOD_CODE=null,
3732: CARRIER_ID = null,
3733: MODE_OF_TRANSPORT = null,
3734: SERVICE_LEVEL = null
3811: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:Adjust_Planned_Flag_Err');
3812: END IF;--}
3813:
3814: WHEN others THEN
3815: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.AUTOCREATE_DEL_TRIP');
3816: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3817:
3818:
3819: --
3940: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:REPRICE_REQUIRED_ERR');
3941: END IF;
3942: --
3943: WHEN others THEN
3944: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.ASSIGN_TRIP');
3945: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3946:
3947: --
3948: -- Debug Statements
4171: END IF;
4172: --
4173: EXCEPTION
4174: WHEN others THEN
4175: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.CHECK_ASSIGN_TRIP');
4176: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4177:
4178: --
4179: -- Debug Statements
4646: IF l_debug_on THEN
4647: 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);
4648: END IF;
4649:
4650: IF WSH_TRIPS_ACTIONS.GET_STOP_SEQ_MODE = WSH_INTERFACE_GRP.G_STOP_SEQ_MODE_SSN THEN
4651: x_dropoff_seq_num := derive_next_ssn(p_trip_id, x_dropoff_stop_id);
4652: IF l_debug_on THEN
4653: WSH_DEBUG_SV.log(l_module_name,'x_dropoff_seq_num adjusted', x_dropoff_seq_num);
4654: END IF;
4675: IF l_debug_on THEN
4676: WSH_DEBUG_SV.log(l_module_name,'Have to create new internal stop as existing physical stop has activities',l_dropoff_location_id);
4677: END IF;
4678:
4679: IF WSH_TRIPS_ACTIONS.GET_STOP_SEQ_MODE = WSH_INTERFACE_GRP.G_STOP_SEQ_MODE_SSN THEN
4680: x_dropoff_seq_num := derive_next_ssn(p_trip_id, x_dropoff_stop_id);
4681: IF l_debug_on THEN
4682: WSH_DEBUG_SV.log(l_module_name,'x_dropoff_seq_num adjusted', x_dropoff_seq_num);
4683: END IF;
4740: --
4741: WHEN OTHERS THEN
4742: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4743: WSH_UTIL_CORE.DEFAULT_HANDLER(
4744: 'WSH_TRIPS_ACTIONS.process_dels_for_internal_locs',
4745: l_module_name);
4746:
4747: IF l_debug_on THEN
4748: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4792: others EXCEPTION;
4793: /* H integration for Multi Leg */
4794: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
4795: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
4796: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
4797: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
4798: l_num_warn NUMBER := 0;
4799: l_num_err NUMBER := 0;
4800: --l_return_status VARCHAR2(30);
4793: /* H integration for Multi Leg */
4794: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
4795: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
4796: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
4797: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
4798: l_num_warn NUMBER := 0;
4799: l_num_err NUMBER := 0;
4800: --l_return_status VARCHAR2(30);
4801:
4837: THEN
4838: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4839: END IF;
4840: --
4841: l_stop_seq_mode := WSH_TRIPS_ACTIONS.GET_STOP_SEQ_MODE;
4842:
4843: IF l_debug_on THEN
4844: WSH_DEBUG_SV.push(l_module_name);
4845: --
5153: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:STOP_NOT_FOUND');
5154: END IF;
5155: --
5156: WHEN others THEN
5157: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.ASSIGN_TRIP');
5158: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5159:
5160: --
5161: -- Debug Statements
5206:
5207: CURSOR trip_vehicle_info (c_trip_id NUMBER) IS
5208: SELECT vehicle_item_id,
5209: vehicle_organization_id
5210: FROM wsh_trips
5211: WHERE trip_id = c_trip_id;
5212:
5213: CURSOR trip_info (c_trip_id NUMBER) IS
5214: SELECT msi.maximum_load_weight,
5217: msi.weight_uom_code,
5218: msi.volume_uom_code,
5219: msi.organization_id
5220: FROM mtl_system_items msi,
5221: wsh_trips t
5222: WHERE msi.organization_id = t.vehicle_organization_id AND
5223: t.vehicle_item_id = msi.inventory_item_id AND
5224: t.trip_id = c_trip_id;
5225:
5225:
5226: CURSOR org_info (c_trip_id NUMBER) IS
5227: SELECT wsp.percent_fill_basis_flag
5228: FROM wsh_shipping_parameters wsp,
5229: wsh_trips t
5230: WHERE wsp.organization_id = t.vehicle_organization_id AND
5231: t.trip_id = c_trip_id;
5232:
5233: l_trip_id NUMBER;
5376: END IF;
5377: IF org_info%ISOPEN THEN
5378: CLOSE org_info;
5379: END IF;
5380: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.CALC_STOP_FILL_PERCENT');
5381: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5382:
5383: IF l_debug_on THEN
5384: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5474: msi.weight_uom_code,
5475: msi.volume_uom_code,
5476: msi.organization_id
5477: FROM mtl_system_items msi,
5478: wsh_trips t
5479: WHERE msi.organization_id = t.vehicle_organization_id AND
5480: t.vehicle_item_id = msi.inventory_item_id AND
5481: t.trip_id = l_trip_id;
5482:
5482:
5483: CURSOR org_info (l_trip_id NUMBER) IS
5484: SELECT wsp.percent_fill_basis_flag
5485: FROM wsh_shipping_parameters wsp,
5486: wsh_trips t
5487: WHERE wsp.organization_id = t.vehicle_organization_id AND
5488: t.trip_id = l_trip_id;
5489:
5490: CURSOR trip_vehicle_info (l_trip_id NUMBER) IS
5489:
5490: CURSOR trip_vehicle_info (l_trip_id NUMBER) IS
5491: SELECT vehicle_item_id,
5492: vehicle_organization_id
5493: FROM wsh_trips
5494: WHERE trip_id = l_trip_id;
5495:
5496:
5497: l_net_weight NUMBER;
5545: l_locked_stop_id NUMBER := 0;
5546: others EXCEPTION;
5547: /* H integration for Multi Leg */
5548: l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
5549: l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
5550:
5551: --R12 MDC
5552: l_total_pick_up_weight NUMBER := 0;
5553: l_total_drop_off_weight NUMBER := 0;
5661: --
5662: -- Debug Statements
5663: --
5664: IF l_debug_on THEN
5665: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5666: END IF;
5667: --
5668: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
5669: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5664: IF l_debug_on THEN
5665: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5666: END IF;
5667: --
5668: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
5669: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5670: wsh_util_core.add_message(x_return_status);
5671: goto wt_vol_error;
5672: END IF;
5685: --
5686: -- Debug Statements
5687: --
5688: IF l_debug_on THEN
5689: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5690: END IF;
5691: --
5692: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
5693: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5688: IF l_debug_on THEN
5689: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5690: END IF;
5691: --
5692: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
5693: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5694: wsh_util_core.add_message(x_return_status);
5695: goto wt_vol_error;
5696: END IF;
6240: --
6241: -- Debug Statements
6242: --
6243: IF l_debug_on THEN
6244: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6245: END IF;
6246: --
6247: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
6248: wsh_util_core.add_message(x_return_status);
6243: IF l_debug_on THEN
6244: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6245: END IF;
6246: --
6247: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
6248: wsh_util_core.add_message(x_return_status);
6249: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
6250: l_num_error := l_num_error + 1;
6251: ELSIF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
6274: IF (l_num_error > 0) OR (l_num_warn > 0) THEN
6275: -- Bug 3688384 Display separate message for one trip .
6276: IF (p_trip_rows.count = 1) and (l_num_error = 0) and (l_num_warn > 0) THEN
6277: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_WT_VOL_WARN');
6278: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(1)));
6279: ELSIF (p_trip_rows.count = 1) and (l_num_error > 0) THEN
6280: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_WT_VOL_ERR');
6281: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(1)));
6282: ELSIF (p_trip_rows.count > 1) then
6277: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_WT_VOL_WARN');
6278: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(1)));
6279: ELSIF (p_trip_rows.count = 1) and (l_num_error > 0) THEN
6280: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_WT_VOL_ERR');
6281: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(1)));
6282: ELSIF (p_trip_rows.count > 1) then
6283: -- Bug 3688384 .
6284: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_WT_VOL_SUMMARY');
6285: FND_MESSAGE.SET_TOKEN('NUM_WARN',l_num_warn);
6314: WHEN others THEN
6315: IF lock_stop%ISOPEN THEN
6316: CLOSE lock_stop;
6317: END IF;
6318: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.TRIP_WEIGHT_VOLUME');
6319: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6320:
6321: --
6322: -- Debug Statements
6414: --
6415: -- Debug Statements
6416: --
6417: IF l_debug_on THEN
6418: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6419: END IF;
6420: --
6421: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_id));
6422: wsh_util_core.add_message(x_return_status);
6417: IF l_debug_on THEN
6418: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6419: END IF;
6420: --
6421: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_id));
6422: wsh_util_core.add_message(x_return_status);
6423: --
6424: -- Debug Statements
6425: --
6428: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INVALID_SEQUENCE');
6429: END IF;
6430: --
6431: WHEN others THEN
6432: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.VALIDATE_STOP_SEQUENCE');
6433: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6434:
6435: --
6436: -- Debug Statements
6495:
6496: -- bug 2374603: we cannot unassign from PLANNED trips
6497: --J can assign from planned trip
6498: stmt_str := 'select st.trip_id, count(*) '||
6499: 'from wsh_trip_stops st, wsh_delivery_legs dg, wsh_trips tr '||
6500: 'where dg.pick_up_stop_id = st.stop_id AND '||
6501: 'tr.trip_id = st.trip_id AND tr.planned_flag IN (''N'',''Y'') AND ' ||
6502: 'dg.delivery_id IN (' || del_str || ') ' ||
6503: 'group by st.trip_id '||
6527: END IF;
6528: --
6529: EXCEPTION
6530: WHEN others THEN
6531: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.CHECK_UNASSIGN_TRIP');
6532: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6533:
6534: --
6535: -- Debug Statements
6548: x_return_status OUT NOCOPY VARCHAR2) IS
6549:
6550: cursor get_trip_info(p_trip_id in number) is
6551: SELECT name, planned_flag
6552: FROM wsh_trips
6553: WHERE trip_id = p_trip_id;
6554:
6555:
6556: cursor get_consol_child_deliveries(p_trip_id in number, p_delivery_id in number) is
6563: l_trip_tab WSH_UTIL_CORE.id_tab_type;
6564: l_mdc_del_tab WSH_UTIL_CORE.id_tab_type;
6565: l_return_status VARCHAR2(10);
6566: l_trip_name VARCHAR2(30);
6567: l_planned_flag wsh_trips.planned_flag%TYPE;
6568: j NUMBER := 0;
6569: unassign_deliveries_err EXCEPTION;
6570: reprice_required_err EXCEPTION;
6571: trip_not_found EXCEPTION;
6763: WHEN others THEN
6764: IF get_trip_info%ISOPEN THEN
6765: CLOSE get_trip_info;
6766: END IF;
6767: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.UNASSIGN_TRIP');
6768: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6769:
6770: --
6771: -- Debug Statements
6807: p_fill_percent IN NUMBER,
6808: x_return_status OUT NOCOPY VARCHAR2) IS
6809:
6810: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
6811: l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
6812: l_return_status VARCHAR2(1);
6813:
6814: l_debug_on BOOLEAN;
6815: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'FTE_LOAD_TENDER';
6874: END IF;
6875:
6876: EXCEPTION
6877: WHEN others THEN
6878: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.Fte_Load_Tender');
6879: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6880:
6881: IF l_debug_on THEN
6882: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6927: wst.planned_departure_date,
6928: wst.actual_arrival_date,
6929: wst.actual_departure_date
6930: FROM wsh_trip_stops wst,
6931: wsh_trips wtp
6932: WHERE wtp.status_code in ('OP', 'IT')
6933: AND wtp.trip_id = c_trip_id
6934: AND wtp.trip_id = wst.trip_id
6935: ORDER BY wst.stop_sequence_number,wst.stop_id;
7027: NVL(l_last_stop_rec.actual_departure_date, l_last_stop_rec.actual_arrival_date));
7028: END IF;
7029:
7030: IF l_stop_rec.planned_arrival_date <= l_base_date THEN
7031: l_stop_rec.planned_arrival_date := l_base_date + WSH_TRIPS_ACTIONS.C_TEN_MINUTES;
7032: l_update_flag := TRUE;
7033: END IF;
7034:
7035: IF l_update_flag THEN
7156: END IF;
7157: IF c_trip_stops%ISOPEN THEN
7158: close c_trip_stops;
7159: END IF;
7160: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.reset_stop_planned_dates');
7161: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7162:
7163: IF l_debug_on THEN
7164: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
7220: END IF;
7221: return;
7222: END IF;
7223: l_trip_ids(1) := p_trip_id;
7224: WSH_TRIPS_ACTIONS.reset_stop_planned_dates
7225: ( p_trip_ids => l_trip_ids,
7226: p_caller => p_caller,
7227: x_success_trip_ids => l_success_trip_ids,
7228: x_return_status => l_return_status);
7239:
7240: EXCEPTION
7241:
7242: WHEN others THEN
7243: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.reset_stop_planned_dates');
7244: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7245:
7246: IF l_debug_on THEN
7247: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
7607: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
7608: l_warn_num := 0;
7609: x_success_trip_ids.delete;
7610:
7611: l_stop_seq_mode := WSH_TRIPS_ACTIONS.GET_STOP_SEQ_MODE;
7612:
7613: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
7614: IF l_debug_on IS NULL THEN
7615: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
7727: IF l_debug_on THEN
7728: WSH_DEBUG_SV.log(l_module_name,'link to next stop at stop', l_getstops_stop_id(j));
7729: END IF;
7730: l_getstops_phys_stop_id(j) := l_getstops_stop_id(j+1);
7731: l_getstops_pl_arr_date(j) := l_getstops_pl_arr_date(j+1) - WSH_TRIPS_ACTIONS.C_TEN_SECONDS;
7732: l_getstops_pl_dep_date(j) := l_getstops_pl_arr_date(j);
7733: IF l_stop_seq_mode = WSH_INTERFACE_GRP.G_STOP_SEQ_MODE_SSN THEN
7734: l_getstops_stop_seq_num(j) := l_getstops_stop_seq_num(j+1) - 1;
7735: END IF;
7768: l_getstops_stop_loc_id(j-1) := l_getstops_tmp.stop_location_id;
7769: l_getstops_phys_loc_id(j-1) := l_getstops_tmp.physical_location_id;
7770: l_getstops_status_code(j-1) := l_getstops_tmp.status_code;
7771: l_getstops_phys_stop_id(j-1) := l_getstops_stop_id(j); -- link
7772: l_getstops_pl_arr_date(j-1) := l_getstops_pl_arr_date(j) - WSH_TRIPS_ACTIONS.C_TEN_SECONDS;
7773: l_getstops_pl_dep_date(j-1) := l_getstops_pl_arr_date(j-1);
7774:
7775: IF l_stop_seq_mode = WSH_INTERFACE_GRP.G_STOP_SEQ_MODE_SSN THEN
7776: -- we need to adjust the sequence numbers of these stops flipped:
7837: (l_getstops_pl_arr_date(j) <> l_getstops_org_pl_arr_date(j) and
7838: l_getstops_phys_stop_id(j) is not NULL ) THEN
7839: -- the linked physical stop has been modified, it is ok to push
7840: -- other stops to avoid the time conflict
7841: l_getstops_pl_arr_date(j-1) := l_getstops_pl_arr_date(j) - WSH_TRIPS_ACTIONS.C_TEN_SECONDS;
7842: l_getstops_pl_dep_date(j-1) := l_getstops_pl_arr_date(j-1);
7843: l_update_flag := TRUE;
7844: l_push_date_allowed := TRUE;
7845: ELSE
8078: IF c_get_stops_SSN%ISOPEN THEN
8079: close c_get_stops_SSN;
8080: END IF;
8081:
8082: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.LINK_TO_REGULAR_STOPS');
8083: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8084:
8085: IF l_debug_on THEN
8086: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
8116: , freight_terms_code
8117: , Load_tender_status
8118: , lane_id
8119: , rank_id
8120: FROM WSH_TRIPS
8121: WHERE trip_id = c_tripid;
8122:
8123: -- Cursor to get stop level information
8124: CURSOR c_get_trip_stops(c_tripid IN NUMBER) is
8228:
8229: l_commit VARCHAR2(100) := FND_API.G_FALSE;
8230: l_init_msg_list VARCHAR2(100) := FND_API.G_FALSE;
8231:
8232: l_trip_in_rec WSH_TRIPS_GRP.tripInRecType;
8233: l_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
8234: l_trip_out_rec_tab WSH_TRIPS_GRP.Trip_Out_Tab_Type;
8235:
8236: l_msg_count NUMBER;
8229: l_commit VARCHAR2(100) := FND_API.G_FALSE;
8230: l_init_msg_list VARCHAR2(100) := FND_API.G_FALSE;
8231:
8232: l_trip_in_rec WSH_TRIPS_GRP.tripInRecType;
8233: l_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
8234: l_trip_out_rec_tab WSH_TRIPS_GRP.Trip_Out_Tab_Type;
8235:
8236: l_msg_count NUMBER;
8237: l_msg_data VARCHAR2(2000);
8230: l_init_msg_list VARCHAR2(100) := FND_API.G_FALSE;
8231:
8232: l_trip_in_rec WSH_TRIPS_GRP.tripInRecType;
8233: l_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
8234: l_trip_out_rec_tab WSH_TRIPS_GRP.Trip_Out_Tab_Type;
8235:
8236: l_msg_count NUMBER;
8237: l_msg_data VARCHAR2(2000);
8238: l_return_message VARCHAR2(2000);
8650: END IF;
8651:
8652: --IF (l_cs_result_tab(rec_cnt).rank = 1) THEN
8653: -- Has to update trip
8654: -- Call WSH_TRIPS_GRP.create_update_trip to update trip if required
8655:
8656: -- AG
8657: l_trip_info_tab.DELETE;
8658: --l_trip_in_rec.caller := 'FTE_CARRIER_SELECTION_FORM';
8660: l_trip_in_rec.caller := 'FTE_ROUTING_GUIDE';
8661: l_trip_in_rec.phase := null;
8662: l_trip_in_rec.action_code := 'UPDATE';
8663:
8664: WSH_TRIPS_PVT.populate_record(
8665: p_trip_id => l_trip_id,
8666: x_trip_info => l_trip_info_tab(1),
8667: x_return_status => l_return_status);
8668:
8684: -- AG
8685: l_trip_info_tab(1).routing_rule_id := l_cs_result_tab(rec_cnt).rule_id;
8686: l_trip_info_tab(1).append_flag := 'Y';
8687:
8688: WSH_TRIPS_GRP.Create_Update_Trip(
8689: p_api_version_number => 1.0,
8690: p_init_msg_list => l_init_msg_list,
8691: p_commit => l_commit,
8692: x_return_status => l_return_status,
8869: CLOSE c_get_trip_cmove;
8870: END IF;
8871: */
8872:
8873: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.PROCESS_CARRIER_SELECTION');
8874: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8875:
8876: IF l_debug_on THEN
8877: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
8998: IF l_delivery_tab.count = 0 THEN
8999: RAISE WSH_INVALID_ACTION;
9000: END IF;
9001: IF l_debug_on THEN
9002: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_Trips_Actions.Unassign_Trip',WSH_DEBUG_SV.C_PROC_LEVEL);
9003: END IF;
9004:
9005: WSH_Trips_Actions.Unassign_Trip(
9006: p_del_rows => l_delivery_tab,
9001: IF l_debug_on THEN
9002: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_Trips_Actions.Unassign_Trip',WSH_DEBUG_SV.C_PROC_LEVEL);
9003: END IF;
9004:
9005: WSH_Trips_Actions.Unassign_Trip(
9006: p_del_rows => l_delivery_tab,
9007: p_trip_id => p_trip_id_tab(i),
9008: x_return_status => l_return_status);
9009:
9053: WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
9054: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
9055: END IF;
9056: WHEN OTHERS THEN
9057: wsh_util_core.default_handler('wsh_trips_actions',l_module_name);
9058: --
9059: IF l_debug_on THEN
9060: wsh_debug_sv.pop(l_module_name, 'EXCEPTION:OTHERS');
9061: END IF;
9062:
9063: END Remove_Consolidation;
9064: --
9065:
9066: END WSH_TRIPS_ACTIONS;