[Home] [Help]
PACKAGE BODY: APPS.CSF_TDS_PUB
Source
1 package body csf_tds_pub as
2 /* $Header: CSFPTDSB.pls 120.2.12010000.2 2008/10/08 13:27:08 gmarwah noship $ */
3
4 PROCEDURE dbgl (p_msg_data VARCHAR2);
5
6 PROCEDURE put_stream (p_handle IN NUMBER, p_msg_data IN VARCHAR2);
7
8 FUNCTION CACHED_ROUTE_EXISTS
9 ( l_config_string VARCHAR2,
10 l_from_segment_id csf_tds_route_cache.to_segment_id%TYPE,
11 l_from_spot csf_tds_route_cache.to_segment_position%TYPE,
12 l_from_side csf_tds_route_cache.to_segment_side%TYPE,
13 l_to_segment_id csf_tds_route_cache.to_segment_id%TYPE,
14 l_to_spot csf_tds_route_cache.to_segment_position%TYPE,
15 l_to_side csf_tds_route_cache.to_segment_side%TYPE,
16 multiDataSetProfileValue VARCHAR2
17 )
18 RETURN boolean IS
19
20 return_val boolean;
21
22 route_cache_cursor t_crs;
23
24 route_cache_qry VARCHAR2(1000);
25
26 l_segment_id csf_tds_route_cache.to_segment_id%TYPE;
27
28 BEGIN
29 return_val := false;
30
31
32 route_cache_qry := 'SELECT distinct FROM_SEGMENT_ID
33 FROM csf_tds_route_cache'||multiDataSetProfileValue ||'
34 WHERE CONFIG = '||l_config_string||' AND
35 FROM_SEGMENT_ID = '||l_from_segment_id||' AND
36 FROM_SEGMENT_SIDE = '|| l_from_side ||' AND
37 FROM_SEGMENT_POSITION = '|| l_from_spot ||' AND
38 TO_SEGMENT_ID = '||l_to_segment_id||' AND
39 TO_SEGMENT_SIDE = '||l_to_side || ' AND
40 TO_SEGMENT_POSITION = '||l_to_spot;
41
42 l_segment_id := NULL;
43
44 OPEN route_cache_cursor FOR route_cache_qry;
45 LOOP
46 FETCH route_cache_cursor into l_segment_id;
47 IF route_cache_cursor%FOUND AND l_segment_id IS NOT NULL
48 THEN
49 return_val := true;
50 END IF;
51 EXIT WHEN route_cache_cursor%notfound;
52 END LOOP;
53 CLOSE route_cache_cursor;
54
55 return return_val;
56 END CACHED_ROUTE_EXISTS;
57
58
59
60 PROCEDURE TDS_ROUTES_TO_BE_CALCULATED(query_id IN NUMBER,config_string IN
61 VARCHAR2, user_id IN NUMBER,status OUT NOCOPY NUMBER , msg OUT NOCOPY VARCHAR2) IS
62
63 CURSOR QueryWhereClause(qid NUMBER) IS
64 SELECT where_clause
65 FROM csf_dc_queries_b
66 WHERE query_id = qid;
67
68 -- CURSOR ResourceAddressCursor IS
69 -- SELECT DISTINCT csf_locus_pub.get_locus_segmentid(geometry) AS segment_id,
70 -- csf_locus_pub.get_locus_spot(geometry) AS spot,
71 -- csf_locus_pub.get_locus_side(geometry) AS side,
72 -- csf_locus_pub.get_locus_lat(geometry) AS lat,
73 -- csf_locus_pub.get_locus_lon(geometry) AS lon,
74 -- csf_locus_pub.get_locus_srid(geometry) AS srid,
75 -- tile_id
76 -- FROM hz_locations loc,
77 -- csf_tds_segments seg,
78 -- jtf_terr_rsc_all rsc,
79 -- jtf_terr_usgs_all usg,
80 -- jtf_rs_resource_extns extn,
81 -- hz_party_sites site,
82 -- per_all_people_f ppl
83 -- WHERE usg.source_id = -1002
84 -- AND rsc.terr_id = usg.terr_id
85 -- AND rsc.resource_id = extn.resource_id
86 -- AND extn.source_id = ppl.person_id
87 -- AND ppl.party_id = site.party_id
88 -- AND site.location_id = loc.location_id
89 -- AND csf_locus_pub.get_locus_segmentid(geometry) = seg.SEGMENT_ID
90 -- AND geometry IS NOT NULL
91 -- AND loc.location_id > 0
92 -- ;
93
94 wClause VARCHAR2(1000);
95
96 task_address_cursor t_crs;
97 task_address_cursor_inner t_crs;
98
99 res_address_Cursor t_crs;
100 res_address_Cursor_inner t_crs;
101
102 -- l_location_id hz_locations.location_id%TYPE;
103 l_segment_id csf_tds_route_cache.from_segment_id%TYPE;
104 l_spot csf_tds_route_cache.from_segment_position%TYPE;
105 l_side csf_tds_route_cache.from_segment_side%TYPE;
106 l_lat NUMBER;
107 l_lon NUMBER;
108 l_SRID NUMBER;
109 l_tile_id NUMBER;
110
111
112 -- l_location_id_inner hz_locations.location_id%TYPE;
113 l_segment_id_inner csf_tds_route_cache.to_segment_id%TYPE;
114 l_spot_inner csf_tds_route_cache.to_segment_position%TYPE;
115 l_side_inner csf_tds_route_cache.to_segment_side%TYPE;
116 l_lat_inner NUMBER;
117 l_lon_inner NUMBER;
118 l_SRID_inner NUMBER;
119 l_tile_id_inner NUMBER;
120
121 l_seg_From csf_tds_route_cache.from_segment_id%TYPE;
122
123 task_qry_stmt VARCHAR2(2000);
124 res_qry_stmt VARCHAR2(2000);
125
126 config_string_trimmed varchar2 (100);
127
128 distance NUMBER;
129 threshhold NUMBER;
130
131 multiDataSetProfileValue VARCHAR2(10);
132
133 BEGIN
134 status := 1;
135 msg := 'SUCCESS';
136
137 SELECT NVL( TO_NUMBER(fnd_profile.value('CSR_MAX_TRAVEL_DIST_FOR_ESTIMATION')) ,100)
138 ,NVL(fnd_profile.value('CSF_SPATIAL_DATASET_NAME'),'')
139 INTO threshhold, multiDataSetProfileValue
140 FROM dual;
141
142 IF UPPER(multiDataSetProfileValue)= 'NONE'
143 THEN
144 multiDataSetProfileValue := '';
145 END IF;
146
147
148 threshhold := threshhold *1000;
149
150 config_string_trimmed := trim(config_string);
151
152 -- Getting the where clause for the taks's query
153 OPEN QueryWhereClause(query_id);
154
155 FETCH QueryWhereClause
156 INTO wClause;
157 CLOSE QueryWhereClause;
158
159 --Query used for resource address
160 res_qry_stmt := ' SELECT DISTINCT csf_locus_pub.get_locus_segmentid(geometry) AS segment_id,
161 csf_locus_pub.get_locus_spot(geometry) AS spot,
162 csf_locus_pub.get_locus_side(geometry) AS side,
163 csf_locus_pub.get_locus_lat(geometry) AS lat,
164 csf_locus_pub.get_locus_lon(geometry) AS lon,
165 csf_locus_pub.get_locus_srid(geometry) AS srid,
166 tile_id
167 FROM hz_locations loc,
168 csf_tds_segments'||multiDataSetProfileValue ||' seg,
169 jtf_terr_rsc_all rsc,
170 jtf_terr_usgs_all usg,
171 jtf_rs_resource_extns extn,
172 hz_party_sites site,
173 per_all_people_f ppl
174 WHERE usg.source_id = -1002
175 AND rsc.terr_id = usg.terr_id
176 AND rsc.resource_id = extn.resource_id
177 AND extn.source_id = ppl.person_id
178 AND ppl.party_id = site.party_id
179 AND site.location_id = loc.location_id
180 AND csf_locus_pub.get_locus_segmentid(geometry) = seg.SEGMENT_ID
181 AND geometry IS NOT NULL
182 AND loc.location_id > 0';
183
184 -- Query used for tasks
185 task_qry_stmt := 'SELECT DISTINCT csf_locus_pub.get_locus_segmentid(geometry) AS segment_id,
186 csf_locus_pub.get_locus_spot(geometry) AS spot,
187 csf_locus_pub.get_locus_side(geometry) AS side,
188 csf_locus_pub.get_locus_lat(geometry) AS LAT,
189 csf_locus_pub.get_locus_lon(geometry) AS LON,
190 csf_locus_pub.GET_LOCUS_SRID(geometry) AS SRID,
191 tile_id
192 FROM hz_locations a,csf_tds_segments'||multiDataSetProfileValue ||' seg
193 WHERE geometry is not null
194 AND a.location_id > 0
195 AND csf_locus_pub.get_locus_segmentid(geometry) = seg.SEGMENT_ID
196 AND a.location_id IN (SELECT location_id
197 FROM csf_ct_tasks WHERE ' || wClause || ' )';
198
199
200 -- Now for all the resource address to task address --
201 OPEN task_address_cursor FOR task_qry_stmt;
202 LOOP
203 FETCH task_address_cursor into l_segment_id,l_spot,l_side,l_lat, l_lon, l_SRID,l_tile_id;
204 EXIT WHEN task_address_cursor%notfound;
205
206 --FOR res IN ResourceAddressCursor
207 OPEN res_address_cursor FOR res_qry_stmt;
208 LOOP
209 FETCH res_address_cursor into l_segment_id_inner,
210 l_spot_inner,l_side_inner,l_lat_inner, l_lon_inner, l_SRID_inner,l_tile_id_inner;
211 EXIT WHEN res_address_cursor%notfound;
212
213 IF CACHED_ROUTE_EXISTS( config_string_trimmed,l_segment_id_inner,
214 l_spot_inner, l_side_inner, l_segment_id , l_spot, l_side, multiDataSetProfileValue)
215 = false
216 THEN
217
218 IF l_segment_id <> l_segment_id_inner AND l_SRID = l_SRID_inner
219 THEN
220
221 distance := 0;
222 GEO_DISTANCE(l_SRID_inner,l_lon_inner,l_lat_inner,l_lon,l_lat,distance);
223
224 IF distance < threshhold
225 THEN
226
227 INSERT INTO csf_tds_route_to_be_calculated
228 (SEGMENT_ID_FROM, SIDE_FROM, SPOT_FROM, SRID_FROM,
229 LATITUDE_FROM, LONGITUDE_FROM, SEGMENT_ID_TO, SIDE_TO,
230 SPOT_TO, SRID_TO, LATITUDE_TO, LONGITUDE_TO, USER_ID,
231 FROM_TILE_ID, TO_TILE_ID)
232 VALUES (l_segment_id_inner, -- SEGMENT_ID_FROM
233 l_side_inner, --SIDE_FROM
234 l_spot_inner, --SPOT_FROM
235 l_SRID_inner, --SRID_FROM
236 l_lat_inner, --LATITUDE_FROM
237 l_lon_inner, --LONGITUDE_FROM
238 l_segment_id, --SEGMENT_ID_TO
239 l_side, -- SIDE_TO
240 l_spot, -- SPOT_TO
241 l_SRID, --SRID_TO
242 l_lat, --LATITUDE_TO
243 l_lon, -- LONGITUDE_TO
244 user_id, -- USER_ID
245 l_tile_id_inner, -- FROM_SEGMENT_TILE_ID
246 l_tile_id -- TO_SEGMENT_TILE_ID
247 );
248
249 END IF;
250 END IF;
251 END IF;
252 END LOOP;
253 CLOSE res_address_cursor;
254 END LOOP;
255 CLOSE task_address_cursor;
256 COMMIT;
257
258
259 -- Now for all the task address to other task address --
260 OPEN task_address_cursor FOR task_qry_stmt;
261 LOOP
262 FETCH task_address_cursor into l_segment_id,l_spot,l_side,l_lat, l_lon, l_SRID,l_tile_id;
263 EXIT WHEN task_address_cursor%notfound;
264
265 OPEN task_address_cursor_inner FOR task_qry_stmt;
266 LOOP
267 FETCH task_address_cursor_inner into l_segment_id_inner,
268 l_spot_inner,l_side_inner,l_lat_inner, l_lon_inner, l_SRID_inner,l_tile_id_inner;
269 EXIT WHEN task_address_cursor_inner%notfound;
270
271 IF CACHED_ROUTE_EXISTS( config_string_trimmed,l_segment_id , l_spot, l_side,
272 l_segment_id_inner,l_spot_inner, l_side_inner, multiDataSetProfileValue)= false
273 THEN
274
275 IF l_segment_id <> l_segment_id_inner AND l_SRID = l_SRID_inner
276 THEN
277 -- IF l_seg_From IS NULL
278 -- THEN
279 distance := 0;
280 GEO_DISTANCE(l_SRID,l_lon,l_lat,l_lon_inner,l_lat_inner,distance);
281
282 IF distance < threshhold
283 THEN
284
285 INSERT INTO csf_tds_route_to_be_calculated
286 (SEGMENT_ID_FROM, SIDE_FROM, SPOT_FROM, SRID_FROM,
287 LATITUDE_FROM, LONGITUDE_FROM, SEGMENT_ID_TO, SIDE_TO,
288 SPOT_TO, SRID_TO, LATITUDE_TO, LONGITUDE_TO, USER_ID,
289 FROM_TILE_ID, TO_TILE_ID)
290 VALUES (l_segment_id, -- SEGMENT_ID_FROM
291 l_side, --SIDE_FROM
292 l_spot, --SPOT_FROM
293 l_SRID, --SRID_FROM
294 l_lat, --LATITUDE_FROM
295 l_lon, --LONGITUDE_FROM
296 l_segment_id_inner, --SEGMENT_ID_TO
297 l_side_inner, -- SIDE_TO
298 l_spot_inner, -- SPOT_TO
299 l_SRID_inner, --SRID_TO
300 l_lat_inner, --LATITUDE_TO
301 l_lon_inner, -- LONGITUDE_TO
302 user_id, -- USER_ID
303 l_tile_id, -- FROM_SEGMENT_TILE_ID
304 l_tile_id_inner -- TO_SEGMENT_TILE_ID
305 );
306
307 END IF;
308 END IF;
309 END IF;
310 END LOOP;
311 CLOSE task_address_cursor_inner;
312
313 END LOOP;
314 CLOSE task_address_cursor;
315
316 COMMIT;
317 EXCEPTION
318 WHEN OTHERS THEN
319 status := 0;
320 msg := sqlerrm;
321 ROLLBACK;
322
323 END TDS_ROUTES_TO_BE_CALCULATED;
324
325 PROCEDURE GEO_DISTANCE (srId IN NUMBER,x1 IN NUMBER, y1 IN NUMBER,
326 x2 IN NUMBER,y2 IN NUMBER , result OUT NOCOPY NUMBER) IS
327
328 EARTH_RADIUS NUMBER;
329 northDist NUMBER;
330 eastDist NUMBER;
331 PI NUMBER;
332 BEGIN
333
334
335 IF srId <> 8307
336 THEN
337 result := SQRT( POWER((x1 - x2),2)+POWER((y1 - y2),2));
338
339 ELSE
340 EARTH_RADIUS := 6378137;
341 PI := 3.14159265358979323846;
342
343 northDist := (((y2 * PI )/ 180.0) - ((y1 * PI) / 180.0)) *
344 EARTH_RADIUS;
345
346 eastDist := (((x2 * PI )/ 180.0) - ((x1 * PI) / 180.0)) *
347 COS(((y2 * PI) / 180.0)) * EARTH_RADIUS;
348
349 result := SQRT(POWER(northDist,2) + POWER (eastDist,2));
350
351
352 END IF;
353
354
355 END GEO_DISTANCE;
356
357 PROCEDURE PURGE_UNUSED_CACHE (
358 errbuf OUT NOCOPY VARCHAR2,
359 retcode OUT NOCOPY VARCHAR2,
360 p_start_date IN VARCHAR2 DEFAULT NULL,
361 p_end_date IN VARCHAR2 DEFAULT NULL
362 )
363 IS
364
365 l_api_name CONSTANT VARCHAR2 (30) := 'PURGE_UNUSED_CACHE';
366 l_api_version CONSTANT NUMBER := 1.0;
367 -- predefined error codes for concurrent programs
368 l_rc_succ CONSTANT NUMBER := 0;
369 l_rc_warn CONSTANT NUMBER := 1;
370 l_rc_err CONSTANT NUMBER := 2;
371 -- predefined error buffer output strings (replaced by translated messages)
372 l_msg_succ VARCHAR2 (80);
373 l_msg_warn VARCHAR2 (80);
374 l_msg_err VARCHAR2 (80);
375 --
376 -- the date range
377 --
378 l_start_date DATE;
379 l_end_date DATE;
380
381 l_fmt VARCHAR2 (100);
382 l_msg_data VARCHAR2 (2000);
383
384 l_del_qry VARCHAR2(500);
385
386 multiDataSetProfileValue VARCHAR2(10);
387
388
389 BEGIN
390
391 SELECT NVL(fnd_profile.value('CSF_SPATIAL_DATASET_NAME'),'')
392 INTO multiDataSetProfileValue
393 FROM dual;
394
395 IF UPPER(multiDataSetProfileValue) = 'NONE'
396 THEN
397 multiDataSetProfileValue :='';
398 END IF;
399
400 fnd_msg_pub.initialize;
401 -- get termination messages
402 fnd_message.set_name ('CSF', 'CSF_GST_DONE_SUCC');
403 l_msg_succ := fnd_message.get;
404 fnd_message.set_name ('CSF', 'CSF_GST_DONE_WARN');
405 l_msg_warn := fnd_message.get;
406 fnd_message.set_name ('CSF', 'CSF_GST_DONE_ERR');
407 l_msg_err := fnd_message.get;
408 -- Initialize API return status to success
409 -- API body
410
411 fnd_message.set_name ('CSF', 'CSF_TDS_PUB');
412 l_msg_data := fnd_message.get;
413 put_stream (g_output, l_msg_data);
414
415 -- start date defaults to today (truncated)
416 -- later converted back to server timezone
417 -- e.g. client timezone is CET (GMT+1)
418 -- server timezone is PST (GMT-8)
419 -- If it is 6-Aug 06:00 for the client, then it is 5-Aug 21:00 for the
420 -- server, and trunc(sysdate) will give 5-Aug instead of 6-Aug. Hence
421 -- we need to convert to client timezone before truncating.
422 -- When the parameter *is* specified, in the same case it will already
423 -- read 6-aug-2003.
424 --
425 IF p_start_date IS NULL
426 THEN
427 l_start_date :=
428 TRUNC (csf_timezones_pvt.date_to_client_tz_date (SYSDATE - 14));
429 -- convert to server timezone
430 l_start_date :=
431 csf_timezones_pvt.date_to_server_tz_date (l_start_date);
432 ELSE
433 -- all fnd_date converts to server timezone so need for conversion
434 l_start_date := fnd_date.canonical_to_date (p_start_date);
435 END IF;
436
437 --
438 -- end date defaults to same day as start date (also truncated)
439 --
440 IF p_end_date IS NULL
441 THEN
442 IF p_start_date IS NULL
443 THEN
444 l_end_date := l_start_date - 7;
445 ELSE
446 l_end_date := l_start_date + 1;
447 END IF;
448 -- all fnd_date converts to server timezone so need for conversion
449 l_end_date := csf_timezones_pvt.date_to_server_tz_date (l_end_date);
450
451 ELSE
452 l_end_date := fnd_date.canonical_to_date (p_end_date);
453 END IF;
454
455 --
456 -- get date format
457 l_fmt := fnd_profile.VALUE ('ICX_DATE_FORMAT_MASK');
458
459 IF l_fmt IS NULL
460 THEN
461 l_fmt := 'dd-MON-yyyy';
462 END IF;
463
464 --
465 -- feedback the date range
466 fnd_message.set_name ('CSF', 'CSF_AUTO_COMMIT_DATE_RANGE');
467 fnd_message.set_token ('P_START_DATE', TO_CHAR (l_start_date, l_fmt));
468 fnd_message.set_token ('P_END_DATE', TO_CHAR (l_end_date, l_fmt));
469 put_stream (g_output, fnd_message.get);
470 --
471 -- finally convert the date range to server timezone before processing
472 --
473 l_start_date := csf_timezones_pvt.date_to_server_tz_date (l_start_date);
474 l_end_date := csf_timezones_pvt.date_to_server_tz_date (l_end_date);
475
476 l_del_qry := 'DELETE FROM CSF_TDS_ROUTE_CACHE'||multiDataSetProfileValue
477 ||' WHERE HITCOUNT = 0
478 AND CREATION_DATE >= :1
479 AND CREATION_DATE <= :2';
480
481 EXECUTE IMMEDIATE l_del_qry
482 USING l_start_date,l_end_date;
483
484 COMMIT;
485
486 retcode := l_rc_succ;
487 errbuf := l_msg_succ;
488
489
490 put_stream (g_log, l_msg_succ);
491 put_stream (g_output, l_msg_succ);
492
493 fnd_message.set_name ('CSF', 'CSF_TDS_PUB_DEL_SCC');
494 l_msg_data := fnd_message.get;
495 put_stream (g_log, l_msg_data);
496
497
498
499 EXCEPTION
500 WHEN OTHERS
501 THEN
502 retcode := l_rc_err;
503 errbuf := l_msg_err;
504
505 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
506 THEN
507 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
508 END IF;
509
510 put_stream (SQLCODE, SQLERRM);
511 dbms_output.put_line(SQLCODE|| SQLERRM);
512
513 END PURGE_UNUSED_CACHE;
514
515
516 -- logging procedures
517
518 PROCEDURE put_stream (p_handle IN NUMBER, p_msg_data IN VARCHAR2)
519 IS
520 BEGIN
521 IF p_handle = 0
522 THEN
523 dbgl (p_msg_data);
524 ELSIF p_handle = -1
525 THEN
526 IF g_debug
527 THEN
528 dbgl (p_msg_data);
529 END IF;
530 ELSE
531 fnd_file.put_line (p_handle, p_msg_data);
532 END IF;
533 END put_stream;
534
535
536 PROCEDURE dbgl (p_msg_data VARCHAR2)
537 IS
538 i PLS_INTEGER;
539 l_msg VARCHAR2 (300);
540 BEGIN
541 i := 1;
542
543 LOOP
544 l_msg := SUBSTR (p_msg_data, i, 255);
545 EXIT WHEN l_msg IS NULL;
546
547 EXECUTE IMMEDIATE g_debug_p
548 USING l_msg;
549
550 i := i + 255;
551 END LOOP;
552 END dbgl;
553
554
555 end CSF_TDS_PUB;