[Home] [Help]
PACKAGE BODY: APPS.CSF_LOCUS_PUB
Source
1 PACKAGE BODY csf_locus_pub AS
2 /* $Header: CSFPLCSB.pls 120.12.12010000.2 2008/09/25 09:13:57 vpalle 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 PROCEDURE read_locus(
9 p_api_version IN NUMBER
10 , x_return_status OUT NOCOPY VARCHAR2
11 , x_msg_count OUT NOCOPY NUMBER
12 , x_msg_data OUT NOCOPY VARCHAR2
13 , p_locus IN MDSYS.SDO_GEOMETRY
14 , x_geom OUT NOCOPY MDSYS.SDO_GEOMETRY
15 , x_segid OUT NOCOPY NUMBER
16 , x_offset OUT NOCOPY NUMBER
17 , x_direction OUT NOCOPY NUMBER
18 ) AS
19 l_i NUMBER;
20 l_api_name CONSTANT VARCHAR2(30) := 'read_locus';
21 BEGIN
22 IF (p_locus IS NULL)
23 OR (p_locus.sdo_gtype <> 2001)
24 OR (p_locus.sdo_elem_info IS NULL)
25 OR (p_locus.sdo_ordinates IS NULL)
26 OR (p_locus.sdo_elem_info.COUNT <> 6)
27 /*Commented for LF enhancement of forced accuracy
28 OR (p_locus.sdo_ordinates(p_locus.sdo_elem_info(4)) <> -9999)*/
29 OR (p_locus.sdo_ordinates(p_locus.sdo_elem_info(4) + 1) <> -9999) THEN
30 x_segid := NULL;
31 x_offset := NULL;
32 x_direction := NULL;
33 x_return_status := g_ret_locus_invalid_locus;
34 RETURN;
35 END IF;
36
37 x_segid := p_locus.sdo_ordinates(p_locus.sdo_elem_info(4) + 2);
38 x_offset := p_locus.sdo_ordinates(p_locus.sdo_elem_info(4) + 3);
39 x_direction := p_locus.sdo_ordinates(p_locus.sdo_elem_info(4) + 4);
40 x_geom :=
41 MDSYS.SDO_GEOMETRY(
42 2001
43 , NULL
44 , MDSYS.sdo_point_type(p_locus.sdo_ordinates(1), p_locus.sdo_ordinates(2), NULL)
45 , MDSYS.sdo_elem_info_array(1, 1, 1)
46 , NULL
47 );
48 x_return_status := g_ret_locus_success;
49 END read_locus;
50
51 PROCEDURE write_locus(
52 p_api_version IN NUMBER
53 , x_return_status OUT NOCOPY VARCHAR2
54 , x_msg_count OUT NOCOPY NUMBER
55 , x_msg_data OUT NOCOPY VARCHAR2
56 , p_geom IN MDSYS.SDO_GEOMETRY
57 , p_segid IN NUMBER
58 , p_offset IN NUMBER
59 , p_direction IN NUMBER
60 --added for LF enhancement of forced accuracy
61 ,p_accuracyFactor IN NUMBER
62 , x_locus OUT NOCOPY MDSYS.SDO_GEOMETRY
63 ) AS
64 l_ordinates MDSYS.sdo_ordinate_array;
65 i NUMBER;
66 l_api_name CONSTANT VARCHAR2(30) := 'WRITE_LOCUS';
67 BEGIN
68 IF (p_geom.sdo_gtype <> 2001)
69 OR (p_geom IS NULL)
70 OR ((p_geom.sdo_elem_info.COUNT < 2) AND(p_geom.sdo_point IS NULL)) THEN
71 x_locus := NULL;
72 x_return_status := g_ret_locus_invalid_geometry;
73 RETURN;
74 END IF;
75
76 IF (p_geom.sdo_point IS NULL) THEN
77 l_ordinates := p_geom.sdo_ordinates;
78 ELSE
79 l_ordinates := MDSYS.sdo_ordinate_array(p_geom.sdo_point.x, p_geom.sdo_point.y);
80
81 IF (p_geom.sdo_point.z IS NOT NULL) THEN
82 l_ordinates.EXTEND;
83 l_ordinates(3) := p_geom.sdo_point.z;
84 END IF;
85 END IF;
86
87 x_locus :=
88 MDSYS.SDO_GEOMETRY(
89 2001
90 , NULL
91 , NULL
92 , MDSYS.sdo_elem_info_array(1, 1, 1, l_ordinates.COUNT + 1, 0, 5)
93 , l_ordinates
94 );
95 x_locus.sdo_ordinates.EXTEND(5);
96 x_locus.sdo_ordinates(x_locus.sdo_elem_info(4)) := p_accuracyFactor;
97 x_locus.sdo_ordinates(x_locus.sdo_elem_info(4) + 1) := -9999;
98 x_locus.sdo_ordinates(x_locus.sdo_elem_info(4) + 2) := p_segid;
99 x_locus.sdo_ordinates(x_locus.sdo_elem_info(4) + 3) := p_offset;
100 x_locus.sdo_ordinates(x_locus.sdo_elem_info(4) + 4) := p_direction;
101 x_return_status := g_ret_locus_success;
102 END write_locus;
103
104 PROCEDURE verify_locus(
105 p_api_version IN NUMBER
106 , x_return_status OUT NOCOPY VARCHAR2
107 , x_msg_count OUT NOCOPY NUMBER
108 , x_msg_data OUT NOCOPY VARCHAR2
109 , p_locus IN MDSYS.SDO_GEOMETRY
110 , x_result OUT NOCOPY VARCHAR2
111 ) AS
112 l_api_name CONSTANT VARCHAR2(30) := 'VERIFY_LOCUS';
113 BEGIN
114 x_result := 'FALSE';
115
116 IF p_locus IS NULL THEN
117 RETURN;
118 END IF;
119
120 IF p_locus.sdo_gtype <> 2001 THEN
121 RETURN;
122 END IF;
123
124 -- added: a null check
125 IF p_locus.sdo_elem_info IS NULL THEN
126 RETURN;
127 END IF;
128
129 IF p_locus.sdo_elem_info.COUNT <> 6 THEN
130 RETURN;
131 END IF;
132
133 -- added: a null check
134 IF p_locus.sdo_ordinates IS NULL THEN
135 RETURN;
136 END IF;
137
138 -- added: a size check
139 IF p_locus.sdo_ordinates.COUNT < p_locus.sdo_elem_info(4) + 4 THEN
140 RETURN;
141 END IF;
142
143 IF
144 /*Commented for LF enhancement of forced accuracy
145 p_locus.sdo_ordinates(p_locus.sdo_elem_info(4)) <> -9999
146 OR */
147 p_locus.sdo_ordinates(p_locus.sdo_elem_info(4) + 1) <> -9999 THEN
148 RETURN;
149 END IF;
150
151 x_result := 'TRUE';
152 END verify_locus;
153
154 FUNCTION get_locus_segmentid(
155 p_api_version IN NUMBER
156 , x_return_status OUT NOCOPY VARCHAR2
157 , x_msg_count OUT NOCOPY NUMBER
158 , x_msg_data OUT NOCOPY VARCHAR2
159 , p_geom IN MDSYS.SDO_GEOMETRY
160 )
161 RETURN NUMBER IS
162 RESULT VARCHAR2(6);
163 BEGIN
164 x_return_status := fnd_api.g_ret_sts_success;
165
166 verify_locus(
167 p_api_version => 1
168 , p_locus => p_geom
169 , x_msg_count => x_msg_count
170 , x_msg_data => x_msg_data
171 , x_result => RESULT
172 , x_return_status => x_return_status
173 );
174
175 IF (RESULT = 'TRUE') THEN
176 RETURN p_geom.sdo_ordinates(p_geom.sdo_elem_info(4) + 2);
177 ELSE
178 RETURN -9999;
179 END IF;
180 END;
181
182 FUNCTION get_locus_spot(
183 p_api_version IN NUMBER
184 , x_return_status OUT NOCOPY VARCHAR2
185 , x_msg_count OUT NOCOPY NUMBER
186 , x_msg_data OUT NOCOPY VARCHAR2
187 , p_geom IN MDSYS.SDO_GEOMETRY
188 )
189 RETURN NUMBER IS
190 RESULT VARCHAR2(6);
191 BEGIN
192 verify_locus(
193 p_api_version => 1
194 , p_locus => p_geom
195 , x_msg_count => x_msg_count
196 , x_msg_data => x_msg_data
197 , x_result => RESULT
198 , x_return_status => x_return_status
199 );
200
201 IF (RESULT = 'TRUE') THEN
202 RETURN p_geom.sdo_ordinates(p_geom.sdo_elem_info(4) + 3);
203 ELSE
204 RETURN -9999;
205 END IF;
206 END;
207
208 FUNCTION get_locus_side(
209 p_api_version IN NUMBER
210 , x_return_status OUT NOCOPY VARCHAR2
211 , x_msg_count OUT NOCOPY NUMBER
212 , x_msg_data OUT NOCOPY VARCHAR2
213 , p_geom IN MDSYS.SDO_GEOMETRY
214 )
215 RETURN NUMBER IS
216 RESULT VARCHAR2(6);
217 BEGIN
218 verify_locus(
219 p_api_version => 1
220 , p_locus => p_geom
221 , x_msg_count => x_msg_count
222 , x_msg_data => x_msg_data
223 , x_result => RESULT
224 , x_return_status => x_return_status
225 );
226
227 IF (RESULT = 'TRUE') THEN
228 RETURN p_geom.sdo_ordinates(p_geom.sdo_elem_info(4) + 4);
229 ELSE
230 RETURN -9999;
231 END IF;
232 END;
233
234 FUNCTION get_locus_lat(
235 p_api_version IN NUMBER
236 , x_return_status OUT NOCOPY VARCHAR2
237 , x_msg_count OUT NOCOPY NUMBER
238 , x_msg_data OUT NOCOPY VARCHAR2
239 , p_geom IN MDSYS.SDO_GEOMETRY
240 )
241 RETURN NUMBER IS
242 RESULT VARCHAR2(6);
243 BEGIN
244 verify_locus(
245 p_api_version => 1
246 , p_locus => p_geom
247 , x_msg_count => x_msg_count
248 , x_msg_data => x_msg_data
249 , x_result => RESULT
250 , x_return_status => x_return_status
251 );
252
253 IF (RESULT = 'TRUE') THEN
254 RETURN p_geom.sdo_ordinates(2);
255 ELSE
256 RETURN -9999;
257 END IF;
258 END;
259
260 FUNCTION get_locus_lon(
261 p_api_version IN NUMBER
262 , x_return_status OUT NOCOPY VARCHAR2
263 , x_msg_count OUT NOCOPY NUMBER
264 , x_msg_data OUT NOCOPY VARCHAR2
265 , p_geom IN MDSYS.SDO_GEOMETRY
266 )
267 RETURN NUMBER IS
268 RESULT VARCHAR2(6);
269 BEGIN
270 verify_locus(
271 p_api_version => 1
272 , p_locus => p_geom
273 , x_msg_count => x_msg_count
274 , x_msg_data => x_msg_data
275 , x_result => RESULT
276 , x_return_status => x_return_status
277 );
278
279 IF (RESULT = 'TRUE') THEN
280 RETURN p_geom.sdo_ordinates(1);
281 ELSE
282 RETURN -9999;
283 END IF;
284 END;
285
286 FUNCTION get_locus_srid ( p_api_version in number,
287 p_geom in mdsys.sdo_geometry,
288 x_msg_count out nocopy number,
289 x_msg_data out nocopy varchar2,
290 x_return_status out nocopy varchar2) return NUMBER is
291 result VARCHAR2(6);
292 BEGIN
293 VERIFY_LOCUS( p_api_version => 1,
294 p_locus => p_geom,
295 x_msg_count => x_msg_count,
296 x_msg_data => x_msg_data,
297 x_result => result,
298 x_return_status => x_return_status);
299
300 if (result = 'TRUE') then
301 return p_geom.sdo_srid;
302 else
303 return -9999;
304 end if;
305 END;
306
307 FUNCTION should_call_lf(p_geom IN MDSYS.SDO_GEOMETRY)
308 RETURN VARCHAR2 AS
309 BEGIN
310 IF p_geom IS NULL THEN
311 RETURN fnd_api.g_true;
312 ELSE
313 RETURN fnd_api.g_false;
314 END IF;
315 END should_call_lf;
316
317 FUNCTION get_locus_segmentid(p_geom IN MDSYS.SDO_GEOMETRY)
318 RETURN NUMBER AS
319 l_return_status VARCHAR2(1);
320 l_msg_count NUMBER;
321 l_msg_data VARCHAR2(1000);
322 BEGIN
323 RETURN get_locus_segmentid(
324 p_api_version => 1
325 , p_geom => p_geom
326 , x_msg_count => l_msg_count
327 , x_msg_data => l_msg_data
328 , x_return_status => l_return_status
329 );
330 END;
331
332 FUNCTION get_locus_side(p_geom IN MDSYS.SDO_GEOMETRY)
333 RETURN NUMBER AS
334 l_return_status VARCHAR2(1);
335 l_msg_count NUMBER;
336 l_msg_data VARCHAR2(1000);
337 BEGIN
338 RETURN get_locus_side(
339 p_api_version => 1
340 , p_geom => p_geom
341 , x_msg_count => l_msg_count
342 , x_msg_data => l_msg_data
343 , x_return_status => l_return_status
344 );
345 END;
346
347 FUNCTION get_locus_spot(p_geom IN MDSYS.SDO_GEOMETRY)
348 RETURN NUMBER AS
349 l_return_status VARCHAR2(1);
350 l_msg_count NUMBER;
351 l_msg_data VARCHAR2(1000);
352 BEGIN
353 RETURN get_locus_spot(
354 p_api_version => 1
355 , p_geom => p_geom
356 , x_msg_count => l_msg_count
357 , x_msg_data => l_msg_data
358 , x_return_status => l_return_status
359 );
360 END;
361
362 FUNCTION get_locus_lat(p_geom IN MDSYS.SDO_GEOMETRY)
363 RETURN NUMBER AS
364 l_return_status VARCHAR2(1);
365 l_msg_count NUMBER;
366 l_msg_data VARCHAR2(1000);
367 BEGIN
368 RETURN get_locus_lat(
369 p_api_version => 1
370 , p_geom => p_geom
371 , x_msg_count => l_msg_count
372 , x_msg_data => l_msg_data
373 , x_return_status => l_return_status
374 );
375 END;
376
377 FUNCTION get_locus_lon(p_geom IN MDSYS.SDO_GEOMETRY)
378 RETURN NUMBER AS
379 l_return_status VARCHAR2(1);
380 l_msg_count NUMBER;
381 l_msg_data VARCHAR2(1000);
382 BEGIN
383 RETURN get_locus_lon(
384 p_api_version => 1
385 , p_geom => p_geom
386 , x_msg_count => l_msg_count
387 , x_msg_data => l_msg_data
388 , x_return_status => l_return_status
389 );
390 END;
391
392
393 FUNCTION get_locus_srid(p_geom IN MDSYS.SDO_GEOMETRY)
394 RETURN NUMBER AS
395 l_return_status VARCHAR2(1);
396 l_msg_count NUMBER;
397 l_msg_data VARCHAR2(1000);
398 l_srid NUMBER;
399 BEGIN
400 l_srid := -9999;
401 IF p_geom IS NOT NULL THEN
402 l_srid := get_locus_srid(p_api_version => 1
403 , p_geom => p_geom
404 , x_msg_count => l_msg_count
405 , x_msg_data => l_msg_data
406 , x_return_status => l_return_status
407 );
408
409 END IF;
410
411 RETURN l_srid;
412 END;
413
414
415 FUNCTION get_geometry (p_geometry MDSYS.SDO_GEOMETRY, p_item VARCHAR2, p_index NUMBER DEFAULT NULL)
416 RETURN NUMBER
417 AS
418 -- Bug 1633731
419 -- This function is called with p_item = 'SDO_POINT' and p_index = 1 (X)
420 -- or p_index = 2 (Y) to determine the coordinates of a point location
421 -- such as a customer, resource or task.
422 -- If the SDO_POINT item is null it is assumed that the geometry is a
423 -- valid locus (see package CSF_LOCUS_PUB). For performance reasons this
424 -- will not be checked. The X and Y values can then be obtained from the
425 -- first two elements of the SDO_ORDINATES array.
429 RETURN NULL;
426 BEGIN
427 IF p_geometry IS NULL
428 THEN
430 END IF;
431
432 IF p_item = 'SDO_GTYPE'
433 THEN
434 RETURN p_geometry.sdo_gtype;
435 ELSIF p_item = 'SDO_SRID'
436 THEN
437 RETURN p_geometry.sdo_srid;
438 ELSE
439 -- for all other fields the index has to be defined
440 IF p_index IS NULL
441 THEN
442 RETURN NULL;
443 END IF;
444
445 IF p_item = 'SDO_POINT'
446 THEN
447 IF p_geometry.sdo_point IS NULL AND p_index IN (1, 2)
448 THEN
449 RETURN get_geometry (p_geometry, 'SDO_ORDINATES', p_index);
450 END IF;
451
452 IF p_index = 1
453 THEN
454 RETURN p_geometry.sdo_point.x;
455 ELSIF p_index = 2
456 THEN
457 RETURN p_geometry.sdo_point.y;
458 ELSIF p_index = 3
459 THEN
460 RETURN p_geometry.sdo_point.z;
461 END IF;
462 ELSIF p_item = 'SDO_ELEM_INFO'
463 THEN
464 IF p_geometry.sdo_elem_info IS NOT NULL
465 THEN
466 IF p_geometry.sdo_elem_info.COUNT >= p_index
467 THEN
468 RETURN p_geometry.sdo_elem_info (p_index);
469 END IF;
470 END IF;
471 ELSIF p_item = 'SDO_ORDINATES'
472 THEN
473 IF p_geometry.sdo_ordinates IS NOT NULL
474 THEN
475 IF p_geometry.sdo_ordinates.COUNT >= p_index
476 THEN
477 RETURN p_geometry.sdo_ordinates (p_index);
478 END IF;
479 END IF;
480 END IF;
481 END IF;
482
483 -- in all other cases return null
484 RETURN NULL;
485 END get_geometry;
486
487 /* FUNCTION get_serv_area_coordinates (p_country_id NUMBER, p_index NUMBER)
488 RETURN NUMBER
489 AS
490 l_geom MDSYS.SDO_GEOMETRY;
491 l_coord NUMBER := NULL;
492
493 CURSOR c1
494 IS
495 SELECT SDO_GEOM.SDO_MBR(default_display_center)
496 FROM csf_sdm_ctry_profiles
497 WHERE country_profile_id = p_country_id;
498 BEGIN
499 OPEN c1;
500 FETCH c1 INTO l_geom;
501 IF c1%FOUND
502 THEN
503 l_coord := get_geometry (l_geom, 'SDO_ORDINATES', p_index);
504 END IF;
505 CLOSE c1;
506
507 RETURN l_coord;
508 END get_serv_area_coordinates; */
509
510 FUNCTION get_serv_area_coordinates (p_country_id NUMBER, p_index NUMBER)
511 RETURN NUMBER
512 AS
513 l_geom MDSYS.SDO_GEOMETRY;
514 l_coord NUMBER := NULL;
515 TYPE REF_DISPLAY IS REF CURSOR;
516 c1 REF_DISPLAY;
517 sql_stmt_str VARCHAR2(2000);
518 l_data_set_name VARCHAR2(40);
519 BEGIN
520
521 l_data_set_name := fnd_profile.value('CSF_SPATIAL_DATASET_NAME');
522 IF (l_data_set_name = 'NONE' OR l_data_set_name IS NULL) THEN
523 l_data_set_name := '';
524 END IF;
525
526 sql_stmt_str := 'SELECT default_display_center
527 FROM csf_sdm_ctry_profiles'||l_data_set_name ||'
528 WHERE country_profile_id = '|| p_country_id;
529
530 OPEN c1 FOR sql_stmt_str;
531 FETCH c1 INTO l_geom;
532 IF c1%FOUND THEN
533 l_coord := get_geometry (l_geom, 'SDO_ORDINATES', p_index);
534 END IF;
535 CLOSE c1;
536
537 RETURN l_coord;
538 END get_serv_area_coordinates;
539
540 /**
541 * Returns the Geometry as a String corresponding to the list of Segment IDs
542 * given
543 *
544 * @param p_segment_id_tbl Table of Segment IDs
545 * @param p_sampling_level Whats the Sampling Rate to be used on the Geometry
546 */
547 FUNCTION get_geometry_tbl(
548 p_segment_id_tbl jtf_number_table
549 , p_sampling_level VARCHAR2 DEFAULT NULL
550 )
551 RETURN jtf_varchar2_table_2000 IS
552
553 TYPE geometry_tbl_type IS TABLE OF MDSYS.SDO_GEOMETRY;
554 l_geometry_tbl geometry_tbl_type;
555 l_geometry_str_tbl jtf_varchar2_table_2000;
556 j PLS_INTEGER;
557
558 CURSOR c_geometry IS
559 SELECT /*+ cardinality(l, 1) */
560 roadsegment_geometry
561 FROM csf_lf_roadsegments
562 , TABLE (CAST (p_segment_id_tbl AS jtf_number_table )) l
563 WHERE roadsegment_id = l.COLUMN_VALUE;
564
565 FUNCTION get_coord(p_tbl_index NUMBER, p_coord_index NUMBER) RETURN NUMBER IS
566 BEGIN
567 RETURN ROUND(l_geometry_tbl(p_tbl_index).sdo_ordinates(p_coord_index), 4);
568 END get_coord;
569 BEGIN
570 OPEN c_geometry;
571 FETCH c_geometry BULK COLLECT INTO l_geometry_tbl;
572 CLOSE c_geometry;
573
574 l_geometry_str_tbl := jtf_varchar2_table_2000();
575
576 FOR i IN 1..l_geometry_tbl.COUNT LOOP
577 l_geometry_str_tbl.extend(1);
578 IF l_geometry_tbl(i) IS NULL OR l_geometry_tbl(i).sdo_ordinates IS NULL THEN
579 l_geometry_str_tbl(i) := '';
580 ELSE
581 IF p_sampling_level IS NULL OR p_sampling_level = 'Y' THEN
582 -- Number of Coordinate Pairs
583 j := l_geometry_tbl(i).sdo_ordinates.COUNT/2;
584 --
585 -- We have just one Coordinate Pair. Return it as it is
586 IF j = 1 THEN
587 l_geometry_str_tbl(i) := get_coord(i, 2) || ',' || get_coord(i, 1);
588 ELSE
589 j := ROUND(j / 2); -- Center Coordinate Pair
590 l_geometry_str_tbl(i) := get_coord(i, 2*j) || ',' || get_coord(i, 2*j-1);
591 END IF;
592 ELSE
593 l_geometry_str_tbl(i) := get_coord(i, 2) || ',' || get_coord(i, 1);
594 FOR j IN 2 .. l_geometry_tbl(i).sdo_ordinates.COUNT / 2 LOOP
595 l_geometry_str_tbl(i) := l_geometry_str_tbl(i) || ',' || get_coord(i, 2*j) || ',' || get_coord(i, 2*j-1);
596 END LOOP;
597 END IF;
598 END IF;
599 END LOOP;
600
601 RETURN l_geometry_str_tbl;
602 END get_geometry_tbl;
603
604
605 /**
606 * Computes the Geometry of the Route given as the Segment IDs
607 * Table and then saves the Geometry of the Route in
608 * CSF_TDS_ROUTE_CACHE to be used in future computations.
609 */
610 PROCEDURE compute_and_save_route(
611 p_api_version IN NUMBER
612 , p_init_msg_list IN VARCHAR2
613 , p_commit IN VARCHAR2
614 , x_return_status OUT NOCOPY VARCHAR2
615 , x_msg_count OUT NOCOPY NUMBER
616 , x_msg_data OUT NOCOPY VARCHAR2
617 , p_segment_id_tbl IN jtf_number_table
618 , p_start_side IN NUMBER
619 , p_start_offset IN NUMBER
620 , p_end_side IN NUMBER
621 , p_end_offset IN NUMBER
622 , p_tds_calc_type IN NUMBER
623 , p_travel_time IN NUMBER
624 , p_travel_distance IN NUMBER
625 ) IS
626 l_api_version CONSTANT NUMBER := 1.0;
627 l_api_name CONSTANT VARCHAR2(30) := 'COMPUTE_AND_SAVE_ROUTE';
628
629 l_geometry_tbl jtf_varchar2_table_2000;
630 i PLS_INTEGER;
631 l_route clob;
632
633 l_from_segment_id NUMBER;
634 l_to_segment_id NUMBER;
635 BEGIN
636 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
637 RAISE fnd_api.g_exc_unexpected_error;
638 END IF;
639
640 -- Initialize message list if p_init_msg_list is set to TRUE
641 IF fnd_api.to_boolean (p_init_msg_list) THEN
642 fnd_msg_pub.initialize;
643 END IF;
644
645 -- Initialize API return status to success
646 x_return_status := fnd_api.g_ret_sts_success;
647
648 l_from_segment_id := p_segment_id_tbl(p_segment_id_tbl.FIRST);
649 l_to_segment_id := p_segment_id_tbl(p_segment_id_tbl.LAST);
650
651 INSERT INTO CSF_TDS_ROUTE_CACHE (
652 ROUTE_CACHE_ID
653 , SEGMENT_FROM
654 , SIDE_FROM
655 , SPOT_FROM
656 , SEGMENT_TO
657 , SIDE_TO
658 , SPOT_TO
659 , RESULTTIME
660 , RESULTDISTANCE
661 , ROUTE
662 , DATETIME
663 , HITCOUNT
664 )
665 VALUES (
666 CSF_TDS_ROUTE_CACHE_S1.NEXTVAL
667 , l_from_segment_id
668 , p_start_side
669 , p_start_offset
670 , l_to_segment_id
671 , p_end_side
672 , p_end_offset
673 , p_travel_time
674 , p_travel_distance
675 , empty_clob
676 , SYSDATE
677 , 1
678 )
679 RETURNING route INTO l_route;
680
681 l_geometry_tbl := get_geometry_tbl(p_segment_id_tbl);
682
683 -- Open the CLOB
684 dbms_lob.OPEN(l_route, dbms_lob.lob_readwrite);
685
686 -- Read from Geometry Table and write to the CLOB
687 i := l_geometry_tbl.FIRST;
688 WHILE i IS NOT NULL LOOP
689 dbms_lob.writeappend(l_route, LENGTH(l_geometry_tbl(i)) + 1, l_geometry_tbl(i) || ',');
690 i := l_geometry_tbl.NEXT(i);
691 END LOOP;
692
693 i := 1;
694 IF dbms_lob.getlength(l_route) > 0 THEN
695 dbms_lob.erase(l_route, i, dbms_lob.getlength(l_route));
696 END IF;
697
698 -- Close the CLOB
699 dbms_lob.CLOSE(l_route);
700
701 IF fnd_api.to_boolean(p_commit) THEN
702 COMMIT;
703 END IF;
704 EXCEPTION
705 WHEN OTHERS THEN
706 x_return_status := fnd_api.g_ret_sts_unexp_error;
707 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
708 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
709 END IF;
710 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
711 END compute_and_save_route;
712
713 /**
714 * For each Location given in the Location Table, the Location Record
715 * in HZ_LOCATIONS will be updated with the Geometry containing the
716 * Latitude and Longitude as given by the corresponding PLSQL Tables.
717 */
718 PROCEDURE compute_and_save_locuses(
719 p_api_version IN NUMBER
720 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
721 , p_commit IN VARCHAR2 DEFAULT NULL
722 , x_return_status OUT NOCOPY VARCHAR2
723 , x_msg_count OUT NOCOPY NUMBER
724 , x_msg_data OUT NOCOPY VARCHAR2
725 , p_srid IN NUMBER
726 , p_location_id_tbl IN jtf_number_table
727 , p_latitude_tbl IN jtf_number_table
728 , p_longitude_tbl IN jtf_number_table
729 ) IS
730 l_api_version CONSTANT NUMBER := 1.0;
731 l_api_name CONSTANT VARCHAR2(30) := 'COMPUTE_AND_SAVE_LOCUSES';
732
733 l_srid NUMBER;
734 BEGIN
735 SAVEPOINT csf_save_locuses;
736
737 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
738 RAISE fnd_api.g_exc_unexpected_error;
739 END IF;
740
741 -- Initialize message list if p_init_msg_list is set to TRUE
742 IF fnd_api.to_boolean (p_init_msg_list) THEN
743 fnd_msg_pub.initialize;
744 END IF;
745
746 -- Initialize API return status to success
747 x_return_status := fnd_api.g_ret_sts_success;
748
749 l_srid := NVL(p_srid, 8307);
750
751 FORALL i IN 1..p_location_id_tbl.COUNT
752 UPDATE hz_locations
753 SET geometry = mdsys.sdo_geometry(
754 2001
755 , l_srid
756 , mdsys.sdo_point_type( p_longitude_tbl(i), p_latitude_tbl(i), 0)
757 , mdsys.sdo_elem_info_array(1,1,1)
758 , mdsys.sdo_ordinate_array( p_longitude_tbl(i), p_latitude_tbl(i) )
759 )
760 WHERE location_id = p_location_id_tbl(i);
761
762 IF fnd_api.to_boolean(p_commit) THEN
763 COMMIT;
764 END IF;
765 EXCEPTION
766 WHEN OTHERS THEN
767 ROLLBACK TO csf_save_locuses;
768 x_return_status := fnd_api.g_ret_sts_unexp_error;
769 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
770 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
771 END IF;
772 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
773 END compute_and_save_locuses;
774
775 /**
776 * Returns the Given Geometry in String Representation with each attribute
777 * separated by @. The sequence of the attributes are Longitude, Latitude,
778 * Segment Id, Offset and Side.
779 */
780 FUNCTION get_locus_string(p_geom IN MDSYS.SDO_GEOMETRY, p_soft_validation VARCHAR2)
781 RETURN VARCHAR2 AS
782 l_return_status VARCHAR2(1);
783 l_msg_count NUMBER;
784 l_msg_data VARCHAR2(1000);
785
786 l_result VARCHAR2(6);
787 l_geometry mdsys.sdo_geometry;
788 l_locus_string VARCHAR2(200);
789 BEGIN
790 IF (p_soft_validation = fnd_api.g_true) THEN
791 IF p_geom IS NULL
792 OR p_geom.sdo_gtype <> 2001
793 OR p_geom.sdo_elem_info IS NULL
794 OR p_geom.sdo_ordinates IS NULL
795 THEN
796 l_result := 'FALSE';
797 ELSE
798 l_result := 'TRUE';
799 END IF;
800 ELSE
801 verify_locus(
802 p_api_version => 1
803 , p_locus => p_geom
804 , x_msg_count => l_msg_count
805 , x_msg_data => l_msg_data
806 , x_result => l_result
807 , x_return_status => l_return_status
808 );
809 END IF;
810
811 IF l_result <> 'TRUE' THEN
812 RETURN NULL;
813 END IF;
814
815 l_geometry := p_geom;
816 IF l_geometry.sdo_srid <> 8307 THEN
817 l_geometry := sdo_cs.transform(p_geom, 8307);
818 END IF;
819
820 l_locus_string := ROUND(p_geom.sdo_ordinates(1), 8)
821 || '@' || ROUND(p_geom.sdo_ordinates(2), 8);
822
823 IF p_geom.sdo_elem_info.COUNT = 6
824 AND p_geom.sdo_ordinates.COUNT >= p_geom.sdo_elem_info(4) + 4
825 /*Commented for LF enhancement of forced accuracy
826 AND p_geom.sdo_ordinates(p_geom.sdo_elem_info(4)) = -9999*/
827 AND p_geom.sdo_ordinates(p_geom.sdo_elem_info(4) + 1) = -9999
828 THEN
829 l_locus_string := l_locus_string
830 || '@' || p_geom.sdo_ordinates(p_geom.sdo_elem_info(4) + 2)
831 || '@' || p_geom.sdo_ordinates(p_geom.sdo_elem_info(4) + 3)
832 || '@' || p_geom.sdo_ordinates(p_geom.sdo_elem_info(4) + 4);
833 END IF;
834
835 RETURN l_locus_string;
836 EXCEPTION
837 WHEN OTHERS THEN
838 RETURN NULL;
839 END;
840
841 END csf_locus_pub;