[Home] [Help]
44: WSH_DEBUG_SV.push(l_module_name);
45: END IF;
46:
47: --OTM R12, glog proj, use Global Variable
48: l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
49:
50: -- If null, call the function
51: IF l_gc3_is_installed IS NULL THEN
52: l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
48: l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
49:
50: -- If null, call the function
51: IF l_gc3_is_installed IS NULL THEN
52: l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
53: END IF;
54: -- end of OTM R12, glog proj
55:
56: -- OTM R12, glog project
58: IF G_STOP_SEQ_MODE IS NULL THEN--{
59: IF l_gc3_is_installed = 'Y' THEN
60: G_STOP_SEQ_MODE := WSH_INTERFACE_GRP.G_STOP_SEQ_MODE_SSN;
61: ELSE
62: IF WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y' THEN
63: G_STOP_SEQ_MODE := WSH_INTERFACE_GRP.G_STOP_SEQ_MODE_PAD;
64: ELSIF fnd_profile.value('WSH_STOP_SEQ_MODE') = ('PAD') THEN
65: G_STOP_SEQ_MODE := WSH_INTERFACE_GRP.G_STOP_SEQ_MODE_PAD;
66: ELSE -- non-existent profile or any other value of fnd_profile.value(' WSH_STOP_SEQ_MODE') implies SSN
157: WSH_DEBUG_SV.log(l_module_name,'p_trip_id', p_trip_id);
158: WSH_DEBUG_SV.log(l_module_name,'p_trip_name', p_trip_name);
159: END IF;
160:
161: x_return_status := wsh_util_core.g_ret_sts_success;
162:
163: open get_pickup_stop (p_trip_id);
164: fetch get_pickup_stop into l_stop_id, l_stop_location_id;
165: if get_pickup_stop%notfound then
165: if get_pickup_stop%notfound then
166: close get_pickup_stop;
167: fnd_message.set_name('WSH','WSH_TRIP_NO_STOPS');
168: fnd_message.set_token('TRIP_NAME',p_trip_name);
169: x_return_status := wsh_util_core.g_ret_sts_error;
170: raise e_trip_confirm_exception;
171: else
172: fetch get_pickup_stop into l_stop_id, l_stop_location_id;
173: if get_pickup_stop%found then
176: l_stop_location_code := fnd_message.get;
177: x_def_rec.trip_multiple_pickup := 'Y';
178: else
179: close get_pickup_stop;
180: l_stop_location_code := wsh_util_core.get_location_description(l_stop_location_id, 'NEW UI CODE');
181: l_stop_location_code := substrb(l_stop_location_code, 1, 60);
182: open get_pickup_delivery(l_stop_id, l_stop_location_id);
183: fetch get_pickup_delivery into l_exists;
184: if get_pickup_delivery%notfound then
185: close get_pickup_delivery;
186: fnd_message.set_name('WSH','WSH_TRIP_CONFIRM_MISSING_DEL');
187: fnd_message.set_token('TRIP',p_trip_name);
188: fnd_message.set_token('STOP_NAME',l_stop_location_code);
189: x_return_status := wsh_util_core.g_ret_sts_error;
190: raise e_trip_confirm_exception;
191: else
192: close get_pickup_delivery;
193: x_def_rec.trip_multiple_pickup := 'N';
212: close get_org_id;
213:
214: if l_org_id is not null then
215: wsh_shipping_params_pvt.get(l_org_id, l_param_info, l_return_status);
216: wsh_util_core.api_post_call(p_return_status =>l_return_status,
217: x_num_warnings =>l_num_warning,
218: x_num_errors =>l_num_errors);
219:
220: -- Bug 3346237:Value for parameter Defer_interface to be taken from Global Parameters table.
221: wsh_shipping_params_pvt.Get_Global_Parameters(
222: x_param_info => l_global_info,
223: x_return_status => l_return_status);
224:
225: wsh_util_core.api_post_call(p_return_status =>l_return_status,
226: x_num_warnings =>l_num_warning,
227: x_num_errors =>l_num_errors);
228:
229: x_def_rec.defer_interface_flag := l_global_info.defer_interface ;
246:
247: EXCEPTION
248:
249: WHEN e_trip_confirm_exception THEN
250: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
251: wsh_util_core.add_message(x_return_status,l_module_name);
252: IF l_debug_on THEN
253: wsh_debug_sv.log (l_module_name,'G_EXC_ERROR');
254: WSH_DEBUG_SV.pop(l_module_name);
247: EXCEPTION
248:
249: WHEN e_trip_confirm_exception THEN
250: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
251: wsh_util_core.add_message(x_return_status,l_module_name);
252: IF l_debug_on THEN
253: wsh_debug_sv.log (l_module_name,'G_EXC_ERROR');
254: WSH_DEBUG_SV.pop(l_module_name);
255: END IF;
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));
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));
264: WSH_DEBUG_SV.pop(l_module_name);
265: END IF;
383:
384: END IF;
385: --
386:
387: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
388:
389: IF p_trip_id IS NULL THEN
390: RAISE others ;
391: END IF;
394: WSH_MBOLS_PVT.Generate_MBOL(
395: p_trip_id => p_trip_id,
396: x_sequence_number => l_document_number,
397: x_return_status => l_return_status );
398: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
399: x_return_status := l_return_status;
400: wsh_util_core.add_message(x_return_status);
401: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
402: raise TRIP_CONFIRM_ERROR;
396: x_sequence_number => l_document_number,
397: x_return_status => l_return_status );
398: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
399: x_return_status := l_return_status;
400: wsh_util_core.add_message(x_return_status);
401: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
402: raise TRIP_CONFIRM_ERROR;
403: END IF;
404: END IF;
397: x_return_status => l_return_status );
398: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
399: x_return_status := l_return_status;
400: wsh_util_core.add_message(x_return_status);
401: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
402: raise TRIP_CONFIRM_ERROR;
403: END IF;
404: END IF;
405: END IF;
420: p_logging_entity_name => 'TRIP',
421: p_consider_content => 'Y',
422: x_exceptions_tab => l_exceptions_tab
423: );
424: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
425: x_return_status := l_return_status;
426: wsh_util_core.add_message(x_return_status);
427: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
428: raise others;
422: x_exceptions_tab => l_exceptions_tab
423: );
424: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
425: x_return_status := l_return_status;
426: wsh_util_core.add_message(x_return_status);
427: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
428: raise others;
429: END IF;
430: END IF;
423: );
424: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
425: x_return_status := l_return_status;
426: wsh_util_core.add_message(x_return_status);
427: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
428: raise others;
429: END IF;
430: END IF;
431: FOR exp_cnt in 1..l_exceptions_tab.COUNT LOOP
437: END IF;
438: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
439: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
440: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Error');
441: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
442: wsh_util_core.add_message(x_return_status);
443: raise check_exceptions;
444: ELSIF l_exceptions_tab(exp_cnt).exception_behavior = 'WARNING' THEN
445: IF l_exceptions_tab(exp_cnt).entity_name = 'TRIP' THEN
438: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
439: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
440: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Error');
441: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
442: wsh_util_core.add_message(x_return_status);
443: raise check_exceptions;
444: ELSIF l_exceptions_tab(exp_cnt).exception_behavior = 'WARNING' THEN
445: IF l_exceptions_tab(exp_cnt).entity_name = 'TRIP' THEN
446: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_ENTITY');
446: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_ENTITY');
447: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
448: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
449: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
450: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
451: wsh_util_core.add_message(x_return_status);
452: ELSIF NOT (l_exp_logged) THEN
453: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
454: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
447: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
448: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
449: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
450: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
451: wsh_util_core.add_message(x_return_status);
452: ELSIF NOT (l_exp_logged) THEN
453: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
454: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
455: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
453: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
454: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
455: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
456: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
457: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
458: l_exp_logged := TRUE;
459: wsh_util_core.add_message(x_return_status);
460: END IF;
461: END IF;
455: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
456: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
457: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
458: l_exp_logged := TRUE;
459: wsh_util_core.add_message(x_return_status);
460: END IF;
461: END IF;
462: END LOOP;
463: END IF; -- end of check if trip is being set to In-Transit or Closed
528: p_defer_interface_flag => p_defer_interface_flag,
529: x_return_status => l_return_status
530: );
531:
532: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
533: -- For first stop, always error out ; for others, treat as warning
534: IF first_stop = 'Y' THEN
535: x_return_status := l_return_status ;
536: rollback to sp_confirm_trip;
536: rollback to sp_confirm_trip;
537: EXIT ;
538: ELSE
539: -- since it's not the first stop, it can be treated as warning
540: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
541: END IF;
542:
543: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
544: -- For Unexpected errors, always error out
539: -- since it's not the first stop, it can be treated as warning
540: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
541: END IF;
542:
543: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
544: -- For Unexpected errors, always error out
545: x_return_status := l_return_status ;
546: rollback to sp_confirm_trip;
547: EXIT ;
545: x_return_status := l_return_status ;
546: rollback to sp_confirm_trip;
547: EXIT ;
548:
549: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
550: x_return_status := l_return_status ;
551:
552: END IF;
553:
582: p_logging_entity_name => 'TRIP',
583: p_consider_content => 'Y'
584: ) ;
585:
586: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
587: x_return_status := l_return_status;
588: wsh_util_core.add_message(x_return_status);
589: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
590: raise TRIP_CONFIRM_ERROR;
584: ) ;
585:
586: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
587: x_return_status := l_return_status;
588: wsh_util_core.add_message(x_return_status);
589: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
590: raise TRIP_CONFIRM_ERROR;
591: END IF;
592: END IF;
585:
586: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
587: x_return_status := l_return_status;
588: wsh_util_core.add_message(x_return_status);
589: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
590: raise TRIP_CONFIRM_ERROR;
591: END IF;
592: END IF;
593: END IF;
597: NULL;
598:
599:
600: -- Bug 3374306
601: IF x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
602: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_CONFIRM_WARNING');
603: wsh_util_core.add_message(x_return_status);
604: END IF;
605:
599:
600: -- Bug 3374306
601: IF x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
602: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_CONFIRM_WARNING');
603: wsh_util_core.add_message(x_return_status);
604: END IF;
605:
606: --Bug# 2867209 - Start
607: IF l_debug_on THEN
607: IF l_debug_on THEN
608: WSH_DEBUG_SV.log(l_module_name,'l_is_action_not_performed',l_is_action_not_performed);
609: END IF;
610:
611: IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS AND P_CLOSE_FLAG = 'N' AND l_is_action_not_performed THEN
612: FND_MESSAGE.SET_NAME('WSH','WSH_NO_CHANGE_ACTION');
613: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING);
614: END IF;
615: --Bug# 2867209 - End
609: END IF;
610:
611: IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS AND P_CLOSE_FLAG = 'N' AND l_is_action_not_performed THEN
612: FND_MESSAGE.SET_NAME('WSH','WSH_NO_CHANGE_ACTION');
613: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING);
614: END IF;
615: --Bug# 2867209 - End
616: --
617:
647: WSH_DEBUG_SV.logmsg(l_module_name,'trip_confirm_error exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
648: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:trip_confirm_error');
649: END IF;
650: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_CONFIRM_ERROR');
651: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
652: wsh_util_core.add_message(x_return_status);
653: --
654: WHEN check_exceptions THEN
655: --
648: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:trip_confirm_error');
649: END IF;
650: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_CONFIRM_ERROR');
651: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
652: wsh_util_core.add_message(x_return_status);
653: --
654: WHEN check_exceptions THEN
655: --
656: IF l_debug_on THEN
657: WSH_DEBUG_SV.logmsg(l_module_name,'Check_Exceptions exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
658: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:Check_Exceptions');
659: END IF;
660: --
661: WSH_UTIL_CORE.get_messages('N', l_msg_summary, l_msg_details, x_msg_count);
662: --
663: IF l_debug_on THEN
664: WSH_DEBUG_SV.logmsg(l_module_name, L_MSG_SUMMARY );
665: END IF;
672:
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);
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');
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');
682: END IF;
683: --
684: WSH_UTIL_CORE.get_messages('N', l_msg_summary, l_msg_details, x_msg_count);
685: --
686: IF l_debug_on THEN
687: WSH_DEBUG_SV.logmsg(l_module_name, L_MSG_SUMMARY );
688: END IF;
697:
698:
699:
700: PROCEDURE Plan(
701: p_trip_rows IN wsh_util_core.id_tab_type,
702: p_action IN VARCHAR2,
703: x_return_status OUT NOCOPY VARCHAR2) IS
704:
705: l_num_error NUMBER := 0;
703: x_return_status OUT NOCOPY VARCHAR2) IS
704:
705: l_num_error NUMBER := 0;
706: l_num_warn NUMBER := 0;
707: l_trip_rows wsh_util_core.id_tab_type;
708: l_trip_status VARCHAR2(2);
709: l_stop_status VARCHAR2(2);
710:
711: cursor get_status(c_trip_id IN NUMBER) is
755: --
756: WSH_DEBUG_SV.log(l_module_name,'P_ACTION',P_ACTION);
757: END IF;
758: --
759: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
760:
761:
762: IF (p_trip_rows.count = 0) THEN
763: raise others;
786: --
787: wsh_trip_validations.check_unplan(p_trip_rows(i), x_return_status);
788: END IF;
789:
790: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
791: goto plan_error;
792: ELSIF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
793: l_num_warn := l_num_warn + 1;
794: END IF;
788: END IF;
789:
790: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
791: goto plan_error;
792: ELSIF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
793: l_num_warn := l_num_warn + 1;
794: END IF;
795:
796: /* H integration ,this call is made from Group and Public API
802: IF p_action='FIRM' THEN
803: wsh_tp_release.firm_entity( p_entity => 'TRIP',
804: p_entity_id =>p_trip_rows(i),
805: x_return_status =>x_return_status);
806: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
807: IF x_return_status=WSH_UTIL_CORE.G_RET_STS_WARNING THEN
808: l_num_warn := l_num_warn + 1;
809: ELSE
810: goto plan_error;
803: wsh_tp_release.firm_entity( p_entity => 'TRIP',
804: p_entity_id =>p_trip_rows(i),
805: x_return_status =>x_return_status);
806: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
807: IF x_return_status=WSH_UTIL_CORE.G_RET_STS_WARNING THEN
808: l_num_warn := l_num_warn + 1;
809: ELSE
810: goto plan_error;
811: END IF;
815: wsh_tp_release.unfirm_entity( p_entity => 'TRIP',
816: p_entity_id =>p_trip_rows(i),
817: p_action =>p_action,
818: x_return_status =>x_return_status);
819: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
820: IF x_return_status=WSH_UTIL_CORE.G_RET_STS_WARNING THEN
821: l_num_warn := l_num_warn + 1;
822: ELSE
823: goto plan_error;
816: p_entity_id =>p_trip_rows(i),
817: p_action =>p_action,
818: x_return_status =>x_return_status);
819: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
820: IF x_return_status=WSH_UTIL_CORE.G_RET_STS_WARNING THEN
821: l_num_warn := l_num_warn + 1;
822: ELSE
823: goto plan_error;
824: END IF;
844: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
845: END IF;
846: --
847: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
848: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
849: wsh_util_core.add_message(x_return_status);
850: l_num_error := l_num_error + 1;
851:
852: <
845: END IF;
846: --
847: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
848: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
849: wsh_util_core.add_message(x_return_status);
850: l_num_error := l_num_error + 1;
851:
852: <
853: null;
871: FND_MESSAGE.SET_TOKEN('NUM_ERROR',l_num_error);
872: FND_MESSAGE.SET_TOKEN('NUM_SUCCESS',p_trip_rows.count - l_num_error - l_num_warn);
873:
874: IF (p_trip_rows.count = l_num_error) THEN
875: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
876: ELSE
877: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
878: END IF;
879:
873:
874: IF (p_trip_rows.count = l_num_error) THEN
875: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
876: ELSE
877: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
878: END IF;
879:
880: wsh_util_core.add_message(x_return_status);
881:
876: ELSE
877: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
878: END IF;
879:
880: wsh_util_core.add_message(x_return_status);
881:
882: ELSE
883: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
884: END IF;
879:
880: wsh_util_core.add_message(x_return_status);
881:
882: ELSE
883: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
884: END IF;
885:
886: END IF;
887:
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
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
902: --
958: -- J-IB-NPARIKH-{
959: --
960: -- stubbed out as no longer being called.
961: --
962: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
963: RETURN;
964: -- J-IB-NPARIKH-}
965: **/
966:
980: p_logging_entity_name => 'TRIP',
981: p_consider_content => 'Y',
982: x_exceptions_tab => l_exceptions_tab
983: );
984: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
985: x_return_status := l_return_status;
986: wsh_util_core.add_message(x_return_status);
987: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
988: raise FND_API.G_EXC_ERROR;
982: x_exceptions_tab => l_exceptions_tab
983: );
984: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
985: x_return_status := l_return_status;
986: wsh_util_core.add_message(x_return_status);
987: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
988: raise FND_API.G_EXC_ERROR;
989: END IF;
990: END IF;
983: );
984: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
985: x_return_status := l_return_status;
986: wsh_util_core.add_message(x_return_status);
987: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
988: raise FND_API.G_EXC_ERROR;
989: END IF;
990: END IF;
991: FOR exp_cnt in 1..l_exceptions_tab.COUNT LOOP
997: END IF;
998: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
999: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
1000: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Error');
1001: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1002: wsh_util_core.add_message(x_return_status);
1003: raise check_exceptions;
1004: ELSIF l_exceptions_tab(exp_cnt).exception_behavior = 'WARNING' THEN
1005: IF l_exceptions_tab(exp_cnt).entity_name = 'TRIP' THEN
998: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
999: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
1000: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Error');
1001: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1002: wsh_util_core.add_message(x_return_status);
1003: raise check_exceptions;
1004: ELSIF l_exceptions_tab(exp_cnt).exception_behavior = 'WARNING' THEN
1005: IF l_exceptions_tab(exp_cnt).entity_name = 'TRIP' THEN
1006: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_ENTITY');
1006: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_ENTITY');
1007: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
1008: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
1009: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
1010: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1011: wsh_util_core.add_message(x_return_status);
1012: ELSIF NOT (l_exp_logged) THEN
1013: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
1014: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
1007: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
1008: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
1009: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
1010: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1011: wsh_util_core.add_message(x_return_status);
1012: ELSIF NOT (l_exp_logged) THEN
1013: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
1014: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
1015: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
1013: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
1014: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
1015: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
1016: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
1017: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1018: l_exp_logged := TRUE;
1019: wsh_util_core.add_message(x_return_status);
1020: END IF;
1021: END IF;
1015: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
1016: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
1017: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1018: l_exp_logged := TRUE;
1019: wsh_util_core.add_message(x_return_status);
1020: END IF;
1021: END IF;
1022: END LOOP;
1023: END IF;
1022: END LOOP;
1023: END IF;
1024:
1025: --
1026: IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y') THEN
1027: -- Get pvt type record structure for trip
1028: --
1029: -- Debug Statements
1030: --
1036: (p_trip_id => p_trip_id,
1037: x_trip_rec => l_trip_rec,
1038: x_return_status => l_return_status);
1039:
1040: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1041: x_return_status := l_return_status;
1042: --
1043: -- Debug Statements
1044: --
1062: p_trip_rec => l_trip_rec,
1063: p_action => 'UPDATE',
1064: x_return_status => l_return_status);
1065:
1066: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1067: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1068: l_num_warn := l_num_warn + 1;
1069: ELSE
1070: x_return_status := l_return_status;
1063: p_action => 'UPDATE',
1064: x_return_status => l_return_status);
1065:
1066: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1067: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1068: l_num_warn := l_num_warn + 1;
1069: ELSE
1070: x_return_status := l_return_status;
1071: --
1091: last_update_login = fnd_global.login_id
1092: WHERE trip_id = p_trip_id;
1093:
1094: IF (SQL%NOTFOUND) THEN
1095: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1096: --
1097: -- Debug Statements
1098: --
1099: IF l_debug_on THEN
1117: p_logging_entity_name => 'TRIP',
1118: p_consider_content => 'Y'
1119: ) ;
1120:
1121: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1122: x_return_status := l_return_status;
1123: wsh_util_core.add_message(x_return_status);
1124: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1125: raise FND_API.G_EXC_ERROR;
1119: ) ;
1120:
1121: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1122: x_return_status := l_return_status;
1123: wsh_util_core.add_message(x_return_status);
1124: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1125: raise FND_API.G_EXC_ERROR;
1126: END IF;
1127: END IF;
1120:
1121: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1122: x_return_status := l_return_status;
1123: wsh_util_core.add_message(x_return_status);
1124: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1125: raise FND_API.G_EXC_ERROR;
1126: END IF;
1127: END IF;
1128:
1126: END IF;
1127: END IF;
1128:
1129: IF l_num_warn >0 THEN
1130: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1131: ELSE
1132: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1133: END IF;
1134: END IF;
1128:
1129: IF l_num_warn >0 THEN
1130: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1131: ELSE
1132: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1133: END IF;
1134: END IF;
1135:
1136: -- Debug Statements
1141: --
1142:
1143: EXCEPTION
1144: WHEN check_exceptions THEN
1145: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1146: --
1147: IF l_debug_on THEN
1148: WSH_DEBUG_SV.logmsg(l_module_name,'Check_Exceptions exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1149: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:Check_Exceptions');
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);
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);
1159: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1213: -- Exceptions Project
1214: l_msg_count NUMBER;
1215: l_msg_data VARCHAR2(4000);
1216:
1217: l_trip_id_tab wsh_util_core.id_tab_type;
1218: l_del_tmp_rows wsh_util_core.id_tab_type;
1219:
1220: l_wf_rs VARCHAR2(1); -- Workflow Project
1221: l_debug_on BOOLEAN;
1214: l_msg_count NUMBER;
1215: l_msg_data VARCHAR2(4000);
1216:
1217: l_trip_id_tab wsh_util_core.id_tab_type;
1218: l_del_tmp_rows wsh_util_core.id_tab_type;
1219:
1220: l_wf_rs VARCHAR2(1); -- Workflow Project
1221: l_debug_on BOOLEAN;
1222: --
1248: wsh_debug_sv.LOG(l_module_name, 'p_in_rec.actual_date ', p_in_rec.actual_date );
1249: wsh_debug_sv.LOG(l_module_name, 'p_in_rec.stop_id ', p_in_rec.stop_id );
1250: END IF;
1251: --
1252: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1253: l_num_warnings := 0;
1254: l_num_errors := 0;
1255: --
1256: IF p_in_rec.new_status_code NOT IN ('OP','IT','CL')
1260: -- Invalid status for trip, raise error.
1261: --
1262: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_INVALID_STATUS');
1263: FND_MESSAGE.SET_TOKEN('TRIP_NAME','p_in_rec.name');
1264: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
1265: RAISE FND_API.G_EXC_ERROR;
1266: --}
1267: END IF;
1268: --
1266: --}
1267: END IF;
1268: --
1269:
1270: IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y')
1271: THEN
1272: --{
1273: -- Get pvt type record structure for trip
1274: --
1282: (p_trip_id => p_in_rec.trip_id,
1283: x_trip_rec => l_trip_rec,
1284: x_return_status => l_return_status);
1285: --
1286: WSH_UTIL_CORE.api_post_call
1287: (
1288: p_return_status => l_return_status,
1289: x_num_warnings => l_num_warnings,
1290: x_num_errors => l_num_errors
1304: p_trip_rec => l_trip_rec,
1305: p_action => 'UPDATE',
1306: x_return_status => l_return_status);
1307: --
1308: WSH_UTIL_CORE.api_post_call
1309: (
1310: p_return_status => l_return_status,
1311: x_num_warnings => l_num_warnings,
1312: x_num_errors => l_num_errors
1363: -- End of code for Workflow Project
1364:
1365: --setting trip to in-transit, need to set delivery to be firmed
1366: --which will make trip to be atleast planned
1367: IF WSH_UTIL_CORE.TP_IS_INSTALLED='Y' AND l_trip_rec.ship_method_code is not null THEN
1368: IF l_trip_rec.mode_of_transport ='TRUCK' and l_trip_rec.vehicle_item_id is null THEN
1369: --if mode is truck, we can't plan/firm trip.
1370: IF l_debug_on THEN
1371: WSH_DEBUG_SV.logmsg(l_module_name,'Not trying to firm trip/delivery because mode is truck');
1380:
1381: IF l_del_tmp_rows.COUNT>0 THEN
1382: wsh_new_delivery_actions.firm (p_del_rows => l_del_tmp_rows,
1383: x_return_status => l_return_status);
1384: WSH_UTIL_CORE.api_post_call
1385: (
1386: p_return_status => l_return_status,
1387: x_num_warnings => l_num_warnings,
1388: x_num_errors => l_num_errors
1396: p_trip_rows => l_trip_id_tab,
1397: p_action => 'FIRM',
1398: x_return_status => l_return_status);
1399:
1400: WSH_UTIL_CORE.api_post_call
1401: (
1402: p_return_status => l_return_status,
1403: x_num_warnings => l_num_warnings,
1404: x_num_errors => l_num_errors
1404: x_num_errors => l_num_errors
1405: );
1406: END IF;--p_status_code
1407: END IF;
1408: ELSIF WSH_UTIL_CORE.TP_IS_INSTALLED='Y' AND l_trip_rec.ship_method_code is null THEN
1409: --TP is installed and ship method is null, cannot make the trip as firmed
1410: IF l_debug_on THEN
1411: WSH_DEBUG_SV.logmsg(l_module_name,'Not trying to firm trip/delivery because ship method is null');
1412: END IF;
1426: p_logging_entity_name => 'TRIP',
1427: p_consider_content => 'Y'
1428: ) ;
1429:
1430: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1431: x_return_status := l_return_status;
1432: wsh_util_core.add_message(x_return_status);
1433: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1434: raise FND_API.G_EXC_ERROR;
1428: ) ;
1429:
1430: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1431: x_return_status := l_return_status;
1432: wsh_util_core.add_message(x_return_status);
1433: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1434: raise FND_API.G_EXC_ERROR;
1435: END IF;
1436: END IF;
1429:
1430: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1431: x_return_status := l_return_status;
1432: wsh_util_core.add_message(x_return_status);
1433: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1434: raise FND_API.G_EXC_ERROR;
1435: END IF;
1436: END IF;
1437: END IF;
1438: --
1439: --
1440: IF l_num_errors > 0
1441: THEN
1442: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1443: ELSIF l_num_warnings > 0
1444: THEN
1445: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1446: ELSE
1441: THEN
1442: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1443: ELSIF l_num_warnings > 0
1444: THEN
1445: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1446: ELSE
1447: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1448: END IF;
1449: --
1443: ELSIF l_num_warnings > 0
1444: THEN
1445: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1446: ELSE
1447: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1448: END IF;
1449: --
1450: --
1451: --
1461: WHEN FND_API.G_EXC_ERROR THEN
1462:
1463: --ROLLBACK TO trip_chgStatus_begin_sp;
1464: --
1465: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1466: --
1467: IF l_debug_on THEN
1468: WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1469: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1472: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1473:
1474: --ROLLBACK TO trip_chgStatus_begin_sp;
1475: --
1476: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1477: --
1478: IF l_debug_on THEN
1479: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1480: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1479: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1480: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1481: END IF;
1482: --
1483: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1484: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1485: --
1486: IF l_debug_on THEN
1487: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1480: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1481: END IF;
1482: --
1483: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1484: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1485: --
1486: IF l_debug_on THEN
1487: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1488: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1483: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1484: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1485: --
1486: IF l_debug_on THEN
1487: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1488: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1489: END IF;
1490: WHEN others THEN
1491: wsh_util_core.default_handler('WSH_NEW_DELIVERY_ACTIONS.changeStatus',l_module_name);
1484: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1485: --
1486: IF l_debug_on THEN
1487: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1488: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1489: END IF;
1490: WHEN others THEN
1491: wsh_util_core.default_handler('WSH_NEW_DELIVERY_ACTIONS.changeStatus',l_module_name);
1492: --
1487: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1488: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1489: END IF;
1490: WHEN others THEN
1491: wsh_util_core.default_handler('WSH_NEW_DELIVERY_ACTIONS.changeStatus',l_module_name);
1492: --
1493: --ROLLBACK TO trip_chgStatus_begin_sp;
1494: --
1495: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1491: wsh_util_core.default_handler('WSH_NEW_DELIVERY_ACTIONS.changeStatus',l_module_name);
1492: --
1493: --ROLLBACK TO trip_chgStatus_begin_sp;
1494: --
1495: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1496: --
1497: -- Debug Statements
1498: --
1499: IF l_debug_on THEN
1550: AND nvl(wnd.shipment_direction,'O') NOT IN ('O','IO') -- J-IB-NPARIKH
1551: AND wnd.initial_pickup_location_id = wts.stop_location_id;
1552: --
1553: --
1554: l_deliveryIdTbl WSH_UTIL_CORE.key_value_tab_type;
1555: l_deliveryIdExtTbl WSH_UTIL_CORE.key_value_tab_type;
1556: --
1557: l_action_prms wsh_deliveries_grp.action_parameters_rectype;
1558: l_del_action_out_rec wsh_deliveries_grp.Delivery_Action_Out_Rec_Type;
1551: AND wnd.initial_pickup_location_id = wts.stop_location_id;
1552: --
1553: --
1554: l_deliveryIdTbl WSH_UTIL_CORE.key_value_tab_type;
1555: l_deliveryIdExtTbl WSH_UTIL_CORE.key_value_tab_type;
1556: --
1557: l_action_prms wsh_deliveries_grp.action_parameters_rectype;
1558: l_del_action_out_rec wsh_deliveries_grp.Delivery_Action_Out_Rec_Type;
1559: l_delivery_id_tab wsh_util_core.id_tab_type;
1555: l_deliveryIdExtTbl WSH_UTIL_CORE.key_value_tab_type;
1556: --
1557: l_action_prms wsh_deliveries_grp.action_parameters_rectype;
1558: l_del_action_out_rec wsh_deliveries_grp.Delivery_Action_Out_Rec_Type;
1559: l_delivery_id_tab wsh_util_core.id_tab_type;
1560: l_trip_name VARCHAR2(30);
1561: l_cnt NUMBER;
1562: l_totalCnt NUMBER;
1563: --
1614: --
1615: --
1616: IF l_debug_on THEN
1617: wsh_debug_sv.log (l_module_name,'dlvy_rec.delivery_id',dlvy_rec.delivery_id);
1618: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_CACHED_VALUE-l_deliveryIdTbl',WSH_DEBUG_SV.C_PROC_LEVEL);
1619: END IF;
1620: --
1621: -- Build a cache of unique delivery IDs
1622: --
1619: END IF;
1620: --
1621: -- Build a cache of unique delivery IDs
1622: --
1623: wsh_util_core.get_cached_value
1624: (
1625: p_cache_tbl => l_deliveryIdTbl,
1626: p_cache_ext_tbl => l_deliveryIdExtTbl,
1627: p_key => dlvy_rec.delivery_id,
1632: --
1633: --
1634: IF l_debug_on THEN
1635: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1636: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1637: END IF;
1638: --
1639: wsh_util_core.api_post_call
1640: (
1635: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1636: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1637: END IF;
1638: --
1639: wsh_util_core.api_post_call
1640: (
1641: p_return_status => l_return_status,
1642: x_num_warnings => l_num_warnings,
1643: x_num_errors => l_num_errors
1664: --FND_MESSAGE.SET_TOKEN('TRIP_NAME',NVL(l_trip_name,p_rec_attr_tab(l_index).trip_id));
1665: FND_MESSAGE.SET_TOKEN('TRIP_NAME',NVL(l_trip_name, wsh_trips_pvt.get_name( p_rec_attr_tab(l_index).trip_id)));
1666: END IF;
1667: --
1668: wsh_util_core.add_message(wsh_util_core.g_ret_sts_warning,l_module_name);
1669: l_num_warnings := NVL(l_num_warnings,0) + 1;
1670: --}
1671: END IF;
1672: --
1745: IF l_debug_on THEN
1746: wsh_debug_sv.log(l_module_name,'Return Status After Calling generate_routing_response',l_return_status);
1747: END IF;
1748: --
1749: wsh_util_core.api_post_call(
1750: p_return_status => l_return_status,
1751: x_num_warnings => l_num_warnings,
1752: x_num_errors => l_num_errors,
1753: p_msg_Data => l_msg_data);
1761: END IF;
1762: --
1763: IF l_num_errors > 0
1764: THEN
1765: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1766: ELSIF l_num_warnings > 0
1767: THEN
1768: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1769: ELSE
1764: THEN
1765: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1766: ELSIF l_num_warnings > 0
1767: THEN
1768: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1769: ELSE
1770: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1771: END IF;
1772: --
1766: ELSIF l_num_warnings > 0
1767: THEN
1768: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1769: ELSE
1770: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1771: END IF;
1772: --
1773: -- Debug Statements
1774: --
1779: --}
1780: EXCEPTION
1781: --{
1782: WHEN FND_API.G_EXC_ERROR THEN
1783: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1784: --
1785: -- Debug Statements
1786: --
1787: IF l_debug_on THEN
1789: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1790: END IF;
1791: --
1792: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1793: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1794: --
1795: -- Debug Statements
1796: --
1797: IF l_debug_on THEN
1798: WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1799: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1800: END IF;
1801: --
1802: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1803: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1804: --
1805: -- Debug Statements
1806: --
1799: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1800: END IF;
1801: --
1802: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1803: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1804: --
1805: -- Debug Statements
1806: --
1807: IF l_debug_on THEN
1804: --
1805: -- Debug Statements
1806: --
1807: IF l_debug_on THEN
1808: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1809: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1810: END IF;
1811: --
1812: WHEN OTHERS THEN
1805: -- Debug Statements
1806: --
1807: IF l_debug_on THEN
1808: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1809: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1810: END IF;
1811: --
1812: WHEN OTHERS THEN
1813: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1809: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
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: --
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
1854: --
1855: --========================================================================
1856: PROCEDURE get_next_del_rows( p_del_tbl IN Del_Rec_Type,
1857: x_idx IN OUT NOCOPY NUMBER,
1858: x_del_rows OUT NOCOPY wsh_util_core.id_tab_type,
1859: x_return_status OUT NOCOPY VARCHAR) IS
1860:
1861: --
1862: j NUMBER;
1891: wsh_debug_sv.log(l_module_name, 'p_del_tbl.COUNT', p_del_tbl.delivery_id.COUNT);
1892: END IF;
1893: --
1894:
1895: IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y') THEN
1896: l_fte_flag := 'Y';
1897: END IF;
1898:
1899: --If the x_idx is greater than the delivery_id.COUNT then
1963: l_counter := l_counter + 1;
1964:
1965: END LOOP; --}
1966: END IF; --}
1967: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1968:
1969: IF l_debug_on THEN
1970: wsh_debug_sv.log(l_module_name, 'x_idx', x_idx);
1971: WSH_DEBUG_SV.pop(l_module_name);
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);
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);
1983: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2073: -- the column sequence determines the sorting
2074:
2075: CLOSE c_sort_del;
2076:
2077: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2078:
2079: IF l_debug_on THEN
2080: wsh_debug_sv.log(l_module_name, 'x_del_tbl.COUNT', x_del_tbl.delivery_id.COUNT);
2081: WSH_DEBUG_SV.pop(l_module_name);
2083: --
2084: EXCEPTION
2085: --
2086: WHEN e_invalid_count THEN
2087: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2088: --
2089: IF l_debug_on THEN
2090: WSH_DEBUG_SV.logmsg(l_module_name,'The input table is empty.' ,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2091: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:e_invalid_count');
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);
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);
2101: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2119: --
2120: --========================================================================
2121:
2122: PROCEDURE autocreate_trip_wrp(
2123: p_del_rows IN wsh_util_core.id_tab_type,
2124: p_entity IN VARCHAR2,
2125: x_trip_ids OUT NOCOPY wsh_util_core.id_tab_type,
2126: x_trip_names OUT NOCOPY wsh_util_core.Column_Tab_Type,
2127: x_return_status OUT NOCOPY VARCHAR2,
2121:
2122: PROCEDURE autocreate_trip_wrp(
2123: p_del_rows IN wsh_util_core.id_tab_type,
2124: p_entity IN VARCHAR2,
2125: x_trip_ids OUT NOCOPY wsh_util_core.id_tab_type,
2126: x_trip_names OUT NOCOPY wsh_util_core.Column_Tab_Type,
2127: x_return_status OUT NOCOPY VARCHAR2,
2128: p_sc_pickup_date IN DATE DEFAULT NULL,
2129: p_sc_dropoff_date IN DATE DEFAULT NULL) IS
2122: PROCEDURE autocreate_trip_wrp(
2123: p_del_rows IN wsh_util_core.id_tab_type,
2124: p_entity IN VARCHAR2,
2125: x_trip_ids OUT NOCOPY wsh_util_core.id_tab_type,
2126: x_trip_names OUT NOCOPY wsh_util_core.Column_Tab_Type,
2127: x_return_status OUT NOCOPY VARCHAR2,
2128: p_sc_pickup_date IN DATE DEFAULT NULL,
2129: p_sc_dropoff_date IN DATE DEFAULT NULL) IS
2130:
2143:
2144: l_del_tbl Del_Rec_Type;
2145: l_del_tbl_not_sorted Del_Rec_Type;
2146: l_return_status varchar2(1);
2147: l_del_rows wsh_util_core.id_tab_type;
2148: l_trip_id NUMBER;
2149: l_trip_name wsh_trips.name%TYPE;
2150: l_initial_pickup_location_id NUMBER;
2151: l_ultimate_dropoff_location_id NUMBER;
2197: -- and have different pickup and drop-off locations then group these
2198: -- deliveries and create several trips for them.
2199:
2200: sort_del(l_del_tbl_not_sorted,l_del_tbl, l_return_status );
2201: wsh_util_core.api_post_call(
2202: p_return_status => l_return_status,
2203: x_num_warnings => l_num_warnings,
2204: x_num_errors => l_num_errors
2205: );
2212: get_next_del_rows( p_del_tbl => l_del_tbl,
2213: x_idx => l_idx,
2214: x_del_rows => l_del_rows,
2215: x_return_status => l_return_status);
2216: wsh_util_core.api_post_call(
2217: p_return_status => l_return_status,
2218: x_num_warnings => l_num_warnings,
2219: x_num_errors => l_num_errors
2220: );
2227: x_return_status => l_return_status,
2228: p_sc_pickup_date => p_sc_pickup_date,
2229: p_sc_dropoff_date => p_sc_dropoff_date);
2230:
2231: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2232: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2233: END IF;
2234: wsh_util_core.api_post_call(
2235: p_return_status => l_return_status,
2230:
2231: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2232: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2233: END IF;
2234: wsh_util_core.api_post_call(
2235: p_return_status => l_return_status,
2236: x_num_warnings => l_num_warnings,
2237: x_num_errors => l_num_errors,
2238: p_raise_error_flag => FALSE
2245: END LOOP; --}
2246:
2247:
2248: IF l_num_errors = 0 AND l_num_warnings = 0 THEN
2249: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2250: ELSIF l_num_errors = l_api_calls THEN
2251: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2252: ELSE
2253: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
2247:
2248: IF l_num_errors = 0 AND l_num_warnings = 0 THEN
2249: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2250: ELSIF l_num_errors = l_api_calls THEN
2251: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2252: ELSE
2253: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
2254: END IF;
2255:
2249: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2250: ELSIF l_num_errors = l_api_calls THEN
2251: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2252: ELSE
2253: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
2254: END IF;
2255:
2256: IF l_debug_on THEN
2257: WSH_DEBUG_SV.pop(l_module_name);
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);
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);
2269: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2276: -- Procedure: Autocreate_Trip_multi
2277: -- New procedure which gets the ignore_for_planning flag
2278: -- and groups them on the basis of that.
2279: PROCEDURE autocreate_trip_multi(
2280: p_del_rows IN wsh_util_core.id_tab_type,
2281: p_entity IN VARCHAR2,
2282: x_trip_ids OUT NOCOPY wsh_util_core.id_tab_type,
2283: x_trip_names OUT NOCOPY wsh_util_core.Column_Tab_Type,
2284: x_return_status OUT NOCOPY VARCHAR2) IS
2278: -- and groups them on the basis of that.
2279: PROCEDURE autocreate_trip_multi(
2280: p_del_rows IN wsh_util_core.id_tab_type,
2281: p_entity IN VARCHAR2,
2282: x_trip_ids OUT NOCOPY wsh_util_core.id_tab_type,
2283: x_trip_names OUT NOCOPY wsh_util_core.Column_Tab_Type,
2284: x_return_status OUT NOCOPY VARCHAR2) IS
2285:
2286: --OTM R12, glog proj
2279: PROCEDURE autocreate_trip_multi(
2280: p_del_rows IN wsh_util_core.id_tab_type,
2281: p_entity IN VARCHAR2,
2282: x_trip_ids OUT NOCOPY wsh_util_core.id_tab_type,
2283: x_trip_names OUT NOCOPY wsh_util_core.Column_Tab_Type,
2284: x_return_status OUT NOCOPY VARCHAR2) IS
2285:
2286: --OTM R12, glog proj
2287: -- When OTM is installed and user performs autocreate Trip action
2295: SELECT NVL(ignore_for_planning,p_ignore_flag) ignore_for_planning
2296: FROM wsh_new_deliveries
2297: WHERE delivery_id=p_delid;
2298:
2299: l_ignore_ids wsh_util_core.id_tab_type;
2300: l_include_ids wsh_util_core.id_tab_type;
2301: l_return_status VARCHAR2(1);
2302: l_trip_id NUMBER;
2303: l_trip_name wsh_trips.name%TYPE;
2296: FROM wsh_new_deliveries
2297: WHERE delivery_id=p_delid;
2298:
2299: l_ignore_ids wsh_util_core.id_tab_type;
2300: l_include_ids wsh_util_core.id_tab_type;
2301: l_return_status VARCHAR2(1);
2302: l_trip_id NUMBER;
2303: l_trip_name wsh_trips.name%TYPE;
2304: l_index NUMBER;
2301: l_return_status VARCHAR2(1);
2302: l_trip_id NUMBER;
2303: l_trip_name wsh_trips.name%TYPE;
2304: l_index NUMBER;
2305: l_trip_ids wsh_util_core.id_tab_type;
2306: l_trip_names wsh_util_core.Column_Tab_Type;
2307: l_gc3_is_installed VARCHAR2(1); -- OTM R12, glog proj
2308: l_ignore_flag WSH_TRIPS.IGNORE_FOR_PLANNING%TYPE; -- OTM R12, glog proj
2309: l_count_error NUMBER;
2302: l_trip_id NUMBER;
2303: l_trip_name wsh_trips.name%TYPE;
2304: l_index NUMBER;
2305: l_trip_ids wsh_util_core.id_tab_type;
2306: l_trip_names wsh_util_core.Column_Tab_Type;
2307: l_gc3_is_installed VARCHAR2(1); -- OTM R12, glog proj
2308: l_ignore_flag WSH_TRIPS.IGNORE_FOR_PLANNING%TYPE; -- OTM R12, glog proj
2309: l_count_error NUMBER;
2310:
2330: wsh_debug_sv.log(l_module_name, 'p_entity', p_entity);
2331: wsh_debug_sv.log(l_module_name, 'p_del_rows.COUNT', p_del_rows.COUNT);
2332: END IF;
2333: --
2334: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2335:
2336: --OTM R12, glog proj, use Global Variable
2337: l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
2338:
2333: --
2334: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2335:
2336: --OTM R12, glog proj, use Global Variable
2337: l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
2338:
2339: -- If null, call the function
2340: IF l_gc3_is_installed IS NULL THEN
2341: l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
2337: l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
2338:
2339: -- If null, call the function
2340: IF l_gc3_is_installed IS NULL THEN
2341: l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
2342: END IF;
2343:
2344: IF l_gc3_is_installed = 'Y' THEN
2345: l_ignore_flag := 'Y';
2350:
2351:
2352: -- OTM R12, glog project
2353: -- If gc3 is installed, sort Include and Ignore lines/deliveries
2354: IF (WSH_UTIL_CORE.TP_IS_INSTALLED = 'N') AND (l_gc3_is_installed = 'N') THEN
2355: --just call the core api
2356: autocreate_trip_wrp(
2357: p_del_rows => p_del_rows,
2358: p_entity => p_entity,
2391: -- Only filtered deliveries will be considered for further processing
2392: IF l_count_error > 0 THEN
2393: FND_MESSAGE.SET_NAME('WSH','WSH_OTM_CR_TRIP_SUMMARY');
2394: IF l_count_error = p_del_rows.count THEN
2395: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
2396: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2397: x_return_status := l_return_status;
2398: IF l_debug_on THEN
2399: WSH_DEBUG_SV.log(l_module_name, 'Count of Errors', l_count_error);
2392: IF l_count_error > 0 THEN
2393: FND_MESSAGE.SET_NAME('WSH','WSH_OTM_CR_TRIP_SUMMARY');
2394: IF l_count_error = p_del_rows.count THEN
2395: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
2396: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2397: x_return_status := l_return_status;
2398: IF l_debug_on THEN
2399: WSH_DEBUG_SV.log(l_module_name, 'Count of Errors', l_count_error);
2400: WSH_DEBUG_SV.pop(l_module_name);
2400: WSH_DEBUG_SV.pop(l_module_name);
2401: END IF;
2402: RETURN;
2403: ELSE
2404: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
2405: l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2406: x_return_status := l_return_status;
2407: END IF;
2408: END IF;
2401: END IF;
2402: RETURN;
2403: ELSE
2404: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
2405: l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2406: x_return_status := l_return_status;
2407: END IF;
2408: END IF;
2409:
2415: x_trip_ids => l_trip_ids,
2416: x_trip_names => l_trip_names,
2417: x_return_status => l_return_status);
2418:
2419: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
2420: x_return_status:=l_return_status;
2421: IF l_debug_on THEN
2422: WSH_DEBUG_SV.pop(l_module_name);
2423: END IF;
2441: x_trip_names => l_trip_names,
2442: x_return_status => l_return_status);
2443:
2444:
2445: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
2446: x_return_status:=l_return_status; -- Bug#3884302
2447: IF l_debug_on THEN
2448: WSH_DEBUG_SV.pop(l_module_name);
2449: END IF;
2456: END LOOP;
2457: END IF;
2458: END IF;
2459:
2460: IF l_return_status=WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2461: x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
2462: END IF;
2463:
2464: END IF; --tp_is_installed
2457: END IF;
2458: END IF;
2459:
2460: IF l_return_status=WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2461: x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
2462: END IF;
2463:
2464: END IF; --tp_is_installed
2465:
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
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
2481: --
2487: END autocreate_trip_multi;
2488:
2489:
2490: PROCEDURE autocreate_trip(
2491: p_del_rows IN wsh_util_core.id_tab_type,
2492: p_entity IN VARCHAR2,
2493: x_trip_id OUT NOCOPY NUMBER,
2494: x_trip_name OUT NOCOPY VARCHAR2,
2495: x_return_status OUT NOCOPY VARCHAR2,
2548: l_rowid VARCHAR2(30);
2549: l_trip_info WSH_TRIPS_PVT.TRIP_REC_TYPE;
2550: assigned_del_error EXCEPTION;
2551: l_return_status VARCHAR2(1);
2552: l_del_legs WSH_UTIL_CORE.ID_TAB_TYPE;
2553: l_good_dels WSH_UTIL_CORE.ID_TAB_TYPE;
2554:
2555: /* H integration for Multi Leg */
2556: l_num_warn NUMBER := 0;
2549: l_trip_info WSH_TRIPS_PVT.TRIP_REC_TYPE;
2550: assigned_del_error EXCEPTION;
2551: l_return_status VARCHAR2(1);
2552: l_del_legs WSH_UTIL_CORE.ID_TAB_TYPE;
2553: l_good_dels WSH_UTIL_CORE.ID_TAB_TYPE;
2554:
2555: /* H integration for Multi Leg */
2556: l_num_warn NUMBER := 0;
2557: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
2570: -- Bug 3413364, skip the delivery if it does not have both initial pickup date and ultimate dropoff date
2571: l_initial_pickup_date DATE;
2572: l_ultimate_dropoff_date DATE;
2573: l_num_skipped NUMBER := 0;
2574: l_stop_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
2575: l_dbi_rs VARCHAR2(1); -- DBI Project
2576:
2577: l_trip_ids WSH_UTIL_CORE.ID_TAB_TYPE;
2578: l_success_trip_ids WSH_UTIL_CORE.ID_TAB_TYPE;
2573: l_num_skipped NUMBER := 0;
2574: l_stop_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
2575: l_dbi_rs VARCHAR2(1); -- DBI Project
2576:
2577: l_trip_ids WSH_UTIL_CORE.ID_TAB_TYPE;
2578: l_success_trip_ids WSH_UTIL_CORE.ID_TAB_TYPE;
2579:
2580: l_gc3_is_installed VARCHAR2(1); -- OTM R12, glog proj
2581: l_ignore_flag WSH_TRIPS.IGNORE_FOR_PLANNING%TYPE; -- OTM R12, glog proj
2574: l_stop_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
2575: l_dbi_rs VARCHAR2(1); -- DBI Project
2576:
2577: l_trip_ids WSH_UTIL_CORE.ID_TAB_TYPE;
2578: l_success_trip_ids WSH_UTIL_CORE.ID_TAB_TYPE;
2579:
2580: l_gc3_is_installed VARCHAR2(1); -- OTM R12, glog proj
2581: l_ignore_flag WSH_TRIPS.IGNORE_FOR_PLANNING%TYPE; -- OTM R12, glog proj
2582: l_count_error NUMBER;
2583:
2584: --
2585: BEGIN
2586: -- Bug 3413364, skip the delivery if it does not have both initial pickup date and ultimate dropoff date
2587: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2588: -- Create a list of deliveries that are unassigned. All other deliveries have
2589: -- error messages set.
2590: --
2591: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2604: END IF;
2605: --
2606:
2607: --OTM R12, glog proj, use Global Variable
2608: l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
2609: l_count_error := 0;
2610:
2611: -- If null, call the function
2612: IF l_gc3_is_installed IS NULL THEN
2609: l_count_error := 0;
2610:
2611: -- If null, call the function
2612: IF l_gc3_is_installed IS NULL THEN
2613: l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
2614: END IF;
2615: IF l_gc3_is_installed = 'Y' THEN
2616: l_ignore_flag := 'Y';
2617: ELSE
2635: --{
2636: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_AU_TRIP_ASSIGN_DEL');
2637: FND_MESSAGE.SET_TOKEN('DEL_NAME',
2638: wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
2639: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
2640: l_num_skipped := l_num_skipped + 1;
2641: --}
2642: END IF;
2643: ELSE
2660:
2661: IF l_gc3_is_installed = 'Y' AND l_ignore_for_planning = 'N' THEN--{
2662: -- Raise a new generic error message here, just trip creation will not happen.
2663: FND_MESSAGE.SET_NAME('WSH','WSH_OTM_CR_TRIP');
2664: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
2665: ----- skip this delivery, handled towards the end
2666: l_num_skipped := l_num_skipped + 1;
2667: l_count_error := l_count_error + 1;
2668: ELSE--} {
2685: AND nvl(l_shipment_direction,'O') IN ('O', 'IO') THEN--{
2686: --
2687: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_DATES_MISSING');
2688: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
2689: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
2690: l_num_skipped := l_num_skipped + 1;
2691: --
2692: ELSE
2693: --} {
2739: -- API - Error or Warning
2740: IF l_count_error > 0 THEN
2741: FND_MESSAGE.SET_NAME('WSH','WSH_OTM_CR_TRIP_SUMMARY');
2742: IF l_count_error = p_del_rows.count THEN
2743: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2744: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
2745: IF l_debug_on THEN
2746: WSH_DEBUG_SV.log(l_module_name, 'Count of Errors', l_count_error);
2747: WSH_DEBUG_SV.pop(l_module_name);
2740: IF l_count_error > 0 THEN
2741: FND_MESSAGE.SET_NAME('WSH','WSH_OTM_CR_TRIP_SUMMARY');
2742: IF l_count_error = p_del_rows.count THEN
2743: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2744: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
2745: IF l_debug_on THEN
2746: WSH_DEBUG_SV.log(l_module_name, 'Count of Errors', l_count_error);
2747: WSH_DEBUG_SV.pop(l_module_name);
2748: END IF;
2747: WSH_DEBUG_SV.pop(l_module_name);
2748: END IF;
2749: RETURN;
2750: ELSE -- x_return_status is populated at the end
2751: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
2752: END IF;
2753: END IF;
2754:
2755: --
2779: END IF;
2780: --
2781: wsh_trips_pvt.create_trip(l_trip_info, l_rowid, x_trip_id, x_trip_name,x_return_status);
2782: --
2783: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2784: --
2785: IF l_debug_on THEN
2786: wsh_debug_sv.log(l_module_name, 'Return Status after calling create_trip', x_return_Status);
2787: WSH_DEBUG_SV.pop(l_module_name);
2807: p_sc_dropoff_date => p_sc_dropoff_date
2808: );
2809: --
2810:
2811: IF x_return_status in ( WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2812: --
2813: IF l_debug_on THEN
2814: wsh_debug_sv.log(l_module_name, 'Return Status after calling wsh_delivery_legs_actions.assign_deliveries', x_return_Status);
2815: WSH_DEBUG_SV.pop(l_module_name);
2820: ELSE
2821: --{
2822: -- H integration
2823: --
2824: IF x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2825: l_num_warn := l_num_warn + 1;
2826: END IF;
2827:
2828: IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y') THEN
2824: IF x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2825: l_num_warn := l_num_warn + 1;
2826: END IF;
2827:
2828: IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y') THEN
2829: l_fte_flag := 'Y';
2830: END IF;
2831: --
2832: FOR st IN trip_stops(x_trip_id) LOOP
2846: (p_stop_id => st.stop_id,
2847: x_stop_rec => l_stop_rec,
2848: x_return_status => l_return_status);
2849: --
2850: IF x_return_status in ( WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2851: --
2852: x_return_status := l_return_status;
2853: --
2854: IF l_debug_on THEN
2856: END IF;
2857: --
2858: RETURN;
2859: --
2860: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2861: l_num_warn := l_num_warn + 1;
2862: END IF;
2863: --
2864: l_stop_rec.planned_departure_date := nvl(l_stop_rec.planned_arrival_date,SYSDATE);
2872: p_trip_rec => l_trip_rec,
2873: p_action => 'UPDATE',
2874: x_return_status => l_return_status);
2875: --
2876: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2877: --
2878: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2879: l_num_warn := l_num_warn + 1;
2880: ELSE
2874: x_return_status => l_return_status);
2875: --
2876: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2877: --
2878: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2879: l_num_warn := l_num_warn + 1;
2880: ELSE
2881: x_return_status := l_return_status;
2882: --
2914: (p_stop_id => st.stop_id,
2915: x_stop_rec => l_stop_rec,
2916: x_return_status => l_return_status);
2917: --
2918: IF x_return_status in ( WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2919: x_return_status := l_return_status;
2920: --
2921: IF l_debug_on THEN
2922: WSH_DEBUG_SV.pop(l_module_name);
2922: WSH_DEBUG_SV.pop(l_module_name);
2923: END IF;
2924: --
2925: RETURN;
2926: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2927: l_num_warn := l_num_warn + 1;
2928: END IF;
2929: --
2930: l_stop_rec.planned_arrival_date := nvl(l_stop_rec.planned_departure_date,SYSDATE);
2938: p_trip_rec => l_trip_rec,
2939: p_action => 'UPDATE',
2940: x_return_status => l_return_status);
2941: --
2942: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2943: --
2944: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2945: l_num_warn := l_num_warn + 1;
2946: ELSE
2940: x_return_status => l_return_status);
2941: --
2942: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2943: --
2944: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2945: l_num_warn := l_num_warn + 1;
2946: ELSE
2947: x_return_status := l_return_status;
2948: --
2977:
2978: IF l_debug_on THEN
2979: WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
2980: END IF;
2981: IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2982: x_return_status := l_dbi_rs;
2983: -- just pass this return status to caller API
2984: IF l_debug_on THEN
2985: WSH_DEBUG_SV.log(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
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
3010: l_num_warn := l_num_warn + 1;
3011: ELSE
3012: x_return_status := l_return_status;
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
3010: l_num_warn := l_num_warn + 1;
3011: ELSE
3012: x_return_status := l_return_status;
3013: --
3025: ( p_trip_ids => l_trip_ids,
3026: p_caller => 'WSH_AUTOCREATE_TRIP',
3027: x_success_trip_ids => l_success_trip_ids,
3028: x_return_status => l_return_status);
3029: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
3030: x_return_status := l_return_status;
3031: --
3032: IF l_debug_on THEN
3033: WSH_DEBUG_SV.pop(l_module_name);
3033: WSH_DEBUG_SV.pop(l_module_name);
3034: END IF;
3035: --
3036: RETURN;
3037: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3038: l_num_warn := l_num_warn + 1;
3039: END IF;
3040: END IF; --if get_stop_seq_mode = PAD
3041: --}
3050: (p_table_name => 'WSH_TRIPS',
3051: p_primary_id => x_trip_id,
3052: x_return_status => x_return_status);
3053:
3054: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3055: IF l_debug_on THEN
3056: WSH_DEBUG_SV.logmsg(l_module_name, 'PROC WSH_FLEXFIELD_UTILS.WRITE_DFF_ATTRIBUTES RETURNED ERROR' );
3057: END IF;
3058: IF l_debug_on THEN
3064: -- Bug 3413364, skip the delivery if it does not have both
3065: -- initial pickup date and ultimate dropoff date
3066: --
3067: IF l_num_skipped > 0 AND l_num_skipped = p_del_rows.count THEN
3068: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3069: ELSIF l_num_skipped > 0 or l_num_warn> 0 THEN
3070: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3071: END IF;
3072: --
3066: --
3067: IF l_num_skipped > 0 AND l_num_skipped = p_del_rows.count THEN
3068: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3069: ELSIF l_num_skipped > 0 or l_num_warn> 0 THEN
3070: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3071: END IF;
3072: --
3073: -- TO DO: Add message for successful completion of action
3074: IF x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3070: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3071: END IF;
3072: --
3073: -- TO DO: Add message for successful completion of action
3074: IF x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3075: FND_MESSAGE.SET_NAME('WSH', 'WSH_AUTOCREATE_TRIP_WARN');
3076: wsh_util_core.add_message(x_return_status);
3077: END IF;
3078: --
3072: --
3073: -- TO DO: Add message for successful completion of action
3074: IF x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3075: FND_MESSAGE.SET_NAME('WSH', 'WSH_AUTOCREATE_TRIP_WARN');
3076: wsh_util_core.add_message(x_return_status);
3077: END IF;
3078: --
3079: IF l_debug_on THEN
3080: WSH_DEBUG_SV.pop(l_module_name);
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);
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);
3092: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3097:
3098: --Compatibility Changes - removed trip_id, trip_name and added x_trip_rows
3099:
3100: PROCEDURE autocreate_del_trip(
3101: p_line_rows IN wsh_util_core.id_tab_type,
3102: p_org_rows IN wsh_util_core.id_tab_type,
3103: p_max_detail_commit IN NUMBER := 1000,
3104: x_del_rows OUT NOCOPY wsh_util_core.id_tab_type,
3105: x_trip_rows OUT NOCOPY wsh_util_core.id_tab_type,
3098: --Compatibility Changes - removed trip_id, trip_name and added x_trip_rows
3099:
3100: PROCEDURE autocreate_del_trip(
3101: p_line_rows IN wsh_util_core.id_tab_type,
3102: p_org_rows IN wsh_util_core.id_tab_type,
3103: p_max_detail_commit IN NUMBER := 1000,
3104: x_del_rows OUT NOCOPY wsh_util_core.id_tab_type,
3105: x_trip_rows OUT NOCOPY wsh_util_core.id_tab_type,
3106: x_return_status OUT NOCOPY VARCHAR2) IS
3100: PROCEDURE autocreate_del_trip(
3101: p_line_rows IN wsh_util_core.id_tab_type,
3102: p_org_rows IN wsh_util_core.id_tab_type,
3103: p_max_detail_commit IN NUMBER := 1000,
3104: x_del_rows OUT NOCOPY wsh_util_core.id_tab_type,
3105: x_trip_rows OUT NOCOPY wsh_util_core.id_tab_type,
3106: x_return_status OUT NOCOPY VARCHAR2) IS
3107:
3108: l_grouping_rows wsh_util_core.id_tab_type;
3101: p_line_rows IN wsh_util_core.id_tab_type,
3102: p_org_rows IN wsh_util_core.id_tab_type,
3103: p_max_detail_commit IN NUMBER := 1000,
3104: x_del_rows OUT NOCOPY wsh_util_core.id_tab_type,
3105: x_trip_rows OUT NOCOPY wsh_util_core.id_tab_type,
3106: x_return_status OUT NOCOPY VARCHAR2) IS
3107:
3108: l_grouping_rows wsh_util_core.id_tab_type;
3109:
3104: x_del_rows OUT NOCOPY wsh_util_core.id_tab_type,
3105: x_trip_rows OUT NOCOPY wsh_util_core.id_tab_type,
3106: x_return_status OUT NOCOPY VARCHAR2) IS
3107:
3108: l_grouping_rows wsh_util_core.id_tab_type;
3109:
3110: --
3111: l_debug_on BOOLEAN;
3112: --
3119: l_cc_group_info WSH_FTE_COMP_CONSTRAINT_PKG.cc_group_tab_type;
3120:
3121: b_cc_linefailed boolean;
3122: b_cc_groupidexists boolean;
3123: l_id_tab_temp wsh_util_core.id_tab_type;
3124: l_line_rows_temp wsh_util_core.id_tab_type;
3125: l_cc_count_success NUMBER;
3126: l_cc_count_group_ids NUMBER;
3127: l_cc_count_rec NUMBER;
3120:
3121: b_cc_linefailed boolean;
3122: b_cc_groupidexists boolean;
3123: l_id_tab_temp wsh_util_core.id_tab_type;
3124: l_line_rows_temp wsh_util_core.id_tab_type;
3125: l_cc_count_success NUMBER;
3126: l_cc_count_group_ids NUMBER;
3127: l_cc_count_rec NUMBER;
3128: l_cc_group_ids wsh_util_core.id_tab_type;
3124: l_line_rows_temp wsh_util_core.id_tab_type;
3125: l_cc_count_success NUMBER;
3126: l_cc_count_group_ids NUMBER;
3127: l_cc_count_rec NUMBER;
3128: l_cc_group_ids wsh_util_core.id_tab_type;
3129: l_cc_count_trip_rows NUMBER;
3130: l_cc_count_del_rows NUMBER;
3131: l_cc_count_grouping_rows NUMBER;
3132: l_del_rows_temp wsh_util_core.id_tab_type;
3128: l_cc_group_ids wsh_util_core.id_tab_type;
3129: l_cc_count_trip_rows NUMBER;
3130: l_cc_count_del_rows NUMBER;
3131: l_cc_count_grouping_rows NUMBER;
3132: l_del_rows_temp wsh_util_core.id_tab_type;
3133: --l_trip_name VARCHAR2(30);
3134: l_cc_trip_id wsh_util_core.id_tab_type;
3135: l_trip_id_tab wsh_util_core.id_tab_type;
3136: l_cc_del_rows wsh_util_core.id_tab_type;
3130: l_cc_count_del_rows NUMBER;
3131: l_cc_count_grouping_rows NUMBER;
3132: l_del_rows_temp wsh_util_core.id_tab_type;
3133: --l_trip_name VARCHAR2(30);
3134: l_cc_trip_id wsh_util_core.id_tab_type;
3135: l_trip_id_tab wsh_util_core.id_tab_type;
3136: l_cc_del_rows wsh_util_core.id_tab_type;
3137: l_cc_grouping_rows wsh_util_core.id_tab_type;
3138: l_cc_return_status VARCHAR2(1);
3131: l_cc_count_grouping_rows NUMBER;
3132: l_del_rows_temp wsh_util_core.id_tab_type;
3133: --l_trip_name VARCHAR2(30);
3134: l_cc_trip_id wsh_util_core.id_tab_type;
3135: l_trip_id_tab wsh_util_core.id_tab_type;
3136: l_cc_del_rows wsh_util_core.id_tab_type;
3137: l_cc_grouping_rows wsh_util_core.id_tab_type;
3138: l_cc_return_status VARCHAR2(1);
3139: -- l_trip_id NUMBER;
3132: l_del_rows_temp wsh_util_core.id_tab_type;
3133: --l_trip_name VARCHAR2(30);
3134: l_cc_trip_id wsh_util_core.id_tab_type;
3135: l_trip_id_tab wsh_util_core.id_tab_type;
3136: l_cc_del_rows wsh_util_core.id_tab_type;
3137: l_cc_grouping_rows wsh_util_core.id_tab_type;
3138: l_cc_return_status VARCHAR2(1);
3139: -- l_trip_id NUMBER;
3140:
3133: --l_trip_name VARCHAR2(30);
3134: l_cc_trip_id wsh_util_core.id_tab_type;
3135: l_trip_id_tab wsh_util_core.id_tab_type;
3136: l_cc_del_rows wsh_util_core.id_tab_type;
3137: l_cc_grouping_rows wsh_util_core.id_tab_type;
3138: l_cc_return_status VARCHAR2(1);
3139: -- l_trip_id NUMBER;
3140:
3141: l_cc_upd_dlvy_intmed_ship_to VARCHAR2(1);
3151: l_cc_del_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
3152: l_cc_det_attr_tab WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type;
3153: l_cc_trip_attr_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
3154: l_cc_stop_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
3155: l_cc_in_ids wsh_util_core.id_tab_type;
3156: l_cc_fail_ids wsh_util_core.id_tab_type;
3157:
3158: CURSOR del_cur(p_dlvy_id NUMBER) IS
3159: SELECT SHIP_METHOD_CODE, INTMED_SHIP_TO_LOCATION_ID
3152: l_cc_det_attr_tab WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type;
3153: l_cc_trip_attr_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
3154: l_cc_stop_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
3155: l_cc_in_ids wsh_util_core.id_tab_type;
3156: l_cc_fail_ids wsh_util_core.id_tab_type;
3157:
3158: CURSOR del_cur(p_dlvy_id NUMBER) IS
3159: SELECT SHIP_METHOD_CODE, INTMED_SHIP_TO_LOCATION_ID
3160: FROM wsh_new_deliveries
3166: FROM wsh_trips
3167: WHERE trip_id = p_trip_id;
3168: --and SHIP_METHOD_CODE is not null;
3169:
3170: l_line_rows wsh_util_core.id_tab_type:=p_line_rows;
3171: --Compatibility Changes
3172:
3173: /* J TP Release */
3174: l_trip_ids wsh_util_core.id_tab_type;
3170: l_line_rows wsh_util_core.id_tab_type:=p_line_rows;
3171: --Compatibility Changes
3172:
3173: /* J TP Release */
3174: l_trip_ids wsh_util_core.id_tab_type;
3175: l_trip_names wsh_util_core.column_tab_type;
3176: l_tripindex NUMBER;
3177:
3178: -- deliveryMerge
3171: --Compatibility Changes
3172:
3173: /* J TP Release */
3174: l_trip_ids wsh_util_core.id_tab_type;
3175: l_trip_names wsh_util_core.column_tab_type;
3176: l_tripindex NUMBER;
3177:
3178: -- deliveryMerge
3179: l_return_status VARCHAR2(1);
3205: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_AUTOCREATE.AUTOCREATE_DEL_ACROSS_ORGS',WSH_DEBUG_SV.C_PROC_LEVEL);
3206: END IF;--}
3207: --
3208: --Compatibility Changes
3209: IF wsh_util_core.fte_is_installed = 'Y' THEN--{
3210:
3211: WSH_FTE_COMP_CONSTRAINT_PKG.validate_constraint_main(
3212: p_api_version_number => 1.0,
3213: p_init_msg_list => FND_API.G_FALSE,
3239: END IF;--}
3240: --
3241:
3242:
3243: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
3244: OR (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR AND l_cc_failed_records.COUNT=l_line_rows.COUNT)
3245: THEN--{
3246: --
3247: -- Debug Statements
3240: --
3241:
3242:
3243: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
3244: OR (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR AND l_cc_failed_records.COUNT=l_line_rows.COUNT)
3245: THEN--{
3246: --
3247: -- Debug Statements
3248: --
3252: --
3253: RETURN;
3254: END IF;--}
3255:
3256: IF l_cc_failed_records.COUNT>0 AND x_return_status=wsh_util_core.g_ret_sts_error THEN--{
3257: IF l_debug_on THEN--{
3258: WSH_DEBUG_SV.logmsg(l_module_name,'All dels have failed compatibility -> delivery and Trip not created');
3259: END IF;--}
3260: -- if one one delivery fails for auto create trip, all the lines should
3259: END IF;--}
3260: -- if one one delivery fails for auto create trip, all the lines should
3261: -- should be unassigned from the deliveries and the action should
3262: -- fail for all the lines. (pack J 2862777)
3263: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3264: fnd_message.set_name('WSH', 'WSH_COMP_ACT_FAIL');
3265: wsh_util_core.add_message(x_return_status);
3266: IF l_debug_on THEN--{
3267: WSH_DEBUG_SV.pop(l_module_name);
3261: -- should be unassigned from the deliveries and the action should
3262: -- fail for all the lines. (pack J 2862777)
3263: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3264: fnd_message.set_name('WSH', 'WSH_COMP_ACT_FAIL');
3265: wsh_util_core.add_message(x_return_status);
3266: IF l_debug_on THEN--{
3267: WSH_DEBUG_SV.pop(l_module_name);
3268: END IF;--}
3269: --
3267: WSH_DEBUG_SV.pop(l_module_name);
3268: END IF;--}
3269: --
3270: RETURN;
3271: ELSIF l_cc_line_groups.COUNT>0 AND x_return_status=wsh_util_core.g_ret_sts_error THEN--}{
3272:
3273: --1. get the group ids by which the constraints API has grouped the lines
3274: l_cc_count_group_ids:=1;
3275: FOR i in l_cc_line_groups.FIRST..l_cc_line_groups.LAST LOOP --{
3382: END LOOP;--}
3383: END IF;--}
3384: END IF; --}
3385:
3386: IF (l_cc_return_status is not null AND l_cc_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN--{
3387: x_return_status:=l_cc_return_status;
3388: ELSIF (l_cc_return_status is not null AND l_cc_return_status=WSH_UTIL_CORE.G_RET_STS_WARNING AND x_return_status=WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN--}{
3389: x_return_status:=l_cc_return_status;
3390: ELSE--}{
3384: END IF; --}
3385:
3386: IF (l_cc_return_status is not null AND l_cc_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN--{
3387: x_return_status:=l_cc_return_status;
3388: ELSIF (l_cc_return_status is not null AND l_cc_return_status=WSH_UTIL_CORE.G_RET_STS_WARNING AND x_return_status=WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN--}{
3389: x_return_status:=l_cc_return_status;
3390: ELSE--}{
3391: l_cc_return_status:=x_return_status;
3392: END IF;--}
3483: wsh_debug_sv.log(l_module_name,'Return Status After Calling Autocreate_Del_across_orgs',x_return_status);
3484: END IF;--}
3485:
3486:
3487: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR
3488: (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN--{
3489: --
3490: -- Debug Statements
3491: --
3484: END IF;--}
3485:
3486:
3487: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR
3488: (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN--{
3489: --
3490: -- Debug Statements
3491: --
3492: IF l_debug_on THEN--{
3502: l_cc_failed_records.DELETE;
3503: l_cc_line_groups.DELETE;
3504: l_cc_group_ids.DELETE;
3505: l_id_tab_temp.DELETE;
3506: IF wsh_util_core.fte_is_installed = 'Y' THEN--{
3507:
3508: WSH_FTE_COMP_CONSTRAINT_PKG.validate_constraint_main(
3509: p_api_version_number => 1.0,
3510: p_init_msg_list => FND_API.G_FALSE,
3536: wsh_debug_sv.log(l_module_name,'group_info count After Calling validate_constraint_main',l_cc_group_info.COUNT);
3537: END IF;--}
3538: --
3539:
3540: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN--{
3541: --
3542: -- Debug Statements
3543: --
3544: IF l_debug_on THEN--{
3548: RETURN;
3549: END IF;--}
3550:
3551: --do nothing if all dels have errored out (deliveries shud be created and trip shud not be
3552: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR AND l_cc_failed_records.COUNT > 0) THEN--{
3553: IF l_debug_on THEN--{
3554: WSH_DEBUG_SV.logmsg(l_module_name,'All dels have failed compatibility -> Trip not created');
3555: END IF;--}
3556: -- if one one delivery fails for auto create trip, all the lines should
3555: END IF;--}
3556: -- if one one delivery fails for auto create trip, all the lines should
3557: -- should be unassigned from the deliveries and the action should
3558: -- fail for all the lines. (pack J 2862777)
3559: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3560: fnd_message.set_name('WSH', 'WSH_COMP_ACT_FAIL');
3561: wsh_util_core.add_message(x_return_status);
3562: IF l_debug_on THEN--{
3563: WSH_DEBUG_SV.pop(l_module_name);
3557: -- should be unassigned from the deliveries and the action should
3558: -- fail for all the lines. (pack J 2862777)
3559: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3560: fnd_message.set_name('WSH', 'WSH_COMP_ACT_FAIL');
3561: wsh_util_core.add_message(x_return_status);
3562: IF l_debug_on THEN--{
3563: WSH_DEBUG_SV.pop(l_module_name);
3564: END IF;--}
3565: --
3563: WSH_DEBUG_SV.pop(l_module_name);
3564: END IF;--}
3565: --
3566: RETURN;
3567: ELSIF l_cc_line_groups.COUNT>0 AND x_return_status=wsh_util_core.g_ret_sts_error THEN--}{
3568:
3569: --1. get the group ids by which the constraints API has grouped the lines
3570: l_cc_count_group_ids:=1;
3571: FOR i in l_cc_line_groups.FIRST..l_cc_line_groups.LAST LOOP --{
3613: IF l_debug_on THEN --{
3614: wsh_debug_sv.log(l_module_name,'Return status from WSH_NEW_DELIVERY_ACTIONS.Adjust_Planned_Flag ', l_return_status);
3615: END IF;--}
3616:
3617: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN--{
3618: raise Adjust_Planned_Flag_Err;
3619: END IF; --}
3620:
3621: /* J TP Release */
3660: l_cc_trip_id(l_cc_trip_id.COUNT+1):=l_trip_ids(l_tripindex);
3661: END LOOP;--}
3662: END IF;--}
3663:
3664: IF (l_cc_return_status is not null AND l_cc_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN--{
3665: x_return_status:=l_cc_return_status;
3666: ELSIF (l_cc_return_status is not null AND l_cc_return_status=WSH_UTIL_CORE.G_RET_STS_WARNING AND x_return_status=WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN--}{
3667: x_return_status:=l_cc_return_status;
3668: ELSE --}{
3662: END IF;--}
3663:
3664: IF (l_cc_return_status is not null AND l_cc_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN--{
3665: x_return_status:=l_cc_return_status;
3666: ELSIF (l_cc_return_status is not null AND l_cc_return_status=WSH_UTIL_CORE.G_RET_STS_WARNING AND x_return_status=WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN--}{
3667: x_return_status:=l_cc_return_status;
3668: ELSE --}{
3669: l_cc_return_status:=x_return_status;
3670: END IF;--}
3692: IF l_debug_on THEN --{
3693: wsh_debug_sv.log(l_module_name,'Return status from WSH_NEW_DELIVERY_ACTIONS.Adjust_Planned_Flag ', l_return_status);
3694: END IF;--}
3695:
3696: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN--{
3697: raise Adjust_Planned_Flag_Err;
3698: END IF; --}
3699:
3700: /* J TP Release */
3755: IF l_debug_on THEN --{
3756: wsh_debug_sv.log(l_module_name,'Return status from WSH_NEW_DELIVERY_ACTIONS.Adjust_Planned_Flag ', l_return_status);
3757: END IF;--}
3758:
3759: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN--{
3760: raise Adjust_Planned_Flag_Err;
3761: END IF; --}
3762:
3763: /* J TP Release */
3774: x_trip_rows:=l_trip_id_tab;
3775:
3776:
3777:
3778: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN--{
3779: --
3780: -- Debug Statements
3781: --
3782: IF l_debug_on THEN--{
3785: --
3786: RETURN;
3787: END IF;--}
3788:
3789: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3790:
3791: --
3792: -- Debug Statements
3793: --
3800:
3801: -- deliveryMerge
3802: WHEN Adjust_Planned_Flag_Err THEN
3803: FND_MESSAGE.SET_NAME('WSH', 'WSH_ADJUST_PLANNED_FLAG_ERR');
3804: WSH_UTIL_CORE.add_message(l_return_status,l_module_name);
3805: x_return_status := l_return_status;
3806:
3807: IF l_debug_on THEN--{
3808: WSH_DEBUG_SV.logmsg(l_module_name,'Adjust_Planned_Flag_Err exception has occured.',WSH_DEBUG_SV.C_EXCEP_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: --
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: --
3818: -- Debug Statements
3823: END IF;--}
3824: --
3825: END autocreate_del_trip;
3826:
3827: PROCEDURE assign_trip( p_line_rows IN wsh_util_core.id_tab_type,
3828: p_trip_id IN NUMBER,
3829: x_del_rows OUT NOCOPY wsh_util_core.id_tab_type,
3830: x_return_status OUT NOCOPY VARCHAR2) IS
3831:
3825: END autocreate_del_trip;
3826:
3827: PROCEDURE assign_trip( p_line_rows IN wsh_util_core.id_tab_type,
3828: p_trip_id IN NUMBER,
3829: x_del_rows OUT NOCOPY wsh_util_core.id_tab_type,
3830: x_return_status OUT NOCOPY VARCHAR2) IS
3831:
3832: l_grouping_rows wsh_util_core.id_tab_type;
3833: l_del_legs wsh_util_core.id_tab_type;
3828: p_trip_id IN NUMBER,
3829: x_del_rows OUT NOCOPY wsh_util_core.id_tab_type,
3830: x_return_status OUT NOCOPY VARCHAR2) IS
3831:
3832: l_grouping_rows wsh_util_core.id_tab_type;
3833: l_del_legs wsh_util_core.id_tab_type;
3834: l_org_rows wsh_util_core.id_tab_type;
3835:
3836: l_return_status VARCHAR2(1);
3829: x_del_rows OUT NOCOPY wsh_util_core.id_tab_type,
3830: x_return_status OUT NOCOPY VARCHAR2) IS
3831:
3832: l_grouping_rows wsh_util_core.id_tab_type;
3833: l_del_legs wsh_util_core.id_tab_type;
3834: l_org_rows wsh_util_core.id_tab_type;
3835:
3836: l_return_status VARCHAR2(1);
3837: reprice_required_err EXCEPTION;
3830: x_return_status OUT NOCOPY VARCHAR2) IS
3831:
3832: l_grouping_rows wsh_util_core.id_tab_type;
3833: l_del_legs wsh_util_core.id_tab_type;
3834: l_org_rows wsh_util_core.id_tab_type;
3835:
3836: l_return_status VARCHAR2(1);
3837: reprice_required_err EXCEPTION;
3838:
3875: x_del_rows => x_del_rows,
3876: x_grouping_rows => l_grouping_rows,
3877: x_return_status => x_return_status);
3878:
3879: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR
3880: (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3881: --
3882: -- Debug Statements
3883: --
3876: x_grouping_rows => l_grouping_rows,
3877: x_return_status => x_return_status);
3878:
3879: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR
3880: (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3881: --
3882: -- Debug Statements
3883: --
3884: IF l_debug_on THEN
3902: x_leg_rows => l_del_legs,
3903: x_return_status => x_return_status
3904: );
3905:
3906: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR
3907: (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3908: --
3909: -- Debug Statements
3910: --
3903: x_return_status => x_return_status
3904: );
3905:
3906: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR
3907: (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3908: --
3909: -- Debug Statements
3910: --
3911: IF l_debug_on THEN
3928: EXCEPTION
3929: WHEN reprice_required_err THEN
3930: x_return_status := l_return_status;
3931: fnd_message.set_name('WSH', 'WSH_REPRICE_REQUIRED_ERR');
3932: wsh_util_core.add_message(x_return_status);
3933: --
3934: -- Debug Statements
3935: --
3936: IF l_debug_on THEN
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
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
3947: --
3953: END assign_trip;
3954:
3955:
3956: PROCEDURE check_assign_trip (
3957: p_del_rows IN wsh_util_core.id_tab_type,
3958: p_trip_id IN NUMBER,
3959: p_pickup_stop_id IN NUMBER := NULL,
3960: p_dropoff_stop_id IN NUMBER := NULL,
3961: p_pickup_location_id IN NUMBER := NULL,
4028: WSH_DEBUG_SV.log(l_module_name,'P_DROPOFF_ARR_DATE',P_DROPOFF_ARR_DATE);
4029: WSH_DEBUG_SV.log(l_module_name,'P_DROPOFF_DEP_DATE',P_DROPOFF_DEP_DATE);
4030: END IF;
4031: --
4032: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4033:
4034: IF (p_pickup_location_id = p_dropoff_location_id) THEN
4035: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_LOCATIONS_SAME');
4036: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4032: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4033:
4034: IF (p_pickup_location_id = p_dropoff_location_id) THEN
4035: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_LOCATIONS_SAME');
4036: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4037: wsh_util_core.add_message(x_return_status);
4038: --
4039: -- Debug Statements
4040: --
4033:
4034: IF (p_pickup_location_id = p_dropoff_location_id) THEN
4035: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_LOCATIONS_SAME');
4036: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4037: wsh_util_core.add_message(x_return_status);
4038: --
4039: -- Debug Statements
4040: --
4041: IF l_debug_on THEN
4069: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
4070: END IF;
4071: --
4072: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
4073: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4074: wsh_util_core.add_message(x_return_status);
4075: warn_flag := TRUE;
4076:
4077: --
4070: END IF;
4071: --
4072: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
4073: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4074: wsh_util_core.add_message(x_return_status);
4075: warn_flag := TRUE;
4076:
4077: --
4078: -- Debug Statements
4102: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
4103: END IF;
4104: --
4105: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
4106: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4107: wsh_util_core.add_message(x_return_status);
4108: warn_flag := TRUE;
4109:
4110: --
4103: END IF;
4104: --
4105: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
4106: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4107: wsh_util_core.add_message(x_return_status);
4108: warn_flag := TRUE;
4109:
4110: --
4111: -- Debug Statements
4133: p_pickup_stop_id => p_pickup_stop_id,
4134: p_dropoff_stop_id => p_dropoff_stop_id,
4135: x_return_status => x_return_status);
4136:
4137: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
4138: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
4139: num_warn := num_warn + 1;
4140: ELSE
4141: --
4134: p_dropoff_stop_id => p_dropoff_stop_id,
4135: x_return_status => x_return_status);
4136:
4137: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
4138: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
4139: num_warn := num_warn + 1;
4140: ELSE
4141: --
4142: -- Debug Statements
4157:
4158: END LOOP;
4159:
4160: IF (num_warn > 0) OR (num_error > 0) THEN
4161: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4162: END IF;
4163:
4164: --
4165: -- 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
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
4178: --
4220: -- other delivery getting dropoff or pickedup at the stop's location, convert the location for
4221: -- this stop to be the dummy location . Else create new dropoff stop with dummy loc
4222:
4223: PROCEDURE process_dels_for_internal_locs(
4224: p_del_rows IN wsh_util_core.id_tab_type,
4225: p_trip_id IN NUMBER,
4226: p_pickup_stop_id IN NUMBER := NULL,
4227: p_dropoff_stop_id IN NUMBER := NULL,
4228: p_pickup_location_id IN NUMBER := NULL,
4233: x_dropoff_stop_id OUT NOCOPY NUMBER,
4234: x_pickup_location_id OUT NOCOPY NUMBER,
4235: x_dropoff_location_id OUT NOCOPY NUMBER,
4236: x_dropoff_seq_num IN OUT NOCOPY NUMBER,
4237: x_internal_del_ids OUT NOCOPY wsh_util_core.id_tab_type,
4238: x_del_ids OUT NOCOPY wsh_util_core.id_tab_type
4239: )
4240: IS
4241: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'process_dels_for_internal_locs';
4234: x_pickup_location_id OUT NOCOPY NUMBER,
4235: x_dropoff_location_id OUT NOCOPY NUMBER,
4236: x_dropoff_seq_num IN OUT NOCOPY NUMBER,
4237: x_internal_del_ids OUT NOCOPY wsh_util_core.id_tab_type,
4238: x_del_ids OUT NOCOPY wsh_util_core.id_tab_type
4239: )
4240: IS
4241: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'process_dels_for_internal_locs';
4242: --
4275: l_trip_pickup_loc_id NUMBER;
4276: l_trip_dropoff_loc_id NUMBER;
4277: l_physical_stop_id NUMBER;
4278: l_find_stop NUMBER;
4279: l_trip_ids wsh_util_core.id_tab_type;
4280: l_dummy_trip_ids wsh_util_core.id_tab_type;
4281: l_getstops_stop_id wsh_util_core.id_tab_type;
4282: l_getstops_stop_loc_id wsh_util_core.id_tab_type;
4283: l_getstops_status_code WSH_UTIL_CORE.Column_Tab_Type;
4276: l_trip_dropoff_loc_id NUMBER;
4277: l_physical_stop_id NUMBER;
4278: l_find_stop NUMBER;
4279: l_trip_ids wsh_util_core.id_tab_type;
4280: l_dummy_trip_ids wsh_util_core.id_tab_type;
4281: l_getstops_stop_id wsh_util_core.id_tab_type;
4282: l_getstops_stop_loc_id wsh_util_core.id_tab_type;
4283: l_getstops_status_code WSH_UTIL_CORE.Column_Tab_Type;
4284: l_is_stop_pickup VARCHAR2(1):='N';
4277: l_physical_stop_id NUMBER;
4278: l_find_stop NUMBER;
4279: l_trip_ids wsh_util_core.id_tab_type;
4280: l_dummy_trip_ids wsh_util_core.id_tab_type;
4281: l_getstops_stop_id wsh_util_core.id_tab_type;
4282: l_getstops_stop_loc_id wsh_util_core.id_tab_type;
4283: l_getstops_status_code WSH_UTIL_CORE.Column_Tab_Type;
4284: l_is_stop_pickup VARCHAR2(1):='N';
4285: l_is_stop_dropoff VARCHAR2(1):='N';
4278: l_find_stop NUMBER;
4279: l_trip_ids wsh_util_core.id_tab_type;
4280: l_dummy_trip_ids wsh_util_core.id_tab_type;
4281: l_getstops_stop_id wsh_util_core.id_tab_type;
4282: l_getstops_stop_loc_id wsh_util_core.id_tab_type;
4283: l_getstops_status_code WSH_UTIL_CORE.Column_Tab_Type;
4284: l_is_stop_pickup VARCHAR2(1):='N';
4285: l_is_stop_dropoff VARCHAR2(1):='N';
4286: l_find_leg VARCHAR2(1);
4279: l_trip_ids wsh_util_core.id_tab_type;
4280: l_dummy_trip_ids wsh_util_core.id_tab_type;
4281: l_getstops_stop_id wsh_util_core.id_tab_type;
4282: l_getstops_stop_loc_id wsh_util_core.id_tab_type;
4283: l_getstops_status_code WSH_UTIL_CORE.Column_Tab_Type;
4284: l_is_stop_pickup VARCHAR2(1):='N';
4285: l_is_stop_dropoff VARCHAR2(1):='N';
4286: l_find_leg VARCHAR2(1);
4287: b_physical_loc_updated BOOLEAN:=FALSE;
4299: l_num_warn NUMBER := 0;
4300: bad_trip_stop EXCEPTION;
4301: l_internal_del_id_count NUMBER := 0;
4302: l_del_id_count NUMBER := 0;
4303: l_internal_del_ids wsh_util_core.id_tab_type;
4304: l_del_ids wsh_util_core.id_tab_type;
4305: b_checkstopupdate BOOLEAN:=TRUE;
4306: b_checkdels BOOLEAN:=FALSE;
4307: b_gotstops BOOLEAN:=FALSE;
4300: bad_trip_stop EXCEPTION;
4301: l_internal_del_id_count NUMBER := 0;
4302: l_del_id_count NUMBER := 0;
4303: l_internal_del_ids wsh_util_core.id_tab_type;
4304: l_del_ids wsh_util_core.id_tab_type;
4305: b_checkstopupdate BOOLEAN:=TRUE;
4306: b_checkdels BOOLEAN:=FALSE;
4307: b_gotstops BOOLEAN:=FALSE;
4308: l_del_phys_dropoff VARCHAR2(1);
4370: WSH_DEBUG_SV.log(l_module_name, 'p_dropoff_location_id', p_dropoff_location_id);
4371: END IF;
4372: --
4373: --
4374: x_return_status :=WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4375: x_pickup_stop_id :=p_pickup_stop_id;
4376: x_dropoff_stop_id :=p_dropoff_stop_id;
4377: x_pickup_location_id :=p_pickup_location_id;
4378: x_dropoff_location_id :=p_dropoff_location_id;
4408: WSH_LOCATIONS_PKG.Convert_internal_cust_location(
4409: p_internal_cust_location_id => l_trip_pickup_loc_id,
4410: x_internal_org_location_id => l_phys_trip_pickup_loc_id,
4411: x_return_status => l_return_status);
4412: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
4413: x_return_status:=l_return_status;
4414: RETURN;
4415: END IF;
4416: IF l_debug_on THEN
4507: x_pickup_location_id:=l_phys_trip_pickup_loc_id;
4508: END IF;
4509:
4510: ELSE--caller is WSH
4511: x_return_status:=WSH_UTIL_CORE.G_RET_STS_ERROR;
4512: FND_MESSAGE.SET_NAME('WSH','WSH_CANNOT_USE_DUMMY_PICKUP');
4513: IF l_debug_on THEN
4514: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.get_location_description',WSH_DEBUG_SV.C_PROC_LEVEL);
4515: END IF;
4510: ELSE--caller is WSH
4511: x_return_status:=WSH_UTIL_CORE.G_RET_STS_ERROR;
4512: FND_MESSAGE.SET_NAME('WSH','WSH_CANNOT_USE_DUMMY_PICKUP');
4513: IF l_debug_on THEN
4514: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.get_location_description',WSH_DEBUG_SV.C_PROC_LEVEL);
4515: END IF;
4516: FND_MESSAGE.SET_TOKEN('STOP_NAME',SUBSTR(WSH_UTIL_CORE.get_location_description(l_phys_trip_pickup_loc_id, 'NEW UI CODE'),1,60));
4517: wsh_util_core.add_message(x_return_status,l_module_name);
4518: IF l_debug_on THEN
4512: FND_MESSAGE.SET_NAME('WSH','WSH_CANNOT_USE_DUMMY_PICKUP');
4513: IF l_debug_on THEN
4514: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.get_location_description',WSH_DEBUG_SV.C_PROC_LEVEL);
4515: END IF;
4516: FND_MESSAGE.SET_TOKEN('STOP_NAME',SUBSTR(WSH_UTIL_CORE.get_location_description(l_phys_trip_pickup_loc_id, 'NEW UI CODE'),1,60));
4517: wsh_util_core.add_message(x_return_status,l_module_name);
4518: IF l_debug_on THEN
4519: WSH_DEBUG_SV.pop(l_module_name);
4520: END IF;
4513: IF l_debug_on THEN
4514: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.get_location_description',WSH_DEBUG_SV.C_PROC_LEVEL);
4515: END IF;
4516: FND_MESSAGE.SET_TOKEN('STOP_NAME',SUBSTR(WSH_UTIL_CORE.get_location_description(l_phys_trip_pickup_loc_id, 'NEW UI CODE'),1,60));
4517: wsh_util_core.add_message(x_return_status,l_module_name);
4518: IF l_debug_on THEN
4519: WSH_DEBUG_SV.pop(l_module_name);
4520: END IF;
4521: RETURN;
4554: p_internal_cust_location_id => l_dropoff_location_id,
4555: x_internal_org_location_id => l_phys_del_dropoff_loc_id,
4556: x_return_status => l_return_status);
4557:
4558: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
4559: x_return_status:=l_return_status;
4560: RETURN;
4561: END IF;
4562:
4725: IF get_stop_location%isopen THEN
4726: close get_stop_location;
4727: END IF;
4728: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
4729: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4730: wsh_util_core.add_message(x_return_status);
4731: --
4732: -- Debug Statements
4733: --
4726: close get_stop_location;
4727: END IF;
4728: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
4729: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4730: wsh_util_core.add_message(x_return_status);
4731: --
4732: -- Debug Statements
4733: --
4734: IF l_debug_on THEN
4736: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:BAD_TRIP_STOP');
4737: 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:
4737: 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
4749:
4750: END process_dels_for_internal_locs;
4751:
4752: PROCEDURE assign_trip(
4753: p_del_rows IN wsh_util_core.id_tab_type,
4754: p_trip_id IN NUMBER,
4755: p_pickup_stop_id IN NUMBER := NULL,
4756: p_pickup_stop_seq IN NUMBER := NULL,
4757: p_dropoff_stop_id IN NUMBER := NULL,
4781: WHERE st.stop_location_id = l_loc_id AND
4782: dg.drop_off_stop_id = st.stop_id AND
4783: dg.delivery_id = l_del_id;
4784:
4785: l_del_legs wsh_util_core.id_tab_type;
4786: l_stop_id NUMBER;
4787: l_return_status VARCHAR2(1);
4788:
4789: stop_not_found EXCEPTION;
4804: --
4805: -- patchset J csun Stop Sequence Change
4806: l_stop_details_rec WSH_TRIP_STOPS_VALIDATIONS.stop_details;
4807:
4808: l_del_rows wsh_util_core.id_tab_type;
4809: l_pickup_stop_id NUMBER;
4810: l_dropoff_stop_id NUMBER;
4811: l_pickup_location_id NUMBER;
4812: l_dropoff_location_id NUMBER;
4811: l_pickup_location_id NUMBER;
4812: l_dropoff_location_id NUMBER;
4813: l_pickup_stop_seq NUMBER;
4814: l_dropoff_stop_seq NUMBER;
4815: l_trip_ids wsh_util_core.id_tab_type;
4816: l_dummy_trip_ids wsh_util_core.id_tab_type;
4817: l_del_ids wsh_util_core.id_tab_type;
4818: l_internal_del_ids wsh_util_core.id_tab_type;
4819:
4812: l_dropoff_location_id NUMBER;
4813: l_pickup_stop_seq NUMBER;
4814: l_dropoff_stop_seq NUMBER;
4815: l_trip_ids wsh_util_core.id_tab_type;
4816: l_dummy_trip_ids wsh_util_core.id_tab_type;
4817: l_del_ids wsh_util_core.id_tab_type;
4818: l_internal_del_ids wsh_util_core.id_tab_type;
4819:
4820: l_stop_seq_mode NUMBER; --SSN
4813: l_pickup_stop_seq NUMBER;
4814: l_dropoff_stop_seq NUMBER;
4815: l_trip_ids wsh_util_core.id_tab_type;
4816: l_dummy_trip_ids wsh_util_core.id_tab_type;
4817: l_del_ids wsh_util_core.id_tab_type;
4818: l_internal_del_ids wsh_util_core.id_tab_type;
4819:
4820: l_stop_seq_mode NUMBER; --SSN
4821:
4814: l_dropoff_stop_seq NUMBER;
4815: l_trip_ids wsh_util_core.id_tab_type;
4816: l_dummy_trip_ids wsh_util_core.id_tab_type;
4817: l_del_ids wsh_util_core.id_tab_type;
4818: l_internal_del_ids wsh_util_core.id_tab_type;
4819:
4820: l_stop_seq_mode NUMBER; --SSN
4821:
4822: --WF: CMR
4819:
4820: l_stop_seq_mode NUMBER; --SSN
4821:
4822: --WF: CMR
4823: l_del_old_carrier_ids WSH_UTIL_CORE.ID_TAB_TYPE;
4824: l_wf_rs VARCHAR2(1);
4825:
4826: BEGIN
4827: /* The validations for stop sequence number are already done before calling this */
4860: IF (p_del_rows.count = 0) THEN
4861: raise others;
4862: END IF;
4863:
4864: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4865:
4866: -- Pack J csun Stop Sequence Change
4867: -- This is the change to sequence trip stops by
4868: -- planned arrival date rather than stop sequence number.
4885: )THEN
4886: FND_MESSAGE.SET_NAME('WSH', 'WSH_INVALID_PLANNED_DATE');
4887: FND_MESSAGE.SET_TOKEN('PICKUP_DATE', fnd_date.date_to_displaydt(p_pickup_dep_date));
4888: FND_MESSAGE.SET_TOKEN('DROPOFF_DATE', fnd_date.date_to_displaydt(p_dropoff_arr_date));
4889: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4890: wsh_util_core.add_message(x_return_status);
4891: --
4892: -- Debug Statements
4893: --
4886: FND_MESSAGE.SET_NAME('WSH', 'WSH_INVALID_PLANNED_DATE');
4887: FND_MESSAGE.SET_TOKEN('PICKUP_DATE', fnd_date.date_to_displaydt(p_pickup_dep_date));
4888: FND_MESSAGE.SET_TOKEN('DROPOFF_DATE', fnd_date.date_to_displaydt(p_dropoff_arr_date));
4889: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4890: wsh_util_core.add_message(x_return_status);
4891: --
4892: -- Debug Statements
4893: --
4894: IF l_debug_on THEN
4915: p_dropoff_arr_date => p_dropoff_arr_date,
4916: p_dropoff_dep_date => p_dropoff_dep_date,
4917: x_return_status => x_return_status);
4918:
4919: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
4920: --
4921: -- Debug Statements
4922: --
4923: IF l_debug_on THEN
4959: x_dropoff_seq_num => l_dropoff_stop_seq,
4960: x_del_ids => l_del_ids,
4961: x_internal_del_ids => l_internal_del_ids);
4962:
4963: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
4964: IF l_debug_on THEN
4965: WSH_DEBUG_SV.log(l_module_name,'process_dels_for_internal_locs return_status',x_return_status);
4966: WSH_DEBUG_SV.pop(l_module_name);
4967: END IF;
5003: IF l_debug_on THEN
5004: WSH_DEBUG_SV.log(l_module_name,'wsh_delivery_legs_actions.assign_deliveries return_status',l_return_status);
5005: END IF;
5006:
5007: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
5008: x_return_status := l_return_status;
5009: IF l_debug_on THEN
5010: WSH_DEBUG_SV.pop(l_module_name);
5011: END IF;
5009: IF l_debug_on THEN
5010: WSH_DEBUG_SV.pop(l_module_name);
5011: END IF;
5012: RETURN;
5013: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
5014: x_return_status := l_return_status;
5015: END IF;
5016: END IF;
5017:
5041: IF l_debug_on THEN
5042: WSH_DEBUG_SV.log(l_module_name,'wsh_delivery_legs_actions.assign_deliveries return_status',l_return_status);
5043: END IF;
5044:
5045: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
5046: x_return_status := l_return_status;
5047: IF l_debug_on THEN
5048: WSH_DEBUG_SV.pop(l_module_name);
5049: END IF;
5047: IF l_debug_on THEN
5048: WSH_DEBUG_SV.pop(l_module_name);
5049: END IF;
5050: RETURN;
5051: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
5052: x_return_status := l_return_status;
5053: END IF;
5054: END IF;
5055:
5070: IF l_debug_on THEN
5071: WSH_DEBUG_SV.log(l_module_name,'return status from WSH_TRIP_STOPS_ACTIONS.RESET_STOP_SEQ_NUMBERS',l_return_status);
5072: END IF;
5073:
5074: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
5075: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
5076: l_num_warn := l_num_warn + 1;
5077: ELSE
5078: x_return_status := l_return_status;
5071: WSH_DEBUG_SV.log(l_module_name,'return status from WSH_TRIP_STOPS_ACTIONS.RESET_STOP_SEQ_NUMBERS',l_return_status);
5072: END IF;
5073:
5074: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
5075: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
5076: l_num_warn := l_num_warn + 1;
5077: ELSE
5078: x_return_status := l_return_status;
5079: --
5100: IF l_debug_on THEN
5101: wsh_debug_sv.log(l_module_name,'Return Status From WSH_TRIP_STOPS_VALIDATIONS.Validate_Stop_Dates for trip '||p_trip_id ,l_return_status);
5102: END IF;
5103:
5104: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
5105: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
5106: l_num_warn := l_num_warn + 1;
5107: ELSE
5108: x_return_status := l_return_status;
5101: wsh_debug_sv.log(l_module_name,'Return Status From WSH_TRIP_STOPS_VALIDATIONS.Validate_Stop_Dates for trip '||p_trip_id ,l_return_status);
5102: END IF;
5103:
5104: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
5105: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
5106: l_num_warn := l_num_warn + 1;
5107: ELSE
5108: x_return_status := l_return_status;
5109: --
5126: p_del_ids => p_del_rows,
5127: p_del_old_carrier_ids => l_del_old_carrier_ids,
5128: x_return_status => l_wf_rs);
5129: */
5130: IF (l_num_warn > 0 AND x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
5131: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5132: END IF;
5133:
5134: --
5127: p_del_old_carrier_ids => l_del_old_carrier_ids,
5128: x_return_status => l_wf_rs);
5129: */
5130: IF (l_num_warn > 0 AND x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
5131: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5132: END IF;
5133:
5134: --
5135: -- Debug Statements
5140: --
5141: EXCEPTION
5142: WHEN stop_not_found THEN
5143: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
5144: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5145: wsh_util_core.add_message(x_return_status);
5146: --
5147: -- Debug Statements
5148: --
5141: EXCEPTION
5142: WHEN stop_not_found THEN
5143: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
5144: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5145: wsh_util_core.add_message(x_return_status);
5146: --
5147: -- Debug Statements
5148: --
5149: IF l_debug_on THEN
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
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
5160: --
5261: WSH_DEBUG_SV.log(l_module_name,'P_GROSS_WEIGHT',P_GROSS_WEIGHT);
5262: WSH_DEBUG_SV.log(l_module_name,'P_VOLUME',P_VOLUME);
5263: END IF;
5264: --
5265: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5266:
5267: -- Return if stop is null or if W/V is null
5268: IF p_stop_id is NULL OR (p_gross_weight is NULL AND p_volume is NULL) THEN
5269: IF l_debug_on THEN
5302: l_trip_org_id;
5303: IF (trip_info%NOTFOUND) THEN
5304: CLOSE trip_info;
5305: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NOT_FOUND');
5306: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5307: wsh_util_core.add_message(x_return_status);
5308: END IF;
5309: CLOSE trip_info;
5310: END IF;
5303: IF (trip_info%NOTFOUND) THEN
5304: CLOSE trip_info;
5305: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NOT_FOUND');
5306: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5307: wsh_util_core.add_message(x_return_status);
5308: END IF;
5309: CLOSE trip_info;
5310: END IF;
5311:
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);
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);
5383: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5384: END IF;
5385:
5386: END calc_stop_fill_percent;
5387:
5388: PROCEDURE trip_weight_volume( p_trip_rows IN wsh_util_core.id_tab_type,
5389: p_override_flag IN VARCHAR2,
5390: p_calc_wv_if_frozen IN VARCHAR2,
5391: p_start_departure_date IN DATE,
5392: p_calc_del_wv IN VARCHAR2,
5581: WSH_DEBUG_SV.log(l_module_name,'P_CALC_DEL_WV',P_CALC_DEL_WV);
5582: WSH_DEBUG_SV.log(l_module_name,'P_SUPPRESS_ERRORS',P_SUPPRESS_ERRORS);
5583: END IF;
5584: --
5585: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5586: IF (p_trip_rows.count = 0) THEN
5587: raise others;
5588: END IF;
5589: FOR i IN 1..p_trip_rows.count LOOP
5605: FETCH trip_info INTO l_trip_max_weight, l_trip_max_volume, l_trip_min_fill, l_trip_weight_uom, l_trip_volume_uom, l_trip_org_id;
5606: IF (trip_info%NOTFOUND) THEN
5607: CLOSE trip_info;
5608: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NOT_FOUND');
5609: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5610: wsh_util_core.add_message(x_return_status);
5611: goto wt_vol_error;
5612: END IF;
5613: CLOSE trip_info;
5606: IF (trip_info%NOTFOUND) THEN
5607: CLOSE trip_info;
5608: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NOT_FOUND');
5609: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5610: wsh_util_core.add_message(x_return_status);
5611: goto wt_vol_error;
5612: END IF;
5613: CLOSE trip_info;
5614: END IF;
5626: --
5627: -- Debug Statements
5628: --
5629: IF l_debug_on THEN
5630: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_ORG_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5631: END IF;
5632: --
5633: FND_MESSAGE.SET_TOKEN('l_vehicle_org', WSH_UTIL_CORE.Get_Org_Name(l_vehicle_org_id));
5634: --End of Bug 2415809
5629: IF l_debug_on THEN
5630: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_ORG_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5631: END IF;
5632: --
5633: FND_MESSAGE.SET_TOKEN('l_vehicle_org', WSH_UTIL_CORE.Get_Org_Name(l_vehicle_org_id));
5634: --End of Bug 2415809
5635: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5636: wsh_util_core.add_message(x_return_status);
5637: l_trip_num_warn:=l_trip_num_warn+1;
5631: END IF;
5632: --
5633: FND_MESSAGE.SET_TOKEN('l_vehicle_org', WSH_UTIL_CORE.Get_Org_Name(l_vehicle_org_id));
5634: --End of Bug 2415809
5635: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5636: wsh_util_core.add_message(x_return_status);
5637: l_trip_num_warn:=l_trip_num_warn+1;
5638: l_fill_basis := 'W';
5639: END IF;
5632: --
5633: FND_MESSAGE.SET_TOKEN('l_vehicle_org', WSH_UTIL_CORE.Get_Org_Name(l_vehicle_org_id));
5634: --End of Bug 2415809
5635: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5636: wsh_util_core.add_message(x_return_status);
5637: l_trip_num_warn:=l_trip_num_warn+1;
5638: l_fill_basis := 'W';
5639: END IF;
5640:
5659: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5660: END IF;
5661: --
5662: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
5663: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5664: wsh_util_core.add_message(x_return_status);
5665: goto wt_vol_error;
5666: END IF;
5667:
5660: END IF;
5661: --
5662: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
5663: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5664: wsh_util_core.add_message(x_return_status);
5665: goto wt_vol_error;
5666: END IF;
5667:
5668: OPEN last_stop(p_trip_rows(i));
5683: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5684: END IF;
5685: --
5686: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
5687: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5688: wsh_util_core.add_message(x_return_status);
5689: goto wt_vol_error;
5690: END IF;
5691: CLOSE last_stop;
5684: END IF;
5685: --
5686: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
5687: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5688: wsh_util_core.add_message(x_return_status);
5689: goto wt_vol_error;
5690: END IF;
5691: CLOSE last_stop;
5692:
5707: END IF;
5708: -- end bug 2366163
5709: -- bug 2732503 , need a message only when FTE is installed
5710: -- else continue
5711: IF (wsh_util_core.fte_is_installed <> 'Y') THEN
5712: goto loop_end;
5713: END IF;
5714:
5715: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_UOM_NULL');
5722: --
5723: FND_MESSAGE.SET_TOKEN('stop_name',wsh_trip_stops_pvt.get_name(l_start_stop_id,p_caller));
5724: -- bug 2732503 , need a warning message only when FTE is installed
5725: -- still will go to wt_vol_error
5726: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5727: wsh_util_core.add_message(x_return_status);
5728: l_num_warn := l_num_warn + 1;
5729: goto loop_end;
5730: END IF;
5723: FND_MESSAGE.SET_TOKEN('stop_name',wsh_trip_stops_pvt.get_name(l_start_stop_id,p_caller));
5724: -- bug 2732503 , need a warning message only when FTE is installed
5725: -- still will go to wt_vol_error
5726: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5727: wsh_util_core.add_message(x_return_status);
5728: l_num_warn := l_num_warn + 1;
5729: goto loop_end;
5730: END IF;
5731: END IF;
5776: -- end bug 2366163
5777: -- bug 2732503 , need a message only when FTE is installed
5778: -- this will skip calculating wt/vol for all the stops
5779: -- else continue
5780: IF (wsh_util_core.fte_is_installed <> 'Y') THEN
5781: goto loop_end;
5782: END IF;
5783:
5784: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_UOM_NULL');
5791: --
5792: FND_MESSAGE.SET_TOKEN('stop_name',wsh_trip_stops_pvt.get_name(l_stop_id,p_caller));
5793: -- bug 2732503 , need a warning message only when FTE is installed
5794: -- still will go to wt_vol_error
5795: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5796: wsh_util_core.add_message(x_return_status);
5797: l_stop_num_warn := l_stop_num_warn + 1;
5798: goto continue_next;
5799: END IF;
5792: FND_MESSAGE.SET_TOKEN('stop_name',wsh_trip_stops_pvt.get_name(l_stop_id,p_caller));
5793: -- bug 2732503 , need a warning message only when FTE is installed
5794: -- still will go to wt_vol_error
5795: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5796: wsh_util_core.add_message(x_return_status);
5797: l_stop_num_warn := l_stop_num_warn + 1;
5798: goto continue_next;
5799: END IF;
5800:
5827: END IF;
5828: --
5829: l_volume := wsh_wv_utils.convert_uom(l_prev_vol_uom, l_stop_vol_uom, l_total_volume);
5830:
5831: g_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5832:
5833: IF l_debug_on THEN
5834: WSH_DEBUG_SV.logmsg(l_module_name,'Processing Pickup Deliveries ...');
5835: END IF;
5852: x_net_weight => l_net_weight,
5853: x_volume => l_volume,
5854: x_return_status => l_return_status);
5855:
5856: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
5857: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
5858: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_WT_VOL_FAILED');
5859: --
5860: -- Debug Statements
5853: x_volume => l_volume,
5854: x_return_status => l_return_status);
5855:
5856: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
5857: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
5858: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_WT_VOL_FAILED');
5859: --
5860: -- Debug Statements
5861: --
5863: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5864: END IF;
5865: --
5866: FND_MESSAGE.SET_TOKEN('del_name',wsh_new_deliveries_pvt.get_name(pkup_dl.d_id));
5867: g_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5868: wsh_util_core.add_message(g_return_status);
5869: END IF;
5870:
5871:
5864: END IF;
5865: --
5866: FND_MESSAGE.SET_TOKEN('del_name',wsh_new_deliveries_pvt.get_name(pkup_dl.d_id));
5867: g_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5868: wsh_util_core.add_message(g_return_status);
5869: END IF;
5870:
5871:
5872: ELSE
5955:
5956:
5957: END LOOP;
5958:
5959: IF (g_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
5960: x_return_status := g_return_status;
5961: l_stop_num_warn := l_stop_num_warn + 1;
5962: END IF;
5963:
6079: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6080: END IF;
6081: --
6082: FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(l_stop_id,p_caller));
6083: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6084: wsh_util_core.add_message(x_return_status);
6085: l_stop_num_warn := l_stop_num_warn + 1;
6086: END IF;
6087: END IF;
6080: END IF;
6081: --
6082: FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(l_stop_id,p_caller));
6083: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6084: wsh_util_core.add_message(x_return_status);
6085: l_stop_num_warn := l_stop_num_warn + 1;
6086: END IF;
6087: END IF;
6088:
6107: END IF;
6108: END IF;
6109:
6110: /* H integration - J: W/V Changes */
6111: IF WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y' AND p_override_flag = 'Y' THEN
6112: IF l_debug_on THEN
6113: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Fte_Load_Tender',WSH_DEBUG_SV.C_PROC_LEVEL);
6114: END IF;
6115: Fte_Load_Tender(
6119: p_volume => l_total_volume,
6120: p_fill_percent => l_stop_fill_percent,
6121: x_return_status => l_return_status);
6122:
6123: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
6124: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
6125: l_stop_num_warn := l_stop_num_warn + 1;
6126: ELSE
6127: x_return_status := l_return_status;
6120: p_fill_percent => l_stop_fill_percent,
6121: x_return_status => l_return_status);
6122:
6123: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
6124: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
6125: l_stop_num_warn := l_stop_num_warn + 1;
6126: ELSE
6127: x_return_status := l_return_status;
6128: IF l_debug_on THEN
6160: WHERE stop_id = l_stop_id;
6161:
6162: IF (SQL%NOTFOUND) THEN
6163: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
6164: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6165: wsh_util_core.add_message(x_return_status);
6166: goto wt_vol_error;
6167: END IF;
6168:
6161:
6162: IF (SQL%NOTFOUND) THEN
6163: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
6164: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6165: wsh_util_core.add_message(x_return_status);
6166: goto wt_vol_error;
6167: END IF;
6168:
6169: --R12 MDC
6177: wv_frozen_flag = 'N'
6178: WHERE stop_id = l_stop_id;
6179: IF (SQL%NOTFOUND) THEN
6180: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
6181: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6182: wsh_util_core.add_message(x_return_status);
6183: goto wt_vol_error;
6184: END IF;
6185: END IF;
6178: WHERE stop_id = l_stop_id;
6179: IF (SQL%NOTFOUND) THEN
6180: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
6181: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6182: wsh_util_core.add_message(x_return_status);
6183: goto wt_vol_error;
6184: END IF;
6185: END IF;
6186:
6233: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6234: END IF;
6235: --
6236: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
6237: wsh_util_core.add_message(x_return_status);
6238: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
6239: l_num_error := l_num_error + 1;
6240: ELSIF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
6241: l_num_warn := l_num_warn + 1;
6234: END IF;
6235: --
6236: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
6237: wsh_util_core.add_message(x_return_status);
6238: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
6239: l_num_error := l_num_error + 1;
6240: ELSIF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
6241: l_num_warn := l_num_warn + 1;
6242: END IF;
6236: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
6237: wsh_util_core.add_message(x_return_status);
6238: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
6239: l_num_error := l_num_error + 1;
6240: ELSIF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
6241: l_num_warn := l_num_warn + 1;
6242: END IF;
6243:
6244: <
6275: FND_MESSAGE.SET_TOKEN('NUM_ERROR',l_num_error);
6276: FND_MESSAGE.SET_TOKEN('NUM_SUCCESS',p_trip_rows.count - l_num_error - l_num_warn);
6277: END IF;
6278: IF (p_trip_rows.count = l_num_error) THEN
6279: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6280: ELSE
6281: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6282: END IF;
6283:
6277: END IF;
6278: IF (p_trip_rows.count = l_num_error) THEN
6279: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6280: ELSE
6281: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6282: END IF;
6283:
6284: wsh_util_core.add_message(x_return_status);
6285:
6280: ELSE
6281: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6282: END IF;
6283:
6284: wsh_util_core.add_message(x_return_status);
6285:
6286: ELSE
6287: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6288: END IF;
6283:
6284: wsh_util_core.add_message(x_return_status);
6285:
6286: ELSE
6287: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6288: END IF;
6289:
6290: IF l_debug_on THEN
6291: WSH_DEBUG_SV.log(l_module_name,'RET STATUS-'||x_return_status);
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
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
6312: --
6387: END IF;
6388:
6389: END LOOP;
6390:
6391: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6392:
6393: --
6394: -- Debug Statements
6395: --
6407: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6408: END IF;
6409: --
6410: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_id));
6411: wsh_util_core.add_message(x_return_status);
6412: --
6413: -- Debug Statements
6414: --
6415: IF l_debug_on THEN
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
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
6426: --
6431: --
6432: END validate_stop_sequence;
6433:
6434: PROCEDURE Check_Unassign_Trip(
6435: p_del_rows IN wsh_util_core.id_tab_type,
6436: x_trip_rows OUT NOCOPY wsh_util_core.id_tab_type,
6437: x_return_status OUT NOCOPY VARCHAR2) IS
6438:
6439: cnt NUMBER := 0;
6432: END validate_stop_sequence;
6433:
6434: PROCEDURE Check_Unassign_Trip(
6435: p_del_rows IN wsh_util_core.id_tab_type,
6436: x_trip_rows OUT NOCOPY wsh_util_core.id_tab_type,
6437: x_return_status OUT NOCOPY VARCHAR2) IS
6438:
6439: cnt NUMBER := 0;
6440: l_count NUMBER;
6469: IF l_debug_on THEN
6470: WSH_DEBUG_SV.push(l_module_name);
6471: END IF;
6472: --
6473: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6474:
6475: IF (p_del_rows.count = 0) THEN
6476: raise others;
6477: END IF;
6503: CLOSE deltrip_cv;
6504:
6505: IF (cnt = 1) THEN -- cnt := cnt + 1 before we exit loop above, so cnt is always > 0, bug 2434673.
6506: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_MULTI_UNASSIGN_ERROR');
6507: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6508: wsh_util_core.add_message(x_return_status);
6509: END IF;
6510:
6511: --
6504:
6505: IF (cnt = 1) THEN -- cnt := cnt + 1 before we exit loop above, so cnt is always > 0, bug 2434673.
6506: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_MULTI_UNASSIGN_ERROR');
6507: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6508: wsh_util_core.add_message(x_return_status);
6509: END IF;
6510:
6511: --
6512: -- 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
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
6525: --
6531: END Check_Unassign_Trip;
6532:
6533:
6534: PROCEDURE Unassign_Trip(
6535: p_del_rows IN wsh_util_core.id_tab_type,
6536: p_trip_id IN NUMBER,
6537: x_return_status OUT NOCOPY VARCHAR2) IS
6538:
6539: cursor get_trip_info(p_trip_id in number) is
6548: and l.parent_delivery_leg_id is not null
6549: and l.pick_up_stop_id = s.stop_id
6550: and s.trip_id = p_trip_id;
6551:
6552: l_trip_tab WSH_UTIL_CORE.id_tab_type;
6553: l_mdc_del_tab WSH_UTIL_CORE.id_tab_type;
6554: l_return_status VARCHAR2(10);
6555: l_trip_name VARCHAR2(30);
6556: l_planned_flag wsh_trips.planned_flag%TYPE;
6549: and l.pick_up_stop_id = s.stop_id
6550: and s.trip_id = p_trip_id;
6551:
6552: l_trip_tab WSH_UTIL_CORE.id_tab_type;
6553: l_mdc_del_tab WSH_UTIL_CORE.id_tab_type;
6554: l_return_status VARCHAR2(10);
6555: l_trip_name VARCHAR2(30);
6556: l_planned_flag wsh_trips.planned_flag%TYPE;
6557: j NUMBER := 0;
6560: trip_not_found EXCEPTION;
6561: trip_planned EXCEPTION;
6562:
6563: --WF: CMR
6564: l_del_old_carrier_ids WSH_UTIL_CORE.ID_TAB_TYPE;
6565: l_wf_rs VARCHAR2(1);
6566:
6567:
6568: --
6598: END IF;
6599: CLOSE get_trip_info;
6600:
6601: IF l_trip_name IS NULL THEN
6602: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6603: raise trip_not_found;
6604: END IF;
6605:
6606: IF l_planned_flag = 'F' THEN
6603: raise trip_not_found;
6604: END IF;
6605:
6606: IF l_planned_flag = 'F' THEN
6607: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6608: raise trip_planned;
6609: END IF;
6610:
6611: --- MDC: Check if the deliveries are assigned to consol deliveries at this trip, and unassgin them from the consol delas well.
6627: p_parent_del => NULL,
6628: p_caller => 'WSH_UNASSIGN_TRIP',
6629: p_del_tab => l_mdc_del_tab,
6630: x_return_status => l_return_status);
6631: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
6632: raise unassign_deliveries_err;
6633: ELSE
6634: x_return_status := l_return_status;
6635: END IF;
6649: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_ACTIONS.UNASSIGN_DELIVERIES',WSH_DEBUG_SV.C_PROC_LEVEL);
6650: END IF;
6651: --
6652: wsh_delivery_legs_actions.unassign_deliveries( p_del_rows, p_trip_id, NULL, NULL, l_return_status);
6653: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
6654: raise unassign_deliveries_err;
6655: ELSE
6656: x_return_status := l_return_status;
6657: END IF;
6676: WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required(
6677: p_entity_type => 'TRIP',
6678: p_entity_ids => l_trip_tab,
6679: x_return_status => l_return_status);
6680: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
6681: raise reprice_required_err;
6682: ELSE
6683: IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
6684: l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
6679: x_return_status => l_return_status);
6680: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
6681: raise reprice_required_err;
6682: ELSE
6683: IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
6684: l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
6685: x_return_status := l_return_status;
6686: END IF;
6687: END IF;
6680: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
6681: raise reprice_required_err;
6682: ELSE
6683: IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
6684: l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
6685: x_return_status := l_return_status;
6686: END IF;
6687: END IF;
6688:
6697:
6698: WHEN trip_not_found THEN
6699: x_return_status := l_return_status;
6700: fnd_message.set_name('WSH', 'WSH_TRIP_NOT_FOUND');
6701: wsh_util_core.add_message(x_return_status);
6702:
6703: --
6704: -- Debug Statements
6705: --
6711: WHEN unassign_deliveries_err THEN
6712: x_return_status := l_return_status;
6713: fnd_message.set_name('WSH', 'WSH_DEL_UNASSIGN_ERROR');
6714: fnd_message.set_token('TRIP_NAME', l_trip_name);
6715: wsh_util_core.add_message(x_return_status);
6716:
6717: --
6718: -- Debug Statements
6719: --
6724: --
6725: WHEN reprice_required_err THEN
6726: x_return_status := l_return_status;
6727: fnd_message.set_name('WSH', 'WSH_REPRICE_REQUIRED_ERR');
6728: wsh_util_core.add_message(x_return_status);
6729:
6730: --
6731: -- Debug Statements
6732: --
6738: WHEN trip_planned THEN
6739: x_return_status := l_return_status;
6740: fnd_message.set_name('WSH', 'WSH_PLANNED_TRIP_NO_ACTION');
6741: fnd_message.set_token('TRIP_NAME', l_trip_name);
6742: wsh_util_core.add_message(x_return_status);
6743:
6744: --
6745: -- Debug Statements
6746: --
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
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
6761: --
6817: WSH_DEBUG_SV.log(l_module_name,'P_NET_WEIGHT',P_NET_WEIGHT);
6818: WSH_DEBUG_SV.log(l_module_name,'P_VOLUME',P_VOLUME);
6819: WSH_DEBUG_SV.log(l_module_name,'P_FILL_PERCENT',P_FILL_PERCENT);
6820: END IF;
6821: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6822:
6823: -- Get pvt type record structure for stop
6824: IF l_debug_on THEN
6825: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
6829: p_stop_id => p_stop_id,
6830: x_stop_rec => l_stop_rec,
6831: x_return_status => l_return_status);
6832:
6833: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
6834: x_return_status := l_return_status;
6835:
6836: IF l_debug_on THEN
6837: WSH_DEBUG_SV.pop(l_module_name);
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);
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);
6872: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
6895: -- Error : if none of the trips are validated or updated
6896: -- Unexpected Error
6897: -- ----------------------------------------------------------------------
6898: PROCEDURE Reset_Stop_Planned_Dates
6899: ( p_trip_ids IN wsh_util_core.id_tab_type,
6900: p_caller IN VARCHAR2,
6901: x_success_trip_ids OUT NOCOPY wsh_util_core.id_tab_type,
6902: x_return_status OUT NOCOPY VARCHAR2)
6903: IS
6897: -- ----------------------------------------------------------------------
6898: PROCEDURE Reset_Stop_Planned_Dates
6899: ( p_trip_ids IN wsh_util_core.id_tab_type,
6900: p_caller IN VARCHAR2,
6901: x_success_trip_ids OUT NOCOPY wsh_util_core.id_tab_type,
6902: x_return_status OUT NOCOPY VARCHAR2)
6903: IS
6904:
6905: CURSOR lock_stop(c_stop_id NUMBER) IS
6925:
6926: l_stop_rec c_trip_stops%ROWTYPE;
6927: l_last_stop_rec c_trip_stops%ROWTYPE;
6928:
6929: l_trip_ids wsh_util_core.id_tab_type;
6930: l_success_trip_ids wsh_util_core.id_tab_type;
6931: l_return_status VARCHAR2(1);
6932:
6933: l_update_flag BOOLEAN;
6926: l_stop_rec c_trip_stops%ROWTYPE;
6927: l_last_stop_rec c_trip_stops%ROWTYPE;
6928:
6929: l_trip_ids wsh_util_core.id_tab_type;
6930: l_success_trip_ids wsh_util_core.id_tab_type;
6931: l_return_status VARCHAR2(1);
6932:
6933: l_update_flag BOOLEAN;
6934: l_trip_error BOOLEAN;
6941: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RESET_STOP_PLANNED_DATES';
6942:
6943: stop_locked EXCEPTION;
6944:
6945: l_stop_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
6946: l_dbi_rs VARCHAR2(1); -- DBI Project
6947:
6948: PRAGMA EXCEPTION_INIT(stop_locked, -00054);
6949:
6948: PRAGMA EXCEPTION_INIT(stop_locked, -00054);
6949:
6950: BEGIN
6951: l_warn_num := 0;
6952: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6953: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
6954: IF l_debug_on IS NULL THEN
6955: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
6956: END IF;
6960: WSH_DEBUG_SV.log(l_module_name,'p_trip_ids.count ', p_trip_ids.count);
6961: END IF;
6962:
6963: IF p_trip_ids.count = 0 THEN
6964: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6965: IF l_debug_on THEN
6966: WSH_DEBUG_SV.logmsg(l_module_name,' p_trip_ids.count is zero');
6967: WSH_DEBUG_SV.pop(l_module_name);
6968: END IF;
7053:
7054: IF l_debug_on THEN
7055: WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
7056: END IF;
7057: IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
7058: x_return_status := l_dbi_rs;
7059: -- just pass this return status to caller API
7060: IF l_debug_on THEN
7061: WSH_DEBUG_SV.log(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
7104: ( p_trip_ids => l_trip_ids,
7105: p_caller => p_caller,
7106: x_success_trip_ids => l_success_trip_ids,
7107: x_return_status => l_return_status);
7108: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
7109: l_trip_error := TRUE;
7110: exit;
7111: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
7112: l_warn_num := l_warn_num + 1;
7107: x_return_status => l_return_status);
7108: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
7109: l_trip_error := TRUE;
7110: exit;
7111: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
7112: l_warn_num := l_warn_num + 1;
7113: END IF;
7114:
7115:
7122: i := p_trip_ids.next(i);
7123: END LOOP;
7124:
7125: IF x_success_trip_ids.count = 0 THEN
7126: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7127: ELSIF x_success_trip_ids.count < p_trip_ids.count
7128: OR l_warn_num > 0 THEN
7129: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
7130:
7125: IF x_success_trip_ids.count = 0 THEN
7126: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7127: ELSIF x_success_trip_ids.count < p_trip_ids.count
7128: OR l_warn_num > 0 THEN
7129: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
7130:
7131: END IF;
7132:
7133: IF l_debug_on 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);
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);
7154: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
7181: p_caller IN VARCHAR2,
7182: x_return_status OUT NOCOPY VARCHAR2)
7183: IS
7184:
7185: l_trip_ids wsh_util_core.id_tab_type;
7186: l_success_trip_ids wsh_util_core.id_tab_type;
7187: l_return_status VARCHAR2(1);
7188: l_debug_on BOOLEAN;
7189: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RESET_STOP_PLANNED_DATES';
7182: x_return_status OUT NOCOPY VARCHAR2)
7183: IS
7184:
7185: l_trip_ids wsh_util_core.id_tab_type;
7186: l_success_trip_ids wsh_util_core.id_tab_type;
7187: l_return_status VARCHAR2(1);
7188: l_debug_on BOOLEAN;
7189: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RESET_STOP_PLANNED_DATES';
7190:
7189: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RESET_STOP_PLANNED_DATES';
7190:
7191: BEGIN
7192:
7193: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
7194: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
7195: IF l_debug_on IS NULL THEN
7196: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
7197: END IF;
7201: WSH_DEBUG_SV.log(l_module_name,'p_trip_id ', p_trip_id);
7202: END IF;
7203:
7204: IF p_trip_id is NULL THEN
7205: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7206: IF l_debug_on THEN
7207: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR: Trip ID is NULL');
7208: WSH_DEBUG_SV.pop(l_module_name);
7209: END IF;
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);
7218: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR , WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
7219: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7220: ELSE
7221: x_return_status := l_return_status;
7222: END IF;
7215: p_caller => p_caller,
7216: x_success_trip_ids => l_success_trip_ids,
7217: x_return_status => l_return_status);
7218: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR , WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
7219: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7220: ELSE
7221: x_return_status := l_return_status;
7222: END IF;
7223:
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);
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);
7237: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
7318: -- Error : if none of the trips are validated or updated
7319: --
7320: -- ----------------------------------------------------------------------
7321: PROCEDURE Handle_Internal_Stops
7322: ( p_trip_ids IN wsh_util_core.id_tab_type,
7323: p_caller IN VARCHAR2,
7324: x_success_trip_ids OUT NOCOPY wsh_util_core.id_tab_type,
7325: x_return_status OUT NOCOPY VARCHAR2)
7326: IS
7320: -- ----------------------------------------------------------------------
7321: PROCEDURE Handle_Internal_Stops
7322: ( p_trip_ids IN wsh_util_core.id_tab_type,
7323: p_caller IN VARCHAR2,
7324: x_success_trip_ids OUT NOCOPY wsh_util_core.id_tab_type,
7325: x_return_status OUT NOCOPY VARCHAR2)
7326: IS
7327: --
7328: -- make a list of stops sequenced by PAD,
7550: AND drop_off_stop_id = c_dummy_stop_id
7551: AND rownum = 1;
7552:
7553: --
7554: l_getstops_stop_id wsh_util_core.id_tab_type;
7555: l_getstops_stop_loc_id wsh_util_core.id_tab_type;
7556: l_getstops_phys_loc_id wsh_util_core.id_tab_type;
7557: l_getstops_phys_stop_id wsh_util_core.id_tab_type;
7558: l_getstops_stop_seq_num wsh_util_core.id_tab_type;
7551: AND rownum = 1;
7552:
7553: --
7554: l_getstops_stop_id wsh_util_core.id_tab_type;
7555: l_getstops_stop_loc_id wsh_util_core.id_tab_type;
7556: l_getstops_phys_loc_id wsh_util_core.id_tab_type;
7557: l_getstops_phys_stop_id wsh_util_core.id_tab_type;
7558: l_getstops_stop_seq_num wsh_util_core.id_tab_type;
7559: l_getstops_pl_arr_date wsh_util_core.date_tab_type;
7552:
7553: --
7554: l_getstops_stop_id wsh_util_core.id_tab_type;
7555: l_getstops_stop_loc_id wsh_util_core.id_tab_type;
7556: l_getstops_phys_loc_id wsh_util_core.id_tab_type;
7557: l_getstops_phys_stop_id wsh_util_core.id_tab_type;
7558: l_getstops_stop_seq_num wsh_util_core.id_tab_type;
7559: l_getstops_pl_arr_date wsh_util_core.date_tab_type;
7560: l_getstops_pl_dep_date wsh_util_core.date_tab_type;
7553: --
7554: l_getstops_stop_id wsh_util_core.id_tab_type;
7555: l_getstops_stop_loc_id wsh_util_core.id_tab_type;
7556: l_getstops_phys_loc_id wsh_util_core.id_tab_type;
7557: l_getstops_phys_stop_id wsh_util_core.id_tab_type;
7558: l_getstops_stop_seq_num wsh_util_core.id_tab_type;
7559: l_getstops_pl_arr_date wsh_util_core.date_tab_type;
7560: l_getstops_pl_dep_date wsh_util_core.date_tab_type;
7561: l_getstops_org_stop_seq_num wsh_util_core.id_tab_type;
7554: l_getstops_stop_id wsh_util_core.id_tab_type;
7555: l_getstops_stop_loc_id wsh_util_core.id_tab_type;
7556: l_getstops_phys_loc_id wsh_util_core.id_tab_type;
7557: l_getstops_phys_stop_id wsh_util_core.id_tab_type;
7558: l_getstops_stop_seq_num wsh_util_core.id_tab_type;
7559: l_getstops_pl_arr_date wsh_util_core.date_tab_type;
7560: l_getstops_pl_dep_date wsh_util_core.date_tab_type;
7561: l_getstops_org_stop_seq_num wsh_util_core.id_tab_type;
7562: l_getstops_org_pl_arr_date wsh_util_core.date_tab_type;
7555: l_getstops_stop_loc_id wsh_util_core.id_tab_type;
7556: l_getstops_phys_loc_id wsh_util_core.id_tab_type;
7557: l_getstops_phys_stop_id wsh_util_core.id_tab_type;
7558: l_getstops_stop_seq_num wsh_util_core.id_tab_type;
7559: l_getstops_pl_arr_date wsh_util_core.date_tab_type;
7560: l_getstops_pl_dep_date wsh_util_core.date_tab_type;
7561: l_getstops_org_stop_seq_num wsh_util_core.id_tab_type;
7562: l_getstops_org_pl_arr_date wsh_util_core.date_tab_type;
7563: l_getstops_org_pl_dep_date wsh_util_core.date_tab_type;
7556: l_getstops_phys_loc_id wsh_util_core.id_tab_type;
7557: l_getstops_phys_stop_id wsh_util_core.id_tab_type;
7558: l_getstops_stop_seq_num wsh_util_core.id_tab_type;
7559: l_getstops_pl_arr_date wsh_util_core.date_tab_type;
7560: l_getstops_pl_dep_date wsh_util_core.date_tab_type;
7561: l_getstops_org_stop_seq_num wsh_util_core.id_tab_type;
7562: l_getstops_org_pl_arr_date wsh_util_core.date_tab_type;
7563: l_getstops_org_pl_dep_date wsh_util_core.date_tab_type;
7564: l_getstops_status_code WSH_UTIL_CORE.Column_Tab_Type;
7557: l_getstops_phys_stop_id wsh_util_core.id_tab_type;
7558: l_getstops_stop_seq_num wsh_util_core.id_tab_type;
7559: l_getstops_pl_arr_date wsh_util_core.date_tab_type;
7560: l_getstops_pl_dep_date wsh_util_core.date_tab_type;
7561: l_getstops_org_stop_seq_num wsh_util_core.id_tab_type;
7562: l_getstops_org_pl_arr_date wsh_util_core.date_tab_type;
7563: l_getstops_org_pl_dep_date wsh_util_core.date_tab_type;
7564: l_getstops_status_code WSH_UTIL_CORE.Column_Tab_Type;
7565: l_getstops_break_link_flags WSH_UTIL_CORE.Column_Tab_Type;
7558: l_getstops_stop_seq_num wsh_util_core.id_tab_type;
7559: l_getstops_pl_arr_date wsh_util_core.date_tab_type;
7560: l_getstops_pl_dep_date wsh_util_core.date_tab_type;
7561: l_getstops_org_stop_seq_num wsh_util_core.id_tab_type;
7562: l_getstops_org_pl_arr_date wsh_util_core.date_tab_type;
7563: l_getstops_org_pl_dep_date wsh_util_core.date_tab_type;
7564: l_getstops_status_code WSH_UTIL_CORE.Column_Tab_Type;
7565: l_getstops_break_link_flags WSH_UTIL_CORE.Column_Tab_Type;
7566:
7559: l_getstops_pl_arr_date wsh_util_core.date_tab_type;
7560: l_getstops_pl_dep_date wsh_util_core.date_tab_type;
7561: l_getstops_org_stop_seq_num wsh_util_core.id_tab_type;
7562: l_getstops_org_pl_arr_date wsh_util_core.date_tab_type;
7563: l_getstops_org_pl_dep_date wsh_util_core.date_tab_type;
7564: l_getstops_status_code WSH_UTIL_CORE.Column_Tab_Type;
7565: l_getstops_break_link_flags WSH_UTIL_CORE.Column_Tab_Type;
7566:
7567: -- both the cursors c_get_stops_PAD and c_get_stops_SSN select identical fields
7560: l_getstops_pl_dep_date wsh_util_core.date_tab_type;
7561: l_getstops_org_stop_seq_num wsh_util_core.id_tab_type;
7562: l_getstops_org_pl_arr_date wsh_util_core.date_tab_type;
7563: l_getstops_org_pl_dep_date wsh_util_core.date_tab_type;
7564: l_getstops_status_code WSH_UTIL_CORE.Column_Tab_Type;
7565: l_getstops_break_link_flags WSH_UTIL_CORE.Column_Tab_Type;
7566:
7567: -- both the cursors c_get_stops_PAD and c_get_stops_SSN select identical fields
7568: l_getstops_tmp c_get_stops_PAD%ROWTYPE;
7561: l_getstops_org_stop_seq_num wsh_util_core.id_tab_type;
7562: l_getstops_org_pl_arr_date wsh_util_core.date_tab_type;
7563: l_getstops_org_pl_dep_date wsh_util_core.date_tab_type;
7564: l_getstops_status_code WSH_UTIL_CORE.Column_Tab_Type;
7565: l_getstops_break_link_flags WSH_UTIL_CORE.Column_Tab_Type;
7566:
7567: -- both the cursors c_get_stops_PAD and c_get_stops_SSN select identical fields
7568: l_getstops_tmp c_get_stops_PAD%ROWTYPE;
7569:
7592:
7593: l_stop_seq_mode NUMBER;
7594:
7595: BEGIN
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;
7610: WSH_DEBUG_SV.log(l_module_name,'p_caller ', p_caller);
7611: END IF;
7612:
7613: IF p_trip_ids.count = 0 THEN
7614: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7615: IF l_debug_on THEN
7616: WSH_DEBUG_SV.logmsg(l_module_name,' p_trip_ids.count is zero');
7617: WSH_DEBUG_SV.pop(l_module_name);
7618: END IF;
7920:
7921: IF l_debug_on THEN
7922: WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
7923: END IF;
7924: IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
7925: x_return_status := l_dbi_rs;
7926: rollback to start_of_the_trip;
7927: -- just pass this return status to caller API
7928: IF l_debug_on THEN
7949: IF l_debug_on THEN
7950: wsh_debug_sv.log(l_module_name,'Return Status From WWSH_TRIP_STOPS_ACTIONS.RESET_STOP_SEQ_NUMBERS for trip '||p_trip_ids(i) ,l_return_status);
7951: END IF;
7952:
7953: IF l_return_status in ( WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
7954: raise reset_stop_seq_number_error;
7955: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
7956: l_warn_num := l_warn_num + 1;
7957: END IF;
7951: END IF;
7952:
7953: IF l_return_status in ( WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
7954: raise reset_stop_seq_number_error;
7955: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
7956: l_warn_num := l_warn_num + 1;
7957: END IF;
7958:
7959: WSH_TRIP_VALIDATIONS.Validate_Stop_Dates (
7963: IF l_debug_on THEN
7964: wsh_debug_sv.log(l_module_name,'Return Status From WSH_TRIP_STOPS_VALIDATIONS.Validate_Stop_Dates for trip '||p_trip_ids(i) ,l_return_status);
7965: END IF;
7966:
7967: IF l_return_status in ( WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
7968: raise validate_stop_date_error;
7969: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
7970: l_warn_num := l_warn_num + 1;
7971: END IF;
7965: END IF;
7966:
7967: IF l_return_status in ( WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
7968: raise validate_stop_date_error;
7969: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
7970: l_warn_num := l_warn_num + 1;
7971: END IF;
7972:
7973: END IF; -- PAD resequencing
7990: rollback to start_of_the_trip;
7991: FND_MESSAGE.SET_NAME('WSH', 'WSH_BETWEEN_LINKED_STOPS');
7992: FND_MESSAGE.SET_TOKEN('DUMMY_STOP_DATE', fnd_date.date_to_displaydt(l_getstops_pl_arr_date(j)));
7993: FND_MESSAGE.SET_TOKEN('DUMMY_LOCATION_DESP',
7994: WSH_UTIL_CORE.Get_Location_Description (l_getstops_stop_loc_id(j),'NEW UI CODE INFO'));
7995: FND_MESSAGE.SET_TOKEN('PHYSICAL_STOP_DATE', fnd_date.date_to_displaydt(l_getstops_pl_arr_date(j+1)));
7996: FND_MESSAGE.SET_TOKEN('PHYSICAL_LOCATION_DESP',
7997: WSH_UTIL_CORE.Get_Location_Description (l_getstops_stop_loc_id(j+1),'NEW UI CODE INFO'));
7998: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
7993: FND_MESSAGE.SET_TOKEN('DUMMY_LOCATION_DESP',
7994: WSH_UTIL_CORE.Get_Location_Description (l_getstops_stop_loc_id(j),'NEW UI CODE INFO'));
7995: FND_MESSAGE.SET_TOKEN('PHYSICAL_STOP_DATE', fnd_date.date_to_displaydt(l_getstops_pl_arr_date(j+1)));
7996: FND_MESSAGE.SET_TOKEN('PHYSICAL_LOCATION_DESP',
7997: WSH_UTIL_CORE.Get_Location_Description (l_getstops_stop_loc_id(j+1),'NEW UI CODE INFO'));
7998: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
7999:
8000: -- Handle SSN Exception
8001: WHEN invalid_stop_seq_num THEN
7994: WSH_UTIL_CORE.Get_Location_Description (l_getstops_stop_loc_id(j),'NEW UI CODE INFO'));
7995: FND_MESSAGE.SET_TOKEN('PHYSICAL_STOP_DATE', fnd_date.date_to_displaydt(l_getstops_pl_arr_date(j+1)));
7996: FND_MESSAGE.SET_TOKEN('PHYSICAL_LOCATION_DESP',
7997: WSH_UTIL_CORE.Get_Location_Description (l_getstops_stop_loc_id(j+1),'NEW UI CODE INFO'));
7998: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
7999:
8000: -- Handle SSN Exception
8001: WHEN invalid_stop_seq_num THEN
8002: rollback to start_of_the_trip;
8002: rollback to start_of_the_trip;
8003: FND_MESSAGE.SET_NAME('WSH', 'WSH_BETWEEN_LINKED_STOPS_SSN');
8004: FND_MESSAGE.SET_TOKEN('DUMMY_STOP_SEQ_NUM', l_getstops_stop_seq_num(j));
8005: FND_MESSAGE.SET_TOKEN('DUMMY_LOCATION_DESP',
8006: WSH_UTIL_CORE.Get_Location_Description (l_getstops_stop_loc_id(j),'NEW UI CODE INFO'));
8007: FND_MESSAGE.SET_TOKEN('PHYSICAL_STOP_SEQ_NUM', l_getstops_stop_seq_num(j+1));
8008: FND_MESSAGE.SET_TOKEN('PHYSICAL_LOCATION_DESP',
8009: WSH_UTIL_CORE.Get_Location_Description (l_getstops_stop_loc_id(j+1),'NEW UI CODE INFO'));
8010: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
8005: FND_MESSAGE.SET_TOKEN('DUMMY_LOCATION_DESP',
8006: WSH_UTIL_CORE.Get_Location_Description (l_getstops_stop_loc_id(j),'NEW UI CODE INFO'));
8007: FND_MESSAGE.SET_TOKEN('PHYSICAL_STOP_SEQ_NUM', l_getstops_stop_seq_num(j+1));
8008: FND_MESSAGE.SET_TOKEN('PHYSICAL_LOCATION_DESP',
8009: WSH_UTIL_CORE.Get_Location_Description (l_getstops_stop_loc_id(j+1),'NEW UI CODE INFO'));
8010: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
8011:
8012: WHEN invalid_ssn_adjustment THEN
8013: rollback to start_of_the_trip;
8006: WSH_UTIL_CORE.Get_Location_Description (l_getstops_stop_loc_id(j),'NEW UI CODE INFO'));
8007: FND_MESSAGE.SET_TOKEN('PHYSICAL_STOP_SEQ_NUM', l_getstops_stop_seq_num(j+1));
8008: FND_MESSAGE.SET_TOKEN('PHYSICAL_LOCATION_DESP',
8009: WSH_UTIL_CORE.Get_Location_Description (l_getstops_stop_loc_id(j+1),'NEW UI CODE INFO'));
8010: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
8011:
8012: WHEN invalid_ssn_adjustment THEN
8013: rollback to start_of_the_trip;
8014: FND_MESSAGE.SET_NAME('WSH', 'WSH_INVALID_SSN_ADJUSTMENT');
8011:
8012: WHEN invalid_ssn_adjustment THEN
8013: rollback to start_of_the_trip;
8014: FND_MESSAGE.SET_NAME('WSH', 'WSH_INVALID_SSN_ADJUSTMENT');
8015: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
8016:
8017: -- Handle non unique SSN exception (bug 4245339)
8018: WHEN duplicate_stop_seq THEN
8019: rollback to start_of_the_trip;
8017: -- Handle non unique SSN exception (bug 4245339)
8018: WHEN duplicate_stop_seq THEN
8019: rollback to start_of_the_trip;
8020: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_DUP_SEQUENCE');
8021: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
8022:
8023: WHEN reset_stop_seq_number_error THEN
8024: rollback to start_of_the_trip;
8025:
8045: i := p_trip_ids.next(i);
8046: END LOOP;
8047:
8048: IF x_success_trip_ids.count = 0 THEN
8049: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8050: ELSIF x_success_trip_ids.count < p_trip_ids.count
8051: OR l_warn_num > 0 THEN
8052: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8053: END IF;
8048: IF x_success_trip_ids.count = 0 THEN
8049: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8050: ELSIF x_success_trip_ids.count < p_trip_ids.count
8051: OR l_warn_num > 0 THEN
8052: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8053: END IF;
8054:
8055: IF l_debug_on THEN
8056: WSH_DEBUG_SV.log(l_module_name,'x_success_trip_ids.count', x_success_trip_ids.count);
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);
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);
8076: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
8081:
8082:
8083: PROCEDURE PROCESS_CARRIER_SELECTION (
8084: p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
8085: p_trip_id_tab IN wsh_util_core.id_tab_type,
8086: p_caller IN VARCHAR2 DEFAULT NULL, -- WSH_FSTRX / WSH_PUB / WSH_GROUP/ FTE
8087: x_msg_count OUT NOCOPY NUMBER,
8088: x_msg_data OUT NOCOPY VARCHAR2,
8089: x_return_status OUT NOCOPY VARCHAR2)
8196: l_prev_trip_id NUMBER := 0;
8197: l_trip_result_type VARCHAR2(30);
8198: l_rank_result_cnt NUMBER := 0;
8199:
8200: l_organization_tab WSH_UTIL_CORE.id_tab_type;
8201: l_customer_tab WSH_UTIL_CORE.id_tab_type;
8202: l_organization_id NUMBER := 0;
8203: l_customer_id NUMBER := 0;
8204: l_delivery_id NUMBER := 0;
8197: l_trip_result_type VARCHAR2(30);
8198: l_rank_result_cnt NUMBER := 0;
8199:
8200: l_organization_tab WSH_UTIL_CORE.id_tab_type;
8201: l_customer_tab WSH_UTIL_CORE.id_tab_type;
8202: l_organization_id NUMBER := 0;
8203: l_customer_id NUMBER := 0;
8204: l_delivery_id NUMBER := 0;
8205: l_customer_site_id NUMBER := NULL;
8204: l_delivery_id NUMBER := 0;
8205: l_customer_site_id NUMBER := NULL;
8206: l_rg_trip BOOLEAN := TRUE;
8207: --l_rg_trip_cnt NUMBER := 0;
8208: l_stop_organization_id_tab WSH_UTIL_CORE.id_tab_type;
8209: l_stop_customer_id_tab WSH_UTIL_CORE.id_tab_type;
8210: l_stop_delivery_id_tab WSH_UTIL_CORE.id_tab_type;
8211: l_base_weight_uom VARCHAR2(3);
8212: l_base_volume_uom VARCHAR2(3);
8205: l_customer_site_id NUMBER := NULL;
8206: l_rg_trip BOOLEAN := TRUE;
8207: --l_rg_trip_cnt NUMBER := 0;
8208: l_stop_organization_id_tab WSH_UTIL_CORE.id_tab_type;
8209: l_stop_customer_id_tab WSH_UTIL_CORE.id_tab_type;
8210: l_stop_delivery_id_tab WSH_UTIL_CORE.id_tab_type;
8211: l_base_weight_uom VARCHAR2(3);
8212: l_base_volume_uom VARCHAR2(3);
8213: l_pickup_weight_convert NUMBER := 0;
8206: l_rg_trip BOOLEAN := TRUE;
8207: --l_rg_trip_cnt NUMBER := 0;
8208: l_stop_organization_id_tab WSH_UTIL_CORE.id_tab_type;
8209: l_stop_customer_id_tab WSH_UTIL_CORE.id_tab_type;
8210: l_stop_delivery_id_tab WSH_UTIL_CORE.id_tab_type;
8211: l_base_weight_uom VARCHAR2(3);
8212: l_base_volume_uom VARCHAR2(3);
8213: l_pickup_weight_convert NUMBER := 0;
8214: l_pickup_volume_convert NUMBER := 0;
8266: WSH_DEBUG_SV.logmsg(l_module_name,'p_init_msg_list : '||p_init_msg_list);
8267: WSH_DEBUG_SV.logmsg(l_module_name,'P_CALLER : '||P_CALLER);
8268: END IF;
8269: --
8270: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
8271:
8272: SAVEPOINT before_trip_update;
8273:
8274: i := p_trip_id_tab.FIRST;
8323: IF l_cs_tripstops_tab.COUNT = 0 THEN
8324: FND_MESSAGE.SET_NAME('WSH','WSH_FTE_SEL_NO_STOPS');
8325: --FND_MESSAGE.SET_TOKEN('TRIPID',p_trip_id_tab(i));
8326: --FND_MSG_PUB.ADD;
8327: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8328: l_rg_trip := FALSE;
8329: WSH_UTIL_CORE.add_message(x_return_status);
8330: END IF;
8331:
8325: --FND_MESSAGE.SET_TOKEN('TRIPID',p_trip_id_tab(i));
8326: --FND_MSG_PUB.ADD;
8327: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8328: l_rg_trip := FALSE;
8329: WSH_UTIL_CORE.add_message(x_return_status);
8330: END IF;
8331:
8332: j:=l_cs_tripstops_tab.FIRST;
8333: IF j IS NOT NULL THEN
8383: -- organization_id is that of last stop / any delivery being dropped off at the last stop of the trip
8384:
8385: IF j IN (l_cs_tripstops_tab.FIRST,l_cs_tripstops_tab.LAST) THEN
8386:
8387: WSH_UTIL_CORE.get_org_from_location(
8388: p_location_id => nvl(l_cs_tripstops_tab(j).physical_location_id,l_cs_tripstops_tab(j).stop_location_id),
8389: x_organization_tab => l_organization_tab,
8390: x_return_status => l_return_status);
8391:
8445: l_tripdest_org_id := l_organization_id;
8446: l_ultimate_dropoff_loc_id := nvl(l_cs_tripstops_tab(j).physical_location_id,l_cs_tripstops_tab(j).stop_location_id);
8447: l_ultimate_dropoff_date := l_cs_tripstops_tab(j).planned_arrival_date;
8448:
8449: WSH_UTIL_CORE.get_customer_from_loc(
8450: p_location_id => nvl(l_cs_tripstops_tab(j).physical_location_id,l_cs_tripstops_tab(j).stop_location_id),
8451: x_customer_id_tab => l_customer_tab,
8452: x_return_status => l_return_status);
8453:
8496: IF l_customer_id IS NOT NULL THEN
8497: FND_MESSAGE.SET_NAME('WSH','WSH_LOCATION_NO_SITE');
8498: --FND_MESSAGE.Set_Token('LOCATION',l_ultimate_dropoff_loc_id);
8499: FND_MESSAGE.Set_Token('TRIPNAME',l_cs_trip_rec.trip_name);
8500: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8501: l_rg_trip := FALSE;
8502: ELSE
8503: l_customer_site_id := NULL;
8504: END IF;
8583: x_return_status => l_return_status);
8584:
8585: -- Handle results (Error out if multileg)
8586:
8587: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
8588:
8589: IF l_debug_on THEN
8590: WSH_DEBUG_SV.logmsg(l_module_name,'l_cs_result_tab.COUNT is '|| l_cs_result_tab.COUNT);
8591: END IF;
8616: IF l_trip_id <> l_prev_trip_id THEN
8617: FND_MESSAGE.SET_NAME('WSH','WSH_FTE_CS_MULTILEG_TRIP');
8618: FND_MESSAGE.SET_TOKEN('TRIPID',l_trip_id);
8619: --FND_MSG_PUB.ADD;
8620: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8621: WSH_UTIL_CORE.add_message(x_return_status);
8622: END IF;
8623: ELSIF l_trip_result_type = 'RANK' THEN
8624: l_rank_result_cnt := l_rank_result_cnt + 1;
8617: FND_MESSAGE.SET_NAME('WSH','WSH_FTE_CS_MULTILEG_TRIP');
8618: FND_MESSAGE.SET_TOKEN('TRIPID',l_trip_id);
8619: --FND_MSG_PUB.ADD;
8620: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8621: WSH_UTIL_CORE.add_message(x_return_status);
8622: END IF;
8623: ELSIF l_trip_result_type = 'RANK' THEN
8624: l_rank_result_cnt := l_rank_result_cnt + 1;
8625: IF l_trip_id <> l_prev_trip_id THEN
8633: --FND_MSG_PUB.ADD;
8634: IF l_debug_on THEN
8635: WSH_DEBUG_SV.logmsg(l_module_name,'Trip already has a ranked list ');
8636: END IF;
8637: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8638: WSH_UTIL_CORE.add_message(x_return_status);
8639: END IF;
8640:
8641: --IF (l_cs_result_tab(rec_cnt).rank = 1) THEN
8634: IF l_debug_on THEN
8635: WSH_DEBUG_SV.logmsg(l_module_name,'Trip already has a ranked list ');
8636: END IF;
8637: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8638: WSH_UTIL_CORE.add_message(x_return_status);
8639: END IF;
8640:
8641: --IF (l_cs_result_tab(rec_cnt).rank = 1) THEN
8642: -- Has to update trip
8654: p_trip_id => l_trip_id,
8655: x_trip_info => l_trip_info_tab(1),
8656: x_return_status => l_return_status);
8657:
8658: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
8659: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
8660: raise FND_API.G_EXC_UNEXPECTED_ERROR;
8661: END IF;
8662:
8655: x_trip_info => l_trip_info_tab(1),
8656: x_return_status => l_return_status);
8657:
8658: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
8659: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
8660: raise FND_API.G_EXC_UNEXPECTED_ERROR;
8661: END IF;
8662:
8663: --l_trip_info_tab(1).trip_id := l_trip_id;
8684: p_trip_info_tab => l_trip_info_tab,
8685: p_in_rec => l_trip_in_rec,
8686: x_out_tab => l_trip_out_rec_tab);
8687:
8688: --IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
8689: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
8690: raise FND_API.G_EXC_UNEXPECTED_ERROR;
8691: END IF;
8692:
8685: p_in_rec => l_trip_in_rec,
8686: x_out_tab => l_trip_out_rec_tab);
8687:
8688: --IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
8689: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
8690: raise FND_API.G_EXC_UNEXPECTED_ERROR;
8691: END IF;
8692:
8693: --END IF;
8744: p_trip_id => l_trip_id,
8745: p_rank_id => l_rank_id
8746: --x_ranklist => l_ranked_list
8747: );
8748: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
8749: raise FND_API.G_EXC_UNEXPECTED_ERROR;
8750: END IF;
8751: l_ranked_list.DELETE;
8752: list_cnt := 0;
8764: --
8765: -- All results found are multileg, return an error
8766: --
8767: FND_MESSAGE.SET_NAME('WSH','WSH_FTE_CS_NO_RANKED_RESULT');
8768: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8769: WSH_UTIL_CORE.add_message(x_return_status);
8770: --
8771: -- Debug Statements
8772: --
8765: -- All results found are multileg, return an error
8766: --
8767: FND_MESSAGE.SET_NAME('WSH','WSH_FTE_CS_NO_RANKED_RESULT');
8768: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8769: WSH_UTIL_CORE.add_message(x_return_status);
8770: --
8771: -- Debug Statements
8772: --
8773: IF l_debug_on THEN
8780: --
8781: -- No results at all where found, return a warning
8782: --
8783: FND_MESSAGE.SET_NAME('WSH','WSH_FTE_CS_NO_CARRIER_SELECTED');
8784: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8785: WSH_UTIL_CORE.add_message(x_return_status);
8786: --
8787: -- Debug Statements
8788: --
8781: -- No results at all where found, return a warning
8782: --
8783: FND_MESSAGE.SET_NAME('WSH','WSH_FTE_CS_NO_CARRIER_SELECTED');
8784: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8785: WSH_UTIL_CORE.add_message(x_return_status);
8786: --
8787: -- Debug Statements
8788: --
8789: IF l_debug_on THEN
8796: END IF;
8797: -- TODO
8798: ELSE -- No eligible trips were found
8799: FND_MESSAGE.SET_NAME('WSH','WSH_FTE_CS_NO_VALID_TRIPS');
8800: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8801: WSH_UTIL_CORE.add_message(x_return_status);
8802: --
8803: -- Debug Statements
8804: --
8797: -- TODO
8798: ELSE -- No eligible trips were found
8799: FND_MESSAGE.SET_NAME('WSH','WSH_FTE_CS_NO_VALID_TRIPS');
8800: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8801: WSH_UTIL_CORE.add_message(x_return_status);
8802: --
8803: -- Debug Statements
8804: --
8805: IF l_debug_on THEN
8826: EXCEPTION
8827: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8828:
8829: ROLLBACK TO before_trip_update;
8830: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
8831: --
8832: IF l_debug_on THEN
8833: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
8834: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
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);
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);
8867: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
8885:
8886:
8887:
8888: PROCEDURE Remove_Consolidation(
8889: p_trip_id_tab IN wsh_util_core.id_tab_type,
8890: p_unassign_all IN VARCHAR2,
8891: p_caller IN VARCHAR2,
8892: x_return_status OUT NOCOPY VARCHAR2) IS
8893:
8898: and l.pick_up_stop_id = s.stop_id
8899: and s.trip_id = p_trip_id
8900: order by d.delivery_type;
8901:
8902: l_delivery_tab wsh_util_core.id_tab_type;
8903: l_delivery_type_tab wsh_util_core.column_tab_type;
8904: l_consol_delivery_tab wsh_util_core.id_tab_type;
8905: l_content_delivery_tab wsh_util_core.id_tab_type;
8906: l_dummy_tab wsh_util_core.id_tab_type;
8899: and s.trip_id = p_trip_id
8900: order by d.delivery_type;
8901:
8902: l_delivery_tab wsh_util_core.id_tab_type;
8903: l_delivery_type_tab wsh_util_core.column_tab_type;
8904: l_consol_delivery_tab wsh_util_core.id_tab_type;
8905: l_content_delivery_tab wsh_util_core.id_tab_type;
8906: l_dummy_tab wsh_util_core.id_tab_type;
8907: j NUMBER := 0;
8900: order by d.delivery_type;
8901:
8902: l_delivery_tab wsh_util_core.id_tab_type;
8903: l_delivery_type_tab wsh_util_core.column_tab_type;
8904: l_consol_delivery_tab wsh_util_core.id_tab_type;
8905: l_content_delivery_tab wsh_util_core.id_tab_type;
8906: l_dummy_tab wsh_util_core.id_tab_type;
8907: j NUMBER := 0;
8908: k NUMBER := 0;
8901:
8902: l_delivery_tab wsh_util_core.id_tab_type;
8903: l_delivery_type_tab wsh_util_core.column_tab_type;
8904: l_consol_delivery_tab wsh_util_core.id_tab_type;
8905: l_content_delivery_tab wsh_util_core.id_tab_type;
8906: l_dummy_tab wsh_util_core.id_tab_type;
8907: j NUMBER := 0;
8908: k NUMBER := 0;
8909:
8902: l_delivery_tab wsh_util_core.id_tab_type;
8903: l_delivery_type_tab wsh_util_core.column_tab_type;
8904: l_consol_delivery_tab wsh_util_core.id_tab_type;
8905: l_content_delivery_tab wsh_util_core.id_tab_type;
8906: l_dummy_tab wsh_util_core.id_tab_type;
8907: j NUMBER := 0;
8908: k NUMBER := 0;
8909:
8910: l_num_warnings NUMBER := 0;
8971: p_caller => p_caller,
8972: p_del_tab => l_dummy_tab,
8973: x_return_status => l_return_status);
8974:
8975: wsh_util_core.api_post_call
8976: (
8977: p_return_status => l_return_status,
8978: x_num_warnings => l_num_warnings,
8979: x_num_errors => l_num_errors
8995: p_del_rows => l_delivery_tab,
8996: p_trip_id => p_trip_id_tab(i),
8997: x_return_status => l_return_status);
8998:
8999: wsh_util_core.api_post_call
9000: (
9001: p_return_status => l_return_status,
9002: x_num_warnings => l_num_warnings,
9003: x_num_errors => l_num_errors
9007: END LOOP;
9008:
9009: IF l_num_errors > 0
9010: THEN
9011: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9012: ELSIF l_num_warnings > 0
9013: THEN
9014: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
9015: ELSE
9010: THEN
9011: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9012: ELSIF l_num_warnings > 0
9013: THEN
9014: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
9015: ELSE
9016: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
9017: END IF;
9018: --
9012: ELSIF l_num_warnings > 0
9013: THEN
9014: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
9015: ELSE
9016: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
9017: END IF;
9018: --
9019: -- Debug Statements
9020: --
9022: WSH_DEBUG_SV.pop(l_module_name);
9023: END IF;
9024: EXCEPTION
9025: WHEN WSH_INVALID_ACTION THEN
9026: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9027: --
9028: -- Debug Statements
9029: FND_MESSAGE.SET_NAME('WSH','WSH_REMOVE_CONSOL_ERR');
9030: wsh_util_core.add_message(x_return_status);
9026: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9027: --
9028: -- Debug Statements
9029: FND_MESSAGE.SET_NAME('WSH','WSH_REMOVE_CONSOL_ERR');
9030: wsh_util_core.add_message(x_return_status);
9031: IF l_debug_on THEN
9032: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_ACTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
9033: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_ACTION');
9034: END IF;
9033: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_ACTION');
9034: END IF;
9035:
9036: WHEN FND_API.G_EXC_ERROR THEN
9037: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9038: --
9039: -- Debug Statements
9040: --
9041: IF l_debug_on THEN
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;