[Home] [Help]
PACKAGE BODY: APPS.CSF_LF_GEOPVT
Source
1 PACKAGE BODY CSF_LF_GEOPVT AS
2 /* $Header: CSFVGEOB.pls 120.7 2011/02/10 07:37:59 sseshaiy noship $*/
3
4 /**
5 * Returns the address components like country, state, city,street
6 *and building after doing reverse geo coding.
7 */
8 PROCEDURE insert_geo_batch_address(
9 p_task_rec csf_task_address_pvt.task_rec_type
10 )
11 IS
12 BEGIN
13 INSERT INTO csf_geo_batch_address(
14 ADDRESS1,
15 ADDRESS2,
16 ADDRESS3,
17 ADDRESS4,
18 POSTAL_CODE,
19 CITY,
20 COUNTY,
21 STATE,
22 COUNTRY,
23 ADDRESS_ID)
24 VALUES
25 (initcap(p_task_rec.address1),
26 initcap(p_task_rec.address2),
27 initcap(p_task_rec.address3),
28 initcap(p_task_rec.address4),
29 p_task_rec.postal_code,
30 initcap(p_task_rec.city),
31 initcap(p_task_rec.county),
32 initcap(p_task_rec.state),
33 initcap(p_task_rec.country),
34 p_task_rec.location_id
35 );
36 END;
37
38 PROCEDURE update_geo_batch_address(
39 p_task_rec csf_task_address_pvt.task_rec_type
40 )
41 IS
42 BEGIN
43 UPDATE csf_geo_batch_address SET
44 ADDRESS1 = initcap(p_task_rec.address1),
45 ADDRESS2 = initcap(p_task_rec.address2),
46 ADDRESS3 = initcap(p_task_rec.address3),
47 ADDRESS4 = initcap(p_task_rec.address4),
48 POSTAL_CODE = p_task_rec.postal_code,
49 CITY = initcap(p_task_rec.city),
50 COUNTY = initcap(p_task_rec.county),
51 STATE = initcap(p_task_rec.state),
52 COUNTRY = initcap(p_task_rec.country),
53 ACCURACY = NULL,
54 LATITUDE = NULL,
55 LONGITUDE = NULL,
56 SEGMENT_ID = NULL
57 WHERE ADDRESS_ID = p_task_rec.location_id;
58 END;
59
60 PROCEDURE delete_valid_geo_batch_address
61 IS
62 BEGIN
63 DELETE FROM csf_geo_batch_address
64 WHERE ACCURACY >= fnd_profile.VALUE('CSF_LOC_ACC_LEVELS');
65 commit;
66 END;
67
68 PROCEDURE move_invalid_to_geo_batch(
69 p_task_rec csf_task_address_pvt.task_rec_type
70 )
71 IS
72 l_location_id NUMBER;
73 BEGIN
74 l_location_id := 0;
75 SELECT count(*) into l_location_id FROM csf_geo_batch_address WHERE address_id = p_task_rec.location_id;
76 IF(l_location_id = 0)
77 THEN
78 insert_geo_batch_address(p_task_rec);
79 ELSIF(l_location_id = 1)
80 THEN
81 update_geo_batch_address(p_task_rec);
82 END IF;
83 END;
84
85 PROCEDURE resolve_address(
89 , x_msg_data OUT NOCOPY VARCHAR2
86 p_api_version IN NUMBER
87 , x_return_status OUT NOCOPY VARCHAR2
88 , x_msg_count OUT NOCOPY NUMBER
90 , p_location_id IN NUMBER
91 , p_address1 IN VARCHAR2
92 , p_address2 IN VARCHAR2
93 , p_address3 IN VARCHAR2
94 , p_address4 IN VARCHAR2
95 , p_city IN VARCHAR2
96 , p_postalcode IN VARCHAR2
97 , p_county IN VARCHAR2
98 , p_state IN VARCHAR2
99 , p_province IN VARCHAR2
100 , p_country IN VARCHAR2
101 , p_accu_fac OUT NOCOPY NUMBER
102 , p_segment_id OUT NOCOPY NUMBER
103 , p_offset OUT NOCOPY NUMBER
104 , p_side OUT NOCOPY NUMBER
105 , p_lon OUT NOCOPY NUMBER
106 , p_lat OUT NOCOPY NUMBER
107 )
108 IS
109 l_country_code hz_locations.country%TYPE;
110 l_geometry mdsys.sdo_geometry;
111 CURSOR c_country_code (p_country hz_locations.country%TYPE)
112 IS
113 SELECT ftt.territory_code country_code
114 FROM fnd_territories_tl ftt
115 WHERE UPPER (ftt.territory_short_name) = UPPER (p_country)
116 AND ftt.language = 'US';
117 BEGIN
118 OPEN c_country_code (p_country);
119 FETCH c_country_code
120 INTO l_country_code;
121 CLOSE c_country_code;
122
123 csf_resource_address_pvt.resolve_address(
124 p_api_version => p_api_version
125 , x_return_status => x_return_status
126 , x_msg_count => x_msg_count
127 , x_msg_data => x_msg_data
128 , p_location_id => p_location_id
129 , p_building_num => '_'
130 , p_address1 => p_address1
131 , p_address2 => p_address2
132 , p_address3 => p_address3
133 , p_address4 => p_address4
134 , p_city => p_city
135 , p_state => p_state
136 , p_postalcode => p_postalcode
137 , p_county => p_county
138 , p_province => p_province
139 , p_country => p_country
140 , p_country_code => l_country_code
141 , x_geometry => l_geometry
142 , p_commit => 'T'
143 , p_update_address => 'T'
144 );
145 IF (x_return_status = 'S')THEN
146 p_segment_id := l_geometry.sdo_ordinates(5);
147 p_offset := l_geometry.sdo_ordinates(6);
148 p_side := l_geometry.sdo_ordinates(7);
149 p_lon := l_geometry.sdo_ordinates(2);
150 p_lat := l_geometry.sdo_ordinates(1);
151 p_accu_fac := l_geometry.sdo_ordinates(3);
152 END IF;
153 END;
154
155 PROCEDURE CSF_LF_ResolveGEOAddress
156 ( p_api_version IN NUMBER
157 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
158 , p_latitude IN NUMBER
159 , p_longitude IN NUMBER
160 , p_dataset IN VARCHAR2
161 , p_country OUT NOCOPY VARCHAR2
162 , p_state OUT NOCOPY VARCHAR2
163 , p_county OUT NOCOPY VARCHAR2
164 , p_city OUT NOCOPY VARCHAR2
165 , p_roadname OUT NOCOPY VARCHAR2
166 , p_postalcode OUT NOCOPY VARCHAR2
167 , p_bnum OUT NOCOPY VARCHAR2
168 , p_dist OUT NOCOPY VARCHAR2
169 , p_accuracy_lvl OUT NOCOPY VARCHAR2
170 , x_msg_count OUT NOCOPY NUMBER
171 , x_msg_data OUT NOCOPY VARCHAR2
172 , x_return_status OUT NOCOPY VARCHAR2
173 )
174
175 IS
176 l_api_name CONSTANT VARCHAR2(30) := 'CSF_LF_ResolveGEOAddress';
177 l_api_version CONSTANT NUMBER := 1.0;
178 l_count NUMBER := 0;
179 l_dist NUMBER := -1;
180 l_within_dist NUMBER := -1;
181 l_parentid VARCHAR2(40);
182 l_place_lvl VARCHAR2(10);
183 l_countryid VARCHAR2(40);
184 l_name VARCHAR2(100);
185 l_prevname VARCHAR2(100);
186 l_roadsegmentid VARCHAR2(40);
187 l_accuracy_lvl VARCHAR2(10) := NVL(fnd_profile.value('CSF_LOC_ACC_LEVELS'),''+0);
188 l_profile VARCHAR2(10);
189 l_acc_dist VARCHAR2(40);
190 l_sql_stmt VARCHAR2(2000);
191 l_dyn_tbl_name VARCHAR2(10) := NVL(fnd_profile.value('CSF_SPATIAL_DATASET_NAME'),'');
192
193 TYPE ref_cursor_type IS REF CURSOR;
194
195 --Cursor to find out the nearest geometry with in specified range from poi table
196 cursor_poi_dist_chk ref_cursor_type;
197 cursor_poi ref_cursor_type;
198
199 --Cursor to find out the nearest geometry with in specified range from road segment table in case if not exist in poi table
200 cursor_rdseg_dist_chk ref_cursor_type;
201 cursor_rdseg ref_cursor_type;
202
203 BEGIN
204
205 if ( l_api_version <> p_api_version ) then
206 raise csf_lf_version_error;
207 end if;
208
209 if ( p_init_msg_list = 'TRUE' ) then
210 x_msg_count := 0; /* FND_MSG_PUB.initialize; */
211 end if;
212
213 l_dyn_tbl_name := p_dataset;
214 x_return_status := FND_API.G_RET_STS_SUCCESS;
215 p_accuracy_lvl := l_accuracy_lvl;
216 --
217 -- Validate parameters
218 --
219
220 if ( p_latitude = NULL or p_latitude = '' ) then
221 raise CSF_LF_LATITUDE_NOT_SET_ERROR;
222 end if;
223
224 if ( p_longitude = NULL or p_longitude = '' ) then
225 raise CSF_LF_LONGITUDE_NOT_SET_ERROR;
226 end if;
227
228 --Initialize message count and mssage data. we will use var x_msg_data to store info which can be used for debug purpose
229 x_msg_count := 0;
233 l_profile := '100';
230 x_msg_data := 'Success';
231
232 if(l_accuracy_lvl = '2') then
234 elsif (l_accuracy_lvl = '1') then
235 l_profile := '2000';
236 else
237 l_profile := '10000';
238 end if;
239
240 l_acc_dist := 'distance=' || l_profile ;
241
242 if(l_dyn_tbl_name = 'NONE') then
243 l_dyn_tbl_name := '';
244 end if;
245
246
247
248 --Open cursor to check the nearest geometry with in specified range from poi table
249 l_sql_stmt := 'SELECT 1 FROM csf_lf_pois' || l_dyn_tbl_name || ' r
250 WHERE SDO_WITHIN_DISTANCE(r.poi_point, SDO_GEOMETRY(2004,8307,null,SDO_ELEM_INFO_ARRAY(1,1,1,3,0,5),
251 SDO_ORDINATE_ARRAY(:1, :2)),:3) = ''TRUE'' and rownum = 1';
252
253 OPEN cursor_poi_dist_chk for l_sql_stmt USING p_longitude,p_latitude,l_acc_dist;
254 LOOP
255 FETCH cursor_poi_dist_chk INTO l_within_dist;
256 EXIT WHEN cursor_poi_dist_chk%NOTFOUND;
257 END LOOP;
258 CLOSE cursor_poi_dist_chk;
259
260 --Open cursor to fetch the nearest geometry with in specified range from poi table
261 IF(l_within_dist <> -1) THEN
262 l_sql_stmt := 'SELECT /*+ INDEX(r CSF_LF_POIS_N2) */ ROADSEGMENT_ID, SDO_NN_DISTANCE(1) dist
263 FROM csf_lf_pois' || l_dyn_tbl_name || ' r
264 WHERE SDO_NN(r.poi_point, SDO_GEOMETRY(2004,8307,null,SDO_ELEM_INFO_ARRAY(1,1,1,3,0,5),
265 SDO_ORDINATE_ARRAY(:1, :2)), ''sdo_num_res=1'', 1) = ''TRUE'' ORDER BY dist';
266
267 OPEN cursor_poi for l_sql_stmt USING p_longitude,p_latitude;
268 LOOP
269 FETCH cursor_poi INTO l_roadsegmentid, l_dist; -- fetch data into local variables
270 EXIT WHEN cursor_poi%NOTFOUND;
271 END LOOP;
272 CLOSE cursor_poi;
273 END IF;
274
275 x_msg_count := 1;
276 x_msg_data := ':acrcy_lvl:' || l_accuracy_lvl || ':profile_dist:' || l_profile || ':poi_dist:' || l_dist;
277
278 --Open cursor to fetch the nearest geometry with in specified range from road segment table in case if not exist in poi table
279 if(l_dist = -1) THEN
280 l_within_dist := -1;
281 l_sql_stmt := 'SELECT 1 FROM csf_lf_roadsegments' || l_dyn_tbl_name || ' r
282 WHERE SDO_WITHIN_DISTANCE(r.ROADSEGMENT_GEOMETRY, SDO_GEOMETRY(2002,8307,null,SDO_ELEM_INFO_ARRAY(1,2,1),
283 SDO_ORDINATE_ARRAY(:1, :2)),:3) = ''TRUE'' and rownum = 1';
284
285 --Open cursor to check the nearest geometry with in specified range from road segmen table
286 OPEN cursor_rdseg_dist_chk for l_sql_stmt USING p_longitude,p_latitude,l_acc_dist;
287 LOOP
288 FETCH cursor_rdseg_dist_chk INTO l_within_dist; -- fetch data into local variables
289 EXIT WHEN cursor_rdseg_dist_chk%NOTFOUND;
290 END LOOP;
291 CLOSE cursor_rdseg_dist_chk;
292
293
294 IF(l_within_dist <> -1) THEN
295 l_sql_stmt := 'SELECT /*+ INDEX(r CSF_LF_RDSEGS_N2) */ ROADSEGMENT_ID, SDO_NN_DISTANCE(1) dist
296 FROM csf_lf_roadsegments' || l_dyn_tbl_name || ' r
297 WHERE SDO_NN(r.ROADSEGMENT_GEOMETRY, SDO_GEOMETRY(2002,8307,null,SDO_ELEM_INFO_ARRAY(1,2,1),
298 SDO_ORDINATE_ARRAY(:1, :2, :3, :4)),
299 ''sdo_num_res=1'', 1) = ''TRUE'' ORDER BY dist';
300
301 OPEN cursor_rdseg for l_sql_stmt USING p_longitude,p_latitude,p_longitude,p_latitude;
302 LOOP
303 FETCH cursor_rdseg INTO l_roadsegmentid, l_dist; -- fetch data into local variables
304 EXIT WHEN cursor_rdseg%NOTFOUND;
305 END LOOP;
306 CLOSE cursor_rdseg;
307 x_msg_count := 1;
308 x_msg_data := ':acrcy_lvl:' || l_accuracy_lvl || ':rd_dist:' || l_dist;
309 END IF;
310 END IF;
311
312 p_dist := l_dist;
313
314 --check if any nearest geometry found with in the specified range
315 if (l_dist <> -1) THEN
316 ---Query to find postal code for given roadsegmentId
317 l_sql_stmt := 'select postal_code from csf_lf_roadsegm_posts' || l_dyn_tbl_name || ' rp, csf_lf_postcodes' || l_dyn_tbl_name || ' p where rp.postal_code_id =
318 p.postal_code_id and rp.roadsegment_id = :1 and rownum = 1';
319
320 EXECUTE IMMEDIATE l_sql_stmt into p_postalcode USING l_roadsegmentid;
321
322 x_msg_data := x_msg_data || ':pc:' || p_postalcode;
323
324 ---Query to find street name for given roadsegmentId
325 l_sql_stmt := 'select n.name , p.place_id from csf_lf_names' || l_dyn_tbl_name || ' n,csf_lf_roadsegm_places' || l_dyn_tbl_name ||
326 ' pn, csf_lf_places' || l_dyn_tbl_name || ' p, csf_lf_roadsegm_names' || l_dyn_tbl_name ||
327 ' rn where rn.roadsegment_id = :1 and rn.name_id = n.name_id and rn.roadsegment_id =
328 pn.roadsegment_id and pn.place_id = p.place_id and rownum = 1';
329
330 EXECUTE IMMEDIATE l_sql_stmt into p_roadname, l_parentid USING l_roadsegmentid;
331
332 x_msg_data := x_msg_data || ':st_name:' || p_roadname || ':pl_id:' || l_parentid;
333
334 ---Query to find building number for given roadsegmentId
335 l_sql_stmt := 'Select round((min(address_start) + max(address_end))/2) from (select block_id, 1
336 address_side,left_address_format address_format, left_address_scheme address_scheme, start_left_address
337 address_start, end_left_address address_end, address_type from csf_lf_blocks' || l_dyn_tbl_name || ' where roadsegment_id = :1 union all
338 select block_id, 2, right_address_format, right_address_scheme,start_right_address, end_right_address, address_type
339 from csf_lf_blocks where roadsegment_id = :2 and rownum = 1)';
340
341 EXECUTE IMMEDIATE l_sql_stmt into p_bnum USING l_roadsegmentid,l_roadsegmentid;
342
343 x_msg_data := x_msg_data || ':bnum:' || p_bnum;
344
345 ---Query to find parent place names for derived street name using its parentId
346 while l_parentid <> -1 LOOP
347 l_countryid := l_parentid;
348
349 --Query to find name of place Id and its parent place Id
353
350 l_sql_stmt := 'select n.name, p.parent_place_id, p.place_parent_level from csf_lf_names' || l_dyn_tbl_name || ' n,
351 csf_lf_place_names' || l_dyn_tbl_name || ' pn, csf_lf_places' || l_dyn_tbl_name || ' p where n.name_id = pn.name_id and
352 pn.place_id = p.place_id and p.place_id = :1 and pn.name_type = ''O'' and rownum = 1';
354 EXECUTE IMMEDIATE l_sql_stmt into l_name, l_parentid, l_place_lvl USING l_parentid;
355
356
357 x_msg_data := x_msg_data || ':parent_name_' || l_count || ':' || l_name || ':parent_id_' || l_count || ':' || l_parentid;
358
359 if(l_count = 0) THEN
360 p_city := l_name;
361 ELSE
362 if (l_prevname <> l_name and l_parentid <> -1 and l_place_lvl <> 0) THEN
363 p_county := l_name;
364 end if;
365
366 if (l_prevname <> l_name and l_parentid <> -1) THEN
367 p_state := l_name;
368 end if;
369 end if;
370
371 l_count := l_count + 1;
372 l_prevname := l_name;
373 END LOOP;
374
375 x_msg_data := x_msg_data || ':county:' || p_county || ':state:' || p_state;
376
377 --Query to find out country name
378 l_sql_stmt := 'select country_name from csf_sdm_ctry_profiles' || l_dyn_tbl_name || ' where place_id = :1 and
379 NATIONAL_LANG_CODE = ''ENG''';
380
381 EXECUTE IMMEDIATE l_sql_stmt into p_country USING l_countryid;
382
383
384 x_msg_data := x_msg_data || ':country:' || p_country;
385
386 ELSE
387 x_return_status := FND_API.G_RET_STS_ERROR;
388 end if; -- End of if (l_dist <> -1)
389
390 x_msg_data := x_msg_data || ':ret_status:' || x_return_status;
391
392 EXCEPTION
393 when CSF_LF_VERSION_ERROR then
394 x_return_status := FND_API.G_RET_STS_ERROR;
395 x_msg_count := 1;
396 x_msg_data := 'Incompatibale version';
397 when others then
398 x_return_status := FND_API.G_RET_STS_ERROR;
399
400 END CSF_LF_ResolveGEOAddress;
401
402 PROCEDURE CSF_LF_ResolveAddress
403 ( p_api_version IN NUMBER
404 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
405 , x_return_status OUT NOCOPY VARCHAR2
406 , x_msg_count OUT NOCOPY NUMBER
407 , x_msg_data OUT NOCOPY VARCHAR2
408 , p_country IN VARCHAR2
409 , p_state IN VARCHAR2 default NULL
410 , p_county IN VARCHAR2 DEFAULT NULL
411 , p_province IN VARCHAR2 DEFAULT NULL
412 , p_city IN VARCHAR2
413 , p_postalCode IN VARCHAR2 default NULL
414 , p_roadname IN VARCHAR2
415 , p_buildingnum IN VARCHAR2 default NULL
416 , p_alternate IN VARCHAR2 default NULL
417 , p_accu_fac OUT NOCOPY NUMBER
418 , p_segment_id OUT NOCOPY NUMBER
419 , p_offset OUT NOCOPY NUMBER
420 , p_side OUT NOCOPY NUMBER
421 , p_lon OUT NOCOPY NUMBER
422 , p_lat OUT NOCOPY NUMBER
423 , p_srid OUT NOCOPY NUMBER
424 )
425 IS
426 l_api_name CONSTANT VARCHAR2(30) := 'CSF_LF_ResolveAddress';
427 l_api_version CONSTANT NUMBER := 1.0;
428 l_retCode NUMBER;
429 l_numResults NUMBER;
430 l_return_status VARCHAR2(1);
431 l_result_array csf_lf_pub.csf_lf_resultarray;
432 BEGIN
433 l_result_array := csf_lf_pub.csf_lf_resultarray();
434 csf_lf_pub.csf_lf_resolveaddress(
435 p_api_version => l_api_version
436 , p_init_msg_list => FND_API.G_FALSE
437 , x_return_status => x_return_status
438 , x_msg_count => x_msg_count
439 , x_msg_data => x_msg_data
440 , p_country => p_country
441 , p_state => p_state
442 , p_city => p_city
443 , p_postalCode => p_postalcode
444 , p_roadname => p_roadname
445 , p_buildingnum => p_buildingnum
446 , p_alternate => p_alternate
447 , x_resultsArray => l_result_array);
448 IF (x_return_status = 'S')THEN
449 p_accu_fac := l_result_array(1).accuracy_factor;
450 p_segment_id := l_result_array(1).locus.sdo_ordinates(5);
451 p_offset := l_result_array(1).locus.sdo_ordinates(6);
452 p_side := l_result_array(1).locus.sdo_ordinates(7);
453 p_lon := l_result_array(1).locus.sdo_ordinates(2);
454 p_lat := l_result_array(1).locus.sdo_ordinates(1);
455 p_srid := l_result_array(1).locus.sdo_srid;
456 END IF;
457 END CSF_LF_ResolveAddress;
458
459 END CSF_LF_GEOPVT;