[Home] [Help]
22: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
23: --
24: IF l_debug_on IS NULL
25: THEN
26: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
27: END IF;
28: --
29: -- Debug Statements
30: --
28: --
29: -- Debug Statements
30: --
31: IF l_debug_on THEN
32: WSH_DEBUG_SV.push(l_sub_module_name);
33: END IF;
34:
35: FOR i in 1..p_all_details.COUNT LOOP
36: --{
44: --}
45: END LOOP;
46:
47: IF l_debug_on THEN
48: WSH_DEBUG_SV.pop(l_sub_module_name);
49: END IF;
50:
51: EXCEPTION
52:
51: EXCEPTION
52:
53: WHEN FND_API.G_EXC_ERROR THEN
54: IF l_debug_on THEN
55: WSH_DEBUG_SV.log(l_sub_module_name,' FND_API.G_EXC_ERROR',sqlerrm);
56: WSH_DEBUG_SV.pop(l_sub_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
57: END IF;
58: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
59: IF l_debug_on THEN
52:
53: WHEN FND_API.G_EXC_ERROR THEN
54: IF l_debug_on THEN
55: WSH_DEBUG_SV.log(l_sub_module_name,' FND_API.G_EXC_ERROR',sqlerrm);
56: WSH_DEBUG_SV.pop(l_sub_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
57: END IF;
58: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
59: IF l_debug_on THEN
60: WSH_DEBUG_SV.log(l_sub_module_name,' FND_API.G_EXC_UNEXPECTED_ERROR',sqlerrm);
56: WSH_DEBUG_SV.pop(l_sub_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
57: END IF;
58: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
59: IF l_debug_on THEN
60: WSH_DEBUG_SV.log(l_sub_module_name,' FND_API.G_EXC_UNEXPECTED_ERROR',sqlerrm);
61: WSH_DEBUG_SV.pop(l_sub_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
62: END IF;
63: WHEN OTHERS THEN
64: wsh_util_core.default_handler('WSH_OTM_OUTBOUND.GET_DEL_DETAILS',l_sub_module_name);
57: END IF;
58: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
59: IF l_debug_on THEN
60: WSH_DEBUG_SV.log(l_sub_module_name,' FND_API.G_EXC_UNEXPECTED_ERROR',sqlerrm);
61: WSH_DEBUG_SV.pop(l_sub_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
62: END IF;
63: WHEN OTHERS THEN
64: wsh_util_core.default_handler('WSH_OTM_OUTBOUND.GET_DEL_DETAILS',l_sub_module_name);
65: IF l_debug_on THEN
62: END IF;
63: WHEN OTHERS THEN
64: wsh_util_core.default_handler('WSH_OTM_OUTBOUND.GET_DEL_DETAILS',l_sub_module_name);
65: IF l_debug_on THEN
66: WSH_DEBUG_SV.log(l_sub_module_name,' OTHERS',sqlerrm);
67: WSH_DEBUG_SV.pop(l_sub_module_name,'EXCEPTION:OTHERS');
68: END IF;
69: END;
70:
63: WHEN OTHERS THEN
64: wsh_util_core.default_handler('WSH_OTM_OUTBOUND.GET_DEL_DETAILS',l_sub_module_name);
65: IF l_debug_on THEN
66: WSH_DEBUG_SV.log(l_sub_module_name,' OTHERS',sqlerrm);
67: WSH_DEBUG_SV.pop(l_sub_module_name,'EXCEPTION:OTHERS');
68: END IF;
69: END;
70:
71:
193: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
194: --
195: IF l_debug_on IS NULL
196: THEN
197: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
198: END IF;
199: --
200: -- Debug Statements
201: --
199: --
200: -- Debug Statements
201: --
202: IF l_debug_on THEN
203: WSH_DEBUG_SV.push(l_sub_module_name);
204: END IF;
205:
206: -- Setting the apps context
207: FND_GLOBAL.apps_initialize(user_id => p_user_id,
208: resp_id =>p_resp_id,
209: resp_appl_id => p_resp_appl_id);
210:
211: IF l_debug_on THEN
212: WSH_DEBUG_SV.log(l_sub_module_name,'Building the dymanic sql to fetch Trips');
213: END IF;
214:
215: --dbms_output.put_line('Building the sql');
216: -- Bug 6732184: Added null values for dock_dook_xid, start_time and end_time of WSH_OTM_STOP_OBJ
327: l_trip_tab(l_trip_tab.COUNT).shipment_stops := WSH_OTM_STOP_TAB();
328:
329: IF l_debug_on
330: THEN
331: WSH_DEBUG_SV.log(l_sub_module_name,'Fetch Trip Id', l_trip_obj.trip_id);
332: END IF;
333:
334:
335: -- Get the OrganizationId of Trip
335: -- Get the OrganizationId of Trip
336: l_organization_id := WSH_UTIL_CORE.GET_TRIP_ORGANIZATION_ID(l_trip_obj.trip_id);
337: IF l_debug_on
338: THEN
339: WSH_DEBUG_SV.log(l_sub_module_name,' Organization Id after calling GET_TRIP_ORGANIZATION_ID ' ,
340: l_organization_id);
341: END IF;
342: --If Vehicle_organization_id is null then take the trip Organization_id
343: IF l_trip_tab(l_trip_tab.COUNT).EQUIPMENT_GROUP_XID IS NULL THEN
350: IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS then
351: --{
352: -- Bug 6497991: Deleting the trip record before raising exception
353: IF l_debug_on THEN
354: WSH_DEBUG_SV.log(l_sub_module_name,'failed in get_default_uoms' ,x_return_status);
355: END IF;
356: l_del_trip_rec := l_trip_tab(l_trip_tab.COUNT).trip_id;
357: x_error_trip_id_tab.extend;
358: x_error_trip_id_tab(x_error_trip_id_tab.COUNT) := l_trip_tab(l_trip_tab.COUNT).trip_id;
368: IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
369: (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
370: --{
371: IF l_debug_on THEN
372: WSH_DEBUG_SV.log(l_sub_module_name,'Get_EBS_To_OTM_UOM Weight Failed');
373: END IF;
374: -- Bug 6497991: Deleting the trip record before raising exception
375: l_del_trip_rec := l_trip_tab(l_trip_tab.COUNT).trip_id;
376: x_error_trip_id_tab.extend;
387:
388: IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
389: (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
390: IF l_debug_on THEN
391: WSH_DEBUG_SV.log(l_sub_module_name,'Get_EBS_To_OTM_UOM Weight Failed');
392: END IF;
393: -- Bug 6497991: Deleting the trip record before raising exception
394: --l_trip_tab.delete(l_trip_tab.COUNT);
395: l_del_trip_rec := l_trip_tab(l_trip_tab.COUNT).trip_id;
442: EXCEPTION
443: WHEN GET_DEAFULT_UOMS_FALIED THEN
444: l_num_error := l_num_error + 1;
445: IF l_debug_on THEN
446: WSH_DEBUG_SV.log(l_sub_module_name,' GET_DEAFULT_UOMS_FALIED',sqlerrm);
447: WSH_DEBUG_SV.pop(l_sub_module_name,'EXCEPTION:GET_DEAFULT_UOMS_FALIED');
448: END IF;
449:
450: WHEN FND_API.G_EXC_ERROR THEN
443: WHEN GET_DEAFULT_UOMS_FALIED THEN
444: l_num_error := l_num_error + 1;
445: IF l_debug_on THEN
446: WSH_DEBUG_SV.log(l_sub_module_name,' GET_DEAFULT_UOMS_FALIED',sqlerrm);
447: WSH_DEBUG_SV.pop(l_sub_module_name,'EXCEPTION:GET_DEAFULT_UOMS_FALIED');
448: END IF;
449:
450: WHEN FND_API.G_EXC_ERROR THEN
451: l_num_error := l_num_error + 1;
449:
450: WHEN FND_API.G_EXC_ERROR THEN
451: l_num_error := l_num_error + 1;
452: IF l_debug_on THEN
453: WSH_DEBUG_SV.log(l_sub_module_name,' FND_API.G_EXC_ERROR',sqlerrm);
454: WSH_DEBUG_SV.pop(l_sub_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
455: END IF;
456:
457: END;
450: WHEN FND_API.G_EXC_ERROR THEN
451: l_num_error := l_num_error + 1;
452: IF l_debug_on THEN
453: WSH_DEBUG_SV.log(l_sub_module_name,' FND_API.G_EXC_ERROR',sqlerrm);
454: WSH_DEBUG_SV.pop(l_sub_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
455: END IF;
456:
457: END;
458: --}
458: --}
459: END LOOP;
460:
461: IF l_debug_on THEN
462: WSH_DEBUG_SV.log(l_sub_module_name,'Number of Trips Fetched' , l_trip_tab.COUNT);
463: FOR i in 1..l_trip_tab.COUNT LOOP
464: WSH_DEBUG_SV.log(l_sub_module_name,'Fetched Trips' , l_trip_tab(i).trip_id);
465: END LOOP;
466: END IF;
460:
461: IF l_debug_on THEN
462: WSH_DEBUG_SV.log(l_sub_module_name,'Number of Trips Fetched' , l_trip_tab.COUNT);
463: FOR i in 1..l_trip_tab.COUNT LOOP
464: WSH_DEBUG_SV.log(l_sub_module_name,'Fetched Trips' , l_trip_tab(i).trip_id);
465: END LOOP;
466: END IF;
467:
468: IF l_trip_tab.COUNT >0 THEN
473: IF l_trip_tab.EXISTS(i) THEN
474: BEGIN
475: --{
476: IF l_debug_on THEN
477: WSH_DEBUG_SV.log(l_sub_module_name,'Processing Trip with Id ' , l_trip_tab(i).trip_id);
478: END IF;
479:
480: l_dlv_ids := WSH_OTM_ID_TAB();
481: l_stops_tab := l_trip_tab(i).shipment_stops;
518: IF l_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
519: --{
520: -- Bug 6497991: Deleting the trip record before raising exception
521: IF l_debug_on THEN
522: WSH_DEBUG_SV.log(l_sub_module_name,'Get_Trip_Manual_Freight_Cost Failed');
523: END IF;
524: x_error_trip_id_tab.extend;
525: x_error_trip_id_tab(x_error_trip_id_tab.COUNT) := l_trip_tab(i).trip_id;
526: l_trip_tab.delete(i);
538: FOR i1 in 1..l_stops_tab.COUNT LOOP
539: --{
540:
541: IF l_debug_on THEN
542: WSH_DEBUG_SV.log(l_sub_module_name,'Processing Stop with Id ' , l_stops_tab(i1).STOP_LOCATION_XID);
543: END IF;
544:
545: l_stop_details := WSH_OTM_STOP_DET_TAB();
546:
551: --{
552: fetch get_deliveries_picked into l_delivery_id;
553: EXIT WHEN (get_deliveries_picked%NOTFOUND);
554: IF l_debug_on THEN
555: WSH_DEBUG_SV.log(l_sub_module_name,'Delivery being Picked up ' , l_delivery_id);
556: END IF;
557: l_dlv_ids.extend;
558: l_dlv_ids(l_dlv_ids.COUNT) := l_delivery_id;
559: -- Pick Up Stop
568: --{
569: fetch get_deliveries_dropped into l_delivery_id;
570: EXIT WHEN (get_deliveries_dropped%NOTFOUND);
571: IF l_debug_on THEN
572: WSH_DEBUG_SV.log(l_sub_module_name,'Delivery being Dropped off ' , l_delivery_id);
573: END IF;
574:
575: -- Drop Off Stop
576: l_drop_off_flag := 'Y';
603:
604: CLOSE get_deliveries_picked;
605: CLOSE get_deliveries_dropped;
606: IF l_debug_on THEN
607: WSH_DEBUG_SV.log(l_sub_module_name,'Assigning the stop Details');
608: END IF;
609: l_stops_tab(i1).stop_details := l_stop_details;
610:
611: l_stops_tab(i1).STOP_LOCATION_XID := WSH_OTM_REF_DATA_GEN_PKG.GET_STOP_LOCATION_XID(l_stops_tab(i1).STOP_LOCATION_XID);
615:
616:
617:
618: IF l_debug_on THEN
619: WSH_DEBUG_SV.log(l_sub_module_name,'List of delivery Ids passed to GET_DELIVERY_OBJECTS');
620: END IF;
621: FOR i in 1..l_dlv_ids.COUNT LOOP
622: --{
623: IF l_debug_on THEN
620: END IF;
621: FOR i in 1..l_dlv_ids.COUNT LOOP
622: --{
623: IF l_debug_on THEN
624: WSH_DEBUG_SV.log(l_sub_module_name,'dlv_ids' ,l_dlv_ids(i));
625: END IF;
626: --}
627: END LOOP;
628:
634:
635: IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS then
636: --{
637: IF l_debug_on THEN
638: WSH_DEBUG_SV.log(l_sub_module_name,'failed in Get_Delivery_objects' ,x_return_status);
639: END IF;
640: x_error_trip_id_tab.extend;
641: x_error_trip_id_tab(x_error_trip_id_tab.COUNT) := l_trip_tab(i).trip_id;
642: l_trip_tab.delete(i);
693: EXCEPTION
694: WHEN GET_FREIGHT_COST_ERROR THEN
695: l_num_error := l_num_error + 1;
696: IF l_debug_on THEN
697: WSH_DEBUG_SV.log(l_sub_module_name,' GET_FREIGHT_COST_ERROR',sqlerrm);
698: WSH_DEBUG_SV.pop(l_sub_module_name,'EXCEPTION:GET_FREIGHT_COST_ERROR');
699: END IF;
700:
701: WHEN GET_DELIVERY_OBJECTS_FALIED THEN
694: WHEN GET_FREIGHT_COST_ERROR THEN
695: l_num_error := l_num_error + 1;
696: IF l_debug_on THEN
697: WSH_DEBUG_SV.log(l_sub_module_name,' GET_FREIGHT_COST_ERROR',sqlerrm);
698: WSH_DEBUG_SV.pop(l_sub_module_name,'EXCEPTION:GET_FREIGHT_COST_ERROR');
699: END IF;
700:
701: WHEN GET_DELIVERY_OBJECTS_FALIED THEN
702: l_num_error := l_num_error + 1;
700:
701: WHEN GET_DELIVERY_OBJECTS_FALIED THEN
702: l_num_error := l_num_error + 1;
703: IF l_debug_on THEN
704: WSH_DEBUG_SV.log(l_sub_module_name,' GET_DELIVERY_OBJECTS_FALIED',sqlerrm);
705: WSH_DEBUG_SV.pop(l_sub_module_name,'EXCEPTION:GET_DELIVERY_OBJECTS_FALIED');
706: END IF;
707:
708: END;
701: WHEN GET_DELIVERY_OBJECTS_FALIED THEN
702: l_num_error := l_num_error + 1;
703: IF l_debug_on THEN
704: WSH_DEBUG_SV.log(l_sub_module_name,' GET_DELIVERY_OBJECTS_FALIED',sqlerrm);
705: WSH_DEBUG_SV.pop(l_sub_module_name,'EXCEPTION:GET_DELIVERY_OBJECTS_FALIED');
706: END IF;
707:
708: END;
709: END IF;
746: --
747: -- Bug 6497991(begin): for error trips, update tms_interface_flag back to 'ASR'
748: --
749: IF l_debug_on THEN
750: WSH_DEBUG_SV.log(l_sub_module_name,'ERROR TRIP ids Count',x_error_trip_id_tab.count);
751: END IF;
752: IF ( x_error_trip_id_tab.count > 0 ) THEN
753: --{
754: i := x_error_trip_id_tab.first;
759: CLOSE get_trip_stops;
760: IF l_upd_trip_stops.COUNT > 0 THEN
761: --{
762: IF l_debug_on THEN
763: WSH_DEBUG_SV.log(l_sub_module_name,'Calling WSH_TRIP_STOPS_PVT.UPDATE_TMS_INTERFACE_FLAG Total stops ids',l_upd_trip_stops.COUNT);
764: END IF;
765: --
766: WSH_TRIP_STOPS_PVT.UPDATE_TMS_INTERFACE_FLAG
767: (P_STOP_ID_TAB=>l_upd_trip_stops,
768: P_TMS_INTERFACE_FLAG_TAB =>l_upd_tms_interface_flags,
769: X_RETURN_STATUS =>l_return_status);
770: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS then
771: IF l_debug_on THEN
772: WSH_DEBUG_SV.log(l_sub_module_name,'failed in UPDATE_TMS_INTERFACE_FLAG');
773: END IF;
774: raise FND_API.G_EXC_ERROR;
775: END IF;
776: --}
784: --
785: -- Debug Statements
786: --
787: IF l_debug_on THEN
788: WSH_DEBUG_SV.pop(l_sub_module_name);
789: END IF;
790: --
791: EXCEPTION
792: -- Bug 6497991: Defined exceptions to be handled inside the LOOP
799: IF get_deliveries_dropped%ISOPEN THEN
800: CLOSE get_deliveries_dropped;
801: END IF;
802: IF l_debug_on THEN
803: WSH_DEBUG_SV.log(l_sub_module_name,' OTHERS',sqlerrm);
804: WSH_DEBUG_SV.pop(l_sub_module_name,'EXCEPTION:OTHERS');
805: END IF;
806: END GET_TRIP_OBJECTS;
807:
800: CLOSE get_deliveries_dropped;
801: END IF;
802: IF l_debug_on THEN
803: WSH_DEBUG_SV.log(l_sub_module_name,' OTHERS',sqlerrm);
804: WSH_DEBUG_SV.pop(l_sub_module_name,'EXCEPTION:OTHERS');
805: END IF;
806: END GET_TRIP_OBJECTS;
807:
808:
971: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
972: --
973: IF l_debug_on IS NULL
974: THEN
975: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
976: END IF;
977: --
978: -- Debug Statements
979: --
977: --
978: -- Debug Statements
979: --
980: IF l_debug_on THEN
981: WSH_DEBUG_SV.push(l_sub_module_name);
982: END IF;
983:
984: IF l_debug_on THEN
985: FOR i in 1..p_dlv_id_tab.COUNT LOOP
982: END IF;
983:
984: IF l_debug_on THEN
985: FOR i in 1..p_dlv_id_tab.COUNT LOOP
986: WSH_DEBUG_SV.log(l_sub_module_name,'Dlv Id : ' , p_dlv_id_tab(i));
987: END LOOP;
988: WSH_DEBUG_SV.log(l_sub_module_name,'p_user_id' , p_user_id);
989: WSH_DEBUG_SV.log(l_sub_module_name,'p_resp_id' , p_resp_id);
990: WSH_DEBUG_SV.log(l_sub_module_name,'p_resp_appl_id' , p_resp_appl_id);
984: IF l_debug_on THEN
985: FOR i in 1..p_dlv_id_tab.COUNT LOOP
986: WSH_DEBUG_SV.log(l_sub_module_name,'Dlv Id : ' , p_dlv_id_tab(i));
987: END LOOP;
988: WSH_DEBUG_SV.log(l_sub_module_name,'p_user_id' , p_user_id);
989: WSH_DEBUG_SV.log(l_sub_module_name,'p_resp_id' , p_resp_id);
990: WSH_DEBUG_SV.log(l_sub_module_name,'p_resp_appl_id' , p_resp_appl_id);
991: WSH_DEBUG_SV.log(l_sub_module_name,'p_caller' , p_caller);
992: END IF ;
985: FOR i in 1..p_dlv_id_tab.COUNT LOOP
986: WSH_DEBUG_SV.log(l_sub_module_name,'Dlv Id : ' , p_dlv_id_tab(i));
987: END LOOP;
988: WSH_DEBUG_SV.log(l_sub_module_name,'p_user_id' , p_user_id);
989: WSH_DEBUG_SV.log(l_sub_module_name,'p_resp_id' , p_resp_id);
990: WSH_DEBUG_SV.log(l_sub_module_name,'p_resp_appl_id' , p_resp_appl_id);
991: WSH_DEBUG_SV.log(l_sub_module_name,'p_caller' , p_caller);
992: END IF ;
993: -- Bug 7207835 p_caller value not defaulting to 'D' when passed as NULL.
986: WSH_DEBUG_SV.log(l_sub_module_name,'Dlv Id : ' , p_dlv_id_tab(i));
987: END LOOP;
988: WSH_DEBUG_SV.log(l_sub_module_name,'p_user_id' , p_user_id);
989: WSH_DEBUG_SV.log(l_sub_module_name,'p_resp_id' , p_resp_id);
990: WSH_DEBUG_SV.log(l_sub_module_name,'p_resp_appl_id' , p_resp_appl_id);
991: WSH_DEBUG_SV.log(l_sub_module_name,'p_caller' , p_caller);
992: END IF ;
993: -- Bug 7207835 p_caller value not defaulting to 'D' when passed as NULL.
994: l_p_caller := NVL(p_caller,'D');
987: END LOOP;
988: WSH_DEBUG_SV.log(l_sub_module_name,'p_user_id' , p_user_id);
989: WSH_DEBUG_SV.log(l_sub_module_name,'p_resp_id' , p_resp_id);
990: WSH_DEBUG_SV.log(l_sub_module_name,'p_resp_appl_id' , p_resp_appl_id);
991: WSH_DEBUG_SV.log(l_sub_module_name,'p_caller' , p_caller);
992: END IF ;
993: -- Bug 7207835 p_caller value not defaulting to 'D' when passed as NULL.
994: l_p_caller := NVL(p_caller,'D');
995: -- End Bug 7207835
1005: fnd_profile.get('WSH_OTM_PASSWORD',x_otm_pwd);
1006: x_server_tz_code := FND_TIMEZONES.get_server_timezone_code();
1007:
1008: IF l_debug_on THEN
1009: WSH_DEBUG_SV.log(l_sub_module_name,'Building the dymanic sql to fetch Deliveries');
1010: END IF;
1011:
1012: l_deliveries_sql :=
1013: 'select '
1108: --}
1109: END LOOP;
1110:
1111: IF l_debug_on THEN
1112: WSH_DEBUG_SV.log(l_sub_module_name,'count of deliveries ' , l_dlv_tab.COUNT);
1113: END IF;
1114:
1115:
1116: IF l_dlv_tab.COUNT >0 THEN
1189: IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
1190: (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
1191: IF l_debug_on
1192: THEN
1193: WSH_DEBUG_SV.log(l_sub_module_name,'Get_EBS_To_OTM_UOM Weight Failed');
1194: END IF;
1195: raise FND_API.G_EXC_ERROR;
1196: END IF;
1197:
1202: IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
1203: (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
1204: IF l_debug_on
1205: THEN
1206: WSH_DEBUG_SV.log(l_sub_module_name,'Get_EBS_To_OTM_UOM Weight Failed');
1207: END IF;
1208: raise FND_API.G_EXC_ERROR;
1209: END IF;
1210:
1247: IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
1248: (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
1249: IF l_debug_on
1250: THEN
1251: WSH_DEBUG_SV.log(l_sub_module_name,'Get_EBS_To_OTM_UOM Weight Failed');
1252: END IF;
1253: raise FND_API.G_EXC_ERROR;
1254: END IF;
1255:
1260: IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
1261: (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
1262: IF l_debug_on
1263: THEN
1264: WSH_DEBUG_SV.log(l_sub_module_name,'Get_EBS_To_OTM_UOM Weight Failed');
1265: END IF;
1266: raise FND_API.G_EXC_ERROR;
1267: END IF;
1268:
1307: l_organization_id);
1308: fetch get_container_details into l_cont_type, l_length, l_height, l_width, l_uom;
1309: close get_container_details;
1310: IF l_debug_on THEN
1311: WSH_DEBUG_SV.log(l_sub_module_name,'Calling Get_EBS_To_OTM_UOM Weight for Loose Item : '||substr(l_inventory_item,instr(l_inventory_item,'-') + 1));
1312: END IF;
1313: WSH_OTM_RIQ_XML.Get_EBS_To_OTM_UOM(
1314: p_uom=>l_uom,
1315: x_uom=>l_otm_dimen_uom ,
1317: IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
1318: (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
1319: IF l_debug_on
1320: THEN
1321: WSH_DEBUG_SV.log(l_sub_module_name,'Get_EBS_To_OTM_UOM Weight Failed for Loose Item');
1322: END IF;
1323: raise FND_API.G_EXC_ERROR;
1324: END IF;
1325:
1376: IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
1377: (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
1378: IF l_debug_on
1379: THEN
1380: WSH_DEBUG_SV.log(l_sub_module_name,'Get_EBS_To_OTM_UOM Weight Failed');
1381: END IF;
1382: raise FND_API.G_EXC_ERROR;
1383: END IF;
1384:
1434: WHEN GET_DEAFULT_UOMS_FAILED THEN
1435: -- 6830854
1436: IF l_debug_on
1437: THEN
1438: WSH_DEBUG_SV.log(l_sub_module_name,'GET_DEAFULT_UOMS_FALIED',sqlerrm);
1439: WSH_DEBUG_SV.log(l_sub_module_name,'Delivery id: '||l_delivery_id);
1440: END IF;
1441:
1442: WHEN CONVERT_INT_LOC_FALIED THEN
1435: -- 6830854
1436: IF l_debug_on
1437: THEN
1438: WSH_DEBUG_SV.log(l_sub_module_name,'GET_DEAFULT_UOMS_FALIED',sqlerrm);
1439: WSH_DEBUG_SV.log(l_sub_module_name,'Delivery id: '||l_delivery_id);
1440: END IF;
1441:
1442: WHEN CONVERT_INT_LOC_FALIED THEN
1443: -- 6830854
1442: WHEN CONVERT_INT_LOC_FALIED THEN
1443: -- 6830854
1444: IF l_debug_on
1445: THEN
1446: WSH_DEBUG_SV.log(l_sub_module_name,'CONVERT_INT_LOC_FALIED',sqlerrm);
1447: WSH_DEBUG_SV.log(l_sub_module_name,'Delivery id: '||l_delivery_id);
1448: END IF;
1449:
1450: WHEN FND_API.G_EXC_ERROR THEN
1443: -- 6830854
1444: IF l_debug_on
1445: THEN
1446: WSH_DEBUG_SV.log(l_sub_module_name,'CONVERT_INT_LOC_FALIED',sqlerrm);
1447: WSH_DEBUG_SV.log(l_sub_module_name,'Delivery id: '||l_delivery_id);
1448: END IF;
1449:
1450: WHEN FND_API.G_EXC_ERROR THEN
1451: -- 6830854
1450: WHEN FND_API.G_EXC_ERROR THEN
1451: -- 6830854
1452: IF l_debug_on
1453: THEN
1454: WSH_DEBUG_SV.log(l_sub_module_name,' FND_API.G_EXC_ERROR',sqlerrm);
1455: WSH_DEBUG_SV.log(l_sub_module_name,'Delivery id: '||l_delivery_id);
1456: END IF;
1457:
1458: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1451: -- 6830854
1452: IF l_debug_on
1453: THEN
1454: WSH_DEBUG_SV.log(l_sub_module_name,' FND_API.G_EXC_ERROR',sqlerrm);
1455: WSH_DEBUG_SV.log(l_sub_module_name,'Delivery id: '||l_delivery_id);
1456: END IF;
1457:
1458: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1459: -- 6830854
1458: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1459: -- 6830854
1460: IF l_debug_on
1461: THEN
1462: WSH_DEBUG_SV.log(l_sub_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR',sqlerrm);
1463: WSH_DEBUG_SV.log(l_sub_module_name,'Delivery id: '||l_delivery_id);
1464: END IF;
1465:
1466: WHEN OTHERS THEN
1459: -- 6830854
1460: IF l_debug_on
1461: THEN
1462: WSH_DEBUG_SV.log(l_sub_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR',sqlerrm);
1463: WSH_DEBUG_SV.log(l_sub_module_name,'Delivery id: '||l_delivery_id);
1464: END IF;
1465:
1466: WHEN OTHERS THEN
1467: wsh_util_core.default_handler('WSH_OTM_OUTBOUND.GET_DELIVERY_OBJECTS',l_sub_module_name);
1468: -- 6922924 Note : Removed all ref: to x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1469: -- in this Exception and above Exceptions
1470: IF l_debug_on
1471: THEN
1472: WSH_DEBUG_SV.log(l_sub_module_name,'WHEN OTHERS inside Get Delivery Loop',sqlerrm);
1473: WSH_DEBUG_SV.log(l_sub_module_name,'Delivery id: '||l_delivery_id);
1474: END IF;
1475: END;
1476:
1469: -- in this Exception and above Exceptions
1470: IF l_debug_on
1471: THEN
1472: WSH_DEBUG_SV.log(l_sub_module_name,'WHEN OTHERS inside Get Delivery Loop',sqlerrm);
1473: WSH_DEBUG_SV.log(l_sub_module_name,'Delivery id: '||l_delivery_id);
1474: END IF;
1475: END;
1476:
1477: -- 6922924
1505: IF l_found = FALSE THEN
1506: l_error_dlv_id_tab.extend;
1507: l_error_dlv_id_tab(l_error_dlv_id_tab.COUNT) := p_dlv_id_tab(i);
1508: IF l_debug_on THEN
1509: WSH_DEBUG_SV.log(l_sub_module_name,'Errored Delivery id: ' , p_dlv_id_tab(i) );
1510: WSH_DEBUG_SV.log(l_sub_module_name,'Count of i' , i);
1511: END IF;
1512: END IF;
1513: --}
1506: l_error_dlv_id_tab.extend;
1507: l_error_dlv_id_tab(l_error_dlv_id_tab.COUNT) := p_dlv_id_tab(i);
1508: IF l_debug_on THEN
1509: WSH_DEBUG_SV.log(l_sub_module_name,'Errored Delivery id: ' , p_dlv_id_tab(i) );
1510: WSH_DEBUG_SV.log(l_sub_module_name,'Count of i' , i);
1511: END IF;
1512: END IF;
1513: --}
1514: END LOOP;
1524: --6922924
1525: -- Update the tms_interface_flag to 'CR' and Print the List of Errored Delivery Ids
1526: IF ( x_error_dlv_id_tab.count > 0 ) THEN
1527: IF l_debug_on THEN
1528: WSH_DEBUG_SV.log(l_sub_module_name,'ERROR DELIVERY ids Count',x_error_dlv_id_tab.count);
1529: END IF;
1530: --{
1531: i := x_error_dlv_id_tab.first;
1532: WHILE (i is not NULL) LOOP
1536: CLOSE get_errored_dlvys;
1537: IF l_upd_err_dlvys.COUNT > 0 THEN
1538: --{
1539: IF l_debug_on THEN
1540: WSH_DEBUG_SV.log(l_sub_module_name,'Calling WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG');
1541: END IF;
1542: --
1543: WSH_NEW_DELIVERIES_PVT.Update_Tms_interface_flag
1544: ( p_delivery_id_tab => l_upd_err_dlvys,
1546: x_return_status => l_return_status );
1547: --
1548: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS then
1549: IF l_debug_on THEN
1550: WSH_DEBUG_SV.log(l_sub_module_name,'failed in WND_PVT.UPDATE_TMS_INTERFACE_FLAG');
1551: END IF;
1552: -- 6830854 raise FND_API.G_EXC_ERROR;
1553: -- removing the above as Error can occur in Validation again
1554: END IF;
1565: IF l_debug_on THEN
1566: --{
1567: FOR k in 1..x_dlv_tab.COUNT LOOP
1568: --{
1569: WSH_DEBUG_SV.log(l_sub_module_name,'dlv_id' , x_dlv_tab(k).delivery_id);
1570: FOR i in 1..x_dlv_tab(k).rl_details.COUNT LOOP
1571: WSH_DEBUG_SV.log(l_sub_module_name,'DD ' , x_dlv_tab(k).rl_details(i).delivery_detail_id);
1572: END LOOP;
1573: WSH_DEBUG_SV.log(l_sub_module_name,'Ship Units Count' , x_dlv_tab(k).lpn.COUNT);
1567: FOR k in 1..x_dlv_tab.COUNT LOOP
1568: --{
1569: WSH_DEBUG_SV.log(l_sub_module_name,'dlv_id' , x_dlv_tab(k).delivery_id);
1570: FOR i in 1..x_dlv_tab(k).rl_details.COUNT LOOP
1571: WSH_DEBUG_SV.log(l_sub_module_name,'DD ' , x_dlv_tab(k).rl_details(i).delivery_detail_id);
1572: END LOOP;
1573: WSH_DEBUG_SV.log(l_sub_module_name,'Ship Units Count' , x_dlv_tab(k).lpn.COUNT);
1574: FOR i in 1..x_dlv_tab(k).lpn.COUNT LOOP
1575: --{
1569: WSH_DEBUG_SV.log(l_sub_module_name,'dlv_id' , x_dlv_tab(k).delivery_id);
1570: FOR i in 1..x_dlv_tab(k).rl_details.COUNT LOOP
1571: WSH_DEBUG_SV.log(l_sub_module_name,'DD ' , x_dlv_tab(k).rl_details(i).delivery_detail_id);
1572: END LOOP;
1573: WSH_DEBUG_SV.log(l_sub_module_name,'Ship Units Count' , x_dlv_tab(k).lpn.COUNT);
1574: FOR i in 1..x_dlv_tab(k).lpn.COUNT LOOP
1575: --{
1576: WSH_DEBUG_SV.log(l_sub_module_name,'Lpn Id ' , x_dlv_tab(k).lpn(i).lpn_id);
1577: FOR j in 1..x_dlv_tab(k).lpn(i).packed_items.COUNT LOOP
1572: END LOOP;
1573: WSH_DEBUG_SV.log(l_sub_module_name,'Ship Units Count' , x_dlv_tab(k).lpn.COUNT);
1574: FOR i in 1..x_dlv_tab(k).lpn.COUNT LOOP
1575: --{
1576: WSH_DEBUG_SV.log(l_sub_module_name,'Lpn Id ' , x_dlv_tab(k).lpn(i).lpn_id);
1577: FOR j in 1..x_dlv_tab(k).lpn(i).packed_items.COUNT LOOP
1578: WSH_DEBUG_SV.log(l_sub_module_name,'Content ' ,x_dlv_tab(k).lpn(i).packed_items(j).content_id);
1579: WSH_DEBUG_SV.log(l_sub_module_name,'Content ' ,x_dlv_tab(k).lpn(i).packed_items(j).line_number);
1580: END LOOP;
1574: FOR i in 1..x_dlv_tab(k).lpn.COUNT LOOP
1575: --{
1576: WSH_DEBUG_SV.log(l_sub_module_name,'Lpn Id ' , x_dlv_tab(k).lpn(i).lpn_id);
1577: FOR j in 1..x_dlv_tab(k).lpn(i).packed_items.COUNT LOOP
1578: WSH_DEBUG_SV.log(l_sub_module_name,'Content ' ,x_dlv_tab(k).lpn(i).packed_items(j).content_id);
1579: WSH_DEBUG_SV.log(l_sub_module_name,'Content ' ,x_dlv_tab(k).lpn(i).packed_items(j).line_number);
1580: END LOOP;
1581: --}
1582: END LOOP;
1575: --{
1576: WSH_DEBUG_SV.log(l_sub_module_name,'Lpn Id ' , x_dlv_tab(k).lpn(i).lpn_id);
1577: FOR j in 1..x_dlv_tab(k).lpn(i).packed_items.COUNT LOOP
1578: WSH_DEBUG_SV.log(l_sub_module_name,'Content ' ,x_dlv_tab(k).lpn(i).packed_items(j).content_id);
1579: WSH_DEBUG_SV.log(l_sub_module_name,'Content ' ,x_dlv_tab(k).lpn(i).packed_items(j).line_number);
1580: END LOOP;
1581: --}
1582: END LOOP;
1583: --}
1588: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1589: --
1590: ELSE
1591: IF l_debug_on THEN
1592: WSH_DEBUG_SV.log(l_sub_module_name,'Else error');
1593: END IF;
1594: x_error_dlv_id_tab := p_dlv_id_tab;
1595: p_dlv_id_tab := l_dlv_id_tab;
1596: x_dlv_tab := l_dlv_tab;
1600: --
1601: -- Debug Statements
1602: --
1603: IF l_debug_on THEN
1604: WSH_DEBUG_SV.pop(l_sub_module_name);
1605: END IF;
1606: --
1607: EXCEPTION
1608: -- 6922924 removeing all Exceptions to Inside the Loop as in above
1615: IF get_container_details%ISOPEN THEN
1616: CLOSE get_container_details;
1617: END IF;
1618: IF l_debug_on THEN
1619: WSH_DEBUG_SV.pop(l_sub_module_name,'When Others');
1620: END IF;
1621: end GET_DELIVERY_OBJECTS;
1622:
1623:
1723: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1724: --
1725: IF l_debug_on IS NULL
1726: THEN
1727: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1728: END IF;
1729: --
1730: -- initialize Apps variables
1731: IF p_userid is NOT NULL THEN
1739: --
1740: IF l_debug_on THEN
1741: --{
1742: fnd_profile.get('WSH_DEBUG_LOG_DIRECTORY',l_debugfile);
1743: l_debugfile := l_debugfile||'/'||WSH_DEBUG_SV.g_file;
1744:
1745: WSH_DEBUG_SV.push(l_sub_module_name);
1746: WSH_DEBUG_SV.log(l_sub_module_name,'Begin of the process ',l_debugfile);
1747: WSH_DEBUG_SV.log(l_sub_module_name,'p_entity_type ',p_entity_type);
1741: --{
1742: fnd_profile.get('WSH_DEBUG_LOG_DIRECTORY',l_debugfile);
1743: l_debugfile := l_debugfile||'/'||WSH_DEBUG_SV.g_file;
1744:
1745: WSH_DEBUG_SV.push(l_sub_module_name);
1746: WSH_DEBUG_SV.log(l_sub_module_name,'Begin of the process ',l_debugfile);
1747: WSH_DEBUG_SV.log(l_sub_module_name,'p_entity_type ',p_entity_type);
1748: WSH_DEBUG_SV.log(l_sub_module_name,'p_new_intf_status ',p_new_intf_status);
1749: --}
1742: fnd_profile.get('WSH_DEBUG_LOG_DIRECTORY',l_debugfile);
1743: l_debugfile := l_debugfile||'/'||WSH_DEBUG_SV.g_file;
1744:
1745: WSH_DEBUG_SV.push(l_sub_module_name);
1746: WSH_DEBUG_SV.log(l_sub_module_name,'Begin of the process ',l_debugfile);
1747: WSH_DEBUG_SV.log(l_sub_module_name,'p_entity_type ',p_entity_type);
1748: WSH_DEBUG_SV.log(l_sub_module_name,'p_new_intf_status ',p_new_intf_status);
1749: --}
1750: END IF;
1743: l_debugfile := l_debugfile||'/'||WSH_DEBUG_SV.g_file;
1744:
1745: WSH_DEBUG_SV.push(l_sub_module_name);
1746: WSH_DEBUG_SV.log(l_sub_module_name,'Begin of the process ',l_debugfile);
1747: WSH_DEBUG_SV.log(l_sub_module_name,'p_entity_type ',p_entity_type);
1748: WSH_DEBUG_SV.log(l_sub_module_name,'p_new_intf_status ',p_new_intf_status);
1749: --}
1750: END IF;
1751: --
1744:
1745: WSH_DEBUG_SV.push(l_sub_module_name);
1746: WSH_DEBUG_SV.log(l_sub_module_name,'Begin of the process ',l_debugfile);
1747: WSH_DEBUG_SV.log(l_sub_module_name,'p_entity_type ',p_entity_type);
1748: WSH_DEBUG_SV.log(l_sub_module_name,'p_new_intf_status ',p_new_intf_status);
1749: --}
1750: END IF;
1751: --
1752: --Validations
1751: --
1752: --Validations
1753: IF p_new_intf_status NOT IN ('IN_PROCESS','COMPLETE') THEN
1754: IF l_debug_on THEN
1755: WSH_DEBUG_SV.log(l_sub_module_name,'Invalid p_new_intf_status',p_new_intf_status);
1756: END IF;
1757: RAISE INVALID_NEW_INTF_STATUS;
1758: END IF;
1759: -- General Process
1772: k := 0;
1773: IF p_entity_type = 'DELIVERY' THEN
1774: --{
1775: IF l_debug_on THEN
1776: WSH_DEBUG_SV.log(l_sub_module_name,'Processing Delivery Count',p_entity_id_tab.COUNT);
1777: END IF;
1778: --
1779: FOR i IN 1..p_entity_id_tab.COUNT
1780: LOOP
1783: fetch get_del_tms_interface_flag into l_del_current_status,l_del_status_code,l_ignore_for_planning;
1784: --
1785: IF get_del_tms_interface_flag%NOTFOUND then
1786: IF l_debug_on THEN
1787: WSH_DEBUG_SV.log(l_sub_module_name,'Processing Delivery Not found',p_entity_id_tab(i));
1788: END IF;
1789: -- Move delivery id to error table - p_error_id_tab
1790: l_del_id_error_flag := 'Y';
1791: END IF;
1793: close get_del_tms_interface_flag;
1794: --
1795: IF p_new_intf_status = 'IN_PROCESS' then
1796: IF l_debug_on THEN
1797: WSH_DEBUG_SV.log(l_sub_module_name,'Processing Delivery - Status',p_new_intf_status);
1798: END IF;
1799: --{
1800: -- Query the delivery for the given delivery id list and
1801: -- if the current interface flag is not in 'CR,UR,DR',
1802: -- no update required, move the delivery id to error id table.
1803: --
1804: IF l_del_current_status = 'CR' then
1805: IF l_debug_on THEN
1806: WSH_DEBUG_SV.log(l_sub_module_name,'value of k1 '|| k);
1807: END IF;
1808: l_status_tab(k) := 'CP' ;
1809: l_id_tab(k) := p_entity_id_tab(i);
1810: k := k+ 1;
1820: -- no change to the status since the delivery is in process
1821: null;
1822: else
1823: IF l_debug_on THEN
1824: WSH_DEBUG_SV.log(l_sub_module_name,'Processing Delivery - Error ',p_entity_id_tab(i));
1825: END IF;
1826: -- set l_del_id_error_flag to "YES" to move this del-id to error table.
1827: l_del_id_error_flag := 'Y';
1828: end if;
1851: -- check if the delivery is empty or not. If it not empty, set the status to "CR"
1852: l_is_delivery_empty := WSH_NEW_DELIVERY_ACTIONS.IS_DELIVERY_EMPTY(p_entity_id_tab(i));
1853: IF (l_is_delivery_empty = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
1854: IF l_debug_on THEN
1855: WSH_DEBUG_SV.log(l_sub_module_name,'Error from wsh_new_delivery_actions.is_delivery_empty API for Delivery '||p_entity_id_tab(i));
1856: END IF;
1857: -- Setting this delivery to Error List since it is failed to process
1858: l_del_id_error_flag := 'Y';
1859: END IF;
1878: IF l_del_id_error_flag = 'Y' THEN
1879: --{
1880: -- Move delivery id to error table - p_error_id_tab
1881: IF l_debug_on THEN
1882: WSH_DEBUG_SV.log(l_sub_module_name,'Processing Delivery ID-Moving to error table ');
1883: END IF;
1884: l_error_id_out_tab.extend;
1885: l_error_id_out_tab(l_error_id_out_tab.COUNT):=p_entity_id_tab(i);
1886: --
1886: --
1887: --}
1888: ELSE
1889: IF l_debug_on THEN
1890: WSH_DEBUG_SV.log(l_sub_module_name,'Processing Delivery ID-Success to table ',p_entity_id_tab(i));
1891: END IF;
1892:
1893: -- Move delivery id to success table - l_entity_id_out_tab
1894: l_entity_id_out_tab.extend;
1897: --
1898: END LOOP;
1899: IF l_id_tab.COUNT > 0 THEN
1900: IF l_debug_on THEN
1901: WSH_DEBUG_SV.log(l_sub_module_name,'Processing Delivery Calling WSH API to update status-Del-Count',l_id_tab.COUNT);
1902: END IF;
1903: --{
1904: --Call WSH API to update the new status
1905: WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG
1909: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS then
1910: raise UPD_DEL_INTF_FLAG_API_FALIED;
1911: END IF;
1912: IF l_debug_on THEN
1913: WSH_DEBUG_SV.log(l_sub_module_name,'Processing Delivery Calling WSH API to update status-Success-Del-Count',l_id_tab.COUNT);
1914: END IF;
1915: --}
1916: END IF;
1917: --}
1927: -- Otherwise no change.
1928: -- if there is no trip stops for the give trip id, move the trip id to error id table.
1929: k := 0;
1930: IF l_debug_on THEN
1931: WSH_DEBUG_SV.log(l_sub_module_name,'Trip Count ',p_entity_id_tab.COUNT);
1932: END IF;
1933: --{
1934: FOR i IN 1..p_entity_id_tab.COUNT
1935: LOOP
1934: FOR i IN 1..p_entity_id_tab.COUNT
1935: LOOP
1936: --{
1937: IF l_debug_on THEN
1938: WSH_DEBUG_SV.log(l_sub_module_name,'Trip Stop ID ',p_entity_id_tab(i));
1939: END IF;
1940: OPEN get_trip_stops(p_entity_id_tab(i));
1941: FETCH get_trip_stops BULK COLLECT into l_stop_id_tab,l_stop_status_tab;
1942: CLOSE get_trip_stops;
1941: FETCH get_trip_stops BULK COLLECT into l_stop_id_tab,l_stop_status_tab;
1942: CLOSE get_trip_stops;
1943: --
1944: IF l_debug_on THEN
1945: WSH_DEBUG_SV.log(l_sub_module_name,'Trip Stps count ',l_stop_id_tab.COUNT);
1946: END IF;
1947: IF l_stop_id_tab.COUNT > 0 THEN
1948: --{
1949: j := l_stop_id_tab.FIRST;
1950: WHILE j IS NOT NULL
1951: LOOP
1952: --{
1953: IF l_debug_on THEN
1954: WSH_DEBUG_SV.log(l_sub_module_name,'Processing Trip STOP ID ',l_stop_id_tab(j) ||'-'||l_stop_status_tab(j));
1955: END IF;
1956: --
1957: IF p_new_intf_status = 'IN_PROCESS' THEN
1958: --
1958: --
1959: --IF l_stop_status_tab(j) = 'ASR' then
1960: l_status_tab(k) := 'ASP' ;
1961: IF l_debug_on THEN
1962: WSH_DEBUG_SV.log(l_sub_module_name,'Processing Trip STOP ID -New Status ',l_status_tab(k));
1963: END IF;
1964: l_id_tab(k) := l_stop_id_tab(j);
1965: k := k + 1;
1966: --end if ;
1969: --
1970: --IF l_stop_status_tab(j) = 'ASP' then
1971: l_status_tab(k) := 'CMP';
1972: IF l_debug_on THEN
1973: WSH_DEBUG_SV.log(l_sub_module_name,'Processing Trip STOP ID -New Status ',l_status_tab(k));
1974: END IF;
1975: l_id_tab(k) := l_stop_id_tab(j);
1976: k := k + 1;
1977: --END IF;
1987: --}
1988: ELSE
1989: --{
1990: IF l_debug_on THEN
1991: WSH_DEBUG_SV.log(l_sub_module_name,'Moving to Error table for Trip id',p_entity_id_tab(i));
1992: END IF;
1993: -- Move the Trip Id into error table
1994: l_error_id_out_tab.extend;
1995: l_error_id_out_tab(l_error_id_out_tab.COUNT):=p_entity_id_tab(i);
2000: --Call WSH API to update the new status in Trip Stops
2001: IF l_id_tab.COUNT > 0 THEN
2002: --{
2003: IF l_debug_on THEN
2004: WSH_DEBUG_SV.log(l_sub_module_name,'Calling WSH API-UPDATE_TMS_INTERFACE_FLAG Total stops ids',l_id_tab.COUNT);
2005: END IF;
2006: --
2007: WSH_TRIP_STOPS_PVT.UPDATE_TMS_INTERFACE_FLAG
2008: (P_STOP_ID_TAB=>l_id_tab,
2011: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS then
2012: raise UPD_STOP_INTF_FLAG_API_FALIED;
2013: END IF;
2014: IF l_debug_on THEN
2015: WSH_DEBUG_SV.log(l_sub_module_name,'Processing Trip Calling WSH API to update status-Success-Trip-Stops-Count',l_id_tab.COUNT);
2016: END IF;
2017: --}
2018: END IF;
2019: --}
2024: -- store the error deliveries/trips tp_error_id_tab table
2025: p_entity_id_tab := l_entity_id_out_tab;
2026: p_error_id_tab := l_error_id_out_tab;
2027: IF l_debug_on THEN
2028: WSH_DEBUG_SV.log(l_sub_module_name,'End of Process - Delivery/Trip Success',p_entity_id_tab.COUNT);
2029: WSH_DEBUG_SV.log(l_sub_module_name,'End of Process - Delivery/Trip Error',p_error_id_tab.COUNT);
2030: END IF;
2031: --
2032: EXCEPTION
2025: p_entity_id_tab := l_entity_id_out_tab;
2026: p_error_id_tab := l_error_id_out_tab;
2027: IF l_debug_on THEN
2028: WSH_DEBUG_SV.log(l_sub_module_name,'End of Process - Delivery/Trip Success',p_entity_id_tab.COUNT);
2029: WSH_DEBUG_SV.log(l_sub_module_name,'End of Process - Delivery/Trip Error',p_error_id_tab.COUNT);
2030: END IF;
2031: --
2032: EXCEPTION
2033: WHEN UPD_DEL_INTF_FLAG_API_FALIED THEN
2031: --
2032: EXCEPTION
2033: WHEN UPD_DEL_INTF_FLAG_API_FALIED THEN
2034: IF l_debug_on THEN
2035: WSH_DEBUG_SV.log(l_sub_module_name,'UPD_DEL_INTF_FLAG_API_FALIED',sqlerrm);
2036: END IF;
2037: ROLLBACK TO UPDATE_ENTITY_INTF_STATUS;
2038: -- returning all entitiy id to error id table
2039: p_error_id_tab := p_entity_id_tab;
2039: p_error_id_tab := p_entity_id_tab;
2040: x_return_status := FND_API.G_RET_STS_ERROR;
2041: WHEN UPD_STOP_INTF_FLAG_API_FALIED THEN
2042: IF l_debug_on THEN
2043: WSH_DEBUG_SV.log(l_sub_module_name,'UPD_STOP_INTF_FLAG_API_FALIED',sqlerrm);
2044: END IF;
2045: ROLLBACK TO UPDATE_ENTITY_INTF_STATUS;
2046: -- returning all entitiy id to error id table
2047: p_error_id_tab := p_entity_id_tab;
2047: p_error_id_tab := p_entity_id_tab;
2048: x_return_status := FND_API.G_RET_STS_ERROR;
2049: WHEN INVALID_ENTITY_TYPE THEN
2050: IF l_debug_on THEN
2051: WSH_DEBUG_SV.log(l_sub_module_name,'INVALID_ENTITY_TYPE',sqlerrm);
2052: END IF;
2053: ROLLBACK TO UPDATE_ENTITY_INTF_STATUS;
2054: -- returning all entitiy id to error id table
2055: p_error_id_tab := p_entity_id_tab;
2055: p_error_id_tab := p_entity_id_tab;
2056: x_return_status := FND_API.G_RET_STS_ERROR;
2057: WHEN INVALID_NEW_INTF_STATUS THEN
2058: IF l_debug_on THEN
2059: WSH_DEBUG_SV.log(l_sub_module_name,'INVALID_NEW_INTF_STATUS',sqlerrm);
2060: END IF;
2061: ROLLBACK TO UPDATE_ENTITY_INTF_STATUS;
2062: -- returning all entitiy id to error id table
2063: p_error_id_tab := p_entity_id_tab;
2063: p_error_id_tab := p_entity_id_tab;
2064: x_return_status := FND_API.G_RET_STS_ERROR;
2065: WHEN FND_API.G_EXC_ERROR THEN
2066: IF l_debug_on THEN
2067: WSH_DEBUG_SV.log(l_sub_module_name,' FND_API.G_EXC_ERROR',sqlerrm);
2068: END IF;
2069: ROLLBACK TO UPDATE_ENTITY_INTF_STATUS;
2070: -- returning all entitiy id to error id table
2071: p_error_id_tab := p_entity_id_tab;
2071: p_error_id_tab := p_entity_id_tab;
2072: x_return_status := FND_API.G_RET_STS_ERROR;
2073: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2074: IF l_debug_on THEN
2075: WSH_DEBUG_SV.log(l_sub_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR',sqlerrm);
2076: END IF;
2077: ROLLBACK TO UPDATE_ENTITY_INTF_STATUS;
2078: -- returning all entitiy id to error id table
2079: p_error_id_tab := p_entity_id_tab;
2079: p_error_id_tab := p_entity_id_tab;
2080: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2081: WHEN OTHERS then
2082: IF l_debug_on THEN
2083: WSH_DEBUG_SV.log(l_sub_module_name,'OTHERS - ERROR',sqlerrm);
2084: END IF;
2085: -- returning all entitiy id to error id table
2086: p_error_id_tab := p_entity_id_tab;
2087: x_return_status := FND_API.G_RET_STS_ERROR;