[Home] [Help]
136: SELECT rowid
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);
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;
146: OPEN curowid;
147: FETCH curowid INTO l_rowid;
148: IF curowid%NOTFOUND THEN
149: x_return_status := fnd_api.g_ret_sts_error;
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;
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
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 :' ||
155: p_prefix=>'ERROR',
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);
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;
165: hz_locations_pkg.update_row (
166: x_rowid => l_rowid,
167: x_location_id => p_location_id,
233: );
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;
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
243: hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.update_row for location_id :' ||
244: TO_CHAR(p_location_id)||'(-)',
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
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);
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;
248:
249: -- Fix perf bug 3669930, 4220460, cache profile option value into global variable
250: IF g_cp_detail = 'Y' THEN
259: p_encoded => fnd_api.g_false,
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);
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;
269: END update_geo_location;
270:
271: --------------------------------------
544:
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;
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:
554:
555:
581: l_nb_row_update := TO_NUMBER(p_nb_row_update);
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;
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;
591: END IF;
592: END IF;
598: -- Get the website we're supposed to access for geospatial information.
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;
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;
608: END IF;
609:
644: fnd_file.put_line(fnd_file.output, l_port_var || ': ' || l_port);
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,
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,
654: p_msg_level=>fnd_log.level_statement);
655: hz_utility_v2pub.debug(p_message=>l_port_var || ': ' || l_port,
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,
657: p_msg_level=>fnd_log.level_statement);
658: END IF;
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:
660: IF l_port IS NOT NULL THEN
661: -- J. del Callar: set the port number and handle non-numeric values
683: -- and non-incremental mode.
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'),
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,
693: p_msg_level=>fnd_log.level_statement);
694: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
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,
696: p_msg_level=>fnd_log.level_statement);
697: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), '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,
699: p_msg_level=>fnd_log.level_statement);
700: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, '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,
702: p_msg_level=>fnd_log.level_statement);
703: END IF;
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;
705:
706: -- J. del Callar: fetch the next set of location information.
712: -- J. del Callar: exit the loop if we've processed all records
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,
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',
722: p_msg_level=>fnd_log.level_exception);
723: END IF;
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;
725: END IF;
726: ELSE -- cu_loc1a/cu_loc1ac check p_country is null or not
725: END IF;
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'),
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,
735: p_msg_level=>fnd_log.level_statement);
736: hz_utility_v2pub.debug(p_message=>'country=' || p_country,
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,
738: p_msg_level=>fnd_log.level_statement);
739: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'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,
741: p_msg_level=>fnd_log.level_statement);
742: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
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,
744: p_msg_level=>fnd_log.level_statement);
745: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, '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,
747: p_msg_level=>fnd_log.level_statement);
748: END IF;
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;
750:
751: -- J. del Callar: fetch the next set of location information.
757: -- J. del Callar: exit the loop if we've processed all records
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,
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',
767: p_msg_level=>fnd_log.level_exception);
768: END IF;
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;
770: END IF;
771: END IF; -- cu_loc1a/cu_loc1ac check p_country is null or not
775: -- and incremental mode.
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'),
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,
785: p_msg_level=>fnd_log.level_statement);
786: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
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,
788: p_msg_level=>fnd_log.level_statement);
789: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), '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,
791: p_msg_level=>fnd_log.level_statement);
792: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, '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,
794: p_msg_level=>fnd_log.level_statement);
795: END IF;
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;
797:
798: -- J. del Callar: fetch the next set of location information.
804: -- J. del Callar: exit the loop if we've processed all records
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,
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',
814: p_msg_level=>fnd_log.level_exception);
815: END IF;
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;
817: END IF;
818: ELSE -- cu_loc1b/cu_loc1bc, check if p_country is null or not
817: END IF;
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'),
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,
827: p_msg_level=>fnd_log.level_statement);
828: hz_utility_v2pub.debug(p_message=>'country=' || p_country,
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,
830: p_msg_level=>fnd_log.level_statement);
831: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'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,
833: p_msg_level=>fnd_log.level_statement);
834: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
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,
836: p_msg_level=>fnd_log.level_statement);
837: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, '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,
839: p_msg_level=>fnd_log.level_statement);
840: END IF;
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;
842:
843: -- J. del Callar: fetch the next set of location information.
849: -- J. del Callar: exit the loop if we've processed all records
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,
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',
859: p_msg_level=>fnd_log.level_exception);
860: END IF;
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;
862: END IF;
863: END IF; -- cu_loc1b/cu_loc1bc, check if p_country is null or not
867: -- and non-incremental mode.
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'),
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,
877: p_msg_level=>fnd_log.level_statement);
878: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
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,
880: p_msg_level=>fnd_log.level_statement);
881: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), '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,
883: p_msg_level=>fnd_log.level_statement);
884: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, '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,
886: p_msg_level=>fnd_log.level_statement);
887: END IF;
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;
889:
890: -- J. del Callar: fetch the next set of location information.
896: -- J. del Callar: exit the loop if we've processed all records
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,
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',
906: p_msg_level=>fnd_log.level_exception);
907: END IF;
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;
909: END IF;
910: ELSE -- cu_loc2a/cu_loc2ac, check if p_country is null or not
909: END IF;
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'),
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,
919: p_msg_level=>fnd_log.level_statement);
920: hz_utility_v2pub.debug(p_message=>'country=' || p_country,
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,
922: p_msg_level=>fnd_log.level_statement);
923: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'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,
925: p_msg_level=>fnd_log.level_statement);
926: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
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,
928: p_msg_level=>fnd_log.level_statement);
929: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, '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,
931: p_msg_level=>fnd_log.level_statement);
932: END IF;
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;
934:
935: -- J. del Callar: fetch the next set of location information.
941: -- J. del Callar: exit the loop if we've processed all records
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,
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',
951: p_msg_level=>fnd_log.level_exception);
952: END IF;
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;
954: END IF;
955: END IF; -- cu_loc2a/cu_loc2ac, check if p_country is null or not
959: -- and incremental mode.
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'),
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,
969: p_msg_level=>fnd_log.level_statement);
970: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
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,
972: p_msg_level=>fnd_log.level_statement);
973: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), '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,
975: p_msg_level=>fnd_log.level_statement);
976: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, '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,
978: p_msg_level=>fnd_log.level_statement);
979: END IF;
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;
981:
982: -- J. del Callar: fetch the next set of location information.
988: -- J. del Callar: exit the loop if we've processed all records
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,
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',
998: p_msg_level=>fnd_log.level_exception);
999: END IF;
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;
1001: END IF;
1002: ELSE -- cu_loc2b/cu_loc2bc, check if p_country is null or not
1001: END IF;
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'),
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,
1011: p_msg_level=>fnd_log.level_statement);
1012: hz_utility_v2pub.debug(p_message=>'country=' || p_country,
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,
1014: p_msg_level=>fnd_log.level_statement);
1015: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'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,
1017: p_msg_level=>fnd_log.level_statement);
1018: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
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,
1020: p_msg_level=>fnd_log.level_statement);
1021: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, '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,
1023: p_msg_level=>fnd_log.level_statement);
1024: END IF;
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;
1026:
1027: -- J. del Callar: fetch the next set of location information.
1033: -- J. del Callar: exit the loop if we've processed all records
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,
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',
1043: p_msg_level=>fnd_log.level_exception);
1044: END IF;
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;
1046: END IF;
1047: END IF; -- cu_loc2b/cu_loc2bc, check if p_country is null or not
1057:
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'),
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,
1067: p_msg_level=>fnd_log.level_statement);
1068: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
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,
1070: p_msg_level=>fnd_log.level_statement);
1071: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), '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,
1073: p_msg_level=>fnd_log.level_statement);
1074: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, '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,
1076: p_msg_level=>fnd_log.level_statement);
1077: hz_utility_v2pub.debug(p_message=>'loc_type=' || NVL(p_loc_type, '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,
1079: p_msg_level=>fnd_log.level_statement);
1080: END IF;
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;
1082:
1083: -- swbhatna: fetch the next set of location information.
1089: -- swbhatna: exit the loop if we've processed all records
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,
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',
1099: p_msg_level=>fnd_log.level_exception);
1100: END IF;
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;
1102: END IF;
1103: ELSE -- cu_loc3a/cu_loc3ac, check if p_country is null or not
1102: END IF;
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'),
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,
1112: p_msg_level=>fnd_log.level_statement);
1113: hz_utility_v2pub.debug(p_message=>'country=' || p_country,
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,
1115: p_msg_level=>fnd_log.level_statement);
1116: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'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,
1118: p_msg_level=>fnd_log.level_statement);
1119: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
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,
1121: p_msg_level=>fnd_log.level_statement);
1122: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, '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,
1124: p_msg_level=>fnd_log.level_statement);
1125: hz_utility_v2pub.debug(p_message=>'loc_type=' || NVL(p_loc_type, '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,
1127: p_msg_level=>fnd_log.level_statement);
1128: END IF;
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;
1130:
1131: -- swbhatna: fetch the next set of location information.
1137: -- swbhatna: exit the loop if we've processed all records
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,
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',
1147: p_msg_level=>fnd_log.level_exception);
1148: END IF;
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;
1150: END IF; -- cu_loc3a/cu_loc3ac, check if p_country is null or not
1151:
1155:
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'),
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,
1165: p_msg_level=>fnd_log.level_statement);
1166: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'NULL'),
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,
1168: p_msg_level=>fnd_log.level_statement);
1169: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), '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,
1171: p_msg_level=>fnd_log.level_statement);
1172: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, '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,
1174: p_msg_level=>fnd_log.level_statement);
1175: hz_utility_v2pub.debug(p_message=>'loc_type=' || NVL(p_loc_type, '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,
1177: p_msg_level=>fnd_log.level_statement);
1178: END IF;
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;
1180:
1181: -- swbhatna: fetch the next set of location information.
1187: -- swbhatna: exit the loop if we've processed all records
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,
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',
1197: p_msg_level=>fnd_log.level_exception);
1198: END IF;
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;
1200: END IF;
1201: ELSE -- cu_loc3b/cu_loc3bc, check if p_country is null or not
1200: END IF;
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'),
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,
1210: p_msg_level=>fnd_log.level_statement);
1211: hz_utility_v2pub.debug(p_message=>'country=' || p_country,
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,
1213: p_msg_level=>fnd_log.level_statement);
1214: hz_utility_v2pub.debug(p_message=>'iden_addr_only=' || NVL(p_iden_addr_only,'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,
1216: p_msg_level=>fnd_log.level_statement);
1217: hz_utility_v2pub.debug(p_message=>'request_id=' || NVL(TO_CHAR(l_request_id), 'NULL'),
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,
1219: p_msg_level=>fnd_log.level_statement);
1220: hz_utility_v2pub.debug(p_message=>'incremental=' || NVL(p_incremental, '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,
1222: p_msg_level=>fnd_log.level_statement);
1223: hz_utility_v2pub.debug(p_message=>'loc_type=' || NVL(p_loc_type, '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,
1225: p_msg_level=>fnd_log.level_statement);
1226: END IF;
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;
1228:
1229: -- swbhatna: fetch the next set of location information.
1235: -- swbhatna: exit the loop if we've processed all records
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,
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',
1245: p_msg_level=>fnd_log.level_exception);
1246: END IF;
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;
1248: END IF; -- cu_loc3b/cu_loc3bc, check if p_country is null or not
1249: END IF;
1250:
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;
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
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;
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;
1269: END IF;
1270:
1272: IF p_all_partial <> 'ALL' AND cpt_update >= l_nb_row_update THEN
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,
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',
1282: p_msg_level=>fnd_log.level_exception);
1283: hz_utility_v2pub.debug(p_message=>'nb_row_update=' || l_nb_row_update,
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',
1285: p_msg_level=>fnd_log.level_exception);
1286: END IF;
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;
1288: END IF;
1289:
1307: l_array(cpt).county := l_counties(i);
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;
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:
1317: -- execute the synchronization routine every l_batch_size, or
1318: -- if we have reached the last record to be updated, or
1335: );
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;
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:
1345: -- Close the open cursor (depends on the site use type and the
1346: -- update mode).
1435:
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:
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;
1445: EXIT;
1446: END IF;
1626: fnd_file.put_line(fnd_file.log,
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;
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:
1636: --disable_debug;
1637:
1644: fnd_global.local_chr(10);
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;
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;
1654: retcode := '2';
1655: errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
1663: fnd_global.local_chr(10);
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;
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;
1673: retcode := '2';
1674: errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
1681: fnd_global.local_chr(10);
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;
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;
1691: retcode := '2';
1692: errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
1699: fnd_global.local_chr(10);
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;
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;
1709: retcode := '2';
1710: errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
1718: fnd_global.local_chr(10);
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;
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;
1728: retcode := '2';
1729: errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
1734: fnd_global.local_chr(10);
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;
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;
1744: retcode := '2';
1745: errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
1746:
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),
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',
1756: p_msg_level=>fnd_log.level_error);
1757: END IF;
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;
1759: retcode := '2';
1760: errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
2023:
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;
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:
2033: x_return_status := fnd_api.g_ret_sts_success;
2034: l_return_status := fnd_api.g_ret_sts_success;
2058: l_nb_row_update := TO_NUMBER(p_nb_row_update);
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;
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;
2068: END IF;
2069: END IF;
2075: -- Get the website we're supposed to access for geospatial information.
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;
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;
2085: END IF;
2086:
2122: fnd_file.put_line(fnd_file.output, l_port_var || ': ' || l_port);
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);
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;
2132:
2133: IF l_port IS NOT NULL THEN
2134: -- J. del Callar: set the port number and handle non-numeric values
2145: --Location Type is having invalid value.
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;
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;
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
2151: p_prefix=>'ERROR',
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,
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,
2161: p_msg_level=>fnd_log.level_procedure);
2162: END IF;
2163:
2164: -- Dynamic SQL statement
2165: l_qry_str := 'SELECT hl.location_id, hl.address1, hl.address2, hl.address3, hl.address4, hl.city, ';
2226: END IF;
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;
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);
2236: --fnd_file.put_line(fnd_file.log,'Query '||l_qry_str);
2237:
2243: FETCH l_Loc_geo_cursor BULK COLLECT
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;
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:
2253: IF l_rec_cnt = 0 THEN
2254: l_rec_cnt := l_location_ids.COUNT;
2256:
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;
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;
2266:
2267: -- Exit the loop if no records were fetched
2273: IF p_all_partial <> 'ALL' AND cpt_update >= l_nb_row_update THEN
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);
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;
2283: EXIT;
2284: END IF;
2285:
2302: l_array(cpt).province := l_provinces(i);
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;
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:
2312: -- execute the synchronization routine every l_batch_size, or
2313: -- if we have reached the last record to be updated, or
2330: );
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;
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:
2340: CLOSE l_Loc_geo_cursor;
2341: status_handler(l_return_status, x_return_status);
2443:
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:
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;
2453: EXIT;
2454: END IF;
2467: l_counties.DELETE;
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);
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:
2477: END IF;
2478:
2479: END LOOP;
2535: fnd_file.put_line(fnd_file.log,
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;
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:
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 (-)',
2541: p_prefix=>l_debug_prefix,
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;
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:
2551: EXCEPTION
2552: WHEN expect_http_ad THEN
2557: fnd_global.local_chr(10);
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;
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;
2567: retcode := '2';
2568: errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
2576: fnd_global.local_chr(10);
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;
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;
2586: retcode := '2';
2587: errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
2594: fnd_global.local_chr(10);
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;
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;
2604: retcode := '2';
2605: errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
2612: fnd_global.local_chr(10);
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;
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;
2622: retcode := '2';
2623: errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
2631: fnd_global.local_chr(10);
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;
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;
2641: retcode := '2';
2642: errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
2647: fnd_global.local_chr(10);
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;
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;
2657: retcode := '2';
2658: errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
2659:
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),
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',
2669: p_msg_level=>fnd_log.level_error);
2670: END IF;
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;
2672: retcode := '2';
2673: errbuf := fnd_message.get_string('FND', 'CONC-CHECK LOG FOR DETAILS');
2811: EXECUTE IMMEDIATE x_drop_index;
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;
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
2821: -- concurrent program. We do not want to change the value of the return
2822: -- code if it has been initialized by the calling program.
2827: -- Bug 12916969 (Moved Rebuild Index script here from top)
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;
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;
2837: END IF; /* Index Is Invalid */
2838: END IF; /* Index is Missing */
2853: -- otherwise, just push the error onto the stack.
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;
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;
2863: --disable_debug;
2864: -- restore previous state: re-enable previously enabled policies
2884: 'CONC-CHECK LOG FOR DETAILS'));
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,
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',
2894: p_msg_level=>fnd_log.level_error);
2895: END IF;
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
2897: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.rebuild_location_index (-)',
2898: p_prefix=>l_debug_prefix,
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
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;
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
2902: FOR l_count IN 1..l_owners.COUNT LOOP
2903: dbms_rls.enable_policy(l_owners(l_count), l_objects(l_count),