[Home] [Help]
294: i NUMBER;
295: l_root_text VARCHAR2(200);
296: l_debug_prefix VARCHAR2(30) := '';
297: BEGIN
298: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
299: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.get_response_lines (+)',
300: p_prefix=>l_debug_prefix,
301: p_msg_level=>fnd_log.level_procedure);
302: END IF;
297: BEGIN
298: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
299: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.get_response_lines (+)',
300: p_prefix=>l_debug_prefix,
301: p_msg_level=>fnd_log.level_procedure);
302: END IF;
303:
304: l_root_text := UPPER(p_root_text);
305: l_temp_sta := '<'||UPPER(LTRIM(p_temp_sta,'<'));
342: EXIT;
343: END IF;
344: END LOOP;
345: END IF;
346: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
347: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.get_response_lines (-)',
348: p_prefix=>l_debug_prefix,
349: p_msg_level=>fnd_log.level_procedure);
350: END IF;
345: END IF;
346: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
347: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.get_response_lines (-)',
348: p_prefix=>l_debug_prefix,
349: p_msg_level=>fnd_log.level_procedure);
350: END IF;
351: END get_response_lines;
352:
353: --
392: ) IS
393: i NUMBER;
394: l_debug_prefix VARCHAR2(30) := '';
395: BEGIN
396: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
397: hz_utility_v2pub.debug(p_message=>'hz_geocode_pkg.set_matching_geometry (+)',
398: p_prefix=>l_debug_prefix,
399: p_msg_level=>fnd_log.level_procedure);
400: END IF;
395: BEGIN
396: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
397: hz_utility_v2pub.debug(p_message=>'hz_geocode_pkg.set_matching_geometry (+)',
398: p_prefix=>l_debug_prefix,
399: p_msg_level=>fnd_log.level_procedure);
400: END IF;
401: FOR i IN 1..p_loc_array.COUNT LOOP
402: -- find the matching location ID in the array. set the geometry and
403: -- geometry status, and return if successful.
404: IF p_loc_array(i).location_id = p_location_id THEN
405: p_loc_array(i).geometry := p_geometry;
406: p_loc_array(i).geometry_status_code := p_geometry_status_code;
407: -- Debug info.
408: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
409: hz_utility_v2pub.debug(p_message=>'hz_geocode_pkg.set_matching_geometry (-)',
410: p_prefix=>l_debug_prefix,
411: p_msg_level=>fnd_log.level_procedure);
412: END IF;
407: -- Debug info.
408: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
409: hz_utility_v2pub.debug(p_message=>'hz_geocode_pkg.set_matching_geometry (-)',
410: p_prefix=>l_debug_prefix,
411: p_msg_level=>fnd_log.level_procedure);
412: END IF;
413: RETURN;
414: END IF;
415: END LOOP;
418: x_return_status := fnd_api.g_ret_sts_error;
419: fnd_message.set_name('AR', 'HZ_NO_LOCATION_FOUND');
420: fnd_message.set_token('LOC_ID', TO_CHAR(p_location_id));
421: fnd_msg_pub.add;
422: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
423: hz_utility_v2pub.debug(p_message=>'Location record not found',
424: p_prefix=>'ERROR',
425: p_msg_level=>fnd_log.level_error);
426: END IF;
421: fnd_msg_pub.add;
422: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
423: hz_utility_v2pub.debug(p_message=>'Location record not found',
424: p_prefix=>'ERROR',
425: p_msg_level=>fnd_log.level_error);
426: END IF;
427: END set_matching_geometry;
428:
429: -------------------------------
536: l_address3 := xmlize_line(p_address3);
537: l_address4 := xmlize_line(p_address4);
538: us_form_str := '';
539:
540: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
541: hz_utility_v2pub.debug(p_message=>g_pkg_name ||'.compose_elocation_detail_old for location_id:'
542: || TO_CHAR(p_location_id) ||' (+)',
543: p_prefix=>l_debug_prefix,
544: p_msg_level=>fnd_log.level_procedure);
540: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
541: hz_utility_v2pub.debug(p_message=>g_pkg_name ||'.compose_elocation_detail_old for location_id:'
542: || TO_CHAR(p_location_id) ||' (+)',
543: p_prefix=>l_debug_prefix,
544: p_msg_level=>fnd_log.level_procedure);
545: END IF;
546:
547: IF l_city IS NOT NULL AND l_state IS NOT NULL THEN
548: us_form_str := '
545: END IF;
546:
547: IF l_city IS NOT NULL AND l_state IS NOT NULL THEN
548: us_form_str := '
550: hz_utility_v2pub.debug(p_message=>'City :'||l_city||' and state :'||l_state||' are not null : Success',
551: p_prefix =>l_debug_prefix,
552: p_msg_level=>fnd_log.level_statement);
553: END IF;
548: us_form_str := '
550: hz_utility_v2pub.debug(p_message=>'City :'||l_city||' and state :'||l_state||' are not null : Success',
551: p_prefix =>l_debug_prefix,
552: p_msg_level=>fnd_log.level_statement);
553: END IF;
554: ELSIF l_postal_code IS NOT NULL THEN
555: us_form_str := '
552: p_msg_level=>fnd_log.level_statement);
553: END IF;
554: ELSIF l_postal_code IS NOT NULL THEN
555: us_form_str := '
557: hz_utility_v2pub.debug(p_message=>'Postal Code :'||l_postal_code||' is not null : Success',
558: p_prefix =>l_debug_prefix,
559: p_msg_level=>fnd_log.level_statement);
560: END IF;
555: us_form_str := '
557: hz_utility_v2pub.debug(p_message=>'Postal Code :'||l_postal_code||' is not null : Success',
558: p_prefix =>l_debug_prefix,
559: p_msg_level=>fnd_log.level_statement);
560: END IF;
561: ELSE
562: l_return_status := fnd_api.g_ret_sts_error;
563: fnd_message.set_name('AR', 'HZ_ZIP_OR_CITY_AND_STATE');
562: l_return_status := fnd_api.g_ret_sts_error;
563: fnd_message.set_name('AR', 'HZ_ZIP_OR_CITY_AND_STATE');
564: fnd_message.set_token('LOC_ID', TO_CHAR(p_location_id));
565: fnd_msg_pub.add;
566: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
567: hz_utility_v2pub.debug(p_message=>'City, state and postal code are null : Error',
568: p_prefix=>'ERROR',
569: p_msg_level=>fnd_log.level_error);
570: END IF;
565: fnd_msg_pub.add;
566: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
567: hz_utility_v2pub.debug(p_message=>'City, state and postal code are null : Error',
568: p_prefix=>'ERROR',
569: p_msg_level=>fnd_log.level_error);
570: END IF;
571: END IF;
572:
573: -- p_name not mandatory
587:
588: -- Address1 is mandatory
589: IF l_address IS NOT NULL THEN
590: us_form_str := us_form_str || 'street="' || l_address ||'" ';
591: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
592: hz_utility_v2pub.debug(p_message=>'Address : '||l_address ||' is not null : Success',
593: p_prefix =>l_debug_prefix,
594: p_msg_level=>fnd_log.level_statement);
595: END IF;
590: us_form_str := us_form_str || 'street="' || l_address ||'" ';
591: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
592: hz_utility_v2pub.debug(p_message=>'Address : '||l_address ||' is not null : Success',
593: p_prefix =>l_debug_prefix,
594: p_msg_level=>fnd_log.level_statement);
595: END IF;
596: ELSE
597: l_return_status := fnd_api.g_ret_sts_error;
598: fnd_message.set_name('AR', 'HZ_ADDRESS_LINE_MANDATORY');
597: l_return_status := fnd_api.g_ret_sts_error;
598: fnd_message.set_name('AR', 'HZ_ADDRESS_LINE_MANDATORY');
599: fnd_message.set_token('LOC_ID', TO_CHAR(p_location_id));
600: fnd_msg_pub.add;
601: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
602: hz_utility_v2pub.debug(p_message=>'Address is not null : Error ',
603: p_prefix=>'ERROR',
604: p_msg_level=>fnd_log.level_error);
605: END IF;
600: fnd_msg_pub.add;
601: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
602: hz_utility_v2pub.debug(p_message=>'Address is not null : Error ',
603: p_prefix=>'ERROR',
604: p_msg_level=>fnd_log.level_error);
605: END IF;
606: END IF;
607:
608: -- City is mandatory
621: END IF;
622:
623: --Country NULL or US
624: IF is_country_valid(l_country ,'Y') <> 'Y' THEN
625: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
626: hz_utility_v2pub.debug(p_message=>'Country must be null or US : Error',
627: p_prefix=>'ERROR',
628: p_msg_level=>fnd_log.level_error);
629: END IF;
624: IF is_country_valid(l_country ,'Y') <> 'Y' THEN
625: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
626: hz_utility_v2pub.debug(p_message=>'Country must be null or US : Error',
627: p_prefix=>'ERROR',
628: p_msg_level=>fnd_log.level_error);
629: END IF;
630: l_return_status := fnd_api.g_ret_sts_error;
631: fnd_message.set_name('AR', 'HZ_SPATIAL_INVALID_COUNTRY');
632: fnd_message.set_token('LOC_ID', TO_CHAR(p_location_id));
638: IF l_return_status <> fnd_api.g_ret_sts_success THEN
639: RAISE notcorrectaddress;
640: END IF;
641:
642: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
643: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.compose_elocation_detail_old :'|| TO_CHAR(p_location_id) ||' (-)',
644: p_prefix=>l_debug_prefix,
645: p_msg_level=>fnd_log.level_procedure);
646: END IF;
641:
642: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
643: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.compose_elocation_detail_old :'|| TO_CHAR(p_location_id) ||' (-)',
644: p_prefix=>l_debug_prefix,
645: p_msg_level=>fnd_log.level_procedure);
646: END IF;
647:
648: fnd_msg_pub.count_and_get(
649: p_encoded => fnd_api.g_false,
749: l_province := p_province;
750: l_county := p_county;
751: l_xml_address := '';
752:
753: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
754: hz_utility_v2pub.debug(p_message=>g_pkg_name ||
755: '.compose_elocation_detail for location_id:' ||
756: TO_CHAR(p_location_id) ||' (+)',
757: p_prefix=>l_debug_prefix,
754: hz_utility_v2pub.debug(p_message=>g_pkg_name ||
755: '.compose_elocation_detail for location_id:' ||
756: TO_CHAR(p_location_id) ||' (+)',
757: p_prefix=>l_debug_prefix,
758: p_msg_level=>fnd_log.level_procedure);
759: END IF;
760:
761: -- Check for valid countries
762: IF is_country_valid(l_country ,'Y') <> 'Y' THEN
759: END IF;
760:
761: -- Check for valid countries
762: IF is_country_valid(l_country ,'Y') <> 'Y' THEN
763: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
764: hz_utility_v2pub.debug(p_message=>'Country invalid',
765: p_prefix=>'ERROR',
766: p_msg_level=>fnd_log.level_error);
767: END IF;
762: IF is_country_valid(l_country ,'Y') <> 'Y' THEN
763: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
764: hz_utility_v2pub.debug(p_message=>'Country invalid',
765: p_prefix=>'ERROR',
766: p_msg_level=>fnd_log.level_error);
767: END IF;
768: l_return_status := fnd_api.g_ret_sts_error;
769: fnd_message.set_name('AR', 'HZ_SPATIAL_INVALID_COUNTRY');
770: fnd_message.set_token('LOC_ID', TO_CHAR(p_location_id));
826: IF l_return_status <> fnd_api.g_ret_sts_success THEN
827: RAISE notcorrectaddress;
828: END IF;
829:
830: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
831: hz_utility_v2pub.debug(p_message=>g_pkg_name ||
832: '.compose_elocation_detail :' ||
833: TO_CHAR(p_location_id) ||' (-)',
834: p_prefix=>l_debug_prefix,
831: hz_utility_v2pub.debug(p_message=>g_pkg_name ||
832: '.compose_elocation_detail :' ||
833: TO_CHAR(p_location_id) ||' (-)',
834: p_prefix=>l_debug_prefix,
835: p_msg_level=>fnd_log.level_procedure);
836: END IF;
837:
838: fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
839: p_count => x_msg_count,
872: l_loc_rec hz_location_v2pub.location_rec_type;
873: l_debug_prefix VARCHAR2(30):= '';
874: msg VARCHAR2(2000);
875: BEGIN
876: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
877: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.location_xml (+)',
878: p_prefix=>l_debug_prefix,
879: p_msg_level=>fnd_log.level_procedure);
880: END IF;
875: BEGIN
876: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
877: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.location_xml (+)',
878: p_prefix=>l_debug_prefix,
879: p_msg_level=>fnd_log.level_procedure);
880: END IF;
881: l_count := p_loc_array.COUNT;
882: IF l_count <> 0 THEN
883: At_least_one:= 'N';
934: '';
935: ELSE
936: xml_request := '';
937: END IF;
938: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
939: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.location_xml (-)',
940: p_prefix=>l_debug_prefix,
941: p_msg_level=>fnd_log.level_procedure);
942: END IF;
937: END IF;
938: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
939: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.location_xml (-)',
940: p_prefix=>l_debug_prefix,
941: p_msg_level=>fnd_log.level_procedure);
942: END IF;
943: fnd_msg_pub.count_and_get(
944: p_encoded => fnd_api.g_false,
945: p_count => x_msg_count,
1100: -- J. del Callar: set the geo status to success
1101: x_geo_status := g_good;
1102:
1103: -- Debug info.
1104: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1105: hz_utility_v2pub.debug(p_message=>'hz_geocode_pkg.parse_one_response (+)',
1106: p_prefix=>l_debug_prefix,
1107: p_msg_level=>fnd_log.level_procedure);
1108: END IF;
1103: -- Debug info.
1104: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1105: hz_utility_v2pub.debug(p_message=>'hz_geocode_pkg.parse_one_response (+)',
1106: p_prefix=>l_debug_prefix,
1107: p_msg_level=>fnd_log.level_procedure);
1108: END IF;
1109:
1110: l_recep := elt_value(p_str, 'GEOCODE ID');
1111: IF l_recep IS NULL THEN
1112: l_return_status := fnd_api.g_ret_sts_error;
1113: fnd_message.set_name('AR', 'HZ_MISSING_GEOCODE_ID');
1114: fnd_msg_pub.add;
1115: status_handler(l_return_status, x_return_status);
1116: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1117: hz_utility_v2pub.debug(p_message=>'No Geocode Id Found : Error',
1118: p_prefix=>'ERROR',
1119: p_msg_level=>fnd_log.level_error);
1120: END IF;
1115: status_handler(l_return_status, x_return_status);
1116: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1117: hz_utility_v2pub.debug(p_message=>'No Geocode Id Found : Error',
1118: p_prefix=>'ERROR',
1119: p_msg_level=>fnd_log.level_error);
1120: END IF;
1121: END IF;
1122:
1123: -- J. del Callar: convert location ID into a number, but handle non-numeric
1130: l_return_status, x_msg_count, x_msg_data);
1131: status_handler(l_return_status, x_return_status);
1132: RETURN;
1133: END;
1134: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1135: hz_utility_v2pub.debug(p_message=>'Geocode Id :'||l_recep,
1136: p_prefix =>l_debug_prefix,
1137: p_msg_level=>fnd_log.level_statement);
1138: END IF;
1133: END;
1134: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1135: hz_utility_v2pub.debug(p_message=>'Geocode Id :'||l_recep,
1136: p_prefix =>l_debug_prefix,
1137: p_msg_level=>fnd_log.level_statement);
1138: END IF;
1139:
1140: -- determine the match count.
1141: l_match_count_temp := elt_value(p_str, 'MATCH_COUNT');
1151: fnd_message.set_name('AR', 'HZ_MISSING_LATITUDE');
1152: fnd_message.set_token('LOC_ID', TO_CHAR(x_loc_id));
1153: fnd_msg_pub.add;
1154: -- Debug info.
1155: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1156: hz_utility_v2pub.debug(p_message=>'Latitude not found',
1157: p_prefix=>'ERROR',
1158: p_msg_level=>fnd_log.level_error);
1159: END IF;
1154: -- Debug info.
1155: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1156: hz_utility_v2pub.debug(p_message=>'Latitude not found',
1157: p_prefix=>'ERROR',
1158: p_msg_level=>fnd_log.level_error);
1159: END IF;
1160: END IF;
1161: status_handler(l_return_status, x_return_status);
1162: l_lat_found := FALSE;
1170: non_num_handle('LATITUDE', l_recep, x_loc_id,
1171: l_return_status, x_msg_count, x_msg_data);
1172: status_handler(l_return_status, x_return_status);
1173: END;
1174: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1175: hz_utility_v2pub.debug(p_message=>'Latitude: '||l_recep,
1176: p_prefix =>l_debug_prefix,
1177: p_msg_level=>fnd_log.level_statement);
1178: END IF;
1173: END;
1174: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1175: hz_utility_v2pub.debug(p_message=>'Latitude: '||l_recep,
1176: p_prefix =>l_debug_prefix,
1177: p_msg_level=>fnd_log.level_statement);
1178: END IF;
1179: l_lat_found := TRUE;
1180: END IF;
1181:
1190: fnd_message.set_name('AR', 'HZ_MISSING_LONGITUDE');
1191: fnd_message.set_token('LOC_ID', TO_CHAR(x_loc_id));
1192: fnd_msg_pub.add;
1193: -- Debug info.
1194: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1195: hz_utility_v2pub.debug(p_message=>'Longitude not found',
1196: p_prefix=>'ERROR',
1197: p_msg_level=>fnd_log.level_error);
1198: END IF;
1193: -- Debug info.
1194: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1195: hz_utility_v2pub.debug(p_message=>'Longitude not found',
1196: p_prefix=>'ERROR',
1197: p_msg_level=>fnd_log.level_error);
1198: END IF;
1199: END IF;
1200:
1201: status_handler(l_return_status, x_return_status);
1210: non_num_handle('LONGITUDE', l_recep, x_loc_id,
1211: l_return_status, x_msg_count, x_msg_data);
1212: status_handler(l_return_status, x_return_status);
1213: END;
1214: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1215: hz_utility_v2pub.debug(p_message=>'Longitude: '||l_recep,
1216: p_prefix =>l_debug_prefix,
1217: p_msg_level=>fnd_log.level_statement);
1218: END IF;
1213: END;
1214: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1215: hz_utility_v2pub.debug(p_message=>'Longitude: '||l_recep,
1216: p_prefix =>l_debug_prefix,
1217: p_msg_level=>fnd_log.level_statement);
1218: END IF;
1219:
1220:
1221: l_long_found := TRUE;
1238: -- that the city or zip center was returned.
1239: x_geo_status := g_noexactmatch;
1240:
1241: -- Debug info.
1242: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1243: hz_utility_v2pub.debug(p_message=>'The street was not found',
1244: p_prefix=>'WARNING',
1245: p_msg_level=>fnd_log.level_exception);
1246:
1241: -- Debug info.
1242: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1243: hz_utility_v2pub.debug(p_message=>'The street was not found',
1244: p_prefix=>'WARNING',
1245: p_msg_level=>fnd_log.level_exception);
1246:
1247: END IF;
1248: ELSE
1249: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1245: p_msg_level=>fnd_log.level_exception);
1246:
1247: END IF;
1248: ELSE
1249: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1250: hz_utility_v2pub.debug(p_message=>'Street: '||l_street,
1251: p_prefix =>l_debug_prefix,
1252: p_msg_level=>fnd_log.level_statement);
1253: END IF;
1248: ELSE
1249: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1250: hz_utility_v2pub.debug(p_message=>'Street: '||l_street,
1251: p_prefix =>l_debug_prefix,
1252: p_msg_level=>fnd_log.level_statement);
1253: END IF;
1254: END IF;
1255:
1256: -- J. del Callar: convert match count into a number and handle non-numeric
1268: -- J. del Callar: set the status reported in
1269: -- HZ_LOCATIONS.GEOMETRY_STATUS_CODE to MULTIMATCH to reflect that
1270: -- multiple matches were returned.
1271: x_geo_status := g_multimatch;
1272: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1273: hz_utility_v2pub.debug(p_message=>'Multiple matching addresses:'|| l_match_count_temp,
1274: p_prefix=>'WARNING',
1275: p_msg_level=>fnd_log.level_exception);
1276: END IF;
1271: x_geo_status := g_multimatch;
1272: IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1273: hz_utility_v2pub.debug(p_message=>'Multiple matching addresses:'|| l_match_count_temp,
1274: p_prefix=>'WARNING',
1275: p_msg_level=>fnd_log.level_exception);
1276: END IF;
1277: ELSIF l_match_count = 0 THEN
1278: l_return_status := fnd_api.g_ret_sts_error;
1279: fnd_message.set_name('AR','HZ_NB_MATCHES');
1284: -- J. del Callar: no rows were returned by location service
1285: -- mark this as an error.
1286: x_geo_status := g_error;
1287:
1288: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1289: hz_utility_v2pub.debug(p_message=>'No matches (match_count=' ||
1290: l_match_count_temp ||
1291: '), update not possible.',
1292: p_prefix=>'ERROR',
1289: hz_utility_v2pub.debug(p_message=>'No matches (match_count=' ||
1290: l_match_count_temp ||
1291: '), update not possible.',
1292: p_prefix=>'ERROR',
1293: p_msg_level=>fnd_log.level_error);
1294: END IF;
1295:
1296: END IF;
1297: status_handler(l_return_status, x_return_status);
1320: p_data => x_msg_data
1321: );
1322:
1323: -- Debug info.
1324: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1325: hz_utility_v2pub.debug(p_message=>'parse_one_reponse (-)',
1326: p_prefix=>l_debug_prefix,
1327: p_msg_level=>fnd_log.level_procedure);
1328: END IF;
1323: -- Debug info.
1324: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1325: hz_utility_v2pub.debug(p_message=>'parse_one_reponse (-)',
1326: p_prefix=>l_debug_prefix,
1327: p_msg_level=>fnd_log.level_procedure);
1328: END IF;
1329: END parse_one_response;
1330:
1331: --
1381: l_location_id NUMBER;
1382: l_debug_prefix VARCHAR2(30) := '';
1383: BEGIN
1384: -- Debug info.
1385: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1386: hz_utility_v2pub.debug(p_message=>'parse_responses (+)',
1387: p_prefix=>l_debug_prefix,
1388: p_msg_level=>fnd_log.level_procedure);
1389: END IF;
1384: -- Debug info.
1385: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1386: hz_utility_v2pub.debug(p_message=>'parse_responses (+)',
1387: p_prefix=>l_debug_prefix,
1388: p_msg_level=>fnd_log.level_procedure);
1389: END IF;
1390: l_return_status := fnd_api.g_ret_sts_success;
1391: i := p_tab_address.COUNT;
1392: IF i > 0 THEN
1423: p_count => x_msg_count,
1424: p_data => x_msg_data
1425: );
1426: -- Debug info.
1427: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1428: hz_utility_v2pub.debug(p_message=>'parse_n_reponse (-)',
1429: p_prefix=>l_debug_prefix,
1430: p_msg_level=>fnd_log.level_procedure);
1431: END IF;
1426: -- Debug info.
1427: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1428: hz_utility_v2pub.debug(p_message=>'parse_n_reponse (-)',
1429: p_prefix=>l_debug_prefix,
1430: p_msg_level=>fnd_log.level_procedure);
1431: END IF;
1432: END parse_responses;
1433:
1434: --
1465: l_url_domain VARCHAR2(2000);
1466: l_debug_prefix VARCHAR2(30) := '';
1467: BEGIN
1468: --enable_debug;
1469: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1470: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.in_bypass_list (+)',
1471: p_prefix=>l_debug_prefix,
1472: p_msg_level=>fnd_log.level_procedure);
1473: END IF;
1468: --enable_debug;
1469: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1470: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.in_bypass_list (+)',
1471: p_prefix=>l_debug_prefix,
1472: p_msg_level=>fnd_log.level_procedure);
1473: END IF;
1474: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1475: hz_utility_v2pub.debug(p_message=>'Checking for URL: ' || p_url_target,
1476: p_prefix =>l_debug_prefix,
1470: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.in_bypass_list (+)',
1471: p_prefix=>l_debug_prefix,
1472: p_msg_level=>fnd_log.level_procedure);
1473: END IF;
1474: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1475: hz_utility_v2pub.debug(p_message=>'Checking for URL: ' || p_url_target,
1476: p_prefix =>l_debug_prefix,
1477: p_msg_level=>fnd_log.level_statement);
1478: hz_utility_v2pub.debug(p_message=>'In list:',
1473: END IF;
1474: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1475: hz_utility_v2pub.debug(p_message=>'Checking for URL: ' || p_url_target,
1476: p_prefix =>l_debug_prefix,
1477: p_msg_level=>fnd_log.level_statement);
1478: hz_utility_v2pub.debug(p_message=>'In list:',
1479: p_prefix =>l_debug_prefix,
1480: p_msg_level=>fnd_log.level_statement);
1481: hz_utility_v2pub.debug(p_message=>l_exclusion_list,
1476: p_prefix =>l_debug_prefix,
1477: p_msg_level=>fnd_log.level_statement);
1478: hz_utility_v2pub.debug(p_message=>'In list:',
1479: p_prefix =>l_debug_prefix,
1480: p_msg_level=>fnd_log.level_statement);
1481: hz_utility_v2pub.debug(p_message=>l_exclusion_list,
1482: p_prefix =>l_debug_prefix,
1483: p_msg_level=>fnd_log.level_statement);
1484: END IF;
1479: p_prefix =>l_debug_prefix,
1480: p_msg_level=>fnd_log.level_statement);
1481: hz_utility_v2pub.debug(p_message=>l_exclusion_list,
1482: p_prefix =>l_debug_prefix,
1483: p_msg_level=>fnd_log.level_statement);
1484: END IF;
1485:
1486: -- First determine what the delimiter in the exclusion list is. We support
1487: -- "|" (Java-style), "," (Netscape-style) and ";" (Microsoft-style)
1492: l_delimiter := ',';
1493: ELSIF INSTRB(l_exclusion_list, ';') > 0 THEN
1494: l_delimiter := ';';
1495: ELSE
1496: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1497: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.in_bypass_list (fmt)',
1498: p_prefix =>l_debug_prefix,
1499: p_msg_level=>fnd_log.level_statement);
1500: END IF;
1495: ELSE
1496: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1497: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.in_bypass_list (fmt)',
1498: p_prefix =>l_debug_prefix,
1499: p_msg_level=>fnd_log.level_statement);
1500: END IF;
1501:
1502: --disable_debug;
1503:
1511: -- second, remove the protocol specifier.
1512: l_pos := INSTRB(l_url_domain, '://');
1513: IF l_pos > 0 THEN
1514: l_url_domain := SUBSTRB(l_url_domain, l_pos+3);
1515: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1516: hz_utility_v2pub.debug(p_message=>':// found at position ' || l_pos,
1517: p_prefix =>l_debug_prefix,
1518: p_msg_level=>fnd_log.level_statement);
1519: hz_utility_v2pub.debug(p_message=>'Stripped domain: ' || l_url_domain,
1514: l_url_domain := SUBSTRB(l_url_domain, l_pos+3);
1515: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1516: hz_utility_v2pub.debug(p_message=>':// found at position ' || l_pos,
1517: p_prefix =>l_debug_prefix,
1518: p_msg_level=>fnd_log.level_statement);
1519: hz_utility_v2pub.debug(p_message=>'Stripped domain: ' || l_url_domain,
1520: p_prefix =>l_debug_prefix,
1521: p_msg_level=>fnd_log.level_statement);
1522: END IF;
1517: p_prefix =>l_debug_prefix,
1518: p_msg_level=>fnd_log.level_statement);
1519: hz_utility_v2pub.debug(p_message=>'Stripped domain: ' || l_url_domain,
1520: p_prefix =>l_debug_prefix,
1521: p_msg_level=>fnd_log.level_statement);
1522: END IF;
1523: END IF;
1524:
1525: l_pos := INSTRB(l_url_domain, ':/');
1524:
1525: l_pos := INSTRB(l_url_domain, ':/');
1526: IF l_pos > 0 THEN
1527: l_url_domain := SUBSTRB(l_url_domain, l_pos+2);
1528: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1529: hz_utility_v2pub.debug(p_message=>':/ found at position ' || l_pos,
1530: p_prefix =>l_debug_prefix,
1531: p_msg_level=>fnd_log.level_statement);
1532: hz_utility_v2pub.debug(p_message=>'Stripped domain: ' || l_url_domain,
1527: l_url_domain := SUBSTRB(l_url_domain, l_pos+2);
1528: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1529: hz_utility_v2pub.debug(p_message=>':/ found at position ' || l_pos,
1530: p_prefix =>l_debug_prefix,
1531: p_msg_level=>fnd_log.level_statement);
1532: hz_utility_v2pub.debug(p_message=>'Stripped domain: ' || l_url_domain,
1533: p_prefix =>l_debug_prefix,
1534: p_msg_level=>fnd_log.level_statement);
1535: END IF;
1530: p_prefix =>l_debug_prefix,
1531: p_msg_level=>fnd_log.level_statement);
1532: hz_utility_v2pub.debug(p_message=>'Stripped domain: ' || l_url_domain,
1533: p_prefix =>l_debug_prefix,
1534: p_msg_level=>fnd_log.level_statement);
1535: END IF;
1536: END IF;
1537:
1538: -- third, remove the trailing URL information.
1538: -- third, remove the trailing URL information.
1539: l_pos := INSTRB(l_url_domain, '/');
1540: IF l_pos > 0 THEN
1541: l_url_domain := SUBSTRB(l_url_domain, 1, l_pos-1);
1542: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1543: hz_utility_v2pub.debug(p_message=>'/ found at position ' || l_pos,
1544: p_prefix =>l_debug_prefix,
1545: p_msg_level=>fnd_log.level_statement);
1546: hz_utility_v2pub.debug(p_message=>'Stripped domain: ' || l_url_domain,
1541: l_url_domain := SUBSTRB(l_url_domain, 1, l_pos-1);
1542: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1543: hz_utility_v2pub.debug(p_message=>'/ found at position ' || l_pos,
1544: p_prefix =>l_debug_prefix,
1545: p_msg_level=>fnd_log.level_statement);
1546: hz_utility_v2pub.debug(p_message=>'Stripped domain: ' || l_url_domain,
1547: p_prefix =>l_debug_prefix,
1548: p_msg_level=>fnd_log.level_statement);
1549: END IF;
1544: p_prefix =>l_debug_prefix,
1545: p_msg_level=>fnd_log.level_statement);
1546: hz_utility_v2pub.debug(p_message=>'Stripped domain: ' || l_url_domain,
1547: p_prefix =>l_debug_prefix,
1548: p_msg_level=>fnd_log.level_statement);
1549: END IF;
1550: END IF;
1551:
1552: -- lastly, remove spaces in the exclusion list
1569: -- domain name. It may have a * sign in it depending on the syntax of
1570: -- the exclusion list. * signs should be treated as % signs in SQL.
1571: l_excluded_domain := REPLACE(l_excluded_domain, '*', '%');
1572:
1573: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1574: hz_utility_v2pub.debug(p_message=>'Matching URL: ' || l_url_domain,
1575: p_prefix =>l_debug_prefix,
1576: p_msg_level=>fnd_log.level_statement);
1577: hz_utility_v2pub.debug(p_message=>'Against dom: ' || l_excluded_domain,
1572:
1573: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1574: hz_utility_v2pub.debug(p_message=>'Matching URL: ' || l_url_domain,
1575: p_prefix =>l_debug_prefix,
1576: p_msg_level=>fnd_log.level_statement);
1577: hz_utility_v2pub.debug(p_message=>'Against dom: ' || l_excluded_domain,
1578: p_prefix =>l_debug_prefix,
1579: p_msg_level=>fnd_log.level_statement);
1580: END IF;
1575: p_prefix =>l_debug_prefix,
1576: p_msg_level=>fnd_log.level_statement);
1577: hz_utility_v2pub.debug(p_message=>'Against dom: ' || l_excluded_domain,
1578: p_prefix =>l_debug_prefix,
1579: p_msg_level=>fnd_log.level_statement);
1580: END IF;
1581:
1582: -- check to see if the URL domain matches an excluded domain.
1583: IF l_url_domain LIKE '%' || l_excluded_domain THEN
1580: END IF;
1581:
1582: -- check to see if the URL domain matches an excluded domain.
1583: IF l_url_domain LIKE '%' || l_excluded_domain THEN
1584: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1585: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.in_bypass_list (match)',
1586: p_prefix =>l_debug_prefix,
1587: p_msg_level=>fnd_log.level_statement);
1588: END IF;
1583: IF l_url_domain LIKE '%' || l_excluded_domain THEN
1584: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1585: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.in_bypass_list (match)',
1586: p_prefix =>l_debug_prefix,
1587: p_msg_level=>fnd_log.level_statement);
1588: END IF;
1589:
1590: --disable_debug;
1591:
1600: -- get the remaining domain exclusions to be checked.
1601: l_exclusion_list := SUBSTRB(l_exclusion_list, l_pos+1);
1602: END IF;
1603: END LOOP;
1604: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1605: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.in_bypass_list (eol)',
1606: p_prefix =>l_debug_prefix,
1607: p_msg_level=>fnd_log.level_statement);
1608: END IF;
1603: END LOOP;
1604: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1605: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.in_bypass_list (eol)',
1606: p_prefix =>l_debug_prefix,
1607: p_msg_level=>fnd_log.level_statement);
1608: END IF;
1609:
1610: --disable_debug;
1611:
1678: i NUMBER;
1679: l_debug_prefix VARCHAR2(30) := '';
1680: BEGIN
1681: --enable_debug;
1682: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1683: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.get_spatial_coords (+)',
1684: p_prefix=>l_debug_prefix,
1685: p_msg_level=>fnd_log.level_procedure);
1686: END IF;
1681: --enable_debug;
1682: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1683: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.get_spatial_coords (+)',
1684: p_prefix=>l_debug_prefix,
1685: p_msg_level=>fnd_log.level_procedure);
1686: END IF;
1687:
1688: l_return_status := fnd_api.g_ret_sts_success;
1689:
1700:
1701: status_handler(l_return_status, x_return_status);
1702:
1703: IF lxml IS NOT NULL THEN
1704: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1705: hz_utility_v2pub.debug(p_message=>'hz_http_pkg.post (+)',
1706: p_prefix=>l_debug_prefix,
1707: p_msg_level=>fnd_log.level_procedure);
1708: END IF;
1703: IF lxml IS NOT NULL THEN
1704: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1705: hz_utility_v2pub.debug(p_message=>'hz_http_pkg.post (+)',
1706: p_prefix=>l_debug_prefix,
1707: p_msg_level=>fnd_log.level_procedure);
1708: END IF;
1709: cpt := 0;
1710: LOOP
1711: cpt := cpt + 1;
1721: x_return_status => l_return_status,
1722: x_msg_count => x_msg_count,
1723: x_msg_data => x_msg_data);
1724: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1725: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1726: hz_utility_v2pub.debug(p_message=>'hz_http_pkg.post failed',
1727: p_prefix=>'UNEXPECTED ERROR',
1728: p_msg_level=>fnd_log.level_error);
1729: END IF;
1724: IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1725: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1726: hz_utility_v2pub.debug(p_message=>'hz_http_pkg.post failed',
1727: p_prefix=>'UNEXPECTED ERROR',
1728: p_msg_level=>fnd_log.level_error);
1729: END IF;
1730: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1731: hz_utility_v2pub.debug(p_message=>'hz_http_pkg.post (-)',
1732: p_prefix=>l_debug_prefix,
1726: hz_utility_v2pub.debug(p_message=>'hz_http_pkg.post failed',
1727: p_prefix=>'UNEXPECTED ERROR',
1728: p_msg_level=>fnd_log.level_error);
1729: END IF;
1730: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1731: hz_utility_v2pub.debug(p_message=>'hz_http_pkg.post (-)',
1732: p_prefix=>l_debug_prefix,
1733: p_msg_level=>fnd_log.level_procedure);
1734: END IF;
1729: END IF;
1730: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1731: hz_utility_v2pub.debug(p_message=>'hz_http_pkg.post (-)',
1732: p_prefix=>l_debug_prefix,
1733: p_msg_level=>fnd_log.level_procedure);
1734: END IF;
1735: RAISE exchttp;
1736: END IF;
1737: EXIT WHEN lresp IS NOT NULL;
1737: EXIT WHEN lresp IS NOT NULL;
1738: EXIT WHEN cpt > p_retry;
1739: END LOOP;
1740:
1741: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1742: hz_utility_v2pub.debug(p_message=>'hz_http_pkg.post (-)',
1743: p_prefix=>l_debug_prefix,
1744: p_msg_level=>fnd_log.level_procedure);
1745: END IF;
1740:
1741: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1742: hz_utility_v2pub.debug(p_message=>'hz_http_pkg.post (-)',
1743: p_prefix=>l_debug_prefix,
1744: p_msg_level=>fnd_log.level_procedure);
1745: END IF;
1746:
1747:
1748: IF lresp IS NULL THEN
1757: -- Set the error status for all records in this batch
1758: FOR i IN 1..p_loc_array.COUNT LOOP
1759: p_loc_array(i).geometry_status_code := g_error;
1760: END LOOP;
1761: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1762: hz_utility_v2pub.debug(p_message=>'Null response',
1763: p_prefix=>'ERROR',
1764: p_msg_level=>fnd_log.level_error);
1765: END IF;
1760: END LOOP;
1761: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1762: hz_utility_v2pub.debug(p_message=>'Null response',
1763: p_prefix=>'ERROR',
1764: p_msg_level=>fnd_log.level_error);
1765: END IF;
1766: status_handler(l_return_status, x_return_status);
1767: ELSIF UPPER(lresp) NOT LIKE '%
1768: -- Spatial response was not parseable. This is an unrecoverable error.
1776: -- Set the error status for all records in this batch
1777: FOR i IN 1..p_loc_array.COUNT LOOP
1778: p_loc_array(i).geometry_status_code := g_error;
1779: END LOOP;
1780: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1781: hz_utility_v2pub.debug(p_message=>'Malformed response: '||SUBSTRB(lresp, 1, 200),
1782: p_prefix=>'ERROR',
1783: p_msg_level=>fnd_log.level_error);
1784: END IF;
1779: END LOOP;
1780: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1781: hz_utility_v2pub.debug(p_message=>'Malformed response: '||SUBSTRB(lresp, 1, 200),
1782: p_prefix=>'ERROR',
1783: p_msg_level=>fnd_log.level_error);
1784: END IF;
1785: status_handler(l_return_status, x_return_status);
1786: ELSE
1787: get_response_lines(
1800: FOR i IN 1..p_loc_array.COUNT LOOP
1801: p_loc_array(i).geometry_status_code := g_error;
1802: END LOOP;
1803:
1804: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1805: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.get_response_lines returned 0 rows',
1806: p_prefix=>'ERROR',
1807: p_msg_level=>fnd_log.level_error);
1808: END IF;
1803:
1804: IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1805: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.get_response_lines returned 0 rows',
1806: p_prefix=>'ERROR',
1807: p_msg_level=>fnd_log.level_error);
1808: END IF;
1809: ELSE
1810: parse_responses(
1811: p_tab_address => ltab,
1823: p_encoded => fnd_api.g_false,
1824: p_count => x_msg_count,
1825: p_data => x_msg_data
1826: );
1827: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1828: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.get_spatial_coords (-)',
1829: p_prefix=>l_debug_prefix,
1830: p_msg_level=>fnd_log.level_procedure);
1831: END IF;
1826: );
1827: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1828: hz_utility_v2pub.debug(p_message=>g_pkg_name||'.get_spatial_coords (-)',
1829: p_prefix=>l_debug_prefix,
1830: p_msg_level=>fnd_log.level_procedure);
1831: END IF;
1832:
1833: --disable_debug;
1834: EXCEPTION