[Home] [Help]
140: TYPE cur_sql_type IS REF CURSOR;
141: cv_sql cur_sql_type;
142:
143: BEGIN
144: -- FND Logging for debug purpose
145: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
146: hz_utility_v2pub.debug
147: (p_message => 'Begin procedure validate_input_values_proc(+)',
148: p_prefix => l_debug_prefix,
141: cv_sql cur_sql_type;
142:
143: BEGIN
144: -- FND Logging for debug purpose
145: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
146: hz_utility_v2pub.debug
147: (p_message => 'Begin procedure validate_input_values_proc(+)',
148: p_prefix => l_debug_prefix,
149: p_msg_level => fnd_log.level_procedure,
145: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
146: hz_utility_v2pub.debug
147: (p_message => 'Begin procedure validate_input_values_proc(+)',
148: p_prefix => l_debug_prefix,
149: p_msg_level => fnd_log.level_procedure,
150: p_module_prefix => l_module_prefix,
151: p_module => l_module
152: );
153: END IF;
163: CLOSE c_geo_id;
164:
165: IF (l_country_geo_id IS NOT NULL) THEN
166:
167: -- FND Logging for debug purpose
168: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
169: hz_utility_v2pub.debug
170: (p_message => 'Fetched country geography id. It is:'||l_country_geo_id,
171: p_prefix => l_debug_prefix,
164:
165: IF (l_country_geo_id IS NOT NULL) THEN
166:
167: -- FND Logging for debug purpose
168: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
169: hz_utility_v2pub.debug
170: (p_message => 'Fetched country geography id. It is:'||l_country_geo_id,
171: p_prefix => l_debug_prefix,
172: p_msg_level => fnd_log.level_statement,
168: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
169: hz_utility_v2pub.debug
170: (p_message => 'Fetched country geography id. It is:'||l_country_geo_id,
171: p_prefix => l_debug_prefix,
172: p_msg_level => fnd_log.level_statement,
173: p_module_prefix => l_module_prefix,
174: p_module => l_module
175: );
176: END IF;
436: --ns_debug.put_line('Record Count Flag ='||l_rec_count_flag);
437: --ns_debug.put_line('Level ='||l_level);
438: --ns_debug.put_line('Success Level='||l_success_geo_level);
439:
440: -- FND Logging for debug purpose
441: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
442: hz_utility_v2pub.debug
443: (p_message => 'Record Count Flag is:'||l_rec_count_flag||
444: '. Level is:'||l_level||'. Success Geo Level is:'||l_success_geo_level,
437: --ns_debug.put_line('Level ='||l_level);
438: --ns_debug.put_line('Success Level='||l_success_geo_level);
439:
440: -- FND Logging for debug purpose
441: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
442: hz_utility_v2pub.debug
443: (p_message => 'Record Count Flag is:'||l_rec_count_flag||
444: '. Level is:'||l_level||'. Success Geo Level is:'||l_success_geo_level,
445: p_prefix => l_debug_prefix,
442: hz_utility_v2pub.debug
443: (p_message => 'Record Count Flag is:'||l_rec_count_flag||
444: '. Level is:'||l_level||'. Success Geo Level is:'||l_success_geo_level,
445: p_prefix => l_debug_prefix,
446: p_msg_level => fnd_log.level_statement,
447: p_module_prefix => l_module_prefix,
448: p_module => l_module
449: );
450: END IF;
455: -- If we have unique record till user entered value, tell user values from next level
456: IF (l_success_rec_count = '1') THEN
457: l_not_validated_geo_type := get_geo_type_list(l_geo_struct_tbl, l_success_geo_level+1, 'USAGE');
458:
459: -- FND Logging for debug purpose
460: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
461: hz_utility_v2pub.debug
462: (p_message => 'For passed in values we have 1 record at level:'||l_success_geo_level,
463: p_prefix => l_debug_prefix,
456: IF (l_success_rec_count = '1') THEN
457: l_not_validated_geo_type := get_geo_type_list(l_geo_struct_tbl, l_success_geo_level+1, 'USAGE');
458:
459: -- FND Logging for debug purpose
460: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
461: hz_utility_v2pub.debug
462: (p_message => 'For passed in values we have 1 record at level:'||l_success_geo_level,
463: p_prefix => l_debug_prefix,
464: p_msg_level => fnd_log.level_statement,
460: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
461: hz_utility_v2pub.debug
462: (p_message => 'For passed in values we have 1 record at level:'||l_success_geo_level,
463: p_prefix => l_debug_prefix,
464: p_msg_level => fnd_log.level_statement,
465: p_module_prefix => l_module_prefix,
466: p_module => l_module
467: );
468: END IF;
482: IF (l_not_validated_geo_type IS NULL) THEN
483: l_not_validated_geo_type := get_geo_type_list(l_geo_struct_tbl, l_success_geo_level, 'USAGE');
484: END IF;
485:
486: -- FND Logging for debug purpose
487: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
488: hz_utility_v2pub.debug
489: (p_message => 'For passed in values we have multiple records at level:'||l_success_geo_level||
490: '. Geo Type not validated for msg:'||l_not_validated_geo_type,
483: l_not_validated_geo_type := get_geo_type_list(l_geo_struct_tbl, l_success_geo_level, 'USAGE');
484: END IF;
485:
486: -- FND Logging for debug purpose
487: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
488: hz_utility_v2pub.debug
489: (p_message => 'For passed in values we have multiple records at level:'||l_success_geo_level||
490: '. Geo Type not validated for msg:'||l_not_validated_geo_type,
491: p_prefix => l_debug_prefix,
488: hz_utility_v2pub.debug
489: (p_message => 'For passed in values we have multiple records at level:'||l_success_geo_level||
490: '. Geo Type not validated for msg:'||l_not_validated_geo_type,
491: p_prefix => l_debug_prefix,
492: p_msg_level => fnd_log.level_statement,
493: p_module_prefix => l_module_prefix,
494: p_module => l_module
495: );
496: END IF;
496: END IF;
497:
498: END IF;
499:
500: -- FND Logging for debug purpose
501: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
502: hz_utility_v2pub.debug
503: (p_message => 'For Record Count Flag = 0, l_success_rec_count='||l_success_rec_count
504: ||', geo type not validated is :'||l_not_validated_geo_type,
497:
498: END IF;
499:
500: -- FND Logging for debug purpose
501: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
502: hz_utility_v2pub.debug
503: (p_message => 'For Record Count Flag = 0, l_success_rec_count='||l_success_rec_count
504: ||', geo type not validated is :'||l_not_validated_geo_type,
505: p_prefix => l_debug_prefix,
502: hz_utility_v2pub.debug
503: (p_message => 'For Record Count Flag = 0, l_success_rec_count='||l_success_rec_count
504: ||', geo type not validated is :'||l_not_validated_geo_type,
505: p_prefix => l_debug_prefix,
506: p_msg_level => fnd_log.level_statement,
507: p_module_prefix => l_module_prefix,
508: p_module => l_module
509: );
510: END IF;
517: -- i.e. 1. or more rec found but it is not complete for geo usage
518: -- l_rec_count_flag > 0
519: IF (l_not_validated_geo_type IS NULL AND l_level > 0) THEN
520:
521: -- FND Logging for debug purpose
522: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
523: hz_utility_v2pub.debug
524: (p_message => 'Everthing passed is valid, but may not be complete, i.e. 1. rec found but it is not complete for geo usage',
525: p_prefix => l_debug_prefix,
518: -- l_rec_count_flag > 0
519: IF (l_not_validated_geo_type IS NULL AND l_level > 0) THEN
520:
521: -- FND Logging for debug purpose
522: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
523: hz_utility_v2pub.debug
524: (p_message => 'Everthing passed is valid, but may not be complete, i.e. 1. rec found but it is not complete for geo usage',
525: p_prefix => l_debug_prefix,
526: p_msg_level => fnd_log.level_statement,
522: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
523: hz_utility_v2pub.debug
524: (p_message => 'Everthing passed is valid, but may not be complete, i.e. 1. rec found but it is not complete for geo usage',
525: p_prefix => l_debug_prefix,
526: p_msg_level => fnd_log.level_statement,
527: p_module_prefix => l_module_prefix,
528: p_module => l_module
529: );
530: END IF;
535: -- Corner case. Should not happen.
536: IF (l_success_geo_level >= lx_max_usage_element_col_value) THEN
537: l_not_validated_geo_type := get_missing_input_fields(l_geo_struct_tbl);
538: --ns_debug.put_line('All geo elements passed are valid, use missing elements for geo');
539: -- FND Logging for debug purpose
540: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
541: hz_utility_v2pub.debug
542: (p_message => 'All geo elements passed are valid, try reporting missing elements for geo',
543: p_prefix => l_debug_prefix,
536: IF (l_success_geo_level >= lx_max_usage_element_col_value) THEN
537: l_not_validated_geo_type := get_missing_input_fields(l_geo_struct_tbl);
538: --ns_debug.put_line('All geo elements passed are valid, use missing elements for geo');
539: -- FND Logging for debug purpose
540: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
541: hz_utility_v2pub.debug
542: (p_message => 'All geo elements passed are valid, try reporting missing elements for geo',
543: p_prefix => l_debug_prefix,
544: p_msg_level => fnd_log.level_statement,
540: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
541: hz_utility_v2pub.debug
542: (p_message => 'All geo elements passed are valid, try reporting missing elements for geo',
543: p_prefix => l_debug_prefix,
544: p_msg_level => fnd_log.level_statement,
545: p_module_prefix => l_module_prefix,
546: p_module => l_module
547: );
548: END IF;
565: END IF;
566:
567: l_not_validated_geo_type := get_geo_type_list(l_geo_struct_tbl,l_temp_level,l_temp_usage);
568:
569: -- FND Logging for debug purpose
570: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
571: hz_utility_v2pub.debug
572: (p_message => 'All required input fields were populated, '||
573: 'very tough to figure out wrong components. '||
566:
567: l_not_validated_geo_type := get_geo_type_list(l_geo_struct_tbl,l_temp_level,l_temp_usage);
568:
569: -- FND Logging for debug purpose
570: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
571: hz_utility_v2pub.debug
572: (p_message => 'All required input fields were populated, '||
573: 'very tough to figure out wrong components. '||
574: 'Report invalid comp for which multiple rec found for : '||
573: 'very tough to figure out wrong components. '||
574: 'Report invalid comp for which multiple rec found for : '||
575: l_temp_usage||' : '||l_not_validated_geo_type,
576: p_prefix => l_debug_prefix,
577: p_msg_level => fnd_log.level_statement,
578: p_module_prefix => l_module_prefix,
579: p_module => l_module
580: );
581: END IF;
590: END IF;
591:
592: l_not_validated_geo_type := get_geo_type_list(l_geo_struct_tbl,l_temp_level,'USAGE');
593:
594: -- FND Logging for debug purpose
595: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
596: hz_utility_v2pub.debug
597: (p_message => 'All required geo elements are not passed. Start the list from '||
598: 'next level :'||l_not_validated_geo_type,
591:
592: l_not_validated_geo_type := get_geo_type_list(l_geo_struct_tbl,l_temp_level,'USAGE');
593:
594: -- FND Logging for debug purpose
595: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
596: hz_utility_v2pub.debug
597: (p_message => 'All required geo elements are not passed. Start the list from '||
598: 'next level :'||l_not_validated_geo_type,
599: p_prefix => l_debug_prefix,
596: hz_utility_v2pub.debug
597: (p_message => 'All required geo elements are not passed. Start the list from '||
598: 'next level :'||l_not_validated_geo_type,
599: p_prefix => l_debug_prefix,
600: p_msg_level => fnd_log.level_statement,
601: p_module_prefix => l_module_prefix,
602: p_module => l_module
603: );
604: END IF;
604: END IF;
605:
606: END IF;
607:
608: -- FND Logging for debug purpose
609: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
610: hz_utility_v2pub.debug
611: (p_message => 'Everthing passed is valid, but not complete. '||
612: 'Following geo type not validated :'||l_not_validated_geo_type,
605:
606: END IF;
607:
608: -- FND Logging for debug purpose
609: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
610: hz_utility_v2pub.debug
611: (p_message => 'Everthing passed is valid, but not complete. '||
612: 'Following geo type not validated :'||l_not_validated_geo_type,
613: p_prefix => l_debug_prefix,
610: hz_utility_v2pub.debug
611: (p_message => 'Everthing passed is valid, but not complete. '||
612: 'Following geo type not validated :'||l_not_validated_geo_type,
613: p_prefix => l_debug_prefix,
614: p_msg_level => fnd_log.level_statement,
615: p_module_prefix => l_module_prefix,
616: p_module => l_module
617: );
618: END IF;
627: END IF; -- end of geo structure tbl > 0 count
628:
629: END IF; -- country found check
630:
631: -- FND Logging for debug purpose
632: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
633: hz_utility_v2pub.debug
634: (p_message => 'End procedure validate_input_values_proc(-)',
635: p_prefix => l_debug_prefix,
628:
629: END IF; -- country found check
630:
631: -- FND Logging for debug purpose
632: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
633: hz_utility_v2pub.debug
634: (p_message => 'End procedure validate_input_values_proc(-)',
635: p_prefix => l_debug_prefix,
636: p_msg_level => fnd_log.level_procedure,
632: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
633: hz_utility_v2pub.debug
634: (p_message => 'End procedure validate_input_values_proc(-)',
635: p_prefix => l_debug_prefix,
636: p_msg_level => fnd_log.level_procedure,
637: p_module_prefix => l_module_prefix,
638: p_module => l_module
639: );
640: END IF;
640: END IF;
641:
642: EXCEPTION WHEN OTHERS THEN
643: --ns_debug.put_line('Exception occured in validate_input_values_proc:'||SQLERRM);
644: -- FND Logging for debug purpose
645: IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
646: hz_utility_v2pub.debug
647: (p_message => 'Error in validate_input_values_proc:'||
648: SUBSTR(SQLERRM,1,200),
641:
642: EXCEPTION WHEN OTHERS THEN
643: --ns_debug.put_line('Exception occured in validate_input_values_proc:'||SQLERRM);
644: -- FND Logging for debug purpose
645: IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
646: hz_utility_v2pub.debug
647: (p_message => 'Error in validate_input_values_proc:'||
648: SUBSTR(SQLERRM,1,200),
649: p_prefix => l_debug_prefix,
646: hz_utility_v2pub.debug
647: (p_message => 'Error in validate_input_values_proc:'||
648: SUBSTR(SQLERRM,1,200),
649: p_prefix => l_debug_prefix,
650: p_msg_level => fnd_log.level_exception,
651: p_module_prefix => l_module_prefix,
652: p_module => l_module
653: );
654: END IF;
1720: PRAGMA EXCEPTION_INIT(INCONSISTENT_DATATYPE, -932);
1721:
1722: BEGIN
1723:
1724: -- FND Logging for debug purpose
1725: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1726: hz_utility_v2pub.debug
1727: (p_message => 'Trying to fetch Parents for Multi Parent record for Geo id:'||
1728: ll_geo_id||' and geo type '||ll_geo_type ,
1721:
1722: BEGIN
1723:
1724: -- FND Logging for debug purpose
1725: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1726: hz_utility_v2pub.debug
1727: (p_message => 'Trying to fetch Parents for Multi Parent record for Geo id:'||
1728: ll_geo_id||' and geo type '||ll_geo_type ,
1729: p_prefix => l_debug_prefix,
1726: hz_utility_v2pub.debug
1727: (p_message => 'Trying to fetch Parents for Multi Parent record for Geo id:'||
1728: ll_geo_id||' and geo type '||ll_geo_type ,
1729: p_prefix => l_debug_prefix,
1730: p_msg_level => fnd_log.level_statement,
1731: p_module_prefix => l_module_prefix,
1732: p_module => l_module
1733: );
1734: END IF;
1810: -- ns_debug.put_line(l_sql_stmt_mp);
1811: -- ns_debug.put_line(' ======================');
1812:
1813: -----------------debug statements---------------+
1814: -- FND Logging for debug purpose
1815: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1816: hz_utility_v2pub.debug
1817: (p_message => 'Total NULL cols appended:'||l_total_null_cols_mp,
1818: p_prefix => l_debug_prefix,
1811: -- ns_debug.put_line(' ======================');
1812:
1813: -----------------debug statements---------------+
1814: -- FND Logging for debug purpose
1815: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1816: hz_utility_v2pub.debug
1817: (p_message => 'Total NULL cols appended:'||l_total_null_cols_mp,
1818: p_prefix => l_debug_prefix,
1819: p_msg_level => fnd_log.level_statement,
1815: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1816: hz_utility_v2pub.debug
1817: (p_message => 'Total NULL cols appended:'||l_total_null_cols_mp,
1818: p_prefix => l_debug_prefix,
1819: p_msg_level => fnd_log.level_statement,
1820: p_module_prefix => l_module_prefix,
1821: p_module => l_module
1822: );
1823: END IF;
1821: p_module => l_module
1822: );
1823: END IF;
1824:
1825: -- FND Logging for debug purpose
1826: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1827: hz_utility_v2pub.debug
1828: (p_message => 'Multiparent Query :'||l_sql_stmt_mp ,
1829: p_prefix => l_debug_prefix,
1822: );
1823: END IF;
1824:
1825: -- FND Logging for debug purpose
1826: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1827: hz_utility_v2pub.debug
1828: (p_message => 'Multiparent Query :'||l_sql_stmt_mp ,
1829: p_prefix => l_debug_prefix,
1830: p_msg_level => fnd_log.level_statement,
1826: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1827: hz_utility_v2pub.debug
1828: (p_message => 'Multiparent Query :'||l_sql_stmt_mp ,
1829: p_prefix => l_debug_prefix,
1830: p_msg_level => fnd_log.level_statement,
1831: p_module_prefix => l_module_prefix,
1832: p_module => l_module
1833: );
1834: END IF;
2045:
2046: END IF;
2047: END IF;
2048:
2049: -- FND Logging for debug purpose
2050: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2051: hz_utility_v2pub.debug
2052: (p_message => 'Done with Multi Parent Case...' ,
2053: p_prefix => l_debug_prefix,
2046: END IF;
2047: END IF;
2048:
2049: -- FND Logging for debug purpose
2050: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2051: hz_utility_v2pub.debug
2052: (p_message => 'Done with Multi Parent Case...' ,
2053: p_prefix => l_debug_prefix,
2054: p_msg_level => fnd_log.level_statement,
2050: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2051: hz_utility_v2pub.debug
2052: (p_message => 'Done with Multi Parent Case...' ,
2053: p_prefix => l_debug_prefix,
2054: p_msg_level => fnd_log.level_statement,
2055: p_module_prefix => l_module_prefix,
2056: p_module => l_module
2057: );
2058: END IF;
2059:
2060: EXCEPTION
2061: WHEN INCONSISTENT_DATATYPE THEN
2062: NULL;
2063: -- FND Logging for debug purpose
2064: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2065: hz_utility_v2pub.debug
2066: (p_message => 'INCONSISTENT_DATATYPE Exception in multiple_parent_proc.'||
2067: 'This has occured because HZ_HIERARCHY_NODES data is not '||
2060: EXCEPTION
2061: WHEN INCONSISTENT_DATATYPE THEN
2062: NULL;
2063: -- FND Logging for debug purpose
2064: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2065: hz_utility_v2pub.debug
2066: (p_message => 'INCONSISTENT_DATATYPE Exception in multiple_parent_proc.'||
2067: 'This has occured because HZ_HIERARCHY_NODES data is not '||
2068: 'correctly setup for geography id:'||ll_geo_id ,
2066: (p_message => 'INCONSISTENT_DATATYPE Exception in multiple_parent_proc.'||
2067: 'This has occured because HZ_HIERARCHY_NODES data is not '||
2068: 'correctly setup for geography id:'||ll_geo_id ,
2069: p_prefix => l_debug_prefix,
2070: p_msg_level => fnd_log.level_statement,
2071: p_module_prefix => l_module_prefix,
2072: p_module => l_module
2073: );
2074: END IF;
2074: END IF;
2075: -- ns_debug.put_line('hz_hierarchy_nodes data is not correctly setup for geography id:'||ll_geo_id);
2076: WHEN OTHERS THEN
2077: NULL;
2078: -- FND Logging for debug purpose
2079: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2080: hz_utility_v2pub.debug
2081: (p_message => SUBSTR('Exception OTHERS in multiple_parent_proc.'||
2082: 'HZ_HIERARCHY_NODES data is not '||
2075: -- ns_debug.put_line('hz_hierarchy_nodes data is not correctly setup for geography id:'||ll_geo_id);
2076: WHEN OTHERS THEN
2077: NULL;
2078: -- FND Logging for debug purpose
2079: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2080: hz_utility_v2pub.debug
2081: (p_message => SUBSTR('Exception OTHERS in multiple_parent_proc.'||
2082: 'HZ_HIERARCHY_NODES data is not '||
2083: 'correctly setup for geography id:'||ll_geo_id||':'||SQLERRM,1,255),
2081: (p_message => SUBSTR('Exception OTHERS in multiple_parent_proc.'||
2082: 'HZ_HIERARCHY_NODES data is not '||
2083: 'correctly setup for geography id:'||ll_geo_id||':'||SQLERRM,1,255),
2084: p_prefix => l_debug_prefix,
2085: p_msg_level => fnd_log.level_statement,
2086: p_module_prefix => l_module_prefix,
2087: p_module => l_module
2088: );
2089: END IF;
2248: END IF;
2249:
2250: l_bind_counter := TO_NUMBER(pp_bind_table.COUNT);
2251:
2252: -- FND Logging for debug purpose
2253: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2254: hz_utility_v2pub.debug
2255: (p_message => 'Search Type:'||pp_search_type||
2256: ', No. of Bind Values:'||TO_CHAR(l_bind_counter),
2249:
2250: l_bind_counter := TO_NUMBER(pp_bind_table.COUNT);
2251:
2252: -- FND Logging for debug purpose
2253: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2254: hz_utility_v2pub.debug
2255: (p_message => 'Search Type:'||pp_search_type||
2256: ', No. of Bind Values:'||TO_CHAR(l_bind_counter),
2257: p_prefix => l_debug_prefix,
2254: hz_utility_v2pub.debug
2255: (p_message => 'Search Type:'||pp_search_type||
2256: ', No. of Bind Values:'||TO_CHAR(l_bind_counter),
2257: p_prefix => l_debug_prefix,
2258: p_msg_level => fnd_log.level_statement,
2259: p_module_prefix => l_module_prefix,
2260: p_module => l_module
2261: );
2262: END IF;
3709:
3710: END do_match;
3711:
3712: BEGIN
3713: -- FND Logging for debug purpose
3714: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3715: hz_utility_v2pub.debug
3716: (p_message => 'Begin procedure check_exact_match_del_rest(+)',
3717: p_prefix => l_debug_prefix,
3710: END do_match;
3711:
3712: BEGIN
3713: -- FND Logging for debug purpose
3714: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3715: hz_utility_v2pub.debug
3716: (p_message => 'Begin procedure check_exact_match_del_rest(+)',
3717: p_prefix => l_debug_prefix,
3718: p_msg_level => fnd_log.level_procedure,
3714: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3715: hz_utility_v2pub.debug
3716: (p_message => 'Begin procedure check_exact_match_del_rest(+)',
3717: p_prefix => l_debug_prefix,
3718: p_msg_level => fnd_log.level_procedure,
3719: p_module_prefix => l_module_prefix,
3720: p_module => l_module
3721: );
3722: END IF;
3727:
3728: -- check if user has entered all mapped columns.
3729: IF (geo_struct_tbl.COUNT > 0 AND geo_suggest_tbl.COUNT > 0) THEN
3730:
3731: -- FND Logging for debug purpose
3732: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3733: hz_utility_v2pub.debug
3734: (p_message => 'Geo_struct_tbl and geo_suggest_tbl count greater than 0',
3735: p_prefix => l_debug_prefix,
3728: -- check if user has entered all mapped columns.
3729: IF (geo_struct_tbl.COUNT > 0 AND geo_suggest_tbl.COUNT > 0) THEN
3730:
3731: -- FND Logging for debug purpose
3732: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3733: hz_utility_v2pub.debug
3734: (p_message => 'Geo_struct_tbl and geo_suggest_tbl count greater than 0',
3735: p_prefix => l_debug_prefix,
3736: p_msg_level => fnd_log.level_statement,
3732: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3733: hz_utility_v2pub.debug
3734: (p_message => 'Geo_struct_tbl and geo_suggest_tbl count greater than 0',
3735: p_prefix => l_debug_prefix,
3736: p_msg_level => fnd_log.level_statement,
3737: p_module_prefix => l_module_prefix,
3738: p_module => l_module
3739: );
3740: END IF;
3741:
3742: FOR i IN geo_struct_tbl.FIRST..geo_struct_tbl.LAST LOOP
3743: IF (geo_struct_tbl(i).v_param_value IS NULL) THEN
3744:
3745: -- FND Logging for debug purpose
3746: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3747: hz_utility_v2pub.debug
3748: (p_message => 'Input value for '||geo_struct_tbl(i).v_tab_col||' is null '||
3749: 'No use proceeding with input out put match. Exiting procedure.',
3742: FOR i IN geo_struct_tbl.FIRST..geo_struct_tbl.LAST LOOP
3743: IF (geo_struct_tbl(i).v_param_value IS NULL) THEN
3744:
3745: -- FND Logging for debug purpose
3746: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3747: hz_utility_v2pub.debug
3748: (p_message => 'Input value for '||geo_struct_tbl(i).v_tab_col||' is null '||
3749: 'No use proceeding with input out put match. Exiting procedure.',
3750: p_prefix => l_debug_prefix,
3747: hz_utility_v2pub.debug
3748: (p_message => 'Input value for '||geo_struct_tbl(i).v_tab_col||' is null '||
3749: 'No use proceeding with input out put match. Exiting procedure.',
3750: p_prefix => l_debug_prefix,
3751: p_msg_level => fnd_log.level_statement,
3752: p_module_prefix => l_module_prefix,
3753: p_module => l_module
3754: );
3755: END IF;
3758: EXIT;
3759: END IF;
3760: END LOOP;
3761: ELSE
3762: -- FND Logging for debug purpose
3763: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3764: hz_utility_v2pub.debug
3765: (p_message => 'Geo_struct_tbl and geo_suggest_tbl count is less than 0.'||
3766: 'No use proceeding with input out put match. Exiting procedure.',
3759: END IF;
3760: END LOOP;
3761: ELSE
3762: -- FND Logging for debug purpose
3763: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3764: hz_utility_v2pub.debug
3765: (p_message => 'Geo_struct_tbl and geo_suggest_tbl count is less than 0.'||
3766: 'No use proceeding with input out put match. Exiting procedure.',
3767: p_prefix => l_debug_prefix,
3764: hz_utility_v2pub.debug
3765: (p_message => 'Geo_struct_tbl and geo_suggest_tbl count is less than 0.'||
3766: 'No use proceeding with input out put match. Exiting procedure.',
3767: p_prefix => l_debug_prefix,
3768: p_msg_level => fnd_log.level_statement,
3769: p_module_prefix => l_module_prefix,
3770: p_module => l_module
3771: );
3772: END IF;
3776:
3777: -- all parameters are passed
3778: IF (l_check_next_row = 'Y') THEN
3779:
3780: -- FND Logging for debug purpose
3781: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3782: hz_utility_v2pub.debug
3783: (p_message => 'All params passed. Start matching input values with fetched data',
3784: p_prefix => l_debug_prefix,
3777: -- all parameters are passed
3778: IF (l_check_next_row = 'Y') THEN
3779:
3780: -- FND Logging for debug purpose
3781: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3782: hz_utility_v2pub.debug
3783: (p_message => 'All params passed. Start matching input values with fetched data',
3784: p_prefix => l_debug_prefix,
3785: p_msg_level => fnd_log.level_statement,
3781: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3782: hz_utility_v2pub.debug
3783: (p_message => 'All params passed. Start matching input values with fetched data',
3784: p_prefix => l_debug_prefix,
3785: p_msg_level => fnd_log.level_statement,
3786: p_module_prefix => l_module_prefix,
3787: p_module => l_module
3788: );
3789: END IF;
3805: END LOOP; -- geo_struct_tbl loop
3806:
3807: IF (l_exact_row_match_found = 'Y') THEN
3808:
3809: -- FND Logging for debug purpose
3810: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3811: hz_utility_v2pub.debug
3812: (p_message => 'Exact match found. Copying that row into '||
3813: 'final suggest tbl and deleting rest of suggestions',
3806:
3807: IF (l_exact_row_match_found = 'Y') THEN
3808:
3809: -- FND Logging for debug purpose
3810: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3811: hz_utility_v2pub.debug
3812: (p_message => 'Exact match found. Copying that row into '||
3813: 'final suggest tbl and deleting rest of suggestions',
3814: p_prefix => l_debug_prefix,
3811: hz_utility_v2pub.debug
3812: (p_message => 'Exact match found. Copying that row into '||
3813: 'final suggest tbl and deleting rest of suggestions',
3814: p_prefix => l_debug_prefix,
3815: p_msg_level => fnd_log.level_statement,
3816: p_module_prefix => l_module_prefix,
3817: p_module => l_module
3818: );
3819: END IF;
3833: END LOOP; -- geo_suggest_tbl loop
3834:
3835: END IF;
3836:
3837: -- FND Logging for debug purpose
3838: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3839: hz_utility_v2pub.debug
3840: (p_message => 'Completed procedure check_exact_match_del_rest(-)',
3841: p_prefix => l_debug_prefix,
3834:
3835: END IF;
3836:
3837: -- FND Logging for debug purpose
3838: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3839: hz_utility_v2pub.debug
3840: (p_message => 'Completed procedure check_exact_match_del_rest(-)',
3841: p_prefix => l_debug_prefix,
3842: p_msg_level => fnd_log.level_procedure,
3838: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3839: hz_utility_v2pub.debug
3840: (p_message => 'Completed procedure check_exact_match_del_rest(-)',
3841: p_prefix => l_debug_prefix,
3842: p_msg_level => fnd_log.level_procedure,
3843: p_module_prefix => l_module_prefix,
3844: p_module => l_module
3845: );
3846: END IF;
3966: END IF; -- end of geo struct tbl count check
3967:
3968: IF (l_match = 2) THEN
3969:
3970: -- FND Logging for debug purpose
3971: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3972: hz_utility_v2pub.debug
3973: (p_message => 'Input values not exactly same as fetched values for usage',
3974: p_prefix => l_debug_prefix,
3967:
3968: IF (l_match = 2) THEN
3969:
3970: -- FND Logging for debug purpose
3971: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3972: hz_utility_v2pub.debug
3973: (p_message => 'Input values not exactly same as fetched values for usage',
3974: p_prefix => l_debug_prefix,
3975: p_msg_level => fnd_log.level_statement,
3971: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3972: hz_utility_v2pub.debug
3973: (p_message => 'Input values not exactly same as fetched values for usage',
3974: p_prefix => l_debug_prefix,
3975: p_msg_level => fnd_log.level_statement,
3976: p_module_prefix => l_module_prefix,
3977: p_module => l_module
3978: );
3979: END IF;
3980:
3981: RETURN FALSE;
3982: ELSE
3983:
3984: -- FND Logging for debug purpose
3985: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3986: hz_utility_v2pub.debug
3987: (p_message => 'Input values matches fetched values',
3988: p_prefix => l_debug_prefix,
3981: RETURN FALSE;
3982: ELSE
3983:
3984: -- FND Logging for debug purpose
3985: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3986: hz_utility_v2pub.debug
3987: (p_message => 'Input values matches fetched values',
3988: p_prefix => l_debug_prefix,
3989: p_msg_level => fnd_log.level_statement,
3985: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3986: hz_utility_v2pub.debug
3987: (p_message => 'Input values matches fetched values',
3988: p_prefix => l_debug_prefix,
3989: p_msg_level => fnd_log.level_statement,
3990: p_module_prefix => l_module_prefix,
3991: p_module => l_module
3992: );
3993: END IF;
4082: ELSE
4083: l_address_style := pv_address_style;
4084: END IF;
4085:
4086: -- FND Logging for debug purpose
4087: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4088: hz_utility_v2pub.debug
4089: (p_message => 'Address Style passed is :'||l_address_style,
4090: p_prefix => l_debug_prefix,
4083: l_address_style := pv_address_style;
4084: END IF;
4085:
4086: -- FND Logging for debug purpose
4087: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4088: hz_utility_v2pub.debug
4089: (p_message => 'Address Style passed is :'||l_address_style,
4090: p_prefix => l_debug_prefix,
4091: p_msg_level => fnd_log.level_statement,
4087: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4088: hz_utility_v2pub.debug
4089: (p_message => 'Address Style passed is :'||l_address_style,
4090: p_prefix => l_debug_prefix,
4091: p_msg_level => fnd_log.level_statement,
4092: p_module_prefix => l_module_prefix,
4093: p_module => l_module
4094: );
4095: END IF;
4109:
4110: -- if no data found then, try with NULL address style (which is default address style)
4111: IF (geo_struct_tbl.COUNT = 0) THEN
4112:
4113: -- FND Logging for debug purpose
4114: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4115: hz_utility_v2pub.debug
4116: (p_message => 'Either address style is NULL or mapping for passed address style not found.'||
4117: ' Trying with NULL address style.',
4110: -- if no data found then, try with NULL address style (which is default address style)
4111: IF (geo_struct_tbl.COUNT = 0) THEN
4112:
4113: -- FND Logging for debug purpose
4114: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4115: hz_utility_v2pub.debug
4116: (p_message => 'Either address style is NULL or mapping for passed address style not found.'||
4117: ' Trying with NULL address style.',
4118: p_prefix => l_debug_prefix,
4115: hz_utility_v2pub.debug
4116: (p_message => 'Either address style is NULL or mapping for passed address style not found.'||
4117: ' Trying with NULL address style.',
4118: p_prefix => l_debug_prefix,
4119: p_msg_level => fnd_log.level_statement,
4120: p_module_prefix => l_module_prefix,
4121: p_module => l_module
4122: );
4123: END IF;
4159: END LOOP;
4160: END LOOP;
4161: END IF;
4162:
4163: -- FND Logging for debug purpose
4164: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4165: hz_utility_v2pub.debug
4166: (p_message => 'Mapped structure count :'||TO_CHAR(geo_struct_tbl.COUNT),
4167: p_prefix => l_debug_prefix,
4160: END LOOP;
4161: END IF;
4162:
4163: -- FND Logging for debug purpose
4164: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4165: hz_utility_v2pub.debug
4166: (p_message => 'Mapped structure count :'||TO_CHAR(geo_struct_tbl.COUNT),
4167: p_prefix => l_debug_prefix,
4168: p_msg_level => fnd_log.level_statement,
4164: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4165: hz_utility_v2pub.debug
4166: (p_message => 'Mapped structure count :'||TO_CHAR(geo_struct_tbl.COUNT),
4167: p_prefix => l_debug_prefix,
4168: p_msg_level => fnd_log.level_statement,
4169: p_module_prefix => l_module_prefix,
4170: p_module => l_module
4171: );
4172: END IF;
4276: END IF;
4277:
4278: END LOOP;
4279:
4280: -- FND Logging for debug purpose
4281: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4282: hz_utility_v2pub.debug
4283: (p_message => SUBSTR('Mapped Columns:'||l_mapped_column_list,1,255),
4284: p_prefix => l_debug_prefix,
4277:
4278: END LOOP;
4279:
4280: -- FND Logging for debug purpose
4281: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4282: hz_utility_v2pub.debug
4283: (p_message => SUBSTR('Mapped Columns:'||l_mapped_column_list,1,255),
4284: p_prefix => l_debug_prefix,
4285: p_msg_level => fnd_log.level_statement,
4281: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4282: hz_utility_v2pub.debug
4283: (p_message => SUBSTR('Mapped Columns:'||l_mapped_column_list,1,255),
4284: p_prefix => l_debug_prefix,
4285: p_msg_level => fnd_log.level_statement,
4286: p_module_prefix => l_module_prefix,
4287: p_module => l_module
4288: );
4289: END IF;
4287: p_module => l_module
4288: );
4289: END IF;
4290:
4291: -- FND Logging for debug purpose
4292: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4293: hz_utility_v2pub.debug
4294: (p_message => SUBSTR('Mapped Geo Types:'||l_mapped_geo_type_list,1,255),
4295: p_prefix => l_debug_prefix,
4288: );
4289: END IF;
4290:
4291: -- FND Logging for debug purpose
4292: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4293: hz_utility_v2pub.debug
4294: (p_message => SUBSTR('Mapped Geo Types:'||l_mapped_geo_type_list,1,255),
4295: p_prefix => l_debug_prefix,
4296: p_msg_level => fnd_log.level_statement,
4292: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4293: hz_utility_v2pub.debug
4294: (p_message => SUBSTR('Mapped Geo Types:'||l_mapped_geo_type_list,1,255),
4295: p_prefix => l_debug_prefix,
4296: p_msg_level => fnd_log.level_statement,
4297: p_module_prefix => l_module_prefix,
4298: p_module => l_module
4299: );
4300: END IF;
4298: p_module => l_module
4299: );
4300: END IF;
4301:
4302: -- FND Logging for debug purpose
4303: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4304: hz_utility_v2pub.debug
4305: (p_message => SUBSTR('Passed Param Values'||l_passed_param_values,1,255),
4306: p_prefix => l_debug_prefix,
4299: );
4300: END IF;
4301:
4302: -- FND Logging for debug purpose
4303: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4304: hz_utility_v2pub.debug
4305: (p_message => SUBSTR('Passed Param Values'||l_passed_param_values,1,255),
4306: p_prefix => l_debug_prefix,
4307: p_msg_level => fnd_log.level_statement,
4303: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4304: hz_utility_v2pub.debug
4305: (p_message => SUBSTR('Passed Param Values'||l_passed_param_values,1,255),
4306: p_prefix => l_debug_prefix,
4307: p_msg_level => fnd_log.level_statement,
4308: p_module_prefix => l_module_prefix,
4309: p_module => l_module
4310: );
4311: END IF;
4309: p_module => l_module
4310: );
4311: END IF;
4312:
4313: -- FND Logging for debug purpose
4314: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4315: hz_utility_v2pub.debug
4316: (p_message => 'Max Mapped Element Column Value:'||TO_CHAR(l_max_mapped_element_col_value),
4317: p_prefix => l_debug_prefix,
4310: );
4311: END IF;
4312:
4313: -- FND Logging for debug purpose
4314: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4315: hz_utility_v2pub.debug
4316: (p_message => 'Max Mapped Element Column Value:'||TO_CHAR(l_max_mapped_element_col_value),
4317: p_prefix => l_debug_prefix,
4318: p_msg_level => fnd_log.level_statement,
4314: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4315: hz_utility_v2pub.debug
4316: (p_message => 'Max Mapped Element Column Value:'||TO_CHAR(l_max_mapped_element_col_value),
4317: p_prefix => l_debug_prefix,
4318: p_msg_level => fnd_log.level_statement,
4319: p_module_prefix => l_module_prefix,
4320: p_module => l_module
4321: );
4322: END IF;
4320: p_module => l_module
4321: );
4322: END IF;
4323:
4324: -- FND Logging for debug purpose
4325: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4326: hz_utility_v2pub.debug
4327: (p_message => 'Max Geo Usage Element Col Value:'||TO_CHAR(l_max_usage_element_col_value),
4328: p_prefix => l_debug_prefix,
4321: );
4322: END IF;
4323:
4324: -- FND Logging for debug purpose
4325: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4326: hz_utility_v2pub.debug
4327: (p_message => 'Max Geo Usage Element Col Value:'||TO_CHAR(l_max_usage_element_col_value),
4328: p_prefix => l_debug_prefix,
4329: p_msg_level => fnd_log.level_statement,
4325: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4326: hz_utility_v2pub.debug
4327: (p_message => 'Max Geo Usage Element Col Value:'||TO_CHAR(l_max_usage_element_col_value),
4328: p_prefix => l_debug_prefix,
4329: p_msg_level => fnd_log.level_statement,
4330: p_module_prefix => l_module_prefix,
4331: p_module => l_module
4332: );
4333: END IF;
4331: p_module => l_module
4332: );
4333: END IF;
4334:
4335: -- FND Logging for debug purpose
4336: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4337: hz_utility_v2pub.debug
4338: (p_message => 'Max Geo Type for which Geo usage is marked: '||l_lowest_usage_geo_type,
4339: p_prefix => l_debug_prefix,
4332: );
4333: END IF;
4334:
4335: -- FND Logging for debug purpose
4336: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4337: hz_utility_v2pub.debug
4338: (p_message => 'Max Geo Type for which Geo usage is marked: '||l_lowest_usage_geo_type,
4339: p_prefix => l_debug_prefix,
4340: p_msg_level => fnd_log.level_statement,
4336: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4337: hz_utility_v2pub.debug
4338: (p_message => 'Max Geo Type for which Geo usage is marked: '||l_lowest_usage_geo_type,
4339: p_prefix => l_debug_prefix,
4340: p_msg_level => fnd_log.level_statement,
4341: p_module_prefix => l_module_prefix,
4342: p_module => l_module
4343: );
4344: END IF;
4342: p_module => l_module
4343: );
4344: END IF;
4345:
4346: -- FND Logging for debug purpose
4347: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4348: hz_utility_v2pub.debug
4349: (p_message => 'Lowest Geo Type for which Geo usage is marked and value is passed: '||l_last_geo_type_usg_val_psd,
4350: p_prefix => l_debug_prefix,
4343: );
4344: END IF;
4345:
4346: -- FND Logging for debug purpose
4347: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4348: hz_utility_v2pub.debug
4349: (p_message => 'Lowest Geo Type for which Geo usage is marked and value is passed: '||l_last_geo_type_usg_val_psd,
4350: p_prefix => l_debug_prefix,
4351: p_msg_level => fnd_log.level_statement,
4347: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4348: hz_utility_v2pub.debug
4349: (p_message => 'Lowest Geo Type for which Geo usage is marked and value is passed: '||l_last_geo_type_usg_val_psd,
4350: p_prefix => l_debug_prefix,
4351: p_msg_level => fnd_log.level_statement,
4352: p_module_prefix => l_module_prefix,
4353: p_module => l_module
4354: );
4355: END IF;
4545: -- Build Suggestion List for consumer teams. This is done by concatnating
4546: -- all fetched and mapped values of Final Table (geo_suggest_tbl).
4547: -- This will be based on the order of mapping fo components.
4548: ---------------------------------------------------------------------+
4549: -- FND Logging for debug purpose
4550: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4551: hz_utility_v2pub.debug
4552: (p_message => 'Building Suggestion list for Java APIs: build_suggestion_list(+)',
4553: p_prefix => l_debug_prefix,
4546: -- all fetched and mapped values of Final Table (geo_suggest_tbl).
4547: -- This will be based on the order of mapping fo components.
4548: ---------------------------------------------------------------------+
4549: -- FND Logging for debug purpose
4550: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4551: hz_utility_v2pub.debug
4552: (p_message => 'Building Suggestion list for Java APIs: build_suggestion_list(+)',
4553: p_prefix => l_debug_prefix,
4554: p_msg_level => fnd_log.level_procedure,
4550: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4551: hz_utility_v2pub.debug
4552: (p_message => 'Building Suggestion list for Java APIs: build_suggestion_list(+)',
4553: p_prefix => l_debug_prefix,
4554: p_msg_level => fnd_log.level_procedure,
4555: p_module_prefix => l_module_prefix,
4556: p_module => l_module
4557: );
4558: END IF;
4567: END IF;
4568: geo_suggest_tbl(i).suggestion_list := l_suggest_list;
4569: END LOOP;
4570:
4571: -- FND LOGGING FOR DEBUG PURPOSE
4572: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4573: HZ_UTILITY_V2PUB.DEBUG
4574: (P_MESSAGE => 'Begin procedure quick_sort_suggestion_list(+)',
4575: P_PREFIX => L_DEBUG_PREFIX,
4568: geo_suggest_tbl(i).suggestion_list := l_suggest_list;
4569: END LOOP;
4570:
4571: -- FND LOGGING FOR DEBUG PURPOSE
4572: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4573: HZ_UTILITY_V2PUB.DEBUG
4574: (P_MESSAGE => 'Begin procedure quick_sort_suggestion_list(+)',
4575: P_PREFIX => L_DEBUG_PREFIX,
4576: P_MSG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
4572: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4573: HZ_UTILITY_V2PUB.DEBUG
4574: (P_MESSAGE => 'Begin procedure quick_sort_suggestion_list(+)',
4575: P_PREFIX => L_DEBUG_PREFIX,
4576: P_MSG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
4577: P_MODULE_PREFIX => L_MODULE_PREFIX,
4578: P_MODULE => L_MODULE
4579: );
4580: END IF;
4581:
4582: -- do sorting on geo_suggest_tbl based on suggestion_list column
4583: quick_sort_suggestion_list(geo_suggest_tbl.FIRST, geo_suggest_tbl.LAST, geo_suggest_tbl);
4584:
4585: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4586: hz_utility_v2pub.debug
4587: (p_message => 'End procedure quick_sort_suggestion_list(-)',
4588: p_prefix => l_debug_prefix,
4589: p_msg_level => fnd_log.level_procedure,
4585: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4586: hz_utility_v2pub.debug
4587: (p_message => 'End procedure quick_sort_suggestion_list(-)',
4588: p_prefix => l_debug_prefix,
4589: p_msg_level => fnd_log.level_procedure,
4590: p_module_prefix => l_module_prefix,
4591: p_module => l_module
4592: );
4593: END IF;
4598: -- then suggestion list will be null. In that case delete that country row also.
4599: -- Bug 5391120 (Nishant 21-Jul-2006)
4600: IF (geo_suggest_tbl.COUNT = 1) THEN
4601:
4602: -- FND Logging for debug purpose
4603: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4604: hz_utility_v2pub.debug
4605: (p_message => 'Only 1 row in Suggestion list. Its value is:'||geo_suggest_tbl(1).suggestion_list,
4606: p_prefix => l_debug_prefix,
4599: -- Bug 5391120 (Nishant 21-Jul-2006)
4600: IF (geo_suggest_tbl.COUNT = 1) THEN
4601:
4602: -- FND Logging for debug purpose
4603: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4604: hz_utility_v2pub.debug
4605: (p_message => 'Only 1 row in Suggestion list. Its value is:'||geo_suggest_tbl(1).suggestion_list,
4606: p_prefix => l_debug_prefix,
4607: p_msg_level => fnd_log.level_procedure,
4603: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4604: hz_utility_v2pub.debug
4605: (p_message => 'Only 1 row in Suggestion list. Its value is:'||geo_suggest_tbl(1).suggestion_list,
4606: p_prefix => l_debug_prefix,
4607: p_msg_level => fnd_log.level_procedure,
4608: p_module_prefix => l_module_prefix,
4609: p_module => l_module
4610: );
4611: END IF;
4613: IF (geo_suggest_tbl(1).suggestion_list IS NULL OR
4614: LENGTH(LTRIM(RTRIM(geo_suggest_tbl(1).suggestion_list))) = 0) THEN
4615: geo_suggest_tbl.DELETE(1);
4616:
4617: -- FND Logging for debug purpose
4618: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4619: hz_utility_v2pub.debug
4620: (p_message => 'Since value in suggestion list is NULL or of 0 length,'||
4621: 'deleting the row from geo_suggest_tbl',
4614: LENGTH(LTRIM(RTRIM(geo_suggest_tbl(1).suggestion_list))) = 0) THEN
4615: geo_suggest_tbl.DELETE(1);
4616:
4617: -- FND Logging for debug purpose
4618: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4619: hz_utility_v2pub.debug
4620: (p_message => 'Since value in suggestion list is NULL or of 0 length,'||
4621: 'deleting the row from geo_suggest_tbl',
4622: p_prefix => l_debug_prefix,
4619: hz_utility_v2pub.debug
4620: (p_message => 'Since value in suggestion list is NULL or of 0 length,'||
4621: 'deleting the row from geo_suggest_tbl',
4622: p_prefix => l_debug_prefix,
4623: p_msg_level => fnd_log.level_procedure,
4624: p_module_prefix => l_module_prefix,
4625: p_module => l_module
4626: );
4627: END IF;
4628:
4629: END IF;
4630: END IF;
4631:
4632: -- FND Logging for debug purpose
4633: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4634: hz_utility_v2pub.debug
4635: (p_message => 'Completed building Suggestion list for Java APIs: build_suggestion_list(-)',
4636: p_prefix => l_debug_prefix,
4629: END IF;
4630: END IF;
4631:
4632: -- FND Logging for debug purpose
4633: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4634: hz_utility_v2pub.debug
4635: (p_message => 'Completed building Suggestion list for Java APIs: build_suggestion_list(-)',
4636: p_prefix => l_debug_prefix,
4637: p_msg_level => fnd_log.level_procedure,
4633: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4634: hz_utility_v2pub.debug
4635: (p_message => 'Completed building Suggestion list for Java APIs: build_suggestion_list(-)',
4636: p_prefix => l_debug_prefix,
4637: p_msg_level => fnd_log.level_procedure,
4638: p_module_prefix => l_module_prefix,
4639: p_module => l_module
4640: );
4641: END IF;
4694: l_miss_min_geo_type VARCHAR2(200);
4695: l_msg_name VARCHAR2(100);
4696: l_msg_token_name VARCHAR2(100);
4697: BEGIN
4698: -- FND Logging for debug purpose
4699: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4700: hz_utility_v2pub.debug
4701: (p_message => 'BEGIN: process_output_data_proc(+)',
4702: p_prefix => l_debug_prefix,
4695: l_msg_name VARCHAR2(100);
4696: l_msg_token_name VARCHAR2(100);
4697: BEGIN
4698: -- FND Logging for debug purpose
4699: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4700: hz_utility_v2pub.debug
4701: (p_message => 'BEGIN: process_output_data_proc(+)',
4702: p_prefix => l_debug_prefix,
4703: p_msg_level => fnd_log.level_procedure,
4699: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4700: hz_utility_v2pub.debug
4701: (p_message => 'BEGIN: process_output_data_proc(+)',
4702: p_prefix => l_debug_prefix,
4703: p_msg_level => fnd_log.level_procedure,
4704: p_module_prefix => l_module_prefix,
4705: p_module => l_module
4706: );
4707: END IF;
4711: build_suggestion_list;
4712:
4713: IF (l_addr_val_level = 'ERROR') THEN
4714:
4715: -- FND Logging for debug purpose
4716: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4717: hz_utility_v2pub.debug
4718: (p_message => 'Validation Level : ERROR',
4719: p_prefix => l_debug_prefix,
4712:
4713: IF (l_addr_val_level = 'ERROR') THEN
4714:
4715: -- FND Logging for debug purpose
4716: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4717: hz_utility_v2pub.debug
4718: (p_message => 'Validation Level : ERROR',
4719: p_prefix => l_debug_prefix,
4720: p_msg_level => fnd_log.level_statement,
4716: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4717: hz_utility_v2pub.debug
4718: (p_message => 'Validation Level : ERROR',
4719: p_prefix => l_debug_prefix,
4720: p_msg_level => fnd_log.level_statement,
4721: p_module_prefix => l_module_prefix,
4722: p_module => l_module
4723: );
4724: END IF;
4725:
4726: -- If it is 2nd pass and geo validation success, then return ADDRESS_COMPLETE
4727: IF ((HZ_GNR_PVT.G_USER_ATTEMPT_COUNT IS NOT NULL) AND -- this is 2nd attempt
4728: (l_success = 'Y')) THEN
4729: -- FND Logging for debug purpose
4730: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4731: hz_utility_v2pub.debug
4732: (p_message => 'Geo Validated Address is complete, return code=0(ADDRESS_COMPLETE)',
4733: p_prefix => l_debug_prefix,
4726: -- If it is 2nd pass and geo validation success, then return ADDRESS_COMPLETE
4727: IF ((HZ_GNR_PVT.G_USER_ATTEMPT_COUNT IS NOT NULL) AND -- this is 2nd attempt
4728: (l_success = 'Y')) THEN
4729: -- FND Logging for debug purpose
4730: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4731: hz_utility_v2pub.debug
4732: (p_message => 'Geo Validated Address is complete, return code=0(ADDRESS_COMPLETE)',
4733: p_prefix => l_debug_prefix,
4734: p_msg_level => fnd_log.level_statement,
4730: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4731: hz_utility_v2pub.debug
4732: (p_message => 'Geo Validated Address is complete, return code=0(ADDRESS_COMPLETE)',
4733: p_prefix => l_debug_prefix,
4734: p_msg_level => fnd_log.level_statement,
4735: p_module_prefix => l_module_prefix,
4736: p_module => l_module
4737: );
4738: END IF;
4746: ELSE -- do further processing to verify if record is success
4747:
4748: IF (geo_suggest_tbl.COUNT = 0) THEN
4749:
4750: -- FND Logging for debug purpose
4751: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4752:
4753: hz_utility_v2pub.debug
4754: (p_message => 'Records count is 0, return code= -1(ADDRESS_INVALID)',
4747:
4748: IF (geo_suggest_tbl.COUNT = 0) THEN
4749:
4750: -- FND Logging for debug purpose
4751: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4752:
4753: hz_utility_v2pub.debug
4754: (p_message => 'Records count is 0, return code= -1(ADDRESS_INVALID)',
4755: p_prefix => l_debug_prefix,
4752:
4753: hz_utility_v2pub.debug
4754: (p_message => 'Records count is 0, return code= -1(ADDRESS_INVALID)',
4755: p_prefix => l_debug_prefix,
4756: p_msg_level => fnd_log.level_statement,
4757: p_module_prefix => l_module_prefix,
4758: p_module => l_module
4759: );
4760:
4790: IF (geo_suggest_tbl.COUNT =1) THEN
4791: -- check if all input values match all fetched values
4792: IF (do_input_output_match_check) THEN
4793:
4794: -- FND Logging for debug purpose
4795: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4796: hz_utility_v2pub.debug
4797: (p_message => 'Address is complete, return code=0(ADDRESS_COMPLETE)',
4798: p_prefix => l_debug_prefix,
4791: -- check if all input values match all fetched values
4792: IF (do_input_output_match_check) THEN
4793:
4794: -- FND Logging for debug purpose
4795: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4796: hz_utility_v2pub.debug
4797: (p_message => 'Address is complete, return code=0(ADDRESS_COMPLETE)',
4798: p_prefix => l_debug_prefix,
4799: p_msg_level => fnd_log.level_statement,
4795: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4796: hz_utility_v2pub.debug
4797: (p_message => 'Address is complete, return code=0(ADDRESS_COMPLETE)',
4798: p_prefix => l_debug_prefix,
4799: p_msg_level => fnd_log.level_statement,
4800: p_module_prefix => l_module_prefix,
4801: p_module => l_module
4802: );
4803: END IF;
4808: -- address suggestion kickes in initially
4809: HZ_GNR_PVT.G_USER_ATTEMPT_COUNT := NULL;
4810:
4811: ELSE
4812: -- FND Logging for debug purpose
4813: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4814: hz_utility_v2pub.debug
4815: (p_message => 'input output match check failed, return code=1(ADDRESS_VALID)',
4816: p_prefix => l_debug_prefix,
4809: HZ_GNR_PVT.G_USER_ATTEMPT_COUNT := NULL;
4810:
4811: ELSE
4812: -- FND Logging for debug purpose
4813: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4814: hz_utility_v2pub.debug
4815: (p_message => 'input output match check failed, return code=1(ADDRESS_VALID)',
4816: p_prefix => l_debug_prefix,
4817: p_msg_level => fnd_log.level_statement,
4813: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4814: hz_utility_v2pub.debug
4815: (p_message => 'input output match check failed, return code=1(ADDRESS_VALID)',
4816: p_prefix => l_debug_prefix,
4817: p_msg_level => fnd_log.level_statement,
4818: p_module_prefix => l_module_prefix,
4819: p_module => l_module
4820: );
4821: END IF;
4841:
4842: ELSIF -- more than 55
4843: ((geo_suggest_tbl.COUNT > 1) AND (l_geo_data_count > l_max_fetch_count)) THEN
4844:
4845: -- FND Logging for debug purpose
4846: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4847: hz_utility_v2pub.debug
4848: (p_message => 'more than 55 records, return code=1000(TOO_MANY_ROWS)',
4849: p_prefix => l_debug_prefix,
4842: ELSIF -- more than 55
4843: ((geo_suggest_tbl.COUNT > 1) AND (l_geo_data_count > l_max_fetch_count)) THEN
4844:
4845: -- FND Logging for debug purpose
4846: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4847: hz_utility_v2pub.debug
4848: (p_message => 'more than 55 records, return code=1000(TOO_MANY_ROWS)',
4849: p_prefix => l_debug_prefix,
4850: p_msg_level => fnd_log.level_statement,
4846: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4847: hz_utility_v2pub.debug
4848: (p_message => 'more than 55 records, return code=1000(TOO_MANY_ROWS)',
4849: p_prefix => l_debug_prefix,
4850: p_msg_level => fnd_log.level_statement,
4851: p_module_prefix => l_module_prefix,
4852: p_module => l_module
4853: );
4854: END IF;
4875: x_records_count := NULL;
4876:
4877: ELSE -- between 2 and 55
4878:
4879: -- FND Logging for debug purpose
4880: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4881: hz_utility_v2pub.debug
4882: (p_message => 'No. of records between 2 and 55, return code=1(ADDRESS_VALID)',
4883: p_prefix => l_debug_prefix,
4876:
4877: ELSE -- between 2 and 55
4878:
4879: -- FND Logging for debug purpose
4880: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4881: hz_utility_v2pub.debug
4882: (p_message => 'No. of records between 2 and 55, return code=1(ADDRESS_VALID)',
4883: p_prefix => l_debug_prefix,
4884: p_msg_level => fnd_log.level_statement,
4880: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4881: hz_utility_v2pub.debug
4882: (p_message => 'No. of records between 2 and 55, return code=1(ADDRESS_VALID)',
4883: p_prefix => l_debug_prefix,
4884: p_msg_level => fnd_log.level_statement,
4885: p_module_prefix => l_module_prefix,
4886: p_module => l_module
4887: );
4888: END IF;
4910: END IF; -- validation level = ERROR
4911:
4912: IF (l_addr_val_level = 'WARNING') THEN
4913:
4914: -- FND Logging for debug purpose
4915: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4916: hz_utility_v2pub.debug
4917: (p_message => 'Validation Level : WARNING',
4918: p_prefix => l_debug_prefix,
4911:
4912: IF (l_addr_val_level = 'WARNING') THEN
4913:
4914: -- FND Logging for debug purpose
4915: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4916: hz_utility_v2pub.debug
4917: (p_message => 'Validation Level : WARNING',
4918: p_prefix => l_debug_prefix,
4919: p_msg_level => fnd_log.level_statement,
4915: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4916: hz_utility_v2pub.debug
4917: (p_message => 'Validation Level : WARNING',
4918: p_prefix => l_debug_prefix,
4919: p_msg_level => fnd_log.level_statement,
4920: p_module_prefix => l_module_prefix,
4921: p_module => l_module
4922: );
4923: END IF;
4923: END IF;
4924:
4925: IF (geo_suggest_tbl.COUNT = 0) THEN
4926:
4927: -- FND Logging for debug purpose
4928: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4929: hz_utility_v2pub.debug
4930: (p_message => 'No. of records 0, return code=1(ADDRESS_VALID)',
4931: p_prefix => l_debug_prefix,
4924:
4925: IF (geo_suggest_tbl.COUNT = 0) THEN
4926:
4927: -- FND Logging for debug purpose
4928: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4929: hz_utility_v2pub.debug
4930: (p_message => 'No. of records 0, return code=1(ADDRESS_VALID)',
4931: p_prefix => l_debug_prefix,
4932: p_msg_level => fnd_log.level_statement,
4928: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4929: hz_utility_v2pub.debug
4930: (p_message => 'No. of records 0, return code=1(ADDRESS_VALID)',
4931: p_prefix => l_debug_prefix,
4932: p_msg_level => fnd_log.level_statement,
4933: p_module_prefix => l_module_prefix,
4934: p_module => l_module
4935: );
4936: END IF;
4946:
4947: -- check if all input values match all fetched values
4948: IF (do_input_output_match_check) THEN
4949:
4950: -- FND Logging for debug purpose
4951: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4952: hz_utility_v2pub.debug
4953: (p_message => 'No. of records 1, exact match, return code=0(ADDRESS_COMPLETE)',
4954: p_prefix => l_debug_prefix,
4947: -- check if all input values match all fetched values
4948: IF (do_input_output_match_check) THEN
4949:
4950: -- FND Logging for debug purpose
4951: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4952: hz_utility_v2pub.debug
4953: (p_message => 'No. of records 1, exact match, return code=0(ADDRESS_COMPLETE)',
4954: p_prefix => l_debug_prefix,
4955: p_msg_level => fnd_log.level_statement,
4951: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4952: hz_utility_v2pub.debug
4953: (p_message => 'No. of records 1, exact match, return code=0(ADDRESS_COMPLETE)',
4954: p_prefix => l_debug_prefix,
4955: p_msg_level => fnd_log.level_statement,
4956: p_module_prefix => l_module_prefix,
4957: p_module => l_module
4958: );
4959: END IF;
4962: setup_msg_and_ret_code_proc(p_return_code => 0);/* ADDRESS_COMPLETE */
4963:
4964: ELSE -- input output match check failed
4965:
4966: -- FND Logging for debug purpose
4967: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4968: hz_utility_v2pub.debug
4969: (p_message => 'No. of records 1, input output match check failed, '||
4970: 'return code=1(ADDRESS_VALID)',
4963:
4964: ELSE -- input output match check failed
4965:
4966: -- FND Logging for debug purpose
4967: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4968: hz_utility_v2pub.debug
4969: (p_message => 'No. of records 1, input output match check failed, '||
4970: 'return code=1(ADDRESS_VALID)',
4971: p_prefix => l_debug_prefix,
4968: hz_utility_v2pub.debug
4969: (p_message => 'No. of records 1, input output match check failed, '||
4970: 'return code=1(ADDRESS_VALID)',
4971: p_prefix => l_debug_prefix,
4972: p_msg_level => fnd_log.level_statement,
4973: p_module_prefix => l_module_prefix,
4974: p_module => l_module
4975: );
4976: END IF;
4984:
4985: ELSIF -- more than 55
4986: ((geo_suggest_tbl.COUNT > 1) AND (l_geo_data_count > l_max_fetch_count)) THEN
4987:
4988: -- FND Logging for debug purpose
4989: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4990: hz_utility_v2pub.debug
4991: (p_message => 'No. of records more than 55, '||
4992: 'return code=1000(TOO_MANY_ROWS)',
4985: ELSIF -- more than 55
4986: ((geo_suggest_tbl.COUNT > 1) AND (l_geo_data_count > l_max_fetch_count)) THEN
4987:
4988: -- FND Logging for debug purpose
4989: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4990: hz_utility_v2pub.debug
4991: (p_message => 'No. of records more than 55, '||
4992: 'return code=1000(TOO_MANY_ROWS)',
4993: p_prefix => l_debug_prefix,
4990: hz_utility_v2pub.debug
4991: (p_message => 'No. of records more than 55, '||
4992: 'return code=1000(TOO_MANY_ROWS)',
4993: p_prefix => l_debug_prefix,
4994: p_msg_level => fnd_log.level_statement,
4995: p_module_prefix => l_module_prefix,
4996: p_module => l_module
4997: );
4998: END IF;
5007: x_records_count := NULL;
5008:
5009: ELSE -- between 2 and 55
5010:
5011: -- FND Logging for debug purpose
5012: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5013: hz_utility_v2pub.debug
5014: (p_message => 'No. of records between 2 and 55, '||
5015: 'return code=1(ADDRESS_VALID)',
5008:
5009: ELSE -- between 2 and 55
5010:
5011: -- FND Logging for debug purpose
5012: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5013: hz_utility_v2pub.debug
5014: (p_message => 'No. of records between 2 and 55, '||
5015: 'return code=1(ADDRESS_VALID)',
5016: p_prefix => l_debug_prefix,
5013: hz_utility_v2pub.debug
5014: (p_message => 'No. of records between 2 and 55, '||
5015: 'return code=1(ADDRESS_VALID)',
5016: p_prefix => l_debug_prefix,
5017: p_msg_level => fnd_log.level_statement,
5018: p_module_prefix => l_module_prefix,
5019: p_module => l_module
5020: );
5021: END IF;
5030: END IF; -- validation level = WARNING
5031:
5032: IF (l_addr_val_level = 'MINIMUM') THEN
5033:
5034: -- FND Logging for debug purpose
5035: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5036: hz_utility_v2pub.debug
5037: (p_message => 'Validation Level : MINIMUM',
5038: p_prefix => l_debug_prefix,
5031:
5032: IF (l_addr_val_level = 'MINIMUM') THEN
5033:
5034: -- FND Logging for debug purpose
5035: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5036: hz_utility_v2pub.debug
5037: (p_message => 'Validation Level : MINIMUM',
5038: p_prefix => l_debug_prefix,
5039: p_msg_level => fnd_log.level_statement,
5035: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5036: hz_utility_v2pub.debug
5037: (p_message => 'Validation Level : MINIMUM',
5038: p_prefix => l_debug_prefix,
5039: p_msg_level => fnd_log.level_statement,
5040: p_module_prefix => l_module_prefix,
5041: p_module => l_module
5042: );
5043: END IF;
5046: l_miss_min_geo_type := get_missing_input_fields (l_geo_struct_tbl => geo_struct_tbl);
5047:
5048: IF (l_miss_min_geo_type IS NOT NULL) THEN -- minimum check failed
5049:
5050: -- FND Logging for debug purpose
5051: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5052: hz_utility_v2pub.debug
5053: (p_message => 'Minimum check failed, '||
5054: 'return code=-1(ADDRESS_INVALID)',
5047:
5048: IF (l_miss_min_geo_type IS NOT NULL) THEN -- minimum check failed
5049:
5050: -- FND Logging for debug purpose
5051: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5052: hz_utility_v2pub.debug
5053: (p_message => 'Minimum check failed, '||
5054: 'return code=-1(ADDRESS_INVALID)',
5055: p_prefix => l_debug_prefix,
5052: hz_utility_v2pub.debug
5053: (p_message => 'Minimum check failed, '||
5054: 'return code=-1(ADDRESS_INVALID)',
5055: p_prefix => l_debug_prefix,
5056: p_msg_level => fnd_log.level_statement,
5057: p_module_prefix => l_module_prefix,
5058: p_module => l_module
5059: );
5060: END IF;
5065: p_msg_token_name => 'P_MISSING_ELEMENTS',
5066: p_msg_token_value => l_miss_min_geo_type);
5067: ELSE -- minimum check passed
5068:
5069: -- FND Logging for debug purpose
5070: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5071: hz_utility_v2pub.debug
5072: (p_message => 'Minimum check passed, '||
5073: 'return code=0(ADDRESS_COMPLETE)',
5066: p_msg_token_value => l_miss_min_geo_type);
5067: ELSE -- minimum check passed
5068:
5069: -- FND Logging for debug purpose
5070: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5071: hz_utility_v2pub.debug
5072: (p_message => 'Minimum check passed, '||
5073: 'return code=0(ADDRESS_COMPLETE)',
5074: p_prefix => l_debug_prefix,
5071: hz_utility_v2pub.debug
5072: (p_message => 'Minimum check passed, '||
5073: 'return code=0(ADDRESS_COMPLETE)',
5074: p_prefix => l_debug_prefix,
5075: p_msg_level => fnd_log.level_statement,
5076: p_module_prefix => l_module_prefix,
5077: p_module => l_module
5078: );
5079: END IF;
5084: END IF; -- minimum check
5085: END IF; -- validation level = MINIMUM
5086:
5087: IF (l_addr_val_level = 'NONE') THEN
5088: -- FND Logging for debug purpose
5089: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5090: hz_utility_v2pub.debug
5091: (p_message => 'Validation Level : NONE, return code=0(ADDRESS_COMPLETE)',
5092: p_prefix => l_debug_prefix,
5085: END IF; -- validation level = MINIMUM
5086:
5087: IF (l_addr_val_level = 'NONE') THEN
5088: -- FND Logging for debug purpose
5089: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5090: hz_utility_v2pub.debug
5091: (p_message => 'Validation Level : NONE, return code=0(ADDRESS_COMPLETE)',
5092: p_prefix => l_debug_prefix,
5093: p_msg_level => fnd_log.level_statement,
5089: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5090: hz_utility_v2pub.debug
5091: (p_message => 'Validation Level : NONE, return code=0(ADDRESS_COMPLETE)',
5092: p_prefix => l_debug_prefix,
5093: p_msg_level => fnd_log.level_statement,
5094: p_module_prefix => l_module_prefix,
5095: p_module => l_module
5096: );
5097: END IF;
5099: -- set return code and return message
5100: setup_msg_and_ret_code_proc(p_return_code => 0); /* ADDRESS_COMPLETE */
5101: END IF; -- validation level = MINIMUM
5102:
5103: -- FND Logging for debug purpose
5104: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5105: hz_utility_v2pub.debug
5106: (p_message => 'End: process_output_data_proc(-)',
5107: p_prefix => l_debug_prefix,
5100: setup_msg_and_ret_code_proc(p_return_code => 0); /* ADDRESS_COMPLETE */
5101: END IF; -- validation level = MINIMUM
5102:
5103: -- FND Logging for debug purpose
5104: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5105: hz_utility_v2pub.debug
5106: (p_message => 'End: process_output_data_proc(-)',
5107: p_prefix => l_debug_prefix,
5108: p_msg_level => fnd_log.level_procedure,
5104: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5105: hz_utility_v2pub.debug
5106: (p_message => 'End: process_output_data_proc(-)',
5107: p_prefix => l_debug_prefix,
5108: p_msg_level => fnd_log.level_procedure,
5109: p_module_prefix => l_module_prefix,
5110: p_module => l_module
5111: );
5112: END IF;
5245:
5246: l_child_map_count := 0;
5247: IF (geo_struct_tbl.COUNT > 1) THEN
5248:
5249: -- FND Logging for debug purpose
5250: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5251: hz_utility_v2pub.debug
5252: (p_message => 'Trying to fetch children for complete next level...',
5253: p_prefix => l_debug_prefix,
5246: l_child_map_count := 0;
5247: IF (geo_struct_tbl.COUNT > 1) THEN
5248:
5249: -- FND Logging for debug purpose
5250: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5251: hz_utility_v2pub.debug
5252: (p_message => 'Trying to fetch children for complete next level...',
5253: p_prefix => l_debug_prefix,
5254: p_msg_level => fnd_log.level_statement,
5250: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5251: hz_utility_v2pub.debug
5252: (p_message => 'Trying to fetch children for complete next level...',
5253: p_prefix => l_debug_prefix,
5254: p_msg_level => fnd_log.level_statement,
5255: p_module_prefix => l_module_prefix,
5256: p_module => l_module
5257: );
5258: END IF;
5260: -- get max column for which value is fetched
5261: l_max_fetched_value := get_max_fetched_value;
5262: -- ns_debug.put_line('MAX FETCH VALUE :'||l_max_fetched_value);
5263:
5264: -- FND Logging for debug purpose
5265: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5266: hz_utility_v2pub.debug
5267: (p_message => 'l_max_fetched_value:'||l_max_fetched_value
5268: ||' l_max_mapped_element_col_value:'||l_max_mapped_element_col_value,
5261: l_max_fetched_value := get_max_fetched_value;
5262: -- ns_debug.put_line('MAX FETCH VALUE :'||l_max_fetched_value);
5263:
5264: -- FND Logging for debug purpose
5265: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5266: hz_utility_v2pub.debug
5267: (p_message => 'l_max_fetched_value:'||l_max_fetched_value
5268: ||' l_max_mapped_element_col_value:'||l_max_mapped_element_col_value,
5269: p_prefix => l_debug_prefix,
5266: hz_utility_v2pub.debug
5267: (p_message => 'l_max_fetched_value:'||l_max_fetched_value
5268: ||' l_max_mapped_element_col_value:'||l_max_mapped_element_col_value,
5269: p_prefix => l_debug_prefix,
5270: p_msg_level => fnd_log.level_statement,
5271: p_module_prefix => l_module_prefix,
5272: p_module => l_module
5273: );
5274: END IF;
5276: -- does not have to execute child query if l_max_fetched_value = max mapped value
5277: -- i.e. there is no more child level
5278: IF (l_max_fetched_value < l_max_mapped_element_col_value) THEN
5279:
5280: -- FND Logging for debug purpose
5281: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5282: hz_utility_v2pub.debug
5283: (p_message => 'Trying to get child data...',
5284: p_prefix => l_debug_prefix,
5277: -- i.e. there is no more child level
5278: IF (l_max_fetched_value < l_max_mapped_element_col_value) THEN
5279:
5280: -- FND Logging for debug purpose
5281: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5282: hz_utility_v2pub.debug
5283: (p_message => 'Trying to get child data...',
5284: p_prefix => l_debug_prefix,
5285: p_msg_level => fnd_log.level_statement,
5281: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5282: hz_utility_v2pub.debug
5283: (p_message => 'Trying to get child data...',
5284: p_prefix => l_debug_prefix,
5285: p_msg_level => fnd_log.level_statement,
5286: p_module_prefix => l_module_prefix,
5287: p_module => l_module
5288: );
5289: END IF;
5343: END IF;
5344:
5345: l_sql_stmt_child := l_select_child||l_from_child||l_where_child;
5346:
5347: -- FND Logging for debug purpose
5348: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5349: hz_utility_v2pub.debug
5350: (p_message => 'Total NULL columns appended in fetch child query :'||l_total_null_cols_child,
5351: p_prefix => l_debug_prefix,
5344:
5345: l_sql_stmt_child := l_select_child||l_from_child||l_where_child;
5346:
5347: -- FND Logging for debug purpose
5348: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5349: hz_utility_v2pub.debug
5350: (p_message => 'Total NULL columns appended in fetch child query :'||l_total_null_cols_child,
5351: p_prefix => l_debug_prefix,
5352: p_msg_level => fnd_log.level_statement,
5348: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5349: hz_utility_v2pub.debug
5350: (p_message => 'Total NULL columns appended in fetch child query :'||l_total_null_cols_child,
5351: p_prefix => l_debug_prefix,
5352: p_msg_level => fnd_log.level_statement,
5353: p_module_prefix => l_module_prefix,
5354: p_module => l_module
5355: );
5356: END IF;
5354: p_module => l_module
5355: );
5356: END IF;
5357:
5358: -- FND Logging for debug purpose
5359: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5360: hz_utility_v2pub.debug
5361: (p_message => 'Child Query :'||l_sql_stmt_child,
5362: p_prefix => l_debug_prefix,
5355: );
5356: END IF;
5357:
5358: -- FND Logging for debug purpose
5359: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5360: hz_utility_v2pub.debug
5361: (p_message => 'Child Query :'||l_sql_stmt_child,
5362: p_prefix => l_debug_prefix,
5363: p_msg_level => fnd_log.level_statement,
5359: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5360: hz_utility_v2pub.debug
5361: (p_message => 'Child Query :'||l_sql_stmt_child,
5362: p_prefix => l_debug_prefix,
5363: p_msg_level => fnd_log.level_statement,
5364: p_module_prefix => l_module_prefix,
5365: p_module => l_module
5366: );
5367: END IF;
5650: -- Main code for Search_geographies procedure
5651: ---------------------------------------------------------------------+
5652: BEGIN
5653:
5654: -- FND Logging for debug purpose
5655: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5656: hz_utility_v2pub.debug
5657: (p_message => 'BEGIN: Address Suggestion API',
5658: p_prefix => l_debug_prefix,
5651: ---------------------------------------------------------------------+
5652: BEGIN
5653:
5654: -- FND Logging for debug purpose
5655: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5656: hz_utility_v2pub.debug
5657: (p_message => 'BEGIN: Address Suggestion API',
5658: p_prefix => l_debug_prefix,
5659: p_msg_level => fnd_log.level_statement,
5655: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5656: hz_utility_v2pub.debug
5657: (p_message => 'BEGIN: Address Suggestion API',
5658: p_prefix => l_debug_prefix,
5659: p_msg_level => fnd_log.level_statement,
5660: p_module_prefix => l_module_prefix,
5661: p_module => l_module
5662: );
5663: END IF;
5682: -- we are exposing this parameter. (This is based on TDD review on 27 July 2005).
5683: -- l_address_usage := p_address_usage;
5684: l_address_usage := 'GEOGRAPHY';
5685:
5686: -- FND Logging for debug purpose
5687: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5688: hz_utility_v2pub.debug
5689: (p_message => 'Get mapping details. Call build_geo_struct_tbl_pvt(+)',
5690: p_prefix => l_debug_prefix,
5683: -- l_address_usage := p_address_usage;
5684: l_address_usage := 'GEOGRAPHY';
5685:
5686: -- FND Logging for debug purpose
5687: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5688: hz_utility_v2pub.debug
5689: (p_message => 'Get mapping details. Call build_geo_struct_tbl_pvt(+)',
5690: p_prefix => l_debug_prefix,
5691: p_msg_level => fnd_log.level_procedure,
5687: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5688: hz_utility_v2pub.debug
5689: (p_message => 'Get mapping details. Call build_geo_struct_tbl_pvt(+)',
5690: p_prefix => l_debug_prefix,
5691: p_msg_level => fnd_log.level_procedure,
5692: p_module_prefix => l_module_prefix,
5693: p_module => l_module
5694: );
5695: END IF;
5697: -- get full mapped structure for specific address_style and
5698: -- store it in plsql table
5699: build_geo_struct_tbl_pvt(p_country_code, p_address_style, p_table_name, l_address_usage);
5700:
5701: -- FND Logging for debug purpose
5702: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5703: hz_utility_v2pub.debug
5704: (p_message => 'Finished calling build_geo_struct_tbl_pvt(-)',
5705: p_prefix => l_debug_prefix,
5698: -- store it in plsql table
5699: build_geo_struct_tbl_pvt(p_country_code, p_address_style, p_table_name, l_address_usage);
5700:
5701: -- FND Logging for debug purpose
5702: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5703: hz_utility_v2pub.debug
5704: (p_message => 'Finished calling build_geo_struct_tbl_pvt(-)',
5705: p_prefix => l_debug_prefix,
5706: p_msg_level => fnd_log.level_procedure,
5702: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5703: hz_utility_v2pub.debug
5704: (p_message => 'Finished calling build_geo_struct_tbl_pvt(-)',
5705: p_prefix => l_debug_prefix,
5706: p_msg_level => fnd_log.level_procedure,
5707: p_module_prefix => l_module_prefix,
5708: p_module => l_module
5709: );
5710: END IF;
5715: l_addr_val_level := NVL(hz_gnr_pub.get_addr_val_level(UPPER(p_country_code)),'NONE');
5716: --l_addr_val_level := 'ERROR';
5717: x_validation_level := l_addr_val_level;
5718:
5719: -- FND Logging for debug purpose
5720: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5721: hz_utility_v2pub.debug
5722: (p_message => 'Validation Level:'||l_addr_val_level,
5723: p_prefix => l_debug_prefix,
5716: --l_addr_val_level := 'ERROR';
5717: x_validation_level := l_addr_val_level;
5718:
5719: -- FND Logging for debug purpose
5720: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5721: hz_utility_v2pub.debug
5722: (p_message => 'Validation Level:'||l_addr_val_level,
5723: p_prefix => l_debug_prefix,
5724: p_msg_level => fnd_log.level_statement,
5720: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5721: hz_utility_v2pub.debug
5722: (p_message => 'Validation Level:'||l_addr_val_level,
5723: p_prefix => l_debug_prefix,
5724: p_msg_level => fnd_log.level_statement,
5725: p_module_prefix => l_module_prefix,
5726: p_module => l_module
5727: );
5728: END IF;
5747: ------------------------------------------------------------------------+*/
5748:
5749: IF (l_addr_val_level = 'ERROR') THEN
5750:
5751: -- FND Logging for debug purpose
5752: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5753: hz_utility_v2pub.debug
5754: (p_message => 'Performing Geo Validation to see if it will pass or fail later with current data (+)',
5755: p_prefix => l_debug_prefix,
5748:
5749: IF (l_addr_val_level = 'ERROR') THEN
5750:
5751: -- FND Logging for debug purpose
5752: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5753: hz_utility_v2pub.debug
5754: (p_message => 'Performing Geo Validation to see if it will pass or fail later with current data (+)',
5755: p_prefix => l_debug_prefix,
5756: p_msg_level => fnd_log.level_statement,
5752: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5753: hz_utility_v2pub.debug
5754: (p_message => 'Performing Geo Validation to see if it will pass or fail later with current data (+)',
5755: p_prefix => l_debug_prefix,
5756: p_msg_level => fnd_log.level_statement,
5757: p_module_prefix => l_module_prefix,
5758: p_module => l_module
5759: );
5760: END IF;
5792:
5793: IF (LX_STATUS <> FND_API.G_RET_STS_SUCCESS) THEN
5794: l_success := 'N';
5795:
5796: -- FND Logging for debug purpose
5797: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5798: hz_utility_v2pub.debug
5799: (p_message => 'Geo Validation Failed. Perform suggestion logic.',
5800: p_prefix => l_debug_prefix,
5793: IF (LX_STATUS <> FND_API.G_RET_STS_SUCCESS) THEN
5794: l_success := 'N';
5795:
5796: -- FND Logging for debug purpose
5797: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5798: hz_utility_v2pub.debug
5799: (p_message => 'Geo Validation Failed. Perform suggestion logic.',
5800: p_prefix => l_debug_prefix,
5801: p_msg_level => fnd_log.level_statement,
5797: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5798: hz_utility_v2pub.debug
5799: (p_message => 'Geo Validation Failed. Perform suggestion logic.',
5800: p_prefix => l_debug_prefix,
5801: p_msg_level => fnd_log.level_statement,
5802: p_module_prefix => l_module_prefix,
5803: p_module => l_module
5804: );
5805: END IF;
5809: IF (HZ_GNR_PVT.G_USER_ATTEMPT_COUNT IS NULL) THEN
5810: l_success := 'N'; -- so that we can suggest more if there is anything during 1st attempt
5811: l_geo_validation_passed := 'Y'; -- This will be used while setting message for validation level ERROR
5812:
5813: -- FND Logging for debug purpose
5814: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5815: hz_utility_v2pub.debug
5816: (p_message => 'Even though Geo Validation Passed, it was users 1st attempt, so continue with suggestion..',
5817: p_prefix => l_debug_prefix,
5810: l_success := 'N'; -- so that we can suggest more if there is anything during 1st attempt
5811: l_geo_validation_passed := 'Y'; -- This will be used while setting message for validation level ERROR
5812:
5813: -- FND Logging for debug purpose
5814: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5815: hz_utility_v2pub.debug
5816: (p_message => 'Even though Geo Validation Passed, it was users 1st attempt, so continue with suggestion..',
5817: p_prefix => l_debug_prefix,
5818: p_msg_level => fnd_log.level_statement,
5814: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5815: hz_utility_v2pub.debug
5816: (p_message => 'Even though Geo Validation Passed, it was users 1st attempt, so continue with suggestion..',
5817: p_prefix => l_debug_prefix,
5818: p_msg_level => fnd_log.level_statement,
5819: p_module_prefix => l_module_prefix,
5820: p_module => l_module
5821: );
5822: END IF;
5823:
5824: ELSE
5825: l_success := 'Y';
5826:
5827: -- FND Logging for debug purpose
5828: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5829: hz_utility_v2pub.debug
5830: (p_message => 'Geo Validation Successful. No need for suggestion.',
5831: p_prefix => l_debug_prefix,
5824: ELSE
5825: l_success := 'Y';
5826:
5827: -- FND Logging for debug purpose
5828: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5829: hz_utility_v2pub.debug
5830: (p_message => 'Geo Validation Successful. No need for suggestion.',
5831: p_prefix => l_debug_prefix,
5832: p_msg_level => fnd_log.level_statement,
5828: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5829: hz_utility_v2pub.debug
5830: (p_message => 'Geo Validation Successful. No need for suggestion.',
5831: p_prefix => l_debug_prefix,
5832: p_msg_level => fnd_log.level_statement,
5833: p_module_prefix => l_module_prefix,
5834: p_module => l_module
5835: );
5836: END IF;
5842: -- if any exception happens in Geo Validation API, continue with suggestion logic.
5843: EXCEPTION WHEN OTHERS THEN
5844: l_success := 'N';
5845:
5846: -- FND Logging for debug purpose
5847: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5848: hz_utility_v2pub.debug
5849: (p_message => 'Exception happend in Geo Validation API. Perform suggestion logic. '||SQLERRM,
5850: p_prefix => l_debug_prefix,
5843: EXCEPTION WHEN OTHERS THEN
5844: l_success := 'N';
5845:
5846: -- FND Logging for debug purpose
5847: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5848: hz_utility_v2pub.debug
5849: (p_message => 'Exception happend in Geo Validation API. Perform suggestion logic. '||SQLERRM,
5850: p_prefix => l_debug_prefix,
5851: p_msg_level => fnd_log.level_statement,
5847: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5848: hz_utility_v2pub.debug
5849: (p_message => 'Exception happend in Geo Validation API. Perform suggestion logic. '||SQLERRM,
5850: p_prefix => l_debug_prefix,
5851: p_msg_level => fnd_log.level_statement,
5852: p_module_prefix => l_module_prefix,
5853: p_module => l_module
5854: );
5855: END IF;
5860: --ns_debug.put_line('Geo Validation X_ADDR_VAL_STATUS :'||LX_ADDR_VAL_STATUS);
5861: ELSE
5862: --ns_debug.put_line('Second Pass Test Failed...');
5863:
5864: -- FND Logging for debug purpose
5865: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5866: hz_utility_v2pub.debug
5867: (p_message => 'Validation level is not ERROR...Did not go into Geo Validation check..',
5868: p_prefix => l_debug_prefix,
5861: ELSE
5862: --ns_debug.put_line('Second Pass Test Failed...');
5863:
5864: -- FND Logging for debug purpose
5865: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5866: hz_utility_v2pub.debug
5867: (p_message => 'Validation level is not ERROR...Did not go into Geo Validation check..',
5868: p_prefix => l_debug_prefix,
5869: p_msg_level => fnd_log.level_statement,
5865: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5866: hz_utility_v2pub.debug
5867: (p_message => 'Validation level is not ERROR...Did not go into Geo Validation check..',
5868: p_prefix => l_debug_prefix,
5869: p_msg_level => fnd_log.level_statement,
5870: p_module_prefix => l_module_prefix,
5871: p_module => l_module
5872: );
5873: END IF;
5878: -- get into address suggestion mode only if geo validation (2nd pass) did not
5879: -- succeed.
5880: IF (l_addr_val_level IN ('ERROR','WARNING') AND (l_success = 'N')) THEN
5881:
5882: -- FND Logging for debug purpose
5883: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5884: hz_utility_v2pub.debug
5885: (p_message => 'Mapped Elements Table Count:'||NVL(geo_struct_tbl.COUNT,0),
5886: p_prefix => l_debug_prefix,
5879: -- succeed.
5880: IF (l_addr_val_level IN ('ERROR','WARNING') AND (l_success = 'N')) THEN
5881:
5882: -- FND Logging for debug purpose
5883: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5884: hz_utility_v2pub.debug
5885: (p_message => 'Mapped Elements Table Count:'||NVL(geo_struct_tbl.COUNT,0),
5886: p_prefix => l_debug_prefix,
5887: p_msg_level => fnd_log.level_statement,
5883: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5884: hz_utility_v2pub.debug
5885: (p_message => 'Mapped Elements Table Count:'||NVL(geo_struct_tbl.COUNT,0),
5886: p_prefix => l_debug_prefix,
5887: p_msg_level => fnd_log.level_statement,
5888: p_module_prefix => l_module_prefix,
5889: p_module => l_module
5890: );
5891: END IF;
5925: -------------------------------------------------------------------+
5926: -- SEARCH 1: Here we query for all passed parameters for which mapping
5927: -- exist in mapping table.
5928: -------------------------------------------------------------------+
5929: -- FND Logging for debug purpose
5930: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5931: hz_utility_v2pub.debug
5932: (p_message => 'Begin search 1 for ALL_PASSED_PARAM_SEARCH (+)',
5933: p_prefix => l_debug_prefix,
5926: -- SEARCH 1: Here we query for all passed parameters for which mapping
5927: -- exist in mapping table.
5928: -------------------------------------------------------------------+
5929: -- FND Logging for debug purpose
5930: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5931: hz_utility_v2pub.debug
5932: (p_message => 'Begin search 1 for ALL_PASSED_PARAM_SEARCH (+)',
5933: p_prefix => l_debug_prefix,
5934: p_msg_level => fnd_log.level_procedure,
5930: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5931: hz_utility_v2pub.debug
5932: (p_message => 'Begin search 1 for ALL_PASSED_PARAM_SEARCH (+)',
5933: p_prefix => l_debug_prefix,
5934: p_msg_level => fnd_log.level_procedure,
5935: p_module_prefix => l_module_prefix,
5936: p_module => l_module
5937: );
5938: END IF;
5940: l_search_type := 'ALL_PASSED_PARAM_SEARCH';
5941: search_routine_pvt (pt_search_type => l_search_type,
5942: pt_bind_table => bind_table_1);
5943:
5944: -- FND Logging for debug purpose
5945: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5946: hz_utility_v2pub.debug
5947: (p_message => 'End of search 1 for ALL_PASSED_PARAM_SEARCH (-)',
5948: p_prefix => l_debug_prefix,
5941: search_routine_pvt (pt_search_type => l_search_type,
5942: pt_bind_table => bind_table_1);
5943:
5944: -- FND Logging for debug purpose
5945: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5946: hz_utility_v2pub.debug
5947: (p_message => 'End of search 1 for ALL_PASSED_PARAM_SEARCH (-)',
5948: p_prefix => l_debug_prefix,
5949: p_msg_level => fnd_log.level_procedure,
5945: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5946: hz_utility_v2pub.debug
5947: (p_message => 'End of search 1 for ALL_PASSED_PARAM_SEARCH (-)',
5948: p_prefix => l_debug_prefix,
5949: p_msg_level => fnd_log.level_procedure,
5950: p_module_prefix => l_module_prefix,
5951: p_module => l_module
5952: );
5953: END IF;
5984: -- ELSE
5985: -- 1.1.2. If No, Suggest 1 level down children.
5986: -- This will put data in geo_suggest_tbl also
5987:
5988: -- FND Logging for debug purpose
5989: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5990: hz_utility_v2pub.debug
5991: (p_message => '1 Rec found in ALL_PASSED_PARAM_SEARCH. Performing child search (+)',
5992: p_prefix => l_debug_prefix,
5985: -- 1.1.2. If No, Suggest 1 level down children.
5986: -- This will put data in geo_suggest_tbl also
5987:
5988: -- FND Logging for debug purpose
5989: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5990: hz_utility_v2pub.debug
5991: (p_message => '1 Rec found in ALL_PASSED_PARAM_SEARCH. Performing child search (+)',
5992: p_prefix => l_debug_prefix,
5993: p_msg_level => fnd_log.level_procedure,
5989: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5990: hz_utility_v2pub.debug
5991: (p_message => '1 Rec found in ALL_PASSED_PARAM_SEARCH. Performing child search (+)',
5992: p_prefix => l_debug_prefix,
5993: p_msg_level => fnd_log.level_procedure,
5994: p_module_prefix => l_module_prefix,
5995: p_module => l_module
5996: );
5997: END IF;
5997: END IF;
5998:
5999: get_next_level_children_proc;
6000:
6001: -- FND Logging for debug purpose
6002: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6003: hz_utility_v2pub.debug
6004: (p_message => 'Child search for ALL_PASSED_PARAM_SEARCH completed (-)',
6005: p_prefix => l_debug_prefix,
5998:
5999: get_next_level_children_proc;
6000:
6001: -- FND Logging for debug purpose
6002: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6003: hz_utility_v2pub.debug
6004: (p_message => 'Child search for ALL_PASSED_PARAM_SEARCH completed (-)',
6005: p_prefix => l_debug_prefix,
6006: p_msg_level => fnd_log.level_procedure,
6002: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6003: hz_utility_v2pub.debug
6004: (p_message => 'Child search for ALL_PASSED_PARAM_SEARCH completed (-)',
6005: p_prefix => l_debug_prefix,
6006: p_msg_level => fnd_log.level_procedure,
6007: p_module_prefix => l_module_prefix,
6008: p_module => l_module
6009: );
6010: END IF;
6034: (l_max_mapped_element_col_value-1 = l_max_passed_element_col_value)) AND
6035: (l_max_passed_element_col_value > 3)
6036: )THEN
6037:
6038: -- FND Logging for debug purpose
6039: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6040: hz_utility_v2pub.debug
6041: (p_message => 'Begin search 2..l_max_passed_element_col_value='||
6042: l_max_passed_element_col_value||' l_max_mapped_element_col_value='||
6035: (l_max_passed_element_col_value > 3)
6036: )THEN
6037:
6038: -- FND Logging for debug purpose
6039: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6040: hz_utility_v2pub.debug
6041: (p_message => 'Begin search 2..l_max_passed_element_col_value='||
6042: l_max_passed_element_col_value||' l_max_mapped_element_col_value='||
6043: l_max_mapped_element_col_value,
6041: (p_message => 'Begin search 2..l_max_passed_element_col_value='||
6042: l_max_passed_element_col_value||' l_max_mapped_element_col_value='||
6043: l_max_mapped_element_col_value,
6044: p_prefix => l_debug_prefix,
6045: p_msg_level => fnd_log.level_statement,
6046: p_module_prefix => l_module_prefix,
6047: p_module => l_module
6048: );
6049: END IF;
6060: -- if l_count_for_where_clause_2 = 2 i.e. all required params passed,
6061: -- build where clause
6062: IF (l_count_for_where_clause_2 = 2) THEN
6063:
6064: -- FND Logging for debug purpose
6065: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6066: hz_utility_v2pub.debug
6067: (p_message => 'For Where Clause 2...required params are passed',
6068: p_prefix => l_debug_prefix,
6061: -- build where clause
6062: IF (l_count_for_where_clause_2 = 2) THEN
6063:
6064: -- FND Logging for debug purpose
6065: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6066: hz_utility_v2pub.debug
6067: (p_message => 'For Where Clause 2...required params are passed',
6068: p_prefix => l_debug_prefix,
6069: p_msg_level => fnd_log.level_statement,
6065: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6066: hz_utility_v2pub.debug
6067: (p_message => 'For Where Clause 2...required params are passed',
6068: p_prefix => l_debug_prefix,
6069: p_msg_level => fnd_log.level_statement,
6070: p_module_prefix => l_module_prefix,
6071: p_module => l_module
6072: );
6073: END IF;
6097: END LOOP;
6098: -- if l_count_for_where_clause_3 = 2 i.e. all required params passed,
6099: -- build where clause
6100: IF (l_count_for_where_clause_3 = 2) THEN
6101: -- FND Logging for debug purpose
6102: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6103: hz_utility_v2pub.debug
6104: (p_message => 'For Where Clause 3...required params are passed',
6105: p_prefix => l_debug_prefix,
6098: -- if l_count_for_where_clause_3 = 2 i.e. all required params passed,
6099: -- build where clause
6100: IF (l_count_for_where_clause_3 = 2) THEN
6101: -- FND Logging for debug purpose
6102: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6103: hz_utility_v2pub.debug
6104: (p_message => 'For Where Clause 3...required params are passed',
6105: p_prefix => l_debug_prefix,
6106: p_msg_level => fnd_log.level_statement,
6102: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6103: hz_utility_v2pub.debug
6104: (p_message => 'For Where Clause 3...required params are passed',
6105: p_prefix => l_debug_prefix,
6106: p_msg_level => fnd_log.level_statement,
6107: p_module_prefix => l_module_prefix,
6108: p_module => l_module
6109: );
6110: END IF;
6126:
6127: -- execute query only if any of these 2 sql queries are constructed completely
6128: IF ((l_count_for_where_clause_2 = 2) OR (l_count_for_where_clause_3 = 2)) THEN
6129:
6130: -- FND Logging for debug purpose
6131: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6132: hz_utility_v2pub.debug
6133: (p_message => 'Begin search 2 for LEVEL4_UNION_LEVEL5_SEARCH (+)',
6134: p_prefix => l_debug_prefix,
6127: -- execute query only if any of these 2 sql queries are constructed completely
6128: IF ((l_count_for_where_clause_2 = 2) OR (l_count_for_where_clause_3 = 2)) THEN
6129:
6130: -- FND Logging for debug purpose
6131: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6132: hz_utility_v2pub.debug
6133: (p_message => 'Begin search 2 for LEVEL4_UNION_LEVEL5_SEARCH (+)',
6134: p_prefix => l_debug_prefix,
6135: p_msg_level => fnd_log.level_procedure,
6131: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6132: hz_utility_v2pub.debug
6133: (p_message => 'Begin search 2 for LEVEL4_UNION_LEVEL5_SEARCH (+)',
6134: p_prefix => l_debug_prefix,
6135: p_msg_level => fnd_log.level_procedure,
6136: p_module_prefix => l_module_prefix,
6137: p_module => l_module
6138: );
6139: END IF;
6141: l_search_type := 'LEVEL4_UNION_LEVEL5_SEARCH';
6142: search_routine_pvt (pt_search_type => l_search_type,
6143: pt_bind_table => bind_table_2);
6144:
6145: -- FND Logging for debug purpose
6146: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6147: hz_utility_v2pub.debug
6148: (p_message => 'End search 3 for LEVEL4_UNION_LEVEL5_SEARCH (-)',
6149: p_prefix => l_debug_prefix,
6142: search_routine_pvt (pt_search_type => l_search_type,
6143: pt_bind_table => bind_table_2);
6144:
6145: -- FND Logging for debug purpose
6146: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6147: hz_utility_v2pub.debug
6148: (p_message => 'End search 3 for LEVEL4_UNION_LEVEL5_SEARCH (-)',
6149: p_prefix => l_debug_prefix,
6150: p_msg_level => fnd_log.level_procedure,
6146: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6147: hz_utility_v2pub.debug
6148: (p_message => 'End search 3 for LEVEL4_UNION_LEVEL5_SEARCH (-)',
6149: p_prefix => l_debug_prefix,
6150: p_msg_level => fnd_log.level_procedure,
6151: p_module_prefix => l_module_prefix,
6152: p_module => l_module
6153: );
6154: END IF;
6154: END IF;
6155:
6156: ELSE -- ignored LEVEL4_UNION_LEVEL5_SEARCH search
6157:
6158: -- FND Logging for debug purpose
6159: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6160: hz_utility_v2pub.debug
6161: (p_message => 'Ignored LEVEL4_UNION_LEVEL5_SEARCH because values for '||
6162: 'neither Level1+Level(n) '||
6155:
6156: ELSE -- ignored LEVEL4_UNION_LEVEL5_SEARCH search
6157:
6158: -- FND Logging for debug purpose
6159: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6160: hz_utility_v2pub.debug
6161: (p_message => 'Ignored LEVEL4_UNION_LEVEL5_SEARCH because values for '||
6162: 'neither Level1+Level(n) '||
6163: 'nor Level1 + Level(n-1) are passed',
6161: (p_message => 'Ignored LEVEL4_UNION_LEVEL5_SEARCH because values for '||
6162: 'neither Level1+Level(n) '||
6163: 'nor Level1 + Level(n-1) are passed',
6164: p_prefix => l_debug_prefix,
6165: p_msg_level => fnd_log.level_procedure,
6166: p_module_prefix => l_module_prefix,
6167: p_module => l_module
6168: );
6169: END IF;
6172:
6173: -- Again check, if 1 row fetched, try to get child records
6174: IF (geo_rec_tbl.COUNT = 1) THEN
6175:
6176: -- FND Logging for debug purpose
6177: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6178: hz_utility_v2pub.debug
6179: (p_message => '1 Rec found in LEVEL4_UNION_LEVEL5_SEARCH. Performing child search (+)',
6180: p_prefix => l_debug_prefix,
6173: -- Again check, if 1 row fetched, try to get child records
6174: IF (geo_rec_tbl.COUNT = 1) THEN
6175:
6176: -- FND Logging for debug purpose
6177: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6178: hz_utility_v2pub.debug
6179: (p_message => '1 Rec found in LEVEL4_UNION_LEVEL5_SEARCH. Performing child search (+)',
6180: p_prefix => l_debug_prefix,
6181: p_msg_level => fnd_log.level_procedure,
6177: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6178: hz_utility_v2pub.debug
6179: (p_message => '1 Rec found in LEVEL4_UNION_LEVEL5_SEARCH. Performing child search (+)',
6180: p_prefix => l_debug_prefix,
6181: p_msg_level => fnd_log.level_procedure,
6182: p_module_prefix => l_module_prefix,
6183: p_module => l_module
6184: );
6185: END IF;
6185: END IF;
6186:
6187: get_next_level_children_proc;
6188:
6189: -- FND Logging for debug purpose
6190: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6191: hz_utility_v2pub.debug
6192: (p_message => 'Completed child search for LEVEL4_UNION_LEVEL5_SEARCH (-)',
6193: p_prefix => l_debug_prefix,
6186:
6187: get_next_level_children_proc;
6188:
6189: -- FND Logging for debug purpose
6190: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6191: hz_utility_v2pub.debug
6192: (p_message => 'Completed child search for LEVEL4_UNION_LEVEL5_SEARCH (-)',
6193: p_prefix => l_debug_prefix,
6194: p_msg_level => fnd_log.level_procedure,
6190: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6191: hz_utility_v2pub.debug
6192: (p_message => 'Completed child search for LEVEL4_UNION_LEVEL5_SEARCH (-)',
6193: p_prefix => l_debug_prefix,
6194: p_msg_level => fnd_log.level_procedure,
6195: p_module_prefix => l_module_prefix,
6196: p_module => l_module
6197: );
6198: END IF;
6250: -- 1. If 1 row is identified,
6251: -- If more than 1 rows, then just suggest them.
6252: IF (geo_rec_tbl.COUNT = 1) THEN
6253:
6254: -- FND Logging for debug purpose
6255: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6256: hz_utility_v2pub.debug
6257: (p_message => '1 record found for SEARCH_FROM_TOP search. Performing child search (+)',
6258: p_prefix => l_debug_prefix,
6251: -- If more than 1 rows, then just suggest them.
6252: IF (geo_rec_tbl.COUNT = 1) THEN
6253:
6254: -- FND Logging for debug purpose
6255: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6256: hz_utility_v2pub.debug
6257: (p_message => '1 record found for SEARCH_FROM_TOP search. Performing child search (+)',
6258: p_prefix => l_debug_prefix,
6259: p_msg_level => fnd_log.level_procedure,
6255: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6256: hz_utility_v2pub.debug
6257: (p_message => '1 record found for SEARCH_FROM_TOP search. Performing child search (+)',
6258: p_prefix => l_debug_prefix,
6259: p_msg_level => fnd_log.level_procedure,
6260: p_module_prefix => l_module_prefix,
6261: p_module => l_module
6262: );
6263: END IF;
6263: END IF;
6264:
6265: get_next_level_children_proc;
6266:
6267: -- FND Logging for debug purpose
6268: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6269: hz_utility_v2pub.debug
6270: (p_message => 'Completed child search for SEARCH_FROM_TOP search.(-)',
6271: p_prefix => l_debug_prefix,
6264:
6265: get_next_level_children_proc;
6266:
6267: -- FND Logging for debug purpose
6268: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6269: hz_utility_v2pub.debug
6270: (p_message => 'Completed child search for SEARCH_FROM_TOP search.(-)',
6271: p_prefix => l_debug_prefix,
6272: p_msg_level => fnd_log.level_procedure,
6268: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6269: hz_utility_v2pub.debug
6270: (p_message => 'Completed child search for SEARCH_FROM_TOP search.(-)',
6271: p_prefix => l_debug_prefix,
6272: p_msg_level => fnd_log.level_procedure,
6273: p_module_prefix => l_module_prefix,
6274: p_module => l_module
6275: );
6276: END IF;
6290: -- One example is: If country is passed and there are no states setup
6291: -- for that country.
6292: IF ((geo_suggest_tbl.COUNT = 0) AND (geo_rec_tbl.COUNT > 0)) THEN
6293:
6294: -- FND Logging for debug purpose
6295: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6296: hz_utility_v2pub.debug
6297: (p_message => 'Geo_suggest_tbl count is 0.. while geo_rec_tbl count is '
6298: ||TO_CHAR(geo_rec_tbl.COUNT),
6291: -- for that country.
6292: IF ((geo_suggest_tbl.COUNT = 0) AND (geo_rec_tbl.COUNT > 0)) THEN
6293:
6294: -- FND Logging for debug purpose
6295: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6296: hz_utility_v2pub.debug
6297: (p_message => 'Geo_suggest_tbl count is 0.. while geo_rec_tbl count is '
6298: ||TO_CHAR(geo_rec_tbl.COUNT),
6299: p_prefix => l_debug_prefix,
6296: hz_utility_v2pub.debug
6297: (p_message => 'Geo_suggest_tbl count is 0.. while geo_rec_tbl count is '
6298: ||TO_CHAR(geo_rec_tbl.COUNT),
6299: p_prefix => l_debug_prefix,
6300: p_msg_level => fnd_log.level_statement,
6301: p_module_prefix => l_module_prefix,
6302: p_module => l_module
6303: );
6304: END IF;
6306: FOR i IN geo_rec_tbl.FIRST..geo_rec_tbl.LAST LOOP
6307: move_from_geo_rec_to_suggest(i,i);
6308: END LOOP;
6309:
6310: -- FND Logging for debug purpose
6311: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6312: hz_utility_v2pub.debug
6313: (p_message => 'Moved records from geo_rec_tbl to geo_suggest_tbl',
6314: p_prefix => l_debug_prefix,
6307: move_from_geo_rec_to_suggest(i,i);
6308: END LOOP;
6309:
6310: -- FND Logging for debug purpose
6311: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6312: hz_utility_v2pub.debug
6313: (p_message => 'Moved records from geo_rec_tbl to geo_suggest_tbl',
6314: p_prefix => l_debug_prefix,
6315: p_msg_level => fnd_log.level_statement,
6311: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6312: hz_utility_v2pub.debug
6313: (p_message => 'Moved records from geo_rec_tbl to geo_suggest_tbl',
6314: p_prefix => l_debug_prefix,
6315: p_msg_level => fnd_log.level_statement,
6316: p_module_prefix => l_module_prefix,
6317: p_module => l_module
6318: );
6319: END IF;
6337:
6338: --------------------no geo mapping exist --------------------------+
6339: ELSIF (geo_struct_tbl.COUNT = 0) THEN
6340:
6341: -- FND Logging for debug purpose
6342: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6343: hz_utility_v2pub.debug
6344: (p_message => 'No Mapping Exists for passed country code and '||
6345: 'address style combination (return code = NO_MAPPING_EXISTS)',
6338: --------------------no geo mapping exist --------------------------+
6339: ELSIF (geo_struct_tbl.COUNT = 0) THEN
6340:
6341: -- FND Logging for debug purpose
6342: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6343: hz_utility_v2pub.debug
6344: (p_message => 'No Mapping Exists for passed country code and '||
6345: 'address style combination (return code = NO_MAPPING_EXISTS)',
6346: p_prefix => l_debug_prefix,
6343: hz_utility_v2pub.debug
6344: (p_message => 'No Mapping Exists for passed country code and '||
6345: 'address style combination (return code = NO_MAPPING_EXISTS)',
6346: p_prefix => l_debug_prefix,
6347: p_msg_level => fnd_log.level_statement,
6348: p_module_prefix => l_module_prefix,
6349: p_module => l_module
6350: );
6351: END IF;
6360:
6361: ----------------Validation level is miniumum or none -----------------+
6362: ELSE -- Validation level is MINIMUM or NONE
6363:
6364: -- FND Logging for debug purpose
6365: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6366: hz_utility_v2pub.debug
6367: (p_message => 'Validation Level is :'||l_addr_val_level||
6368: '. Calling procedure process_output_data_proc(+)',
6361: ----------------Validation level is miniumum or none -----------------+
6362: ELSE -- Validation level is MINIMUM or NONE
6363:
6364: -- FND Logging for debug purpose
6365: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6366: hz_utility_v2pub.debug
6367: (p_message => 'Validation Level is :'||l_addr_val_level||
6368: '. Calling procedure process_output_data_proc(+)',
6369: p_prefix => l_debug_prefix,
6366: hz_utility_v2pub.debug
6367: (p_message => 'Validation Level is :'||l_addr_val_level||
6368: '. Calling procedure process_output_data_proc(+)',
6369: p_prefix => l_debug_prefix,
6370: p_msg_level => fnd_log.level_procedure,
6371: p_module_prefix => l_module_prefix,
6372: p_module => l_module
6373: );
6374: END IF;
6374: END IF;
6375:
6376: process_output_data_proc;
6377:
6378: -- FND Logging for debug purpose
6379: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6380: hz_utility_v2pub.debug
6381: (p_message => 'Completed procedure process_output_data_proc(-)',
6382: p_prefix => l_debug_prefix,
6375:
6376: process_output_data_proc;
6377:
6378: -- FND Logging for debug purpose
6379: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6380: hz_utility_v2pub.debug
6381: (p_message => 'Completed procedure process_output_data_proc(-)',
6382: p_prefix => l_debug_prefix,
6383: p_msg_level => fnd_log.level_procedure,
6379: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
6380: hz_utility_v2pub.debug
6381: (p_message => 'Completed procedure process_output_data_proc(-)',
6382: p_prefix => l_debug_prefix,
6383: p_msg_level => fnd_log.level_procedure,
6384: p_module_prefix => l_module_prefix,
6385: p_module => l_module
6386: );
6387: END IF;
6387: END IF;
6388:
6389: END IF;
6390:
6391: -- FND Logging for debug purpose
6392: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6393: hz_utility_v2pub.debug
6394: (p_message => 'END: Address Suggestion API.'||
6395: ' Value of x_return_code='||x_return_code||
6388:
6389: END IF;
6390:
6391: -- FND Logging for debug purpose
6392: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
6393: hz_utility_v2pub.debug
6394: (p_message => 'END: Address Suggestion API.'||
6395: ' Value of x_return_code='||x_return_code||
6396: ', x_records_count='||x_records_count ,
6394: (p_message => 'END: Address Suggestion API.'||
6395: ' Value of x_return_code='||x_return_code||
6396: ', x_records_count='||x_records_count ,
6397: p_prefix => l_debug_prefix,
6398: p_msg_level => fnd_log.level_statement,
6399: p_module_prefix => l_module_prefix,
6400: p_module => l_module
6401: );
6402: END IF;
6403:
6404: EXCEPTION
6405: WHEN OTHERS THEN
6406:
6407: -- FND Logging for debug purpose
6408: IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
6409: hz_utility_v2pub.debug
6410: (p_message => SUBSTR('SYSTEM ERROR occured in Address Suggestion API. '||
6411: SQLERRM, 1,255),
6404: EXCEPTION
6405: WHEN OTHERS THEN
6406:
6407: -- FND Logging for debug purpose
6408: IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
6409: hz_utility_v2pub.debug
6410: (p_message => SUBSTR('SYSTEM ERROR occured in Address Suggestion API. '||
6411: SQLERRM, 1,255),
6412: p_prefix => l_debug_prefix,
6409: hz_utility_v2pub.debug
6410: (p_message => SUBSTR('SYSTEM ERROR occured in Address Suggestion API. '||
6411: SQLERRM, 1,255),
6412: p_prefix => l_debug_prefix,
6413: p_msg_level => fnd_log.level_exception,
6414: p_module_prefix => l_module_prefix,
6415: p_module => l_module
6416: );
6417: END IF;