[Home] [Help]
204: p_to_region_id IN NUMBER) RETURN VARCHAR2
205: IS
206:
207: l_key VARCHAR2(32767);
208: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
209: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_fixed_key';
210:
211: BEGIN
212:
210:
211: BEGIN
212:
213: IF l_debug_on THEN
214: wsh_debug_sv.push (l_module_name);
215: END IF;
216:
217: l_key := TO_CHAR(p_group_id, g_int_mask) || '-' ||
218: TO_CHAR(p_from_region_id,g_int_mask) || '-' ||
218: TO_CHAR(p_from_region_id,g_int_mask) || '-' ||
219: TO_CHAR(p_to_region_id , g_int_mask);
220:
221: IF l_debug_on THEN
222: WSH_DEBUG_SV.log(l_module_name,'Fixed Key ',l_key);
223: WSH_DEBUG_SV.POP (l_module_name);
224: END IF;
225:
226: RETURN (l_key);
219: TO_CHAR(p_to_region_id , g_int_mask);
220:
221: IF l_debug_on THEN
222: WSH_DEBUG_SV.log(l_module_name,'Fixed Key ',l_key);
223: WSH_DEBUG_SV.POP (l_module_name);
224: END IF;
225:
226: RETURN (l_key);
227: EXCEPTION
229: WHEN others THEN
230: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.get_fixed_key');
231:
232: IF l_debug_on THEN
233: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
234: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
235: END IF;
236: --
237: END get_fixed_key;
230: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.get_fixed_key');
231:
232: IF l_debug_on THEN
233: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
234: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
235: END IF;
236: --
237: END get_fixed_key;
238:
250: FUNCTION get_key_for_num( p_fixed_key IN VARCHAR2,
251: p_number IN NUMBER) RETURN VARCHAR2
252: IS
253: l_key VARCHAR2(32767);
254: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
255: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_key_for_num';
256:
257: BEGIN
258:
256:
257: BEGIN
258:
259: IF l_debug_on THEN
260: wsh_debug_sv.push (l_module_name);
261: END IF;
262:
263:
264: l_key := p_fixed_key|| '-' ||
264: l_key := p_fixed_key|| '-' ||
265: TO_CHAR(p_number,g_num_mask);
266:
267: IF l_debug_on THEN
268: WSH_DEBUG_SV.log(l_module_name,'Number Key ',l_key);
269: WSH_DEBUG_SV.POP (l_module_name);
270: END IF;
271:
272: RETURN l_key;
265: TO_CHAR(p_number,g_num_mask);
266:
267: IF l_debug_on THEN
268: WSH_DEBUG_SV.log(l_module_name,'Number Key ',l_key);
269: WSH_DEBUG_SV.POP (l_module_name);
270: END IF;
271:
272: RETURN l_key;
273:
275: WHEN others THEN
276: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.get_key_for_num');
277:
278: IF l_debug_on THEN
279: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
280: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
281: END IF;
282: --
283: END get_key_for_num;
276: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.get_key_for_num');
277:
278: IF l_debug_on THEN
279: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
280: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
281: END IF;
282: --
283: END get_key_for_num;
284:
295: FUNCTION get_key_for_char( p_fixed_key IN VARCHAR2,
296: p_char IN VARCHAR2) RETURN VARCHAR2
297: IS
298: l_key VARCHAR2(32767);
299: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
300: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_key_for_char';
301: BEGIN
302:
303: IF l_debug_on THEN
300: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_key_for_char';
301: BEGIN
302:
303: IF l_debug_on THEN
304: wsh_debug_sv.push (l_module_name);
305: END IF;
306:
307: l_key := p_fixed_key||'-' ||
308: LPAD(p_char,g_lpad_length,g_lpad_char);
307: l_key := p_fixed_key||'-' ||
308: LPAD(p_char,g_lpad_length,g_lpad_char);
309:
310: IF l_debug_on THEN
311: WSH_DEBUG_SV.log(l_module_name,'Char Key ',l_key);
312: WSH_DEBUG_SV.POP (l_module_name);
313: END IF;
314:
315: RETURN l_key;
308: LPAD(p_char,g_lpad_length,g_lpad_char);
309:
310: IF l_debug_on THEN
311: WSH_DEBUG_SV.log(l_module_name,'Char Key ',l_key);
312: WSH_DEBUG_SV.POP (l_module_name);
313: END IF;
314:
315: RETURN l_key;
316: EXCEPTION
317: WHEN others THEN
318: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.get_key_for_char');
319:
320: IF l_debug_on THEN
321: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
322: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
323: END IF;
324: --
325: END get_key_for_char;
318: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.get_key_for_char');
319:
320: IF l_debug_on THEN
321: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
322: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
323: END IF;
324: --
325: END get_key_for_char;
326:
337: --***************************************************************************--
338: FUNCTION get_key_for_null( p_fixed_key IN VARCHAR2) RETURN VARCHAR2
339: IS
340: l_key VARCHAR2(32767);
341: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
342: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_key_for_null';
343: BEGIN
344: IF l_debug_on THEN
345: wsh_debug_sv.push (l_module_name);
341: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
342: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_key_for_null';
343: BEGIN
344: IF l_debug_on THEN
345: wsh_debug_sv.push (l_module_name);
346: END IF;
347:
348: l_key := p_fixed_key||'-'||LPAD(g_val_absent,g_lpad_length,g_lpad_char);
349:
347:
348: l_key := p_fixed_key||'-'||LPAD(g_val_absent,g_lpad_length,g_lpad_char);
349:
350: IF l_debug_on THEN
351: WSH_DEBUG_SV.log(l_module_name,'NULL Key',l_key);
352: WSH_DEBUG_SV.POP (l_module_name);
353: END IF;
354:
355: RETURN l_key;
348: l_key := p_fixed_key||'-'||LPAD(g_val_absent,g_lpad_length,g_lpad_char);
349:
350: IF l_debug_on THEN
351: WSH_DEBUG_SV.log(l_module_name,'NULL Key',l_key);
352: WSH_DEBUG_SV.POP (l_module_name);
353: END IF;
354:
355: RETURN l_key;
356: EXCEPTION
356: EXCEPTION
357: WHEN others THEN
358: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.get_key_for_null');
359: IF l_debug_on THEN
360: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
361: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
362: END IF;
363: --
364:
357: WHEN others THEN
358: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.get_key_for_null');
359: IF l_debug_on THEN
360: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
361: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
362: END IF;
363: --
364:
365: END get_key_for_null;
401: -- 25. - - - -
402: --***************************************************************************--
403: PROCEDURE initialize_tables (x_return_status OUT NOCOPY VARCHAR2) IS
404:
405: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
406: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'initialize_tables';
407:
408: BEGIN
409: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
408: BEGIN
409: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
410:
411: IF l_debug_on THEN
412: wsh_debug_sv.push (l_module_name);
413: END IF;
414:
415: --All the priorites
416: g_all_priority := priority_tab_type(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25);
432:
433: g_table_initialized := TRUE;
434:
435: IF l_debug_on THEN
436: WSH_DEBUG_SV.POP(l_module_name);
437: END IF;
438: EXCEPTION
439: WHEN others THEN
440: g_table_initialized := FALSE;
441: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.INITIALIZE_TABLES');
442: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
443:
444: IF l_debug_on THEN
445: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
446: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
447: END IF;
448: --
449: END initialize_tables;
442: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
443:
444: IF l_debug_on THEN
445: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
446: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
447: END IF;
448: --
449: END initialize_tables;
450:
460:
461: PROCEDURE delete_rule_caches( x_return_status OUT NOCOPY VARCHAR2)
462: IS
463:
464: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
465: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'delete_rule_caches';
466:
467: BEGIN
468:
468:
469: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
470:
471: IF l_debug_on THEN
472: wsh_debug_sv.push (l_module_name);
473: END IF;
474:
475: g_groups_cache.DELETE;
476: g_weight_cache.DELETE;
480: g_to_postal_cache.DELETE;
481: g_fob_cache.DELETE;
482:
483: IF l_debug_on THEN
484: WSH_DEBUG_SV.logmsg(l_module_name,'CACHES DELETED');
485: WSH_DEBUG_SV.POP (l_module_name);
486: END IF;
487:
488: EXCEPTION
481: g_fob_cache.DELETE;
482:
483: IF l_debug_on THEN
484: WSH_DEBUG_SV.logmsg(l_module_name,'CACHES DELETED');
485: WSH_DEBUG_SV.POP (l_module_name);
486: END IF;
487:
488: EXCEPTION
489: WHEN others THEN
491: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.DELETE_RULE_CACHES');
492: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
493:
494: IF l_debug_on THEN
495: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
496: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
497: END IF;
498: --
499: END delete_rule_caches;
492: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
493:
494: IF l_debug_on THEN
495: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
496: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
497: END IF;
498: --
499: END delete_rule_caches;
500:
512: x_return_status OUT NOCOPY VARCHAR2 )
513: IS
514:
515: l_session_id NUMBER := 0;
516: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
517: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'refresh_cache';
518:
519: BEGIN
520: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
519: BEGIN
520: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
521: --
522: IF l_debug_on THEN
523: wsh_debug_sv.push (l_module_name);
524: END IF;
525: --
526:
527: l_session_id := icx_sec.g_session_id;
526:
527: l_session_id := icx_sec.g_session_id;
528:
529: IF l_debug_on THEN
530: wsh_debug_sv.logmsg(l_module_name,'g_session_id : '||g_session_id||' Current session id : '||l_session_id);
531: END IF;
532:
533: IF g_session_id IS NULL OR l_session_id <> g_session_id THEN
534: delete_rule_caches(x_return_status => x_return_status);
535:
536: g_session_id := l_session_id;
537:
538: IF l_debug_on THEN
539: wsh_debug_sv.logmsg(l_module_name,'Rules Cache cleared');
540: END IF;
541:
542: END IF;
543:
542: END IF;
543:
544: --
545: IF l_debug_on THEN
546: wsh_debug_sv.pop (l_module_name);
547: END IF;
548: --
549:
550: EXCEPTION
552: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.refresh_cache');
553: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
554: --
555: IF l_debug_on THEN
556: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
557: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
558: END IF;
559: --
560: END refresh_cache;
553: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
554: --
555: IF l_debug_on THEN
556: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
557: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
558: END IF;
559: --
560: END refresh_cache;
561:
578: j NUMBER;
579: temp NUMBER;
580: more_sort BOOLEAN := TRUE;
581:
582: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
583: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'sort_priority_tab';
584:
585: BEGIN
586: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
585: BEGIN
586: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
587:
588: IF l_debug_on THEN
589: wsh_debug_sv.push (l_module_name);
590: END IF;
591:
592: IF (p_priority_tab.COUNT<>1) THEN
593:
617:
618: END IF;
619:
620: IF l_debug_on THEN
621: WSH_DEBUG_SV.POP (l_module_name);
622: END IF;
623: EXCEPTION
624: WHEN others THEN
625:
626: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.SORT_PRIORITY_TAB');
627: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
628:
629: IF l_debug_on THEN
630: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
631: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
632: END IF;
633: --
634: END sort_priority_tab;
627: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
628:
629: IF l_debug_on THEN
630: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
631: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
632: END IF;
633: --
634: END sort_priority_tab;
635:
658:
659: l_sort BOOLEAN := FALSE;
660: l_priority_tab PRIORITY_TAB_TYPE;
661:
662: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
663: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'get_sorted_priority_tab';
664:
665: BEGIN
666: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
665: BEGIN
666: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
667:
668: IF l_debug_on THEN
669: wsh_debug_sv.push (l_module_name);
670: END IF;
671:
672: l_priority_tab := g_all_priority;
673:
712:
713: x_priority_tab := l_priority_tab;
714:
715: IF l_debug_on THEN
716: WSH_DEBUG_SV.POP (l_module_name);
717: END IF;
718: EXCEPTION
719: WHEN others THEN
720: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.GET_SORTED_PRIORITY_TAB');
720: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.GET_SORTED_PRIORITY_TAB');
721: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
722:
723: IF l_debug_on THEN
724: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
725: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
726: END IF;
727: --
728: END get_sorted_priority_tab;
721: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
722:
723: IF l_debug_on THEN
724: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
725: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
726: END IF;
727: --
728: END get_sorted_priority_tab;
729:
762: l_group_cache_rec GROUP_CACHE_REC;
763: l_priority_tab PRIORITY_TAB_TYPE;
764: l_priority_str VARCHAR2(600);
765:
766: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
767: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'add_group_to_group_cache';
768:
769: BEGIN
770: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
769: BEGIN
770: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
771:
772: IF l_debug_on THEN
773: wsh_debug_sv.push (l_module_name);
774: WSH_DEBUG_SV.log(l_module_name,'Adding group to Group Cache ',p_group_id);
775: WSH_DEBUG_SV.logmsg(l_module_name,'Attribtues to be checked ');
776: END IF;
777:
770: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
771:
772: IF l_debug_on THEN
773: wsh_debug_sv.push (l_module_name);
774: WSH_DEBUG_SV.log(l_module_name,'Adding group to Group Cache ',p_group_id);
775: WSH_DEBUG_SV.logmsg(l_module_name,'Attribtues to be checked ');
776: END IF;
777:
778: OPEN c_get_group_attributes;
771:
772: IF l_debug_on THEN
773: wsh_debug_sv.push (l_module_name);
774: WSH_DEBUG_SV.log(l_module_name,'Adding group to Group Cache ',p_group_id);
775: WSH_DEBUG_SV.logmsg(l_module_name,'Attribtues to be checked ');
776: END IF;
777:
778: OPEN c_get_group_attributes;
779: FETCH c_get_group_attributes BULK COLLECT INTO l_attr_tab;
788: l_group_attr(g_wt_idx) := g_value_present;
789: l_group_cache_rec.weight_uom_code := l_attr_tab(itr).attr_val;
790:
791: IF l_debug_on THEN
792: WSH_DEBUG_SV.logmsg(l_module_name,'Weight');
793: END IF;
794:
795: ELSIF (l_attr_tab(itr).attr_code = 'VOLUME') THEN
796: l_group_attr(g_vol_idx) := g_value_present;
796: l_group_attr(g_vol_idx) := g_value_present;
797: l_group_cache_rec.volume_uom_code := l_attr_tab(itr).attr_val;
798:
799: IF l_debug_on THEN
800: WSH_DEBUG_SV.logmsg(l_module_name,'Volume');
801: END IF;
802:
803: ELSIF (l_attr_tab(itr).attr_code = 'TRANSIT_TIME') THEN
804: l_group_attr(g_transit_time_idx) := g_value_present;
803: ELSIF (l_attr_tab(itr).attr_code = 'TRANSIT_TIME') THEN
804: l_group_attr(g_transit_time_idx) := g_value_present;
805:
806: IF l_debug_on THEN
807: WSH_DEBUG_SV.logmsg(l_module_name,'Transit Time');
808: END IF;
809:
810: --
811: -- FTE_SEL_RULE_RESTICTIONS has FROM_REGION_ID
817: l_group_attr(g_fr_reg_idx) := g_value_present;
818: IF (l_attr_tab(itr).attr_code = 'FROM_REGION') THEN
819: l_group_attr(g_fr_post_idx) := g_value_present;
820: IF l_debug_on THEN
821: WSH_DEBUG_SV.logmsg(l_module_name,'From Region Postal Code');
822: END IF;
823: END IF;
824:
825: IF l_debug_on THEN
822: END IF;
823: END IF;
824:
825: IF l_debug_on THEN
826: WSH_DEBUG_SV.logmsg(l_module_name,'From Region');
827: END IF;
828:
829: --
830: -- FTE_SEL_RULE_RESTRICTIONS has TO_REGION_ID
836: -- R12 UI enters 'TO_REGION' for postal codes also.
837: IF (l_attr_tab(itr).attr_code = 'TO_REGION') THEN
838: l_group_attr(g_to_post_idx) := g_value_present;
839: IF l_debug_on THEN
840: WSH_DEBUG_SV.logmsg(l_module_name,'To Region Postal Code');
841: END IF;
842: END IF;
843:
844: IF l_debug_on THEN
841: END IF;
842: END IF;
843:
844: IF l_debug_on THEN
845: WSH_DEBUG_SV.logmsg(l_module_name,'To Region');
846: END IF;
847:
848: -- Prior to R12 , UI stores 'FROM_REGION_POSTAL_CODE' for from postal code.
849: ELSIF (l_attr_tab(itr).attr_code IN('FROM_REGION_POSTAL_CODE')) THEN
849: ELSIF (l_attr_tab(itr).attr_code IN('FROM_REGION_POSTAL_CODE')) THEN
850: l_group_attr(g_fr_post_idx) := g_value_present;
851:
852: IF l_debug_on THEN
853: WSH_DEBUG_SV.logmsg(l_module_name,'From Region Postal Code');
854: END IF;
855:
856: -- Prior to R12 , UI stores 'TO_REGION_POSTAL_CODE' for To postal codes.
857: ELSIF (l_attr_tab(itr).attr_code IN ('TO_REGION_POSTAL_CODE')) THEN
857: ELSIF (l_attr_tab(itr).attr_code IN ('TO_REGION_POSTAL_CODE')) THEN
858: l_group_attr(g_to_post_idx) := g_value_present;
859:
860: IF l_debug_on THEN
861: WSH_DEBUG_SV.logmsg(l_module_name,'To Region Postal Code');
862: END IF;
863:
864: ELSIF (l_attr_tab(itr).attr_code = 'FOB_CODE') THEN
865: l_group_attr(g_fob_code_idx) := g_value_present;
864: ELSIF (l_attr_tab(itr).attr_code = 'FOB_CODE') THEN
865: l_group_attr(g_fob_code_idx) := g_value_present;
866:
867: IF l_debug_on THEN
868: WSH_DEBUG_SV.logmsg(l_module_name,'Fob Code');
869: END IF;
870:
871: END IF;
872:
902: EXIT WHEN itr = l_priority_tab.LAST;
903: END LOOP;
904: END IF;
905:
906: WSH_DEBUG_SV.log(l_module_name,'Priorities To be checked',l_priority_str);
907: WSH_DEBUG_SV.POP (l_module_name);
908: END IF;
909:
910: EXCEPTION
903: END LOOP;
904: END IF;
905:
906: WSH_DEBUG_SV.log(l_module_name,'Priorities To be checked',l_priority_str);
907: WSH_DEBUG_SV.POP (l_module_name);
908: END IF;
909:
910: EXCEPTION
911: WHEN OTHERS THEN
917: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.add_group_to_cache');
918: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
919:
920: IF l_debug_on THEN
921: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
922: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
923: END IF;
924: --
925: END add_group_to_group_cache;
918: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
919:
920: IF l_debug_on THEN
921: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
922: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
923: END IF;
924: --
925: END add_group_to_group_cache;
926:
951:
952: l_rule_tab exact_match_tab;
953: l_key VARCHAR2(32767);
954:
955: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
956: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'add_to_non_range_cache';
957:
958: BEGIN
959:
959:
960: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
961:
962: IF l_debug_on THEN
963: WSH_DEBUG_SV.push(l_module_name);
964: WSH_DEBUG_SV.log(l_module_name,'p_fixed_key',p_fixed_key);
965: WSH_DEBUG_SV.log(l_module_name,'p_null_key ', p_null_key);
966: WSH_DEBUG_SV.log(l_module_name,'p_rule_id ',p_rule_id);
967: END IF;
960: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
961:
962: IF l_debug_on THEN
963: WSH_DEBUG_SV.push(l_module_name);
964: WSH_DEBUG_SV.log(l_module_name,'p_fixed_key',p_fixed_key);
965: WSH_DEBUG_SV.log(l_module_name,'p_null_key ', p_null_key);
966: WSH_DEBUG_SV.log(l_module_name,'p_rule_id ',p_rule_id);
967: END IF;
968:
961:
962: IF l_debug_on THEN
963: WSH_DEBUG_SV.push(l_module_name);
964: WSH_DEBUG_SV.log(l_module_name,'p_fixed_key',p_fixed_key);
965: WSH_DEBUG_SV.log(l_module_name,'p_null_key ', p_null_key);
966: WSH_DEBUG_SV.log(l_module_name,'p_rule_id ',p_rule_id);
967: END IF;
968:
969: IF (p_fixed_key IS NOT NULL) THEN
962: IF l_debug_on THEN
963: WSH_DEBUG_SV.push(l_module_name);
964: WSH_DEBUG_SV.log(l_module_name,'p_fixed_key',p_fixed_key);
965: WSH_DEBUG_SV.log(l_module_name,'p_null_key ', p_null_key);
966: WSH_DEBUG_SV.log(l_module_name,'p_rule_id ',p_rule_id);
967: END IF;
968:
969: IF (p_fixed_key IS NOT NULL) THEN
970: l_key := p_fixed_key;
983:
984: p_cache(l_key) := l_rule_tab;
985:
986: IF l_debug_on THEN
987: WSH_DEBUG_SV.POP (l_module_name);
988: END IF;
989:
990: EXCEPTION
991: WHEN others THEN
992: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.ADD_TO_NON_RANGE_CACHE');
993: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
994:
995: IF l_debug_on THEN
996: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
997: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
998: END IF;
999: --
1000: END add_to_non_range_cache;
993: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
994:
995: IF l_debug_on THEN
996: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
997: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
998: END IF;
999: --
1000: END add_to_non_range_cache;
1001:
1034: l_rule_tab_low range_match_tab;
1035: l_rule_tab_high range_match_tab;
1036: l_rule_tab_null range_match_tab;
1037:
1038: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1039: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' ||G_PKG_NAME ||'.'||'add_to_range_cache';
1040:
1041: BEGIN
1042:
1043: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1044:
1045: IF l_debug_on THEN
1046:
1047: WSH_DEBUG_SV.push(l_module_name);
1048: WSH_DEBUG_SV.log(l_module_name,'p_key_low ',p_key_low);
1049: WSH_DEBUG_SV.log(l_module_name,'p_key_high ',p_key_high);
1050: WSH_DEBUG_SV.log(l_module_name,'p_null_key ',p_null_key);
1051: WSH_DEBUG_SV.log(l_module_name,'p_overlap_flag',p_overlap_flag);
1044:
1045: IF l_debug_on THEN
1046:
1047: WSH_DEBUG_SV.push(l_module_name);
1048: WSH_DEBUG_SV.log(l_module_name,'p_key_low ',p_key_low);
1049: WSH_DEBUG_SV.log(l_module_name,'p_key_high ',p_key_high);
1050: WSH_DEBUG_SV.log(l_module_name,'p_null_key ',p_null_key);
1051: WSH_DEBUG_SV.log(l_module_name,'p_overlap_flag',p_overlap_flag);
1052: WSH_DEBUG_SV.log(l_module_name,'p_rule_id ',p_rule_id);
1045: IF l_debug_on THEN
1046:
1047: WSH_DEBUG_SV.push(l_module_name);
1048: WSH_DEBUG_SV.log(l_module_name,'p_key_low ',p_key_low);
1049: WSH_DEBUG_SV.log(l_module_name,'p_key_high ',p_key_high);
1050: WSH_DEBUG_SV.log(l_module_name,'p_null_key ',p_null_key);
1051: WSH_DEBUG_SV.log(l_module_name,'p_overlap_flag',p_overlap_flag);
1052: WSH_DEBUG_SV.log(l_module_name,'p_rule_id ',p_rule_id);
1053:
1046:
1047: WSH_DEBUG_SV.push(l_module_name);
1048: WSH_DEBUG_SV.log(l_module_name,'p_key_low ',p_key_low);
1049: WSH_DEBUG_SV.log(l_module_name,'p_key_high ',p_key_high);
1050: WSH_DEBUG_SV.log(l_module_name,'p_null_key ',p_null_key);
1051: WSH_DEBUG_SV.log(l_module_name,'p_overlap_flag',p_overlap_flag);
1052: WSH_DEBUG_SV.log(l_module_name,'p_rule_id ',p_rule_id);
1053:
1054: END IF;
1047: WSH_DEBUG_SV.push(l_module_name);
1048: WSH_DEBUG_SV.log(l_module_name,'p_key_low ',p_key_low);
1049: WSH_DEBUG_SV.log(l_module_name,'p_key_high ',p_key_high);
1050: WSH_DEBUG_SV.log(l_module_name,'p_null_key ',p_null_key);
1051: WSH_DEBUG_SV.log(l_module_name,'p_overlap_flag',p_overlap_flag);
1052: WSH_DEBUG_SV.log(l_module_name,'p_rule_id ',p_rule_id);
1053:
1054: END IF;
1055:
1048: WSH_DEBUG_SV.log(l_module_name,'p_key_low ',p_key_low);
1049: WSH_DEBUG_SV.log(l_module_name,'p_key_high ',p_key_high);
1050: WSH_DEBUG_SV.log(l_module_name,'p_null_key ',p_null_key);
1051: WSH_DEBUG_SV.log(l_module_name,'p_overlap_flag',p_overlap_flag);
1052: WSH_DEBUG_SV.log(l_module_name,'p_rule_id ',p_rule_id);
1053:
1054: END IF;
1055:
1056: IF (p_null_key IS NULL) THEN
1105:
1106: END IF;
1107:
1108: IF l_debug_on THEN
1109: WSH_DEBUG_SV.POP (l_module_name);
1110: END IF;
1111:
1112: EXCEPTION
1113: WHEN others THEN
1115: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.ADD_TO_RANGE_CACHE');
1116: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1117:
1118: IF l_debug_on THEN
1119: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1120: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1121: END IF;
1122: END add_to_range_cache;
1123:
1116: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1117:
1118: IF l_debug_on THEN
1119: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1120: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1121: END IF;
1122: END add_to_range_cache;
1123:
1124:
1200: next_rule_id NUMBER;
1201:
1202: l_group_attr WSH_UTIL_CORE.ID_TAB_TYPE;
1203:
1204: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1205: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'build_cache';
1206:
1207: --
1208: --Exceptions
1213:
1214: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1215:
1216: IF l_debug_on THEN
1217: WSH_DEBUG_SV.push(l_module_name);
1218: WSH_DEBUG_SV.log(l_module_name,'p_group_id ',p_group_id);
1219: END IF;
1220:
1221: refresh_cache(x_return_status);
1214: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1215:
1216: IF l_debug_on THEN
1217: WSH_DEBUG_SV.push(l_module_name);
1218: WSH_DEBUG_SV.log(l_module_name,'p_group_id ',p_group_id);
1219: END IF;
1220:
1221: refresh_cache(x_return_status);
1222:
1347: END LOOP;
1348:
1349: IF l_debug_on THEN
1350:
1351: WSH_DEBUG_SV.log(l_module_name,' Rule ID ', curr_rule_id );
1352: WSH_DEBUG_SV.log(l_module_name,' From_region ', from_region );
1353: WSH_DEBUG_SV.log(l_module_name,' To_region ', to_region);
1354: WSH_DEBUG_SV.log(l_module_name,' From_postal_low ', from_postal_low);
1355: WSH_DEBUG_SV.log(l_module_name,' From_postal_high ', from_postal_high);
1348:
1349: IF l_debug_on THEN
1350:
1351: WSH_DEBUG_SV.log(l_module_name,' Rule ID ', curr_rule_id );
1352: WSH_DEBUG_SV.log(l_module_name,' From_region ', from_region );
1353: WSH_DEBUG_SV.log(l_module_name,' To_region ', to_region);
1354: WSH_DEBUG_SV.log(l_module_name,' From_postal_low ', from_postal_low);
1355: WSH_DEBUG_SV.log(l_module_name,' From_postal_high ', from_postal_high);
1356: WSH_DEBUG_SV.log(l_module_name,' To_postal_low ', to_postal_low);
1349: IF l_debug_on THEN
1350:
1351: WSH_DEBUG_SV.log(l_module_name,' Rule ID ', curr_rule_id );
1352: WSH_DEBUG_SV.log(l_module_name,' From_region ', from_region );
1353: WSH_DEBUG_SV.log(l_module_name,' To_region ', to_region);
1354: WSH_DEBUG_SV.log(l_module_name,' From_postal_low ', from_postal_low);
1355: WSH_DEBUG_SV.log(l_module_name,' From_postal_high ', from_postal_high);
1356: WSH_DEBUG_SV.log(l_module_name,' To_postal_low ', to_postal_low);
1357: WSH_DEBUG_SV.log(l_module_name,' To_postal_high ', to_postal_high);
1350:
1351: WSH_DEBUG_SV.log(l_module_name,' Rule ID ', curr_rule_id );
1352: WSH_DEBUG_SV.log(l_module_name,' From_region ', from_region );
1353: WSH_DEBUG_SV.log(l_module_name,' To_region ', to_region);
1354: WSH_DEBUG_SV.log(l_module_name,' From_postal_low ', from_postal_low);
1355: WSH_DEBUG_SV.log(l_module_name,' From_postal_high ', from_postal_high);
1356: WSH_DEBUG_SV.log(l_module_name,' To_postal_low ', to_postal_low);
1357: WSH_DEBUG_SV.log(l_module_name,' To_postal_high ', to_postal_high);
1358: WSH_DEBUG_SV.log(l_module_name,' From_weight ', from_weight);
1351: WSH_DEBUG_SV.log(l_module_name,' Rule ID ', curr_rule_id );
1352: WSH_DEBUG_SV.log(l_module_name,' From_region ', from_region );
1353: WSH_DEBUG_SV.log(l_module_name,' To_region ', to_region);
1354: WSH_DEBUG_SV.log(l_module_name,' From_postal_low ', from_postal_low);
1355: WSH_DEBUG_SV.log(l_module_name,' From_postal_high ', from_postal_high);
1356: WSH_DEBUG_SV.log(l_module_name,' To_postal_low ', to_postal_low);
1357: WSH_DEBUG_SV.log(l_module_name,' To_postal_high ', to_postal_high);
1358: WSH_DEBUG_SV.log(l_module_name,' From_weight ', from_weight);
1359: WSH_DEBUG_SV.log(l_module_name,' To_weight ', to_weight);
1352: WSH_DEBUG_SV.log(l_module_name,' From_region ', from_region );
1353: WSH_DEBUG_SV.log(l_module_name,' To_region ', to_region);
1354: WSH_DEBUG_SV.log(l_module_name,' From_postal_low ', from_postal_low);
1355: WSH_DEBUG_SV.log(l_module_name,' From_postal_high ', from_postal_high);
1356: WSH_DEBUG_SV.log(l_module_name,' To_postal_low ', to_postal_low);
1357: WSH_DEBUG_SV.log(l_module_name,' To_postal_high ', to_postal_high);
1358: WSH_DEBUG_SV.log(l_module_name,' From_weight ', from_weight);
1359: WSH_DEBUG_SV.log(l_module_name,' To_weight ', to_weight);
1360: WSH_DEBUG_SV.log(l_module_name,' From_volume ', from_volume);
1353: WSH_DEBUG_SV.log(l_module_name,' To_region ', to_region);
1354: WSH_DEBUG_SV.log(l_module_name,' From_postal_low ', from_postal_low);
1355: WSH_DEBUG_SV.log(l_module_name,' From_postal_high ', from_postal_high);
1356: WSH_DEBUG_SV.log(l_module_name,' To_postal_low ', to_postal_low);
1357: WSH_DEBUG_SV.log(l_module_name,' To_postal_high ', to_postal_high);
1358: WSH_DEBUG_SV.log(l_module_name,' From_weight ', from_weight);
1359: WSH_DEBUG_SV.log(l_module_name,' To_weight ', to_weight);
1360: WSH_DEBUG_SV.log(l_module_name,' From_volume ', from_volume);
1361: WSH_DEBUG_SV.log(l_module_name,' To_volume ', to_volume);
1354: WSH_DEBUG_SV.log(l_module_name,' From_postal_low ', from_postal_low);
1355: WSH_DEBUG_SV.log(l_module_name,' From_postal_high ', from_postal_high);
1356: WSH_DEBUG_SV.log(l_module_name,' To_postal_low ', to_postal_low);
1357: WSH_DEBUG_SV.log(l_module_name,' To_postal_high ', to_postal_high);
1358: WSH_DEBUG_SV.log(l_module_name,' From_weight ', from_weight);
1359: WSH_DEBUG_SV.log(l_module_name,' To_weight ', to_weight);
1360: WSH_DEBUG_SV.log(l_module_name,' From_volume ', from_volume);
1361: WSH_DEBUG_SV.log(l_module_name,' To_volume ', to_volume);
1362: WSH_DEBUG_SV.log(l_module_name,' From_transit_time ', from_transit_time);
1355: WSH_DEBUG_SV.log(l_module_name,' From_postal_high ', from_postal_high);
1356: WSH_DEBUG_SV.log(l_module_name,' To_postal_low ', to_postal_low);
1357: WSH_DEBUG_SV.log(l_module_name,' To_postal_high ', to_postal_high);
1358: WSH_DEBUG_SV.log(l_module_name,' From_weight ', from_weight);
1359: WSH_DEBUG_SV.log(l_module_name,' To_weight ', to_weight);
1360: WSH_DEBUG_SV.log(l_module_name,' From_volume ', from_volume);
1361: WSH_DEBUG_SV.log(l_module_name,' To_volume ', to_volume);
1362: WSH_DEBUG_SV.log(l_module_name,' From_transit_time ', from_transit_time);
1363: WSH_DEBUG_SV.log(l_module_name,' To_transit_time ', to_transit_time);
1356: WSH_DEBUG_SV.log(l_module_name,' To_postal_low ', to_postal_low);
1357: WSH_DEBUG_SV.log(l_module_name,' To_postal_high ', to_postal_high);
1358: WSH_DEBUG_SV.log(l_module_name,' From_weight ', from_weight);
1359: WSH_DEBUG_SV.log(l_module_name,' To_weight ', to_weight);
1360: WSH_DEBUG_SV.log(l_module_name,' From_volume ', from_volume);
1361: WSH_DEBUG_SV.log(l_module_name,' To_volume ', to_volume);
1362: WSH_DEBUG_SV.log(l_module_name,' From_transit_time ', from_transit_time);
1363: WSH_DEBUG_SV.log(l_module_name,' To_transit_time ', to_transit_time);
1364: WSH_DEBUG_SV.log(l_module_name,' Fob_code ', fob_code);
1357: WSH_DEBUG_SV.log(l_module_name,' To_postal_high ', to_postal_high);
1358: WSH_DEBUG_SV.log(l_module_name,' From_weight ', from_weight);
1359: WSH_DEBUG_SV.log(l_module_name,' To_weight ', to_weight);
1360: WSH_DEBUG_SV.log(l_module_name,' From_volume ', from_volume);
1361: WSH_DEBUG_SV.log(l_module_name,' To_volume ', to_volume);
1362: WSH_DEBUG_SV.log(l_module_name,' From_transit_time ', from_transit_time);
1363: WSH_DEBUG_SV.log(l_module_name,' To_transit_time ', to_transit_time);
1364: WSH_DEBUG_SV.log(l_module_name,' Fob_code ', fob_code);
1365:
1358: WSH_DEBUG_SV.log(l_module_name,' From_weight ', from_weight);
1359: WSH_DEBUG_SV.log(l_module_name,' To_weight ', to_weight);
1360: WSH_DEBUG_SV.log(l_module_name,' From_volume ', from_volume);
1361: WSH_DEBUG_SV.log(l_module_name,' To_volume ', to_volume);
1362: WSH_DEBUG_SV.log(l_module_name,' From_transit_time ', from_transit_time);
1363: WSH_DEBUG_SV.log(l_module_name,' To_transit_time ', to_transit_time);
1364: WSH_DEBUG_SV.log(l_module_name,' Fob_code ', fob_code);
1365:
1366: END IF;
1359: WSH_DEBUG_SV.log(l_module_name,' To_weight ', to_weight);
1360: WSH_DEBUG_SV.log(l_module_name,' From_volume ', from_volume);
1361: WSH_DEBUG_SV.log(l_module_name,' To_volume ', to_volume);
1362: WSH_DEBUG_SV.log(l_module_name,' From_transit_time ', from_transit_time);
1363: WSH_DEBUG_SV.log(l_module_name,' To_transit_time ', to_transit_time);
1364: WSH_DEBUG_SV.log(l_module_name,' Fob_code ', fob_code);
1365:
1366: END IF;
1367:
1360: WSH_DEBUG_SV.log(l_module_name,' From_volume ', from_volume);
1361: WSH_DEBUG_SV.log(l_module_name,' To_volume ', to_volume);
1362: WSH_DEBUG_SV.log(l_module_name,' From_transit_time ', from_transit_time);
1363: WSH_DEBUG_SV.log(l_module_name,' To_transit_time ', to_transit_time);
1364: WSH_DEBUG_SV.log(l_module_name,' Fob_code ', fob_code);
1365:
1366: END IF;
1367:
1368: fixed_key := get_fixed_key(p_group_id => p_group_id,
1385:
1386: IF (l_group_attr.EXISTS(g_wt_idx)) THEN
1387:
1388: IF l_debug_on THEN
1389: WSH_DEBUG_SV.logmsg(l_module_name,'Adding to Weight Cache');
1390: END IF;
1391:
1392: IF (from_weight <> g_num_absent) AND (to_weight <> g_num_absent) THEN
1393:
1432: IF (l_group_attr.EXISTS(g_vol_idx)) THEN
1433:
1434:
1435: IF l_debug_on THEN
1436: WSH_DEBUG_SV.logmsg(l_module_name,'Adding to Volume Cache');
1437: END IF;
1438:
1439: IF (from_volume <> g_num_absent) AND (to_volume <> g_num_absent) THEN
1440:
1477:
1478: IF (l_group_attr.EXISTS(g_transit_time_idx)) THEN
1479:
1480: IF l_debug_on THEN
1481: WSH_DEBUG_SV.logmsg(l_module_name,'Adding to Transit Time Cache');
1482: END IF;
1483:
1484:
1485: IF (from_transit_time <> g_num_absent) AND (to_transit_time <> g_num_absent) THEN
1524:
1525: IF (l_group_attr.EXISTS(g_to_post_idx)) THEN
1526:
1527: IF l_debug_on THEN
1528: WSH_DEBUG_SV.logmsg(l_module_name,'Adding to To Postal Code Cache');
1529: END IF;
1530:
1531: IF (to_postal_low <> g_val_absent) AND (to_postal_high <> g_val_absent) THEN
1532:
1569:
1570: ELSIF (to_postal_low = g_val_absent) AND (to_postal_high <> g_val_absent) THEN
1571:
1572: IF l_debug_on THEN
1573: WSH_DEBUG_SV.logmsg(l_module_name,'Lower postal code is NULL');
1574: END IF;
1575:
1576: key_high := get_key_for_char( p_fixed_key => fixed_key,
1577: p_char => to_postal_high);
1591:
1592: ELSE
1593:
1594: IF l_debug_on THEN
1595: WSH_DEBUG_SV.logmsg(l_module_name,'Upper postal code is NULL');
1596: END IF;
1597:
1598: key_low := get_key_for_char( p_fixed_key => fixed_key,
1599: p_char => to_postal_low);
1615:
1616: IF (l_group_attr.EXISTS(g_fr_post_idx)) THEN
1617:
1618: IF l_debug_on THEN
1619: WSH_DEBUG_SV.logmsg(l_module_name,'Adding to From Postal Code Cache');
1620: END IF;
1621:
1622: IF (from_postal_low <> g_val_absent) AND (from_postal_high <> g_val_absent) THEN
1623:
1642:
1643: ELSIF (from_postal_low = g_val_absent) AND (from_postal_high = g_val_absent) THEN
1644:
1645: IF l_debug_on THEN
1646: WSH_DEBUG_SV.logmsg(l_module_name,'Both from and to Postal Codes are NULL');
1647: END IF;
1648:
1649: add_to_range_cache( p_cache => g_from_postal_cache,
1650: p_null_key => null_key,
1659:
1660: ELSIF (from_postal_low = g_val_absent) AND (from_postal_high <> g_val_absent) THEN
1661:
1662: IF l_debug_on THEN
1663: WSH_DEBUG_SV.logmsg(l_module_name,'Lower postal code is NULL');
1664: END IF;
1665:
1666: key_high := get_key_for_char( p_fixed_key => fixed_key,
1667: p_char => from_postal_high);
1681:
1682: ELSE
1683:
1684: IF l_debug_on THEN
1685: WSH_DEBUG_SV.logmsg(l_module_name,'Upper postal code is NULL');
1686: END IF;
1687:
1688: key_low := get_key_for_char( p_fixed_key => fixed_key,
1689: p_char => from_postal_low);
1707:
1708: IF (l_group_attr.EXISTS(g_fob_code_idx)) THEN
1709:
1710: IF l_debug_on THEN
1711: WSH_DEBUG_SV.logmsg(l_module_name,'Adding to FOB Cache');
1712: END IF;
1713:
1714: IF ( fob_code <> g_val_absent) THEN
1715:
1753:
1754: END IF;
1755:
1756: IF l_debug_on THEN
1757: WSH_DEBUG_SV.POP (l_module_name);
1758: END IF;
1759:
1760: EXCEPTION
1761: WHEN FTE_CS_ERROR_INVALID_RANGE THEN
1766: --
1767: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1768: --
1769: IF l_debug_on THEN
1770: WSH_DEBUG_SV.pop(l_module_name,'Attribute Range has either FROM or TO Only (Range is Invalid');
1771: END IF;
1772:
1773: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1774:
1779: --
1780: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1781: --
1782: IF l_debug_on THEN
1783: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1784: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1785: END IF;
1786: --
1787:
1780: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1781: --
1782: IF l_debug_on THEN
1783: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1784: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1785: END IF;
1786: --
1787:
1788: WHEN others THEN
1794: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.ADD_TO_RANGE_CACHE');
1795: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1796: --
1797: IF l_debug_on THEN
1798: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1799: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1800: END IF;
1801:
1802: END build_cache;
1795: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1796: --
1797: IF l_debug_on THEN
1798: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1799: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1800: END IF;
1801:
1802: END build_cache;
1803:
1822: l_null_key VARCHAR2(32767);
1823: l_rule_tab exact_match_tab;
1824: itr NUMBER;
1825:
1826: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1827: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'search_exact_cache_for_null';
1828:
1829: BEGIN
1830:
1828:
1829: BEGIN
1830:
1831: IF l_debug_on THEN
1832: wsh_debug_sv.push (l_module_name);
1833: END IF;
1834:
1835: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1836:
1854: END IF;
1855: END IF;
1856:
1857: IF l_debug_on THEN
1858: WSH_DEBUG_SV.POP (l_module_name);
1859: END IF;
1860:
1861: EXCEPTION
1862: WHEN others THEN
1864: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.search_exact_cache_for_null');
1865: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1866:
1867: IF l_debug_on THEN
1868: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1869: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1870: END IF;
1871: --
1872: END search_exact_cache_for_null;
1865: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1866:
1867: IF l_debug_on THEN
1868: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1869: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1870: END IF;
1871: --
1872: END search_exact_cache_for_null;
1873:
1893: l_null_key VARCHAR2(32767);
1894: l_rule_tab range_match_tab;
1895: itr NUMBER;
1896:
1897: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1898: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'search_range_cache_for_null';
1899:
1900: BEGIN
1901:
1899:
1900: BEGIN
1901:
1902: IF l_debug_on THEN
1903: wsh_debug_sv.push (l_module_name);
1904: END IF;
1905:
1906: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1907:
1931: END IF;
1932: END IF;
1933:
1934: IF l_debug_on THEN
1935: WSH_DEBUG_SV.POP (l_module_name);
1936: END IF;
1937:
1938: EXCEPTION
1939: WHEN others THEN
1940:
1941: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.search_range_cache_for_null');
1942: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1943: IF l_debug_on THEN
1944: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1945: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1946: END IF;
1947: --
1948: END search_range_cache_for_null;
1941: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.search_range_cache_for_null');
1942: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1943: IF l_debug_on THEN
1944: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1945: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1946: END IF;
1947: --
1948: END search_range_cache_for_null;
1949:
1971: IS
1972: l_curr_idx VARCHAR2(32767);
1973: l_rule_tab range_match_tab;
1974: itr NUMBER;
1975: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1976: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'GET_HIGHER_BREAKS';
1977: BEGIN
1978: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1979:
1977: BEGIN
1978: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1979:
1980: IF l_debug_on THEN
1981: wsh_debug_sv.push(l_module_name);
1982: END IF;
1983:
1984: l_curr_idx := p_cache.NEXT(p_start_idx);
1985:
2015: l_curr_idx := p_cache.NEXT(l_curr_idx);
2016: END LOOP;
2017:
2018: IF l_debug_on THEN
2019: WSH_DEBUG_SV.POP(l_module_name);
2020: END IF;
2021:
2022: EXCEPTION
2023: WHEN others THEN
2025: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.get_higher_breaks');
2026: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2027:
2028: IF l_debug_on THEN
2029: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2030: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2031: END IF;
2032: --
2033: END get_higher_breaks;
2026: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2027:
2028: IF l_debug_on THEN
2029: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2030: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2031: END IF;
2032: --
2033: END get_higher_breaks;
2034:
2059: l_curr_idx VARCHAR2(32767);
2060: l_rule_tab range_match_tab;
2061: itr NUMBER;
2062:
2063: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2064: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'GET_LOWER_BREAKS';
2065:
2066: BEGIN
2067:
2067:
2068: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2069:
2070: IF l_debug_on THEN
2071: wsh_debug_sv.push (l_module_name);
2072: END IF;
2073:
2074: l_curr_idx := p_cache.PRIOR(p_start_idx);
2075:
2099: l_curr_idx := p_cache.PRIOR(l_curr_idx);
2100: END LOOP;
2101:
2102: IF l_debug_on THEN
2103: WSH_DEBUG_SV.POP (l_module_name);
2104: END IF;
2105: EXCEPTION
2106: WHEN others THEN
2107: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.get_lower_breaks');
2107: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.get_lower_breaks');
2108: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2109:
2110: IF l_debug_on THEN
2111: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2112: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2113: END IF;
2114:
2115: END get_lower_breaks;
2108: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2109:
2110: IF l_debug_on THEN
2111: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2112: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2113: END IF;
2114:
2115: END get_lower_breaks;
2116:
2139: IS
2140: l_rule_tab EXACT_MATCH_TAB;
2141: itr NUMBER;
2142:
2143: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2144: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'search_exact_cache';
2145: BEGIN
2146:
2147: IF l_debug_on THEN
2144: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'search_exact_cache';
2145: BEGIN
2146:
2147: IF l_debug_on THEN
2148: wsh_debug_sv.push (l_module_name);
2149: END IF;
2150:
2151: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2152:
2181: END IF;
2182: END IF;
2183:
2184: IF l_debug_on THEN
2185: WSH_DEBUG_SV.POP (l_module_name);
2186: END IF;
2187:
2188: EXCEPTION
2189: WHEN others THEN
2191: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.search_exact_cache');
2192: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2193:
2194: IF l_debug_on THEN
2195: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2196: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2197: END IF;
2198: --
2199: END search_exact_cache;
2192: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2193:
2194: IF l_debug_on THEN
2195: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2196: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2197: END IF;
2198: --
2199: END search_exact_cache;
2200:
2241: lower_breaks rule_match_tab;
2242: higher_breaks rule_match_tab;
2243: match_rules rule_match_tab;
2244:
2245: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2246: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'search_range_cache';
2247:
2248: BEGIN
2249:
2247:
2248: BEGIN
2249:
2250: IF l_debug_on THEN
2251: wsh_debug_sv.push (l_module_name);
2252: END IF;
2253:
2254: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2255: l_unique_rule := true;
2326:
2327: IF (NOT check_overlap) THEN
2328:
2329: IF l_debug_on THEN
2330: WSH_DEBUG_SV.POP (l_module_name);
2331: END IF;
2332: RETURN;
2333:
2334: END IF;
2347: OR (INSTR(l_prev_idx,p_fixed_key,1,1) <> 1) OR (l_prev_idx = l_null_key)
2348: OR (INSTR(l_next_idx,p_fixed_key,1,1) <> 1) OR (l_next_idx = l_null_key)
2349: THEN
2350: IF l_debug_on THEN
2351: WSH_DEBUG_SV.POP (l_module_name);
2352: END IF;
2353: RETURN;
2354: END IF;
2355:
2390: --
2391:
2392: IF (l_prev_rec.overlap_flag='N' AND l_next_rec.overlap_flag = 'N') THEN
2393: IF l_debug_on THEN
2394: WSH_DEBUG_SV.POP (l_module_name);
2395: END IF;
2396: RETURN;
2397: END IF;
2398: END IF;
2421: END IF;
2422:
2423: IF (lower_breaks IS NULL) THEN
2424: IF l_debug_on THEN
2425: WSH_DEBUG_SV.POP (l_module_name);
2426: END IF;
2427: RETURN;
2428: END IF;
2429:
2441: END IF;
2442:
2443: IF (higher_breaks IS NULL) THEN
2444: IF l_debug_on THEN
2445: WSH_DEBUG_SV.POP (l_module_name);
2446: END IF;
2447: RETURN;
2448: END IF;
2449:
2458: END IF;
2459: END IF;
2460:
2461: IF l_debug_on THEN
2462: WSH_DEBUG_SV.POP (l_module_name);
2463: END IF;
2464:
2465: EXCEPTION
2466: WHEN others THEN
2468: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.search_range_cache');
2469: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2470:
2471: IF l_debug_on THEN
2472: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2473: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2474: END IF;
2475: --
2476: END search_range_cache;
2469: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2470:
2471: IF l_debug_on THEN
2472: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2473: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2474: END IF;
2475: --
2476: END search_range_cache;
2477:
2495: itr NUMBER;
2496: l_rule_tab_itr VARCHAR2(32767);
2497: match_rules rule_match_tab;
2498:
2499: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2500: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_all_rules_in_cache';
2501:
2502: BEGIN
2503:
2501:
2502: BEGIN
2503:
2504: IF l_debug_on THEN
2505: wsh_debug_sv.push (l_module_name);
2506: END IF;
2507:
2508: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2509:
2534: END LOOP;
2535: END IF;
2536:
2537: IF l_debug_on THEN
2538: WSH_DEBUG_SV.POP (l_module_name);
2539: END IF;
2540:
2541: EXCEPTION
2542: WHEN others THEN
2544: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.get_all_rules_in_cache');
2545: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2546:
2547: IF l_debug_on THEN
2548: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2549: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2550: END IF;
2551: --
2552: END get_all_rules_in_cache;
2545: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2546:
2547: IF l_debug_on THEN
2548: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2549: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2550: END IF;
2551: --
2552: END get_all_rules_in_cache;
2553:
2571: l_rule_str VARCHAR2(4000);
2572: sql_string VARCHAR2(4000);
2573: itr NUMBER;
2574:
2575: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2576: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'search_min_high_transit_time';
2577:
2578: BEGIN
2579: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2602: || ' ORDER BY attribute_value_to_number)'
2603: || ' WHERE ROWNUM=1';
2604:
2605: IF l_debug_on THEN
2606: WSH_DEBUG_SV.logmsg(l_module_name,'Rule String is '||l_rule_str);
2607: WSH_DEBUG_SV.logmsg(l_module_name,'SQL String is '||sql_string);
2608: END IF;
2609:
2610: EXECUTE IMMEDIATE sql_string INTO x_rule_id;
2603: || ' WHERE ROWNUM=1';
2604:
2605: IF l_debug_on THEN
2606: WSH_DEBUG_SV.logmsg(l_module_name,'Rule String is '||l_rule_str);
2607: WSH_DEBUG_SV.logmsg(l_module_name,'SQL String is '||sql_string);
2608: END IF;
2609:
2610: EXECUTE IMMEDIATE sql_string INTO x_rule_id;
2611:
2609:
2610: EXECUTE IMMEDIATE sql_string INTO x_rule_id;
2611:
2612: IF l_debug_on THEN
2613: WSH_DEBUG_SV.logmsg(l_module_name,'Rule Id is '||x_rule_id);
2614: wsh_debug_sv.pop(l_module_name);
2615: END IF;
2616:
2617: EXCEPTION
2610: EXECUTE IMMEDIATE sql_string INTO x_rule_id;
2611:
2612: IF l_debug_on THEN
2613: WSH_DEBUG_SV.logmsg(l_module_name,'Rule Id is '||x_rule_id);
2614: wsh_debug_sv.pop(l_module_name);
2615: END IF;
2616:
2617: EXCEPTION
2618: WHEN others THEN
2619: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.search_min_high_transit_time');
2620: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2621:
2622: IF l_debug_on THEN
2623: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2624: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2625: END IF;
2626: END search_min_high_transit_time;
2627:
2620: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2621:
2622: IF l_debug_on THEN
2623: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2624: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2625: END IF;
2626: END search_min_high_transit_time;
2627:
2628:
2677: itr NUMBER;
2678:
2679: l_group_name VARCHAR2(32767);
2680:
2681: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2682: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'FIND_RULE_FOR_KEY';
2683:
2684: OVERLAPPING_RULES_EXIST EXCEPTION;
2685: BEGIN
2686:
2687: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2688:
2689: IF l_debug_on THEN
2690: wsh_debug_sv.push(l_module_name);
2691: END IF;
2692:
2693: x_rule_id := g_rule_not_found;
2694:
2706: --Compare with the old engine.
2707: --
2708:
2709: IF l_debug_on THEN
2710: wsh_debug_sv.logmsg(l_module_name,'Searching FOB (Not null) ');
2711: END IF;
2712:
2713:
2714: l_attribute_key := get_key_for_char( p_fixed_key => p_fixed_key ,
2728: END IF;
2729: ELSE
2730:
2731: IF l_debug_on THEN
2732: wsh_debug_sv.logmsg(l_module_name,'Searching FOB (Null) ');
2733: END IF;
2734:
2735: search_exact_cache_for_null( p_cache => g_fob_cache,
2736: p_fixed_key => p_fixed_key,
2747: IF l_debug_on THEN
2748: IF l_temp_set IS NOT NULL THEN
2749: itr := l_temp_set.FIRST;
2750: LOOP
2751: wsh_debug_sv.log(l_module_name,'Temp Set Rule Matched',l_temp_set(itr));
2752: EXIT WHEN itr = l_temp_set.LAST;
2753: itr := l_temp_set.NEXT(itr);
2754: END LOOP;
2755: END IF;
2757:
2758: IF (l_temp_set IS NULL) THEN
2759: --No valid rule found
2760: IF l_debug_on THEN
2761: wsh_debug_sv.pop(l_module_name);
2762: END IF;
2763: RETURN;
2764: ELSE
2765: -- This is the first attribute. Here no need to use intersect.
2769: IF l_debug_on THEN
2770: IF l_result_set IS NOT NULL THEN
2771: itr := l_result_set.FIRST;
2772: LOOP
2773: wsh_debug_sv.log(l_module_name,'Result Set Rule Matched',l_result_set(itr));
2774: EXIT WHEN itr = l_result_set.LAST;
2775: itr := l_result_set.NEXT(itr);
2776: END LOOP;
2777: END IF;
2788: IF (p_weight IS NOT NULL) THEN
2789:
2790:
2791: IF l_debug_on THEN
2792: wsh_debug_sv.logmsg(l_module_name,'Searching Weight (Not Null) ');
2793: END IF;
2794:
2795: l_attribute_key := get_key_for_num( p_fixed_key => p_fixed_key ,
2796: p_number => p_weight);
2811:
2812: ELSE
2813:
2814: IF l_debug_on THEN
2815: wsh_debug_sv.logmsg(l_module_name,'Searching Weight (Null) ');
2816: END IF;
2817:
2818: search_range_cache_for_null( p_cache => g_weight_cache,
2819: p_fixed_key => p_fixed_key,
2831: IF l_debug_on THEN
2832: IF l_temp_set IS NOT NULL THEN
2833: itr := l_temp_set.FIRST;
2834: LOOP
2835: wsh_debug_sv.log(l_module_name,'Temp Set Rule Matched',l_temp_set(itr));
2836: EXIT WHEN itr = l_temp_set.LAST;
2837: itr := l_temp_set.NEXT(itr);
2838: END LOOP;
2839: END IF;
2841:
2842: IF (l_temp_set IS NULL) THEN
2843: -- No valid rule is found.
2844: IF l_debug_on THEN
2845: wsh_debug_sv.pop(l_module_name);
2846: END IF;
2847: RETURN;
2848: ELSE
2849: IF (l_result_set IS NULL) THEN
2852: l_result_set := l_result_set MULTISET INTERSECT l_temp_set;
2853:
2854: IF (l_result_set.COUNT = 0) THEN
2855: IF l_debug_on THEN
2856: wsh_debug_sv.pop(l_module_name);
2857: END IF;
2858: RETURN;
2859: END IF;
2860: END IF;
2863: IF l_debug_on THEN
2864: IF l_result_set IS NOT NULL THEN
2865: itr := l_result_set.FIRST;
2866: LOOP
2867: wsh_debug_sv.log(l_module_name,'Result Set Rule Matched',l_result_set(itr));
2868: EXIT WHEN itr = l_result_set.LAST;
2869: itr := l_result_set.NEXT(itr);
2870: END LOOP;
2871: END IF;
2880:
2881: IF (p_volume IS NOT NULL) THEN
2882:
2883: IF l_debug_on THEN
2884: wsh_debug_sv.logmsg(l_module_name,'Searching Volume (Not Null) ');
2885: END IF;
2886:
2887: l_attribute_key := get_key_for_num( p_fixed_key => p_fixed_key ,
2888: p_number => p_volume);
2902: END IF;
2903: ELSE
2904:
2905: IF l_debug_on THEN
2906: wsh_debug_sv.logmsg(l_module_name,'Searching Volume (Null) ');
2907: END IF;
2908:
2909: search_range_cache_for_null( p_cache => g_volume_cache,
2910: p_fixed_key => p_fixed_key,
2921: IF l_debug_on THEN
2922: IF l_temp_set IS NOT NULL THEN
2923: itr := l_temp_set.FIRST;
2924: LOOP
2925: wsh_debug_sv.log(l_module_name,'Temp Set Rule Matched',l_temp_set(itr));
2926: EXIT WHEN itr = l_temp_set.LAST;
2927: itr := l_temp_set.NEXT(itr);
2928: END LOOP;
2929: END IF;
2931:
2932: IF (l_temp_set IS NULL) THEN
2933: -- No valid rule is found.
2934: IF l_debug_on THEN
2935: wsh_debug_sv.pop(l_module_name);
2936: END IF;
2937: RETURN;
2938: ELSE
2939: IF (l_result_set IS NULL) THEN
2942: l_result_set := l_result_set MULTISET INTERSECT l_temp_set;
2943:
2944: IF (l_result_set.COUNT = 0 ) THEN
2945: IF l_debug_on THEN
2946: wsh_debug_sv.pop(l_module_name);
2947: END IF;
2948: RETURN;
2949: END IF;
2950: END IF;
2953: IF l_debug_on THEN
2954: IF l_result_set IS NOT NULL THEN
2955: itr := l_result_set.FIRST;
2956: LOOP
2957: wsh_debug_sv.log(l_module_name,'Result Set Rule Matched',l_result_set(itr));
2958: EXIT WHEN itr = l_result_set.LAST;
2959: itr := l_result_set.NEXT(itr);
2960: END LOOP;
2961: END IF;
2970: -- Do not search for NULL postal codes as that is handled in a
2971: -- different priority.
2972: --
2973: IF l_debug_on THEN
2974: wsh_debug_sv.logmsg(l_module_name,'Searching From Postal Code ');
2975: END IF;
2976:
2977:
2978: l_attribute_key := get_key_for_char( p_fixed_key => p_fixed_key,
2998: -- cache only for null values.
2999: --
3000:
3001: IF l_debug_on THEN
3002: wsh_debug_sv.logmsg(l_module_name,'Searching From Postal Code(null) ');
3003: END IF;
3004:
3005:
3006: search_range_cache_for_null(p_cache => g_from_postal_cache,
3018: IF l_debug_on THEN
3019: IF l_temp_set IS NOT NULL THEN
3020: itr := l_temp_set.FIRST;
3021: LOOP
3022: wsh_debug_sv.log(l_module_name,'Temp Set Rule Matched',l_temp_set(itr));
3023: EXIT WHEN itr = l_temp_set.LAST;
3024: itr := l_temp_set.NEXT(itr);
3025: END LOOP;
3026: END IF;
3030: --
3031: IF (l_temp_set IS NULL) THEN
3032: --No valid rule found
3033: IF l_debug_on THEN
3034: wsh_debug_sv.pop (l_module_name);
3035: END IF;
3036: RETURN;
3037: ELSE
3038: IF (l_result_set IS NULL) THEN
3041:
3042: l_result_set := l_result_set MULTISET INTERSECT l_temp_set;
3043: IF (l_result_set.COUNT=0) THEN
3044: IF l_debug_on THEN
3045: wsh_debug_sv.pop (l_module_name);
3046: END IF;
3047: RETURN;
3048: END IF;
3049: END IF;
3052: IF l_debug_on THEN
3053: IF l_result_set IS NOT NULL THEN
3054: itr := l_result_set.FIRST;
3055: LOOP
3056: wsh_debug_sv.log(l_module_name,'Result Set Rule Matched',l_result_set(itr));
3057: EXIT WHEN itr = l_result_set.LAST;
3058: itr := l_result_set.NEXT(itr);
3059: END LOOP;
3060: END IF;
3069:
3070: IF (p_to_postal_flag) AND (p_to_postal_code IS NOT NULL) THEN
3071:
3072: IF l_debug_on THEN
3073: wsh_debug_sv.logmsg(l_module_name,'Searching to Postal Code ');
3074: END IF;
3075:
3076:
3077: --
3099: -- If postal code is null or flag is false then we will search the
3100: -- cache only for null values.
3101: --
3102: IF l_debug_on THEN
3103: wsh_debug_sv.logmsg(l_module_name,'Searching to Postal Code(null) ');
3104: END IF;
3105:
3106: search_range_cache_for_null( p_cache => g_to_postal_cache,
3107: p_fixed_key => p_fixed_key,
3118: IF l_debug_on THEN
3119: IF l_temp_set IS NOT NULL THEN
3120: itr := l_temp_set.FIRST;
3121: LOOP
3122: wsh_debug_sv.log(l_module_name,'Temp Set Rule Matched',l_temp_set(itr));
3123: EXIT WHEN itr = l_temp_set.LAST;
3124: itr := l_temp_set.NEXT(itr);
3125: END LOOP;
3126: END IF;
3130: --
3131: IF (l_temp_set IS NULL) THEN
3132: --No valid rule found
3133: IF l_debug_on THEN
3134: wsh_debug_sv.pop (l_module_name);
3135: END IF;
3136: RETURN;
3137: ELSE
3138:
3141: ELSE
3142: l_result_set := l_result_set MULTISET INTERSECT l_temp_set;
3143: IF (l_result_set.COUNT = 0) THEN
3144: IF l_debug_on THEN
3145: wsh_debug_sv.pop (l_module_name);
3146: END IF;
3147: RETURN;
3148: END IF;
3149: END IF;
3152: IF l_debug_on THEN
3153: IF l_result_set IS NOT NULL THEN
3154: itr := l_result_set.FIRST;
3155: LOOP
3156: wsh_debug_sv.log(l_module_name,'Result Set Rule Matched',l_result_set(itr));
3157: EXIT WHEN itr = l_result_set.LAST;
3158: itr := l_result_set.NEXT(itr);
3159: END LOOP;
3160: END IF;
3171: IF (p_transit_time IS NOT NULL AND (p_transit_time >= 1)) THEN
3172:
3173:
3174: IF l_debug_on THEN
3175: wsh_debug_sv.logmsg(l_module_name,'Searching Transit Time (Not Null)');
3176: END IF;
3177:
3178: l_attribute_key := get_key_for_num( p_fixed_key => p_fixed_key,
3179: p_number => p_transit_time);
3200: IF l_debug_on THEN
3201: IF l_temp_set IS NOT NULL THEN
3202: itr := l_temp_set.FIRST;
3203: LOOP
3204: wsh_debug_sv.log(l_module_name,'Temp Set Rule Matched',l_temp_set(itr));
3205: EXIT WHEN itr = l_temp_set.LAST;
3206: itr := l_temp_set.NEXT(itr);
3207: END LOOP;
3208: END IF;
3210:
3211: IF (l_temp_set IS NULL) THEN
3212: -- No valid rule is found.
3213: IF l_debug_on THEN
3214: wsh_debug_sv.pop (l_module_name);
3215: END IF;
3216: RETURN;
3217: ELSE
3218: IF (l_result_set IS NULL) THEN
3220: ELSE
3221: l_result_set := l_result_set MULTISET INTERSECT l_temp_set;
3222: IF (l_result_set.COUNT = 0 ) THEN
3223: IF l_debug_on THEN
3224: wsh_debug_sv.pop (l_module_name);
3225: END IF;
3226: RETURN;
3227: END IF;
3228: END IF;
3231: IF l_debug_on THEN
3232: IF l_result_set IS NOT NULL THEN
3233: itr := l_result_set.FIRST;
3234: LOOP
3235: wsh_debug_sv.log(l_module_name,'Result Set Rule Matched',l_result_set(itr));
3236: EXIT WHEN itr = l_result_set.LAST;
3237: itr := l_result_set.NEXT(itr);
3238: END LOOP;
3239: END IF;
3251: -- (If only 1 rule then do nothing)
3252: --
3253:
3254: IF l_debug_on THEN
3255: wsh_debug_sv.logmsg(l_module_name,'Searching min high transit time ');
3256: END IF;
3257:
3258: search_min_high_Transit_Time( p_result_tab => l_result_set,
3259: x_rule_id => x_rule_id,
3265: END IF;
3266: END IF;
3267:
3268: IF l_debug_on THEN
3269: wsh_debug_sv.pop (l_module_name);
3270: END IF;
3271: RETURN;
3272: END IF;
3273: END IF;
3299: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3300: WSH_UTIL_CORE.add_message(x_return_status);
3301:
3302: IF l_debug_on THEN
3303: WSH_DEBUG_SV.logmsg(l_module_name,'Overlapping Rules Exist for Group'||l_group_name);
3304: WSH_DEBUG_SV.pop(l_module_name);
3305: END IF;
3306:
3307: WHEN others THEN
3300: WSH_UTIL_CORE.add_message(x_return_status);
3301:
3302: IF l_debug_on THEN
3303: WSH_DEBUG_SV.logmsg(l_module_name,'Overlapping Rules Exist for Group'||l_group_name);
3304: WSH_DEBUG_SV.pop(l_module_name);
3305: END IF;
3306:
3307: WHEN others THEN
3308: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.find_rule_for_key');
3308: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.find_rule_for_key');
3309: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3310:
3311: IF l_debug_on THEN
3312: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3313: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3314: END IF;
3315:
3316: END find_rule_for_key;
3309: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3310:
3311: IF l_debug_on THEN
3312: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3313: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3314: END IF;
3315:
3316: END find_rule_for_key;
3317:
3376:
3377: FTE_CS_WGHT_CONV_ERR EXCEPTION;
3378: FTE_CS_VOL_CONV_ERR EXCEPTION;
3379:
3380: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
3381: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'get_matching_rule';
3382:
3383:
3384: BEGIN
3385:
3386: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3387:
3388: IF l_debug_on THEN
3389: wsh_debug_sv.push (l_module_name);
3390: WSH_DEBUG_SV.log(l_module_name,'Group Id ',p_info.group_id);
3391: WSH_DEBUG_SV.log(l_module_name,'Delivery Id ',p_info.delivery_id);
3392: WSH_DEBUG_SV.log(l_module_name,'Trip Id ',p_info.trip_id);
3393: WSH_DEBUG_SV.log(l_module_name,'Weight ',p_info.weight);
3386: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3387:
3388: IF l_debug_on THEN
3389: wsh_debug_sv.push (l_module_name);
3390: WSH_DEBUG_SV.log(l_module_name,'Group Id ',p_info.group_id);
3391: WSH_DEBUG_SV.log(l_module_name,'Delivery Id ',p_info.delivery_id);
3392: WSH_DEBUG_SV.log(l_module_name,'Trip Id ',p_info.trip_id);
3393: WSH_DEBUG_SV.log(l_module_name,'Weight ',p_info.weight);
3394: WSH_DEBUG_SV.log(l_module_name,'Weight UOM ',p_info.weight_uom_code);
3387:
3388: IF l_debug_on THEN
3389: wsh_debug_sv.push (l_module_name);
3390: WSH_DEBUG_SV.log(l_module_name,'Group Id ',p_info.group_id);
3391: WSH_DEBUG_SV.log(l_module_name,'Delivery Id ',p_info.delivery_id);
3392: WSH_DEBUG_SV.log(l_module_name,'Trip Id ',p_info.trip_id);
3393: WSH_DEBUG_SV.log(l_module_name,'Weight ',p_info.weight);
3394: WSH_DEBUG_SV.log(l_module_name,'Weight UOM ',p_info.weight_uom_code);
3395: WSH_DEBUG_SV.log(l_module_name,'Volume ',p_info.volume);
3388: IF l_debug_on THEN
3389: wsh_debug_sv.push (l_module_name);
3390: WSH_DEBUG_SV.log(l_module_name,'Group Id ',p_info.group_id);
3391: WSH_DEBUG_SV.log(l_module_name,'Delivery Id ',p_info.delivery_id);
3392: WSH_DEBUG_SV.log(l_module_name,'Trip Id ',p_info.trip_id);
3393: WSH_DEBUG_SV.log(l_module_name,'Weight ',p_info.weight);
3394: WSH_DEBUG_SV.log(l_module_name,'Weight UOM ',p_info.weight_uom_code);
3395: WSH_DEBUG_SV.log(l_module_name,'Volume ',p_info.volume);
3396: WSH_DEBUG_SV.log(l_module_name,'Volume UOM ',p_info.volume_uom_code);
3389: wsh_debug_sv.push (l_module_name);
3390: WSH_DEBUG_SV.log(l_module_name,'Group Id ',p_info.group_id);
3391: WSH_DEBUG_SV.log(l_module_name,'Delivery Id ',p_info.delivery_id);
3392: WSH_DEBUG_SV.log(l_module_name,'Trip Id ',p_info.trip_id);
3393: WSH_DEBUG_SV.log(l_module_name,'Weight ',p_info.weight);
3394: WSH_DEBUG_SV.log(l_module_name,'Weight UOM ',p_info.weight_uom_code);
3395: WSH_DEBUG_SV.log(l_module_name,'Volume ',p_info.volume);
3396: WSH_DEBUG_SV.log(l_module_name,'Volume UOM ',p_info.volume_uom_code);
3397: WSH_DEBUG_SV.log(l_module_name,'Transit Time ',p_info.transit_time);
3390: WSH_DEBUG_SV.log(l_module_name,'Group Id ',p_info.group_id);
3391: WSH_DEBUG_SV.log(l_module_name,'Delivery Id ',p_info.delivery_id);
3392: WSH_DEBUG_SV.log(l_module_name,'Trip Id ',p_info.trip_id);
3393: WSH_DEBUG_SV.log(l_module_name,'Weight ',p_info.weight);
3394: WSH_DEBUG_SV.log(l_module_name,'Weight UOM ',p_info.weight_uom_code);
3395: WSH_DEBUG_SV.log(l_module_name,'Volume ',p_info.volume);
3396: WSH_DEBUG_SV.log(l_module_name,'Volume UOM ',p_info.volume_uom_code);
3397: WSH_DEBUG_SV.log(l_module_name,'Transit Time ',p_info.transit_time);
3398: WSH_DEBUG_SV.log(l_module_name,'Ship From Id ',p_info.ship_from_location_id);
3391: WSH_DEBUG_SV.log(l_module_name,'Delivery Id ',p_info.delivery_id);
3392: WSH_DEBUG_SV.log(l_module_name,'Trip Id ',p_info.trip_id);
3393: WSH_DEBUG_SV.log(l_module_name,'Weight ',p_info.weight);
3394: WSH_DEBUG_SV.log(l_module_name,'Weight UOM ',p_info.weight_uom_code);
3395: WSH_DEBUG_SV.log(l_module_name,'Volume ',p_info.volume);
3396: WSH_DEBUG_SV.log(l_module_name,'Volume UOM ',p_info.volume_uom_code);
3397: WSH_DEBUG_SV.log(l_module_name,'Transit Time ',p_info.transit_time);
3398: WSH_DEBUG_SV.log(l_module_name,'Ship From Id ',p_info.ship_from_location_id);
3399: WSH_DEBUG_SV.log(l_module_name,'Ship To Id ',p_info.ship_to_location_id);
3392: WSH_DEBUG_SV.log(l_module_name,'Trip Id ',p_info.trip_id);
3393: WSH_DEBUG_SV.log(l_module_name,'Weight ',p_info.weight);
3394: WSH_DEBUG_SV.log(l_module_name,'Weight UOM ',p_info.weight_uom_code);
3395: WSH_DEBUG_SV.log(l_module_name,'Volume ',p_info.volume);
3396: WSH_DEBUG_SV.log(l_module_name,'Volume UOM ',p_info.volume_uom_code);
3397: WSH_DEBUG_SV.log(l_module_name,'Transit Time ',p_info.transit_time);
3398: WSH_DEBUG_SV.log(l_module_name,'Ship From Id ',p_info.ship_from_location_id);
3399: WSH_DEBUG_SV.log(l_module_name,'Ship To Id ',p_info.ship_to_location_id);
3400: WSH_DEBUG_SV.log(l_module_name,'Fob Code ',p_info.fob_code);
3393: WSH_DEBUG_SV.log(l_module_name,'Weight ',p_info.weight);
3394: WSH_DEBUG_SV.log(l_module_name,'Weight UOM ',p_info.weight_uom_code);
3395: WSH_DEBUG_SV.log(l_module_name,'Volume ',p_info.volume);
3396: WSH_DEBUG_SV.log(l_module_name,'Volume UOM ',p_info.volume_uom_code);
3397: WSH_DEBUG_SV.log(l_module_name,'Transit Time ',p_info.transit_time);
3398: WSH_DEBUG_SV.log(l_module_name,'Ship From Id ',p_info.ship_from_location_id);
3399: WSH_DEBUG_SV.log(l_module_name,'Ship To Id ',p_info.ship_to_location_id);
3400: WSH_DEBUG_SV.log(l_module_name,'Fob Code ',p_info.fob_code);
3401: END IF;
3394: WSH_DEBUG_SV.log(l_module_name,'Weight UOM ',p_info.weight_uom_code);
3395: WSH_DEBUG_SV.log(l_module_name,'Volume ',p_info.volume);
3396: WSH_DEBUG_SV.log(l_module_name,'Volume UOM ',p_info.volume_uom_code);
3397: WSH_DEBUG_SV.log(l_module_name,'Transit Time ',p_info.transit_time);
3398: WSH_DEBUG_SV.log(l_module_name,'Ship From Id ',p_info.ship_from_location_id);
3399: WSH_DEBUG_SV.log(l_module_name,'Ship To Id ',p_info.ship_to_location_id);
3400: WSH_DEBUG_SV.log(l_module_name,'Fob Code ',p_info.fob_code);
3401: END IF;
3402:
3395: WSH_DEBUG_SV.log(l_module_name,'Volume ',p_info.volume);
3396: WSH_DEBUG_SV.log(l_module_name,'Volume UOM ',p_info.volume_uom_code);
3397: WSH_DEBUG_SV.log(l_module_name,'Transit Time ',p_info.transit_time);
3398: WSH_DEBUG_SV.log(l_module_name,'Ship From Id ',p_info.ship_from_location_id);
3399: WSH_DEBUG_SV.log(l_module_name,'Ship To Id ',p_info.ship_to_location_id);
3400: WSH_DEBUG_SV.log(l_module_name,'Fob Code ',p_info.fob_code);
3401: END IF;
3402:
3403: IF NOT (g_table_initialized) THEN
3396: WSH_DEBUG_SV.log(l_module_name,'Volume UOM ',p_info.volume_uom_code);
3397: WSH_DEBUG_SV.log(l_module_name,'Transit Time ',p_info.transit_time);
3398: WSH_DEBUG_SV.log(l_module_name,'Ship From Id ',p_info.ship_from_location_id);
3399: WSH_DEBUG_SV.log(l_module_name,'Ship To Id ',p_info.ship_to_location_id);
3400: WSH_DEBUG_SV.log(l_module_name,'Fob Code ',p_info.fob_code);
3401: END IF;
3402:
3403: IF NOT (g_table_initialized) THEN
3404:
3404:
3405: initialize_tables(x_return_status => l_return_status);
3406:
3407: IF l_debug_on THEN
3408: WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
3409: END IF;
3410:
3411: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3412: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3420: build_cache( p_group_id => p_info.group_id,
3421: x_return_status => l_return_status);
3422:
3423: IF l_debug_on THEN
3424: WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
3425: END IF;
3426:
3427: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3428: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3444: -- Change weight
3445: IF (l_group_cache_rec.weight_uom_code <> p_info.weight_uom_code) THEN
3446:
3447: IF l_debug_on THEN
3448: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_ACS_RULE_UTIL_PKG.CONV_TO_BASE_UOM');
3449: END IF;
3450: --
3451: l_weight_val:=FTE_ACS_RULE_UTIL_PKG.CONV_TO_BASE_UOM(p_input_value => p_info.weight,
3452: p_from_uom => p_info.weight_uom_code,
3459: l_weight_val := p_info.weight;
3460: END IF;
3461:
3462: IF l_debug_on THEN
3463: WSH_DEBUG_SV.logmsg(l_module_name,'Weight '||l_weight_val||'Weight UOM '||l_group_cache_rec.weight_uom_code);
3464: END IF;
3465:
3466: END IF;
3467:
3471: -- Change volume
3472: IF (l_group_cache_rec.volume_uom_code <> p_info.volume_uom_code) THEN
3473:
3474: IF l_debug_on THEN
3475: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_ACS_RULE_UTIL_PKG.CONV_TO_BASE_UOM');
3476: END IF;
3477: --
3478: l_volume_val:=FTE_ACS_RULE_UTIL_PKG.CONV_TO_BASE_UOM(p_input_value => p_info.volume,
3479: p_from_uom => p_info.volume_uom_code,
3486: l_volume_val := p_info.volume;
3487: END IF;
3488:
3489: IF l_debug_on THEN
3490: WSH_DEBUG_SV.logmsg(l_module_name,'Volume '||l_volume_val||'Volume UOM '||l_group_cache_rec.volume_uom_code);
3491: END IF;
3492: END IF;
3493:
3494: IF (l_attribute_tab.EXISTS(g_fr_reg_idx)) THEN
3503: x_postal_zone_tab => from_postal_zone_tab,
3504: x_return_status => l_return_status);
3505:
3506: IF l_debug_on THEN
3507: WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
3508: END IF;
3509:
3510: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3511: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3518:
3519: IF (from_all_region_tab.count = 0 ) THEN
3520:
3521: IF l_debug_on THEN
3522: WSH_DEBUG_SV.logmsg(l_module_name,'No From Region Associated with the entity');
3523: END IF;
3524:
3525: l_priority_tab := l_priority_tab MULTISET EXCEPT g_from_region_priority;
3526: l_sort_flag := TRUE;
3539:
3540: IF (from_postal_zone_tab.count = 0) THEN
3541:
3542: IF l_debug_on THEN
3543: WSH_DEBUG_SV.logmsg(l_module_name,'No From Postal Zone Associated with the entity');
3544: END IF;
3545:
3546: l_priority_tab := l_priority_tab MULTISET EXCEPT g_from_pzone_priority;
3547: l_sort_flag := TRUE;
3558: x_postal_zone_tab => to_postal_zone_tab,
3559: x_return_status => l_return_status);
3560:
3561: IF l_debug_on THEN
3562: WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
3563: END IF;
3564:
3565: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3566: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3575:
3576: IF (to_all_region_tab.count = 0 ) THEN
3577:
3578: IF l_debug_on THEN
3579: WSH_DEBUG_SV.logmsg(l_module_name,'No To Region Associated with the entity');
3580: END IF;
3581: l_priority_tab := l_priority_tab MULTISET EXCEPT g_to_region_priority;
3582: l_sort_flag := TRUE;
3583: END IF;
3584:
3585: IF (to_postal_zone_tab.count = 0) THEN
3586:
3587: IF l_debug_on THEN
3588: WSH_DEBUG_SV.logmsg(l_module_name,'No To Postal Zone Associated with the entity');
3589: END IF;
3590: l_priority_tab := l_priority_tab MULTISET EXCEPT g_to_pzone_priority;
3591: l_sort_flag := TRUE;
3592: END IF;
3599: x_postal_code => from_postal_code,
3600: x_return_status => l_return_status);
3601:
3602: IF l_debug_on THEN
3603: WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
3604: END IF;
3605:
3606: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3607: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3632: x_postal_code => to_postal_code,
3633: x_return_status => l_return_status);
3634:
3635: IF l_debug_on THEN
3636: WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
3637: END IF;
3638:
3639: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3640: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3648: sort_priority_tab( p_priority_tab => l_priority_tab,
3649: x_return_status => l_return_status);
3650:
3651: IF l_debug_on THEN
3652: WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
3653: END IF;
3654:
3655: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3656: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3665: LOOP
3666: priority := l_priority_tab(p_itr);
3667:
3668: IF l_debug_on THEN
3669: WSH_DEBUG_SV.log(l_module_name,'Current Priority ',priority);
3670: END IF;
3671:
3672:
3673: IF (priority = 1 ) THEN
3858:
3859: IF (from_tab.COUNT<>0 AND to_tab.COUNT<>0) THEN
3860:
3861: IF l_debug_on THEN
3862: WSH_DEBUG_SV.log(l_module_name,'Calling Find Rule for key for Priority',priority);
3863: END IF;
3864:
3865: -- Loop over the tables here only.
3866:
3902: x_rule_id => l_rule_id,
3903: x_return_status => l_return_status);
3904:
3905: IF l_debug_on THEN
3906: WSH_DEBUG_SV.log(l_module_name,'l_rule_id ', l_rule_id);
3907: WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
3908: END IF;
3909:
3910: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3903: x_return_status => l_return_status);
3904:
3905: IF l_debug_on THEN
3906: WSH_DEBUG_SV.log(l_module_name,'l_rule_id ', l_rule_id);
3907: WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
3908: END IF;
3909:
3910: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3911: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3917:
3918: x_rule_id := l_rule_id ;
3919:
3920: IF l_debug_on THEN
3921: WSH_DEBUG_SV.log(l_module_name,'x_rule_id',x_rule_id);
3922: WSH_DEBUG_SV.POP (l_module_name);
3923: END IF;
3924: RETURN;
3925: END IF;
3918: x_rule_id := l_rule_id ;
3919:
3920: IF l_debug_on THEN
3921: WSH_DEBUG_SV.log(l_module_name,'x_rule_id',x_rule_id);
3922: WSH_DEBUG_SV.POP (l_module_name);
3923: END IF;
3924: RETURN;
3925: END IF;
3926: END IF;
3947: -- If we reach here then no rule was found for the delivery
3948: x_rule_id := g_rule_not_found;
3949:
3950: IF l_debug_on THEN
3951: WSH_DEBUG_SV.POP (l_module_name);
3952: END IF;
3953:
3954: EXCEPTION
3955:
3959: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3960: WSH_UTIL_CORE.add_message(x_return_status);
3961:
3962: IF l_debug_on THEN
3963: WSH_DEBUG_SV.pop(l_module_name,'Volume UOM Conversion Error');
3964: END IF;
3965:
3966: WHEN FTE_CS_WGHT_CONV_ERR THEN
3967:
3969: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3970: WSH_UTIL_CORE.add_message(x_return_status);
3971:
3972: IF l_debug_on THEN
3973: WSH_DEBUG_SV.pop(l_module_name,'Volume UOM Conversion Error');
3974: END IF;
3975:
3976: WHEN others THEN
3977:
3978: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.get_matching_rule');
3979: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3980:
3981: IF l_debug_on THEN
3982: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3983: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3984: END IF;
3985: --
3986: END get_matching_rule;
3979: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3980:
3981: IF l_debug_on THEN
3982: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3983: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3984: END IF;
3985: --
3986: END get_matching_rule;
3987:
4034:
4035: INVALID_RESULT_ATTRIBUTE EXCEPTION;
4036: NO_RESULTS_FOR_RULE EXCEPTION;
4037:
4038: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
4039: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'build_rule_result_cache';
4040:
4041: BEGIN
4042:
4046: --
4047: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4048:
4049: IF l_debug_on THEN
4050: WSH_DEBUG_SV.push(l_module_name);
4051: WSH_DEBUG_SV.log(l_module_name,'p_rule_id',p_rule_id);
4052: END IF;
4053:
4054:
4047: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4048:
4049: IF l_debug_on THEN
4050: WSH_DEBUG_SV.push(l_module_name);
4051: WSH_DEBUG_SV.log(l_module_name,'p_rule_id',p_rule_id);
4052: END IF;
4053:
4054:
4055: OPEN c_get_result_id;
4062: LOOP
4063: l_result_id := l_result_id_tab(itr);
4064:
4065: IF l_debug_on THEN
4066: WSH_DEBUG_SV.log(l_module_name,'Result id to be queried',l_result_id);
4067: END IF;
4068:
4069: IF NOT(g_result_attribute_cache.EXISTS(l_result_id)) THEN
4070:
4165: RAISE NO_RESULTS_FOR_RULE;
4166: END IF;
4167:
4168: IF l_debug_on THEN
4169: WSH_DEBUG_SV.POP(l_module_name);
4170: END IF;
4171:
4172:
4173: EXCEPTION
4174: WHEN INVALID_RESULT_ATTRIBUTE THEN
4175: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4176: --
4177: IF l_debug_on THEN
4178: WSH_DEBUG_SV.pop(l_module_name,'Invalid Attribute associated with Rule'||p_rule_id);
4179: END IF;
4180:
4181: WHEN NO_RESULTS_FOR_RULE THEN
4182:
4182:
4183: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4184: --
4185: IF l_debug_on THEN
4186: WSH_DEBUG_SV.pop(l_module_name,'No Results Associated with Rule'||p_rule_id);
4187: END IF;
4188:
4189: WHEN OTHERS THEN
4190: --
4192: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4193: --
4194:
4195: IF l_debug_on THEN
4196: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4197: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4198: END IF;
4199:
4200: END build_rule_result_cache;
4193: --
4194:
4195: IF l_debug_on THEN
4196: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4197: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4198: END IF;
4199:
4200: END build_rule_result_cache;
4201:
4228: l_cnt NUMBER := 0;
4229: l_return_status VARCHAR2(1);
4230: l_itr NUMBER;
4231:
4232: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
4233: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'get_results_for_rule';
4234:
4235: NO_RESULTS_FOR_RULE EXCEPTION;
4236:
4238:
4239: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4240:
4241: IF l_debug_on THEN
4242: WSH_DEBUG_SV.push(l_module_name);
4243: WSH_DEBUG_SV.log(l_module_name,'p_rule_id',p_rule_id);
4244: END IF;
4245:
4246: IF NOT(g_rule_result_cache.EXISTS(p_rule_id)) THEN
4239: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4240:
4241: IF l_debug_on THEN
4242: WSH_DEBUG_SV.push(l_module_name);
4243: WSH_DEBUG_SV.log(l_module_name,'p_rule_id',p_rule_id);
4244: END IF;
4245:
4246: IF NOT(g_rule_result_cache.EXISTS(p_rule_id)) THEN
4247:
4248: BUILD_RULE_RESULT_CACHE( p_rule_id => p_rule_id,
4249: x_return_status => l_return_status);
4250:
4251: IF l_debug_on THEN
4252: WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
4253: END IF;
4254:
4255: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4256: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
4265:
4266: IF (l_tab.COUNT>0) THEN
4267: itr := l_tab.FIRST;
4268: LOOP
4269: WSH_DEBUG_SV.log(l_module_name,'Result id',l_tab(itr));
4270: EXIT WHEN itr = l_tab.LAST;
4271: itr := l_tab.NEXT(itr);
4272: END LOOP;
4273: END IF;
4290: l_itr := x_result_tab.FIRST;
4291:
4292: IF (l_itr IS NOT NULL) THEN
4293: LOOP
4294: WSH_DEBUG_SV.logmsg(l_module_name,'Output Record for result');
4295: WSH_DEBUG_SV.logmsg(l_module_name,'result_type '||x_result_tab(l_itr).result_type);
4296: WSH_DEBUG_SV.logmsg(l_module_name,'rank '||x_result_tab(l_itr).rank);
4297: WSH_DEBUG_SV.logmsg(l_module_name,'leg_destination '||x_result_tab(l_itr).leg_destination);
4298: WSH_DEBUG_SV.logmsg(l_module_name,'leg_sequence '||x_result_tab(l_itr).leg_sequence);
4291:
4292: IF (l_itr IS NOT NULL) THEN
4293: LOOP
4294: WSH_DEBUG_SV.logmsg(l_module_name,'Output Record for result');
4295: WSH_DEBUG_SV.logmsg(l_module_name,'result_type '||x_result_tab(l_itr).result_type);
4296: WSH_DEBUG_SV.logmsg(l_module_name,'rank '||x_result_tab(l_itr).rank);
4297: WSH_DEBUG_SV.logmsg(l_module_name,'leg_destination '||x_result_tab(l_itr).leg_destination);
4298: WSH_DEBUG_SV.logmsg(l_module_name,'leg_sequence '||x_result_tab(l_itr).leg_sequence);
4299: WSH_DEBUG_SV.logmsg(l_module_name,'carrier_id '||x_result_tab(l_itr).carrier_id);
4292: IF (l_itr IS NOT NULL) THEN
4293: LOOP
4294: WSH_DEBUG_SV.logmsg(l_module_name,'Output Record for result');
4295: WSH_DEBUG_SV.logmsg(l_module_name,'result_type '||x_result_tab(l_itr).result_type);
4296: WSH_DEBUG_SV.logmsg(l_module_name,'rank '||x_result_tab(l_itr).rank);
4297: WSH_DEBUG_SV.logmsg(l_module_name,'leg_destination '||x_result_tab(l_itr).leg_destination);
4298: WSH_DEBUG_SV.logmsg(l_module_name,'leg_sequence '||x_result_tab(l_itr).leg_sequence);
4299: WSH_DEBUG_SV.logmsg(l_module_name,'carrier_id '||x_result_tab(l_itr).carrier_id);
4300: WSH_DEBUG_SV.logmsg(l_module_name,'mode_of_transport '||x_result_tab(l_itr).mode_of_transport);
4293: LOOP
4294: WSH_DEBUG_SV.logmsg(l_module_name,'Output Record for result');
4295: WSH_DEBUG_SV.logmsg(l_module_name,'result_type '||x_result_tab(l_itr).result_type);
4296: WSH_DEBUG_SV.logmsg(l_module_name,'rank '||x_result_tab(l_itr).rank);
4297: WSH_DEBUG_SV.logmsg(l_module_name,'leg_destination '||x_result_tab(l_itr).leg_destination);
4298: WSH_DEBUG_SV.logmsg(l_module_name,'leg_sequence '||x_result_tab(l_itr).leg_sequence);
4299: WSH_DEBUG_SV.logmsg(l_module_name,'carrier_id '||x_result_tab(l_itr).carrier_id);
4300: WSH_DEBUG_SV.logmsg(l_module_name,'mode_of_transport '||x_result_tab(l_itr).mode_of_transport);
4301: WSH_DEBUG_SV.logmsg(l_module_name,'service_level '||x_result_tab(l_itr).service_level);
4294: WSH_DEBUG_SV.logmsg(l_module_name,'Output Record for result');
4295: WSH_DEBUG_SV.logmsg(l_module_name,'result_type '||x_result_tab(l_itr).result_type);
4296: WSH_DEBUG_SV.logmsg(l_module_name,'rank '||x_result_tab(l_itr).rank);
4297: WSH_DEBUG_SV.logmsg(l_module_name,'leg_destination '||x_result_tab(l_itr).leg_destination);
4298: WSH_DEBUG_SV.logmsg(l_module_name,'leg_sequence '||x_result_tab(l_itr).leg_sequence);
4299: WSH_DEBUG_SV.logmsg(l_module_name,'carrier_id '||x_result_tab(l_itr).carrier_id);
4300: WSH_DEBUG_SV.logmsg(l_module_name,'mode_of_transport '||x_result_tab(l_itr).mode_of_transport);
4301: WSH_DEBUG_SV.logmsg(l_module_name,'service_level '||x_result_tab(l_itr).service_level);
4302: WSH_DEBUG_SV.logmsg(l_module_name,'freight_terms_code '||x_result_tab(l_itr).freight_terms_code);
4295: WSH_DEBUG_SV.logmsg(l_module_name,'result_type '||x_result_tab(l_itr).result_type);
4296: WSH_DEBUG_SV.logmsg(l_module_name,'rank '||x_result_tab(l_itr).rank);
4297: WSH_DEBUG_SV.logmsg(l_module_name,'leg_destination '||x_result_tab(l_itr).leg_destination);
4298: WSH_DEBUG_SV.logmsg(l_module_name,'leg_sequence '||x_result_tab(l_itr).leg_sequence);
4299: WSH_DEBUG_SV.logmsg(l_module_name,'carrier_id '||x_result_tab(l_itr).carrier_id);
4300: WSH_DEBUG_SV.logmsg(l_module_name,'mode_of_transport '||x_result_tab(l_itr).mode_of_transport);
4301: WSH_DEBUG_SV.logmsg(l_module_name,'service_level '||x_result_tab(l_itr).service_level);
4302: WSH_DEBUG_SV.logmsg(l_module_name,'freight_terms_code '||x_result_tab(l_itr).freight_terms_code);
4303: WSH_DEBUG_SV.logmsg(l_module_name,'consignee_carrier_ac_no '||x_result_tab(l_itr).consignee_carrier_ac_no);
4296: WSH_DEBUG_SV.logmsg(l_module_name,'rank '||x_result_tab(l_itr).rank);
4297: WSH_DEBUG_SV.logmsg(l_module_name,'leg_destination '||x_result_tab(l_itr).leg_destination);
4298: WSH_DEBUG_SV.logmsg(l_module_name,'leg_sequence '||x_result_tab(l_itr).leg_sequence);
4299: WSH_DEBUG_SV.logmsg(l_module_name,'carrier_id '||x_result_tab(l_itr).carrier_id);
4300: WSH_DEBUG_SV.logmsg(l_module_name,'mode_of_transport '||x_result_tab(l_itr).mode_of_transport);
4301: WSH_DEBUG_SV.logmsg(l_module_name,'service_level '||x_result_tab(l_itr).service_level);
4302: WSH_DEBUG_SV.logmsg(l_module_name,'freight_terms_code '||x_result_tab(l_itr).freight_terms_code);
4303: WSH_DEBUG_SV.logmsg(l_module_name,'consignee_carrier_ac_no '||x_result_tab(l_itr).consignee_carrier_ac_no);
4304: WSH_DEBUG_SV.logmsg(l_module_name,'result_level '||x_result_tab(l_itr).result_level);
4297: WSH_DEBUG_SV.logmsg(l_module_name,'leg_destination '||x_result_tab(l_itr).leg_destination);
4298: WSH_DEBUG_SV.logmsg(l_module_name,'leg_sequence '||x_result_tab(l_itr).leg_sequence);
4299: WSH_DEBUG_SV.logmsg(l_module_name,'carrier_id '||x_result_tab(l_itr).carrier_id);
4300: WSH_DEBUG_SV.logmsg(l_module_name,'mode_of_transport '||x_result_tab(l_itr).mode_of_transport);
4301: WSH_DEBUG_SV.logmsg(l_module_name,'service_level '||x_result_tab(l_itr).service_level);
4302: WSH_DEBUG_SV.logmsg(l_module_name,'freight_terms_code '||x_result_tab(l_itr).freight_terms_code);
4303: WSH_DEBUG_SV.logmsg(l_module_name,'consignee_carrier_ac_no '||x_result_tab(l_itr).consignee_carrier_ac_no);
4304: WSH_DEBUG_SV.logmsg(l_module_name,'result_level '||x_result_tab(l_itr).result_level);
4305:
4298: WSH_DEBUG_SV.logmsg(l_module_name,'leg_sequence '||x_result_tab(l_itr).leg_sequence);
4299: WSH_DEBUG_SV.logmsg(l_module_name,'carrier_id '||x_result_tab(l_itr).carrier_id);
4300: WSH_DEBUG_SV.logmsg(l_module_name,'mode_of_transport '||x_result_tab(l_itr).mode_of_transport);
4301: WSH_DEBUG_SV.logmsg(l_module_name,'service_level '||x_result_tab(l_itr).service_level);
4302: WSH_DEBUG_SV.logmsg(l_module_name,'freight_terms_code '||x_result_tab(l_itr).freight_terms_code);
4303: WSH_DEBUG_SV.logmsg(l_module_name,'consignee_carrier_ac_no '||x_result_tab(l_itr).consignee_carrier_ac_no);
4304: WSH_DEBUG_SV.logmsg(l_module_name,'result_level '||x_result_tab(l_itr).result_level);
4305:
4306: EXIT WHEN l_itr = x_result_tab.LAST;
4299: WSH_DEBUG_SV.logmsg(l_module_name,'carrier_id '||x_result_tab(l_itr).carrier_id);
4300: WSH_DEBUG_SV.logmsg(l_module_name,'mode_of_transport '||x_result_tab(l_itr).mode_of_transport);
4301: WSH_DEBUG_SV.logmsg(l_module_name,'service_level '||x_result_tab(l_itr).service_level);
4302: WSH_DEBUG_SV.logmsg(l_module_name,'freight_terms_code '||x_result_tab(l_itr).freight_terms_code);
4303: WSH_DEBUG_SV.logmsg(l_module_name,'consignee_carrier_ac_no '||x_result_tab(l_itr).consignee_carrier_ac_no);
4304: WSH_DEBUG_SV.logmsg(l_module_name,'result_level '||x_result_tab(l_itr).result_level);
4305:
4306: EXIT WHEN l_itr = x_result_tab.LAST;
4307: l_itr := x_result_tab.NEXT(l_itr);
4300: WSH_DEBUG_SV.logmsg(l_module_name,'mode_of_transport '||x_result_tab(l_itr).mode_of_transport);
4301: WSH_DEBUG_SV.logmsg(l_module_name,'service_level '||x_result_tab(l_itr).service_level);
4302: WSH_DEBUG_SV.logmsg(l_module_name,'freight_terms_code '||x_result_tab(l_itr).freight_terms_code);
4303: WSH_DEBUG_SV.logmsg(l_module_name,'consignee_carrier_ac_no '||x_result_tab(l_itr).consignee_carrier_ac_no);
4304: WSH_DEBUG_SV.logmsg(l_module_name,'result_level '||x_result_tab(l_itr).result_level);
4305:
4306: EXIT WHEN l_itr = x_result_tab.LAST;
4307: l_itr := x_result_tab.NEXT(l_itr);
4308: END LOOP;
4306: EXIT WHEN l_itr = x_result_tab.LAST;
4307: l_itr := x_result_tab.NEXT(l_itr);
4308: END LOOP;
4309: END IF;
4310: WSH_DEBUG_SV.POP (l_module_name);
4311: END IF;
4312:
4313: EXCEPTION
4314:
4316:
4317: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4318: --
4319: IF l_debug_on THEN
4320: WSH_DEBUG_SV.pop(l_module_name,'No Results Associated with Rule'||p_rule_id);
4321: END IF;
4322:
4323: WHEN OTHERS THEN
4324:
4325: WSH_UTIL_CORE.default_handler('FTE_ACS_CACHE_PKG.GET_RESULTS_FOR_RULE');
4326: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4327: --
4328: IF l_debug_on THEN
4329: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4330: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4331: END IF;
4332:
4333: END get_results_for_rule;
4326: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4327: --
4328: IF l_debug_on THEN
4329: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4330: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4331: END IF;
4332:
4333: END get_results_for_rule;
4334: