[Home] [Help]
Skip to content
PACKAGE BODY: APPS.CSF_LF_PUB
Source
1 PACKAGE BODY CSF_LF_PUB AS
2 /*$Header: CSFPLFB.pls 120.20 2011/04/25 13:32:47 sseshaiy ship $*/
3
4 g_debug VARCHAR2(1) := NVL(fnd_profile.value('AFLOG_ENABLED'), 'N');
5 g_debug_level NUMBER := NVL(fnd_profile.value('AFLOG_LEVEL'), fnd_log.level_event);
6
7 -- ----------------------------------------------------------------------------
8 -- register JSP
9 -- ----------------------------------------------------------------------------
10 FUNCTION resolveaddress (
11 p_country VARCHAR2,
12 p_state VARCHAR2,
13 p_province VARCHAR2,
14 p_county VARCHAR2,
15 p_city VARCHAR2,
16 p_postalcode VARCHAR2,
17 p_roadname VARCHAR2,
18 p_buildingnum VARCHAR2,
19 p_alternate VARCHAR2,
20 -- added for LF enhancement of forced accuracy
21 p_requiredAccuracy VARCHAR2
22 )
23 RETURN csf_lf_resultarray_int
24 AS
25 LANGUAGE JAVA
26 NAME 'oracle.apps.csf.lf.server.Address.resolveAddress(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String
27 , java.lang.String, java.lang.String, java.lang.String, java.lang.String,
28 java.lang.String) return oracle.sql.ARRAY';
29
30 -- ----------------------------------------------------------------------------
31 -- private types and constants
32 -- ----------------------------------------------------------------------------
33 TYPE POINT_COORD IS RECORD(LAT NUMBER, LONG NUMBER);
34
35 EARTH_RADIUS CONSTANT NUMBER := 6378137;
36 PI CONSTANT NUMBER := 3.14159265359;
37
38 -- ----------------------------------------------------------------------------
39 -- forward declaration of private procedures
40 -- ----------------------------------------------------------------------------
41 -- duplicate of oracle.apps.csf.lf.server.LFCommon.deg2rad()
42 FUNCTION DEG2RAD ( DEG NUMBER ) RETURN NUMBER;
43
44 FUNCTION RAD2DEG ( DEG NUMBER ) RETURN NUMBER;
45
46 -- duplicate of oracle.apps.csf.lf.server.LFCommon.getDistance()
47 FUNCTION GEODISTANCE ( POS1 IN POINT_COORD
48 , POS2 IN POINT_COORD ) RETURN NUMBER;
49
50 PROCEDURE NEW_COORD ( POS1 IN POINT_COORD
51 , DISTANCE IN NUMBER
52 , HEAD IN NUMBER
53 , POS2 OUT NOCOPY POINT_COORD );
54
55 FUNCTION HEADING ( POS1 IN POINT_COORD
56 , POS2 IN POINT_COORD ) RETURN NUMBER;
57
58 PROCEDURE GETCOORDONSEGMENT ( segmentLength NUMBER
59 , segmentGeometry MDSYS.SDO_ORDINATE_ARRAY
60 , percentAlongSeg NUMBER
61 , coord OUT NOCOPY POINT_COORD );
62
63 PROCEDURE GETSEGMENTLENGTH ( segmentGeometry IN MDSYS.SDO_ORDINATE_ARRAY
64 , segmentLength OUT NOCOPY NUMBER );
65
66 PROCEDURE debug(p_message VARCHAR2, p_module VARCHAR2, p_level NUMBER) IS
67 BEGIN
68 IF g_debug = 'Y' AND p_level >= g_debug_level THEN
69 IF fnd_file.log > 0 THEN
70 IF p_message = ' ' THEN
71 fnd_file.put_line(fnd_file.log, '');
72 ELSE
73 fnd_file.put_line(fnd_file.log, rpad(p_module, 20) || ': ' || p_message);
74 END IF;
75 ELSE
76 fnd_log.string(p_level, 'csf.plsql.CSF_LF_PUB.' || p_module, p_message);
77 END IF;
78 END IF;
79 --dbms_output.put_line(rpad(p_module, 20) || ': ' || p_message);
80 END debug;
81
82 -- ----------------------------------------------------------------------------
83 -- public procedures
84 -- ----------------------------------------------------------------------------
85
86 -- Start of Comments
87 -- API Name : CSF_LF_ResolveAddress
88 -- Type : Public
89 -- Pre-req :
90 -- Function : Return a locus from an input address specification
91 -- Parameters :
92 -- p_api_version IN NUMBER, required
93 -- p_init_msg_list IN VARCHAR2 := NULL optional
94 -- x_return_status OUT VARCHAR2,
95 -- x_msg_count OUT NUMBER,
96 -- x_msg_data OUT VARCHAR2,
97 -- p_country IN VARCHAR2 required
98 -- p_state IN VARCHAR2 := NULL optional
99 -- p_county IN VARCHAR2 := NULL optional
100 -- p_province IN VARCHAR2 := NULL optional
101 -- p_city IN VARCHAR2 required
102 -- p_postalCode IN VARCHAR2 := NULL optional
103 -- p_roadname IN VARCHAR2 required
104 -- p_buildingnum IN VARCHAR := NULL optioanl
105 -- p_alternate IN VARCHAR2 := NULL optional
106 -- Version: 1.0
107 -- History: 19-04-2000 L Mbekeani Added lat long to locus
108 -- End Comments
109 PROCEDURE CSF_LF_ResolveAddress
110 ( p_api_version IN NUMBER
111 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
112 , x_return_status OUT NOCOPY VARCHAR2
113 , x_msg_count OUT NOCOPY NUMBER
114 , x_msg_data OUT NOCOPY VARCHAR2
115 , p_country IN VARCHAR2
116 , p_state IN VARCHAR2 default NULL
117 , p_county IN VARCHAR2 DEFAULT NULL
118 , p_province IN VARCHAR2 DEFAULT NULL
119 , p_city IN VARCHAR2
120 , p_postalCode IN VARCHAR2 default NULL
121 , p_roadname IN VARCHAR2
122 , p_buildingnum IN VARCHAR2 default NULL
123 , p_alternate IN VARCHAR2 default NULL
124 , x_resultsArray OUT NOCOPY CSF_LF_RESULTARRAY
125 )
126 IS
127 l_api_name CONSTANT VARCHAR2(30) := 'CSF_LF_ResolveAddress';
128 l_api_version CONSTANT NUMBER := 1.0;
129 l_result_int CSF_LF_RESULTARRAY_INT;
130 l_retCode NUMBER;
131 l_numResults NUMBER;
132 J NUMBER;
133 l_srid number;
134 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
135 -- added for LF enhancement of forced accuracy
136 l_requiredAccuracy VARCHAR2(50);
137 TYPE REF_SRID IS REF CURSOR;
138 C_SRID REF_SRID;
139 TYPE country_refcur IS REF CURSOR;
140 ref_cur country_refcur;
141 sql_stmt_str VARCHAR2(2000);
142 l_data_set_name VARCHAR2(40);
143 /*
144 CURSOR C_SRID
145 IS
146 SELECT nvl(cscp.default_display_center.sdo_srid, 8307) srid
147 FROM csf_sdm_ctry_profiles cscp
148 WHERE cscp.default_display_center IS NOT NULL;
149 */
150 l_country VARCHAR2(100);
151 CURSOR ctry_hr_to_spatial IS
152 SELECT SPATIAL_COUNTRY_NAME
153 FROM CSF_SPATIAL_CTRY_MAPPINGS
154 WHERE HR_COUNTRY_NAME = p_country;
155 BEGIN
156
157 IF l_debug THEN
158 debug(' --> Inside CSF_LF_ResolveAddress ' , l_api_name, fnd_log.level_statement);
159 END IF;
160
161 if ( l_api_version <> p_api_version ) then
162 raise csf_lf_version_error;
163 end if;
164
165 if ( p_init_msg_list = 'TRUE' ) then
166 x_msg_count := 0; /* FND_MSG_PUB.initialize; */
167 end if;
168
169 x_return_status := FND_API.G_RET_STS_SUCCESS;
170
171 --
172 -- Validate parameters
173 --
174
175 if ( p_country = NULL ) then
176 raise CSF_LF_COUNTRY_NOT_SET_ERROR;
177 end if;
178
179 if ( p_city = NULL ) then
180 raise CSF_LF_CITY_NOT_SET_ERROR;
181 end if;
182
183 if ( (p_roadname = NULL) or (p_roadname = '') ) then
184 raise CSF_LF_ROAD_NOT_SET_ERROR;
185 end if;
186
187 x_msg_count := 0;
188 x_msg_data := 'Success';
189
190 --l_result_int := RESOLVEADDRESS(p_country, p_state, p_city, p_postalCode, p_roadName, p_buildingnum, p_alternate);
191 --l_result_int := RESOLVEADDRESS(p_country, p_state, p_city, p_postalCode, p_roadName, '_', p_alternate);
192
193 -- added for LF enhancement of forced accuracy
194 l_requiredAccuracy := NVL(fnd_profile.value('CSF_LOC_ACC_LEVELS'),''+0);
195
196 /** Bug 7570463 **/
197 open ctry_hr_to_spatial;
198 fetch ctry_hr_to_spatial into l_country;
199 close ctry_hr_to_spatial;
200
201 IF (l_country is null) THEN
202 l_country := p_country;
203 END IF;
204
205 IF l_debug THEN
206 debug(' --> p_buildingnum = ' || p_buildingnum, l_api_name, fnd_log.level_statement);
207 END IF;
208
209 IF ((p_buildingnum = NULL) OR (p_buildingnum = ''))
210 THEN
211 l_result_int :=
212 resolveaddress (l_country,
213 p_state,
214 p_province,
215 p_county,
216 p_city,
217 p_postalcode,
218 p_roadname,
219 '_',
220 p_alternate,
221 -- added for LF enhancement of forced accuracy
222 l_requiredAccuracy
223 );
224 ELSE
225 l_result_int :=
226 resolveaddress (l_country,
227 p_state,
228 p_province,
229 p_county,
230 p_city,
231 p_postalcode,
232 p_roadname,
233 p_buildingnum,
234 p_alternate,
235 -- added for LF enhancement of forced accuracy
236 l_requiredAccuracy
237 );
238 END IF;
239
240 l_retCode := l_result_int(1).record_type;
241
242 IF l_debug THEN
243 debug(' --> l_retCode = ' || l_retCode, l_api_name, fnd_log.level_statement);
244 END IF;
245
246 --
247 -- TODO: Perform Geometry to Locus conversion then load the result array back
248 -- If the first record type is greater than 0 then we havw a problem */
249 --
250
251 /*
252 if ( l_retCode = MISSING_COUNTRY_NAME ) then
253
254 x_return_status := FND_API.G_RET_STS_ERROR;
255 x_msg_count := 1;
256 x_msg_data := l_result_int(1).msg_data;
257
258 raise CSF_LF_COUNTRY_NOT_SET_ERROR;
259
260 elsif ( l_retCode = MISSING_STATE_NAME ) then
261
262 x_return_status := FND_API.G_RET_STS_ERROR;
263 x_msg_count := 1;
264 x_msg_data := l_result_int(1).msg_data;
265
266 raise CSF_LF_STATE_NOT_SET_ERROR;
267
268 elsif ( l_retCode = MISSING_CITY_NAME ) then
269
270 x_return_status := FND_API.G_RET_STS_ERROR;
271 x_msg_count := 1;
272 x_msg_data := l_result_int(1).msg_data;
273
274 raise CSF_LF_CITY_NOT_SET_ERROR;
275
276 elsif ( l_retCode = MISSING_ROAD_NAME ) then
277
278 x_return_status := FND_API.G_RET_STS_ERROR;
279 x_msg_count := 1;
280 x_msg_data := l_result_int(1).msg_data;
281
282 raise CSF_LF_ROAD_NOT_SET_ERROR;
283 */
284 -- added for LF enhancement of forced accuracy
285 if ( l_retCode = REQUIRED_ACCURACY_EXCEPTION ) then
286 x_return_status := FND_API.G_RET_STS_ERROR;
287 x_msg_count := 1;
288 x_msg_data := l_result_int(1).msg_data;
289 RAISE CSF_LF_REQ_ACCURACY_NOT_GOT;
290
291 elsif ( l_retCode > 0 ) then
292
293 x_return_status := FND_API.G_RET_STS_ERROR;
294 x_msg_count := 1;
295 x_msg_data := l_result_int(1).msg_data;
296
297 else
298
299 --
300 -- Load the result items
301 --
302 l_numResults := l_result_int.count();
303 x_resultsArray := CSF_LF_RESULTARRAY();
304
305 IF l_debug THEN
306 debug(' --> l_numResults = ' || l_numResults, l_api_name, fnd_log.level_statement);
307 END IF;
308
309 l_data_set_name := fnd_profile.value('CSF_SPATIAL_MULTIDATASET_ENABLED');
310 IF (l_data_set_name = 'N' OR l_data_set_name IS NULL ) THEN
311 l_data_set_name := '';
312 ELSE
313 BEGIN
314 l_data_set_name := '';
315 sql_stmt_str := 'select spatial_dataset from csf_spatial_ctry_mappings
316 WHERE spatial_country_name = ''' || upper(l_country)|| '''';
317 OPEN ref_cur FOR sql_stmt_str;
318 FETCH ref_cur INTO l_data_set_name;
319 CLOSE ref_cur;
320 EXCEPTION
321 WHEN NO_DATA_FOUND THEN
322 l_data_set_name := '';
323 END;
324 END IF;
325
326 IF l_debug THEN
327 debug(' --> l_data_set_name = ' || l_data_set_name, l_api_name, fnd_log.level_statement);
328 END IF;
329
330 sql_stmt_str := 'SELECT nvl(cscp.default_display_center.sdo_srid, 8307) srid
331 FROM csf_sdm_ctry_profiles' || l_data_set_name || ' cscp
332 WHERE cscp.default_display_center IS NOT NULL';
333 --OPEN C_SRID;
334 OPEN C_SRID FOR sql_stmt_str;
335 FETCH C_SRID INTO l_srid;
336 CLOSE C_SRID;
337
338 -- Fix for bug 9299548
339 if((NVL(fnd_profile.VALUE('CSF_LF_INSTALLED'),'N') = 'N') OR l_srid IS NULL) then
340 l_srid := 8307;
341 end if;
342
343 IF l_debug THEN
344 debug(' --> l_srid = ' || l_srid, l_api_name, fnd_log.level_statement);
345 END IF;
346
347 for J in 1..l_numresults loop
348
349 x_resultsArray.extend(1);
350
351 IF l_debug THEN
352 debug(' --> l_result_int(J).lon = ' || l_result_int(J).lon, l_api_name, fnd_log.level_statement);
353 debug(' --> l_result_int(J).lat = ' || l_result_int(J).lat, l_api_name, fnd_log.level_statement);
354 debug(' --> l_result_int(J).accuracy_fact = ' || l_result_int(J).accuracy_fact, l_api_name, fnd_log.level_statement);
355 debug(' --> l_result_int(J).segment_id = ' || l_result_int(J).segment_id, l_api_name, fnd_log.level_statement);
356 END IF;
357
358 x_resultsArray(J).locus := mdsys.sdo_geometry(2001,l_srid,
359 mdsys.sdo_point_type( l_result_int(J).lon
360 , l_result_int(J).lat
361 , 0),
362 mdsys.sdo_elem_info_array(1,1,1,3,0,5),
363 mdsys.sdo_ordinate_array( l_result_int(J).lon
364 , l_result_int(J).lat
365 -- added for LF enhancement of forced accuracy
366 , l_result_int(J).accuracy_fact
367 , -9999
371 )
368 , l_result_int(J).segment_id
369 , l_result_int(J).percentage
370 , l_result_int(J).segment_side
372 );
373 x_resultsArray(J).record_type := l_result_int(J).record_type;
374 x_resultsArray(J).accuracy_factor := l_result_int(J).accuracy_fact;
375 x_resultsArray(J).country := l_result_int(J).country;
376 x_resultsArray(J).state := l_result_int(J).state;
377 x_resultsarray (j).county := l_result_int (j).county;
378 x_resultsarray (j).province := l_result_int (j).province;
379 x_resultsArray(J).city := l_result_int(J).city;
380 x_resultsArray(J).postalcode := l_result_int(J).postalcode;
381 x_resultsArray(J).road := l_result_int(J).road;
382 x_resultsArray(J).buildingnum := l_result_int(J).buildingnum;
383 x_resultsArray(J).alternate := l_result_int(J).alternate;
384
385 end loop;
386
387 x_return_status := FND_API.G_RET_STS_SUCCESS;
388 x_msg_count := l_numResults;
389 x_msg_data := 'Success';
390
391 IF l_debug THEN
392 debug(' --> End of CSF_LF_ResolveAddress ' , l_api_name, fnd_log.level_statement);
393 END IF;
394
395 end if;
396
397 EXCEPTION
398
399 when CSF_LF_VERSION_ERROR then
400 x_return_status := FND_API.G_RET_STS_ERROR;
401 x_msg_count := 1;
402 x_msg_data := 'Incompatibale version';
403
404 when CSF_LF_REQ_ACCURACY_NOT_GOT then
405 x_return_status := FND_API.G_RET_STS_ERROR;
406 x_msg_count := 1;
407 x_msg_data := l_result_int(1).msg_data;
408
409 when OTHERS then
410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
411 x_msg_count := 1;
412 x_msg_data := SQLERRM;
413
414 END CSF_LF_ResolveAddress;
415
416 -- Start of Comments
417 -- API Name : CSF_LocustoGeometry
418 -- Type : Public
419 -- Pre-req :
420 -- Function : Converts a Locust to a geometry
421 -- Parameters :
422 -- IN
423 -- p_api_version IN NUMBER required :=
424 -- p_init_msg_list IN VARCHAR2 = NULL optional :=
425 -- x_return_status OUT VARCHAR2 :=
426 -- x_msg_count OUT NUMBER :=
427 -- x_msg_data OUT VARCHAR2 :=
428 -- p_locus MDSYS.SDO_GEOMETRY required :=
429 -- x_point OUT MDSYS.SDO_GEOMETRY
430 -- Version:
431 -- End Comments
432 PROCEDURE CSF_LocustoGeometry
433 ( p_api_version IN NUMBER
434 , p_init_msg_list IN VARCHAR2 default fnd_api.g_false
435 , x_return_status OUT NOCOPY VARCHAR2
436 , x_msg_count OUT NOCOPY NUMBER
437 , x_msg_data OUT NOCOPY VARCHAR2
438 , p_locus IN MDSYS.SDO_GEOMETRY
439 , x_point OUT NOCOPY MDSYS.SDO_GEOMETRY
440 )
441 IS
442 x_geom mdsys.sdo_geometry;
443 segmentGeometry mdsys.sdo_geometry;
444 segmentLength number;
445 coord POINT_COORD;
446 x_segid number;
447 x_offset number;
448 x_direction number;
449 l_msg_count number;
450 l_msg_data varchar2(1000);
451 l_api_name CONSTANT VARCHAR2(30) := 'CSF_LocustoGeometry';
452 l_api_version number := 1.0;
453 TYPE REF_SEGID IS REF CURSOR;
454 C_SEGID REF_SEGID;
455 sql_stmt_str VARCHAR2(2000);
456 l_data_set_name VARCHAR2(40);
457
458 BEGIN
459
460 if ( l_api_version <> p_api_version ) then
461 raise CSF_LF_VERSION_ERROR;
462 end if;
463
464 if ( p_init_msg_list = 'TRUE' ) then
465 null; /* FND_MSG_PUB.initialize; */
466 end if;
467
468 x_return_status := FND_API.G_RET_STS_SUCCESS;
469
470 CSF_LOCUS_PUB.read_locus (
471 p_api_version => l_api_version
472 , x_return_status => x_return_status
473 , x_msg_count => l_msg_count
474 , x_msg_data => l_msg_data
475 , p_locus => p_locus
476 , x_geom => x_geom
477 , x_segid => x_segid
478 , x_offset => x_offset
479 , x_direction => x_direction
480 );
481
482 if ( x_return_status <> CSF_LOCUS_PUB.G_RET_LOCUS_SUCCESS ) then
483 return;
484 else
485 /* select roadsegment_geometry into segmentGeometry
486 from csf_lf_roadsegments
487 where roadSegment_id = x_segid; */
488
489 l_data_set_name := fnd_profile.value('CSF_SPATIAL_DATASET_NAME');
490 IF (l_data_set_name = 'NONE' OR l_data_set_name IS NULL ) THEN
491 l_data_set_name := '';
492 END IF;
493
494 sql_stmt_str := 'SELECT roadsegment_geometry into segmentGeometry
495 FROM csf_lf_roadsegments' || l_data_set_name||'
496 WHERE roadSegment_id = '|| x_segid;
497 OPEN C_SEGID FOR sql_stmt_str;
498 FETCH C_SEGID INTO segmentGeometry;
499 CLOSE C_SEGID;
500
501 getSegmentLength(segmentGeometry.sdo_ordinates,segmentLength);
502 getCoordonSegment(segmentLength, segmentGeometry.sdo_ordinates,x_offset,coord);
503
504 x_point := mdsys.sdo_geometry(2001,8307,mdsys.sdo_point_type(coord.long,coord.lat,0),null,null);
505
506 x_return_status := FND_API.G_RET_STS_SUCCESS;
507 end if;
508
509 -- Get the segId
510 -- Fetch The segment geometry
511 -- call GetLocusPosition
512 -- call create PointGeo.
513
514 EXCEPTION
515
516 when CSF_LF_VERSION_ERROR then
517 x_return_status := FND_API.G_RET_STS_ERROR;
518
519 when NO_DATA_FOUND then
523
520 x_return_status := FND_API.G_RET_STS_ERROR;
521
522 END CSF_LocustoGeometry;
524 -- Start of Comments
525 -- API Name : CSF_GeometrytoLocus
526 -- Type : Public
527 -- Pre-req :
528 -- Functiom : Converts a geomrty to a Locus
529 -- Parameters :
530 -- IN
531 -- p_api_version IN NUMBER required
532 -- p_init_msg_list IN VARCHAR2 : = NULL optional
533 -- x_return_status OUT VARCHAR2 :=
534 -- x_msg_count OUT NUMBER :=
535 -- x_msg_data OUT VARCHAR2 :=
536 -- p_point MDSYS.SDO_GEOMETRY := required
537 -- x_locus MDSYS.SDO_GEOMETRY
538 -- Version:
539 -- End Comments
540 PROCEDURE CSF_GeometrytoLocus
541 ( p_api_version IN NUMBER
542 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
543 , x_return_status OUT NOCOPY VARCHAR2
544 , x_msg_count OUT NOCOPY NUMBER
545 , x_msg_data OUT NOCOPY VARCHAR2
546 , p_point IN MDSYS.SDO_GEOMETRY
547 , x_locus OUT NOCOPY MDSYS.SDO_GEOMETRY
548 )
549 IS
550
551 l_api_name CONSTANT VARCHAR2(30) := 'CSF_GeometrytoLocus';
552 l_api_version NUMBER := 1.0;
553
554 BEGIN
555
556 if ( l_api_version <> p_api_version ) then
557 raise CSF_LF_VERSION_ERROR;
558 end if;
559
560 if ( p_init_msg_list = 'TRUE' ) then
561 null; /* FND_MSG_PUB.initialize; */
562 end if;
563
564 x_return_status := FND_API.G_RET_STS_SUCCESS;
565
566 EXCEPTION
567
568 when CSF_LF_VERSION_ERROR then
569 x_return_status := FND_API.G_RET_STS_ERROR;
570
571 END CSF_GeometrytoLocus;
572
573 -- Start of Comments
574 -- API Name : CSF_LocustoTimeZone
575 -- Type : Public
576 -- Pre-req :
577 -- Functiom : Converts a Locus to a time zone
578 -- Parameters :
579 -- IN
580 -- p_api_version IN NUMBER required
581 -- p_init_msg_list IN VARCHAR2 := NULL optional
582 -- x_return_status OUT VARCHAR2 :=
583 -- x_msg_count OUT NUMBER :=
584 -- x_msg_data OUT VARCHAR2 :=
585 -- p_point MDSYS.SDO_GEOMETRY := required
586 -- x_timezone OUT NUMBER
587 -- Version:
588 -- End Comments
589
590 PROCEDURE CSF_LocustoTimeZone
591 ( p_api_version IN NUMBER
592 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
593 , x_return_status OUT NOCOPY VARCHAR2
594 , x_msg_count OUT NOCOPY NUMBER
595 , x_msg_data OUT NOCOPY VARCHAR2
596 , p_locus IN MDSYS.SDO_GEOMETRY
597 , x_timezone OUT NOCOPY NUMBER
598 )
599 IS
600
601 l_api_name CONSTANT VARCHAR2(30) := 'CSF_LocustoTimeZone';
602 l_api_version CONSTANT NUMBER := 1.0;
603 l_result varchar2(1000);
604 TYPE REF_TIMEZONE IS REF CURSOR;
605 C_TIMEZONE REF_TIMEZONE;
606 sql_stmt_str VARCHAR2(2000);
607 l_data_set_name VARCHAR2(40);
608 BEGIN
609
610 if ( l_api_version <> p_api_version ) THEN
611 raise CSF_LF_VERSION_ERROR;
612 end if;
613
614 if ( p_init_msg_list = 'TRUE' ) THEN
615 x_msg_count := 0; /* FND_MSG_PUB.initialize; */
616 end if;
617
618 x_return_status := FND_API.G_RET_STS_SUCCESS;
619
620 CSF_LOCUS_PUB.VERIFY_LOCUS (
621 p_api_version => l_api_version
622 , x_return_status => x_return_status
623 , x_msg_count => x_msg_count
624 , x_msg_data => x_msg_data
625 , p_locus => p_locus
626 , x_result => l_result
627 );
628
629 if ( l_result = 'TRUE' ) then
630 /* select time_zone into x_timezone
631 from csf_lf_roadsegments
632 where roadsegment_id = p_locus.sdo_ordinates(6); */
633
634 l_data_set_name := fnd_profile.value('CSF_SPATIAL_DATASET_NAME');
635 IF (l_data_set_name = 'NONE' OR l_data_set_name IS NULL ) THEN
636 l_data_set_name := '';
637 END IF;
638 sql_stmt_str := 'SELECT time_zone into x_timezone
639 FROM csf_lf_roadsegments'|| l_data_set_name||'
640 WHERE roadsegment_id = '|| p_locus.sdo_ordinates(6);
641
642 OPEN C_TIMEZONE FOR sql_stmt_str;
643 FETCH C_TIMEZONE INTO x_timezone;
644 CLOSE C_TIMEZONE;
645
646 x_return_status := FND_API.G_RET_STS_SUCCESS;
647 else
648 x_return_status := FND_API.G_RET_STS_ERROR;
649 end if;
650
651 EXCEPTION
652
653 when NO_DATA_FOUND then
654 x_return_status := FND_API.G_RET_STS_ERROR;
655 x_msg_data := 'INVALID ROADSEGMENT_ID';
656
657 when OTHERS then
658 x_return_status := FND_API.G_RET_STS_ERROR;
659 x_msg_data := 'UNKNOWN ERROR';
660 END;
661
662 -- ----------------------------------------------------------------------------
663 -- private procedures
664 -- ----------------------------------------------------------------------------
665
666 PROCEDURE getSegmentLength
667 ( segmentGeometry IN MDSYS.SDO_ORDINATE_ARRAY
668 , segmentLength OUT NOCOPY NUMBER)
669 IS
670 prevPos POINT_COORD;
671 currPos POINT_COORD;
672 distanceFromLastShapePoint NUMBER:= 0;
673 numCoordinates NUMBER := 0;
674
675 BEGIN
676 segmentLength := 0;
677 /* Starting from the first shape point which we know is the reference node....*/
678 prevPos.Long := segmentGeometry(1);
679 prevPos.lat := segmentGeometry(2);
680 numCoordinates := segmentGeometry.count / 2;
681
682 /* ..take a walk along the shape points until we have walked a distance greater
683 than segmentLength * percentAlongSeg */
684 FOR J IN 1..numCoordinates - 1
685 LOOP
686 currPos.LONG := segmentGeometry(J*2 + 1);
690 END LOOP;
687 currPos.LAT := segmentGeometry(J*2 + 2);
688 segmentLength := segmentLength + GEODISTANCE(prevPos, currPos);
689 prevPos := currPos;
691 END getSegmentLength;
692
693 PROCEDURE getCoordOnSegment
694 ( segmentLength NUMBER
695 , segmentGeometry MDSYS.SDO_ORDINATE_ARRAY
696 , percentAlongSeg NUMBER
697 , coord OUT NOCOPY POINT_COORD )
698 IS
699 prevPos POINT_COORD;
700 currPos POINT_COORD;
701 lengthSoFar NUMBER := 0;
702 prevLengthSoFar NUMBER := 0;
703 vheading NUMBER;
704 distanceFromLastShapePoint NUMBER:= 0;
705 numCoordinates NUMBER := 0;
706
707 BEGIN
708 /* Starting from the fist shape point which we know is the reference node....*/
709 prevPos.long := segmentGeometry(1);
710 prevPos.lat := segmentGeometry(2);
711 numCoordinates := segmentGeometry.count / 2;
712
713 /* ..take a walk along the shape points until we have walked a distance greater than segmentLength * percentAlongSeg */
714 FOR J IN 1..numCoordinates - 1
715 LOOP
716 currPos.LONG := segmentGeometry(J*2 + 1);
717 currPos.LAT := segmentGeometry(J*2 + 2);
718 lengthSoFar := lengthSoFar + GEODISTANCE(prevPos, currPos);
719 IF lengthSoFar >= segmentLength * percentAlongSeg
720 THEN
721 /* distanceFromLastShapePoint is the distance from the last shape point to the position */
722 distanceFromLastShapePoint := segmentLength * percentAlongSeg - prevLengthSoFar;
723 vheading := HEADING(prevPos, currPos);
724
725 /* Now use the heading, previous shape point and distance from last shape point to determine coord point */
726 NEW_COORD( prevPos, distanceFromLastShapePoint, vheading, coord);
727 EXIT;
728 ELSE
729 prevPos := currPos;
730 prevLengthSoFar := lengthSoFar;
731 END IF;
732 END LOOP;
733 END getCoordOnSegment;
734
735
736 -- DETERMINE NEW COORD FROM START COORD, DISTANCE BETWEEN POINTS AND HEADING (RADIANS);
737 PROCEDURE NEW_COORD( POS1 IN POINT_COORD, DISTANCE IN NUMBER, HEAD NUMBER, POS2 OUT NOCOPY POINT_COORD) IS
738 BEGIN
739 POS2.LAT := RAD2DEG(((DISTANCE * SIN(HEAD)) / EARTH_RADIUS) + DEG2RAD(POS1.LAT));
740 POS2.LONG := RAD2DEG(((DISTANCE * COS(HEAD)) / EARTH_RADIUS) + DEG2RAD(POS1.LONG));
741 END NEW_COORD;
742 -- HEADING IN RADIANS WILL ALWAYS BE POSITIVE AND IS DEFINES AS RADIANS FROM NORTH;
743
744 -- COMPUTES HEADING ANGLE BETWEEN TWO POINTS IN RADIANS
745 FUNCTION HEADING(POS1 IN POINT_COORD,POS2 IN POINT_COORD) RETURN NUMBER IS
746 NORTH_DIST NUMBER;
747 EAST_DIST NUMBER;
748 HEAD NUMBER;
749 BEGIN
750 NORTH_DIST := (DEG2RAD(POS2.LAT) - DEG2RAD(POS1.LAT)) * EARTH_RADIUS;
751 EAST_DIST := (DEG2RAD(POS2.LONG) - DEG2RAD(POS1.LONG)) * COS(DEG2RAD(POS2.LAT)) * EARTH_RADIUS;
752 IF (NORTH_DIST = 0) AND (EAST_DIST = 0) THEN
753 HEAD := 0;
754 ELSE
755 HEAD := ATAN2(EAST_DIST,NORTH_DIST);
756 IF HEAD < 0 THEN
757 HEAD := HEAD + 2* PI;
758 END IF;
759 END IF;
760 RETURN HEAD;
761 END HEADING;
762
763 -- COMPUTES DISTANCE BETWEEN TWO COORD.
764 FUNCTION GEODISTANCE(POS1 IN POINT_COORD,POS2 IN POINT_COORD) RETURN NUMBER IS
765 NORTH_DIST NUMBER;
766 EAST_DIST NUMBER;
767 DISTANCE NUMBER;
768 BEGIN
769 NORTH_DIST := (DEG2RAD(POS2.LAT) - DEG2RAD(POS1.LAT)) * EARTH_RADIUS;
770 EAST_DIST := (DEG2RAD(POS2.LONG) - DEG2RAD(POS1.LONG)) * COS(DEG2RAD(POS2.LAT)) * EARTH_RADIUS;
771 DISTANCE := SQRT((NORTH_DIST * NORTH_DIST) + (EAST_DIST *EAST_DIST));
772 RETURN DISTANCE;
773 END GEODISTANCE;
774
775 -- CONVERTS DEGREES UNITS TO RADIANS
776 FUNCTION DEG2RAD(DEG NUMBER) RETURN NUMBER IS
777 RADIANS NUMBER;
778 BEGIN
779 RADIANS := DEG * PI/180.0;
780 RETURN RADIANS;
781 END DEG2RAD;
782
783 -- CONVERTS RADIANS TO DEGREES
784 FUNCTION RAD2DEG(DEG NUMBER) RETURN NUMBER IS
785 DEGREES NUMBER;
786 BEGIN
787 DEGREES := ( 180.0/PI) * DEG;
788 RETURN DEGREES;
789 END RAD2DEG;
790
791 END CSF_LF_PUB;