[Home] [Help]
PACKAGE BODY: APPS.CSF_GPS_PUB
Source
1 PACKAGE BODY csf_gps_pub AS
2 /* $Header: CSFPGPSB.pls 120.1 2010/09/13 08:30:12 rkamasam noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'CSF_GPS_PUB';
5 g_level_cp_output CONSTANT NUMBER := fnd_log.level_unexpected + 1;
6 g_epoch CONSTANT DATE := to_date('01-01-1970', 'DD-MM-YYYY');
7 g_counter NUMBER := 0;
8
9 PROCEDURE debug(p_message VARCHAR2, p_module VARCHAR2, p_level NUMBER, p_indent NUMBER) IS
10 BEGIN
11 IF p_level = g_level_cp_output AND fnd_file.output > 0 THEN
12 fnd_file.put_line(fnd_file.output, p_message);
13 END IF;
14
15 IF NVL(fnd_profile.value('AFLOG_ENABLED'), 'N') = 'Y'
16 AND p_level >= NVL(fnd_profile.value('AFLOG_LEVEL'), 1)
17 THEN
18 IF fnd_file.log > 0 THEN
19 IF p_message = ' ' THEN
20 fnd_file.put_line(fnd_file.log, '');
21 ELSE
22 fnd_file.put_line(fnd_file.log, rpad(p_module, 20) || ': ' || p_message);
23 END IF;
24 END IF;
25 IF ( p_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
26 THEN
27 fnd_log.string(p_level, 'csf.plsql.CSF_GPS_PUB.' || p_module, p_message);
28 END IF;
29 END IF;
30 --dbms_output.put_line(rpad(p_module, 20) || ': ' || p_message);
31 END debug;
32
33 FUNCTION is_gps_enabled RETURN VARCHAR2 IS
34 l_enabled VARCHAR2(1);
35 CURSOR c_vendors IS
36 SELECT DECODE(COUNT(*), 0, 'N', 'Y')
37 FROM csf_gps_vendors_b WHERE enabled = 'Y';
38 BEGIN
39 OPEN c_vendors;
40 FETCH c_vendors INTO l_enabled;
41 CLOSE c_vendors;
42
43 RETURN l_enabled;
44 END is_gps_enabled;
45
46 FUNCTION get_gps_label(
47 p_device_id NUMBER DEFAULT NULL
48 , p_resource_id NUMBER DEFAULT NULL
49 , p_resource_type VARCHAR2 DEFAULT NULL
50 , p_date DATE DEFAULT NULL
51 )
52 RETURN VARCHAR2 IS
53 --
54 l_label csf_gps_devices.device_tag%TYPE;
55 --
56 CURSOR c_device_label IS
57 SELECT device_tag
58 FROM csf_gps_devices
59 WHERE device_id = p_device_id;
60 --
61 CURSOR c_resource_device_label IS
62 SELECT device_tag
63 FROM csf_gps_device_assignments a
64 , csf_gps_devices d
65 WHERE a.resource_id = p_resource_id
66 AND a.resource_type = p_resource_type
67 AND NVL(p_date, SYSDATE) BETWEEN a.start_date_active AND NVL(a.end_date_active, SYSDATE + 1);
68 BEGIN
69 IF p_device_id IS NOT NULL THEN
70 OPEN c_device_label;
71 FETCH c_device_label INTO l_label;
72 CLOSE c_device_label;
73 ELSIF p_resource_id IS NOT NULL THEN
74 OPEN c_resource_device_label;
75 FETCH c_resource_device_label INTO l_label;
76 CLOSE c_resource_device_label;
77 END IF;
78
79 RETURN l_label;
80 END;
81
82 FUNCTION get_vendor_name(p_vendor_id NUMBER)
83 RETURN VARCHAR2 IS
84 --
85 l_label csf_gps_vendors_tl.vendor_name%TYPE;
86 CURSOR c_vendors IS
87 SELECT vendor_name
88 FROM csf_gps_vendors_vl
89 WHERE vendor_id = p_vendor_id;
90 BEGIN
91 OPEN c_vendors;
92 FETCH c_vendors INTO l_label;
93 CLOSE c_vendors;
94
95 RETURN l_label;
96 END;
97
98 PROCEDURE get_location(
99 p_device_id IN NUMBER DEFAULT NULL
100 , p_resource_id IN NUMBER DEFAULT NULL
101 , p_resource_type IN VARCHAR2 DEFAULT NULL
102 , p_date IN DATE DEFAULT NULL
103 , x_feed_time OUT NOCOPY DATE
104 , x_status_code OUT NOCOPY VARCHAR2
105 , x_latitude OUT NOCOPY NUMBER
106 , x_longitude OUT NOCOPY NUMBER
107 , x_speed OUT NOCOPY NUMBER
108 , x_direction OUT NOCOPY VARCHAR2
109 , x_parked_time OUT NOCOPY NUMBER
110 , x_address OUT NOCOPY VARCHAR2
111 , x_creation_date OUT NOCOPY DATE
112 , x_device_tag OUT NOCOPY VARCHAR2
113 , x_status_code_meaning OUT NOCOPY VARCHAR2
114 ) IS
115 c_location_cursor SYS_REFCURSOR;
116
117 CURSOR c_status_lookup IS
118 SELECT NVL(meaning, 'UNKNOWN')
122 BEGIN
119 FROM fnd_lookups
120 WHERE lookup_type = 'CSF_GPS_DEVICE_STATUSES'
121 AND lookup_code = 'UNKNOWN';
123 IF p_device_id IS NOT NULL THEN
124 OPEN c_location_cursor FOR
125 SELECT l.vendor_feed_time
126 , l.status
127 , trunc(l.latitude, 3)
128 , trunc(l.longitude, 3)
129 , l.speed
130 , l.direction
131 , l.parked_time
132 , l.address
133 , d.device_tag
134 , (
135 SELECT NVL(fl.meaning, stmap.internal_status_code) FROM fnd_lookups fl, csf_gps_vendor_status_maps stmap
136 WHERE fl.lookup_type = 'CSF_GPS_DEVICE_STATUSES' AND fl.lookup_code = stmap.internal_status_code AND stmap.vendor_status_code = l.status
137 ) status_code_meaning
138 , l.creation_date
139 FROM csf_gps_location_feeds l
140 , csf_gps_devices d
141 WHERE d.device_id = p_device_id
142 AND l.device_id = d.device_id
143 -- AND (
144 -- p_date IS NULL AND l.creation_date > SYSDATE - 1
145 -- OR p_date BETWEEN (l.creation_date - 1) AND (l.creation_date + 1)
146 -- )
147 ORDER BY ABS(NVL(p_date, l.creation_date) - l.creation_date) ASC, l.creation_date DESC;
148 ELSE
149 OPEN c_location_cursor FOR
150 SELECT l.vendor_feed_time
151 , l.status
152 , trunc(l.latitude, 3)
153 , trunc(l.longitude, 3)
154 , l.speed
155 , l.direction
156 , l.parked_time
157 , l.address
158 , d.device_tag
159 , (
160 SELECT NVL(fl.meaning, stmap.internal_status_code) FROM fnd_lookups fl, csf_gps_vendor_status_maps stmap
161 WHERE fl.lookup_type = 'CSF_GPS_DEVICE_STATUSES' AND fl.lookup_code = stmap.internal_status_code AND stmap.vendor_status_code = l.status
162 ) status_code_meaning
163 , l.creation_date
164 FROM csf_gps_location_feeds l
165 , csf_gps_device_assignments a
166 , csf_gps_devices d
167 WHERE a.resource_id = p_resource_id
168 AND a.resource_type = p_resource_type
169 AND a.device_id = l.device_id
170 AND d.device_id = a.device_id
171 -- AND (
172 -- p_date IS NULL AND l.creation_date > SYSDATE - 1
173 -- OR p_date BETWEEN (l.creation_date - 1) AND (l.creation_date + 1)
174 -- AND p_date BETWEEN a.start_date_active AND NVL(a.end_date_active, SYSDATE+1)
175 -- )
176 ORDER BY ABS(NVL(p_date, l.creation_date) - l.creation_date) ASC, l.creation_date DESC;
177 END IF;
178
179 FETCH c_location_cursor INTO
180 x_feed_time
181 , x_status_code
182 , x_latitude
183 , x_longitude
184 , x_speed
185 , x_direction
186 , x_parked_time
187 , x_address
188 , x_device_tag
189 , x_status_code_meaning
190 , x_creation_date
191 ;
192 CLOSE c_location_cursor;
193
194 IF x_latitude IS NULL OR x_longitude IS NULL THEN
195 x_longitude := -9999;
196 x_latitude := -9999;
197 END IF;
198 IF x_status_code_meaning IS NULL AND x_device_tag IS NOT NULL THEN
199 OPEN c_status_lookup;
200 FETCH c_status_lookup INTO x_status_code_meaning;
201 CLOSE c_status_lookup;
202 END IF;
203 END get_location;
204
205 FUNCTION get_location (
206 p_device_id IN NUMBER DEFAULT NULL
207 , p_resource_id IN NUMBER DEFAULT NULL
208 , p_resource_type IN VARCHAR2 DEFAULT NULL
209 , p_date IN DATE DEFAULT NULL
210 )
211 RETURN MDSYS.SDO_POINT_TYPE IS
212 l_feed_time csf_gps_location_feeds.vendor_feed_time%TYPE;
213 l_status_code csf_gps_location_feeds.status%TYPE;
214 l_latitude csf_gps_location_feeds.latitude%TYPE;
215 l_longitude csf_gps_location_feeds.longitude%TYPE;
216 l_speed csf_gps_location_feeds.speed%TYPE;
217 l_direction csf_gps_location_feeds.direction%TYPE;
218 l_parked_time csf_gps_location_feeds.parked_time%TYPE;
219 l_address csf_gps_location_feeds.address%TYPE;
220 l_creation_date csf_gps_location_feeds.creation_date%TYPE;
221 l_device_tag csf_gps_devices.device_tag%TYPE;
222 l_status_code_meaning fnd_lookups.meaning%TYPE;
223 BEGIN
224 get_location(
225 p_device_id => p_device_id
226 , p_resource_id => p_resource_id
227 , p_resource_type => p_resource_type
228 , p_date => p_date
229 , x_feed_time => l_feed_time
230 , x_status_code => l_status_code
231 , x_latitude => l_latitude
232 , x_longitude => l_longitude
233 , x_speed => l_speed
234 , x_direction => l_direction
235 , x_parked_time => l_parked_time
236 , x_address => l_address
237 , x_creation_date => l_creation_date
238 , x_device_tag => l_device_tag
239 , x_status_code_meaning => l_status_code_meaning
240 );
241
242 RETURN MDSYS.SDO_POINT_TYPE(l_longitude, l_latitude, 0);
243
244 END get_location;
245
246 PROCEDURE save_location_feeds(
247 p_api_version IN NUMBER
248 , p_init_msg_list IN VARCHAR2
252 , x_msg_count OUT NOCOPY NUMBER
249 , p_commit IN VARCHAR2
250 , x_return_status OUT NOCOPY VARCHAR2
251 , x_msg_data OUT NOCOPY VARCHAR2
253 , p_count IN NUMBER
254 , p_device_id_tbl IN jtf_number_table
255 , p_feed_time_tbl IN jtf_date_table
256 , p_status_tbl IN jtf_varchar2_table_100
257 , p_lat_tbl IN jtf_number_table
258 , p_lng_tbl IN jtf_number_table
259 , p_speed_tbl IN jtf_number_table
260 , p_dir_tbl IN jtf_varchar2_table_100
261 , p_parked_time_tbl IN jtf_number_table
262 , p_address_tbl IN jtf_varchar2_table_300
263 ) IS
264 l_api_name CONSTANT VARCHAR2(30) := 'SAVE_LOCATION_FEEDS';
265 BEGIN
266 SAVEPOINT csf_save_location_feeds;
267
268 -- Check for API Compatibility
269 IF NOT fnd_api.compatible_api_call(1.0, p_api_version, l_api_name, g_pkg_name) THEN
270 RAISE fnd_api.g_exc_unexpected_error;
271 END IF;
272
273 -- Initialize Message Stack if required
274 IF p_init_msg_list = fnd_api.g_true THEN
275 fnd_msg_pub.initialize;
276 END IF;
277
278 -- Initialize Return Status
279 x_return_status := fnd_api.g_ret_sts_success;
280
281 FORALL i IN 1..p_count
282 INSERT INTO csf_gps_location_feeds(
283 location_feed_id
284 , device_id
285 , creation_date
286 , vendor_feed_time
287 , status
288 , latitude
289 , longitude
290 , speed
291 , direction
292 , parked_time
293 , address
294 )
295 VALUES (
296 csf_gps_location_feeds_s.NEXTVAL
297 , p_device_id_tbl(i)
298 , SYSDATE
299 , p_feed_time_tbl(i)
300 , p_status_tbl(i)
301 , p_lat_tbl(i)
302 , p_lng_tbl(i)
303 , p_speed_tbl(i)
304 , p_dir_tbl(i)
305 , p_parked_time_tbl(i)
306 , p_address_tbl(i)
307 );
308
309 IF fnd_api.to_boolean(p_commit) THEN
310 COMMIT;
311 END IF;
312 EXCEPTION
313 WHEN OTHERS THEN
314 x_return_status := fnd_api.g_ret_sts_unexp_error;
315 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
316 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
317 END IF;
318 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
319 ROLLBACK TO csf_save_location_feeds;
320 END save_location_feeds;
321
322 /***************************************************************************************
323 * *
324 * Purging Routines *
325 * *
326 ***************************************************************************************/
327
328 PROCEDURE purge_res_device_feeds(
329 p_device_id NUMBER
330 , p_device_tag VARCHAR2
331 , p_start_date DATE
332 , p_end_date DATE
333 , p_indent NUMBER
334 ) IS
335 l_api_name CONSTANT VARCHAR2(30) := 'PURGE_RES_DEVICE_FEEDS';
336 BEGIN
337 fnd_message.set_name('CSF', 'CSF_GPS_PURGING_DEVICE_LOCS');
338 fnd_message.set_token('DEVICE_TAG', p_device_tag);
339 debug(fnd_message.get, l_api_name, g_level_cp_output, p_indent);
340
341 DELETE csf_gps_location_feeds
342 WHERE device_id = p_device_id
343 AND creation_date BETWEEN p_start_date AND p_end_date;
344 g_counter := g_counter + SQL%ROWCOUNT;
345 fnd_message.set_name('CSF', 'CSF_GPS_PURGE_DEL_ROWS_STATS');
346 fnd_message.set_token('COUNT', SQL%ROWCOUNT);
347 debug(fnd_message.get, l_api_name, g_level_cp_output, p_indent + 4);
348 END purge_res_device_feeds;
349
350 PROCEDURE purge_res_location_feeds(
351 p_resource_id NUMBER
352 , p_resource_type VARCHAR2
353 , p_start_date DATE
354 , p_end_date DATE
355 , p_indent NUMBER
356 ) IS
357 l_api_name CONSTANT VARCHAR2(30) := 'PURGE_RES_LOCATION_FEEDS';
358 l_res_info csf_resource_pub.resource_rec_type;
359 BEGIN
360 l_res_info := csf_resource_pub.get_resource_info(p_resource_id, p_resource_type);
361
362 fnd_message.set_name('CSF', 'CSF_GPS_PURGING_RES_LOCS');
363 fnd_message.set_token('RESOURCE', l_res_info.resource_name || '(' || csf_resource_pub.get_resource_type_name(l_res_info.resource_type) || ', ' || l_res_info.resource_number || ')');
364 debug(fnd_message.get, l_api_name, g_level_cp_output, p_indent);
365
366 DELETE csf_gps_location_feeds
367 WHERE device_id IN (
368 SELECT device_id
369 FROM csf_gps_device_assignments
370 WHERE resource_id = p_resource_id
371 AND resource_type = p_resource_type
372 AND start_date_active < p_end_date
373 AND NVL(end_date_active, SYSDATE) > p_start_date
374 )
375 AND creation_date BETWEEN p_start_date AND p_end_date;
376 g_counter := g_counter + SQL%ROWCOUNT;
377 fnd_message.set_name('CSF', 'CSF_GPS_PURGE_DEL_ROWS_STATS');
378 fnd_message.set_token('COUNT', SQL%ROWCOUNT);
379 debug(fnd_message.get, l_api_name, g_level_cp_output, p_indent + 4);
380 END purge_res_location_feeds;
381
385 , p_vendor_id IN NUMBER DEFAULT NULL
382 PROCEDURE purge_location_feeds(
383 errbuf OUT NOCOPY VARCHAR2
384 , retcode OUT NOCOPY VARCHAR2
386 , p_device_id IN NUMBER DEFAULT NULL
387 , p_device_assignment_id IN NUMBER DEFAULT NULL
388 , p_territory_id IN NUMBER DEFAULT NULL
389 , p_resource_type IN VARCHAR2 DEFAULT NULL
390 , p_resource_id IN NUMBER DEFAULT NULL
391 , p_start_date IN VARCHAR2 DEFAULT NULL
392 , p_end_date IN VARCHAR2 DEFAULT NULL
393 , p_num_days IN NUMBER DEFAULT NULL
394 ) IS
395 l_api_name CONSTANT VARCHAR2(30) := 'PURGE_LOCATION_FEEDS';
396 l_start_date DATE;
397 l_end_date DATE;
398
399 --
400 CURSOR c_vendors IS
401 SELECT vendor_id, vendor_name
402 FROM csf_gps_vendors_vl;
403 --
404 CURSOR c_vendor_devices(v_vendor_id NUMBER) IS
405 SELECT device_id, device_tag
406 FROM csf_gps_devices
407 WHERE vendor_id = v_vendor_id;
408 --
409 CURSOR c_territory_resources IS
410 SELECT resource_id, resource_type
411 FROM jtf_terr_rsc_all
412 WHERE terr_id = p_territory_id;
413
414 l_datetime_format fnd_profile_option_values.profile_option_value%TYPE;
415
416 BEGIN
417 fnd_message.set_name('CSF', 'CSF_GPS_PURGE_CP_STARTED');
418 debug(fnd_message.get, l_api_name, g_level_cp_output, 0);
419
420 IF p_num_days IS NULL THEN
421 IF p_start_date IS NOT NULL THEN
422 l_start_date := fnd_date.canonical_to_date(p_start_date);
423 ELSE
424 l_start_date := SYSDATE-8;
425 END IF;
426
427 IF p_end_date IS NOT NULL THEN
428 l_end_date := fnd_date.canonical_to_date(p_end_date);
429 ELSE
430 l_end_date := TRUNC(SYSDATE-1);
431 END IF;
432 ELSE
433 l_start_date := SYSDATE - trunc(p_num_days);
434 l_end_date := SYSDATE;
435 END IF;
436
437 l_datetime_format := fnd_profile.value('ICX_DATE_FORMAT_MASK');
438 IF l_datetime_format IS NULL THEN
439 l_datetime_format := 'DD-MM-YYYY HH24:MI';
440 ELSE
441 l_datetime_format := l_datetime_format || ' HH24:MI';
442 END IF;
443 fnd_message.set_name('CSF', 'CSF_GPS_PURGE_DATE_RANGE');
444 fnd_message.set_token('START_DATE', to_char(l_start_date, l_datetime_format));
445 fnd_message.set_token('END_DATE', to_char(l_end_date, l_datetime_format));
446 debug(fnd_message.get, l_api_name, g_level_cp_output, 0);
447
448 SAVEPOINT csf_gps_loc_purge;
449
450 IF p_vendor_id IS NOT NULL THEN
451 --
452 -- Purging the Locations of all the Devices attached to the Vendor
453 --
454 fnd_message.set_name('CSF', 'CSF_GPS_PURGING_VENDOR_LOCS');
455 fnd_message.set_token('VENDOR', get_vendor_name(p_vendor_id));
456 debug(fnd_message.get, l_api_name, g_level_cp_output, 0);
457
458 FOR v_device IN c_vendor_devices(p_vendor_id) LOOP
459 purge_res_device_feeds(v_device.device_id, v_device.device_tag, l_start_date, l_end_date, 4);
460 END LOOP;
461 ELSIF p_device_id IS NOT NULL THEN
462 --
463 -- Purging the Locations of the Device
464 --
465 purge_res_device_feeds(p_device_id, get_gps_label(p_device_id), l_start_date, l_end_date, 0);
466 ELSIF p_device_assignment_id IS NOT NULL THEN
467 --
468 -- Purging the Locations of the Device Assignment
469 --
470 fnd_message.set_name('CSF', 'CSF_GPS_PURGING_ASSIGN_LOCS');
471 debug(fnd_message.get, l_api_name, g_level_cp_output, 0);
472
473 DELETE csf_gps_location_feeds
474 WHERE device_id IN (
475 SELECT device_id
476 FROM csf_gps_device_assignments
477 WHERE device_assignment_id = p_device_assignment_id
478 AND start_date_active < l_end_date
479 AND NVL(end_date_active, SYSDATE) > l_start_date
480 )
481 AND creation_date BETWEEN l_start_date AND l_end_date;
482 g_counter := g_counter + SQL%ROWCOUNT;
483 fnd_message.set_name('CSF', 'CSF_GPS_PURGE_DEL_ROWS_STATS');
484 fnd_message.set_token('COUNT', SQL%ROWCOUNT);
485 debug(fnd_message.get, l_api_name, g_level_cp_output, 4);
486 ELSIF p_resource_id IS NOT NULL THEN
487 purge_res_location_feeds(p_resource_id, p_resource_type, l_start_date, l_end_date, 0);
488 ELSIF p_territory_id IS NOT NULL THEN
489 FOR v_res IN c_territory_resources LOOP
490 purge_res_location_feeds(v_res.resource_id, v_res.resource_type, l_start_date, l_end_date, 4);
491 END LOOP;
492 ELSE
493 FOR v_vendor IN c_vendors LOOP
494 fnd_message.set_name('CSF', 'CSF_GPS_PURGING_VENDOR_LOCS');
495 fnd_message.set_token('VENDOR', v_vendor.vendor_name);
496 debug(fnd_message.get, l_api_name, g_level_cp_output, 0);
497
498 FOR v_device IN c_vendor_devices(v_vendor.vendor_id) LOOP
499 purge_res_device_feeds(v_device.device_id, v_device.device_tag, l_start_date, l_end_date, 4);
500 END LOOP;
501 END LOOP;
502 END IF;
503
504 fnd_message.set_name('CSF', 'CSF_GPS_PURGE_CP_COMPLETED');
505 fnd_message.set_token('COUNT', g_counter);
506 debug(fnd_message.get, l_api_name, g_level_cp_output, 0);
507
508 COMMIT;
509 g_counter := 0;
510 retcode := 0;
514 WHEN OTHERS THEN
511 fnd_message.set_name('CSF', 'CSF_CP_DONE_SUCCESS');
512 errbuf := fnd_message.get;
513 EXCEPTION
515 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
516 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
517 END IF;
518
519 retcode := 2;
520 g_counter := 0;
521 fnd_message.set_name('CSF', 'CSF_CP_DONE_ERROR');
522 errbuf := fnd_message.get;
523 ROLLBACK TO csf_gps_loc_purge;
524 END purge_location_feeds;
525
526 PROCEDURE add_language IS
527 BEGIN
528 DELETE FROM csf_gps_vendors_tl t
529 WHERE NOT EXISTS (SELECT NULL FROM csf_gps_vendors_b b WHERE b.vendor_id = t.vendor_id);
530
531 UPDATE csf_gps_vendors_tl cgvt
532 SET (cgvt.vendor_name, cgvt.description) = (
533 SELECT cgvtl.vendor_name, cgvtl.description
534 FROM csf_gps_vendors_tl cgvtl
535 WHERE cgvtl.vendor_id = cgvt.vendor_id
536 AND cgvtl.language = cgvt.source_lang
537 )
538 WHERE (cgvt.vendor_id, cgvt.language) IN (
539 SELECT subt.vendor_id, subt.language
540 FROM csf_gps_vendors_tl subb, csf_gps_vendors_tl subt
541 WHERE subb.vendor_id = subt.vendor_id
542 AND subb.language = subt.source_lang
543 AND (
544 subb.vendor_name <> subt.vendor_name
545 OR subb.description <> subt.description
546 OR (subb.description IS NULL AND subt.description IS NOT NULL)
547 OR (subb.description IS NOT NULL AND subt.description IS NULL)
548 )
549 );
550
551 INSERT INTO csf_gps_vendors_tl (
552 vendor_id
553 , vendor_name
554 , description
555 , created_by
556 , creation_date
557 , last_updated_by
558 , last_update_date
559 , last_update_login
560 , language
561 , source_lang
562 )
563 SELECT cgvt.vendor_id
564 , cgvt.vendor_name
565 , cgvt.description
566 , cgvt.created_by
567 , cgvt.creation_date
568 , cgvt.last_updated_by
569 , cgvt.last_update_date
570 , cgvt.last_update_login
571 , l.language_code
572 , cgvt.source_lang
573 FROM csf_gps_vendors_tl cgvt
574 , fnd_languages l
575 WHERE l.installed_flag IN ('I', 'B')
576 AND cgvt.language = userenv('LANG')
577 AND NOT EXISTS (
578 SELECT NULL
579 FROM csf_gps_vendors_tl t
580 WHERE t.vendor_id = cgvt.vendor_id
581 AND t.language = l.language_code
582 );
583 END add_language;
584 END csf_gps_pub;