1 PACKAGE BODY csf_locus_pub AS
2 /* $Header: CSFPLCSB.pls 120.22.12020000.2 2012/07/23 05:10:33 srguntur ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'CSF_LOCUS_PUB';
5
6 -- message count must always equal ZERO or ONE.
7 -- cannot use messgage list because of pragma
8
9 PROCEDURE read_locus(
10 p_api_version IN NUMBER
11 , x_return_status OUT NOCOPY VARCHAR2
12 , x_msg_count OUT NOCOPY NUMBER
13 , x_msg_data OUT NOCOPY VARCHAR2
14 , p_locus IN MDSYS.SDO_GEOMETRY
15 , x_geom OUT NOCOPY MDSYS.SDO_GEOMETRY
16 , x_segid OUT NOCOPY NUMBER
17 , x_offset OUT NOCOPY NUMBER
18 , x_direction OUT NOCOPY NUMBER
19 ) AS
20 l_i NUMBER;
21 l_api_name CONSTANT VARCHAR2(30) := 'read_locus';
22 BEGIN
23 IF (p_locus IS NOT NULL)
24 AND (p_locus.sdo_gtype = 2001)
25 AND (p_locus.sdo_elem_info IS NOT NULL)
26 AND (p_locus.sdo_ordinates IS NOT NULL)
27 AND (p_locus.sdo_elem_info.COUNT = 6)
28 AND (p_locus.sdo_ordinates(p_locus.sdo_elem_info(4) + 1) = -9999) THEN
29
30 x_segid := p_locus.sdo_ordinates(p_locus.sdo_elem_info(4) + 2);
31 x_offset := p_locus.sdo_ordinates(p_locus.sdo_elem_info(4) + 3);
32 x_direction := p_locus.sdo_ordinates(p_locus.sdo_elem_info(4) + 4);
33 x_geom :=
34 MDSYS.SDO_GEOMETRY(
35 2001
36 , NULL
37 , MDSYS.sdo_point_type(p_locus.sdo_ordinates(1), p_locus.sdo_ordinates(2), NULL)
38 , MDSYS.sdo_elem_info_array(1, 1, 1)
39 , NULL
40 );
41 x_return_status := g_ret_locus_success;
42
43 ELSIF (p_locus IS NOT NULL)
44 AND (p_locus.sdo_gtype = 2001)
45 AND (p_locus.sdo_point IS NOT NULL)
46 AND (p_locus.sdo_elem_info IS NULL) THEN
47
48 x_segid := 9999;
49 x_offset := 9;
50 x_direction := 9;
51 x_geom :=
52 MDSYS.SDO_GEOMETRY(
53 2001
54 , NULL
55 , MDSYS.sdo_point_type(p_locus.sdo_point.x, p_locus.sdo_point.y, NULL)
56 , MDSYS.sdo_elem_info_array(1, 1, 1)
57 , NULL
58 );
59 x_return_status := g_ret_locus_success;
60 ELSE
61 x_segid := NULL;
62 x_offset := NULL;
63 x_direction := NULL;
64 x_return_status := g_ret_locus_invalid_locus;
65 END IF;
66 END read_locus;
67
68 PROCEDURE write_locus(
69 p_api_version IN NUMBER
70 , x_return_status OUT NOCOPY VARCHAR2
71 , x_msg_count OUT NOCOPY NUMBER
72 , x_msg_data OUT NOCOPY VARCHAR2
73 , p_geom IN MDSYS.SDO_GEOMETRY
74 , p_segid IN NUMBER
75 , p_offset IN NUMBER
76 , p_direction IN NUMBER
77 , p_accuracyFactor IN NUMBER
78 , x_locus OUT NOCOPY MDSYS.SDO_GEOMETRY
79 ) AS
80 l_ordinates MDSYS.sdo_ordinate_array;
81 i NUMBER;
82 l_api_name CONSTANT VARCHAR2(30) := 'WRITE_LOCUS';
83 BEGIN
84 IF (p_geom.sdo_gtype <> 2001)
85 OR (p_geom IS NULL)
86 OR ((p_geom.sdo_elem_info.COUNT < 2) AND(p_geom.sdo_point IS NULL)) THEN
87 x_locus := NULL;
88 x_return_status := g_ret_locus_invalid_geometry;
89 RETURN;
90 END IF;
91
92 IF (p_geom.sdo_point IS NULL) THEN
93 l_ordinates := p_geom.sdo_ordinates;
94 ELSE
95 l_ordinates := MDSYS.sdo_ordinate_array(p_geom.sdo_point.x, p_geom.sdo_point.y);
96
97 IF (p_geom.sdo_point.z IS NOT NULL) THEN
98 l_ordinates.EXTEND;
99 l_ordinates(3) := p_geom.sdo_point.z;
100 END IF;
101 END IF;
102
103 x_locus :=
104 MDSYS.SDO_GEOMETRY(
105 2001
106 , NULL
107 , NULL
108 , MDSYS.sdo_elem_info_array(1, 1, 1, l_ordinates.COUNT + 1, 0, 5)
109 , l_ordinates
110 );
111 x_locus.sdo_ordinates.EXTEND(5);
112 x_locus.sdo_ordinates(x_locus.sdo_elem_info(4)) := p_accuracyFactor;
113 x_locus.sdo_ordinates(x_locus.sdo_elem_info(4) + 1) := -9999;
114 x_locus.sdo_ordinates(x_locus.sdo_elem_info(4) + 2) := p_segid;
115 x_locus.sdo_ordinates(x_locus.sdo_elem_info(4) + 3) := p_offset;
116 x_locus.sdo_ordinates(x_locus.sdo_elem_info(4) + 4) := p_direction;
117 x_return_status := g_ret_locus_success;
118 END write_locus;
119
120 PROCEDURE verify_locus_local(
121 p_api_version IN NUMBER
122 , x_return_status OUT NOCOPY VARCHAR2
123 , x_msg_count OUT NOCOPY NUMBER
124 , x_msg_data OUT NOCOPY VARCHAR2
125 , p_locus IN MDSYS.SDO_GEOMETRY
126 , x_result OUT NOCOPY VARCHAR2
127 , x_geo_type OUT NOCOPY VARCHAR2
128 ) AS
129 l_api_name CONSTANT VARCHAR2(30) := 'VERIFY_LOCUS';
130 BEGIN
131 x_result := 'FALSE';
132 x_return_status := 'S';
133
134 IF p_locus IS NULL THEN
135 RETURN;
136 END IF;
137
138 IF p_locus.sdo_gtype <> 2001 THEN
139 RETURN;
140 END IF;
141
142 -- added: a null check
143 IF p_locus.sdo_elem_info IS NULL
144 OR p_locus.sdo_elem_info.COUNT <> 6
145 OR p_locus.sdo_ordinates IS NULL
146 OR p_locus.sdo_ordinates.COUNT < p_locus.sdo_elem_info(4) + 4
147 OR p_locus.sdo_ordinates(p_locus.sdo_elem_info(4) + 1) <> -9999 THEN
148
149 IF p_locus.sdo_point IS NULL
150 THEN
151 RETURN;
152 ELSE
153 x_result := 'TRUE';
154 x_geo_type := 'TCA';
155 END IF;
156 ELSE
157 x_result := 'TRUE';
158 x_geo_type := 'CSF';
159 END IF;
160 END verify_locus_local;
161
162 PROCEDURE verify_locus(
163 p_api_version IN NUMBER
164 , x_return_status OUT NOCOPY VARCHAR2
165 , x_msg_count OUT NOCOPY NUMBER
166 , x_msg_data OUT NOCOPY VARCHAR2
167 , p_locus IN MDSYS.SDO_GEOMETRY
168 , x_result OUT NOCOPY VARCHAR2
169 ) AS
170 l_geo_type VARCHAR2(6);
171 BEGIN
172
173 verify_locus_local(
174 p_api_version => p_api_version
175 , p_locus => p_locus
176 , x_msg_count => x_msg_count
177 , x_msg_data => x_msg_data
178 , x_result => x_result
179 , x_geo_type => l_geo_type
180 , x_return_status => x_return_status
181 );
182
183 END;
184
185 FUNCTION get_locus_segmentid(
186 p_api_version IN NUMBER
187 , x_return_status OUT NOCOPY VARCHAR2
188 , x_msg_count OUT NOCOPY NUMBER
189 , x_msg_data OUT NOCOPY VARCHAR2
190 , p_geom IN MDSYS.SDO_GEOMETRY
191 )
192 RETURN NUMBER IS
193 RESULT VARCHAR2(6);
194 GEO_TYPE VARCHAR2(6);
195 BEGIN
196 x_return_status := fnd_api.g_ret_sts_success;
197
198 verify_locus_local(
199 p_api_version => 1
200 , p_locus => p_geom
201 , x_msg_count => x_msg_count
202 , x_msg_data => x_msg_data
203 , x_result => RESULT
204 , x_geo_type => GEO_TYPE
205 , x_return_status => x_return_status
206 );
207
211 ELSIF (GEO_TYPE = 'TCA') THEN
208 IF (RESULT='TRUE') THEN
209 IF (GEO_TYPE = 'CSF') THEN
210 RETURN p_geom.sdo_ordinates(p_geom.sdo_elem_info(4) + 2);
212 RETURN 9999;
213 END IF;
214 ELSE
215 RETURN -9999;
216 END IF;
217 END;
218
219 FUNCTION get_locus_spot(
220 p_api_version IN NUMBER
221 , x_return_status OUT NOCOPY VARCHAR2
222 , x_msg_count OUT NOCOPY NUMBER
223 , x_msg_data OUT NOCOPY VARCHAR2
224 , p_geom IN MDSYS.SDO_GEOMETRY
225 )
226 RETURN NUMBER IS
227 RESULT VARCHAR2(6);
228 GEO_TYPE VARCHAR2(6);
229 BEGIN
230 verify_locus_local(
231 p_api_version => 1
232 , p_locus => p_geom
233 , x_msg_count => x_msg_count
234 , x_msg_data => x_msg_data
235 , x_result => RESULT
236 , x_geo_type => GEO_TYPE
237 , x_return_status => x_return_status
238 );
239
240 IF (RESULT='TRUE') THEN
241 IF (GEO_TYPE = 'CSF') THEN
242 RETURN p_geom.sdo_ordinates(p_geom.sdo_elem_info(4) + 3);
243 ELSIF (GEO_TYPE = 'TCA') THEN
244 RETURN 9;
245 END IF;
246 ELSE
247 RETURN -9999;
248 END IF;
249 END;
250
251 FUNCTION get_locus_side(
252 p_api_version IN NUMBER
253 , x_return_status OUT NOCOPY VARCHAR2
254 , x_msg_count OUT NOCOPY NUMBER
255 , x_msg_data OUT NOCOPY VARCHAR2
256 , p_geom IN MDSYS.SDO_GEOMETRY
257 )
258 RETURN NUMBER IS
259 RESULT VARCHAR2(6);
260 GEO_TYPE VARCHAR2(6);
261 BEGIN
262 verify_locus_local(
263 p_api_version => 1
264 , p_locus => p_geom
265 , x_msg_count => x_msg_count
266 , x_msg_data => x_msg_data
267 , x_result => RESULT
268 , x_geo_type => GEO_TYPE
269 , x_return_status => x_return_status
270 );
271
272 IF (RESULT ='TRUE') THEN
273 IF (GEO_TYPE = 'CSF') THEN
274 RETURN p_geom.sdo_ordinates(p_geom.sdo_elem_info(4) + 4);
275 ELSIF (GEO_TYPE = 'TCA') THEN
276 RETURN 9;
277 END IF;
278 ELSE
279 RETURN -9999;
280 END IF;
281 END;
282
283 FUNCTION get_locus_lat(
284 p_api_version IN NUMBER
285 , x_return_status OUT NOCOPY VARCHAR2
286 , x_msg_count OUT NOCOPY NUMBER
287 , x_msg_data OUT NOCOPY VARCHAR2
288 , p_geom IN MDSYS.SDO_GEOMETRY
289 )
290 RETURN NUMBER IS
291 RESULT VARCHAR2(6);
292 GEO_TYPE VARCHAR2(6);
293 BEGIN
294 verify_locus_local(
295 p_api_version => 1
296 , p_locus => p_geom
297 , x_msg_count => x_msg_count
298 , x_msg_data => x_msg_data
299 , x_result => RESULT
300 , x_geo_type => GEO_TYPE
301 , x_return_status => x_return_status
302 );
303
304 IF (RESULT='TRUE') THEN
305 IF (GEO_TYPE = 'CSF') THEN
306 RETURN p_geom.sdo_ordinates(2);
307 ELSIF (GEO_TYPE = 'TCA') THEN
308 RETURN p_geom.sdo_point.y;
309 END IF;
310 ELSE
311 RETURN -9999;
312 END IF;
313 END;
314
315 FUNCTION get_locus_lon(
316 p_api_version IN NUMBER
317 , x_return_status OUT NOCOPY VARCHAR2
318 , x_msg_count OUT NOCOPY NUMBER
319 , x_msg_data OUT NOCOPY VARCHAR2
320 , p_geom IN MDSYS.SDO_GEOMETRY
321 )
322 RETURN NUMBER IS
323 RESULT VARCHAR2(6);
324 GEO_TYPE VARCHAR2(6);
325 BEGIN
326 verify_locus_local(
327 p_api_version => 1
328 , p_locus => p_geom
329 , x_msg_count => x_msg_count
330 , x_msg_data => x_msg_data
331 , x_result => RESULT
332 , x_geo_type => GEO_TYPE
333 , x_return_status => x_return_status
334 );
335
336 IF (RESULT='TRUE') THEN
337 IF (GEO_TYPE = 'CSF') THEN
338 RETURN p_geom.sdo_ordinates(1);
339 ELSIF (GEO_TYPE = 'TCA') THEN
340 RETURN p_geom.sdo_point.x;
341 END IF;
342 ELSE
343 RETURN -9999;
344 END IF;
345 END;
346
347 FUNCTION get_locus_srid ( p_api_version in number,
348 p_geom in mdsys.sdo_geometry,
349 x_msg_count out nocopy number,
350 x_msg_data out nocopy varchar2,
351 x_return_status out nocopy varchar2) return NUMBER is
352 result VARCHAR2(6);
353 geo_type VARCHAR2(6);
354 BEGIN
355 verify_locus_local(
356 p_api_version => 1,
357 p_locus => p_geom,
358 x_msg_count => x_msg_count,
359 x_msg_data => x_msg_data,
360 x_result => result,
361 x_geo_type => geo_type,
365 return p_geom.sdo_srid;
362 x_return_status => x_return_status);
363
364 if (result = 'TRUE') then
366 else
367 return -9999;
368 end if;
369 END;
370
371 FUNCTION should_call_lf(p_geom IN MDSYS.SDO_GEOMETRY)
372 RETURN VARCHAR2 AS
373 BEGIN
374 IF p_geom IS NULL THEN
375 RETURN fnd_api.g_true;
376 ELSE
377 RETURN fnd_api.g_false;
378 END IF;
379 END should_call_lf;
380
381 FUNCTION get_locus_segmentid(p_geom IN MDSYS.SDO_GEOMETRY)
382 RETURN NUMBER AS
383 l_return_status VARCHAR2(1);
384 l_msg_count NUMBER;
385 l_msg_data VARCHAR2(1000);
386 BEGIN
387 RETURN get_locus_segmentid(
388 p_api_version => 1
389 , p_geom => p_geom
390 , x_msg_count => l_msg_count
391 , x_msg_data => l_msg_data
392 , x_return_status => l_return_status
393 );
394 END;
395
396 FUNCTION get_locus_side(p_geom IN MDSYS.SDO_GEOMETRY)
397 RETURN NUMBER AS
398 l_return_status VARCHAR2(1);
399 l_msg_count NUMBER;
400 l_msg_data VARCHAR2(1000);
401 BEGIN
402 RETURN get_locus_side(
403 p_api_version => 1
404 , p_geom => p_geom
405 , x_msg_count => l_msg_count
406 , x_msg_data => l_msg_data
407 , x_return_status => l_return_status
408 );
409 END;
410
411 FUNCTION get_locus_spot(p_geom IN MDSYS.SDO_GEOMETRY)
412 RETURN NUMBER AS
413 l_return_status VARCHAR2(1);
414 l_msg_count NUMBER;
415 l_msg_data VARCHAR2(1000);
416 BEGIN
417 RETURN get_locus_spot(
418 p_api_version => 1
419 , p_geom => p_geom
420 , x_msg_count => l_msg_count
421 , x_msg_data => l_msg_data
422 , x_return_status => l_return_status
423 );
424 END;
425
426 FUNCTION get_locus_lat(p_geom IN MDSYS.SDO_GEOMETRY)
427 RETURN NUMBER AS
428 l_return_status VARCHAR2(1);
429 l_msg_count NUMBER;
430 l_msg_data VARCHAR2(1000);
431 BEGIN
432 RETURN get_locus_lat(
433 p_api_version => 1
434 , p_geom => p_geom
435 , x_msg_count => l_msg_count
436 , x_msg_data => l_msg_data
437 , x_return_status => l_return_status
438 );
439 END;
440
441 FUNCTION get_locus_lon(p_geom IN MDSYS.SDO_GEOMETRY)
442 RETURN NUMBER AS
443 l_return_status VARCHAR2(1);
444 l_msg_count NUMBER;
445 l_msg_data VARCHAR2(1000);
446 BEGIN
447 RETURN get_locus_lon(
448 p_api_version => 1
449 , p_geom => p_geom
450 , x_msg_count => l_msg_count
451 , x_msg_data => l_msg_data
452 , x_return_status => l_return_status
453 );
454 END;
455
456
457 FUNCTION get_locus_srid(p_geom IN MDSYS.SDO_GEOMETRY)
458 RETURN NUMBER AS
459 l_return_status VARCHAR2(1);
460 l_msg_count NUMBER;
461 l_msg_data VARCHAR2(1000);
462 l_srid NUMBER;
463 BEGIN
464 l_srid := -9999;
465 IF p_geom IS NOT NULL THEN
466 l_srid := get_locus_srid(p_api_version => 1
467 , p_geom => p_geom
468 , x_msg_count => l_msg_count
469 , x_msg_data => l_msg_data
470 , x_return_status => l_return_status
474
471 );
472
473 END IF;
475 RETURN l_srid;
476 END;
477
478
479 FUNCTION get_geometry (p_geometry MDSYS.SDO_GEOMETRY, p_item VARCHAR2, p_index NUMBER DEFAULT NULL)
480 RETURN NUMBER
481 AS
482 -- Bug 1633731
483 -- This function is called with p_item = 'SDO_POINT' and p_index = 1 (X)
484 -- or p_index = 2 (Y) to determine the coordinates of a point location
485 -- such as a customer, resource or task.
486 -- If the SDO_POINT item is null it is assumed that the geometry is a
487 -- valid locus (see package CSF_LOCUS_PUB). For performance reasons this
488 -- will not be checked. The X and Y values can then be obtained from the
489 -- first two elements of the SDO_ORDINATES array.
490 BEGIN
491 IF p_geometry IS NULL
492 THEN
493 RETURN NULL;
494 END IF;
495
496 IF p_item = 'SDO_GTYPE'
497 THEN
498 RETURN p_geometry.sdo_gtype;
499 ELSIF p_item = 'SDO_SRID'
500 THEN
501 RETURN p_geometry.sdo_srid;
502 ELSE
503 -- for all other fields the index has to be defined
504 IF p_index IS NULL
505 THEN
506 RETURN NULL;
507 END IF;
508
509 IF p_item = 'SDO_POINT'
510 THEN
511 IF p_geometry.sdo_point IS NULL AND p_index IN (1, 2)
512 THEN
513 RETURN get_geometry (p_geometry, 'SDO_ORDINATES', p_index);
514 END IF;
515
516 IF p_index = 1
517 THEN
518 RETURN p_geometry.sdo_point.x;
519 ELSIF p_index = 2
520 THEN
521 RETURN p_geometry.sdo_point.y;
522 ELSIF p_index = 3
523 THEN
524 RETURN p_geometry.sdo_point.z;
525 END IF;
526 ELSIF p_item = 'SDO_ELEM_INFO'
527 THEN
528 IF p_geometry.sdo_elem_info IS NOT NULL
529 THEN
530 IF p_geometry.sdo_elem_info.COUNT >= p_index
531 THEN
532 RETURN p_geometry.sdo_elem_info (p_index);
533 END IF;
534 END IF;
535 ELSIF p_item = 'SDO_ORDINATES'
536 THEN
537 IF p_geometry.sdo_ordinates IS NOT NULL
538 THEN
539 IF p_geometry.sdo_ordinates.COUNT >= p_index
540 THEN
541 RETURN p_geometry.sdo_ordinates (p_index);
542 END IF;
543 END IF;
544 END IF;
545 END IF;
546
547 -- in all other cases return null
548 RETURN NULL;
549 END get_geometry;
550
551 /* FUNCTION get_serv_area_coordinates (p_country_id NUMBER, p_index NUMBER)
552 RETURN NUMBER
553 AS
554 l_geom MDSYS.SDO_GEOMETRY;
555 l_coord NUMBER := NULL;
556
557 CURSOR c1
558 IS
559 SELECT SDO_GEOM.SDO_MBR(default_display_center)
560 FROM csf_sdm_ctry_profiles
561 WHERE country_profile_id = p_country_id;
562 BEGIN
563 OPEN c1;
564 FETCH c1 INTO l_geom;
565 IF c1%FOUND
566 THEN
567 l_coord := get_geometry (l_geom, 'SDO_ORDINATES', p_index);
568 END IF;
569 CLOSE c1;
570
571 RETURN l_coord;
572 END get_serv_area_coordinates; */
573
574 FUNCTION get_serv_area_coordinates (p_country_id NUMBER, p_index NUMBER)
575 RETURN NUMBER
576 AS
577 l_geom MDSYS.SDO_GEOMETRY;
578 l_coord NUMBER := NULL;
579 TYPE REF_DISPLAY IS REF CURSOR;
580 c1 REF_DISPLAY;
581 sql_stmt_str VARCHAR2(2000);
582 l_data_set_name VARCHAR2(40);
583 BEGIN
584
585 l_data_set_name := fnd_profile.value('CSF_SPATIAL_MULTIDATASET_ENABLED');
586 IF (l_data_set_name = 'N' OR l_data_set_name IS NULL) THEN
587 l_data_set_name := ' ';
588 ELSE
589 l_data_set_name := fnd_profile.VALUE('CSF_EMAP_DATASET_NAME') ;
590 IF (l_data_set_name = 'NONE' OR l_data_set_name IS NULL) THEN
591 l_data_set_name := ' ';
592 END IF;
593 END IF;
594
595 sql_stmt_str := 'SELECT default_display_center
596 FROM csf_sdm_ctry_profiles'||l_data_set_name ||'
597 WHERE country_profile_id = '|| p_country_id;
598
599 OPEN c1 FOR sql_stmt_str;
600 FETCH c1 INTO l_geom;
601 IF c1%FOUND THEN
602 l_coord := get_geometry (l_geom, 'SDO_ORDINATES', p_index);
603 END IF;
604 CLOSE c1;
605
606 RETURN l_coord;
607 END get_serv_area_coordinates;
608
609 FUNCTION get_serv_area_coordinates (p_country_id NUMBER,p_dataset VARCHAR2, p_index NUMBER)
610 RETURN NUMBER
611 AS
612 l_geom MDSYS.SDO_GEOMETRY;
613 l_coord NUMBER := NULL;
614 TYPE REF_DISPLAY IS REF CURSOR;
615 c1 REF_DISPLAY;
616 sql_stmt_str VARCHAR2(2000);
617 l_data_set_name VARCHAR2(40);
618 BEGIN
619
620 l_data_set_name := fnd_profile.value('CSF_SPATIAL_MULTIDATASET_ENABLED');
621 IF (l_data_set_name = 'N' OR l_data_set_name IS NULL) THEN
622 l_data_set_name := ' ';
623 ELSE
624 l_data_set_name := p_dataset;
625 IF (l_data_set_name = 'NONE' OR l_data_set_name IS NULL) THEN
626 l_data_set_name := ' ';
627 END IF;
628 END IF;
629
630 sql_stmt_str := 'SELECT default_display_center
631 FROM csf_sdm_ctry_profiles'||l_data_set_name ||'
635 FETCH c1 INTO l_geom;
632 WHERE country_profile_id = '|| p_country_id;
633
634 OPEN c1 FOR sql_stmt_str;
636 IF c1%FOUND THEN
637 l_coord := get_geometry (l_geom, 'SDO_ORDINATES', p_index);
638 END IF;
639 CLOSE c1;
640
641 RETURN l_coord;
642 END get_serv_area_coordinates;
643
644 /**
645 * Returns the Geometry as a String corresponding to the list of Segment IDs
646 * given
647 *
648 * @param p_segment_id_tbl Table of Segment IDs
649 * @param p_sampling_level Whats the Sampling Rate to be used on the Geometry
650 */
651 FUNCTION get_geometry_tbl(
652 p_segment_id_tbl jtf_number_table
653 , p_sampling_level VARCHAR2 DEFAULT NULL
654 )
655 RETURN jtf_varchar2_table_2000 IS
656
657 TYPE geometry_tbl_type IS TABLE OF MDSYS.SDO_GEOMETRY;
658 l_geometry_tbl geometry_tbl_type;
659 l_geometry_str_tbl jtf_varchar2_table_2000;
660 j PLS_INTEGER;
661
662 CURSOR c_geometry IS
663 SELECT /*+ cardinality(l, 1) */
664 roadsegment_geometry
665 FROM csf_lf_roadsegments
666 , TABLE (CAST (p_segment_id_tbl AS jtf_number_table )) l
667 WHERE roadsegment_id = l.COLUMN_VALUE;
668
669 FUNCTION get_coord(p_tbl_index NUMBER, p_coord_index NUMBER) RETURN NUMBER IS
670 BEGIN
671 RETURN ROUND(l_geometry_tbl(p_tbl_index).sdo_ordinates(p_coord_index), 4);
672 END get_coord;
673 BEGIN
674 OPEN c_geometry;
675 FETCH c_geometry BULK COLLECT INTO l_geometry_tbl;
676 CLOSE c_geometry;
677
678 l_geometry_str_tbl := jtf_varchar2_table_2000();
679
680 FOR i IN 1..l_geometry_tbl.COUNT LOOP
681 l_geometry_str_tbl.extend(1);
682 IF l_geometry_tbl(i) IS NULL OR l_geometry_tbl(i).sdo_ordinates IS NULL THEN
683 l_geometry_str_tbl(i) := '';
684 ELSE
685 IF p_sampling_level IS NULL OR p_sampling_level = 'Y' THEN
686 -- Number of Coordinate Pairs
687 j := l_geometry_tbl(i).sdo_ordinates.COUNT/2;
688 --
689 -- We have just one Coordinate Pair. Return it as it is
690 IF j = 1 THEN
691 l_geometry_str_tbl(i) := get_coord(i, 2) || ',' || get_coord(i, 1);
692 ELSE
693 j := ROUND(j / 2); -- Center Coordinate Pair
694 l_geometry_str_tbl(i) := get_coord(i, 2*j) || ',' || get_coord(i, 2*j-1);
695 END IF;
696 ELSE
697 l_geometry_str_tbl(i) := get_coord(i, 2) || ',' || get_coord(i, 1);
698 FOR j IN 2 .. l_geometry_tbl(i).sdo_ordinates.COUNT / 2 LOOP
699 l_geometry_str_tbl(i) := l_geometry_str_tbl(i) || ',' || get_coord(i, 2*j) || ',' || get_coord(i, 2*j-1);
700 END LOOP;
701 END IF;
702 END IF;
703 END LOOP;
704
705 RETURN l_geometry_str_tbl;
706 END get_geometry_tbl;
707
708
709 /**
710 * Computes the Geometry of the Route given as the Segment IDs
711 * Table and then saves the Geometry of the Route in
712 * CSF_TDS_ROUTE_CACHE to be used in future computations.
713 */
714 PROCEDURE compute_and_save_route(
715 p_api_version IN NUMBER
716 , p_init_msg_list IN VARCHAR2
717 , p_commit IN VARCHAR2
718 , x_return_status OUT NOCOPY VARCHAR2
719 , x_msg_count OUT NOCOPY NUMBER
720 , x_msg_data OUT NOCOPY VARCHAR2
721 , p_segment_id_tbl IN jtf_number_table
722 , p_start_side IN NUMBER
723 , p_start_offset IN NUMBER
724 , p_end_side IN NUMBER
725 , p_end_offset IN NUMBER
726 , p_tds_calc_type IN NUMBER
727 , p_travel_time IN NUMBER
728 , p_travel_distance IN NUMBER
729 ) IS
730 l_api_version CONSTANT NUMBER := 1.0;
731 l_api_name CONSTANT VARCHAR2(30) := 'COMPUTE_AND_SAVE_ROUTE';
732
733 l_geometry_tbl jtf_varchar2_table_2000;
734 i PLS_INTEGER;
735 l_route clob;
736
737 l_from_segment_id NUMBER;
738 l_to_segment_id NUMBER;
739 BEGIN
740 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
741 RAISE fnd_api.g_exc_unexpected_error;
742 END IF;
743
744 -- Initialize message list if p_init_msg_list is set to TRUE
745 IF fnd_api.to_boolean (p_init_msg_list) THEN
746 fnd_msg_pub.initialize;
747 END IF;
748
749 -- Initialize API return status to success
750 x_return_status := fnd_api.g_ret_sts_success;
751
752 l_from_segment_id := p_segment_id_tbl(p_segment_id_tbl.FIRST);
753 l_to_segment_id := p_segment_id_tbl(p_segment_id_tbl.LAST);
754
755 INSERT INTO CSF_TDS_ROUTE_CACHE (
756 ROUTE_CACHE_ID
757 , SEGMENT_FROM
758 , SIDE_FROM
759 , SPOT_FROM
760 , SEGMENT_TO
761 , SIDE_TO
762 , SPOT_TO
763 , RESULTTIME
764 , RESULTDISTANCE
765 , ROUTE
766 , DATETIME
767 , HITCOUNT
768 )
769 VALUES (
770 CSF_TDS_ROUTE_CACHE_S1.NEXTVAL
771 , l_from_segment_id
772 , p_start_side
773 , p_start_offset
774 , l_to_segment_id
775 , p_end_side
776 , p_end_offset
777 , p_travel_time
778 , p_travel_distance
779 , empty_clob
780 , SYSDATE
781 , 1
782 )
783 RETURNING route INTO l_route;
784
788 dbms_lob.OPEN(l_route, dbms_lob.lob_readwrite);
785 l_geometry_tbl := get_geometry_tbl(p_segment_id_tbl);
786
787 -- Open the CLOB
789
790 -- Read from Geometry Table and write to the CLOB
791 i := l_geometry_tbl.FIRST;
792 WHILE i IS NOT NULL LOOP
793 dbms_lob.writeappend(l_route, LENGTH(l_geometry_tbl(i)) + 1, l_geometry_tbl(i) || ',');
794 i := l_geometry_tbl.NEXT(i);
795 END LOOP;
796
797 i := 1;
798 IF dbms_lob.getlength(l_route) > 0 THEN
799 dbms_lob.erase(l_route, i, dbms_lob.getlength(l_route));
800 END IF;
801
802 -- Close the CLOB
803 dbms_lob.CLOSE(l_route);
804
805 IF fnd_api.to_boolean(p_commit) THEN
806 COMMIT;
807 END IF;
808 EXCEPTION
809 WHEN OTHERS THEN
810 x_return_status := fnd_api.g_ret_sts_unexp_error;
811 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
812 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
813 END IF;
814 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
815 END compute_and_save_route;
816
817 /**
818 * For each Location given in the Location Table, the Location Record
819 * in HZ_LOCATIONS will be updated with the Geometry containing the
820 * Latitude and Longitude as given by the corresponding PLSQL Tables.
821 */
822 PROCEDURE compute_and_save_locuses(
823 p_api_version IN NUMBER
824 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
825 , p_commit IN VARCHAR2 DEFAULT NULL
826 , x_return_status OUT NOCOPY VARCHAR2
827 , x_msg_count OUT NOCOPY NUMBER
828 , x_msg_data OUT NOCOPY VARCHAR2
829 , p_srid IN NUMBER
830 , p_location_id_tbl IN jtf_number_table
831 , p_latitude_tbl IN jtf_number_table
832 , p_longitude_tbl IN jtf_number_table
833 ) IS
834 l_api_version CONSTANT NUMBER := 1.0;
835 l_api_name CONSTANT VARCHAR2(30) := 'COMPUTE_AND_SAVE_LOCUSES';
836
837 l_srid NUMBER;
838 BEGIN
839 SAVEPOINT csf_save_locuses;
840
841 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
842 RAISE fnd_api.g_exc_unexpected_error;
843 END IF;
844
845 -- Initialize message list if p_init_msg_list is set to TRUE
846 IF fnd_api.to_boolean (p_init_msg_list) THEN
847 fnd_msg_pub.initialize;
848 END IF;
849
850 -- Initialize API return status to success
851 x_return_status := fnd_api.g_ret_sts_success;
852
853 l_srid := NVL(p_srid, 8307);
854
855 FORALL i IN 1..p_location_id_tbl.COUNT
856 UPDATE hz_locations
857 SET geometry = mdsys.sdo_geometry(
858 2001
859 , l_srid
860 , mdsys.sdo_point_type( p_longitude_tbl(i), p_latitude_tbl(i), 0)
861 , mdsys.sdo_elem_info_array(1,1,1)
862 , mdsys.sdo_ordinate_array( p_longitude_tbl(i), p_latitude_tbl(i) )
863 )
864 WHERE location_id = p_location_id_tbl(i);
865
866 IF fnd_api.to_boolean(p_commit) THEN
867 COMMIT;
868 END IF;
869 EXCEPTION
870 WHEN OTHERS THEN
871 ROLLBACK TO csf_save_locuses;
872 x_return_status := fnd_api.g_ret_sts_unexp_error;
873 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
874 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
875 END IF;
876 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
877 END compute_and_save_locuses;
878
879 /**
880 * Returns the Given Geometry in String Representation with each attribute
881 * separated by @. The sequence of the attributes are Longitude, Latitude,
882 * Segment Id, Offset and Side.
883 */
884 FUNCTION get_locus_string(p_geom IN MDSYS.SDO_GEOMETRY, p_soft_validation VARCHAR2)
885 RETURN VARCHAR2 AS
886 l_return_status VARCHAR2(1);
887 l_msg_count NUMBER;
888 l_msg_data VARCHAR2(1000);
889
890 l_result VARCHAR2(6);
891 l_geometry mdsys.sdo_geometry;
892 l_locus_string VARCHAR2(200);
893 l_geo_type VARCHAR2(6);
894 BEGIN
895 IF (p_soft_validation = fnd_api.g_true) THEN
896 IF p_geom IS NULL
897 OR p_geom.sdo_gtype <> 2001
898 OR p_geom.sdo_elem_info IS NULL
899 OR p_geom.sdo_ordinates IS NULL
900 THEN
901 l_result := 'FALSE';
902 ELSE
903 l_result := 'TRUE';
904 END IF;
905 ELSE
906 verify_locus_local(
907 p_api_version => 1
908 , p_locus => p_geom
909 , x_msg_count => l_msg_count
910 , x_msg_data => l_msg_data
911 , x_result => l_result
912 , x_geo_type => l_geo_type
913 , x_return_status => l_return_status
914 );
915 END IF;
916
917 IF l_result <> 'TRUE' THEN
918 RETURN NULL;
919 END IF;
920
921 l_geometry := p_geom;
922 IF l_geometry.sdo_srid <> 8307 THEN
923 l_geometry := sdo_cs.transform(p_geom, 8307);
924 END IF;
925
926 IF p_geom.sdo_elem_info IS NOT NULL
927 AND p_geom.sdo_ordinates IS NOT NULL
928 THEN
929 l_locus_string := ROUND(p_geom.sdo_ordinates(1), 8)
930 || '@' || ROUND(p_geom.sdo_ordinates(2), 8);
931
935 THEN
932 IF p_geom.sdo_elem_info.COUNT = 6
933 AND p_geom.sdo_ordinates.COUNT >= p_geom.sdo_elem_info(4) + 4
934 AND p_geom.sdo_ordinates(p_geom.sdo_elem_info(4) + 1) = -9999
936 l_locus_string := l_locus_string
937 || '@' || p_geom.sdo_ordinates(p_geom.sdo_elem_info(4) + 2)
938 || '@' || p_geom.sdo_ordinates(p_geom.sdo_elem_info(4) + 3)
939 || '@' || p_geom.sdo_ordinates(p_geom.sdo_elem_info(4) + 4);
940 END IF;
941 ELSIF p_geom.sdo_point IS NOT NULL
942 THEN
943 l_locus_string := ROUND(p_geom.sdo_point.x, 8)
944 || '@' || ROUND(p_geom.sdo_point.y, 8)
945 || '@' || '9999'
946 || '@' || '9'
947 || '@' || '9';
948 END IF;
949
950 /* To convert as a float value when the Number Format is set as '10.000,00'*/
951 SELECT REPLACE(l_locus_string, ',', '.') INTO l_locus_string FROM dual;
952
953 RETURN l_locus_string;
954 EXCEPTION
955 WHEN OTHERS THEN
956 RETURN NULL;
957 END;
958
959 /**
960 * This API is for fetching street segment id based on the inputs passed as
961 * latitude ,longitude and country .
962 *
963 * @param p_latitude Latitude
964 * @param p_longitude Longitude
965 * @param p_country Country of the address
966 * @param x_segment_id Nearest Street Segment Id.
967 */
968 FUNCTION get_segment_id (
969 p_api_version IN NUMBER default 1.0
970 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
971 , p_latitude IN NUMBER
972 , p_longitude IN NUMBER
973 , p_country IN VARCHAR2
974 , x_segment_id OUT NOCOPY NUMBER
975 , x_msg_count OUT NOCOPY NUMBER
976 , x_msg_data OUT NOCOPY VARCHAR2
977 , x_return_status OUT NOCOPY VARCHAR2
978 )
979 RETURN NUMBER AS
980 l_api_name CONSTANT VARCHAR2(30) := 'csf_lf_get_segment_id';
981 l_api_version CONSTANT NUMBER := 1.0;
982 l_roadsegmentid NUMBER := -1;
983 l_tbl_sufx VARCHAR2(10);
984 l_dist NUMBER := -1;
985 l_sql_stmt VARCHAR2(2000);
986
987 TYPE ref_cursor_type IS REF CURSOR;
988
989 --Cursor to find out the nearest geometry with in specified range from road segment table in case if not exist in poi table
990 cursor_rdseg_dist_chk ref_cursor_type;
991 cursor_rdseg ref_cursor_type;
992
993
994 BEGIN
995
996 if ( p_init_msg_list = 'TRUE' ) then
997 x_msg_count := 0; /* FND_MSG_PUB.initialize; */
998 end if;
999
1000 x_return_status := FND_API.G_RET_STS_SUCCESS;
1001
1002 --
1003 -- Validate parameters
1004 --
1005
1006 if ( l_api_version <> p_api_version ) then
1007 x_msg_data := ':p_api_version:' || p_api_version ;
1008 raise CSF_LF_VERSION_ERROR;
1009 end if;
1010
1011 if ( p_latitude is NULL or p_latitude < -90 or p_latitude > 90 ) then
1012 x_msg_data := ':p_latitude:' || p_latitude ;
1013 raise CSF_LF_LATITUDE_NOT_SET_ERROR;
1014 end if;
1015
1016 if ( p_longitude is NULL or p_longitude < -180 or p_longitude > 180 ) then
1017 x_msg_data := ':p_longitude:' || p_longitude ;
1018 raise CSF_LF_LONGITUDE_NOT_SET_ERROR;
1019 end if;
1020
1021 if ( p_country is NULL or p_country = '' ) then
1022 x_msg_data := ':p_country:' || p_country ;
1023 raise CSF_LF_COUNTRY_NOT_SET_ERROR;
1024 end if;
1025
1026 --Initialize message count and mssage data. we will use var x_msg_data to store info which can be used for debug purpose
1027 x_msg_count := 0;
1028 x_msg_data := 'Success';
1029
1030 --Fetch country specific data set to support muti dataset in single instance
1031 select spatial_dataset into l_tbl_sufx from CSF_SPATIAL_CTRY_MAPPINGS where spatial_country_name = p_country or spatial_country_code = p_country;
1032
1033 l_sql_stmt := 'SELECT /*+ INDEX(r CSF_LF_RDSEGS_N2) */ ROADSEGMENT_ID, SDO_NN_DISTANCE(1) dist
1034 FROM csf_lf_roadsegments' || l_tbl_sufx || ' r
1035 WHERE SDO_NN(r.ROADSEGMENT_GEOMETRY, SDO_GEOMETRY(2002,8307,null,SDO_ELEM_INFO_ARRAY(1,2,1),
1036 SDO_ORDINATE_ARRAY(:1, :2, :3, :4)), ''sdo_num_res=1'', 1) = ''TRUE'' ORDER BY dist';
1037
1038 OPEN cursor_rdseg for l_sql_stmt USING p_longitude,p_latitude,p_longitude,p_latitude;
1039 LOOP
1040 FETCH cursor_rdseg INTO l_roadsegmentid, l_dist; -- fetch data into local variables
1041 EXIT WHEN cursor_rdseg%NOTFOUND;
1042 END LOOP;
1043 CLOSE cursor_rdseg;
1044
1045 x_segment_id := l_roadsegmentid;
1046 x_msg_count := 1;
1047 x_msg_data := 'roadsegmentid:' || l_roadsegmentid || ':distance:' || l_dist;
1048
1049 if (l_dist = -1) THEN
1050 x_return_status := FND_API.G_RET_STS_ERROR;
1051 END if;
1052
1053 RETURN x_segment_id;
1054
1055 END;
1056
1057 FUNCTION is_manual_geometry(p_geometry IN MDSYS.SDO_GEOMETRY)
1058 RETURN BOOLEAN IS
1059 l_segment_id NUMBER := csf_locus_pub.get_locus_segmentid(p_geometry);
1060 l_locus_lat NUMBER := csf_locus_pub.get_locus_lat(p_geometry);
1061 l_locus_lon NUMBER := csf_locus_pub.get_locus_lon(p_geometry);
1062 BEGIN
1063 IF p_geometry IS NOT NULL THEN
1064 IF l_segment_id = 1 AND l_locus_lat <> -9999 AND l_locus_lon <> -9999
1065 THEN
1066 RETURN TRUE;
1067 END IF;
1068 END IF;
1069 RETURN FALSE;
1070 END is_manual_geometry;
1071
1072 END csf_locus_pub;
[Home] [Help]