[Home] [Help]
145:
146: l_delivery_tab WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type;
147: l_delivery_addnl_attr_tab addnl_del_attr_tab_type;
148: l_sel_del_attr select_del_flags_rec_type;
149: l_failed_records WSH_UTIL_CORE.id_tab_type;
150: l_failed_index NUMBER;
151: l_failed_del_id NUMBER;
152: l_group_by_flags group_by_flags_rec_type;
153: l_hash_string VARCHAR2(1000);
160: l_unassign_trip_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
161: l_action_params WSH_DELIVERIES_GRP.action_parameters_rectype;
162: l_rating_in_param_rec WSH_FTE_INTEGRATION.rate_del_in_param_rec;
163: l_rating_out_param_rec WSH_FTE_INTEGRATION.rate_del_out_param_rec;
164: l_consol_trip_id WSH_UTIL_CORE.id_tab_type;
165: l_consol_del_id WSH_UTIL_CORE.id_tab_type;
166: l_del_all WSH_UTIL_CORE.id_tab_type;
167: l_trips_all WSH_UTIL_CORE.id_tab_type;
168: l_dummy_exception_list WSH_UTIL_CORE.Column_Tab_Type;
161: l_action_params WSH_DELIVERIES_GRP.action_parameters_rectype;
162: l_rating_in_param_rec WSH_FTE_INTEGRATION.rate_del_in_param_rec;
163: l_rating_out_param_rec WSH_FTE_INTEGRATION.rate_del_out_param_rec;
164: l_consol_trip_id WSH_UTIL_CORE.id_tab_type;
165: l_consol_del_id WSH_UTIL_CORE.id_tab_type;
166: l_del_all WSH_UTIL_CORE.id_tab_type;
167: l_trips_all WSH_UTIL_CORE.id_tab_type;
168: l_dummy_exception_list WSH_UTIL_CORE.Column_Tab_Type;
169: l_dummy_in_ids WSH_UTIL_CORE.id_tab_type;
162: l_rating_in_param_rec WSH_FTE_INTEGRATION.rate_del_in_param_rec;
163: l_rating_out_param_rec WSH_FTE_INTEGRATION.rate_del_out_param_rec;
164: l_consol_trip_id WSH_UTIL_CORE.id_tab_type;
165: l_consol_del_id WSH_UTIL_CORE.id_tab_type;
166: l_del_all WSH_UTIL_CORE.id_tab_type;
167: l_trips_all WSH_UTIL_CORE.id_tab_type;
168: l_dummy_exception_list WSH_UTIL_CORE.Column_Tab_Type;
169: l_dummy_in_ids WSH_UTIL_CORE.id_tab_type;
170: l_dummy_dlvy_asgnd_lines WSH_FTE_CONSTRAINT_FRAMEWORK.detail_ccinfo_tab_type;
163: l_rating_out_param_rec WSH_FTE_INTEGRATION.rate_del_out_param_rec;
164: l_consol_trip_id WSH_UTIL_CORE.id_tab_type;
165: l_consol_del_id WSH_UTIL_CORE.id_tab_type;
166: l_del_all WSH_UTIL_CORE.id_tab_type;
167: l_trips_all WSH_UTIL_CORE.id_tab_type;
168: l_dummy_exception_list WSH_UTIL_CORE.Column_Tab_Type;
169: l_dummy_in_ids WSH_UTIL_CORE.id_tab_type;
170: l_dummy_dlvy_asgnd_lines WSH_FTE_CONSTRAINT_FRAMEWORK.detail_ccinfo_tab_type;
171: l_dummy_tar_trip WSH_FTE_CONSTRAINT_FRAMEWORK.trip_ccinfo_rec_type;
164: l_consol_trip_id WSH_UTIL_CORE.id_tab_type;
165: l_consol_del_id WSH_UTIL_CORE.id_tab_type;
166: l_del_all WSH_UTIL_CORE.id_tab_type;
167: l_trips_all WSH_UTIL_CORE.id_tab_type;
168: l_dummy_exception_list WSH_UTIL_CORE.Column_Tab_Type;
169: l_dummy_in_ids WSH_UTIL_CORE.id_tab_type;
170: l_dummy_dlvy_asgnd_lines WSH_FTE_CONSTRAINT_FRAMEWORK.detail_ccinfo_tab_type;
171: l_dummy_tar_trip WSH_FTE_CONSTRAINT_FRAMEWORK.trip_ccinfo_rec_type;
172: l_dummy_tar_tstops WSH_FTE_CONSTRAINT_FRAMEWORK.target_tripstop_cc_rec_type;
165: l_consol_del_id WSH_UTIL_CORE.id_tab_type;
166: l_del_all WSH_UTIL_CORE.id_tab_type;
167: l_trips_all WSH_UTIL_CORE.id_tab_type;
168: l_dummy_exception_list WSH_UTIL_CORE.Column_Tab_Type;
169: l_dummy_in_ids WSH_UTIL_CORE.id_tab_type;
170: l_dummy_dlvy_asgnd_lines WSH_FTE_CONSTRAINT_FRAMEWORK.detail_ccinfo_tab_type;
171: l_dummy_tar_trip WSH_FTE_CONSTRAINT_FRAMEWORK.trip_ccinfo_rec_type;
172: l_dummy_tar_tstops WSH_FTE_CONSTRAINT_FRAMEWORK.target_tripstop_cc_rec_type;
173: l_dummy_tar_t_asgn_dels WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type;
226: WHERE ORGANIZATION_ID = c_org_id;
227:
228: BEGIN
229:
230: IF WSH_UTIL_CORE.G_START_OF_SESSION_API is null
231: THEN
232: WSH_UTIL_CORE.G_START_OF_SESSION_API := l_api_session_name;
233: END IF;
234:
228: BEGIN
229:
230: IF WSH_UTIL_CORE.G_START_OF_SESSION_API is null
231: THEN
232: WSH_UTIL_CORE.G_START_OF_SESSION_API := l_api_session_name;
233: END IF;
234:
235: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
236:
231: THEN
232: WSH_UTIL_CORE.G_START_OF_SESSION_API := l_api_session_name;
233: END IF;
234:
235: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
236:
237: IF p_log_level IS NOT NULL
238: THEN
239: WSH_UTIL_CORE.Set_Log_Level(p_log_level);
235: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
236:
237: IF p_log_level IS NOT NULL
238: THEN
239: WSH_UTIL_CORE.Set_Log_Level(p_log_level);
240: WSH_UTIL_CORE.PrintMsg('p_log_level is ' || to_char(p_log_level));
241: END IF;
242:
243: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
236:
237: IF p_log_level IS NOT NULL
238: THEN
239: WSH_UTIL_CORE.Set_Log_Level(p_log_level);
240: WSH_UTIL_CORE.PrintMsg('p_log_level is ' || to_char(p_log_level));
241: END IF;
242:
243: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
244: IF l_debug_on IS NULL
247: END IF;
248:
249: IF p_batch_id IS NULL
250: THEN
251: WSH_UTIL_CORE.PrintMsg('Batch Id is null');
252: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Schedule_Batch');
253: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
254: errbuf := 'Error occurred in WSH_MDC_SRS.Schedule_Batch';
255: retcode := '2';
248:
249: IF p_batch_id IS NULL
250: THEN
251: WSH_UTIL_CORE.PrintMsg('Batch Id is null');
252: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Schedule_Batch');
253: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
254: errbuf := 'Error occurred in WSH_MDC_SRS.Schedule_Batch';
255: retcode := '2';
256: raise FND_API.G_EXC_ERROR;
276: p_batch_id => p_batch_id,
277: x_sel_del_attr => l_sel_del_attr,
278: x_return_status => l_return_status);
279:
280: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
281: THEN
282: IF l_debug_on THEN
283: WSH_DEBUG_SV.logmsg(l_module_name, 'Got Batch Parameters' );
284: END IF;
281: THEN
282: IF l_debug_on THEN
283: WSH_DEBUG_SV.logmsg(l_module_name, 'Got Batch Parameters' );
284: END IF;
285: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
286: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
287: THEN
288: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Get_Batch_Parameters');
289: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
282: IF l_debug_on THEN
283: WSH_DEBUG_SV.logmsg(l_module_name, 'Got Batch Parameters' );
284: END IF;
285: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
286: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
287: THEN
288: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Get_Batch_Parameters');
289: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
290: errbuf := 'Error occurred in WSH_MDC_SRS.Get_Batch_Parameters';
284: END IF;
285: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
286: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
287: THEN
288: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Get_Batch_Parameters');
289: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
290: errbuf := 'Error occurred in WSH_MDC_SRS.Get_Batch_Parameters';
291: retcode := '2';
292: IF l_debug_on THEN
304: l_org_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_type(
305: p_organization_id => l_sel_del_attr.org_id,
306: x_return_status => l_return_status);
307:
308: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
309: THEN
310: IF l_debug_on THEN
311: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type returned with warnings');
312: END IF;
309: THEN
310: IF l_debug_on THEN
311: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type returned with warnings');
312: END IF;
313: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
314: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
315: THEN
316: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type');
317: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type');
310: IF l_debug_on THEN
311: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type returned with warnings');
312: END IF;
313: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
314: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
315: THEN
316: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type');
317: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type');
318: errbuf := 'Error occurred in WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type';
312: END IF;
313: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
314: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
315: THEN
316: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type');
317: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type');
318: errbuf := 'Error occurred in WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type';
319: retcode := '2';
320: IF l_debug_on THEN
328: END IF;
329:
330: IF l_org_type = 'TPW'
331: THEN
332: WSH_UTIL_CORE.PrintMsg('The Organization is TPW enabled. Quitting Consolidation.');
333: raise l_exc_complete;
334: END IF;
335:
336: -- set the organization cms enabled flag
358: x_delivery_tab => l_delivery_tab,
359: x_delivery_addnl_attr_tab => l_delivery_addnl_attr_tab,
360: x_return_status => l_return_status);
361:
362: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
363: THEN
364: IF l_debug_on THEN
365: WSH_DEBUG_SV.logmsg(l_module_name, 'Got Deliveries' );
366: END IF;
363: THEN
364: IF l_debug_on THEN
365: WSH_DEBUG_SV.logmsg(l_module_name, 'Got Deliveries' );
366: END IF;
367: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
368: THEN
369: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Get_Deliveries');
370: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
371: errbuf := 'Error occurred in WSH_MDC_SRS.Get_Deliveries';
365: WSH_DEBUG_SV.logmsg(l_module_name, 'Got Deliveries' );
366: END IF;
367: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
368: THEN
369: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Get_Deliveries');
370: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
371: errbuf := 'Error occurred in WSH_MDC_SRS.Get_Deliveries';
372: retcode := '2';
373: raise FND_API.G_EXC_ERROR;
370: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
371: errbuf := 'Error occurred in WSH_MDC_SRS.Get_Deliveries';
372: retcode := '2';
373: raise FND_API.G_EXC_ERROR;
374: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
375: THEN
376: WSH_UTIL_CORE.PrintMsg('No deliveries to consolidate');
377: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
378: errbuf := 'Error occurred in WSH_MDC_SRS.Get_Deliveries';
372: retcode := '2';
373: raise FND_API.G_EXC_ERROR;
374: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
375: THEN
376: WSH_UTIL_CORE.PrintMsg('No deliveries to consolidate');
377: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
378: errbuf := 'Error occurred in WSH_MDC_SRS.Get_Deliveries';
379: retcode := '2';
380: raise FND_API.G_EXC_ERROR;
381: END IF;
382:
383: IF l_delivery_tab.COUNT = 0
384: THEN
385: WSH_UTIL_CORE.PrintMsg('No deliveries selected for consolidation');
386: raise l_exc_complete;
387: ELSE
388: WSH_UTIL_CORE.PrintMsg('The following deliveries are selected for consolidation');
389: i := l_delivery_tab.FIRST;
384: THEN
385: WSH_UTIL_CORE.PrintMsg('No deliveries selected for consolidation');
386: raise l_exc_complete;
387: ELSE
388: WSH_UTIL_CORE.PrintMsg('The following deliveries are selected for consolidation');
389: i := l_delivery_tab.FIRST;
390: WHILE i IS NOT NULL
391: LOOP
392: WSH_UTIL_CORE.PrintMsg(' Delivery: '||l_delivery_tab(i).delivery_id);
388: WSH_UTIL_CORE.PrintMsg('The following deliveries are selected for consolidation');
389: i := l_delivery_tab.FIRST;
390: WHILE i IS NOT NULL
391: LOOP
392: WSH_UTIL_CORE.PrintMsg(' Delivery: '||l_delivery_tab(i).delivery_id);
393: i := l_delivery_tab.next(i);
394: END LOOP;
395: END IF;
396:
423: p_delivery_id => l_delivery_tab(i).delivery_id,
424: p_carrier_id => l_delivery_tab(i).carrier_id,
425: x_return_status => l_return_status
426: );
427: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
428: THEN
429: IF l_debug_on THEN
430: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type returned with warnings');
431: END IF;
428: THEN
429: IF l_debug_on THEN
430: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type returned with warnings');
431: END IF;
432: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
433: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
434: THEN
435: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type');
436: WSH_UTIL_CORE.PrintMsg('Failed to get the manifest information. Removing delivery '||l_delivery_tab(i).delivery_id||' from consolidation');
429: IF l_debug_on THEN
430: WSH_DEBUG_SV.logmsg(l_module_name,'WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type returned with warnings');
431: END IF;
432: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
433: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
434: THEN
435: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type');
436: WSH_UTIL_CORE.PrintMsg('Failed to get the manifest information. Removing delivery '||l_delivery_tab(i).delivery_id||' from consolidation');
437: l_completion_status := 'WARNING';
431: END IF;
432: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
433: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
434: THEN
435: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type');
436: WSH_UTIL_CORE.PrintMsg('Failed to get the manifest information. Removing delivery '||l_delivery_tab(i).delivery_id||' from consolidation');
437: l_completion_status := 'WARNING';
438: retcode := '1';
439:
432: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
433: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
434: THEN
435: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type');
436: WSH_UTIL_CORE.PrintMsg('Failed to get the manifest information. Removing delivery '||l_delivery_tab(i).delivery_id||' from consolidation');
437: l_completion_status := 'WARNING';
438: retcode := '1';
439:
440: l_failed_records(l_failed_index) := l_delivery_tab(i).delivery_id;
449:
450:
451: IF l_del_cms = 'CMS'
452: THEN
453: WSH_UTIL_CORE.PrintMsg('Delivery '||l_delivery_tab(i).delivery_id||' is carrier manifesting enabled. Removing from consolidation');
454:
455: k := l_delivery_tab.next(i);
456: l_delivery_tab.delete(i);
457: l_delivery_addnl_attr_tab.delete(i);
501: x_msg_count => l_msg_count,
502: x_msg_data => l_msg_data
503: );
504:
505: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
506: THEN
507: IF l_debug_on THEN
508: WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery '||l_delivery_tab(i).delivery_id||' unassigned successfully');
509: END IF;
506: THEN
507: IF l_debug_on THEN
508: WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery '||l_delivery_tab(i).delivery_id||' unassigned successfully');
509: END IF;
510: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
511: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
512: THEN
513: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_DELIVERIES_GRP.Delivery_Action');
514: FOR m in 1..l_msg_count
507: IF l_debug_on THEN
508: WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery '||l_delivery_tab(i).delivery_id||' unassigned successfully');
509: END IF;
510: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
511: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
512: THEN
513: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_DELIVERIES_GRP.Delivery_Action');
514: FOR m in 1..l_msg_count
515: LOOP
509: END IF;
510: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
511: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
512: THEN
513: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_DELIVERIES_GRP.Delivery_Action');
514: FOR m in 1..l_msg_count
515: LOOP
516: l_message := fnd_msg_pub.get(m,'F');
517: l_message := replace(l_message,chr(0),' ');
514: FOR m in 1..l_msg_count
515: LOOP
516: l_message := fnd_msg_pub.get(m,'F');
517: l_message := replace(l_message,chr(0),' ');
518: WSH_UTIL_CORE.PrintMsg(l_message);
519: IF l_debug_on THEN
520: WSH_DEBUG_SV.logmsg(l_module_name, l_message);
521: END IF;
522: END LOOP;
522: END LOOP;
523:
524: fnd_msg_pub.delete_msg();
525:
526: WSH_UTIL_CORE.PrintMsg('Failed to unassign from trip. Removing delivery '||l_delivery_tab(i).delivery_id||' from consolidation');
527: l_completion_status := 'WARNING';
528: retcode := '1';
529:
530: l_failed_records(l_failed_index) := l_delivery_tab(i).delivery_id;
535: l_failed_index := l_failed_index + 1;
536: GOTO LOOP_END;
537:
538:
539: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
540: THEN
541: IF l_debug_on THEN
542: WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery '||l_delivery_tab(i).delivery_id||' unassigned from trip with warnings');
543: FOR m in 1..l_msg_count
550: fnd_msg_pub.delete_msg();
551: END IF;
552:
553: ELSE
554: WSH_UTIL_CORE.PrintMsg('Delivery '||l_delivery_tab(i).delivery_id||' has mulitple legs. Removing from consolidation');
555:
556: k := l_delivery_tab.next(i);
557: l_delivery_tab.delete(i);
558: l_delivery_addnl_attr_tab.delete(i);
572: IF l_debug_on THEN
573: WSH_DEBUG_SV.logmsg(l_module_name,'Checking if FTE is installed');
574: END IF;
575:
576: IF WSH_UTIL_CORE.fte_is_installed = 'Y'
577: THEN
578: --{
579: -- group the deliveries based on constraints
580: WSH_UTIL_CORE.PrintMsg('Grouping deliveries based on constraints');
576: IF WSH_UTIL_CORE.fte_is_installed = 'Y'
577: THEN
578: --{
579: -- group the deliveries based on constraints
580: WSH_UTIL_CORE.PrintMsg('Grouping deliveries based on constraints');
581:
582: IF l_debug_on THEN
583: WSH_DEBUG_SV.logmsg(l_module_name,'FTE is installed');
584: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FTE_CONSTRAINT_FRAMEWORK.validate_constraint_dlvy',WSH_DEBUG_SV.C_PROC_LEVEL);
603: x_msg_count => l_msg_count,
604: x_msg_data => l_msg_data,
605: x_return_status => l_return_status);
606:
607: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
608: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
609: THEN
610: IF l_debug_on THEN
611: WSH_DEBUG_SV.logmsg(l_module_name, 'validate constraints call successful');
604: x_msg_data => l_msg_data,
605: x_return_status => l_return_status);
606:
607: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
608: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
609: THEN
610: IF l_debug_on THEN
611: WSH_DEBUG_SV.logmsg(l_module_name, 'validate constraints call successful');
612: WSH_DEBUG_SV.logmsg(l_module_name, 'Deliveries grouped based on constraints');
610: IF l_debug_on THEN
611: WSH_DEBUG_SV.logmsg(l_module_name, 'validate constraints call successful');
612: WSH_DEBUG_SV.logmsg(l_module_name, 'Deliveries grouped based on constraints');
613: END IF;
614: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
615: THEN
616: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_FTE_CONSTRAINT_FRAMEWORK.validate_constraint_dlvy');
617: FOR m in 1..l_msg_count
618: LOOP
612: WSH_DEBUG_SV.logmsg(l_module_name, 'Deliveries grouped based on constraints');
613: END IF;
614: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
615: THEN
616: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_FTE_CONSTRAINT_FRAMEWORK.validate_constraint_dlvy');
617: FOR m in 1..l_msg_count
618: LOOP
619: l_message := fnd_msg_pub.get(m,'F');
620: l_message := replace(l_message,chr(0),' ');
617: FOR m in 1..l_msg_count
618: LOOP
619: l_message := fnd_msg_pub.get(m,'F');
620: l_message := replace(l_message,chr(0),' ');
621: WSH_UTIL_CORE.PrintMsg(l_message);
622: IF l_debug_on THEN
623: WSH_DEBUG_SV.logmsg(l_module_name, l_message);
624: END IF;
625: END LOOP;
629: errbuf := 'Error occurred in WSH_FTE_CONSTRAINT_FRAMEWORK.validate_constraint_dlvy';
630: retcode := '2';
631:
632: raise FND_API.G_EXC_ERROR;
633: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
634: THEN
635: IF l_debug_on THEN
636: WSH_DEBUG_SV.logmsg(l_module_name, 'validate constraints call successful with following warnings');
637: FOR m in 1..l_msg_count
651: -- FOR i in 1 .. l_line_groups.COUNT
652: WHILE i IS NOT NULL
653: LOOP
654: l_delivery_addnl_attr_tab(l_line_groups(i).entity_line_id).group_id := l_line_groups(i).line_group_id;
655: WSH_UTIL_CORE.PrintMsg(' Delivery: '||l_line_groups(i).entity_line_id||', Group: '||l_line_groups(i).line_group_id);
656: i := l_line_groups.NEXT(i);
657: END LOOP;
658:
659: -- Remove failed records
671: l_delivery_tab.delete(l_failed_del_id);
672: l_delivery_addnl_attr_tab.delete(l_failed_del_id);
673: l_failed_index := l_failed_index + 1;
674: i := l_failed_lines.NEXT(i);
675: WSH_UTIL_CORE.PrintMsg('Delivery Id: '||l_failed_del_id||' failed constraint validation. Removing from consolidation');
676: --}
677: END LOOP;
678:
679: IF l_delivery_tab.COUNT = 0
677: END LOOP;
678:
679: IF l_delivery_tab.COUNT = 0
680: THEN
681: WSH_UTIL_CORE.PrintMsg('All deliveries failed validation. Terminating concurrent request');
682: raise l_exc_complete;
683: END IF;
684:
685: --} -- if fte is installed
695: p_grouping_rule_id => l_sel_del_attr.rule_id,
696: x_group_by_flags => l_group_by_flags,
697: x_return_status => l_return_status);
698:
699: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
700: THEN
701: IF l_debug_on THEN
702: WSH_DEBUG_SV.logmsg(l_module_name, 'Got Grouping Attrs' );
703: END IF;
700: THEN
701: IF l_debug_on THEN
702: WSH_DEBUG_SV.logmsg(l_module_name, 'Got Grouping Attrs' );
703: END IF;
704: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
705: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
706: THEN
707: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Get_Grouping_Attrs');
708: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
701: IF l_debug_on THEN
702: WSH_DEBUG_SV.logmsg(l_module_name, 'Got Grouping Attrs' );
703: END IF;
704: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
705: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
706: THEN
707: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Get_Grouping_Attrs');
708: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
709: errbuf := 'Error occurred in WSH_MDC_SRS.Get_Grouping_Attrs';
703: END IF;
704: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
705: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
706: THEN
707: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Get_Grouping_Attrs');
708: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
709: errbuf := 'Error occurred in WSH_MDC_SRS.Get_Grouping_Attrs';
710: retcode := '2';
711: raise FND_API.G_EXC_ERROR;
725: x_delivery_addnl_attr_tab => l_delivery_addnl_attr_tab,
726: x_failed_records => l_failed_records,
727: x_return_status => l_return_status);
728:
729: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
730: THEN
731: IF l_debug_on THEN
732: WSH_DEBUG_SV.logmsg(l_module_name, 'Set Intermediate call successful');
733: END IF;
730: THEN
731: IF l_debug_on THEN
732: WSH_DEBUG_SV.logmsg(l_module_name, 'Set Intermediate call successful');
733: END IF;
734: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
735: THEN
736: IF l_debug_on THEN
737: WSH_DEBUG_SV.logmsg(l_module_name, 'Set Intermediate Location call completed with warnings');
738: END IF;
737: WSH_DEBUG_SV.logmsg(l_module_name, 'Set Intermediate Location call completed with warnings');
738: END IF;
739: l_completion_status := 'WARNING';
740: retcode := '1';
741: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
742: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
743: THEN
744: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Set_Intermediate_Location');
745: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
738: END IF;
739: l_completion_status := 'WARNING';
740: retcode := '1';
741: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
742: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
743: THEN
744: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Set_Intermediate_Location');
745: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
746: errbuf := 'Error occurred in WSH_MDC_SRS.Set_Intermediate_Location';
740: retcode := '1';
741: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
742: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
743: THEN
744: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Set_Intermediate_Location');
745: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
746: errbuf := 'Error occurred in WSH_MDC_SRS.Set_Intermediate_Location';
747: retcode := '2';
748: raise FND_API.G_EXC_ERROR;
749: END IF;
750:
751: IF l_delivery_tab.COUNT = 0
752: THEN
753: WSH_UTIL_CORE.PrintMsg('All deliveries failed to get intermediate location. Terminating concurrent request');
754: raise l_exc_complete;
755: END IF;
756:
757:
754: raise l_exc_complete;
755: END IF;
756:
757:
758: WSH_UTIL_CORE.PrintMsg('Grouping deliveries based on the rule');
759:
760: WHILE(l_valid_hash <> TRUE)
761: LOOP
762: --{
789: x_hash_string => l_hash_string,
790: x_hash_value => l_hash_value,
791: x_return_status => l_return_status);
792:
793: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
794: THEN
795: IF l_debug_on THEN
796: WSH_DEBUG_SV.logmsg(l_module_name, 'Got Hash Value' );
797: END IF;
794: THEN
795: IF l_debug_on THEN
796: WSH_DEBUG_SV.logmsg(l_module_name, 'Got Hash Value' );
797: END IF;
798: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
799: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
800: THEN
801: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Get_Hash_Value');
802: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
795: IF l_debug_on THEN
796: WSH_DEBUG_SV.logmsg(l_module_name, 'Got Hash Value' );
797: END IF;
798: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
799: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
800: THEN
801: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Get_Hash_Value');
802: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
803: errbuf := 'Error occurred in WSH_MDC_SRS.Get_Hash_Value';
797: END IF;
798: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
799: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
800: THEN
801: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Get_Hash_Value');
802: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
803: errbuf := 'Error occurred in WSH_MDC_SRS.Get_Hash_Value';
804: retcode := '2';
805: raise FND_API.G_EXC_ERROR;
820: -- hash value not unique
821: IF l_debug_on THEN
822: WSH_DEBUG_SV.logmsg(l_module_name, 'Hash value not unique. Rebuilding hash table with bigger hash size');
823: END IF;
824: WSH_UTIL_CORE.PrintMsg('Hash conflict. Dropping and rebuilding rule based grouping.');
825: l_valid_hash := FALSE;
826: EXIT;
827: --}
828: END IF;
829: -- add delivery to existing group
830: IF l_debug_on THEN
831: WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery '||l_delivery_addnl_attr_tab(i).delivery_id||' added to existing hash group '||l_group_tab(l_group_index).group_id);
832: END IF;
833: WSH_UTIL_CORE.PrintMsg(' Delivery: '||l_delivery_addnl_attr_tab(i).delivery_id||', Hash Group: '||l_group_tab(l_group_index).group_id);
834: l_delivery_addnl_attr_tab(i).group_id := l_group_tab(l_group_index).group_id;
835: l_del_index := l_group_tab(l_group_index).delivery_list.LAST;
836: l_group_tab(l_group_index).delivery_list(l_del_index+1) := l_delivery_addnl_attr_tab(i).delivery_id;
837: --}
843: l_delivery_addnl_attr_tab(i).group_id := l_group_tab(l_group_index).group_id;
844: IF l_debug_on THEN
845: WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery '||l_delivery_addnl_attr_tab(i).delivery_id||' added to new hash group '||l_group_tab(l_group_index).group_id);
846: END IF;
847: WSH_UTIL_CORE.PrintMsg(' Delivery: '||l_delivery_addnl_attr_tab(i).delivery_id||', Hash Group: '||l_group_tab(l_group_index).group_id);
848: l_group_tab(l_group_index).delivery_list(0) := l_delivery_addnl_attr_tab(i).delivery_id;
849: --}
850: END IF;
851: --}
874: x_trips_all => l_trips_all,
875: x_failed_records => l_failed_records,
876: x_return_status => l_return_status);
877:
878: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
879: THEN
880: IF l_debug_on THEN
881: WSH_DEBUG_SV.logmsg(l_module_name, 'Create Consolidations Successful');
882: END IF;
879: THEN
880: IF l_debug_on THEN
881: WSH_DEBUG_SV.logmsg(l_module_name, 'Create Consolidations Successful');
882: END IF;
883: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
884: THEN
885: IF l_debug_on THEN
886: WSH_DEBUG_SV.logmsg(l_module_name, 'Create Consolidations completed with warnings');
887: END IF;
887: END IF;
888: l_completion_status := 'WARNING';
889: retcode := '1';
890:
891: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
892: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
893: THEN
894: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Create_Consolidations');
895: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
888: l_completion_status := 'WARNING';
889: retcode := '1';
890:
891: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
892: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
893: THEN
894: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Create_Consolidations');
895: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
896: errbuf := 'Error occurred in WSH_MDC_SRS.Create_Consolidations';
890:
891: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
892: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
893: THEN
894: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Create_Consolidations');
895: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
896: errbuf := 'Error occurred in WSH_MDC_SRS.Create_Consolidations';
897: retcode := '2';
898: raise FND_API.G_EXC_ERROR;
914: p_trip_name_prefix => l_sel_del_attr.trip_name_prefix,
915: x_trips_all => l_trips_all,
916: x_return_status => l_return_status);
917:
918: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
919: THEN
920: IF l_debug_on THEN
921: WSH_DEBUG_SV.logmsg(l_module_name, 'Create Deconsolidation Trips Successful');
922: END IF;
919: THEN
920: IF l_debug_on THEN
921: WSH_DEBUG_SV.logmsg(l_module_name, 'Create Deconsolidation Trips Successful');
922: END IF;
923: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
924: THEN
925: IF l_debug_on THEN
926: WSH_DEBUG_SV.logmsg(l_module_name, 'Create Deconsolidation Trips completed with warnings');
927: END IF;
926: WSH_DEBUG_SV.logmsg(l_module_name, 'Create Deconsolidation Trips completed with warnings');
927: END IF;
928: l_completion_status := 'WARNING';
929: retcode := '1';
930: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
931: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
932: THEN
933: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Create_Deconsol_Trips');
934: l_completion_status := 'WARNING';
927: END IF;
928: l_completion_status := 'WARNING';
929: retcode := '1';
930: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
931: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
932: THEN
933: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Create_Deconsol_Trips');
934: l_completion_status := 'WARNING';
935: retcode := '1';
929: retcode := '1';
930: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
931: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
932: THEN
933: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Create_Deconsol_Trips');
934: l_completion_status := 'WARNING';
935: retcode := '1';
936: END IF;
937:
941:
942: -- route trips
943: IF(l_sel_del_attr.route_trips_flag = 'Y')
944: THEN
945: WSH_UTIL_CORE.PrintMsg('Routing the trips created');
946: IF l_debug_on THEN
947: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_ACTIONS.PROCESS_CARRIER_SELECTION',WSH_DEBUG_SV.C_PROC_LEVEL);
948: END IF;
949: /*
969: x_return_status => l_return_status,
970: x_msg_count => l_msg_count,
971: x_msg_data => l_msg_data);
972:
973: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
974: THEN
975: IF l_debug_on THEN
976: WSH_DEBUG_SV.logmsg(l_module_name, 'Routing Trips Successful');
977: END IF;
974: THEN
975: IF l_debug_on THEN
976: WSH_DEBUG_SV.logmsg(l_module_name, 'Routing Trips Successful');
977: END IF;
978: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
979: THEN
980: IF l_debug_on THEN
981: WSH_DEBUG_SV.logmsg(l_module_name, 'Routing Trips completed with warnings');
982: END IF;
983: FOR m in 1..l_msg_count
984: LOOP
985: l_message := fnd_msg_pub.get(m,'F');
986: l_message := replace(l_message,chr(0),' ');
987: WSH_UTIL_CORE.PrintMsg(l_message);
988: IF l_debug_on THEN
989: WSH_DEBUG_SV.logmsg(l_module_name, l_message);
990: END IF;
991: END LOOP;
993: fnd_msg_pub.delete_msg();
994:
995: l_completion_status := 'WARNING';
996: retcode := '1';
997: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
998: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
999: THEN
1000: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_TRIPS_ACTIONS.PROCESS_CARRIER_SELECTION');
1001: FOR m in 1..l_msg_count
994:
995: l_completion_status := 'WARNING';
996: retcode := '1';
997: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
998: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
999: THEN
1000: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_TRIPS_ACTIONS.PROCESS_CARRIER_SELECTION');
1001: FOR m in 1..l_msg_count
1002: LOOP
996: retcode := '1';
997: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
998: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
999: THEN
1000: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_TRIPS_ACTIONS.PROCESS_CARRIER_SELECTION');
1001: FOR m in 1..l_msg_count
1002: LOOP
1003: l_message := fnd_msg_pub.get(m,'F');
1004: l_message := replace(l_message,chr(0),' ');
1001: FOR m in 1..l_msg_count
1002: LOOP
1003: l_message := fnd_msg_pub.get(m,'F');
1004: l_message := replace(l_message,chr(0),' ');
1005: WSH_UTIL_CORE.PrintMsg(l_message);
1006: IF l_debug_on THEN
1007: WSH_DEBUG_SV.logmsg(l_module_name, l_message);
1008: END IF;
1009: END LOOP;
1038: i := l_consol_del_id.NEXT(i);
1039: END LOOP;
1040:
1041:
1042: WSH_UTIL_CORE.PrintMsg('Rating Deliveries');
1043: IF l_debug_on THEN
1044: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FTE_INTEGRATION.Rate_Delivery',WSH_DEBUG_SV.C_PROC_LEVEL);
1045: END IF;
1046:
1056: x_return_status => l_return_status,
1057: x_msg_count => l_msg_count,
1058: x_msg_data => l_msg_data);
1059:
1060: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
1061: THEN
1062: IF l_debug_on THEN
1063: WSH_DEBUG_SV.logmsg(l_module_name, 'Rating Deliveries Successful');
1064: END IF;
1061: THEN
1062: IF l_debug_on THEN
1063: WSH_DEBUG_SV.logmsg(l_module_name, 'Rating Deliveries Successful');
1064: END IF;
1065: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
1066: THEN
1067: IF l_debug_on THEN
1068: WSH_DEBUG_SV.logmsg(l_module_name, 'Rating Deliveries completed with warnings');
1069: END IF;
1068: WSH_DEBUG_SV.logmsg(l_module_name, 'Rating Deliveries completed with warnings');
1069: END IF;
1070: l_completion_status := 'WARNING';
1071: retcode := '1';
1072: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
1073: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1074: THEN
1075: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_FTE_INTEGRATION.Rate_Delivery');
1076: FOR m in 1..l_msg_count
1069: END IF;
1070: l_completion_status := 'WARNING';
1071: retcode := '1';
1072: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
1073: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1074: THEN
1075: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_FTE_INTEGRATION.Rate_Delivery');
1076: FOR m in 1..l_msg_count
1077: LOOP
1071: retcode := '1';
1072: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
1073: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1074: THEN
1075: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_FTE_INTEGRATION.Rate_Delivery');
1076: FOR m in 1..l_msg_count
1077: LOOP
1078: l_message := fnd_msg_pub.get(m,'F');
1079: l_message := replace(l_message,chr(0),' ');
1076: FOR m in 1..l_msg_count
1077: LOOP
1078: l_message := fnd_msg_pub.get(m,'F');
1079: l_message := replace(l_message,chr(0),' ');
1080: WSH_UTIL_CORE.PrintMsg(l_message);
1081: END LOOP;
1082:
1083: fnd_msg_pub.delete_msg();
1084:
1090:
1091: IF l_completion_status = 'NORMAL'
1092: THEN
1093: retcode := '0';
1094: WSH_UTIL_CORE.PrintMsg('Delivery Consolidation completed successfully');
1095: IF l_debug_on THEN
1096: WSH_DEBUG_SV.logmsg(l_module_name, 'WSH_MDC_SRS.Schedule_Batch completed successfully');
1097: WSH_DEBUG_SV.logmsg(l_module_name, 'Current time is '||SYSDATE);
1098: END IF;
1098: END IF;
1099: ELSIF l_completion_status = 'WARNING'
1100: THEN
1101: retcode := '1';
1102: WSH_UTIL_CORE.PrintMsg('Delivery Consolidation completed with warnings');
1103: IF l_debug_on THEN
1104: WSH_DEBUG_SV.logmsg(l_module_name, 'WSH_MDC_SRS.Schedule_Batch completed with warnings');
1105: WSH_DEBUG_SV.logmsg(l_module_name, 'Current time is '||SYSDATE);
1106: END IF;
1114: EXCEPTION
1115: WHEN FND_API.G_EXC_ERROR
1116: THEN
1117: ROLLBACK;
1118: WSH_UTIL_CORE.PrintMsg('SQLCODE: '||sqlcode||' SQLERRM: '||sqlerrm);
1119: WSH_UTIL_CORE.PrintMsg('Exception occurred in SCHEDULE_BATCH');
1120: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
1121: IF errbuf IS NULL
1122: THEN
1115: WHEN FND_API.G_EXC_ERROR
1116: THEN
1117: ROLLBACK;
1118: WSH_UTIL_CORE.PrintMsg('SQLCODE: '||sqlcode||' SQLERRM: '||sqlerrm);
1119: WSH_UTIL_CORE.PrintMsg('Exception occurred in SCHEDULE_BATCH');
1120: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
1121: IF errbuf IS NULL
1122: THEN
1123: errbuf := 'Exception occurred in SCHEDULE_BATCH';
1130:
1131: WHEN l_exc_complete
1132: THEN
1133: retcode := '1';
1134: WSH_UTIL_CORE.PrintMsg('Delivery Consolidation completed successfully');
1135: IF l_debug_on THEN
1136: WSH_DEBUG_SV.logmsg(l_module_name, 'WSH_MDC_SRS.Schedule_Batch completed successfully');
1137: WSH_DEBUG_SV.logmsg(l_module_name, 'Current time is '||SYSDATE);
1138: END IF;
1143:
1144: WHEN OTHERS
1145: THEN
1146: ROLLBACK;
1147: WSH_UTIL_CORE.PrintMsg('SQLCODE: '||sqlcode||' SQLERRM: '||sqlerrm);
1148: WSH_UTIL_CORE.PrintMsg('Exception occurred in SCHEDULE_BATCH');
1149:
1150: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
1151: errbuf := 'Exception occurred in SCHEDULE_BATCH';
1144: WHEN OTHERS
1145: THEN
1146: ROLLBACK;
1147: WSH_UTIL_CORE.PrintMsg('SQLCODE: '||sqlcode||' SQLERRM: '||sqlerrm);
1148: WSH_UTIL_CORE.PrintMsg('Exception occurred in SCHEDULE_BATCH');
1149:
1150: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
1151: errbuf := 'Exception occurred in SCHEDULE_BATCH';
1152: retcode := '2';
1270:
1271: IF l_debug_on THEN
1272: WSH_DEBUG_SV.pop(l_module_name);
1273: END IF;
1274: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1275: EXCEPTION
1276: WHEN OTHERS
1277: THEN
1278: IF l_debug_on THEN
1278: IF l_debug_on THEN
1279: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1280: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1281: END IF;
1282: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1283: END Get_Batch_Parameters;
1284:
1285: --========================================================================
1286: -- PROCEDURE : Get_Deliveries
1304: l_select_clause varchar2(1000);
1305: l_from_clause varchar2(1000);
1306: l_where_clause varchar2(3000);
1307: l_query varchar2(5000);
1308: l_col_tab WSH_UTIL_CORE.tbl_varchar;
1309: l_col_count NUMBER;
1310: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DELIVERIES';
1311: l_debug_on BOOLEAN;
1312: l_wc_index NUMBER;
1311: l_debug_on BOOLEAN;
1312: l_wc_index NUMBER;
1313:
1314:
1315: c_deliveries WSH_UTIL_CORE.RefCurType;
1316: BEGIN
1317:
1318: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1319: IF l_debug_on IS NULL
1394: l_col_tab(l_col_count) := 'STANDARD';
1395: l_col_count := l_col_count+1;
1396:
1397:
1398: WSH_UTIL_CORE.PrintMsg('Delivery filtering criteria for the consolidation batch: ');
1399:
1400: IF p_sel_del_attr.delivery_name_from IS NOT NULL AND p_sel_del_attr.delivery_name_to IS NOT NULL
1401: THEN
1402: WSH_UTIL_CORE.PrintMsg(' Delivery name from: '||p_sel_del_attr.delivery_name_from);
1398: WSH_UTIL_CORE.PrintMsg('Delivery filtering criteria for the consolidation batch: ');
1399:
1400: IF p_sel_del_attr.delivery_name_from IS NOT NULL AND p_sel_del_attr.delivery_name_to IS NOT NULL
1401: THEN
1402: WSH_UTIL_CORE.PrintMsg(' Delivery name from: '||p_sel_del_attr.delivery_name_from);
1403: WSH_UTIL_CORE.PrintMsg(' Delivery name to: '||p_sel_del_attr.delivery_name_to);
1404:
1405: IF p_sel_del_attr.delivery_name_from = p_sel_del_attr.delivery_name_to
1406: THEN
1399:
1400: IF p_sel_del_attr.delivery_name_from IS NOT NULL AND p_sel_del_attr.delivery_name_to IS NOT NULL
1401: THEN
1402: WSH_UTIL_CORE.PrintMsg(' Delivery name from: '||p_sel_del_attr.delivery_name_from);
1403: WSH_UTIL_CORE.PrintMsg(' Delivery name to: '||p_sel_del_attr.delivery_name_to);
1404:
1405: IF p_sel_del_attr.delivery_name_from = p_sel_del_attr.delivery_name_to
1406: THEN
1407: SELECT INSTR(p_sel_del_attr.delivery_name_from,'%') INTO l_wc_index FROM DUAL;
1425: END IF;
1426:
1427: IF p_sel_del_attr.org_id IS NOT NULL
1428: THEN
1429: WSH_UTIL_CORE.PrintMsg(' Organization: '||p_sel_del_attr.org_id);
1430: l_where_clause := l_where_clause || ' AND WND.ORGANIZATION_ID = :org_id';
1431: l_col_tab(l_col_count) := p_sel_del_attr.org_id;
1432: l_col_count := l_col_count+1;
1433: END IF;
1433: END IF;
1434:
1435: IF p_sel_del_attr.pick_up_date_starts_within IS NOT NULL
1436: THEN
1437: WSH_UTIL_CORE.PrintMsg(' Pick-up date starts within: '||p_sel_del_attr.pick_up_date_starts_within);
1438: l_where_clause := l_where_clause || ' AND WND.INITIAL_PICKUP_DATE >= SYSDATE + :p_pick_up_start_date';
1439: l_col_tab(l_col_count) := p_sel_del_attr.pick_up_date_starts_within;
1440: l_col_count := l_col_count+1;
1441: END IF;
1441: END IF;
1442:
1443: IF p_sel_del_attr.pick_up_date_ends_within IS NOT NULL
1444: THEN
1445: WSH_UTIL_CORE.PrintMsg(' Pick-up date ends within: '||p_sel_del_attr.pick_up_date_ends_within);
1446: l_where_clause := l_where_clause || ' AND WND.INITIAL_PICKUP_DATE <= SYSDATE + :p_pick_up_end_date';
1447: l_col_tab(l_col_count) := p_sel_del_attr.pick_up_date_ends_within;
1448: l_col_count := l_col_count+1;
1449: END IF;
1449: END IF;
1450:
1451: IF p_sel_del_attr.drop_off_date_starts_within IS NOT NULL
1452: THEN
1453: WSH_UTIL_CORE.PrintMsg(' Drop-off date starts within: '||p_sel_del_attr.drop_off_date_starts_within);
1454: l_where_clause := l_where_clause || ' AND WND.ULTIMATE_DROPOFF_DATE >= SYSDATE + :p_drop_off_start_date';
1455: l_col_tab(l_col_count) := p_sel_del_attr.drop_off_date_starts_within;
1456: l_col_count := l_col_count+1;
1457: END IF;
1457: END IF;
1458:
1459: IF p_sel_del_attr.drop_off_date_ends_within IS NOT NULL
1460: THEN
1461: WSH_UTIL_CORE.PrintMsg(' Drop-off date ends within: '||p_sel_del_attr.drop_off_date_ends_within);
1462: l_where_clause := l_where_clause || ' AND WND.ULTIMATE_DROPOFF_DATE <= SYSDATE + :p_drop_off_end_date';
1463: l_col_tab(l_col_count) := p_sel_del_attr.drop_off_date_ends_within;
1464: l_col_count := l_col_count+1;
1465: END IF;
1465: END IF;
1466:
1467: IF p_sel_del_attr.pick_release_batch_id IS NOT NULL
1468: THEN
1469: WSH_UTIL_CORE.PrintMsg(' Pick-Release batch: '||p_sel_del_attr.pick_release_batch_id);
1470: l_where_clause := l_where_clause || ' AND WND.BATCH_ID = :p_batch_id';
1471: l_col_tab(l_col_count) := p_sel_del_attr.pick_release_batch_id;
1472: l_col_count := l_col_count+1;
1473: END IF;
1473: END IF;
1474:
1475: IF p_sel_del_attr.fob_code IS NOT NULL
1476: THEN
1477: WSH_UTIL_CORE.PrintMsg(' FOB Code: '||p_sel_del_attr.fob_code);
1478: l_where_clause := l_where_clause || ' AND WND.FOB_CODE = :p_fob_code';
1479: l_col_tab(l_col_count) := p_sel_del_attr.fob_code;
1480: l_col_count := l_col_count+1;
1481: END IF;
1481: END IF;
1482:
1483: IF p_sel_del_attr.freight_terms_code IS NOT NULL
1484: THEN
1485: WSH_UTIL_CORE.PrintMsg(' Freight Terms Code: '||p_sel_del_attr.freight_terms_code);
1486: l_where_clause := l_where_clause || ' AND WND.FREIGHT_TERMS_CODE = :p_freight_terms_code';
1487: l_col_tab(l_col_count) := p_sel_del_attr.freight_terms_code;
1488: l_col_count := l_col_count+1;
1489: END IF;
1489: END IF;
1490:
1491: IF p_sel_del_attr.carrier_id IS NOT NULL
1492: THEN
1493: WSH_UTIL_CORE.PrintMsg(' Carrier ID: '||p_sel_del_attr.carrier_id);
1494: l_where_clause := l_where_clause || ' AND WND.CARRIER_ID = :p_carrier_id';
1495: l_col_tab(l_col_count) := p_sel_del_attr.carrier_id;
1496: l_col_count := l_col_count+1;
1497: END IF;
1497: END IF;
1498:
1499: IF p_sel_del_attr.mode_of_transport IS NOT NULL
1500: THEN
1501: WSH_UTIL_CORE.PrintMsg(' Mode of Transport: '||p_sel_del_attr.mode_of_transport);
1502: l_where_clause := l_where_clause || ' AND WND.MODE_OF_TRANSPORT = :p_mode_of_transport';
1503: l_col_tab(l_col_count) := p_sel_del_attr.mode_of_transport;
1504: l_col_count := l_col_count+1;
1505: END IF;
1505: END IF;
1506:
1507: IF p_sel_del_attr.service_level IS NOT NULL
1508: THEN
1509: WSH_UTIL_CORE.PrintMsg(' Service level: '||p_sel_del_attr.service_level);
1510: l_where_clause := l_where_clause || ' AND WND.SERVICE_LEVEL = :p_service_level';
1511: l_col_tab(l_col_count) := p_sel_del_attr.service_level;
1512: l_col_count := l_col_count+1;
1513: END IF;
1513: END IF;
1514:
1515: IF p_sel_del_attr.loading_sequence IS NOT NULL
1516: THEN
1517: WSH_UTIL_CORE.PrintMsg(' Loading sequence: '||p_sel_del_attr.loading_sequence);
1518: l_where_clause := l_where_clause || ' AND WND.LOADING_SEQUENCE = :p_loading_sequence';
1519: l_col_tab(l_col_count) := p_sel_del_attr.loading_sequence;
1520: l_col_count := l_col_count+1;
1521: END IF;
1521: END IF;
1522:
1523: IF p_sel_del_attr.customer_id IS NOT NULL
1524: THEN
1525: WSH_UTIL_CORE.PrintMsg(' Customer ID: '||p_sel_del_attr.customer_id);
1526: l_where_clause := l_where_clause || ' AND WND.CUSTOMER_ID = :p_customer_id';
1527: l_col_tab(l_col_count) := p_sel_del_attr.customer_id;
1528: l_col_count := l_col_count+1;
1529: END IF;
1529: END IF;
1530:
1531: IF p_sel_del_attr.intmed_ship_to_loc_id IS NOT NULL
1532: THEN
1533: WSH_UTIL_CORE.PrintMsg(' Intermediate Ship to Location: '||p_sel_del_attr.intmed_ship_to_loc_id);
1534: l_where_clause := l_where_clause || ' AND WND.INTMED_SHIP_TO_LOCATION_ID = :p_intmed_location_id';
1535: l_col_tab(l_col_count) := p_sel_del_attr.intmed_ship_to_loc_id;
1536: l_col_count := l_col_count+1;
1537: END IF;
1537: END IF;
1538:
1539: IF p_sel_del_attr.ulti_ship_to_loc_id IS NOT NULL
1540: THEN
1541: WSH_UTIL_CORE.PrintMsg(' Ulitmate Ship to Location: '||p_sel_del_attr.ulti_ship_to_loc_id);
1542: l_where_clause := l_where_clause || ' AND WND.ULTIMATE_DROPOFF_LOCATION_ID = :p_dropoff_location_id';
1543: l_col_tab(l_col_count) := p_sel_del_attr.ulti_ship_to_loc_id;
1544: l_col_count := l_col_count+1;
1545: END IF;
1545: END IF;
1546:
1547: IF p_sel_del_attr.ulti_ship_to_region_id IS NOT NULL
1548: THEN
1549: WSH_UTIL_CORE.PrintMsg(' Ultimate Ship to Region: '||p_sel_del_attr.ulti_ship_to_region_id);
1550: l_where_clause := l_where_clause || ' AND WND.ULTIMATE_DROPOFF_LOCATION_ID IN (SELECT LOCATION_ID FROM WSH_REGION_LOCATIONS WHERE REGION_ID = :p_region_id)';
1551: l_col_tab(l_col_count) := p_sel_del_attr.ulti_ship_to_region_id;
1552: l_col_count := l_col_count+1;
1553: END IF;
1559: END IF;
1560:
1561: IF p_sel_del_attr.ulti_ship_to_zip_from IS NOT NULL
1562: THEN
1563: WSH_UTIL_CORE.PrintMsg(' Ultimate ship to zip(from): '||p_sel_del_attr.ulti_ship_to_zip_from);
1564: l_where_clause := l_where_clause || ' AND WL.POSTAL_CODE >= :p_zip_code_from';
1565: l_col_tab(l_col_count) := p_sel_del_attr.ulti_ship_to_zip_from;
1566: l_col_count := l_col_count+1;
1567: END IF;
1567: END IF;
1568:
1569: IF p_sel_del_attr.ulti_ship_to_zip_to IS NOT NULL
1570: THEN
1571: WSH_UTIL_CORE.PrintMsg(' Ultimate ship to zip(to): '||p_sel_del_attr.ulti_ship_to_zip_to);
1572: l_where_clause := l_where_clause || ' AND WL.POSTAL_CODE <= :p_zip_code_to';
1573: l_col_tab(l_col_count) := p_sel_del_attr.ulti_ship_to_zip_to;
1574: l_col_count := l_col_count+1;
1575: END IF;
1575: END IF;
1576:
1577: IF p_sel_del_attr.ulti_ship_to_zone_id IS NOT NULL
1578: THEN
1579: WSH_UTIL_CORE.PrintMsg(' Ultimate ship to zone: '||p_sel_del_attr.ulti_ship_to_zone_id);
1580: l_where_clause := l_where_clause || ' AND WND.ULTIMATE_DROPOFF_LOCATION_ID IN (SELECT LOCATION_ID FROM WSH_REGION_LOCATIONS WHERE REGION_ID IN (SELECT REGION_ID FROM WSH_ZONE_REGIONS WHERE PARENT_REGION_ID = :p_zone_id))';
1581: l_col_tab(l_col_count) := p_sel_del_attr.ulti_ship_to_zone_id;
1582: l_col_count := l_col_count+1;
1583: END IF;
1585: IF p_sel_del_attr.inc_del_assgnd_trip_flag IS NULL
1586: THEN
1587: l_where_clause := l_where_clause || ' AND NOT EXISTS ( SELECT 1 FROM WSH_DELIVERY_LEGS WHERE DELIVERY_ID = WND.DELIVERY_ID AND ROWNUM < 2)';
1588: ELSE
1589: WSH_UTIL_CORE.PrintMsg(' Including deliveries assigned to trip');
1590: END IF;
1591:
1592: IF p_sel_del_attr.inc_staged_del_flag IS NULL
1593: THEN
1596: l_col_tab(l_col_count) := 'Y';
1597: l_col_count := l_col_count+1;
1598:
1599: ELSE
1600: WSH_UTIL_CORE.PrintMsg(' Including staged deliveries');
1601: END IF;
1602:
1603: -- is the delivery already consolidated?
1604: l_where_clause := l_where_clause || ' AND NOT EXISTS (SELECT 1 FROM WSH_DELIVERY_ASSIGNMENTS WDA, WSH_NEW_DELIVERIES CONSOL';
1611: END IF;
1612:
1613:
1614: IF l_debug_on THEN
1615: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.OpenDynamicCursor',WSH_DEBUG_SV.C_PROC_LEVEL);
1616: END IF;
1617:
1618: WSH_UTIL_CORE.OpenDynamicCursor(c_deliveries, l_query, l_col_tab);
1619:
1614: IF l_debug_on THEN
1615: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.OpenDynamicCursor',WSH_DEBUG_SV.C_PROC_LEVEL);
1616: END IF;
1617:
1618: WSH_UTIL_CORE.OpenDynamicCursor(c_deliveries, l_query, l_col_tab);
1619:
1620: IF l_debug_on THEN
1621: WSH_DEBUG_SV.logmsg(l_module_name,'Dynamic cursor open');
1622: END IF;
1671:
1672: IF l_debug_on THEN
1673: WSH_DEBUG_SV.pop(l_module_name);
1674: END IF;
1675: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1676:
1677: EXCEPTION
1678: WHEN OTHERS
1679: THEN
1680: IF l_debug_on THEN
1681: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1682: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1683: END IF;
1684: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1685: END Get_Deliveries;
1686:
1687: --========================================================================
1688: -- PROCEDURE : Set_Intermediate_Location
1708: p_override_ship_to_flag IN VARCHAR2,
1709: p_rule_zone_id IN NUMBER,
1710: x_delivery_tab IN OUT NOCOPY WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type,
1711: x_delivery_addnl_attr_tab IN OUT NOCOPY addnl_del_attr_tab_type,
1712: x_failed_records IN OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
1713: x_return_status OUT NOCOPY VARCHAR2)
1714: IS
1715: l_deconsol_output_tab WSH_FTE_CONSTRAINT_FRAMEWORK.deconsol_output_tab_type;
1716: l_delivery_id NUMBER;
1714: IS
1715: l_deconsol_output_tab WSH_FTE_CONSTRAINT_FRAMEWORK.deconsol_output_tab_type;
1716: l_delivery_id NUMBER;
1717: l_failed_index NUMBER;
1718: l_dummy_list WSH_UTIL_CORE.id_tab_type;
1719: l_override_flag BOOLEAN;
1720: l_init_msg_list VARCHAR2(1000);
1721: l_msg_count NUMBER;
1722: l_msg_data VARCHAR2(2000);
1744: WSH_DEBUG_SV.log(l_module_name,'p_rule_zone_id',p_rule_zone_id);
1745: WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
1746: END IF;
1747:
1748: WSH_UTIL_CORE.PrintMsg('Setting intermediate location for the deliveries');
1749: WSH_UTIL_CORE.PrintMsg(' Default intermediate location is '||p_consol_loc_id);
1750: IF p_override_ship_to_flag = 'Y'
1751: THEN
1752: l_override_flag := TRUE;
1745: WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
1746: END IF;
1747:
1748: WSH_UTIL_CORE.PrintMsg('Setting intermediate location for the deliveries');
1749: WSH_UTIL_CORE.PrintMsg(' Default intermediate location is '||p_consol_loc_id);
1750: IF p_override_ship_to_flag = 'Y'
1751: THEN
1752: l_override_flag := TRUE;
1753: WSH_UTIL_CORE.PrintMsg(' Note: Override flag is turned on');
1749: WSH_UTIL_CORE.PrintMsg(' Default intermediate location is '||p_consol_loc_id);
1750: IF p_override_ship_to_flag = 'Y'
1751: THEN
1752: l_override_flag := TRUE;
1753: WSH_UTIL_CORE.PrintMsg(' Note: Override flag is turned on');
1754: ELSE
1755: l_override_flag := FALSE;
1756: WSH_UTIL_CORE.PrintMsg(' Note: Override flag is turned off');
1757: END IF;
1752: l_override_flag := TRUE;
1753: WSH_UTIL_CORE.PrintMsg(' Note: Override flag is turned on');
1754: ELSE
1755: l_override_flag := FALSE;
1756: WSH_UTIL_CORE.PrintMsg(' Note: Override flag is turned off');
1757: END IF;
1758:
1759: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1760:
1755: l_override_flag := FALSE;
1756: WSH_UTIL_CORE.PrintMsg(' Note: Override flag is turned off');
1757: END IF;
1758:
1759: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1760:
1761: -- call constraints API to set the intermediate location
1762: IF l_debug_on THEN
1763: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FTE_COMP_CONSTRAINT_PKG.validate_constraint_deconsol',WSH_DEBUG_SV.C_PROC_LEVEL);
1774: x_msg_count => l_msg_count,
1775: x_msg_data => l_msg_data,
1776: x_return_status => l_return_status);
1777:
1778: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
1779: THEN
1780: IF l_debug_on THEN
1781: WSH_DEBUG_SV.logmsg(l_module_name, 'Intermediate location set successfully for the deliveries');
1782: END IF;
1779: THEN
1780: IF l_debug_on THEN
1781: WSH_DEBUG_SV.logmsg(l_module_name, 'Intermediate location set successfully for the deliveries');
1782: END IF;
1783: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
1784: THEN
1785: IF l_debug_on THEN
1786: WSH_DEBUG_SV.logmsg(l_module_name, 'Intermediate location set with warnings');
1787: END IF;
1784: THEN
1785: IF l_debug_on THEN
1786: WSH_DEBUG_SV.logmsg(l_module_name, 'Intermediate location set with warnings');
1787: END IF;
1788: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
1789: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1790: THEN
1791: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_FTE_COMP_CONSTRAINT_PKG.validate_constraint_deconsol');
1792: FOR m in 1..l_msg_count
1785: IF l_debug_on THEN
1786: WSH_DEBUG_SV.logmsg(l_module_name, 'Intermediate location set with warnings');
1787: END IF;
1788: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
1789: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1790: THEN
1791: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_FTE_COMP_CONSTRAINT_PKG.validate_constraint_deconsol');
1792: FOR m in 1..l_msg_count
1793: LOOP
1787: END IF;
1788: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
1789: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1790: THEN
1791: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_FTE_COMP_CONSTRAINT_PKG.validate_constraint_deconsol');
1792: FOR m in 1..l_msg_count
1793: LOOP
1794: l_message := fnd_msg_pub.get(m,'F');
1795: l_message := replace(l_message,chr(0),' ');
1795: l_message := replace(l_message,chr(0),' ');
1796: IF l_debug_on THEN
1797: WSH_DEBUG_SV.logmsg(l_module_name, l_message);
1798: END IF;
1799: WSH_UTIL_CORE.PrintMsg(l_message);
1800: END LOOP;
1801:
1802: fnd_msg_pub.delete_msg();
1803:
1833: p_internal_cust_location_id => l_deconsol_output_tab(i).deconsol_location,
1834: x_internal_org_location_id => l_dummy_loc,
1835: x_return_status => l_return_status);
1836:
1837: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
1838: THEN
1839: IF l_debug_on THEN
1840: WSH_DEBUG_SV.logmsg(l_module_name, 'Internal location checked successfully');
1841: END IF;
1838: THEN
1839: IF l_debug_on THEN
1840: WSH_DEBUG_SV.logmsg(l_module_name, 'Internal location checked successfully');
1841: END IF;
1842: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
1843: THEN
1844: IF l_debug_on THEN
1845: WSH_DEBUG_SV.logmsg(l_module_name, 'Internal location checked with warnings');
1846: END IF;
1843: THEN
1844: IF l_debug_on THEN
1845: WSH_DEBUG_SV.logmsg(l_module_name, 'Internal location checked with warnings');
1846: END IF;
1847: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
1848: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1849: THEN
1850: IF l_debug_on THEN
1851: WSH_DEBUG_SV.logmsg(l_module_name, 'Internal location check errored out');
1844: IF l_debug_on THEN
1845: WSH_DEBUG_SV.logmsg(l_module_name, 'Internal location checked with warnings');
1846: END IF;
1847: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
1848: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1849: THEN
1850: IF l_debug_on THEN
1851: WSH_DEBUG_SV.logmsg(l_module_name, 'Internal location check errored out');
1852: END IF;
1870: l_failed_index := l_failed_index +1;
1871: x_failed_records(l_failed_index) := l_delivery_id;
1872: x_delivery_tab.delete(l_delivery_id);
1873: x_delivery_addnl_attr_tab.delete(l_delivery_id);
1874: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1875: WSH_UTIL_CORE.PrintMsg('Failed to get Intermediate Location. Removing delivery '||l_delivery_id||' from consolidation');
1876: ELSE
1877: WSH_UTIL_CORE.PrintMsg(' Delivery: '||l_deconsol_output_tab(i).entity_id||', Intermediate location: '||l_deconsol_output_tab(i).deconsol_location);
1878: END IF;
1871: x_failed_records(l_failed_index) := l_delivery_id;
1872: x_delivery_tab.delete(l_delivery_id);
1873: x_delivery_addnl_attr_tab.delete(l_delivery_id);
1874: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1875: WSH_UTIL_CORE.PrintMsg('Failed to get Intermediate Location. Removing delivery '||l_delivery_id||' from consolidation');
1876: ELSE
1877: WSH_UTIL_CORE.PrintMsg(' Delivery: '||l_deconsol_output_tab(i).entity_id||', Intermediate location: '||l_deconsol_output_tab(i).deconsol_location);
1878: END IF;
1879: i := l_deconsol_output_tab.NEXT(i);
1873: x_delivery_addnl_attr_tab.delete(l_delivery_id);
1874: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1875: WSH_UTIL_CORE.PrintMsg('Failed to get Intermediate Location. Removing delivery '||l_delivery_id||' from consolidation');
1876: ELSE
1877: WSH_UTIL_CORE.PrintMsg(' Delivery: '||l_deconsol_output_tab(i).entity_id||', Intermediate location: '||l_deconsol_output_tab(i).deconsol_location);
1878: END IF;
1879: i := l_deconsol_output_tab.NEXT(i);
1880: END LOOP;
1881:
1887: THEN
1888: IF l_debug_on THEN
1889: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1890: END IF;
1891: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1892:
1893: WHEN OTHERS
1894: THEN
1895: IF l_debug_on THEN
1895: IF l_debug_on THEN
1896: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1897: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1898: END IF;
1899: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1900:
1901: END Set_Intermediate_Location;
1902:
1903:
1964: OPEN Get_Rule_Name(p_grouping_rule_id);
1965: FETCH Get_Rule_Name INTO l_rule_name;
1966: CLOSE Get_Rule_Name;
1967:
1968: WSH_UTIL_CORE.PrintMsg('Rule used for consolidation: '''||l_rule_name||'''');
1969:
1970: IF l_debug_on THEN
1971: WSH_DEBUG_SV.pop(l_module_name);
1972: END IF;
1969:
1970: IF l_debug_on THEN
1971: WSH_DEBUG_SV.pop(l_module_name);
1972: END IF;
1973: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1974: EXCEPTION
1975: WHEN OTHERS
1976: THEN
1977: IF l_debug_on THEN
1977: IF l_debug_on THEN
1978: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1979: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1980: END IF;
1981: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1982: END Get_Grouping_Attrs;
1983:
1984: --========================================================================
1985: -- PROCEDURE : Get_Hash_Value
2186:
2187: IF l_debug_on THEN
2188: WSH_DEBUG_SV.pop(l_module_name);
2189: END IF;
2190: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2191:
2192: EXCEPTION
2193: WHEN OTHERS
2194: THEN
2195: IF l_debug_on THEN
2196: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2197: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2198: END IF;
2199: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2200:
2201: END Get_Hash_Value;
2202:
2203:
2227: p_group_tab IN grp_attr_tab_type,
2228: p_max_trip_weight IN NUMBER,
2229: p_max_weight_uom IN VARCHAR2,
2230: p_trip_name_prefix IN VARCHAR2,
2231: x_consol_trip_id OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
2232: x_consol_del_id OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
2233: x_trips_all OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
2234: x_failed_records OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
2235: x_return_status OUT NOCOPY VARCHAR2)
2228: p_max_trip_weight IN NUMBER,
2229: p_max_weight_uom IN VARCHAR2,
2230: p_trip_name_prefix IN VARCHAR2,
2231: x_consol_trip_id OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
2232: x_consol_del_id OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
2233: x_trips_all OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
2234: x_failed_records OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
2235: x_return_status OUT NOCOPY VARCHAR2)
2236: IS
2229: p_max_weight_uom IN VARCHAR2,
2230: p_trip_name_prefix IN VARCHAR2,
2231: x_consol_trip_id OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
2232: x_consol_del_id OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
2233: x_trips_all OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
2234: x_failed_records OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
2235: x_return_status OUT NOCOPY VARCHAR2)
2236: IS
2237:
2230: p_trip_name_prefix IN VARCHAR2,
2231: x_consol_trip_id OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
2232: x_consol_del_id OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
2233: x_trips_all OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
2234: x_failed_records OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
2235: x_return_status OUT NOCOPY VARCHAR2)
2236: IS
2237:
2238: l_trip_weight_flag BOOLEAN := FALSE;
2236: IS
2237:
2238: l_trip_weight_flag BOOLEAN := FALSE;
2239: l_consol_index NUMBER; -- relative count of record in consol tab
2240: l_del_tab_grp WSH_UTIL_CORE.id_tab_type;
2241: l_d_id NUMBER;
2242: l_del_weight NUMBER;
2243: l_failed_index NUMBER;
2244: l_del_rec WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;
2287: THEN
2288: l_trip_weight_flag := TRUE;
2289: END IF;
2290:
2291: WSH_UTIL_CORE.PrintMsg('Creating consolidations');
2292: IF l_trip_weight_flag
2293: THEN
2294: WSH_UTIL_CORE.PrintMsg(' Note: Consolidation trip weight threshold is '||p_max_trip_weight||' '||p_max_weight_uom);
2295: IF l_debug_on THEN
2290:
2291: WSH_UTIL_CORE.PrintMsg('Creating consolidations');
2292: IF l_trip_weight_flag
2293: THEN
2294: WSH_UTIL_CORE.PrintMsg(' Note: Consolidation trip weight threshold is '||p_max_trip_weight||' '||p_max_weight_uom);
2295: IF l_debug_on THEN
2296: WSH_DEBUG_SV.logmsg(l_module_name,'Consolidation Trip weight taken into consideration');
2297: END IF;
2298: ELSE
2307: l_failed_index := 0;
2308: END IF;
2309:
2310:
2311: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2312: l_consol_count := 0;
2313: l_trips_all_count := 0;
2314: i := p_group_tab.FIRST;
2315: -- FOR l_index_i IN 1 .. p_group_tab.COUNT
2319: --p_consol_del_id is a table of consolidation deliveries created
2320: l_del_tab_grp := p_group_tab(i).delivery_list;
2321: l_consol_tab.DELETE;
2322: j := l_del_tab_grp.FIRST;
2323: WSH_UTIL_CORE.PrintMsg(' Consolidating for group '||p_group_tab(i).group_id);
2324: --FOR l_index_j IN 1 .. l_del_tab_grp.COUNT
2325: WHILE j IS NOT NULL
2326: LOOP
2327: --{
2348: l_failed_index := l_failed_index +1;
2349: x_failed_records(l_failed_index) := l_d_id;
2350: x_delivery_tab.DELETE(l_d_id);
2351: x_delivery_addnl_attr_tab.DELETE(l_d_id);
2352: WSH_UTIL_CORE.PrintMsg('Delivery '||l_d_id||' weight exceeds threshold. Removing from consolidation');
2353: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2354:
2355: ELSE
2356: l_del_rec := Copy_Record(x_delivery_tab(l_d_id),x_delivery_addnl_attr_tab(l_d_id));
2349: x_failed_records(l_failed_index) := l_d_id;
2350: x_delivery_tab.DELETE(l_d_id);
2351: x_delivery_addnl_attr_tab.DELETE(l_d_id);
2352: WSH_UTIL_CORE.PrintMsg('Delivery '||l_d_id||' weight exceeds threshold. Removing from consolidation');
2353: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2354:
2355: ELSE
2356: l_del_rec := Copy_Record(x_delivery_tab(l_d_id),x_delivery_addnl_attr_tab(l_d_id));
2357: IF l_debug_on THEN
2367: x_consol_tab => l_consol_tab,
2368: x_consol_index => l_consol_index,
2369: x_return_status => l_return_status); -- relative index in the consolidation table
2370:
2371: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
2372: THEN
2373: IF l_debug_on THEN
2374: WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery added for consolidation');
2375: END IF;
2372: THEN
2373: IF l_debug_on THEN
2374: WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery added for consolidation');
2375: END IF;
2376: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
2377: THEN
2378: IF l_debug_on THEN
2379: WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery added with warning');
2380: END IF;
2377: THEN
2378: IF l_debug_on THEN
2379: WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery added with warning');
2380: END IF;
2381: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2382: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2383: THEN
2384: WSH_UTIL_CORE.PrintMsg('Could not add delivery for consolidation');
2385: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2378: IF l_debug_on THEN
2379: WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery added with warning');
2380: END IF;
2381: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2382: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2383: THEN
2384: WSH_UTIL_CORE.PrintMsg('Could not add delivery for consolidation');
2385: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2386: raise FND_API.G_EXC_ERROR;
2380: END IF;
2381: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2382: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2383: THEN
2384: WSH_UTIL_CORE.PrintMsg('Could not add delivery for consolidation');
2385: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2386: raise FND_API.G_EXC_ERROR;
2387: END IF;
2388:
2381: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2382: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2383: THEN
2384: WSH_UTIL_CORE.PrintMsg('Could not add delivery for consolidation');
2385: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2386: raise FND_API.G_EXC_ERROR;
2387: END IF;
2388:
2389: x_delivery_addnl_attr_tab(l_d_id).consol_index := l_consol_index + l_consol_count;
2405: x_consol_tab => l_consol_tab,
2406: x_consol_index => l_consol_index,
2407: x_return_status => l_return_status); -- relative index in the consolidation table
2408:
2409: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
2410: THEN
2411: IF l_debug_on THEN
2412: WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery added for consolidation');
2413: END IF;
2410: THEN
2411: IF l_debug_on THEN
2412: WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery added for consolidation');
2413: END IF;
2414: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
2415: THEN
2416: IF l_debug_on THEN
2417: WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery added with warning');
2418: END IF;
2415: THEN
2416: IF l_debug_on THEN
2417: WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery added with warning');
2418: END IF;
2419: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2420: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2421: THEN
2422: WSH_UTIL_CORE.PrintMsg('Could not add delivery for consolidation');
2423: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2416: IF l_debug_on THEN
2417: WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery added with warning');
2418: END IF;
2419: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2420: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2421: THEN
2422: WSH_UTIL_CORE.PrintMsg('Could not add delivery for consolidation');
2423: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2424: raise FND_API.G_EXC_ERROR;
2418: END IF;
2419: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2420: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2421: THEN
2422: WSH_UTIL_CORE.PrintMsg('Could not add delivery for consolidation');
2423: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2424: raise FND_API.G_EXC_ERROR;
2425: END IF;
2426:
2419: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2420: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2421: THEN
2422: WSH_UTIL_CORE.PrintMsg('Could not add delivery for consolidation');
2423: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2424: raise FND_API.G_EXC_ERROR;
2425: END IF;
2426:
2427: x_delivery_addnl_attr_tab(l_d_id).consol_index := l_consol_index + l_consol_count;
2461: x_return_status => l_return_status,
2462: x_msg_count => l_mesg_count,
2463: x_msg_data => l_mesg_data);
2464:
2465: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
2466: THEN
2467: IF l_debug_on THEN
2468: WSH_DEBUG_SV.logmsg(l_module_name, 'Consolidation Created successfully');
2469: END IF;
2466: THEN
2467: IF l_debug_on THEN
2468: WSH_DEBUG_SV.logmsg(l_module_name, 'Consolidation Created successfully');
2469: END IF;
2470: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2471: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2472: THEN
2473: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_DELIVERIES_GRP.Delivery_ACTION');
2474: FOR i in 1..l_mesg_count
2467: IF l_debug_on THEN
2468: WSH_DEBUG_SV.logmsg(l_module_name, 'Consolidation Created successfully');
2469: END IF;
2470: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2471: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2472: THEN
2473: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_DELIVERIES_GRP.Delivery_ACTION');
2474: FOR i in 1..l_mesg_count
2475: LOOP
2469: END IF;
2470: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2471: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2472: THEN
2473: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_DELIVERIES_GRP.Delivery_ACTION');
2474: FOR i in 1..l_mesg_count
2475: LOOP
2476: l_message := fnd_msg_pub.get(m,'F');
2477: l_message := replace(l_message,chr(0),' ');
2474: FOR i in 1..l_mesg_count
2475: LOOP
2476: l_message := fnd_msg_pub.get(m,'F');
2477: l_message := replace(l_message,chr(0),' ');
2478: WSH_UTIL_CORE.PrintMsg(l_message);
2479: END LOOP;
2480:
2481: fnd_msg_pub.delete_msg();
2482: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2478: WSH_UTIL_CORE.PrintMsg(l_message);
2479: END LOOP;
2480:
2481: fnd_msg_pub.delete_msg();
2482: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2483: raise FND_API.G_EXC_ERROR;
2484: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
2485: THEN
2486: IF l_debug_on THEN
2480:
2481: fnd_msg_pub.delete_msg();
2482: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2483: raise FND_API.G_EXC_ERROR;
2484: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
2485: THEN
2486: IF l_debug_on THEN
2487: WSH_DEBUG_SV.logmsg(l_module_name, 'Created consolidations with following warnings');
2488: FOR i in 1..l_mesg_count
2492: WSH_DEBUG_SV.logmsg(l_module_name, l_message);
2493: END LOOP;
2494: END IF;
2495: fnd_msg_pub.delete_msg();
2496: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2497: END IF;
2498:
2499: l_index := l_delivery_out_rec.valid_ids_tab.FIRST;
2500: IF l_index IS NOT NULL
2505: -- Store trip id in a table for future calls to rating/ routing
2506: x_trips_all(l_trips_all_count) := x_consol_trip_id(l_count);
2507: l_trips_all_count := l_trips_all_count+1;
2508: END IF;
2509: WSH_UTIL_CORE.PrintMsg(' Consolidation Index: '||l_consol_tab(k).consol_index||', Consol Delivery: '||x_consol_del_id(l_count)||', Consol Trip: '||x_consol_trip_id(l_count));
2510: k := l_consol_tab.NEXT(k);
2511: --}
2512: END LOOP;
2513: --}
2523: END IF;
2524: EXCEPTION
2525: WHEN FND_API.G_EXC_ERROR
2526: THEN
2527: WSH_UTIL_CORE.PrintMsg('Exception occurred in Create_Consolidation');
2528: IF l_debug_on THEN
2529: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2530: END IF;
2531: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2527: WSH_UTIL_CORE.PrintMsg('Exception occurred in Create_Consolidation');
2528: IF l_debug_on THEN
2529: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2530: END IF;
2531: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2532: WHEN OTHERS
2533: THEN
2534: WSH_UTIL_CORE.PrintMsg('SQLCODE: '||sqlcode||' SQLERRM: '||sqlerrm);
2535: WSH_UTIL_CORE.PrintMsg('Exception occurred in Create_Consolidation');
2530: END IF;
2531: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2532: WHEN OTHERS
2533: THEN
2534: WSH_UTIL_CORE.PrintMsg('SQLCODE: '||sqlcode||' SQLERRM: '||sqlerrm);
2535: WSH_UTIL_CORE.PrintMsg('Exception occurred in Create_Consolidation');
2536: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2537: IF l_debug_on THEN
2538: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2531: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2532: WHEN OTHERS
2533: THEN
2534: WSH_UTIL_CORE.PrintMsg('SQLCODE: '||sqlcode||' SQLERRM: '||sqlerrm);
2535: WSH_UTIL_CORE.PrintMsg('Exception occurred in Create_Consolidation');
2536: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2537: IF l_debug_on THEN
2538: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2539: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2532: WHEN OTHERS
2533: THEN
2534: WSH_UTIL_CORE.PrintMsg('SQLCODE: '||sqlcode||' SQLERRM: '||sqlerrm);
2535: WSH_UTIL_CORE.PrintMsg('Exception occurred in Create_Consolidation');
2536: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2537: IF l_debug_on THEN
2538: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2539: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2540: END IF;
2556: --========================================================================
2557: PROCEDURE Create_Deconsol_Trips(
2558: x_delivery_tab IN OUT NOCOPY WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type,
2559: x_delivery_addnl_attr_tab IN OUT NOCOPY addnl_del_attr_tab_type,
2560: p_consol_trip_id IN WSH_UTIL_CORE.id_tab_type,
2561: p_trip_name_prefix IN VARCHAR2,
2562: x_trips_all IN OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
2563: x_return_status OUT NOCOPY VARCHAR2)
2564: IS
2558: x_delivery_tab IN OUT NOCOPY WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type,
2559: x_delivery_addnl_attr_tab IN OUT NOCOPY addnl_del_attr_tab_type,
2560: p_consol_trip_id IN WSH_UTIL_CORE.id_tab_type,
2561: p_trip_name_prefix IN VARCHAR2,
2562: x_trips_all IN OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
2563: x_return_status OUT NOCOPY VARCHAR2)
2564: IS
2565: l_trip_in_rec WSH_TRIPS_GRP.tripInRecType;
2566: l_init_msg_list VARCHAR2(100);
2617: WSH_DEBUG_SV.push(l_module_name);
2618: WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
2619: END IF;
2620:
2621: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2622: WSH_UTIL_CORE.PrintMsg('Creating deconsolidation trips');
2623: i := x_delivery_tab.FIRST;
2624: l_trips_all_count := x_trips_all.LAST+1;
2625:
2618: WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
2619: END IF;
2620:
2621: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2622: WSH_UTIL_CORE.PrintMsg('Creating deconsolidation trips');
2623: i := x_delivery_tab.FIRST;
2624: l_trips_all_count := x_trips_all.LAST+1;
2625:
2626: -- FOR i IN x_delivery_tab.FIRST .. x_delivery_tab.LAST
2632: -- Checking if the deconsol location is same as ultimate dropoff
2633: -- Do not create deconsol trip in such case
2634: IF x_delivery_tab(i).intmed_ship_to_location_id = x_delivery_tab(i).ultimate_dropoff_location_id
2635: THEN
2636: WSH_UTIL_CORE.PrintMsg('Not creating deconsol trip for the delivery '||x_delivery_tab(i).delivery_id);
2637: ELSE
2638:
2639: SAVEPOINT CREATE_TRIP;
2640: l_del_id := x_delivery_tab(i).delivery_id;
2676: p_in_rec => l_trip_in_rec,
2677: x_out_tab => l_trip_out_rec_tab);
2678:
2679:
2680: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
2681: THEN
2682: IF l_debug_on THEN
2683: WSH_DEBUG_SV.logmsg(l_module_name, 'Created trip succesfully');
2684: END IF;
2681: THEN
2682: IF l_debug_on THEN
2683: WSH_DEBUG_SV.logmsg(l_module_name, 'Created trip succesfully');
2684: END IF;
2685: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2686: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2687: THEN
2688: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_TRIPS_GRP.Create_Update_Trip');
2689: IF l_debug_on THEN
2682: IF l_debug_on THEN
2683: WSH_DEBUG_SV.logmsg(l_module_name, 'Created trip succesfully');
2684: END IF;
2685: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2686: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2687: THEN
2688: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_TRIPS_GRP.Create_Update_Trip');
2689: IF l_debug_on THEN
2690: FOR m in 1..l_msg_count
2684: END IF;
2685: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2686: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2687: THEN
2688: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_TRIPS_GRP.Create_Update_Trip');
2689: IF l_debug_on THEN
2690: FOR m in 1..l_msg_count
2691: LOOP
2692: l_message := fnd_msg_pub.get(m,'F');
2696: END IF;
2697: fnd_msg_pub.delete_msg();
2698: raise FND_API.G_EXC_ERROR;
2699:
2700: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
2701: THEN
2702: IF l_debug_on THEN
2703: WSH_DEBUG_SV.logmsg(l_module_name,'Trip created with warnings');
2704: FOR m in 1..l_msg_count
2744: p_in_rec => l_trip_in_rec,
2745: x_out_tab => l_trip_out_rec_tab);
2746:
2747:
2748: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
2749: THEN
2750: IF l_debug_on THEN
2751: WSH_DEBUG_SV.logmsg(l_module_name, 'Renamed trip succesfully');
2752: END IF;
2749: THEN
2750: IF l_debug_on THEN
2751: WSH_DEBUG_SV.logmsg(l_module_name, 'Renamed trip succesfully');
2752: END IF;
2753: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2754: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2755: THEN
2756: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_TRIPS_GRP.Create_Update_Trip');
2757: IF l_debug_on THEN
2750: IF l_debug_on THEN
2751: WSH_DEBUG_SV.logmsg(l_module_name, 'Renamed trip succesfully');
2752: END IF;
2753: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2754: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2755: THEN
2756: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_TRIPS_GRP.Create_Update_Trip');
2757: IF l_debug_on THEN
2758: FOR m in 1..l_msg_count
2752: END IF;
2753: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2754: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2755: THEN
2756: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_TRIPS_GRP.Create_Update_Trip');
2757: IF l_debug_on THEN
2758: FOR m in 1..l_msg_count
2759: LOOP
2760: l_message := fnd_msg_pub.get(m,'F');
2764: END IF;
2765: fnd_msg_pub.delete_msg();
2766: raise FND_API.G_EXC_ERROR;
2767:
2768: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
2769: THEN
2770: IF l_debug_on THEN
2771: WSH_DEBUG_SV.logmsg(l_module_name,'Trip renamed with warnings');
2772: FOR m in 1..l_msg_count
2812: x_return_status => l_return_status,
2813: x_msg_count => l_msg_count,
2814: x_msg_data => l_msg_data);
2815:
2816: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
2817: THEN
2818: IF l_debug_on THEN
2819: WSH_DEBUG_SV.logmsg(l_module_name, 'Trip unplanned succesfully');
2820: END IF;
2817: THEN
2818: IF l_debug_on THEN
2819: WSH_DEBUG_SV.logmsg(l_module_name, 'Trip unplanned succesfully');
2820: END IF;
2821: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2822: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2823: THEN
2824: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_TRIPS_GRP.Trip_Action');
2825: IF l_debug_on THEN
2818: IF l_debug_on THEN
2819: WSH_DEBUG_SV.logmsg(l_module_name, 'Trip unplanned succesfully');
2820: END IF;
2821: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2822: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2823: THEN
2824: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_TRIPS_GRP.Trip_Action');
2825: IF l_debug_on THEN
2826: FOR m in 1..l_msg_count
2820: END IF;
2821: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2822: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2823: THEN
2824: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_TRIPS_GRP.Trip_Action');
2825: IF l_debug_on THEN
2826: FOR m in 1..l_msg_count
2827: LOOP
2828: l_message := fnd_msg_pub.get(m,'F');
2832: END IF;
2833: fnd_msg_pub.delete_msg();
2834: raise FND_API.G_EXC_ERROR;
2835:
2836: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
2837: THEN
2838: IF l_debug_on THEN
2839: WSH_DEBUG_SV.logmsg(l_module_name,'Trip unplanned with warnings');
2840: FOR m in 1..l_msg_count
2876: x_msg_count => l_msg_count,
2877: x_msg_data => l_msg_data,
2878: x_stop_wt_vol_out_tab => l_stop_wt_vol_out_tab);
2879:
2880: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
2881: THEN
2882: IF l_debug_on THEN
2883: WSH_DEBUG_SV.logmsg(l_module_name, 'Pickup Stop created succesfully');
2884: END IF;
2881: THEN
2882: IF l_debug_on THEN
2883: WSH_DEBUG_SV.logmsg(l_module_name, 'Pickup Stop created succesfully');
2884: END IF;
2885: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2886: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2887: THEN
2888: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP');
2889: IF l_debug_on THEN
2882: IF l_debug_on THEN
2883: WSH_DEBUG_SV.logmsg(l_module_name, 'Pickup Stop created succesfully');
2884: END IF;
2885: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2886: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2887: THEN
2888: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP');
2889: IF l_debug_on THEN
2890: FOR m in 1..l_msg_count
2884: END IF;
2885: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2886: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2887: THEN
2888: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP');
2889: IF l_debug_on THEN
2890: FOR m in 1..l_msg_count
2891: LOOP
2892: l_message := fnd_msg_pub.get(m,'F');
2896: END IF;
2897: fnd_msg_pub.delete_msg();
2898: raise FND_API.G_EXC_ERROR;
2899:
2900: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
2901: THEN
2902: IF l_debug_on THEN
2903: WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop created with warnings');
2904: FOR m in 1..l_msg_count
2934: x_msg_count => l_msg_count,
2935: x_msg_data => l_msg_data,
2936: x_stop_wt_vol_out_tab => l_stop_wt_vol_out_tab);
2937:
2938: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
2939: THEN
2940: IF l_debug_on THEN
2941: WSH_DEBUG_SV.logmsg(l_module_name, 'Dropoff Stop created succesfully');
2942: END IF;
2939: THEN
2940: IF l_debug_on THEN
2941: WSH_DEBUG_SV.logmsg(l_module_name, 'Dropoff Stop created succesfully');
2942: END IF;
2943: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2944: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2945: THEN
2946: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP');
2947: IF l_debug_on THEN
2940: IF l_debug_on THEN
2941: WSH_DEBUG_SV.logmsg(l_module_name, 'Dropoff Stop created succesfully');
2942: END IF;
2943: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2944: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2945: THEN
2946: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP');
2947: IF l_debug_on THEN
2948: FOR m in 1..l_msg_count
2942: END IF;
2943: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2944: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2945: THEN
2946: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP');
2947: IF l_debug_on THEN
2948: FOR m in 1..l_msg_count
2949: LOOP
2950: l_message := fnd_msg_pub.get(m,'F');
2954: END IF;
2955: fnd_msg_pub.delete_msg();
2956: raise FND_API.G_EXC_ERROR;
2957:
2958: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
2959: THEN
2960: IF l_debug_on THEN
2961: WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop created with warnings');
2962: FOR m in 1..l_msg_count
3025: x_return_status => l_return_status,
3026: x_msg_count => l_msg_count,
3027: x_msg_data => l_msg_data);
3028:
3029: IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
3030: THEN
3031: IF l_debug_on THEN
3032: WSH_DEBUG_SV.logmsg(l_module_name, 'Assigned delivery to trip succesfully');
3033: END IF;
3030: THEN
3031: IF l_debug_on THEN
3032: WSH_DEBUG_SV.logmsg(l_module_name, 'Assigned delivery to trip succesfully');
3033: END IF;
3034: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
3035: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
3036: THEN
3037: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_DELIVERIES_GRP.Delivery_Action');
3038: IF l_debug_on THEN
3031: IF l_debug_on THEN
3032: WSH_DEBUG_SV.logmsg(l_module_name, 'Assigned delivery to trip succesfully');
3033: END IF;
3034: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
3035: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
3036: THEN
3037: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_DELIVERIES_GRP.Delivery_Action');
3038: IF l_debug_on THEN
3039: FOR m in 1..l_msg_count
3033: END IF;
3034: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
3035: OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
3036: THEN
3037: WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_DELIVERIES_GRP.Delivery_Action');
3038: IF l_debug_on THEN
3039: FOR m in 1..l_msg_count
3040: LOOP
3041: l_message := fnd_msg_pub.get(m,'F');
3045: END IF;
3046: fnd_msg_pub.delete_msg();
3047: raise FND_API.G_EXC_ERROR;
3048:
3049: ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
3050: THEN
3051: IF l_debug_on THEN
3052: WSH_DEBUG_SV.logmsg(l_module_name,'Assigned delivery to trip with warnings');
3053: FOR m in 1..l_msg_count
3058: END LOOP;
3059: END IF;
3060: fnd_msg_pub.delete_msg();
3061: END IF;
3062: WSH_UTIL_CORE.PrintMsg(' Delivery: '||x_delivery_tab(i).delivery_id||', Trip: '||l_trip_name);
3063:
3064: END IF;
3065:
3066: i := x_delivery_tab.NEXT(i);
3068: EXCEPTION
3069: WHEN FND_API.G_EXC_ERROR
3070: THEN
3071: ROLLBACK TO CREATE_TRIP;
3072: WSH_UTIL_CORE.PrintMsg('Exception occurred while creating deconsol trip for delivery '||l_del_id);
3073: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3074: i := x_delivery_tab.NEXT(i);
3075: IF l_trip_cr_flag
3076: THEN
3069: WHEN FND_API.G_EXC_ERROR
3070: THEN
3071: ROLLBACK TO CREATE_TRIP;
3072: WSH_UTIL_CORE.PrintMsg('Exception occurred while creating deconsol trip for delivery '||l_del_id);
3073: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3074: i := x_delivery_tab.NEXT(i);
3075: IF l_trip_cr_flag
3076: THEN
3077: x_trips_all(l_trips_all_count) := null;
3079: END IF;
3080: WHEN OTHERS
3081: THEN
3082: ROLLBACK TO CREATE_TRIP;
3083: WSH_UTIL_CORE.PrintMsg('Exception occurred while creating deconsol trip for delivery '||l_del_id);
3084: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3085: IF l_debug_on THEN
3086: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3087: END IF;
3080: WHEN OTHERS
3081: THEN
3082: ROLLBACK TO CREATE_TRIP;
3083: WSH_UTIL_CORE.PrintMsg('Exception occurred while creating deconsol trip for delivery '||l_del_id);
3084: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3085: IF l_debug_on THEN
3086: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3087: END IF;
3088: i := x_delivery_tab.NEXT(i);
3099:
3100: EXCEPTION
3101: WHEN FND_API.G_EXC_ERROR
3102: THEN
3103: WSH_UTIL_CORE.PrintMsg('Exception occurred in Create_Deconsol_Trip');
3104: IF l_debug_on THEN
3105: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3106: END IF;
3107: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3103: WSH_UTIL_CORE.PrintMsg('Exception occurred in Create_Deconsol_Trip');
3104: IF l_debug_on THEN
3105: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3106: END IF;
3107: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3108: WHEN OTHERS
3109: THEN
3110: WSH_UTIL_CORE.PrintMsg('SQLCODE: '||sqlcode||' SQLERRM: '||sqlerrm);
3111: WSH_UTIL_CORE.PrintMsg('Exception occurred in Create_Deconsol_Trip');
3106: END IF;
3107: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3108: WHEN OTHERS
3109: THEN
3110: WSH_UTIL_CORE.PrintMsg('SQLCODE: '||sqlcode||' SQLERRM: '||sqlerrm);
3111: WSH_UTIL_CORE.PrintMsg('Exception occurred in Create_Deconsol_Trip');
3112: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3113: IF l_debug_on THEN
3114: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3107: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3108: WHEN OTHERS
3109: THEN
3110: WSH_UTIL_CORE.PrintMsg('SQLCODE: '||sqlcode||' SQLERRM: '||sqlerrm);
3111: WSH_UTIL_CORE.PrintMsg('Exception occurred in Create_Deconsol_Trip');
3112: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3113: IF l_debug_on THEN
3114: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3115: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3108: WHEN OTHERS
3109: THEN
3110: WSH_UTIL_CORE.PrintMsg('SQLCODE: '||sqlcode||' SQLERRM: '||sqlerrm);
3111: WSH_UTIL_CORE.PrintMsg('Exception occurred in Create_Deconsol_Trip');
3112: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3113: IF l_debug_on THEN
3114: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3115: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3116: END IF;
3190: END IF;
3191:
3192: END IF;
3193:
3194: WSH_UTIL_CORE.PrintMsg(' Delivery: '||p_delivery_rec.delivery_id||', Consolidation Index: '||x_consol_index);
3195: IF l_debug_on THEN
3196: WSH_DEBUG_SV.pop(l_module_name);
3197: END IF;
3198: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3194: WSH_UTIL_CORE.PrintMsg(' Delivery: '||p_delivery_rec.delivery_id||', Consolidation Index: '||x_consol_index);
3195: IF l_debug_on THEN
3196: WSH_DEBUG_SV.pop(l_module_name);
3197: END IF;
3198: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3199: EXCEPTION
3200: WHEN OTHERS
3201: THEN
3202: IF l_debug_on THEN
3202: IF l_debug_on THEN
3203: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3204: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3205: END IF;
3206: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3207: END Add_For_Consolidation;
3208:
3209: END WSH_MDC_SRS;
3210: