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