[Home] [Help]
PACKAGE BODY: APPS.CS_KB_ELEMENTS_PKG
Source
1 PACKAGE BODY CS_KB_ELEMENTS_PKG AS
2 /* $Header: cskbelb.pls 120.0 2005/06/01 11:41:58 appldev noship $ */
3 /*=======================================================================+
4 | Copyright (c) 1999 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | FILENAME cskbelb.pls
8 | DESCRIPTION
9 | PL/SQL body for package: CS_KB_ELEMENTS_PKG
10 | This contains the Private Table Handlers for a Statement (Element)
11 |
12 | History:
13 | 10.18.99 AWWONG Created
14 | 01.05.00 HBALA Added LOAD_ROW, TRANSLATE_ROW
15 | 01.20.00 AWWONG Add check links before delete, fill name(2000)
16 | 18-Nov-2003 MKETTLE Cleanup for 11.5.10
17 | - Obsolete unused apis
18 | - Moved ELE_AUDIT table Handlers back here
19 | 17-May-2005 MKETTLE Cleanup - Removed obs Incr_Element_Element in 115.50
20 *=======================================================================*/
21
22 PROCEDURE Get_Who(
23 X_SYSDATE OUT NOCOPY DATE,
24 X_USER_ID OUT NOCOPY NUMBER,
25 X_LOGIN_ID OUT NOCOPY NUMBER )
26 IS
27 BEGIN
28
29 x_sysdate := sysdate;
30 x_user_id := fnd_global.user_id;
31 x_login_id := fnd_global.login_id;
32
33 END Get_Who;
34
35 PROCEDURE Insert_Row (
36 X_ROWID IN OUT NOCOPY VARCHAR2,
37 X_ELEMENT_ID IN NUMBER,
38 X_ELEMENT_NUMBER IN VARCHAR2,
39 X_ELEMENT_TYPE_ID IN NUMBER,
40 X_ELEMENT_NAME IN VARCHAR2,
41 X_GROUP_FLAG IN NUMBER,
42 X_STATUS IN VARCHAR2,
43 X_ACCESS_LEVEL IN NUMBER,
44 X_NAME IN VARCHAR2,
45 X_DESCRIPTION IN CLOB,
46 X_CREATION_DATE IN DATE,
47 X_CREATED_BY IN NUMBER,
48 X_LAST_UPDATE_DATE IN DATE,
49 X_LAST_UPDATED_BY IN NUMBER,
50 X_LAST_UPDATE_LOGIN IN NUMBER,
51 X_LOCKED_BY IN NUMBER,
52 X_LOCK_DATE IN DATE,
53 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
54 X_ATTRIBUTE1 IN VARCHAR2,
55 X_ATTRIBUTE2 IN VARCHAR2,
56 X_ATTRIBUTE3 IN VARCHAR2,
57 X_ATTRIBUTE4 IN VARCHAR2,
58 X_ATTRIBUTE5 IN VARCHAR2,
59 X_ATTRIBUTE6 IN VARCHAR2,
60 X_ATTRIBUTE7 IN VARCHAR2,
61 X_ATTRIBUTE8 IN VARCHAR2,
62 X_ATTRIBUTE9 IN VARCHAR2,
63 X_ATTRIBUTE10 IN VARCHAR2,
64 X_ATTRIBUTE11 IN VARCHAR2,
65 X_ATTRIBUTE12 IN VARCHAR2,
66 X_ATTRIBUTE13 IN VARCHAR2,
67 X_ATTRIBUTE14 IN VARCHAR2,
68 X_ATTRIBUTE15 IN VARCHAR2,
69 X_START_ACTIVE_DATE IN DATE,
70 X_END_ACTIVE_DATE IN DATE,
71 X_CONTENT_TYPE IN VARCHAR2 )
72
73 IS
74
75 l_access_level CS_KB_ELEMENTS_B.ACCESS_LEVEL%TYPE;
76 l_content_type CS_KB_ELEMENTS_B.CONTENT_TYPE%TYPE;
77
78 l_srclen INTEGER :=0;
79 l_clob CLOB;
80
81 CURSOR c IS
82 SELECT rowid
83 FROM CS_KB_ELEMENTS_B
84 WHERE element_id = x_element_id;
85
86 CURSOR c_tl(c_id IN NUMBER) IS
87 SELECT element_id,
88 language,
89 description
90 FROM CS_KB_ELEMENTS_TL
91 WHERE element_id = c_id;
92
93 BEGIN
94
95 IF x_access_level IS NULL THEN
96 FND_PROFILE.GET('CS_KB_SMP_SOL_ACCESS_LEVEL', l_access_level);
97
98 IF l_access_level is null THEN
99 l_access_level := 3000;
100 END IF;
101
102 ELSE
103 l_access_level := x_access_level;
104 END IF;
105
106 IF x_content_type IS NULL THEN
107 FND_PROFILE.GET('CS_KB_SMP_SOL_CONTENT_TYPE', l_content_type);
108
109 IF l_content_type is null THEN
110 l_content_type := 'TEXT/HTML';
111 END IF;
112
113 ELSE
114 l_content_type := x_content_type;
115 END IF;
116
117 INSERT INTO CS_KB_ELEMENTS_B (
118 element_id,
119 element_number,
120 element_type_id,
121 element_name,
122 group_flag,
123 status,
124 access_level,
125 creation_date,
126 created_by,
127 last_update_date,
128 last_updated_by,
129 last_update_login,
130 locked_by,
131 lock_date,
132 attribute_category,
133 attribute1,
134 attribute2,
135 attribute3,
136 attribute4,
137 attribute5,
138 attribute6,
139 attribute7,
140 attribute8,
141 attribute9,
142 attribute10,
143 attribute11,
144 attribute12,
145 attribute13,
146 attribute14,
147 attribute15,
148 start_active_date,
149 end_active_date,
150 content_type
151 ) VALUES (
152 x_element_id,
153 x_element_number,
154 x_element_type_id,
155 x_element_name,
156 x_group_flag,
157 x_status,
158 l_access_level,
159 x_creation_date,
160 x_created_by,
161 x_last_update_date,
162 x_last_updated_by,
163 x_last_update_login,
164 x_locked_by,
165 x_lock_date,
166 x_attribute_category,
167 x_attribute1,
168 x_attribute2,
169 x_attribute3,
170 x_attribute4,
171 x_attribute5,
172 x_attribute6,
173 x_attribute7,
174 x_attribute8,
175 x_attribute9,
176 x_attribute10,
177 x_attribute11,
178 x_attribute12,
179 x_attribute13,
180 x_attribute14,
181 x_attribute15,
182 x_start_active_date,
183 x_end_active_date,
184 l_content_type );
185
186 INSERT INTO CS_KB_ELEMENTS_TL (
187 element_id,
188 name,
189 description,
190 creation_date,
191 created_by,
192 last_update_date,
193 last_updated_by,
194 last_update_login,
195 language,
196 source_lang
197 ) SELECT
198 x_element_id,
199 x_name,
200 empty_clob(),
201 x_creation_date,
202 x_created_by,
203 x_last_update_date,
204 x_last_updated_by,
205 x_last_update_login,
206 l.language_code,
207 USERENV('LANG')
208 FROM FND_LANGUAGES l
209 WHERE l.installed_flag IN ('I', 'B')
210 AND NOT EXISTS
211 (SELECT NULL
212 FROM CS_KB_ELEMENTS_TL t
213 WHERE t.element_id = x_element_id
214 AND t.language = l.language_code);
215
216 --INSERT given clob INTO clob_locator FOR all languages.
217 IF(x_description IS NOT NULL AND
218 DBMS_LOB.getlength(x_description)>0) THEN
219
220 l_srclen := DBMS_LOB.getlength(x_description);
221
222 FOR rectl IN c_tl(x_element_id) LOOP
223
224 DBMS_LOB.copy(rectl.description, x_description, l_srclen, 1,1);
225
226 END LOOP;
227
228 END IF;
229
230 OPEN c;
231 FETCH c INTO x_rowid;
232 IF (c%NOTFOUND) THEN
233 CLOSE c;
234 RAISE NO_DATA_FOUND;
235 END IF;
236 CLOSE c;
237
238 END Insert_Row;
239
240
241 PROCEDURE Lock_Row (
242 X_ELEMENT_ID IN NUMBER,
243 X_ELEMENT_NUMBER IN VARCHAR2,
244 X_ELEMENT_TYPE_ID IN NUMBER,
245 X_ELEMENT_NAME IN VARCHAR2,
246 X_GROUP_FLAG IN NUMBER,
247 X_STATUS IN VARCHAR2,
248 X_ACCESS_LEVEL IN NUMBER,
249 X_NAME IN VARCHAR2,
250 X_DESCRIPTION IN CLOB,
251 X_LOCKED_BY IN NUMBER,
252 X_LOCK_DATE IN DATE,
253 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
254 X_ATTRIBUTE1 IN VARCHAR2,
255 X_ATTRIBUTE2 IN VARCHAR2,
256 X_ATTRIBUTE3 IN VARCHAR2,
257 X_ATTRIBUTE4 IN VARCHAR2,
258 X_ATTRIBUTE5 IN VARCHAR2,
259 X_ATTRIBUTE6 IN VARCHAR2,
260 X_ATTRIBUTE7 IN VARCHAR2,
261 X_ATTRIBUTE8 IN VARCHAR2,
262 X_ATTRIBUTE9 IN VARCHAR2,
263 X_ATTRIBUTE10 IN VARCHAR2,
264 X_ATTRIBUTE11 IN VARCHAR2,
265 X_ATTRIBUTE12 IN VARCHAR2,
266 X_ATTRIBUTE13 IN VARCHAR2,
267 X_ATTRIBUTE14 IN VARCHAR2,
268 X_ATTRIBUTE15 IN VARCHAR2,
269 X_START_ACTIVE_DATE IN DATE,
270 X_END_ACTIVE_DATE IN DATE,
271 X_CONTENT_TYPE IN VARCHAR2 )
272 IS
273 CURSOR c IS
274 SELECT
275 element_id,
276 element_number,
277 element_type_id,
278 element_name,
279 group_flag,
280 status,
281 access_level,
282 locked_by,
283 lock_date,
284 attribute_category,
285 attribute1,
286 attribute2,
287 attribute3,
288 attribute4,
289 attribute5,
290 attribute6,
291 attribute7,
292 attribute8,
293 attribute9,
294 attribute10,
295 attribute11,
296 attribute12,
297 attribute13,
298 attribute14,
299 attribute15,
300 start_active_date,
301 end_active_date,
302 content_type
303 FROM CS_KB_ELEMENTS_B
304 WHERE element_id = x_element_id
305 FOR UPDATE OF element_id NOWAIT;
306
307 recinfo c%ROWTYPE;
308
309 CURSOR c1 IS
310 SELECT name,
311 description,
312 decode(language, USERENV('LANG'), 'Y', 'N') baselang
313 FROM CS_KB_ELEMENTS_TL
314 WHERE element_id = x_element_id
315 AND USERENV('LANG') IN (language, source_lang)
316 FOR UPDATE OF element_id NOWAIT;
317
318 BEGIN
319
320 OPEN c;
321 FETCH c INTO recinfo;
322 IF (c%NOTFOUND) THEN
323 CLOSE c;
324 FND_MESSAGE.set_name('FND', 'FORM_RECORD_DELETED');
325 APP_EXCEPTION.raise_exception;
326 END IF;
327 CLOSE c;
328 IF ( ((recinfo.element_number = x_element_number)
329 OR ((recinfo.element_number IS NULL) AND (x_element_number IS NULL)))
330 AND ((recinfo.element_id = x_element_id)
331 OR ((recinfo.element_id IS NULL) AND (x_element_id IS NULL)))
332 AND ((recinfo.locked_by = x_locked_by)
333 OR ((recinfo.locked_by IS NULL) AND (x_locked_by IS NULL)))
334 AND ((recinfo.lock_date = x_lock_date)
335 OR ((recinfo.lock_date IS NULL) AND (x_lock_date IS NULL)))
336 AND ((recinfo.element_type_id = x_element_type_id)
337 OR ((recinfo.element_type_id IS NULL) AND (x_element_type_id IS NULL)))
338 AND ((recinfo.element_name = x_element_name)
339 OR ((recinfo.element_name IS NULL) AND (x_element_name IS NULL)))
340 AND ((recinfo.group_flag = x_group_flag)
341 OR ((recinfo.group_flag IS NULL) AND (x_group_flag IS NULL)))
342 AND ((recinfo.status = x_status)
343 OR ((recinfo.status IS NULL) AND (x_status IS NULL)))
344 AND ((recinfo.access_level = x_access_level)
345 OR ((recinfo.access_level IS NULL) AND (x_access_level IS NULL)))
346 AND ((recinfo.attribute_category = x_attribute_category)
347 OR ((recinfo.attribute_category IS NULL) AND (x_attribute_category IS NULL)))
348 AND ((recinfo.attribute1 = x_attribute1)
349 OR ((recinfo.attribute1 IS NULL) AND (x_attribute1 IS NULL)))
350 AND ((recinfo.attribute2 = x_attribute2)
351 OR ((recinfo.attribute2 IS NULL) AND (x_attribute2 IS NULL)))
352 AND ((recinfo.attribute3 = x_attribute3)
353 OR ((recinfo.attribute3 IS NULL) AND (x_attribute3 IS NULL)))
354 AND ((recinfo.attribute4 = x_attribute4)
355 OR ((recinfo.attribute4 IS NULL) AND (x_attribute4 IS NULL)))
356 AND ((recinfo.attribute5 = x_attribute5)
357 OR ((recinfo.attribute5 IS NULL) AND (x_attribute5 IS NULL)))
358 AND ((recinfo.attribute6 = x_attribute6)
359 OR ((recinfo.attribute6 IS NULL) AND (x_attribute6 IS NULL)))
360 AND ((recinfo.attribute7 = x_attribute7)
361 OR ((recinfo.attribute7 IS NULL) AND (x_attribute7 IS NULL)))
362 AND ((recinfo.attribute8 = x_attribute8)
363 OR ((recinfo.attribute8 IS NULL) AND (x_attribute8 IS NULL)))
364 AND ((recinfo.attribute9 = x_attribute9)
365 OR ((recinfo.attribute9 IS NULL) AND (x_attribute9 IS NULL)))
366 AND ((recinfo.attribute10 = x_attribute10)
367 OR ((recinfo.attribute10 IS NULL) AND (x_attribute10 IS NULL)))
368 AND ((recinfo.attribute11 = x_attribute11)
369 OR ((recinfo.attribute11 IS NULL) AND (x_attribute11 IS NULL)))
370 AND ((recinfo.attribute12 = x_attribute12)
371 OR ((recinfo.attribute12 IS NULL) AND (x_attribute12 IS NULL)))
372 AND ((recinfo.attribute13 = x_attribute13)
373 OR ((recinfo.attribute13 IS NULL) AND (x_attribute13 IS NULL)))
374 AND ((recinfo.attribute14 = x_attribute14)
375 OR ((recinfo.attribute14 IS NULL) AND (x_attribute14 IS NULL)))
376 AND ((recinfo.attribute15 = x_attribute15)
377 OR ((recinfo.attribute15 IS NULL) AND (x_attribute15 IS NULL)))
378 AND ((recinfo.start_active_date = x_start_active_date)
379 OR ((recinfo.start_active_date IS NULL) AND (x_start_active_date IS NULL)))
380 AND ((recinfo.end_active_date = x_end_active_date)
381 OR ((recinfo.end_active_date IS NULL) AND (x_end_active_date IS NULL)))
382 AND ((recinfo.content_type = x_content_type)
383 OR ((recinfo.content_type IS NULL) AND (x_content_type IS NULL)))
384 ) THEN
385 NULL;
386 ELSE
387 FND_MESSAGE.set_name('FND', 'FORM_RECORD_CHANGED');
388 APP_EXCEPTION.raise_exception;
389 END IF;
390
391 FOR tlinfo IN c1 LOOP
392 IF (tlinfo.baselang = 'Y') THEN
393 IF ( ((tlinfo.name = x_name)
394 OR ((tlinfo.name IS NULL) AND (x_name IS NULL)))
395 AND ((
396 DBMS_LOB.compare(x_description, tlinfo.description,
397 DBMS_LOB.getlength(x_description),1,1)=0 )
398 OR ((tlinfo.description IS NULL) AND (x_description IS NULL)))
399 ) THEN
400 NULL;
401 ELSE
402 FND_MESSAGE.set_name('FND', 'FORM_RECORD_CHANGED');
403 APP_EXCEPTION.raise_exception;
404 END IF;
405 END IF;
406 END LOOP;
407 RETURN;
408
412 PROCEDURE Update_Row (
409 END Lock_Row;
410
411
413 X_ELEMENT_ID IN NUMBER,
414 X_ELEMENT_NUMBER IN VARCHAR2,
415 X_ELEMENT_TYPE_ID IN NUMBER,
416 X_ELEMENT_NAME IN VARCHAR2,
417 X_GROUP_FLAG IN NUMBER,
418 X_STATUS IN VARCHAR2,
419 X_ACCESS_LEVEL IN NUMBER,
420 X_NAME IN VARCHAR2,
421 X_DESCRIPTION IN CLOB,
422 X_LAST_UPDATE_DATE IN DATE,
423 X_LAST_UPDATED_BY IN NUMBER,
424 X_LAST_UPDATE_LOGIN IN NUMBER,
425 X_LOCKED_BY IN NUMBER,
426 X_LOCK_DATE IN DATE,
427 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
428 X_ATTRIBUTE1 IN VARCHAR2,
429 X_ATTRIBUTE2 IN VARCHAR2,
430 X_ATTRIBUTE3 IN VARCHAR2,
431 X_ATTRIBUTE4 IN VARCHAR2,
432 X_ATTRIBUTE5 IN VARCHAR2,
433 X_ATTRIBUTE6 IN VARCHAR2,
434 X_ATTRIBUTE7 IN VARCHAR2,
435 X_ATTRIBUTE8 IN VARCHAR2,
436 X_ATTRIBUTE9 IN VARCHAR2,
437 X_ATTRIBUTE10 IN VARCHAR2,
438 X_ATTRIBUTE11 IN VARCHAR2,
439 X_ATTRIBUTE12 IN VARCHAR2,
440 X_ATTRIBUTE13 IN VARCHAR2,
441 X_ATTRIBUTE14 IN VARCHAR2,
442 X_ATTRIBUTE15 IN VARCHAR2,
443 X_START_ACTIVE_DATE IN DATE,
444 X_END_ACTIVE_DATE IN DATE,
445 X_CONTENT_TYPE IN VARCHAR2 )
446 IS
447
448 l_access_level CS_KB_ELEMENTS_B.ACCESS_LEVEL%TYPE;
449 l_content_type CS_KB_ELEMENTS_B.CONTENT_TYPE%TYPE;
450
451 l_srclen INTEGER :=0;
452 l_destlen INTEGER :=0;
453
454 CURSOR c_tl(c_id IN NUMBER) IS
455 SELECT element_id,
456 language,
457 description
458 FROM CS_KB_ELEMENTS_TL
459 WHERE element_id = c_id
460 AND USERENV('LANG') IN (language, source_lang) FOR UPDATE;
461
462 BEGIN
463 IF x_access_level IS NULL THEN
464 FND_PROFILE.GET('CS_KB_SMP_SOL_ACCESS_LEVEL', l_access_level);
465
466 IF l_access_level is null THEN
467 l_access_level := 3000;
468 END IF;
469
470 ELSE
471 l_access_level := x_access_level;
472 END IF;
473
474 IF x_content_type IS NULL THEN
475 FND_PROFILE.GET('CS_KB_SMP_SOL_CONTENT_TYPE', l_content_type);
476
477 IF l_content_type is null THEN
478 l_content_type := 'TEXT/HTML';
479 END IF;
480
481 ELSE
482 l_content_type := x_content_type;
483 END IF;
484
485 UPDATE CS_KB_ELEMENTS_B SET
486 element_type_id = x_element_type_id,
487 element_name = x_element_name,
488 group_flag = x_group_flag,
489 status = x_status,
490 access_level = l_access_level,
491 last_update_date = x_last_update_date,
492 last_updated_by = x_last_updated_by,
493 last_update_login = x_last_update_login,
494 locked_by = x_locked_by,
495 lock_date = x_lock_date,
496 attribute_category = x_attribute_category,
497 attribute1 = x_attribute1,
498 attribute2 = x_attribute2,
499 attribute3 = x_attribute3,
500 attribute4 = x_attribute4,
501 attribute5 = x_attribute5,
502 attribute6 = x_attribute6,
503 attribute7 = x_attribute7,
504 attribute8 = x_attribute8,
505 attribute9 = x_attribute9,
506 attribute10 = x_attribute10,
507 attribute11 = x_attribute11,
508 attribute12 = x_attribute12,
509 attribute13 = x_attribute13,
510 attribute14 = x_attribute14,
511 attribute15 = x_attribute15,
512 start_active_date = x_start_active_date,
513 end_active_date = x_end_active_date,
514 content_type = l_content_type
515 WHERE element_id = x_element_id;
516 IF (SQL%NOTFOUND) THEN
517 RAISE NO_DATA_FOUND;
518 END IF;
519
520 UPDATE CS_KB_ELEMENTS_TL SET
521 name = x_name,
522 description = EMPTY_CLOB(),
523 last_update_date = x_last_update_date,
524 last_updated_by = x_last_updated_by,
525 last_update_login = x_last_update_login,
526 source_lang = USERENV('LANG')
527 WHERE element_id = x_element_id
528 AND USERENV('LANG') IN (language, source_lang);
529
530 IF(x_description IS NOT NULL AND
531 DBMS_LOB.getlength(x_description)>0) THEN
532 l_srclen := DBMS_LOB.getlength(x_description);
533
534 FOR rectl IN c_tl(x_element_id) LOOP
535 DBMS_LOB.copy(rectl.description, x_description, l_srclen, 1,1);
536 END LOOP;
537
538 END IF;
539
540 IF (SQL%NOTFOUND) THEN
541 RAISE NO_DATA_FOUND;
542 END IF;
543
544 END Update_Row;
545
546
547 PROCEDURE Delete_Row (
548 X_ELEMENT_NUMBER IN VARCHAR2)
549 IS
550
551 CURSOR c IS
552 SELECT element_id
553 FROM CS_KB_ELEMENTS_B
554 WHERE element_number = x_element_number;
555
556 BEGIN
557
558 FOR rec IN c LOOP
559
560 DELETE FROM CS_KB_ELEMENTS_TL
561 WHERE element_id = rec.element_id;
562
563 END LOOP;
564
565 DELETE FROM CS_KB_ELEMENTS_B
566 WHERE element_number = x_element_number;
567
568 END Delete_Row;
569
573
570 PROCEDURE Add_Language
571 IS
572 BEGIN
574 DELETE FROM CS_KB_ELEMENTS_TL t
575 WHERE NOT EXISTS (SELECT NULL
576 FROM CS_KB_ELEMENTS_B b
577 WHERE b.element_id = t.element_id );
578
579 UPDATE CS_KB_ELEMENTS_TL t
580 SET ( name,
581 description
582 ) = (SELECT b.name,
583 b.description
584 FROM CS_KB_ELEMENTS_TL b
585 WHERE b.element_id = t.element_id
586 AND b.language = t.source_lang )
587 WHERE ( t.element_id,
588 t.language) IN (SELECT subt.element_id,
589 subt.language
590 FROM CS_KB_ELEMENTS_TL subb,
591 CS_KB_ELEMENTS_TL subt
592 WHERE subb.element_id = subt.element_id
593 AND subb.language = subt.source_lang
594 AND (subb.name <> subt.name
595 OR (subb.name IS NULL AND subt.name IS NOT NULL)
596 OR (subb.name IS NOT NULL AND subt.name IS NULL)
597 OR DBMS_LOB.compare(subb.description, subt.description,
598 DBMS_LOB.getlength(subb.description), 1,1)<>0
599 OR (subb.description IS NULL AND subt.description IS NOT NULL)
600 OR (subb.description IS NOT NULL AND subt.description IS NULL)
601 ));
602
603 INSERT INTO CS_KB_ELEMENTS_TL (
604 element_id,
605 name,
606 description,
607 creation_date,
608 created_by,
609 last_update_date,
610 last_updated_by,
611 last_update_login,
612 language,
613 source_lang
614 ) SELECT
615 b.element_id,
616 b.name,
617 b.description,
618 b.creation_date,
619 b.created_by,
620 b.last_update_date,
621 b.last_updated_by,
622 b.last_update_login,
623 l.language_code,
624 b.source_lang
625 FROM CS_KB_ELEMENTS_TL b, fnd_languages l
626 WHERE l.installed_flag IN ('I', 'B')
627 AND b.language = USERENV('LANG')
628 AND NOT EXISTS
629 (SELECT NULL
630 FROM CS_KB_ELEMENTS_TL t
631 WHERE t.element_id = b.element_id
632 AND t.language = l.language_code);
633
634 END Add_Language;
635
636
637 PROCEDURE Translate_Row(
638 X_ELEMENT_ID IN NUMBER,
639 X_ELEMENT_NUMBER IN VARCHAR2,
640 X_OWNER IN VARCHAR2,
641 X_NAME IN VARCHAR2,
642 X_DESCRIPTION IN VARCHAR2)
643 IS
644
645 l_srclen integer :=0;
646 l_destlen integer :=0;
647
648 CURSOR c_tl(c_id IN NUMBER) IS
649 SELECT
650 element_id,
651 language,
652 description
653 FROM CS_KB_ELEMENTS_TL
654 WHERE element_id = c_id
655 AND USERENV('LANG') IN (language, source_lang) FOR UPDATE;
656
657 l_user_id NUMBER;
658 l_clob clob := NULL;
659 l_offset NUMBER;
660 l_amt NUMBER;
661
662 BEGIN
663 -- write desc to temporary clob
664 IF(x_description IS NOT NULL ) THEN
665 DBMS_LOB.createtemporary(l_clob, true, DBMS_LOB.session);
666 l_offset := 1;
667 l_amt := length(x_description);
668 DBMS_LOB.write(l_clob, l_amt, l_offset, x_description);
669 END IF;
670
671 -- UPDATE translated non-clob portions FOR specified language
672 UPDATE CS_KB_ELEMENTS_TL SET
673 name = x_name,
674 last_update_date = SYSDATE,
675 last_updated_by = decode(x_owner, 'SEED', 1, 0),
676 last_update_login = 0,
677 source_lang = USERENV('LANG')
678 WHERE element_id = TO_NUMBER(x_element_id) --change
679 AND USERENV('LANG') IN (language, source_lang);
680
681 --copy given clob INTO clob_locator FOR current language.
682 FOR rectl IN c_tl(x_element_id) LOOP
683 l_srclen := 0;
684 IF(x_description IS NOT NULL AND
685 DBMS_LOB.getlength(l_clob)>0) THEN
686 l_srclen := DBMS_LOB.getlength(l_clob);
687 END IF;
688
689 l_destlen := DBMS_LOB.getlength(rectl.description);
690
691 IF(l_destlen > l_srclen) THEN
692 DBMS_LOB.trim(rectl.description, l_srclen);
693 END IF;
694
695 IF(x_description IS NOT NULL AND
696 DBMS_LOB.getlength(l_clob)>0) THEN
697 --DBMS_LOB.copy(rectl.description, l_clob, DBMS_LOB.lobmaxsize, 1,1);
698 DBMS_LOB.copy(rectl.description, l_clob, l_srclen, 1,1);
699 END IF;
700 END LOOP;
701
702 IF(x_description IS NOT NULL) THEN
703 DBMS_LOB.freetemporary(l_clob);
704 END IF;
705
706 EXCEPTION
707 WHEN NO_DATA_FOUND THEN
708 NULL;
709 END Translate_Row;
710
711
712 PROCEDURE Load_Row(
713 X_ELEMENT_ID IN NUMBER,
714 X_ELEMENT_NUMBER IN VARCHAR2,
715 X_ELEMENT_TYPE_ID IN NUMBER,
716 X_STATUS IN VARCHAR2,
717 X_ACCESS_LEVEL IN NUMBER,
718 X_OWNER IN VARCHAR2,
719 X_NAME IN VARCHAR2,
720 X_DESCRIPTION IN VARCHAR2)
721 IS
722
723 l_user_id NUMBER;
724 l_rowid VARCHAR2(100);
725 l_clob clob := NULL;
726 l_offset NUMBER;
727 l_amt NUMBER;
728 l_locked_by NUMBER;
729 l_lock_date DATE;
730
731 BEGIN
732
733 IF (x_owner = 'SEED') THEN
734 l_user_id := 1;
735 ELSE
736 l_user_id := 0;
737 END IF;
738
739 -- write desc to clob
740
744 l_offset := 1;
741 IF( x_description IS NOT NULL) THEN
742
743 DBMS_LOB.createtemporary(l_clob, true, DBMS_LOB.session);
745 l_amt := length(x_description);
746 DBMS_LOB.write(l_clob, l_amt, l_offset, x_description);
747
748 END IF;
749
750 Update_Row(
751 X_ELEMENT_ID => x_element_id,
752 X_ELEMENT_NUMBER => x_element_number,
753 X_ELEMENT_TYPE_ID => x_element_type_id,
754 X_ELEMENT_NAME => NULL,
755 X_GROUP_FLAG => NULL,
756 X_STATUS => x_status,
757 X_ACCESS_LEVEL => x_access_level,
758 X_NAME => x_name,
759 X_DESCRIPTION => l_clob,
760 X_LAST_UPDATE_DATE => SYSDATE,
761 X_LAST_UPDATED_BY => l_user_id,
762 X_LAST_UPDATE_LOGIN => 0,
763 X_LOCKED_BY => null,
764 X_LOCK_DATE => null);
765
766 EXCEPTION
767 WHEN NO_DATA_FOUND THEN
768 Insert_Row(
769 x_rowid => l_rowid,
770 x_element_id => x_element_id,
771 x_element_number => x_element_number,
772 x_element_type_id => x_element_type_id,
773 x_element_name => NULL,
774 x_group_flag => NULL,
775 x_status => x_status,
776 x_access_level => x_access_level,
777 x_name => x_name,
778 x_description => l_clob,
779 x_creation_date => SYSDATE,
780 x_created_by => l_user_id,
781 x_last_update_date => SYSDATE,
782 x_last_updated_by => l_user_id,
783 x_last_update_login => 0,
784 x_locked_by => NULL,
785 x_lock_date => NULL);
786
787 IF(x_description IS NOT NULL) THEN
788 DBMS_LOB.freetemporary(l_clob);
789 END IF;
790
791 END Load_Row;
792
793 -- 13-Jan-2004 MK
794 -- Added api back as required by import program
795 PROCEDURE Update_Clobs(
796 P_ELEMENT_ID IN NUMBER)
797 IS
798 l_srclen integer :=0;
799 l_destlen integer :=0;
800 l_clob clob;
801
802 l_sysdate DATE := sysdate;
803 l_user_id NUMBER(15) := fnd_global.user_id;
804 l_login_id NUMBER(15) := fnd_global.login_id;
805
806 CURSOR C_TL IS
807 SELECT
808 description
809 FROM CS_KB_ELEMENTS_TL
810 WHERE element_id = p_element_id
811 AND userenv('LANG') = SOURCE_LANG
812 AND userenv('LANG') <> LANGUAGE
813 FOR UPDATE;
814
815 CURSOR c_desc IS
816 SELECT description
817 FROM cs_kb_elements_tl
818 WHERE element_id = p_element_id
819 AND language = userenv('LANG');
820 BEGIN
821
822 OPEN c_desc;
823 FETCH c_desc INTO l_clob;
824 CLOSE c_desc;
825
826 IF (sql%notfound) THEN
827 RETURN;
828 END IF;
829
830 UPDATE CS_KB_ELEMENTS_TL SET
831 description = EMPTY_CLOB() ,
832 last_update_date = l_sysdate,
833 last_updated_by = l_user_id,
834 last_update_login = l_login_id
835 WHERE element_id = p_element_id
836 AND userenv('LANG') = SOURCE_LANG
837 AND userenv('LANG') <> LANGUAGE;
838
839 FOR recTL IN C_TL LOOP
840 l_srclen := 0;
841 IF (l_clob IS NOT NULL AND
842
843 dbms_lob.getlength(l_clob)>0) THEN
844 l_srclen := dbms_lob.getlength(l_clob);
845 dbms_lob.copy(recTL.description, l_clob, l_srclen, 1,1);
846
847 END IF;
848
849 END LOOP;
850
851 END Update_Clobs;
852
853 END CS_KB_ELEMENTS_PKG;