[Home] [Help]
PACKAGE BODY: APPS.IRC_LOCATION_UTILITY
Source
1 package body IRC_LOCATION_UTILITY as
2 /* $Header: irlocutl.pkb 120.5.12010000.2 2008/08/05 10:49:59 ubhat ship $ */
3 --Package Variables
4 --
5 g_package varchar2(33) := 'irc_location_utility.';
6 g_conversion number;
7 --
8 --
9 -- Function to remove invalid characters (these cause error when the request
10 -- is processed by Geocode)
11 -- -------------------------------------------------------------------------
12 -- |-------------------------< removeInvalidChars >------------------------|
13 -- -------------------------------------------------------------------------
14 function removeInvalidChars(p_address_line varchar2) return varchar2
15 is
16 l_address_line varchar2 (240);
17 begin
18 l_address_line := replace(p_address_line,'&',' ');
19 return l_address_line;
20 end;
21 --
22 -- -------------------------------------------------------------------------
23 -- |-------------------------< address2geocodexml >------------------------|
24 -- -------------------------------------------------------------------------
25 --
26 function address2geocodexml
27 (name VARCHAR2
28 ,street VARCHAR2
29 ,city VARCHAR2
30 ,state VARCHAR2
31 ,zip_code VARCHAR2)
32 return VARCHAR2
33 AS
34 geocoder_host VARCHAR2(255);
35 us_form2 VARCHAR2(32767);
36 xml_request VARCHAR2(32767);
37 url VARCHAR2(32767);
38 return_string VARCHAR2(32767);
39 return_string_array utl_http.html_pieces;
40 max_pieces binary_integer:=16;
41 l_proc varchar2(72) := g_package||'address2geocodexml';
42 BEGIN
43 hr_utility.set_location(' Entering: ' || l_proc, 10);
44 -- construct us_form2
45 us_form2 := '<us_form2 ';
46 IF name IS not NULL THEN
47 us_form2 := us_form2 || 'name="' || removeInvalidChars(name) || '" ';
48 END IF;
49 IF street IS not null THEN
50 us_form2 := us_form2 || 'street="' || removeInvalidChars(street) || '" ';
51 END IF;
52 IF city IS not null THEN
53 us_form2 := us_form2 || 'city="' || removeInvalidChars(city) || '" ';
54 END IF;
55 IF state IS not null THEN
56 us_form2 := us_form2 || 'state="' || removeInvalidChars(state) || '" ';
57 END IF;
58 IF zip_code IS not null THEN
59 us_form2 := us_form2 || 'zip_code="' || removeInvalidChars(zip_code) || '" ';
60 END IF;
61 us_form2 := us_form2 || '/>';
62 hr_utility.set_location(l_proc, 20);
63 -- construct XML request
64 xml_request := '<?xml version="1.0" standalone="yes" ?>' ||
65 '<geocode_request vendor="elocation">' ||
66 ' <address_list>' ||
67 ' <input_location id="1">' ||
68 ' <input_address match_mode=' ||
69 ' "relax_street_type">' ||
70 us_form2 ||
71 ' </input_address>' ||
72 ' </input_location>' ||
73 ' </address_list>' ||
74 '</geocode_request>';
75
76 -- replace characters in xml_request with escapes
77 xml_request := replace(xml_request, '"', '%22');
78 xml_request := replace(xml_request, '#', '%23');
79 xml_request := replace(xml_request, '''','%27');
80 xml_request := replace(xml_request, ' ', '%20');
81 xml_request := replace(xml_request, '<', '%3C');
82 xml_request := replace(xml_request, '>', '%3E');
83 xml_request := replace(xml_request, ';', '%3B');
84 xml_request := replace(xml_request, '/', '%2F');
85 xml_request := replace(xml_request, '?', '%3F');
86 xml_request := replace(xml_request, ':', '%3A');
87 xml_request := replace(xml_request, '@', '%40');
88 xml_request := replace(xml_request, '&', '%26');
89 xml_request := replace(xml_request, '=', '%3D');
90 xml_request := replace(xml_request, '+', '%2B');
91 xml_request := replace(xml_request, '$', '%24');
92 xml_request := replace(xml_request, ',', '%2C');
93 hr_utility.set_location(l_proc, 30);
94
95 -- construct URL
96 geocoder_host:=fnd_profile.value('IRC_GEOCODE_HOST');
97
98
99 url := geocoder_host ||
100 '?xml_request=' ||
101 xml_request;
102
103 return_string_array:= irc_xml_util.http_get_pieces(
104 url => url,
105 max_pieces => max_pieces);
106 return_string:=null;
107 for j in return_string_array.first..return_string_array.last loop
108 return_string:=return_string||return_string_array(j);
109 end loop;
110 return return_string;
111
112 hr_utility.set_location('Leaving :'||l_proc, 40);
113 end address2geocodexml;
114 -- -------------------------------------------------------------------------
115 -- |-------------------------< address2geocodexml >------------------------|
116 -- -------------------------------------------------------------------------
117 --
118 function address2geocodexml
119 (name VARCHAR2
120 ,street VARCHAR2
121 ,intersecting_street VARCHAR2
122 ,builtup_area VARCHAR2
123 ,order8_area VARCHAR2
124 ,order2_area VARCHAR2
125 ,order1_area VARCHAR2
126 ,country VARCHAR2
127 ,postal_code VARCHAR2
128 ,postal_addon_code VARCHAR2)
129 return VARCHAR2
130 AS
131 geocoder_host VARCHAR2(255);
132 gdf_form VARCHAR2(32767);
133 xml_request VARCHAR2(32767);
134 url VARCHAR2(32767);
135 return_string VARCHAR2(32767);
136 return_string_array utl_http.html_pieces;
137 max_pieces binary_integer:=16;
138 l_proc varchar2(72) := g_package||'address2geocodexml';
139 BEGIN
140 hr_utility.set_location(' Entering: ' || l_proc, 10);
141 -- construct gdf_form
142 gdf_form := '<gdf_form ';
143 IF name IS not NULL THEN
144 gdf_form := gdf_form || 'name="' || removeInvalidChars(name) || '" ';
145 END IF;
146 IF street IS not null THEN
147 gdf_form := gdf_form || 'street="' || removeInvalidChars(street) || '" ';
148 END IF;
149 IF intersecting_street IS not null THEN
150 gdf_form := gdf_form || 'intersecting_street="' || removeInvalidChars(intersecting_street) || '" ';
151 END IF;
152 IF builtup_area IS not null THEN
153 gdf_form := gdf_form || 'builtup_area="' || removeInvalidChars(builtup_area) || '" ';
154 END IF;
155 IF order8_area IS not null THEN
156 gdf_form := gdf_form || 'order8_area="' || removeInvalidChars(order8_area) || '" ';
157 END IF;
158 IF order2_area IS not null THEN
159 gdf_form := gdf_form || 'order2_area="' || removeInvalidChars(order2_area) || '" ';
160 END IF;
161 IF order1_area IS not null THEN
162 gdf_form := gdf_form || 'order1_area="' || removeInvalidChars(order1_area) || '" ';
163 END IF;
164 IF country IS not null THEN
165 gdf_form := gdf_form || 'country="' || removeInvalidChars(country) || '" ';
166 END IF;
167 IF postal_code IS not null THEN
168 gdf_form := gdf_form || 'postal_code="' || removeInvalidChars(postal_code) || '" ';
169 END IF;
170 IF postal_addon_code IS not null THEN
171 gdf_form := gdf_form || 'postal_addon_code="' || removeInvalidChars(postal_addon_code) || '" ';
172 END IF;
173 gdf_form := gdf_form || '/>';
174 hr_utility.set_location(l_proc, 20);
175 -- construct XML request
176 xml_request := '<?xml version="1.0" standalone="yes" ?>' ||
177 '<geocode_request vendor="elocation">' ||
178 ' <address_list>' ||
179 ' <input_location id="1">' ||
180 ' <input_address match_mode=' ||
181 ' "relax_street_type">' ||
182 gdf_form ||
183 ' </input_address>' ||
184 ' </input_location>' ||
185 ' </address_list>' ||
186 '</geocode_request>';
187 -- replace characters in xml_request with escapes
188 xml_request := replace(xml_request, '"', '%22');
189 xml_request := replace(xml_request, '#', '%23');
190 xml_request := replace(xml_request, '''','%27');
191 xml_request := replace(xml_request, ' ', '%20');
192 xml_request := replace(xml_request, '<', '%3C');
193 xml_request := replace(xml_request, '>', '%3E');
194 xml_request := replace(xml_request, ';', '%3B');
195 xml_request := replace(xml_request, '/', '%2F');
196 xml_request := replace(xml_request, '?', '%3F');
197 xml_request := replace(xml_request, ':', '%3A');
198 xml_request := replace(xml_request, '@', '%40');
199 xml_request := replace(xml_request, '&', '%26');
200 xml_request := replace(xml_request, '=', '%3D');
201 xml_request := replace(xml_request, '+', '%2B');
202 xml_request := replace(xml_request, '$', '%24');
203 xml_request := replace(xml_request, ',', '%2C');
204 hr_utility.set_location(l_proc, 30);
205
206 -- construct URL
207 geocoder_host:=fnd_profile.value('IRC_GEOCODE_HOST');
208
209 url := geocoder_host ||
210 '?xml_request=' ||
211 xml_request;
212
213 return_string_array:= irc_xml_util.http_get_pieces(
214 url => url,
215 max_pieces => max_pieces);
216 return_string:=null;
217 for j in return_string_array.first..return_string_array.last loop
218 return_string:=return_string||return_string_array(j);
219 end loop;
220 return return_string;
221
222 hr_utility.set_location('Leaving :'||l_proc, 40);
223 end address2geocodexml;
224 -- -------------------------------------------------------------------------
225 -- |-----------------------------< address2xml >---------------------------|
226 -- -------------------------------------------------------------------------
227 --
228 function address2xml
229 (address_id number default null
230 ,address_line1 varchar2 default null
231 ,address_line2 varchar2 default null
232 ,address_line3 varchar2 default null
233 ,address_line4 varchar2 default null
234 ,address_line5 varchar2 default null
235 ,address_line6 varchar2 default null
236 ,address_line7 varchar2 default null
237 ,address_line8 varchar2 default null
238 ,address_line9 varchar2 default null
239 ,country varchar2 default null)
240 return VARCHAR2
241 AS
242 unformatted_form VARCHAR2(32767);
243 xml_request VARCHAR2(32767);
244 l_proc varchar2(72) := g_package||'address2xml';
245 BEGIN
246 hr_utility.set_location(' Entering: ' || l_proc, 10);
247 -- construct unformatted_form
248 unformatted_form := '<unformatted ';
249 IF country IS not NULL THEN
250 unformatted_form := unformatted_form || 'country="' || removeInvalidChars(country) || '" ';
251 END IF;
252 unformatted_form := unformatted_form || '> ';
253 unformatted_form := unformatted_form || '<address_line value="'|| removeInvalidChars(address_line1) ||'" /> ';
254
255 IF address_line2 IS not null THEN
256 unformatted_form := unformatted_form || '<address_line value="'|| removeInvalidChars(address_line2) ||'" /> ';
257 END IF;
258 IF address_line3 IS not null THEN
259 unformatted_form := unformatted_form || '<address_line value="'|| removeInvalidChars(address_line3) ||'" /> ';
260 END IF;
261 IF address_line4 IS not null THEN
262 unformatted_form := unformatted_form || '<address_line value="'|| removeInvalidChars(address_line4) ||'" /> ';
263 END IF;
264 IF address_line5 IS not null THEN
265 unformatted_form := unformatted_form || '<address_line value="'|| removeInvalidChars(address_line5) ||'" /> ';
266 END IF;
267 IF address_line6 IS not null THEN
268 unformatted_form := unformatted_form || '<address_line value="'|| removeInvalidChars(address_line6) ||'" /> ';
269 END IF;
270 IF address_line7 IS not null THEN
271 unformatted_form := unformatted_form || '<address_line value="'|| removeInvalidChars(address_line7) ||'" /> ';
272 END IF;
273 IF address_line8 IS not null THEN
274 unformatted_form := unformatted_form || '<address_line value="'|| removeInvalidChars(address_line8) ||'" /> ';
275 END IF;
276 IF address_line9 IS not null THEN
277 unformatted_form := unformatted_form || '<address_line value="'|| removeInvalidChars(address_line9) ||'" /> ';
278 END IF;
279 unformatted_form:=unformatted_form||'</unformatted>';
280 unformatted_form := '<input_location id="'||nvl(address_id,1)||'"> '
281 ||'<input_address match_mode="relax_street_type">'
282 ||unformatted_form
283 ||'</input_address></input_location>';
284 hr_utility.set_location(l_proc, 20);
285 return unformatted_form;
286 end address2xml;
287 -- -------------------------------------------------------------------------
288 -- |-------------------------< address2geocodexml >------------------------|
289 -- -------------------------------------------------------------------------
290 --
291 function address2geocodexml
292 (address_line1 varchar2
293 ,address_line2 varchar2 default null
294 ,address_line3 varchar2 default null
295 ,address_line4 varchar2 default null
296 ,address_line5 varchar2 default null
297 ,address_line6 varchar2 default null
298 ,address_line7 varchar2 default null
299 ,address_line8 varchar2 default null
300 ,address_line9 varchar2 default null
301 ,country varchar2 default null)
302 return VARCHAR2
303 AS
304 geocoder_host VARCHAR2(255);
305 unformatted_form VARCHAR2(32767);
306 xml_request VARCHAR2(32767);
307 url VARCHAR2(32767);
308 return_string VARCHAR2(32767);
309 return_string_array utl_http.html_pieces;
310 max_pieces binary_integer:=16;
311 l_proc varchar2(72) := g_package||'address2geocodexml';
312 BEGIN
313 hr_utility.set_location(' Entering: ' || l_proc, 10);
314 -- construct unformatted_form
315 unformatted_form :=address2xml(address_line1 => address_line1
316 ,address_line2 => address_line2
317 ,address_line3 => address_line3
318 ,address_line4 => address_line4
319 ,address_line5 => address_line5
320 ,address_line6 => address_line6
321 ,address_line7 => address_line7
322 ,address_line8 => address_line8
323 ,address_line9 => address_line9
324 ,country => country);
325
326 hr_utility.set_location(l_proc, 20);
327 -- construct XML request
328 xml_request := '<?xml version="1.0" standalone="yes" ?>' ||
329 '<geocode_request vendor="elocation">' ||
330 ' <address_list>' ||
331 unformatted_form ||
332 ' </address_list>' ||
333 '</geocode_request>';
334 -- replace characters in xml_request with escapes
335 xml_request := replace(xml_request, '"', '%22');
336 xml_request := replace(xml_request, '#', '%23');
337 xml_request := replace(xml_request, '''','%27');
338 xml_request := replace(xml_request, ' ', '%20');
339 xml_request := replace(xml_request, '<', '%3C');
340 xml_request := replace(xml_request, '>', '%3E');
341 xml_request := replace(xml_request, ';', '%3B');
345 xml_request := replace(xml_request, '@', '%40');
342 xml_request := replace(xml_request, '/', '%2F');
343 xml_request := replace(xml_request, '?', '%3F');
344 xml_request := replace(xml_request, ':', '%3A');
346 xml_request := replace(xml_request, '&', '%26');
347 xml_request := replace(xml_request, '=', '%3D');
348 xml_request := replace(xml_request, '+', '%2B');
349 xml_request := replace(xml_request, '$', '%24');
350 xml_request := replace(xml_request, ',', '%2C');
351 hr_utility.set_location(l_proc, 30);
352 -- construct URL
353 geocoder_host:=fnd_profile.value('IRC_GEOCODE_HOST');
354
355 url := geocoder_host ||
356 '?xml_request=' ||
357 xml_request;
358
359 return_string_array:= irc_xml_util.http_get_pieces(
360 url => url,
361 max_pieces => max_pieces);
362 return_string:=null;
363 for j in return_string_array.first..return_string_array.last loop
364 return_string:=return_string||return_string_array(j);
365 end loop;
366 return return_string;
367
368 hr_utility.set_location('Leaving :'||l_proc, 40);
369 end address2geocodexml;
370 --
371 -- -------------------------------------------------------------------------
372 -- |---------------------------< process_return_xml >----------------------|
373 -- -------------------------------------------------------------------------
374 --
375 function process_return_xml
376 (xml_response varchar2)
377 return MDSYS.SDO_GEOMETRY
378 as
379 address_doc xmldom.DOMDocument;
380 match_nodes xmlDom.DOMNodeList;
381 match_node xmlDom.DOMNode;
382 parser xmlparser.parser;
383 l_longitude number;
384 l_latitude number;
385 l_proc varchar2(72) := g_package||'process_return_xml';
386 begin
387
388 hr_utility.set_location(' Entering: '||l_proc, 10);
389 parser:=xmlparser.newParser;
390 xmlparser.parseBuffer(parser,xml_response);
391 address_doc:=xmlparser.getDocument(parser);
392 xmlparser.freeParser(parser);
393 hr_utility.set_location(l_proc, 20);
394 match_nodes:=xslprocessor.selectNodes(xmldom.makeNode(address_doc),'/geocode_response/geocode/match');
395 if xmldom.getlength(match_nodes)>0 then
396 hr_utility.set_location(l_proc, 30);
397 match_node :=xmldom.item(match_nodes,0);
398 xslprocessor.valueOf(match_node,'@longitude',l_longitude);
399 xslprocessor.valueOf(match_node,'@latitude',l_latitude);
400 end if;
401 xmldom.freeDocument(address_doc);
402 hr_utility.set_location(l_proc, 40);
403 if (l_longitude is not null and l_latitude is not null) then
404 RETURN MDSYS.SDO_GEOMETRY(2001
405 ,8307
406 ,MDSYS.SDO_POINT_TYPE
407 (l_longitude
408 ,l_latitude
409 ,NULL)
410 ,NULL
411 ,NULL);
412 else
413 return null;
414 end if;
415 exception
416 when others then
417 begin
418 hr_utility.set_location(l_proc, 50);
419 xmldom.freeDocument(address_doc);
420 return null;
421 exception
422 when others then
423 return null;
424 end;
425
426 end process_return_xml;
427 --
428 -- -------------------------------------------------------------------------
429 -- |---------------------------< address2geometry >------------------------|
430 -- -------------------------------------------------------------------------
431 --
432 FUNCTION address2geometry
433 (name varchar2 default null
434 ,street varchar2 default null
435 ,city varchar2 default null
436 ,state varchar2 default null
437 ,zip_code varchar2 default null)
438 RETURN MDSYS.SDO_GEOMETRY
439 AS
440 xml_response VARCHAR2(32767);
441 l_proc varchar2(72) := g_package||'address2geometry';
442 BEGIN
443 hr_utility.set_location('Entering :'||l_proc, 10);
444 -- Get xml geocode response string
445 xml_response := irc_location_utility.address2geocodexml
446 (name => name
447 ,street => street
448 ,city => city
449 ,state => state
450 ,zip_code => zip_code);
451 return irc_location_utility.process_return_xml(xml_response);
452 end address2geometry;
453 --
454 -- -------------------------------------------------------------------------
455 -- |---------------------------< address2geometry >------------------------|
456 -- -------------------------------------------------------------------------
457 --
458 function address2geometry
459 (name varchar2 default null
460 ,street varchar2 default null
461 ,intersecting_street varchar2 default null
462 ,builtup_area varchar2 default null
463 ,order8_area varchar2 default null
464 ,order2_area varchar2 default null
465 ,order1_area varchar2 default null
466 ,country varchar2 default null
467 ,postal_code varchar2 default null
468 ,postal_addon_code varchar2 default null)
469 return MDSYS.SDO_GEOMETRY
470 AS
471 i number;
472 xml_response VARCHAR2(32767);
473 l_proc varchar2(72) := g_package||'address2geometry';
474 BEGIN
475 hr_utility.set_location('Entering :'||l_proc, 10);
476 -- Get xml geocode response string
477 xml_response := irc_location_utility.address2geocodexml
478 (name => name
479 ,street => street
483 ,order2_area => order2_area
480 ,intersecting_street => intersecting_street
481 ,builtup_area => builtup_area
482 ,order8_area => order8_area
484 ,order1_area => order1_area
485 ,country => country
486 ,postal_code => postal_code
487 ,postal_addon_code => postal_addon_code);
488 /*i:=0;
489 while (i<length(xml_response)) loop
490 hr_utility.set_location(substr(xml_response,i,70),20);
491 i:=i+70;
492 end loop;*/
493 return irc_location_utility.process_return_xml(xml_response);
494 end address2geometry;
495 --
496 -- -------------------------------------------------------------------------
497 -- |---------------------------< address2geometry >------------------------|
498 -- -------------------------------------------------------------------------
499 --
500 function address2geometry
501 (address_line1 varchar2
502 ,address_line2 varchar2 default null
503 ,address_line3 varchar2 default null
504 ,address_line4 varchar2 default null
505 ,address_line5 varchar2 default null
506 ,address_line6 varchar2 default null
507 ,address_line7 varchar2 default null
508 ,address_line8 varchar2 default null
509 ,address_line9 varchar2 default null
510 ,country varchar2 default null)
511 return MDSYS.SDO_GEOMETRY
512 AS
513 i number;
514 xml_response VARCHAR2(32767);
515 l_proc varchar2(72) := g_package||'address2geometry';
516 BEGIN
517 hr_utility.set_location('Entering :'||l_proc, 10);
518 -- Get xml geocode response string
519 xml_response := irc_location_utility.address2geocodexml
520 (address_line1 => address_line1
521 ,address_line2 => address_line2
522 ,address_line3 => address_line3
523 ,address_line4 => address_line4
524 ,address_line5 => address_line5
525 ,address_line6 => address_line6
526 ,address_line7 => address_line7
527 ,address_line8 => address_line8
528 ,address_line9 => address_line9
529 ,country => country);
530 /*i:=0;
531 while (i<length(xml_response)) loop
532 hr_utility.set_location(substr(xml_response,i,70),20);
533 i:=i+70;
534 end loop;*/
535 return irc_location_utility.process_return_xml(xml_response);
536 end address2geometry;
537 --
538 -- -------------------------------------------------------------------------
539 -- |-------------------------< bulk_address2geometry >---------------------|
540 -- -------------------------------------------------------------------------
541 --
542 procedure bulk_address2geometry(address_id in t_address_id
543 ,address_line1 in t_address_line
544 ,address_line2 in t_address_line
545 ,address_line3 in t_address_line
546 ,address_line4 in t_address_line
547 ,address_line5 in t_address_line
548 ,address_line6 in t_address_line
549 ,address_line7 in t_address_line
550 ,address_line8 in t_address_line
551 ,address_line9 in t_address_line
552 ,country in t_address_line
553 ,latitude out nocopy t_address_id
554 ,longitude out nocopy t_address_id
555 ,success out nocopy number
556 ,failure out nocopy number) as
557 --+
558 xml_response VARCHAR2(32767);
559 address_string VARCHAR2(32767):='';
560 address_list VARCHAR2(32767):='';
561 url VARCHAR2(32767);
562 return_string VARCHAR2(32767);
563 errorMsg varchar2(4000);
564 l_proc varchar2(72) := g_package||'bulk_address2geometry';
565 id_loc number;
566 i number;
567 k number;
568 latitude_loc NUMBER;
569 longitude_loc NUMBER;
570 l_success number := 0;
571 l_failure number := 0;
572 first_line number;
573 address_doc xmldom.DOMDocument;
574 geocode_nodes xmlDom.DOMNodeList;
575 geocode_node xmlDom.DOMNode;
576 match_node xmlDom.DOMNode;
577 parser xmlparser.parser;
578 return_string_array utl_http.html_pieces;
579 max_pieces binary_integer:=16;
580 --+
581 procedure process_addresses is
582 begin
583 hr_utility.set_location('Entering process_addresses :', 100);
584 xml_response:= '<?xml version="1.0" standalone="yes" ?>'
585 || '<geocode_request vendor="elocation">'
586 || '<address_list>'
587 || address_list
588 || '</address_list></geocode_request>';
589 -- replace characters in xml_request with escapes
590 xml_response := replace(xml_response, '"', '%22');
591 xml_response := replace(xml_response, '#', '%23');
592 xml_response := replace(xml_response, '''','%27');
593 xml_response := replace(xml_response, ' ', '%20');
594 xml_response := replace(xml_response, '<', '%3C');
595 xml_response := replace(xml_response, '>', '%3E');
596 xml_response := replace(xml_response, ';', '%3B');
597 xml_response := replace(xml_response, '/', '%2F');
598 xml_response := replace(xml_response, '?', '%3F');
599 xml_response := replace(xml_response, ':', '%3A');
603 xml_response := replace(xml_response, '+', '%2B');
600 xml_response := replace(xml_response, '@', '%40');
601 xml_response := replace(xml_response, '&', '%26');
602 xml_response := replace(xml_response, '=', '%3D');
604 xml_response := replace(xml_response, '$', '%24');
605 xml_response := replace(xml_response, ',', '%2C');
606 --+ construct URL
607 url := fnd_profile.value('IRC_GEOCODE_HOST')
608 || '?xml_request='
609 || xml_response;
610 hr_utility.set_location('Length of url '||lengthb(url), 110);
611 return_string_array :=
612 irc_xml_util.http_get_pieces(url => url
613 ,max_pieces => max_pieces);
614 hr_utility.set_location('recieved the response ', 120);
615 return_string := null;
616 for j in return_string_array.first .. return_string_array.last loop
617 return_string := return_string || return_string_array(j);
618 end loop;
619 xmlparser.parseBuffer(parser,return_string);
620 address_doc := xmlparser.getDocument(parser);
621 BEGIN
622 geocode_nodes := xslprocessor.selectNodes(xmldom.makeNode(address_doc),'/geocode_response/geocode');
623 if xmldom.getLength(geocode_nodes) > 0 then
624 for j in 1 .. xmldom.getLength(geocode_nodes) loop
625 geocode_node := xmldom.item(geocode_nodes,j-1);
626 hr_utility.set_location(xslprocessor.valueOf(geocode_node,'@match_count')||' for line '||j,130);
627 if to_number(xslprocessor.valueOf(geocode_node,'@match_count'))>0 then
628 match_node :=xslprocessor.selectSingleNode(geocode_node,'match');
629 longitude(first_line+j) :=xslprocessor.valueOf(match_node,'@longitude');
630 latitude(first_line+j) :=xslprocessor.valueOf(match_node,'@latitude');
631 if latitude(first_line+j) is not null and longitude(first_line+j) is not null then
632 l_success:=l_success+1;
633 hr_utility.set_location('Geometry found for address_id :'||address_id(first_line+j) ,140);
634 hr_utility.set_location('Longitude :'||longitude(first_line+j) ,141);
635 hr_utility.set_location('Latitude :'||latitude(first_line+j) ,142);
636 end if;
637 else
638 hr_utility.set_location('Geometry not found for address_id :'||address_id(first_line+j) ,150);
639 end if;
640 end loop;
641 else
642 geocode_node := xslprocessor.selectSingleNode(xmldom.makeNode(address_doc),'/component_error');
643 errorMsg := xslprocessor.valueOf(geocode_node,'/component_error');
644 hr_utility.set_location('ERROR thrown from geocode host',155);
645 hr_utility.set_location(substrb(substrb(errorMsg,1,instrb(errorMsg,fnd_global.local_chr(10))-1),1,80),160);
646 end if;
647 xmldom.freeDocument(address_doc);
648 EXCEPTION
649 when OTHERS then
650 xmldom.freeDocument(address_doc);
651 hr_utility.set_location('Unexpected error in elocation response processing',194);
652 hr_utility.set_location('Unexpected error :'||SQLERRM,195);
653 hr_utility.set_location('Unexpected error :'||SQLCODE,196);
654 END;
655 --+
656 hr_utility.set_location('Leaving process_addresses :',170);
657 exception
658 when others then
659 --this is a bulk process, so ignore errors and move on
660 hr_utility.set_location('Unexpected error in address processing',197);
661 hr_utility.set_location('Unexpected error :'||SQLERRM,198);
662 hr_utility.set_location('Unexpected error :'||SQLCODE,199);
663 end process_addresses;
664 BEGIN
665 hr_utility.set_location('Entering :'||l_proc, 10);
666 first_line := 0;
667 parser := xmlparser.newParser;
668 for i in address_id.first..address_id.last loop
669 longitude(i):= null;
670 latitude(i) := null;
671 address_string:=address2xml
672 (address_id =>address_id(i)
673 ,address_line1 =>address_line1(i)
674 ,address_line2 =>address_line2(i)
675 ,address_line3 =>address_line3(i)
676 ,address_line4 =>address_line4(i)
677 ,address_line5 =>address_line5(i)
678 ,address_line6 =>address_line6(i)
679 ,address_line7 =>address_line7(i)
680 ,address_line8 =>address_line8(i)
681 ,address_line9 =>address_line9(i)
682 ,country =>country(i));
683 hr_utility.set_location('count of address_id :'||address_id.count,20);
684 address_list := address_string;
685 first_line := i-1;
686 process_addresses();
687 end loop;
688 --+
689 success := l_success;
690 failure := address_id.count - l_success;
691 xmlparser.freeParser(parser);
692 --+
693 hr_utility.set_location('Leaving :'||l_proc, 45);
694 exception
695 when others then
696 -- this is a bulk process, so ignore errors and move on
697 xmlparser.freeParser(parser);
698 hr_utility.set_location('Unexpected error in bulk processing',50);
699 hr_utility.set_location('Unexpected error :'||SQLERRM,55);
700 hr_utility.set_location('Unexpected error :'||SQLCODE,56);
701 end bulk_address2geometry;
702 --
703 -- -------------------------------------------------------------------------
704 -- |---------------------------< address2full >------------------------|
705 -- -------------------------------------------------------------------------
706 --
707 procedure address2full
708 (address_line1 in varchar2
709 ,address_line2 in varchar2 default null
710 ,address_line3 in varchar2 default null
711 ,address_line4 in varchar2 default null
712 ,address_line5 in varchar2 default null
716 ,address_line9 in varchar2 default null
713 ,address_line6 in varchar2 default null
714 ,address_line7 in varchar2 default null
715 ,address_line8 in varchar2 default null
717 ,country in varchar2 default null
718 ,name out nocopy varchar2
719 ,house_number out nocopy varchar2
720 ,street out nocopy varchar2
721 ,builtup_area out nocopy varchar2
722 ,order1_area out nocopy varchar2
723 ,order2_area out nocopy varchar2
724 ,order8_area out nocopy varchar2
725 ,country_name out nocopy varchar2
726 ,postal_code out nocopy varchar2
727 ,geometry out nocopy MDSYS.SDO_GEOMETRY
728 )
729 AS
730 i number;
731 xml_response VARCHAR2(32767);
732 l_proc varchar2(72) := g_package||'address2full';
733 address_doc xmldom.DOMDocument;
734 geocode_node xmlDom.DOMNode;
735 match_node xmlDom.DOMNode;
736 address_node xmlDom.DOMNode;
737 parser xmlparser.parser;
738 l_longitude number;
739 l_latitude number;
740 l_match_count varchar2(255);
741 BEGIN
742 hr_utility.set_location('Entering :'||l_proc, 10);
743 -- Get xml geocode response string
744 xml_response := irc_location_utility.address2geocodexml
745 (address_line1 => address_line1
746 ,address_line2 => address_line2
747 ,address_line3 => address_line3
748 ,address_line4 => address_line4
749 ,address_line5 => address_line5
750 ,address_line6 => address_line6
751 ,address_line7 => address_line7
752 ,address_line8 => address_line8
753 ,address_line9 => address_line9
754 ,country => country);
755 /* i:=0;
756 while (i<length(xml_response)) loop
757 hr_utility.set_location(substr(xml_response,i,70),20);
758 i:=i+70;
759 end loop;*/
760 parser:=xmlparser.newParser;
761 xmlparser.parseBuffer(parser,xml_response);
762 address_doc:=xmlparser.getDocument(parser);
763 xmlparser.freeParser(parser);
764 geocode_node:=xslprocessor.selectSingleNode(xmldom.makeNode(address_doc),'/geocode_response/geocode');
765 xslprocessor.valueOf(geocode_node,'@match_count',l_match_count);
766 if to_number(xslprocessor.valueOf(geocode_node,'@match_count'))>0 then
767 match_node :=xslprocessor.selectSingleNode(geocode_node,'match');
768 address_node :=xslprocessor.selectSingleNode(match_node,'output_address');
769 xslprocessor.valueOf(address_node,'@name',name);
770 xslprocessor.valueOf(address_node,'@house_number',house_number);
771 xslprocessor.valueOf(address_node,'@street',street);
772 xslprocessor.valueOf(address_node,'@builtup_area',builtup_area);
773 xslprocessor.valueOf(address_node,'@order1_area',order1_area);
774 xslprocessor.valueOf(address_node,'@order2_area',order2_area);
775 xslprocessor.valueOf(address_node,'@order8_area',order8_area);
776 xslprocessor.valueOf(address_node,'@country',country_name);
777 xslprocessor.valueOf(address_node,'@postal_code',postal_code);
778 xslprocessor.valueOf(match_node,'@longitude',l_longitude);
779 xslprocessor.valueOf(match_node,'@latitude',l_latitude);
780 if (l_longitude is not null and l_latitude is not null) then
781 geometry :=MDSYS.SDO_GEOMETRY(2001
782 ,8307
783 ,MDSYS.SDO_POINT_TYPE
784 (l_longitude
785 ,l_latitude
786 ,NULL)
787 ,NULL
788 ,NULL);
789 else
790 geometry :=null;
791 end if;
792 end if;
793 xmldom.freeDocument(address_doc);
794 exception
795 when others then
796 xmldom.freeDocument(address_doc);
797 raise;
798
799 end address2full;
800 --
801 -- -------------------------------------------------------------------------
802 -- |---------------------------< sdo_distance >----------------------------|
803 -- -------------------------------------------------------------------------
804 -- wrapper for sdo_geom.sdo_distance function
805 --
806 function sdo_distance
807 (geom1 in MDSYS.SDO_GEOMETRY
808 ,geom2 in MDSYS.SDO_GEOMETRY
809 ,tolerance in number) return number
810 as
811 l_retval number;
812 begin
813 if geom1 is not null and geom2 is not null then
814 l_retval:=sdo_miles(geom1,geom2,tolerance);
815 if l_retval is not null then
816 l_retval:=l_retval/69.171;
817 end if;
818 end if;
819 return l_retval;
820 end sdo_distance;
821 --
822 -- -------------------------------------------------------------------------
823 -- |---------------------------< sdo_miles >----------------------------|
824 -- -------------------------------------------------------------------------
825 -- wrapper for sdo_geom.sdo_distance function
826 --
827 function sdo_miles
828 (geom1 in MDSYS.SDO_GEOMETRY
829 ,geom2 in MDSYS.SDO_GEOMETRY
830 ,tolerance in number) return number
831 as
832 begin
833 if geom1 is null or geom2 is null then
834 return to_number(null);
835 else
836 if g_conversion is null then
837 if hr_general2.get_oracle_db_version>=9 then
838 g_conversion:=1/1609.344;
839 else
840 g_conversion:=69.171;
841 end if;
842 end if;
843 return sdo_geom.sdo_distance(geom1,geom2,tolerance)*g_conversion;
844 end if;
845 end sdo_miles;
846 end irc_location_utility;