[Home] [Help]
PACKAGE BODY: APPS.CSK_SETUP_UTILITY_PKG
Source
1 PACKAGE BODY CSK_SETUP_UTILITY_PKG
2 /* $Header: csktsub.pls 120.0 2005/06/01 11:46:35 appldev noship $ */
3 AS
4
5 -- Create Category Group
6 PROCEDURE Create_CG (P_ID NUMBER, P_NAME VARCHAR2)
7 IS
8 BEGIN
9 insert into CS_KB_CATEGORY_GROUPS_B (
10 CATEGORY_GROUP_ID,
11 CREATION_DATE,
12 CREATED_BY,
13 LAST_UPDATE_DATE,
14 LAST_UPDATED_BY,
15 LAST_UPDATE_LOGIN
16 ) values (
17 P_ID,
18 sysdate,
19 -1351,
20 sysdate,
21 -1351,
22 -1351 );
23
24 insert into CS_KB_CATEGORY_GROUPS_TL (
25 CATEGORY_GROUP_ID,
26 NAME,
27 DESCRIPTION,
28 CREATION_DATE,
29 CREATED_BY,
30 LAST_UPDATE_DATE,
31 LAST_UPDATED_BY,
32 LAST_UPDATE_LOGIN,
33 LANGUAGE,
34 SOURCE_LANG
35 ) select
36 P_ID,
37 P_NAME,
38 P_NAME,
39 sysdate,
40 -1351,
41 sysdate,
42 -1351,
43 -1351,
44 L.LANGUAGE_CODE,
45 userenv('LANG')
46 from FND_LANGUAGES L
47 where L.INSTALLED_FLAG in ('I', 'B')
48 and not exists
49 (select NULL
50 from CS_KB_CATEGORY_GROUPS_TL T
51 where T.CATEGORY_GROUP_ID = P_ID
52 and T.LANGUAGE = L.LANGUAGE_CODE);
53 END Create_CG;
54
55 --Create Solution Type
56 PROCEDURE Create_Soln_Type (P_ID NUMBER, P_NAME VARCHAR2)
57 IS
58 BEGIN
59 insert into CS_KB_SET_TYPES_B (
60 SET_TYPE_ID,
61 CREATION_DATE,
62 CREATED_BY,
63 LAST_UPDATE_DATE,
64 LAST_UPDATED_BY,
65 LAST_UPDATE_LOGIN
66 ) values (
67 P_ID,
68 sysdate,
69 -1351,
70 sysdate,
71 -1351,
72 -1351 );
73
74 insert into CS_KB_SET_TYPES_TL (
75 SET_TYPE_ID,
76 NAME,
77 DESCRIPTION,
78 CREATION_DATE,
79 CREATED_BY,
80 LAST_UPDATE_DATE,
81 LAST_UPDATED_BY,
82 LAST_UPDATE_LOGIN,
83 LANGUAGE,
84 SOURCE_LANG
85 ) select
86 P_ID,
87 P_NAME,
88 P_NAME,
89 sysdate,
90 -1351,
91 sysdate,
92 -1351,
93 -1351,
94 L.LANGUAGE_CODE,
95 userenv('LANG')
96 from FND_LANGUAGES L
97 where L.INSTALLED_FLAG in ('I', 'B')
98 and not exists
99 (select NULL
100 from CS_KB_SET_TYPES_TL T
101 where T.SET_TYPE_ID = P_ID
102 and T.LANGUAGE = L.LANGUAGE_CODE);
103 END Create_Soln_Type;
104
105 --Create statement type
106 PROCEDURE Create_Stmt_Type (P_ID NUMBER, P_NAME VARCHAR2)
107 IS
108 BEGIN
109 insert into CS_KB_ELEMENT_TYPES_B (
110 ELEMENT_TYPE_ID,
111 CREATION_DATE,
112 CREATED_BY,
113 LAST_UPDATE_DATE,
114 LAST_UPDATED_BY,
115 LAST_UPDATE_LOGIN
116 ) values (
117 P_ID,
118 sysdate,
119 -1351,
120 sysdate,
121 -1351,
122 -1351 );
123
124 insert into CS_KB_ELEMENT_TYPES_TL (
125 ELEMENT_TYPE_ID,
126 NAME,
127 DESCRIPTION,
128 CREATION_DATE,
129 CREATED_BY,
130 LAST_UPDATE_DATE,
131 LAST_UPDATED_BY,
132 LAST_UPDATE_LOGIN,
133 LANGUAGE,
134 SOURCE_LANG
135 ) select
136 P_ID,
137 P_NAME,
138 P_NAME,
139 sysdate,
140 -1351,
141 sysdate,
142 -1351,
143 -1351,
144 L.LANGUAGE_CODE,
145 userenv('LANG')
146 from FND_LANGUAGES L
147 where L.INSTALLED_FLAG in ('I', 'B')
148 and not exists
149 (select NULL
150 from CS_KB_ELEMENT_TYPES_TL T
151 where T.ELEMENT_TYPE_ID = P_ID
152 and T.LANGUAGE = L.LANGUAGE_CODE);
153 END Create_Stmt_Type;
154
155 --Create visibility
156 PROCEDURE Create_Visibility (P_ID NUMBER, P_NAME VARCHAR2, P_POSN NUMBER)
157 IS
158 BEGIN
159 insert into CS_KB_VISIBILITIES_B (
160 VISIBILITY_ID,
161 POSITION,
162 CREATION_DATE,
163 CREATED_BY,
164 LAST_UPDATE_DATE,
165 LAST_UPDATED_BY,
166 LAST_UPDATE_LOGIN
167 ) values (
168 P_ID,
169 P_POSN,
170 sysdate,
171 -1351,
172 sysdate,
173 -1351,
174 -1351 );
175
176 insert into CS_KB_VISIBILITIES_TL (
177 VISIBILITY_ID,
178 NAME,
179 DESCRIPTION,
180 CREATION_DATE,
181 CREATED_BY,
182 LAST_UPDATE_DATE,
183 LAST_UPDATED_BY,
184 LAST_UPDATE_LOGIN,
185 LANGUAGE,
186 SOURCE_LANG
187 ) select
188 P_ID,
189 P_NAME,
190 P_NAME,
191 sysdate,
192 -1351,
193 sysdate,
194 -1351,
195 -1351,
196 L.LANGUAGE_CODE,
197 userenv('LANG')
198 from FND_LANGUAGES L
199 where L.INSTALLED_FLAG in ('I', 'B')
200 and not exists
201 (select NULL
202 from CS_KB_VISIBILITIES_TL T
203 where T.VISIBILITY_ID = P_ID
204 and T.LANGUAGE = L.LANGUAGE_CODE);
205 END Create_Visibility;
206
207 -- Validate common data setup for: category group, solution type, statement
208 -- type, visibility, authoring flow.
209 PROCEDURE Validate_Seeded_Setups(
210 p_api_version in number,
211 p_init_msg_list in varchar2 := FND_API.G_FALSE,
212 p_commit in varchar2 := FND_API.G_FALSE,
213 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
214 x_return_status OUT NOCOPY varchar2,
215 x_msg_count OUT NOCOPY number,
216 x_msg_data OUT NOCOPY varchar2)
217 IS
218 l_max NUMBER;
219 BEGIN
220 -------------------------------------------------------------
221 -- Create Seeded Category Groups
222 -------------------------------------------------------------
223 DELETE FROM CS_KB_CATEGORY_GROUPS_B WHERE CATEGORY_GROUP_ID = CAT_GROUP_API_TEST_DEFAULT;
224 DELETE FROM CS_KB_CATEGORY_GROUPS_TL WHERE CATEGORY_GROUP_ID = CAT_GROUP_API_TEST_DEFAULT;
225 Create_CG (P_ID => CAT_GROUP_API_TEST_DEFAULT, P_NAME => 'Api_Test_Default_CG');
226
227 DELETE FROM CS_KB_CATEGORY_GROUPS_B WHERE CATEGORY_GROUP_ID = CAT_GROUP_API_TEST_CG1;
228 DELETE FROM CS_KB_CATEGORY_GROUPS_TL WHERE CATEGORY_GROUP_ID = CAT_GROUP_API_TEST_CG1;
229 Create_CG (P_ID => CAT_GROUP_API_TEST_CG1, P_NAME => 'Api_Test_CG1');
230
231 DELETE FROM CS_KB_CATEGORY_GROUPS_B WHERE CATEGORY_GROUP_ID = CAT_GROUP_API_TEST_CG2;
232 DELETE FROM CS_KB_CATEGORY_GROUPS_TL WHERE CATEGORY_GROUP_ID = CAT_GROUP_API_TEST_CG2;
233 Create_CG (P_ID => CAT_GROUP_API_TEST_CG2, P_NAME => 'Api_Test_CG2');
234
235 -------------------------------------------------------------
236 -- Create Seeded Solution Types
237 -------------------------------------------------------------
238 DELETE FROM CS_KB_SET_TYPES_B WHERE SET_TYPE_ID = SOLN_TYPE_FAQ_API_TEST;
239 DELETE FROM CS_KB_SET_TYPES_TL WHERE SET_TYPE_ID = SOLN_TYPE_FAQ_API_TEST;
240 Create_Soln_Type (P_ID => SOLN_TYPE_FAQ_API_TEST, P_NAME => 'FAQ_API_TEST');
241
242 -------------------------------------------------------------
243 -- Create Seeded Statement Types
244 -------------------------------------------------------------
245 DELETE FROM CS_KB_ELEMENT_TYPES_B WHERE ELEMENT_TYPE_ID = STMT_TYPE_FAQ_API_TEST;
246 DELETE FROM CS_KB_ELEMENT_TYPES_TL WHERE ELEMENT_TYPE_ID = STMT_TYPE_FAQ_API_TEST;
247 Create_Stmt_Type (P_ID => STMT_TYPE_FAQ_API_TEST, P_NAME => 'FAQ_API_TEST');
248
249 -------------------------------------------------------------
250 -- Create Seeded Solution to Statement Type Associations
251 -------------------------------------------------------------
252 DELETE FROM CS_KB_SET_ELE_TYPES WHERE SET_TYPE_ID = SOLN_TYPE_FAQ_API_TEST AND ELEMENT_TYPE_ID = STMT_TYPE_FAQ_API_TEST;
253 INSERT INTO CS_KB_SET_ELE_TYPES ( SET_TYPE_ID,ELEMENT_TYPE_ID,ELEMENT_TYPE_ORDER
254 ,OPTIONAL_FLAG,CREATION_DATE,CREATED_BY
255 ,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
256 VALUES (SOLN_TYPE_FAQ_API_TEST, STMT_TYPE_FAQ_API_TEST, 1, 'Y', sysdate,
257 -1351, sysdate, -1351, -1351);
258
259 -------------------------------------------------------------
260 -- Create Seeded Visibilities
261 -------------------------------------------------------------
262 SELECT MAX(Position) INTO l_max
263 FROM CS_KB_VISIBILITIES_B
264 WHERE Visibility_id > 1;
265
266 DELETE FROM CS_KB_VISIBILITIES_B WHERE Visibility_ID = VISIBILITY_RESTRICTED_API_TEST;
267 DELETE FROM CS_KB_VISIBILITIES_TL WHERE Visibility_ID = VISIBILITY_RESTRICTED_API_TEST;
268 Create_Visibility(P_ID => VISIBILITY_RESTRICTED_API_TEST, P_NAME => 'Restricted_API_TEST', P_POSN => l_max+1);
269 UPDATE CS_KB_CAT_GROUP_DENORM SET VISIBILITY_POSITION = l_max+1
270 WHERE VISIBILITY_ID = VISIBILITY_RESTRICTED_API_TEST;
271
272 DELETE FROM CS_KB_VISIBILITIES_B WHERE Visibility_ID = VISIBILITY_INTERNAL_API_TEST;
273 DELETE FROM CS_KB_VISIBILITIES_TL WHERE Visibility_ID = VISIBILITY_INTERNAL_API_TEST;
274 Create_Visibility(P_ID => VISIBILITY_INTERNAL_API_TEST, P_NAME => 'Internal_API_TEST', P_POSN => l_max+2);
275 UPDATE CS_KB_CAT_GROUP_DENORM SET VISIBILITY_POSITION = l_max+2
276 WHERE VISIBILITY_ID = VISIBILITY_INTERNAL_API_TEST;
277
278 DELETE FROM CS_KB_VISIBILITIES_B WHERE Visibility_ID = VISIBILITY_LIMITED_API_TEST;
279 DELETE FROM CS_KB_VISIBILITIES_TL WHERE Visibility_ID = VISIBILITY_LIMITED_API_TEST;
280 Create_Visibility(P_ID => VISIBILITY_LIMITED_API_TEST, P_NAME => 'Limited_API_TEST', P_POSN => l_max+3);
281 UPDATE CS_KB_CAT_GROUP_DENORM SET VISIBILITY_POSITION = l_max+3
282 WHERE VISIBILITY_ID = VISIBILITY_LIMITED_API_TEST;
283
284 DELETE FROM CS_KB_VISIBILITIES_B WHERE Visibility_ID = VISIBILITY_EXTERNAL_API_TEST;
285 DELETE FROM CS_KB_VISIBILITIES_TL WHERE Visibility_ID = VISIBILITY_EXTERNAL_API_TEST;
286 Create_Visibility(P_ID => VISIBILITY_EXTERNAL_API_TEST, P_NAME => 'External_API_TEST', P_POSN => l_max+4);
287 UPDATE CS_KB_CAT_GROUP_DENORM SET VISIBILITY_POSITION = l_max+4
288 WHERE VISIBILITY_ID = VISIBILITY_EXTERNAL_API_TEST;
289
290 -------------------------------------------------------------
291 -- Create Seeded Flow
292 -------------------------------------------------------------
293 DELETE FROM CS_KB_WF_FLOWS_B WHERE FLOW_ID = FLOW_API_TEST_FLOW;
294 DELETE FROM CS_KB_WF_FLOWS_TL WHERE FLOW_ID = FLOW_API_TEST_FLOW;
295 DELETE FROM CS_KB_WF_FLOW_DETAILS WHERE FLOW_ID = FLOW_API_TEST_FLOW;
296
297 insert into CS_KB_WF_FLOWS_B (
298 FLOW_ID,
299 CREATION_DATE,
300 CREATED_BY,
301 LAST_UPDATE_DATE,
302 LAST_UPDATED_BY,
303 LAST_UPDATE_LOGIN
304 ) values (
305 FLOW_API_TEST_FLOW,
306 sysdate,
307 -1351,
308 sysdate,
309 -1351,
310 -1351 );
311
312
313
314 insert into CS_KB_WF_FLOWS_TL (
315 FLOW_ID,
316 NAME,
317 CREATION_DATE,
318 CREATED_BY,
319 LAST_UPDATE_DATE,
320 LAST_UPDATED_BY,
321 LAST_UPDATE_LOGIN,
322 LANGUAGE,
323 SOURCE_LANG
324 ) select
325 FLOW_API_TEST_FLOW,
326 'Api Test Flow',
327 sysdate,
328 -1351,
329 sysdate,
330 -1351,
331 -1351,
332 L.LANGUAGE_CODE,
333 userenv('LANG')
334 from FND_LANGUAGES L
335 where L.INSTALLED_FLAG in ('I', 'B')
336 and not exists
337 (select NULL
338 from CS_KB_WF_FLOWS_TL T
339 where T.FLOW_ID = -1
340 and T.LANGUAGE = L.LANGUAGE_CODE);
341
342 INSERT INTO CS_KB_WF_FLOW_DETAILS (FLOW_DETAILS_ID, FLOW_ID, STEP, ORDER_NUM,
343 ACTION, GROUP_ID, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
344 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
345 VALUES (-1,FLOW_API_TEST_FLOW,'TECHNICAL_REVIEW',10, 'NOT',100000121, -1351,sysdate,-1351,sysdate,-1351);
346
347 INSERT INTO CS_KB_WF_FLOW_DETAILS (FLOW_DETAILS_ID, FLOW_ID, STEP, ORDER_NUM,
348 ACTION, GROUP_ID, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
349 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
350 VALUES (-2,FLOW_API_TEST_FLOW,'PUBLISHED',20, 'PUB',100000121, -1351,sysdate,-1351,sysdate,-1351);
351 -------------------------------------------------------------
352 -------------------------------------------------------------
353
354 if fnd_api.to_boolean( p_commit ) then
355 commit;
356 end if;
357 END Validate_Seeded_Setups;
358
359 --Create category
360 PROCEDURE Create_Category (
361 p_api_version in number,
362 p_init_msg_list in varchar2 := FND_API.G_FALSE,
363 p_commit in varchar2 := FND_API.G_FALSE,
364 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
365 x_return_status OUT NOCOPY varchar2,
366 x_msg_count OUT NOCOPY number,
367 x_msg_data OUT NOCOPY varchar2,
368 P_PARENT_CATEGORY_ID IN NUMBER,
372 IS
369 P_CATEGORY_ID IN NUMBER,
370 P_CATEGORY_NAME IN VARCHAR2,
371 P_VISIBILITY_ID IN NUMBER)
373
374 CURSOR Get_Posn IS
375 SELECT Position
376 FROM CS_KB_VISIBILITIES_B
377 WHERE VISIBILITY_ID = P_VISIBILITY_ID;
378 l_posn NUMBER;
379
380 BEGIN
381
382 DELETE FROM cs_kb_soln_categories_b WHERE CATEGORY_ID = P_CATEGORY_ID;
383 DELETE FROM cs_kb_soln_categories_tl WHERE CATEGORY_ID = P_CATEGORY_ID;
384
385 insert into CS_KB_SOLN_CATEGORIES_B
386 (
387 CATEGORY_ID,
388 PARENT_CATEGORY_ID,
389 CREATION_DATE,
390 CREATED_BY,
391 LAST_UPDATE_DATE,
392 LAST_UPDATED_BY,
393 LAST_UPDATE_LOGIN,
394 VISIBILITY_ID
395 )
396 values
397 ( P_CATEGORY_ID,
398 P_PARENT_CATEGORY_ID,
399 sysdate,
400 -1351,
401 sysdate,
402 -1351,
403 -1351,
404 P_VISIBILITY_ID
405 );
406
407 insert into CS_KB_SOLN_CATEGORIES_TL
408 (
409 CATEGORY_ID,
410 NAME,
411 DESCRIPTION,
412 CREATION_DATE,
413 CREATED_BY,
414 LAST_UPDATE_DATE,
415 LAST_UPDATED_BY,
416 LAST_UPDATE_LOGIN,
417 LANGUAGE,
418 SOURCE_LANG
419 )
420 select
421 P_CATEGORY_ID,
422 P_CATEGORY_NAME,
423 P_CATEGORY_NAME,
424 sysdate,
425 -1351,
426 sysdate,
427 -1351,
428 -1351,
429 L.LANGUAGE_CODE,
430 userenv('LANG')
431 from FND_LANGUAGES L
432 where L.INSTALLED_FLAG in ('I', 'B')
433 and not exists
434 (select NULL
435 from CS_KB_SOLN_CATEGORIES_TL T
436 where T.CATEGORY_ID = P_CATEGORY_ID
437 and T.LANGUAGE = L.LANGUAGE_CODE);
438
439 OPEN Get_Posn;
440 FETCH Get_Posn INTO l_posn;
441 CLOSE Get_Posn;
442
443 UPDATE CS_KB_CAT_GROUP_DENORM
444 SET VISIBILITY_ID = P_VISIBILITY_ID,
445 VISIBILITY_POSITION = l_posn
446 WHERE CHILD_CATEGORY_ID = P_CATEGORY_ID;
447
448 if fnd_api.to_boolean( p_commit ) then
449 commit;
450 end if;
451 END Create_Category;
452
453 --Delete category
454 procedure Delete_Category(
455 p_api_version in number,
456 p_init_msg_list in varchar2 := FND_API.G_FALSE,
457 p_commit in varchar2 := FND_API.G_FALSE,
458 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
459 x_return_status OUT NOCOPY varchar2,
460 x_msg_count OUT NOCOPY number,
461 x_msg_data OUT NOCOPY varchar2,
462 p_category_id in number)
463 is
464 n_child_solutions number;
465 n_subcatgories number;
466 l_delete_status number;
467 begin
468
469 select /*+ index(sl) */ count( * ) into n_child_solutions
470 from cs_kb_set_categories sl, cs_kb_sets_b b
471 where sl.category_id = p_category_id
472 and b.set_id = sl.set_id
473 and (b.status = 'PUB' or (b.status <> 'OBS' and b.latest_version_flag = 'Y'));
474
475 select count( * ) into n_subcatgories
476 from cs_kb_soln_categories_b
477 where parent_category_id = p_category_id;
478
479 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
480 -- check if the category is deletable
481 -- i.e. it does not contain sub-categories nor PUBlished child solutions
482 if( n_child_solutions <> 0 OR n_subcatgories <> 0 ) then
483 FND_MSG_PUB.initialize;
484 FND_MESSAGE.set_name('CS', 'CS_KB_C_CAT_DELETE_FAILED');
485 FND_MSG_PUB.ADD;
486 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
487 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
488 p_count => X_MSG_COUNT,
489 p_data => X_MSG_DATA);
490
491 ELSE
492 -- Delete all set category links (which should not be PUBlished versions)
493 delete /*+ index(sl) */ from cs_kb_set_categories sl
494 where sl.category_id = p_category_id;
495
496 -- Delete this leaf category
497 delete from cs_kb_soln_categories_tl
498 where category_id = p_category_id;
499
500 delete from cs_kb_soln_categories_b
501 where category_id = p_category_id;
502
503 -- cs_kb_security_pvt.REMOVE_CATEGORY_FROM_CAT_GROUP
504 -- Removes Category from Members table if the Category Exists
505 DELETE FROM CS_KB_CAT_GROUP_MEMBERS
506 WHERE Category_Id = P_CATEGORY_ID;
507 -- Removes Category from Denorm table if the Category Exists
508 DELETE FROM CS_KB_CAT_GROUP_DENORM
509 WHERE Child_Category_Id = P_CATEGORY_ID;
510
511 if fnd_api.to_boolean( p_commit ) then
512 commit;
513 end if;
514 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
515 end if;
516 end Delete_Category;
517
518 --Create solution
519 -- !!! Incomplete: missing the way to submit it to a certain flow.
520 PROCEDURE Create_Solution (
521 p_api_version in number,
522 p_init_msg_list in varchar2 := FND_API.G_FALSE,
523 p_commit in varchar2 := FND_API.G_FALSE,
524 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
525 x_return_status OUT NOCOPY varchar2,
526 x_msg_count OUT NOCOPY number,
527 x_msg_data OUT NOCOPY varchar2,
528 P_SOLN_REC IN Soln_rec_type,
529 P_STMT_TBL IN Stmt_tbl_type,
530 p_CAT_TBL IN Cat_tbl_type,
531 P_PUBLISH IN Boolean)
532 IS
533 a pls_integer;
534
535 i1 pls_integer;
536
537 l_status VARCHAR2(30);
541 i2 pls_integer;
538 l_locked_by NUMBER;
539 l_fdi NUMBER;
540 b NUMBER;
542
543 BEGIN
544 dbms_output.put_line('Before Delete');
545 DELETE FROM CS_KB_SETS_B WHERE SET_ID = P_SOLN_REC.Set_id;
546 DELETE FROM CS_KB_SETS_TL WHERE SET_ID = P_SOLN_REC.Set_id;
547 DELETE FROM CS_KB_SET_CATEGORIES WHERE SET_ID = P_SOLN_REC.Set_id;
548 DELETE FROM CS_KB_SET_ELES WHERE SET_ID = P_SOLN_REC.Set_id;
549
550 a := P_STMT_TBL.FIRST;
551
552 WHILE a IS NOT NULL LOOP
553
554 DELETE FROM CS_KB_ELEMENTS_B WHERE ELEMENT_ID = P_STMT_TBL(a).element_id;
555
556 DELETE FROM CS_KB_ELEMENTS_TL WHERE ELEMENT_ID = P_STMT_TBL(a).element_id;
557
558 a := P_STMT_TBL.NEXT(a);
559
560 END LOOP;
561 dbms_output.put_line('After Delete');
562 IF P_PUBLISH = true THEN
563 l_status := 'PUB';
564 l_locked_by := null;
565 l_fdi := null;
566 ELSE
567 l_status := 'NOT';
568 l_locked_by := -1;
569 l_fdi := -1;
570 END IF;
571
572
573 INSERT INTO CS_KB_SETS_B (
574 set_id,
575 set_number,
576 set_type_id,
577 status,
578 creation_date,
579 created_by,
580 last_update_date,
581 last_updated_by,
582 last_update_login,
583 locked_by,
584 priority_code,
585 original_author,
586 original_author_date,
587 visibility_id,
588 latest_version_flag,
589 USAGE_SCORE,
590 Flow_Details_id )
591 VALUES (
592 P_SOLN_REC.set_id,
593 P_SOLN_REC.set_number,
594 P_SOLN_REC.set_type_id,
595 l_status,
596 sysdate,
597 -1351,
598 sysdate,
599 -1351,
600 -1351,
601 l_locked_by,
602 4,
603 -1351,
604 sysdate,
605 P_SOLN_REC.visibility_id,
606 'Y',
607 0,
608 l_fdi );
609
610 INSERT INTO CS_KB_SETS_TL (
611 set_id,
612 name,
613 creation_date,
614 created_by,
615 last_update_date,
616 last_updated_by,
617 last_update_login,
618 language,
619 source_lang
620 ) SELECT
621 P_SOLN_REC.set_id,
622 P_SOLN_REC.name,
623 sysdate,
624 -1351,
625 sysdate,
626 -1351,
627 -1351,
628 l.language_code,
629 USERENV('LANG')
630 FROM fnd_languages l
631 WHERE l.installed_flag IN ('I', 'B')
632 AND NOT EXISTS
633 (SELECT NULL
634 FROM CS_KB_SETS_TL t
635 WHERE t.set_id = P_SOLN_REC.set_id
636 AND t.language = l.language_code);
637
638
639 dbms_output.put_line('After Insert Solution');
640 IF P_PUBLISH = true THEN
641 l_status := 'PUBLISHED';
642 ELSE
643 l_status := 'DRAFT';
644 END IF;
645
646 b := 1;
647 i1 := P_STMT_TBL.FIRST;
648
649 while i1 is not null loop
650
651 INSERT INTO CS_KB_ELEMENTS_B (
652 element_id,
653 element_number,
654 element_type_id,
655 status,
656 access_level,
657 creation_date,
658 created_by,
659 last_update_date,
660 last_updated_by,
661 last_update_login,
662 content_type
663 ) VALUES (
664 P_STMT_TBL(i1).element_id,
665 P_STMT_TBL(i1).element_number,
666 P_STMT_TBL(i1).element_type_id,
667 l_status,
668 P_STMT_TBL(i1).access_level,
669 sysdate,
670 -1351,
671 sysdate,
672 -1351,
673 -1351,
674 P_STMT_TBL(i1).content_type );
675
676 INSERT INTO CS_KB_ELEMENTS_TL (
677 element_id,
678 name,
679 creation_date,
680 created_by,
681 last_update_date,
682 last_updated_by,
683 last_update_login,
684 language,
685 source_lang
686 ) SELECT
687 P_STMT_TBL(i1).element_id,
688 P_STMT_TBL(i1).name,
689 sysdate,
690 -1351,
691 sysdate,
692 -1351,
693 -1351,
694 l.language_code,
695 USERENV('LANG')
696 FROM FND_LANGUAGES l
697 WHERE l.installed_flag IN ('I', 'B')
698 AND NOT EXISTS
699 (SELECT NULL
700 FROM CS_KB_ELEMENTS_TL t
701 WHERE t.element_id = P_STMT_TBL(i1).element_id
702 AND t.language = l.language_code);
703
704 INSERT INTO CS_KB_SET_ELES (SET_ID,
705 ELEMENT_ID,
706 ELEMENT_ORDER,
707 CREATION_DATE,
708 CREATED_BY,
709 LAST_UPDATE_DATE,
710 LAST_UPDATED_BY,
711 LAST_UPDATE_LOGIN)
712 VALUES( P_SOLN_REC.set_id,
713 P_STMT_TBL(i1).element_id,
714 b,
715 sysdate,
716 -1351,
717 sysdate,
718 -1351,
719 -1351);
720 b := b+1;
721 i1 := P_STMT_TBL.NEXT(i1);
722 end loop;
723
724 i2 := p_CAT_TBL.FIRST;
725 while i2 is not null loop
726 dbms_output.put_line('Add Cat'||p_CAT_TBL(i2) );
727 INSERT INTO CS_KB_SET_CATEGORIES (SET_ID,
728 CATEGORY_ID,
729 CREATION_DATE,
730 CREATED_BY,
731 LAST_UPDATE_DATE,
732 LAST_UPDATED_BY,
733 LAST_UPDATE_LOGIN )
734 VALUES ( P_SOLN_REC.set_id,
735 p_CAT_TBL(i2),
736 sysdate,
737 -1351,
738 sysdate,
739 -1351,
740 -1351);
741 i2 := P_CAT_TBL.NEXT(i2);
742 end loop;
743 if fnd_api.to_boolean( p_commit ) then
744 commit;
745 end if;
746 END Create_Solution;
747
748
749 --Delete solution
750 PROCEDURE Delete_Solution (
751 p_api_version in number,
752 p_init_msg_list in varchar2 := FND_API.G_FALSE,
753 p_commit in varchar2 := FND_API.G_FALSE,
754 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
755 x_return_status OUT NOCOPY varchar2,
756 x_msg_count OUT NOCOPY number,
757 x_msg_data OUT NOCOPY varchar2,
758 P_SET_ID IN NUMBER)
759 IS
760 CURSOR Get_Elements IS
761 SELECT ELEMENT_ID
762 FROM CS_KB_SET_ELES
763 WHERE SET_ID = P_SET_ID;
764 l_element_id NUMBER;
765 BEGIN
766 --Remove Solution
767 DELETE FROM CS_KB_SETS_B WHERE SET_ID = P_SET_ID;
768 DELETE FROM CS_KB_SETS_TL WHERE SET_ID = P_SET_ID;
769 --Remove Solution Category
770 DELETE FROM CS_KB_SET_CATEGORIES WHERE SET_ID = P_SET_ID;
771 --Remove Solution Statements
772 OPEN Get_Elements;
773 LOOP
774 FETCH Get_Elements INTO l_element_id;
775 EXIT WHEN Get_Elements%NOTFOUND;
776 DELETE FROM CS_KB_ELEMENTS_B WHERE ELEMENT_ID = l_element_id;
777 DELETE FROM CS_KB_ELEMENTS_TL WHERE ELEMENT_ID = l_element_id;
778 DELETE FROM CS_KB_SET_ELES WHERE SET_ID = P_SET_ID and ELEMENT_ID = l_element_id;
779 END LOOP;
780 CLOSE Get_Elements;
781 if fnd_api.to_boolean( p_commit ) then
782 commit;
783 end if;
784 END Delete_Solution;
785
786 --Delete solution
787 PROCEDURE Obsolete_Solution (
788 p_api_version in number,
789 p_init_msg_list in varchar2 := FND_API.G_FALSE,
790 p_commit in varchar2 := FND_API.G_FALSE,
791 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
792 x_return_status OUT NOCOPY varchar2,
793 x_msg_count OUT NOCOPY number,
794 x_msg_data OUT NOCOPY varchar2,
795 P_SET_ID IN NUMBER)
796 IS
797 BEGIN
798 --Remove Solution
799 UPDATE CS_KB_SETS_B SET STATUS = 'OBS' WHERE SET_ID = P_SET_ID;
800 END Obsolete_Solution;
801
802 --Get next solution ID from sequence
803 FUNCTION Get_Next_Set_ID
804 RETURN NUMBER
805 IS
806 l_next_val number;
807 BEGIN
808 SELECT CS_KB_SETS_S.NEXTVAL INTO l_next_val FROM DUAL;
809 return l_next_val;
810 END;
811
812 --Get next solution Number from sequence
813 FUNCTION Get_Next_Set_Number
814 RETURN NUMBER
815 IS
816 l_next_val number;
817 BEGIN
818 SELECT CS_KB_SET_NUMBER_S.NEXTVAL INTO l_next_val FROM DUAL;
819 return l_next_val;
820 END;
821
822 --Get next statement ID from sequence
823 FUNCTION Get_Next_Element_ID
824 RETURN NUMBER
825 IS
826 l_next_val number;
827 BEGIN
828 SELECT CS_KB_ELEMENTS_S.NEXTVAL INTO l_next_val FROM DUAL;
829 return l_next_val;
830 END;
831
832 --Get next statement number from sequence
833 FUNCTION Get_Next_Element_Number
834 RETURN NUMBER
835 IS
836 l_next_val number;
837 BEGIN
838 SELECT CS_KB_ELEMENT_NUMBER_S.NEXTVAL INTO l_next_val FROM DUAL;
839 return l_next_val;
840 END;
841
842 --Get next category ID
843 FUNCTION Get_Next_Category_ID
844 RETURN NUMBER
845 IS
846 l_next_val number;
847 BEGIN
848 SELECT CS_KB_SOLN_CATEGORIES_S.NEXTVAL INTO l_next_val FROM DUAL;
849 return l_next_val;
850 END;
851
852 FUNCTION Calculate_Set_Index_Content (P_SET_ID IN NUMBER)
853 RETURN VARCHAR2
854 IS
855 l_lob CLOB;
856 l_len INTEGER;
857 l_offset INTEGER;
858 l_text_content varchar2(4000) := '';
859 begin
860 dbms_lob.createtemporary(l_lob, TRUE, dbms_lob.session);
861 cs_kb_ctx_pkg.Synthesize_Solution_Content(P_SET_ID,userenv('LANG'),l_lob);
862 l_len := dbms_lob.GETLENGTH(l_lob);
863 l_offset := 1;
864
865 while l_len > 100 loop
866 l_text_content := l_text_content || dbms_lob.substr(l_lob,100,l_offset);
867 l_offset := l_offset + 100;
868 l_len := l_len - 100;
869 end loop;
870 l_text_content := l_text_content || dbms_lob.substr(l_lob,l_len,l_offset);
871 dbms_output.put_line(substr(l_text_content,0,200));
872 return l_text_content;
873 end;
874
875
876 END CSK_SETUP_UTILITY_PKG;