[Home] [Help]
12: -- PRIVATE PROCEDURE enable_debug
13: -- DESCRIPTION
14: -- Turn on debug mode.
15: -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
16: -- HZ_UTILITY_V2PUB.enable_debug
17: -- MODIFICATION HISTORY
18: -- 01-10-2002 Herve Yu
19: --------------------------------------
20: /*PROCEDURE enable_debug IS
23: IF G_DEBUG_COUNT = 1 THEN
24: IF fnd_profile.value('HZ_API_FILE_DEBUG_ON') = 'Y' OR
25: fnd_profile.value('HZ_API_DBMS_DEBUG_ON') = 'Y'
26: THEN
27: hz_utility_v2pub.enable_debug;
28: g_debug := TRUE;
29: IF fnd_profile.value('HZ_API_FILE_DEBUG_ON') = 'Y' THEN
30: g_file_debug := TRUE;
31: END IF;
38: -- PRIVATE PROCEDURE disable_debug
39: -- DESCRIPTION
40: -- Turn off debug mode.
41: -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
42: -- HZ_UTILITY_V2PUB.disable_debug
43: -- MODIFICATION HISTORY
44: -- 01-10-2002 Herve Yu
45: --------------------------------------
46: /*PROCEDURE disable_debug IS
47: BEGIN
48: IF g_debug THEN
49: g_debug_count := g_debug_count - 1;
50: IF g_debug_count = 0 THEN
51: hz_utility_v2pub.disable_debug;
52: g_debug := FALSE;
53: g_file_debug := FALSE;
54: END IF;
55: END IF;
94: -- DESCRIPTION
95: -- Utility routine for performance testing. Prints the argument with
96: -- a timestamp.
97: -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
98: -- hz_utility_v2pub.debug
99: -- MODIFICATION HISTORY
100: -- 01-10-2002 J. del Callar
101: --------------------------------------
102: /*PROCEDURE time_put_line (msg IN VARCHAR2) IS
105: fnd_file.put_line(fnd_file.log,
106: TO_CHAR(SYSDATE, 'HH:MI:SS') ||
107: ': ' || SUBSTRB(msg, 1, 240));
108: ELSE
109: hz_utility_v2pub.debug(TO_CHAR(SYSDATE, 'HH:MI:SS') ||
110: ': ' || SUBSTRB(msg, 1, 240));
111: END IF;
112: END time_put_line;
113: */
137: FROM hz_locations
138: WHERE location_id = p_location_id;
139: BEGIN
140: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
141: hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.update_geo_location for location_id :'||
142: TO_CHAR(p_location_id)||'(+)',
143: p_prefix=>l_debug_prefix,
144: p_msg_level=>fnd_log.level_procedure);
145: END IF;
150: fnd_message.set_name('AR', 'HZ_NO_LOCATION_FOUND');
151: fnd_message.set_token('LOC_ID', TO_CHAR(p_location_id));
152: fnd_msg_pub.add;
153: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
154: hz_utility_v2pub.debug(p_message=>'No Record found : Error',
155: p_prefix=>'ERROR',
156: p_msg_level=>fnd_log.level_error);
157: END IF;
158: ELSE
156: p_msg_level=>fnd_log.level_error);
157: END IF;
158: ELSE
159: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
160: hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.update_row for location_id :' ||
161: TO_CHAR(p_location_id)||'(+)',
162: p_prefix=>l_debug_prefix,
163: p_msg_level=>fnd_log.level_procedure);
164: END IF;
234:
235: x_count := x_count + 1;
236:
237: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
238: hz_utility_v2pub.debug(p_message=>'Location successfully updated for location_id :'||TO_CHAR(p_location_id),
239: p_prefix =>l_debug_prefix,
240: p_msg_level=>fnd_log.level_statement);
241: END IF;
242: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
239: p_prefix =>l_debug_prefix,
240: p_msg_level=>fnd_log.level_statement);
241: END IF;
242: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
243: hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.update_row for location_id :' ||
244: TO_CHAR(p_location_id)||'(-)',
245: p_prefix=>l_debug_prefix,
246: p_msg_level=>fnd_log.level_procedure);
247: END IF;
260: p_count => x_msg_count,
261: p_data => x_msg_data
262: );
263: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
264: hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.update_geo_location for location_id :'
265: || TO_CHAR(p_location_id)||'(-)',
266: p_prefix=>l_debug_prefix,
267: p_msg_level=>fnd_log.level_procedure);
268: END IF;
273: -- DESCRIPTION
274: -- Synchronized geometry in hz_locations with latitude and longitude from
275: -- Oracle's eLocation service.
276: -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
277: -- hz_utility_v2pub
278: -- fnd_message
279: -- fnd_msg_pub
280: -- MODIFICATION HISTORY
281: -- 01-10-2002 H. Yu Created.
527: atleastonerow EXCEPTION;
528: msg VARCHAR2(2000);
529: l_return_status VARCHAR2(10);
530: l_set_size NUMBER;
531: l_request_id NUMBER := hz_utility_v2pub.request_id;
532: i NUMBER;
533: l_nb_retries NUMBER := NVL(p_nb_try, 3);
534: l_batch_size NUMBER := NVL(p_nb_row, hz_geocode_pkg.g_max_rows);
535: l_retcode VARCHAR2(10);
545: /* l_nb_update := 0;
546: l_set_size := l_batch_size * g_sets_per_commit;
547:
548: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
549: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (+)',
550: p_prefix=>l_debug_prefix,
551: p_msg_level=>fnd_log.level_procedure);
552: END IF;
553:
582: END IF;
583: IF l_nb_row_update IS NULL OR l_nb_row_update <= 0 THEN
584: time_put_line('At least one row error.');
585: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
586: hz_utility_v2pub.debug(p_message=>'At least one row error.',
587: p_prefix=>'ERROR',
588: p_msg_level=>fnd_log.level_error);
589: END IF;
590: RAISE atleastonerow;
599: fnd_profile.get('HZ_GEOCODE_WEBSITE', l_http_ad);
600: IF l_http_ad IS NULL THEN
601: time_put_line('HTTP address missing');
602: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
603: hz_utility_v2pub.debug(p_message=>'HTTP address missing',
604: p_prefix=>'ERROR',
605: p_msg_level=>fnd_log.level_error);
606: END IF;
607: RAISE expect_http_ad;
645: fnd_file.put_line(fnd_file.output, '');
646:
647: -- repeat in debug output.
648: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
649: hz_utility_v2pub.debug(p_message=>'HZ_GEOCODE_WEBSITE: ' || l_http_ad,
650: p_prefix =>l_debug_prefix,
651: p_msg_level=>fnd_log.level_statement);
652: hz_utility_v2pub.debug(p_message=>l_proxy_var || ': ' || l_proxy,
653: p_prefix =>l_debug_prefix,
648: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
649: hz_utility_v2pub.debug(p_message=>'HZ_GEOCODE_WEBSITE: ' || l_http_ad,
650: p_prefix =>l_debug_prefix,
651: p_msg_level=>fnd_log.level_statement);
652: hz_utility_v2pub.debug(p_message=>l_proxy_var || ': ' || l_proxy,
653: p_prefix =>l_debug_prefix,
654: p_msg_level=>fnd_log.level_statement);
655: hz_utility_v2pub.debug(p_message=>l_port_var || ': ' || l_port,
656: p_prefix =>l_debug_prefix,
651: p_msg_level=>fnd_log.level_statement);
652: hz_utility_v2pub.debug(p_message=>l_proxy_var || ': ' || l_proxy,
653: p_prefix =>l_debug_prefix,
654: p_msg_level=>fnd_log.level_statement);
655: hz_utility_v2pub.debug(p_message=>l_port_var || ': ' || l_port,
656: p_prefix =>l_debug_prefix,
657: p_msg_level=>fnd_log.level_statement);
658: END IF;
659:
684: IF(p_country IS NULL) THEN
685: IF NOT cu_loc1a%ISOPEN THEN
686: OPEN cu_loc1a (l_request_id);
687: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
688: hz_utility_v2pub.debug(p_message=>'Opening cursor 1a with args:',
689: p_prefix =>l_debug_prefix,
690: p_msg_level=>fnd_log.level_statement);
691: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
692: p_prefix =>l_debug_prefix,
687: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
688: hz_utility_v2pub.debug(p_message=>'Opening cursor 1a with args:',
689: p_prefix =>l_debug_prefix,
690: p_msg_level=>fnd_log.level_statement);
691: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
692: p_prefix =>l_debug_prefix,
693: p_msg_level=>fnd_log.level_statement);
694: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
695: p_prefix =>l_debug_prefix,
690: p_msg_level=>fnd_log.level_statement);
691: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
692: p_prefix =>l_debug_prefix,
693: p_msg_level=>fnd_log.level_statement);
694: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
695: p_prefix =>l_debug_prefix,
696: p_msg_level=>fnd_log.level_statement);
697: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
698: p_prefix =>l_debug_prefix,
693: p_msg_level=>fnd_log.level_statement);
694: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
695: p_prefix =>l_debug_prefix,
696: p_msg_level=>fnd_log.level_statement);
697: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
698: p_prefix =>l_debug_prefix,
699: p_msg_level=>fnd_log.level_statement);
700: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
701: p_prefix =>l_debug_prefix,
696: p_msg_level=>fnd_log.level_statement);
697: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
698: p_prefix =>l_debug_prefix,
699: p_msg_level=>fnd_log.level_statement);
700: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
701: p_prefix =>l_debug_prefix,
702: p_msg_level=>fnd_log.level_statement);
703: END IF;
704: END IF;
713: IF cu_loc1a%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
714: time_put_line('Exiting because of NOTFOUND condition');
715: time_put_line('Count=' || l_location_ids.COUNT);
716: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
717: hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
718: p_prefix=>'WARNING',
719: p_msg_level=>fnd_log.level_exception);
720: hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
721: p_prefix=>'WARNING',
716: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
717: hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
718: p_prefix=>'WARNING',
719: p_msg_level=>fnd_log.level_exception);
720: hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
721: p_prefix=>'WARNING',
722: p_msg_level=>fnd_log.level_exception);
723: END IF;
724: EXIT;
726: ELSE -- cu_loc1a/cu_loc1ac check p_country is null or not
727: IF NOT cu_loc1ac%ISOPEN THEN
728: OPEN cu_loc1ac (l_request_id);
729: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
730: hz_utility_v2pub.debug(p_message=>'Opening cursor 1ac with args:',
731: p_prefix =>l_debug_prefix,
732: p_msg_level=>fnd_log.level_statement);
733: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
734: p_prefix =>l_debug_prefix,
729: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
730: hz_utility_v2pub.debug(p_message=>'Opening cursor 1ac with args:',
731: p_prefix =>l_debug_prefix,
732: p_msg_level=>fnd_log.level_statement);
733: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
734: p_prefix =>l_debug_prefix,
735: p_msg_level=>fnd_log.level_statement);
736: hz_utility_v2pub.debug(p_message=>'country=' || p_country,
737: p_prefix =>l_debug_prefix,
732: p_msg_level=>fnd_log.level_statement);
733: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
734: p_prefix =>l_debug_prefix,
735: p_msg_level=>fnd_log.level_statement);
736: hz_utility_v2pub.debug(p_message=>'country=' || p_country,
737: p_prefix =>l_debug_prefix,
738: p_msg_level=>fnd_log.level_statement);
739: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
740: p_prefix =>l_debug_prefix,
735: p_msg_level=>fnd_log.level_statement);
736: hz_utility_v2pub.debug(p_message=>'country=' || p_country,
737: p_prefix =>l_debug_prefix,
738: p_msg_level=>fnd_log.level_statement);
739: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
740: p_prefix =>l_debug_prefix,
741: p_msg_level=>fnd_log.level_statement);
742: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
743: p_prefix =>l_debug_prefix,
738: p_msg_level=>fnd_log.level_statement);
739: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
740: p_prefix =>l_debug_prefix,
741: p_msg_level=>fnd_log.level_statement);
742: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
743: p_prefix =>l_debug_prefix,
744: p_msg_level=>fnd_log.level_statement);
745: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
746: p_prefix =>l_debug_prefix,
741: p_msg_level=>fnd_log.level_statement);
742: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
743: p_prefix =>l_debug_prefix,
744: p_msg_level=>fnd_log.level_statement);
745: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
746: p_prefix =>l_debug_prefix,
747: p_msg_level=>fnd_log.level_statement);
748: END IF;
749: END IF;
758: IF cu_loc1ac%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
759: time_put_line('Exiting because of NOTFOUND condition');
760: time_put_line('Count=' || l_location_ids.COUNT);
761: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
762: hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
763: p_prefix=>'WARNING',
764: p_msg_level=>fnd_log.level_exception);
765: hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
766: p_prefix=>'WARNING',
761: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
762: hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
763: p_prefix=>'WARNING',
764: p_msg_level=>fnd_log.level_exception);
765: hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
766: p_prefix=>'WARNING',
767: p_msg_level=>fnd_log.level_exception);
768: END IF;
769: EXIT;
776: IF(p_country IS NULL) THEN
777: IF NOT cu_loc1b%ISOPEN THEN
778: OPEN cu_loc1b (l_request_id);
779: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
780: hz_utility_v2pub.debug(p_message=>'Opening cursor 1b with args:',
781: p_prefix =>l_debug_prefix,
782: p_msg_level=>fnd_log.level_statement);
783: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
784: p_prefix =>l_debug_prefix,
779: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
780: hz_utility_v2pub.debug(p_message=>'Opening cursor 1b with args:',
781: p_prefix =>l_debug_prefix,
782: p_msg_level=>fnd_log.level_statement);
783: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
784: p_prefix =>l_debug_prefix,
785: p_msg_level=>fnd_log.level_statement);
786: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
787: p_prefix =>l_debug_prefix,
782: p_msg_level=>fnd_log.level_statement);
783: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
784: p_prefix =>l_debug_prefix,
785: p_msg_level=>fnd_log.level_statement);
786: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
787: p_prefix =>l_debug_prefix,
788: p_msg_level=>fnd_log.level_statement);
789: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
790: p_prefix =>l_debug_prefix,
785: p_msg_level=>fnd_log.level_statement);
786: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
787: p_prefix =>l_debug_prefix,
788: p_msg_level=>fnd_log.level_statement);
789: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
790: p_prefix =>l_debug_prefix,
791: p_msg_level=>fnd_log.level_statement);
792: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
793: p_prefix =>l_debug_prefix,
788: p_msg_level=>fnd_log.level_statement);
789: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
790: p_prefix =>l_debug_prefix,
791: p_msg_level=>fnd_log.level_statement);
792: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
793: p_prefix =>l_debug_prefix,
794: p_msg_level=>fnd_log.level_statement);
795: END IF;
796: END IF;
805: IF cu_loc1b%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
806: time_put_line('Exiting because of NOTFOUND condition');
807: time_put_line('Count=' || l_location_ids.COUNT);
808: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
809: hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
810: p_prefix=>'WARNING',
811: p_msg_level=>fnd_log.level_exception);
812: hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
813: p_prefix=>'WARNING',
808: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
809: hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
810: p_prefix=>'WARNING',
811: p_msg_level=>fnd_log.level_exception);
812: hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
813: p_prefix=>'WARNING',
814: p_msg_level=>fnd_log.level_exception);
815: END IF;
816: EXIT;
818: ELSE -- cu_loc1b/cu_loc1bc, check if p_country is null or not
819: IF NOT cu_loc1bc%ISOPEN THEN
820: OPEN cu_loc1bc (l_request_id);
821: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
822: hz_utility_v2pub.debug(p_message=>'Opening cursor 1bc with args:',
823: p_prefix =>l_debug_prefix,
824: p_msg_level=>fnd_log.level_statement);
825: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
826: p_prefix =>l_debug_prefix,
821: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
822: hz_utility_v2pub.debug(p_message=>'Opening cursor 1bc with args:',
823: p_prefix =>l_debug_prefix,
824: p_msg_level=>fnd_log.level_statement);
825: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
826: p_prefix =>l_debug_prefix,
827: p_msg_level=>fnd_log.level_statement);
828: hz_utility_v2pub.debug(p_message=>'country=' || p_country,
829: p_prefix =>l_debug_prefix,
824: p_msg_level=>fnd_log.level_statement);
825: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
826: p_prefix =>l_debug_prefix,
827: p_msg_level=>fnd_log.level_statement);
828: hz_utility_v2pub.debug(p_message=>'country=' || p_country,
829: p_prefix =>l_debug_prefix,
830: p_msg_level=>fnd_log.level_statement);
831: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
832: p_prefix =>l_debug_prefix,
827: p_msg_level=>fnd_log.level_statement);
828: hz_utility_v2pub.debug(p_message=>'country=' || p_country,
829: p_prefix =>l_debug_prefix,
830: p_msg_level=>fnd_log.level_statement);
831: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
832: p_prefix =>l_debug_prefix,
833: p_msg_level=>fnd_log.level_statement);
834: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
835: p_prefix =>l_debug_prefix,
830: p_msg_level=>fnd_log.level_statement);
831: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
832: p_prefix =>l_debug_prefix,
833: p_msg_level=>fnd_log.level_statement);
834: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
835: p_prefix =>l_debug_prefix,
836: p_msg_level=>fnd_log.level_statement);
837: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
838: p_prefix =>l_debug_prefix,
833: p_msg_level=>fnd_log.level_statement);
834: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
835: p_prefix =>l_debug_prefix,
836: p_msg_level=>fnd_log.level_statement);
837: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
838: p_prefix =>l_debug_prefix,
839: p_msg_level=>fnd_log.level_statement);
840: END IF;
841: END IF;
850: IF cu_loc1bc%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
851: time_put_line('Exiting because of NOTFOUND condition');
852: time_put_line('Count=' || l_location_ids.COUNT);
853: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
854: hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
855: p_prefix=>'WARNING',
856: p_msg_level=>fnd_log.level_exception);
857: hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
858: p_prefix=>'WARNING',
853: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
854: hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
855: p_prefix=>'WARNING',
856: p_msg_level=>fnd_log.level_exception);
857: hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
858: p_prefix=>'WARNING',
859: p_msg_level=>fnd_log.level_exception);
860: END IF;
861: EXIT;
868: IF(p_country IS NULL) THEN
869: IF NOT cu_loc2a%ISOPEN THEN
870: OPEN cu_loc2a (l_request_id);
871: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
872: hz_utility_v2pub.debug(p_message=>'Opening cursor 2a with args:',
873: p_prefix =>l_debug_prefix,
874: p_msg_level=>fnd_log.level_statement);
875: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
876: p_prefix =>l_debug_prefix,
871: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
872: hz_utility_v2pub.debug(p_message=>'Opening cursor 2a with args:',
873: p_prefix =>l_debug_prefix,
874: p_msg_level=>fnd_log.level_statement);
875: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
876: p_prefix =>l_debug_prefix,
877: p_msg_level=>fnd_log.level_statement);
878: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
879: p_prefix =>l_debug_prefix,
874: p_msg_level=>fnd_log.level_statement);
875: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
876: p_prefix =>l_debug_prefix,
877: p_msg_level=>fnd_log.level_statement);
878: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
879: p_prefix =>l_debug_prefix,
880: p_msg_level=>fnd_log.level_statement);
881: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
882: p_prefix =>l_debug_prefix,
877: p_msg_level=>fnd_log.level_statement);
878: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
879: p_prefix =>l_debug_prefix,
880: p_msg_level=>fnd_log.level_statement);
881: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
882: p_prefix =>l_debug_prefix,
883: p_msg_level=>fnd_log.level_statement);
884: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
885: p_prefix =>l_debug_prefix,
880: p_msg_level=>fnd_log.level_statement);
881: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
882: p_prefix =>l_debug_prefix,
883: p_msg_level=>fnd_log.level_statement);
884: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
885: p_prefix =>l_debug_prefix,
886: p_msg_level=>fnd_log.level_statement);
887: END IF;
888: END IF;
897: IF cu_loc2a%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
898: time_put_line('Exiting because of NOTFOUND condition');
899: time_put_line('Count=' || l_location_ids.COUNT);
900: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
901: hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
902: p_prefix=>'WARNING',
903: p_msg_level=>fnd_log.level_exception);
904: hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
905: p_prefix=>'WARNING',
900: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
901: hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
902: p_prefix=>'WARNING',
903: p_msg_level=>fnd_log.level_exception);
904: hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
905: p_prefix=>'WARNING',
906: p_msg_level=>fnd_log.level_exception);
907: END IF;
908: EXIT;
910: ELSE -- cu_loc2a/cu_loc2ac, check if p_country is null or not
911: IF NOT cu_loc2ac%ISOPEN THEN
912: OPEN cu_loc2ac (l_request_id);
913: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
914: hz_utility_v2pub.debug(p_message=>'Opening cursor 2ac with args:',
915: p_prefix =>l_debug_prefix,
916: p_msg_level=>fnd_log.level_statement);
917: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
918: p_prefix =>l_debug_prefix,
913: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
914: hz_utility_v2pub.debug(p_message=>'Opening cursor 2ac with args:',
915: p_prefix =>l_debug_prefix,
916: p_msg_level=>fnd_log.level_statement);
917: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
918: p_prefix =>l_debug_prefix,
919: p_msg_level=>fnd_log.level_statement);
920: hz_utility_v2pub.debug(p_message=>'country=' || p_country,
921: p_prefix =>l_debug_prefix,
916: p_msg_level=>fnd_log.level_statement);
917: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
918: p_prefix =>l_debug_prefix,
919: p_msg_level=>fnd_log.level_statement);
920: hz_utility_v2pub.debug(p_message=>'country=' || p_country,
921: p_prefix =>l_debug_prefix,
922: p_msg_level=>fnd_log.level_statement);
923: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
924: p_prefix =>l_debug_prefix,
919: p_msg_level=>fnd_log.level_statement);
920: hz_utility_v2pub.debug(p_message=>'country=' || p_country,
921: p_prefix =>l_debug_prefix,
922: p_msg_level=>fnd_log.level_statement);
923: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
924: p_prefix =>l_debug_prefix,
925: p_msg_level=>fnd_log.level_statement);
926: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
927: p_prefix =>l_debug_prefix,
922: p_msg_level=>fnd_log.level_statement);
923: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
924: p_prefix =>l_debug_prefix,
925: p_msg_level=>fnd_log.level_statement);
926: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
927: p_prefix =>l_debug_prefix,
928: p_msg_level=>fnd_log.level_statement);
929: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
930: p_prefix =>l_debug_prefix,
925: p_msg_level=>fnd_log.level_statement);
926: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
927: p_prefix =>l_debug_prefix,
928: p_msg_level=>fnd_log.level_statement);
929: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
930: p_prefix =>l_debug_prefix,
931: p_msg_level=>fnd_log.level_statement);
932: END IF;
933: END IF;
942: IF cu_loc2ac%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
943: time_put_line('Exiting because of NOTFOUND condition');
944: time_put_line('Count=' || l_location_ids.COUNT);
945: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
946: hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
947: p_prefix=>'WARNING',
948: p_msg_level=>fnd_log.level_exception);
949: hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
950: p_prefix=>'WARNING',
945: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
946: hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
947: p_prefix=>'WARNING',
948: p_msg_level=>fnd_log.level_exception);
949: hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
950: p_prefix=>'WARNING',
951: p_msg_level=>fnd_log.level_exception);
952: END IF;
953: EXIT;
960: IF(p_country IS NULL) THEN
961: IF NOT cu_loc2b%ISOPEN THEN
962: OPEN cu_loc2b (l_request_id);
963: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
964: hz_utility_v2pub.debug(p_message=>'Opening cursor 2b with args:',
965: p_prefix =>l_debug_prefix,
966: p_msg_level=>fnd_log.level_statement);
967: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
968: p_prefix =>l_debug_prefix,
963: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
964: hz_utility_v2pub.debug(p_message=>'Opening cursor 2b with args:',
965: p_prefix =>l_debug_prefix,
966: p_msg_level=>fnd_log.level_statement);
967: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
968: p_prefix =>l_debug_prefix,
969: p_msg_level=>fnd_log.level_statement);
970: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
971: p_prefix =>l_debug_prefix,
966: p_msg_level=>fnd_log.level_statement);
967: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
968: p_prefix =>l_debug_prefix,
969: p_msg_level=>fnd_log.level_statement);
970: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
971: p_prefix =>l_debug_prefix,
972: p_msg_level=>fnd_log.level_statement);
973: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
974: p_prefix =>l_debug_prefix,
969: p_msg_level=>fnd_log.level_statement);
970: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
971: p_prefix =>l_debug_prefix,
972: p_msg_level=>fnd_log.level_statement);
973: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
974: p_prefix =>l_debug_prefix,
975: p_msg_level=>fnd_log.level_statement);
976: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
977: p_prefix =>l_debug_prefix,
972: p_msg_level=>fnd_log.level_statement);
973: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
974: p_prefix =>l_debug_prefix,
975: p_msg_level=>fnd_log.level_statement);
976: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
977: p_prefix =>l_debug_prefix,
978: p_msg_level=>fnd_log.level_statement);
979: END IF;
980: END IF;
989: IF cu_loc2b%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
990: time_put_line('Exiting because of NOTFOUND condition');
991: time_put_line('Count=' || l_location_ids.COUNT);
992: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
993: hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
994: p_prefix=>'WARNING',
995: p_msg_level=>fnd_log.level_exception);
996: hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
997: p_prefix=>'WARNING',
992: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
993: hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
994: p_prefix=>'WARNING',
995: p_msg_level=>fnd_log.level_exception);
996: hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
997: p_prefix=>'WARNING',
998: p_msg_level=>fnd_log.level_exception);
999: END IF;
1000: EXIT;
1002: ELSE -- cu_loc2b/cu_loc2bc, check if p_country is null or not
1003: IF NOT cu_loc2bc%ISOPEN THEN
1004: OPEN cu_loc2bc (l_request_id);
1005: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1006: hz_utility_v2pub.debug(p_message=>'Opening cursor 2bc with args:',
1007: p_prefix =>l_debug_prefix,
1008: p_msg_level=>fnd_log.level_statement);
1009: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
1010: p_prefix =>l_debug_prefix,
1005: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1006: hz_utility_v2pub.debug(p_message=>'Opening cursor 2bc with args:',
1007: p_prefix =>l_debug_prefix,
1008: p_msg_level=>fnd_log.level_statement);
1009: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
1010: p_prefix =>l_debug_prefix,
1011: p_msg_level=>fnd_log.level_statement);
1012: hz_utility_v2pub.debug(p_message=>'country=' || p_country,
1013: p_prefix =>l_debug_prefix,
1008: p_msg_level=>fnd_log.level_statement);
1009: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
1010: p_prefix =>l_debug_prefix,
1011: p_msg_level=>fnd_log.level_statement);
1012: hz_utility_v2pub.debug(p_message=>'country=' || p_country,
1013: p_prefix =>l_debug_prefix,
1014: p_msg_level=>fnd_log.level_statement);
1015: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
1016: p_prefix =>l_debug_prefix,
1011: p_msg_level=>fnd_log.level_statement);
1012: hz_utility_v2pub.debug(p_message=>'country=' || p_country,
1013: p_prefix =>l_debug_prefix,
1014: p_msg_level=>fnd_log.level_statement);
1015: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
1016: p_prefix =>l_debug_prefix,
1017: p_msg_level=>fnd_log.level_statement);
1018: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
1019: p_prefix =>l_debug_prefix,
1014: p_msg_level=>fnd_log.level_statement);
1015: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
1016: p_prefix =>l_debug_prefix,
1017: p_msg_level=>fnd_log.level_statement);
1018: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
1019: p_prefix =>l_debug_prefix,
1020: p_msg_level=>fnd_log.level_statement);
1021: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
1022: p_prefix =>l_debug_prefix,
1017: p_msg_level=>fnd_log.level_statement);
1018: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
1019: p_prefix =>l_debug_prefix,
1020: p_msg_level=>fnd_log.level_statement);
1021: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
1022: p_prefix =>l_debug_prefix,
1023: p_msg_level=>fnd_log.level_statement);
1024: END IF;
1025: END IF;
1034: IF cu_loc2bc%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
1035: time_put_line('Exiting because of NOTFOUND condition');
1036: time_put_line('Count=' || l_location_ids.COUNT);
1037: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1038: hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
1039: p_prefix=>'WARNING',
1040: p_msg_level=>fnd_log.level_exception);
1041: hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
1042: p_prefix=>'WARNING',
1037: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1038: hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
1039: p_prefix=>'WARNING',
1040: p_msg_level=>fnd_log.level_exception);
1041: hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
1042: p_prefix=>'WARNING',
1043: p_msg_level=>fnd_log.level_exception);
1044: END IF;
1045: EXIT;
1058: IF(p_country IS NULL) THEN
1059: IF NOT cu_loc3a%ISOPEN THEN
1060: OPEN cu_loc3a (l_request_id);
1061: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1062: hz_utility_v2pub.debug(p_message=>'Opening cursor 3a with args:',
1063: p_prefix =>l_debug_prefix,
1064: p_msg_level=>fnd_log.level_statement);
1065: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
1066: p_prefix =>l_debug_prefix,
1061: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1062: hz_utility_v2pub.debug(p_message=>'Opening cursor 3a with args:',
1063: p_prefix =>l_debug_prefix,
1064: p_msg_level=>fnd_log.level_statement);
1065: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
1066: p_prefix =>l_debug_prefix,
1067: p_msg_level=>fnd_log.level_statement);
1068: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
1069: p_prefix =>l_debug_prefix,
1064: p_msg_level=>fnd_log.level_statement);
1065: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
1066: p_prefix =>l_debug_prefix,
1067: p_msg_level=>fnd_log.level_statement);
1068: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
1069: p_prefix =>l_debug_prefix,
1070: p_msg_level=>fnd_log.level_statement);
1071: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
1072: p_prefix =>l_debug_prefix,
1067: p_msg_level=>fnd_log.level_statement);
1068: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
1069: p_prefix =>l_debug_prefix,
1070: p_msg_level=>fnd_log.level_statement);
1071: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
1072: p_prefix =>l_debug_prefix,
1073: p_msg_level=>fnd_log.level_statement);
1074: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
1075: p_prefix =>l_debug_prefix,
1070: p_msg_level=>fnd_log.level_statement);
1071: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
1072: p_prefix =>l_debug_prefix,
1073: p_msg_level=>fnd_log.level_statement);
1074: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
1075: p_prefix =>l_debug_prefix,
1076: p_msg_level=>fnd_log.level_statement);
1077: hz_utility_v2pub.debug(p_message=>'loc_type=' || NVL(p_loc_type, 'NULL'),
1078: p_prefix =>l_debug_prefix,
1073: p_msg_level=>fnd_log.level_statement);
1074: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
1075: p_prefix =>l_debug_prefix,
1076: p_msg_level=>fnd_log.level_statement);
1077: hz_utility_v2pub.debug(p_message=>'loc_type=' || NVL(p_loc_type, 'NULL'),
1078: p_prefix =>l_debug_prefix,
1079: p_msg_level=>fnd_log.level_statement);
1080: END IF;
1081: END IF;
1090: IF cu_loc3a%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
1091: time_put_line('Exiting because of NOTFOUND condition');
1092: time_put_line('Count=' || l_location_ids.COUNT);
1093: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1094: hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
1095: p_prefix=>'WARNING',
1096: p_msg_level=>fnd_log.level_exception);
1097: hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
1098: p_prefix=>'WARNING',
1093: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1094: hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
1095: p_prefix=>'WARNING',
1096: p_msg_level=>fnd_log.level_exception);
1097: hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
1098: p_prefix=>'WARNING',
1099: p_msg_level=>fnd_log.level_exception);
1100: END IF;
1101: EXIT;
1103: ELSE -- cu_loc3a/cu_loc3ac, check if p_country is null or not
1104: IF NOT cu_loc3ac%ISOPEN THEN
1105: OPEN cu_loc3ac (l_request_id);
1106: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1107: hz_utility_v2pub.debug(p_message=>'Opening cursor 3ac with args:',
1108: p_prefix =>l_debug_prefix,
1109: p_msg_level=>fnd_log.level_statement);
1110: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
1111: p_prefix =>l_debug_prefix,
1106: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1107: hz_utility_v2pub.debug(p_message=>'Opening cursor 3ac with args:',
1108: p_prefix =>l_debug_prefix,
1109: p_msg_level=>fnd_log.level_statement);
1110: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
1111: p_prefix =>l_debug_prefix,
1112: p_msg_level=>fnd_log.level_statement);
1113: hz_utility_v2pub.debug(p_message=>'country=' || p_country,
1114: p_prefix =>l_debug_prefix,
1109: p_msg_level=>fnd_log.level_statement);
1110: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
1111: p_prefix =>l_debug_prefix,
1112: p_msg_level=>fnd_log.level_statement);
1113: hz_utility_v2pub.debug(p_message=>'country=' || p_country,
1114: p_prefix =>l_debug_prefix,
1115: p_msg_level=>fnd_log.level_statement);
1116: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
1117: p_prefix =>l_debug_prefix,
1112: p_msg_level=>fnd_log.level_statement);
1113: hz_utility_v2pub.debug(p_message=>'country=' || p_country,
1114: p_prefix =>l_debug_prefix,
1115: p_msg_level=>fnd_log.level_statement);
1116: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
1117: p_prefix =>l_debug_prefix,
1118: p_msg_level=>fnd_log.level_statement);
1119: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
1120: p_prefix =>l_debug_prefix,
1115: p_msg_level=>fnd_log.level_statement);
1116: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
1117: p_prefix =>l_debug_prefix,
1118: p_msg_level=>fnd_log.level_statement);
1119: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
1120: p_prefix =>l_debug_prefix,
1121: p_msg_level=>fnd_log.level_statement);
1122: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
1123: p_prefix =>l_debug_prefix,
1118: p_msg_level=>fnd_log.level_statement);
1119: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
1120: p_prefix =>l_debug_prefix,
1121: p_msg_level=>fnd_log.level_statement);
1122: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
1123: p_prefix =>l_debug_prefix,
1124: p_msg_level=>fnd_log.level_statement);
1125: hz_utility_v2pub.debug(p_message=>'loc_type=' || NVL(p_loc_type, 'NULL'),
1126: p_prefix =>l_debug_prefix,
1121: p_msg_level=>fnd_log.level_statement);
1122: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
1123: p_prefix =>l_debug_prefix,
1124: p_msg_level=>fnd_log.level_statement);
1125: hz_utility_v2pub.debug(p_message=>'loc_type=' || NVL(p_loc_type, 'NULL'),
1126: p_prefix =>l_debug_prefix,
1127: p_msg_level=>fnd_log.level_statement);
1128: END IF;
1129: END IF;
1138: IF cu_loc3ac%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
1139: time_put_line('Exiting because of NOTFOUND condition');
1140: time_put_line('Count=' || l_location_ids.COUNT);
1141: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1142: hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
1143: p_prefix=>'WARNING',
1144: p_msg_level=>fnd_log.level_exception);
1145: hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
1146: p_prefix=>'WARNING',
1141: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1142: hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
1143: p_prefix=>'WARNING',
1144: p_msg_level=>fnd_log.level_exception);
1145: hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
1146: p_prefix=>'WARNING',
1147: p_msg_level=>fnd_log.level_exception);
1148: END IF;
1149: END IF;
1156: IF(p_country IS NULL) THEN
1157: IF NOT cu_loc3b%ISOPEN THEN
1158: OPEN cu_loc3b (l_request_id);
1159: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1160: hz_utility_v2pub.debug(p_message=>'Opening cursor 3b with args:',
1161: p_prefix =>l_debug_prefix,
1162: p_msg_level=>fnd_log.level_statement);
1163: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
1164: p_prefix =>l_debug_prefix,
1159: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1160: hz_utility_v2pub.debug(p_message=>'Opening cursor 3b with args:',
1161: p_prefix =>l_debug_prefix,
1162: p_msg_level=>fnd_log.level_statement);
1163: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
1164: p_prefix =>l_debug_prefix,
1165: p_msg_level=>fnd_log.level_statement);
1166: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
1167: p_prefix =>l_debug_prefix,
1162: p_msg_level=>fnd_log.level_statement);
1163: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
1164: p_prefix =>l_debug_prefix,
1165: p_msg_level=>fnd_log.level_statement);
1166: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
1167: p_prefix =>l_debug_prefix,
1168: p_msg_level=>fnd_log.level_statement);
1169: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
1170: p_prefix =>l_debug_prefix,
1165: p_msg_level=>fnd_log.level_statement);
1166: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
1167: p_prefix =>l_debug_prefix,
1168: p_msg_level=>fnd_log.level_statement);
1169: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
1170: p_prefix =>l_debug_prefix,
1171: p_msg_level=>fnd_log.level_statement);
1172: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
1173: p_prefix =>l_debug_prefix,
1168: p_msg_level=>fnd_log.level_statement);
1169: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
1170: p_prefix =>l_debug_prefix,
1171: p_msg_level=>fnd_log.level_statement);
1172: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
1173: p_prefix =>l_debug_prefix,
1174: p_msg_level=>fnd_log.level_statement);
1175: hz_utility_v2pub.debug(p_message=>'loc_type=' || NVL(p_loc_type, 'NULL'),
1176: p_prefix =>l_debug_prefix,
1171: p_msg_level=>fnd_log.level_statement);
1172: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
1173: p_prefix =>l_debug_prefix,
1174: p_msg_level=>fnd_log.level_statement);
1175: hz_utility_v2pub.debug(p_message=>'loc_type=' || NVL(p_loc_type, 'NULL'),
1176: p_prefix =>l_debug_prefix,
1177: p_msg_level=>fnd_log.level_statement);
1178: END IF;
1179: END IF;
1188: IF cu_loc3b%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
1189: time_put_line('Exiting because of NOTFOUND condition');
1190: time_put_line('Count=' || l_location_ids.COUNT);
1191: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1192: hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
1193: p_prefix=>'WARNING',
1194: p_msg_level=>fnd_log.level_exception);
1195: hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
1196: p_prefix=>'WARNING',
1191: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1192: hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
1193: p_prefix=>'WARNING',
1194: p_msg_level=>fnd_log.level_exception);
1195: hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
1196: p_prefix=>'WARNING',
1197: p_msg_level=>fnd_log.level_exception);
1198: END IF;
1199: EXIT;
1201: ELSE -- cu_loc3b/cu_loc3bc, check if p_country is null or not
1202: IF NOT cu_loc3bc%ISOPEN THEN
1203: OPEN cu_loc3bc (l_request_id);
1204: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1205: hz_utility_v2pub.debug(p_message=>'Opening cursor 3bc with args:',
1206: p_prefix =>l_debug_prefix,
1207: p_msg_level=>fnd_log.level_statement);
1208: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
1209: p_prefix =>l_debug_prefix,
1204: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1205: hz_utility_v2pub.debug(p_message=>'Opening cursor 3bc with args:',
1206: p_prefix =>l_debug_prefix,
1207: p_msg_level=>fnd_log.level_statement);
1208: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
1209: p_prefix =>l_debug_prefix,
1210: p_msg_level=>fnd_log.level_statement);
1211: hz_utility_v2pub.debug(p_message=>'country=' || p_country,
1212: p_prefix =>l_debug_prefix,
1207: p_msg_level=>fnd_log.level_statement);
1208: hz_utility_v2pub.debug(p_message=>'site_use_type=' || NVL(p_site_use_type,'NULL'),
1209: p_prefix =>l_debug_prefix,
1210: p_msg_level=>fnd_log.level_statement);
1211: hz_utility_v2pub.debug(p_message=>'country=' || p_country,
1212: p_prefix =>l_debug_prefix,
1213: p_msg_level=>fnd_log.level_statement);
1214: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
1215: p_prefix =>l_debug_prefix,
1210: p_msg_level=>fnd_log.level_statement);
1211: hz_utility_v2pub.debug(p_message=>'country=' || p_country,
1212: p_prefix =>l_debug_prefix,
1213: p_msg_level=>fnd_log.level_statement);
1214: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
1215: p_prefix =>l_debug_prefix,
1216: p_msg_level=>fnd_log.level_statement);
1217: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
1218: p_prefix =>l_debug_prefix,
1213: p_msg_level=>fnd_log.level_statement);
1214: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
1215: p_prefix =>l_debug_prefix,
1216: p_msg_level=>fnd_log.level_statement);
1217: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
1218: p_prefix =>l_debug_prefix,
1219: p_msg_level=>fnd_log.level_statement);
1220: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
1221: p_prefix =>l_debug_prefix,
1216: p_msg_level=>fnd_log.level_statement);
1217: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
1218: p_prefix =>l_debug_prefix,
1219: p_msg_level=>fnd_log.level_statement);
1220: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
1221: p_prefix =>l_debug_prefix,
1222: p_msg_level=>fnd_log.level_statement);
1223: hz_utility_v2pub.debug(p_message=>'loc_type=' || NVL(p_loc_type, 'NULL'),
1224: p_prefix =>l_debug_prefix,
1219: p_msg_level=>fnd_log.level_statement);
1220: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, 'NULL'),
1221: p_prefix =>l_debug_prefix,
1222: p_msg_level=>fnd_log.level_statement);
1223: hz_utility_v2pub.debug(p_message=>'loc_type=' || NVL(p_loc_type, 'NULL'),
1224: p_prefix =>l_debug_prefix,
1225: p_msg_level=>fnd_log.level_statement);
1226: END IF;
1227: END IF;
1236: IF cu_loc3bc%NOTFOUND AND l_location_ids.COUNT <= 0 THEN
1237: time_put_line('Exiting because of NOTFOUND condition');
1238: time_put_line('Count=' || l_location_ids.COUNT);
1239: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1240: hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
1241: p_prefix=>'WARNING',
1242: p_msg_level=>fnd_log.level_exception);
1243: hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
1244: p_prefix=>'WARNING',
1239: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1240: hz_utility_v2pub.debug(p_message=>'Exiting because of NOTFOUND condition',
1241: p_prefix=>'WARNING',
1242: p_msg_level=>fnd_log.level_exception);
1243: hz_utility_v2pub.debug(p_message=>'Count=' || l_location_ids.COUNT,
1244: p_prefix=>'WARNING',
1245: p_msg_level=>fnd_log.level_exception);
1246: END IF;
1247: END IF;
1251: ELSE
1252: l_return_status := fnd_api.g_ret_sts_unexp_error;
1253: time_put_line('Unexpected mode encountered');
1254: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1255: hz_utility_v2pub.debug(p_message=>'Unexpected mode encountered',
1256: p_prefix=>'ERROR',
1257: p_msg_level=>fnd_log.level_error);
1258: END IF;
1259: END IF;
1260: -- J. del Callar: exit the loop if no records were fetched the first time
1261: IF l_location_ids.COUNT = 0 THEN
1262: time_put_line('Exiting because COUNT=0');
1263: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1264: hz_utility_v2pub.debug(p_message=>'Exiting because COUNT=0',
1265: p_prefix=>'WARNING',
1266: p_msg_level=>fnd_log.level_exception);
1267: END IF;
1268: EXIT;
1273: time_put_line('Exiting because partial=' || p_all_partial);
1274: time_put_line('cpt_update=' || cpt_update);
1275: time_put_line('nb_row_update=' || l_nb_row_update);
1276: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1277: hz_utility_v2pub.debug(p_message=>'Exiting because partial=' || p_all_partial,
1278: p_prefix=>'WARNING',
1279: p_msg_level=>fnd_log.level_exception);
1280: hz_utility_v2pub.debug(p_message=>'cpt_update=' || cpt_update,
1281: p_prefix=>'WARNING',
1276: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1277: hz_utility_v2pub.debug(p_message=>'Exiting because partial=' || p_all_partial,
1278: p_prefix=>'WARNING',
1279: p_msg_level=>fnd_log.level_exception);
1280: hz_utility_v2pub.debug(p_message=>'cpt_update=' || cpt_update,
1281: p_prefix=>'WARNING',
1282: p_msg_level=>fnd_log.level_exception);
1283: hz_utility_v2pub.debug(p_message=>'nb_row_update=' || l_nb_row_update,
1284: p_prefix=>'WARNING',
1279: p_msg_level=>fnd_log.level_exception);
1280: hz_utility_v2pub.debug(p_message=>'cpt_update=' || cpt_update,
1281: p_prefix=>'WARNING',
1282: p_msg_level=>fnd_log.level_exception);
1283: hz_utility_v2pub.debug(p_message=>'nb_row_update=' || l_nb_row_update,
1284: p_prefix=>'WARNING',
1285: p_msg_level=>fnd_log.level_exception);
1286: END IF;
1287: EXIT;
1308:
1309: fnd_file.put_line(fnd_file.log,
1310: 'Processing location '||l_array(cpt).location_id);
1311: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1312: hz_utility_v2pub.debug(p_message=>'Processing location ' || l_array(cpt).location_id,
1313: p_prefix =>l_debug_prefix,
1314: p_msg_level=>fnd_log.level_statement);
1315: END IF;
1316:
1336:
1337: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1338: time_put_line('Unexpected error encountered');
1339: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1340: hz_utility_v2pub.debug(p_message=>'Unexpected error encountered',
1341: p_prefix=>'ERROR',
1342: p_msg_level=>fnd_log.level_error);
1343: END IF;
1344:
1436: -- J. del Callar: exit the loop if our update limit has been exceeded.
1437: IF p_all_partial <> 'ALL' AND cpt_update >= l_nb_row_update THEN
1438: time_put_line('Exiting due to update limit.');
1439: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1440: hz_utility_v2pub.debug(p_message=>'Exiting due to update limit.',
1441: p_prefix=>'WARNING',
1442: p_msg_level=>fnd_log.level_exception);
1443:
1444: END IF;
1627: fnd_message.get_string('FND',
1628: 'CONC-CP SUCCESSFUL TERMINATION'));
1629:
1630: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1631: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (-)',
1632: p_prefix=>l_debug_prefix,
1633: p_msg_level=>fnd_log.level_procedure);
1634: END IF;
1635:
1645: trace_handler(msg);
1646: END LOOP;
1647: fnd_message.clear;
1648: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1649: hz_utility_v2pub.debug(p_message=>g_pkg_name ||'.update_geometry (- expect_http_ad)',
1650: p_prefix=>'ERROR',
1651: p_msg_level=>fnd_log.level_error);
1652: END IF;
1653: --disable_debug;
1664: trace_handler(msg);
1665: END LOOP;
1666: fnd_message.clear;
1667: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1668: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (- port_number)',
1669: p_prefix=>'ERROR',
1670: p_msg_level=>fnd_log.level_error);
1671: END IF;
1672: --disable_debug;
1682: trace_handler(msg);
1683: END LOOP;
1684: fnd_message.clear;
1685: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1686: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (- nlsnumexp)',
1687: p_prefix=>'ERROR',
1688: p_msg_level=>fnd_log.level_error);
1689: END IF;
1690: --disable_debug;
1700: trace_handler(msg);
1701: END LOOP;
1702: fnd_message.clear;
1703: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1704: hz_utility_v2pub.debug(p_message=>g_pkg_name ||'.update_geometry (- atleastonerow)',
1705: p_prefix=>'ERROR',
1706: p_msg_level=>fnd_log.level_error);
1707: END IF;
1708: --disable_debug;
1719: trace_handler(msg);
1720: END LOOP;
1721: fnd_message.clear;
1722: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1723: hz_utility_v2pub.debug(p_message=>g_pkg_name ||'.update_geometry (- morethanmaxrow)',
1724: p_prefix=>'ERROR',
1725: p_msg_level=>fnd_log.level_error);
1726: END IF;
1727: --disable_debug;
1735: trace_handler(msg);
1736: END LOOP;
1737: fnd_message.clear;
1738: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1739: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (- exchttp)',
1740: p_prefix=>'ERROR',
1741: p_msg_level=>fnd_log.level_error);
1742: END IF;
1743: --disable_debug;
1747: WHEN OTHERS THEN
1748: fnd_file.put_line(fnd_file.log, SQLERRM);
1749: msg := SQLERRM;
1750: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1751: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (- others)',
1752: p_prefix=>'SQL ERROR',
1753: p_msg_level=>fnd_log.level_error);
1754: hz_utility_v2pub.debug(p_message=>'msg='||SUBSTRB(msg, 1, 250),
1755: p_prefix=>'SQL ERROR',
1750: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1751: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (- others)',
1752: p_prefix=>'SQL ERROR',
1753: p_msg_level=>fnd_log.level_error);
1754: hz_utility_v2pub.debug(p_message=>'msg='||SUBSTRB(msg, 1, 250),
1755: p_prefix=>'SQL ERROR',
1756: p_msg_level=>fnd_log.level_error);
1757: END IF;
1758: --disable_debug;
1915: -- DESCRIPTION
1916: -- Synchronized geometry in hz_locations with latitude,longitude and accuracy from
1917: -- Oracle's eLocation service.
1918: -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1919: -- hz_utility_v2pub
1920: -- fnd_message
1921: -- fnd_msg_pub
1922: -- MODIFICATION HISTORY
1923: -- 29-11-2010 Sudhir Gokavarapu Created - ER10211310
1988: atleastonerow EXCEPTION;
1989: msg VARCHAR2(2000);
1990: l_return_status VARCHAR2(10);
1991: l_set_size NUMBER;
1992: l_request_id NUMBER := hz_utility_v2pub.request_id;
1993: i NUMBER;
1994: l_nb_retries NUMBER := NVL(p_nb_try, 3);
1995: l_batch_size NUMBER := NVL(p_nb_row, hz_geocode_pkg.g_max_rows);
1996: l_retcode VARCHAR2(10);
2024: l_nb_update := 0;
2025: l_set_size := l_batch_size * g_sets_per_commit;
2026:
2027: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2028: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry_all (+)',
2029: p_prefix=>l_debug_prefix,
2030: p_msg_level=>fnd_log.level_procedure);
2031: END IF;
2032:
2059: END IF;
2060: IF l_nb_row_update IS NULL OR l_nb_row_update <= 0 THEN
2061: time_put_line('At least one row error.');
2062: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2063: hz_utility_v2pub.debug(p_message=>'At least one row error.',
2064: p_prefix=>'ERROR',
2065: p_msg_level=>fnd_log.level_error);
2066: END IF;
2067: RAISE atleastonerow;
2076: fnd_profile.get('HZ_GEOCODE_WEBSITE', l_http_ad);
2077: IF l_http_ad IS NULL THEN
2078: time_put_line('HTTP address missing');
2079: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2080: hz_utility_v2pub.debug(p_message=>'HTTP address missing',
2081: p_prefix=>'ERROR',
2082: p_msg_level=>fnd_log.level_error);
2083: END IF;
2084: RAISE expect_http_ad;
2123: fnd_file.put_line(fnd_file.output, '');
2124:
2125: -- repeat in debug output.
2126: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2127: hz_utility_v2pub.debug(p_message=>'HZ_GEOCODE_WEBSITE: ' || l_http_ad||' '||l_proxy_var || ': ' || l_proxy||' '||
2128: l_port_var || ': ' || l_port,
2129: p_prefix =>l_debug_prefix,
2130: p_msg_level=>fnd_log.level_statement);
2131: END IF;
2146: IF p_loc_type IS NOT NULL AND p_loc_type <> 'P' AND p_loc_type <> 'S' THEN
2147: l_return_status := fnd_api.g_ret_sts_unexp_error;
2148: time_put_line('Unexpected mode encountered');
2149: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2150: hz_utility_v2pub.debug(p_message=>'Unexpected mode encountered Invalid Location Type Value',
2151: p_prefix=>'ERROR',
2152: p_msg_level=>fnd_log.level_error);
2153: END IF;
2154: END IF;
2152: p_msg_level=>fnd_log.level_error);
2153: END IF;
2154: END IF;
2155: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2156: hz_utility_v2pub.debug(p_message=>'p_nb_row :'||p_nb_row||' p_nb_try :'||p_nb_try||' p_nb_row_update :'||p_nb_row_update||' p_country :'||p_country
2157: ||' p_state :'||p_state||' p_province :'||p_province||' p_city :'||p_city||' p_geometry_accuracy :'||p_geometry_accuracy
2158: ||' p_geometry_source :'||p_geometry_source||' p_incremental :'||p_incremental
2159: ||' p_loc_type :'||p_loc_type||' p_site_use_type :'||p_site_use_type,
2160: p_prefix=>l_debug_prefix,
2227:
2228: l_qry_str := l_qry_str ||' ORDER BY hl.location_id ';
2229:
2230: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2231: hz_utility_v2pub.debug(p_message=>'Dynamic Query 1 :'||l_qry_str,
2232: p_prefix=>l_debug_prefix,
2233: p_msg_level=>fnd_log.level_procedure);
2234: END IF;
2235: --fnd_file.put_line(fnd_file.output,'Query '||l_qry_str);
2244: INTO l_location_ids, l_address1s, l_address2s, l_address3s, l_address4s,
2245: l_cities, l_postal_codes, l_states, l_countries, l_counties, l_provinces
2246: LIMIT l_set_size;
2247: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2248: hz_utility_v2pub.debug(p_message=>'Record Count ' || l_location_ids.COUNT,
2249: p_prefix =>l_debug_prefix,
2250: p_msg_level=>fnd_log.level_statement);
2251: END IF;
2252:
2257: -- Exit the loop if no records were fetched the first time
2258: IF l_rec_cnt = 0 THEN
2259: time_put_line('Exiting because Record Count is ZERO');
2260: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
2261: hz_utility_v2pub.debug(p_message=>'Exiting because COUNT=0',
2262: p_prefix=>'WARNING',
2263: p_msg_level=>fnd_log.level_exception);
2264: END IF;
2265: END IF;
2274: time_put_line('Exiting because partial=' || p_all_partial);
2275: time_put_line('cpt_update=' || cpt_update);
2276: time_put_line('nb_row_update=' || l_nb_row_update);
2277: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
2278: hz_utility_v2pub.debug(p_message=>'Exiting because partial=' || p_all_partial||' cpt_update=' || cpt_update
2279: ||' nb_row_update=' || l_nb_row_update,
2280: p_prefix=>'WARNING',
2281: p_msg_level=>fnd_log.level_exception);
2282: END IF;
2303: l_array(cpt).county := l_counties(i);
2304: --Commented as log file format is changed.
2305: -- fnd_file.put_line(fnd_file.log,'Processing location '||l_array(cpt).location_id);
2306: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2307: hz_utility_v2pub.debug(p_message=>'Processing location ' || l_array(cpt).location_id,
2308: p_prefix =>l_debug_prefix,
2309: p_msg_level=>fnd_log.level_statement);
2310: END IF;
2311:
2331: -- When Unexpected Error Occurs
2332: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2333: time_put_line('Unexpected error encountered');
2334: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2335: hz_utility_v2pub.debug(p_message=>'Unexpected error encountered',
2336: p_prefix=>'ERROR',
2337: p_msg_level=>fnd_log.level_error);
2338: END IF;
2339: -- Close cursor:
2373: SET geometry = NULL,
2374: geometry_status_code = l_array(j).geometry_status_code,
2375: geometry_source = 'ELOCATION',
2376: geometry_accuracy = l_array(j).geometry_accuracy,
2377: last_update_date = hz_utility_v2pub.last_update_date,
2378: last_updated_by = hz_utility_v2pub.last_updated_by,
2379: last_update_login = hz_utility_v2pub.last_update_login,
2380: request_id = l_request_id,
2381: program_application_id = hz_utility_v2pub.program_application_id,
2374: geometry_status_code = l_array(j).geometry_status_code,
2375: geometry_source = 'ELOCATION',
2376: geometry_accuracy = l_array(j).geometry_accuracy,
2377: last_update_date = hz_utility_v2pub.last_update_date,
2378: last_updated_by = hz_utility_v2pub.last_updated_by,
2379: last_update_login = hz_utility_v2pub.last_update_login,
2380: request_id = l_request_id,
2381: program_application_id = hz_utility_v2pub.program_application_id,
2382: program_id = hz_utility_v2pub.program_id,
2375: geometry_source = 'ELOCATION',
2376: geometry_accuracy = l_array(j).geometry_accuracy,
2377: last_update_date = hz_utility_v2pub.last_update_date,
2378: last_updated_by = hz_utility_v2pub.last_updated_by,
2379: last_update_login = hz_utility_v2pub.last_update_login,
2380: request_id = l_request_id,
2381: program_application_id = hz_utility_v2pub.program_application_id,
2382: program_id = hz_utility_v2pub.program_id,
2383: program_update_date = hz_utility_v2pub.program_update_date
2377: last_update_date = hz_utility_v2pub.last_update_date,
2378: last_updated_by = hz_utility_v2pub.last_updated_by,
2379: last_update_login = hz_utility_v2pub.last_update_login,
2380: request_id = l_request_id,
2381: program_application_id = hz_utility_v2pub.program_application_id,
2382: program_id = hz_utility_v2pub.program_id,
2383: program_update_date = hz_utility_v2pub.program_update_date
2384: WHERE location_id = l_array(j).location_id;
2385: ELSE
2378: last_updated_by = hz_utility_v2pub.last_updated_by,
2379: last_update_login = hz_utility_v2pub.last_update_login,
2380: request_id = l_request_id,
2381: program_application_id = hz_utility_v2pub.program_application_id,
2382: program_id = hz_utility_v2pub.program_id,
2383: program_update_date = hz_utility_v2pub.program_update_date
2384: WHERE location_id = l_array(j).location_id;
2385: ELSE
2386: UPDATE HZ_LOCATIONS
2379: last_update_login = hz_utility_v2pub.last_update_login,
2380: request_id = l_request_id,
2381: program_application_id = hz_utility_v2pub.program_application_id,
2382: program_id = hz_utility_v2pub.program_id,
2383: program_update_date = hz_utility_v2pub.program_update_date
2384: WHERE location_id = l_array(j).location_id;
2385: ELSE
2386: UPDATE HZ_LOCATIONS
2387: SET geometry = l_array(j).geometry,
2387: SET geometry = l_array(j).geometry,
2388: geometry_status_code = l_array(j).geometry_status_code,
2389: geometry_source = 'ELOCATION',
2390: geometry_accuracy = l_array(j).geometry_accuracy,
2391: last_update_date = hz_utility_v2pub.last_update_date,
2392: last_updated_by = hz_utility_v2pub.last_updated_by,
2393: last_update_login = hz_utility_v2pub.last_update_login,
2394: request_id = l_request_id,
2395: program_application_id = hz_utility_v2pub.program_application_id,
2388: geometry_status_code = l_array(j).geometry_status_code,
2389: geometry_source = 'ELOCATION',
2390: geometry_accuracy = l_array(j).geometry_accuracy,
2391: last_update_date = hz_utility_v2pub.last_update_date,
2392: last_updated_by = hz_utility_v2pub.last_updated_by,
2393: last_update_login = hz_utility_v2pub.last_update_login,
2394: request_id = l_request_id,
2395: program_application_id = hz_utility_v2pub.program_application_id,
2396: program_id = hz_utility_v2pub.program_id,
2389: geometry_source = 'ELOCATION',
2390: geometry_accuracy = l_array(j).geometry_accuracy,
2391: last_update_date = hz_utility_v2pub.last_update_date,
2392: last_updated_by = hz_utility_v2pub.last_updated_by,
2393: last_update_login = hz_utility_v2pub.last_update_login,
2394: request_id = l_request_id,
2395: program_application_id = hz_utility_v2pub.program_application_id,
2396: program_id = hz_utility_v2pub.program_id,
2397: program_update_date = hz_utility_v2pub.program_update_date
2391: last_update_date = hz_utility_v2pub.last_update_date,
2392: last_updated_by = hz_utility_v2pub.last_updated_by,
2393: last_update_login = hz_utility_v2pub.last_update_login,
2394: request_id = l_request_id,
2395: program_application_id = hz_utility_v2pub.program_application_id,
2396: program_id = hz_utility_v2pub.program_id,
2397: program_update_date = hz_utility_v2pub.program_update_date
2398: WHERE location_id = l_array(j).location_id;
2399: END IF;
2392: last_updated_by = hz_utility_v2pub.last_updated_by,
2393: last_update_login = hz_utility_v2pub.last_update_login,
2394: request_id = l_request_id,
2395: program_application_id = hz_utility_v2pub.program_application_id,
2396: program_id = hz_utility_v2pub.program_id,
2397: program_update_date = hz_utility_v2pub.program_update_date
2398: WHERE location_id = l_array(j).location_id;
2399: END IF;
2400: --Increment Accuracy count varaibles these values will be used
2393: last_update_login = hz_utility_v2pub.last_update_login,
2394: request_id = l_request_id,
2395: program_application_id = hz_utility_v2pub.program_application_id,
2396: program_id = hz_utility_v2pub.program_id,
2397: program_update_date = hz_utility_v2pub.program_update_date
2398: WHERE location_id = l_array(j).location_id;
2399: END IF;
2400: --Increment Accuracy count varaibles these values will be used
2401: --in Log file summary generation.
2444: -- Exit the loop if our update limit has been exceeded.
2445: IF p_all_partial <> 'ALL' AND cpt_update >= l_nb_row_update THEN
2446: time_put_line('Exiting due to update limit.');
2447: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
2448: hz_utility_v2pub.debug(p_message=>'Exiting due to update limit.',
2449: p_prefix=>'WARNING',
2450: p_msg_level=>fnd_log.level_exception);
2451:
2452: END IF;
2468: l_states.DELETE;
2469: l_provinces.DELETE;
2470: l_countries.DELETE;
2471: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
2472: hz_utility_v2pub.debug(p_message=>'Processed Record Count Accuracy 1-'||l_mc_1||' 2-'||l_mc_2||' 3-'||l_mc_3 ||' 4-'||
2473: l_mc_4 ||' 10-'||l_mc_10 ||' 11-'||l_mc_11 ||' 0-'||l_mc_0,
2474: p_prefix=>l_debug_prefix,
2475: p_msg_level=>fnd_log.level_exception);
2476:
2536: fnd_message.get_string('FND',
2537: 'CONC-CP SUCCESSFUL TERMINATION'));
2538:
2539: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2540: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry (-)',
2541: p_prefix=>l_debug_prefix,
2542: p_msg_level=>fnd_log.level_procedure);
2543: END IF;
2544:
2542: p_msg_level=>fnd_log.level_procedure);
2543: END IF;
2544:
2545: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2546: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry_all (-)',
2547: p_prefix=>l_debug_prefix,
2548: p_msg_level=>fnd_log.level_procedure);
2549: END IF;
2550:
2558: trace_handler(msg);
2559: END LOOP;
2560: fnd_message.clear;
2561: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2562: hz_utility_v2pub.debug(p_message=>g_pkg_name ||'.update_geometry_all (- expect_http_ad)',
2563: p_prefix=>'ERROR',
2564: p_msg_level=>fnd_log.level_error);
2565: END IF;
2566: --disable_debug;
2577: trace_handler(msg);
2578: END LOOP;
2579: fnd_message.clear;
2580: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2581: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry_all (- port_number)',
2582: p_prefix=>'ERROR',
2583: p_msg_level=>fnd_log.level_error);
2584: END IF;
2585: --disable_debug;
2595: trace_handler(msg);
2596: END LOOP;
2597: fnd_message.clear;
2598: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2599: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry_all (- nlsnumexp)',
2600: p_prefix=>'ERROR',
2601: p_msg_level=>fnd_log.level_error);
2602: END IF;
2603: --disable_debug;
2613: trace_handler(msg);
2614: END LOOP;
2615: fnd_message.clear;
2616: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2617: hz_utility_v2pub.debug(p_message=>g_pkg_name ||'.update_geometry_all (- atleastonerow)',
2618: p_prefix=>'ERROR',
2619: p_msg_level=>fnd_log.level_error);
2620: END IF;
2621: --disable_debug;
2632: trace_handler(msg);
2633: END LOOP;
2634: fnd_message.clear;
2635: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2636: hz_utility_v2pub.debug(p_message=>g_pkg_name ||'.update_geometry_all (- morethanmaxrow)',
2637: p_prefix=>'ERROR',
2638: p_msg_level=>fnd_log.level_error);
2639: END IF;
2640: --disable_debug;
2648: trace_handler(msg);
2649: END LOOP;
2650: fnd_message.clear;
2651: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2652: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry_all (- exchttp)',
2653: p_prefix=>'ERROR',
2654: p_msg_level=>fnd_log.level_error);
2655: END IF;
2656: --disable_debug;
2660: WHEN OTHERS THEN
2661: fnd_file.put_line(fnd_file.log, SQLERRM);
2662: msg := SQLERRM;
2663: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2664: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry_all (- others)',
2665: p_prefix=>'SQL ERROR',
2666: p_msg_level=>fnd_log.level_error);
2667: hz_utility_v2pub.debug(p_message=>'msg='||SUBSTRB(msg, 1, 250),
2668: p_prefix=>'SQL ERROR',
2663: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2664: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.update_geometry_all (- others)',
2665: p_prefix=>'SQL ERROR',
2666: p_msg_level=>fnd_log.level_error);
2667: hz_utility_v2pub.debug(p_message=>'msg='||SUBSTRB(msg, 1, 250),
2668: p_prefix=>'SQL ERROR',
2669: p_msg_level=>fnd_log.level_error);
2670: END IF;
2671: --disable_debug;
2681: -- Rebuilds the spatial index on HZ_LOCATIONS.GEOMETRY. Rebuilding the
2682: -- spatial index is required so that the index performs adequately and
2683: -- that queries can accurately extract the spatial data.
2684: -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2685: -- hz_utility_v2pub
2686: -- fnd_message
2687: -- fnd_msg_pub
2688: -- ARGUMENTS
2689: -- IN:
2812: -- Create Index
2813: Create_Index;
2814: ELSE /* Index Exists and Valid */
2815: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2816: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.rebuild_location_index (+)',
2817: p_prefix=>l_debug_prefix,
2818: p_msg_level=>fnd_log.level_procedure);
2819: END IF;
2820: -- Initialize the return code only if we are running as an independent
2828: l_rebuild_string := 'ALTER INDEX ' || x_index_owner ||'.' ||'HZ_LOCATIONS_N15 REBUILD ' ||
2829: 'PARAMETERS(''sdo_commit_interval=' ||g_commit_interval || ''')';
2830:
2831: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2832: hz_utility_v2pub.debug(p_message=>'rebuilding with:' || l_rebuild_string,
2833: p_prefix =>l_debug_prefix,
2834: p_msg_level=>fnd_log.level_statement);
2835: END IF;
2836: EXECUTE IMMEDIATE l_rebuild_string;
2854: fnd_message.set_name('AR', 'HZ_GEO_INDEX_REBUILT');
2855: fnd_msg_pub.add;
2856: END IF;
2857: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2858: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.rebuild_location_index (-)',
2859: p_prefix=>l_debug_prefix,
2860: p_msg_level=>fnd_log.level_procedure);
2861: END IF;
2862: Close c_index;
2885: ELSE
2886: fnd_msg_pub.add;
2887: END IF;
2888: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2889: hz_utility_v2pub.debug(p_message=>g_pkg_name||' Error:',
2890: p_prefix=>'SQL ERROR',
2891: p_msg_level=>fnd_log.level_error);
2892: hz_utility_v2pub.debug(p_message=>SQLERRM,
2893: p_prefix=>'SQL ERROR',
2888: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2889: hz_utility_v2pub.debug(p_message=>g_pkg_name||' Error:',
2890: p_prefix=>'SQL ERROR',
2891: p_msg_level=>fnd_log.level_error);
2892: hz_utility_v2pub.debug(p_message=>SQLERRM,
2893: p_prefix=>'SQL ERROR',
2894: p_msg_level=>fnd_log.level_error);
2895: END IF;
2896: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2893: p_prefix=>'SQL ERROR',
2894: p_msg_level=>fnd_log.level_error);
2895: END IF;
2896: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2897: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.rebuild_location_index (-)',
2898: p_prefix=>l_debug_prefix,
2899: p_msg_level=>fnd_log.level_procedure);
2900: END IF;
2901: -- bug fix 3872778 enable policy function