1 PACKAGE BODY hz_geocode_pkg AS
2 /*$Header: ARHGEOCB.pls 120.8 2005/09/08 21:50:23 acng noship $*/
3
4 -- private global variables
5
6 g_warning VARCHAR2(1) := 'W';
7 g_last_valid_country VARCHAR2(60) := NULL;
8
9 --------------------------------------
10 -- private procedures and functions
11 --------------------------------------
12 --
13 -- PRIVATE PROCEDURE enable_debug
14 -- DESCRIPTION
15 -- Turn on debug mode.
16 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
17 -- HZ_UTILITY_V2PUB.enable_debug
18 -- MODIFICATION HISTORY
19 -- 01-10-2002 Herve Yu
20 --------------------------------------
21 /*PROCEDURE enable_debug IS
22 BEGIN
23 g_debug_count := g_debug_count + 1;
24 IF g_debug_count = 1 THEN
25 IF fnd_profile.value('HZ_API_FILE_DEBUG_ON') = 'Y' OR
26 fnd_profile.value('HZ_API_DBMS_DEBUG_ON') = 'Y'
27 THEN
28 hz_utility_v2pub.enable_debug;
29 g_debug := TRUE;
30 END IF;
31 END IF;
32 END enable_debug;
33 */
34
35 --------------------------------------
36 -- PRIVATE PROCEDURE time_put_line
37 -- DESCRIPTION
38 -- Utility routine for performance testing. Prints the argument with
39 -- a timestamp.
40 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
41 -- hz_utility_v2pub.debug
42 -- MODIFICATION HISTORY
43 -- 01-10-2002 J. del Callar
44 --------------------------------------
45 PROCEDURE time_put_line (msg IN VARCHAR2) IS
46 BEGIN
47 hz_utility_v2pub.debug(TO_CHAR(SYSDATE, 'HH:MI:SS') ||
48 ': ' || SUBSTRB(msg, 1, 240));
49 END time_put_line;
50
51 --------------------------------------
52 -- PRIVATE PROCEDURE xml_put_line
53 -- DESCRIPTION
54 -- Utility routine for testing. Prints the argument in a manner that
55 -- dbms_output can deal with.
56 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
57 -- hz_utility_v2pub.debug
58 -- MODIFICATION HISTORY
59 -- 01-10-2002 J. del Callar
60 --------------------------------------
61 PROCEDURE xml_put_line (msg IN VARCHAR2) IS
62 BEGIN
63 hz_utility_v2pub.debug('XML request:');
64 FOR i IN 1..LENGTHB(msg)/240 LOOP
65 hz_utility_v2pub.debug(SUBSTRB(msg, (i-1)*240+1, 240));
66 END LOOP;
67 END xml_put_line;
68
69 --------------------------------------
70 -- PRIVATE PROCEDURE disable_debug
71 -- DESCRIPTION
72 -- Turn off debug mode.
73 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
74 -- HZ_UTILITY_V2PUB.disable_debug
75 -- MODIFICATION HISTORY
76 -- 01-10-2002 Herve Yu
77 --------------------------------------
78 /*PROCEDURE disable_debug IS
79 BEGIN
80 IF g_debug THEN
81 g_debug_count := g_debug_count - 1;
82 IF g_debug_count = 0 THEN
83 hz_utility_v2pub.disable_debug;
84 g_debug := FALSE;
85 END IF;
86 END IF;
87 END disable_debug;
88 */
89
90 --------------------------------------
91 -- Copy the status for the upper layer
92 --------------------------------------
93 PROCEDURE status_handler (
94 l_return_status IN VARCHAR2,
95 x_return_status IN OUT NOCOPY VARCHAR2
96 ) IS
97 BEGIN
98 IF x_return_status = fnd_api.g_ret_sts_error THEN
99 NULL;
100 ELSIF x_return_status = g_warning
101 AND l_return_status = fnd_api.g_ret_sts_success
102 THEN
103 NULL;
104 ELSE
105 x_return_status := l_return_status;
106 END IF;
107 END status_handler;
108
109 -------------------------------------------
110 -- Default XML end tag for a given node tag
111 -------------------------------------------
112 FUNCTION xml_end_tag (p_start_tag IN VARCHAR2) RETURN VARCHAR2 IS
113 l_end_tag VARCHAR2(2000);
114 l_pos NUMBER := 0;
115 BEGIN
116 l_end_tag := UPPER(p_start_tag);
117 l_pos := INSTRB(l_end_tag, ' ');
118 IF l_pos <> 0 THEN
119 l_end_tag := SUBSTRB(l_end_tag, 1, l_pos);
120 END IF;
121 l_end_tag := RTRIM(LTRIM(TRIM(l_end_tag),'<'),'>');
122 l_end_tag := '</'||l_end_tag||'>';
123 RETURN l_end_tag;
124 END xml_end_tag;
125
126 --
127 -- PRIVATE FUNCTION
128 -- get_terminal_string
129 --
130 -- DESCRIPTION
131 -- Gets the terminal string for an XML statement. The string can either
132 -- be a tag terminator (/>) or a matching end tag (e.g., </tag>).
133 --
134 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
135 --
136 -- MODIFICATION HISTORY
137 --
138 -- 02-28-2002 Herve Yu Created.
139 -- 04-01-2002 Joe del Callar Changed the name to make it more
140 -- understandable. Put this comment in.
141 --
142 FUNCTION get_terminal_string (
143 p_str IN VARCHAR2,
144 p_temp_sta IN VARCHAR2,
145 p_temp_end IN VARCHAR2 DEFAULT NULL,
146 p_provider IN VARCHAR2
147 ) RETURN VARCHAR2 IS
148 test_char VARCHAR2(1);
149 l_temp_sta VARCHAR2(200);
150 l_temp_end VARCHAR2(200);
151 l_str VARCHAR2(32767);
152 l_len NUMBER;
153 l_init NUMBER;
154 temp_pos NUMBER;
155 char_to_find VARCHAR2(10);
156 i NUMBER;
157 BEGIN
158 char_to_find := NULL;
159 l_temp_sta := p_temp_sta;
160 --l_temp_end := UPPER(p_temp_end);
161 --l_str := UPPER(p_str);
162 l_temp_end := p_temp_end;
163 l_str := p_str;
164 l_len := LENGTHB(l_str);
165 --l_temp_sta := '<'||UPPER(RTRIM(LTRIM(TRIM(l_temp_sta),'<'),'>'));
166 l_temp_sta := '<'||RTRIM(LTRIM(TRIM(l_temp_sta),'<'),'>');
167 l_init := INSTRB(l_str, l_temp_sta);
168 temp_pos := l_init + LENGTHB(l_temp_sta);
169 IF l_temp_end IS NULL THEN
170 l_temp_end := xml_end_tag(p_temp_sta);
171 END IF;
172 IF INSTRB(l_str, l_temp_sta) <> 0 THEN
173 LOOP
174 test_char := SUBSTRB(l_str,temp_pos,1);
175 temp_pos := temp_pos + 1;
176 EXIT WHEN temp_pos > l_len;
177 IF test_char = '/' THEN
178 test_char := SUBSTRB(l_str,temp_pos,1);
179 temp_pos := temp_pos + 1;
180 IF test_char = '>' THEN
181 char_to_find := '/>';
182 EXIT;
183 END IF;
184 ELSIF test_char = '>' THEN
185 char_to_find := l_temp_end;
186 EXIT;
187 END IF;
188 END LOOP;
189 END IF;
190 RETURN char_to_find;
191 END get_terminal_string;
192
193 --
194 -- PUBLIC FUNCTION
195 -- remove_whitespace
196 --
197 -- DESCRIPTION
198 -- Remove whitespace from a string
199 --
200 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
201 --
202 -- MODIFICATION HISTORY
203 --
204 -- 02-28-2002 Joe del Callar Created.
205 --
206 FUNCTION remove_whitespace (p_str IN VARCHAR2) RETURN VARCHAR2 IS
207 l_str VARCHAR2(32767);
208 BEGIN
209 l_str := p_str;
210 l_str := RTRIM(LTRIM(l_str, fnd_global.local_chr(13)),
211 fnd_global.local_chr(13));
212 l_str := RTRIM(LTRIM(l_str, fnd_global.local_chr(8)),
213 fnd_global.local_chr(8));
214 l_str := RTRIM(LTRIM(l_str, fnd_global.local_chr(10)),
215 fnd_global.local_chr(10));
216 l_str := RTRIM(LTRIM(l_str, fnd_global.local_chr(0)),
217 fnd_global.local_chr(0));
218 l_str := RTRIM(LTRIM(l_str));
219 RETURN l_str;
220 END remove_whitespace;
221
222 --
223 -- PRIVATE FUNCTION
224 -- xmlize_line
225 --
226 -- DESCRIPTION
227 -- Return an XML interpretation of a single address line in HZ_LOCATIONS.
228 -- The strings &, < and > have to be removed.
229 --
230 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
231 -- HZ_UTILITY_V2PUB.enable_debug
232 --
233 -- MODIFICATION HISTORY
234 --
235 -- 01-28-2002 Joe del Callar Created.
236 -- 02-28-2002 J. del Callar Generalized to also remove whitespace.
237 -- 04-01-2002 J. del Callar Changed to also remove double quotes.
238 --
239 FUNCTION xmlize_line (p_str IN VARCHAR2) RETURN VARCHAR2 IS
240 l_str VARCHAR2(32767);
241 BEGIN
242 l_str := p_str;
243
244 -- clean the string of reserved characters before returning
245 l_str := REPLACE(l_str, '&'); -- get rid of ampersands
246 l_str := REPLACE(l_str, '<'); -- get rid of open brackets
247 l_str := REPLACE(l_str, '>'); -- get rid of close brackets
248 l_str := REPLACE(l_str, '"'); -- get rid of double quotes
249 l_str := REPLACE(l_str, ''''); -- get rid of double quotes
250 l_str := REPLACE(l_str, '#'); -- get rid of hash
251 l_str := REPLACE(l_str, '%'); -- get rid of percentage
252 l_str := REPLACE(l_str, '+'); -- get rid of plus
253 l_str := REPLACE(l_str, '&'); -- get rid of ampersands
254
255
256 RETURN remove_whitespace(l_str);
257 END xmlize_line;
258
259 --
260 -- PRIVATE PROCEDURE
261 -- get_response_lines
262 -- DESCRIPTION
263 -- Fill a VARRAY with individual response from the website
264 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
265 -- hz_utility_v2pub.debug
266 -- MODIFICATION HISTORY
267 -- 01-10-2002 Herve Yu
268 -- 04-01-2002 J. del Callar Put comments in. Added p_loc_array
269 -- parameter so that this procedure can
270 -- set the geometry status for instances
271 -- where the location was not returned by
272 -- the data provider.
273 --
274
275 PROCEDURE get_response_lines (
276 p_str IN VARCHAR2,
277 p_lines IN OUT NOCOPY hz_geocode_pkg.array_t,
278 p_loc_array IN OUT NOCOPY loc_array,
279 p_temp_sta IN VARCHAR2,
280 p_temp_end IN VARCHAR2 DEFAULT NULL,
281 p_root_text IN VARCHAR2 DEFAULT NULL,
282 p_provider IN VARCHAR2 DEFAULT 'ELOCATION'
283 ) IS
284 l_init NUMBER;
285 l_end NUMBER;
286 l_str_search VARCHAR2(32767);
287 l_result VARCHAR2(4000);
288 l_len NUMBER;
289 l_break VARCHAR2(1000);
290 l_temp_sta VARCHAR2(200);
291 l_temp_end VARCHAR2(200);
292 l_provider VARCHAR2(200);
293 l_pos NUMBER := 0;
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;
303
304 l_root_text := UPPER(p_root_text);
305 l_temp_sta := '<'||UPPER(LTRIM(p_temp_sta,'<'));
306 l_temp_end := UPPER(p_temp_end);
307 i := 0;
308 l_provider := UPPER(p_provider);
309 l_len := LENGTHB(p_str);
310 l_str_search := UPPER(p_str);
311 l_init := 1;
312 l_end := INSTRB(l_str_search, l_temp_sta);
313 IF l_end <> 0 THEN
314 l_str_search := LTRIM(l_str_search, '<?XML VERSION="1.0" ENCODING="UTF-8" ?>');
315 l_str_search := remove_whitespace(l_str_search);
316 l_str_search := LTRIM(l_str_search, l_root_text);
317 LOOP
318 l_break := get_terminal_string(
319 p_str => l_str_search,
320 p_temp_sta => l_temp_sta,
321 p_temp_end => l_temp_end,
322 p_provider => l_provider
323 );
324 EXIT WHEN l_break IS NULL;
325 l_end := INSTRB(l_str_search, l_break);
326
327 -- J. del Callar: ignore instances where the delimiter (l_break) is
328 -- not in the search string (l_str_search). This can occur if the
329 -- user specified an end string (l_temp_end) in the call to
330 -- get_terminal_string, but the string was not found in the p_str
331 -- variable.
332 IF l_end <> 0 THEN
333 l_end := l_end + LENGTHB(l_break);
334 l_result := SUBSTRB(l_str_search, 1, l_end);
335 i := i + 1;
336 p_lines(i) := l_result;
337 l_str_search := SUBSTRB(l_str_search, l_end);
338 ELSE
339 -- J. del Callar: set the status of the address lines which did not
340 -- get returned.
341 p_loc_array(i).geometry_status_code := g_error;
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;
351 END get_response_lines;
352
353 --
354 -- PROCEDURE set_matching_geometry
355 --
356 -- DESCRIPTION
357 -- Set the geometry and geometry_status_code attributes of the location
358 -- record identified by p_location_id.
359 --
360 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
361 --
362 -- ARGUMENTS
363 -- IN:
364 -- p_location_id
365 -- p_geometry
366 -- p_geometry_status_code
367 -- p_index
368 -- IN/OUT:
369 -- p_loc_array
370 -- OUT:
371 -- x_return_status Return status after the call. The status can
372 -- be FND_API.G_RET_STS_SUCCESS (success),
373 -- FND_API.G_RET_STS_ERROR (error),
374 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
375 -- x_msg_count Number of messages in message stack.
376 -- x_msg_data Message text if x_msg_count is 1.
377 --
378 -- NOTES
379 --
380 -- MODIFICATION HISTORY
381 --
382 -- 01-28-2002 Joe del Callar Created to facilitate integration with HR.
383 --
384 PROCEDURE set_matching_geometry (
385 p_loc_array IN OUT NOCOPY loc_array,
386 p_location_id IN NUMBER,
387 p_geometry IN mdsys.sdo_geometry,
388 p_geometry_status_code IN VARCHAR2,
389 x_return_status IN OUT NOCOPY VARCHAR2,
390 x_msg_count IN OUT NOCOPY NUMBER,
391 x_msg_data IN OUT NOCOPY VARCHAR2
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;
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;
413 RETURN;
414 END IF;
415 END LOOP;
416
417 -- No matching ID was found in the array: report on this.
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;
425 p_msg_level=>fnd_log.level_error);
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',
426 END IF;
427 END set_matching_geometry;
428
429 -------------------------------
430 -- RETURN Y if country is valid
431 -- N otherwise
432 -------------------------------
433 FUNCTION is_country_valid (
434 p_country_code IN VARCHAR2,
435 p_null_ok IN VARCHAR2 DEFAULT 'Y'
436 ) RETURN VARCHAR2 IS
437 CURSOR c_validcountries IS
438 SELECT al.lookup_code
439 FROM ar_lookups al
440 WHERE al.lookup_type = 'HZ_SPATIAL_VALID_COUNTRIES'
441 AND al.lookup_code = p_country_code;
442 l_country VARCHAR2(30);
443 BEGIN
444 IF g_last_valid_country = p_country_code THEN
445 RETURN 'Y';
446 ELSIF p_null_ok = 'Y' AND p_country_code IS NULL THEN
447 RETURN 'Y';
448 ELSE
449 OPEN c_validcountries;
450 FETCH c_validcountries INTO l_country;
451 IF c_validcountries%NOTFOUND THEN
452 CLOSE c_validcountries;
453 RETURN 'N';
454 END IF;
455 CLOSE c_validcountries;
456
457 -- found a valid country, set the cached value
458 g_last_valid_country := l_country;
459 RETURN 'Y';
460 END IF;
461 END is_country_valid;
462
463 FUNCTION success_output (v IN VARCHAR2) RETURN VARCHAR2 IS
464 BEGIN
465 IF INSTRB(v,'<?XML VERSION="1.0" ENCODING="UTF-8"?>') <> 0 THEN
466 RETURN LTRIM(remove_whitespace(LTRIM(remove_whitespace(v),'<?XML VERSION="1.0" ENCODING="UTF-8"?>')),'<GEOCODE_RESPONSE>');
467 ELSE
468 RETURN v;
469 END IF;
470 END success_output;
471
472 -------------------------------------------------------
473 -- RETURN Y if nls_numeric_character = '.,' US standard
474 -- N otherwise
475 -------------------------------------------------------
476 FUNCTION is_nls_num_char_pt_com RETURN VARCHAR2 IS
477 CURSOR cu_nls_num IS
478 SELECT vp.value
479 FROM v$parameter vp
480 WHERE LOWER(vp.name) = 'nls_numeric_characters';
481 l_value VARCHAR2(10);
482 BEGIN
483 OPEN cu_nls_num;
484 FETCH cu_nls_num INTO l_value;
485 CLOSE cu_nls_num;
486 IF l_value = '.,' THEN
487 RETURN 'Y';
488 ELSE
489 RETURN 'N';
490 END IF;
491 END is_nls_num_char_pt_com;
492
493 --------------------------------------------------
494 -- RETURN the line "<us_form>" for the xml request
495 -- if error then x_return_status := 'E'
496 --------------------------------------------------
497 FUNCTION compose_elocation_detail_old (
498 p_location_id NUMBER,
499 p_country VARCHAR2,
500 p_address1 VARCHAR2,
501 p_address2 VARCHAR2,
502 p_address3 VARCHAR2,
503 p_address4 VARCHAR2,
504 p_city VARCHAR2,
505 p_postal_code VARCHAR2,
506 p_state VARCHAR2,
507 p_name VARCHAR2 DEFAULT NULL,
508 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
509 x_return_status IN OUT NOCOPY VARCHAR2,
510 x_msg_count IN OUT NOCOPY NUMBER,
511 x_msg_data IN OUT NOCOPY VARCHAR2
512 ) RETURN VARCHAR2 IS
513 l_country VARCHAR2(200);
514 l_address1 VARCHAR2(200);
515 l_address2 VARCHAR2(200);
516 l_address3 VARCHAR2(200);
517 l_address4 VARCHAR2(200);
518 l_city VARCHAR2(200);
519 l_postal_code VARCHAR2(200);
520 l_state VARCHAR2(200);
521 l_name VARCHAR2(200);
522 us_form_str VARCHAR2(1250);
523 l_address VARCHAR2(1000);
524 notcorrectaddress EXCEPTION;
525 l_return_status VARCHAR2(10);
526 l_debug_prefix VARCHAR2(30) := '';
527 BEGIN
528 l_return_status := fnd_api.g_ret_sts_success;
529 l_country := xmlize_line(p_country);
530 l_city := xmlize_line(p_city);
531 l_postal_code := xmlize_line(p_postal_code);
532 l_state := xmlize_line(p_state);
533 l_name := xmlize_line(p_name);
534 l_address1 := xmlize_line(p_address1);
535 l_address2 := xmlize_line(p_address2);
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);
545 END IF;
546
547 IF l_city IS NOT NULL AND l_state IS NOT NULL THEN
548 us_form_str := '<us_form2 ';
549 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
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;
557 hz_utility_v2pub.debug(p_message=>'Postal Code :'||l_postal_code||' is not null : Success',
554 ELSIF l_postal_code IS NOT NULL THEN
555 us_form_str := '<us_form1 ';
556 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
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');
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;
571 END IF;
572
573 -- p_name not mandatory
574 IF l_name IS NOT NULL THEN
575 us_form_str := us_form_str || 'name="' || l_name || '" ';
576 END IF;
577 l_address := l_address1;
578 IF l_address2 IS NOT NULL THEN
579 l_address := l_address ||' '||l_address2;
580 END IF;
581 IF l_address3 IS NOT NULL THEN
582 l_address := l_address ||' '||l_address3;
583 END IF;
584 IF l_address4 IS NOT NULL THEN
585 l_address := l_address ||' '||l_address4;
586 END IF;
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;
596 ELSE
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;
606 END IF;
607
608 -- City is mandatory
609 IF l_city IS NOT NULL THEN
610 us_form_str := us_form_str || 'city="' || l_city || '" ';
611 END IF;
612
613 -- State is mandatory
614 IF l_state IS NOT NULL THEN
615 us_form_str := us_form_str || 'state="' || l_state || '" ';
616 END IF;
617
618 -- Postal code is not manadatory
619 IF l_postal_code IS NOT NULL THEN
620 us_form_str := us_form_str || 'lastline="' || l_postal_code || '" ';
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;
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));
633 fnd_message.set_token('COUNTRY', l_country);
634 fnd_msg_pub.add;
635 END IF;
636
637 us_form_str := us_form_str || '/>';
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;
647
648 fnd_msg_pub.count_and_get(
649 p_encoded => fnd_api.g_false,
650 p_count => x_msg_count,
651 p_data => x_msg_data );
652
653 RETURN us_form_str;
654 EXCEPTION
655 WHEN notcorrectaddress THEN
656 us_form_str := NULL;
657 status_handler(l_return_status, x_return_status);
658 fnd_msg_pub.count_and_get(
659 p_encoded => fnd_api.g_false,
660 p_count => x_msg_count,
661 p_data => x_msg_data
662 );
663 RETURN us_form_str;
664 END compose_elocation_detail_old;
665
666 --
667 -- PRIVATE PROCEDURE compose_elocation_detail
668 --
669 -- DESCRIPTION
670 -- Return an XML interpretation of an address in HZ_LOCATIONS. The XML
671 -- returned will be of the following form:
672 -- <unformatted country="[ISO country code]">
673 -- <address_line value="Mr. Ji Yang" />
674 -- <address_line value="Oracle Corp" />
675 -- <address_line value="1 Oracle drive" />
676 -- <address_line value="3rd floor" />
677 -- <address_line value="Nashua" />
678 -- <address_line value="NH" />
679 -- </unformatted>
680 --
681 -- where each address_line value represents an American address column in
682 -- HZ_LOCATIONS.
683 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
684 --
685 -- MODIFICATION HISTORY
686 --
690 FUNCTION compose_elocation_detail (
687 -- 01-23-2002 Joe del Callar Created.
688 --
689 --
691 p_location_id NUMBER,
692 p_country VARCHAR2,
693 p_address1 VARCHAR2,
694 p_address2 VARCHAR2,
695 p_address3 VARCHAR2,
696 p_address4 VARCHAR2,
697 p_city VARCHAR2,
698 p_postal_code VARCHAR2,
699 p_county VARCHAR2,
700 p_state VARCHAR2,
701 p_province VARCHAR2,
702 p_name VARCHAR2 DEFAULT NULL,
703 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
704 x_return_status IN OUT NOCOPY VARCHAR2,
705 x_msg_count IN OUT NOCOPY NUMBER,
706 x_msg_data IN OUT NOCOPY VARCHAR2
707 ) RETURN VARCHAR2 IS
708 l_country VARCHAR2(200);
709 l_address1 VARCHAR2(240);
710 l_address2 VARCHAR2(240);
711 l_address3 VARCHAR2(240);
712 l_address4 VARCHAR2(240);
713 l_city VARCHAR2(200);
714 l_postal_code VARCHAR2(200);
715 l_state VARCHAR2(200);
716 l_county VARCHAR2(200);
717 l_province VARCHAR2(200);
718 l_name VARCHAR2(200);
719 l_xml_address VARCHAR2(1750);
720 l_line_break VARCHAR2(200) := '"/> <address_line value="';
721 l_formatted_address VARCHAR2(1500);
722 l_return_status VARCHAR2(10);
723 l_line_cnt NUMBER;
724 l_formatted_address_tbl hz_format_pub.string_tbl_type;
725 l_debug_prefix VARCHAR2(30) := '';
726 notcorrectaddress EXCEPTION;
727 BEGIN
728 l_return_status := fnd_api.g_ret_sts_success;
729 -- Fix perf bug 3669930, 4220460, remove xmlize_line function call for
730 -- some parameter. For others, check if the value is not null, then
731 -- do xmlize_line
732 l_country := p_country;
733 l_city := p_city;
734 IF(p_postal_code IS NOT NULL) THEN
735 l_postal_code := xmlize_line(p_postal_code);
736 END IF;
737 l_state := p_state;
738 l_name := p_name;
739 l_address1 := xmlize_line(p_address1);
740 IF(p_address2 IS NOT NULL) THEN
741 l_address2 := xmlize_line(p_address2);
742 END IF;
743 IF(p_address3 IS NOT NULL) THEN
744 l_address3 := xmlize_line(p_address3);
745 END IF;
746 IF(p_address4 IS NOT NULL) THEN
747 l_address4 := xmlize_line(p_address4);
748 END IF;
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,
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
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));
771 fnd_message.set_token('COUNTRY', l_country);
772 fnd_msg_pub.add;
773 -- raise the error immediately and forget about processing the rest.
774 RAISE notcorrectaddress;
775 END IF;
776
777 -- if state and city are not null, null out NOCOPY the postal code so that
778 -- it does not get composed into the address, as eLocations has some issues
779 -- with inconsistent city/state/postal code combinations.
780 IF l_city IS NOT NULL AND l_state IS NOT NULL
781 THEN
782 l_postal_code := NULL;
783 END IF;
784
785 -- if state and province are null and the postal code is not, then null
786 -- out NOCOPY the city so that eLocations does its search based only on the
787 -- postal code only.
788 IF l_state IS NULL
789 AND l_province IS NULL
790 AND l_postal_code IS NOT NULL
791 THEN
792 l_city := NULL;
793 END IF;
794
795 -- Set the head of the request.
796 l_xml_address := '<unformatted country="' || l_country
797 || '"> <address_line value="';
798
799 -- call the address format API
800 hz_format_pub.format_eloc_address (
801 p_style_code => 'POSTAL_ADDR',
802 p_style_format_code => NULL,
803 p_line_break => l_line_break,
804 p_space_replace => ' ',
805 p_from_territory_code => l_country,
806 p_address_line_1 => l_address1,
807 p_address_line_2 => l_address2,
808 p_address_line_3 => l_address3,
809 p_address_line_4 => l_address4,
813 p_province => l_province,
810 p_city => l_city,
811 p_postal_code => l_postal_code,
812 p_state => l_state,
814 p_county => l_county,
815 p_country => l_country,
816 x_return_status => l_return_status,
817 x_msg_count => x_msg_count,
818 x_msg_data => x_msg_data,
819 x_formatted_address => l_formatted_address,
820 x_formatted_lines_cnt => l_line_cnt,
821 x_formatted_address_tbl => l_formatted_address_tbl
822 );
823
824 l_xml_address := l_xml_address || l_formatted_address
825 || '"/> </unformatted>';
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,
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,
840 p_data => x_msg_data);
841
842 RETURN l_xml_address;
843 EXCEPTION
844 WHEN notcorrectaddress THEN
845 l_xml_address := NULL;
846 status_handler(l_return_status, x_return_status);
847 fnd_msg_pub.count_and_get(
848 p_encoded => fnd_api.g_false,
849 p_count => x_msg_count,
850 p_data => x_msg_data
851 );
852 RETURN l_xml_address;
853 END compose_elocation_detail;
854
855 ---------------------------------------------------
856 -- RETURN the xml request for 1 to 12 location info
857 -- If error then x_return_status = E
858 ---------------------------------------------------
859 FUNCTION location_xml (
860 p_loc_array IN OUT NOCOPY loc_array,
861 p_name IN VARCHAR2 DEFAULT NULL,
862 p_provider IN VARCHAR2 DEFAULT 'ELOCATION',
863 x_return_status IN OUT NOCOPY VARCHAR2,
864 x_msg_count IN OUT NOCOPY NUMBER,
865 x_msg_data IN OUT NOCOPY VARCHAR2
866 ) RETURN VARCHAR2 IS
867 l_return_status VARCHAR2(10);
868 xml_request VARCHAR2(32640);
869 l_count NUMBER;
870 l_form_us VARCHAR2(2500);
871 at_least_one VARCHAR2(1);
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;
881 l_count := p_loc_array.COUNT;
882 IF l_count <> 0 THEN
883 At_least_one:= 'N';
884 xml_request := '';
885 xml_request := 'xml_request=<?xml version="1.0" standalone="yes" ?>' ||
886 '<geocode_request vendor="elocation">' ||
887 '<address_list>'||FND_GLOBAL.local_chr(10);
888 FOR i IN 1..l_count LOOP
889 l_return_status := fnd_api.g_ret_sts_success;
890 l_loc_rec := p_loc_array(i);
891 l_form_us :=
892 compose_elocation_detail (
893 p_location_id => l_loc_rec.location_id,
894 p_country => l_loc_rec.country,
895 p_address1 => l_loc_rec.address1,
896 p_address2 => l_loc_rec.address2,
897 p_address3 => l_loc_rec.address3,
898 p_address4 => l_loc_rec.address4,
899 p_city => l_loc_rec.city,
900 p_postal_code => l_loc_rec.postal_code,
901 p_county => l_loc_rec.county,
902 p_state => l_loc_rec.state,
903 p_province => l_loc_rec.province,
904 p_name => p_name,
905 p_init_msg_list => fnd_api.g_true,
906 x_return_status => l_return_status,
907 x_msg_count => x_msg_count,
908 x_msg_data => x_msg_data
909 );
910
911 -- J. del Callar: set the error status of any offending records.
912 IF l_return_status <> fnd_api.g_ret_sts_success THEN
913 status_handler(l_return_status, x_return_status);
914 p_loc_array(i).geometry_status_code := g_error;
915 END IF;
916
917 IF l_form_us IS NOT NULL THEN
918 at_least_one:='Y';
919 xml_request := xml_request ||
920 '<input_location id="' ||
921 TO_CHAR(p_loc_array(i).location_id)||'" ' ||
922 'multimatch_number= "3" >' ||
923 '<input_address match_mode="relax_street_type">' ||
924 l_form_us ||
925 '</input_address>' ||
926 '</input_location>' || FND_GLOBAL.local_chr(10);
927 END IF;
928 END LOOP;
929 END IF;
930
931 IF at_least_one = 'Y' THEN
932 xml_request := xml_request ||
933 '</address_list>' ||
934 '</geocode_request>';
935 ELSE
939 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.location_xml (-)',
936 xml_request := '';
937 END IF;
938 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
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,
946 p_data => x_msg_data );
947
948 RETURN xml_request;
949 END location_xml;
950
951 FUNCTION elt_value
952 --------------------------------------------
953 -- RETURN the value of the requested element
954 -- NULL if element NOT FOUND
955 --------------------------------------------
956 ( p_str IN VARCHAR2,
957 p_elt IN VARCHAR2)
958 RETURN VARCHAR2
959 IS
960 retVal VARCHAR2(2000);
961 l_str VARCHAR2(4000);
962 l_elt VARCHAR2(2000);
963 l_pos_deb NUMBER;
964 first_quote NUMBER;
965 second_quote NUMBER;
966 l_equal VARCHAR2(1);
967 l_len NUMBER;
968 i NUMBER;
969 BEGIN
970 i := 1;
971 l_str := upper(p_str);
972 -- Fix perf bug 3669930, 4220460, remove upper of p_elt since the pass in
973 -- value is always in upper case
974 l_elt := p_elt;
975 l_len := lengthb(p_elt);
976 LOOP
977 l_pos_deb := INSTRB(l_str, l_elt, 1, i);
978 IF l_pos_deb = 0 THEN
979 RETURN NULL;
980 ELSE
981 l_equal := SUBSTRB(l_str, l_pos_deb + l_len, 1);
982 IF l_equal <> '=' THEN
983 i := i + 1;
984 ELSE
985 EXIT;
986 END IF;
987 END IF;
988 END LOOP;
989 first_quote := INSTRB(l_str, '"', l_pos_deb, 1);
990 second_quote := INSTRB(l_str, '"', l_pos_deb, 2);
991 retval := SUBSTRB(l_str,
992 first_quote + 1,
993 (second_quote - 1)-(first_quote + 1) + 1);
994 RETURN retval;
995 END elt_value;
996
997 FUNCTION gen_geo
998 (p_SDO_GTYPE NUMBER,
999 p_SDO_SRID NUMBER,
1000 p_xlo NUMBER,
1001 p_yla NUMBER,
1002 p_zdp NUMBER DEFAULT NULL,
1003 p_info_array mdsys.SDO_ELEM_INFO_ARRAY DEFAULT NULL,
1004 p_ordi_array MDSYS.SDO_ORDINATE_ARRAY DEFAULT NULL,
1005 p_provider VARCHAR2 DEFAULT 'ELOCATION')
1006 RETURN MDSYS.SDO_GEOMETRY
1007 IS
1008 l_geo MDSYS.SDO_GEOMETRY;
1009 l_sdo_pt MDSYS.SDO_POINT_TYPE;
1010 BEGIN
1011 -- p_sdo_gtype = 2001; --
1012 -- p_sdo_srid = 8307; --
1013 l_sdo_pt := MDSYS.SDO_POINT_TYPE(p_xlo,
1014 p_yla,
1015 p_zdp);
1016 l_geo := MDSYS.SDO_GEOMETRY(p_sdo_gtype,
1017 p_sdo_srid,
1018 l_sdo_pt,
1019 p_info_array,
1020 p_ordi_array);
1021 RETURN l_geo;
1022 END gen_geo;
1023
1024 PROCEDURE non_num_handle(
1025 Elt_name IN VARCHAR2,
1026 Elt_value IN VARCHAR2,
1027 p_location_id IN NUMBER,
1028 x_return_status IN OUT NOCOPY VARCHAR2,
1029 x_msg_count IN OUT NOCOPY NUMBER,
1030 x_msg_data IN OUT NOCOPY VARCHAR2)
1031 IS
1032 BEGIN
1033 x_return_status := fnd_api.g_ret_sts_error;
1034 fnd_message.set_name('AR', 'HZ_NUMBER_MANDATORY');
1035 fnd_message.set_token('VALUE',Elt_value);
1036 fnd_message.set_token('ELT',Elt_name);
1037 fnd_message.set_token('LOC_ID',TO_CHAR(p_location_id));
1038 fnd_msg_pub.add;
1039 END non_num_handle;
1040
1041 --
1042 -- PROCEDURE parse_one_response
1043 --
1044 -- DESCRIPTION
1045 -- Accept One location Xml
1046 -- Do Validation (GEOCODE ID mandatory, LATITUDE mandatory, LONGITUDE
1047 -- mandatory, STREET warn, MATCH_COUNT warn)
1048 -- Do update TCA Registry
1049 -- If error then x_return_status = E
1050 --
1051 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1052 --
1053 -- ARGUMENTS
1054 -- IN:
1055 -- p_str
1056 -- IN/OUT:
1057 -- x_loc_id
1058 -- x_geo
1059 -- x_geo_status
1060 -- x_return_status Return status after the call. The status can
1061 -- be FND_API.G_RET_STS_SUCCESS (success),
1062 -- FND_API.G_RET_STS_ERROR (error),
1063 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1064 -- x_msg_count Number of messages in message stack.
1065 -- x_msg_data Message text if x_msg_count is 1.
1066 --
1067 -- NOTES
1068 --
1069 -- MODIFICATION HISTORY
1070 --
1071 -- 01-09-2002 Herve Yu Created.
1072 -- 01-28-2002 Joe del Callar Modified to return the geometry in order
1073 -- to facilitate integration with HR.
1074 --
1075
1076 PROCEDURE parse_one_response (
1077 p_str IN VARCHAR2,
1078 x_loc_id IN OUT NOCOPY NUMBER,
1079 x_geo IN OUT NOCOPY mdsys.sdo_geometry,
1080 x_geo_status IN OUT NOCOPY VARCHAR2,
1081 x_return_status IN OUT NOCOPY VARCHAR2,
1082 x_msg_count IN OUT NOCOPY NUMBER,
1086 l_long_found BOOLEAN := FALSE;
1083 x_msg_data IN OUT NOCOPY VARCHAR2
1084 ) IS
1085 l_recep VARCHAR2(1000);
1087 l_lat_found BOOLEAN := FALSE;
1088 l_latitude NUMBER;
1089 l_longitude NUMBER;
1090 l_match_count NUMBER;
1091 l_match_count_temp VARCHAR2(1000);
1092 l_street VARCHAR2(1200);
1093 l_debug_prefix VARCHAR2(30) := '';
1094 l_return_status VARCHAR2(10);
1095 BEGIN
1096 -- Procedure pour tester la possibilite de transformer l_recep en nombre qui soit controle
1097 -- Afin de capturer les erreurs evebntuellement de Alfa num.
1098 l_return_status := fnd_api.g_ret_sts_success;
1099
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;
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;
1121 END IF;
1122
1123 -- J. del Callar: convert location ID into a number, but handle non-numeric
1124 -- value errors.
1125 BEGIN
1126 x_loc_id := TO_NUMBER(l_recep);
1127 EXCEPTION
1128 WHEN OTHERS THEN
1129 non_num_handle('GEOCODE ID', l_recep, x_loc_id,
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;
1139
1140 -- determine the match count.
1141 l_match_count_temp := elt_value(p_str, 'MATCH_COUNT');
1142
1143 l_recep := elt_value(p_str, 'LATITUDE');
1144 IF l_recep IS NULL THEN
1145 l_return_status := fnd_api.g_ret_sts_error;
1146
1147 -- J. del Callar: report this error to the user only if a row was
1148 -- returned. if a row was not returned, the error gets reported in a
1149 -- more meaningful way later on.
1150 IF l_match_count_temp <> '0' THEN
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;
1160 END IF;
1161 status_handler(l_return_status, x_return_status);
1162 l_lat_found := FALSE;
1163 ELSE
1164 -- J. del Callar: convert latitude into a number, but handle non-numeric
1165 -- value errors.
1166 BEGIN
1167 l_latitude := TO_NUMBER(l_recep);
1168 EXCEPTION
1169 WHEN OTHERS THEN
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;
1179 l_lat_found := TRUE;
1180 END IF;
1181
1182 l_recep := elt_value( p_str, 'LONGITUDE');
1183 IF l_recep IS NULL THEN
1184 l_return_status := fnd_api.g_ret_sts_error;
1185
1186 -- J. del Callar: report this error to the user only if a row was
1187 -- returned. if a row was not returned, the error gets reported in a
1188 -- more meaningful way later on.
1189 IF l_match_count_temp <> '0' THEN
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;
1199 END IF;
1200
1201 status_handler(l_return_status, x_return_status);
1202 l_long_found := FALSE;
1203 ELSE
1204 -- J. del Callar: convert latitude into a number, but handle non-numeric
1205 -- value errors.
1206 BEGIN
1207 l_longitude := TO_NUMBER(l_recep);
1208 EXCEPTION
1209 WHEN OTHERS THEN
1210 non_num_handle('LONGITUDE', l_recep, x_loc_id,
1214 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1211 l_return_status, x_msg_count, x_msg_data);
1212 status_handler(l_return_status, x_return_status);
1213 END;
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;
1222 END IF;
1223
1224 l_street := elt_value(p_str, 'STREET');
1225
1226 -- J. del Callar: inform user that the city center was returned by the
1227 -- spatial data provider. The message should only occur if the street is
1228 -- not found, but the latitude and longitude were found.
1229 IF l_street IS NULL AND l_lat_found AND l_long_found THEN
1230 l_return_status := g_warning;
1231 fnd_message.set_name('AR','HZ_MISSING_STREET');
1232 fnd_message.set_token('LOC_ID', TO_CHAR(x_loc_id));
1233 fnd_msg_pub.add;
1234 status_handler(l_return_status, x_return_status);
1235
1236 -- J. del Callar: set the status reported in
1237 -- HZ_LOCATIONS.GEOMETRY_STATUS_CODE to NOEXACTMATCH to reflect the fact
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
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;
1254 END IF;
1255
1256 -- J. del Callar: convert match count into a number and handle non-numeric
1257 -- errors.
1258 BEGIN
1259 l_match_count := TO_NUMBER(l_match_count_temp);
1260
1261 IF l_match_count > 1 THEN
1262 l_return_status := g_warning;
1263 fnd_message.set_name('AR','HZ_NB_MATCHES');
1264 fnd_message.set_token('NB', l_match_count_temp);
1265 fnd_message.set_token('LOC_ID', TO_CHAR(x_loc_id));
1266 fnd_msg_pub.add;
1267
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;
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');
1280 fnd_message.set_token('NB', l_match_count_temp);
1281 fnd_message.set_token('LOC_ID', TO_CHAR(x_loc_id));
1282 fnd_msg_pub.add;
1283
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',
1293 p_msg_level=>fnd_log.level_error);
1294 END IF;
1295
1296 END IF;
1297 status_handler(l_return_status, x_return_status);
1298 EXCEPTION
1299 WHEN OTHERS THEN
1300 non_num_handle('MATCH_COUNT', l_match_count_temp, x_loc_id,
1301 l_return_status, x_msg_count, x_msg_data);
1302 status_handler(l_return_status, x_return_status);
1303 END;
1304
1305 IF l_return_status <> fnd_api.g_ret_sts_error THEN
1306 x_geo := gen_geo(p_sdo_gtype => 2001,
1307 p_sdo_srid => 8307,
1308 p_xlo => l_longitude,
1309 p_yla => l_latitude);
1310
1311 status_handler(l_return_status, x_return_status);
1312 ELSE
1313 x_geo := NULL;
1314 status_handler(l_return_status, x_return_status);
1315 END IF;
1316
1317 fnd_msg_pub.count_and_get(
1318 p_encoded => fnd_api.g_false,
1319 p_count => x_msg_count,
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;
1329 END parse_one_response;
1330
1331 --
1332 -- PRIVATE PROCEDURE
1333 -- parse_responses
1334 --
1335 -- DESCRIPTION
1336 -- Accept One location Xml
1337 -- Do Validation (GEOCODE ID mandatory, LATITUDE mandatory, LONGITUDE
1338 -- mandatory, STREET warn, MATCH_COUNT warn)
1339 -- Do update TCA Registry
1340 -- If error then x_return_status = E
1341 --
1342 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1346 -- p_str
1343 --
1344 -- ARGUMENTS
1345 -- IN:
1347 -- IN/OUT:
1348 -- x_loc_id
1349 -- x_geo
1350 -- x_geo_status
1351 -- x_return_status Return status after the call. The status can
1352 -- be FND_API.G_RET_STS_SUCCESS (success),
1353 -- FND_API.G_RET_STS_ERROR (error),
1354 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1355 -- x_msg_count Number of messages in message stack.
1356 -- x_msg_data Message text if x_msg_count is 1.
1357 --
1358 -- NOTES
1359 --
1360 -- MODIFICATION HISTORY
1361 --
1362 -- 01-09-2002 Herve Yu Created.
1363 -- 01-28-2002 Joe del Callar Modified to return the geometry in order
1364 -- to facilitate integration with HR.
1365 --
1366 PROCEDURE parse_responses (
1367 p_tab_address IN array_t,
1368 p_loc_array IN OUT NOCOPY loc_array,
1369 x_return_status IN OUT NOCOPY VARCHAR2,
1370 x_msg_count IN OUT NOCOPY NUMBER,
1371 x_msg_data IN OUT NOCOPY VARCHAR2
1372 ) IS
1373 l_message VARCHAR2(4000);
1374 i NUMBER;
1375 j NUMBER;
1376 l_return_status VARCHAR2(20);
1377 l_msg_count NUMBER;
1378 l_msg_data VARCHAR2(2000);
1379 l_geometry mdsys.sdo_geometry;
1380 l_geometry_status_code VARCHAR2(30);
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;
1390 l_return_status := fnd_api.g_ret_sts_success;
1391 i := p_tab_address.COUNT;
1392 IF i > 0 THEN
1393 FOR j IN 1..i LOOP
1394 parse_one_response(
1395 p_str => p_tab_address(j),
1396 x_loc_id => l_location_id,
1397 x_geo => l_geometry,
1398 x_geo_status => l_geometry_status_code,
1399 x_return_status => l_return_status,
1400 x_msg_count => l_msg_count,
1401 x_msg_data => l_msg_data
1402 );
1403 status_handler(l_return_status, x_return_status);
1404 set_matching_geometry(p_loc_array,
1405 l_location_id,
1406 l_geometry,
1407 l_geometry_status_code,
1408 l_return_status,
1409 x_msg_count,
1410 x_msg_data);
1411 status_handler(l_return_status, x_return_status);
1412 END LOOP;
1413 -- all records with an unset geometry status are considered to be in
1414 -- error - set their status
1415 FOR j IN 1..p_loc_array.COUNT LOOP
1416 IF p_loc_array(j).geometry_status_code IS NULL THEN
1417 p_loc_array(j).geometry_status_code := g_error;
1418 END IF;
1419 END LOOP;
1420 END IF;
1421 fnd_msg_pub.count_and_get(
1422 p_encoded => fnd_api.g_false,
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;
1432 END parse_responses;
1433
1434 --
1435 -- PUBLIC FUNCTION
1436 -- in_bypass_list
1437 --
1438 -- DESCRIPTION
1439 -- Returns TRUE if the argument p_url_target is in p_exclusion_list, FALSE
1440 -- otherwise. Used to determine whether or not to use a proxy. This
1441 -- functionality can only be used with fixed-length character set
1442 -- exclusion lists and targets, which is okay since these are URLs.
1443 --
1444 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1445 --
1446 -- ARGUMENTS
1447 -- IN:
1448 -- p_target_url
1449 -- p_exclusion_list
1450 --
1451 -- NOTES
1452 --
1453 -- MODIFICATION HISTORY
1454 --
1455 -- 03-27-2002 J. del Callar Created.
1456 --
1457 FUNCTION in_bypass_list (
1458 p_url_target IN VARCHAR2,
1459 p_exclusion_list IN VARCHAR2
1460 ) RETURN BOOLEAN IS
1461 l_exclusion_list VARCHAR2(2000) := LOWER(p_exclusion_list);
1462 l_excluded_domain VARCHAR2(240);
1463 l_delimiter VARCHAR2(1);
1464 l_pos NUMBER;
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;
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,
1480 p_msg_level=>fnd_log.level_statement);
1477 p_msg_level=>fnd_log.level_statement);
1478 hz_utility_v2pub.debug(p_message=>'In list:',
1479 p_prefix =>l_debug_prefix,
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)
1488 -- delimiters. Java-style is given priority.
1489 IF INSTRB(l_exclusion_list, '|') > 0 THEN
1490 l_delimiter := '|';
1491 ELSIF INSTRB(l_exclusion_list, ',') > 0 THEN
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;
1501
1502 --disable_debug;
1503
1504 RETURN FALSE;
1505 END IF;
1506
1507 -- get the domain portion of the URL.
1508 -- first, put the domain in the same case as the exclusion list.
1509 l_url_domain := LOWER(p_url_target);
1510
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,
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, ':/');
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,
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.
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,
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
1553 l_exclusion_list := REPLACE(l_exclusion_list, ' ');
1554
1555 WHILE l_exclusion_list IS NOT NULL LOOP
1556 -- get the position of the 1st delimiter in the remaining exclusion list
1557 l_pos := INSTRB(l_exclusion_list, l_delimiter);
1558
1559 IF l_pos = 0 THEN
1560 -- no delimiters implies that this is the last domain to be checked.
1561 l_excluded_domain := l_exclusion_list;
1562 ELSE
1563 -- need to do a SUBSTRB if there is a delimiter in the exclusion list
1564 -- to get the first domain left in the exclusion list.
1565 l_excluded_domain := SUBSTRB(l_exclusion_list, 1, l_pos-1);
1566 END IF;
1567
1568 -- The domain should not have a % sign in it because it should be a
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,
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
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
1592 -- a match was found, return a positive result.
1593 RETURN TRUE;
1594 END IF;
1595
1596 IF l_pos = 0 THEN
1597 -- no more domains to be checked if no delimiters were found.
1601 l_exclusion_list := SUBSTRB(l_exclusion_list, l_pos+1);
1598 l_exclusion_list := NULL;
1599 ELSE
1600 -- get the remaining domain exclusions to be checked.
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;
1609
1610 --disable_debug;
1611
1612 -- no domain match was found, return false
1613 RETURN FALSE;
1614 END in_bypass_list;
1615
1616 --
1617 -- PUBLIC PROCEDURE
1618 -- get_spatial_coords
1619 --
1620 -- DESCRIPTION
1621 -- Build the xml request for n locations
1622 -- Post the Xml request
1623 -- Split the Response into individual responses
1624 -- Parse and update hz_locations with the responses
1625 -- If error Then x_return_status = E
1626 --
1627 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1628 --
1629 -- ARGUMENTS
1630 -- IN:
1631 -- p_name
1632 -- p_http_ad
1633 -- p_proxy
1634 -- p_port
1635 -- p_retry
1636 -- p_init_msg_list
1637 -- IN/OUT:
1638 -- p_loc_array
1639 -- OUT:
1640 -- x_return_status Return status after the call. The status can
1641 -- be FND_API.G_RET_STS_SUCCESS (success),
1642 -- FND_API.G_RET_STS_ERROR (error),
1643 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1644 -- x_msg_count Number of messages in message stack.
1645 -- x_msg_data Message text if x_msg_count is 1.
1646 --
1647 -- NOTES
1648 --
1649 -- MODIFICATION HISTORY
1650 --
1651 -- 01-09-2002 Herve Yu Created.
1652 -- 01-28-2002 Joe del Callar Modified to return the geometry in the
1653 -- loc_array structure in order to facilitate
1654 -- integration with HR.
1655 --
1656
1657 PROCEDURE get_spatial_coords (
1658 p_loc_array IN OUT NOCOPY loc_array,
1659 p_name IN VARCHAR2 DEFAULT NULL,
1660 p_http_ad IN VARCHAR2,
1661 p_proxy IN VARCHAR2 DEFAULT NULL,
1662 p_port IN NUMBER DEFAULT NULL,
1663 p_retry IN NUMBER DEFAULT 3,
1664 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
1665 x_return_status OUT NOCOPY VARCHAR2,
1666 x_msg_count OUT NOCOPY NUMBER,
1667 x_msg_data OUT NOCOPY VARCHAR2
1668 ) IS
1669 lxml VARCHAR2(32767);
1670 lresp VARCHAR2(32767);
1671 lrespct VARCHAR2(200);
1672 ltab array_t;
1673 msg VARCHAR2(4000);
1674 cpt NUMBER;
1675 l_return_status VARCHAR2(10);
1676 l_err_resp VARCHAR2(32767);
1677 exchttp EXCEPTION;
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;
1687
1688 l_return_status := fnd_api.g_ret_sts_success;
1689
1690 IF fnd_api.to_boolean(p_init_msg_list) THEN
1691 fnd_msg_pub.initialize;
1692 END IF;
1693
1694 lxml := location_xml(p_loc_array => p_loc_array,
1695 p_name => p_name,
1696 p_provider => 'ELOCATION',
1697 x_return_status => l_return_status,
1698 x_msg_count => x_msg_count,
1699 x_msg_data => x_msg_data);
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;
1709 cpt := 0;
1710 LOOP
1711 cpt := cpt + 1;
1712 hz_http_pkg.post(
1713 doc => lxml,
1714 content_type =>'application/x-www-form-urlencoded',
1715 url => p_http_ad,
1716 resp => lresp,
1717 resp_content_type => lrespct,
1718 proxyserver => p_proxy,
1719 proxyport => p_port,
1720 err_resp => l_err_resp,
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;
1730 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1734 END IF;
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);
1735 RAISE exchttp;
1736 END IF;
1740
1737 EXIT WHEN lresp IS NOT NULL;
1738 EXIT WHEN cpt > p_retry;
1739 END LOOP;
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
1749 -- The post did not succeed, even after several retries. This is an
1750 -- unrecoverable error.
1751 l_return_status := fnd_api.g_ret_sts_unexp_error;
1752 fnd_message.set_name('AR','HZ_HTTP_POST_FAILED');
1753 fnd_message.set_token('RETRY', p_retry);
1754 fnd_message.set_token('LASTMSG', NVL(l_err_resp, '<NULL>'));
1755 fnd_msg_pub.add;
1756
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;
1766 status_handler(l_return_status, x_return_status);
1767 ELSIF UPPER(lresp) NOT LIKE '%<GEOCODE_RESPONSE>%' THEN
1768 -- Spatial response was not parseable. This is an unrecoverable error.
1769 l_return_status := fnd_api.g_ret_sts_unexp_error;
1770 fnd_message.set_name('AR','HZ_MALFORMED_SPATIAL_RESPONSE');
1771 -- limit the size of token to 200 characters, otherwise if lresp is too
1772 -- long, will get buffer too small error
1773 fnd_message.set_token('RESP', substrb(lresp,1,200));
1774 fnd_msg_pub.add;
1775
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;
1785 status_handler(l_return_status, x_return_status);
1786 ELSE
1787 get_response_lines(
1788 p_str => lresp,
1789 p_lines => ltab,
1790 p_temp_sta => 'geocode',
1791 p_temp_end => '</GEOCODE>',
1792 p_root_text => '<GEOCODE_RESPONSE>',
1793 p_provider => 'ELOCATION',
1794 p_loc_array => p_loc_array
1795 );
1796
1797 IF ltab.COUNT = 0 THEN
1798 -- Set the error status for all records in this batch since no rows
1799 -- were returned by the data provider.
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;
1809 ELSE
1810 parse_responses(
1811 p_tab_address => ltab,
1812 p_loc_array => p_loc_array,
1813 x_return_status => l_return_status,
1814 x_msg_count => x_msg_count,
1815 x_msg_data => x_msg_data
1816 );
1817
1818 status_handler(l_return_status, x_return_status);
1819 END IF;
1820 END IF;
1821 END IF;
1822 fnd_msg_pub.count_and_get(
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;
1832
1833 --disable_debug;
1834 EXCEPTION
1835 WHEN exchttp THEN
1836 status_handler(l_return_status, x_return_status);
1837 --disable_debug;
1838 END get_spatial_coords;
1839
1840 END hz_geocode_pkg;
[Home] [Help]