[Home] [Help]
222: --
223: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
224: --
225: IF l_debug_on IS NULL THEN
226: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
227: END IF;
228: --
229: --
230: -- Debug Statements
229: --
230: -- Debug Statements
231: --
232: IF l_debug_on THEN
233: WSH_DEBUG_SV.push(l_module_name);
234: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
235: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
236: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_REGION_FLAG',p_location_region_flag);
237: WSH_DEBUG_SV.log(l_module_name,'P_MESSAGING_YN',p_messaging_yn);
230: -- Debug Statements
231: --
232: IF l_debug_on THEN
233: WSH_DEBUG_SV.push(l_module_name);
234: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
235: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
236: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_REGION_FLAG',p_location_region_flag);
237: WSH_DEBUG_SV.log(l_module_name,'P_MESSAGING_YN',p_messaging_yn);
238: WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',p_api_version);
231: --
232: IF l_debug_on THEN
233: WSH_DEBUG_SV.push(l_module_name);
234: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
235: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
236: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_REGION_FLAG',p_location_region_flag);
237: WSH_DEBUG_SV.log(l_module_name,'P_MESSAGING_YN',p_messaging_yn);
238: WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',p_api_version);
239: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
232: IF l_debug_on THEN
233: WSH_DEBUG_SV.push(l_module_name);
234: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
235: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
236: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_REGION_FLAG',p_location_region_flag);
237: WSH_DEBUG_SV.log(l_module_name,'P_MESSAGING_YN',p_messaging_yn);
238: WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',p_api_version);
239: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
240: WSH_DEBUG_SV.logmsg(l_module_name,'-------- p_distance_input_tab ------');
233: WSH_DEBUG_SV.push(l_module_name);
234: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
235: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
236: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_REGION_FLAG',p_location_region_flag);
237: WSH_DEBUG_SV.log(l_module_name,'P_MESSAGING_YN',p_messaging_yn);
238: WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',p_api_version);
239: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
240: WSH_DEBUG_SV.logmsg(l_module_name,'-------- p_distance_input_tab ------');
241:
234: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
235: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
236: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_REGION_FLAG',p_location_region_flag);
237: WSH_DEBUG_SV.log(l_module_name,'P_MESSAGING_YN',p_messaging_yn);
238: WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',p_api_version);
239: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
240: WSH_DEBUG_SV.logmsg(l_module_name,'-------- p_distance_input_tab ------');
241:
242: IF (p_distance_input_tab.COUNT > 0) THEN
235: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
236: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_REGION_FLAG',p_location_region_flag);
237: WSH_DEBUG_SV.log(l_module_name,'P_MESSAGING_YN',p_messaging_yn);
238: WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',p_api_version);
239: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
240: WSH_DEBUG_SV.logmsg(l_module_name,'-------- p_distance_input_tab ------');
241:
242: IF (p_distance_input_tab.COUNT > 0) THEN
243: FOR dbdit IN p_distance_input_tab.FIRST..p_distance_input_tab.LAST LOOP
236: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_REGION_FLAG',p_location_region_flag);
237: WSH_DEBUG_SV.log(l_module_name,'P_MESSAGING_YN',p_messaging_yn);
238: WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',p_api_version);
239: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
240: WSH_DEBUG_SV.logmsg(l_module_name,'-------- p_distance_input_tab ------');
241:
242: IF (p_distance_input_tab.COUNT > 0) THEN
243: FOR dbdit IN p_distance_input_tab.FIRST..p_distance_input_tab.LAST LOOP
244: WSH_DEBUG_SV.log(l_module_name,'origin_id', p_distance_input_tab(dbdit).origin_id);
240: WSH_DEBUG_SV.logmsg(l_module_name,'-------- p_distance_input_tab ------');
241:
242: IF (p_distance_input_tab.COUNT > 0) THEN
243: FOR dbdit IN p_distance_input_tab.FIRST..p_distance_input_tab.LAST LOOP
244: WSH_DEBUG_SV.log(l_module_name,'origin_id', p_distance_input_tab(dbdit).origin_id);
245: WSH_DEBUG_SV.log(l_module_name,'destination_id',p_distance_input_tab(dbdit).destination_id);
246: END LOOP;
247: ELSE
248: WSH_DEBUG_SV.logmsg(l_module_name,'NO INPUT ATTRIBUTES IN p_distance_input_tab INPUT TABLE');
241:
242: IF (p_distance_input_tab.COUNT > 0) THEN
243: FOR dbdit IN p_distance_input_tab.FIRST..p_distance_input_tab.LAST LOOP
244: WSH_DEBUG_SV.log(l_module_name,'origin_id', p_distance_input_tab(dbdit).origin_id);
245: WSH_DEBUG_SV.log(l_module_name,'destination_id',p_distance_input_tab(dbdit).destination_id);
246: END LOOP;
247: ELSE
248: WSH_DEBUG_SV.logmsg(l_module_name,'NO INPUT ATTRIBUTES IN p_distance_input_tab INPUT TABLE');
249: END IF;
244: WSH_DEBUG_SV.log(l_module_name,'origin_id', p_distance_input_tab(dbdit).origin_id);
245: WSH_DEBUG_SV.log(l_module_name,'destination_id',p_distance_input_tab(dbdit).destination_id);
246: END LOOP;
247: ELSE
248: WSH_DEBUG_SV.logmsg(l_module_name,'NO INPUT ATTRIBUTES IN p_distance_input_tab INPUT TABLE');
249: END IF;
250: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
251:
252:
246: END LOOP;
247: ELSE
248: WSH_DEBUG_SV.logmsg(l_module_name,'NO INPUT ATTRIBUTES IN p_distance_input_tab INPUT TABLE');
249: END IF;
250: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
251:
252:
253: END IF;
254:
261: --
262: -- Clear the message tab
263: --
264: IF l_debug_on THEN
265: WSH_DEBUG_SV.logmsg(l_module_name,'resetting the global message table');
266: END IF;
267:
268: g_message_tab.DELETE;
269:
268: g_message_tab.DELETE;
269:
270:
271: IF l_debug_on THEN
272: WSH_DEBUG_SV.logmsg(l_module_name,'resetting the local tables');
273: END IF;
274: --
275: -- Reset the local tables
276: --
307: --
308: -- No input data exists - return back an error
309: --
310: IF l_debug_on THEN
311: WSH_DEBUG_SV.log(l_module_name,' Input table has no data - p_distance_input_tab.COUNT',p_distance_input_tab.COUNT);
312: END IF;
313:
314: RAISE FTE_DIST_NO_INPUT_DATA;
315:
326: --
327: -- Invalid location_region_flag, raise an error
328: --
329: IF l_debug_on THEN
330: WSH_DEBUG_SV.log(l_module_name,'location region input flag is invalid - p_location_region_flag',p_location_region_flag);
331: END IF;
332:
333: RAISE FTE_DIST_INVALID_LOC_REG_FLAG;
334:
339: -- Get the profile option of the distance stuff to
340: -- see what region level we should be searching for
341: --
342: IF l_debug_on THEN
343: WSH_DEBUG_SV.logmsg(l_module_name,'getting the distance profile valie fnd_profile.get(FTE_DISTANCE_LVL)');
344: END IF;
345:
346: fnd_profile.get('FTE_DISTANCE_LVL',l_distance_profile);
347:
345:
346: fnd_profile.get('FTE_DISTANCE_LVL',l_distance_profile);
347:
348: IF l_debug_on THEN
349: WSH_DEBUG_SV.log(l_module_name,'fnd_profile.get(FTE_DISTANCE_LVL)= ',l_distance_profile);
350: END IF;
351:
352: IF (l_distance_profile is null) THEN
353: --
353: --
354: -- The profile option is null - raise an error
355: --
356: IF l_debug_on THEN
357: WSH_DEBUG_SV.logmsg(l_module_name,'distance profile is null raise FTE_DIST_NULL_PROFILE exception');
358: END IF;
359: RAISE FTE_DIST_NULL_PROFILE;
360:
361: END IF;
382: --
383: l_region_type := 2;
384:
385: IF l_debug_on THEN
386: WSH_DEBUG_SV.log(l_module_name,'distance profile is CITYSTATE - region type = ',l_region_type);
387: END IF;
388: ELSIF (l_distance_profile = 'ZIP') THEN
389: --
390: -- region type is zip/postal level
391: --
392: l_region_type := 3;
393:
394: IF l_debug_on THEN
395: WSH_DEBUG_SV.log(l_module_name,'distance profile is ZIP - region type = ',l_region_type);
396: END IF;
397: ELSIF (l_distance_profile = 'COUNTY') THEN
398: --
399: -- region type is county level
399: -- region type is county level
400: --
401: l_region_type := 4;
402: IF l_debug_on THEN
403: WSH_DEBUG_SV.log(l_module_name,'distance profile is COUNTY - region type = ',l_region_type);
404: END IF;
405: ELSE
406: --
407: -- The profile option has an invalid value - raise an error
406: --
407: -- The profile option has an invalid value - raise an error
408: --
409: IF l_debug_on THEN
410: WSH_DEBUG_SV.logmsg(l_module_name,'The profile option has an invalid value - raise an error RAISE FTE_DIST_INVALID_PROFILE');
411: END IF;
412:
413: RAISE FTE_DIST_INVALID_PROFILE;
414:
419: --
420: -- region type is null cannot have that Raise an error
421: --
422: IF l_debug_on THEN
423: WSH_DEBUG_SV.logmsg(l_module_name,'region type is null - raise an error FTE_DIST_NULL_REGION_TYPE');
424: END IF;
425: RAISE FTE_DIST_NULL_REGION_TYPE;
426:
427: END IF;
435: -- The input is in location id form - convert the
436: -- locations to regions
437: --
438: IF l_debug_on THEN
439: WSH_DEBUG_SV.logmsg(l_module_name,'The input is in location id form going to get region mappings');
440: WSH_DEBUG_SV.log(l_module_name,'l_region_type = ',l_region_type);
441: END IF;
442:
443: --
436: -- locations to regions
437: --
438: IF l_debug_on THEN
439: WSH_DEBUG_SV.logmsg(l_module_name,'The input is in location id form going to get region mappings');
440: WSH_DEBUG_SV.log(l_module_name,'l_region_type = ',l_region_type);
441: END IF;
442:
443: --
444: -- Search for origin region ids from the origin location id
445: --
446: -- reset the origin result tables
447: --
448: IF l_debug_on THEN
449: WSH_DEBUG_SV.logmsg(l_module_name,' Search for origin region ids from the origin location id - reset the origin result tables');
450: END IF;
451:
452: l_origin_location_id_tab.DELETE;
453: l_origin_region_id_tab.DELETE;
453: l_origin_region_id_tab.DELETE;
454: l_loc_region_id_origin_tab.DELETE;
455:
456: IF l_debug_on THEN
457: WSH_DEBUG_SV.logmsg(l_module_name,'LOOPING and opening cursor c_get_regions_for_locs(orig)');
458: END IF;
459:
460:
461: --
462: -- execute the query to get all the region ids for the passed in origin locations
463: --
464: FOR aaa IN p_distance_input_tab.FIRST..p_distance_input_tab.LAST LOOP
465: IF l_debug_on THEN
466: WSH_DEBUG_SV.logmsg(l_module_name,'Open c_get_regions_for_locs (orig) with ...');
467: WSH_DEBUG_SV.log(l_module_name,'aaa p_distance_input_tab(aaa).origin_id = ',p_distance_input_tab(aaa).origin_id);
468: WSH_DEBUG_SV.log(l_module_name,'l_region_type = ',l_region_type);
469: END IF;
470:
463: --
464: FOR aaa IN p_distance_input_tab.FIRST..p_distance_input_tab.LAST LOOP
465: IF l_debug_on THEN
466: WSH_DEBUG_SV.logmsg(l_module_name,'Open c_get_regions_for_locs (orig) with ...');
467: WSH_DEBUG_SV.log(l_module_name,'aaa p_distance_input_tab(aaa).origin_id = ',p_distance_input_tab(aaa).origin_id);
468: WSH_DEBUG_SV.log(l_module_name,'l_region_type = ',l_region_type);
469: END IF;
470:
471: OPEN c_get_regions_for_locs(p_distance_input_tab(aaa).origin_id,
464: FOR aaa IN p_distance_input_tab.FIRST..p_distance_input_tab.LAST LOOP
465: IF l_debug_on THEN
466: WSH_DEBUG_SV.logmsg(l_module_name,'Open c_get_regions_for_locs (orig) with ...');
467: WSH_DEBUG_SV.log(l_module_name,'aaa p_distance_input_tab(aaa).origin_id = ',p_distance_input_tab(aaa).origin_id);
468: WSH_DEBUG_SV.log(l_module_name,'l_region_type = ',l_region_type);
469: END IF;
470:
471: OPEN c_get_regions_for_locs(p_distance_input_tab(aaa).origin_id,
472: l_region_type);
482: -- Origin regions and locations were returned by the query, now we create mapping
483: -- tables of locations to regions
484: --
485: IF l_debug_on THEN
486: WSH_DEBUG_SV.log(l_module_name,'l_origin_location_id_tab.COUNT = ',l_origin_location_id_tab.COUNT);
487: END IF;
488:
489: FOR bbb in l_origin_location_id_tab.FIRST..l_origin_location_id_tab.LAST LOOP
490: IF l_debug_on THEN
487: END IF;
488:
489: FOR bbb in l_origin_location_id_tab.FIRST..l_origin_location_id_tab.LAST LOOP
490: IF l_debug_on THEN
491: WSH_DEBUG_SV.log(l_module_name,'l_origin_location_id_tab(bbb) = ',l_origin_location_id_tab(bbb));
492: END IF;
493:
494: l_loc_region_id_origin_tab(l_origin_location_id_tab(bbb)) := l_origin_region_id_tab(bbb);
495: IF l_debug_on THEN
492: END IF;
493:
494: l_loc_region_id_origin_tab(l_origin_location_id_tab(bbb)) := l_origin_region_id_tab(bbb);
495: IF l_debug_on THEN
496: WSH_DEBUG_SV.log(l_module_name,'l_loc_region_id_origin_tab(l_origin_location_id_tab(bbb)) =',l_loc_region_id_origin_tab(l_origin_location_id_tab(bbb)));
497: END IF;
498: END LOOP;
499: END IF;
500:
505: --
506: -- Reset the destination result tables
507: --
508: IF l_debug_on THEN
509: WSH_DEBUG_SV.logmsg(l_module_name,' Search for destination region ids from the destination location id - reset the destination result tables');
510: END IF;
511:
512: l_destination_location_id_tab.DELETE;
513: l_destination_region_id_tab.DELETE;
513: l_destination_region_id_tab.DELETE;
514: l_loc_region_id_dest_tab.DELETE;
515:
516: IF l_debug_on THEN
517: WSH_DEBUG_SV.logmsg(l_module_name,'LOOPING and opening cursor c_get_regions_for_locs (dest)');
518: END IF;
519:
520:
521: --
524: --
525: FOR ccc IN p_distance_input_tab.FIRST..p_distance_input_tab.LAST LOOP
526:
527: IF l_debug_on THEN
528: WSH_DEBUG_SV.logmsg(l_module_name,'Open c_get_regions_for_locs (dest) with ...');
529: WSH_DEBUG_SV.log(l_module_name,'aaa p_distance_input_tab(ccc).destination_id = ',p_distance_input_tab(ccc).destination_id);
530: WSH_DEBUG_SV.log(l_module_name,'l_region_type = ',l_region_type);
531: END IF;
532:
525: FOR ccc IN p_distance_input_tab.FIRST..p_distance_input_tab.LAST LOOP
526:
527: IF l_debug_on THEN
528: WSH_DEBUG_SV.logmsg(l_module_name,'Open c_get_regions_for_locs (dest) with ...');
529: WSH_DEBUG_SV.log(l_module_name,'aaa p_distance_input_tab(ccc).destination_id = ',p_distance_input_tab(ccc).destination_id);
530: WSH_DEBUG_SV.log(l_module_name,'l_region_type = ',l_region_type);
531: END IF;
532:
533: OPEN c_get_regions_for_locs(p_distance_input_tab(ccc).destination_id,
526:
527: IF l_debug_on THEN
528: WSH_DEBUG_SV.logmsg(l_module_name,'Open c_get_regions_for_locs (dest) with ...');
529: WSH_DEBUG_SV.log(l_module_name,'aaa p_distance_input_tab(ccc).destination_id = ',p_distance_input_tab(ccc).destination_id);
530: WSH_DEBUG_SV.log(l_module_name,'l_region_type = ',l_region_type);
531: END IF;
532:
533: OPEN c_get_regions_for_locs(p_distance_input_tab(ccc).destination_id,
534: l_region_type);
543: -- The query returned some records now create mapping tables of locations
544: -- to regions
545: --
546: IF l_debug_on THEN
547: WSH_DEBUG_SV.log(l_module_name,'l_destination_location_id_tab.COUNT = ',l_destination_location_id_tab.COUNT);
548: END IF;
549:
550:
551: FOR ddd in l_destination_location_id_tab.FIRST..l_destination_location_id_tab.LAST LOOP
549:
550:
551: FOR ddd in l_destination_location_id_tab.FIRST..l_destination_location_id_tab.LAST LOOP
552: IF l_debug_on THEN
553: WSH_DEBUG_SV.log(l_module_name,'l_destination_location_id_tab(ddd) = ',l_destination_location_id_tab(ddd));
554: END IF;
555:
556: l_loc_region_id_dest_tab(l_destination_location_id_tab(ddd)) := l_destination_region_id_tab(ddd);
557:
555:
556: l_loc_region_id_dest_tab(l_destination_location_id_tab(ddd)) := l_destination_region_id_tab(ddd);
557:
558: IF l_debug_on THEN
559: WSH_DEBUG_SV.log(l_module_name,'l_loc_region_id_dest_tab(l_destination_location_id_tab(ddd)) =',l_loc_region_id_dest_tab(l_destination_location_id_tab(ddd)));
560: END IF;
561: END LOOP;
562: END IF;
563: END LOOP;
569: -- any input locations do not have regions, i.e. were not
570: -- found during the search
571: --
572: IF l_debug_on THEN
573: WSH_DEBUG_SV.logmsg(l_module_name,'Populate the search table with OD pairs and Check ifany input locations do not have regions');
574: END IF;
575:
576: --
577: -- reset the search table index
579: l_ctr := 0;
580: FOR eee IN p_distance_input_tab.FIRST..p_distance_input_tab.LAST LOOP
581:
582: IF l_debug_on THEN
583: WSH_DEBUG_SV.log(l_module_name,'p_distance_input_tab(eee).origin_id = ',p_distance_input_tab(eee).origin_id);
584: WSH_DEBUG_SV.log(l_module_name,'p_distance_input_tab(eee).destination_id = ',p_distance_input_tab(eee).destination_id);
585: END IF;
586:
587:
580: FOR eee IN p_distance_input_tab.FIRST..p_distance_input_tab.LAST LOOP
581:
582: IF l_debug_on THEN
583: WSH_DEBUG_SV.log(l_module_name,'p_distance_input_tab(eee).origin_id = ',p_distance_input_tab(eee).origin_id);
584: WSH_DEBUG_SV.log(l_module_name,'p_distance_input_tab(eee).destination_id = ',p_distance_input_tab(eee).destination_id);
585: END IF;
586:
587:
588: IF (l_loc_region_id_origin_tab.EXISTS(p_distance_input_tab(eee).origin_id)) THEN
586:
587:
588: IF (l_loc_region_id_origin_tab.EXISTS(p_distance_input_tab(eee).origin_id)) THEN
589: IF l_debug_on THEN
590: WSH_DEBUG_SV.log(l_module_name,'A region for the location exists p_distance_input_tab(eee).origin_id = ',p_distance_input_tab(eee).origin_id);
591: END IF;
592:
593: --
594: -- origin exists, check the destination
594: -- origin exists, check the destination
595: --
596: IF (l_loc_region_id_dest_tab.EXISTS(p_distance_input_tab(eee).destination_id)) THEN
597: IF l_debug_on THEN
598: WSH_DEBUG_SV.log(l_module_name,'A region for the destination location exists p_distance_input_tab(eee).destination_id = ',p_distance_input_tab(eee).destination_id);
599: END IF;
600:
601: --
602: -- destination exists put it in the search tab
601: --
602: -- destination exists put it in the search tab
603: --
604: IF l_debug_on THEN
605: WSH_DEBUG_SV.logmsg(l_module_name,'Add the OD pair to the search table');
606: END IF;
607:
608: --
609: -- increment the index counter
627: -- The destination location has no region
628: -- log a message do not include it in the search
629: --
630: IF l_debug_on THEN
631: WSH_DEBUG_SV.logmsg(l_module_name,'The destination location has no region log a message do not include it in the search');
632: END IF;
633:
634:
635: IF (p_messaging_yn = 'Y') THEN
633:
634:
635: IF (p_messaging_yn = 'Y') THEN
636: IF l_debug_on THEN
637: WSH_DEBUG_SV.logmsg(l_module_name,'logging to the message table NO_REGION_MAP_D');
638: END IF;
639:
640: l_msg_ctr := msg_message_text_tab.COUNT + 1;
641: FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_REGN_MAP_DEST_LOC');
656: -- The origin location has no region
657: -- log a message - do not include it in the search
658: --
659: IF l_debug_on THEN
660: WSH_DEBUG_SV.logmsg(l_module_name,'The origin location has no region log a message do not include it in the search');
661: END IF;
662:
663:
664: IF (p_messaging_yn = 'Y') THEN
662:
663:
664: IF (p_messaging_yn = 'Y') THEN
665: IF l_debug_on THEN
666: WSH_DEBUG_SV.logmsg(l_module_name,'logging to the message table NO_REGION_MAP_O');
667: END IF;
668:
669: l_msg_ctr := msg_message_text_tab.COUNT + 1;
670: FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_REGN_MAP_ORIG_LOC');
689: -- All messages have been logged in the temp tables now add the messages to
690: -- the global tables
691: --
692: IF l_debug_on THEN
693: WSH_DEBUG_SV.logmsg(l_module_name,'Messaging is on and nessages exist Log the messages in the global message table');
694: END IF;
695:
696: --
697: -- Log the messages in the global message table
699: --
700: -- Debug Statements
701: --
702: IF l_debug_on THEN
703: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_DIST_INT_PKG.LOG_DISTANCE_MESSAGES',WSH_DEBUG_SV.C_PROC_LEVEL);
704: END IF;
705: --
706: FTE_DIST_INT_PKG.LOG_DISTANCE_MESSAGES(p_message_type_tab => msg_message_type_tab,
707: p_message_code_tab => msg_message_code_tab,
714: p_input_destination_tab => msg_input_destination_tab,
715: x_return_status => l_return_status,
716: x_return_message => l_return_message);
717: IF l_debug_on THEN
718: WSH_DEBUG_SV.logmsg(l_module_name,'Back from calling FTE_DIST_INT_PKG.LOG_DISTANCE_MESSAGES');
719: END IF;
720: END IF;
721:
722:
739: -- The input is in region id form which means that we can go ahead
740: -- and search without converting
741: --
742: IF l_debug_on THEN
743: WSH_DEBUG_SV.logmsg(l_module_name,'Input IDs are in region form, no need to convert just add to the search table');
744: END IF;
745:
746:
747: --
751: --
752: l_ctr := 0;
753: FOR fff IN p_distance_input_tab.FIRST..p_distance_input_tab.LAST LOOP
754: IF l_debug_on THEN
755: WSH_DEBUG_SV.logmsg(l_module_name,'Adding to the search table');
756: WSH_DEBUG_SV.log(l_module_name,'p_distance_input_tab(fff).origin_id = ',p_distance_input_tab(fff).origin_id);
757: WSH_DEBUG_SV.log(l_module_name,'p_distance_input_tab(fff).destination_id = ',p_distance_input_tab(fff).destination_id);
758: END IF;
759:
752: l_ctr := 0;
753: FOR fff IN p_distance_input_tab.FIRST..p_distance_input_tab.LAST LOOP
754: IF l_debug_on THEN
755: WSH_DEBUG_SV.logmsg(l_module_name,'Adding to the search table');
756: WSH_DEBUG_SV.log(l_module_name,'p_distance_input_tab(fff).origin_id = ',p_distance_input_tab(fff).origin_id);
757: WSH_DEBUG_SV.log(l_module_name,'p_distance_input_tab(fff).destination_id = ',p_distance_input_tab(fff).destination_id);
758: END IF;
759:
760:
753: FOR fff IN p_distance_input_tab.FIRST..p_distance_input_tab.LAST LOOP
754: IF l_debug_on THEN
755: WSH_DEBUG_SV.logmsg(l_module_name,'Adding to the search table');
756: WSH_DEBUG_SV.log(l_module_name,'p_distance_input_tab(fff).origin_id = ',p_distance_input_tab(fff).origin_id);
757: WSH_DEBUG_SV.log(l_module_name,'p_distance_input_tab(fff).destination_id = ',p_distance_input_tab(fff).destination_id);
758: END IF;
759:
760:
761: l_ctr := l_ctr + 1;
778: END IF;
779:
780:
781: IF l_debug_on THEN
782: WSH_DEBUG_SV.logmsg(l_module_name,'Check the search table is populated');
783: END IF;
784:
785:
786: IF (l_search_tab.COUNT > 0) THEN
788: -- We have O/D pairs to search with
789: -- Call the Distance search procedure
790: --
791: IF l_debug_on THEN
792: WSH_DEBUG_SV.log(l_module_name,'records exist in search table l_search_tab.COUNT = ',l_search_tab.COUNT);
793: END IF;
794:
795:
796: --
802: --
803: -- Debug Statements
804: --
805: IF l_debug_on THEN
806: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_DIST_INT_PKG.DISTANCE_SEARCH',WSH_DEBUG_SV.C_PROC_LEVEL);
807: WSH_DEBUG_SV.logmsg(l_module_name,'---------- INPUT PARAMETERS -------------');
808: WSH_DEBUG_SV.log(l_module_name,'p_location_region_flag',p_location_region_flag);
809: WSH_DEBUG_SV.log(l_module_name,'p_messaging_yn',p_messaging_yn);
810: WSH_DEBUG_SV.log(l_module_name,'p_search_tab count = ', l_search_tab.COUNT);
803: -- Debug Statements
804: --
805: IF l_debug_on THEN
806: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_DIST_INT_PKG.DISTANCE_SEARCH',WSH_DEBUG_SV.C_PROC_LEVEL);
807: WSH_DEBUG_SV.logmsg(l_module_name,'---------- INPUT PARAMETERS -------------');
808: WSH_DEBUG_SV.log(l_module_name,'p_location_region_flag',p_location_region_flag);
809: WSH_DEBUG_SV.log(l_module_name,'p_messaging_yn',p_messaging_yn);
810: WSH_DEBUG_SV.log(l_module_name,'p_search_tab count = ', l_search_tab.COUNT);
811: IF (l_search_tab.COUNT > 0) THEN
804: --
805: IF l_debug_on THEN
806: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_DIST_INT_PKG.DISTANCE_SEARCH',WSH_DEBUG_SV.C_PROC_LEVEL);
807: WSH_DEBUG_SV.logmsg(l_module_name,'---------- INPUT PARAMETERS -------------');
808: WSH_DEBUG_SV.log(l_module_name,'p_location_region_flag',p_location_region_flag);
809: WSH_DEBUG_SV.log(l_module_name,'p_messaging_yn',p_messaging_yn);
810: WSH_DEBUG_SV.log(l_module_name,'p_search_tab count = ', l_search_tab.COUNT);
811: IF (l_search_tab.COUNT > 0) THEN
812: FOR dbst IN l_search_tab.FIRST..l_search_tab.LAST LOOP
805: IF l_debug_on THEN
806: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_DIST_INT_PKG.DISTANCE_SEARCH',WSH_DEBUG_SV.C_PROC_LEVEL);
807: WSH_DEBUG_SV.logmsg(l_module_name,'---------- INPUT PARAMETERS -------------');
808: WSH_DEBUG_SV.log(l_module_name,'p_location_region_flag',p_location_region_flag);
809: WSH_DEBUG_SV.log(l_module_name,'p_messaging_yn',p_messaging_yn);
810: WSH_DEBUG_SV.log(l_module_name,'p_search_tab count = ', l_search_tab.COUNT);
811: IF (l_search_tab.COUNT > 0) THEN
812: FOR dbst IN l_search_tab.FIRST..l_search_tab.LAST LOOP
813: WSH_DEBUG_SV.log(l_module_name,'l_search_tab(dbst).origin_id', l_search_tab(dbst).origin_id);
806: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_DIST_INT_PKG.DISTANCE_SEARCH',WSH_DEBUG_SV.C_PROC_LEVEL);
807: WSH_DEBUG_SV.logmsg(l_module_name,'---------- INPUT PARAMETERS -------------');
808: WSH_DEBUG_SV.log(l_module_name,'p_location_region_flag',p_location_region_flag);
809: WSH_DEBUG_SV.log(l_module_name,'p_messaging_yn',p_messaging_yn);
810: WSH_DEBUG_SV.log(l_module_name,'p_search_tab count = ', l_search_tab.COUNT);
811: IF (l_search_tab.COUNT > 0) THEN
812: FOR dbst IN l_search_tab.FIRST..l_search_tab.LAST LOOP
813: WSH_DEBUG_SV.log(l_module_name,'l_search_tab(dbst).origin_id', l_search_tab(dbst).origin_id);
814: WSH_DEBUG_SV.log(l_module_name,'l_search_tab(dbst).destination_id', l_search_tab(dbst).destination_id);
809: WSH_DEBUG_SV.log(l_module_name,'p_messaging_yn',p_messaging_yn);
810: WSH_DEBUG_SV.log(l_module_name,'p_search_tab count = ', l_search_tab.COUNT);
811: IF (l_search_tab.COUNT > 0) THEN
812: FOR dbst IN l_search_tab.FIRST..l_search_tab.LAST LOOP
813: WSH_DEBUG_SV.log(l_module_name,'l_search_tab(dbst).origin_id', l_search_tab(dbst).origin_id);
814: WSH_DEBUG_SV.log(l_module_name,'l_search_tab(dbst).destination_id', l_search_tab(dbst).destination_id);
815: WSH_DEBUG_SV.log(l_module_name,'l_search_tab(dbst).origin_loc_id',l_search_tab(dbst).origin_loc_id);
816: WSH_DEBUG_SV.log(l_module_name,'l_search_tab(dbst).dest_loc_id',l_search_tab(dbst).dest_loc_id);
817: END LOOP;
810: WSH_DEBUG_SV.log(l_module_name,'p_search_tab count = ', l_search_tab.COUNT);
811: IF (l_search_tab.COUNT > 0) THEN
812: FOR dbst IN l_search_tab.FIRST..l_search_tab.LAST LOOP
813: WSH_DEBUG_SV.log(l_module_name,'l_search_tab(dbst).origin_id', l_search_tab(dbst).origin_id);
814: WSH_DEBUG_SV.log(l_module_name,'l_search_tab(dbst).destination_id', l_search_tab(dbst).destination_id);
815: WSH_DEBUG_SV.log(l_module_name,'l_search_tab(dbst).origin_loc_id',l_search_tab(dbst).origin_loc_id);
816: WSH_DEBUG_SV.log(l_module_name,'l_search_tab(dbst).dest_loc_id',l_search_tab(dbst).dest_loc_id);
817: END LOOP;
818: END IF;
811: IF (l_search_tab.COUNT > 0) THEN
812: FOR dbst IN l_search_tab.FIRST..l_search_tab.LAST LOOP
813: WSH_DEBUG_SV.log(l_module_name,'l_search_tab(dbst).origin_id', l_search_tab(dbst).origin_id);
814: WSH_DEBUG_SV.log(l_module_name,'l_search_tab(dbst).destination_id', l_search_tab(dbst).destination_id);
815: WSH_DEBUG_SV.log(l_module_name,'l_search_tab(dbst).origin_loc_id',l_search_tab(dbst).origin_loc_id);
816: WSH_DEBUG_SV.log(l_module_name,'l_search_tab(dbst).dest_loc_id',l_search_tab(dbst).dest_loc_id);
817: END LOOP;
818: END IF;
819: WSH_DEBUG_SV.log(l_module_name,'p_origin_reg_loc_tab count = ', l_region_idx_loc_orig_tab.COUNT);
812: FOR dbst IN l_search_tab.FIRST..l_search_tab.LAST LOOP
813: WSH_DEBUG_SV.log(l_module_name,'l_search_tab(dbst).origin_id', l_search_tab(dbst).origin_id);
814: WSH_DEBUG_SV.log(l_module_name,'l_search_tab(dbst).destination_id', l_search_tab(dbst).destination_id);
815: WSH_DEBUG_SV.log(l_module_name,'l_search_tab(dbst).origin_loc_id',l_search_tab(dbst).origin_loc_id);
816: WSH_DEBUG_SV.log(l_module_name,'l_search_tab(dbst).dest_loc_id',l_search_tab(dbst).dest_loc_id);
817: END LOOP;
818: END IF;
819: WSH_DEBUG_SV.log(l_module_name,'p_origin_reg_loc_tab count = ', l_region_idx_loc_orig_tab.COUNT);
820: IF (l_region_idx_loc_orig_tab.COUNT > 0) THEN
815: WSH_DEBUG_SV.log(l_module_name,'l_search_tab(dbst).origin_loc_id',l_search_tab(dbst).origin_loc_id);
816: WSH_DEBUG_SV.log(l_module_name,'l_search_tab(dbst).dest_loc_id',l_search_tab(dbst).dest_loc_id);
817: END LOOP;
818: END IF;
819: WSH_DEBUG_SV.log(l_module_name,'p_origin_reg_loc_tab count = ', l_region_idx_loc_orig_tab.COUNT);
820: IF (l_region_idx_loc_orig_tab.COUNT > 0) THEN
821: FOR dbrilo IN l_region_idx_loc_orig_tab.FIRST..l_region_idx_loc_orig_tab.LAST LOOP
822: IF (l_region_idx_loc_orig_tab.EXISTS(dbrilo)) THEN
823: WSH_DEBUG_SV.log(l_module_name,'(dbrilo)',dbrilo);
819: WSH_DEBUG_SV.log(l_module_name,'p_origin_reg_loc_tab count = ', l_region_idx_loc_orig_tab.COUNT);
820: IF (l_region_idx_loc_orig_tab.COUNT > 0) THEN
821: FOR dbrilo IN l_region_idx_loc_orig_tab.FIRST..l_region_idx_loc_orig_tab.LAST LOOP
822: IF (l_region_idx_loc_orig_tab.EXISTS(dbrilo)) THEN
823: WSH_DEBUG_SV.log(l_module_name,'(dbrilo)',dbrilo);
824: WSH_DEBUG_SV.log(l_module_name,'l_region_idx_loc_orig_tab(dbrilo)',l_region_idx_loc_orig_tab(dbrilo));
825: END IF;
826: END LOOP;
827: END IF;
820: IF (l_region_idx_loc_orig_tab.COUNT > 0) THEN
821: FOR dbrilo IN l_region_idx_loc_orig_tab.FIRST..l_region_idx_loc_orig_tab.LAST LOOP
822: IF (l_region_idx_loc_orig_tab.EXISTS(dbrilo)) THEN
823: WSH_DEBUG_SV.log(l_module_name,'(dbrilo)',dbrilo);
824: WSH_DEBUG_SV.log(l_module_name,'l_region_idx_loc_orig_tab(dbrilo)',l_region_idx_loc_orig_tab(dbrilo));
825: END IF;
826: END LOOP;
827: END IF;
828: WSH_DEBUG_SV.log(l_module_name,'p_dest_reg_loc_tab count = ', l_region_idx_loc_dest_tab.COUNT);
824: WSH_DEBUG_SV.log(l_module_name,'l_region_idx_loc_orig_tab(dbrilo)',l_region_idx_loc_orig_tab(dbrilo));
825: END IF;
826: END LOOP;
827: END IF;
828: WSH_DEBUG_SV.log(l_module_name,'p_dest_reg_loc_tab count = ', l_region_idx_loc_dest_tab.COUNT);
829: IF (l_region_idx_loc_dest_tab.COUNT > 0) THEN
830: FOR dbrild IN l_region_idx_loc_dest_tab.FIRST..l_region_idx_loc_dest_tab.LAST LOOP
831: IF (l_region_idx_loc_dest_tab.EXISTS(dbrild)) THEN
832: WSH_DEBUG_SV.log(l_module_name,'(dbrild)',dbrild);
828: WSH_DEBUG_SV.log(l_module_name,'p_dest_reg_loc_tab count = ', l_region_idx_loc_dest_tab.COUNT);
829: IF (l_region_idx_loc_dest_tab.COUNT > 0) THEN
830: FOR dbrild IN l_region_idx_loc_dest_tab.FIRST..l_region_idx_loc_dest_tab.LAST LOOP
831: IF (l_region_idx_loc_dest_tab.EXISTS(dbrild)) THEN
832: WSH_DEBUG_SV.log(l_module_name,'(dbrild)',dbrild);
833: WSH_DEBUG_SV.log(l_module_name,'l_region_idx_loc_dest_tab(dbrild)',l_region_idx_loc_dest_tab(dbrild));
834: END IF;
835: END LOOP;
836: END IF;
829: IF (l_region_idx_loc_dest_tab.COUNT > 0) THEN
830: FOR dbrild IN l_region_idx_loc_dest_tab.FIRST..l_region_idx_loc_dest_tab.LAST LOOP
831: IF (l_region_idx_loc_dest_tab.EXISTS(dbrild)) THEN
832: WSH_DEBUG_SV.log(l_module_name,'(dbrild)',dbrild);
833: WSH_DEBUG_SV.log(l_module_name,'l_region_idx_loc_dest_tab(dbrild)',l_region_idx_loc_dest_tab(dbrild));
834: END IF;
835: END LOOP;
836: END IF;
837: END IF;
848: x_return_message => l_return_message,
849: x_return_status => l_return_status);
850:
851: IF l_debug_on THEN
852: WSH_DEBUG_SV.logmsg(l_module_name,'------- back from FTE_DIST_INT_PKG.DISTANCE_SEARCH -----');
853: WSH_DEBUG_SV.logmsg(l_module_name,'---------- OUTPUT PARAMETERS -------------');
854: WSH_DEBUG_SV.log(l_module_name,'x_result_found',l_result_found_flag);
855: WSH_DEBUG_SV.log(l_module_name,'x_result_table count = ',l_result_table.COUNT);
856: WSH_DEBUG_SV.log(l_module_name,'x_return_message',l_return_message);
849: x_return_status => l_return_status);
850:
851: IF l_debug_on THEN
852: WSH_DEBUG_SV.logmsg(l_module_name,'------- back from FTE_DIST_INT_PKG.DISTANCE_SEARCH -----');
853: WSH_DEBUG_SV.logmsg(l_module_name,'---------- OUTPUT PARAMETERS -------------');
854: WSH_DEBUG_SV.log(l_module_name,'x_result_found',l_result_found_flag);
855: WSH_DEBUG_SV.log(l_module_name,'x_result_table count = ',l_result_table.COUNT);
856: WSH_DEBUG_SV.log(l_module_name,'x_return_message',l_return_message);
857: WSH_DEBUG_SV.log(l_module_name,'x_return_status',l_return_status);
850:
851: IF l_debug_on THEN
852: WSH_DEBUG_SV.logmsg(l_module_name,'------- back from FTE_DIST_INT_PKG.DISTANCE_SEARCH -----');
853: WSH_DEBUG_SV.logmsg(l_module_name,'---------- OUTPUT PARAMETERS -------------');
854: WSH_DEBUG_SV.log(l_module_name,'x_result_found',l_result_found_flag);
855: WSH_DEBUG_SV.log(l_module_name,'x_result_table count = ',l_result_table.COUNT);
856: WSH_DEBUG_SV.log(l_module_name,'x_return_message',l_return_message);
857: WSH_DEBUG_SV.log(l_module_name,'x_return_status',l_return_status);
858: END IF;
851: IF l_debug_on THEN
852: WSH_DEBUG_SV.logmsg(l_module_name,'------- back from FTE_DIST_INT_PKG.DISTANCE_SEARCH -----');
853: WSH_DEBUG_SV.logmsg(l_module_name,'---------- OUTPUT PARAMETERS -------------');
854: WSH_DEBUG_SV.log(l_module_name,'x_result_found',l_result_found_flag);
855: WSH_DEBUG_SV.log(l_module_name,'x_result_table count = ',l_result_table.COUNT);
856: WSH_DEBUG_SV.log(l_module_name,'x_return_message',l_return_message);
857: WSH_DEBUG_SV.log(l_module_name,'x_return_status',l_return_status);
858: END IF;
859:
852: WSH_DEBUG_SV.logmsg(l_module_name,'------- back from FTE_DIST_INT_PKG.DISTANCE_SEARCH -----');
853: WSH_DEBUG_SV.logmsg(l_module_name,'---------- OUTPUT PARAMETERS -------------');
854: WSH_DEBUG_SV.log(l_module_name,'x_result_found',l_result_found_flag);
855: WSH_DEBUG_SV.log(l_module_name,'x_result_table count = ',l_result_table.COUNT);
856: WSH_DEBUG_SV.log(l_module_name,'x_return_message',l_return_message);
857: WSH_DEBUG_SV.log(l_module_name,'x_return_status',l_return_status);
858: END IF;
859:
860:
853: WSH_DEBUG_SV.logmsg(l_module_name,'---------- OUTPUT PARAMETERS -------------');
854: WSH_DEBUG_SV.log(l_module_name,'x_result_found',l_result_found_flag);
855: WSH_DEBUG_SV.log(l_module_name,'x_result_table count = ',l_result_table.COUNT);
856: WSH_DEBUG_SV.log(l_module_name,'x_return_message',l_return_message);
857: WSH_DEBUG_SV.log(l_module_name,'x_return_status',l_return_status);
858: END IF;
859:
860:
861: IF ((l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR
863: --
864: -- A serious error occurred performing the search
865: --
866: IF l_debug_on THEN
867: WSH_DEBUG_SV.logmsg(l_module_name,'A serious error occurred performing the search - RAISE FTE_DIST_DISTANCE_SEARCH_ERR');
868: END IF;
869:
870:
871: RAISE FTE_DIST_DISTANCE_SEARCH_ERR;
878: -- at all
879: -- Raise an error and return
880: --
881: IF l_debug_on THEN
882: WSH_DEBUG_SV.logmsg(l_module_name,'The search table is empty which means that there are no regions to search for');
883:
884: END IF;
885:
886:
888: --
889: -- No regions were found for any location ids - raise an error
890: --
891: IF l_debug_on THEN
892: WSH_DEBUG_SV.logmsg(l_module_name,'No regions were found for any location ids - raise an error FTE_DIST_NO_REGS_FOR_ANY_LOCS');
893: END IF;
894:
895: RAISE FTE_DIST_NO_REGS_FOR_ANY_LOCS;
896:
899: -- something went horribly wrong, none of the regions populated
900: -- in the search table!
901: --
902: IF l_debug_on THEN
903: WSH_DEBUG_SV.logmsg(l_module_name,'something went horribly wrong, none of the regions populatedin the search table - RAISE FTE_DIST_NO_REGS_IN_SEARCH');
904: END IF;
905:
906: RAISE FTE_DIST_NO_REGS_IN_SEARCH;
907:
914: --
915: -- a result has been found return to the calling program
916: --
917: IF l_debug_on THEN
918: WSH_DEBUG_SV.logmsg(l_module_name,'The search executed successfully and at least 1 result was found');
919: IF (l_result_table.COUNT > 0) THEN
920: FOR sss in l_result_table.FIRST..l_result_table.LAST LOOP
921: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).location_region_flag = ',l_result_table(sss).location_region_flag);
922: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).origin_location_id = ',l_result_table(sss).origin_location_id);
917: IF l_debug_on THEN
918: WSH_DEBUG_SV.logmsg(l_module_name,'The search executed successfully and at least 1 result was found');
919: IF (l_result_table.COUNT > 0) THEN
920: FOR sss in l_result_table.FIRST..l_result_table.LAST LOOP
921: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).location_region_flag = ',l_result_table(sss).location_region_flag);
922: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).origin_location_id = ',l_result_table(sss).origin_location_id);
923: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).destination_location_id = ',l_result_table(sss).destination_location_id);
924: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).origin_region_id = ',l_result_table(sss).origin_region_id);
925: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).destination_region_id = ',l_result_table(sss).destination_region_id);
918: WSH_DEBUG_SV.logmsg(l_module_name,'The search executed successfully and at least 1 result was found');
919: IF (l_result_table.COUNT > 0) THEN
920: FOR sss in l_result_table.FIRST..l_result_table.LAST LOOP
921: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).location_region_flag = ',l_result_table(sss).location_region_flag);
922: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).origin_location_id = ',l_result_table(sss).origin_location_id);
923: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).destination_location_id = ',l_result_table(sss).destination_location_id);
924: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).origin_region_id = ',l_result_table(sss).origin_region_id);
925: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).destination_region_id = ',l_result_table(sss).destination_region_id);
926: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).type = ',l_result_table(sss).type);
919: IF (l_result_table.COUNT > 0) THEN
920: FOR sss in l_result_table.FIRST..l_result_table.LAST LOOP
921: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).location_region_flag = ',l_result_table(sss).location_region_flag);
922: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).origin_location_id = ',l_result_table(sss).origin_location_id);
923: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).destination_location_id = ',l_result_table(sss).destination_location_id);
924: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).origin_region_id = ',l_result_table(sss).origin_region_id);
925: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).destination_region_id = ',l_result_table(sss).destination_region_id);
926: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).type = ',l_result_table(sss).type);
927: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).distance = ',l_result_table(sss).distance);
920: FOR sss in l_result_table.FIRST..l_result_table.LAST LOOP
921: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).location_region_flag = ',l_result_table(sss).location_region_flag);
922: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).origin_location_id = ',l_result_table(sss).origin_location_id);
923: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).destination_location_id = ',l_result_table(sss).destination_location_id);
924: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).origin_region_id = ',l_result_table(sss).origin_region_id);
925: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).destination_region_id = ',l_result_table(sss).destination_region_id);
926: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).type = ',l_result_table(sss).type);
927: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).distance = ',l_result_table(sss).distance);
928: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).distance_uom = ',l_result_table(sss).distance_uom);
921: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).location_region_flag = ',l_result_table(sss).location_region_flag);
922: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).origin_location_id = ',l_result_table(sss).origin_location_id);
923: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).destination_location_id = ',l_result_table(sss).destination_location_id);
924: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).origin_region_id = ',l_result_table(sss).origin_region_id);
925: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).destination_region_id = ',l_result_table(sss).destination_region_id);
926: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).type = ',l_result_table(sss).type);
927: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).distance = ',l_result_table(sss).distance);
928: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).distance_uom = ',l_result_table(sss).distance_uom);
929: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).transit_time = ',l_result_table(sss).transit_time);
922: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).origin_location_id = ',l_result_table(sss).origin_location_id);
923: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).destination_location_id = ',l_result_table(sss).destination_location_id);
924: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).origin_region_id = ',l_result_table(sss).origin_region_id);
925: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).destination_region_id = ',l_result_table(sss).destination_region_id);
926: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).type = ',l_result_table(sss).type);
927: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).distance = ',l_result_table(sss).distance);
928: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).distance_uom = ',l_result_table(sss).distance_uom);
929: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).transit_time = ',l_result_table(sss).transit_time);
930: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).transit_time_uom = ',l_result_table(sss).transit_time_uom);
923: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).destination_location_id = ',l_result_table(sss).destination_location_id);
924: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).origin_region_id = ',l_result_table(sss).origin_region_id);
925: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).destination_region_id = ',l_result_table(sss).destination_region_id);
926: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).type = ',l_result_table(sss).type);
927: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).distance = ',l_result_table(sss).distance);
928: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).distance_uom = ',l_result_table(sss).distance_uom);
929: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).transit_time = ',l_result_table(sss).transit_time);
930: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).transit_time_uom = ',l_result_table(sss).transit_time_uom);
931: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).status = ',l_result_table(sss).status);
924: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).origin_region_id = ',l_result_table(sss).origin_region_id);
925: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).destination_region_id = ',l_result_table(sss).destination_region_id);
926: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).type = ',l_result_table(sss).type);
927: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).distance = ',l_result_table(sss).distance);
928: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).distance_uom = ',l_result_table(sss).distance_uom);
929: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).transit_time = ',l_result_table(sss).transit_time);
930: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).transit_time_uom = ',l_result_table(sss).transit_time_uom);
931: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).status = ',l_result_table(sss).status);
932: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).error_msg = ',l_result_table(sss).error_msg);
925: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).destination_region_id = ',l_result_table(sss).destination_region_id);
926: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).type = ',l_result_table(sss).type);
927: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).distance = ',l_result_table(sss).distance);
928: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).distance_uom = ',l_result_table(sss).distance_uom);
929: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).transit_time = ',l_result_table(sss).transit_time);
930: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).transit_time_uom = ',l_result_table(sss).transit_time_uom);
931: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).status = ',l_result_table(sss).status);
932: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).error_msg = ',l_result_table(sss).error_msg);
933: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).msg_id = ',l_result_table(sss).msg_id);
926: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).type = ',l_result_table(sss).type);
927: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).distance = ',l_result_table(sss).distance);
928: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).distance_uom = ',l_result_table(sss).distance_uom);
929: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).transit_time = ',l_result_table(sss).transit_time);
930: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).transit_time_uom = ',l_result_table(sss).transit_time_uom);
931: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).status = ',l_result_table(sss).status);
932: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).error_msg = ',l_result_table(sss).error_msg);
933: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).msg_id = ',l_result_table(sss).msg_id);
934: END LOOP;
927: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).distance = ',l_result_table(sss).distance);
928: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).distance_uom = ',l_result_table(sss).distance_uom);
929: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).transit_time = ',l_result_table(sss).transit_time);
930: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).transit_time_uom = ',l_result_table(sss).transit_time_uom);
931: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).status = ',l_result_table(sss).status);
932: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).error_msg = ',l_result_table(sss).error_msg);
933: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).msg_id = ',l_result_table(sss).msg_id);
934: END LOOP;
935: END IF;
928: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).distance_uom = ',l_result_table(sss).distance_uom);
929: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).transit_time = ',l_result_table(sss).transit_time);
930: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).transit_time_uom = ',l_result_table(sss).transit_time_uom);
931: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).status = ',l_result_table(sss).status);
932: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).error_msg = ',l_result_table(sss).error_msg);
933: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).msg_id = ',l_result_table(sss).msg_id);
934: END LOOP;
935: END IF;
936: END IF;
929: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).transit_time = ',l_result_table(sss).transit_time);
930: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).transit_time_uom = ',l_result_table(sss).transit_time_uom);
931: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).status = ',l_result_table(sss).status);
932: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).error_msg = ',l_result_table(sss).error_msg);
933: WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).msg_id = ',l_result_table(sss).msg_id);
934: END LOOP;
935: END IF;
936: END IF;
937:
944: --
945: -- Debug Statements
946: --
947: IF l_debug_on THEN
948: WSH_DEBUG_SV.pop(l_module_name);
949: END IF;
950: --
951: RETURN;
952: ELSE
953: --
954: -- If here, no errors, but no result found - return a warning indicator
955: --
956: IF l_debug_on THEN
957: WSH_DEBUG_SV.logmsg(l_module_name,'The search executed successfully but no records were found - return with a warning status');
958: END IF;
959:
960: x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
961: x_return_message := WSH_UTIL_CORE.G_RET_STS_WARNING;
965: --
966: -- Debug Statements
967: --
968: IF l_debug_on THEN
969: WSH_DEBUG_SV.pop(l_module_name);
970: END IF;
971: --
972: RETURN;
973: END IF;
976: --
977: -- Debug Statements
978: --
979: IF l_debug_on THEN
980: WSH_DEBUG_SV.pop(l_module_name);
981: END IF;
982: --
983:
984:
1000: --
1001: -- Debug Statements
1002: --
1003: IF l_debug_on THEN
1004: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_NO_INPUT_DATA RAISED');
1005: WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_INPUT_DATA exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1006: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_INPUT_DATA');
1007: END IF;
1008:
1001: -- Debug Statements
1002: --
1003: IF l_debug_on THEN
1004: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_NO_INPUT_DATA RAISED');
1005: WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_INPUT_DATA exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1006: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_INPUT_DATA');
1007: END IF;
1008:
1009: WHEN FTE_DIST_INVALID_LOC_REG_FLAG THEN
1002: --
1003: IF l_debug_on THEN
1004: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_NO_INPUT_DATA RAISED');
1005: WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_INPUT_DATA exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1006: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_INPUT_DATA');
1007: END IF;
1008:
1009: WHEN FTE_DIST_INVALID_LOC_REG_FLAG THEN
1010: --5067249
1024: --
1025: -- Debug Statements
1026: --
1027: IF l_debug_on THEN
1028: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_ACS_PKG.START_ACS FTE_DIST_INVALID_LOC_REG_FLAG RAISED');
1029: WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_INVALID_LOC_REG_FLAG exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1030: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_INVALID_LOC_REG_FLAG');
1031: END IF;
1032:
1025: -- Debug Statements
1026: --
1027: IF l_debug_on THEN
1028: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_ACS_PKG.START_ACS FTE_DIST_INVALID_LOC_REG_FLAG RAISED');
1029: WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_INVALID_LOC_REG_FLAG exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1030: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_INVALID_LOC_REG_FLAG');
1031: END IF;
1032:
1033: WHEN FTE_DIST_DISTANCE_SEARCH_ERR THEN
1026: --
1027: IF l_debug_on THEN
1028: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_ACS_PKG.START_ACS FTE_DIST_INVALID_LOC_REG_FLAG RAISED');
1029: WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_INVALID_LOC_REG_FLAG exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1030: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_INVALID_LOC_REG_FLAG');
1031: END IF;
1032:
1033: WHEN FTE_DIST_DISTANCE_SEARCH_ERR THEN
1034: x_return_status := l_return_status;
1045: --
1046: -- Debug Statements
1047: --
1048: IF l_debug_on THEN
1049: WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM FTE_DIST_INT_PKG.GET_DISTANCE_TIME ( FTE_DIST_DISTANCE_SEARCH_ERR ) IS '||L_RETURN_STATUS||': '||L_RETURN_MESSAGE );
1050: WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_DISTANCE_SEARCH_ERR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1051: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_DISTANCE_SEARCH_ERR');
1052: END IF;
1053: --
1046: -- Debug Statements
1047: --
1048: IF l_debug_on THEN
1049: WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM FTE_DIST_INT_PKG.GET_DISTANCE_TIME ( FTE_DIST_DISTANCE_SEARCH_ERR ) IS '||L_RETURN_STATUS||': '||L_RETURN_MESSAGE );
1050: WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_DISTANCE_SEARCH_ERR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1051: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_DISTANCE_SEARCH_ERR');
1052: END IF;
1053: --
1054: WSH_UTIL_CORE.default_handler('FTE_DIST_INT.GET_DISTANCE_TIME');
1047: --
1048: IF l_debug_on THEN
1049: WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM FTE_DIST_INT_PKG.GET_DISTANCE_TIME ( FTE_DIST_DISTANCE_SEARCH_ERR ) IS '||L_RETURN_STATUS||': '||L_RETURN_MESSAGE );
1050: WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_DISTANCE_SEARCH_ERR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1051: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_DISTANCE_SEARCH_ERR');
1052: END IF;
1053: --
1054: WSH_UTIL_CORE.default_handler('FTE_DIST_INT.GET_DISTANCE_TIME');
1055:
1071: --
1072: -- Debug Statements
1073: --
1074: IF l_debug_on THEN
1075: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_INVALID_PROFILE RAISED');
1076: WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_INVALID_PROFILE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1077: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_INVALID_PROFILE');
1078: END IF;
1079:
1072: -- Debug Statements
1073: --
1074: IF l_debug_on THEN
1075: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_INVALID_PROFILE RAISED');
1076: WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_INVALID_PROFILE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1077: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_INVALID_PROFILE');
1078: END IF;
1079:
1080: WHEN FTE_DIST_NULL_PROFILE THEN
1073: --
1074: IF l_debug_on THEN
1075: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_INVALID_PROFILE RAISED');
1076: WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_INVALID_PROFILE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1077: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_INVALID_PROFILE');
1078: END IF;
1079:
1080: WHEN FTE_DIST_NULL_PROFILE THEN
1081: --5067249
1095: --
1096: -- Debug Statements
1097: --
1098: IF l_debug_on THEN
1099: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_NULL_PROFILE RAISED');
1100: WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NULL_PROFILE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1101: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NULL_PROFILE');
1102: END IF;
1103:
1096: -- Debug Statements
1097: --
1098: IF l_debug_on THEN
1099: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_NULL_PROFILE RAISED');
1100: WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NULL_PROFILE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1101: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NULL_PROFILE');
1102: END IF;
1103:
1104: WHEN FTE_DIST_NO_REGS_FOR_ANY_LOCS THEN
1097: --
1098: IF l_debug_on THEN
1099: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_NULL_PROFILE RAISED');
1100: WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NULL_PROFILE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1101: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NULL_PROFILE');
1102: END IF;
1103:
1104: WHEN FTE_DIST_NO_REGS_FOR_ANY_LOCS THEN
1105: --5067249
1119: --
1120: -- Debug Statements
1121: --
1122: IF l_debug_on THEN
1123: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_NO_REGS_FOR_ANY_LOCS RAISED');
1124: WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_REGS_FOR_ANY_LOCS exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1125: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_REGS_FOR_ANY_LOCS');
1126: END IF;
1127:
1120: -- Debug Statements
1121: --
1122: IF l_debug_on THEN
1123: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_NO_REGS_FOR_ANY_LOCS RAISED');
1124: WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_REGS_FOR_ANY_LOCS exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1125: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_REGS_FOR_ANY_LOCS');
1126: END IF;
1127:
1128: WHEN FTE_DIST_NO_REGS_IN_SEARCH THEN
1121: --
1122: IF l_debug_on THEN
1123: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_NO_REGS_FOR_ANY_LOCS RAISED');
1124: WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_REGS_FOR_ANY_LOCS exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1125: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_REGS_FOR_ANY_LOCS');
1126: END IF;
1127:
1128: WHEN FTE_DIST_NO_REGS_IN_SEARCH THEN
1129: --5067249
1143: --
1144: -- Debug Statements
1145: --
1146: IF l_debug_on THEN
1147: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_NO_REGS_IN_SEARCH RAISED');
1148: WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_REGS_IN_SEARCH exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1149: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_REGS_IN_SEARCH');
1150: END IF;
1151:
1144: -- Debug Statements
1145: --
1146: IF l_debug_on THEN
1147: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_NO_REGS_IN_SEARCH RAISED');
1148: WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_REGS_IN_SEARCH exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1149: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_REGS_IN_SEARCH');
1150: END IF;
1151:
1152: WHEN FTE_DIST_NULL_REGION_TYPE THEN
1145: --
1146: IF l_debug_on THEN
1147: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_NO_REGS_IN_SEARCH RAISED');
1148: WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_REGS_IN_SEARCH exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1149: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_REGS_IN_SEARCH');
1150: END IF;
1151:
1152: WHEN FTE_DIST_NULL_REGION_TYPE THEN
1153: --5067249
1167: --
1168: -- Debug Statements
1169: --
1170: IF l_debug_on THEN
1171: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_NULL_REGION_TYPE RAISED');
1172: WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NULL_REGION_TYPE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1173: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NULL_REGION_TYPE');
1174: END IF;
1175:
1168: -- Debug Statements
1169: --
1170: IF l_debug_on THEN
1171: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_NULL_REGION_TYPE RAISED');
1172: WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NULL_REGION_TYPE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1173: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NULL_REGION_TYPE');
1174: END IF;
1175:
1176: WHEN OTHERS THEN
1169: --
1170: IF l_debug_on THEN
1171: WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_NULL_REGION_TYPE RAISED');
1172: WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NULL_REGION_TYPE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1173: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NULL_REGION_TYPE');
1174: END IF;
1175:
1176: WHEN OTHERS THEN
1177: l_error_text := SQLERRM;
1186: --
1187: -- Debug Statements
1188: --
1189: IF l_debug_on THEN
1190: WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM FTE_DIST_INT_PKG.GET_DISTANCE_TIME IS ' ||L_ERROR_TEXT );
1191: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1192: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1193: END IF;
1194: --
1187: -- Debug Statements
1188: --
1189: IF l_debug_on THEN
1190: WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM FTE_DIST_INT_PKG.GET_DISTANCE_TIME IS ' ||L_ERROR_TEXT );
1191: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1192: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1193: END IF;
1194: --
1195: WSH_UTIL_CORE.default_handler('FTE_DIST_INT_PKG.GET_DISTANCE_TIME');
1188: --
1189: IF l_debug_on THEN
1190: WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM FTE_DIST_INT_PKG.GET_DISTANCE_TIME IS ' ||L_ERROR_TEXT );
1191: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1192: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1193: END IF;
1194: --
1195: WSH_UTIL_CORE.default_handler('FTE_DIST_INT_PKG.GET_DISTANCE_TIME');
1196: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1326: --
1327: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1328: --
1329: IF l_debug_on IS NULL THEN
1330: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1331: END IF;
1332:
1333: --
1334: -- Debug Statements for input parameters
1333: --
1334: -- Debug Statements for input parameters
1335: --
1336: IF l_debug_on THEN
1337: WSH_DEBUG_SV.push(l_module_name);
1338: WSH_DEBUG_SV.logmsg(l_module_name,'------- DISTANCE SEARCH INPUT PARAMETERS ------');
1339: WSH_DEBUG_SV.logmsg(l_module_name,'-----------------------------------------------');
1340: WSH_DEBUG_SV.log(l_module_name,'p_location_region_flag',p_location_region_flag);
1341: WSH_DEBUG_SV.log(l_module_name,'p_messaging_yn',p_messaging_yn);
1334: -- Debug Statements for input parameters
1335: --
1336: IF l_debug_on THEN
1337: WSH_DEBUG_SV.push(l_module_name);
1338: WSH_DEBUG_SV.logmsg(l_module_name,'------- DISTANCE SEARCH INPUT PARAMETERS ------');
1339: WSH_DEBUG_SV.logmsg(l_module_name,'-----------------------------------------------');
1340: WSH_DEBUG_SV.log(l_module_name,'p_location_region_flag',p_location_region_flag);
1341: WSH_DEBUG_SV.log(l_module_name,'p_messaging_yn',p_messaging_yn);
1342: WSH_DEBUG_SV.log(l_module_name,'p_search_tab count = ', p_search_tab.COUNT);
1335: --
1336: IF l_debug_on THEN
1337: WSH_DEBUG_SV.push(l_module_name);
1338: WSH_DEBUG_SV.logmsg(l_module_name,'------- DISTANCE SEARCH INPUT PARAMETERS ------');
1339: WSH_DEBUG_SV.logmsg(l_module_name,'-----------------------------------------------');
1340: WSH_DEBUG_SV.log(l_module_name,'p_location_region_flag',p_location_region_flag);
1341: WSH_DEBUG_SV.log(l_module_name,'p_messaging_yn',p_messaging_yn);
1342: WSH_DEBUG_SV.log(l_module_name,'p_search_tab count = ', p_search_tab.COUNT);
1343: IF (p_search_tab.COUNT > 0) THEN
1336: IF l_debug_on THEN
1337: WSH_DEBUG_SV.push(l_module_name);
1338: WSH_DEBUG_SV.logmsg(l_module_name,'------- DISTANCE SEARCH INPUT PARAMETERS ------');
1339: WSH_DEBUG_SV.logmsg(l_module_name,'-----------------------------------------------');
1340: WSH_DEBUG_SV.log(l_module_name,'p_location_region_flag',p_location_region_flag);
1341: WSH_DEBUG_SV.log(l_module_name,'p_messaging_yn',p_messaging_yn);
1342: WSH_DEBUG_SV.log(l_module_name,'p_search_tab count = ', p_search_tab.COUNT);
1343: IF (p_search_tab.COUNT > 0) THEN
1344: FOR dbst IN p_search_tab.FIRST..p_search_tab.LAST LOOP
1337: WSH_DEBUG_SV.push(l_module_name);
1338: WSH_DEBUG_SV.logmsg(l_module_name,'------- DISTANCE SEARCH INPUT PARAMETERS ------');
1339: WSH_DEBUG_SV.logmsg(l_module_name,'-----------------------------------------------');
1340: WSH_DEBUG_SV.log(l_module_name,'p_location_region_flag',p_location_region_flag);
1341: WSH_DEBUG_SV.log(l_module_name,'p_messaging_yn',p_messaging_yn);
1342: WSH_DEBUG_SV.log(l_module_name,'p_search_tab count = ', p_search_tab.COUNT);
1343: IF (p_search_tab.COUNT > 0) THEN
1344: FOR dbst IN p_search_tab.FIRST..p_search_tab.LAST LOOP
1345: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(dbst).origin_id', p_search_tab(dbst).origin_id);
1338: WSH_DEBUG_SV.logmsg(l_module_name,'------- DISTANCE SEARCH INPUT PARAMETERS ------');
1339: WSH_DEBUG_SV.logmsg(l_module_name,'-----------------------------------------------');
1340: WSH_DEBUG_SV.log(l_module_name,'p_location_region_flag',p_location_region_flag);
1341: WSH_DEBUG_SV.log(l_module_name,'p_messaging_yn',p_messaging_yn);
1342: WSH_DEBUG_SV.log(l_module_name,'p_search_tab count = ', p_search_tab.COUNT);
1343: IF (p_search_tab.COUNT > 0) THEN
1344: FOR dbst IN p_search_tab.FIRST..p_search_tab.LAST LOOP
1345: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(dbst).origin_id', p_search_tab(dbst).origin_id);
1346: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(dbst).destination_id', p_search_tab(dbst).destination_id);
1341: WSH_DEBUG_SV.log(l_module_name,'p_messaging_yn',p_messaging_yn);
1342: WSH_DEBUG_SV.log(l_module_name,'p_search_tab count = ', p_search_tab.COUNT);
1343: IF (p_search_tab.COUNT > 0) THEN
1344: FOR dbst IN p_search_tab.FIRST..p_search_tab.LAST LOOP
1345: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(dbst).origin_id', p_search_tab(dbst).origin_id);
1346: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(dbst).destination_id', p_search_tab(dbst).destination_id);
1347: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(dbst).origin_loc_id',p_search_tab(dbst).origin_loc_id);
1348: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(dbst).dest_loc_id',p_search_tab(dbst).dest_loc_id);
1349: END LOOP;
1342: WSH_DEBUG_SV.log(l_module_name,'p_search_tab count = ', p_search_tab.COUNT);
1343: IF (p_search_tab.COUNT > 0) THEN
1344: FOR dbst IN p_search_tab.FIRST..p_search_tab.LAST LOOP
1345: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(dbst).origin_id', p_search_tab(dbst).origin_id);
1346: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(dbst).destination_id', p_search_tab(dbst).destination_id);
1347: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(dbst).origin_loc_id',p_search_tab(dbst).origin_loc_id);
1348: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(dbst).dest_loc_id',p_search_tab(dbst).dest_loc_id);
1349: END LOOP;
1350: END IF;
1343: IF (p_search_tab.COUNT > 0) THEN
1344: FOR dbst IN p_search_tab.FIRST..p_search_tab.LAST LOOP
1345: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(dbst).origin_id', p_search_tab(dbst).origin_id);
1346: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(dbst).destination_id', p_search_tab(dbst).destination_id);
1347: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(dbst).origin_loc_id',p_search_tab(dbst).origin_loc_id);
1348: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(dbst).dest_loc_id',p_search_tab(dbst).dest_loc_id);
1349: END LOOP;
1350: END IF;
1351: WSH_DEBUG_SV.log(l_module_name,'p_origin_reg_loc_tab count = ', p_origin_reg_loc_tab.COUNT);
1344: FOR dbst IN p_search_tab.FIRST..p_search_tab.LAST LOOP
1345: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(dbst).origin_id', p_search_tab(dbst).origin_id);
1346: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(dbst).destination_id', p_search_tab(dbst).destination_id);
1347: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(dbst).origin_loc_id',p_search_tab(dbst).origin_loc_id);
1348: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(dbst).dest_loc_id',p_search_tab(dbst).dest_loc_id);
1349: END LOOP;
1350: END IF;
1351: WSH_DEBUG_SV.log(l_module_name,'p_origin_reg_loc_tab count = ', p_origin_reg_loc_tab.COUNT);
1352: IF (p_origin_reg_loc_tab.COUNT > 0) THEN
1347: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(dbst).origin_loc_id',p_search_tab(dbst).origin_loc_id);
1348: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(dbst).dest_loc_id',p_search_tab(dbst).dest_loc_id);
1349: END LOOP;
1350: END IF;
1351: WSH_DEBUG_SV.log(l_module_name,'p_origin_reg_loc_tab count = ', p_origin_reg_loc_tab.COUNT);
1352: IF (p_origin_reg_loc_tab.COUNT > 0) THEN
1353: FOR dbrilo IN p_origin_reg_loc_tab.FIRST..p_origin_reg_loc_tab.LAST LOOP
1354: IF (p_origin_reg_loc_tab.EXISTS(dbrilo)) THEN
1355: WSH_DEBUG_SV.log(l_module_name,'(dbrilo)',dbrilo);
1351: WSH_DEBUG_SV.log(l_module_name,'p_origin_reg_loc_tab count = ', p_origin_reg_loc_tab.COUNT);
1352: IF (p_origin_reg_loc_tab.COUNT > 0) THEN
1353: FOR dbrilo IN p_origin_reg_loc_tab.FIRST..p_origin_reg_loc_tab.LAST LOOP
1354: IF (p_origin_reg_loc_tab.EXISTS(dbrilo)) THEN
1355: WSH_DEBUG_SV.log(l_module_name,'(dbrilo)',dbrilo);
1356: WSH_DEBUG_SV.log(l_module_name,'p_origin_reg_loc_tab(dbrilo)',p_origin_reg_loc_tab(dbrilo));
1357: END IF;
1358: END LOOP;
1359: END IF;
1352: IF (p_origin_reg_loc_tab.COUNT > 0) THEN
1353: FOR dbrilo IN p_origin_reg_loc_tab.FIRST..p_origin_reg_loc_tab.LAST LOOP
1354: IF (p_origin_reg_loc_tab.EXISTS(dbrilo)) THEN
1355: WSH_DEBUG_SV.log(l_module_name,'(dbrilo)',dbrilo);
1356: WSH_DEBUG_SV.log(l_module_name,'p_origin_reg_loc_tab(dbrilo)',p_origin_reg_loc_tab(dbrilo));
1357: END IF;
1358: END LOOP;
1359: END IF;
1360: WSH_DEBUG_SV.log(l_module_name,'p_dest_reg_loc_tab count = ', p_dest_reg_loc_tab.COUNT);
1356: WSH_DEBUG_SV.log(l_module_name,'p_origin_reg_loc_tab(dbrilo)',p_origin_reg_loc_tab(dbrilo));
1357: END IF;
1358: END LOOP;
1359: END IF;
1360: WSH_DEBUG_SV.log(l_module_name,'p_dest_reg_loc_tab count = ', p_dest_reg_loc_tab.COUNT);
1361: IF (p_dest_reg_loc_tab.COUNT > 0) THEN
1362: FOR dbrild IN p_dest_reg_loc_tab.FIRST..p_dest_reg_loc_tab.LAST LOOP
1363: IF (p_dest_reg_loc_tab.EXISTS(dbrild)) THEN
1364: WSH_DEBUG_SV.log(l_module_name,'(dbrild)',dbrild);
1360: WSH_DEBUG_SV.log(l_module_name,'p_dest_reg_loc_tab count = ', p_dest_reg_loc_tab.COUNT);
1361: IF (p_dest_reg_loc_tab.COUNT > 0) THEN
1362: FOR dbrild IN p_dest_reg_loc_tab.FIRST..p_dest_reg_loc_tab.LAST LOOP
1363: IF (p_dest_reg_loc_tab.EXISTS(dbrild)) THEN
1364: WSH_DEBUG_SV.log(l_module_name,'(dbrild)',dbrild);
1365: WSH_DEBUG_SV.log(l_module_name,'p_dest_reg_loc_tab(dbrild)',p_dest_reg_loc_tab(dbrild));
1366: END IF;
1367: END LOOP;
1368: END IF;
1361: IF (p_dest_reg_loc_tab.COUNT > 0) THEN
1362: FOR dbrild IN p_dest_reg_loc_tab.FIRST..p_dest_reg_loc_tab.LAST LOOP
1363: IF (p_dest_reg_loc_tab.EXISTS(dbrild)) THEN
1364: WSH_DEBUG_SV.log(l_module_name,'(dbrild)',dbrild);
1365: WSH_DEBUG_SV.log(l_module_name,'p_dest_reg_loc_tab(dbrild)',p_dest_reg_loc_tab(dbrild));
1366: END IF;
1367: END LOOP;
1368: END IF;
1369: END IF;
1422: --
1423: -- Perform the search to find matching rules
1424: --
1425: IF l_debug_on THEN
1426: WSH_DEBUG_SV.logmsg(l_module_name,'-----------------------------------------');
1427: WSH_DEBUG_SV.logmsg(l_module_name,'RUNNING cursor c_perform_distance_search:');
1428: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(ggg).origin_id',p_search_tab(ggg).origin_id);
1429: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(ggg).destination_id',p_search_tab(ggg).destination_id);
1430: WSH_DEBUG_SV.log(l_module_name,'p_level',p_level);
1423: -- Perform the search to find matching rules
1424: --
1425: IF l_debug_on THEN
1426: WSH_DEBUG_SV.logmsg(l_module_name,'-----------------------------------------');
1427: WSH_DEBUG_SV.logmsg(l_module_name,'RUNNING cursor c_perform_distance_search:');
1428: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(ggg).origin_id',p_search_tab(ggg).origin_id);
1429: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(ggg).destination_id',p_search_tab(ggg).destination_id);
1430: WSH_DEBUG_SV.log(l_module_name,'p_level',p_level);
1431: WSH_DEBUG_SV.logmsg(l_module_name,'-----------------------------------------');
1424: --
1425: IF l_debug_on THEN
1426: WSH_DEBUG_SV.logmsg(l_module_name,'-----------------------------------------');
1427: WSH_DEBUG_SV.logmsg(l_module_name,'RUNNING cursor c_perform_distance_search:');
1428: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(ggg).origin_id',p_search_tab(ggg).origin_id);
1429: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(ggg).destination_id',p_search_tab(ggg).destination_id);
1430: WSH_DEBUG_SV.log(l_module_name,'p_level',p_level);
1431: WSH_DEBUG_SV.logmsg(l_module_name,'-----------------------------------------');
1432: END IF;
1425: IF l_debug_on THEN
1426: WSH_DEBUG_SV.logmsg(l_module_name,'-----------------------------------------');
1427: WSH_DEBUG_SV.logmsg(l_module_name,'RUNNING cursor c_perform_distance_search:');
1428: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(ggg).origin_id',p_search_tab(ggg).origin_id);
1429: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(ggg).destination_id',p_search_tab(ggg).destination_id);
1430: WSH_DEBUG_SV.log(l_module_name,'p_level',p_level);
1431: WSH_DEBUG_SV.logmsg(l_module_name,'-----------------------------------------');
1432: END IF;
1433:
1426: WSH_DEBUG_SV.logmsg(l_module_name,'-----------------------------------------');
1427: WSH_DEBUG_SV.logmsg(l_module_name,'RUNNING cursor c_perform_distance_search:');
1428: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(ggg).origin_id',p_search_tab(ggg).origin_id);
1429: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(ggg).destination_id',p_search_tab(ggg).destination_id);
1430: WSH_DEBUG_SV.log(l_module_name,'p_level',p_level);
1431: WSH_DEBUG_SV.logmsg(l_module_name,'-----------------------------------------');
1432: END IF;
1433:
1434: OPEN c_perform_distance_search(p_search_tab(ggg).origin_id,
1427: WSH_DEBUG_SV.logmsg(l_module_name,'RUNNING cursor c_perform_distance_search:');
1428: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(ggg).origin_id',p_search_tab(ggg).origin_id);
1429: WSH_DEBUG_SV.log(l_module_name,'p_search_tab(ggg).destination_id',p_search_tab(ggg).destination_id);
1430: WSH_DEBUG_SV.log(l_module_name,'p_level',p_level);
1431: WSH_DEBUG_SV.logmsg(l_module_name,'-----------------------------------------');
1432: END IF;
1433:
1434: OPEN c_perform_distance_search(p_search_tab(ggg).origin_id,
1435: p_search_tab(ggg).destination_id,
1452: -- results found and load up the output table with the data
1453: -- if so
1454: --
1455: IF l_debug_on THEN
1456: WSH_DEBUG_SV.log(l_module_name,'l_rslt_origin_id_tab.COUNT = ',l_rslt_origin_id_tab.COUNT);
1457: END IF;
1458:
1459: IF (l_rslt_origin_id_tab.COUNT > 0) THEN
1460: --
1478: -- The search was for locations - get the corresponding location for the
1479: -- region found
1480: --
1481: IF l_debug_on THEN
1482: WSH_DEBUG_SV.logmsg(l_module_name,'The search was for locations - get the corresponding location for the region found');
1483: END IF;
1484:
1485: -- AXE
1486: -- l_result_table(l_ctr).origin_location_id := p_origin_reg_loc_tab(l_rslt_origin_id_tab(hhh));
1490: l_result_table(l_ctr).destination_location_id := p_search_tab(ggg).dest_loc_id;
1491:
1492:
1493: IF l_debug_on THEN
1494: WSH_DEBUG_SV.log(l_module_name,'l_result_table(l_ctr).origin_location_id = ',p_origin_reg_loc_tab(l_rslt_origin_id_tab(hhh)));
1495: WSH_DEBUG_SV.log(l_module_name,'l_result_table(l_ctr).destination_location_id = ',p_dest_reg_loc_tab(l_rslt_destination_id_tab(hhh)));
1496: END IF;
1497:
1498: ELSIF (p_location_region_flag = g_region_search_flag) THEN
1491:
1492:
1493: IF l_debug_on THEN
1494: WSH_DEBUG_SV.log(l_module_name,'l_result_table(l_ctr).origin_location_id = ',p_origin_reg_loc_tab(l_rslt_origin_id_tab(hhh)));
1495: WSH_DEBUG_SV.log(l_module_name,'l_result_table(l_ctr).destination_location_id = ',p_dest_reg_loc_tab(l_rslt_destination_id_tab(hhh)));
1496: END IF;
1497:
1498: ELSIF (p_location_region_flag = g_region_search_flag) THEN
1499: --
1499: --
1500: -- The search was for Regions - populate the result table
1501: --
1502: IF l_debug_on THEN
1503: WSH_DEBUG_SV.logmsg(l_module_name,'The search was for Regions - populate the result table');
1504: END IF;
1505:
1506: l_result_table(l_ctr).origin_location_id := null;
1507: l_result_table(l_ctr).destination_location_id := null;
1522: --
1523: -- Nothing was found - log a message
1524: --
1525: IF l_debug_on THEN
1526: WSH_DEBUG_SV.logmsg(l_module_name,'No result was found for the OD pair');
1527: END IF;
1528:
1529: IF (p_messaging_yn = 'Y') THEN
1530: IF l_debug_on THEN
1527: END IF;
1528:
1529: IF (p_messaging_yn = 'Y') THEN
1530: IF l_debug_on THEN
1531: WSH_DEBUG_SV.logmsg(l_module_name,'No result was found for the OD pair - log a NO_RECORDS_FOUND log message');
1532: END IF;
1533:
1534: l_msg_ctr := msg_message_text_tab.COUNT + 1;
1535: msg_message_text_tab(l_msg_ctr) := l_msg_no_rcds_fnd;
1559: --
1560: -- Debug Statements
1561: --
1562: -- IF l_debug_on THEN
1563: -- WSH_DEBUG_SV.pop(l_module_name);
1564: -- END IF;
1565: --
1566: END IF;
1567:
1568: END LOOP; -- search loop
1569:
1570:
1571: IF l_debug_on THEN
1572: WSH_DEBUG_SV.logmsg(l_module_name,'Search is completed');
1573: END IF;
1574:
1575:
1576: --
1581: --
1582: -- We hava winner!!!!
1583: --
1584: IF l_debug_on THEN
1585: WSH_DEBUG_SV.logmsg(l_module_name,'We have a winner');
1586: END IF;
1587: x_result_table := l_result_table;
1588: x_return_message := null;
1589: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1590: --
1591: -- Debug Statements
1592: --
1593: IF l_debug_on THEN
1594: WSH_DEBUG_SV.pop(l_module_name);
1595: END IF;
1596: --
1597:
1598: IF ((p_messaging_yn = 'Y') AND
1615:
1616: RETURN;
1617: ELSE
1618: IF l_debug_on THEN
1619: WSH_DEBUG_SV.logmsg(l_module_name,'no result just return a warning');
1620: END IF;
1621:
1622: IF (p_messaging_yn = 'Y') THEN
1623: --
1655: --
1656: -- Debug Statements
1657: --
1658: IF l_debug_on THEN
1659: WSH_DEBUG_SV.pop(l_module_name);
1660: END IF;
1661: --
1662: RETURN;
1663: END IF;
1666: --
1667: -- Debug Statements
1668: --
1669: IF l_debug_on THEN
1670: WSH_DEBUG_SV.pop(l_module_name);
1671: END IF;
1672: --
1673:
1674:
1687: --
1688: -- Debug Statements
1689: --
1690: IF l_debug_on THEN
1691: WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM FTE_DIST_INT_PKG.DISTANCE_SEARCH IS ' ||L_ERROR_TEXT );
1692: END IF;
1693: --
1694: WSH_UTIL_CORE.default_handler('FTE_DIST_INT_PKG.DISTANCE_SEARCH');
1695: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1697: --
1698: -- Debug Statements
1699: --
1700: IF l_debug_on THEN
1701: WSH_DEBUG_SV.pop(l_module_name);
1702: END IF;
1703: --
1704: RETURN;
1705:
1707: --
1708: -- Debug Statements
1709: --
1710: IF l_debug_on THEN
1711: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1712: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1713: END IF;
1714: --
1715:
1708: -- Debug Statements
1709: --
1710: IF l_debug_on THEN
1711: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1712: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1713: END IF;
1714: --
1715:
1716: END DISTANCE_SEARCH;
1792: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1793: --
1794: IF l_debug_on IS NULL
1795: THEN
1796: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1797: END IF;
1798: --
1799:
1800: -- Bug 4996745
1798: --
1799:
1800: -- Bug 4996745
1801: IF l_debug_on THEN
1802: WSH_DEBUG_SV.push(l_module_name);
1803: END IF;
1804:
1805: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1806: x_return_message := null;
1868: --
1869: -- Debug Statements
1870: --
1871: IF l_debug_on THEN
1872: WSH_DEBUG_SV.pop(l_module_name);
1873: END IF;
1874: --
1875: RETURN;
1876:
1881: --
1882: -- Debug Statements
1883: --
1884: IF l_debug_on THEN
1885: WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM FTE_DIST_INT_PKG.LOG_DISTANCE_MESSAGES IS ' ||L_ERROR_TEXT );
1886: END IF;
1887: --
1888: WSH_UTIL_CORE.default_handler('FTE_DIST_INT_PKG.LOG_DISTANCE_MESSAGES');
1889: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1891: --
1892: -- Debug Statements
1893: --
1894: IF l_debug_on THEN
1895: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1896: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1897: END IF;
1898:
1899: RETURN;
1892: -- Debug Statements
1893: --
1894: IF l_debug_on THEN
1895: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1896: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1897: END IF;
1898:
1899: RETURN;
1900: