1 PACKAGE BODY hz_geocode_pkg AS
2 /*$Header: ARHGEOCB.pls 120.22.12020000.2 2012/07/19 07:23:59 rgokavar ship $*/
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, fnd_global.local_chr(38)); -- get rid of ampersands
247 l_str := REPLACE(l_str, '<'); -- get rid of open brackets
248 l_str := REPLACE(l_str, '>'); -- get rid of close brackets
249 l_str := REPLACE(l_str, '"'); -- get rid of double quotes
250 l_str := REPLACE(l_str, ''''); -- get rid of double quotes
251 l_str := REPLACE(l_str, '#'); -- get rid of hash
252 l_str := REPLACE(l_str, '%'); -- get rid of percentage
253 l_str := REPLACE(l_str, '+'); -- get rid of plus
254 --l_str := REPLACE(l_str, '&'); -- get rid of ampersands
255
256
257
258 RETURN remove_whitespace(l_str);
259 END xmlize_line;
260
261 --
262 -- PRIVATE PROCEDURE
263 -- get_response_lines
264 -- DESCRIPTION
265 -- Fill a VARRAY with individual response from the website
266 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
267 -- hz_utility_v2pub.debug
268 -- MODIFICATION HISTORY
269 -- 01-10-2002 Herve Yu
270 -- 04-01-2002 J. del Callar Put comments in. Added p_loc_array
271 -- parameter so that this procedure can
272 -- set the geometry status for instances
273 -- where the location was not returned by
274 -- the data provider.
275 --
276
277 PROCEDURE get_response_lines (
278 p_str IN VARCHAR2,
279 p_lines IN OUT NOCOPY hz_geocode_pkg.array_t,
280 p_loc_array IN OUT NOCOPY loc_array,
281 p_temp_sta IN VARCHAR2,
282 p_temp_end IN VARCHAR2 DEFAULT NULL,
283 p_root_text IN VARCHAR2 DEFAULT NULL,
284 p_provider IN VARCHAR2 DEFAULT 'ELOCATION'
285 ) IS
286 l_init NUMBER;
287 l_end NUMBER;
288 l_str_search VARCHAR2(32767);
289 l_result VARCHAR2(8000);
290 l_len NUMBER;
291 l_break VARCHAR2(1000);
292 l_temp_sta VARCHAR2(1000);
293 l_temp_end VARCHAR2(1000);
294 l_provider VARCHAR2(1000);
295 l_pos NUMBER := 0;
296 i NUMBER;
297 l_root_text VARCHAR2(1000);
298 l_debug_prefix VARCHAR2(30) := '';
299 BEGIN
300 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
301 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.get_response_lines (+)',
302 p_prefix=>l_debug_prefix,
303 p_msg_level=>fnd_log.level_procedure);
304 END IF;
305
306 l_root_text := UPPER(p_root_text);
307 l_temp_sta := '<'||UPPER(LTRIM(p_temp_sta,'<'));
308 l_temp_end := UPPER(p_temp_end);
309 i := 0;
310 l_provider := UPPER(p_provider);
311 l_len := LENGTHB(p_str);
312 l_str_search := UPPER(p_str);
313 l_init := 1;
314 l_end := INSTRB(l_str_search, l_temp_sta);
315 IF l_end <> 0 THEN
316 l_str_search := LTRIM(l_str_search, '<?XML VERSION="1.0" ENCODING="UTF-8" ?>');
317 l_str_search := remove_whitespace(l_str_search);
318 l_str_search := LTRIM(l_str_search, l_root_text);
319 LOOP
320 l_break := get_terminal_string(
321 p_str => l_str_search,
322 p_temp_sta => l_temp_sta,
323 p_temp_end => l_temp_end,
324 p_provider => l_provider
325 );
326 EXIT WHEN l_break IS NULL;
327 l_end := INSTRB(l_str_search, l_break);
328
329 -- J. del Callar: ignore instances where the delimiter (l_break) is
330 -- not in the search string (l_str_search). This can occur if the
331 -- user specified an end string (l_temp_end) in the call to
332 -- get_terminal_string, but the string was not found in the p_str
333 -- variable.
334 IF l_end <> 0 THEN
335 l_end := l_end + LENGTHB(l_break);
336 l_result := substr(SUBSTRB(l_str_search, 1, l_end),1,4000);
337 i := i + 1;
338 p_lines(i) := l_result;
339 l_str_search := SUBSTRB(l_str_search, l_end);
340 ELSE
341 -- J. del Callar: set the status of the address lines which did not
342 -- get returned.
343 IF i = 0 THEN
344 i := i + 1;
345 END IF;
346 p_loc_array(i).geometry_status_code := g_error;
347 EXIT;
348 END IF;
349 END LOOP;
350 END IF;
351 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
352 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.get_response_lines (-)',
353 p_prefix=>l_debug_prefix,
354 p_msg_level=>fnd_log.level_procedure);
355 END IF;
356 END get_response_lines;
357
358 --
359 -- PROCEDURE set_matching_geometry
360 --
361 -- DESCRIPTION
362 -- Set the geometry and geometry_status_code attributes of the location
363 -- record identified by p_location_id.
364 --
365 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
366 --
367 -- ARGUMENTS
368 -- IN:
369 -- p_location_id
370 -- p_geometry
371 -- p_geometry_status_code
372 -- p_index
373 -- IN/OUT:
374 -- p_loc_array
375 -- OUT:
376 -- x_return_status Return status after the call. The status can
377 -- be FND_API.G_RET_STS_SUCCESS (success),
378 -- FND_API.G_RET_STS_ERROR (error),
379 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
380 -- x_msg_count Number of messages in message stack.
381 -- x_msg_data Message text if x_msg_count is 1.
382 --
383 -- NOTES
384 --
385 -- MODIFICATION HISTORY
386 --
387 -- 01-28-2002 Joe del Callar Created to facilitate integration with HR.
388 -- 29-NOV-2010 Sudhir Gokavarapu ER10211310 - Added logic for geometry_accuracy.
389 --
390 PROCEDURE set_matching_geometry (
391 p_loc_array IN OUT NOCOPY loc_array,
392 p_location_id IN NUMBER,
393 p_geometry IN mdsys.sdo_geometry,
394 p_geometry_status_code IN VARCHAR2,
395 p_geometry_accuracy IN NUMBER,
396 x_return_status IN OUT NOCOPY VARCHAR2,
397 x_msg_count IN OUT NOCOPY NUMBER,
398 x_msg_data IN OUT NOCOPY VARCHAR2
399 ) IS
400 i NUMBER;
401 l_debug_prefix VARCHAR2(30) := '';
402 BEGIN
403 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
404 hz_utility_v2pub.debug(p_message=>'hz_geocode_pkg.set_matching_geometry (+)',
405 p_prefix=>l_debug_prefix,
406 p_msg_level=>fnd_log.level_procedure);
407 END IF;
408 FOR i IN 1..p_loc_array.COUNT LOOP
409 -- find the matching location ID in the array. set the geometry and
410 -- geometry status, and return if successful.
411 IF p_loc_array(i).location_id = p_location_id THEN
412 p_loc_array(i).geometry := p_geometry;
413 p_loc_array(i).geometry_status_code := p_geometry_status_code;
414 p_loc_array(i).geometry_accuracy := p_geometry_accuracy;
415 -- Debug info.
416 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
417 hz_utility_v2pub.debug(p_message=>'hz_geocode_pkg.set_matching_geometry (-)',
418 p_prefix=>l_debug_prefix,
419 p_msg_level=>fnd_log.level_procedure);
420 END IF;
421 RETURN;
422 END IF;
423 END LOOP;
424
425 -- No matching ID was found in the array: report on this.
426 x_return_status := fnd_api.g_ret_sts_error;
427 fnd_message.set_name('AR', 'HZ_NO_LOCATION_FOUND');
428 fnd_message.set_token('LOC_ID', TO_CHAR(p_location_id));
429 fnd_msg_pub.add;
430 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
431 hz_utility_v2pub.debug(p_message=>'Location record not found',
432 p_prefix=>'ERROR',
433 p_msg_level=>fnd_log.level_error);
434 END IF;
435 END set_matching_geometry;
436
437 -------------------------------
438 -- RETURN Y if country is valid
439 -- N otherwise
440 -------------------------------
441 FUNCTION is_country_valid (
442 p_country_code IN VARCHAR2,
443 p_null_ok IN VARCHAR2 DEFAULT 'Y'
444 ) RETURN VARCHAR2 IS
445 CURSOR c_validcountries IS
446 SELECT al.lookup_code
447 FROM ar_lookups al
448 WHERE al.lookup_type = 'HZ_SPATIAL_VALID_COUNTRIES'
449 AND al.lookup_code = p_country_code;
450 l_country VARCHAR2(30);
451 BEGIN
452 IF g_last_valid_country = p_country_code THEN
453 RETURN 'Y';
454 ELSIF p_null_ok = 'Y' AND p_country_code IS NULL THEN
455 RETURN 'Y';
456 ELSE
457 OPEN c_validcountries;
458 FETCH c_validcountries INTO l_country;
459 IF c_validcountries%NOTFOUND THEN
460 CLOSE c_validcountries;
461 RETURN 'N';
462 END IF;
463 CLOSE c_validcountries;
464
465 -- found a valid country, set the cached value
466 g_last_valid_country := l_country;
467 RETURN 'Y';
468 END IF;
469 END is_country_valid;
470
471 FUNCTION success_output (v IN VARCHAR2) RETURN VARCHAR2 IS
472 BEGIN
473 IF INSTRB(v,'<?XML VERSION="1.0" ENCODING="UTF-8"?>') <> 0 THEN
474 RETURN LTRIM(remove_whitespace(LTRIM(remove_whitespace(v),'<?XML VERSION="1.0" ENCODING="UTF-8"?>')),'<GEOCODE_RESPONSE>');
475 ELSE
476 RETURN v;
477 END IF;
478 END success_output;
479
480 -------------------------------------------------------
481 -- RETURN Y if nls_numeric_character = '.,' US standard
482 -- N otherwise
483 -------------------------------------------------------
484 FUNCTION is_nls_num_char_pt_com RETURN VARCHAR2 IS
485 CURSOR cu_nls_num IS
486 SELECT vp.value
487 FROM v$parameter vp
488 WHERE LOWER(vp.name) = 'nls_numeric_characters';
489 l_value VARCHAR2(10);
490 BEGIN
491 OPEN cu_nls_num;
492 FETCH cu_nls_num INTO l_value;
493 CLOSE cu_nls_num;
494 IF l_value = '.,' THEN
495 RETURN 'Y';
496 ELSE
497 RETURN 'N';
498 END IF;
499 END is_nls_num_char_pt_com;
500
501 --------------------------------------------------
502 -- RETURN the line "<us_form>" for the xml request
503 -- if error then x_return_status := 'E'
504 --------------------------------------------------
505 FUNCTION compose_elocation_detail_old (
506 p_location_id NUMBER,
507 p_country VARCHAR2,
508 p_address1 VARCHAR2,
509 p_address2 VARCHAR2,
510 p_address3 VARCHAR2,
511 p_address4 VARCHAR2,
512 p_city VARCHAR2,
513 p_postal_code VARCHAR2,
514 p_state VARCHAR2,
515 p_name VARCHAR2 DEFAULT NULL,
516 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
517 x_return_status IN OUT NOCOPY VARCHAR2,
518 x_msg_count IN OUT NOCOPY NUMBER,
519 x_msg_data IN OUT NOCOPY VARCHAR2
520 ) RETURN VARCHAR2 IS
521 l_country VARCHAR2(200);
522 l_address1 VARCHAR2(200);
523 l_address2 VARCHAR2(200);
524 l_address3 VARCHAR2(200);
525 l_address4 VARCHAR2(200);
526 l_city VARCHAR2(200);
527 l_postal_code VARCHAR2(200);
528 l_state VARCHAR2(200);
529 l_name VARCHAR2(200);
530 us_form_str VARCHAR2(1250);
531 l_address VARCHAR2(1000);
532 notcorrectaddress EXCEPTION;
533 l_return_status VARCHAR2(10);
534 l_debug_prefix VARCHAR2(30) := '';
535 BEGIN
536 l_return_status := fnd_api.g_ret_sts_success;
537 l_country := xmlize_line(p_country);
538 l_city := xmlize_line(p_city);
539 l_postal_code := xmlize_line(p_postal_code);
540 l_state := xmlize_line(p_state);
541 l_name := xmlize_line(p_name);
542 l_address1 := xmlize_line(p_address1);
543 l_address2 := xmlize_line(p_address2);
544 l_address3 := xmlize_line(p_address3);
545 l_address4 := xmlize_line(p_address4);
546 us_form_str := '';
547
548 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
549 hz_utility_v2pub.debug(p_message=>g_pkg_name ||'.compose_elocation_detail_old for location_id:'
550 || TO_CHAR(p_location_id) ||' (+)',
551 p_prefix=>l_debug_prefix,
552 p_msg_level=>fnd_log.level_procedure);
553 END IF;
554
555 IF l_city IS NOT NULL AND l_state IS NOT NULL THEN
556 us_form_str := '<us_form2 ';
557 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
558 hz_utility_v2pub.debug(p_message=>'City :'||l_city||' and state :'||l_state||' are not null : Success',
559 p_prefix =>l_debug_prefix,
560 p_msg_level=>fnd_log.level_statement);
561 END IF;
562 ELSIF l_postal_code IS NOT NULL THEN
563 us_form_str := '<us_form1 ';
564 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
565 hz_utility_v2pub.debug(p_message=>'Postal Code :'||l_postal_code||' is not null : Success',
566 p_prefix =>l_debug_prefix,
567 p_msg_level=>fnd_log.level_statement);
568 END IF;
569 ELSE
570 l_return_status := fnd_api.g_ret_sts_error;
571 fnd_message.set_name('AR', 'HZ_ZIP_OR_CITY_AND_STATE');
572 fnd_message.set_token('LOC_ID', TO_CHAR(p_location_id));
573 fnd_msg_pub.add;
574 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
575 hz_utility_v2pub.debug(p_message=>'City, state and postal code are null : Error',
576 p_prefix=>'ERROR',
577 p_msg_level=>fnd_log.level_error);
578 END IF;
579 END IF;
580
581 -- p_name not mandatory
582 IF l_name IS NOT NULL THEN
583 us_form_str := us_form_str || 'name="' || l_name || '" ';
584 END IF;
585 l_address := l_address1;
586 IF l_address2 IS NOT NULL THEN
587 l_address := l_address ||' '||l_address2;
588 END IF;
589 IF l_address3 IS NOT NULL THEN
590 l_address := l_address ||' '||l_address3;
591 END IF;
592 IF l_address4 IS NOT NULL THEN
593 l_address := l_address ||' '||l_address4;
594 END IF;
595
596 -- Address1 is mandatory
597 IF l_address IS NOT NULL THEN
598 us_form_str := us_form_str || 'street="' || l_address ||'" ';
599 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
600 hz_utility_v2pub.debug(p_message=>'Address : '||l_address ||' is not null : Success',
601 p_prefix =>l_debug_prefix,
602 p_msg_level=>fnd_log.level_statement);
603 END IF;
604 ELSE
605 l_return_status := fnd_api.g_ret_sts_error;
606 fnd_message.set_name('AR', 'HZ_ADDRESS_LINE_MANDATORY');
607 fnd_message.set_token('LOC_ID', TO_CHAR(p_location_id));
608 fnd_msg_pub.add;
609 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
610 hz_utility_v2pub.debug(p_message=>'Address is not null : Error ',
611 p_prefix=>'ERROR',
612 p_msg_level=>fnd_log.level_error);
613 END IF;
614 END IF;
615
616 -- City is mandatory
617 IF l_city IS NOT NULL THEN
618 us_form_str := us_form_str || 'city="' || l_city || '" ';
619 END IF;
620
621 -- State is mandatory
622 IF l_state IS NOT NULL THEN
623 us_form_str := us_form_str || 'state="' || l_state || '" ';
624 END IF;
625
626 -- Postal code is not manadatory
627 IF l_postal_code IS NOT NULL THEN
628 us_form_str := us_form_str || 'lastline="' || l_postal_code || '" ';
629 END IF;
630
631 --Country NULL or US
632 IF is_country_valid(l_country ,'Y') <> 'Y' THEN
633 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
634 hz_utility_v2pub.debug(p_message=>'Country must be null or US : Error',
635 p_prefix=>'ERROR',
636 p_msg_level=>fnd_log.level_error);
637 END IF;
638 l_return_status := fnd_api.g_ret_sts_error;
639 fnd_message.set_name('AR', 'HZ_SPATIAL_INVALID_COUNTRY');
640 fnd_message.set_token('LOC_ID', TO_CHAR(p_location_id));
641 fnd_message.set_token('COUNTRY', l_country);
642 fnd_msg_pub.add;
643 END IF;
644
645 us_form_str := us_form_str || '/>';
646 IF l_return_status <> fnd_api.g_ret_sts_success THEN
647 RAISE notcorrectaddress;
648 END IF;
649
650 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
651 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.compose_elocation_detail_old :'|| TO_CHAR(p_location_id) ||' (-)',
652 p_prefix=>l_debug_prefix,
653 p_msg_level=>fnd_log.level_procedure);
654 END IF;
655
656 fnd_msg_pub.count_and_get(
657 p_encoded => fnd_api.g_false,
658 p_count => x_msg_count,
659 p_data => x_msg_data );
660
661 RETURN us_form_str;
662 EXCEPTION
663 WHEN notcorrectaddress THEN
664 us_form_str := NULL;
665 status_handler(l_return_status, x_return_status);
666 fnd_msg_pub.count_and_get(
667 p_encoded => fnd_api.g_false,
668 p_count => x_msg_count,
669 p_data => x_msg_data
670 );
671 RETURN us_form_str;
672 END compose_elocation_detail_old;
673
674 --
675 -- PRIVATE PROCEDURE compose_elocation_detail
676 --
677 -- DESCRIPTION
678 -- Return an XML interpretation of an address in HZ_LOCATIONS. The XML
679 -- returned will be of the following form:
680 -- <unformatted country="[ISO country code]">
681 -- <address_line value="Mr. Ji Yang" />
682 -- <address_line value="Oracle Corp" />
683 -- <address_line value="1 Oracle drive" />
684 -- <address_line value="3rd floor" />
685 -- <address_line value="Nashua" />
686 -- <address_line value="NH" />
687 -- </unformatted>
688 --
689 -- where each address_line value represents an American address column in
690 -- HZ_LOCATIONS.
691 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
692 --
693 -- MODIFICATION HISTORY
694 --
695 -- 01-23-2002 Joe del Callar Created.
696 --
697 --
698 FUNCTION compose_elocation_detail (
699 p_location_id NUMBER,
700 p_country VARCHAR2,
701 p_address1 VARCHAR2,
702 p_address2 VARCHAR2,
703 p_address3 VARCHAR2,
704 p_address4 VARCHAR2,
705 p_city VARCHAR2,
706 p_postal_code VARCHAR2,
707 p_county VARCHAR2,
708 p_state VARCHAR2,
709 p_province VARCHAR2,
710 p_name VARCHAR2 DEFAULT NULL,
711 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
712 x_return_status IN OUT NOCOPY VARCHAR2,
713 x_msg_count IN OUT NOCOPY NUMBER,
714 x_msg_data IN OUT NOCOPY VARCHAR2
715 ) RETURN VARCHAR2 IS
716 l_country VARCHAR2(200);
717 l_address1 VARCHAR2(240);
718 l_address2 VARCHAR2(240);
719 l_address3 VARCHAR2(240);
720 l_address4 VARCHAR2(240);
721 l_city VARCHAR2(200);
722 l_postal_code VARCHAR2(200);
723 l_state VARCHAR2(200);
724 l_county VARCHAR2(200);
725 l_province VARCHAR2(200);
726 l_name VARCHAR2(200);
727 l_xml_address VARCHAR2(1750);
728 l_line_break VARCHAR2(200) := '"/> <address_line value="';
729 l_formatted_address VARCHAR2(1500);
730 l_return_status VARCHAR2(10);
731 l_line_cnt NUMBER;
732 l_formatted_address_tbl hz_format_pub.string_tbl_type;
733 l_debug_prefix VARCHAR2(30) := '';
734 notcorrectaddress EXCEPTION;
735 BEGIN
736 l_return_status := fnd_api.g_ret_sts_success;
737 -- Fix perf bug 3669930, 4220460, remove xmlize_line function call for
738 -- some parameter. For others, check if the value is not null, then
739 -- do xmlize_line
740 l_country := p_country;
741 l_city := p_city;
742 IF(p_postal_code IS NOT NULL) THEN
743 l_postal_code := xmlize_line(p_postal_code);
744 END IF;
745 l_state := p_state;
746 l_name := p_name;
747 l_address1 := xmlize_line(p_address1);
748 IF(p_address2 IS NOT NULL) THEN
749 l_address2 := xmlize_line(p_address2);
750 END IF;
751 IF(p_address3 IS NOT NULL) THEN
752 l_address3 := xmlize_line(p_address3);
753 END IF;
754 IF(p_address4 IS NOT NULL) THEN
755 l_address4 := xmlize_line(p_address4);
756 END IF;
757 l_province := p_province;
758 l_county := p_county;
759 l_xml_address := '';
760
761 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
762 hz_utility_v2pub.debug(p_message=>g_pkg_name ||
763 '.compose_elocation_detail for location_id:' ||
764 TO_CHAR(p_location_id) ||' (+)',
765 p_prefix=>l_debug_prefix,
766 p_msg_level=>fnd_log.level_procedure);
767 END IF;
768
769 -- Check for valid countries
770 IF is_country_valid(l_country ,'Y') <> 'Y' THEN
771 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
772 hz_utility_v2pub.debug(p_message=>'Country invalid',
773 p_prefix=>'ERROR',
774 p_msg_level=>fnd_log.level_error);
775 END IF;
776 l_return_status := fnd_api.g_ret_sts_error;
777 fnd_message.set_name('AR', 'HZ_SPATIAL_INVALID_COUNTRY');
778 fnd_message.set_token('LOC_ID', TO_CHAR(p_location_id));
779 fnd_message.set_token('COUNTRY', l_country);
780 fnd_msg_pub.add;
781 -- raise the error immediately and forget about processing the rest.
782 RAISE notcorrectaddress;
783 END IF;
784
785 -- if state and city are not null, null out NOCOPY the postal code so that
786 -- it does not get composed into the address, as eLocations has some issues
787 -- with inconsistent city/state/postal code combinations.
788 -- ER10211310 - Below logic removed as per eLocation team advice
789 /*IF l_city IS NOT NULL AND l_state IS NOT NULL
790 THEN
791 l_postal_code := NULL;
792 END IF;
793 */
794 -- if state and province are null and the postal code is not, then null
795 -- out NOCOPY the city so that eLocations does its search based only on the
796 -- postal code only.
797 -- ER10211310 - Below logic removed as per eLocation team advice
798 /*IF l_state IS NULL
799 AND l_province IS NULL
800 AND l_postal_code IS NOT NULL
801 THEN
802 l_city := NULL;
803 END IF;
804 */
805
806 -- ER10211310
807 -- Removed format address and formating string as per gen_form.
808 /* -- Set the head of the request.
809 l_xml_address := '<unformatted country="' || l_country
810 || '"> <address_line value="';
811
812 -- call the address format API
813 hz_format_pub.format_eloc_address (
814 p_style_code => 'POSTAL_ADDR',
815 p_style_format_code => NULL,
816 p_line_break => l_line_break,
817 p_space_replace => ' ',
818 p_from_territory_code => l_country,
819 p_address_line_1 => l_address1,
820 p_address_line_2 => l_address2,
821 p_address_line_3 => l_address3,
822 p_address_line_4 => l_address4,
823 p_city => l_city,
824 p_postal_code => l_postal_code,
825 p_state => l_state,
826 p_province => l_province,
827 p_county => l_county,
828 p_country => l_country,
829 x_return_status => l_return_status,
830 x_msg_count => x_msg_count,
831 x_msg_data => x_msg_data,
832 x_formatted_address => l_formatted_address,
833 x_formatted_lines_cnt => l_line_cnt,
834 x_formatted_address_tbl => l_formatted_address_tbl
835 );
836
837 l_xml_address := l_xml_address || l_formatted_address
838 || '"/> </unformatted>';
839 */
840
841 -- ER10211310
842 -- XML string as per Gen Form.
843 -- Bug 14230889
844 l_xml_address := '<gen_form street="'||l_address1||', '||l_address2||', '||l_address3||', '||l_address4||'"'
845 ||' city="'||l_city||'" region=" '||NVL(l_state,l_province)||'" country="'||l_country
846 ||'" postal_code="'||l_postal_code||'" />';
847
848 IF l_return_status <> fnd_api.g_ret_sts_success THEN
849 RAISE notcorrectaddress;
850 END IF;
851
852 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
853 hz_utility_v2pub.debug(p_message=>g_pkg_name ||
854 '.compose_elocation_detail :' ||
855 TO_CHAR(p_location_id) ||' (-)',
856 p_prefix=>l_debug_prefix,
857 p_msg_level=>fnd_log.level_procedure);
858 END IF;
859
860 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
861 p_count => x_msg_count,
862 p_data => x_msg_data);
863
864 RETURN l_xml_address;
865 EXCEPTION
866 WHEN notcorrectaddress THEN
867 l_xml_address := NULL;
868 status_handler(l_return_status, x_return_status);
869 fnd_msg_pub.count_and_get(
870 p_encoded => fnd_api.g_false,
871 p_count => x_msg_count,
872 p_data => x_msg_data
873 );
874 RETURN l_xml_address;
875 END compose_elocation_detail;
876
877 ---------------------------------------------------
878 -- RETURN the xml request for 1 to 12 location info
879 -- If error then x_return_status = E
880 -- l_loc_array is changed to type HZ_ELOCATION_PKG.geo_location_rec_type
881 ---------------------------------------------------
882 FUNCTION location_xml (
883 p_loc_array IN OUT NOCOPY loc_array,
884 p_name IN VARCHAR2 DEFAULT NULL,
885 p_provider IN VARCHAR2 DEFAULT 'ELOCATION',
886 x_return_status IN OUT NOCOPY VARCHAR2,
887 x_msg_count IN OUT NOCOPY NUMBER,
888 x_msg_data IN OUT NOCOPY VARCHAR2
889 ) RETURN VARCHAR2 IS
890 l_return_status VARCHAR2(10);
891 xml_request VARCHAR2(32640);
892 l_count NUMBER;
893 l_form_us VARCHAR2(2500);
894 at_least_one VARCHAR2(1);
895 -- l_loc_rec hz_location_v2pub.location_rec_type;
896 l_loc_rec HZ_ELOCATION_PKG.geo_location_rec_type;
897 l_debug_prefix VARCHAR2(30):= '';
898 msg VARCHAR2(2000);
899 BEGIN
900 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
901 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.location_xml (+)',
902 p_prefix=>l_debug_prefix,
903 p_msg_level=>fnd_log.level_procedure);
904 END IF;
905 l_count := p_loc_array.COUNT;
906 IF l_count <> 0 THEN
907 At_least_one:= 'N';
908 xml_request := '';
909 xml_request := 'xml_request=<?xml version="1.0" standalone="yes" ?>' ||
910 '<geocode_request vendor="elocation">' ||
911 '<address_list>'||FND_GLOBAL.local_chr(10);
912 FOR i IN 1..l_count LOOP
913 l_return_status := fnd_api.g_ret_sts_success;
914 l_loc_rec := p_loc_array(i);
915 l_form_us :=
916 compose_elocation_detail (
917 p_location_id => l_loc_rec.location_id,
918 p_country => l_loc_rec.country,
919 p_address1 => l_loc_rec.address1,
920 p_address2 => l_loc_rec.address2,
921 p_address3 => l_loc_rec.address3,
922 p_address4 => l_loc_rec.address4,
923 p_city => l_loc_rec.city,
924 p_postal_code => l_loc_rec.postal_code,
925 p_county => l_loc_rec.county,
926 p_state => l_loc_rec.state,
927 p_province => l_loc_rec.province,
928 p_name => p_name,
929 p_init_msg_list => fnd_api.g_true,
930 x_return_status => l_return_status,
931 x_msg_count => x_msg_count,
932 x_msg_data => x_msg_data
933 );
934
935 -- J. del Callar: set the error status of any offending records.
936 IF l_return_status <> fnd_api.g_ret_sts_success THEN
937 status_handler(l_return_status, x_return_status);
938 p_loc_array(i).geometry_status_code := g_error;
939 END IF;
940
941 IF l_form_us IS NOT NULL THEN
942 at_least_one:='Y';
943 xml_request := xml_request ||
944 '<input_location id="' ||
945 TO_CHAR(p_loc_array(i).location_id)||'" ' ||
946 'multimatch_number= "3" >' ||
947 -- '<input_address match_mode="relax_street_type">' ||
948 '<input_address match_mode="DEFAULT">' ||
949 l_form_us ||
950 '</input_address>' ||
951 '</input_location>' || FND_GLOBAL.local_chr(10);
952 END IF;
953 END LOOP;
954 END IF;
955
956 IF at_least_one = 'Y' THEN
957 xml_request := xml_request ||
958 '</address_list>' ||
959 '</geocode_request>';
960 ELSE
961 xml_request := '';
962 END IF;
963 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
964 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.location_xml (-)',
965 p_prefix=>l_debug_prefix,
966 p_msg_level=>fnd_log.level_procedure);
967 END IF;
968 fnd_msg_pub.count_and_get(
969 p_encoded => fnd_api.g_false,
970 p_count => x_msg_count,
971 p_data => x_msg_data );
972
973 RETURN xml_request;
974 END location_xml;
975
976 FUNCTION elt_value
977 --------------------------------------------
978 -- RETURN the value of the requested element
979 -- NULL if element NOT FOUND
980 --------------------------------------------
981 ( p_str IN VARCHAR2,
982 p_elt IN VARCHAR2)
983 RETURN VARCHAR2
984 IS
985 retVal VARCHAR2(4000);
986 l_str VARCHAR2(8000);
987 l_elt VARCHAR2(4000);
988 l_pos_deb NUMBER;
989 first_quote NUMBER;
990 second_quote NUMBER;
991 l_equal VARCHAR2(1);
992 l_len NUMBER;
993 i NUMBER;
994 BEGIN
995 i := 1;
996 l_str := upper(p_str);
997 -- Fix perf bug 3669930, 4220460, remove upper of p_elt since the pass in
998 -- value is always in upper case
999 l_elt := p_elt;
1000 l_len := lengthb(p_elt);
1001 LOOP
1002 l_pos_deb := INSTRB(l_str, l_elt, 1, i);
1003 IF l_pos_deb = 0 THEN
1004 RETURN NULL;
1005 ELSE
1006 l_equal := SUBSTRB(l_str, l_pos_deb + l_len, 1);
1007 IF l_equal <> '=' THEN
1008 i := i + 1;
1009 ELSE
1010 EXIT;
1011 END IF;
1012 END IF;
1013 END LOOP;
1014 first_quote := INSTRB(l_str, '"', l_pos_deb, 1);
1015 second_quote := INSTRB(l_str, '"', l_pos_deb, 2);
1016 retval := SUBSTRB(l_str,
1017 first_quote + 1,
1018 (second_quote - 1)-(first_quote + 1) + 1);
1019 RETURN retval;
1020 END elt_value;
1021
1022 FUNCTION gen_geo
1023 (p_SDO_GTYPE NUMBER,
1024 p_SDO_SRID NUMBER,
1025 p_xlo NUMBER,
1026 p_yla NUMBER,
1027 p_zdp NUMBER DEFAULT NULL,
1028 p_info_array mdsys.SDO_ELEM_INFO_ARRAY DEFAULT NULL,
1029 p_ordi_array MDSYS.SDO_ORDINATE_ARRAY DEFAULT NULL,
1030 p_provider VARCHAR2 DEFAULT 'ELOCATION')
1031 RETURN MDSYS.SDO_GEOMETRY
1032 IS
1033 l_geo MDSYS.SDO_GEOMETRY;
1034 l_sdo_pt MDSYS.SDO_POINT_TYPE;
1035 BEGIN
1036 -- p_sdo_gtype = 2001; --
1037 -- p_sdo_srid = 8307; --
1038 l_sdo_pt := MDSYS.SDO_POINT_TYPE(p_xlo,
1039 p_yla,
1040 p_zdp);
1041 l_geo := MDSYS.SDO_GEOMETRY(p_sdo_gtype,
1042 p_sdo_srid,
1043 l_sdo_pt,
1044 p_info_array,
1045 p_ordi_array);
1046 RETURN l_geo;
1047 END gen_geo;
1048
1049 PROCEDURE non_num_handle(
1050 Elt_name IN VARCHAR2,
1051 Elt_value IN VARCHAR2,
1052 p_location_id IN NUMBER,
1053 x_return_status IN OUT NOCOPY VARCHAR2,
1054 x_msg_count IN OUT NOCOPY NUMBER,
1055 x_msg_data IN OUT NOCOPY VARCHAR2)
1056 IS
1057 BEGIN
1058 x_return_status := fnd_api.g_ret_sts_error;
1059 fnd_message.set_name('AR', 'HZ_NUMBER_MANDATORY');
1060 fnd_message.set_token('VALUE',Elt_value);
1061 fnd_message.set_token('ELT',Elt_name);
1062 fnd_message.set_token('LOC_ID',TO_CHAR(p_location_id));
1063 fnd_msg_pub.add;
1064 END non_num_handle;
1065
1066 --
1067 -- PROCEDURE parse_one_response
1068 --
1069 -- DESCRIPTION
1070 -- Accept One location Xml
1071 -- Do Validation (GEOCODE ID mandatory, LATITUDE mandatory, LONGITUDE
1072 -- mandatory, STREET warn, MATCH_COUNT warn)
1073 -- Do update TCA Registry
1074 -- If error then x_return_status = E
1075 --
1076 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1077 --
1078 -- ARGUMENTS
1079 -- IN:
1080 -- p_str
1081 -- IN/OUT:
1082 -- x_loc_id
1083 -- x_geo
1084 -- x_geo_status
1085 -- x_return_status Return status after the call. The status can
1086 -- be FND_API.G_RET_STS_SUCCESS (success),
1087 -- FND_API.G_RET_STS_ERROR (error),
1088 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1089 -- x_msg_count Number of messages in message stack.
1090 -- x_msg_data Message text if x_msg_count is 1.
1091 --
1092 -- NOTES
1093 --
1094 -- MODIFICATION HISTORY
1095 --
1096 -- 01-09-2002 Herve Yu Created.
1097 -- 01-28-2002 Joe del Callar Modified to return the geometry in order
1098 -- to facilitate integration with HR.
1099 -- 29-NOV-2010 Sudhir Gokavarapu ER10211310 Added x_geometry_accuracy parameter to
1100 -- process and return accuracy value.
1101 --
1102
1103 PROCEDURE parse_one_response (
1104 p_str IN VARCHAR2,
1105 x_loc_id IN OUT NOCOPY NUMBER,
1106 x_geo IN OUT NOCOPY mdsys.sdo_geometry,
1107 x_geo_status IN OUT NOCOPY VARCHAR2,
1108 x_geometry_accuracy IN OUT NOCOPY NUMBER,
1109 x_return_status IN OUT NOCOPY VARCHAR2,
1110 x_msg_count IN OUT NOCOPY NUMBER,
1111 x_msg_data IN OUT NOCOPY VARCHAR2
1112 ) IS
1113 l_recep VARCHAR2(4000);
1114 l_long_found BOOLEAN := FALSE;
1115 l_lat_found BOOLEAN := FALSE;
1116 l_latitude NUMBER;
1117 l_longitude NUMBER;
1118 l_match_count NUMBER;
1119 l_match_count_temp VARCHAR2(1000);
1120 l_street VARCHAR2(1200);
1121 l_debug_prefix VARCHAR2(30) := '';
1122 l_return_status VARCHAR2(10);
1123 BEGIN
1124 -- Procedure pour tester la possibilite de transformer l_recep en nombre qui soit controle
1125 -- Afin de capturer les erreurs evebntuellement de Alfa num.
1126 l_return_status := fnd_api.g_ret_sts_success;
1127
1128 -- J. del Callar: set the geo status to success
1129 x_geo_status := g_good;
1130
1131 -- Debug info.
1132 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1133 hz_utility_v2pub.debug(p_message=>'hz_geocode_pkg.parse_one_response (+)',
1134 p_prefix=>l_debug_prefix,
1135 p_msg_level=>fnd_log.level_procedure);
1136 END IF;
1137
1138 l_recep := elt_value(p_str, 'GEOCODE ID');
1139 IF l_recep IS NULL THEN
1140 l_return_status := fnd_api.g_ret_sts_error;
1141 fnd_message.set_name('AR', 'HZ_MISSING_GEOCODE_ID');
1142 fnd_msg_pub.add;
1143 status_handler(l_return_status, x_return_status);
1144 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1145 hz_utility_v2pub.debug(p_message=>'No Geocode Id Found : Error',
1146 p_prefix=>'ERROR',
1147 p_msg_level=>fnd_log.level_error);
1148 END IF;
1149 END IF;
1150
1151 -- J. del Callar: convert location ID into a number, but handle non-numeric
1152 -- value errors.
1153 BEGIN
1154 x_loc_id := TO_NUMBER(l_recep);
1155 EXCEPTION
1156 WHEN OTHERS THEN
1157 non_num_handle('GEOCODE ID', l_recep, x_loc_id,
1158 l_return_status, x_msg_count, x_msg_data);
1159 status_handler(l_return_status, x_return_status);
1160 RETURN;
1161 END;
1162
1163
1164 -- determine the match count.
1165 l_match_count_temp := elt_value(p_str, 'MATCH_COUNT');
1166
1167 l_recep := elt_value(p_str, 'LATITUDE');
1168 IF l_recep IS NULL THEN
1169 /* l_return_status := fnd_api.g_ret_sts_error;
1170
1171 -- J. del Callar: report this error to the user only if a row was
1172 -- returned. if a row was not returned, the error gets reported in a
1173 -- more meaningful way later on.
1174 IF l_match_count_temp <> '0' THEN
1175 fnd_message.set_name('AR', 'HZ_MISSING_LATITUDE');
1176 fnd_message.set_token('LOC_ID', TO_CHAR(x_loc_id));
1177 fnd_msg_pub.add;
1178 -- Debug info.
1179 END IF;
1180 status_handler(l_return_status, x_return_status);
1181 */ IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1182 hz_utility_v2pub.debug(p_message=>'Latitude not found',
1183 p_prefix=>'ERROR',
1184 p_msg_level=>fnd_log.level_error);
1185 END IF;
1186
1187 l_lat_found := FALSE;
1188 ELSE
1189 -- J. del Callar: convert latitude into a number, but handle non-numeric
1190 -- value errors.
1191 BEGIN
1192 l_latitude := TO_NUMBER(l_recep);
1193 EXCEPTION
1194 WHEN OTHERS THEN
1195 non_num_handle('LATITUDE', l_recep, x_loc_id,
1196 l_return_status, x_msg_count, x_msg_data);
1197 status_handler(l_return_status, x_return_status);
1198 END;
1199
1200 l_lat_found := TRUE;
1201 END IF;
1202
1203 l_recep := elt_value( p_str, 'LONGITUDE');
1204 IF l_recep IS NULL THEN
1205 /*l_return_status := fnd_api.g_ret_sts_error;
1206
1207 -- J. del Callar: report this error to the user only if a row was
1208 -- returned. if a row was not returned, the error gets reported in a
1209 -- more meaningful way later on.
1210 IF l_match_count_temp <> '0' THEN
1211 fnd_message.set_name('AR', 'HZ_MISSING_LONGITUDE');
1212 fnd_message.set_token('LOC_ID', TO_CHAR(x_loc_id));
1213 fnd_msg_pub.add;
1214
1215 END IF;
1216 status_handler(l_return_status, x_return_status);
1217 */
1218 -- Debug info.
1219 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1220 hz_utility_v2pub.debug(p_message=>'Longitude not found',
1221 p_prefix=>'ERROR',
1222 p_msg_level=>fnd_log.level_error);
1223 END IF;
1224 l_long_found := FALSE;
1225 ELSE
1226 -- J. del Callar: convert latitude into a number, but handle non-numeric
1227 -- value errors.
1228 BEGIN
1229 l_longitude := TO_NUMBER(l_recep);
1230 EXCEPTION
1231 WHEN OTHERS THEN
1232 non_num_handle('LONGITUDE', l_recep, x_loc_id,
1233 l_return_status, x_msg_count, x_msg_data);
1234 status_handler(l_return_status, x_return_status);
1235 END;
1236
1237 l_long_found := TRUE;
1238 END IF;
1239
1240 --ER10211310
1241 --Processing Geometry Accuracy
1242 l_recep := elt_value(p_str, 'MATCH_CODE');
1243 IF l_recep IS NULL THEN
1244 l_recep := '0';
1245
1246 END IF;
1247 -- Convert Accuracy into a number, but handle non-numeric
1248 -- value errors.
1249 BEGIN
1250 x_geometry_accuracy := TO_NUMBER(l_recep);
1251
1252 EXCEPTION
1253 WHEN OTHERS THEN
1254 non_num_handle('Geometry Accuracy', l_recep, x_loc_id,
1255 l_return_status, x_msg_count, x_msg_data);
1256 status_handler(l_return_status, x_return_status);
1257 END;
1258 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1259 hz_utility_v2pub.debug(p_message=>'Geocode Id : '||x_loc_id||' Latitude: '||l_latitude||' Longitude: '||l_longitude||' Geometry Accuracy: '||x_geometry_accuracy,
1260 p_prefix =>l_debug_prefix,
1261 p_msg_level=>fnd_log.level_statement);
1262 END IF;
1263
1264 l_street := elt_value(p_str, 'STREET');
1265
1266 -- J. del Callar: inform user that the city center was returned by the
1267 -- spatial data provider. The message should only occur if the street is
1268 -- not found, but the latitude and longitude were found.
1269 IF l_street IS NULL AND l_lat_found AND l_long_found THEN
1270 /*--l_return_status := g_warning;
1271 fnd_message.set_name('AR','HZ_MISSING_STREET');
1272 fnd_message.set_token('LOC_ID', TO_CHAR(x_loc_id));
1273 fnd_msg_pub.add;
1274 status_handler(l_return_status, x_return_status);
1275
1276 -- J. del Callar: set the status reported in
1277 -- HZ_LOCATIONS.GEOMETRY_STATUS_CODE to NOEXACTMATCH to reflect the fact
1278 -- that the city or zip center was returned.
1279 */
1280 x_geo_status := g_noexactmatch;
1281
1282 -- Debug info.
1283 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1284 hz_utility_v2pub.debug(p_message=>'The street was not found for Location Id : '||x_loc_id,
1285 p_prefix=>'WARNING',
1286 p_msg_level=>fnd_log.level_exception);
1287
1288 END IF;
1289 ELSE
1290 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1291 hz_utility_v2pub.debug(p_message=>'Street: '||l_street,
1292 p_prefix =>l_debug_prefix,
1293 p_msg_level=>fnd_log.level_statement);
1294 END IF;
1295 END IF;
1296
1297 -- J. del Callar: convert match count into a number and handle non-numeric
1298 -- errors.
1299 BEGIN
1300 l_match_count := TO_NUMBER(l_match_count_temp);
1301
1302 IF l_match_count > 1 THEN
1303 /*--l_return_status := g_warning;
1304 fnd_message.set_name('AR','HZ_NB_MATCHES');
1305 fnd_message.set_token('NB', l_match_count_temp);
1306 fnd_message.set_token('LOC_ID', TO_CHAR(x_loc_id));
1307 fnd_msg_pub.add;
1308
1309 -- J. del Callar: set the status reported in
1310 -- HZ_LOCATIONS.GEOMETRY_STATUS_CODE to MULTIMATCH to reflect that
1311 -- multiple matches were returned.
1312 */
1313 x_geo_status := g_multimatch;
1314
1315 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1316 hz_utility_v2pub.debug(p_message=>'Multiple matching addresses: '|| l_match_count_temp||' for Location Id :'||x_loc_id,
1317 p_prefix=>'WARNING',
1318 p_msg_level=>fnd_log.level_exception);
1319 END IF;
1320 ELSIF l_match_count = 0 THEN
1321 /* l_return_status := fnd_api.g_ret_sts_error;
1322 fnd_message.set_name('AR','HZ_NB_MATCHES');
1323 fnd_message.set_token('NB', l_match_count_temp);
1324 fnd_message.set_token('LOC_ID', TO_CHAR(x_loc_id));
1325 fnd_msg_pub.add;
1326
1327 -- J. del Callar: no rows were returned by location service
1328 -- mark this as an error.
1329 */
1330 x_geo_status := g_error;
1331
1332
1333 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1334 hz_utility_v2pub.debug(p_message=>'No matches (match_count=' ||
1335 l_match_count_temp ||
1336 ') for '||x_loc_id||', update not possible.',
1337 p_prefix=>'ERROR',
1338 p_msg_level=>fnd_log.level_error);
1339 END IF;
1340
1341 END IF;
1342 status_handler(l_return_status, x_return_status);
1343 EXCEPTION
1344 WHEN OTHERS THEN
1345 non_num_handle('MATCH_COUNT', l_match_count_temp, x_loc_id,
1346 l_return_status, x_msg_count, x_msg_data);
1347 status_handler(l_return_status, x_return_status);
1348 END;
1349
1350 IF l_return_status <> fnd_api.g_ret_sts_error THEN
1351 x_geo := gen_geo(p_sdo_gtype => 2001,
1352 p_sdo_srid => 8307,
1353 p_xlo => l_longitude,
1354 p_yla => l_latitude);
1355
1356 status_handler(l_return_status, x_return_status);
1357 ELSE
1358 x_geo := NULL;
1359 status_handler(l_return_status, x_return_status);
1360 END IF;
1361
1362 fnd_msg_pub.count_and_get(
1363 p_encoded => fnd_api.g_false,
1364 p_count => x_msg_count,
1365 p_data => x_msg_data
1366 );
1367
1368 -- Debug info.
1369 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1370 hz_utility_v2pub.debug(p_message=>'parse_one_reponse (-)',
1371 p_prefix=>l_debug_prefix,
1372 p_msg_level=>fnd_log.level_procedure);
1373 END IF;
1374 END parse_one_response;
1375
1376 --
1377 -- PRIVATE PROCEDURE
1378 -- parse_responses
1379 --
1380 -- DESCRIPTION
1381 -- Accept One location Xml
1382 -- Do Validation (GEOCODE ID mandatory, LATITUDE mandatory, LONGITUDE
1383 -- mandatory, STREET warn, MATCH_COUNT warn)
1384 -- Do update TCA Registry
1385 -- If error then x_return_status = E
1386 --
1387 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1388 --
1389 -- ARGUMENTS
1390 -- IN:
1391 -- p_str
1392 -- IN/OUT:
1393 -- x_loc_id
1394 -- x_geo
1395 -- x_geo_status
1396 -- x_return_status Return status after the call. The status can
1397 -- be FND_API.G_RET_STS_SUCCESS (success),
1398 -- FND_API.G_RET_STS_ERROR (error),
1399 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1400 -- x_msg_count Number of messages in message stack.
1401 -- x_msg_data Message text if x_msg_count is 1.
1402 --
1403 -- NOTES
1404 --
1405 -- MODIFICATION HISTORY
1406 --
1407 -- 01-09-2002 Herve Yu Created.
1408 -- 01-28-2002 Joe del Callar Modified to return the geometry in order
1409 -- to facilitate integration with HR.
1410 -- 29-NOV-2010 Sudhir Gokavarapu Added parameter in parse_one_response,
1411 -- set_matching_geometry calls.
1412 --
1413 PROCEDURE parse_responses (
1414 p_tab_address IN array_t,
1415 p_loc_array IN OUT NOCOPY loc_array,
1416 x_return_status IN OUT NOCOPY VARCHAR2,
1417 x_msg_count IN OUT NOCOPY NUMBER,
1418 x_msg_data IN OUT NOCOPY VARCHAR2
1419 ) IS
1420 l_message VARCHAR2(4000);
1421 i NUMBER;
1422 j NUMBER;
1423 l_return_status VARCHAR2(20);
1424 l_msg_count NUMBER;
1425 l_msg_data VARCHAR2(2000);
1426 l_geometry mdsys.sdo_geometry;
1427 l_geometry_status_code VARCHAR2(30);
1428 l_location_id NUMBER;
1429 l_debug_prefix VARCHAR2(30) := '';
1430 l_geometry_accuracy NUMBER;
1431 BEGIN
1432 -- Debug info.
1433 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1434 hz_utility_v2pub.debug(p_message=>'parse_responses (+)',
1435 p_prefix=>l_debug_prefix,
1436 p_msg_level=>fnd_log.level_procedure);
1437 END IF;
1438 l_return_status := fnd_api.g_ret_sts_success;
1439 i := p_tab_address.COUNT;
1440 IF i > 0 THEN
1441 FOR j IN 1..i LOOP
1442 parse_one_response(
1443 p_str => p_tab_address(j),
1444 x_loc_id => l_location_id,
1445 x_geo => l_geometry,
1446 x_geo_status => l_geometry_status_code,
1447 x_geometry_accuracy => l_geometry_accuracy,
1448 x_return_status => l_return_status,
1449 x_msg_count => l_msg_count,
1450 x_msg_data => l_msg_data
1451 );
1452 status_handler(l_return_status, x_return_status);
1453 set_matching_geometry(p_loc_array,
1454 l_location_id,
1455 l_geometry,
1456 l_geometry_status_code,
1457 l_geometry_accuracy,
1458 l_return_status,
1459 x_msg_count,
1460 x_msg_data);
1461 status_handler(l_return_status, x_return_status);
1462 END LOOP;
1463 -- all records with an unset geometry status are considered to be in
1464 -- error - set their status
1465 FOR j IN 1..p_loc_array.COUNT LOOP
1466 IF p_loc_array(j).geometry_status_code IS NULL THEN
1467 p_loc_array(j).geometry_status_code := g_error;
1468 END IF;
1469 END LOOP;
1470 END IF;
1471 fnd_msg_pub.count_and_get(
1472 p_encoded => fnd_api.g_false,
1473 p_count => x_msg_count,
1474 p_data => x_msg_data
1475 );
1476 -- Debug info.
1477 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1478 hz_utility_v2pub.debug(p_message=>'parse_n_reponse (-)',
1479 p_prefix=>l_debug_prefix,
1480 p_msg_level=>fnd_log.level_procedure);
1481 END IF;
1482 END parse_responses;
1483
1484 --
1485 -- PUBLIC FUNCTION
1486 -- in_bypass_list
1487 --
1488 -- DESCRIPTION
1489 -- Returns TRUE if the argument p_url_target is in p_exclusion_list, FALSE
1490 -- otherwise. Used to determine whether or not to use a proxy. This
1491 -- functionality can only be used with fixed-length character set
1492 -- exclusion lists and targets, which is okay since these are URLs.
1493 --
1494 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1495 --
1496 -- ARGUMENTS
1497 -- IN:
1498 -- p_target_url
1499 -- p_exclusion_list
1500 --
1501 -- NOTES
1502 --
1503 -- MODIFICATION HISTORY
1504 --
1505 -- 03-27-2002 J. del Callar Created.
1506 --
1507 FUNCTION in_bypass_list (
1508 p_url_target IN VARCHAR2,
1509 p_exclusion_list IN VARCHAR2
1510 ) RETURN BOOLEAN IS
1511 l_exclusion_list VARCHAR2(2000) := LOWER(p_exclusion_list);
1512 l_excluded_domain VARCHAR2(240);
1513 l_delimiter VARCHAR2(1);
1514 l_pos NUMBER;
1515 l_url_domain VARCHAR2(2000);
1516 l_debug_prefix VARCHAR2(30) := '';
1517 BEGIN
1518 --enable_debug;
1519 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1520 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.in_bypass_list (+)',
1521 p_prefix=>l_debug_prefix,
1522 p_msg_level=>fnd_log.level_procedure);
1523 END IF;
1524 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1525 hz_utility_v2pub.debug(p_message=>'Checking for URL: ' || p_url_target,
1526 p_prefix =>l_debug_prefix,
1527 p_msg_level=>fnd_log.level_statement);
1528 hz_utility_v2pub.debug(p_message=>'In list:',
1529 p_prefix =>l_debug_prefix,
1530 p_msg_level=>fnd_log.level_statement);
1531 hz_utility_v2pub.debug(p_message=>l_exclusion_list,
1532 p_prefix =>l_debug_prefix,
1533 p_msg_level=>fnd_log.level_statement);
1534 END IF;
1535
1536 -- First determine what the delimiter in the exclusion list is. We support
1537 -- "|" (Java-style), "," (Netscape-style) and ";" (Microsoft-style)
1538 -- delimiters. Java-style is given priority.
1539 IF INSTRB(l_exclusion_list, '|') > 0 THEN
1540 l_delimiter := '|';
1541 ELSIF INSTRB(l_exclusion_list, ',') > 0 THEN
1542 l_delimiter := ',';
1543 ELSIF INSTRB(l_exclusion_list, ';') > 0 THEN
1544 l_delimiter := ';';
1545 ELSE
1546 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1547 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.in_bypass_list (fmt)',
1548 p_prefix =>l_debug_prefix,
1549 p_msg_level=>fnd_log.level_statement);
1550 END IF;
1551
1552 --disable_debug;
1553
1554 RETURN FALSE;
1555 END IF;
1556
1557 -- get the domain portion of the URL.
1558 -- first, put the domain in the same case as the exclusion list.
1559 l_url_domain := LOWER(p_url_target);
1560
1561 -- second, remove the protocol specifier.
1562 l_pos := INSTRB(l_url_domain, '://');
1563 IF l_pos > 0 THEN
1564 l_url_domain := SUBSTRB(l_url_domain, l_pos+3);
1565 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1566 hz_utility_v2pub.debug(p_message=>':// found at position ' || l_pos,
1567 p_prefix =>l_debug_prefix,
1568 p_msg_level=>fnd_log.level_statement);
1569 hz_utility_v2pub.debug(p_message=>'Stripped domain: ' || l_url_domain,
1570 p_prefix =>l_debug_prefix,
1571 p_msg_level=>fnd_log.level_statement);
1572 END IF;
1573 END IF;
1574
1575 l_pos := INSTRB(l_url_domain, ':/');
1576 IF l_pos > 0 THEN
1577 l_url_domain := SUBSTRB(l_url_domain, l_pos+2);
1578 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1579 hz_utility_v2pub.debug(p_message=>':/ found at position ' || l_pos,
1580 p_prefix =>l_debug_prefix,
1581 p_msg_level=>fnd_log.level_statement);
1582 hz_utility_v2pub.debug(p_message=>'Stripped domain: ' || l_url_domain,
1583 p_prefix =>l_debug_prefix,
1584 p_msg_level=>fnd_log.level_statement);
1585 END IF;
1586 END IF;
1587
1588 -- third, remove the trailing URL information.
1589 l_pos := INSTRB(l_url_domain, '/');
1590 IF l_pos > 0 THEN
1591 l_url_domain := SUBSTRB(l_url_domain, 1, l_pos-1);
1592 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1593 hz_utility_v2pub.debug(p_message=>'/ found at position ' || l_pos,
1594 p_prefix =>l_debug_prefix,
1595 p_msg_level=>fnd_log.level_statement);
1596 hz_utility_v2pub.debug(p_message=>'Stripped domain: ' || l_url_domain,
1597 p_prefix =>l_debug_prefix,
1598 p_msg_level=>fnd_log.level_statement);
1599 END IF;
1600 END IF;
1601
1602 -- lastly, remove spaces in the exclusion list
1603 l_exclusion_list := REPLACE(l_exclusion_list, ' ');
1604
1605 WHILE l_exclusion_list IS NOT NULL LOOP
1606 -- get the position of the 1st delimiter in the remaining exclusion list
1607 l_pos := INSTRB(l_exclusion_list, l_delimiter);
1608
1609 IF l_pos = 0 THEN
1610 -- no delimiters implies that this is the last domain to be checked.
1611 l_excluded_domain := l_exclusion_list;
1612 ELSE
1613 -- need to do a SUBSTRB if there is a delimiter in the exclusion list
1614 -- to get the first domain left in the exclusion list.
1615 l_excluded_domain := SUBSTRB(l_exclusion_list, 1, l_pos-1);
1616 END IF;
1617
1618 -- The domain should not have a % sign in it because it should be a
1619 -- domain name. It may have a * sign in it depending on the syntax of
1620 -- the exclusion list. * signs should be treated as % signs in SQL.
1621 l_excluded_domain := REPLACE(l_excluded_domain, '*', '%');
1622
1623 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1624 hz_utility_v2pub.debug(p_message=>'Matching URL: ' || l_url_domain,
1625 p_prefix =>l_debug_prefix,
1626 p_msg_level=>fnd_log.level_statement);
1627 hz_utility_v2pub.debug(p_message=>'Against dom: ' || l_excluded_domain,
1628 p_prefix =>l_debug_prefix,
1629 p_msg_level=>fnd_log.level_statement);
1630 END IF;
1631
1632 -- check to see if the URL domain matches an excluded domain.
1633 IF l_url_domain LIKE '%' || l_excluded_domain THEN
1634 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1635 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.in_bypass_list (match)',
1636 p_prefix =>l_debug_prefix,
1637 p_msg_level=>fnd_log.level_statement);
1638 END IF;
1639
1640 --disable_debug;
1641
1642 -- a match was found, return a positive result.
1643 RETURN TRUE;
1644 END IF;
1645
1646 IF l_pos = 0 THEN
1647 -- no more domains to be checked if no delimiters were found.
1648 l_exclusion_list := NULL;
1649 ELSE
1650 -- get the remaining domain exclusions to be checked.
1651 l_exclusion_list := SUBSTRB(l_exclusion_list, l_pos+1);
1652 END IF;
1653 END LOOP;
1654 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1655 hz_utility_v2pub.debug(p_message=>g_pkg_name||'.in_bypass_list (eol)',
1656 p_prefix =>l_debug_prefix,
1657 p_msg_level=>fnd_log.level_statement);
1658 END IF;
1659
1660 --disable_debug;
1661
1662 -- no domain match was found, return false
1663 RETURN FALSE;
1664 END in_bypass_list;
1665
1666 --
1667 -- PUBLIC PROCEDURE
1668 -- get_spatial_coords
1669 --
1670 -- DESCRIPTION
1671 -- Build the xml request for n locations
1672 -- Post the Xml request
1673 -- Split the Response into individual responses
1674 -- Parse and update hz_locations with the responses
1675 -- If error Then x_return_status = E
1676 --
1677 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1678 --
1679 -- ARGUMENTS
1680 -- IN:
1681 -- p_name
1682 -- p_http_ad
1683 -- p_proxy
1684 -- p_port
1685 -- p_retry
1686 -- p_init_msg_list
1687 -- IN/OUT:
1688 -- p_loc_array
1689 -- OUT:
1690 -- x_return_status Return status after the call. The status can
1691 -- be FND_API.G_RET_STS_SUCCESS (success),
1692 -- FND_API.G_RET_STS_ERROR (error),
1693 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1694 -- x_msg_count Number of messages in message stack.
1695 -- x_msg_data Message text if x_msg_count is 1.
1696 --
1697 -- NOTES
1698 --
1699 -- MODIFICATION HISTORY
1700 --
1701 -- 01-09-2002 Herve Yu Created.
1702 -- 01-28-2002 Joe del Callar Modified to return the geometry in the
1703 -- loc_array structure in order to facilitate
1704 -- integration with HR.
1705 --
1706
1707 PROCEDURE get_spatial_coords (
1708 p_loc_array IN OUT NOCOPY loc_array,
1709 p_name IN VARCHAR2 DEFAULT NULL,
1710 p_http_ad IN VARCHAR2,
1711 p_proxy IN VARCHAR2 DEFAULT NULL,
1712 p_port IN NUMBER DEFAULT NULL,
1713 p_retry IN NUMBER DEFAULT 3,
1714 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
1715 x_return_status OUT NOCOPY VARCHAR2,
1716 x_msg_count OUT NOCOPY NUMBER,
1717 x_msg_data OUT NOCOPY VARCHAR2
1718 ) IS
1719
1720 -- local variables
1721
1722 lxml VARCHAR2(32767);
1723 lresp VARCHAR2(32767);
1724 lrespct VARCHAR2(200);
1725 ltab array_t;
1726 msg VARCHAR2(4000);
1727 cpt NUMBER;
1728 l_return_status VARCHAR2(10);
1729 l_err_resp VARCHAR2(32767);
1730 exchttp EXCEPTION;
1731 i NUMBER;
1732 l_debug_prefix VARCHAR2(30) := 'getSpatialCO:';
1733 l_error_loc_ids VARCHAR2(1000);
1734 BEGIN
1735 --enable_debug;
1736 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1737 hz_utility_v2pub.DEBUG
1738 (p_message=>'get_spatial_coords (+)',
1739 p_prefix=>l_debug_prefix,
1740 p_msg_level=>fnd_log.level_procedure,
1741 p_module_prefix => 'HZ_GEOCODE_PKG',
1742 p_module =>'HZ');
1743 END IF;
1744
1745 l_return_status := fnd_api.g_ret_sts_success;
1746
1747 IF fnd_api.to_boolean(p_init_msg_list) THEN
1748 fnd_msg_pub.initialize;
1749 END IF;
1750
1751 lxml := location_xml(p_loc_array => p_loc_array,
1752 p_name => p_name,
1753 p_provider => 'ELOCATION',
1754 x_return_status => l_return_status,
1755 x_msg_count => x_msg_count,
1756 x_msg_data => x_msg_data);
1757
1758 status_handler(l_return_status, x_return_status);
1759
1760 IF lxml IS NOT NULL THEN
1761 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1762 hz_utility_v2pub.DEBUG
1763 (p_message=>'xml returned by location_xml is not null',
1764 p_prefix=>l_debug_prefix,
1765 p_msg_level=>fnd_log.level_procedure,
1766 p_module_prefix => 'HZ_GEOCODE_PKG',
1767 p_module =>'HZ');
1768 hz_utility_v2pub.DEBUG
1769 (p_message=>'bfr hz_http_pkg.post LOOP',
1770 p_prefix=>l_debug_prefix,
1771 p_msg_level=>fnd_log.level_procedure,
1772 p_module_prefix => 'HZ_GEOCODE_PKG',
1773 p_module =>'HZ');
1774 END IF;
1775 cpt := 0;
1776 LOOP
1777 cpt := cpt + 1;
1778 hz_http_pkg.post(
1779 doc => lxml,
1780 content_type =>'application/x-www-form-urlencoded',
1781 url => p_http_ad,
1782 resp => lresp,
1783 resp_content_type => lrespct,
1784 proxyserver => p_proxy,
1785 proxyport => p_port,
1786 err_resp => l_err_resp,
1787 x_return_status => l_return_status,
1788 x_msg_count => x_msg_count,
1789 x_msg_data => x_msg_data);
1790 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1791 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1792 hz_utility_v2pub.DEBUG
1793 (p_message=>'hz_http_pkg.post failed - UNEXPECTED ERROR',
1794 p_prefix=>l_debug_prefix,
1795 p_msg_level=>fnd_log.level_error,
1796 p_module_prefix => 'HZ_GEOCODE_PKG',
1797 p_module =>'HZ');
1798 hz_utility_v2pub.DEBUG
1799 (p_message=>'hz_http_pkg.post (-)',
1800 p_prefix=>l_debug_prefix,
1801 p_msg_level=>fnd_log.level_procedure,
1802 p_module_prefix => 'HZ_GEOCODE_PKG',
1803 p_module =>'HZ');
1804 END IF;
1805 RAISE exchttp;
1806 END IF;
1807 IF l_return_status = fnd_api.G_RET_STS_ERROR THEN
1808 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1809 hz_utility_v2pub.DEBUG
1810 (p_message=>'hz_http_pkg.post failed - EXPECTED ERROR',
1811 p_prefix=>l_debug_prefix,
1812 p_msg_level=>fnd_log.level_error,
1813 p_module_prefix => 'HZ_GEOCODE_PKG',
1814 p_module =>'HZ');
1815 hz_utility_v2pub.DEBUG
1816 (p_message=>'hz_http_pkg.post (-)',
1817 p_prefix=>l_debug_prefix,
1818 p_msg_level=>fnd_log.level_procedure,
1819 p_module_prefix => 'HZ_GEOCODE_PKG',
1820 p_module =>'HZ');
1821 END IF;
1822 RAISE exchttp;
1823 END IF;
1824 IF l_return_status = fnd_api.G_RET_STS_SUCCESS THEN
1825 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1826 hz_utility_v2pub.DEBUG
1827 (p_message=>'hz_http_pkg.post SUCCESS',
1828 p_prefix=>l_debug_prefix,
1829 p_msg_level=>fnd_log.level_procedure,
1830 p_module_prefix => 'HZ_GEOCODE_PKG',
1831 p_module =>'HZ');
1832 END IF;
1833 END IF;
1834 EXIT WHEN lresp IS NOT NULL;
1835 EXIT WHEN cpt > p_retry;
1836 END LOOP;
1837 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1838 hz_utility_v2pub.DEBUG
1839 (p_message=>'Outside hz_http_pkg.post LOOP',
1840 p_prefix=>l_debug_prefix,
1841 p_msg_level=>fnd_log.level_procedure,
1842 p_module_prefix => 'HZ_GEOCODE_PKG',
1843 p_module =>'HZ');
1844 END IF;
1845
1846 IF ltrim(rtrim(lresp)) IS NULL THEN
1847 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1848 hz_utility_v2pub.DEBUG
1849 (p_message=>'respose from hz_http_pkg.post() is NULL',
1850 p_prefix=>l_debug_prefix,
1851 p_msg_level=>fnd_log.level_error,
1852 p_module_prefix => 'HZ_GEOCODE_PKG',
1853 p_module =>'HZ');
1854 hz_utility_v2pub.DEBUG
1855 (p_message=>'printing the error respose (if any)from hz_http_pkg.post',
1856 p_prefix=>l_debug_prefix,
1857 p_msg_level=>fnd_log.level_error,
1858 p_module_prefix => 'HZ_GEOCODE_PKG',
1859 p_module =>'HZ');
1860 hz_utility_v2pub.DEBUG
1861 (p_message=> NVL(l_err_resp,'<NULL>'),
1862 p_prefix=>l_debug_prefix,
1863 p_msg_level=>fnd_log.level_error,
1864 p_module_prefix => 'HZ_GEOCODE_PKG',
1865 p_module =>'HZ');
1866 END IF;
1867 -- The post did not succeed, even after several retries. This is an
1868 -- unrecoverable error.
1869 l_return_status := fnd_api.g_ret_sts_unexp_error;
1870 fnd_message.set_name('AR','HZ_HTTP_POST_FAILED');
1871 fnd_message.set_token('RETRY', p_retry);
1872 -- limit the size of token to 200 characters, otherwise if lresp is too
1873 -- long, will get buffer too small error
1874 fnd_message.set_token('LASTMSG', NVL(substrb(l_err_resp,1,200), '<NULL>'));
1875 fnd_msg_pub.add;
1876
1877 -- Set the error status for all records in this batch
1878 FOR i IN 1..p_loc_array.COUNT LOOP
1879 p_loc_array(i).geometry_status_code := g_error;
1880 END LOOP;
1881 status_handler(l_return_status, x_return_status);
1882 ELSIF UPPER(lresp) NOT LIKE '%<GEOCODE_RESPONSE>%' THEN
1883 l_error_loc_ids := '';
1884 fnd_file.put_line(fnd_file.output,'Response from eLocation is NOT in GEOCODE format for below locations');
1885 FOR i IN 1..p_loc_array.COUNT LOOP
1886 --fnd_file.put_line(fnd_file.output,'Error in Loc Id :'||p_loc_array(i).location_id);
1887 l_error_loc_ids := l_error_loc_ids||p_loc_array(i).location_id||', ';
1888 END LOOP;
1889 fnd_file.put_line(fnd_file.output,l_error_loc_ids);
1890 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1891 hz_utility_v2pub.DEBUG
1892 (p_message=>'Response from eLocation is NOT in GEOCODE format for below locations '||l_error_loc_ids,
1893 p_prefix=>l_debug_prefix,
1894 p_msg_level=>fnd_log.level_error,
1895 p_module_prefix => 'HZ_GEOCODE_PKG',
1896 p_module =>'HZ');
1897
1898 hz_utility_v2pub.DEBUG
1899 (p_message=>'respose from hz_http_pkg.post()is NOT a GEOCODE RESP ',
1900 p_prefix=>l_debug_prefix,
1901 p_msg_level=>fnd_log.level_error,
1902 p_module_prefix => 'HZ_GEOCODE_PKG',
1903 p_module =>'HZ');
1904 hz_utility_v2pub.DEBUG
1905 (p_message=>'printing the error respose (if any)from hz_http_pkg.post',
1906 p_prefix=>l_debug_prefix,
1907 p_msg_level=>fnd_log.level_error,
1908 p_module_prefix => 'HZ_GEOCODE_PKG',
1909 p_module =>'HZ');
1910 hz_utility_v2pub.DEBUG
1911 (p_message=> NVL(l_err_resp,'<NULL>'),
1912 p_prefix=>l_debug_prefix,
1913 p_msg_level=>fnd_log.level_error,
1914 p_module_prefix => 'HZ_GEOCODE_PKG',
1915 p_module =>'HZ');
1916 END IF;
1917 -- Spatial response was not parseable. This is an unrecoverable error.
1918 -- l_return_status := fnd_api.g_ret_sts_unexp_error;
1919 -- fnd_message.set_name('AR','HZ_MALFORMED_SPATIAL_RESPONSE');
1920 -- if the response is some special char that must be handled.
1921 BEGIN
1922 IF (LENGTH(lresp) >2) THEN
1923 -- limit the size of token to 200 characters, otherwise if lresp is too
1924 -- long, will get buffer too small error
1925 fnd_message.set_token('RESP', substrb(lresp,1,200));
1926 ELSE
1927 fnd_message.set_token('RESP', 'Malformed response');
1928 END IF;
1929 EXCEPTION
1930 WHEN OTHERS THEN
1931 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1932 hz_utility_v2pub.DEBUG
1933 (p_message=>'exception when handling resp from hz_http_pkg.post',
1934 p_prefix=>l_debug_prefix,
1935 p_msg_level=>fnd_log.level_error,
1936 p_module_prefix => 'HZ_GEOCODE_PKG',
1937 p_module =>'HZ');
1938 hz_utility_v2pub.DEBUG
1939 (p_message=>SQLERRM,
1940 p_prefix=>l_debug_prefix,
1941 p_msg_level=>fnd_log.level_error,
1942 p_module_prefix => 'HZ_GEOCODE_PKG',
1943 p_module =>'HZ');
1944 END IF;
1945 fnd_message.set_token('RESP', 'Malformed response');
1946 END; -- end of anonymous block
1947 fnd_msg_pub.add;
1948 /*
1949 If the lresp - response from the post() is not null and not a
1950 proper GEOCODE_RESPONSE and it is some other special chars, then
1951 it is safe to do add HZ_MALFORMED_SPATIAL_RESPONSE mesg to the stack
1952 but with l_err_resp as the token.
1953
1954 This would enhance the user experience by getting some error text in the
1955 concurrent log file. Otherwise, users are only seeing
1956 "unexpected error occured" mesg from update_geometry() method.
1957 */
1958
1959 IF LTRIM(RTRIM(l_err_resp)) IS NOT NULL THEN
1960 fnd_message.set_name('AR','HZ_MALFORMED_SPATIAL_RESPONSE');
1961 fnd_message.set_token('RESP', nvl(substrb(l_err_resp,1,400), '<NULL>'));
1962 fnd_msg_pub.add;
1963 END IF;
1964
1965 -- Set the error status for all records in this batch
1966 FOR i IN 1..p_loc_array.COUNT LOOP
1967 p_loc_array(i).geometry_status_code := g_error;
1968 END LOOP;
1969 status_handler(l_return_status, x_return_status);
1970
1971 ELSE
1972 /* This section of the code processes the response from hz_http_pkg.post().
1973 As the above two cases failed, that means the lresp is having GEOCODE*/
1974
1975 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1976 hz_utility_v2pub.DEBUG
1977 (p_message=>'before get_response_lines()',
1978 p_prefix=>l_debug_prefix,
1979 p_msg_level=>fnd_log.level_error,
1980 p_module_prefix => 'HZ_GEOCODE_PKG',
1981 p_module =>'HZ');
1982 END IF;
1983 get_response_lines(
1984 p_str => lresp,
1985 p_lines => ltab,
1986 p_temp_sta => 'geocode',
1987 p_temp_end => '</GEOCODE>',
1988 p_root_text => '<GEOCODE_RESPONSE>',
1989 p_provider => 'ELOCATION',
1990 p_loc_array => p_loc_array
1991 );
1992
1993 IF ltab.COUNT = 0 THEN
1994 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1995 hz_utility_v2pub.DEBUG
1996 (p_message=>'get_response_lines() returned is zero rows',
1997 p_prefix=>l_debug_prefix,
1998 p_msg_level=>fnd_log.level_error,
1999 p_module_prefix => 'HZ_GEOCODE_PKG',
2000 p_module =>'HZ');
2001 END IF;
2002 -- Set the error status for all records in this batch since no rows
2003 -- were returned by the data provider.
2004 FOR i IN 1..p_loc_array.COUNT LOOP
2005 p_loc_array(i).geometry_status_code := g_error;
2006 END LOOP;
2007 ELSE -- if there are rows retunred by get_response_lines()
2008 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2009 hz_utility_v2pub.DEBUG
2010 (p_message=>'before parse_responses()',
2011 p_prefix=>l_debug_prefix,
2012 p_msg_level=>fnd_log.level_procedure,
2013 p_module_prefix => 'HZ_GEOCODE_PKG',
2014 p_module =>'HZ');
2015 END IF;
2016 parse_responses(
2017 p_tab_address => ltab,
2018 p_loc_array => p_loc_array,
2019 x_return_status => l_return_status,
2020 x_msg_count => x_msg_count,
2021 x_msg_data => x_msg_data
2022 );
2023 status_handler(l_return_status, x_return_status);
2024 END IF; -- end of no of rows from get_response_lines() check
2025 END IF;-- end of lresp check
2026 END IF;-- end of lxml check
2027 fnd_msg_pub.count_and_get(
2028 p_encoded => fnd_api.g_false,
2029 p_count => x_msg_count,
2030 p_data => x_msg_data
2031 );
2032 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2033 hz_utility_v2pub.DEBUG
2034 (p_message=>'get_spatial_coords(-)',
2035 p_prefix=>l_debug_prefix,
2036 p_msg_level=>fnd_log.level_procedure,
2037 p_module_prefix => 'HZ_GEOCODE_PKG',
2038 p_module =>'HZ');
2039 END IF;
2040 EXCEPTION
2041 WHEN exchttp THEN
2042 status_handler(l_return_status, x_return_status);
2043 END get_spatial_coords;
2044 ------------------------------------------------------------
2045 ------------------------------------------------------------
2046 END hz_geocode_pkg;
[Home] [Help]