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