[Home] [Help]
55: AND nvl(dl.shipment_direction,'O') IN ('O','IO') -- J-IB-NPARIKH
56: AND dl.delivery_type = 'STANDARD' --sperera, MDC
57: and dl.status_code IN ( 'OP', 'PA', 'SA') ;
58:
59: del_ids wsh_util_core.id_tab_type;
60: stop_ids wsh_util_core.id_tab_type;
61: dummy_ids wsh_util_core.id_tab_type;
62: l_trip_tab wsh_util_core.id_tab_type;
63:
56: AND dl.delivery_type = 'STANDARD' --sperera, MDC
57: and dl.status_code IN ( 'OP', 'PA', 'SA') ;
58:
59: del_ids wsh_util_core.id_tab_type;
60: stop_ids wsh_util_core.id_tab_type;
61: dummy_ids wsh_util_core.id_tab_type;
62: l_trip_tab wsh_util_core.id_tab_type;
63:
64: l_exceptions_tab wsh_xc_util.XC_TAB_TYPE;
57: and dl.status_code IN ( 'OP', 'PA', 'SA') ;
58:
59: del_ids wsh_util_core.id_tab_type;
60: stop_ids wsh_util_core.id_tab_type;
61: dummy_ids wsh_util_core.id_tab_type;
62: l_trip_tab wsh_util_core.id_tab_type;
63:
64: l_exceptions_tab wsh_xc_util.XC_TAB_TYPE;
65: l_exp_logged BOOLEAN := FALSE;
58:
59: del_ids wsh_util_core.id_tab_type;
60: stop_ids wsh_util_core.id_tab_type;
61: dummy_ids wsh_util_core.id_tab_type;
62: l_trip_tab wsh_util_core.id_tab_type;
63:
64: l_exceptions_tab wsh_xc_util.XC_TAB_TYPE;
65: l_exp_logged BOOLEAN := FALSE;
66: l_msg_count NUMBER;
91: WSH_DEBUG_SV.push(l_module_name);
92: WSH_DEBUG_SV.log(l_module_name,'Stop id'||p_stop_id);
93: END IF;
94: --
95: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
96:
97: savepoint sp_confirm_stop ;
98: stop_ids.delete;
99: stop_ids(1) := p_stop_id;
131: END IF;
132:
133: x_return_status := l_return_status ;
134:
135: IF x_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
136: -- Bug#5864517: Needs to call Print_Label API irrespective of p_intransit_flag and p_close_flag
137: -- flag values.
138: -- Call Print Label for WMS
139: IF l_debug_on THEN
136: -- Bug#5864517: Needs to call Print_Label API irrespective of p_intransit_flag and p_close_flag
137: -- flag values.
138: -- Call Print Label for WMS
139: IF l_debug_on THEN
140: WSH_DEBUG_SV.log(l_module_name,'Calling WSH_UTIL_CORE.Print_Label');
141: END IF;
142: WSH_UTIL_CORE.Print_Label(p_stop_ids => stop_ids,
143: p_delivery_ids => dummy_ids,
144: x_return_status => l_return_status);
138: -- Call Print Label for WMS
139: IF l_debug_on THEN
140: WSH_DEBUG_SV.log(l_module_name,'Calling WSH_UTIL_CORE.Print_Label');
141: END IF;
142: WSH_UTIL_CORE.Print_Label(p_stop_ids => stop_ids,
143: p_delivery_ids => dummy_ids,
144: x_return_status => l_return_status);
145: IF l_debug_on THEN
146: WSH_DEBUG_SV.log(l_module_name,'Return status from WSH_UTIL_CORE.Print_Label : ' || l_return_status);
142: WSH_UTIL_CORE.Print_Label(p_stop_ids => stop_ids,
143: p_delivery_ids => dummy_ids,
144: x_return_status => l_return_status);
145: IF l_debug_on THEN
146: WSH_DEBUG_SV.log(l_module_name,'Return status from WSH_UTIL_CORE.Print_Label : ' || l_return_status);
147: END IF;
148: IF l_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
149: --{
150: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING AND
144: x_return_status => l_return_status);
145: IF l_debug_on THEN
146: WSH_DEBUG_SV.log(l_module_name,'Return status from WSH_UTIL_CORE.Print_Label : ' || l_return_status);
147: END IF;
148: IF l_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
149: --{
150: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING AND
151: x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
152: x_return_status := l_return_status ;
146: WSH_DEBUG_SV.log(l_module_name,'Return status from WSH_UTIL_CORE.Print_Label : ' || l_return_status);
147: END IF;
148: IF l_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
149: --{
150: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING AND
151: x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
152: x_return_status := l_return_status ;
153: END IF;
154: ELSE
147: END IF;
148: IF l_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
149: --{
150: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING AND
151: x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
152: x_return_status := l_return_status ;
153: END IF;
154: ELSE
155: x_return_status := l_return_status ;
162: -- Bug 2887720, move End-If back up to correct place
163: END IF;
164:
165:
166: IF x_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
167:
168: IF l_debug_on THEN
169: WSH_DEBUG_SV.log(l_module_name,'In-transit flag'||p_intransit_flag);
170: WSH_DEBUG_SV.log(l_module_name,'Close flag'||p_close_flag);
197: p_logging_entity_name => 'DELIVERY',
198: p_consider_content => 'N',
199: x_exceptions_tab => l_exceptions_tab
200: );
201: WSH_UTIL_CORE.api_post_call
202: (
203: p_return_status => l_return_status,
204: x_num_warnings => l_num_warnings,
205: x_num_errors => l_num_errors
241: WSH_DEBUG_SV.log(l_module_name,'Return status from WSHSTACB.change_status : ' || l_return_status);
242: END IF;
243:
244: IF l_return_status NOT IN
245: (WSH_UTIL_CORE.G_RET_STS_ERROR,
246: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
247: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING AND
248: x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
249: x_return_status := l_return_status ;
242: END IF;
243:
244: IF l_return_status NOT IN
245: (WSH_UTIL_CORE.G_RET_STS_ERROR,
246: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
247: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING AND
248: x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
249: x_return_status := l_return_status ;
250: END IF;
243:
244: IF l_return_status NOT IN
245: (WSH_UTIL_CORE.G_RET_STS_ERROR,
246: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
247: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING AND
248: x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
249: x_return_status := l_return_status ;
250: END IF;
251:
244: IF l_return_status NOT IN
245: (WSH_UTIL_CORE.G_RET_STS_ERROR,
246: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
247: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING AND
248: x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
249: x_return_status := l_return_status ;
250: END IF;
251:
252: ELSE
257: -- on delivery.
258:
259: FND_MESSAGE.SET_NAME('WSH','WSH_EXP_COMPL_SCRN_REQD');
260: FND_MESSAGE.SET_TOKEN('DEL_NAME',l_del_name);
261: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_SUCCESS);
262:
263: END IF;
264: END IF;
265: ELSE
265: ELSE
266: x_return_status := l_return_status ;
267: END IF;
268:
269: IF x_return_status IN ( WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
270: rollback to sp_confirm_stop;
271: END IF;
272:
273: IF l_debug_on THEN
276: END IF;
277:
278: EXCEPTION
279: WHEN others THEN
280: wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.CONFIRM_STOP');
281: rollback to sp_confirm_stop;
282: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
283: IF l_debug_on THEN
284: WSH_DEBUG_SV.log(l_module_name,'Unexpected Error has Occured.Oracle error message is'||SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
278: EXCEPTION
279: WHEN others THEN
280: wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.CONFIRM_STOP');
281: rollback to sp_confirm_stop;
282: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
283: IF l_debug_on THEN
284: WSH_DEBUG_SV.log(l_module_name,'Unexpected Error has Occured.Oracle error message is'||SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
285: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
286: END IF;
288: END;
289:
290:
291: PROCEDURE Check_Update_Stops (
292: p_stop_rows IN wsh_util_core.id_tab_type,
293: p_action IN VARCHAR2,
294: --tkt
295: p_caller IN VARCHAR2,
296: x_return_status OUT NOCOPY VARCHAR2) IS
349: IF l_debug_on THEN
350: WSH_DEBUG_SV.push(l_module_name);
351: END IF;
352: --
353: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
354:
355: IF (p_stop_rows.count = 0) THEN
356: RAISE others;
357: END IF;
367: CLOSE stop_info;
368:
369: IF (l_trip_id IS NULL) THEN
370: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
371: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
372: wsh_util_core.add_message(x_return_status);
373: --
374: -- Debug Statements
375: --
368:
369: IF (l_trip_id IS NULL) THEN
370: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
371: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
372: wsh_util_core.add_message(x_return_status);
373: --
374: -- Debug Statements
375: --
376: IF l_debug_on THEN
381: END IF;
382:
383: IF (l_trip_id = l_old_trip_id) THEN
384: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_UPDATE_SAME_TRIP');
385: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
386: wsh_util_core.add_message(x_return_status);
387: --
388: -- Debug Statements
389: --
382:
383: IF (l_trip_id = l_old_trip_id) THEN
384: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_UPDATE_SAME_TRIP');
385: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
386: wsh_util_core.add_message(x_return_status);
387: --
388: -- Debug Statements
389: --
390: IF l_debug_on THEN
403: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
404: END IF;
405: --
406: FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(p_stop_rows(i),p_caller));
407: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
408: wsh_util_core.add_message(x_return_status);
409: --
410: -- Debug Statements
411: --
404: END IF;
405: --
406: FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(p_stop_rows(i),p_caller));
407: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
408: wsh_util_core.add_message(x_return_status);
409: --
410: -- Debug Statements
411: --
412: IF l_debug_on THEN
435: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
436: END IF;
437: --
438: FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(p_stop_rows(i),p_caller));
439: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
440: wsh_util_core.add_message(x_return_status);
441: --
442: -- Debug Statements
443: --
436: END IF;
437: --
438: FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(p_stop_rows(i),p_caller));
439: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
440: wsh_util_core.add_message(x_return_status);
441: --
442: -- Debug Statements
443: --
444: IF l_debug_on THEN
461: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
462: END IF;
463: --
464: FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(p_stop_rows(i),p_caller));
465: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
466: wsh_util_core.add_message(x_return_status);
467:
468: END IF;
469: CLOSE stop_pickups;
462: END IF;
463: --
464: FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(p_stop_rows(i),p_caller));
465: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
466: wsh_util_core.add_message(x_return_status);
467:
468: END IF;
469: CLOSE stop_pickups;
470: END IF;
484: END IF;
485: --
486: EXCEPTION
487: WHEN others THEN
488: wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.CHECK_UPDATE');
489: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
490:
491: --
492: -- Debug Statements
485: --
486: EXCEPTION
487: WHEN others THEN
488: wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.CHECK_UPDATE');
489: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
490:
491: --
492: -- Debug Statements
493: --
498: --
499: END Check_Update_Stops;
500:
501: PROCEDURE Change_Status (
502: p_stop_rows IN wsh_util_core.id_tab_type,
503: p_action IN VARCHAR2,
504: p_actual_date IN DATE,
505: p_defer_interface_flag IN VARCHAR2, -- bug 1578251
506: x_return_status OUT NOCOPY VARCHAR2,
530: l_msg_data VARCHAR2(4000);
531: --
532: l_debug_on BOOLEAN;
533: --
534: l_stop_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
535: l_dbi_rs VARCHAR2(1); -- DBI Project
536: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHANGE_STATUS';
537: l_wf_rs VARCHAR2(1); -- Workflow Project
538:
558: WSH_DEBUG_SV.log(l_module_name,'P_ACTUAL_DATE',P_ACTUAL_DATE);
559: WSH_DEBUG_SV.log(l_module_name,'P_DEFER_INTERFACE_FLAG',P_DEFER_INTERFACE_FLAG);
560: END IF;
561: --
562: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
563: l_num_warnings := 0;
564: l_num_errors := 0;
565: --
566: l_stopsProcessed := 0;
613: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
614: WSH_DEBUG_SV.log(l_module_name,'l_out_rec.close_Allowed',l_out_rec.close_Allowed);
615: END IF;
616: --
617: WSH_UTIL_CORE.api_post_call
618: (
619: p_return_status => l_return_status,
620: x_num_warnings => l_num_warnings,
621: x_num_errors => l_num_errors
630: ELSIF l_out_rec.close_Allowed = 'NW'
631: THEN
632: l_num_warnings := l_num_warnings + 1;
633: l_stopsNotProcessedWarnings := l_stopsNotProcessedWarnings + 1;
634: RAISE wsh_util_core.g_exc_warning;
635: ELSE
636: l_num_errors := l_num_errors + 1;
637: RAISE FND_API.G_EXC_ERROR;
638: END IF;
649: p_defer_interface_flag => p_defer_interface_flag,
650: x_return_status => l_return_status
651: );
652: --
653: WSH_UTIL_CORE.api_post_call
654: (
655: p_return_status => l_return_status,
656: x_num_warnings => l_num_warnings,
657: x_num_errors => l_num_errors
683: p_consider_content => 'Y',
684: p_caller => p_caller
685: ) ;
686:
687: WSH_UTIL_CORE.api_post_call
688: (
689: p_return_status => l_return_status,
690: x_num_warnings => l_num_warnings,
691: x_num_errors => l_num_errors
689: p_return_status => l_return_status,
690: x_num_warnings => l_num_warnings,
691: x_num_errors => l_num_errors
692: );
693: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
694: RAISE FND_API.G_EXC_ERROR;
695: END IF;
696:
697: --}
715: x_return_status => l_return_status
716: );
717: --
718: --
719: WSH_UTIL_CORE.api_post_call
720: (
721: p_return_status => l_return_status,
722: x_num_warnings => l_num_warnings,
723: x_num_errors => l_num_errors
731:
732: --
733: IF (SQL%NOTFOUND) THEN
734: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
735: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
736: l_num_errors := l_num_errors + 1;
737: RAISE FND_API.G_EXC_ERROR;
738: END IF;
739: --
782:
783: IF l_debug_on THEN
784: WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
785: END IF;
786: IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
787: x_return_status := l_dbi_rs;
788: ROLLBACK TO stop_chg_status_sp;
789: -- just pass this return status to caller API
790: IF l_debug_on THEN
826: --
827: FND_MESSAGE.SET_TOKEN('stop_name',wsh_trip_stops_pvt.get_name(p_stop_rows(i),p_caller));
828: END IF;
829: --
830: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
831: --}
832: WHEN wsh_util_core.g_exc_warning
833: THEN
834: --{
828: END IF;
829: --
830: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
831: --}
832: WHEN wsh_util_core.g_exc_warning
833: THEN
834: --{
835: ROLLBACK to stop_chg_status_sp;
836: --
839: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_CLOSE_ERROR');
840: FND_MESSAGE.SET_TOKEN('STOP_NAME', l_out_rec.stop_name);
841: END IF;
842: --
843: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING,l_module_name);
844: --}
845: --}
846: END;
847: --}
859:
860: IF (l_stopsProcessed = 0 AND l_stopsProcessedWarnings = 0)
861: OR l_num_errors > 0
862: THEN
863: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
864: ELSIF l_stopsProcessedWarnings > 0
865: OR l_stopsNotProcessedWarnings > 0
866: OR l_num_warnings > 0
867: THEN
864: ELSIF l_stopsProcessedWarnings > 0
865: OR l_stopsNotProcessedWarnings > 0
866: OR l_num_warnings > 0
867: THEN
868: l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
869: ELSE
870: l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
871: END IF;
872: --
866: OR l_num_warnings > 0
867: THEN
868: l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
869: ELSE
870: l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
871: END IF;
872: --
873: x_return_status := l_return_status;
874: --
871: END IF;
872: --
873: x_return_status := l_return_status;
874: --
875: IF ( p_stop_rows.count > 1 AND l_stopsProcessed <> p_stop_rows.count) OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
876: THEN
877: --{
878: IF (p_action = 'CLOSE') THEN
879: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_CLOSE_SUMMARY');
892: ELSE
893: FND_MESSAGE.SET_TOKEN('NUM_WARN',l_stopsProcessedWarnings+l_stopsNotProcessedWarnings);
894: END IF;
895: --
896: wsh_util_core.add_message(l_return_status,l_module_name);
897: --}
898: END IF;
899: --
900: -- Debug Statements
906: EXCEPTION
907: WHEN FND_API.G_EXC_ERROR THEN
908: ROLLBACK TO stop_chg_status_begin_sp;
909: --
910: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
911: --
912: IF l_debug_on THEN
913: WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
914: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
917: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
918:
919: ROLLBACK TO stop_chg_status_begin_sp;
920: --
921: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
922: --
923: IF l_debug_on THEN
924: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
925: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
924: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
925: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
926: END IF;
927: --
928: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
929: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
930: --
931: IF l_debug_on THEN
932: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
925: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
926: END IF;
927: --
928: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
929: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
930: --
931: IF l_debug_on THEN
932: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
933: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
928: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
929: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
930: --
931: IF l_debug_on THEN
932: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
933: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
934: END IF;
935: --
936: WHEN others THEN
929: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
930: --
931: IF l_debug_on THEN
932: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
933: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
934: END IF;
935: --
936: WHEN others THEN
937:
934: END IF;
935: --
936: WHEN others THEN
937:
938: wsh_util_core.default_handler('WSH_NEW_DELIVERY_ACTIONS.CHANGE_STATUS',l_module_name);
939: ROLLBACK TO stop_chg_status_begin_sp;
940: --
941: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
942:
937:
938: wsh_util_core.default_handler('WSH_NEW_DELIVERY_ACTIONS.CHANGE_STATUS',l_module_name);
939: ROLLBACK TO stop_chg_status_begin_sp;
940: --
941: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
942:
943: --
944: -- Debug Statements
945: --
952: --wr removed the old I procedure that had been commented with
953: -- instructions to remove after ut.
954:
955: PROCEDURE Plan (
956: p_stop_rows IN wsh_util_core.id_tab_type,
957: p_action IN VARCHAR2,
958: p_caller IN VARCHAR2,
959: x_return_status OUT NOCOPY VARCHAR2) IS
960:
974:
975: j BINARY_INTEGER;
976: l_num_error BINARY_INTEGER := 0;
977: l_num_warn BINARY_INTEGER := 0;
978: l_del_rows wsh_util_core.id_tab_type;
979: l_stop_status VARCHAR2(2);
980: l_trip_status VARCHAR2(2);
981: l_stop_id NUMBER;
982:
1013:
1014: FOR i IN 1..p_stop_rows.count LOOP
1015:
1016: j := 0;
1017: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1018: --removed code checking for closed status as now firm/plan/unfirm can be done at any stage
1019:
1020: FOR s IN stop_dels(p_stop_rows(i)) LOOP
1021:
1045: --
1046: wsh_new_delivery_actions.unplan(l_del_rows, x_return_status);
1047: END IF;
1048: ELSE
1049: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1050: END IF;
1051:
1052: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1053:
1048: ELSE
1049: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1050: END IF;
1051:
1052: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1053:
1054: IF (p_action = 'PLAN') THEN
1055: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_PLAN_ERROR');
1056: ELSE
1064: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1065: END IF;
1066: --
1067: FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(p_stop_rows(i),p_caller));
1068: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1069: wsh_util_core.add_message(x_return_status);
1070: l_num_error := l_num_error + 1;
1071:
1072: ELSIF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1065: END IF;
1066: --
1067: FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(p_stop_rows(i),p_caller));
1068: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1069: wsh_util_core.add_message(x_return_status);
1070: l_num_error := l_num_error + 1;
1071:
1072: ELSIF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1073: l_num_warn := l_num_warn + 1;
1068: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1069: wsh_util_core.add_message(x_return_status);
1070: l_num_error := l_num_error + 1;
1071:
1072: ELSIF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1073: l_num_warn := l_num_warn + 1;
1074: END IF;
1075:
1076: END LOOP;
1087:
1088: FND_MESSAGE.SET_TOKEN('NUM_ERROR',l_num_error);
1089: FND_MESSAGE.SET_TOKEN('NUM_WARN',l_num_warn);
1090: FND_MESSAGE.SET_TOKEN('NUM_SUCCESS',p_stop_rows.count - l_num_error - l_num_warn);
1091: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1092: /*
1093: IF (p_stop_rows.count = l_num_error) THEN
1094: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1095: ELSE
1090: FND_MESSAGE.SET_TOKEN('NUM_SUCCESS',p_stop_rows.count - l_num_error - l_num_warn);
1091: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1092: /*
1093: IF (p_stop_rows.count = l_num_error) THEN
1094: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1095: ELSE
1096: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1097: END IF;
1098: */
1092: /*
1093: IF (p_stop_rows.count = l_num_error) THEN
1094: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1095: ELSE
1096: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1097: END IF;
1098: */
1099: wsh_util_core.add_message(x_return_status);
1100: ELSE
1095: ELSE
1096: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1097: END IF;
1098: */
1099: wsh_util_core.add_message(x_return_status);
1100: ELSE
1101: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1102: END IF;
1103:
1097: END IF;
1098: */
1099: wsh_util_core.add_message(x_return_status);
1100: ELSE
1101: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1102: END IF;
1103:
1104: END IF;
1105:
1118: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_UNPLAN_ERROR');
1119: END IF;
1120:
1121: FND_MESSAGE.SET_TOKEN('STOP_NAME',l_stop_id);
1122: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1123: wsh_util_core.add_message(x_return_status);
1124: --
1125: -- Debug Statements
1126: --
1119: END IF;
1120:
1121: FND_MESSAGE.SET_TOKEN('STOP_NAME',l_stop_id);
1122: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1123: wsh_util_core.add_message(x_return_status);
1124: --
1125: -- Debug Statements
1126: --
1127: IF l_debug_on THEN
1129: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INVALID_STOP');
1130: END IF;
1131: --
1132: WHEN others THEN
1133: wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.CHANGE_STATUS');
1134: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1135:
1136:
1137: --
1130: END IF;
1131: --
1132: WHEN others THEN
1133: wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.CHANGE_STATUS');
1134: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1135:
1136:
1137: --
1138: -- Debug Statements
1150: -- Procedure to calculate the Stop departure weight, volume
1151: -- Input parameter stop id, gets trip id and calls the trip weight, volume
1152: -- calculation API (more efficient)
1153:
1154: PROCEDURE calc_stop_weight_volume( p_stop_rows IN wsh_util_core.id_tab_type,
1155: p_override_flag IN VARCHAR2,
1156: p_calc_wv_if_frozen IN VARCHAR2,
1157: x_return_status OUT NOCOPY VARCHAR2,
1158: --tkt
1156: p_calc_wv_if_frozen IN VARCHAR2,
1157: x_return_status OUT NOCOPY VARCHAR2,
1158: --tkt
1159: p_caller IN VARCHAR2) IS
1160: dummy_trip_ids wsh_util_core.id_tab_type;
1161: l_stop_name VARCHAR2(100);
1162: l_override_flag VARCHAR2(1);
1163: l_suppress_errors VARCHAR2(1);
1164:
1185: WSH_DEBUG_SV.log(l_module_name,'P_OVERRIDE_FLAG',P_OVERRIDE_FLAG);
1186: WSH_DEBUG_SV.log(l_module_name,'P_CALC_WV_IF_FROZEN',P_CALC_WV_IF_FROZEN);
1187: END IF;
1188: --
1189: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1190:
1191: -- bug 2366163: overload p_override_flag 'X' to suppress errors
1192: IF p_override_flag = 'X' THEN
1193: l_override_flag := 'N';
1197: l_suppress_errors := NULL;
1198: END IF;
1199:
1200: IF (p_stop_rows.count = 0) THEN
1201: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1202: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
1203: wsh_util_core.add_message(x_return_status);
1204: ELSE
1205:
1199:
1200: IF (p_stop_rows.count = 0) THEN
1201: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1202: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
1203: wsh_util_core.add_message(x_return_status);
1204: ELSE
1205:
1206: FOR i IN 1..p_stop_rows.count LOOP
1207:
1236: END IF;
1237: --
1238: EXCEPTION
1239: WHEN others THEN
1240: wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.CALC_STOP_WEIGHT_VOLUME');
1241: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1242:
1243: --
1244: -- Debug Statements
1237: --
1238: EXCEPTION
1239: WHEN others THEN
1240: wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.CALC_STOP_WEIGHT_VOLUME');
1241: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1242:
1243: --
1244: -- Debug Statements
1245: --
1319: WSH_DEBUG_SV.log(l_module_name,'P_in_rec.actual_date',P_in_rec.actual_date);
1320: WSH_DEBUG_SV.log(l_module_name,'p_reopenStop',p_reopenStop);
1321: END IF;
1322: --
1323: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1324: x_stop_processed := 'N';
1325: --
1326: l_in_rec := p_in_rec;
1327: l_in_rec.put_messages := FALSE;
1342: x_out_rec => l_out_rec,
1343: x_return_status => l_return_status
1344: );
1345: --
1346: WSH_UTIL_CORE.api_post_call
1347: (
1348: p_return_status => l_return_status,
1349: x_num_warnings => l_num_warnings,
1350: x_num_errors => l_num_errors
1370: x_stop_close_date => l_actual_date,
1371: x_return_status => l_return_status
1372: );
1373: --
1374: WSH_UTIL_CORE.api_post_call
1375: (
1376: p_return_status => l_return_status,
1377: x_num_warnings => l_num_warnings,
1378: x_num_errors => l_num_errors
1394: p_defer_interface_flag => 'Y',
1395: x_return_status => l_return_status
1396: );
1397: --
1398: WSH_UTIL_CORE.api_post_call
1399: (
1400: p_return_status => l_return_status,
1401: x_num_warnings => l_num_warnings,
1402: x_num_errors => l_num_errors
1425: p_in_rec1 => l_out_rec,
1426: x_return_status => l_return_status
1427: );
1428: --
1429: WSH_UTIL_CORE.api_post_call
1430: (
1431: p_return_status => l_return_status,
1432: x_num_warnings => l_num_warnings,
1433: x_num_errors => l_num_errors
1439: --
1440: --
1441: IF l_num_errors > 0
1442: THEN
1443: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1444: ELSIF l_num_warnings > 0
1445: THEN
1446: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1447: ELSE
1442: THEN
1443: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1444: ELSIF l_num_warnings > 0
1445: THEN
1446: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1447: ELSE
1448: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1449: END IF;
1450: --
1444: ELSIF l_num_warnings > 0
1445: THEN
1446: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1447: ELSE
1448: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1449: END IF;
1450: --
1451: --
1452: -- Debug Statements
1458: EXCEPTION
1459: --
1460: WHEN FND_API.G_EXC_ERROR THEN
1461:
1462: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1463: --
1464: IF l_debug_on THEN
1465: WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1466: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1467: END IF;
1468: --
1469: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1470:
1471: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1472: --
1473: IF l_debug_on THEN
1474: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1475: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1474: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1475: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1476: END IF;
1477: --
1478: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1479: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1480: --
1481: IF l_debug_on THEN
1482: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1475: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1476: END IF;
1477: --
1478: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1479: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1480: --
1481: IF l_debug_on THEN
1482: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1483: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1478: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1479: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1480: --
1481: IF l_debug_on THEN
1482: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1483: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1484: END IF;
1485: --
1486: WHEN OTHERS THEN
1479: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1480: --
1481: IF l_debug_on THEN
1482: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1483: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1484: END IF;
1485: --
1486: WHEN OTHERS THEN
1487:
1484: END IF;
1485: --
1486: WHEN OTHERS THEN
1487:
1488: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1489: wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.autoCloseOpen', l_module_name);
1490: IF l_debug_on THEN
1491: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1492: END IF;
1485: --
1486: WHEN OTHERS THEN
1487:
1488: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1489: wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.autoCloseOpen', l_module_name);
1490: IF l_debug_on THEN
1491: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1492: END IF;
1493: --
1542: l_cnt NUMBER := 0;
1543: l_err_cnt NUMBER := 0;
1544: l_index NUMBER;
1545: --
1546: l_trip_tab wsh_util_core.id_tab_type;
1547: l_err_dlvy_id_tbl wsh_util_core.id_tab_type;
1548: l_dlvy_id_tbl wsh_util_core.id_tab_type;
1549: l_dlvy_orgid_tbl wsh_util_core.id_tab_type;
1550: l_warehouse_type VARCHAR2(30);
1543: l_err_cnt NUMBER := 0;
1544: l_index NUMBER;
1545: --
1546: l_trip_tab wsh_util_core.id_tab_type;
1547: l_err_dlvy_id_tbl wsh_util_core.id_tab_type;
1548: l_dlvy_id_tbl wsh_util_core.id_tab_type;
1549: l_dlvy_orgid_tbl wsh_util_core.id_tab_type;
1550: l_warehouse_type VARCHAR2(30);
1551: l_cms_flag VARCHAR2(1);
1544: l_index NUMBER;
1545: --
1546: l_trip_tab wsh_util_core.id_tab_type;
1547: l_err_dlvy_id_tbl wsh_util_core.id_tab_type;
1548: l_dlvy_id_tbl wsh_util_core.id_tab_type;
1549: l_dlvy_orgid_tbl wsh_util_core.id_tab_type;
1550: l_warehouse_type VARCHAR2(30);
1551: l_cms_flag VARCHAR2(1);
1552: l_in_rec WSH_DELIVERY_VALIDATIONS.ChgStatus_in_rec_type;
1545: --
1546: l_trip_tab wsh_util_core.id_tab_type;
1547: l_err_dlvy_id_tbl wsh_util_core.id_tab_type;
1548: l_dlvy_id_tbl wsh_util_core.id_tab_type;
1549: l_dlvy_orgid_tbl wsh_util_core.id_tab_type;
1550: l_warehouse_type VARCHAR2(30);
1551: l_cms_flag VARCHAR2(1);
1552: l_in_rec WSH_DELIVERY_VALIDATIONS.ChgStatus_in_rec_type;
1553: l_trip_in_rec WSH_TRIP_VALIDATIONS.ChgStatus_in_rec_type;
1565:
1566: l_action_params WSH_FTE_INTEGRATION.rating_action_param_rec;
1567: l_msg_count NUMBER;
1568: l_msg_data VARCHAR2(2000);
1569: l_stop_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
1570: l_dbi_rs VARCHAR2(1); -- DBI Project
1571: l_wf_rs VARCHAR2(1); -- Workflow Project
1572:
1573: --OTM R12, glog proj
1609: WSH_DEBUG_SV.log(l_module_name,'P_in_rec1.mode_of_transport',P_in_rec1.mode_of_transport);
1610:
1611: END IF;
1612: --
1613: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1614:
1615: --OTM R12, glog proj, use Global Variable
1616: l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
1617:
1612: --
1613: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1614:
1615: --OTM R12, glog proj, use Global Variable
1616: l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
1617:
1618: -- If null, call the function
1619: IF l_gc3_is_installed IS NULL THEN
1620: l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
1616: l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
1617:
1618: -- If null, call the function
1619: IF l_gc3_is_installed IS NULL THEN
1620: l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
1621: END IF;
1622: IF l_debug_on THEN
1623: WSH_DEBUG_SV.log(l_module_name,'l_gc3_is_installed ',l_gc3_is_installed);
1624: END IF;
1664: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1665: END IF;
1666: --
1667: --
1668: wsh_util_core.api_post_call
1669: (
1670: p_return_status => l_return_status,
1671: x_num_warnings => l_num_warnings,
1672: x_num_errors => l_num_errors,
1673: p_raise_error_flag => FALSE
1674: );
1675: --
1676: --
1677: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
1678: THEN
1679: --{
1680: l_err_cnt := l_err_cnt + 1;
1681: --
1724: x_return_status => l_return_status
1725: );
1726: --
1727: --
1728: wsh_util_core.api_post_call
1729: (
1730: p_return_status => l_return_status,
1731: x_num_warnings => l_num_warnings,
1732: x_num_errors => l_num_errors
1755: p_dropoff_stop_id => NULL,
1756: x_return_status => l_return_status
1757: );
1758: --
1759: wsh_util_core.api_post_call
1760: (
1761: p_return_status => l_return_status,
1762: x_num_warnings => l_num_warnings,
1763: x_num_errors => l_num_errors
1788: p_entity_type => 'TRIP',
1789: p_entity_ids => l_trip_tab,
1790: x_return_status => l_return_status);
1791: --
1792: wsh_util_core.api_post_call
1793: (
1794: p_return_status => l_return_status,
1795: x_num_warnings => l_num_warnings,
1796: x_num_errors => l_num_errors
1800: FND_MESSAGE.SET_TOKEN('STOP_NAME', p_in_rec1.stop_name);
1801: FND_MESSAGE.SET_TOKEN('NUM_SUCCESS', l_cnt);
1802: FND_MESSAGE.SET_TOKEN('NUM_ERROR', l_err_cnt+p_in_rec1.initial_pu_err_dlvy_id_tbl.COUNT);
1803: l_num_warnings := l_num_warnings + 1;
1804: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING);
1805: --}
1806: END IF;
1807: --
1808: IF l_cnt > 0
1852: WSH_DEBUG_SV.log(l_module_name,'l_last_pickup_stop_closed',l_last_pickup_stop_closed);
1853: WSH_DEBUG_SV.log(l_module_name,'l_eligible_for_asr',l_eligible_for_asr);
1854: END IF;
1855:
1856: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1857: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1858:
1859: IF l_debug_on THEN
1860: wsh_debug_sv.log(l_module_name,'last_pickup_stop_closed return error');
1853: WSH_DEBUG_SV.log(l_module_name,'l_eligible_for_asr',l_eligible_for_asr);
1854: END IF;
1855:
1856: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1857: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1858:
1859: IF l_debug_on THEN
1860: wsh_debug_sv.log(l_module_name,'last_pickup_stop_closed return error');
1861: END IF;
1858:
1859: IF l_debug_on THEN
1860: wsh_debug_sv.log(l_module_name,'last_pickup_stop_closed return error');
1861: END IF;
1862: wsh_util_core.add_message(l_return_status);
1863: RAISE FND_API.G_EXC_ERROR;
1864: END IF;
1865: IF l_last_pickup_stop_closed = 'Y' AND l_eligible_for_asr = 'Y' THEN --{
1866: l_tms_interface_flag := WSH_TRIP_STOPS_PVT.C_TMS_ACTUAL_REQUEST;
1892: IF (SQL%NOTFOUND)
1893: THEN
1894: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_EXIST');
1895: FND_MESSAGE.SET_TOKEN('STOP_ID',p_in_rec.stop_id);
1896: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
1897: RAISE FND_API.G_EXC_ERROR;
1898: END IF;
1899: --
1900: --
1948:
1949: IF l_debug_on THEN
1950: WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
1951: END IF;
1952: IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1953: x_return_status := l_dbi_rs;
1954: -- just pass this return status to caller API
1955: IF l_debug_on THEN
1956: WSH_DEBUG_SV.log(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
1962: --
1963:
1964: --
1965: /* H integration: Pricing integration csun */
1966: IF WSH_UTIL_CORE.FTE_Is_Installed = 'Y'
1967: THEN
1968: --{
1969: -- Bug fix 2489164
1970: -- Find if the deliveries in the stop are carrier manifest enabled
1995: wsh_debug_sv.log(l_module_name, 'Warehouse Type', l_warehouse_type);
1996: END IF;
1997: --
1998: --
1999: wsh_util_core.api_post_call
2000: (
2001: p_return_status => l_return_status,
2002: x_num_warnings => l_num_warnings,
2003: x_num_errors => l_num_errors
2034: wsh_debug_sv.log(l_module_name, 'Warehouse Type', l_warehouse_type);
2035: END IF;
2036: --
2037: --
2038: wsh_util_core.api_post_call
2039: (
2040: p_return_status => l_return_status,
2041: x_num_warnings => l_num_warnings,
2042: x_num_errors => l_num_errors
2086: x_return_status => l_return_status,
2087: x_msg_count => l_msg_count,
2088: x_msg_data => l_msg_data);
2089: --
2090: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
2091: THEN
2092: FND_MESSAGE.SET_NAME('WSH','WSH_PRICE_CNSLD_UNEXP_ERROR');
2093: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_in_rec1.trip_id));
2094: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
2090: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
2091: THEN
2092: FND_MESSAGE.SET_NAME('WSH','WSH_PRICE_CNSLD_UNEXP_ERROR');
2093: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_in_rec1.trip_id));
2094: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
2095: RAISE FND_API.G_EXC_ERROR;
2096: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
2097: -- Bug 3278059, treat warning from rate_trip as success
2098: THEN
2092: FND_MESSAGE.SET_NAME('WSH','WSH_PRICE_CNSLD_UNEXP_ERROR');
2093: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_in_rec1.trip_id));
2094: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
2095: RAISE FND_API.G_EXC_ERROR;
2096: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
2097: -- Bug 3278059, treat warning from rate_trip as success
2098: THEN
2099: --{
2100: l_num_warnings := l_num_warnings + 1;
2100: l_num_warnings := l_num_warnings + 1;
2101: --
2102: FND_MESSAGE.SET_NAME('WSH','WSH_PRICE_CNSLD_ERROR');
2103: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_in_rec1.trip_id));
2104: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING,l_module_name);
2105: --}
2106: END IF;
2107: --}
2108: END IF;
2134: wsh_debug_sv.log(l_module_name, 'Return status ship_confirm_a_trip_stop', l_return_status);
2135: END IF;
2136: --
2137: --
2138: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
2139: THEN
2140: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2141: ELSIF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2142: THEN
2137: --
2138: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
2139: THEN
2140: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2141: ELSIF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2142: THEN
2143: --{
2144: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING
2145: THEN
2140: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2141: ELSIF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2142: THEN
2143: --{
2144: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING
2145: THEN
2146: l_num_warnings := l_num_warnings + 1;
2147: ELSE
2148: l_num_errors := l_num_errors + 1;
2150: --
2151: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_CONFIRM_ERROR');
2152: FND_MESSAGE.SET_TOKEN('STOP_NAME',p_in_rec1.stop_name);
2153: FND_MESSAGE.SET_TOKEN('TRIP_NAME',p_in_rec1.trip_name);
2154: wsh_util_core.add_message(l_return_status,l_module_name);
2155: --}
2156: END IF;
2157: --}
2158: END IF;
2192: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2193: END IF;
2194: --
2195: --
2196: wsh_util_core.api_post_call
2197: (
2198: p_return_status => l_return_status,
2199: x_num_warnings => l_num_warnings,
2200: x_num_errors => l_num_errors
2231: p_in_rec => l_trip_in_rec,
2232: x_return_status => l_return_status
2233: );
2234: --
2235: wsh_util_core.api_post_call
2236: (
2237: p_return_status => l_return_status,
2238: x_num_warnings => l_num_warnings,
2239: x_num_errors => l_num_errors
2246: --
2247: --
2248: IF l_num_errors > 0
2249: THEN
2250: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2251: ELSIF l_num_warnings > 0
2252: THEN
2253: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2254: ELSE
2249: THEN
2250: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2251: ELSIF l_num_warnings > 0
2252: THEN
2253: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2254: ELSE
2255: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2256: END IF;
2257: --
2251: ELSIF l_num_warnings > 0
2252: THEN
2253: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2254: ELSE
2255: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2256: END IF;
2257: --
2258: --
2259: --
2269: --
2270: WHEN FND_API.G_EXC_ERROR THEN
2271: --ROLLBACK TO close_stop_begin_sp;
2272: --
2273: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2274: --
2275: IF l_debug_on THEN
2276: WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2277: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2279: --
2280: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2281: --ROLLBACK TO close_stop_begin_sp;
2282: --
2283: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2284: --
2285: IF l_debug_on THEN
2286: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2287: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2286: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2287: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2288: END IF;
2289: --
2290: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
2291: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2292: --
2293: IF l_debug_on THEN
2294: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2287: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2288: END IF;
2289: --
2290: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
2291: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2292: --
2293: IF l_debug_on THEN
2294: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2295: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2290: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
2291: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2292: --
2293: IF l_debug_on THEN
2294: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2295: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2296: END IF;
2297: WHEN others THEN
2298: wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.setClose',l_module_name);
2291: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2292: --
2293: IF l_debug_on THEN
2294: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2295: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2296: END IF;
2297: WHEN others THEN
2298: wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.setClose',l_module_name);
2299: --
2294: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2295: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2296: END IF;
2297: WHEN others THEN
2298: wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.setClose',l_module_name);
2299: --
2300: --ROLLBACK TO close_stop_begin_sp;
2301: --
2302: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2298: wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.setClose',l_module_name);
2299: --
2300: --ROLLBACK TO close_stop_begin_sp;
2301: --
2302: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2303: --
2304: -- Debug Statements
2305: --
2306: IF l_debug_on THEN
2432: l_allowed VARCHAR2(10);
2433: --
2434: l_trip_in_rec WSH_TRIP_VALIDATIONS.ChgStatus_in_rec_type;
2435: l_stop_in_rec WSH_TRIP_STOPS_VALIDATIONS.chkClose_in_rec_type;
2436: l_stop_id_tbl WSH_UTIL_CORE.id_tab_type;
2437: l_stop_cnt NUMBER := 0;
2438: l_index NUMBER;
2439: l_stop_id NUMBER;
2440: l_stop_locationId NUMBER;
2440: l_stop_locationId NUMBER;
2441: --
2442: l_debug_on BOOLEAN;
2443: --
2444: l_stop_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
2445: l_dbi_rs VARCHAR2(1); -- DBI Project
2446: l_wf_rs VARCHAR2(1); -- Workflow Project
2447: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'setOpen';
2448: --
2470: WSH_DEBUG_SV.log(l_module_name,'P_in_rec.actual_date',P_in_rec.actual_date);
2471: WSH_DEBUG_SV.log(l_module_name,'P_in_rec1.trip_status_code',P_in_rec1.trip_status_code);
2472: END IF;
2473: --
2474: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2475: --
2476: --
2477: l_num_warnings := 0;
2478: l_num_errors := 0;
2494: IF (SQL%NOTFOUND)
2495: THEN
2496: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_EXIST');
2497: FND_MESSAGE.SET_TOKEN('STOP_ID',p_in_rec.stop_id);
2498: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
2499: RAISE FND_API.G_EXC_ERROR;
2500: END IF;
2501: --
2502:
2530:
2531: IF l_debug_on THEN
2532: WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
2533: END IF;
2534: IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2535: x_return_status := l_dbi_rs;
2536: -- just pass this return status to caller API
2537: IF l_debug_on THEN
2538: WSH_DEBUG_SV.log(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
2582: x_stop_processed => l_stop_opened,
2583: x_return_status => l_return_status
2584: );
2585: --
2586: WSH_UTIL_CORE.api_post_call
2587: (
2588: p_return_status => l_return_status,
2589: x_num_warnings => l_num_warnings,
2590: x_num_errors => l_num_errors
2652: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2653: WSH_DEBUG_SV.log(l_module_name,'l_Allowed',l_Allowed);
2654: END IF;
2655: --
2656: wsh_util_core.api_post_call
2657: (
2658: p_return_status => l_return_status,
2659: x_num_warnings => l_num_warnings,
2660: x_num_errors => l_num_errors
2702: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2703: WSH_DEBUG_SV.log(l_module_name,'l_Allowed',l_Allowed);
2704: END IF;
2705: --
2706: wsh_util_core.api_post_call
2707: (
2708: p_return_status => l_return_status,
2709: x_num_warnings => l_num_warnings,
2710: x_num_errors => l_num_errors
2746: p_in_rec => l_trip_in_rec,
2747: x_return_status => l_return_status
2748: );
2749: --
2750: wsh_util_core.api_post_call
2751: (
2752: p_return_status => l_return_status,
2753: x_num_warnings => l_num_warnings,
2754: x_num_errors => l_num_errors
2805: x_stop_processed => l_stop_opened,
2806: x_return_status => l_return_status
2807: );
2808: --
2809: WSH_UTIL_CORE.api_post_call
2810: (
2811: p_return_status => l_return_status,
2812: x_num_warnings => l_num_warnings,
2813: x_num_errors => l_num_errors
2839: x_stop_processed => l_stop_opened,
2840: x_return_status => l_return_status
2841: );
2842: --
2843: WSH_UTIL_CORE.api_post_call
2844: (
2845: p_return_status => l_return_status,
2846: x_num_warnings => l_num_warnings,
2847: x_num_errors => l_num_errors
2867: --
2868: --
2869: IF l_num_errors > 0
2870: THEN
2871: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2872: ELSE
2873: --{
2874: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_OPEN_MESSAGE');
2875: FND_MESSAGE.SET_TOKEN('STOP_NAME',p_in_rec1.stop_name);
2873: --{
2874: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_OPEN_MESSAGE');
2875: FND_MESSAGE.SET_TOKEN('STOP_NAME',p_in_rec1.stop_name);
2876: FND_MESSAGE.SET_TOKEN('TRIP_NAME',p_in_rec1.trip_name);
2877: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_SUCCESS,l_module_name);
2878: --
2879: IF l_num_warnings > 0
2880: THEN
2881: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2877: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_SUCCESS,l_module_name);
2878: --
2879: IF l_num_warnings > 0
2880: THEN
2881: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2882: ELSE
2883: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2884: END IF;
2885: --}
2879: IF l_num_warnings > 0
2880: THEN
2881: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2882: ELSE
2883: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2884: END IF;
2885: --}
2886: END IF;
2887: --
2900: WHEN FND_API.G_EXC_ERROR THEN
2901:
2902: --ROLLBACK TO open_stop_begin_sp;
2903: --
2904: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2905: --
2906: IF l_debug_on THEN
2907: WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2908: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2911: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2912:
2913: --ROLLBACK TO open_stop_begin_sp;
2914: --
2915: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2916: --
2917: IF l_debug_on THEN
2918: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2919: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2918: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2919: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2920: END IF;
2921: --
2922: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
2923: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2924: --
2925: IF l_debug_on THEN
2926: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2919: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2920: END IF;
2921: --
2922: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
2923: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2924: --
2925: IF l_debug_on THEN
2926: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2927: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2922: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
2923: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2924: --
2925: IF l_debug_on THEN
2926: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2927: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2928: END IF;
2929: WHEN others THEN
2930: wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.setOpen',l_module_name);
2923: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2924: --
2925: IF l_debug_on THEN
2926: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2927: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2928: END IF;
2929: WHEN others THEN
2930: wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.setOpen',l_module_name);
2931: --
2926: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2927: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2928: END IF;
2929: WHEN others THEN
2930: wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.setOpen',l_module_name);
2931: --
2932: --ROLLBACK TO open_stop_begin_sp;
2933: --
2934: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2930: wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.setOpen',l_module_name);
2931: --
2932: --ROLLBACK TO open_stop_begin_sp;
2933: --
2934: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2935: --
2936: -- Debug Statements
2937: --
2938: IF l_debug_on THEN
2979: l_max_seq_number NUMBER;
2980: l_trip_id_tmp NUMBER;
2981: l_locking_stops VARCHAR2(1);
2982: i NUMBER;
2983: l_trip_id_tab wsh_util_core.id_tab_type;
2984:
2985: --SSN Change, new variables/constants
2986: l_normal_seq_offset number := 10;
2987: l_seq_offset number;
3008: WSH_DEBUG_SV.log(l_module_name,'Trip id ', p_stop_details_rec.trip_id);
3009: WSH_DEBUG_SV.log(l_module_name,'Stop sequence number ', p_stop_details_rec.stop_sequence_number);
3010: END IF;
3011: --
3012: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3013: l_locking_stops := 'N';
3014:
3015: -- lock trip p_trip_id;
3016: WSH_TRIPS_PVT.lock_trip_no_compare(p_stop_details_rec.trip_id);
3103: p_calc_del_wv => 'N',
3104: x_return_status => x_return_status,
3105: p_suppress_errors => 'Y');
3106:
3107: IF x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3108: IF l_debug_on THEN
3109: WSH_DEBUG_SV.logmsg(l_module_name,'Error calculating trip wt/vol');
3110: END IF;
3111: END IF;
3119: WHEN RECORD_LOCKED
3120: OR app_exception.application_exception
3121: OR app_exception.record_lock_exception THEN
3122: ROLLBACK TO begin_reset_stop_seq;
3123: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3124: IF get_all_open_stops%ISOPEN THEN
3125: close get_all_open_stops;
3126: END IF;
3127:
3127:
3128: IF l_locking_stops = 'N' THEN
3129: FND_MESSAGE.SET_NAME('WSH', 'WSH_TRIP_LOCK_FAILED');
3130: FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(l_trip_id_tmp));
3131: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
3132: IF l_debug_on THEN
3133: WSH_DEBUG_SV.log(l_module_name, 'ERROR: Failed to lock trip ID '||to_char(l_trip_id_tmp));
3134: WSH_DEBUG_SV.POP(l_module_name, 'EXCEPTION:RECORD_LOCKED');
3135: END IF;
3134: WSH_DEBUG_SV.POP(l_module_name, 'EXCEPTION:RECORD_LOCKED');
3135: END IF;
3136: ELSE
3137: FND_MESSAGE.SET_NAME('WSH', 'WSH_NO_LOCK');
3138: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
3139: IF l_debug_on THEN
3140: WSH_DEBUG_SV.log(l_module_name, 'ERROR: Failed to lock trip stops belong to trip l_trip_id_tmp' );
3141: WSH_DEBUG_SV.POP(l_module_name, 'EXCEPTION:RECORD_LOCKED');
3142: END IF;
3143: END IF;
3144:
3145: WHEN OTHERS THEN
3146:
3147: wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.reset_stop_sequence_numbers',l_module_name);
3148: IF get_all_open_stops%ISOPEN THEN
3149: close get_all_open_stops;
3150: END IF;
3151: ROLLBACK TO begin_reset_stop_seq;
3152: IF get_all_open_stops%ISOPEN THEN
3153: close get_all_open_stops;
3154: END IF;
3155:
3156: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3157: --
3158: -- Debug Statements
3159: --
3160: IF l_debug_on THEN
3256: wsh_debug_sv.LOG(l_module_name, 'p_trip_id', p_trip_id);
3257: wsh_debug_sv.LOG(l_module_name, 'p_stop_id', p_stop_id);
3258: END IF;
3259:
3260: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3261: x_last_pickup_stop_closed := 'N';
3262:
3263: -- This API is not exposed to end user, so no message needed here
3264: -- the developers need to know the mandatory parameters
3322:
3323: EXCEPTION
3324: --OTM R12 Org-Specific start.
3325: WHEN FND_API.G_EXC_ERROR THEN
3326: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3327: IF pickup_stop_csr%ISOPEN THEN
3328: CLOSE pickup_stop_csr;
3329: END IF;
3330: IF check_eligible_for_asr_csr%ISOPEN THEN
3346: IF check_eligible_for_asr_csr%ISOPEN THEN
3347: CLOSE check_eligible_for_asr_csr;
3348: END IF;
3349:
3350: wsh_util_core.default_handler('wsh_trip_stops_actions.last_pickup_stop_closed',
3351: l_module_name);
3352: --
3353: IF l_debug_on THEN
3354: WSH_DEBUG_SV.log(l_module_name,