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