[Home] [Help]
PACKAGE BODY: APPS.CS_KB_SOLUTION_PVT
Source
1 PACKAGE BODY CS_KB_SOLUTION_PVT AS
2 /* $Header: cskvsolb.pls 120.2.12010000.2 2008/09/12 05:51:31 mmaiya ship $ */
3 /*=======================================================================+
4 | Copyright (c) 2003 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | FILENAME cskvsolb.pls
8 | DESCRIPTION
9 | PL/SQL body for package: CS_KB_SOLUTION_PVT
10 | This contains Private api's for a Solution
11 |
12 | History:
13 | 19 Aug 03 Matt Kettle Created
14 | 12 Sep 03 Matt Kettle Changed Button Logic - If a solution is not
15 | locked and not in a flow -return the checkout
16 | button. Api changed: Get_User_Soln_Access
17 | 25 Sep 03 Matt Kettle Use message CS_KB_INV_SOLN_VIS for invalid
18 | Solution Visibility in cre_sol and upd_sol
19 | 13 Oct 03 Matt Kettle Changed Submit_Solution to check for Dups
20 | within the same solution
21 | 25 Aug 04 Alan Wang Add Move_Solution
22 | 16 Mar 05 Matt Kettle Changed Flag columns to use 'Y' and null
23 | 22 Apr 05 Matt Kettle BugFix 4013998 (FP of 3993200)-Sequence Id Fix
24 | 17 May 05 Matt Kettle Cleanup - removed obs apis (in 115.12):
25 | Update_Element_Element
26 | 19 Jul 05 Matt Kettle Fix for 4464403 - Clone set and set eles
27 | changed to take Source Set id. Removed
28 | Get_Previous_Version_id
29 | 20 Jul 07 ISUGAVAN Bug fix 5947078(FP for Bug 5931800)
30 | 12 Sep 08 mmaiya Bugfix 7117546 - Unlock Locked Solutions in
31 | Draft Mode
32 *=======================================================================*/
33
34 PROCEDURE Get_Who(
35 X_SYSDATE OUT NOCOPY DATE,
36 X_USER_ID OUT NOCOPY NUMBER,
37 X_LOGIN_ID OUT NOCOPY NUMBER )
38 IS
39 BEGIN
40
41 X_SYSDATE := SYSDATE;
42 X_USER_ID := FND_GLOBAL.user_id;
43 X_LOGIN_ID := FND_GLOBAL.login_id;
44
45 END Get_Who;
46
47 PROCEDURE Get_Set_Details(
48 P_SET_ID IN NUMBER,
49 X_SET_NUMBER OUT NOCOPY VARCHAR2,
50 X_STATUS OUT NOCOPY VARCHAR2,
51 X_FLOW_DETAILS_ID OUT NOCOPY NUMBER,
52 X_LOCKED_BY OUT NOCOPY NUMBER )
53 IS
54 BEGIN
55
56 SELECT set_number, status, flow_details_id, locked_by
57 INTO X_SET_NUMBER, X_STATUS, X_FLOW_DETAILS_ID, X_LOCKED_BY
58 FROM CS_KB_SETS_B
59 WHERE set_id = p_set_id;
60
61 END Get_Set_Details;
62
63
64
65 FUNCTION Get_Set_Number(
66 P_SET_ID IN NUMBER)
67 RETURN VARCHAR2
68 IS
69 l_set_number VARCHAR2(30);
70 BEGIN
71
72 SELECT set_number
73 INTO l_set_number
74 FROM CS_KB_SETS_B
75 WHERE set_id = p_set_id;
76
77 RETURN l_set_number;
78
79 END Get_Set_Number;
80
81 -- BugFix 4013998 - Sequence Id Fix
82 FUNCTION Get_Latest_Version_Id(
83 P_SET_NUMBER IN VARCHAR2)
84 RETURN NUMBER IS
85 l_max_set_id NUMBER;
86
87 CURSOR Get_Latest IS
88 SELECT set_id
89 FROM CS_KB_SETS_B
90 WHERE set_number = p_set_number
91 AND latest_version_flag = 'Y';
92
93 BEGIN
94
95 OPEN Get_Latest;
96 FETCH Get_Latest INTO l_max_set_id;
97 CLOSE Get_Latest;
98
99 RETURN l_max_set_id;
100
101 END Get_Latest_Version_Id;
102
103 FUNCTION Get_Published_Set_Id(
104 P_SET_NUMBER IN VARCHAR2)
105 RETURN NUMBER IS
106
107 l_count NUMBER;
108 l_published_set_id NUMBER;
109
110 BEGIN
111
112 SELECT MAX(set_id)
113 INTO l_published_set_id
114 FROM CS_KB_SETS_B
115 WHERE set_number = p_set_number
116 AND status = 'PUB';
117
118 IF (SQL%NOTFOUND) THEN RAISE NO_DATA_FOUND; END IF;
119
120 RETURN l_published_set_id;
121
122 EXCEPTION
123 WHEN NO_DATA_FOUND THEN
124 RETURN ERROR_STATUS;
125 END Get_Published_Set_Id;
126
127
128 FUNCTION Get_Obsoleted_Set_Id(
129 P_SET_NUMBER IN VARCHAR2)
130 RETURN NUMBER IS
131
132 l_count NUMBER;
133 l_obsoleted_set_id NUMBER;
134
135 BEGIN
136
137 SELECT MAX(set_id)
138 INTO l_obsoleted_set_id
139 FROM CS_KB_SETS_B
140 WHERE set_number = p_set_number
141 AND status = 'OBS';
142
143 IF (SQL%NOTFOUND) THEN RAISE NO_DATA_FOUND; END IF;
144
145 RETURN l_obsoleted_set_id;
146
147 EXCEPTION
148 WHEN NO_DATA_FOUND THEN
149 RETURN ERROR_STATUS;
150 END Get_Obsoleted_Set_Id;
151
152
153 FUNCTION Get_Solution_Title(
154 P_SET_ID IN NUMBER)
155 RETURN VARCHAR2 IS
156 l_solution_title CS_KB_SETS_TL.NAME%TYPE;
157 BEGIN
158
159 SELECT name
160 INTO l_solution_title
161 FROM CS_KB_SETS_TL
162 WHERE set_id = p_set_id
163 AND language = USERENV('LANG');
164
165 RETURN l_solution_title;
166
167 END Get_Solution_Title;
168
169
170 PROCEDURE Get_Lock_Info(
171 P_SET_NUMBER IN VARCHAR2,
172 X_LOCKED_BY OUT NOCOPY NUMBER,
173 X_LOCK_DATE OUT NOCOPY DATE )
174 IS
175 BEGIN
176 SELECT locked_by, lock_date
177 INTO X_LOCKED_BY, X_LOCK_DATE
178 FROM CS_KB_SETS_B
179 WHERE set_id = Get_Latest_Version_Id(p_set_number);
180 END Get_Lock_Info;
181
182 PROCEDURE Set_Lock_Info(
183 P_SET_ID IN NUMBER,
184 P_LOCKED_BY IN NUMBER,
185 P_LOCK_DATE IN DATE )
186 IS
187 BEGIN
188
189 UPDATE CS_KB_SETS_B
190 SET locked_by = P_LOCKED_BY,
191 lock_date = P_LOCK_DATE
192 WHERE set_id = P_SET_ID;
193
194 END Set_Lock_Info;
195
196
197 FUNCTION Locked_By(
198 p_set_number IN VARCHAR2)
199 RETURN NUMBER
200 IS
201 l_locked_by NUMBER;
202 l_lock_date DATE;
203 BEGIN
204
205 Get_Lock_Info(p_set_number, l_locked_by, l_lock_date);
206
207 RETURN l_locked_by;
208
209 END Locked_By;
210
211
212 FUNCTION Locked_By(
213 P_SET_ID IN NUMBER)
214 RETURN NUMBER
215 IS
216 l_locked_by NUMBER;
217 BEGIN
218
219 SELECT locked_by
220 INTO l_locked_by
221 FROM CS_KB_SETS_B
222 WHERE set_id = P_SET_ID;
223
224 RETURN l_locked_by;
225
226 END Locked_By;
227
228
229 PROCEDURE Snatch_Lock_From_User(
230 P_SET_ID IN NUMBER,
231 P_SET_NUMBER IN VARCHAR2,
232 P_USER_ID IN NUMBER,
233 P_LOCKED_BY IN NUMBER,
234 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
235 X_MSG_DATA OUT NOCOPY VARCHAR2,
236 X_MSG_COUNT OUT NOCOPY NUMBER) IS
237
238 l_locked_by NUMBER;
239 l_latest_set_id NUMBER;
240
241 BEGIN
242
243 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
244
245 SELECT locked_by
246 INTO l_locked_by
247 FROM CS_KB_SETS_B
248 WHERE set_id = P_SET_ID FOR UPDATE;
249
250 l_latest_set_id := Get_Latest_Version_Id(P_SET_NUMBER);
251
252 -- check to see if the original user is still locking the solution
253 -- and if there is no other new version for this solution
254
255 IF ( (l_locked_by = P_LOCKED_BY) AND (l_latest_set_id = P_SET_ID) )
256 THEN
257 UPDATE CS_KB_SETS_B
258 SET locked_by = P_USER_ID
259 WHERE set_id = P_SET_ID;
260
261 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
262 ELSE
263 FND_MSG_PUB.initialize;
264 FND_MESSAGE.set_name('CS', 'CS_KB_C_SOL_LOCKED_BY_USER');
265 FND_MSG_PUB.ADD;
266 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
267 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
268 p_count => X_MSG_COUNT,
269 p_data => X_MSG_DATA);
270 END IF;
271
272
273 EXCEPTION
274 WHEN OTHERS THEN
275 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
276 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
277
278 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
279 p_data => x_msg_data);
280 END Snatch_Lock_From_User;
281
282
283 PROCEDURE Clear_Permissions(
284 P_SET_NUMBER IN VARCHAR2)
285 IS
286 BEGIN
287
288 UPDATE CS_KB_SETS_B
289 SET locked_by = NULL
290 WHERE set_number = p_set_number;
291
292 EXCEPTION
293 WHEN OTHERS THEN
294 RAISE;
295 END Clear_Permissions;
296
297
298 PROCEDURE Update_Status(
299 P_SET_ID IN NUMBER,
300 P_STATUS IN VARCHAR2 )
301 IS
302 l_set_number VARCHAR2(30);
303 l_access_level NUMBER;
304
305
306 BEGIN
307
308 UPDATE CS_KB_SETS_B
309 SET status = P_STATUS
310 WHERE set_id = P_SET_ID;
311
312 END Update_Status;
313
314
315 PROCEDURE Update_Status(
316 P_SET_NUMBER IN VARCHAR2,
317 P_STATUS IN VARCHAR2 )
318 IS
319 l_max_set_id NUMBER;
320 l_access_level NUMBER;
321
322 BEGIN
323 l_max_set_id := Get_Latest_Version_Id(p_set_number);
324
325 Update_Status(p_set_id => l_max_set_id,
326 p_status => p_status);
327
328 END Update_Status;
329
330
331 PROCEDURE Outdate_Solution(
332 P_SET_NUMBER IN VARCHAR2,
333 P_CURRENT_SET_ID IN NUMBER )
334 IS
335 BEGIN
336 UPDATE CS_KB_SETS_B
337 SET status = 'OUT',
338 --viewable_version_flag = 'N'
339 viewable_version_flag = null
340 WHERE set_number = P_SET_NUMBER
341 AND set_id <> P_CURRENT_SET_ID
342 AND status = 'PUB';
343
344 EXCEPTION
345 WHEN OTHERS THEN
346 RAISE;
347 END Outdate_Solution;
348
349 -- BugFix 4013998 - Sequence Id Fix
350 FUNCTION Clone_Set(
351 P_SET_NUMBER IN VARCHAR2,
352 P_ORIG_SET_ID IN NUMBER,
353 P_STATUS IN VARCHAR2,
354 P_FLOW_DETAILS_ID IN NUMBER,
355 P_LOCKED_BY IN NUMBER )
356 RETURN NUMBER --set_id
357 IS
358 l_count PLS_INTEGER;
359 l_old_set_id NUMBER;
360 l_new_set_id NUMBER;
361 l_SYSDATE DATE;
362 l_user_id NUMBER;
363 l_login_id NUMBER;
364 b_rec CS_KB_SETS_B%ROWTYPE;
365 l_dummy_rowid VARCHAR2(30);
366
367 CURSOR get_tl_rows( v_set_id IN NUMBER) IS
368 SELECT language,
369 source_lang,
370 name,
371 description,
372 composite_assoc_index,
373 positive_assoc_index,
374 negative_assoc_index
375 FROM CS_KB_SETS_TL
376 WHERE Set_Id = v_set_id;
377
378 BEGIN
379 l_old_set_id := P_ORIG_SET_ID; --Get_Latest_Version_Id(p_set_number);
380
381 SELECT * INTO b_rec
382 FROM CS_KB_SETS_B
383 WHERE set_id = l_old_set_id;
384
385 Get_Who(l_SYSDATE, l_user_id, l_login_id);
386
387 UPDATE CS_KB_SETS_B
388 SET LATEST_VERSION_FLAG = null --'N'
389 WHERE SET_NUMBER = b_rec.set_number
390 AND SET_ID = l_old_set_id;
391
392
393 INSERT INTO CS_KB_SETS_B (
394 set_id,
395 set_number,
396 set_type_id,
397 status,
398 creation_date,
399 created_by,
400 last_update_date,
401 last_updated_by,
402 last_update_login,
403 locked_by,
404 lock_date,
405 attribute_category,
406 attribute1,
407 attribute2,
408 attribute3,
409 attribute4,
410 attribute5,
411 attribute6,
412 attribute7,
413 attribute8,
414 attribute9,
415 attribute10,
416 attribute11,
417 attribute12,
418 attribute13,
419 attribute14,
420 attribute15,
421 flow_details_id,
422 priority_code,
423 original_author,
424 original_author_date,
425 latest_version_flag,
426 visibility_id,
427 USAGE_SCORE,
428 NORM_USAGE_SCORE
429 ) VALUES (
430 CS_KB_SETS_S.NEXTVAL,
431 b_rec.set_number,
432 b_rec.set_type_id,
433 p_status,
434 l_SYSDATE,
435 l_user_id,
436 l_SYSDATE,
437 l_user_id,
438 l_login_id,
439 p_locked_by,
440 l_SYSDATE,
441 b_rec.attribute_category,
442 b_rec.attribute1,
443 b_rec.attribute2,
444 b_rec.attribute3,
445 b_rec.attribute4,
446 b_rec.attribute5,
447 b_rec.attribute6,
448 b_rec.attribute7,
449 b_rec.attribute8,
450 b_rec.attribute9,
451 b_rec.attribute10,
452 b_rec.attribute11,
453 b_rec.attribute12,
454 b_rec.attribute13,
455 b_rec.attribute14,
456 b_rec.attribute15,
457 p_flow_details_id,
458 b_rec.priority_code,
459 b_rec.original_author,
460 b_rec.original_author_date,
461 'Y',
462 b_rec.visibility_id,
463 b_rec.usage_score,
464 b_rec.norm_usage_score
465 )
466 RETURNING SET_ID INTO l_new_set_id;
467
468 -- 17-Dec-2003 Perf change - Use DML Returning
469
470 UPDATE CS_KB_SETS_B
471 SET VIEWABLE_VERSION_FLAG = decode(status, 'PUB','Y',null)
472 WHERE SET_NUMBER = b_rec.set_number;
473
474 UPDATE CS_KB_SETS_B s
475 SET s.VIEWABLE_VERSION_FLAG = 'Y'
476 WHERE s.SET_NUMBER = b_rec.set_number
477 AND s.STATUS <> 'OBS'
478 AND s.LATEST_VERSION_FLAG = 'Y'
479 AND NOT EXISTS (SELECT 'x'
480 FROM CS_KB_SETS_B s3
481 WHERE s3.set_number = s.set_number
482 AND s3.STATUS = 'PUB');
483
484 FOR tl_rec IN get_tl_rows(l_old_set_id) LOOP
485
486 INSERT INTO CS_KB_SETS_TL (
487 set_id,
488 language,
489 source_lang,
490 name,
491 description,
492 composite_assoc_index,
493 positive_assoc_index,
494 negative_assoc_index,
495 creation_date,
496 created_by,
497 last_update_date,
498 last_updated_by,
499 last_update_login
500 ) VALUES (
501 l_new_set_id,
502 tl_rec.language,
503 tl_rec.source_lang,
504 tl_rec.name,
505 tl_rec.description,
506 tl_rec.composite_assoc_index,
507 tl_rec.positive_assoc_index,
508 tl_rec.negative_assoc_index,
509 l_SYSDATE,
510 l_user_id,
511 l_SYSDATE,
512 l_user_id,
513 l_login_id
514 );
515 END LOOP;
516
517 RETURN l_new_set_id;
518
519 END Clone_Set;
520
521
522
523 FUNCTION Clone_Solution(
524 P_SET_NUMBER IN VARCHAR2,
525 P_STATUS IN VARCHAR2,
526 P_FLOW_DETAILS_ID IN NUMBER,
527 P_LOCKED_BY IN NUMBER )
528 RETURN NUMBER --set_id
529 IS
530
531 l_ret NUMBER;
532 l_old_set_id NUMBER;
533 l_new_set_id NUMBER;
534
535 l_request_id number;
536 l_return_status varchar2(1);
537 l_asap_idx_enabled varchar2(4) := null;
538 is_dup NUMBER;
539
540 CURSOR GET_ELES_TO_UPDATE (p_set_id IN NUMBER) IS
541 SELECT distinct element_id
542 FROM CS_KB_SET_ELES
543 WHERE set_id = p_set_id;
544
545 --Added to resolve the bug 5947078
546 CURSOR CHECK_STMT_ALREADY_ADDED (p_set_id IN NUMBER, p_ele_id IN NUMBER) IS
547 SELECT count(*)
548 FROM CS_KB_SET_ELES
549 WHERE Set_id = p_set_id
550 AND Element_id = p_ele_id;
551
552 l_check NUMBER;
553
554
555 BEGIN
556
557 -- Retrieve the Current Latest Version
558 l_old_set_id := Get_Latest_Version_Id(P_SET_NUMBER);
559
560 -- Set the locked_by to null for all Solution Versions
561 Clear_Permissions(p_set_number => p_set_number);
562
563 -- Clone the Solution
564 -- Copy the exisiting Latest Version with the new status, lock and flow
565 l_new_set_id := Clone_Set(
566 p_set_number,
567 l_old_set_id,
568 p_status,
569 p_flow_details_id,
570 p_locked_by
571 );
572
573 -- Clone the Solution-Statement associations
574 -- Copy the Statements from the old version to the new clone
575 CS_KB_SET_ELES_PKG.Clone_Rows( P_SET_SOURCE_ID => l_old_set_id,
576 P_SET_TARGET_ID => l_new_set_id);
577
578 -- Clone the Solution External Links
579 l_ret := CS_KB_SET_LINKS_PKG.Clone_Link
580 (p_set_source_id => l_old_set_id,
581 p_set_target_id => l_new_set_id);
582
583 -- Clone any Products, Platforms and Categories associated to the Solution
584 l_ret := CS_KB_ASSOC_PKG.Clone_Link
585 (p_set_source_id => l_old_set_id,
586 p_set_target_id => l_new_set_id);
587
588 -- Clone any attatchments associated to the solution
589 CS_KB_ATTACHMENTS_PKG.Clone_Attachment_Links(
590 p_set_source_id => l_old_set_id,
591 p_set_target_id => l_new_set_id);
592
593 IF p_status = 'PUB' OR
594 p_status = 'OBS' THEN
595
596 -- Outdate previous Published Version ie set the old PUB row to status=OUT
597 Outdate_Solution(p_set_number, l_new_set_id);
598
599 IF (p_status = 'PUB') THEN
600
601 -- If Solution is Published then Publish Statements
602 -- Any Duplicate Statements will be obsoleted
603
604 FOR eles IN GET_ELES_TO_UPDATE(l_new_set_id) LOOP
605
606 is_dup := CS_KB_ELEMENTS_AUDIT_PKG.Is_Element_Created_Dup(eles.element_id);
607
608 IF is_dup = 0 THEN
609 UPDATE CS_KB_ELEMENTS_B
610 SET status = 'PUBLISHED'
611 WHERE element_id = eles.element_id;
612 ELSE --element is duplicate so set to Obsolete
613 UPDATE CS_KB_ELEMENTS_B
614 SET status = 'OBS'
615 WHERE element_id = eles.element_id;
616
617 -- Added to resolve the Bug 5947078
618 OPEN CHECK_STMT_ALREADY_ADDED ( l_new_set_id, is_dup);
619 FETCH CHECK_STMT_ALREADY_ADDED INTO l_check;
620 CLOSE CHECK_STMT_ALREADY_ADDED;
621
622 IF l_check = 0 THEN
623
624 UPDATE CS_KB_SET_ELES
625 SET Element_id = is_dup
626 WHERE set_id = l_new_set_id
627 AND element_id = eles.element_id;
628
629 ELSE
630
631 DELETE FROM CS_KB_SET_ELES
632 WHERE set_id = l_new_set_id
633 AND element_id = eles.element_id;
634
635 END IF;
636 --End of Change for Bug 5947078
637
638 END IF;
639
640 END LOOP;
641
642
643
644 -- Logic For Auto Obsolete Statements Starts
645 CS_KNOWLEDGE_AUDIT_PVT.Auto_Obsolete_For_Solution_Pub(p_set_number,
646 Get_Published_Set_Id(p_set_number));
647
648 -- Mark the new Published Solution Version for indexing
649 CS_KB_SYNC_INDEX_PKG.Mark_Idxs_on_Pub_Soln( p_set_number );
650
651 -- Populate the Solution Content cache
652 CS_KB_SYNC_INDEX_PKG.Populate_Soln_Content_Cache (l_new_set_id);
653
654 ELSIF (p_status = 'OBS') THEN
655 -- Logic For Auto Obsolete Statements Starts
656 CS_KNOWLEDGE_AUDIT_PVT.Auto_Obsolete_For_Solution_Obs(p_set_number,
657 Get_Obsoleted_Set_Id(p_set_number));
658
659 -- Mark the new OBS Solution for indexing
660 cs_kb_sync_index_pkg.Mark_Idxs_on_Obs_Soln( p_set_number );
661
662 END IF;
663
664 fnd_profile.get('CS_KB_ENABLE_ASAP_INDEXING', l_asap_idx_enabled);
665 IF ( l_asap_idx_enabled = 'Y' )
666 THEN
667 CS_KB_SYNC_INDEX_PKG.request_sync_km_indexes( l_request_id, l_return_status );
668 END IF;
669
670 END IF;
671
672 RETURN l_new_set_id;
673
674 END Clone_Solution;
675
676
677 FUNCTION Is_Status_Valid(
678 P_STATUS IN VARCHAR2)
679 RETURN VARCHAR2 IS
680 l_count pls_integer;
681 BEGIN
682
683 SELECT COUNT(*) INTO l_count
684 FROM cs_lookups
685 WHERE lookup_type = 'CS_KB_INTERNAL_CODES'
686 AND lookup_code = upper(p_status);
687 IF(l_count<1) THEN
688 RETURN fnd_api.g_false;
689 ELSE
690 RETURN fnd_api.g_true;
691 END IF;
692
693 RETURN fnd_api.g_true;
694
695 END Is_Status_Valid;
696
697 -- Api's used in 11.5.10 by OAF:
698
699
700 PROCEDURE Create_Solution(
701 X_SET_ID IN OUT NOCOPY NUMBER,
702 P_SET_TYPE_ID IN NUMBER,
703 P_NAME IN VARCHAR2,
704 P_STATUS IN VARCHAR2,
705 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
706 P_ATTRIBUTE1 IN VARCHAR2,
707 P_ATTRIBUTE2 IN VARCHAR2,
708 P_ATTRIBUTE3 IN VARCHAR2,
709 P_ATTRIBUTE4 IN VARCHAR2,
710 P_ATTRIBUTE5 IN VARCHAR2,
711 P_ATTRIBUTE6 IN VARCHAR2,
712 P_ATTRIBUTE7 IN VARCHAR2,
713 P_ATTRIBUTE8 IN VARCHAR2,
714 P_ATTRIBUTE9 IN VARCHAR2,
715 P_ATTRIBUTE10 IN VARCHAR2,
716 P_ATTRIBUTE11 IN VARCHAR2,
717 P_ATTRIBUTE12 IN VARCHAR2,
718 P_ATTRIBUTE13 IN VARCHAR2,
719 P_ATTRIBUTE14 IN VARCHAR2,
720 P_ATTRIBUTE15 IN VARCHAR2,
721 X_SET_NUMBER OUT NOCOPY VARCHAR2,
722 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
723 X_MSG_DATA OUT NOCOPY VARCHAR2,
724 X_MSG_COUNT OUT NOCOPY NUMBER,
725 P_VISIBILITY_ID IN NUMBER )
726 IS
727 l_date DATE;
728 l_created_by NUMBER;
729 l_login NUMBER;
730 l_count PLS_INTEGER;
731 l_rowid VARCHAR2(30);
732 l_status VARCHAR2(30);
733 l_set_id NUMBER;
734 l_set_number VARCHAR2(30);
735 l_ret_status VARCHAR2(1);
736 l_msg VARCHAR2(2000);
737 l_dummy VARCHAR2(1) := null;
738 l_vis_count NUMBER;
739
740 Cursor check_active_type_csr(p_type_id IN NUMBER) Is
741 select 'X' from cs_kb_set_types_b
742 where set_type_id = p_type_id
743 and trunc(sysdate) between trunc(nvl(start_date_active, sysdate))
744 and trunc(nvl(end_date_active, sysdate));
745
746 Cursor Check_Visibility IS
747 SELECT count(*)
748 FROM CS_KB_VISIBILITIES_B
749 WHERE Visibility_Id = p_visibility_id
750 AND sysdate BETWEEN nvl(Start_Date_Active, sysdate-1)
751 AND nvl(End_Date_Active, sysdate+1);
752
753
754 BEGIN
755 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
756
757 -- Check Security
758 OPEN Check_Visibility;
759 FETCH Check_Visibility INTO l_vis_count;
760 CLOSE Check_Visibility;
761
762 IF l_vis_count = 0 THEN
763 FND_MSG_PUB.initialize;
764 FND_MESSAGE.set_name('CS', 'CS_KB_INV_SOLN_VIS');
765 FND_MSG_PUB.ADD;
766 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
767 X_SET_ID := -1;
768 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
769 p_count => X_MSG_COUNT,
770 p_data => X_MSG_DATA);
771 ELSE
772 -- check params
773 IF(p_set_type_id IS NULL OR p_name IS NULL) THEN
774 FND_MSG_PUB.initialize;
775 FND_MESSAGE.set_name('CS', 'CS_KB_C_MISS_PARAM');
776 FND_MSG_PUB.ADD;
777 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
778 X_SET_ID := -1;
779 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
780 p_count => X_MSG_COUNT,
781 p_data => X_MSG_DATA);
782
783 ELSE
784 -- IF type exists
785 SELECT COUNT(*) INTO l_count
786 FROM CS_KB_SET_TYPES_B
787 WHERE set_type_id = p_set_type_id;
788
789 IF(l_count <1) THEN
790 FND_MSG_PUB.initialize;
791 FND_MESSAGE.set_name('CS', 'CS_KB_C_INVALID_SET_TYPE_ID');
792 FND_MSG_PUB.ADD;
793 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
794 X_SET_ID := -2;
795 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
796 p_count => X_MSG_COUNT,
797 p_data => X_MSG_DATA);
798
799 ELSE
800 Open check_active_type_csr(p_set_type_id);
801 Fetch check_active_type_csr Into l_dummy;
802 Close check_active_type_csr;
803
804 IF l_dummy Is Null Then
805 FND_MSG_PUB.initialize;
806 FND_MESSAGE.set_name('CS', 'CS_KB_EXPIRED_SOLUTION_TYPE');
807 FND_MSG_PUB.ADD;
808 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
809 X_SET_ID := -4;
810 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
811 p_count => X_MSG_COUNT,
812 p_data => X_MSG_DATA);
813 ELSE
814 --check status
815 l_status := p_status;
816 IF(l_status IS NULL) THEN
817 l_status := 'SAV';
818 END IF;
819 --check unique set name IN audit table
820 SELECT COUNT(*) INTO l_count
821 FROM CS_KB_SETS_VL
822 WHERE name = p_name
823 AND status = 'PUB';
824
825 IF(l_count >0) THEN
826 FND_MSG_PUB.initialize;
827 FND_MESSAGE.set_name('CS', 'CS_KB_C_DUP_SET_NAME');
828 FND_MSG_PUB.ADD;
829 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
830 X_SET_ID := -3;
831 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
832 p_count => X_MSG_COUNT,
833 p_data => X_MSG_DATA);
834 ELSE
835
836 IF x_set_id IS NULL THEN
837 SELECT CS_KB_SETS_S.NEXTVAL INTO x_set_id FROM DUAL;
838 END IF;
839
840 SELECT TO_CHAR(CS_KB_SET_NUMBER_S.NEXTVAL) INTO x_set_number FROM DUAL;
841 LOOP
842 SELECT COUNT(set_number) INTO l_count
843 FROM CS_KB_SETS_B
844 WHERE set_number = x_set_number;
845 EXIT WHEN l_count = 0;
846 SELECT TO_CHAR(CS_KB_SET_NUMBER_S.NEXTVAL) INTO x_set_number FROM DUAL;
847 END LOOP;
848
849 IF x_set_id is NULL OR x_set_number is null THEN
850 FND_MSG_PUB.initialize;
851 FND_MESSAGE.set_name('CS', 'CS_KB_C_MISS_PARAM');
852 FND_MSG_PUB.ADD;
853 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
854 X_SET_ID := -1;
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
859 ELSE
860 l_date := SYSDATE;
861 l_created_by := FND_GLOBAL.user_id;
862 l_login := FND_GLOBAL.login_id;
863
864 CS_KB_SETS_PKG.Insert_Row( x_rowid => l_rowid,
865 x_set_id => x_set_id,
866 x_set_number => x_set_number,
867 x_set_type_id => p_set_type_id,
868 x_set_name => NULL,
869 x_group_flag => NULL,
870 x_status => l_status,
871 x_access_level => null,
872 x_name => p_name,
873 x_description => null,
874 x_creation_date => l_date,
875 x_created_by => l_created_by,
876 x_last_update_date => l_date,
877 x_last_updated_by => l_created_by,
878 x_last_update_login => l_login,
879 x_locked_by => l_created_by,
880 x_lock_date => NULL,
881 x_attribute_category => p_attribute_category,
882 x_attribute1 => p_attribute1,
883 x_attribute2 => p_attribute2,
884 x_attribute3 => p_attribute3,
885 x_attribute4 => p_attribute4,
886 x_attribute5 => p_attribute5,
887 x_attribute6 => p_attribute6,
888 x_attribute7 => p_attribute7,
889 x_attribute8 => p_attribute8,
890 x_attribute9 => p_attribute9,
891 x_attribute10 => p_attribute10,
892 x_attribute11 => p_attribute11,
893 x_attribute12 => p_attribute12,
894 x_attribute13 => p_attribute13,
895 x_attribute14 => p_attribute14,
896 x_attribute15 => p_attribute15,
897 x_employee_id => NULL,
898 x_party_id => NULL,
899 x_start_active_date => NULL,
900 x_end_active_date => NULL,
901 x_priority_code => 4,
902 x_visibility_id => p_visibility_id );
903
904 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
905
906 UPDATE CS_KB_SETS_B
907 SET LATEST_VERSION_FLAG = null --'N'
908 WHERE SET_NUMBER = x_set_number
909 AND SET_ID <> x_set_id;
910
911 UPDATE CS_KB_SETS_B
912 SET VIEWABLE_VERSION_FLAG = decode(status, 'PUB','Y',null) --'N')
913 WHERE SET_NUMBER = x_set_number;
914
915 -- BugFix 4013998 - Sequence Id Fix
916 UPDATE CS_KB_SETS_B s
917 SET s.VIEWABLE_VERSION_FLAG = 'Y'
918 WHERE s.SET_NUMBER = x_set_number
919 AND s.STATUS <> 'OBS'
920 AND s.latest_version_flag = 'Y'
921 AND NOT EXISTS (SELECT 'x'
922 FROM CS_KB_SETS_B s3
923 WHERE s3.set_number = s.set_number
924 AND s3.STATUS = 'PUB');
925
926 END IF;
927
928 END IF;
929
930 END IF;
931
932 END IF;
933
934 END IF;
935
936 END IF; -- Security Check
937
938 EXCEPTION
939 WHEN OTHERS THEN
940 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
941 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
942
943 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
944 p_data => x_msg_data);
945 END Create_Solution;
946
947
948 PROCEDURE Update_Solution(
949 P_SET_ID IN NUMBER,
950 P_SET_NUMBER IN VARCHAR2,
951 P_SET_TYPE_ID IN NUMBER,
952 P_NAME IN VARCHAR2,
953 P_STATUS IN VARCHAR2,
954 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
955 P_ATTRIBUTE1 IN VARCHAR2,
956 P_ATTRIBUTE2 IN VARCHAR2,
957 P_ATTRIBUTE3 IN VARCHAR2,
958 P_ATTRIBUTE4 IN VARCHAR2,
959 P_ATTRIBUTE5 IN VARCHAR2,
960 P_ATTRIBUTE6 IN VARCHAR2,
961 P_ATTRIBUTE7 IN VARCHAR2,
962 P_ATTRIBUTE8 IN VARCHAR2,
963 P_ATTRIBUTE9 IN VARCHAR2,
964 p_attribute10 IN VARCHAR2,
965 P_ATTRIBUTE11 IN VARCHAR2,
966 P_ATTRIBUTE12 IN VARCHAR2,
967 P_ATTRIBUTE13 IN VARCHAR2,
968 P_ATTRIBUTE14 IN VARCHAR2,
969 P_ATTRIBUTE15 IN VARCHAR2,
970 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
971 X_MSG_DATA OUT NOCOPY VARCHAR2,
972 X_MSG_COUNT OUT NOCOPY NUMBER,
973 P_VISIBILITY_ID IN NUMBER )
974 IS
975 l_ret NUMBER;
976 l_date DATE;
977 l_updated_by NUMBER;
978 l_login NUMBER;
979 l_count PLS_INTEGER;
980
981 l_locked_by NUMBER;
982 l_lock_date DATE;
983 l_vis_count NUMBER;
984
985 --SEDATE
986 l_dummy VARCHAR2(1) := null;
987 Cursor check_active_type_csr(p_type_id IN NUMBER) Is
988 select 'X' from cs_kb_set_types_b
989 where set_type_id = p_type_id
990 and trunc(sysdate) between trunc(nvl(start_date_active, sysdate))
991 and trunc(nvl(end_date_active, sysdate));
992
993 Cursor validate_old_type_used_csr(p_type_id IN NUMBER, p_set_id IN NUMBER) Is
994 select 'x' from CS_KB_SETS_B
995 where set_id = p_set_id
996 and set_type_id = p_type_id;
997
998 Cursor Check_Visibility IS
999 SELECT count(*)
1000 FROM CS_KB_VISIBILITIES_B
1001 WHERE Visibility_Id = p_visibility_id
1002 AND sysdate BETWEEN nvl(Start_Date_Active, sysdate-1)
1003 AND nvl(End_Date_Active, sysdate+1);
1004
1005 BEGIN
1006 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1007
1008 -- Check Security
1009 OPEN Check_Visibility;
1010 FETCH Check_Visibility INTO l_vis_count;
1011 CLOSE Check_Visibility;
1012
1013 IF l_vis_count = 0 THEN
1014 FND_MSG_PUB.initialize;
1015 FND_MESSAGE.set_name('CS', 'CS_KB_INV_SOLN_VIS');
1016 FND_MSG_PUB.ADD;
1017 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1018 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1019 p_count => X_MSG_COUNT,
1020 p_data => X_MSG_DATA);
1021 ELSE
1022 -- validate params
1023 IF(p_set_number IS NULL OR p_set_id IS NULL OR p_set_type_id IS NULL ) THEN
1024 FND_MSG_PUB.initialize;
1025 FND_MESSAGE.set_name('CS', 'CS_KB_C_MISS_PARAM');
1026 FND_MSG_PUB.ADD;
1027 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1028 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1029 p_count => X_MSG_COUNT,
1030 p_data => X_MSG_DATA);
1031 ELSE
1032
1033 SELECT COUNT(*) INTO l_count
1034 FROM CS_KB_SET_TYPES_B
1035 WHERE set_type_id = p_set_type_id;
1036
1037 IF(l_count <1) THEN
1038 FND_MSG_PUB.initialize;
1039 FND_MESSAGE.set_name('CS', 'CS_KB_C_INVALID_SET_TYPE_ID');
1040 FND_MSG_PUB.ADD;
1041 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1042 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1043 p_count => X_MSG_COUNT,
1044 p_data => X_MSG_DATA);
1045
1046 ELSE
1047 --SEDATE
1048 Open check_active_type_csr(p_set_type_id);
1049 Fetch check_active_type_csr Into l_dummy;
1050 Close check_active_type_csr;
1051
1052 If l_dummy Is Null Then
1053 -- Check whether the p_set_type_id is same as the set_type_id in the solution.
1054 -- If yes, let it pass because it is a modification to a solution of which the expired
1055 -- solution type was active at the time when the solution was created.
1056 Open validate_old_type_used_csr(p_set_type_id, p_set_id);
1057 Fetch validate_old_type_used_csr Into l_dummy;
1058 Close validate_old_type_used_csr;
1059 If l_dummy Is Null Then
1060 FND_MSG_PUB.initialize;
1061 FND_MESSAGE.set_name('CS', 'CS_KB_END_DATED_TYPE');
1062 FND_MSG_PUB.ADD;
1063 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1064 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1065 p_count => X_MSG_COUNT,
1066 p_data => X_MSG_DATA);
1067 End If;
1068 End If;
1069
1070 IF l_dummy is not null THEN
1071
1072 -- IF status valid
1073 IF(Is_Status_Valid(p_status) = FND_API.g_false) THEN
1074 FND_MSG_PUB.initialize;
1075 FND_MESSAGE.set_name('CS', 'CS_KB_C_INVALID_SET_STATUS');
1076 FND_MSG_PUB.ADD;
1077 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1078 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1079 p_count => X_MSG_COUNT,
1080 p_data => X_MSG_DATA);
1081 ELSE
1082
1083 --check unique set name IN audit table (except those with same set_number)
1084 SELECT COUNT(*) INTO l_count
1085 FROM CS_KB_SETS_VL
1086 WHERE name = p_name
1087 AND status = 'PUB'
1088 AND set_number <> p_set_number;
1089
1090 IF(l_count >0) THEN
1091 FND_MSG_PUB.initialize;
1092 FND_MESSAGE.set_name('CS', 'CS_KB_C_DUP_SET_NAME');
1093 FND_MSG_PUB.ADD;
1094 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1095 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1096 p_count => X_MSG_COUNT,
1097 p_data => X_MSG_DATA);
1098 ELSE
1099
1100 --prepare data, THEN insert new set
1101 l_date := SYSDATE;
1102 l_updated_by := FND_GLOBAL.user_id;
1103 l_login := FND_GLOBAL.login_id;
1104
1105 Get_Lock_Info(p_set_number, l_locked_by, l_lock_date);
1106
1107 CS_KB_SETS_PKG.Update_Row(
1108 x_set_id => p_set_id,
1109 x_set_number => p_set_number,
1110 x_set_type_id => p_set_type_id,
1111 x_set_name => NULL,
1112 x_group_flag => NULL,
1113 x_status => p_status,
1114 x_access_level => null,
1115 x_name => p_name,
1116 x_description => null,
1117 x_last_update_date => l_date,
1118 x_last_updated_by => l_updated_by,
1119 x_last_update_login => l_login,
1120 x_locked_by => l_locked_by,
1121 x_lock_date => l_lock_date,
1122 x_attribute_category => p_attribute_category,
1123 x_attribute1 => p_attribute1,
1124 x_attribute2 => p_attribute2,
1125 x_attribute3 => p_attribute3,
1126 x_attribute4 => p_attribute4,
1127 x_attribute5 => p_attribute5,
1128 x_attribute6 => p_attribute6,
1129 x_attribute7 => p_attribute7,
1130 x_attribute8 => p_attribute8,
1131 x_attribute9 => p_attribute9,
1132 x_attribute10 => p_attribute10,
1133 x_attribute11 => p_attribute11,
1134 x_attribute12 => p_attribute12,
1135 x_attribute13 => p_attribute13,
1136 x_attribute14 => p_attribute14,
1137 x_attribute15 => p_attribute15,
1138 x_employee_id => null,
1139 x_party_id => null,
1140 x_start_active_date => null,
1141 x_end_active_date => null,
1142 x_priority_code => 4,
1143 x_visibility_id => p_visibility_id );
1144
1145 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1146
1147 END IF; -- check unique solution title (except those with same set_number)
1148
1149 END IF; --valid status
1150
1151 END IF; --valid type
1152
1153 END IF; --valid set_type_id passed in
1154
1155 END IF; --required params passed in
1156
1157 END IF; -- Security Visibility Check
1158
1159 EXCEPTION
1160 WHEN OTHERS THEN
1161 FND_MESSAGE.set_name('CS', 'CS_KB_C_UPDATE_ERR');
1162 FND_MSG_PUB.ADD;
1163 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1164 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1165 p_count => X_MSG_COUNT,
1166 p_data => X_MSG_DATA);
1167 END Update_Solution;
1168
1169
1170 -- Submit_Solution is an Internal api used to submit a solution. This api has the
1171 -- following steps:
1172 -- 1) Replace Duplicate Statements
1173 -- 2) Clone Solution
1174 -- 3) Send Notification via Oracle WorkFlow
1175
1176 PROCEDURE Submit_Solution(
1177 P_SET_NUMBER IN VARCHAR2,
1178 P_SET_ID IN NUMBER ,
1179 P_NEW_STEP IN NUMBER ,
1180 X_RETURN OUT NOCOPY NUMBER,
1181 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1182 X_MSG_DATA OUT NOCOPY VARCHAR2,
1183 X_MSG_COUNT OUT NOCOPY NUMBER )
1184 IS
1185
1186 CURSOR GET_STATEMENTS_FOR_DUP_CHECK(v_set_id IN NUMBER) IS
1187 SELECT se.element_id, e.element_number
1188 FROM CS_KB_SET_ELES se,
1189 CS_KB_ELEMENTS_B e
1190 WHERE se.set_id = v_set_id
1191 AND se.element_id = e.element_id
1192 AND e.status <> 'PUBLISHED';
1193
1194 CURSOR Check_Dup_On_Current_Soln (v_set_id IN NUMBER,
1195 v_dup_element_id IN NUMBER) IS
1196 SELECT count(*)
1197 FROM CS_KB_SET_ELES
1198 WHERE set_id = v_set_id
1199 AND element_id = v_dup_element_id;
1200
1201 l_dup_element_id NUMBER;
1202 l_delete_status NUMBER;
1203 l_results NUMBER;
1204 l_errormsg VARCHAR2(2000);
1205 -- test varchar2(10);
1206 l_dup_count NUMBER;
1207
1208 BEGIN
1209 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1210 X_RETURN := -1;
1211 SAVEPOINT START_OF_SUBMIT;
1212
1213 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1214 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'csk.plsql.CS_KB_SOLUTION_PVT.Submit_Solution.Start',
1215 'Submit Solution started for set id- '||P_SET_ID );
1216 END IF;
1217
1218 -- Firstly perform duplicate statement checking against exisiting
1219 -- Published statements. If duplicates exist
1220 -- then remove and replace set_ele link with original PUB statement
1221
1222 FOR rec IN GET_STATEMENTS_FOR_DUP_CHECK(p_set_id) LOOP
1223 l_dup_element_id := CS_KB_ELEMENTS_AUDIT_PKG.Is_Element_Created_Dup(rec.element_id);
1224
1225 IF l_dup_element_id <> 0 THEN
1226
1227 -- Check if the Duplicate has already been added to the solution already
1228 OPEN Check_Dup_On_Current_Soln (p_set_id, l_dup_element_id);
1229 FETCH Check_Dup_On_Current_Soln INTO l_dup_count;
1230 CLOSE Check_Dup_On_Current_Soln;
1231 IF l_dup_count = 0 THEN
1232 -- If Dup doesnt already exist
1233 UPDATE CS_KB_SET_ELES
1234 SET ELEMENT_ID = l_dup_element_id,
1235 LAST_UPDATE_DATE = sysdate,
1236 LAST_UPDATED_BY = FND_GLOBAL.user_id,
1237 LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
1238 WHERE set_id = p_set_id
1239 AND element_id = rec.element_id;
1240
1241 l_delete_status := CS_KB_ELEMENTS_AUDIT_PKG.Delete_Element(rec.element_number);
1242 -- No need to check delete status - if delete not valid ie statement shared then ignore
1243 ELSE
1244 -- If Dup does already exist
1245 DELETE FROM CS_KB_SET_ELES
1246 WHERE Set_Id = p_set_id
1247 AND element_id = rec.element_id;
1248
1249 END IF;
1250
1251 END IF;
1252
1253 END LOOP;
1254
1255 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1256 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'csk.plsql.CS_KB_SOLUTION_PVT.Submit_Solution',
1257 'After Dup Check before Start_Wf - '||P_SET_ID );
1258 END IF;
1259
1260 -- Start_Wf is an Internal api that performs the following:
1261 -- 1) Clone Solution
1262 -- 2) Send Notification via Oracle WorkFlow
1263
1264 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1265
1266 CS_KB_WF_PKG.Start_Wf( p_set_number => p_set_number,
1267 p_set_id => p_set_id,
1268 p_new_step => p_new_step,
1269 p_results => l_results,
1270 p_errormsg => l_errormsg);
1271
1272 IF l_results < 1 THEN
1273 ROLLBACK TO START_OF_SUBMIT;
1274 X_RETURN := -1; -- 'CS_KB_C_MISS_PARAM'
1275 FND_MSG_PUB.initialize;
1276 FND_MESSAGE.set_name('CS', 'CS_KB_C_ERROR_WF_API');
1277 FND_MESSAGE.SET_TOKEN(TOKEN => 'ERROR_MSG',
1278 VALUE => l_errormsg,
1279 TRANSLATE => true);
1280 FND_MSG_PUB.ADD;
1281 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1282 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1283 p_count => X_MSG_COUNT,
1284 p_data => X_MSG_DATA);
1285 END IF;
1286
1287 X_RETURN := l_results;
1288
1289 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1290 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'csk.plsql.CS_KB_SOLUTION_PVT.Submit_Solution.Finish',
1291 'Submit Solution finished for set id- '||P_SET_ID );
1292 END IF;
1293
1294 EXCEPTION
1295 WHEN OTHERS THEN
1296 X_RETURN := -1;
1297 ROLLBACK TO START_OF_SUBMIT;
1298
1299 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1300 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'csk.plsql.CS_KB_SOLUTION_PVT.Submit_Solution.Unexpected',
1301 'Unexpected Exception for set id- '||P_SET_ID||' '||substrb(sqlerrm,1,200) );
1302 END IF;
1303
1304 FND_MSG_PUB.initialize;
1305 FND_MESSAGE.set_name('CS', 'CS_KB_C_ERROR_WF_API');
1306 FND_MESSAGE.SET_TOKEN(TOKEN => 'ERROR_MSG',
1307 VALUE => SQLERRM,
1308 TRANSLATE => true);
1309 FND_MSG_PUB.ADD;
1310 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1311 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1312 p_count => X_MSG_COUNT,
1313 p_data => X_MSG_DATA);
1314
1315 END Submit_Solution;
1316
1317 -- BugFix 4013998 - Sequence Id Fix
1318 FUNCTION Get_User_Soln_Access (
1319 P_SET_ID IN NUMBER,
1320 P_SET_NUMBER IN VARCHAR2 )
1321 RETURN VARCHAR2 IS
1322
1323 -- Changed to use lvf, removed select max
1324 CURSOR Get_Latest_Soln_From_Id IS
1325 SELECT Set_Id
1326 FROM CS_KB_SETS_B
1327 WHERE Set_Number = (SELECT Set_Number
1328 FROM CS_KB_SETS_B
1329 WHERE Set_Id = P_SET_ID)
1330 AND latest_version_flag = 'Y';
1331
1332 -- Changed to use lvf, removed select max
1333 CURSOR Get_Latest_Soln_From_Num IS
1334 SELECT Set_Id
1335 FROM CS_KB_SETS_B
1336 WHERE Set_Number = P_SET_NUMBER
1337 AND latest_version_flag = 'Y';
1338
1339 CURSOR Get_Soln_Attributes (v_set_id NUMBER) IS
1340 SELECT Status, locked_by, flow_details_id
1341 FROM CS_KB_SETS_B
1342 WHERE Set_Id = v_set_id;
1343
1344 l_default_user NUMBER := -1;
1345 l_current_user NUMBER := FND_GLOBAL.user_id;
1346 l_locked_by NUMBER;
1347 l_flow_details_id NUMBER;
1348 l_latest_set_id NUMBER;
1349 l_status VARCHAR2(30);
1350 Is_Full_Soln_Viewable VARCHAR2(10);
1351 l_result NUMBER;
1352 l_button VARCHAR2(15);
1353 l_set_id NUMBER;
1354
1355 BEGIN
1356 l_button := 'NOBUTTON';
1357 l_set_id := -1;
1358 l_latest_set_id := -2;
1359
1360 -- Firstly Check the Set_Id for the Solution and determine if this is the
1361 -- latest version.
1362 IF P_SET_ID IS NOT NULL THEN
1363
1364 l_set_id := P_SET_ID;
1365
1366 OPEN Get_Latest_Soln_From_Id;
1367 FETCH Get_Latest_Soln_From_Id INTO l_latest_set_id;
1368 CLOSE Get_Latest_Soln_From_Id;
1369
1370 ELSIF P_SET_NUMBER IS NOT NULL AND P_SET_ID IS NULL THEN
1371
1372 OPEN Get_Latest_Soln_From_Num;
1373 FETCH Get_Latest_Soln_From_Num INTO l_latest_set_id;
1374 CLOSE Get_Latest_Soln_From_Num;
1375 l_set_id := l_latest_set_id;
1376
1377 END IF;
1378
1379
1380 IF l_latest_set_id = l_set_id THEN
1381 -- The Current Set_Id is the Latest Version
1382
1383 -- Now check the Status of the current version
1384 OPEN Get_Soln_Attributes (l_set_id);
1385 FETCH Get_Soln_Attributes INTO l_status, l_locked_by, l_flow_details_id;
1386 CLOSE Get_Soln_Attributes;
1387
1388 -- Now Check if the current User can view the Full Solution
1389 Is_Full_Soln_Viewable := CS_KB_SECURITY_PVT.IS_COMPLETE_SOLUTION_VISIBLE
1390 ( l_default_user,
1391 --l_current_user,
1392 l_set_id);
1393 IF Is_Full_Soln_Viewable = 'TRUE' THEN
1394 -- Full Solution is Viewable
1395
1396
1397 IF l_status = 'PUB' THEN
1398 -- If the Latest is Published no need to check Resource Group
1399 -- as anyone can edit it
1400
1401 l_button := 'CHECKOUT';
1402
1403 ELSE
1404 -- Latest is Not Published or Solution is Locked
1405
1406 CS_KB_WF_PKG.Get_Permissions(l_set_id, l_current_user, l_result);
1407
1408 IF l_result = 2 THEN
1409 -- The Current User has the Solution Locked
1410 l_button := 'EDIT';
1411 ELSIF l_result = 1 THEN
1412 -- Solution is in a Workflow and is not locked
1413 -- The Current user is also able to Check Out the Solution
1414 l_button := 'CHECKOUT';
1415 ELSIF l_result = 0 THEN
1416 -- The Current User either does not have the lock or
1417 -- doesnt have permission to CheckOut the Solution
1418
1419 IF l_locked_by <> l_current_user AND
1420 l_locked_by <> -1 THEN
1421 -- Solution is locked by another user
1422 l_button := 'GETLOCK';
1423
1424 -- Note: The UI will check the presence of the GET_LOCK Function
1425 -- to determine whether the User has permission to see the Get Lock
1426 -- button.
1427 END IF;
1428
1429 IF l_locked_by = -1 AND
1430 l_flow_details_id IS NULL THEN
1431 -- The Solution is not locked and is not currently in a flow
1432 -- This situation will occur when a draft solution is unlocked
1433 -- before it is submitted to a flow
1434 l_button := 'CHECKOUT';
1435 END IF;
1436
1437 END IF;
1438
1439 END IF;
1440
1441 ELSE
1442 -- Full Solution is not Viewable
1443 IF l_locked_by = l_current_user THEN
1444 l_button := 'LOCKEDNOACCESS';
1445 ELSE
1446 l_button := 'NOBUTTON';
1447 END IF;
1448
1449
1450 END IF;
1451
1452
1453 ELSE
1454 -- This is not the latest version - Do Not show any Buttons
1455 l_button := 'NOBUTTON';
1456 END IF;
1457
1458 RETURN l_button;
1459
1460 EXCEPTION
1461 WHEN OTHERS THEN
1462
1463 RETURN 'NOBUTTON';
1464
1465 END Get_User_Soln_Access;
1466
1467 PROCEDURE CheckOut_Solution(
1468 P_SET_ID IN NUMBER ,
1469 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1470 X_MSG_DATA OUT NOCOPY VARCHAR2,
1471 X_MSG_COUNT OUT NOCOPY NUMBER )
1472 IS
1473
1474 CURSOR Get_Soln_Attributes (v_set_id NUMBER) IS
1475 SELECT Status, locked_by
1476 FROM CS_KB_SETS_B
1477 WHERE Set_Id = v_set_id;
1478
1479 l_current_user NUMBER := FND_GLOBAL.user_id;
1480 l_locked_by NUMBER;
1481 l_set_number VARCHAR2(30);
1482 l_latest_set_id NUMBER;
1483 l_status VARCHAR2(30);
1484 l_new_set_id NUMBER;
1485
1486 BEGIN
1487
1488 -- Check that the Solution Version is the latest
1489 l_set_number := get_set_number(p_set_id);
1490 l_latest_set_id := get_latest_version_id(l_set_number);
1491
1492 IF P_SET_ID = l_latest_set_id THEN
1493
1494 -- Check the Solution Status
1495 OPEN Get_Soln_Attributes (P_SET_ID);
1496 FETCH Get_Soln_Attributes INTO l_status, l_locked_by;
1497 CLOSE Get_Soln_Attributes;
1498
1499 IF l_status = 'PUB' THEN
1500 -- If Current version is Published
1501 --> Clone Solution and assign current user to it
1502 l_new_set_id := clone_solution(l_set_number,
1503 'SAV',
1504 NULL, --p_flow_details_id
1505 l_current_user);
1506
1507 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1508
1509 ELSE
1510 -- If Not Published assign the lock to the current version for
1511 -- the current user
1512
1513 IF (l_locked_by = -1) THEN
1514 -- Soln is unlocked so lock with current user
1515 Set_Lock_Info(p_set_id, l_current_user, sysdate);
1516 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1517
1518 ELSIF (l_locked_by = l_current_user) THEN
1519 -- Current User already has the lock, ignore - do nothing
1520 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1521 ELSE
1522 -- Another user has locked the Solution
1523 -- return unsuccessful
1524 FND_MSG_PUB.initialize;
1525 FND_MESSAGE.set_name('CS', 'CS_KB_C_SOL_LOCKED_BY_USER');
1526 FND_MSG_PUB.ADD;
1527 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1528 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1529 p_count => X_MSG_COUNT,
1530 p_data => X_MSG_DATA);
1531
1532 END IF;
1533
1534 END IF;
1535
1536 ELSE
1537 -- return unsuccessful
1538 FND_MSG_PUB.initialize;
1539 FND_MESSAGE.set_name('CS', 'CS_KB_CHECK_OUT_CHANGED');
1540 FND_MSG_PUB.ADD;
1541 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1542 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1543 p_count => X_MSG_COUNT,
1544 p_data => X_MSG_DATA);
1545 END IF;
1546 EXCEPTION
1547 WHEN OTHERS THEN
1548 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
1549 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1550 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1551 p_data => x_msg_data);
1552 END CheckOut_Solution;
1553
1554 --Move given solutions from source category to destnation category.
1555 --If P_SRC_CAT_ID is -1, solutions will simply be added to the destination
1556 --category. Otherwise, the existing link to P_SRC_CAT_ID will be removed.
1557 --
1558 PROCEDURE Move_Solutions(
1559 p_api_version in number,
1560 p_init_msg_list in varchar2 := FND_API.G_FALSE,
1561 p_commit in varchar2 := FND_API.G_FALSE,
1562 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
1563 x_return_status OUT NOCOPY varchar2,
1564 x_msg_count OUT NOCOPY number,
1565 x_msg_data OUT NOCOPY varchar2,
1566 P_SET_IDS IN JTF_NUMBER_TABLE,
1567 P_SRC_CAT_ID IN NUMBER,
1568 P_DEST_CAT_ID IN NUMBER)
1569 IS
1570
1571 CURSOR Is_Linked (cp_set_id NUMBER) IS
1572 SELECT count(1)
1573 FROM CS_KB_SET_CATEGORIES
1574 WHERE Set_Id = cp_set_id
1575 AND Category_Id = P_DEST_CAT_ID;
1576
1577 CURSOR Is_Category_Existing (cp_cat_id NUMBER) IS
1578 SELECT count(1)
1579 FROM CS_KB_SOLN_CATEGORIES_B
1580 WHERE Category_Id = cp_cat_id;
1581
1582 l_set_id NUMBER;
1583 l_cat_id NUMBER;
1584 l_count NUMBER;
1585
1586 BEGIN
1587 SAVEPOINT MOVE_SOLUTIONS;
1588 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1589 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1590 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'csk.plsql.CS_KB_SOLUTION_PVT.Move_Solutions.begin',
1591 'User='||fnd_global.user_id);
1592 END IF;
1593
1594 --Validate categories' id
1595 Open Is_Category_Existing(P_DEST_CAT_ID);
1596 Fetch Is_Category_Existing Into l_count;
1597 Close Is_Category_Existing;
1598
1599 If l_count = 0 Then
1600 l_cat_id := P_DEST_CAT_ID;
1601 Raise INVALID_CATEGORY_ID;
1602 End If;
1603
1604 If P_SRC_CAT_ID > 0 Then
1605 Open Is_Category_Existing(P_SRC_CAT_ID);
1606 Fetch Is_Category_Existing Into l_count;
1607 Close Is_Category_Existing;
1608
1609 If l_count = 0 Then
1610 l_cat_id := P_SRC_CAT_ID;
1611 Raise INVALID_CATEGORY_ID;
1612 End If;
1613 End If;
1614
1615 --Clear existing links and create new links
1616 for i in 1..p_set_ids.count loop
1617 l_set_id := p_set_ids(i);
1618
1619 --Remove old link.
1620 If P_SRC_CAT_ID <> -1 Then
1621 delete from cs_kb_set_categories
1622 where set_id = l_set_id and category_id = P_SRC_CAT_ID;
1623 If SQL%notfound Then
1624 l_cat_id := P_SRC_CAT_ID;
1625 Raise INVALID_SET_CATEGORY_LINK;
1626 End If;
1627 End If;
1628
1629 --Check if new link already existing.
1630 Open Is_Linked(l_set_id);
1631 Fetch Is_Linked Into l_count;
1632 Close Is_Linked;
1633
1634 --If not linked create this link.
1635 if(l_count = 0) then
1636 insert into cs_kb_set_categories
1637 (
1638 set_id,
1639 category_id,
1640 creation_date,
1641 created_by,
1642 last_update_date,
1643 last_updated_by,
1644 last_update_login
1645 )
1646 values
1647 (
1648 l_set_id,
1649 P_DEST_CAT_ID,
1650 sysdate,
1651 fnd_global.user_id,
1652 sysdate,
1653 fnd_global.user_id,
1654 fnd_global.login_id
1655 );
1656 end if;
1657 end loop;
1658
1659 --Mark solution for index update.
1660 CS_KB_SYNC_INDEX_PKG.Mark_Idxs_For_Multi_Soln(P_SET_IDS);
1661
1662 --Return.
1663 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1664
1665 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1666 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'csk.plsql.CS_KB_SOLUTION_PVT.Move_Solutions.begin',
1667 'Status='||X_RETURN_STATUS);
1668 END IF;
1669
1670 if fnd_api.to_boolean( p_commit ) then
1671 commit;
1672 end if;
1673
1674 EXCEPTION
1675 WHEN INVALID_CATEGORY_ID THEN
1676 ROLLBACK TO MOVE_SOLUTIONS;
1677 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1678 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'csk.plsql.CS_KB_SECURITY_PVT.Move_Solutions.validate_parameters',
1679 'Invalid category ID:'||l_cat_id);
1680 END IF;
1681
1682 FND_MSG_PUB.initialize;
1683 FND_MESSAGE.set_name('CS', 'CS_KB_INV_API_SOLN_CAT_ID');
1684 FND_MSG_PUB.ADD;
1685 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1686 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1687 p_count => X_MSG_COUNT,
1688 p_data => X_MSG_DATA);
1689 WHEN INVALID_SET_CATEGORY_LINK THEN
1690 ROLLBACK TO MOVE_SOLUTIONS;
1691 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1692 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'csk.plsql.CS_KB_SECURITY_PVT.Move_Solutions.update_link',
1693 'Invalid link (set_id,category_id): ('||l_set_id||','||l_cat_id||')');
1694 END IF;
1695
1696 FND_MSG_PUB.initialize;
1697 FND_MESSAGE.set_name('CS', 'CS_KB_INV_SET_CAT_LINK');
1698 FND_MSG_PUB.ADD;
1699 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1700 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1701 p_count => X_MSG_COUNT,
1702 p_data => X_MSG_DATA);
1703 WHEN OTHERS THEN
1704 ROLLBACK TO MOVE_SOLUTIONS;
1705 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
1706 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1707 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1708 p_data => x_msg_data);
1709 END Move_Solutions;
1710
1711 -- Unlock Locked Solutions in Draft Mode
1712 -- Start Bugfix 7117546
1713 PROCEDURE unlock_solution
1714 (
1715 p_set_id IN NUMBER,
1716 p_commit IN VARCHAR2 DEFAULT 'N',
1717 x_return_status OUT NOCOPY VARCHAR2,
1718 x_msg_data OUT NOCOPY VARCHAR2,
1719 x_msg_count OUT NOCOPY NUMBER)
1720 AS
1721 l_current_status VARCHAR2(3) ;
1722 l_pub_count NUMBER;
1723 l_set_number VARCHAR2(100) ;
1724 BEGIN
1725 SAVEPOINT CS_KB_ULOCK_SOLUTION;
1726
1727 SELECT set_number
1728 INTO l_set_number
1729 FROM cs_kb_sets_b
1730 WHERE set_id = p_set_id;
1731
1732 SELECT
1733 COUNT(*)
1734 INTO
1735 l_pub_count
1736 FROM
1737 cs_kb_sets_b
1738 WHERE
1739 status = 'PUB' AND
1740 set_number = l_set_number;
1741
1742 IF l_pub_count > 0 THEN
1743 SELECT status INTO l_current_status FROM cs_kb_sets_b WHERE set_id = p_set_id;
1744 IF l_current_status = 'NOT' THEN
1745 UPDATE CS_KB_SETS_B SET locked_by = - 1 WHERE set_id = p_set_id;
1746 ELSIF l_current_status = 'SAV' THEN
1747 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(
1748 x_entity_name => 'CS_KB_SETS_B',
1749 x_pk1_value => TO_CHAR(p_set_id),
1750 x_delete_document_flag => 'Y'
1751 ) ;
1752 DELETE FROM CS_KB_SET_CATEGORIES WHERE set_id = p_set_id;
1753 DELETE FROM CS_KB_SET_PRODUCTS WHERE set_id = p_set_id;
1754 DELETE FROM CS_KB_SET_PLATFORMS WHERE set_id = p_set_id;
1755 DELETE FROM CS_KB_SET_LINKS WHERE set_id = p_set_id;
1756 DELETE FROM CS_KB_SET_ELES WHERE set_id = p_set_id;
1757 DELETE FROM CS_KB_SETS_TL WHERE set_id = p_set_id;
1758 UPDATE
1759 CS_KB_SETS_B
1760 SET
1761 latest_version_flag = 'Y'
1762 WHERE
1763 status = 'PUB' AND
1764 set_number = l_set_number;
1765 DELETE FROM CS_KB_SETS_B WHERE set_id = p_set_id;
1766 END IF;
1767 IF p_commit = 'Y' THEN
1768 COMMIT;
1769 END IF;
1770 END IF;
1771 x_return_status := 'S';
1772 EXCEPTION
1773 WHEN OTHERS THEN
1774 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
1775 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1776 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1777 p_data => x_msg_data);
1778 ROLLBACK TO CS_KB_ULOCK_SOLUTION;
1779 END unlock_solution;
1780 -- End Bugfix 7117546
1781
1782 END CS_KB_SOLUTION_PVT;