[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);
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));
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');
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
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);
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);
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);
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
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
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);
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);
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
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);
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
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
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
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
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);
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);
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;