[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
158: WSH_DEBUG_SV.log(l_module_name,'p_trip_id', p_trip_id);
159: WSH_DEBUG_SV.log(l_module_name,'p_trip_name', p_trip_name);
160: END IF;
161:
162: x_return_status := wsh_util_core.g_ret_sts_success;
163:
164: open get_pickup_stop (p_trip_id);
165: fetch get_pickup_stop into l_stop_id, l_stop_location_id;
166: if get_pickup_stop%notfound then
166: if get_pickup_stop%notfound then
167: close get_pickup_stop;
168: fnd_message.set_name('WSH','WSH_TRIP_NO_STOPS');
169: fnd_message.set_token('TRIP_NAME',p_trip_name);
170: x_return_status := wsh_util_core.g_ret_sts_error;
171: raise e_trip_confirm_exception;
172: else
173: fetch get_pickup_stop into l_stop_id, l_stop_location_id;
174: if get_pickup_stop%found then
177: l_stop_location_code := fnd_message.get;
178: x_def_rec.trip_multiple_pickup := 'Y';
179: else
180: close get_pickup_stop;
181: l_stop_location_code := wsh_util_core.get_location_description(l_stop_location_id, 'NEW UI CODE');
182: l_stop_location_code := substrb(l_stop_location_code, 1, 60);
183: open get_pickup_delivery(l_stop_id, l_stop_location_id);
184: fetch get_pickup_delivery into l_exists;
185: if get_pickup_delivery%notfound then
186: close get_pickup_delivery;
187: fnd_message.set_name('WSH','WSH_TRIP_CONFIRM_MISSING_DEL');
188: fnd_message.set_token('TRIP',p_trip_name);
189: fnd_message.set_token('STOP_NAME',l_stop_location_code);
190: x_return_status := wsh_util_core.g_ret_sts_error;
191: raise e_trip_confirm_exception;
192: else
193: close get_pickup_delivery;
194: x_def_rec.trip_multiple_pickup := 'N';
214:
215: if l_org_id is not null then
216: -- LSP PROJECT : just added parameter names in the call.
217: wsh_shipping_params_pvt.get(p_organization_id => l_org_id, x_param_info => l_param_info, x_return_status => l_return_status);
218: wsh_util_core.api_post_call(p_return_status =>l_return_status,
219: x_num_warnings =>l_num_warning,
220: x_num_errors =>l_num_errors);
221:
222: -- Bug 3346237:Value for parameter Defer_interface to be taken from Global Parameters table.
223: wsh_shipping_params_pvt.Get_Global_Parameters(
224: x_param_info => l_global_info,
225: x_return_status => l_return_status);
226:
227: wsh_util_core.api_post_call(p_return_status =>l_return_status,
228: x_num_warnings =>l_num_warning,
229: x_num_errors =>l_num_errors);
230:
231: x_def_rec.defer_interface_flag := l_global_info.defer_interface ;
248:
249: EXCEPTION
250:
251: WHEN e_trip_confirm_exception THEN
252: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
253: wsh_util_core.add_message(x_return_status,l_module_name);
254: IF l_debug_on THEN
255: wsh_debug_sv.log (l_module_name,'G_EXC_ERROR');
256: WSH_DEBUG_SV.pop(l_module_name);
249: EXCEPTION
250:
251: WHEN e_trip_confirm_exception THEN
252: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
253: wsh_util_core.add_message(x_return_status,l_module_name);
254: IF l_debug_on THEN
255: wsh_debug_sv.log (l_module_name,'G_EXC_ERROR');
256: WSH_DEBUG_SV.pop(l_module_name);
257: END IF;
257: END IF;
258:
259: WHEN OTHERS THEN
260:
261: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.Get_Trip_Defaults');
262:
263: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
264: IF l_debug_on THEN
265: wsh_debug_sv.log (l_module_name,'Others',substr(sqlerrm,1,200));
259: WHEN OTHERS THEN
260:
261: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.Get_Trip_Defaults');
262:
263: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
264: IF l_debug_on THEN
265: wsh_debug_sv.log (l_module_name,'Others',substr(sqlerrm,1,200));
266: WSH_DEBUG_SV.pop(l_module_name);
267: END IF;
385:
386: END IF;
387: --
388:
389: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
390:
391: IF p_trip_id IS NULL THEN
392: RAISE others ;
393: END IF;
396: WSH_MBOLS_PVT.Generate_MBOL(
397: p_trip_id => p_trip_id,
398: x_sequence_number => l_document_number,
399: x_return_status => l_return_status );
400: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
401: x_return_status := l_return_status;
402: wsh_util_core.add_message(x_return_status);
403: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
404: raise TRIP_CONFIRM_ERROR;
398: x_sequence_number => l_document_number,
399: x_return_status => l_return_status );
400: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
401: x_return_status := l_return_status;
402: wsh_util_core.add_message(x_return_status);
403: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
404: raise TRIP_CONFIRM_ERROR;
405: END IF;
406: END IF;
399: x_return_status => l_return_status );
400: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
401: x_return_status := l_return_status;
402: wsh_util_core.add_message(x_return_status);
403: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
404: raise TRIP_CONFIRM_ERROR;
405: END IF;
406: END IF;
407: END IF;
422: p_logging_entity_name => 'TRIP',
423: p_consider_content => 'Y',
424: x_exceptions_tab => l_exceptions_tab
425: );
426: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
427: x_return_status := l_return_status;
428: wsh_util_core.add_message(x_return_status);
429: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
430: raise others;
424: x_exceptions_tab => l_exceptions_tab
425: );
426: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
427: x_return_status := l_return_status;
428: wsh_util_core.add_message(x_return_status);
429: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
430: raise others;
431: END IF;
432: END IF;
425: );
426: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
427: x_return_status := l_return_status;
428: wsh_util_core.add_message(x_return_status);
429: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
430: raise others;
431: END IF;
432: END IF;
433: FOR exp_cnt in 1..l_exceptions_tab.COUNT LOOP
439: END IF;
440: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
441: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
442: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Error');
443: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
444: wsh_util_core.add_message(x_return_status);
445: raise check_exceptions;
446: ELSIF l_exceptions_tab(exp_cnt).exception_behavior = 'WARNING' THEN
447: IF l_exceptions_tab(exp_cnt).entity_name = 'TRIP' THEN
440: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
441: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
442: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Error');
443: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
444: wsh_util_core.add_message(x_return_status);
445: raise check_exceptions;
446: ELSIF l_exceptions_tab(exp_cnt).exception_behavior = 'WARNING' THEN
447: IF l_exceptions_tab(exp_cnt).entity_name = 'TRIP' THEN
448: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_ENTITY');
448: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_ENTITY');
449: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
450: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
451: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
452: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
453: wsh_util_core.add_message(x_return_status);
454: ELSIF NOT (l_exp_logged) THEN
455: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
456: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
449: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
450: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
451: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
452: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
453: wsh_util_core.add_message(x_return_status);
454: ELSIF NOT (l_exp_logged) THEN
455: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
456: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
457: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
455: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
456: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
457: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
458: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
459: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
460: l_exp_logged := TRUE;
461: wsh_util_core.add_message(x_return_status);
462: END IF;
463: END IF;
457: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
458: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
459: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
460: l_exp_logged := TRUE;
461: wsh_util_core.add_message(x_return_status);
462: END IF;
463: END IF;
464: END LOOP;
465: END IF; -- end of check if trip is being set to In-Transit or Closed
530: p_defer_interface_flag => p_defer_interface_flag,
531: x_return_status => l_return_status
532: );
533:
534: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
535: -- For first stop, always error out ; for others, treat as warning
536: IF first_stop = 'Y' THEN
537: x_return_status := l_return_status ;
538: rollback to sp_confirm_trip;
538: rollback to sp_confirm_trip;
539: EXIT ;
540: ELSE
541: -- since it's not the first stop, it can be treated as warning
542: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
543: END IF;
544:
545: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
546: -- For Unexpected errors, always error out
541: -- since it's not the first stop, it can be treated as warning
542: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
543: END IF;
544:
545: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
546: -- For Unexpected errors, always error out
547: x_return_status := l_return_status ;
548: rollback to sp_confirm_trip;
549: EXIT ;
547: x_return_status := l_return_status ;
548: rollback to sp_confirm_trip;
549: EXIT ;
550:
551: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
552: x_return_status := l_return_status ;
553:
554: END IF;
555:
584: p_logging_entity_name => 'TRIP',
585: p_consider_content => 'Y'
586: ) ;
587:
588: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
589: x_return_status := l_return_status;
590: wsh_util_core.add_message(x_return_status);
591: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
592: raise TRIP_CONFIRM_ERROR;
586: ) ;
587:
588: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
589: x_return_status := l_return_status;
590: wsh_util_core.add_message(x_return_status);
591: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
592: raise TRIP_CONFIRM_ERROR;
593: END IF;
594: END IF;
587:
588: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
589: x_return_status := l_return_status;
590: wsh_util_core.add_message(x_return_status);
591: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
592: raise TRIP_CONFIRM_ERROR;
593: END IF;
594: END IF;
595: END IF;
599: NULL;
600:
601:
602: -- Bug 3374306
603: IF x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
604: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_CONFIRM_WARNING');
605: wsh_util_core.add_message(x_return_status);
606: END IF;
607:
601:
602: -- Bug 3374306
603: IF x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
604: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_CONFIRM_WARNING');
605: wsh_util_core.add_message(x_return_status);
606: END IF;
607:
608: --Bug# 2867209 - Start
609: IF l_debug_on THEN
609: IF l_debug_on THEN
610: WSH_DEBUG_SV.log(l_module_name,'l_is_action_not_performed',l_is_action_not_performed);
611: END IF;
612:
613: IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS AND P_CLOSE_FLAG = 'N' AND l_is_action_not_performed THEN
614: FND_MESSAGE.SET_NAME('WSH','WSH_NO_CHANGE_ACTION');
615: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING);
616: END IF;
617: --Bug# 2867209 - End
611: END IF;
612:
613: IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS AND P_CLOSE_FLAG = 'N' AND l_is_action_not_performed THEN
614: FND_MESSAGE.SET_NAME('WSH','WSH_NO_CHANGE_ACTION');
615: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING);
616: END IF;
617: --Bug# 2867209 - End
618: --
619:
649: WSH_DEBUG_SV.logmsg(l_module_name,'trip_confirm_error exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
650: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:trip_confirm_error');
651: END IF;
652: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_CONFIRM_ERROR');
653: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
654: wsh_util_core.add_message(x_return_status);
655: --
656: WHEN check_exceptions THEN
657: --
650: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:trip_confirm_error');
651: END IF;
652: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_CONFIRM_ERROR');
653: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
654: wsh_util_core.add_message(x_return_status);
655: --
656: WHEN check_exceptions THEN
657: --
658: IF l_debug_on THEN
659: WSH_DEBUG_SV.logmsg(l_module_name,'Check_Exceptions exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
660: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:Check_Exceptions');
661: END IF;
662: --
663: WSH_UTIL_CORE.get_messages('N', l_msg_summary, l_msg_details, x_msg_count);
664: --
665: IF l_debug_on THEN
666: WSH_DEBUG_SV.logmsg(l_module_name, L_MSG_SUMMARY );
667: END IF;
674:
675:
676: WHEN others THEN
677: rollback to sp_confirm_trip;
678: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
679: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.CONFIRM_TRIP');
680: --
681: IF l_debug_on THEN
682: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
675:
676: WHEN others THEN
677: rollback to sp_confirm_trip;
678: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
679: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.CONFIRM_TRIP');
680: --
681: IF l_debug_on THEN
682: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
683: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
682: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
683: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
684: END IF;
685: --
686: WSH_UTIL_CORE.get_messages('N', l_msg_summary, l_msg_details, x_msg_count);
687: --
688: IF l_debug_on THEN
689: WSH_DEBUG_SV.logmsg(l_module_name, L_MSG_SUMMARY );
690: END IF;
699:
700:
701:
702: PROCEDURE Plan(
703: p_trip_rows IN wsh_util_core.id_tab_type,
704: p_action IN VARCHAR2,
705: x_return_status OUT NOCOPY VARCHAR2) IS
706:
707: l_num_error NUMBER := 0;
705: x_return_status OUT NOCOPY VARCHAR2) IS
706:
707: l_num_error NUMBER := 0;
708: l_num_warn NUMBER := 0;
709: l_trip_rows wsh_util_core.id_tab_type;
710: l_trip_status VARCHAR2(2);
711: l_stop_status VARCHAR2(2);
712:
713: cursor get_status(c_trip_id IN NUMBER) is
757: --
758: WSH_DEBUG_SV.log(l_module_name,'P_ACTION',P_ACTION);
759: END IF;
760: --
761: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
762:
763:
764: IF (p_trip_rows.count = 0) THEN
765: raise others;
788: --
789: wsh_trip_validations.check_unplan(p_trip_rows(i), x_return_status);
790: END IF;
791:
792: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
793: goto plan_error;
794: ELSIF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
795: l_num_warn := l_num_warn + 1;
796: END IF;
790: END IF;
791:
792: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
793: goto plan_error;
794: ELSIF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
795: l_num_warn := l_num_warn + 1;
796: END IF;
797:
798: /* H integration ,this call is made from Group and Public API
804: IF p_action='FIRM' THEN
805: wsh_tp_release.firm_entity( p_entity => 'TRIP',
806: p_entity_id =>p_trip_rows(i),
807: x_return_status =>x_return_status);
808: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
809: IF x_return_status=WSH_UTIL_CORE.G_RET_STS_WARNING THEN
810: l_num_warn := l_num_warn + 1;
811: ELSE
812: goto plan_error;
805: wsh_tp_release.firm_entity( p_entity => 'TRIP',
806: p_entity_id =>p_trip_rows(i),
807: x_return_status =>x_return_status);
808: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
809: IF x_return_status=WSH_UTIL_CORE.G_RET_STS_WARNING THEN
810: l_num_warn := l_num_warn + 1;
811: ELSE
812: goto plan_error;
813: END IF;
817: wsh_tp_release.unfirm_entity( p_entity => 'TRIP',
818: p_entity_id =>p_trip_rows(i),
819: p_action =>p_action,
820: x_return_status =>x_return_status);
821: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
822: IF x_return_status=WSH_UTIL_CORE.G_RET_STS_WARNING THEN
823: l_num_warn := l_num_warn + 1;
824: ELSE
825: goto plan_error;
818: p_entity_id =>p_trip_rows(i),
819: p_action =>p_action,
820: x_return_status =>x_return_status);
821: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
822: IF x_return_status=WSH_UTIL_CORE.G_RET_STS_WARNING THEN
823: l_num_warn := l_num_warn + 1;
824: ELSE
825: goto plan_error;
826: END IF;
846: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
847: END IF;
848: --
849: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
850: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
851: wsh_util_core.add_message(x_return_status);
852: l_num_error := l_num_error + 1;
853:
854: <
847: END IF;
848: --
849: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
850: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
851: wsh_util_core.add_message(x_return_status);
852: l_num_error := l_num_error + 1;
853:
854: <
855: null;
873: FND_MESSAGE.SET_TOKEN('NUM_ERROR',l_num_error);
874: FND_MESSAGE.SET_TOKEN('NUM_SUCCESS',p_trip_rows.count - l_num_error - l_num_warn);
875:
876: IF (p_trip_rows.count = l_num_error) THEN
877: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
878: ELSE
879: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
880: END IF;
881:
875:
876: IF (p_trip_rows.count = l_num_error) THEN
877: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
878: ELSE
879: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
880: END IF;
881:
882: wsh_util_core.add_message(x_return_status);
883:
878: ELSE
879: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
880: END IF;
881:
882: wsh_util_core.add_message(x_return_status);
883:
884: ELSE
885: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
886: END IF;
881:
882: wsh_util_core.add_message(x_return_status);
883:
884: ELSE
885: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
886: END IF;
887:
888: END IF;
889:
895: END IF;
896: --
897: EXCEPTION
898: WHEN others THEN
899: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.PLAN');
900: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
901:
902: --
903: -- Debug Statements
896: --
897: EXCEPTION
898: WHEN others THEN
899: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.PLAN');
900: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
901:
902: --
903: -- Debug Statements
904: --
960: -- J-IB-NPARIKH-{
961: --
962: -- stubbed out as no longer being called.
963: --
964: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
965: RETURN;
966: -- J-IB-NPARIKH-}
967: **/
968:
982: p_logging_entity_name => 'TRIP',
983: p_consider_content => 'Y',
984: x_exceptions_tab => l_exceptions_tab
985: );
986: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
987: x_return_status := l_return_status;
988: wsh_util_core.add_message(x_return_status);
989: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
990: raise FND_API.G_EXC_ERROR;
984: x_exceptions_tab => l_exceptions_tab
985: );
986: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
987: x_return_status := l_return_status;
988: wsh_util_core.add_message(x_return_status);
989: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
990: raise FND_API.G_EXC_ERROR;
991: END IF;
992: END IF;
985: );
986: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
987: x_return_status := l_return_status;
988: wsh_util_core.add_message(x_return_status);
989: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
990: raise FND_API.G_EXC_ERROR;
991: END IF;
992: END IF;
993: FOR exp_cnt in 1..l_exceptions_tab.COUNT LOOP
999: END IF;
1000: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
1001: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
1002: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Error');
1003: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1004: wsh_util_core.add_message(x_return_status);
1005: raise check_exceptions;
1006: ELSIF l_exceptions_tab(exp_cnt).exception_behavior = 'WARNING' THEN
1007: IF l_exceptions_tab(exp_cnt).entity_name = 'TRIP' THEN
1000: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
1001: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
1002: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Error');
1003: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1004: wsh_util_core.add_message(x_return_status);
1005: raise check_exceptions;
1006: ELSIF l_exceptions_tab(exp_cnt).exception_behavior = 'WARNING' THEN
1007: IF l_exceptions_tab(exp_cnt).entity_name = 'TRIP' THEN
1008: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_ENTITY');
1008: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_ENTITY');
1009: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
1010: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
1011: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
1012: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1013: wsh_util_core.add_message(x_return_status);
1014: ELSIF NOT (l_exp_logged) THEN
1015: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
1016: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
1009: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
1010: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
1011: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
1012: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1013: wsh_util_core.add_message(x_return_status);
1014: ELSIF NOT (l_exp_logged) THEN
1015: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
1016: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
1017: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
1015: FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
1016: FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
1017: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
1018: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
1019: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1020: l_exp_logged := TRUE;
1021: wsh_util_core.add_message(x_return_status);
1022: END IF;
1023: END IF;
1017: FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_exceptions_tab(exp_cnt).entity_id);
1018: FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
1019: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1020: l_exp_logged := TRUE;
1021: wsh_util_core.add_message(x_return_status);
1022: END IF;
1023: END IF;
1024: END LOOP;
1025: END IF;
1024: END LOOP;
1025: END IF;
1026:
1027: --
1028: IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y') THEN
1029: -- Get pvt type record structure for trip
1030: --
1031: -- Debug Statements
1032: --
1038: (p_trip_id => p_trip_id,
1039: x_trip_rec => l_trip_rec,
1040: x_return_status => l_return_status);
1041:
1042: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1043: x_return_status := l_return_status;
1044: --
1045: -- Debug Statements
1046: --
1064: p_trip_rec => l_trip_rec,
1065: p_action => 'UPDATE',
1066: x_return_status => l_return_status);
1067:
1068: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1069: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1070: l_num_warn := l_num_warn + 1;
1071: ELSE
1072: x_return_status := l_return_status;
1065: p_action => 'UPDATE',
1066: x_return_status => l_return_status);
1067:
1068: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1069: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1070: l_num_warn := l_num_warn + 1;
1071: ELSE
1072: x_return_status := l_return_status;
1073: --
1093: last_update_login = fnd_global.login_id
1094: WHERE trip_id = p_trip_id;
1095:
1096: IF (SQL%NOTFOUND) THEN
1097: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1098: --
1099: -- Debug Statements
1100: --
1101: IF l_debug_on THEN
1119: p_logging_entity_name => 'TRIP',
1120: p_consider_content => 'Y'
1121: ) ;
1122:
1123: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1124: x_return_status := l_return_status;
1125: wsh_util_core.add_message(x_return_status);
1126: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1127: raise FND_API.G_EXC_ERROR;
1121: ) ;
1122:
1123: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1124: x_return_status := l_return_status;
1125: wsh_util_core.add_message(x_return_status);
1126: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1127: raise FND_API.G_EXC_ERROR;
1128: END IF;
1129: END IF;
1122:
1123: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1124: x_return_status := l_return_status;
1125: wsh_util_core.add_message(x_return_status);
1126: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1127: raise FND_API.G_EXC_ERROR;
1128: END IF;
1129: END IF;
1130:
1128: END IF;
1129: END IF;
1130:
1131: IF l_num_warn >0 THEN
1132: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1133: ELSE
1134: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1135: END IF;
1136: END IF;
1130:
1131: IF l_num_warn >0 THEN
1132: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1133: ELSE
1134: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1135: END IF;
1136: END IF;
1137:
1138: -- Debug Statements
1143: --
1144:
1145: EXCEPTION
1146: WHEN check_exceptions THEN
1147: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1148: --
1149: IF l_debug_on THEN
1150: WSH_DEBUG_SV.logmsg(l_module_name,'Check_Exceptions exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1151: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:Check_Exceptions');
1152: END IF;
1153: --
1154:
1155: WHEN others THEN
1156: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.CHANGE_STATUS');
1157: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1158: --
1159: IF l_debug_on THEN
1160: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1153: --
1154:
1155: WHEN others THEN
1156: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.CHANGE_STATUS');
1157: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1158: --
1159: IF l_debug_on THEN
1160: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1161: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1215: -- Exceptions Project
1216: l_msg_count NUMBER;
1217: l_msg_data VARCHAR2(4000);
1218:
1219: l_trip_id_tab wsh_util_core.id_tab_type;
1220: l_del_tmp_rows wsh_util_core.id_tab_type;
1221:
1222: l_wf_rs VARCHAR2(1); -- Workflow Project
1223: l_debug_on BOOLEAN;
1216: l_msg_count NUMBER;
1217: l_msg_data VARCHAR2(4000);
1218:
1219: l_trip_id_tab wsh_util_core.id_tab_type;
1220: l_del_tmp_rows wsh_util_core.id_tab_type;
1221:
1222: l_wf_rs VARCHAR2(1); -- Workflow Project
1223: l_debug_on BOOLEAN;
1224: --
1250: wsh_debug_sv.LOG(l_module_name, 'p_in_rec.actual_date ', p_in_rec.actual_date );
1251: wsh_debug_sv.LOG(l_module_name, 'p_in_rec.stop_id ', p_in_rec.stop_id );
1252: END IF;
1253: --
1254: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1255: l_num_warnings := 0;
1256: l_num_errors := 0;
1257: --
1258: IF p_in_rec.new_status_code NOT IN ('OP','IT','CL')
1262: -- Invalid status for trip, raise error.
1263: --
1264: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_INVALID_STATUS');
1265: FND_MESSAGE.SET_TOKEN('TRIP_NAME','p_in_rec.name');
1266: wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
1267: RAISE FND_API.G_EXC_ERROR;
1268: --}
1269: END IF;
1270: --
1268: --}
1269: END IF;
1270: --
1271:
1272: IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y')
1273: THEN
1274: --{
1275: -- Get pvt type record structure for trip
1276: --
1284: (p_trip_id => p_in_rec.trip_id,
1285: x_trip_rec => l_trip_rec,
1286: x_return_status => l_return_status);
1287: --
1288: WSH_UTIL_CORE.api_post_call
1289: (
1290: p_return_status => l_return_status,
1291: x_num_warnings => l_num_warnings,
1292: x_num_errors => l_num_errors
1306: p_trip_rec => l_trip_rec,
1307: p_action => 'UPDATE',
1308: x_return_status => l_return_status);
1309: --
1310: WSH_UTIL_CORE.api_post_call
1311: (
1312: p_return_status => l_return_status,
1313: x_num_warnings => l_num_warnings,
1314: x_num_errors => l_num_errors
1365: -- End of code for Workflow Project
1366:
1367: --setting trip to in-transit, need to set delivery to be firmed
1368: --which will make trip to be atleast planned
1369: IF WSH_UTIL_CORE.TP_IS_INSTALLED='Y' AND l_trip_rec.ship_method_code is not null THEN
1370: IF l_trip_rec.mode_of_transport ='TRUCK' and l_trip_rec.vehicle_item_id is null THEN
1371: --if mode is truck, we can't plan/firm trip.
1372: IF l_debug_on THEN
1373: WSH_DEBUG_SV.logmsg(l_module_name,'Not trying to firm trip/delivery because mode is truck');
1382:
1383: IF l_del_tmp_rows.COUNT>0 THEN
1384: wsh_new_delivery_actions.firm (p_del_rows => l_del_tmp_rows,
1385: x_return_status => l_return_status);
1386: WSH_UTIL_CORE.api_post_call
1387: (
1388: p_return_status => l_return_status,
1389: x_num_warnings => l_num_warnings,
1390: x_num_errors => l_num_errors
1398: p_trip_rows => l_trip_id_tab,
1399: p_action => 'FIRM',
1400: x_return_status => l_return_status);
1401:
1402: WSH_UTIL_CORE.api_post_call
1403: (
1404: p_return_status => l_return_status,
1405: x_num_warnings => l_num_warnings,
1406: x_num_errors => l_num_errors
1406: x_num_errors => l_num_errors
1407: );
1408: END IF;--p_status_code
1409: END IF;
1410: ELSIF WSH_UTIL_CORE.TP_IS_INSTALLED='Y' AND l_trip_rec.ship_method_code is null THEN
1411: --TP is installed and ship method is null, cannot make the trip as firmed
1412: IF l_debug_on THEN
1413: WSH_DEBUG_SV.logmsg(l_module_name,'Not trying to firm trip/delivery because ship method is null');
1414: END IF;
1428: p_logging_entity_name => 'TRIP',
1429: p_consider_content => 'Y'
1430: ) ;
1431:
1432: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1433: x_return_status := l_return_status;
1434: wsh_util_core.add_message(x_return_status);
1435: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1436: raise FND_API.G_EXC_ERROR;
1430: ) ;
1431:
1432: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1433: x_return_status := l_return_status;
1434: wsh_util_core.add_message(x_return_status);
1435: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1436: raise FND_API.G_EXC_ERROR;
1437: END IF;
1438: END IF;
1431:
1432: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1433: x_return_status := l_return_status;
1434: wsh_util_core.add_message(x_return_status);
1435: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1436: raise FND_API.G_EXC_ERROR;
1437: END IF;
1438: END IF;
1439: END IF;
1440: --
1441: --
1442: IF l_num_errors > 0
1443: THEN
1444: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1445: ELSIF l_num_warnings > 0
1446: THEN
1447: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1448: ELSE
1443: THEN
1444: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1445: ELSIF l_num_warnings > 0
1446: THEN
1447: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1448: ELSE
1449: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1450: END IF;
1451: --
1445: ELSIF l_num_warnings > 0
1446: THEN
1447: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1448: ELSE
1449: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1450: END IF;
1451: --
1452: --
1453: --
1463: WHEN FND_API.G_EXC_ERROR THEN
1464:
1465: --ROLLBACK TO trip_chgStatus_begin_sp;
1466: --
1467: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1468: --
1469: IF l_debug_on THEN
1470: WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1471: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1474: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1475:
1476: --ROLLBACK TO trip_chgStatus_begin_sp;
1477: --
1478: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1479: --
1480: IF l_debug_on THEN
1481: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1482: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1481: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1482: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1483: END IF;
1484: --
1485: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1486: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1487: --
1488: IF l_debug_on THEN
1489: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1482: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1483: END IF;
1484: --
1485: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1486: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1487: --
1488: IF l_debug_on THEN
1489: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1490: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1485: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1486: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1487: --
1488: IF l_debug_on THEN
1489: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1490: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1491: END IF;
1492: WHEN others THEN
1493: wsh_util_core.default_handler('WSH_NEW_DELIVERY_ACTIONS.changeStatus',l_module_name);
1486: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1487: --
1488: IF l_debug_on THEN
1489: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1490: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1491: END IF;
1492: WHEN others THEN
1493: wsh_util_core.default_handler('WSH_NEW_DELIVERY_ACTIONS.changeStatus',l_module_name);
1494: --
1489: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1490: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1491: END IF;
1492: WHEN others THEN
1493: wsh_util_core.default_handler('WSH_NEW_DELIVERY_ACTIONS.changeStatus',l_module_name);
1494: --
1495: --ROLLBACK TO trip_chgStatus_begin_sp;
1496: --
1497: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1493: wsh_util_core.default_handler('WSH_NEW_DELIVERY_ACTIONS.changeStatus',l_module_name);
1494: --
1495: --ROLLBACK TO trip_chgStatus_begin_sp;
1496: --
1497: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1498: --
1499: -- Debug Statements
1500: --
1501: IF l_debug_on THEN
1552: AND nvl(wnd.shipment_direction,'O') NOT IN ('O','IO') -- J-IB-NPARIKH
1553: AND wnd.initial_pickup_location_id = wts.stop_location_id;
1554: --
1555: --
1556: l_deliveryIdTbl WSH_UTIL_CORE.key_value_tab_type;
1557: l_deliveryIdExtTbl WSH_UTIL_CORE.key_value_tab_type;
1558: --
1559: l_action_prms wsh_deliveries_grp.action_parameters_rectype;
1560: l_del_action_out_rec wsh_deliveries_grp.Delivery_Action_Out_Rec_Type;
1553: AND wnd.initial_pickup_location_id = wts.stop_location_id;
1554: --
1555: --
1556: l_deliveryIdTbl WSH_UTIL_CORE.key_value_tab_type;
1557: l_deliveryIdExtTbl WSH_UTIL_CORE.key_value_tab_type;
1558: --
1559: l_action_prms wsh_deliveries_grp.action_parameters_rectype;
1560: l_del_action_out_rec wsh_deliveries_grp.Delivery_Action_Out_Rec_Type;
1561: l_delivery_id_tab wsh_util_core.id_tab_type;
1557: l_deliveryIdExtTbl WSH_UTIL_CORE.key_value_tab_type;
1558: --
1559: l_action_prms wsh_deliveries_grp.action_parameters_rectype;
1560: l_del_action_out_rec wsh_deliveries_grp.Delivery_Action_Out_Rec_Type;
1561: l_delivery_id_tab wsh_util_core.id_tab_type;
1562: l_trip_name VARCHAR2(30);
1563: l_cnt NUMBER;
1564: l_totalCnt NUMBER;
1565: --
1616: --
1617: --
1618: IF l_debug_on THEN
1619: wsh_debug_sv.log (l_module_name,'dlvy_rec.delivery_id',dlvy_rec.delivery_id);
1620: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_CACHED_VALUE-l_deliveryIdTbl',WSH_DEBUG_SV.C_PROC_LEVEL);
1621: END IF;
1622: --
1623: -- Build a cache of unique delivery IDs
1624: --
1621: END IF;
1622: --
1623: -- Build a cache of unique delivery IDs
1624: --
1625: wsh_util_core.get_cached_value
1626: (
1627: p_cache_tbl => l_deliveryIdTbl,
1628: p_cache_ext_tbl => l_deliveryIdExtTbl,
1629: p_key => dlvy_rec.delivery_id,
1634: --
1635: --
1636: IF l_debug_on THEN
1637: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1638: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1639: END IF;
1640: --
1641: wsh_util_core.api_post_call
1642: (
1637: WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1638: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1639: END IF;
1640: --
1641: wsh_util_core.api_post_call
1642: (
1643: p_return_status => l_return_status,
1644: x_num_warnings => l_num_warnings,
1645: x_num_errors => l_num_errors
1666: --FND_MESSAGE.SET_TOKEN('TRIP_NAME',NVL(l_trip_name,p_rec_attr_tab(l_index).trip_id));
1667: FND_MESSAGE.SET_TOKEN('TRIP_NAME',NVL(l_trip_name, wsh_trips_pvt.get_name( p_rec_attr_tab(l_index).trip_id)));
1668: END IF;
1669: --
1670: wsh_util_core.add_message(wsh_util_core.g_ret_sts_warning,l_module_name);
1671: l_num_warnings := NVL(l_num_warnings,0) + 1;
1672: --}
1673: END IF;
1674: --
1747: IF l_debug_on THEN
1748: wsh_debug_sv.log(l_module_name,'Return Status After Calling generate_routing_response',l_return_status);
1749: END IF;
1750: --
1751: wsh_util_core.api_post_call(
1752: p_return_status => l_return_status,
1753: x_num_warnings => l_num_warnings,
1754: x_num_errors => l_num_errors,
1755: p_msg_Data => l_msg_data);
1763: END IF;
1764: --
1765: IF l_num_errors > 0
1766: THEN
1767: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1768: ELSIF l_num_warnings > 0
1769: THEN
1770: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1771: ELSE
1766: THEN
1767: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1768: ELSIF l_num_warnings > 0
1769: THEN
1770: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1771: ELSE
1772: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1773: END IF;
1774: --
1768: ELSIF l_num_warnings > 0
1769: THEN
1770: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1771: ELSE
1772: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1773: END IF;
1774: --
1775: -- Debug Statements
1776: --
1781: --}
1782: EXCEPTION
1783: --{
1784: WHEN FND_API.G_EXC_ERROR THEN
1785: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1786: --
1787: -- Debug Statements
1788: --
1789: IF l_debug_on THEN
1791: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1792: END IF;
1793: --
1794: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1795: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1796: --
1797: -- Debug Statements
1798: --
1799: IF l_debug_on THEN
1800: WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1801: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1802: END IF;
1803: --
1804: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1805: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1806: --
1807: -- Debug Statements
1808: --
1801: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1802: END IF;
1803: --
1804: WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1805: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1806: --
1807: -- Debug Statements
1808: --
1809: IF l_debug_on THEN
1806: --
1807: -- Debug Statements
1808: --
1809: IF l_debug_on THEN
1810: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1811: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1812: END IF;
1813: --
1814: WHEN OTHERS THEN
1807: -- Debug Statements
1808: --
1809: IF l_debug_on THEN
1810: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1811: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1812: END IF;
1813: --
1814: WHEN OTHERS THEN
1815: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1811: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1812: END IF;
1813: --
1814: WHEN OTHERS THEN
1815: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1816: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.generateRoutingResponse');
1817: --
1818: -- Debug Statements
1819: --
1812: END IF;
1813: --
1814: WHEN OTHERS THEN
1815: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1816: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.generateRoutingResponse');
1817: --
1818: -- Debug Statements
1819: --
1820: IF l_debug_on THEN
1856: --
1857: --========================================================================
1858: PROCEDURE get_next_del_rows( p_del_tbl IN Del_Rec_Type,
1859: x_idx IN OUT NOCOPY NUMBER,
1860: x_del_rows OUT NOCOPY wsh_util_core.id_tab_type,
1861: x_return_status OUT NOCOPY VARCHAR) IS
1862:
1863: --
1864: j NUMBER;
1893: wsh_debug_sv.log(l_module_name, 'p_del_tbl.COUNT', p_del_tbl.delivery_id.COUNT);
1894: END IF;
1895: --
1896:
1897: IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y') THEN
1898: l_fte_flag := 'Y';
1899: END IF;
1900:
1901: --If the x_idx is greater than the delivery_id.COUNT then
1965: l_counter := l_counter + 1;
1966:
1967: END LOOP; --}
1968: END IF; --}
1969: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1970:
1971: IF l_debug_on THEN
1972: wsh_debug_sv.log(l_module_name, 'x_idx', x_idx);
1973: WSH_DEBUG_SV.pop(l_module_name);
1976: EXCEPTION
1977: --
1978: WHEN others THEN
1979: --
1980: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.GET_NEXT_DEL_ROWS');
1981: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1982: --
1983: IF l_debug_on THEN
1984: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1977: --
1978: WHEN others THEN
1979: --
1980: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.GET_NEXT_DEL_ROWS');
1981: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1982: --
1983: IF l_debug_on THEN
1984: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1985: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2075: -- the column sequence determines the sorting
2076:
2077: CLOSE c_sort_del;
2078:
2079: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2080:
2081: IF l_debug_on THEN
2082: wsh_debug_sv.log(l_module_name, 'x_del_tbl.COUNT', x_del_tbl.delivery_id.COUNT);
2083: WSH_DEBUG_SV.pop(l_module_name);
2085: --
2086: EXCEPTION
2087: --
2088: WHEN e_invalid_count THEN
2089: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2090: --
2091: IF l_debug_on THEN
2092: WSH_DEBUG_SV.logmsg(l_module_name,'The input table is empty.' ,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2093: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:e_invalid_count');
2094: END IF;
2095: --
2096: WHEN others THEN
2097: --
2098: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.SORT_DEL');
2099: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2100: --
2101: IF l_debug_on THEN
2102: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2095: --
2096: WHEN others THEN
2097: --
2098: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.SORT_DEL');
2099: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2100: --
2101: IF l_debug_on THEN
2102: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2103: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2121: --
2122: --========================================================================
2123:
2124: PROCEDURE autocreate_trip_wrp(
2125: p_del_rows IN wsh_util_core.id_tab_type,
2126: p_entity IN VARCHAR2,
2127: x_trip_ids OUT NOCOPY wsh_util_core.id_tab_type,
2128: x_trip_names OUT NOCOPY wsh_util_core.Column_Tab_Type,
2129: x_return_status OUT NOCOPY VARCHAR2,
2123:
2124: PROCEDURE autocreate_trip_wrp(
2125: p_del_rows IN wsh_util_core.id_tab_type,
2126: p_entity IN VARCHAR2,
2127: x_trip_ids OUT NOCOPY wsh_util_core.id_tab_type,
2128: x_trip_names OUT NOCOPY wsh_util_core.Column_Tab_Type,
2129: x_return_status OUT NOCOPY VARCHAR2,
2130: p_sc_pickup_date IN DATE DEFAULT NULL,
2131: p_sc_dropoff_date IN DATE DEFAULT NULL) IS
2124: PROCEDURE autocreate_trip_wrp(
2125: p_del_rows IN wsh_util_core.id_tab_type,
2126: p_entity IN VARCHAR2,
2127: x_trip_ids OUT NOCOPY wsh_util_core.id_tab_type,
2128: x_trip_names OUT NOCOPY wsh_util_core.Column_Tab_Type,
2129: x_return_status OUT NOCOPY VARCHAR2,
2130: p_sc_pickup_date IN DATE DEFAULT NULL,
2131: p_sc_dropoff_date IN DATE DEFAULT NULL) IS
2132:
2145:
2146: l_del_tbl Del_Rec_Type;
2147: l_del_tbl_not_sorted Del_Rec_Type;
2148: l_return_status varchar2(1);
2149: l_del_rows wsh_util_core.id_tab_type;
2150: l_trip_id NUMBER;
2151: l_trip_name wsh_trips.name%TYPE;
2152: l_initial_pickup_location_id NUMBER;
2153: l_ultimate_dropoff_location_id NUMBER;
2199: -- and have different pickup and drop-off locations then group these
2200: -- deliveries and create several trips for them.
2201:
2202: sort_del(l_del_tbl_not_sorted,l_del_tbl, l_return_status );
2203: wsh_util_core.api_post_call(
2204: p_return_status => l_return_status,
2205: x_num_warnings => l_num_warnings,
2206: x_num_errors => l_num_errors
2207: );
2214: get_next_del_rows( p_del_tbl => l_del_tbl,
2215: x_idx => l_idx,
2216: x_del_rows => l_del_rows,
2217: x_return_status => l_return_status);
2218: wsh_util_core.api_post_call(
2219: p_return_status => l_return_status,
2220: x_num_warnings => l_num_warnings,
2221: x_num_errors => l_num_errors
2222: );
2229: x_return_status => l_return_status,
2230: p_sc_pickup_date => p_sc_pickup_date,
2231: p_sc_dropoff_date => p_sc_dropoff_date);
2232:
2233: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2234: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2235: END IF;
2236: wsh_util_core.api_post_call(
2237: p_return_status => l_return_status,
2232:
2233: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2234: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2235: END IF;
2236: wsh_util_core.api_post_call(
2237: p_return_status => l_return_status,
2238: x_num_warnings => l_num_warnings,
2239: x_num_errors => l_num_errors,
2240: p_raise_error_flag => FALSE
2247: END LOOP; --}
2248:
2249:
2250: IF l_num_errors = 0 AND l_num_warnings = 0 THEN
2251: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2252: ELSIF l_num_errors = l_api_calls THEN
2253: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2254: ELSE
2255: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
2249:
2250: IF l_num_errors = 0 AND l_num_warnings = 0 THEN
2251: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2252: ELSIF l_num_errors = l_api_calls THEN
2253: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2254: ELSE
2255: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
2256: END IF;
2257:
2251: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2252: ELSIF l_num_errors = l_api_calls THEN
2253: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2254: ELSE
2255: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
2256: END IF;
2257:
2258: IF l_debug_on THEN
2259: WSH_DEBUG_SV.pop(l_module_name);
2262: EXCEPTION
2263: --
2264: WHEN others THEN
2265: --
2266: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.AUTOCREATE_TRIP_WRP');
2267: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2268: --
2269: IF l_debug_on THEN
2270: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2263: --
2264: WHEN others THEN
2265: --
2266: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.AUTOCREATE_TRIP_WRP');
2267: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2268: --
2269: IF l_debug_on THEN
2270: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2271: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2278: -- Procedure: Autocreate_Trip_multi
2279: -- New procedure which gets the ignore_for_planning flag
2280: -- and groups them on the basis of that.
2281: PROCEDURE autocreate_trip_multi(
2282: p_del_rows IN wsh_util_core.id_tab_type,
2283: p_entity IN VARCHAR2,
2284: x_trip_ids OUT NOCOPY wsh_util_core.id_tab_type,
2285: x_trip_names OUT NOCOPY wsh_util_core.Column_Tab_Type,
2286: x_return_status OUT NOCOPY VARCHAR2) IS
2280: -- and groups them on the basis of that.
2281: PROCEDURE autocreate_trip_multi(
2282: p_del_rows IN wsh_util_core.id_tab_type,
2283: p_entity IN VARCHAR2,
2284: x_trip_ids OUT NOCOPY wsh_util_core.id_tab_type,
2285: x_trip_names OUT NOCOPY wsh_util_core.Column_Tab_Type,
2286: x_return_status OUT NOCOPY VARCHAR2) IS
2287:
2288: --OTM R12, glog proj
2281: PROCEDURE autocreate_trip_multi(
2282: p_del_rows IN wsh_util_core.id_tab_type,
2283: p_entity IN VARCHAR2,
2284: x_trip_ids OUT NOCOPY wsh_util_core.id_tab_type,
2285: x_trip_names OUT NOCOPY wsh_util_core.Column_Tab_Type,
2286: x_return_status OUT NOCOPY VARCHAR2) IS
2287:
2288: --OTM R12, glog proj
2289: -- When OTM is installed and user performs autocreate Trip action
2297: SELECT NVL(ignore_for_planning,p_ignore_flag) ignore_for_planning
2298: FROM wsh_new_deliveries
2299: WHERE delivery_id=p_delid;
2300:
2301: l_ignore_ids wsh_util_core.id_tab_type;
2302: l_include_ids wsh_util_core.id_tab_type;
2303: l_return_status VARCHAR2(1);
2304: l_trip_id NUMBER;
2305: l_trip_name wsh_trips.name%TYPE;
2298: FROM wsh_new_deliveries
2299: WHERE delivery_id=p_delid;
2300:
2301: l_ignore_ids wsh_util_core.id_tab_type;
2302: l_include_ids wsh_util_core.id_tab_type;
2303: l_return_status VARCHAR2(1);
2304: l_trip_id NUMBER;
2305: l_trip_name wsh_trips.name%TYPE;
2306: l_index NUMBER;
2303: l_return_status VARCHAR2(1);
2304: l_trip_id NUMBER;
2305: l_trip_name wsh_trips.name%TYPE;
2306: l_index NUMBER;
2307: l_trip_ids wsh_util_core.id_tab_type;
2308: l_trip_names wsh_util_core.Column_Tab_Type;
2309: l_gc3_is_installed VARCHAR2(1); -- OTM R12, glog proj
2310: l_ignore_flag WSH_TRIPS.IGNORE_FOR_PLANNING%TYPE; -- OTM R12, glog proj
2311: l_count_error NUMBER;
2304: l_trip_id NUMBER;
2305: l_trip_name wsh_trips.name%TYPE;
2306: l_index NUMBER;
2307: l_trip_ids wsh_util_core.id_tab_type;
2308: l_trip_names wsh_util_core.Column_Tab_Type;
2309: l_gc3_is_installed VARCHAR2(1); -- OTM R12, glog proj
2310: l_ignore_flag WSH_TRIPS.IGNORE_FOR_PLANNING%TYPE; -- OTM R12, glog proj
2311: l_count_error NUMBER;
2312:
2332: wsh_debug_sv.log(l_module_name, 'p_entity', p_entity);
2333: wsh_debug_sv.log(l_module_name, 'p_del_rows.COUNT', p_del_rows.COUNT);
2334: END IF;
2335: --
2336: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2337:
2338: --OTM R12, glog proj, use Global Variable
2339: l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
2340:
2335: --
2336: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2337:
2338: --OTM R12, glog proj, use Global Variable
2339: l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
2340:
2341: -- If null, call the function
2342: IF l_gc3_is_installed IS NULL THEN
2343: l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
2339: l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
2340:
2341: -- If null, call the function
2342: IF l_gc3_is_installed IS NULL THEN
2343: l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
2344: END IF;
2345:
2346: IF l_gc3_is_installed = 'Y' THEN
2347: l_ignore_flag := 'Y';
2352:
2353:
2354: -- OTM R12, glog project
2355: -- If gc3 is installed, sort Include and Ignore lines/deliveries
2356: IF (WSH_UTIL_CORE.TP_IS_INSTALLED = 'N') AND (l_gc3_is_installed = 'N') THEN
2357: --just call the core api
2358: autocreate_trip_wrp(
2359: p_del_rows => p_del_rows,
2360: p_entity => p_entity,
2393: -- Only filtered deliveries will be considered for further processing
2394: IF l_count_error > 0 THEN
2395: FND_MESSAGE.SET_NAME('WSH','WSH_OTM_CR_TRIP_SUMMARY');
2396: IF l_count_error = p_del_rows.count THEN
2397: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
2398: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2399: x_return_status := l_return_status;
2400: IF l_debug_on THEN
2401: WSH_DEBUG_SV.log(l_module_name, 'Count of Errors', l_count_error);
2394: IF l_count_error > 0 THEN
2395: FND_MESSAGE.SET_NAME('WSH','WSH_OTM_CR_TRIP_SUMMARY');
2396: IF l_count_error = p_del_rows.count THEN
2397: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
2398: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2399: x_return_status := l_return_status;
2400: IF l_debug_on THEN
2401: WSH_DEBUG_SV.log(l_module_name, 'Count of Errors', l_count_error);
2402: WSH_DEBUG_SV.pop(l_module_name);
2402: WSH_DEBUG_SV.pop(l_module_name);
2403: END IF;
2404: RETURN;
2405: ELSE
2406: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
2407: l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2408: x_return_status := l_return_status;
2409: END IF;
2410: END IF;
2403: END IF;
2404: RETURN;
2405: ELSE
2406: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
2407: l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2408: x_return_status := l_return_status;
2409: END IF;
2410: END IF;
2411:
2417: x_trip_ids => l_trip_ids,
2418: x_trip_names => l_trip_names,
2419: x_return_status => l_return_status);
2420:
2421: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
2422: x_return_status:=l_return_status;
2423: IF l_debug_on THEN
2424: WSH_DEBUG_SV.pop(l_module_name);
2425: END IF;
2443: x_trip_names => l_trip_names,
2444: x_return_status => l_return_status);
2445:
2446:
2447: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
2448: x_return_status:=l_return_status; -- Bug#3884302
2449: IF l_debug_on THEN
2450: WSH_DEBUG_SV.pop(l_module_name);
2451: END IF;
2458: END LOOP;
2459: END IF;
2460: END IF;
2461:
2462: IF l_return_status=WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2463: x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
2464: END IF;
2465:
2466: END IF; --tp_is_installed
2459: END IF;
2460: END IF;
2461:
2462: IF l_return_status=WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2463: x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
2464: END IF;
2465:
2466: END IF; --tp_is_installed
2467:
2474: --
2475:
2476: EXCEPTION
2477: WHEN others THEN
2478: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.AUTOCREATE_TRIP_MULTI');
2479: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2480:
2481: --
2482: -- Debug Statements
2475:
2476: EXCEPTION
2477: WHEN others THEN
2478: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.AUTOCREATE_TRIP_MULTI');
2479: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2480:
2481: --
2482: -- Debug Statements
2483: --
2489: END autocreate_trip_multi;
2490:
2491:
2492: PROCEDURE autocreate_trip(
2493: p_del_rows IN wsh_util_core.id_tab_type,
2494: p_entity IN VARCHAR2,
2495: x_trip_id OUT NOCOPY NUMBER,
2496: x_trip_name OUT NOCOPY VARCHAR2,
2497: x_return_status OUT NOCOPY VARCHAR2,
2550: l_rowid VARCHAR2(30);
2551: l_trip_info WSH_TRIPS_PVT.TRIP_REC_TYPE;
2552: assigned_del_error EXCEPTION;
2553: l_return_status VARCHAR2(1);
2554: l_del_legs WSH_UTIL_CORE.ID_TAB_TYPE;
2555: l_good_dels WSH_UTIL_CORE.ID_TAB_TYPE;
2556:
2557: /* H integration for Multi Leg */
2558: l_num_warn NUMBER := 0;
2551: l_trip_info WSH_TRIPS_PVT.TRIP_REC_TYPE;
2552: assigned_del_error EXCEPTION;
2553: l_return_status VARCHAR2(1);
2554: l_del_legs WSH_UTIL_CORE.ID_TAB_TYPE;
2555: l_good_dels WSH_UTIL_CORE.ID_TAB_TYPE;
2556:
2557: /* H integration for Multi Leg */
2558: l_num_warn NUMBER := 0;
2559: l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
2572: -- Bug 3413364, skip the delivery if it does not have both initial pickup date and ultimate dropoff date
2573: l_initial_pickup_date DATE;
2574: l_ultimate_dropoff_date DATE;
2575: l_num_skipped NUMBER := 0;
2576: l_stop_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
2577: l_dbi_rs VARCHAR2(1); -- DBI Project
2578:
2579: l_trip_ids WSH_UTIL_CORE.ID_TAB_TYPE;
2580: l_success_trip_ids WSH_UTIL_CORE.ID_TAB_TYPE;
2575: l_num_skipped NUMBER := 0;
2576: l_stop_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
2577: l_dbi_rs VARCHAR2(1); -- DBI Project
2578:
2579: l_trip_ids WSH_UTIL_CORE.ID_TAB_TYPE;
2580: l_success_trip_ids WSH_UTIL_CORE.ID_TAB_TYPE;
2581:
2582: l_gc3_is_installed VARCHAR2(1); -- OTM R12, glog proj
2583: l_ignore_flag WSH_TRIPS.IGNORE_FOR_PLANNING%TYPE; -- OTM R12, glog proj
2576: l_stop_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
2577: l_dbi_rs VARCHAR2(1); -- DBI Project
2578:
2579: l_trip_ids WSH_UTIL_CORE.ID_TAB_TYPE;
2580: l_success_trip_ids WSH_UTIL_CORE.ID_TAB_TYPE;
2581:
2582: l_gc3_is_installed VARCHAR2(1); -- OTM R12, glog proj
2583: l_ignore_flag WSH_TRIPS.IGNORE_FOR_PLANNING%TYPE; -- OTM R12, glog proj
2584: l_count_error NUMBER;
2585:
2586: --
2587: BEGIN
2588: -- Bug 3413364, skip the delivery if it does not have both initial pickup date and ultimate dropoff date
2589: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2590: -- Create a list of deliveries that are unassigned. All other deliveries have
2591: -- error messages set.
2592: --
2593: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2606: END IF;
2607: --
2608:
2609: --OTM R12, glog proj, use Global Variable
2610: l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
2611: l_count_error := 0;
2612:
2613: -- If null, call the function
2614: IF l_gc3_is_installed IS NULL THEN
2611: l_count_error := 0;
2612:
2613: -- If null, call the function
2614: IF l_gc3_is_installed IS NULL THEN
2615: l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
2616: END IF;
2617: IF l_gc3_is_installed = 'Y' THEN
2618: l_ignore_flag := 'Y';
2619: ELSE
2637: --{
2638: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_AU_TRIP_ASSIGN_DEL');
2639: FND_MESSAGE.SET_TOKEN('DEL_NAME',
2640: wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
2641: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
2642: l_num_skipped := l_num_skipped + 1;
2643: --}
2644: END IF;
2645: ELSE
2662:
2663: IF l_gc3_is_installed = 'Y' AND l_ignore_for_planning = 'N' THEN--{
2664: -- Raise a new generic error message here, just trip creation will not happen.
2665: FND_MESSAGE.SET_NAME('WSH','WSH_OTM_CR_TRIP');
2666: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
2667: ----- skip this delivery, handled towards the end
2668: l_num_skipped := l_num_skipped + 1;
2669: l_count_error := l_count_error + 1;
2670: ELSE--} {
2687: AND nvl(l_shipment_direction,'O') IN ('O', 'IO') THEN--{
2688: --
2689: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_DATES_MISSING');
2690: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
2691: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
2692: l_num_skipped := l_num_skipped + 1;
2693: --
2694: ELSE
2695: --} {
2741: -- API - Error or Warning
2742: IF l_count_error > 0 THEN
2743: FND_MESSAGE.SET_NAME('WSH','WSH_OTM_CR_TRIP_SUMMARY');
2744: IF l_count_error = p_del_rows.count THEN
2745: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2746: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
2747: IF l_debug_on THEN
2748: WSH_DEBUG_SV.log(l_module_name, 'Count of Errors', l_count_error);
2749: WSH_DEBUG_SV.pop(l_module_name);
2742: IF l_count_error > 0 THEN
2743: FND_MESSAGE.SET_NAME('WSH','WSH_OTM_CR_TRIP_SUMMARY');
2744: IF l_count_error = p_del_rows.count THEN
2745: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2746: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
2747: IF l_debug_on THEN
2748: WSH_DEBUG_SV.log(l_module_name, 'Count of Errors', l_count_error);
2749: WSH_DEBUG_SV.pop(l_module_name);
2750: END IF;
2749: WSH_DEBUG_SV.pop(l_module_name);
2750: END IF;
2751: RETURN;
2752: ELSE -- x_return_status is populated at the end
2753: WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
2754: END IF;
2755: END IF;
2756:
2757: --
2781: END IF;
2782: --
2783: wsh_trips_pvt.create_trip(l_trip_info, l_rowid, x_trip_id, x_trip_name,x_return_status);
2784: --
2785: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2786: --
2787: IF l_debug_on THEN
2788: wsh_debug_sv.log(l_module_name, 'Return Status after calling create_trip', x_return_Status);
2789: WSH_DEBUG_SV.pop(l_module_name);
2809: p_sc_dropoff_date => p_sc_dropoff_date
2810: );
2811: --
2812:
2813: IF x_return_status in ( WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2814: --
2815: IF l_debug_on THEN
2816: wsh_debug_sv.log(l_module_name, 'Return Status after calling wsh_delivery_legs_actions.assign_deliveries', x_return_Status);
2817: WSH_DEBUG_SV.pop(l_module_name);
2822: ELSE
2823: --{
2824: -- H integration
2825: --
2826: IF x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2827: l_num_warn := l_num_warn + 1;
2828: END IF;
2829:
2830: IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y') THEN
2826: IF x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2827: l_num_warn := l_num_warn + 1;
2828: END IF;
2829:
2830: IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y') THEN
2831: l_fte_flag := 'Y';
2832: END IF;
2833: --
2834: FOR st IN trip_stops(x_trip_id) LOOP
2848: (p_stop_id => st.stop_id,
2849: x_stop_rec => l_stop_rec,
2850: x_return_status => l_return_status);
2851: --
2852: IF x_return_status in ( WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2853: --
2854: x_return_status := l_return_status;
2855: --
2856: IF l_debug_on THEN
2858: END IF;
2859: --
2860: RETURN;
2861: --
2862: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2863: l_num_warn := l_num_warn + 1;
2864: END IF;
2865: --
2866: l_stop_rec.planned_departure_date := nvl(l_stop_rec.planned_arrival_date,SYSDATE);
2874: p_trip_rec => l_trip_rec,
2875: p_action => 'UPDATE',
2876: x_return_status => l_return_status);
2877: --
2878: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2879: --
2880: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2881: l_num_warn := l_num_warn + 1;
2882: ELSE
2876: x_return_status => l_return_status);
2877: --
2878: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2879: --
2880: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2881: l_num_warn := l_num_warn + 1;
2882: ELSE
2883: x_return_status := l_return_status;
2884: --
2916: (p_stop_id => st.stop_id,
2917: x_stop_rec => l_stop_rec,
2918: x_return_status => l_return_status);
2919: --
2920: IF x_return_status in ( WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2921: x_return_status := l_return_status;
2922: --
2923: IF l_debug_on THEN
2924: WSH_DEBUG_SV.pop(l_module_name);
2924: WSH_DEBUG_SV.pop(l_module_name);
2925: END IF;
2926: --
2927: RETURN;
2928: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2929: l_num_warn := l_num_warn + 1;
2930: END IF;
2931: --
2932: l_stop_rec.planned_arrival_date := nvl(l_stop_rec.planned_departure_date,SYSDATE);
2940: p_trip_rec => l_trip_rec,
2941: p_action => 'UPDATE',
2942: x_return_status => l_return_status);
2943: --
2944: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2945: --
2946: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2947: l_num_warn := l_num_warn + 1;
2948: ELSE
2942: x_return_status => l_return_status);
2943: --
2944: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2945: --
2946: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2947: l_num_warn := l_num_warn + 1;
2948: ELSE
2949: x_return_status := l_return_status;
2950: --
2979:
2980: IF l_debug_on THEN
2981: WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
2982: END IF;
2983: IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2984: x_return_status := l_dbi_rs;
2985: -- just pass this return status to caller API
2986: IF l_debug_on THEN
2987: WSH_DEBUG_SV.log(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
3006:
3007: WSH_TRIPS_ACTIONS.reset_stop_planned_dates( p_trip_id => l_trip_id,
3008: p_caller => 'WSH_AUTOCREATE_TRIP',
3009: x_return_status => l_return_status);
3010: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3011: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3012: l_num_warn := l_num_warn + 1;
3013: ELSE
3014: x_return_status := l_return_status;
3007: WSH_TRIPS_ACTIONS.reset_stop_planned_dates( p_trip_id => l_trip_id,
3008: p_caller => 'WSH_AUTOCREATE_TRIP',
3009: x_return_status => l_return_status);
3010: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3011: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3012: l_num_warn := l_num_warn + 1;
3013: ELSE
3014: x_return_status := l_return_status;
3015: --
3027: ( p_trip_ids => l_trip_ids,
3028: p_caller => 'WSH_AUTOCREATE_TRIP',
3029: x_success_trip_ids => l_success_trip_ids,
3030: x_return_status => l_return_status);
3031: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
3032: x_return_status := l_return_status;
3033: --
3034: IF l_debug_on THEN
3035: WSH_DEBUG_SV.pop(l_module_name);
3035: WSH_DEBUG_SV.pop(l_module_name);
3036: END IF;
3037: --
3038: RETURN;
3039: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3040: l_num_warn := l_num_warn + 1;
3041: END IF;
3042: END IF; --if get_stop_seq_mode = PAD
3043: --}
3052: (p_table_name => 'WSH_TRIPS',
3053: p_primary_id => x_trip_id,
3054: x_return_status => x_return_status);
3055:
3056: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3057: IF l_debug_on THEN
3058: WSH_DEBUG_SV.logmsg(l_module_name, 'PROC WSH_FLEXFIELD_UTILS.WRITE_DFF_ATTRIBUTES RETURNED ERROR' );
3059: END IF;
3060: IF l_debug_on THEN
3066: -- Bug 3413364, skip the delivery if it does not have both
3067: -- initial pickup date and ultimate dropoff date
3068: --
3069: IF l_num_skipped > 0 AND l_num_skipped = p_del_rows.count THEN
3070: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3071: ELSIF l_num_skipped > 0 or l_num_warn> 0 THEN
3072: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3073: END IF;
3074: --
3068: --
3069: IF l_num_skipped > 0 AND l_num_skipped = p_del_rows.count THEN
3070: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3071: ELSIF l_num_skipped > 0 or l_num_warn> 0 THEN
3072: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3073: END IF;
3074: --
3075: -- TO DO: Add message for successful completion of action
3076: IF x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3072: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3073: END IF;
3074: --
3075: -- TO DO: Add message for successful completion of action
3076: IF x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3077: FND_MESSAGE.SET_NAME('WSH', 'WSH_AUTOCREATE_TRIP_WARN');
3078: wsh_util_core.add_message(x_return_status);
3079: END IF;
3080: --
3074: --
3075: -- TO DO: Add message for successful completion of action
3076: IF x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3077: FND_MESSAGE.SET_NAME('WSH', 'WSH_AUTOCREATE_TRIP_WARN');
3078: wsh_util_core.add_message(x_return_status);
3079: END IF;
3080: --
3081: IF l_debug_on THEN
3082: WSH_DEBUG_SV.pop(l_module_name);
3085: EXCEPTION
3086: --
3087: WHEN others THEN
3088: --
3089: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.AUTOCREATE_TRIP');
3090: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3091: --
3092: IF l_debug_on THEN
3093: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3086: --
3087: WHEN others THEN
3088: --
3089: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.AUTOCREATE_TRIP');
3090: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3091: --
3092: IF l_debug_on THEN
3093: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3094: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3099:
3100: --Compatibility Changes - removed trip_id, trip_name and added x_trip_rows
3101:
3102: PROCEDURE autocreate_del_trip(
3103: p_line_rows IN wsh_util_core.id_tab_type,
3104: p_org_rows IN wsh_util_core.id_tab_type,
3105: p_max_detail_commit IN NUMBER := 1000,
3106: x_del_rows OUT NOCOPY wsh_util_core.id_tab_type,
3107: x_trip_rows OUT NOCOPY wsh_util_core.id_tab_type,
3100: --Compatibility Changes - removed trip_id, trip_name and added x_trip_rows
3101:
3102: PROCEDURE autocreate_del_trip(
3103: p_line_rows IN wsh_util_core.id_tab_type,
3104: p_org_rows IN wsh_util_core.id_tab_type,
3105: p_max_detail_commit IN NUMBER := 1000,
3106: x_del_rows OUT NOCOPY wsh_util_core.id_tab_type,
3107: x_trip_rows OUT NOCOPY wsh_util_core.id_tab_type,
3108: x_return_status OUT NOCOPY VARCHAR2) IS
3102: PROCEDURE autocreate_del_trip(
3103: p_line_rows IN wsh_util_core.id_tab_type,
3104: p_org_rows IN wsh_util_core.id_tab_type,
3105: p_max_detail_commit IN NUMBER := 1000,
3106: x_del_rows OUT NOCOPY wsh_util_core.id_tab_type,
3107: x_trip_rows OUT NOCOPY wsh_util_core.id_tab_type,
3108: x_return_status OUT NOCOPY VARCHAR2) IS
3109:
3110: l_grouping_rows wsh_util_core.id_tab_type;
3103: p_line_rows IN wsh_util_core.id_tab_type,
3104: p_org_rows IN wsh_util_core.id_tab_type,
3105: p_max_detail_commit IN NUMBER := 1000,
3106: x_del_rows OUT NOCOPY wsh_util_core.id_tab_type,
3107: x_trip_rows OUT NOCOPY wsh_util_core.id_tab_type,
3108: x_return_status OUT NOCOPY VARCHAR2) IS
3109:
3110: l_grouping_rows wsh_util_core.id_tab_type;
3111:
3106: x_del_rows OUT NOCOPY wsh_util_core.id_tab_type,
3107: x_trip_rows OUT NOCOPY wsh_util_core.id_tab_type,
3108: x_return_status OUT NOCOPY VARCHAR2) IS
3109:
3110: l_grouping_rows wsh_util_core.id_tab_type;
3111:
3112: --
3113: l_debug_on BOOLEAN;
3114: --
3121: l_cc_group_info WSH_FTE_COMP_CONSTRAINT_PKG.cc_group_tab_type;
3122:
3123: b_cc_linefailed boolean;
3124: b_cc_groupidexists boolean;
3125: l_id_tab_temp wsh_util_core.id_tab_type;
3126: l_line_rows_temp wsh_util_core.id_tab_type;
3127: l_cc_count_success NUMBER;
3128: l_cc_count_group_ids NUMBER;
3129: l_cc_count_rec NUMBER;
3122:
3123: b_cc_linefailed boolean;
3124: b_cc_groupidexists boolean;
3125: l_id_tab_temp wsh_util_core.id_tab_type;
3126: l_line_rows_temp wsh_util_core.id_tab_type;
3127: l_cc_count_success NUMBER;
3128: l_cc_count_group_ids NUMBER;
3129: l_cc_count_rec NUMBER;
3130: l_cc_group_ids wsh_util_core.id_tab_type;
3126: l_line_rows_temp wsh_util_core.id_tab_type;
3127: l_cc_count_success NUMBER;
3128: l_cc_count_group_ids NUMBER;
3129: l_cc_count_rec NUMBER;
3130: l_cc_group_ids wsh_util_core.id_tab_type;
3131: l_cc_count_trip_rows NUMBER;
3132: l_cc_count_del_rows NUMBER;
3133: l_cc_count_grouping_rows NUMBER;
3134: l_del_rows_temp wsh_util_core.id_tab_type;
3130: l_cc_group_ids wsh_util_core.id_tab_type;
3131: l_cc_count_trip_rows NUMBER;
3132: l_cc_count_del_rows NUMBER;
3133: l_cc_count_grouping_rows NUMBER;
3134: l_del_rows_temp wsh_util_core.id_tab_type;
3135: --l_trip_name VARCHAR2(30);
3136: l_cc_trip_id wsh_util_core.id_tab_type;
3137: l_trip_id_tab wsh_util_core.id_tab_type;
3138: l_cc_del_rows wsh_util_core.id_tab_type;
3132: l_cc_count_del_rows NUMBER;
3133: l_cc_count_grouping_rows NUMBER;
3134: l_del_rows_temp wsh_util_core.id_tab_type;
3135: --l_trip_name VARCHAR2(30);
3136: l_cc_trip_id wsh_util_core.id_tab_type;
3137: l_trip_id_tab wsh_util_core.id_tab_type;
3138: l_cc_del_rows wsh_util_core.id_tab_type;
3139: l_cc_grouping_rows wsh_util_core.id_tab_type;
3140: l_cc_return_status VARCHAR2(1);
3133: l_cc_count_grouping_rows NUMBER;
3134: l_del_rows_temp wsh_util_core.id_tab_type;
3135: --l_trip_name VARCHAR2(30);
3136: l_cc_trip_id wsh_util_core.id_tab_type;
3137: l_trip_id_tab wsh_util_core.id_tab_type;
3138: l_cc_del_rows wsh_util_core.id_tab_type;
3139: l_cc_grouping_rows wsh_util_core.id_tab_type;
3140: l_cc_return_status VARCHAR2(1);
3141: -- l_trip_id NUMBER;
3134: l_del_rows_temp wsh_util_core.id_tab_type;
3135: --l_trip_name VARCHAR2(30);
3136: l_cc_trip_id wsh_util_core.id_tab_type;
3137: l_trip_id_tab wsh_util_core.id_tab_type;
3138: l_cc_del_rows wsh_util_core.id_tab_type;
3139: l_cc_grouping_rows wsh_util_core.id_tab_type;
3140: l_cc_return_status VARCHAR2(1);
3141: -- l_trip_id NUMBER;
3142:
3135: --l_trip_name VARCHAR2(30);
3136: l_cc_trip_id wsh_util_core.id_tab_type;
3137: l_trip_id_tab wsh_util_core.id_tab_type;
3138: l_cc_del_rows wsh_util_core.id_tab_type;
3139: l_cc_grouping_rows wsh_util_core.id_tab_type;
3140: l_cc_return_status VARCHAR2(1);
3141: -- l_trip_id NUMBER;
3142:
3143: l_cc_upd_dlvy_intmed_ship_to VARCHAR2(1);
3153: l_cc_del_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
3154: l_cc_det_attr_tab WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type;
3155: l_cc_trip_attr_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
3156: l_cc_stop_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
3157: l_cc_in_ids wsh_util_core.id_tab_type;
3158: l_cc_fail_ids wsh_util_core.id_tab_type;
3159:
3160: CURSOR del_cur(p_dlvy_id NUMBER) IS
3161: SELECT SHIP_METHOD_CODE, INTMED_SHIP_TO_LOCATION_ID
3154: l_cc_det_attr_tab WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type;
3155: l_cc_trip_attr_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
3156: l_cc_stop_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
3157: l_cc_in_ids wsh_util_core.id_tab_type;
3158: l_cc_fail_ids wsh_util_core.id_tab_type;
3159:
3160: CURSOR del_cur(p_dlvy_id NUMBER) IS
3161: SELECT SHIP_METHOD_CODE, INTMED_SHIP_TO_LOCATION_ID
3162: FROM wsh_new_deliveries
3168: FROM wsh_trips
3169: WHERE trip_id = p_trip_id;
3170: --and SHIP_METHOD_CODE is not null;
3171:
3172: l_line_rows wsh_util_core.id_tab_type:=p_line_rows;
3173: --Compatibility Changes
3174:
3175: /* J TP Release */
3176: l_trip_ids wsh_util_core.id_tab_type;
3172: l_line_rows wsh_util_core.id_tab_type:=p_line_rows;
3173: --Compatibility Changes
3174:
3175: /* J TP Release */
3176: l_trip_ids wsh_util_core.id_tab_type;
3177: l_trip_names wsh_util_core.column_tab_type;
3178: l_tripindex NUMBER;
3179:
3180: -- deliveryMerge
3173: --Compatibility Changes
3174:
3175: /* J TP Release */
3176: l_trip_ids wsh_util_core.id_tab_type;
3177: l_trip_names wsh_util_core.column_tab_type;
3178: l_tripindex NUMBER;
3179:
3180: -- deliveryMerge
3181: l_return_status VARCHAR2(1);
3207: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_AUTOCREATE.AUTOCREATE_DEL_ACROSS_ORGS',WSH_DEBUG_SV.C_PROC_LEVEL);
3208: END IF;--}
3209: --
3210: --Compatibility Changes
3211: IF wsh_util_core.fte_is_installed = 'Y' THEN--{
3212:
3213: WSH_FTE_COMP_CONSTRAINT_PKG.validate_constraint_main(
3214: p_api_version_number => 1.0,
3215: p_init_msg_list => FND_API.G_FALSE,
3241: END IF;--}
3242: --
3243:
3244:
3245: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
3246: OR (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR AND l_cc_failed_records.COUNT=l_line_rows.COUNT)
3247: THEN--{
3248: --
3249: -- Debug Statements
3242: --
3243:
3244:
3245: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
3246: OR (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR AND l_cc_failed_records.COUNT=l_line_rows.COUNT)
3247: THEN--{
3248: --
3249: -- Debug Statements
3250: --
3254: --
3255: RETURN;
3256: END IF;--}
3257:
3258: IF l_cc_failed_records.COUNT>0 AND x_return_status=wsh_util_core.g_ret_sts_error THEN--{
3259: IF l_debug_on THEN--{
3260: WSH_DEBUG_SV.logmsg(l_module_name,'All dels have failed compatibility -> delivery and Trip not created');
3261: END IF;--}
3262: -- if one one delivery fails for auto create trip, all the lines should
3261: END IF;--}
3262: -- if one one delivery fails for auto create trip, all the lines should
3263: -- should be unassigned from the deliveries and the action should
3264: -- fail for all the lines. (pack J 2862777)
3265: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3266: fnd_message.set_name('WSH', 'WSH_COMP_ACT_FAIL');
3267: wsh_util_core.add_message(x_return_status);
3268: IF l_debug_on THEN--{
3269: WSH_DEBUG_SV.pop(l_module_name);
3263: -- should be unassigned from the deliveries and the action should
3264: -- fail for all the lines. (pack J 2862777)
3265: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3266: fnd_message.set_name('WSH', 'WSH_COMP_ACT_FAIL');
3267: wsh_util_core.add_message(x_return_status);
3268: IF l_debug_on THEN--{
3269: WSH_DEBUG_SV.pop(l_module_name);
3270: END IF;--}
3271: --
3269: WSH_DEBUG_SV.pop(l_module_name);
3270: END IF;--}
3271: --
3272: RETURN;
3273: ELSIF l_cc_line_groups.COUNT>0 AND x_return_status=wsh_util_core.g_ret_sts_error THEN--}{
3274:
3275: --1. get the group ids by which the constraints API has grouped the lines
3276: l_cc_count_group_ids:=1;
3277: FOR i in l_cc_line_groups.FIRST..l_cc_line_groups.LAST LOOP --{
3384: END LOOP;--}
3385: END IF;--}
3386: END IF; --}
3387:
3388: 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--{
3389: x_return_status:=l_cc_return_status;
3390: 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--}{
3391: x_return_status:=l_cc_return_status;
3392: ELSE--}{
3386: END IF; --}
3387:
3388: 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--{
3389: x_return_status:=l_cc_return_status;
3390: 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--}{
3391: x_return_status:=l_cc_return_status;
3392: ELSE--}{
3393: l_cc_return_status:=x_return_status;
3394: END IF;--}
3485: wsh_debug_sv.log(l_module_name,'Return Status After Calling Autocreate_Del_across_orgs',x_return_status);
3486: END IF;--}
3487:
3488:
3489: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR
3490: (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN--{
3491: --
3492: -- Debug Statements
3493: --
3486: END IF;--}
3487:
3488:
3489: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR
3490: (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN--{
3491: --
3492: -- Debug Statements
3493: --
3494: IF l_debug_on THEN--{
3504: l_cc_failed_records.DELETE;
3505: l_cc_line_groups.DELETE;
3506: l_cc_group_ids.DELETE;
3507: l_id_tab_temp.DELETE;
3508: IF wsh_util_core.fte_is_installed = 'Y' THEN--{
3509:
3510: WSH_FTE_COMP_CONSTRAINT_PKG.validate_constraint_main(
3511: p_api_version_number => 1.0,
3512: p_init_msg_list => FND_API.G_FALSE,
3538: wsh_debug_sv.log(l_module_name,'group_info count After Calling validate_constraint_main',l_cc_group_info.COUNT);
3539: END IF;--}
3540: --
3541:
3542: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN--{
3543: --
3544: -- Debug Statements
3545: --
3546: IF l_debug_on THEN--{
3550: RETURN;
3551: END IF;--}
3552:
3553: --do nothing if all dels have errored out (deliveries shud be created and trip shud not be
3554: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR AND l_cc_failed_records.COUNT > 0) THEN--{
3555: IF l_debug_on THEN--{
3556: WSH_DEBUG_SV.logmsg(l_module_name,'All dels have failed compatibility -> Trip not created');
3557: END IF;--}
3558: -- if one one delivery fails for auto create trip, all the lines should
3557: END IF;--}
3558: -- if one one delivery fails for auto create trip, all the lines should
3559: -- should be unassigned from the deliveries and the action should
3560: -- fail for all the lines. (pack J 2862777)
3561: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3562: fnd_message.set_name('WSH', 'WSH_COMP_ACT_FAIL');
3563: wsh_util_core.add_message(x_return_status);
3564: IF l_debug_on THEN--{
3565: WSH_DEBUG_SV.pop(l_module_name);
3559: -- should be unassigned from the deliveries and the action should
3560: -- fail for all the lines. (pack J 2862777)
3561: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3562: fnd_message.set_name('WSH', 'WSH_COMP_ACT_FAIL');
3563: wsh_util_core.add_message(x_return_status);
3564: IF l_debug_on THEN--{
3565: WSH_DEBUG_SV.pop(l_module_name);
3566: END IF;--}
3567: --
3565: WSH_DEBUG_SV.pop(l_module_name);
3566: END IF;--}
3567: --
3568: RETURN;
3569: ELSIF l_cc_line_groups.COUNT>0 AND x_return_status=wsh_util_core.g_ret_sts_error THEN--}{
3570:
3571: --1. get the group ids by which the constraints API has grouped the lines
3572: l_cc_count_group_ids:=1;
3573: FOR i in l_cc_line_groups.FIRST..l_cc_line_groups.LAST LOOP --{
3615: IF l_debug_on THEN --{
3616: wsh_debug_sv.log(l_module_name,'Return status from WSH_NEW_DELIVERY_ACTIONS.Adjust_Planned_Flag ', l_return_status);
3617: END IF;--}
3618:
3619: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN--{
3620: raise Adjust_Planned_Flag_Err;
3621: END IF; --}
3622:
3623: /* J TP Release */
3662: l_cc_trip_id(l_cc_trip_id.COUNT+1):=l_trip_ids(l_tripindex);
3663: END LOOP;--}
3664: END IF;--}
3665:
3666: 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--{
3667: x_return_status:=l_cc_return_status;
3668: 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--}{
3669: x_return_status:=l_cc_return_status;
3670: ELSE --}{
3664: END IF;--}
3665:
3666: 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--{
3667: x_return_status:=l_cc_return_status;
3668: 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--}{
3669: x_return_status:=l_cc_return_status;
3670: ELSE --}{
3671: l_cc_return_status:=x_return_status;
3672: END IF;--}
3694: IF l_debug_on THEN --{
3695: wsh_debug_sv.log(l_module_name,'Return status from WSH_NEW_DELIVERY_ACTIONS.Adjust_Planned_Flag ', l_return_status);
3696: END IF;--}
3697:
3698: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN--{
3699: raise Adjust_Planned_Flag_Err;
3700: END IF; --}
3701:
3702: /* J TP Release */
3757: IF l_debug_on THEN --{
3758: wsh_debug_sv.log(l_module_name,'Return status from WSH_NEW_DELIVERY_ACTIONS.Adjust_Planned_Flag ', l_return_status);
3759: END IF;--}
3760:
3761: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN--{
3762: raise Adjust_Planned_Flag_Err;
3763: END IF; --}
3764:
3765: /* J TP Release */
3776: x_trip_rows:=l_trip_id_tab;
3777:
3778:
3779:
3780: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN--{
3781: --
3782: -- Debug Statements
3783: --
3784: IF l_debug_on THEN--{
3787: --
3788: RETURN;
3789: END IF;--}
3790:
3791: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3792:
3793: --
3794: -- Debug Statements
3795: --
3802:
3803: -- deliveryMerge
3804: WHEN Adjust_Planned_Flag_Err THEN
3805: FND_MESSAGE.SET_NAME('WSH', 'WSH_ADJUST_PLANNED_FLAG_ERR');
3806: WSH_UTIL_CORE.add_message(l_return_status,l_module_name);
3807: x_return_status := l_return_status;
3808:
3809: IF l_debug_on THEN--{
3810: WSH_DEBUG_SV.logmsg(l_module_name,'Adjust_Planned_Flag_Err exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3811: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:Adjust_Planned_Flag_Err');
3812: END IF;--}
3813:
3814: WHEN others THEN
3815: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.AUTOCREATE_DEL_TRIP');
3816: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3817:
3818:
3819: --
3812: END IF;--}
3813:
3814: WHEN others THEN
3815: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.AUTOCREATE_DEL_TRIP');
3816: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3817:
3818:
3819: --
3820: -- Debug Statements
3825: END IF;--}
3826: --
3827: END autocreate_del_trip;
3828:
3829: PROCEDURE assign_trip( p_line_rows IN wsh_util_core.id_tab_type,
3830: p_trip_id IN NUMBER,
3831: x_del_rows OUT NOCOPY wsh_util_core.id_tab_type,
3832: x_return_status OUT NOCOPY VARCHAR2) IS
3833:
3827: END autocreate_del_trip;
3828:
3829: PROCEDURE assign_trip( p_line_rows IN wsh_util_core.id_tab_type,
3830: p_trip_id IN NUMBER,
3831: x_del_rows OUT NOCOPY wsh_util_core.id_tab_type,
3832: x_return_status OUT NOCOPY VARCHAR2) IS
3833:
3834: l_grouping_rows wsh_util_core.id_tab_type;
3835: l_del_legs wsh_util_core.id_tab_type;
3830: p_trip_id IN NUMBER,
3831: x_del_rows OUT NOCOPY wsh_util_core.id_tab_type,
3832: x_return_status OUT NOCOPY VARCHAR2) IS
3833:
3834: l_grouping_rows wsh_util_core.id_tab_type;
3835: l_del_legs wsh_util_core.id_tab_type;
3836: l_org_rows wsh_util_core.id_tab_type;
3837:
3838: l_return_status VARCHAR2(1);
3831: x_del_rows OUT NOCOPY wsh_util_core.id_tab_type,
3832: x_return_status OUT NOCOPY VARCHAR2) IS
3833:
3834: l_grouping_rows wsh_util_core.id_tab_type;
3835: l_del_legs wsh_util_core.id_tab_type;
3836: l_org_rows wsh_util_core.id_tab_type;
3837:
3838: l_return_status VARCHAR2(1);
3839: reprice_required_err EXCEPTION;
3832: x_return_status OUT NOCOPY VARCHAR2) IS
3833:
3834: l_grouping_rows wsh_util_core.id_tab_type;
3835: l_del_legs wsh_util_core.id_tab_type;
3836: l_org_rows wsh_util_core.id_tab_type;
3837:
3838: l_return_status VARCHAR2(1);
3839: reprice_required_err EXCEPTION;
3840:
3877: x_del_rows => x_del_rows,
3878: x_grouping_rows => l_grouping_rows,
3879: x_return_status => x_return_status);
3880:
3881: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR
3882: (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3883: --
3884: -- Debug Statements
3885: --
3878: x_grouping_rows => l_grouping_rows,
3879: x_return_status => x_return_status);
3880:
3881: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR
3882: (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3883: --
3884: -- Debug Statements
3885: --
3886: IF l_debug_on THEN
3904: x_leg_rows => l_del_legs,
3905: x_return_status => x_return_status
3906: );
3907:
3908: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR
3909: (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3910: --
3911: -- Debug Statements
3912: --
3905: x_return_status => x_return_status
3906: );
3907:
3908: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR
3909: (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3910: --
3911: -- Debug Statements
3912: --
3913: IF l_debug_on THEN
3930: EXCEPTION
3931: WHEN reprice_required_err THEN
3932: x_return_status := l_return_status;
3933: fnd_message.set_name('WSH', 'WSH_REPRICE_REQUIRED_ERR');
3934: wsh_util_core.add_message(x_return_status);
3935: --
3936: -- Debug Statements
3937: --
3938: IF l_debug_on THEN
3940: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:REPRICE_REQUIRED_ERR');
3941: END IF;
3942: --
3943: WHEN others THEN
3944: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.ASSIGN_TRIP');
3945: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3946:
3947: --
3948: -- Debug Statements
3941: END IF;
3942: --
3943: WHEN others THEN
3944: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.ASSIGN_TRIP');
3945: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3946:
3947: --
3948: -- Debug Statements
3949: --
3955: END assign_trip;
3956:
3957:
3958: PROCEDURE check_assign_trip (
3959: p_del_rows IN wsh_util_core.id_tab_type,
3960: p_trip_id IN NUMBER,
3961: p_pickup_stop_id IN NUMBER := NULL,
3962: p_dropoff_stop_id IN NUMBER := NULL,
3963: p_pickup_location_id IN NUMBER := NULL,
4030: WSH_DEBUG_SV.log(l_module_name,'P_DROPOFF_ARR_DATE',P_DROPOFF_ARR_DATE);
4031: WSH_DEBUG_SV.log(l_module_name,'P_DROPOFF_DEP_DATE',P_DROPOFF_DEP_DATE);
4032: END IF;
4033: --
4034: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4035:
4036: IF (p_pickup_location_id = p_dropoff_location_id) THEN
4037: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_LOCATIONS_SAME');
4038: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4034: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4035:
4036: IF (p_pickup_location_id = p_dropoff_location_id) THEN
4037: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_LOCATIONS_SAME');
4038: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4039: wsh_util_core.add_message(x_return_status);
4040: --
4041: -- Debug Statements
4042: --
4035:
4036: IF (p_pickup_location_id = p_dropoff_location_id) THEN
4037: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_LOCATIONS_SAME');
4038: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4039: wsh_util_core.add_message(x_return_status);
4040: --
4041: -- Debug Statements
4042: --
4043: IF l_debug_on THEN
4071: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
4072: END IF;
4073: --
4074: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
4075: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4076: wsh_util_core.add_message(x_return_status);
4077: warn_flag := TRUE;
4078:
4079: --
4072: END IF;
4073: --
4074: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
4075: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4076: wsh_util_core.add_message(x_return_status);
4077: warn_flag := TRUE;
4078:
4079: --
4080: -- Debug Statements
4104: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
4105: END IF;
4106: --
4107: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
4108: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4109: wsh_util_core.add_message(x_return_status);
4110: warn_flag := TRUE;
4111:
4112: --
4105: END IF;
4106: --
4107: FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_del_rows(i)));
4108: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4109: wsh_util_core.add_message(x_return_status);
4110: warn_flag := TRUE;
4111:
4112: --
4113: -- Debug Statements
4135: p_pickup_stop_id => p_pickup_stop_id,
4136: p_dropoff_stop_id => p_dropoff_stop_id,
4137: x_return_status => x_return_status);
4138:
4139: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
4140: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
4141: num_warn := num_warn + 1;
4142: ELSE
4143: --
4136: p_dropoff_stop_id => p_dropoff_stop_id,
4137: x_return_status => x_return_status);
4138:
4139: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
4140: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
4141: num_warn := num_warn + 1;
4142: ELSE
4143: --
4144: -- Debug Statements
4159:
4160: END LOOP;
4161:
4162: IF (num_warn > 0) OR (num_error > 0) THEN
4163: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4164: END IF;
4165:
4166: --
4167: -- Debug Statements
4171: END IF;
4172: --
4173: EXCEPTION
4174: WHEN others THEN
4175: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.CHECK_ASSIGN_TRIP');
4176: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4177:
4178: --
4179: -- Debug Statements
4172: --
4173: EXCEPTION
4174: WHEN others THEN
4175: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.CHECK_ASSIGN_TRIP');
4176: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4177:
4178: --
4179: -- Debug Statements
4180: --
4222: -- other delivery getting dropoff or pickedup at the stop's location, convert the location for
4223: -- this stop to be the dummy location . Else create new dropoff stop with dummy loc
4224:
4225: PROCEDURE process_dels_for_internal_locs(
4226: p_del_rows IN wsh_util_core.id_tab_type,
4227: p_trip_id IN NUMBER,
4228: p_pickup_stop_id IN NUMBER := NULL,
4229: p_dropoff_stop_id IN NUMBER := NULL,
4230: p_pickup_location_id IN NUMBER := NULL,
4235: x_dropoff_stop_id OUT NOCOPY NUMBER,
4236: x_pickup_location_id OUT NOCOPY NUMBER,
4237: x_dropoff_location_id OUT NOCOPY NUMBER,
4238: x_dropoff_seq_num IN OUT NOCOPY NUMBER,
4239: x_internal_del_ids OUT NOCOPY wsh_util_core.id_tab_type,
4240: x_del_ids OUT NOCOPY wsh_util_core.id_tab_type
4241: )
4242: IS
4243: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'process_dels_for_internal_locs';
4236: x_pickup_location_id OUT NOCOPY NUMBER,
4237: x_dropoff_location_id OUT NOCOPY NUMBER,
4238: x_dropoff_seq_num IN OUT NOCOPY NUMBER,
4239: x_internal_del_ids OUT NOCOPY wsh_util_core.id_tab_type,
4240: x_del_ids OUT NOCOPY wsh_util_core.id_tab_type
4241: )
4242: IS
4243: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'process_dels_for_internal_locs';
4244: --
4277: l_trip_pickup_loc_id NUMBER;
4278: l_trip_dropoff_loc_id NUMBER;
4279: l_physical_stop_id NUMBER;
4280: l_find_stop NUMBER;
4281: l_trip_ids wsh_util_core.id_tab_type;
4282: l_dummy_trip_ids wsh_util_core.id_tab_type;
4283: l_getstops_stop_id wsh_util_core.id_tab_type;
4284: l_getstops_stop_loc_id wsh_util_core.id_tab_type;
4285: l_getstops_status_code WSH_UTIL_CORE.Column_Tab_Type;
4278: l_trip_dropoff_loc_id NUMBER;
4279: l_physical_stop_id NUMBER;
4280: l_find_stop NUMBER;
4281: l_trip_ids wsh_util_core.id_tab_type;
4282: l_dummy_trip_ids wsh_util_core.id_tab_type;
4283: l_getstops_stop_id wsh_util_core.id_tab_type;
4284: l_getstops_stop_loc_id wsh_util_core.id_tab_type;
4285: l_getstops_status_code WSH_UTIL_CORE.Column_Tab_Type;
4286: l_is_stop_pickup VARCHAR2(1):='N';
4279: l_physical_stop_id NUMBER;
4280: l_find_stop NUMBER;
4281: l_trip_ids wsh_util_core.id_tab_type;
4282: l_dummy_trip_ids wsh_util_core.id_tab_type;
4283: l_getstops_stop_id wsh_util_core.id_tab_type;
4284: l_getstops_stop_loc_id wsh_util_core.id_tab_type;
4285: l_getstops_status_code WSH_UTIL_CORE.Column_Tab_Type;
4286: l_is_stop_pickup VARCHAR2(1):='N';
4287: l_is_stop_dropoff VARCHAR2(1):='N';
4280: l_find_stop NUMBER;
4281: l_trip_ids wsh_util_core.id_tab_type;
4282: l_dummy_trip_ids wsh_util_core.id_tab_type;
4283: l_getstops_stop_id wsh_util_core.id_tab_type;
4284: l_getstops_stop_loc_id wsh_util_core.id_tab_type;
4285: l_getstops_status_code WSH_UTIL_CORE.Column_Tab_Type;
4286: l_is_stop_pickup VARCHAR2(1):='N';
4287: l_is_stop_dropoff VARCHAR2(1):='N';
4288: l_find_leg VARCHAR2(1);
4281: l_trip_ids wsh_util_core.id_tab_type;
4282: l_dummy_trip_ids wsh_util_core.id_tab_type;
4283: l_getstops_stop_id wsh_util_core.id_tab_type;
4284: l_getstops_stop_loc_id wsh_util_core.id_tab_type;
4285: l_getstops_status_code WSH_UTIL_CORE.Column_Tab_Type;
4286: l_is_stop_pickup VARCHAR2(1):='N';
4287: l_is_stop_dropoff VARCHAR2(1):='N';
4288: l_find_leg VARCHAR2(1);
4289: b_physical_loc_updated BOOLEAN:=FALSE;
4301: l_num_warn NUMBER := 0;
4302: bad_trip_stop EXCEPTION;
4303: l_internal_del_id_count NUMBER := 0;
4304: l_del_id_count NUMBER := 0;
4305: l_internal_del_ids wsh_util_core.id_tab_type;
4306: l_del_ids wsh_util_core.id_tab_type;
4307: b_checkstopupdate BOOLEAN:=TRUE;
4308: b_checkdels BOOLEAN:=FALSE;
4309: b_gotstops BOOLEAN:=FALSE;
4302: bad_trip_stop EXCEPTION;
4303: l_internal_del_id_count NUMBER := 0;
4304: l_del_id_count NUMBER := 0;
4305: l_internal_del_ids wsh_util_core.id_tab_type;
4306: l_del_ids wsh_util_core.id_tab_type;
4307: b_checkstopupdate BOOLEAN:=TRUE;
4308: b_checkdels BOOLEAN:=FALSE;
4309: b_gotstops BOOLEAN:=FALSE;
4310: l_del_phys_dropoff VARCHAR2(1);
4372: WSH_DEBUG_SV.log(l_module_name, 'p_dropoff_location_id', p_dropoff_location_id);
4373: END IF;
4374: --
4375: --
4376: x_return_status :=WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4377: x_pickup_stop_id :=p_pickup_stop_id;
4378: x_dropoff_stop_id :=p_dropoff_stop_id;
4379: x_pickup_location_id :=p_pickup_location_id;
4380: x_dropoff_location_id :=p_dropoff_location_id;
4410: WSH_LOCATIONS_PKG.Convert_internal_cust_location(
4411: p_internal_cust_location_id => l_trip_pickup_loc_id,
4412: x_internal_org_location_id => l_phys_trip_pickup_loc_id,
4413: x_return_status => l_return_status);
4414: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
4415: x_return_status:=l_return_status;
4416: RETURN;
4417: END IF;
4418: IF l_debug_on THEN
4509: x_pickup_location_id:=l_phys_trip_pickup_loc_id;
4510: END IF;
4511:
4512: ELSE--caller is WSH
4513: x_return_status:=WSH_UTIL_CORE.G_RET_STS_ERROR;
4514: FND_MESSAGE.SET_NAME('WSH','WSH_CANNOT_USE_DUMMY_PICKUP');
4515: IF l_debug_on THEN
4516: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.get_location_description',WSH_DEBUG_SV.C_PROC_LEVEL);
4517: END IF;
4512: ELSE--caller is WSH
4513: x_return_status:=WSH_UTIL_CORE.G_RET_STS_ERROR;
4514: FND_MESSAGE.SET_NAME('WSH','WSH_CANNOT_USE_DUMMY_PICKUP');
4515: IF l_debug_on THEN
4516: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.get_location_description',WSH_DEBUG_SV.C_PROC_LEVEL);
4517: END IF;
4518: FND_MESSAGE.SET_TOKEN('STOP_NAME',SUBSTR(WSH_UTIL_CORE.get_location_description(l_phys_trip_pickup_loc_id, 'NEW UI CODE'),1,60));
4519: wsh_util_core.add_message(x_return_status,l_module_name);
4520: IF l_debug_on THEN
4514: FND_MESSAGE.SET_NAME('WSH','WSH_CANNOT_USE_DUMMY_PICKUP');
4515: IF l_debug_on THEN
4516: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.get_location_description',WSH_DEBUG_SV.C_PROC_LEVEL);
4517: END IF;
4518: FND_MESSAGE.SET_TOKEN('STOP_NAME',SUBSTR(WSH_UTIL_CORE.get_location_description(l_phys_trip_pickup_loc_id, 'NEW UI CODE'),1,60));
4519: wsh_util_core.add_message(x_return_status,l_module_name);
4520: IF l_debug_on THEN
4521: WSH_DEBUG_SV.pop(l_module_name);
4522: END IF;
4515: IF l_debug_on THEN
4516: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.get_location_description',WSH_DEBUG_SV.C_PROC_LEVEL);
4517: END IF;
4518: FND_MESSAGE.SET_TOKEN('STOP_NAME',SUBSTR(WSH_UTIL_CORE.get_location_description(l_phys_trip_pickup_loc_id, 'NEW UI CODE'),1,60));
4519: wsh_util_core.add_message(x_return_status,l_module_name);
4520: IF l_debug_on THEN
4521: WSH_DEBUG_SV.pop(l_module_name);
4522: END IF;
4523: RETURN;
4556: p_internal_cust_location_id => l_dropoff_location_id,
4557: x_internal_org_location_id => l_phys_del_dropoff_loc_id,
4558: x_return_status => l_return_status);
4559:
4560: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
4561: x_return_status:=l_return_status;
4562: RETURN;
4563: END IF;
4564:
4727: IF get_stop_location%isopen THEN
4728: close get_stop_location;
4729: END IF;
4730: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
4731: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4732: wsh_util_core.add_message(x_return_status);
4733: --
4734: -- Debug Statements
4735: --
4728: close get_stop_location;
4729: END IF;
4730: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
4731: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4732: wsh_util_core.add_message(x_return_status);
4733: --
4734: -- Debug Statements
4735: --
4736: IF l_debug_on THEN
4738: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:BAD_TRIP_STOP');
4739: END IF;
4740: --
4741: WHEN OTHERS THEN
4742: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4743: WSH_UTIL_CORE.DEFAULT_HANDLER(
4744: 'WSH_TRIPS_ACTIONS.process_dels_for_internal_locs',
4745: l_module_name);
4746:
4739: END IF;
4740: --
4741: WHEN OTHERS THEN
4742: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4743: WSH_UTIL_CORE.DEFAULT_HANDLER(
4744: 'WSH_TRIPS_ACTIONS.process_dels_for_internal_locs',
4745: l_module_name);
4746:
4747: IF l_debug_on THEN
4751:
4752: END process_dels_for_internal_locs;
4753:
4754: PROCEDURE assign_trip(
4755: p_del_rows IN wsh_util_core.id_tab_type,
4756: p_trip_id IN NUMBER,
4757: p_pickup_stop_id IN NUMBER := NULL,
4758: p_pickup_stop_seq IN NUMBER := NULL,
4759: p_dropoff_stop_id IN NUMBER := NULL,
4783: WHERE st.stop_location_id = l_loc_id AND
4784: dg.drop_off_stop_id = st.stop_id AND
4785: dg.delivery_id = l_del_id;
4786:
4787: l_del_legs wsh_util_core.id_tab_type;
4788: l_stop_id NUMBER;
4789: l_return_status VARCHAR2(1);
4790:
4791: stop_not_found EXCEPTION;
4806: --
4807: -- patchset J csun Stop Sequence Change
4808: l_stop_details_rec WSH_TRIP_STOPS_VALIDATIONS.stop_details;
4809:
4810: l_del_rows wsh_util_core.id_tab_type;
4811: l_pickup_stop_id NUMBER;
4812: l_dropoff_stop_id NUMBER;
4813: l_pickup_location_id NUMBER;
4814: l_dropoff_location_id NUMBER;
4813: l_pickup_location_id NUMBER;
4814: l_dropoff_location_id NUMBER;
4815: l_pickup_stop_seq NUMBER;
4816: l_dropoff_stop_seq NUMBER;
4817: l_trip_ids wsh_util_core.id_tab_type;
4818: l_dummy_trip_ids wsh_util_core.id_tab_type;
4819: l_del_ids wsh_util_core.id_tab_type;
4820: l_internal_del_ids wsh_util_core.id_tab_type;
4821:
4814: l_dropoff_location_id NUMBER;
4815: l_pickup_stop_seq NUMBER;
4816: l_dropoff_stop_seq NUMBER;
4817: l_trip_ids wsh_util_core.id_tab_type;
4818: l_dummy_trip_ids wsh_util_core.id_tab_type;
4819: l_del_ids wsh_util_core.id_tab_type;
4820: l_internal_del_ids wsh_util_core.id_tab_type;
4821:
4822: l_stop_seq_mode NUMBER; --SSN
4815: l_pickup_stop_seq NUMBER;
4816: l_dropoff_stop_seq NUMBER;
4817: l_trip_ids wsh_util_core.id_tab_type;
4818: l_dummy_trip_ids wsh_util_core.id_tab_type;
4819: l_del_ids wsh_util_core.id_tab_type;
4820: l_internal_del_ids wsh_util_core.id_tab_type;
4821:
4822: l_stop_seq_mode NUMBER; --SSN
4823:
4816: l_dropoff_stop_seq NUMBER;
4817: l_trip_ids wsh_util_core.id_tab_type;
4818: l_dummy_trip_ids wsh_util_core.id_tab_type;
4819: l_del_ids wsh_util_core.id_tab_type;
4820: l_internal_del_ids wsh_util_core.id_tab_type;
4821:
4822: l_stop_seq_mode NUMBER; --SSN
4823:
4824: --WF: CMR
4821:
4822: l_stop_seq_mode NUMBER; --SSN
4823:
4824: --WF: CMR
4825: l_del_old_carrier_ids WSH_UTIL_CORE.ID_TAB_TYPE;
4826: l_wf_rs VARCHAR2(1);
4827:
4828: BEGIN
4829: /* The validations for stop sequence number are already done before calling this */
4862: IF (p_del_rows.count = 0) THEN
4863: raise others;
4864: END IF;
4865:
4866: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4867:
4868: -- Pack J csun Stop Sequence Change
4869: -- This is the change to sequence trip stops by
4870: -- planned arrival date rather than stop sequence number.
4887: )THEN
4888: FND_MESSAGE.SET_NAME('WSH', 'WSH_INVALID_PLANNED_DATE');
4889: FND_MESSAGE.SET_TOKEN('PICKUP_DATE', fnd_date.date_to_displaydt(p_pickup_dep_date,calendar_aware=>FND_DATE.calendar_aware_alt));
4890: FND_MESSAGE.SET_TOKEN('DROPOFF_DATE', fnd_date.date_to_displaydt(p_dropoff_arr_date,calendar_aware=>FND_DATE.calendar_aware_alt));
4891: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4892: wsh_util_core.add_message(x_return_status);
4893: --
4894: -- Debug Statements
4895: --
4888: FND_MESSAGE.SET_NAME('WSH', 'WSH_INVALID_PLANNED_DATE');
4889: FND_MESSAGE.SET_TOKEN('PICKUP_DATE', fnd_date.date_to_displaydt(p_pickup_dep_date,calendar_aware=>FND_DATE.calendar_aware_alt));
4890: FND_MESSAGE.SET_TOKEN('DROPOFF_DATE', fnd_date.date_to_displaydt(p_dropoff_arr_date,calendar_aware=>FND_DATE.calendar_aware_alt));
4891: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4892: wsh_util_core.add_message(x_return_status);
4893: --
4894: -- Debug Statements
4895: --
4896: IF l_debug_on THEN
4917: p_dropoff_arr_date => p_dropoff_arr_date,
4918: p_dropoff_dep_date => p_dropoff_dep_date,
4919: x_return_status => x_return_status);
4920:
4921: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
4922: --
4923: -- Debug Statements
4924: --
4925: IF l_debug_on THEN
4961: x_dropoff_seq_num => l_dropoff_stop_seq,
4962: x_del_ids => l_del_ids,
4963: x_internal_del_ids => l_internal_del_ids);
4964:
4965: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
4966: IF l_debug_on THEN
4967: WSH_DEBUG_SV.log(l_module_name,'process_dels_for_internal_locs return_status',x_return_status);
4968: WSH_DEBUG_SV.pop(l_module_name);
4969: END IF;
5005: IF l_debug_on THEN
5006: WSH_DEBUG_SV.log(l_module_name,'wsh_delivery_legs_actions.assign_deliveries return_status',l_return_status);
5007: END IF;
5008:
5009: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
5010: x_return_status := l_return_status;
5011: IF l_debug_on THEN
5012: WSH_DEBUG_SV.pop(l_module_name);
5013: END IF;
5011: IF l_debug_on THEN
5012: WSH_DEBUG_SV.pop(l_module_name);
5013: END IF;
5014: RETURN;
5015: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
5016: x_return_status := l_return_status;
5017: END IF;
5018: END IF;
5019:
5043: IF l_debug_on THEN
5044: WSH_DEBUG_SV.log(l_module_name,'wsh_delivery_legs_actions.assign_deliveries return_status',l_return_status);
5045: END IF;
5046:
5047: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
5048: x_return_status := l_return_status;
5049: IF l_debug_on THEN
5050: WSH_DEBUG_SV.pop(l_module_name);
5051: END IF;
5049: IF l_debug_on THEN
5050: WSH_DEBUG_SV.pop(l_module_name);
5051: END IF;
5052: RETURN;
5053: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
5054: x_return_status := l_return_status;
5055: END IF;
5056: END IF;
5057:
5072: IF l_debug_on THEN
5073: WSH_DEBUG_SV.log(l_module_name,'return status from WSH_TRIP_STOPS_ACTIONS.RESET_STOP_SEQ_NUMBERS',l_return_status);
5074: END IF;
5075:
5076: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
5077: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
5078: l_num_warn := l_num_warn + 1;
5079: ELSE
5080: x_return_status := l_return_status;
5073: WSH_DEBUG_SV.log(l_module_name,'return status from WSH_TRIP_STOPS_ACTIONS.RESET_STOP_SEQ_NUMBERS',l_return_status);
5074: END IF;
5075:
5076: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
5077: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
5078: l_num_warn := l_num_warn + 1;
5079: ELSE
5080: x_return_status := l_return_status;
5081: --
5102: IF l_debug_on THEN
5103: 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);
5104: END IF;
5105:
5106: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
5107: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
5108: l_num_warn := l_num_warn + 1;
5109: ELSE
5110: x_return_status := l_return_status;
5103: 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);
5104: END IF;
5105:
5106: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
5107: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
5108: l_num_warn := l_num_warn + 1;
5109: ELSE
5110: x_return_status := l_return_status;
5111: --
5128: p_del_ids => p_del_rows,
5129: p_del_old_carrier_ids => l_del_old_carrier_ids,
5130: x_return_status => l_wf_rs);
5131: */
5132: IF (l_num_warn > 0 AND x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
5133: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5134: END IF;
5135:
5136: --
5129: p_del_old_carrier_ids => l_del_old_carrier_ids,
5130: x_return_status => l_wf_rs);
5131: */
5132: IF (l_num_warn > 0 AND x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
5133: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5134: END IF;
5135:
5136: --
5137: -- Debug Statements
5142: --
5143: EXCEPTION
5144: WHEN stop_not_found THEN
5145: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
5146: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5147: wsh_util_core.add_message(x_return_status);
5148: --
5149: -- Debug Statements
5150: --
5143: EXCEPTION
5144: WHEN stop_not_found THEN
5145: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
5146: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5147: wsh_util_core.add_message(x_return_status);
5148: --
5149: -- Debug Statements
5150: --
5151: IF l_debug_on THEN
5153: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:STOP_NOT_FOUND');
5154: END IF;
5155: --
5156: WHEN others THEN
5157: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.ASSIGN_TRIP');
5158: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5159:
5160: --
5161: -- Debug Statements
5154: END IF;
5155: --
5156: WHEN others THEN
5157: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.ASSIGN_TRIP');
5158: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5159:
5160: --
5161: -- Debug Statements
5162: --
5263: WSH_DEBUG_SV.log(l_module_name,'P_GROSS_WEIGHT',P_GROSS_WEIGHT);
5264: WSH_DEBUG_SV.log(l_module_name,'P_VOLUME',P_VOLUME);
5265: END IF;
5266: --
5267: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5268:
5269: -- Return if stop is null or if W/V is null
5270: IF p_stop_id is NULL OR (p_gross_weight is NULL AND p_volume is NULL) THEN
5271: IF l_debug_on THEN
5304: l_trip_org_id;
5305: IF (trip_info%NOTFOUND) THEN
5306: CLOSE trip_info;
5307: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NOT_FOUND');
5308: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5309: wsh_util_core.add_message(x_return_status);
5310: END IF;
5311: CLOSE trip_info;
5312: END IF;
5305: IF (trip_info%NOTFOUND) THEN
5306: CLOSE trip_info;
5307: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NOT_FOUND');
5308: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5309: wsh_util_core.add_message(x_return_status);
5310: END IF;
5311: CLOSE trip_info;
5312: END IF;
5313:
5376: END IF;
5377: IF org_info%ISOPEN THEN
5378: CLOSE org_info;
5379: END IF;
5380: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.CALC_STOP_FILL_PERCENT');
5381: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5382:
5383: IF l_debug_on THEN
5384: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5377: IF org_info%ISOPEN THEN
5378: CLOSE org_info;
5379: END IF;
5380: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.CALC_STOP_FILL_PERCENT');
5381: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5382:
5383: IF l_debug_on THEN
5384: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5385: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5386: END IF;
5387:
5388: END calc_stop_fill_percent;
5389:
5390: PROCEDURE trip_weight_volume( p_trip_rows IN wsh_util_core.id_tab_type,
5391: p_override_flag IN VARCHAR2,
5392: p_calc_wv_if_frozen IN VARCHAR2,
5393: p_start_departure_date IN DATE,
5394: p_calc_del_wv IN VARCHAR2,
5587: WSH_DEBUG_SV.log(l_module_name,'P_CALC_DEL_WV',P_CALC_DEL_WV);
5588: WSH_DEBUG_SV.log(l_module_name,'P_SUPPRESS_ERRORS',P_SUPPRESS_ERRORS);
5589: END IF;
5590: --
5591: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5592: IF (p_trip_rows.count = 0) THEN
5593: raise others;
5594: END IF;
5595: FOR i IN 1..p_trip_rows.count LOOP
5611: 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;
5612: IF (trip_info%NOTFOUND) THEN
5613: CLOSE trip_info;
5614: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NOT_FOUND');
5615: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5616: wsh_util_core.add_message(x_return_status);
5617: goto wt_vol_error;
5618: END IF;
5619: CLOSE trip_info;
5612: IF (trip_info%NOTFOUND) THEN
5613: CLOSE trip_info;
5614: FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NOT_FOUND');
5615: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5616: wsh_util_core.add_message(x_return_status);
5617: goto wt_vol_error;
5618: END IF;
5619: CLOSE trip_info;
5620: END IF;
5632: --
5633: -- Debug Statements
5634: --
5635: IF l_debug_on THEN
5636: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_ORG_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5637: END IF;
5638: --
5639: FND_MESSAGE.SET_TOKEN('l_vehicle_org', WSH_UTIL_CORE.Get_Org_Name(l_vehicle_org_id));
5640: --End of Bug 2415809
5635: IF l_debug_on THEN
5636: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_ORG_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5637: END IF;
5638: --
5639: FND_MESSAGE.SET_TOKEN('l_vehicle_org', WSH_UTIL_CORE.Get_Org_Name(l_vehicle_org_id));
5640: --End of Bug 2415809
5641: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5642: wsh_util_core.add_message(x_return_status);
5643: l_trip_num_warn:=l_trip_num_warn+1;
5637: END IF;
5638: --
5639: FND_MESSAGE.SET_TOKEN('l_vehicle_org', WSH_UTIL_CORE.Get_Org_Name(l_vehicle_org_id));
5640: --End of Bug 2415809
5641: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5642: wsh_util_core.add_message(x_return_status);
5643: l_trip_num_warn:=l_trip_num_warn+1;
5644: l_fill_basis := 'W';
5645: END IF;
5638: --
5639: FND_MESSAGE.SET_TOKEN('l_vehicle_org', WSH_UTIL_CORE.Get_Org_Name(l_vehicle_org_id));
5640: --End of Bug 2415809
5641: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5642: wsh_util_core.add_message(x_return_status);
5643: l_trip_num_warn:=l_trip_num_warn+1;
5644: l_fill_basis := 'W';
5645: END IF;
5646:
5665: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5666: END IF;
5667: --
5668: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
5669: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5670: wsh_util_core.add_message(x_return_status);
5671: goto wt_vol_error;
5672: END IF;
5673:
5666: END IF;
5667: --
5668: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
5669: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5670: wsh_util_core.add_message(x_return_status);
5671: goto wt_vol_error;
5672: END IF;
5673:
5674: OPEN last_stop(p_trip_rows(i));
5689: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5690: END IF;
5691: --
5692: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
5693: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5694: wsh_util_core.add_message(x_return_status);
5695: goto wt_vol_error;
5696: END IF;
5697: CLOSE last_stop;
5690: END IF;
5691: --
5692: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
5693: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5694: wsh_util_core.add_message(x_return_status);
5695: goto wt_vol_error;
5696: END IF;
5697: CLOSE last_stop;
5698:
5713: END IF;
5714: -- end bug 2366163
5715: -- bug 2732503 , need a message only when FTE is installed
5716: -- else continue
5717: IF (wsh_util_core.fte_is_installed <> 'Y') THEN
5718: goto loop_end;
5719: END IF;
5720:
5721: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_UOM_NULL');
5728: --
5729: FND_MESSAGE.SET_TOKEN('stop_name',wsh_trip_stops_pvt.get_name(l_start_stop_id,p_caller));
5730: -- bug 2732503 , need a warning message only when FTE is installed
5731: -- still will go to wt_vol_error
5732: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5733: wsh_util_core.add_message(x_return_status);
5734: l_num_warn := l_num_warn + 1;
5735: goto loop_end;
5736: END IF;
5729: FND_MESSAGE.SET_TOKEN('stop_name',wsh_trip_stops_pvt.get_name(l_start_stop_id,p_caller));
5730: -- bug 2732503 , need a warning message only when FTE is installed
5731: -- still will go to wt_vol_error
5732: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5733: wsh_util_core.add_message(x_return_status);
5734: l_num_warn := l_num_warn + 1;
5735: goto loop_end;
5736: END IF;
5737: END IF;
5782: -- end bug 2366163
5783: -- bug 2732503 , need a message only when FTE is installed
5784: -- this will skip calculating wt/vol for all the stops
5785: -- else continue
5786: IF (wsh_util_core.fte_is_installed <> 'Y') THEN
5787: goto loop_end;
5788: END IF;
5789:
5790: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_UOM_NULL');
5797: --
5798: FND_MESSAGE.SET_TOKEN('stop_name',wsh_trip_stops_pvt.get_name(l_stop_id,p_caller));
5799: -- bug 2732503 , need a warning message only when FTE is installed
5800: -- still will go to wt_vol_error
5801: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5802: wsh_util_core.add_message(x_return_status);
5803: l_stop_num_warn := l_stop_num_warn + 1;
5804: goto continue_next;
5805: END IF;
5798: FND_MESSAGE.SET_TOKEN('stop_name',wsh_trip_stops_pvt.get_name(l_stop_id,p_caller));
5799: -- bug 2732503 , need a warning message only when FTE is installed
5800: -- still will go to wt_vol_error
5801: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5802: wsh_util_core.add_message(x_return_status);
5803: l_stop_num_warn := l_stop_num_warn + 1;
5804: goto continue_next;
5805: END IF;
5806:
5833: END IF;
5834: --
5835: l_volume := wsh_wv_utils.convert_uom(l_prev_vol_uom, l_stop_vol_uom, l_total_volume);
5836:
5837: g_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5838:
5839: IF l_debug_on THEN
5840: WSH_DEBUG_SV.logmsg(l_module_name,'Processing Pickup Deliveries ...');
5841: END IF;
5858: x_net_weight => l_net_weight,
5859: x_volume => l_volume,
5860: x_return_status => l_return_status);
5861:
5862: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
5863: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
5864: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_WT_VOL_FAILED');
5865: --
5866: -- Debug Statements
5859: x_volume => l_volume,
5860: x_return_status => l_return_status);
5861:
5862: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
5863: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
5864: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_WT_VOL_FAILED');
5865: --
5866: -- Debug Statements
5867: --
5869: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
5870: END IF;
5871: --
5872: FND_MESSAGE.SET_TOKEN('del_name',wsh_new_deliveries_pvt.get_name(pkup_dl.d_id));
5873: g_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5874: wsh_util_core.add_message(g_return_status);
5875: END IF;
5876:
5877:
5870: END IF;
5871: --
5872: FND_MESSAGE.SET_TOKEN('del_name',wsh_new_deliveries_pvt.get_name(pkup_dl.d_id));
5873: g_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5874: wsh_util_core.add_message(g_return_status);
5875: END IF;
5876:
5877:
5878: ELSE
5882: l_volume := pkup_dl.volume;
5883:
5884: END IF; --}
5885:
5886: --Bug 9308056 replaced pkup_dl.d_id with l_pkup_dl_id_mod , WSH_UTIL_CORE.C_INDEX_LIMIT value is 2147483648; -- power(2,31)
5887: l_pkup_dl_id_mod := MOD(pkup_dl.d_id,WSH_UTIL_CORE.C_INDEX_LIMIT) ;
5888:
5889: pickup_del_tab(l_pkup_dl_id_mod).d_id := pkup_dl.d_id;
5890: pickup_del_tab(l_pkup_dl_id_mod).wt_uom := pkup_dl.wt_uom;
5883:
5884: END IF; --}
5885:
5886: --Bug 9308056 replaced pkup_dl.d_id with l_pkup_dl_id_mod , WSH_UTIL_CORE.C_INDEX_LIMIT value is 2147483648; -- power(2,31)
5887: l_pkup_dl_id_mod := MOD(pkup_dl.d_id,WSH_UTIL_CORE.C_INDEX_LIMIT) ;
5888:
5889: pickup_del_tab(l_pkup_dl_id_mod).d_id := pkup_dl.d_id;
5890: pickup_del_tab(l_pkup_dl_id_mod).wt_uom := pkup_dl.wt_uom;
5891: pickup_del_tab(l_pkup_dl_id_mod).vol_uom := pkup_dl.vol_uom;
5964:
5965:
5966: END LOOP;
5967:
5968: IF (g_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
5969: x_return_status := g_return_status;
5970: l_stop_num_warn := l_stop_num_warn + 1;
5971: END IF;
5972:
5976: END IF;
5977:
5978:
5979: FOR dpoff_dl IN dropoff_deliveries (l_stop_id) LOOP
5980: --Bug 9308056 replaced dpoff_dl.d_id with l_dpoff_dl_id_mod , WSH_UTIL_CORE.C_INDEX_LIMIT value is 2147483648; -- power(2,31)
5981: l_dpoff_dl_id_mod := MOD(dpoff_dl.d_id,WSH_UTIL_CORE.C_INDEX_LIMIT) ;
5982: IF pickup_del_tab.EXISTS(l_dpoff_dl_id_mod) THEN
5983: IF pickup_del_tab(l_dpoff_dl_id_mod).d_id = dpoff_dl.d_id THEN
5984: IF l_stop_id <> l_last_stop_id THEN
5977:
5978:
5979: FOR dpoff_dl IN dropoff_deliveries (l_stop_id) LOOP
5980: --Bug 9308056 replaced dpoff_dl.d_id with l_dpoff_dl_id_mod , WSH_UTIL_CORE.C_INDEX_LIMIT value is 2147483648; -- power(2,31)
5981: l_dpoff_dl_id_mod := MOD(dpoff_dl.d_id,WSH_UTIL_CORE.C_INDEX_LIMIT) ;
5982: IF pickup_del_tab.EXISTS(l_dpoff_dl_id_mod) THEN
5983: IF pickup_del_tab(l_dpoff_dl_id_mod).d_id = dpoff_dl.d_id THEN
5984: IF l_stop_id <> l_last_stop_id THEN
5985: -- J: W/V Changes
6090: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6091: END IF;
6092: --
6093: FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(l_stop_id,p_caller));
6094: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6095: wsh_util_core.add_message(x_return_status);
6096: l_stop_num_warn := l_stop_num_warn + 1;
6097: END IF;
6098: END IF;
6091: END IF;
6092: --
6093: FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(l_stop_id,p_caller));
6094: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6095: wsh_util_core.add_message(x_return_status);
6096: l_stop_num_warn := l_stop_num_warn + 1;
6097: END IF;
6098: END IF;
6099:
6118: END IF;
6119: END IF;
6120:
6121: /* H integration - J: W/V Changes */
6122: IF WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y' AND p_override_flag = 'Y' THEN
6123: IF l_debug_on THEN
6124: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Fte_Load_Tender',WSH_DEBUG_SV.C_PROC_LEVEL);
6125: END IF;
6126: Fte_Load_Tender(
6130: p_volume => l_total_volume,
6131: p_fill_percent => l_stop_fill_percent,
6132: x_return_status => l_return_status);
6133:
6134: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
6135: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
6136: l_stop_num_warn := l_stop_num_warn + 1;
6137: ELSE
6138: x_return_status := l_return_status;
6131: p_fill_percent => l_stop_fill_percent,
6132: x_return_status => l_return_status);
6133:
6134: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
6135: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
6136: l_stop_num_warn := l_stop_num_warn + 1;
6137: ELSE
6138: x_return_status := l_return_status;
6139: IF l_debug_on THEN
6171: WHERE stop_id = l_stop_id;
6172:
6173: IF (SQL%NOTFOUND) THEN
6174: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
6175: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6176: wsh_util_core.add_message(x_return_status);
6177: goto wt_vol_error;
6178: END IF;
6179:
6172:
6173: IF (SQL%NOTFOUND) THEN
6174: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
6175: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6176: wsh_util_core.add_message(x_return_status);
6177: goto wt_vol_error;
6178: END IF;
6179:
6180: --R12 MDC
6188: wv_frozen_flag = 'N'
6189: WHERE stop_id = l_stop_id;
6190: IF (SQL%NOTFOUND) THEN
6191: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
6192: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6193: wsh_util_core.add_message(x_return_status);
6194: goto wt_vol_error;
6195: END IF;
6196: END IF;
6189: WHERE stop_id = l_stop_id;
6190: IF (SQL%NOTFOUND) THEN
6191: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
6192: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6193: wsh_util_core.add_message(x_return_status);
6194: goto wt_vol_error;
6195: END IF;
6196: END IF;
6197:
6244: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6245: END IF;
6246: --
6247: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
6248: wsh_util_core.add_message(x_return_status);
6249: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
6250: l_num_error := l_num_error + 1;
6251: ELSIF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
6252: l_num_warn := l_num_warn + 1;
6245: END IF;
6246: --
6247: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
6248: wsh_util_core.add_message(x_return_status);
6249: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
6250: l_num_error := l_num_error + 1;
6251: ELSIF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
6252: l_num_warn := l_num_warn + 1;
6253: END IF;
6247: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_rows(i)));
6248: wsh_util_core.add_message(x_return_status);
6249: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
6250: l_num_error := l_num_error + 1;
6251: ELSIF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
6252: l_num_warn := l_num_warn + 1;
6253: END IF;
6254:
6255: <
6286: FND_MESSAGE.SET_TOKEN('NUM_ERROR',l_num_error);
6287: FND_MESSAGE.SET_TOKEN('NUM_SUCCESS',p_trip_rows.count - l_num_error - l_num_warn);
6288: END IF;
6289: IF (p_trip_rows.count = l_num_error) THEN
6290: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6291: ELSE
6292: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6293: END IF;
6294:
6288: END IF;
6289: IF (p_trip_rows.count = l_num_error) THEN
6290: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6291: ELSE
6292: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6293: END IF;
6294:
6295: wsh_util_core.add_message(x_return_status);
6296:
6291: ELSE
6292: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6293: END IF;
6294:
6295: wsh_util_core.add_message(x_return_status);
6296:
6297: ELSE
6298: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6299: END IF;
6294:
6295: wsh_util_core.add_message(x_return_status);
6296:
6297: ELSE
6298: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6299: END IF;
6300:
6301: IF l_debug_on THEN
6302: WSH_DEBUG_SV.log(l_module_name,'RET STATUS-'||x_return_status);
6314: WHEN others THEN
6315: IF lock_stop%ISOPEN THEN
6316: CLOSE lock_stop;
6317: END IF;
6318: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.TRIP_WEIGHT_VOLUME');
6319: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6320:
6321: --
6322: -- Debug Statements
6315: IF lock_stop%ISOPEN THEN
6316: CLOSE lock_stop;
6317: END IF;
6318: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.TRIP_WEIGHT_VOLUME');
6319: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6320:
6321: --
6322: -- Debug Statements
6323: --
6398: END IF;
6399:
6400: END LOOP;
6401:
6402: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6403:
6404: --
6405: -- Debug Statements
6406: --
6418: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
6419: END IF;
6420: --
6421: FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_id));
6422: wsh_util_core.add_message(x_return_status);
6423: --
6424: -- Debug Statements
6425: --
6426: IF l_debug_on THEN
6428: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INVALID_SEQUENCE');
6429: END IF;
6430: --
6431: WHEN others THEN
6432: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.VALIDATE_STOP_SEQUENCE');
6433: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6434:
6435: --
6436: -- Debug Statements
6429: END IF;
6430: --
6431: WHEN others THEN
6432: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.VALIDATE_STOP_SEQUENCE');
6433: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6434:
6435: --
6436: -- Debug Statements
6437: --
6442: --
6443: END validate_stop_sequence;
6444:
6445: PROCEDURE Check_Unassign_Trip(
6446: p_del_rows IN wsh_util_core.id_tab_type,
6447: x_trip_rows OUT NOCOPY wsh_util_core.id_tab_type,
6448: x_return_status OUT NOCOPY VARCHAR2) IS
6449:
6450: cnt NUMBER := 0;
6443: END validate_stop_sequence;
6444:
6445: PROCEDURE Check_Unassign_Trip(
6446: p_del_rows IN wsh_util_core.id_tab_type,
6447: x_trip_rows OUT NOCOPY wsh_util_core.id_tab_type,
6448: x_return_status OUT NOCOPY VARCHAR2) IS
6449:
6450: cnt NUMBER := 0;
6451: l_count NUMBER;
6480: IF l_debug_on THEN
6481: WSH_DEBUG_SV.push(l_module_name);
6482: END IF;
6483: --
6484: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6485:
6486: IF (p_del_rows.count = 0) THEN
6487: raise others;
6488: END IF;
6514: CLOSE deltrip_cv;
6515:
6516: IF (cnt = 1) THEN -- cnt := cnt + 1 before we exit loop above, so cnt is always > 0, bug 2434673.
6517: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_MULTI_UNASSIGN_ERROR');
6518: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6519: wsh_util_core.add_message(x_return_status);
6520: END IF;
6521:
6522: --
6515:
6516: IF (cnt = 1) THEN -- cnt := cnt + 1 before we exit loop above, so cnt is always > 0, bug 2434673.
6517: FND_MESSAGE.SET_NAME('WSH','WSH_DEL_MULTI_UNASSIGN_ERROR');
6518: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6519: wsh_util_core.add_message(x_return_status);
6520: END IF;
6521:
6522: --
6523: -- Debug Statements
6527: END IF;
6528: --
6529: EXCEPTION
6530: WHEN others THEN
6531: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.CHECK_UNASSIGN_TRIP');
6532: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6533:
6534: --
6535: -- Debug Statements
6528: --
6529: EXCEPTION
6530: WHEN others THEN
6531: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.CHECK_UNASSIGN_TRIP');
6532: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6533:
6534: --
6535: -- Debug Statements
6536: --
6542: END Check_Unassign_Trip;
6543:
6544:
6545: PROCEDURE Unassign_Trip(
6546: p_del_rows IN wsh_util_core.id_tab_type,
6547: p_trip_id IN NUMBER,
6548: x_return_status OUT NOCOPY VARCHAR2) IS
6549:
6550: cursor get_trip_info(p_trip_id in number) is
6559: and l.parent_delivery_leg_id is not null
6560: and l.pick_up_stop_id = s.stop_id
6561: and s.trip_id = p_trip_id;
6562:
6563: l_trip_tab WSH_UTIL_CORE.id_tab_type;
6564: l_mdc_del_tab WSH_UTIL_CORE.id_tab_type;
6565: l_return_status VARCHAR2(10);
6566: l_trip_name VARCHAR2(30);
6567: l_planned_flag wsh_trips.planned_flag%TYPE;
6560: and l.pick_up_stop_id = s.stop_id
6561: and s.trip_id = p_trip_id;
6562:
6563: l_trip_tab WSH_UTIL_CORE.id_tab_type;
6564: l_mdc_del_tab WSH_UTIL_CORE.id_tab_type;
6565: l_return_status VARCHAR2(10);
6566: l_trip_name VARCHAR2(30);
6567: l_planned_flag wsh_trips.planned_flag%TYPE;
6568: j NUMBER := 0;
6571: trip_not_found EXCEPTION;
6572: trip_planned EXCEPTION;
6573:
6574: --WF: CMR
6575: l_del_old_carrier_ids WSH_UTIL_CORE.ID_TAB_TYPE;
6576: l_wf_rs VARCHAR2(1);
6577:
6578:
6579: --
6609: END IF;
6610: CLOSE get_trip_info;
6611:
6612: IF l_trip_name IS NULL THEN
6613: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6614: raise trip_not_found;
6615: END IF;
6616:
6617: IF l_planned_flag = 'F' THEN
6614: raise trip_not_found;
6615: END IF;
6616:
6617: IF l_planned_flag = 'F' THEN
6618: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6619: raise trip_planned;
6620: END IF;
6621:
6622: --- MDC: Check if the deliveries are assigned to consol deliveries at this trip, and unassgin them from the consol delas well.
6638: p_parent_del => NULL,
6639: p_caller => 'WSH_UNASSIGN_TRIP',
6640: p_del_tab => l_mdc_del_tab,
6641: x_return_status => l_return_status);
6642: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
6643: raise unassign_deliveries_err;
6644: ELSE
6645: x_return_status := l_return_status;
6646: END IF;
6660: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_ACTIONS.UNASSIGN_DELIVERIES',WSH_DEBUG_SV.C_PROC_LEVEL);
6661: END IF;
6662: --
6663: wsh_delivery_legs_actions.unassign_deliveries( p_del_rows, p_trip_id, NULL, NULL, l_return_status);
6664: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
6665: raise unassign_deliveries_err;
6666: ELSE
6667: x_return_status := l_return_status;
6668: END IF;
6687: WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required(
6688: p_entity_type => 'TRIP',
6689: p_entity_ids => l_trip_tab,
6690: x_return_status => l_return_status);
6691: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
6692: raise reprice_required_err;
6693: ELSE
6694: IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
6695: l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
6690: x_return_status => l_return_status);
6691: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
6692: raise reprice_required_err;
6693: ELSE
6694: IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
6695: l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
6696: x_return_status := l_return_status;
6697: END IF;
6698: END IF;
6691: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
6692: raise reprice_required_err;
6693: ELSE
6694: IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
6695: l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
6696: x_return_status := l_return_status;
6697: END IF;
6698: END IF;
6699:
6708:
6709: WHEN trip_not_found THEN
6710: x_return_status := l_return_status;
6711: fnd_message.set_name('WSH', 'WSH_TRIP_NOT_FOUND');
6712: wsh_util_core.add_message(x_return_status);
6713:
6714: --
6715: -- Debug Statements
6716: --
6722: WHEN unassign_deliveries_err THEN
6723: x_return_status := l_return_status;
6724: fnd_message.set_name('WSH', 'WSH_DEL_UNASSIGN_ERROR');
6725: fnd_message.set_token('TRIP_NAME', l_trip_name);
6726: wsh_util_core.add_message(x_return_status);
6727:
6728: --
6729: -- Debug Statements
6730: --
6735: --
6736: WHEN reprice_required_err THEN
6737: x_return_status := l_return_status;
6738: fnd_message.set_name('WSH', 'WSH_REPRICE_REQUIRED_ERR');
6739: wsh_util_core.add_message(x_return_status);
6740:
6741: --
6742: -- Debug Statements
6743: --
6749: WHEN trip_planned THEN
6750: x_return_status := l_return_status;
6751: fnd_message.set_name('WSH', 'WSH_PLANNED_TRIP_NO_ACTION');
6752: fnd_message.set_token('TRIP_NAME', l_trip_name);
6753: wsh_util_core.add_message(x_return_status);
6754:
6755: --
6756: -- Debug Statements
6757: --
6763: WHEN others THEN
6764: IF get_trip_info%ISOPEN THEN
6765: CLOSE get_trip_info;
6766: END IF;
6767: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.UNASSIGN_TRIP');
6768: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6769:
6770: --
6771: -- Debug Statements
6764: IF get_trip_info%ISOPEN THEN
6765: CLOSE get_trip_info;
6766: END IF;
6767: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.UNASSIGN_TRIP');
6768: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6769:
6770: --
6771: -- Debug Statements
6772: --
6828: WSH_DEBUG_SV.log(l_module_name,'P_NET_WEIGHT',P_NET_WEIGHT);
6829: WSH_DEBUG_SV.log(l_module_name,'P_VOLUME',P_VOLUME);
6830: WSH_DEBUG_SV.log(l_module_name,'P_FILL_PERCENT',P_FILL_PERCENT);
6831: END IF;
6832: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6833:
6834: -- Get pvt type record structure for stop
6835: IF l_debug_on THEN
6836: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
6840: p_stop_id => p_stop_id,
6841: x_stop_rec => l_stop_rec,
6842: x_return_status => l_return_status);
6843:
6844: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
6845: x_return_status := l_return_status;
6846:
6847: IF l_debug_on THEN
6848: WSH_DEBUG_SV.pop(l_module_name);
6874: END IF;
6875:
6876: EXCEPTION
6877: WHEN others THEN
6878: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.Fte_Load_Tender');
6879: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6880:
6881: IF l_debug_on THEN
6882: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6875:
6876: EXCEPTION
6877: WHEN others THEN
6878: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.Fte_Load_Tender');
6879: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6880:
6881: IF l_debug_on THEN
6882: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6883: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
6906: -- Error : if none of the trips are validated or updated
6907: -- Unexpected Error
6908: -- ----------------------------------------------------------------------
6909: PROCEDURE Reset_Stop_Planned_Dates
6910: ( p_trip_ids IN wsh_util_core.id_tab_type,
6911: p_caller IN VARCHAR2,
6912: x_success_trip_ids OUT NOCOPY wsh_util_core.id_tab_type,
6913: x_return_status OUT NOCOPY VARCHAR2)
6914: IS
6908: -- ----------------------------------------------------------------------
6909: PROCEDURE Reset_Stop_Planned_Dates
6910: ( p_trip_ids IN wsh_util_core.id_tab_type,
6911: p_caller IN VARCHAR2,
6912: x_success_trip_ids OUT NOCOPY wsh_util_core.id_tab_type,
6913: x_return_status OUT NOCOPY VARCHAR2)
6914: IS
6915:
6916: CURSOR lock_stop(c_stop_id NUMBER) IS
6936:
6937: l_stop_rec c_trip_stops%ROWTYPE;
6938: l_last_stop_rec c_trip_stops%ROWTYPE;
6939:
6940: l_trip_ids wsh_util_core.id_tab_type;
6941: l_success_trip_ids wsh_util_core.id_tab_type;
6942: l_return_status VARCHAR2(1);
6943:
6944: l_update_flag BOOLEAN;
6937: l_stop_rec c_trip_stops%ROWTYPE;
6938: l_last_stop_rec c_trip_stops%ROWTYPE;
6939:
6940: l_trip_ids wsh_util_core.id_tab_type;
6941: l_success_trip_ids wsh_util_core.id_tab_type;
6942: l_return_status VARCHAR2(1);
6943:
6944: l_update_flag BOOLEAN;
6945: l_trip_error BOOLEAN;
6952: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RESET_STOP_PLANNED_DATES';
6953:
6954: stop_locked EXCEPTION;
6955:
6956: l_stop_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
6957: l_dbi_rs VARCHAR2(1); -- DBI Project
6958:
6959: PRAGMA EXCEPTION_INIT(stop_locked, -00054);
6960:
6959: PRAGMA EXCEPTION_INIT(stop_locked, -00054);
6960:
6961: BEGIN
6962: l_warn_num := 0;
6963: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6964: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
6965: IF l_debug_on IS NULL THEN
6966: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
6967: END IF;
6971: WSH_DEBUG_SV.log(l_module_name,'p_trip_ids.count ', p_trip_ids.count);
6972: END IF;
6973:
6974: IF p_trip_ids.count = 0 THEN
6975: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6976: IF l_debug_on THEN
6977: WSH_DEBUG_SV.logmsg(l_module_name,' p_trip_ids.count is zero');
6978: WSH_DEBUG_SV.pop(l_module_name);
6979: END IF;
7064:
7065: IF l_debug_on THEN
7066: WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
7067: END IF;
7068: IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
7069: x_return_status := l_dbi_rs;
7070: -- just pass this return status to caller API
7071: IF l_debug_on THEN
7072: WSH_DEBUG_SV.log(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
7115: ( p_trip_ids => l_trip_ids,
7116: p_caller => p_caller,
7117: x_success_trip_ids => l_success_trip_ids,
7118: x_return_status => l_return_status);
7119: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
7120: l_trip_error := TRUE;
7121: exit;
7122: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
7123: l_warn_num := l_warn_num + 1;
7118: x_return_status => l_return_status);
7119: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
7120: l_trip_error := TRUE;
7121: exit;
7122: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
7123: l_warn_num := l_warn_num + 1;
7124: END IF;
7125:
7126:
7133: i := p_trip_ids.next(i);
7134: END LOOP;
7135:
7136: IF x_success_trip_ids.count = 0 THEN
7137: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7138: ELSIF x_success_trip_ids.count < p_trip_ids.count
7139: OR l_warn_num > 0 THEN
7140: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
7141:
7136: IF x_success_trip_ids.count = 0 THEN
7137: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7138: ELSIF x_success_trip_ids.count < p_trip_ids.count
7139: OR l_warn_num > 0 THEN
7140: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
7141:
7142: END IF;
7143:
7144: IF l_debug_on THEN
7156: END IF;
7157: IF c_trip_stops%ISOPEN THEN
7158: close c_trip_stops;
7159: END IF;
7160: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.reset_stop_planned_dates');
7161: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7162:
7163: IF l_debug_on THEN
7164: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
7157: IF c_trip_stops%ISOPEN THEN
7158: close c_trip_stops;
7159: END IF;
7160: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.reset_stop_planned_dates');
7161: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7162:
7163: IF l_debug_on THEN
7164: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
7165: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
7192: p_caller IN VARCHAR2,
7193: x_return_status OUT NOCOPY VARCHAR2)
7194: IS
7195:
7196: l_trip_ids wsh_util_core.id_tab_type;
7197: l_success_trip_ids wsh_util_core.id_tab_type;
7198: l_return_status VARCHAR2(1);
7199: l_debug_on BOOLEAN;
7200: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RESET_STOP_PLANNED_DATES';
7193: x_return_status OUT NOCOPY VARCHAR2)
7194: IS
7195:
7196: l_trip_ids wsh_util_core.id_tab_type;
7197: l_success_trip_ids wsh_util_core.id_tab_type;
7198: l_return_status VARCHAR2(1);
7199: l_debug_on BOOLEAN;
7200: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RESET_STOP_PLANNED_DATES';
7201:
7200: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RESET_STOP_PLANNED_DATES';
7201:
7202: BEGIN
7203:
7204: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
7205: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
7206: IF l_debug_on IS NULL THEN
7207: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
7208: END IF;
7212: WSH_DEBUG_SV.log(l_module_name,'p_trip_id ', p_trip_id);
7213: END IF;
7214:
7215: IF p_trip_id is NULL THEN
7216: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7217: IF l_debug_on THEN
7218: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR: Trip ID is NULL');
7219: WSH_DEBUG_SV.pop(l_module_name);
7220: END IF;
7225: ( p_trip_ids => l_trip_ids,
7226: p_caller => p_caller,
7227: x_success_trip_ids => l_success_trip_ids,
7228: x_return_status => l_return_status);
7229: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR , WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
7230: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7231: ELSE
7232: x_return_status := l_return_status;
7233: END IF;
7226: p_caller => p_caller,
7227: x_success_trip_ids => l_success_trip_ids,
7228: x_return_status => l_return_status);
7229: IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR , WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
7230: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7231: ELSE
7232: x_return_status := l_return_status;
7233: END IF;
7234:
7239:
7240: EXCEPTION
7241:
7242: WHEN others THEN
7243: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.reset_stop_planned_dates');
7244: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7245:
7246: IF l_debug_on THEN
7247: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
7240: EXCEPTION
7241:
7242: WHEN others THEN
7243: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.reset_stop_planned_dates');
7244: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
7245:
7246: IF l_debug_on THEN
7247: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
7248: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
7329: -- Error : if none of the trips are validated or updated
7330: --
7331: -- ----------------------------------------------------------------------
7332: PROCEDURE Handle_Internal_Stops
7333: ( p_trip_ids IN wsh_util_core.id_tab_type,
7334: p_caller IN VARCHAR2,
7335: x_success_trip_ids OUT NOCOPY wsh_util_core.id_tab_type,
7336: x_return_status OUT NOCOPY VARCHAR2)
7337: IS
7331: -- ----------------------------------------------------------------------
7332: PROCEDURE Handle_Internal_Stops
7333: ( p_trip_ids IN wsh_util_core.id_tab_type,
7334: p_caller IN VARCHAR2,
7335: x_success_trip_ids OUT NOCOPY wsh_util_core.id_tab_type,
7336: x_return_status OUT NOCOPY VARCHAR2)
7337: IS
7338: --
7339: -- make a list of stops sequenced by PAD,
7561: AND drop_off_stop_id = c_dummy_stop_id
7562: AND rownum = 1;
7563:
7564: --
7565: l_getstops_stop_id wsh_util_core.id_tab_type;
7566: l_getstops_stop_loc_id wsh_util_core.id_tab_type;
7567: l_getstops_phys_loc_id wsh_util_core.id_tab_type;
7568: l_getstops_phys_stop_id wsh_util_core.id_tab_type;
7569: l_getstops_stop_seq_num wsh_util_core.id_tab_type;
7562: AND rownum = 1;
7563:
7564: --
7565: l_getstops_stop_id wsh_util_core.id_tab_type;
7566: l_getstops_stop_loc_id wsh_util_core.id_tab_type;
7567: l_getstops_phys_loc_id wsh_util_core.id_tab_type;
7568: l_getstops_phys_stop_id wsh_util_core.id_tab_type;
7569: l_getstops_stop_seq_num wsh_util_core.id_tab_type;
7570: l_getstops_pl_arr_date wsh_util_core.date_tab_type;
7563:
7564: --
7565: l_getstops_stop_id wsh_util_core.id_tab_type;
7566: l_getstops_stop_loc_id wsh_util_core.id_tab_type;
7567: l_getstops_phys_loc_id wsh_util_core.id_tab_type;
7568: l_getstops_phys_stop_id wsh_util_core.id_tab_type;
7569: l_getstops_stop_seq_num wsh_util_core.id_tab_type;
7570: l_getstops_pl_arr_date wsh_util_core.date_tab_type;
7571: l_getstops_pl_dep_date wsh_util_core.date_tab_type;
7564: --
7565: l_getstops_stop_id wsh_util_core.id_tab_type;
7566: l_getstops_stop_loc_id wsh_util_core.id_tab_type;
7567: l_getstops_phys_loc_id wsh_util_core.id_tab_type;
7568: l_getstops_phys_stop_id wsh_util_core.id_tab_type;
7569: l_getstops_stop_seq_num wsh_util_core.id_tab_type;
7570: l_getstops_pl_arr_date wsh_util_core.date_tab_type;
7571: l_getstops_pl_dep_date wsh_util_core.date_tab_type;
7572: l_getstops_org_stop_seq_num wsh_util_core.id_tab_type;
7565: l_getstops_stop_id wsh_util_core.id_tab_type;
7566: l_getstops_stop_loc_id wsh_util_core.id_tab_type;
7567: l_getstops_phys_loc_id wsh_util_core.id_tab_type;
7568: l_getstops_phys_stop_id wsh_util_core.id_tab_type;
7569: l_getstops_stop_seq_num wsh_util_core.id_tab_type;
7570: l_getstops_pl_arr_date wsh_util_core.date_tab_type;
7571: l_getstops_pl_dep_date wsh_util_core.date_tab_type;
7572: l_getstops_org_stop_seq_num wsh_util_core.id_tab_type;
7573: l_getstops_org_pl_arr_date wsh_util_core.date_tab_type;
7566: l_getstops_stop_loc_id wsh_util_core.id_tab_type;
7567: l_getstops_phys_loc_id wsh_util_core.id_tab_type;
7568: l_getstops_phys_stop_id wsh_util_core.id_tab_type;
7569: l_getstops_stop_seq_num wsh_util_core.id_tab_type;
7570: l_getstops_pl_arr_date wsh_util_core.date_tab_type;
7571: l_getstops_pl_dep_date wsh_util_core.date_tab_type;
7572: l_getstops_org_stop_seq_num wsh_util_core.id_tab_type;
7573: l_getstops_org_pl_arr_date wsh_util_core.date_tab_type;
7574: l_getstops_org_pl_dep_date wsh_util_core.date_tab_type;
7567: l_getstops_phys_loc_id wsh_util_core.id_tab_type;
7568: l_getstops_phys_stop_id wsh_util_core.id_tab_type;
7569: l_getstops_stop_seq_num wsh_util_core.id_tab_type;
7570: l_getstops_pl_arr_date wsh_util_core.date_tab_type;
7571: l_getstops_pl_dep_date wsh_util_core.date_tab_type;
7572: l_getstops_org_stop_seq_num wsh_util_core.id_tab_type;
7573: l_getstops_org_pl_arr_date wsh_util_core.date_tab_type;
7574: l_getstops_org_pl_dep_date wsh_util_core.date_tab_type;
7575: l_getstops_status_code WSH_UTIL_CORE.Column_Tab_Type;
7568: l_getstops_phys_stop_id wsh_util_core.id_tab_type;
7569: l_getstops_stop_seq_num wsh_util_core.id_tab_type;
7570: l_getstops_pl_arr_date wsh_util_core.date_tab_type;
7571: l_getstops_pl_dep_date wsh_util_core.date_tab_type;
7572: l_getstops_org_stop_seq_num wsh_util_core.id_tab_type;
7573: l_getstops_org_pl_arr_date wsh_util_core.date_tab_type;
7574: l_getstops_org_pl_dep_date wsh_util_core.date_tab_type;
7575: l_getstops_status_code WSH_UTIL_CORE.Column_Tab_Type;
7576: l_getstops_break_link_flags WSH_UTIL_CORE.Column_Tab_Type;
7569: l_getstops_stop_seq_num wsh_util_core.id_tab_type;
7570: l_getstops_pl_arr_date wsh_util_core.date_tab_type;
7571: l_getstops_pl_dep_date wsh_util_core.date_tab_type;
7572: l_getstops_org_stop_seq_num wsh_util_core.id_tab_type;
7573: l_getstops_org_pl_arr_date wsh_util_core.date_tab_type;
7574: l_getstops_org_pl_dep_date wsh_util_core.date_tab_type;
7575: l_getstops_status_code WSH_UTIL_CORE.Column_Tab_Type;
7576: l_getstops_break_link_flags WSH_UTIL_CORE.Column_Tab_Type;
7577:
7570: l_getstops_pl_arr_date wsh_util_core.date_tab_type;
7571: l_getstops_pl_dep_date wsh_util_core.date_tab_type;
7572: l_getstops_org_stop_seq_num wsh_util_core.id_tab_type;
7573: l_getstops_org_pl_arr_date wsh_util_core.date_tab_type;
7574: l_getstops_org_pl_dep_date wsh_util_core.date_tab_type;
7575: l_getstops_status_code WSH_UTIL_CORE.Column_Tab_Type;
7576: l_getstops_break_link_flags WSH_UTIL_CORE.Column_Tab_Type;
7577:
7578: -- both the cursors c_get_stops_PAD and c_get_stops_SSN select identical fields
7571: l_getstops_pl_dep_date wsh_util_core.date_tab_type;
7572: l_getstops_org_stop_seq_num wsh_util_core.id_tab_type;
7573: l_getstops_org_pl_arr_date wsh_util_core.date_tab_type;
7574: l_getstops_org_pl_dep_date wsh_util_core.date_tab_type;
7575: l_getstops_status_code WSH_UTIL_CORE.Column_Tab_Type;
7576: l_getstops_break_link_flags WSH_UTIL_CORE.Column_Tab_Type;
7577:
7578: -- both the cursors c_get_stops_PAD and c_get_stops_SSN select identical fields
7579: l_getstops_tmp c_get_stops_PAD%ROWTYPE;
7572: l_getstops_org_stop_seq_num wsh_util_core.id_tab_type;
7573: l_getstops_org_pl_arr_date wsh_util_core.date_tab_type;
7574: l_getstops_org_pl_dep_date wsh_util_core.date_tab_type;
7575: l_getstops_status_code WSH_UTIL_CORE.Column_Tab_Type;
7576: l_getstops_break_link_flags WSH_UTIL_CORE.Column_Tab_Type;
7577:
7578: -- both the cursors c_get_stops_PAD and c_get_stops_SSN select identical fields
7579: l_getstops_tmp c_get_stops_PAD%ROWTYPE;
7580:
7603:
7604: l_stop_seq_mode NUMBER;
7605:
7606: BEGIN
7607: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
7608: l_warn_num := 0;
7609: x_success_trip_ids.delete;
7610:
7611: l_stop_seq_mode := WSH_TRIPS_ACTIONS.GET_STOP_SEQ_MODE;
7621: WSH_DEBUG_SV.log(l_module_name,'p_caller ', p_caller);
7622: END IF;
7623:
7624: IF p_trip_ids.count = 0 THEN
7625: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7626: IF l_debug_on THEN
7627: WSH_DEBUG_SV.logmsg(l_module_name,' p_trip_ids.count is zero');
7628: WSH_DEBUG_SV.pop(l_module_name);
7629: END IF;
7931:
7932: IF l_debug_on THEN
7933: WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
7934: END IF;
7935: IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
7936: x_return_status := l_dbi_rs;
7937: rollback to start_of_the_trip;
7938: -- just pass this return status to caller API
7939: IF l_debug_on THEN
7960: IF l_debug_on THEN
7961: 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);
7962: END IF;
7963:
7964: IF l_return_status in ( WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
7965: raise reset_stop_seq_number_error;
7966: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
7967: l_warn_num := l_warn_num + 1;
7968: END IF;
7962: END IF;
7963:
7964: IF l_return_status in ( WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
7965: raise reset_stop_seq_number_error;
7966: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
7967: l_warn_num := l_warn_num + 1;
7968: END IF;
7969:
7970: WSH_TRIP_VALIDATIONS.Validate_Stop_Dates (
7974: IF l_debug_on THEN
7975: 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);
7976: END IF;
7977:
7978: IF l_return_status in ( WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
7979: raise validate_stop_date_error;
7980: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
7981: l_warn_num := l_warn_num + 1;
7982: END IF;
7976: END IF;
7977:
7978: IF l_return_status in ( WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
7979: raise validate_stop_date_error;
7980: ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
7981: l_warn_num := l_warn_num + 1;
7982: END IF;
7983:
7984: END IF; -- PAD resequencing
8001: rollback to start_of_the_trip;
8002: FND_MESSAGE.SET_NAME('WSH', 'WSH_BETWEEN_LINKED_STOPS');
8003: FND_MESSAGE.SET_TOKEN('DUMMY_STOP_DATE', fnd_date.date_to_displaydt(l_getstops_pl_arr_date(j),calendar_aware=>FND_DATE.calendar_aware_alt));
8004: FND_MESSAGE.SET_TOKEN('DUMMY_LOCATION_DESP',
8005: WSH_UTIL_CORE.Get_Location_Description (l_getstops_stop_loc_id(j),'NEW UI CODE INFO'));
8006: FND_MESSAGE.SET_TOKEN('PHYSICAL_STOP_DATE', fnd_date.date_to_displaydt(l_getstops_pl_arr_date(j+1),calendar_aware=>FND_DATE.calendar_aware_alt));
8007: FND_MESSAGE.SET_TOKEN('PHYSICAL_LOCATION_DESP',
8008: WSH_UTIL_CORE.Get_Location_Description (l_getstops_stop_loc_id(j+1),'NEW UI CODE INFO'));
8009: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
8004: FND_MESSAGE.SET_TOKEN('DUMMY_LOCATION_DESP',
8005: WSH_UTIL_CORE.Get_Location_Description (l_getstops_stop_loc_id(j),'NEW UI CODE INFO'));
8006: FND_MESSAGE.SET_TOKEN('PHYSICAL_STOP_DATE', fnd_date.date_to_displaydt(l_getstops_pl_arr_date(j+1),calendar_aware=>FND_DATE.calendar_aware_alt));
8007: FND_MESSAGE.SET_TOKEN('PHYSICAL_LOCATION_DESP',
8008: WSH_UTIL_CORE.Get_Location_Description (l_getstops_stop_loc_id(j+1),'NEW UI CODE INFO'));
8009: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
8010:
8011: -- Handle SSN Exception
8012: WHEN invalid_stop_seq_num THEN
8005: WSH_UTIL_CORE.Get_Location_Description (l_getstops_stop_loc_id(j),'NEW UI CODE INFO'));
8006: FND_MESSAGE.SET_TOKEN('PHYSICAL_STOP_DATE', fnd_date.date_to_displaydt(l_getstops_pl_arr_date(j+1),calendar_aware=>FND_DATE.calendar_aware_alt));
8007: FND_MESSAGE.SET_TOKEN('PHYSICAL_LOCATION_DESP',
8008: WSH_UTIL_CORE.Get_Location_Description (l_getstops_stop_loc_id(j+1),'NEW UI CODE INFO'));
8009: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
8010:
8011: -- Handle SSN Exception
8012: WHEN invalid_stop_seq_num THEN
8013: rollback to start_of_the_trip;
8013: rollback to start_of_the_trip;
8014: FND_MESSAGE.SET_NAME('WSH', 'WSH_BETWEEN_LINKED_STOPS_SSN');
8015: FND_MESSAGE.SET_TOKEN('DUMMY_STOP_SEQ_NUM', l_getstops_stop_seq_num(j));
8016: FND_MESSAGE.SET_TOKEN('DUMMY_LOCATION_DESP',
8017: WSH_UTIL_CORE.Get_Location_Description (l_getstops_stop_loc_id(j),'NEW UI CODE INFO'));
8018: FND_MESSAGE.SET_TOKEN('PHYSICAL_STOP_SEQ_NUM', l_getstops_stop_seq_num(j+1));
8019: FND_MESSAGE.SET_TOKEN('PHYSICAL_LOCATION_DESP',
8020: WSH_UTIL_CORE.Get_Location_Description (l_getstops_stop_loc_id(j+1),'NEW UI CODE INFO'));
8021: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
8016: FND_MESSAGE.SET_TOKEN('DUMMY_LOCATION_DESP',
8017: WSH_UTIL_CORE.Get_Location_Description (l_getstops_stop_loc_id(j),'NEW UI CODE INFO'));
8018: FND_MESSAGE.SET_TOKEN('PHYSICAL_STOP_SEQ_NUM', l_getstops_stop_seq_num(j+1));
8019: FND_MESSAGE.SET_TOKEN('PHYSICAL_LOCATION_DESP',
8020: WSH_UTIL_CORE.Get_Location_Description (l_getstops_stop_loc_id(j+1),'NEW UI CODE INFO'));
8021: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
8022:
8023: WHEN invalid_ssn_adjustment THEN
8024: rollback to start_of_the_trip;
8017: WSH_UTIL_CORE.Get_Location_Description (l_getstops_stop_loc_id(j),'NEW UI CODE INFO'));
8018: FND_MESSAGE.SET_TOKEN('PHYSICAL_STOP_SEQ_NUM', l_getstops_stop_seq_num(j+1));
8019: FND_MESSAGE.SET_TOKEN('PHYSICAL_LOCATION_DESP',
8020: WSH_UTIL_CORE.Get_Location_Description (l_getstops_stop_loc_id(j+1),'NEW UI CODE INFO'));
8021: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
8022:
8023: WHEN invalid_ssn_adjustment THEN
8024: rollback to start_of_the_trip;
8025: FND_MESSAGE.SET_NAME('WSH', 'WSH_INVALID_SSN_ADJUSTMENT');
8022:
8023: WHEN invalid_ssn_adjustment THEN
8024: rollback to start_of_the_trip;
8025: FND_MESSAGE.SET_NAME('WSH', 'WSH_INVALID_SSN_ADJUSTMENT');
8026: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
8027:
8028: -- Handle non unique SSN exception (bug 4245339)
8029: WHEN duplicate_stop_seq THEN
8030: rollback to start_of_the_trip;
8028: -- Handle non unique SSN exception (bug 4245339)
8029: WHEN duplicate_stop_seq THEN
8030: rollback to start_of_the_trip;
8031: FND_MESSAGE.SET_NAME('WSH','WSH_STOP_DUP_SEQUENCE');
8032: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
8033:
8034: WHEN reset_stop_seq_number_error THEN
8035: rollback to start_of_the_trip;
8036:
8056: i := p_trip_ids.next(i);
8057: END LOOP;
8058:
8059: IF x_success_trip_ids.count = 0 THEN
8060: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8061: ELSIF x_success_trip_ids.count < p_trip_ids.count
8062: OR l_warn_num > 0 THEN
8063: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8064: END IF;
8059: IF x_success_trip_ids.count = 0 THEN
8060: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8061: ELSIF x_success_trip_ids.count < p_trip_ids.count
8062: OR l_warn_num > 0 THEN
8063: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8064: END IF;
8065:
8066: IF l_debug_on THEN
8067: WSH_DEBUG_SV.log(l_module_name,'x_success_trip_ids.count', x_success_trip_ids.count);
8078: IF c_get_stops_SSN%ISOPEN THEN
8079: close c_get_stops_SSN;
8080: END IF;
8081:
8082: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.LINK_TO_REGULAR_STOPS');
8083: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8084:
8085: IF l_debug_on THEN
8086: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
8079: close c_get_stops_SSN;
8080: END IF;
8081:
8082: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.LINK_TO_REGULAR_STOPS');
8083: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8084:
8085: IF l_debug_on THEN
8086: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
8087: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
8092:
8093:
8094: PROCEDURE PROCESS_CARRIER_SELECTION (
8095: p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
8096: p_trip_id_tab IN wsh_util_core.id_tab_type,
8097: p_caller IN VARCHAR2 DEFAULT NULL, -- WSH_FSTRX / WSH_PUB / WSH_GROUP/ FTE
8098: x_msg_count OUT NOCOPY NUMBER,
8099: x_msg_data OUT NOCOPY VARCHAR2,
8100: x_return_status OUT NOCOPY VARCHAR2)
8207: l_prev_trip_id NUMBER := 0;
8208: l_trip_result_type VARCHAR2(30);
8209: l_rank_result_cnt NUMBER := 0;
8210:
8211: l_organization_tab WSH_UTIL_CORE.id_tab_type;
8212: l_customer_tab WSH_UTIL_CORE.id_tab_type;
8213: l_organization_id NUMBER := 0;
8214: l_customer_id NUMBER := 0;
8215: l_delivery_id NUMBER := 0;
8208: l_trip_result_type VARCHAR2(30);
8209: l_rank_result_cnt NUMBER := 0;
8210:
8211: l_organization_tab WSH_UTIL_CORE.id_tab_type;
8212: l_customer_tab WSH_UTIL_CORE.id_tab_type;
8213: l_organization_id NUMBER := 0;
8214: l_customer_id NUMBER := 0;
8215: l_delivery_id NUMBER := 0;
8216: l_customer_site_id NUMBER := NULL;
8215: l_delivery_id NUMBER := 0;
8216: l_customer_site_id NUMBER := NULL;
8217: l_rg_trip BOOLEAN := TRUE;
8218: --l_rg_trip_cnt NUMBER := 0;
8219: l_stop_organization_id_tab WSH_UTIL_CORE.id_tab_type;
8220: l_stop_customer_id_tab WSH_UTIL_CORE.id_tab_type;
8221: l_stop_delivery_id_tab WSH_UTIL_CORE.id_tab_type;
8222: l_base_weight_uom VARCHAR2(3);
8223: l_base_volume_uom VARCHAR2(3);
8216: l_customer_site_id NUMBER := NULL;
8217: l_rg_trip BOOLEAN := TRUE;
8218: --l_rg_trip_cnt NUMBER := 0;
8219: l_stop_organization_id_tab WSH_UTIL_CORE.id_tab_type;
8220: l_stop_customer_id_tab WSH_UTIL_CORE.id_tab_type;
8221: l_stop_delivery_id_tab WSH_UTIL_CORE.id_tab_type;
8222: l_base_weight_uom VARCHAR2(3);
8223: l_base_volume_uom VARCHAR2(3);
8224: l_pickup_weight_convert NUMBER := 0;
8217: l_rg_trip BOOLEAN := TRUE;
8218: --l_rg_trip_cnt NUMBER := 0;
8219: l_stop_organization_id_tab WSH_UTIL_CORE.id_tab_type;
8220: l_stop_customer_id_tab WSH_UTIL_CORE.id_tab_type;
8221: l_stop_delivery_id_tab WSH_UTIL_CORE.id_tab_type;
8222: l_base_weight_uom VARCHAR2(3);
8223: l_base_volume_uom VARCHAR2(3);
8224: l_pickup_weight_convert NUMBER := 0;
8225: l_pickup_volume_convert NUMBER := 0;
8277: WSH_DEBUG_SV.logmsg(l_module_name,'p_init_msg_list : '||p_init_msg_list);
8278: WSH_DEBUG_SV.logmsg(l_module_name,'P_CALLER : '||P_CALLER);
8279: END IF;
8280: --
8281: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
8282:
8283: SAVEPOINT before_trip_update;
8284:
8285: i := p_trip_id_tab.FIRST;
8334: IF l_cs_tripstops_tab.COUNT = 0 THEN
8335: FND_MESSAGE.SET_NAME('WSH','WSH_FTE_SEL_NO_STOPS');
8336: --FND_MESSAGE.SET_TOKEN('TRIPID',p_trip_id_tab(i));
8337: --FND_MSG_PUB.ADD;
8338: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8339: l_rg_trip := FALSE;
8340: WSH_UTIL_CORE.add_message(x_return_status);
8341: END IF;
8342:
8336: --FND_MESSAGE.SET_TOKEN('TRIPID',p_trip_id_tab(i));
8337: --FND_MSG_PUB.ADD;
8338: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8339: l_rg_trip := FALSE;
8340: WSH_UTIL_CORE.add_message(x_return_status);
8341: END IF;
8342:
8343: j:=l_cs_tripstops_tab.FIRST;
8344: IF j IS NOT NULL THEN
8394: -- organization_id is that of last stop / any delivery being dropped off at the last stop of the trip
8395:
8396: IF j IN (l_cs_tripstops_tab.FIRST,l_cs_tripstops_tab.LAST) THEN
8397:
8398: WSH_UTIL_CORE.get_org_from_location(
8399: p_location_id => nvl(l_cs_tripstops_tab(j).physical_location_id,l_cs_tripstops_tab(j).stop_location_id),
8400: x_organization_tab => l_organization_tab,
8401: x_return_status => l_return_status);
8402:
8456: l_tripdest_org_id := l_organization_id;
8457: l_ultimate_dropoff_loc_id := nvl(l_cs_tripstops_tab(j).physical_location_id,l_cs_tripstops_tab(j).stop_location_id);
8458: l_ultimate_dropoff_date := l_cs_tripstops_tab(j).planned_arrival_date;
8459:
8460: WSH_UTIL_CORE.get_customer_from_loc(
8461: p_location_id => nvl(l_cs_tripstops_tab(j).physical_location_id,l_cs_tripstops_tab(j).stop_location_id),
8462: x_customer_id_tab => l_customer_tab,
8463: x_return_status => l_return_status);
8464:
8507: IF l_customer_id IS NOT NULL THEN
8508: FND_MESSAGE.SET_NAME('WSH','WSH_LOCATION_NO_SITE');
8509: --FND_MESSAGE.Set_Token('LOCATION',l_ultimate_dropoff_loc_id);
8510: FND_MESSAGE.Set_Token('TRIPNAME',l_cs_trip_rec.trip_name);
8511: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8512: l_rg_trip := FALSE;
8513: ELSE
8514: l_customer_site_id := NULL;
8515: END IF;
8594: x_return_status => l_return_status);
8595:
8596: -- Handle results (Error out if multileg)
8597:
8598: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
8599:
8600: IF l_debug_on THEN
8601: WSH_DEBUG_SV.logmsg(l_module_name,'l_cs_result_tab.COUNT is '|| l_cs_result_tab.COUNT);
8602: END IF;
8627: IF l_trip_id <> l_prev_trip_id THEN
8628: FND_MESSAGE.SET_NAME('WSH','WSH_FTE_CS_MULTILEG_TRIP');
8629: FND_MESSAGE.SET_TOKEN('TRIPID',l_trip_id);
8630: --FND_MSG_PUB.ADD;
8631: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8632: WSH_UTIL_CORE.add_message(x_return_status);
8633: END IF;
8634: ELSIF l_trip_result_type = 'RANK' THEN
8635: l_rank_result_cnt := l_rank_result_cnt + 1;
8628: FND_MESSAGE.SET_NAME('WSH','WSH_FTE_CS_MULTILEG_TRIP');
8629: FND_MESSAGE.SET_TOKEN('TRIPID',l_trip_id);
8630: --FND_MSG_PUB.ADD;
8631: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8632: WSH_UTIL_CORE.add_message(x_return_status);
8633: END IF;
8634: ELSIF l_trip_result_type = 'RANK' THEN
8635: l_rank_result_cnt := l_rank_result_cnt + 1;
8636: IF l_trip_id <> l_prev_trip_id THEN
8644: --FND_MSG_PUB.ADD;
8645: IF l_debug_on THEN
8646: WSH_DEBUG_SV.logmsg(l_module_name,'Trip already has a ranked list ');
8647: END IF;
8648: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8649: WSH_UTIL_CORE.add_message(x_return_status);
8650: END IF;
8651:
8652: --IF (l_cs_result_tab(rec_cnt).rank = 1) THEN
8645: IF l_debug_on THEN
8646: WSH_DEBUG_SV.logmsg(l_module_name,'Trip already has a ranked list ');
8647: END IF;
8648: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8649: WSH_UTIL_CORE.add_message(x_return_status);
8650: END IF;
8651:
8652: --IF (l_cs_result_tab(rec_cnt).rank = 1) THEN
8653: -- Has to update trip
8665: p_trip_id => l_trip_id,
8666: x_trip_info => l_trip_info_tab(1),
8667: x_return_status => l_return_status);
8668:
8669: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
8670: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
8671: raise FND_API.G_EXC_UNEXPECTED_ERROR;
8672: END IF;
8673:
8666: x_trip_info => l_trip_info_tab(1),
8667: x_return_status => l_return_status);
8668:
8669: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
8670: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
8671: raise FND_API.G_EXC_UNEXPECTED_ERROR;
8672: END IF;
8673:
8674: --l_trip_info_tab(1).trip_id := l_trip_id;
8695: p_trip_info_tab => l_trip_info_tab,
8696: p_in_rec => l_trip_in_rec,
8697: x_out_tab => l_trip_out_rec_tab);
8698:
8699: --IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
8700: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
8701: raise FND_API.G_EXC_UNEXPECTED_ERROR;
8702: END IF;
8703:
8696: p_in_rec => l_trip_in_rec,
8697: x_out_tab => l_trip_out_rec_tab);
8698:
8699: --IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
8700: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
8701: raise FND_API.G_EXC_UNEXPECTED_ERROR;
8702: END IF;
8703:
8704: --END IF;
8755: p_trip_id => l_trip_id,
8756: p_rank_id => l_rank_id
8757: --x_ranklist => l_ranked_list
8758: );
8759: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
8760: raise FND_API.G_EXC_UNEXPECTED_ERROR;
8761: END IF;
8762: l_ranked_list.DELETE;
8763: list_cnt := 0;
8775: --
8776: -- All results found are multileg, return an error
8777: --
8778: FND_MESSAGE.SET_NAME('WSH','WSH_FTE_CS_NO_RANKED_RESULT');
8779: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8780: WSH_UTIL_CORE.add_message(x_return_status);
8781: --
8782: -- Debug Statements
8783: --
8776: -- All results found are multileg, return an error
8777: --
8778: FND_MESSAGE.SET_NAME('WSH','WSH_FTE_CS_NO_RANKED_RESULT');
8779: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8780: WSH_UTIL_CORE.add_message(x_return_status);
8781: --
8782: -- Debug Statements
8783: --
8784: IF l_debug_on THEN
8791: --
8792: -- No results at all where found, return a warning
8793: --
8794: FND_MESSAGE.SET_NAME('WSH','WSH_FTE_CS_NO_CARRIER_SELECTED');
8795: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8796: WSH_UTIL_CORE.add_message(x_return_status);
8797: --
8798: -- Debug Statements
8799: --
8792: -- No results at all where found, return a warning
8793: --
8794: FND_MESSAGE.SET_NAME('WSH','WSH_FTE_CS_NO_CARRIER_SELECTED');
8795: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
8796: WSH_UTIL_CORE.add_message(x_return_status);
8797: --
8798: -- Debug Statements
8799: --
8800: IF l_debug_on THEN
8807: END IF;
8808: -- TODO
8809: ELSE -- No eligible trips were found
8810: FND_MESSAGE.SET_NAME('WSH','WSH_FTE_CS_NO_VALID_TRIPS');
8811: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8812: WSH_UTIL_CORE.add_message(x_return_status);
8813: --
8814: -- Debug Statements
8815: --
8808: -- TODO
8809: ELSE -- No eligible trips were found
8810: FND_MESSAGE.SET_NAME('WSH','WSH_FTE_CS_NO_VALID_TRIPS');
8811: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8812: WSH_UTIL_CORE.add_message(x_return_status);
8813: --
8814: -- Debug Statements
8815: --
8816: IF l_debug_on THEN
8837: EXCEPTION
8838: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8839:
8840: ROLLBACK TO before_trip_update;
8841: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
8842: --
8843: IF l_debug_on THEN
8844: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
8845: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
8869: CLOSE c_get_trip_cmove;
8870: END IF;
8871: */
8872:
8873: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.PROCESS_CARRIER_SELECTION');
8874: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8875:
8876: IF l_debug_on THEN
8877: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
8870: END IF;
8871: */
8872:
8873: wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.PROCESS_CARRIER_SELECTION');
8874: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
8875:
8876: IF l_debug_on THEN
8877: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
8878: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
8896:
8897:
8898:
8899: PROCEDURE Remove_Consolidation(
8900: p_trip_id_tab IN wsh_util_core.id_tab_type,
8901: p_unassign_all IN VARCHAR2,
8902: p_caller IN VARCHAR2,
8903: x_return_status OUT NOCOPY VARCHAR2) IS
8904:
8909: and l.pick_up_stop_id = s.stop_id
8910: and s.trip_id = p_trip_id
8911: order by d.delivery_type;
8912:
8913: l_delivery_tab wsh_util_core.id_tab_type;
8914: l_delivery_type_tab wsh_util_core.column_tab_type;
8915: l_consol_delivery_tab wsh_util_core.id_tab_type;
8916: l_content_delivery_tab wsh_util_core.id_tab_type;
8917: l_dummy_tab wsh_util_core.id_tab_type;
8910: and s.trip_id = p_trip_id
8911: order by d.delivery_type;
8912:
8913: l_delivery_tab wsh_util_core.id_tab_type;
8914: l_delivery_type_tab wsh_util_core.column_tab_type;
8915: l_consol_delivery_tab wsh_util_core.id_tab_type;
8916: l_content_delivery_tab wsh_util_core.id_tab_type;
8917: l_dummy_tab wsh_util_core.id_tab_type;
8918: j NUMBER := 0;
8911: order by d.delivery_type;
8912:
8913: l_delivery_tab wsh_util_core.id_tab_type;
8914: l_delivery_type_tab wsh_util_core.column_tab_type;
8915: l_consol_delivery_tab wsh_util_core.id_tab_type;
8916: l_content_delivery_tab wsh_util_core.id_tab_type;
8917: l_dummy_tab wsh_util_core.id_tab_type;
8918: j NUMBER := 0;
8919: k NUMBER := 0;
8912:
8913: l_delivery_tab wsh_util_core.id_tab_type;
8914: l_delivery_type_tab wsh_util_core.column_tab_type;
8915: l_consol_delivery_tab wsh_util_core.id_tab_type;
8916: l_content_delivery_tab wsh_util_core.id_tab_type;
8917: l_dummy_tab wsh_util_core.id_tab_type;
8918: j NUMBER := 0;
8919: k NUMBER := 0;
8920:
8913: l_delivery_tab wsh_util_core.id_tab_type;
8914: l_delivery_type_tab wsh_util_core.column_tab_type;
8915: l_consol_delivery_tab wsh_util_core.id_tab_type;
8916: l_content_delivery_tab wsh_util_core.id_tab_type;
8917: l_dummy_tab wsh_util_core.id_tab_type;
8918: j NUMBER := 0;
8919: k NUMBER := 0;
8920:
8921: l_num_warnings NUMBER := 0;
8982: p_caller => p_caller,
8983: p_del_tab => l_dummy_tab,
8984: x_return_status => l_return_status);
8985:
8986: wsh_util_core.api_post_call
8987: (
8988: p_return_status => l_return_status,
8989: x_num_warnings => l_num_warnings,
8990: x_num_errors => l_num_errors
9006: p_del_rows => l_delivery_tab,
9007: p_trip_id => p_trip_id_tab(i),
9008: x_return_status => l_return_status);
9009:
9010: wsh_util_core.api_post_call
9011: (
9012: p_return_status => l_return_status,
9013: x_num_warnings => l_num_warnings,
9014: x_num_errors => l_num_errors
9018: END LOOP;
9019:
9020: IF l_num_errors > 0
9021: THEN
9022: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9023: ELSIF l_num_warnings > 0
9024: THEN
9025: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
9026: ELSE
9021: THEN
9022: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9023: ELSIF l_num_warnings > 0
9024: THEN
9025: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
9026: ELSE
9027: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
9028: END IF;
9029: --
9023: ELSIF l_num_warnings > 0
9024: THEN
9025: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
9026: ELSE
9027: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
9028: END IF;
9029: --
9030: -- Debug Statements
9031: --
9033: WSH_DEBUG_SV.pop(l_module_name);
9034: END IF;
9035: EXCEPTION
9036: WHEN WSH_INVALID_ACTION THEN
9037: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9038: --
9039: -- Debug Statements
9040: FND_MESSAGE.SET_NAME('WSH','WSH_REMOVE_CONSOL_ERR');
9041: wsh_util_core.add_message(x_return_status);
9037: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9038: --
9039: -- Debug Statements
9040: FND_MESSAGE.SET_NAME('WSH','WSH_REMOVE_CONSOL_ERR');
9041: wsh_util_core.add_message(x_return_status);
9042: IF l_debug_on THEN
9043: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_ACTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
9044: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_ACTION');
9045: END IF;
9044: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_ACTION');
9045: END IF;
9046:
9047: WHEN FND_API.G_EXC_ERROR THEN
9048: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
9049: --
9050: -- Debug Statements
9051: --
9052: IF l_debug_on THEN
9053: WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
9054: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
9055: END IF;
9056: WHEN OTHERS THEN
9057: wsh_util_core.default_handler('wsh_trips_actions',l_module_name);
9058: --
9059: IF l_debug_on THEN
9060: wsh_debug_sv.pop(l_module_name, 'EXCEPTION:OTHERS');
9061: END IF;