[Home] [Help]
PACKAGE BODY: APPS.CS_KB_ELEMENTS_AUDIT_PKG
Source
1 PACKAGE BODY CS_KB_ELEMENTS_AUDIT_PKG AS
2 /* $Header: cskbelab.pls 120.1 2005/08/09 12:10:18 mkettle noship $ */
3 /*======================================================================+
4 | Copyright (c) 1999 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +======================================================================+
8 | History
9 | 01-APR-2001 Bate Yu created
10 | 14-AUG-2002 KLOU (SEDATE)
11 | 1. Add logic in create_element_CLOB and
12 | update_element_CLOB to validate statement_type.
13 | 24-JAN-2003 MKETTLE Added Update_Statement
14 | 29-JUL-2003 MKETTLE Added Update_Statement_Admin to be used by the
15 | Global Statement Update in OA
16 | 24-SEP-2003 MKETTLE Added ASAP indexing to Update_Statement_Admin
17 | 06-OCT-2003 MKETTLE Changed Update_Statement_Admin to cater for a
18 | duplicate found within the same solution
19 | 31-OCT-2003 MKETTLE In GSU before creating a new Statement check
20 | if the Statement has changed first
21 | 18-Nov-2003 MKETTLE Cleanup for 11.5.10
22 | - Obsolete unused apis
23 | - Moved table Handlers to ELEMENTS_PKG
24 | 25-Nov-2003 MKETTLE Added Obsolete_Unused_Statements
25 | 21-Apr-2004 MKETTLE Fix for Bug 3576066
26 | 22-Apr-2004 MKETTLE Added rollback/savepoint: Update_Statement_Admin
27 | 21-Apr-2005 MKETTLE Commented Cursor Check_Statement_Cat_Grp_Usage in
28 | Update_Statement_Admin, since not used (Perf Rep)
29 | 17-May-2005 MKETTLE Cleanup - Removed unused apis + cursors
30 | apis removed in 115.52:
31 | Get_Previous_Version_id
32 | Get_Lock_Info
33 | Locked_By
34 | Incr_Element_Element
35 | 09-Aug-2005 MKETTLE Resized Elements_Tl.Name variable to 2000 in
36 | api Is_Element_Created_Dup
37 +======================================================================*/
38
39 -- return other_element_id if duplicate can be reused
40 -- return 0 if no duplicate found
41 FUNCTION Is_Element_Created_Dup(
42 P_ELEMENT_ID IN NUMBER)
43 RETURN NUMBER
44 IS
45 l_element_number VARCHAR(30);
46 l_element_type_id NUMBER(15);
47 l_access_level NUMBER(4);
48 l_name VARCHAR2(2000);
49 l_desc CLOB;
50
51 BEGIN
52 SELECT b.element_number, b.element_type_id, b.access_level, tl.name, tl.description
53 INTO l_element_number, l_element_type_id, l_access_level, l_name, l_desc
54 FROM CS_KB_ELEMENTS_B b,
55 CS_KB_ELEMENTS_TL tl
56 WHERE b.element_id = tl.element_id
57 AND tl.language = USERENV('LANG')
58 AND b.element_id = p_element_id;
59
60 RETURN Is_Element_Dup ( l_element_number,
61 l_access_level,
62 l_element_type_id,
63 l_name,
64 l_desc);
65
66 END Is_Element_Created_Dup;
67
68
69 -- return other_element_id if duplicate can be reused
70 -- return 0 if no duplicate found
71 FUNCTION Is_Element_Dup (
72 P_ELEMENT_NUMBER VARCHAR2,
73 P_ACCESS_LEVEL NUMBER,
74 P_ELEMENT_TYPE_ID NUMBER,
75 P_ELEMENT_NAME VARCHAR2,
76 P_ELEMENT_DESC CLOB)
77 RETURN NUMBER
78 IS
79 CURSOR cur_ele_name (c_element_number IN VARCHAR2,
80 c_name IN VARCHAR2,
81 c_element_type_id IN NUMBER,
82 c_access_level IN NUMBER ) IS
83 SELECT tl.element_id,
84 tl.description,
85 b.status
86 FROM CS_KB_ELEMENTS_TL tl,
87 CS_KB_ELEMENTS_B b
88 WHERE tl.name = c_name
89 AND tl.language = USERENV('LANG')
90 AND tl.element_id = b.element_id
91 AND b.element_number <> c_element_number
92 AND b.status = 'PUBLISHED'
93 AND b.element_type_id = c_element_type_id
94 AND b.access_level = c_access_level;
95
96 BEGIN
97
98 FOR rec in cur_ele_name (p_element_number,
99 p_element_name,
100 p_element_type_id,
101 p_access_level) LOOP
102
103 IF DBMS_LOB.GETLENGTH(p_element_desc) > 0 AND
104 rec.description IS NULL THEN
105
106 NULL;
107
108 ELSIF (p_element_desc IS NULL AND rec.description IS NULL) OR
109 (p_element_desc IS NULL AND (DBMS_LOB.GETLENGTH(rec.description) = 0) ) OR
110 ((DBMS_LOB.GETLENGTH(p_element_desc) = 0) AND rec.description IS NULL ) OR
111 (DBMS_LOB.COMPARE(p_element_desc, rec.description) = 0 ) THEN
112
113 RETURN rec.element_id;
114
115 END IF;
116
117 END LOOP;
118
119 RETURN 0;
120
121 END Is_Element_Dup;
122
123
124 -- return other_element_id if duplicate can be reused
125 -- return 0 if no duplicate found
126 FUNCTION Is_Element_Updated_Dup(
127 P_ELEMENT_ID IN NUMBER)
128 RETURN NUMBER
129 IS
130 BEGIN
131 RETURN Is_Element_Created_Dup(p_element_id);
132 END Is_Element_Updated_Dup;
133
134
135 FUNCTION Get_Element_Number(
136 P_ELEMENT_ID IN NUMBER)
137 RETURN VARCHAR2
138 IS
139 l_element_number VARCHAR2(30);
140
141 BEGIN
142
143 SELECT element_number
144 INTO l_element_number
145 FROM CS_KB_ELEMENTS_B
146 WHERE element_id = p_element_id;
147
148 RETURN l_element_number;
149
150 END Get_Element_Number;
151
152
153 FUNCTION Get_Latest_Version_Id(
154 P_ELEMENT_NUMBER IN VARCHAR2)
155 RETURN NUMBER IS
156
157 l_latest_version_id NUMBER;
158
159 BEGIN
160 SELECT MAX(element_id)
161 INTO l_latest_version_id
162 FROM CS_KB_ELEMENTS_B
163 WHERE element_number = p_element_number;
164 IF (SQL%NOTFOUND) THEN
165 RAISE NO_DATA_FOUND;
166 END IF;
167
168 RETURN l_latest_version_id;
169
170 END Get_Latest_Version_Id;
171
172
173 PROCEDURE Get_Who(
174 X_SYSDATE OUT NOCOPY DATE,
175 X_USER_ID OUT NOCOPY NUMBER,
176 X_LOGIN_ID OUT NOCOPY NUMBER)
177 IS
178 BEGIN
179 x_sysdate := SYSDATE;
180 x_user_id := FND_GLOBAL.user_id;
181 x_login_id := FND_GLOBAL.login_id;
182 END Get_Who;
183
184
185 --
186 -- create element. RETURNs element_id.
187 -- accepts VARCHAR2
188 -- use that to create clob AND call Create_Element_CLOB
189 FUNCTION Create_Element(
190 P_ELEMENT_TYPE_ID IN NUMBER,
191 P_DESC IN VARCHAR2,
192 P_NAME IN VARCHAR2,
193 P_STATUS IN VARCHAR2,
194 P_ACCESS_LEVEL IN NUMBER,
195 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
196 P_ATTRIBUTE1 IN VARCHAR2,
197 P_ATTRIBUTE2 IN VARCHAR2,
198 P_ATTRIBUTE3 IN VARCHAR2,
199 P_ATTRIBUTE4 IN VARCHAR2,
200 P_ATTRIBUTE5 IN VARCHAR2,
201 P_ATTRIBUTE6 IN VARCHAR2,
202 P_ATTRIBUTE7 IN VARCHAR2,
203 P_ATTRIBUTE8 IN VARCHAR2,
204 P_ATTRIBUTE9 IN VARCHAR2,
205 P_ATTRIBUTE10 IN VARCHAR2,
206 P_ATTRIBUTE11 IN VARCHAR2,
207 P_ATTRIBUTE12 IN VARCHAR2,
208 P_ATTRIBUTE13 IN VARCHAR2,
209 P_ATTRIBUTE14 IN VARCHAR2,
210 P_ATTRIBUTE15 IN VARCHAR2,
211 P_START_ACTIVE_DATE IN DATE,
212 P_END_ACTIVE_DATE IN DATE,
213 P_CONTENT_TYPE IN VARCHAR2 )
214 RETURN NUMBER
215 IS
216 l_offset NUMBER;
217 l_amt NUMBER;
218 l_clob CLOB;
219 l_element_id NUMBER;
220 l_date DATE;
221 l_created_by NUMBER;
222 l_login NUMBER;
223 l_rowid VARCHAR2(30);
224
225 BEGIN
226 -- check params
227 IF(p_element_type_id IS NULL OR p_name IS NULL) THEN
228 RETURN -1;
229 END IF;
230
231 IF(p_desc IS NOT NULL) THEN
232 DBMS_LOB.createtemporary(l_clob, true, DBMS_LOB.session);
233 l_offset := 1;
234 l_amt := length(p_desc);
235 DBMS_LOB.write(l_clob, l_amt, l_offset, p_desc);
236 END IF;
237
238 l_element_id := Create_Element_CLOB(
239 p_element_type_id => p_element_type_id,
240 p_desc => l_clob,
241 p_name => p_name,
242 p_status => p_status,
243 p_access_level => p_access_level,
244 p_attribute_category => p_attribute_category,
245 p_attribute1 => p_attribute1,
246 p_attribute2 => p_attribute2,
247 p_attribute3 => p_attribute3,
248 p_attribute4 => p_attribute4,
249 p_attribute5 => p_attribute5,
250 p_attribute6 => p_attribute6,
251 p_attribute7 => p_attribute7,
252 p_attribute8 => p_attribute8,
253 p_attribute9 => p_attribute9,
254 p_attribute10 => p_attribute10,
255 p_attribute11 => p_attribute11,
256 p_attribute12 => p_attribute12,
257 p_attribute13 => p_attribute13,
258 p_attribute14 => p_attribute14,
259 p_attribute15 => p_attribute15,
260 p_start_active_date => p_start_active_date,
261 p_end_active_date => p_end_active_date,
262 p_content_type => p_content_type );
263
264 IF(p_desc IS NOT NULL) THEN
265 DBMS_LOB.freetemporary(l_clob);
266 END IF;
267
268 RETURN l_element_id;
269
270 EXCEPTION
271 WHEN FND_API.g_exc_error THEN
272 RETURN ERROR_STATUS;
273 WHEN others THEN
274 IF(l_clob IS NOT NULL) THEN
275 DBMS_LOB.freetemporary(l_clob);
276 END IF;
277 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
278 RETURN ERROR_STATUS;
279
280 END Create_Element;
281
282
283 --
284 -- create element given element_type_id AND desc clob
285 -- other params are NOT used FOR now.
286 -- IF error, RETURNs error.
287 --
288 FUNCTION Create_Element_CLOB(
289 P_ELEMENT_TYPE_ID IN NUMBER,
290 P_DESC IN CLOB,
291 P_NAME IN VARCHAR2,
292 P_STATUS IN VARCHAR2,
293 P_ACCESS_LEVEL IN NUMBER,
294 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
295 P_ATTRIBUTE1 IN VARCHAR2,
296 P_ATTRIBUTE2 IN VARCHAR2,
297 P_ATTRIBUTE3 IN VARCHAR2,
298 P_ATTRIBUTE4 IN VARCHAR2,
299 P_ATTRIBUTE5 IN VARCHAR2,
300 P_ATTRIBUTE6 IN VARCHAR2,
301 P_ATTRIBUTE7 IN VARCHAR2,
302 P_ATTRIBUTE8 IN VARCHAR2,
303 P_ATTRIBUTE9 IN VARCHAR2,
304 P_ATTRIBUTE10 IN VARCHAR2,
305 P_ATTRIBUTE11 IN VARCHAR2,
306 P_ATTRIBUTE12 IN VARCHAR2,
307 P_ATTRIBUTE13 IN VARCHAR2,
308 P_ATTRIBUTE14 IN VARCHAR2,
309 P_ATTRIBUTE15 IN VARCHAR2,
310 P_START_ACTIVE_DATE IN DATE,
311 P_END_ACTIVE_DATE IN DATE,
312 P_CONTENT_TYPE IN VARCHAR2 )
313 RETURN NUMBER
314 IS
315
316 l_element_id NUMBER;
317 l_return_status VARCHAR2(1);
318 l_msg_data VARCHAR2(2000);
319 l_msg_count NUMBER;
320
321 BEGIN
322
323 l_element_id := null;
324
325 Create_Statement( x_element_id => l_element_id,
326 p_element_type_id => p_element_type_id,
327 p_name => p_name,
328 p_desc => p_desc,
329 p_status => p_status,
330 p_access_level => p_access_level,
331 p_attribute_category => p_attribute_category,
332 p_attribute1 => p_attribute1,
333 p_attribute2 => p_attribute2,
334 p_attribute3 => p_attribute3,
335 p_attribute4 => p_attribute4,
336 p_attribute5 => p_attribute5,
337 p_attribute6 => p_attribute6,
338 p_attribute7 => p_attribute7,
339 p_attribute8 => p_attribute8,
340 p_attribute9 => p_attribute9,
341 p_attribute10 => p_attribute10,
342 p_attribute11 => p_attribute11,
343 p_attribute12 => p_attribute12,
344 p_attribute13 => p_attribute13,
345 p_attribute14 => p_attribute14,
346 p_attribute15 => p_attribute15,
347 p_start_active_date => p_start_active_date,
348 p_end_active_date => p_end_active_date,
349 p_content_type => p_content_type,
350 x_return_status => l_return_status,
351 x_msg_data => l_msg_data,
352 x_msg_count => l_msg_count);
353
354 RETURN l_element_id;
355
356 EXCEPTION
357 WHEN OTHERS THEN
358 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
359 RETURN ERROR_STATUS;
360 END Create_Element_CLOB;
361
362
363 FUNCTION Update_Element(
364 P_ELEMENT_ID IN NUMBER,
365 P_ELEMENT_NUMBER IN VARCHAR2,
366 P_ELEMENT_TYPE_ID IN NUMBER,
367 P_DESC IN VARCHAR2,
368 P_NAME IN VARCHAR2,
369 P_STATUS IN VARCHAR2,
370 P_ACCESS_LEVEL IN NUMBER,
371 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
372 P_ATTRIBUTE1 IN VARCHAR2,
373 P_ATTRIBUTE2 IN VARCHAR2,
374 P_ATTRIBUTE3 IN VARCHAR2,
375 P_ATTRIBUTE4 IN VARCHAR2,
376 P_ATTRIBUTE5 IN VARCHAR2,
377 P_ATTRIBUTE6 IN VARCHAR2,
378 P_ATTRIBUTE7 IN VARCHAR2,
379 P_ATTRIBUTE8 IN VARCHAR2,
380 P_ATTRIBUTE9 IN VARCHAR2,
381 P_ATTRIBUTE10 IN VARCHAR2,
382 P_ATTRIBUTE11 IN VARCHAR2,
383 P_ATTRIBUTE12 IN VARCHAR2,
384 P_ATTRIBUTE13 IN VARCHAR2,
385 P_ATTRIBUTE14 IN VARCHAR2,
386 P_ATTRIBUTE15 IN VARCHAR2,
387 P_START_ACTIVE_DATE IN DATE,
388 P_END_ACTIVE_DATE IN DATE,
389 P_CONTENT_TYPE IN VARCHAR2 )
390 RETURN NUMBER
391 IS
392 l_offset NUMBER;
393 l_amt NUMBER;
394 l_clob CLOB;
395 l_ret NUMBER;
396 l_date DATE;
397 l_updated_by NUMBER;
398 l_login NUMBER;
399 --l_count PLS_INTEGER;
400 BEGIN
401 -- validate params
402 IF (p_element_number IS NULL) OR ( NOT p_element_type_id > 0) THEN
403 RETURN -1;
404 END IF;
405
406 -- write desc to clob
407 IF(p_desc IS NOT NULL) THEN
408 DBMS_LOB.createtemporary(l_clob, true, DBMS_LOB.session);
409 l_offset := 1;
410 l_amt := length(p_desc);
411 DBMS_LOB.write(l_clob, l_amt, l_offset, p_desc);
412 END IF;
413
414 l_ret := Update_Element_CLOB(
415 P_ELEMENT_ID => p_element_id,
416 P_ELEMENT_NUMBER => p_element_number,
417 P_ELEMENT_TYPE_ID => p_element_type_id,
418 P_DESC => l_clob,
419 P_NAME => p_name,
420 P_STATUS => p_status,
421 P_ACCESS_LEVEL => p_access_level,
422 P_ATTRIBUTE_CATEGORY => p_attribute_category,
423 P_ATTRIBUTE1 => p_attribute1,
424 P_ATTRIBUTE2 => p_attribute2,
425 P_ATTRIBUTE3 => p_attribute3,
426 P_ATTRIBUTE4 => p_attribute4,
427 P_ATTRIBUTE5 => p_attribute5,
431 P_ATTRIBUTE9 => p_attribute9,
428 P_ATTRIBUTE6 => p_attribute6,
429 P_ATTRIBUTE7 => p_attribute7,
430 P_ATTRIBUTE8 => p_attribute8,
432 P_ATTRIBUTE10 => p_attribute10,
433 P_ATTRIBUTE11 => p_attribute11,
434 P_ATTRIBUTE12 => p_attribute12,
435 P_ATTRIBUTE13 => p_attribute13,
436 P_ATTRIBUTE14 => p_attribute14,
437 P_ATTRIBUTE15 => p_attribute15,
438 P_START_ACTIVE_DATE => p_start_active_date,
439 P_END_ACTIVE_DATE => p_end_active_date,
440 P_CONTENT_TYPE => p_content_type );
441
442 IF(p_desc IS NOT NULL) THEN
443 DBMS_LOB.freetemporary(l_clob);
444 END IF;
445
446 RETURN l_ret;
447 EXCEPTION
448 WHEN others THEN
449 IF(l_clob IS NOT NULL) THEN
450 DBMS_LOB.freetemporary(l_clob);
451 END IF;
452 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
453 RETURN ERROR_STATUS;
454 END Update_Element;
455
456
457 FUNCTION Update_Element_CLOB(
458 p_element_id IN NUMBER,
459 p_element_number IN VARCHAR2,
460 p_element_type_id IN NUMBER,
461 p_desc IN CLOB,
462 p_name IN VARCHAR2,
463 p_status IN VARCHAR2,
464 p_access_level IN NUMBER,
465 p_attribute_category IN VARCHAR2,
466 p_attribute1 IN VARCHAR2,
467 p_attribute2 IN VARCHAR2,
468 p_attribute3 IN VARCHAR2,
469 p_attribute4 IN VARCHAR2,
470 p_attribute5 IN VARCHAR2,
471 p_attribute6 IN VARCHAR2,
472 p_attribute7 IN VARCHAR2,
473 p_attribute8 IN VARCHAR2,
474 p_attribute9 IN VARCHAR2,
475 p_attribute10 IN VARCHAR2,
476 p_attribute11 IN VARCHAR2,
477 p_attribute12 IN VARCHAR2,
478 p_attribute13 IN VARCHAR2,
479 p_attribute14 IN VARCHAR2,
480 p_attribute15 IN VARCHAR2,
481 p_start_active_date IN DATE,
482 p_end_active_date IN DATE,
483 p_content_type IN VARCHAR2 )
484 RETURN NUMBER
485 IS
486
487 l_return NUMBER;
488 l_return_status VARCHAR2(1);
489 l_msg_data VARCHAR2(2000);
490 l_msg_count NUMBER;
491
492 BEGIN
493 Update_Statement(
494 p_element_id => p_element_id,
495 p_element_number => p_element_number,
496 p_element_type_id => p_element_type_id,
497 p_desc => p_desc,
498 p_name => p_name,
499 p_status => p_status,
500 p_access_level => p_access_level,
501 p_attribute_category => p_attribute_category,
502 p_attribute1 => p_attribute1,
503 p_attribute2 => p_attribute2,
504 p_attribute3 => p_attribute3,
505 p_attribute4 => p_attribute4,
506 p_attribute5 => p_attribute5,
507 p_attribute6 => p_attribute6,
508 p_attribute7 => p_attribute7,
509 p_attribute8 => p_attribute8,
510 p_attribute9 => p_attribute9,
511 p_attribute10 => p_attribute10,
512 p_attribute11 => p_attribute11,
513 p_attribute12 => p_attribute12,
514 p_attribute13 => p_attribute13,
515 p_attribute14 => p_attribute14,
516 p_attribute15 => p_attribute15,
517 p_start_active_date => p_start_active_date,
518 p_end_active_date => p_end_active_date,
519 p_content_type => p_content_type,
520 x_return => l_return,
521 x_return_status => l_return_status,
522 x_msg_data => l_msg_data,
523 x_msg_count => l_msg_count);
524
525 RETURN l_return;
526
527 EXCEPTION
528 WHEN others THEN
529 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
530 RETURN ERROR_STATUS;
531 END Update_Element_CLOB;
532
533
534 --
535 -- DELETE element
536 -- RETURNs error IF element IS used by some solution
537 -- OR IF element linked to external
538 --
539 FUNCTION Delete_Element(
540 P_ELEMENT_NUMBER IN VARCHAR2)
541 RETURN NUMBER
542 IS
543 l_ret NUMBER;
544 l_count PLS_INTEGER;
545 BEGIN
546 IF p_element_number IS NULL THEN
547 FND_MESSAGE.set_name('CS', 'CS_KB_C_MISS_PARAM');
548 RETURN ERROR_STATUS;
549 END IF;
550
551 SELECT COUNT(*) INTO l_count
552 FROM CS_KB_SET_ELES
553 WHERE element_id = Get_Latest_Version_Id(p_element_number);
554 IF(l_count > 0) THEN
555 FND_MESSAGE.set_name('CS', 'CS_KB_C_ELE_IN_SET_ERR');
556 RETURN ERROR_STATUS;
557 END IF;
558
559 SELECT COUNT(*) INTO l_count
560 FROM CS_KB_ELEMENT_LINKS
561 WHERE element_id = Get_Latest_Version_Id(p_element_number);
562 IF(l_count > 0) THEN
563 FND_MESSAGE.set_name('CS', 'CS_KB_C_ELE_IN_LINK_ERR');
564 RETURN ERROR_STATUS;
565 END IF;
566
567 --clean cs_kb_elements_audit_b , cs_kb_elements_audit_tl
568 CS_KB_ELEMENTS_PKG.Delete_Row(x_element_number => p_element_number);
569
570 RETURN OKAY_STATUS;
571 END Delete_Element;
572
573
574 PROCEDURE Create_Statement(
575 X_ELEMENT_ID IN OUT NOCOPY NUMBER,
576 P_ELEMENT_TYPE_ID IN NUMBER,
577 P_NAME IN VARCHAR2,
578 P_DESC IN CLOB,
582 P_ATTRIBUTE1 IN VARCHAR2,
579 P_STATUS IN VARCHAR2,
580 P_ACCESS_LEVEL IN NUMBER,
581 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
583 P_ATTRIBUTE2 IN VARCHAR2,
584 P_ATTRIBUTE3 IN VARCHAR2,
585 P_ATTRIBUTE4 IN VARCHAR2,
586 P_ATTRIBUTE5 IN VARCHAR2,
587 P_ATTRIBUTE6 IN VARCHAR2,
588 P_ATTRIBUTE7 IN VARCHAR2,
589 P_ATTRIBUTE8 IN VARCHAR2,
590 P_ATTRIBUTE9 IN VARCHAR2,
591 P_ATTRIBUTE10 IN VARCHAR2,
592 P_ATTRIBUTE11 IN VARCHAR2,
593 P_ATTRIBUTE12 IN VARCHAR2,
594 P_ATTRIBUTE13 IN VARCHAR2,
595 P_ATTRIBUTE14 IN VARCHAR2,
596 P_ATTRIBUTE15 IN VARCHAR2,
597 P_START_ACTIVE_DATE IN DATE,
598 P_END_ACTIVE_DATE IN DATE,
599 P_CONTENT_TYPE IN VARCHAR2,
600 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
601 X_MSG_DATA OUT NOCOPY VARCHAR2,
602 X_MSG_COUNT OUT NOCOPY NUMBER )
603 IS
604
605 l_element_number VARCHAR2(30);
606 l_element_id NUMBER;
607 l_date DATE;
608 l_created_by NUMBER;
609 l_login NUMBER;
610 l_rowid VARCHAR2(30);
611 l_count PLS_INTEGER;
612 l_status VARCHAR2(30);
613 --SEDATE
614 l_dummy VARCHAR2(1) := null;
615
616 CURSOR check_active_type_csr(p_element_type_id IN NUMBER) IS
617 SELECT 'X'
618 FROM cs_kb_element_types_b
619 WHERE element_type_id = p_element_type_id
620 AND trunc(sysdate) BETWEEN trunc(nvl(start_date_active, sysdate))
621 AND trunc(nvl(end_date_active, sysdate));
622
623 BEGIN
624 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
625 -- check params
626 IF(p_element_type_id IS NULL OR p_name IS NULL) THEN
627 FND_MSG_PUB.initialize;
628 FND_MESSAGE.set_name('CS', 'CS_KB_C_MISS_PARAM');
629 FND_MSG_PUB.ADD;
630 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
631 X_ELEMENT_ID := -1;
632 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
633 p_count => x_msg_count,
634 p_data => x_msg_data);
635
636 ELSE
637 -- IF type exists
638 SELECT COUNT(*) INTO l_count
639 FROM CS_KB_ELEMENT_TYPES_B
640 WHERE element_type_id = p_element_type_id;
641
642 IF(l_count <1) THEN
643 FND_MSG_PUB.initialize;
644 FND_MESSAGE.set_name('CS', 'CS_KB_C_INVALID_ELE_TYPE_ID');
645 FND_MSG_PUB.ADD;
646 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
647 X_ELEMENT_ID := -2;
648 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
649 p_count => x_msg_count,
650 p_data => x_msg_data);
651 ELSE
652 -- SEDATE
653 Open check_active_type_csr(p_element_type_id);
654 Fetch check_active_type_csr Into l_dummy;
655 Close check_active_type_csr;
656
657 If l_dummy Is Null Then
658 FND_MSG_PUB.initialize;
659 FND_MESSAGE.set_name('CS', 'CS_KB_EXPIRED_STMT_TYPE');
660 FND_MSG_PUB.ADD;
661 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
662 X_ELEMENT_ID := -3;
663 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
664 p_count => x_msg_count,
665 p_data => x_msg_data);
666 ELSE
667
668 IF x_element_id is null THEN
669 SELECT CS_KB_ELEMENTS_S.NEXTVAL INTO l_element_id FROM DUAL;
670 x_element_id := l_element_id;
671 END IF;
672
673 SELECT TO_CHAR(CS_KB_ELEMENT_NUMBER_S.NEXTVAL) INTO l_element_number FROM DUAL;
674 LOOP
675 SELECT COUNT(element_number) INTO l_count
676 FROM CS_KB_ELEMENTS_B
677 WHERE element_number = l_element_number;
678 EXIT WHEN l_count = 0;
679 SELECT TO_CHAR(CS_KB_ELEMENT_NUMBER_S.NEXTVAL) INTO l_element_number FROM DUAL;
680 END LOOP;
681
682 IF x_element_id IS NULL OR l_element_number IS NULL THEN
683 FND_MSG_PUB.initialize;
684 FND_MESSAGE.set_name('CS', 'CS_KB_C_MISS_PARAM');
685 FND_MSG_PUB.ADD;
686 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
687 X_ELEMENT_ID := -1;
688 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
689 p_count => x_msg_count,
690 p_data => x_msg_data);
691 ELSE
692
693 Get_Who(l_date, l_created_by, l_login);
694
695 IF p_status IS NULL THEN
696 l_status := 'DRAFT';
697 ELSE
698 l_status := p_status;
699 END IF;
700
701
702 CS_KB_ELEMENTS_PKG.Insert_Row(
703 x_rowid => l_rowid,
704 x_element_id => x_element_id,
705 x_element_number => l_element_number,
706 x_element_type_id => p_element_type_id,
707 x_element_name => NULL,
708 x_group_flag => NULL,
709 x_status => l_status,
710 x_access_level => p_access_level,
711 x_name => p_name,
715 x_last_update_date => l_date,
712 x_description => p_desc,
713 x_creation_date => l_date,
714 x_created_by => l_created_by,
716 x_last_updated_by => l_created_by,
717 x_last_update_login => l_login,
718 x_locked_by => NULL,
719 x_lock_date => NULL,
720 x_attribute_category => p_attribute_category,
721 x_attribute1 => p_attribute1,
722 x_attribute2 => p_attribute2,
723 x_attribute3 => p_attribute3,
724 x_attribute4 => p_attribute4,
725 x_attribute5 => p_attribute5,
726 x_attribute6 => p_attribute6,
727 x_attribute7 => p_attribute7,
728 x_attribute8 => p_attribute8,
729 x_attribute9 => p_attribute9,
730 x_attribute10 => p_attribute10,
731 x_attribute11 => p_attribute11,
732 x_attribute12 => p_attribute12,
733 x_attribute13 => p_attribute13,
734 x_attribute14 => p_attribute14,
735 x_attribute15 => p_attribute15,
736 x_start_active_date => p_start_active_date,
737 x_end_active_date => p_end_active_date,
738 x_content_type => p_content_type );
739
740 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
741
742 END IF;
743
744 END IF;
745
746 END IF;
747
748 END IF;
749
750 EXCEPTION
751 WHEN others THEN
752 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
753 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
754
755 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
756 p_data => x_msg_data);
757 END Create_Statement;
758
759
760 FUNCTION create_clob
761 RETURN CLOB
762 IS
763 c1 CLOB;
764 BEGIN
765
766 DBMS_LOB.CREATETEMPORARY(c1,true);
767 DBMS_LOB.OPEN(c1,dbms_lob.lob_readwrite);
768 DBMS_LOB.WRITE(c1,1,1,' ');
769 RETURN c1;
770
771 END create_clob;
772
773
774 PROCEDURE Update_Statement(
775 P_ELEMENT_ID IN NUMBER,
776 P_ELEMENT_NUMBER IN VARCHAR2,
777 P_ELEMENT_TYPE_ID IN NUMBER,
778 P_DESC IN CLOB,
779 P_NAME IN VARCHAR2,
780 P_STATUS IN VARCHAR2,
781 P_ACCESS_LEVEL IN NUMBER,
782 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
783 P_ATTRIBUTE1 IN VARCHAR2,
784 P_ATTRIBUTE2 IN VARCHAR2,
785 P_ATTRIBUTE3 IN VARCHAR2,
786 P_ATTRIBUTE4 IN VARCHAR2,
787 P_ATTRIBUTE5 IN VARCHAR2,
788 P_ATTRIBUTE6 IN VARCHAR2,
789 P_ATTRIBUTE7 IN VARCHAR2,
790 P_ATTRIBUTE8 IN VARCHAR2,
791 P_ATTRIBUTE9 IN VARCHAR2,
792 P_ATTRIBUTE10 IN VARCHAR2,
793 P_ATTRIBUTE11 IN VARCHAR2,
794 P_ATTRIBUTE12 IN VARCHAR2,
795 P_ATTRIBUTE13 IN VARCHAR2,
796 P_ATTRIBUTE14 IN VARCHAR2,
797 P_ATTRIBUTE15 IN VARCHAR2,
798 P_START_ACTIVE_DATE IN DATE,
799 P_END_ACTIVE_DATE IN DATE,
800 P_CONTENT_TYPE IN VARCHAR2,
801 X_RETURN OUT NOCOPY NUMBER,
802 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
803 X_MSG_DATA OUT NOCOPY VARCHAR2,
804 X_MSG_COUNT OUT NOCOPY NUMBER )
805 IS
806
807 l_date DATE;
808 l_updated_by NUMBER;
809 l_login NUMBER;
810 l_count PLS_INTEGER;
811
812 --SEDATE
813 l_dummy VARCHAR2(1) := null;
814 CURSOR check_active_type_csr(p_element_type_id IN NUMBER) Is
815 SELECT 'X'
816 FROM cs_kb_element_types_b
817 WHERE element_type_id = p_element_type_id
818 AND trunc(sysdate) BETWEEN trunc(nvl(start_date_active, sysdate))
819 AND trunc(nvl(end_date_active, sysdate));
820
821 CURSOR validate_old_type_used_csr(p_element_type_id IN NUMBER,
822 p_element_id IN NUMBER) IS
823 SELECT 'x'
824 FROM CS_KB_ELEMENTS_B
825 WHERE element_id = p_element_id
826 AND element_type_id = p_element_type_id;
827
828 BEGIN
829 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
830 X_RETURN := -8;
831
832 -- validate params
833 IF (p_element_id IS NULL) OR ( NOT p_element_type_id > 0) THEN
834 X_RETURN := -1; -- 'CS_KB_C_MISS_PARAM'
835 FND_MSG_PUB.initialize;
836 FND_MESSAGE.set_name('CS', 'CS_KB_C_MISS_PARAM');
837 FND_MSG_PUB.ADD;
838 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
839 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
840 p_count => X_MSG_COUNT,
841 p_data => X_MSG_DATA);
842 ELSE
843
844 -- IF type exists
845 SELECT COUNT(*) INTO l_count
846 FROM CS_KB_ELEMENT_TYPES_B
847 WHERE element_type_id = p_element_type_id;
848
852 FND_MESSAGE.set_name('CS', 'CS_KB_C_INVALID_ELE_TYPE_ID');
849 IF(l_count <1) THEN
850 X_RETURN := -2;
851 FND_MSG_PUB.initialize;
853 FND_MSG_PUB.ADD;
854 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
855 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
856 p_count => X_MSG_COUNT,
857 p_data => X_MSG_DATA);
858 ELSE
859 -- SEDATE
860 Open check_active_type_csr(p_element_type_id);
861 Fetch check_active_type_csr Into l_dummy;
862 Close check_active_type_csr;
863
864 IF l_dummy Is Null Then
865 -- Check whether the p_set_type_id is same as the set_type_id in the solution.
866 -- If yes, let it pass because it is a modification to a solution of which the expired
867 -- solution type was active at the time when the solution was created.
868 Open validate_old_type_used_csr(p_element_type_id, p_element_id);
869 Fetch validate_old_type_used_csr Into l_dummy;
870 Close validate_old_type_used_csr;
871 IF l_dummy Is Null Then
872 X_RETURN := -3;
873 FND_MSG_PUB.initialize;
874 FND_MESSAGE.set_name('CS', 'CS_KB_END_DATED_TYPE');
875 FND_MSG_PUB.ADD;
876 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
877 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
878 p_count => X_MSG_COUNT,
879 p_data => X_MSG_DATA);
880
881 END IF;
882 END IF;
883 --END SEDATE
884
885 IF l_dummy is not null THEN
886 --prepare data, THEN INSERT new ele
887 get_who(l_date, l_updated_by, l_login);
888
889 CS_KB_ELEMENTS_PKG.Update_Row(
890 x_element_id => p_element_id,
891 x_element_number => p_element_number,
892 x_element_type_id => p_element_type_id,
893 x_element_name => NULL,
894 x_group_flag => NULL,
895 x_status => p_status,
896 x_access_level => p_access_level,
897 x_name => p_name,
898 x_description => p_desc,
899 x_last_update_date => l_date,
900 x_last_updated_by => l_updated_by,
901 x_last_update_login => l_login,
902 x_locked_by => null,
903 x_lock_date => null,
904 x_attribute_category => p_attribute_category,
905 x_attribute1 => p_attribute1,
906 x_attribute2 => p_attribute2,
907 x_attribute3 => p_attribute3,
908 x_attribute4 => p_attribute4,
909 x_attribute5 => p_attribute5,
910 x_attribute6 => p_attribute6,
911 x_attribute7 => p_attribute7,
912 x_attribute8 => p_attribute8,
913 x_attribute9 => p_attribute9,
914 x_attribute10 => p_attribute10,
915 x_attribute11 => p_attribute11,
916 x_attribute12 => p_attribute12,
917 x_attribute13 => p_attribute13,
918 x_attribute14 => p_attribute14,
919 x_attribute15 => p_attribute15,
920 x_start_active_date => p_start_active_date,
921 x_end_active_date => p_end_active_date,
922 x_content_type => p_content_type );
923
924 -- Mark the Solutions as updated
925 UPDATE cs_kb_sets_b
926 SET last_update_date = l_date,
927 last_updated_by = l_updated_by,
928 last_update_login = l_login
929 WHERE set_id IN (SELECT set_id
930 FROM CS_KB_SET_ELES
931 WHERE element_id = p_element_id);
932
933 X_RETURN := OKAY_STATUS;
934 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
935
936 END IF; -- Type Not End Dated
937
938 END IF; -- Element Type is valid
939
940 END IF; --Required parameters passed in
941
942 EXCEPTION
943 WHEN OTHERS THEN
944 X_RETURN := -7;--ERROR_STATUS;
945 FND_MESSAGE.set_name('CS', 'CS_KB_C_UPDATE_ERR');
946 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
947 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
948 p_count => X_MSG_COUNT,
949 p_data => X_MSG_DATA);
950 END Update_Statement;
951
952
953 PROCEDURE Update_Statement_Admin (
954 P_ELEMENT_ID IN NUMBER,
955 P_ELEMENT_NUMBER IN VARCHAR2,
956 P_ACCESS_LEVEL IN NUMBER,
957 P_ELEMENT_TYPE_ID IN NUMBER,
958 P_ELEMENT_NAME IN VARCHAR2,
959 P_ELEMENT_DESC IN CLOB,
960 P_CONTENT_TYPE IN VARCHAR2,
961 X_RETURN_ELEMENT OUT NOCOPY NUMBER,
962 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
963 X_MSG_DATA OUT NOCOPY VARCHAR2,
964 X_MSG_COUNT OUT NOCOPY NUMBER ) IS
965
966 CURSOR Get_Other_Stmt_Attributes IS
967 SELECT status,
968 attribute_category,
969 attribute1,
970 attribute2,
974 attribute6,
971 attribute3,
972 attribute4,
973 attribute5,
975 attribute7,
976 attribute8,
977 attribute9,
978 attribute10,
979 attribute11,
980 attribute12,
981 attribute13,
982 attribute14,
983 attribute15
984 FROM CS_KB_ELEMENTS_B
985 WHERE Element_id = P_ELEMENT_ID;
986
987 CURSOR Get_Current_Cat_Group_Solns IS
988 SELECT distinct s.Set_id, s.Set_Number
989 FROM CS_KB_SETS_B s,
990 CS_KB_SET_ELES e,
991 CS_KB_SET_CATEGORIES c,
992 CS_KB_CAT_GROUP_DENORM d
993 WHERE s.Set_id = e.Set_Id
994 AND e.Element_id = P_ELEMENT_ID
995 AND s.Set_id = c.Set_id
996 AND c.Category_id = d.Child_Category_id
997 AND d.Category_Group_Id = CS_KB_SECURITY_PVT.Get_Category_Group_Id
998 AND (s.Latest_Version_Flag = 'Y' OR s.Viewable_Version_Flag = 'Y');
999
1000 CURSOR Get_Curr_CG_Solns_Upd_Stmt (v_dup_id IN NUMBER) IS
1001 SELECT distinct s.Set_id, s.Set_Number
1002 FROM CS_KB_SETS_B s,
1003 CS_KB_SET_ELES e,
1004 CS_KB_SET_CATEGORIES c,
1005 CS_KB_CAT_GROUP_DENORM d
1006 WHERE s.Set_id = e.Set_Id
1007 AND e.Element_id = P_ELEMENT_ID
1008 AND s.Set_id = c.Set_id
1009 AND c.Category_id = d.Child_Category_id
1010 AND d.Category_Group_Id = CS_KB_SECURITY_PVT.Get_Category_Group_Id
1011 AND (s.Latest_Version_Flag = 'Y' OR s.Viewable_Version_Flag = 'Y')
1012 AND NOT EXISTS (SELECT 'x'
1013 FROM CS_KB_SET_ELES se
1014 WHERE se.set_id = e.set_id
1015 AND se.element_id = v_dup_id);
1016
1017 CURSOR Get_Curr_CG_Solns_Rem_Stmt (v_dup_id IN NUMBER) IS
1018 SELECT distinct s.Set_id, s.Set_Number
1019 FROM CS_KB_SETS_B s,
1020 CS_KB_SET_ELES e,
1021 CS_KB_SET_CATEGORIES c,
1022 CS_KB_CAT_GROUP_DENORM d
1023 WHERE s.Set_id = e.Set_Id
1024 AND e.Element_id = P_ELEMENT_ID
1025 AND s.Set_id = c.Set_id
1026 AND c.Category_id = d.Child_Category_id
1027 AND d.Category_Group_Id = CS_KB_SECURITY_PVT.Get_Category_Group_Id
1028 AND (s.Latest_Version_Flag = 'Y' OR s.Viewable_Version_Flag = 'Y')
1029 AND EXISTS (SELECT 'x'
1030 FROM CS_KB_SET_ELES se
1031 WHERE se.set_id = e.set_id
1032 AND se.element_id = v_dup_id);
1033
1034 CURSOR Get_Element_Number IS
1035 SELECT Element_Number
1036 FROM CS_KB_ELEMENTS_B
1037 WHERE Element_id = P_ELEMENT_ID;
1038
1039
1040 CURSOR Stmt_In_Soln_Outside_Cur_CG IS
1041 SELECT count(distinct s.Set_Number)
1042 FROM CS_KB_SETS_VL s,
1043 CS_KB_SET_ELES e
1044 WHERE s.Set_id = e.Set_Id
1045 AND e.Element_id = P_ELEMENT_ID
1046 AND s.Latest_Version_Flag = 'Y'
1047 AND s.Status <> 'OBS'
1048 AND s.Set_Id IN (SELECT setb.Set_Id
1049 FROM CS_KB_SETS_B setb,
1050 CS_KB_SET_ELES setele,
1051 CS_KB_SET_CATEGORIES setcat,
1052 CS_KB_CAT_GROUP_DENORM denorm
1053 WHERE setb.set_id = setele.Set_Id
1054 AND setele.Element_id = e.Element_id
1055 AND setb.Latest_Version_Flag = 'Y'
1056 AND setb.Status <> 'OBS'
1057 AND setb.Set_id = setcat.Set_id
1058 AND setcat.Category_id = denorm.Child_Category_id
1059 AND denorm.Category_Group_Id <> CS_KB_SECURITY_PVT.Get_Category_Group_Id
1060 AND NOT EXISTS (SELECT 'x'
1061 FROM CS_KB_SETS_B setb2,
1062 CS_KB_SET_CATEGORIES setcat2,
1063 CS_KB_CAT_GROUP_DENORM denorm2
1064 WHERE setb2.set_id = setb.Set_Id
1065 AND setb2.Latest_Version_Flag = 'Y'
1066 AND setb2.Status <> 'OBS'
1067 AND setb2.Set_id = setcat2.Set_id
1068 AND setcat2.Category_id = denorm2.Child_Category_id
1069 AND denorm2.Category_Group_Id =
1070 CS_KB_SECURITY_PVT.Get_Category_Group_Id )
1071 );
1072
1073 l_cg_usage_count NUMBER;
1074 l_dup_id NUMBER := NULL;
1075 l_dup_check_id NUMBER := NULL;
1076 l_status VARCHAR2(30);
1077 l_attribute_category VARCHAR2(30);
1078 l_attribute1 VARCHAR2(150);
1079 l_attribute2 VARCHAR2(150);
1080 l_attribute3 VARCHAR2(150);
1081 l_attribute4 VARCHAR2(150);
1082 l_attribute5 VARCHAR2(150);
1083 l_attribute6 VARCHAR2(150);
1084 l_attribute7 VARCHAR2(150);
1085 l_attribute8 VARCHAR2(150);
1086 l_attribute9 VARCHAR2(150);
1087 l_attribute10 VARCHAR2(150);
1088 l_attribute11 VARCHAR2(150);
1089 l_attribute12 VARCHAR2(150);
1090 l_attribute13 VARCHAR2(150);
1091 l_attribute14 VARCHAR2(150);
1092 l_attribute15 VARCHAR2(150);
1093
1094 l_element_number VARCHAR2(30);
1098 l_return NUMBER;
1095 l_new_element_id NUMBER;
1096
1097 l_request_id number;
1099 l_return_status VARCHAR2(1);
1100 l_msg_data VARCHAR2(2000);
1101 l_msg_count NUMBER;
1102
1103 l_asap_idx_enabled varchar2(4) := null;
1104
1105 BEGIN
1106 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1107 X_RETURN_ELEMENT := P_ELEMENT_ID;
1108
1109 SAVEPOINT START_GSU;
1110
1111
1112 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1113 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'csk.plsql.CS_KB_ELEMENTS_AUDIT_PVT.Update_Statement_Admin.Start',
1114 'Started Global Statement Update');
1115 END IF;
1116
1117 -- Is the updated Statement now a Duplicate ?
1118 l_dup_id := Is_Element_Dup ( P_ELEMENT_NUMBER => P_ELEMENT_NUMBER,
1119 P_ACCESS_LEVEL => P_ACCESS_LEVEL,
1120 P_ELEMENT_TYPE_ID => P_ELEMENT_TYPE_ID,
1121 P_ELEMENT_NAME => P_ELEMENT_NAME,
1122 P_ELEMENT_DESC => P_ELEMENT_DESC );
1123
1124 -- If the api returns 0 then no duplicate exists
1125 -- else it returns an element_id
1126
1127
1128 IF l_dup_id <> 0 THEN
1129 -- A Duplicate Statement Exists for this update
1130 -- Update All Solutions within the Current Category Group to be
1131 -- associated to the Duplicate (Already existing Statement).
1132 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1133 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'csk.plsql.CS_KB_ELEMENTS_AUDIT_PVT.Update_Statement_Admin',
1134 'A Duplicate Statement Exists for this update - UPD with Current CG');
1135 END IF;
1136
1137 -- Bug 3576066 moved to above upd + delete
1138 cs_kb_sync_index_pkg.MARK_IDXS_ON_GLOBAL_STMT_UPD(l_dup_id);
1139 cs_kb_sync_index_pkg.MARK_IDXS_ON_GLOBAL_STMT_UPD(P_ELEMENT_ID);
1140
1141 FOR solns IN Get_Curr_CG_Solns_Upd_Stmt (l_dup_id) LOOP
1142 -- This returns Solutions in the current Category Group
1143 -- to be updated. Only solutions that do not already
1144 -- contain the new statement will be returned
1145
1146 UPDATE CS_KB_SET_ELES se
1147 SET se.ELEMENT_ID = l_dup_id,
1148 se.LAST_UPDATE_DATE = sysdate,
1149 se.LAST_UPDATED_BY = FND_GLOBAL.user_id,
1150 se.LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
1151 WHERE se.Element_Id = P_ELEMENT_ID
1152 AND se.Set_Id = solns.Set_Id;
1153
1154 END LOOP;
1155
1156 FOR remstmts IN Get_Curr_CG_Solns_Rem_Stmt (l_dup_id) LOOP
1157 -- This returns Solutions in the current Category Group.
1158 -- These solutions already contain the new duplicate -
1159 -- therefore we will delete the original statement that
1160 -- was updated.
1161
1162 DELETE FROM CS_KB_SET_ELES se
1163 WHERE se.Element_Id = P_ELEMENT_ID
1164 AND se.Set_Id = remstmts.Set_Id;
1165
1166 END LOOP;
1167
1168 fnd_profile.get('CS_KB_ENABLE_ASAP_INDEXING', l_asap_idx_enabled);
1169 IF ( l_asap_idx_enabled = 'Y' )
1170 THEN
1171 CS_KB_SYNC_INDEX_PKG.request_sync_km_indexes( l_request_id, l_return_status );
1172 END IF;
1173
1174 X_RETURN_ELEMENT := l_dup_id;
1175 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1176
1177 ELSE -- The Updated Statement is not a Duplicate
1178
1179 /* -- Need to check Soln Usage !!!
1180 The following Api returns a count of the Number of Solutions that use
1181 the statement, where that Solution only exists in Category Groups
1182 outside of the current user Category Group
1183
1184 -- If the Statement only resides on Solutions within the current CG then
1185 update the statement directly.
1186
1187 -- If the Statement is used on Solutions outside of the current CG only, then these
1188 solutions should remain unchanged
1189 i.e. within the current CG a new Statement will be created and assoicated to the
1190 current CG solutions.
1191 Solutions outside of the current CG will continue to point to the original statement.
1192
1193 -- If a Solution resides in multiple CG's then the statement will be updated directly
1194 and therefore the changed statement will be seen across CG's
1195 */
1196
1197 OPEN Stmt_In_Soln_Outside_Cur_CG;
1198 FETCH Stmt_In_Soln_Outside_Cur_CG INTO l_cg_usage_count;
1199 CLOSE Stmt_In_Soln_Outside_Cur_CG;
1200 --dbms_output.put_line('Usage Count : '||l_cg_usage_count);
1201
1202 -- Check if any solns outside of current CG are affected
1203 -- if Yes - Create a new stmt and link solns within current CG to it
1204 -- - Leave solns outside of current CG to be linked to existing stmt
1205 -- if No - Update as normal
1206
1207
1208 OPEN Get_Other_Stmt_Attributes;
1209 FETCH Get_Other_Stmt_Attributes INTO l_status, l_attribute_category, l_attribute1, l_attribute2,
1210 l_attribute3, l_attribute4,l_attribute5, l_attribute6,
1211 l_attribute7, l_attribute8, l_attribute9, l_attribute10,
1212 l_attribute11, l_attribute12, l_attribute13, l_attribute14,
1213 l_attribute15; --, l_content_type;
1214 CLOSE Get_Other_Stmt_Attributes;
1215
1219 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'csk.plsql.CS_KB_ELEMENTS_AUDIT_PVT.Update_Statement_Admin',
1216 IF l_cg_usage_count = 0 THEN
1217 -- If Yes (count=0) - Statement only used in current Category Group - Update Statement as normal
1218 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1220 'Statement only used in current Category Group - Update Statement');
1221 END IF;
1222
1223 Update_Statement( p_element_id => P_ELEMENT_ID,
1224 p_element_number => P_ELEMENT_NUMBER,
1225 p_element_type_id => P_ELEMENT_TYPE_ID,
1226 p_desc => P_ELEMENT_DESC,
1227 p_name => P_ELEMENT_NAME,
1228 p_status => l_status,
1229 p_access_level => P_ACCESS_LEVEL,
1230 p_attribute_category => l_attribute_category,
1231 p_attribute1 => l_attribute1,
1232 p_attribute2 => l_attribute2,
1233 p_attribute3 => l_attribute3,
1234 p_attribute4 => l_attribute4,
1235 p_attribute5 => l_attribute5,
1236 p_attribute6 => l_attribute6,
1237 p_attribute7 => l_attribute7,
1238 p_attribute8 => l_attribute8,
1239 p_attribute9 => l_attribute9,
1240 p_attribute10 => l_attribute10,
1241 p_attribute11 => l_attribute11,
1242 p_attribute12 => l_attribute12,
1243 p_attribute13 => l_attribute13,
1244 p_attribute14 => l_attribute14,
1245 p_attribute15 => l_attribute15,
1246 p_start_active_date => null,
1247 p_end_active_date => null,
1248 p_content_type => P_CONTENT_TYPE, --l_content_type,
1249 x_return => l_return,
1250 x_return_status => l_return_status,
1251 x_msg_data => l_msg_data,
1252 x_msg_count => l_msg_count);
1253
1254 -- Set Output params
1255 X_RETURN_STATUS := l_return_status;
1256 X_MSG_DATA := l_msg_data;
1257 X_MSG_COUNT := l_msg_count;
1258
1259 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1260
1261 cs_kb_sync_index_pkg.MARK_IDXS_ON_GLOBAL_STMT_UPD(P_ELEMENT_ID);
1262
1263 fnd_profile.get('CS_KB_ENABLE_ASAP_INDEXING', l_asap_idx_enabled);
1264 IF ( l_asap_idx_enabled = 'Y' )
1265 THEN
1266 CS_KB_SYNC_INDEX_PKG.request_sync_km_indexes( l_request_id, l_return_status );
1267 END IF;
1268
1269 END IF;
1270
1271 ELSE -- Statement is shared across multiple Category Groups
1272 -- + Statement is not being updated Duplicate, therefore
1273 -- create a new Statement and associate within current CG
1274 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1275 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'csk.plsql.CS_KB_ELEMENTS_AUDIT_PVT.Update_Statement_Admin',
1276 'Statement is shared across multiple Category Groups - Create New Stmt');
1277 END IF;
1278
1279 -- Before creating a New Statement check that the Stmt is not being updated
1280 -- to itself ie no change to the original content
1281 l_dup_check_id := Is_Element_Dup ( P_ELEMENT_NUMBER => '-123',
1282 P_ACCESS_LEVEL => P_ACCESS_LEVEL,
1283 P_ELEMENT_TYPE_ID => P_ELEMENT_TYPE_ID,
1284 P_ELEMENT_NAME => P_ELEMENT_NAME,
1285 P_ELEMENT_DESC => P_ELEMENT_DESC );
1286
1287 IF l_dup_check_id <> P_ELEMENT_ID THEN
1288
1289 Create_Statement( x_element_id => l_new_element_id,
1290 p_element_type_id => p_element_type_id,
1291 p_desc => p_element_desc,
1292 p_name => p_element_name,
1293 p_status => l_status,
1294 p_access_level => p_access_level,
1295 p_attribute_category => l_attribute_category,
1296 p_attribute1 => l_attribute1,
1297 p_attribute2 => l_attribute2,
1298 p_attribute3 => l_attribute3,
1299 p_attribute4 => l_attribute4,
1300 p_attribute5 => l_attribute5,
1301 p_attribute6 => l_attribute6,
1302 p_attribute7 => l_attribute7,
1303 p_attribute8 => l_attribute8,
1304 p_attribute9 => l_attribute9,
1305 p_attribute10 => l_attribute10,
1306 p_attribute11 => l_attribute11,
1307 p_attribute12 => l_attribute12,
1308 p_attribute13 => l_attribute13,
1312 x_return_status => l_return_status,
1309 p_attribute14 => l_attribute14,
1310 p_attribute15 => l_attribute15,
1311 p_content_type => P_CONTENT_TYPE, --l_content_type,
1313 x_msg_data => l_msg_data,
1314 x_msg_count => l_msg_count);
1315
1316 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1317 X_RETURN_ELEMENT := NULL;
1318 X_RETURN_STATUS := l_return_status;
1319 X_MSG_DATA := l_msg_data;
1320 X_MSG_COUNT := l_msg_count;
1321 ELSE
1322 -- Api returned successful so continue
1323
1324 -- Associate New Statement to All Solutions within the
1325 -- current Category Group only
1326 FOR solns IN Get_Current_Cat_Group_Solns LOOP
1327
1328 UPDATE CS_KB_SET_ELES se
1329 SET se.ELEMENT_ID = l_new_element_id,
1330 se.LAST_UPDATE_DATE = sysdate,
1331 se.LAST_UPDATED_BY = FND_GLOBAL.user_id,
1332 se.LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
1333 WHERE se.Element_Id = P_ELEMENT_ID
1334 AND se.Set_Id = solns.Set_Id;
1335
1336 END LOOP;
1337
1338 -- Reindex all Solutions effected
1339 CS_KB_SYNC_INDEX_PKG.Mark_Idxs_On_Global_Stmt_Upd(l_new_element_id);
1340 CS_KB_SYNC_INDEX_PKG.Mark_Idxs_On_Global_Stmt_Upd(P_ELEMENT_ID);
1341
1342 fnd_profile.get('CS_KB_ENABLE_ASAP_INDEXING', l_asap_idx_enabled);
1343 IF ( l_asap_idx_enabled = 'Y' )
1344 THEN
1345 CS_KB_SYNC_INDEX_PKG.request_sync_km_indexes( l_request_id, l_return_status );
1346 END IF;
1347
1348 X_RETURN_ELEMENT := l_new_element_id;
1349 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1350
1351 END IF;
1352
1353 ELSE
1354 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1355 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'csk.plsql.CS_KB_ELEMENTS_AUDIT_PVT.Update_Statement_Admin',
1356 'Stmt is dup of the original - NO new Stmt created.');
1357 END IF;
1358 X_RETURN_ELEMENT := P_ELEMENT_ID;
1359 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1360 END IF; --check if dup of itself
1361
1362 END IF;
1363
1364 END IF; -- Dup Check
1365
1366
1367 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1368 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'csk.plsql.CS_KB_ELEMENTS_AUDIT_PVT.Update_Statement_Admin.End',
1369 'Finished Global Statement Update - '||X_RETURN_STATUS||'-'||X_RETURN_ELEMENT );
1370 END IF;
1371
1372 EXCEPTION
1373 WHEN OTHERS THEN
1374
1375 ROLLBACK TO START_GSU;
1376
1377 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1378 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'csk.plsql.CS_KB_ELEMENTS_AUDIT_PVT.Update_Statement_Admin.UNEX',
1379 'Unexpected Exception-'||substrb(sqlerrm,1,200) );
1380 END IF;
1381
1382 X_RETURN_ELEMENT := NULL;
1383 FND_MESSAGE.set_name('CS', 'CS_KB_C_UPDATE_ERR');
1384 FND_MSG_PUB.ADD;
1385 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR ;
1386 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1387 p_count => X_MSG_COUNT,
1388 p_data => X_MSG_DATA);
1389 END Update_Statement_Admin;
1390
1391
1392 PROCEDURE Obsolete_Unused_Statements (
1393 ERRBUF OUT NOCOPY VARCHAR2,
1394 RETCODE OUT NOCOPY VARCHAR2 )
1395 IS
1396
1397 CURSOR GET_ORPHANS IS
1398 SELECT E.element_id
1399 FROM CS_KB_ELEMENTS_B E
1400 WHERE E.status <> 'OBS'
1401 AND NOT EXISTS (SELECT 'x'
1402 FROM CS_KB_SETS_B S,
1403 CS_KB_SET_ELES SE
1404 WHERE SE.Set_Id = S.Set_Id
1405 AND SE.Element_Id = E.Element_Id
1406 AND S.Status <> 'OBS'
1407 AND (S.Latest_Version_Flag = 'Y' OR S.Viewable_Version_Flag = 'Y')
1408 );
1409
1410 l_user NUMBER := FND_GLOBAL.User_Id;
1411 l_login NUMBER := FND_GLOBAL.Login_Id;
1412
1413 BEGIN
1414
1415 FOR Statements IN GET_ORPHANS LOOP
1416
1417 UPDATE CS_KB_ELEMENTS_B
1418 SET Status = 'OBS',
1419 Last_Update_Date = sysdate,
1420 Last_Updated_By = l_user,
1421 Last_Update_Login = l_login
1422 WHERE Element_Id = Statements.Element_id;
1423
1424 UPDATE CS_KB_ELEMENTS_TL
1425 SET Composite_Text_Index = 'x',
1426 Last_Update_Date = sysdate,
1427 Last_Updated_By = l_user,
1428 Last_Update_Login = l_login
1429 WHERE Element_Id = Statements.Element_id;
1430
1431 END LOOP;
1432
1433 COMMIT;
1434
1435 RETCODE := 0;
1436
1437 EXCEPTION
1438 WHEN OTHERS THEN
1439 RETCODE := 2;
1440 ERRBUF := fnd_message.GET_STRING('CS','CS_KB_C_UNEXP_ERR')||' '||SQLERRM;
1441 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
1442
1443 END Obsolete_Unused_Statements;
1444
1445 END CS_KB_ELEMENTS_AUDIT_PKG;