[Home] [Help]
PACKAGE BODY: APPS.CS_KNOWLEDGE_AUDIT_PVT
Source
1 PACKAGE BODY CS_KNOWLEDGE_AUDIT_PVT AS
2 /* $Header: cskbapb.pls 120.4.12010000.3 2009/10/21 12:47:50 amganapa ship $ */
3
4 -- An element is updatable if
5 -- 1) no set is linking to it, OR
6 -- 2) only set with p_set_number is linking to it.
7 -- return 'Y' if updatable
8 -- return 'N' if not-updatable
9 FUNCTION Is_Element_Updatable
10 (
11 p_element_number IN VARCHAR2,
12 p_set_number IN VARCHAR2
13 )
14 RETURN VARCHAR2
15 IS
16 l_count NUMBER;
17
18 CURSOR cur_ele IS
19 SELECT element_id
20 FROM CS_KB_ELEMENTS_B
21 WHERE element_number = p_element_number;
22
23 CURSOR cur_set(c_element_id IN NUMBER) IS
24 SELECT DISTINCT s.set_number
25 FROM CS_KB_SET_ELES se,
26 CS_KB_SETS_B s
27 WHERE se.element_id = c_element_id
28 AND se.set_id = s.set_id;
29
30 BEGIN
31
32 -- if p_set_number is null, any existing
33 IF p_set_number IS NULL THEN
34
35 SELECT COUNT(se.set_id)
36 INTO l_count
37 FROM CS_KB_SET_ELES se,
38 CS_KB_ELEMENTS_B e
39 WHERE e.element_number = p_element_number
40 AND se.element_id = e.element_id;
41
42 IF l_count < 1 THEN
43 RETURN 'Y';
44 ELSE
45 RETURN 'N';
46 END IF;
47
48 END IF;
49
50 FOR rec IN cur_ele LOOP
51
52 FOR rec_inner IN cur_set(rec.element_id) LOOP
53 IF ( rec_inner.set_number <> p_set_number ) THEN
54 RETURN 'N';
55 END IF;
56 END LOOP;
57
58 END LOOP;
59
60 RETURN 'Y';
61
62 END Is_Element_Updatable;
63
64
65 -- check IF at least one statement FOR each mandatory element-type
66 -- (which IS defined IN cs_kb_set_ele_types) exists
67 -- FOR this particular version of solution
68 -- RETURN 'N' IF complete, 'Y' IF there IS any missing ele-types
69 FUNCTION Get_Missing_Ele_Type
70 ( p_set_id IN NUMBER--,
71 )
72 RETURN VARCHAR2 IS
73
74 -- Create the table variables to hold returnable info.
75 t_ele_type_id JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
76 t_ele_type_name JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
77
78 -- count of matching element type
79 l_count NUMBER;
80 l_set_type_id NUMBER;
81 l_element_type_name CS_KB_ELEMENT_TYPES_TL.name%TYPE;
82
83 -- Counter variable.
84 counter NUMBER := 0;
85
86 -- list of required element types
87 CURSOR cur_ele_type(p_set_type_id IN NUMBER) IS
88 SELECT B.element_type_id
89 FROM CS_KB_SET_ELE_TYPES A, CS_KB_ELEMENT_TYPES_VL B
90 WHERE A.set_type_id = p_set_type_id
91 AND A.optional_flag = 'N'
92 AND A.element_type_id = B.element_type_id
93 AND trunc(sysdate) between trunc(nvl(B.start_date_active, sysdate)) and trunc(nvl(B.end_date_active, sysdate))
94 ORDER BY A.element_type_order ASC;
95
96 BEGIN
97
98 SELECT set_type_id
99 INTO l_set_type_id
100 FROM CS_KB_SETS_B
101 WHERE set_id = p_set_id;
102
103 FOR rec IN cur_ele_type(l_set_type_id) LOOP
104
105 SELECT count(*)
106 INTO l_count
107 FROM CS_KB_ELEMENTS_B e,
108 CS_KB_SET_ELES se
109 WHERE se.set_id = p_set_id
110 AND se.element_id = e.element_id
111 AND e.element_type_id = rec.element_type_id;
112
113 IF l_count < 1 THEN
114
115 SELECT name
116 INTO l_element_type_name
117 FROM CS_KB_ELEMENT_TYPES_TL
118 WHERE element_type_id = rec.element_type_id
119 AND language = USERENV('LANG');
120
121 -- Extending tables one.
122 t_ele_type_id.EXTEND;
123 t_ele_type_name.EXTEND;
124 counter := counter + 1;
125
126 t_ele_type_id(counter) := rec.element_type_id;
127 t_ele_type_name(counter) := l_element_type_name;
128
129 END IF;
130 END LOOP;
131
132 IF counter = 0 THEN
133 RETURN 'N';
134 ELSE
135 RETURN 'Y';
136 END IF;
137
138 END Get_Missing_Ele_Type;
139
140
141 FUNCTION Decrypt
142 (
143 KEY IN VARCHAR2,
144 VALUE IN VARCHAR2
145 )
146 RETURN VARCHAR2 AS LANGUAGE java name
147 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
148
149
150 --
151 -- Get sysdate, fnd user AND login
152 --
153 PROCEDURE Get_Who(
154 x_sysdate OUT NOCOPY DATE,
155 x_user_id OUT NOCOPY NUMBER,
156 x_login_id OUT NOCOPY NUMBER
157 ) IS
158 BEGIN
159 x_sysdate := SYSDATE;
160 x_user_id := FND_GLOBAL.user_id;
161 x_login_id := FND_GLOBAL.login_id;
162 END Get_Who;
163
164 --
165 -- Check set_type - element_type IS valid
166 -- Valid params:
167 -- (set id, NULL, ele id, NULL)
168 -- (set id, NULL, NULL, ele type)
169 -- (NULL, set type, ele id, NULL)
170 -- (NULL, set type, NULL, ele type)
171 --
172 FUNCTION Is_Set_Ele_Type_Valid(
173 p_set_number IN VARCHAR2 := NULL,
174 p_set_type_id IN NUMBER :=NULL,
175 p_element_number IN VARCHAR2 :=NULL,
176 p_ele_type_id IN NUMBER :=NULL
177 ) RETURN VARCHAR2 IS
178 l_count PLS_INTEGER;
179 BEGIN
180
181 IF p_set_number IS NOT NULL THEN
182 IF p_element_number IS NOT NULL THEN
183 SELECT count(*) INTO l_count
184 FROM cs_kb_set_ele_types se,
185 cs_kb_sets_b s,
186 cs_kb_elements_b e
187 WHERE se.set_type_id = s.set_type_id
188 AND se.element_type_id = e.element_type_id
189 AND s.set_number = p_set_number
190 AND e.element_number = p_element_number;
191
192 ELSIF(p_ele_type_id > 0) THEN
193 SELECT count(*) INTO l_count
194 FROM CS_KB_SET_ELE_TYPES se,
195 CS_KB_SETS_B s
196 WHERE se.set_type_id = s.set_type_id
197 AND s.set_number = p_set_number
198 AND se.element_type_id = p_ele_type_id;
199 END IF;
200
201 ELSIF(p_set_type_id >0) THEN
202 IF p_element_number IS NOT NULL THEN
203 SELECT count(*) INTO l_count
204 FROM CS_KB_SET_ELE_TYPES se,
205 CS_KB_ELEMENTS_B e
206 WHERE se.set_type_id = p_set_type_id
207 AND e.element_number = p_element_number
208 AND se.element_type_id = e.element_type_id;
209
210 ELSIF(p_ele_type_id >0) THEN
211 SELECT count(*) INTO l_count
212 FROM CS_KB_SET_ELE_TYPES se
213 WHERE se.set_type_id = p_set_type_id
214 AND se.element_type_id = p_ele_type_id;
215 END IF;
216 END IF;
217
218 IF(l_count >0) THEN RETURN G_TRUE;
219 ELSE RETURN G_FALSE;
220 END IF;
221 END Is_Set_Ele_Type_Valid;
222
223
224 FUNCTION Del_Element_From_Set(
225 p_element_id IN NUMBER,
226 p_set_id IN NUMBER
227 ) RETURN NUMBER IS--RETURN OKAY_STATUS / ERROR_STATUS
228 l_date DATE;
229 l_user NUMBER;
230 l_login NUMBER;
231
232 CURSOR cur_eles( c_sid IN NUMBER) IS
233 SELECT element_id
234 FROM CS_KB_SET_ELES
235 WHERE set_id = c_sid;
236
237 CURSOR cur_set IS
238 SELECT set_type_id
239 FROM CS_KB_SETS_B
240 WHERE set_id = p_set_id;
241
242 l_set_rec cur_set%ROWTYPE;
243
244 BEGIN
245 -- Check params
246 IF( not p_set_id > 0 ) or (not p_element_id > 0) THEN
247 FND_MESSAGE.set_name('CS', 'CS_KB_C_MISS_PARAM');
248 GOTO ERROR_FOUND;
249 END IF;
250
251 --delete a row IN set_eles_audit
252 DELETE FROM CS_KB_SET_ELES
253 WHERE element_id = p_element_id
254 AND set_id = p_set_id;
255
256 -- change UPDATE DATE of set_audit
257 -- -- AND UPDATE change_history of set
258
259 Get_Who(l_date, l_user, l_login);
260 UPDATE CS_KB_SETS_B SET
261 last_update_date = l_date,
262 last_updated_by = l_user,
263 last_update_login = l_login
264 WHERE set_id = p_set_id;
265
266 -- touch related sets to UPDATE interMedia index
267 UPDATE CS_KB_SETS_TL SET
268 last_update_date = l_date,
269 last_updated_by = l_user,
270 last_update_login = l_login
271 WHERE set_id = p_set_id;
272
273
274 RETURN OKAY_STATUS;
275 <<ERROR_FOUND>>
276 RETURN ERROR_STATUS;
277
278 END Del_Element_From_Set;
279
280
281 FUNCTION Add_Element_To_Set(
282 p_element_number IN VARCHAR2,
283 p_set_id IN NUMBER,
284 p_assoc_degree IN NUMBER := CS_KNOWLEDGE_PUB.G_POSITIVE_ASSOC
285 ) RETURN NUMBER IS --RETURN OKAY_STATUS / ERROR_STATUS
286 l_dummy NUMBER;
287 l_element_id NUMBER;--new
288 l_count PLS_INTEGER;
289 l_date DATE;
290 l_created_by NUMBER;
291 l_login NUMBER;
292 l_order NUMBER(15);
293 l_set_type_id NUMBER;
294 l_ele_type_id NUMBER;
295
296 l_set_number VARCHAR2(30);
297
298 BEGIN
299
300 -- Check params
301 IF( NOT p_set_id > 0 ) OR (p_element_number IS NULL) THEN
302 FND_MESSAGE.set_name('CS', 'CS_KB_C_MISS_PARAM');
303 GOTO ERROR_FOUND;
304 END IF;
305
306 -- check IF element exists
307 SELECT COUNT(*) INTO l_count
308 FROM CS_KB_ELEMENTS_B
309 WHERE element_number = p_element_number;
310 IF(l_count=0) THEN
311 FND_MESSAGE.set_name('CS', 'CS_KB_C_INVALID_ELE_ID');
312 GOTO ERROR_FOUND;
313 END IF;
314
315 l_element_id := CS_KB_ELEMENTS_AUDIT_PKG.Get_Latest_Version_Id(p_element_number);
316
317 -- check IF element already exists
318 SELECT count(se.element_id) INTO l_count
319 FROM CS_KB_SET_ELES se, CS_KB_ELEMENTS_B eb
320 WHERE se.set_id = p_set_id
321 AND se.element_id = eb.element_id
322 AND eb.element_number = p_element_number;
323 IF(l_count>0) THEN
324 FND_MESSAGE.set_name('CS', 'CS_KB_C_ELE_EXIST_ERR');
325 GOTO ERROR_FOUND;
326 END IF;
327
328 --check set ele type match
329
330 SELECT set_type_id INTO l_set_type_id
331 FROM CS_KB_SETS_B
332 WHERE set_id = p_set_id;
333
334 SELECT element_type_id INTO l_ele_type_id
335 FROM CS_KB_ELEMENTS_B
336 WHERE element_id = l_element_id;
337
338 IF( Is_Set_Ele_Type_Valid(
339 p_set_type_id => l_set_type_id,
340 p_ele_type_id => l_ele_type_id)
341 = G_FALSE) THEN
342 FND_MESSAGE.set_name('CS', 'CS_KB_C_INCOMPATIBLE_TYPES');
343 GOTO ERROR_FOUND;
344 END IF;
345
346 -- prepare data to insert
347 Get_Who(l_date, l_created_by, l_login);
348
349 SELECT MAX(element_order) INTO l_order
350 FROM CS_KB_SET_ELES
351 WHERE set_id = p_set_id;
352
353 -- order IS important, need to consider l_order may be NULL
354 IF( l_order > 0) THEN
355 l_order := l_order + 1;
356 ELSE
357 l_order :=1;
358 END IF;
359
360 -- insert INTO set_ele
361 INSERT INTO CS_KB_SET_ELES (
362 set_id, element_id, element_order, assoc_degree,
363 creation_date, created_by,
364 last_update_date, last_updated_by, last_update_login)
365 VALUES(
366 p_set_id, l_element_id, l_order, p_assoc_degree,
367 l_date, l_created_by, l_date, l_created_by, l_login);
368
369 -- change UPDATE DATE of set
370 -- AND UPDATE history
371 UPDATE CS_KB_SETS_B SET
372 last_update_date = l_date,
373 last_updated_by = l_created_by,
374 last_update_login = l_login
375 WHERE set_id = p_set_id;
376
377 SELECT set_number
378 INTO l_set_number
379 FROM CS_KB_SETS_B
380 WHERE set_id = p_set_id;
381
382 -- touch related sets to UPDATE interMedia index
383 UPDATE CS_KB_SETS_TL SET
384 last_update_date = l_date,
385 last_updated_by = l_created_by,
386 last_update_login = l_login
387 WHERE set_id = p_set_id;
388
389
390 RETURN OKAY_STATUS;
391
392 <<ERROR_FOUND>>
393 RETURN ERROR_STATUS;
394 END Add_Element_To_Set;
395
396
397 FUNCTION Create_Element_And_Link_To_Set(
398 p_element_type_id IN NUMBER,
399 p_desc IN VARCHAR2,
400 p_name IN VARCHAR2,
401 p_status IN VARCHAR2,
402 p_access_level IN NUMBER,
403 p_attribute_category IN VARCHAR2,
404 p_attribute1 IN VARCHAR2,
405 p_attribute2 IN VARCHAR2,
406 p_attribute3 IN VARCHAR2,
407 p_attribute4 IN VARCHAR2,
408 p_attribute5 IN VARCHAR2,
409 p_attribute6 IN VARCHAR2,
410 p_attribute7 IN VARCHAR2,
411 p_attribute8 IN VARCHAR2,
412 p_attribute9 IN VARCHAR2,
413 p_attribute10 IN VARCHAR2,
414 p_attribute11 IN VARCHAR2,
415 p_attribute12 IN VARCHAR2,
416 p_attribute13 IN VARCHAR2,
417 p_attribute14 IN VARCHAR2,
418 p_attribute15 IN VARCHAR2,
419 p_set_id IN NUMBER,
420 p_assoc_degree IN NUMBER := CS_KNOWLEDGE_PUB.G_POSITIVE_ASSOC,
421 p_locked_by IN NUMBER,
422 p_start_active_date IN DATE,
423 p_end_active_date IN DATE,
424 p_content_type IN VARCHAR2
425 ) RETURN NUMBER IS--RETURN element_audit_id IF success, or ERROR_STATUS IF fail
426 l_element_id NUMBER;
427 l_element_number VARCHAR2(30);
428 l_ret NUMBER;
429 BEGIN
430 l_element_id := CS_KB_ELEMENTS_AUDIT_PKG.Create_Element(
431 p_element_type_id => p_element_type_id,
432 p_desc => p_desc,
433 p_name => p_name,
434 p_status => p_status,
435 p_access_level => p_access_level,
436 p_attribute_category => p_attribute_category,
437 p_attribute1 => p_attribute1,
438 p_attribute2 => p_attribute2,
439 p_attribute3 => p_attribute3,
440 p_attribute4 => p_attribute4,
441 p_attribute5 => p_attribute5,
442 p_attribute6 => p_attribute6,
443 p_attribute7 => p_attribute7,
444 p_attribute8 => p_attribute8,
445 p_attribute9 => p_attribute9,
446 p_attribute10 => p_attribute10,
447 p_attribute11 => p_attribute11,
448 p_attribute12 => p_attribute12,
449 p_attribute13 => p_attribute13,
450 p_attribute14 => p_attribute14,
451 p_attribute15 => p_attribute15,
452 p_start_active_date => p_start_active_date,
453 p_end_active_date => p_end_active_date,
454 p_content_type => p_content_type
455 );
456
457 IF l_element_id < 0 THEN
458 RETURN l_element_id;
459 END IF;
460
461 SELECT element_number INTO l_element_number
462 FROM CS_KB_ELEMENTS_B
463 WHERE element_id = l_element_id;
464
465 l_ret := Add_Element_To_Set(
466 p_element_number => l_element_number,
467 p_set_id => p_set_id,
468 p_assoc_degree => p_assoc_degree);
469
470 IF l_ret = ERROR_STATUS THEN
471 RETURN ERROR_STATUS;
472 END IF;
473
474 RETURN l_element_id;
475 END Create_Element_And_Link_To_Set;
476
477
478 FUNCTION Sort_Element_Order(p_set_number IN VARCHAR2)
479 RETURN NUMBER IS--RETURN OKAY_STATUS IF success, or ERROR_STATUS IF fail
480
481 CURSOR cur_ele_types IS
482 SELECT t.element_type_id
483 FROM CS_KB_SET_ELE_TYPES t, CS_KB_SETS_B s
484 WHERE t.set_type_id = s.set_type_id
485 AND s.set_id = CS_KB_SOLUTION_PVT.Get_Latest_Version_Id(p_set_number)
486 ORDER BY t.element_type_order;
487
488 CURSOR cur_eles(ele_type_id IN NUMBER) IS
489 SELECT se.set_id, se.element_id
490 FROM CS_KB_SET_ELES se, CS_KB_ELEMENTS_B el
491 WHERE se.set_id = CS_KB_SOLUTION_PVT.Get_Latest_Version_Id(p_set_number)
492 AND se.element_id = el.element_id
493 AND el.element_type_id = ele_type_id
494 ORDER BY se.element_order;
495
496 l_counter PLS_INTEGER := 0;
497
498 BEGIN
499
500 FOR rec_o IN cur_ele_types LOOP
501
502 FOR rec_i IN cur_eles(rec_o.element_type_id) LOOP
503
504 l_counter := l_counter + 1;
505
506 UPDATE CS_KB_SET_ELES
507 SET element_order = l_counter
508 WHERE element_id = rec_i.element_id
509 AND set_id = rec_i.set_id;
510
511 IF (SQL%NOTFOUND) THEN
512 RETURN ERROR_STATUS; -- Show error message: no permission to link this element
513 END IF;
514 END LOOP;
515
516 END LOOP;
517
518 RETURN OKAY_STATUS;
519
520 EXCEPTION
521 WHEN OTHERS THEN
522 RETURN ERROR_STATUS;
523 END Sort_Element_Order;
524
525
526 PROCEDURE Auto_Obsolete_Draft_Stmts(p_set_number IN VARCHAR2,
527 p_max_set_id IN NUMBER) IS
528
529 l_count NUMBER(15);
530 l_prior_set_id NUMBER(15);
531 l_exists BOOLEAN;
532
533 Type element_id_tab_type is TABLE OF CS_KB_ELEMENTS_B.ELEMENT_ID%TYPE INDEX BY BINARY_INTEGER;
534 Type element_status_tab_type is TABLE OF CS_KB_ELEMENTS_B.STATUS%TYPE INDEX BY BINARY_INTEGER;
535
536 l_prev_ver_elem_ids element_id_tab_type;
537
538 l_max_ver_elem_ids element_id_tab_type;
539
540 l_orphan_elem_ids element_id_tab_type;
541
542 CURSOR get_elem_info(c_set_id IN NUMBER)
543 IS
544
545 SELECT B.element_id from cs_kb_set_eles A, cs_kb_elements_b B, cs_kb_elements_tl C
546 WHERE A.set_id = c_set_id
547 AND B.element_id = A.element_id
548 AND B.element_id = C.element_id
549 AND B.status = 'DRAFT'
550 AND C.language = userenv('LANG');
551
552
553 CURSOR get_prev_set_id IS
554 select set_id
555 from cs_kb_sets_b
556 where set_id <> p_max_set_id
557 and set_number = p_set_number
558 order by creation_date desc;
559
560 BEGIN
561
562 Open get_elem_info(p_max_set_id);
563 l_count := 0;
564 Loop
565
566 Fetch get_elem_info INTO l_max_ver_elem_ids(l_count);
567 EXIT WHEN get_elem_info%NOTFOUND;
568 l_count := l_count + 1;
569
570 End Loop;
571
572 Close get_elem_info;
573
574 -- Get the prior set id for this soultion
575
576 --BugFix 3993200 - sequence id fix
577 OPEN get_prev_set_id;
578 FETCH get_prev_set_id INTO l_prior_set_id;
579 CLOSE get_prev_set_id;
580
581 IF (l_prior_set_id is NOT NULL)
582 THEN
583 Open get_elem_info(l_prior_set_id);
584 l_count := 0;
585 Loop
586
587 Fetch get_elem_info INTO l_prev_ver_elem_ids(l_count);
588 EXIT WHEN get_elem_info%NOTFOUND;
589 l_count := l_count + 1;
590
591 End Loop;
592
593 Close get_elem_info;
594
595 -- Compute the difference at this point;
596 -- First Get rid of the Unwanted Statments
597 for j in 0..l_prev_ver_elem_ids.count-1
598 loop
599 l_exists := FALSE;
600 for k in 0..l_max_ver_elem_ids.count-1
601 loop
602 IF (l_max_ver_elem_ids(k) = l_prev_ver_elem_ids(j))
603 THEN
604 l_exists := TRUE;
605 EXIT;
606 END IF;
607
608 end loop;
609 IF (l_exists = FALSE)
610 THEN
611 UPDATE CS_KB_ELEMENTS_B
612 SET status = 'OBS'
613 WHERE element_id = l_prev_ver_elem_ids(j);
614
615 END IF;
616
617 end loop;
618
619 END IF;
620
621 END Auto_Obsolete_Draft_Stmts;
622
623
624
625 PROCEDURE Auto_Obsolete_For_Solution_Pub(p_set_number IN VARCHAR2,
626 p_max_set_id IN NUMBER) IS
627
628 Type element_id_tab_type is TABLE OF CS_KB_ELEMENTS_B.ELEMENT_ID%TYPE INDEX BY BINARY_INTEGER;
629 Type element_status_tab_type is TABLE OF CS_KB_ELEMENTS_B.STATUS%TYPE INDEX BY BINARY_INTEGER;
630
631 l_max_set_id NUMBER(15);
632 l_temp_set_id NUMBER(15);
633 l_exists BOOLEAN;
634
635 l_prev_ver_elem_ids element_id_tab_type;
636 l_prev_ver_elem_stats element_status_tab_type;
637
638 l_max_ver_elem_ids element_id_tab_type;
639 l_max_ver_elem_stats element_status_tab_type;
640
641 l_orphan_elem_ids element_id_tab_type;
642 l_orphan_elem_stats element_status_tab_type;
643
644
645 l_count NUMBER(15);
646
647
648
649 CURSOR get_elem_info(c_set_id IN NUMBER)
650 IS
651
652 SELECT element_id from cs_kb_set_eles
653
654 WHERE set_id = c_set_id;
655
656
657
658 CURSOR get_elem_details(c_set_number IN VARCHAR2, c_set_id IN NUMBER)
659 IS
660
661 SELECT DISTINCT element_id from cs_kb_set_eles
662
663 WHERE set_id in
664 (select set_id from cs_kb_sets_b where set_number = c_set_number
665 and set_id < c_set_id);
666
667 BEGIN
668
669 Open get_elem_info(p_max_set_id);
670 l_count := 0;
671 Loop
672
673 Fetch get_elem_info INTO l_max_ver_elem_ids(l_count);
674 EXIT WHEN get_elem_info%NOTFOUND;
675 l_count := l_count + 1;
676
677 End Loop;
678
679 Close get_elem_info;
680
681
682 Open get_elem_details(p_set_number, p_max_set_id);
683 l_count := 0;
684 Loop
685
686 Fetch get_elem_details INTO l_prev_ver_elem_ids(l_count);
687 EXIT WHEN get_elem_details%NOTFOUND;
688 l_count := l_count + 1;
689
690 End Loop;
691
692 CLOSE get_elem_details;
693
694
695 -- Compute the difference at this point;
696
697 -- First Get rid of the Unwanted Statments
698
699
700 for j in 0..l_prev_ver_elem_ids.count-1
701 loop
702 l_exists := FALSE;
703 for k in 0..l_max_ver_elem_ids.count-1
704 loop
705
706 IF (l_max_ver_elem_ids(k) = l_prev_ver_elem_ids(j))
707 THEN
708 l_exists := TRUE;
709 EXIT;
710 END IF;
711
712 end loop;
713 IF (l_exists = FALSE)
714 THEN
715
716 -- Call to Obsolete the Statement l_prev_ver_elem_ids(j)
717 Obs_Elmt_Status_With_Check(l_prev_ver_elem_ids(j));
718
719 END IF;
720
721 end loop;
722
723 END Auto_Obsolete_For_Solution_Pub;
724
725
726 -- BugFix 3993200 - Sequence id fix
727 -- Replace above Function with new code below:
728 FUNCTION Is_Pub_Element_Obsoletable(p_element_id IN NUMBER)
729 RETURN NUMBER IS
730
731 CURSOR Get_solns IS
732 SELECT count(se.Set_id)
733 FROM CS_KB_SET_ELES se
734 WHERE se.ELEMENT_ID = p_element_id
735 AND EXISTS (Select 'x'
736 From CS_KB_SETS_B s
737 WHERE s.Set_id = se.set_id
738 AND (s.latest_version_flag = 'Y'
739 OR s.viewable_version_flag = 'Y')
740 );
741 l_count NUMBER;
742
743 BEGIN
744
745 -- Check if any Solutions contain Statement p_element_id
746 -- This includes all Latest Draft and Viewable Solution Versions
747 OPEN Get_solns;
748 FETCH Get_solns INTO l_count;
749 CLOSE Get_solns;
750
751 IF l_count = 0 THEN
752 -- No Solutions exist. 1 = Statement can be Obsoleted
753 RETURN 1;
754 ELSE
755 -- At leate One Solution exists. 0 = Statement should NOT be Obsoleted
756 RETURN 0;
757 END IF;
758
759 END;
760
761
762 Procedure Auto_Obsolete_For_Solution_Obs(p_set_number IN VARCHAR2,
763 p_max_set_id IN NUMBER) IS
764
765
766
767 l_element_id NUMBER(15);
768
769 CURSOR get_elem_info(c_set_id IN NUMBER)
770 IS
771
772 SELECT element_id from cs_kb_set_eles
773
774 WHERE set_id = c_set_id;
775
776
777 BEGIN
778
779
780 -- Possibly Obsolete statements in prev versions
781
782 Auto_Obsolete_For_Solution_Pub(p_set_number => p_set_number,
783 p_max_set_id => p_max_set_id);
784
785 -- Possibly obsolete the statements linked to this version;
786
787 Open get_elem_info(p_max_set_id);
788
789 Loop
790
791 Fetch get_elem_info INTO l_element_id;
792 EXIT WHEN get_elem_info%NOTFOUND;
793
794 Obs_Elmt_Status_With_Check(l_element_id);
795
796
797 End Loop;
798
799 Close get_elem_info;
800
801
802
803
804 END Auto_Obsolete_For_Solution_Obs;
805
806
807 PROCEDURE Obs_Elmt_Status_With_Check(p_element_id IN NUMBER) IS
808
809 l_status VARCHAR2(30);
810
811 BEGIN
812
813 select status INTO l_status
814 from cs_kb_elements_b
815 where element_id = p_element_id;
816
817 IF (l_status = 'DRAFT')
818 THEN
819 UPDATE CS_KB_ELEMENTS_B
820 SET status = 'OBS'
821 WHERE element_id = p_element_id;
822 ELSIF (l_status = 'PUBLISHED')
823 THEN
824 IF (Is_Pub_Element_Obsoletable(p_element_id) = 1)
825 THEN
826 UPDATE CS_KB_ELEMENTS_B
827 SET status = 'OBS'
828 WHERE element_id = p_element_id;
829
830 END IF;
831 END IF;
832
833
834 END Obs_Elmt_Status_With_Check;
835
836 PROCEDURE Transfer_Note_To_Element(p_note_id IN NUMBER, p_element_id IN NUMBER)
837 IS
838 l_clob1 clob;
839 l_clob2 clob;
840 BEGIN
841 select notes_detail
842 into l_clob1
843 from jtf_notes_tl
844 where jtf_note_id = p_note_id
845 and language = USERENV('LANG');
846
847 select description
848 into l_clob2
849 from cs_kb_elements_tl
850 where element_id = p_element_id
851 and language = USERENV('LANG')
852 for update;
853
854 DBMS_LOB.TRIM(l_clob2, 0);
855 DBMS_LOB.COPY(l_clob2, l_clob1, DBMS_LOB.GETLENGTH(l_clob1));
856
857 JTF_NOTES_PKG.DELETE_ROW(p_note_id);
858
859 EXCEPTION
860
861 WHEN NO_DATA_FOUND THEN
862 NULL;
863
864 END Transfer_Note_To_Element;
865
866 FUNCTION Get_Concatenated_Elmt_Details(p_set_id IN NUMBER) RETURN CLOB IS
867 l_clob_loc CLOB;
868 l_temp_clob_loc CLOB;
869
870 CURSOR C1(c_set_id IN NUMBER) IS
871 SELECT description from cs_kb_elements_tl, cs_kb_set_eles
872 where cs_kb_set_eles.set_id = c_set_id
873 AND cs_kb_set_eles.element_id = cs_kb_elements_tl.element_id
874 and cs_kb_elements_tl.language = USERENV('LANG');
875
876 BEGIN
877 dbms_lob.CREATETEMPORARY(l_clob_loc, TRUE, DBMS_LOB.session);
878 DBMS_LOB.TRIM(l_clob_loc, 0);
879
880 open c1(p_set_id);
881 loop
882 fetch c1 INTO l_temp_CLOB_LOC;
883 exit when c1%NOTFOUND;
884 dbms_lob.append(l_clob_loc, l_temp_clob_loc);
885 dbms_lob.writeappend(l_clob_loc, 1, ' ');
886 end loop;
887 close c1;
888 return l_clob_loc;
889 END Get_Concatenated_Elmt_Details;
890
891 /*
892 * forwards to Create_Set_With_Validation_2
893 */
894 FUNCTION Create_Set_With_Validation
895 (
896 p_api_version in number,
897 p_init_msg_list in varchar2 := FND_API.G_FALSE,
898 p_commit in varchar2 := FND_API.G_FALSE,
899 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
900 x_return_status OUT NOCOPY varchar2,
901 x_msg_count OUT NOCOPY number,
902 x_msg_data OUT NOCOPY varchar2,
903 p_set_type_name in varchar2,
904 p_set_visibility in varchar2,
905 p_set_title in varchar2,
906 p_set_flow_name in VARCHAR2,
907 p_set_flow_stepcode in VARCHAR2,
908 p_set_products in JTF_VARCHAR2_TABLE_2000,
909 p_set_platforms in JTF_VARCHAR2_TABLE_2000,
910 p_set_categories in JTF_VARCHAR2_TABLE_2000,
911 p_ele_type_name_tbl in JTF_VARCHAR2_TABLE_2000,
912 p_ele_dist_tbl in JTF_VARCHAR2_TABLE_2000,
913 p_ele_content_type_tbl in JTF_VARCHAR2_TABLE_2000,
914 p_ele_summary_tbl in JTF_VARCHAR2_TABLE_2000,
915 p_ele_nos_tbl in JTF_VARCHAR2_TABLE_2000,
916 p_ele_nos_upd_tbl in JTF_VARCHAR2_TABLE_2000,
917 p_ele_dist_upd_tbl in JTF_VARCHAR2_TABLE_2000,
918 p_ele_content_type_upd_tbl in JTF_VARCHAR2_TABLE_2000,
919 p_ele_summary_upd_tbl in JTF_VARCHAR2_TABLE_2000,
920 p_set_category_last_names in JTF_VARCHAR2_TABLE_2000,
921 x_created_ele_ids_tbl OUT NOCOPY JTF_NUMBER_TABLE,
922 x_ele_ids_upd_tbl OUT NOCOPY JTF_NUMBER_TABLE,
923 x_set_number OUT NOCOPY VARCHAR2) RETURN NUMBER IS
924
925 BEGIN
926
927 RETURN Create_Set_With_Validation_2(
928 p_api_version,
929 p_init_msg_list,
930 p_commit,
931 p_validation_level,
932 x_return_status,
933 x_msg_count,
934 x_msg_data,
935 p_set_type_name,
936 p_set_visibility,
937 p_set_title,
938 p_set_flow_name,
939 p_set_flow_stepcode,
940 p_set_products,
941 p_set_platforms,
942 p_set_categories,
943 p_ele_type_name_tbl,
944 p_ele_dist_tbl,
945 p_ele_content_type_tbl,
946 p_ele_summary_tbl,
947 p_ele_nos_tbl,
948 p_ele_nos_upd_tbl,
949 p_ele_dist_upd_tbl,
950 p_ele_content_type_upd_tbl,
951 p_ele_summary_upd_tbl,
952 p_set_category_last_names,
953 x_created_ele_ids_tbl,
954 x_ele_ids_upd_tbl,
955 x_set_number,
956 '>');
957
958 END Create_Set_With_Validation;
959
960 FUNCTION Create_Set_With_Validation_2
961 (
962 p_api_version in number,
963 p_init_msg_list in varchar2 := FND_API.G_FALSE,
964 p_commit in varchar2 := FND_API.G_FALSE,
965 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
966 x_return_status OUT NOCOPY varchar2,
967 x_msg_count OUT NOCOPY number,
968 x_msg_data OUT NOCOPY varchar2,
969 p_set_type_name in varchar2,
970 p_set_visibility in varchar2,
971 p_set_title in varchar2,
972 p_set_flow_name in VARCHAR2,
973 p_set_flow_stepcode in VARCHAR2,
974 p_set_products in JTF_VARCHAR2_TABLE_2000,
975 p_set_platforms in JTF_VARCHAR2_TABLE_2000,
976 p_set_categories in JTF_VARCHAR2_TABLE_2000,
977 p_ele_type_name_tbl in JTF_VARCHAR2_TABLE_2000,
978 p_ele_dist_tbl in JTF_VARCHAR2_TABLE_2000,
979 p_ele_content_type_tbl in JTF_VARCHAR2_TABLE_2000,
980 p_ele_summary_tbl in JTF_VARCHAR2_TABLE_2000,
981 p_ele_nos_tbl in JTF_VARCHAR2_TABLE_2000,
982 p_ele_nos_upd_tbl in JTF_VARCHAR2_TABLE_2000,
983 p_ele_dist_upd_tbl in JTF_VARCHAR2_TABLE_2000,
984 p_ele_content_type_upd_tbl in JTF_VARCHAR2_TABLE_2000,
985 p_ele_summary_upd_tbl in JTF_VARCHAR2_TABLE_2000,
986 p_set_category_last_names in JTF_VARCHAR2_TABLE_2000,
987 x_created_ele_ids_tbl OUT NOCOPY JTF_NUMBER_TABLE,
988 x_ele_ids_upd_tbl OUT NOCOPY JTF_NUMBER_TABLE,
989 x_set_number OUT NOCOPY VARCHAR2,
990 p_delim IN VARCHAR2
991 ) RETURN NUMBER IS
992 l_set_product_segments JTF_VARCHAR2_TABLE_2000;
993 l_set_platform_segments JTF_VARCHAR2_TABLE_2000;
994 BEGIN
995 l_set_product_segments := JTF_VARCHAR2_TABLE_2000();
996 l_set_platform_segments := JTF_VARCHAR2_TABLE_2000();
997 --If p_set_products is not empty, set l_set_product_segments to the same
998 --size and filled with ''. The same for platforms.
999 if(p_set_products.count>0) then
1000 l_set_product_segments.extend(p_set_products.count);
1001 for i in 1..p_set_products.count loop
1002 l_set_product_segments(i) := '';
1003 end loop;
1004 end if;
1005
1006 if (p_set_platforms.count>0) then
1007 l_set_platform_segments.extend(p_set_platforms.count);
1008 for i in 1..p_set_platforms.count loop
1009 l_set_platform_segments(i) := '';
1010 end loop;
1011 end if;
1012
1013 RETURN Create_Set_With_Validation_3(
1014 p_api_version,
1015 p_init_msg_list,
1016 p_commit,
1017 p_validation_level,
1018 x_return_status,
1019 x_msg_count,
1020 x_msg_data,
1021 p_set_type_name,
1022 p_set_visibility,
1023 p_set_title,
1024 p_set_flow_name,
1025 p_set_flow_stepcode,
1026 p_set_products,
1027 l_set_product_segments,
1028 p_set_platforms,
1029 l_set_platform_segments,
1030 p_set_categories,
1031 p_ele_type_name_tbl,
1032 p_ele_dist_tbl,
1033 p_ele_content_type_tbl,
1034 p_ele_summary_tbl,
1035 p_ele_nos_tbl,
1036 p_ele_nos_upd_tbl,
1037 p_ele_dist_upd_tbl,
1038 p_ele_content_type_upd_tbl,
1039 p_ele_summary_upd_tbl,
1040 p_set_category_last_names,
1041 x_created_ele_ids_tbl,
1042 x_ele_ids_upd_tbl,
1043 x_set_number,
1044 '>');
1045
1046 END Create_Set_With_Validation_2;
1047
1048 /*
1049 * forwards to Update_Set_With_Validation_2
1050 */
1051 FUNCTION Update_Set_With_Validation
1052 (
1053 p_api_version in number,
1054 p_init_msg_list in varchar2 := FND_API.G_FALSE,
1055 p_commit in varchar2 := FND_API.G_FALSE,
1056 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
1057 x_return_status OUT NOCOPY varchar2,
1058 x_msg_count OUT NOCOPY number,
1059 x_msg_data OUT NOCOPY varchar2,
1060 p_set_number in varchar2,
1061 p_set_type_name in varchar2,
1062 p_set_visibility in varchar2,
1063 p_set_title in varchar2,
1064 p_set_products in JTF_VARCHAR2_TABLE_2000,
1065 p_set_platforms in JTF_VARCHAR2_TABLE_2000,
1066 p_set_categories in JTF_VARCHAR2_TABLE_2000,
1067 p_ele_type_name_tbl in JTF_VARCHAR2_TABLE_2000,
1068 p_ele_dist_tbl in JTF_VARCHAR2_TABLE_2000,
1069 p_ele_content_type_tbl in JTF_VARCHAR2_TABLE_2000,
1070 p_ele_summary_tbl in JTF_VARCHAR2_TABLE_2000,
1071 p_ele_nos_tbl in JTF_VARCHAR2_TABLE_2000,
1072 p_ele_nos_upd_tbl in JTF_VARCHAR2_TABLE_2000,
1073 p_ele_dist_upd_tbl in JTF_VARCHAR2_TABLE_2000,
1074 p_ele_content_type_upd_tbl in JTF_VARCHAR2_TABLE_2000,
1075 p_ele_summary_upd_tbl in JTF_VARCHAR2_TABLE_2000,
1076 p_set_category_last_names in JTF_VARCHAR2_TABLE_2000,
1077 x_created_ele_ids_tbl OUT NOCOPY JTF_NUMBER_TABLE,
1078 x_ele_ids_upd_tbl OUT NOCOPY JTF_NUMBER_TABLE,
1079 x_set_id OUT NOCOPY number) RETURN NUMBER IS
1080
1081 BEGIN
1082 RETURN Update_Set_With_Validation_2(
1083 p_api_version,
1084 p_init_msg_list,
1085 p_commit,
1086 p_validation_level,
1087 x_return_status,
1088 x_msg_count,
1089 x_msg_data,
1090 p_set_number,
1091 p_set_type_name,
1092 p_set_visibility,
1093 p_set_title,
1094 p_set_products,
1095 p_set_platforms,
1096 p_set_categories,
1097 p_ele_type_name_tbl,
1098 p_ele_dist_tbl,
1099 p_ele_content_type_tbl,
1100 p_ele_summary_tbl,
1101 p_ele_nos_tbl,
1102 p_ele_nos_upd_tbl,
1103 p_ele_dist_upd_tbl,
1104 p_ele_content_type_upd_tbl,
1105 p_ele_summary_upd_tbl,
1106 p_set_category_last_names,
1107 x_created_ele_ids_tbl,
1108 x_ele_ids_upd_tbl,
1109 x_set_id,
1110 '>');
1111
1112 END Update_Set_With_validation;
1113
1114 /*
1115 * This one takes delimeter
1116 */
1117 FUNCTION Update_Set_With_Validation_2
1118 (
1119 p_api_version in number,
1120 p_init_msg_list in varchar2 := FND_API.G_FALSE,
1121 p_commit in varchar2 := FND_API.G_FALSE,
1122 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
1123 x_return_status OUT NOCOPY varchar2,
1124 x_msg_count OUT NOCOPY number,
1125 x_msg_data OUT NOCOPY varchar2,
1126 p_set_number in varchar2,
1127 p_set_type_name in varchar2,
1128 p_set_visibility in varchar2,
1129 p_set_title in varchar2,
1130 p_set_products in JTF_VARCHAR2_TABLE_2000,
1131 p_set_platforms in JTF_VARCHAR2_TABLE_2000,
1132 p_set_categories in JTF_VARCHAR2_TABLE_2000,
1133 p_ele_type_name_tbl in JTF_VARCHAR2_TABLE_2000,
1134 p_ele_dist_tbl in JTF_VARCHAR2_TABLE_2000,
1135 p_ele_content_type_tbl in JTF_VARCHAR2_TABLE_2000,
1136 p_ele_summary_tbl in JTF_VARCHAR2_TABLE_2000,
1137 p_ele_nos_tbl in JTF_VARCHAR2_TABLE_2000,
1138 p_ele_nos_upd_tbl in JTF_VARCHAR2_TABLE_2000,
1139 p_ele_dist_upd_tbl in JTF_VARCHAR2_TABLE_2000,
1140 p_ele_content_type_upd_tbl in JTF_VARCHAR2_TABLE_2000,
1141 p_ele_summary_upd_tbl in JTF_VARCHAR2_TABLE_2000,
1142 p_set_category_last_names in JTF_VARCHAR2_TABLE_2000,
1143 x_created_ele_ids_tbl OUT NOCOPY JTF_NUMBER_TABLE,
1144 x_ele_ids_upd_tbl OUT NOCOPY JTF_NUMBER_TABLE,
1145 x_set_id OUT NOCOPY number,
1146 p_delim IN VARCHAR2
1147 ) RETURN NUMBER IS
1148 l_set_product_segments JTF_VARCHAR2_TABLE_2000;
1149 l_set_platform_segments JTF_VARCHAR2_TABLE_2000;
1150 BEGIN
1151 l_set_product_segments := JTF_VARCHAR2_TABLE_2000();
1152 l_set_platform_segments := JTF_VARCHAR2_TABLE_2000();
1153 --If p_set_products is not empty, set l_set_product_segments to the same
1154 --size and filled with ''. The same for platforms.
1155 if(p_set_products.count>0) then
1156 l_set_product_segments.extend(p_set_products.count);
1157 for i in 1..p_set_products.count loop
1158 l_set_product_segments(i) := '';
1159 end loop;
1160 end if;
1161
1162 if (p_set_platforms.count>0) then
1163 l_set_platform_segments.extend(p_set_platforms.count);
1164 for i in 1..p_set_platforms.count loop
1165 l_set_platform_segments(i) := '';
1166 end loop;
1167 end if;
1168
1169 RETURN Update_Set_With_Validation_3(
1170 p_api_version,
1171 p_init_msg_list,
1172 p_commit,
1173 p_validation_level,
1174 x_return_status,
1175 x_msg_count,
1176 x_msg_data,
1177 p_set_number,
1178 p_set_type_name,
1179 p_set_visibility,
1180 p_set_title,
1181 p_set_products,
1182 l_set_product_segments,
1183 p_set_platforms,
1184 l_set_platform_segments,
1185 p_set_categories,
1186 p_ele_type_name_tbl,
1187 p_ele_dist_tbl,
1188 p_ele_content_type_tbl,
1189 p_ele_summary_tbl,
1190 p_ele_nos_tbl,
1191 p_ele_nos_upd_tbl,
1192 p_ele_dist_upd_tbl,
1193 p_ele_content_type_upd_tbl,
1194 p_ele_summary_upd_tbl,
1195 p_set_category_last_names,
1196 x_created_ele_ids_tbl,
1197 x_ele_ids_upd_tbl,
1198 x_set_id,
1199 '>');
1200
1201 END Update_Set_With_validation_2;
1202
1203 /*
1204 * forwards to VALIDATE_SOLN_ATTRIBUTES_2
1205 */
1206 FUNCTION VALIDATE_SOLN_ATTRIBUTES
1207 (
1208 p_set_type_id IN NUMBER,
1209 p_visibility_name IN VARCHAR2,
1210 p_product_names IN JTF_VARCHAR2_TABLE_2000,
1211 p_platform_names IN JTF_VARCHAR2_TABLE_2000,
1212 p_category_names IN JTF_VARCHAR2_TABLE_2000,
1213 p_category_last_names IN JTF_VARCHAR2_TABLE_2000,
1214 p_ele_nums IN JTF_VARCHAR2_TABLE_2000,
1215 p_ele_upd_nums IN JTF_VARCHAR2_TABLE_2000,
1216 p_ele_upd_content_types IN JTF_VARCHAR2_TABLE_2000,
1217 p_ele_upd_dist_names IN JTF_VARCHAR2_TABLE_2000,
1218 p_element_type_names IN JTF_VARCHAR2_TABLE_2000,
1219 p_ele_content_types IN JTF_VARCHAR2_TABLE_2000,
1220 p_ele_dist_names IN JTF_VARCHAR2_TABLE_2000,
1221 x_visibility_id OUT NOCOPY NUMBER,
1222 x_product_numbers OUT NOCOPY JTF_NUMBER_TABLE,
1223 x_platform_numbers OUT NOCOPY JTF_NUMBER_TABLE,
1224 x_category_numbers OUT NOCOPY JTF_NUMBER_TABLE,
1225 x_ele_ids OUT NOCOPY JTF_NUMBER_TABLE,
1226 x_ele_upd_ids OUT NOCOPY JTF_NUMBER_TABLE,
1227 x_element_type_ids OUT NOCOPY JTF_NUMBER_TABLE,
1228 x_ele_dist_ids OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
1229 x_ele_content_type_codes OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
1230 x_ele_upd_type_ids OUT NOCOPY JTF_NUMBER_TABLE,
1231 x_ele_upd_dist_ids OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
1232 x_ele_upd_content_type_codes OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
1233 x_return_status OUT NOCOPY varchar2,
1234 x_msg_count OUT NOCOPY number,
1235 x_msg_data OUT NOCOPY varchar2
1236
1237 ) RETURN NUMBER IS
1238
1239 BEGIN
1240 RETURN VALIDATE_SOLN_ATTRIBUTES_2(
1241 p_set_type_id,
1242 p_visibility_name,
1243 p_product_names,
1244 p_platform_names,
1245 p_category_names,
1246 p_category_last_names,
1247 p_ele_nums,
1248 p_ele_upd_nums,
1249 p_ele_upd_content_types,
1250 p_ele_upd_dist_names,
1251 p_element_type_names,
1252 p_ele_content_types,
1253 p_ele_dist_names,
1254 x_visibility_id,
1255 x_product_numbers,
1256 x_platform_numbers,
1257 x_category_numbers,
1258 x_ele_ids,
1259 x_ele_upd_ids,
1260 x_element_type_ids,
1261 x_ele_dist_ids,
1262 x_ele_content_type_codes,
1263 x_ele_upd_type_ids,
1264 x_ele_upd_dist_ids,
1265 x_ele_upd_content_type_codes,
1266 x_return_status,
1267 x_msg_count,
1268 x_msg_data,
1269 '>');
1270
1271 END VALIDATE_SOLN_ATTRIBUTES;
1272
1273 FUNCTION VALIDATE_SOLN_ATTRIBUTES_2
1274 (
1275 p_set_type_id IN NUMBER,
1276 p_visibility_name IN VARCHAR2,
1277 p_product_names IN JTF_VARCHAR2_TABLE_2000,
1278 p_platform_names IN JTF_VARCHAR2_TABLE_2000,
1279 p_category_names IN JTF_VARCHAR2_TABLE_2000,
1280 p_category_last_names IN JTF_VARCHAR2_TABLE_2000,
1281 p_ele_nums IN JTF_VARCHAR2_TABLE_2000,
1282 p_ele_upd_nums IN JTF_VARCHAR2_TABLE_2000,
1283 p_ele_upd_content_types IN JTF_VARCHAR2_TABLE_2000,
1284 p_ele_upd_dist_names IN JTF_VARCHAR2_TABLE_2000,
1285 p_element_type_names IN JTF_VARCHAR2_TABLE_2000,
1286 p_ele_content_types IN JTF_VARCHAR2_TABLE_2000,
1287 p_ele_dist_names IN JTF_VARCHAR2_TABLE_2000,
1288 x_visibility_id OUT NOCOPY NUMBER,
1289 x_product_numbers OUT NOCOPY JTF_NUMBER_TABLE,
1290 x_platform_numbers OUT NOCOPY JTF_NUMBER_TABLE,
1291 x_category_numbers OUT NOCOPY JTF_NUMBER_TABLE,
1292 x_ele_ids OUT NOCOPY JTF_NUMBER_TABLE,
1293 x_ele_upd_ids OUT NOCOPY JTF_NUMBER_TABLE,
1294 x_element_type_ids OUT NOCOPY JTF_NUMBER_TABLE,
1295 x_ele_dist_ids OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
1296 x_ele_content_type_codes OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
1297 x_ele_upd_type_ids OUT NOCOPY JTF_NUMBER_TABLE,
1298 x_ele_upd_dist_ids OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
1299 x_ele_upd_content_type_codes OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
1300 x_return_status OUT NOCOPY varchar2,
1301 x_msg_count OUT NOCOPY number,
1302 x_msg_data OUT NOCOPY varchar2,
1303 p_delim IN VARCHAR2
1304
1305 ) RETURN NUMBER IS
1306 l_product_segments JTF_VARCHAR2_TABLE_2000;
1307 l_platform_segments JTF_VARCHAR2_TABLE_2000;
1308 BEGIN
1309 l_product_segments := JTF_VARCHAR2_TABLE_2000();
1310 l_platform_segments := JTF_VARCHAR2_TABLE_2000();
1311 RETURN VALIDATE_SOLN_ATTRIBUTES_3(
1312 p_set_type_id,
1313 p_visibility_name,
1314 p_product_names,
1315 l_product_segments,
1316 p_platform_names,
1317 l_platform_segments,
1318 p_category_names,
1319 p_category_last_names,
1320 p_ele_nums,
1321 p_ele_upd_nums,
1322 p_ele_upd_content_types,
1323 p_ele_upd_dist_names,
1324 p_element_type_names,
1325 p_ele_content_types,
1326 p_ele_dist_names,
1327 x_visibility_id,
1328 x_product_numbers,
1329 x_platform_numbers,
1330 x_category_numbers,
1331 x_ele_ids,
1332 x_ele_upd_ids,
1333 x_element_type_ids,
1334 x_ele_dist_ids,
1335 x_ele_content_type_codes,
1336 x_ele_upd_type_ids,
1337 x_ele_upd_dist_ids,
1338 x_ele_upd_content_type_codes,
1339 x_return_status,
1340 x_msg_count,
1341 x_msg_data,
1342 '>');
1343
1344 END VALIDATE_SOLN_ATTRIBUTES_2;
1345 /*
1346 This is the same as the original create_set_with_validation, except it takes a delimiter param
1347 */
1348 FUNCTION Create_Set_With_Validation_3
1349 (
1350 p_api_version in number,
1351 p_init_msg_list in varchar2 := FND_API.G_FALSE,
1352 p_commit in varchar2 := FND_API.G_FALSE,
1353 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
1354 x_return_status OUT NOCOPY varchar2,
1355 x_msg_count OUT NOCOPY number,
1356 x_msg_data OUT NOCOPY varchar2,
1357 p_set_type_name in varchar2,
1358 p_set_visibility in varchar2,
1359 p_set_title in varchar2,
1360 p_set_flow_name in VARCHAR2,
1361 p_set_flow_stepcode in VARCHAR2,
1362 p_set_products in JTF_VARCHAR2_TABLE_2000,
1363 p_set_product_segments in JTF_VARCHAR2_TABLE_2000,
1364 p_set_platforms in JTF_VARCHAR2_TABLE_2000,
1365 p_set_platform_segments in JTF_VARCHAR2_TABLE_2000,
1366 p_set_categories in JTF_VARCHAR2_TABLE_2000,
1367 p_ele_type_name_tbl in JTF_VARCHAR2_TABLE_2000,
1368 p_ele_dist_tbl in JTF_VARCHAR2_TABLE_2000,
1369 p_ele_content_type_tbl in JTF_VARCHAR2_TABLE_2000,
1370 p_ele_summary_tbl in JTF_VARCHAR2_TABLE_2000,
1371 p_ele_nos_tbl in JTF_VARCHAR2_TABLE_2000,
1372 p_ele_nos_upd_tbl in JTF_VARCHAR2_TABLE_2000,
1373 p_ele_dist_upd_tbl in JTF_VARCHAR2_TABLE_2000,
1374 p_ele_content_type_upd_tbl in JTF_VARCHAR2_TABLE_2000,
1375 p_ele_summary_upd_tbl in JTF_VARCHAR2_TABLE_2000,
1376 p_set_category_last_names in JTF_VARCHAR2_TABLE_2000,
1377 x_created_ele_ids_tbl OUT NOCOPY JTF_NUMBER_TABLE,
1378 x_ele_ids_upd_tbl OUT NOCOPY JTF_NUMBER_TABLE,
1379 x_set_number OUT NOCOPY VARCHAR2,
1380 p_delim IN VARCHAR2
1381 ) RETURN NUMBER IS
1382
1383 l_set_id number;
1384 l_set_type_id NUMBER;
1385 l_set_access_level VARCHAR2(2000);
1386 l_set_visibility_id NUMBER;
1387 l_element_ids JTF_NUMBER_TABLE;
1388 l_element_type_ids JTF_NUMBER_TABLE;
1389 l_element_dist_ids JTF_VARCHAR2_TABLE_2000;
1390 l_element_content_type_codes JTF_VARCHAR2_TABLE_2000;
1391 l_ele_type_id_upd_tbl JTF_NUMBER_TABLE;
1392 l_ele_dist_id_upd_tbl JTF_VARCHAR2_TABLE_2000;
1393 l_ele_conttype_codes_upd_tbl JTF_VARCHAR2_TABLE_2000;
1394 l_set_product_ids JTF_NUMBER_TABLE;
1395 l_set_platform_ids JTF_NUMBER_TABLE;
1396 l_set_category_ids JTF_NUMBER_TABLE;
1397 l_set_product_org_ids JTF_NUMBER_TABLE;
1398 l_set_platform_org_ids JTF_NUMBER_TABLE;
1399 l_temp_clob CLOB;
1400 l_flow_details_id number := null;
1401 l_validate_buf VARCHAR2(1000);
1402 l_return_val NUMBER;
1403 l_return_status VARCHAR2(1);
1404 l_msg_data VARCHAR2(2000);
1405 l_msg_count NUMBER;
1406
1407 BEGIN
1408
1409 SAVEPOINT Create_Set;
1410
1411 x_created_ele_ids_tbl := JTF_NUMBER_TABLE();
1412 x_ele_ids_upd_tbl := JTF_NUMBER_TABLE();
1413 l_element_ids := JTF_NUMBER_TABLE();
1414 l_element_type_ids := JTF_NUMBER_TABLE();
1415 l_element_dist_ids := JTF_VARCHAR2_TABLE_2000();
1416 l_element_content_type_codes := JTF_VARCHAR2_TABLE_2000();
1417 l_ele_type_id_upd_tbl := JTF_NUMBER_TABLE();
1418 l_ele_dist_id_upd_tbl := JTF_VARCHAR2_TABLE_2000();
1419 l_ele_conttype_codes_upd_tbl := JTF_VARCHAR2_TABLE_2000();
1420 l_set_product_ids := JTF_NUMBER_TABLE();
1421 l_set_platform_ids := JTF_NUMBER_TABLE();
1422 l_set_category_ids := JTF_NUMBER_TABLE();
1423 l_set_product_org_ids := JTF_NUMBER_TABLE();
1424 l_set_platform_org_ids := JTF_NUMBER_TABLE();
1425
1426 x_return_status := FND_API.G_RET_STS_SUCCESS;
1427
1428 -- Validate Set Type Name, get Set Type ID
1429 l_validate_buf := p_set_type_name;
1430 l_return_val := Validate_Set_Type_Name_Create(p_set_type_name,
1431 l_set_type_id);
1432 IF (l_return_val = ERROR_STATUS)
1433 THEN RAISE INVALID_SET_TYPE_NAME;
1434 END IF;
1435
1436 -- validate flow info if provided
1437 IF (p_set_flow_name IS NOT NULL) AND (p_set_flow_stepcode IS NOT NULL) THEN
1438 l_return_val := Validate_Flow (
1439 p_flow_name => p_set_flow_name,
1440 p_flow_step => p_set_flow_stepcode,
1441 x_flow_details_id => l_flow_details_id);
1442
1443 IF (l_return_val = ERROR_STATUS)
1444 THEN RAISE INVALID_FLOW;
1445 END IF;
1446 END IF;
1447
1448 -- validate all other attributes
1449 l_return_val := VALIDATE_SOLN_ATTRIBUTES_3 (
1450 p_set_type_id => l_set_type_id,
1451 p_visibility_name => p_set_visibility,
1452 p_product_names => p_set_products,
1453 p_product_segments => p_set_product_segments,
1454 p_platform_names => p_set_platforms,
1455 p_platform_segments => p_set_platform_segments,
1456 p_category_names => p_set_categories,
1457 p_category_last_names => p_set_category_last_names,
1458 p_ele_nums => p_ele_nos_tbl,
1459 p_ele_upd_nums => p_ele_nos_upd_tbl,
1460 p_ele_upd_content_types => p_ele_content_type_upd_tbl,
1461 p_ele_upd_dist_names => p_ele_dist_upd_tbl,
1462 p_element_type_names => p_ele_type_name_tbl,
1463 p_ele_content_types => p_ele_content_type_tbl,
1464 p_ele_dist_names => p_ele_dist_tbl,
1465 x_visibility_id => l_set_visibility_id,
1466 x_product_numbers => l_set_product_ids,
1467 x_platform_numbers => l_set_platform_ids,
1468 x_category_numbers => l_set_category_ids,
1469 x_ele_ids => l_element_ids,
1470 x_ele_upd_ids => x_ele_ids_upd_tbl,
1471 x_element_type_ids => l_element_type_ids,
1472 x_ele_dist_ids => l_element_dist_ids,
1473 x_ele_content_type_codes =>l_element_content_type_codes,
1474 x_ele_upd_type_ids => l_ele_type_id_upd_tbl,
1475 x_ele_upd_dist_ids => l_ele_dist_id_upd_tbl,
1476 x_ele_upd_content_type_codes => l_ele_conttype_codes_upd_tbl,
1477 x_return_status => x_return_status,
1478 x_msg_count => x_msg_count,
1479 x_msg_data => x_msg_data,
1480 p_delim => p_delim
1481 );
1482
1483 -- dbms_output.put_line('return from VALIDATE='||l_return_val);
1484 IF (x_msg_count = 1) THEN
1485 RAISE VALIDATION_ERROR;
1486 END IF;
1487
1488 -- Start Creation at this point
1489 CS_KB_SOLUTION_PVT.Create_Solution
1490 ( p_set_type_id => l_set_type_id,
1491 p_name => p_set_title,
1492 p_visibility_id => l_set_visibility_id,
1493 x_set_id => l_set_id,
1494 x_set_number => x_set_number,
1495 x_return_status => l_return_status,
1496 x_msg_data => l_msg_data,
1497 x_msg_count => l_msg_count );
1498
1499 -- dbms_output.put_line('created x_set_id='||l_set_id);
1500 if( (l_return_status = FND_API.G_RET_STS_ERROR) or
1501 (l_set_id <= 0) ) then
1502 raise FND_API.G_EXC_ERROR;
1503 elsif ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) then
1504 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1505 end if;
1506
1507 -- link set with statements, products, categories, platforms, etc.
1508 -- also create new statements if info provided
1509 l_return_val := Link_Soln_Attributes(
1510 p_validate_type => 'CREATE',
1511 p_set_id => l_set_id,
1512 p_given_element_ids => x_ele_ids_upd_tbl,
1513 p_given_ele_nums => p_ele_nos_upd_tbl,
1514 p_given_ele_type_ids => l_ele_type_id_upd_tbl,
1515 p_given_ele_dist_ids => l_ele_dist_id_upd_tbl,
1516 p_given_ele_content_types => l_ele_conttype_codes_upd_tbl,
1517 p_given_ele_summaryies => p_ele_summary_upd_tbl,
1518 p_element_ids => l_element_ids,
1519 p_element_type_ids => l_element_type_ids,
1520 p_element_dist_ids => l_element_dist_ids,
1521 p_element_content_types => l_element_content_type_codes,
1522 p_element_summaries => p_ele_summary_tbl,
1523 p_element_dummy_detail => l_temp_clob,
1524 p_set_product_ids => l_set_product_ids,
1525 p_set_platform_ids => l_set_platform_ids,
1526 p_set_category_ids => l_set_category_ids,
1527 x_created_element_ids => x_created_ele_ids_tbl,
1528 x_return_status => x_return_status,
1529 x_msg_count => x_msg_count,
1530 x_msg_data => x_msg_data);
1531
1532 -- update set with flow info and status.
1533 IF (l_flow_details_id IS NOT NULL) THEN
1534 if( Get_Missing_Ele_Type( l_set_id ) = 'N' ) then
1535 --l_set_id := CS_KB_SOLUTION_PVT.clone_solution(x_set_number,
1536 -- 'PUB', l_flow_details_id, null);
1537 null ;
1538 --bug fix 6034639 commented the above code.
1539 else
1540 raise MANDATORY_STATEMENT_MISSING;
1541 end if;
1542 END IF;
1543
1544 -- dbms_output.put_line('return from LINK='||l_return_val);
1545
1546 x_return_status := fnd_api.g_ret_sts_success;
1547 x_msg_count := 0;
1548 x_msg_data := null;
1549 RETURN OKAY_STATUS;
1550
1551 EXCEPTION
1552
1553 WHEN VALIDATION_ERROR THEN
1554 /*
1555 x_return_status := FND_API.G_RET_STS_ERROR;
1556 x_msg_count := 1;
1557 x_msg_data := 'Invalid Set Type Name : ' || p_set_type_name;
1558 */
1559
1560 RETURN ERROR_STATUS;
1561
1562 WHEN MANDATORY_STATEMENT_MISSING THEN
1563 ROLLBACK TO Create_Set;
1564 x_return_status := FND_API.G_RET_STS_ERROR;
1565 x_msg_count := 1;
1566 x_msg_data := 'Mandatory Statements Missing.';
1567
1568 RETURN ERROR_STATUS;
1569
1570 WHEN INVALID_SET_TYPE_NAME THEN
1571 ROLLBACK TO Create_Set;
1572 x_return_status := FND_API.G_RET_STS_ERROR;
1573 x_msg_count := 1;
1574 x_msg_data := 'Invalid Set Type Name : ' || p_set_type_name;
1575
1576 RETURN ERROR_STATUS;
1577
1578 WHEN INVALID_FLOW THEN
1579 ROLLBACK TO Create_Set;
1580 x_return_status := FND_API.G_RET_STS_ERROR;
1581 x_msg_count := 1;
1582 x_msg_data := 'Invalid flow: ' || p_set_flow_name ||
1583 ' or step: ' || p_set_flow_stepcode;
1584
1585 RETURN ERROR_STATUS;
1586
1587 WHEN DUPLICATE_SET_NAME THEN
1588 ROLLBACK TO Create_Set;
1589 x_return_status := FND_API.G_RET_STS_ERROR;
1590 x_msg_count := 1;
1591 x_msg_data := 'Duplicate Solution Name:' || l_validate_buf;
1592
1593 RETURN ERROR_STATUS;
1594
1595 WHEN FND_API.G_EXC_ERROR THEN
1596 ROLLBACK TO Create_Set;
1597 x_return_status := FND_API.G_RET_STS_ERROR ;
1598 FND_MSG_PUB.Count_And_Get(
1599 p_encoded => FND_API.G_FALSE,
1600 p_count => x_msg_count,
1601 p_data => x_msg_data);
1602 RETURN ERROR_STATUS;
1603
1604 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1605 ROLLBACK TO Create_Set;
1606 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1607 FND_MSG_PUB.Count_And_Get(
1608 p_encoded => FND_API.G_FALSE ,
1609 p_count => x_msg_count,
1610 p_data => x_msg_data);
1611 RETURN ERROR_STATUS;
1612
1613 WHEN OTHERS THEN
1614 ROLLBACK TO Create_Set;
1615 x_msg_data := 'Creating solution: ' || SQLERRM ;
1616 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1617 x_msg_count := 1;
1618 RETURN ERROR_STATUS;
1619
1620 END Create_Set_With_Validation_3;
1621
1622 /*
1623 Add support for product/platform segments
1624 */
1625 FUNCTION Update_Set_With_Validation_3
1626 (
1627 p_api_version in number,
1628 p_init_msg_list in varchar2 := FND_API.G_FALSE,
1629 p_commit in varchar2 := FND_API.G_FALSE,
1630 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
1631 x_return_status OUT NOCOPY varchar2,
1632 x_msg_count OUT NOCOPY number,
1633 x_msg_data OUT NOCOPY varchar2,
1634 p_set_number in varchar2,
1635 p_set_type_name in varchar2,
1636 p_set_visibility in varchar2,
1637 p_set_title in varchar2,
1638 p_set_products in JTF_VARCHAR2_TABLE_2000,
1639 p_set_product_segments in JTF_VARCHAR2_TABLE_2000,
1640 p_set_platforms in JTF_VARCHAR2_TABLE_2000,
1641 p_set_platform_segments in JTF_VARCHAR2_TABLE_2000,
1642 p_set_categories in JTF_VARCHAR2_TABLE_2000,
1643 p_ele_type_name_tbl in JTF_VARCHAR2_TABLE_2000,
1644 p_ele_dist_tbl in JTF_VARCHAR2_TABLE_2000,
1645 p_ele_content_type_tbl in JTF_VARCHAR2_TABLE_2000,
1646 p_ele_summary_tbl in JTF_VARCHAR2_TABLE_2000,
1647 p_ele_nos_tbl in JTF_VARCHAR2_TABLE_2000,
1648 p_ele_nos_upd_tbl in JTF_VARCHAR2_TABLE_2000,
1649 p_ele_dist_upd_tbl in JTF_VARCHAR2_TABLE_2000,
1650 p_ele_content_type_upd_tbl in JTF_VARCHAR2_TABLE_2000,
1651 p_ele_summary_upd_tbl in JTF_VARCHAR2_TABLE_2000,
1652 p_set_category_last_names in JTF_VARCHAR2_TABLE_2000,
1653 x_created_ele_ids_tbl OUT NOCOPY JTF_NUMBER_TABLE,
1654 x_ele_ids_upd_tbl OUT NOCOPY JTF_NUMBER_TABLE,
1655 x_set_id OUT NOCOPY number,
1656 p_delim IN VARCHAR2
1657 ) RETURN NUMBER IS
1658
1659 l_set_type_id NUMBER;
1660 l_set_access_level VARCHAR2(2000);
1661 l_set_visibility_id NUMBER;
1662 l_element_ids JTF_NUMBER_TABLE;
1663 l_element_type_ids JTF_NUMBER_TABLE;
1664 l_element_dist_ids JTF_VARCHAR2_TABLE_2000;
1665 l_element_content_type_codes JTF_VARCHAR2_TABLE_2000;
1666 l_ele_type_id_upd_tbl JTF_NUMBER_TABLE;
1667 l_ele_dist_id_upd_tbl JTF_VARCHAR2_TABLE_2000;
1668 l_ele_conttype_codes_upd_tbl JTF_VARCHAR2_TABLE_2000;
1669 l_set_product_ids JTF_NUMBER_TABLE;
1670 l_set_platform_ids JTF_NUMBER_TABLE;
1671 l_set_category_ids JTF_NUMBER_TABLE;
1672 l_set_product_org_ids JTF_NUMBER_TABLE;
1673 l_set_platform_org_ids JTF_NUMBER_TABLE;
1674 l_temp_clob CLOB;
1675 l_validate_buf VARCHAR2(1000);
1676 l_return_val NUMBER;
1677 l_return_status VARCHAR2(1);
1678 l_msg_data VARCHAR2(2000);
1679 l_msg_count NUMBER;
1680
1681 -- old IDs to delete
1682 l_old_set_product_ids JTF_NUMBER_TABLE;
1683 l_old_set_platform_ids JTF_NUMBER_TABLE;
1684 l_old_set_product_org_ids JTF_NUMBER_TABLE;
1685 l_old_set_platform_org_ids JTF_NUMBER_TABLE;
1686 counter number := 1;
1687
1688 cursor element_ids_cur (p_set_id IN NUMBER) IS
1689 select element_id
1690 from cs_kb_set_eles
1691 where set_id = p_set_id;
1692
1693 cursor product_ids_cur (p_set_id IN NUMBER) IS
1694 select product_id, product_org_id
1695 from cs_kb_set_products
1696 where set_id = p_set_id;
1697
1698 cursor platform_ids_cur (p_set_id IN NUMBER) IS
1699 select platform_id, platform_org_id
1700 from cs_kb_set_platforms
1701 where set_id = p_set_id;
1702
1703 cursor category_ids_cur (p_set_id IN NUMBER) IS
1704 select category_id
1705 from cs_kb_set_categories
1706 where set_id = p_set_id;
1707
1708
1709 BEGIN
1710 -- dbms_output.put_line('Update Set with Validation - BEGIN');
1711 SAVEPOINT Update_Set;
1712
1713 x_created_ele_ids_tbl := JTF_NUMBER_TABLE();
1714 l_element_ids := JTF_NUMBER_TABLE();
1715 l_element_type_ids := JTF_NUMBER_TABLE();
1716 l_element_dist_ids := JTF_VARCHAR2_TABLE_2000();
1717 l_element_content_type_codes := JTF_VARCHAR2_TABLE_2000();
1718 l_ele_type_id_upd_tbl := JTF_NUMBER_TABLE();
1719 l_ele_dist_id_upd_tbl := JTF_VARCHAR2_TABLE_2000();
1720 x_ele_ids_upd_tbl := JTF_NUMBER_TABLE();
1721 l_ele_conttype_codes_upd_tbl := JTF_VARCHAR2_TABLE_2000();
1722 l_set_product_ids := JTF_NUMBER_TABLE();
1723 l_set_platform_ids := JTF_NUMBER_TABLE();
1724 l_set_category_ids := JTF_NUMBER_TABLE();
1725 l_set_product_org_ids := JTF_NUMBER_TABLE();
1726 l_set_platform_org_ids := JTF_NUMBER_TABLE();
1727
1728 l_old_set_product_ids := JTF_NUMBER_TABLE();
1729 l_old_set_platform_ids := JTF_NUMBER_TABLE();
1730 l_old_set_product_org_ids := JTF_NUMBER_TABLE();
1731 l_old_set_platform_org_ids := JTF_NUMBER_TABLE();
1732
1733 x_return_status := FND_API.G_RET_STS_SUCCESS;
1734
1735 -- dbms_output.put_line('Validate Set number');
1736 -- Validate Set number, get Set ID
1737 l_validate_buf := p_set_number;
1738 l_return_val := Validate_Set_Number(p_set_number,
1739 x_set_id);
1740 IF (l_return_val = ERROR_STATUS)
1741 THEN RAISE INVALID_SET_NUMBER;
1742 END IF;
1743
1744 -- dbms_output.put_line('Validate Set type name');
1745 -- Validate Set Type Name, get Set Type ID
1746 l_validate_buf := p_set_type_name;
1747 l_return_val := Validate_Set_Type_Name_Update(
1748 x_set_id,
1749 p_set_type_name,
1750 l_set_type_id);
1751 IF (l_return_val = ERROR_STATUS)
1752 THEN RAISE INVALID_SET_TYPE_NAME;
1753 END IF;
1754
1755 -- dbms_output.put_line('Validate soln attributes');
1756 -- validate all other attributes
1757 l_return_val := VALIDATE_SOLN_ATTRIBUTES_3 (
1758 p_set_type_id => l_set_type_id,
1759 p_visibility_name => p_set_visibility,
1760 p_product_names => p_set_products,
1761 p_product_segments => p_set_product_segments,
1762 p_platform_names => p_set_platforms,
1763 p_platform_segments => p_set_platform_segments,
1764 p_category_names => p_set_categories,
1765 p_category_last_names => p_set_category_last_names,
1766 p_ele_nums => p_ele_nos_tbl,
1767 p_ele_upd_nums => p_ele_nos_upd_tbl,
1768 p_ele_upd_content_types => p_ele_content_type_upd_tbl,
1769 p_ele_upd_dist_names => p_ele_dist_upd_tbl,
1770 p_element_type_names => p_ele_type_name_tbl,
1771 p_ele_content_types => p_ele_content_type_tbl,
1772 p_ele_dist_names => p_ele_dist_tbl,
1773 x_visibility_id => l_set_visibility_id,
1774 x_product_numbers => l_set_product_ids,
1775 x_platform_numbers => l_set_platform_ids,
1776 x_category_numbers => l_set_category_ids,
1777 x_ele_ids => l_element_ids,
1778 x_ele_upd_ids => x_ele_ids_upd_tbl,
1779 x_element_type_ids => l_element_type_ids,
1780 x_ele_dist_ids => l_element_dist_ids,
1781 x_ele_content_type_codes => l_element_content_type_codes,
1782 x_ele_upd_type_ids => l_ele_type_id_upd_tbl,
1783 x_ele_upd_dist_ids => l_ele_dist_id_upd_tbl,
1784 x_ele_upd_content_type_codes => l_ele_conttype_codes_upd_tbl,
1785 x_return_status => x_return_status,
1786 x_msg_count => x_msg_count,
1787 x_msg_data => x_msg_data,
1788 p_delim => p_delim
1789 );
1790
1791 --dbms_output.put_line('return from CVALIDATE='||l_return_val);
1792
1793 IF (x_msg_count = 1) THEN
1794 RAISE VALIDATION_ERROR;
1795 END IF;
1796
1797 -- dbms_output.put_line('done all validations.');
1798
1799 -- update set
1800 CS_KB_SOLUTION_PVT.Update_Solution
1801 ( p_set_id => x_set_id,
1802 p_set_number => p_set_number,
1803 p_set_type_id => l_set_type_id,
1804 p_name => p_set_title,
1805 p_visibility_id => l_set_visibility_id,
1806 p_status => 'PUB',
1807 x_return_status => l_return_status,
1808 x_msg_count => l_msg_count,
1809 x_msg_data => l_msg_data,
1810 p_attribute_category => null,
1811 p_attribute1 => null,
1812 p_attribute2 => null,
1813 p_attribute3 => null,
1814 p_attribute4 => null,
1815 p_attribute5 => null,
1816 p_attribute6 => null,
1817 p_attribute7 => null,
1818 p_attribute8 => null,
1819 p_attribute9 => null,
1820 p_attribute10 => null,
1821 p_attribute11 => null,
1822 p_attribute12 => null,
1823 p_attribute13 => null,
1824 p_attribute14 => null,
1825 p_attribute15 => null
1826 );
1827
1828 -- dbms_output.put_line('return from UPDATE_SET='||l_return_val);
1829 if( (l_return_status = FND_API.G_RET_STS_ERROR) or
1830 (x_set_id <= 0) ) then
1831 raise FND_API.G_EXC_ERROR;
1832 elsif ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) then
1833 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1834 end if;
1835
1836 -- dbms_output.put_line('updated soln.');
1837
1838 -- Unlink all element ids for this set_id
1839 FOR cur_row IN element_ids_cur(x_set_id) LOOP
1840 l_return_val := Del_Element_From_Set(
1841 p_element_id => cur_row.element_id,
1842 p_set_id => x_set_id
1843 );
1844 if (l_return_val < 0) then
1845 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1846 end if;
1847 END LOOP;
1848 -- dbms_output.put_line('unlinked element ids. ');
1849
1850 -- unlink all products
1851 FOR cur_row IN product_ids_cur(x_set_id) LOOP
1852 l_old_set_product_ids.extend;
1853 l_old_set_product_ids(counter) := cur_row.product_id;
1854 l_old_set_product_org_ids.extend;
1855 l_old_set_product_org_ids(counter) := cur_row.product_org_id;
1856 counter := counter + 1;
1857 END LOOP;
1858
1859 cs_kb_assoc_pkg.add_link(p_item_id => l_old_set_product_ids,
1860 p_org_id => l_old_set_product_org_ids,
1861 p_set_id => x_set_id,
1862 p_link_type => 1,
1863 p_task => 0,
1864 p_result => l_return_val);
1865
1866 if (l_return_val < 0) then
1867 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1868 end if;
1869 -- dbms_output.put_line('unlinked products. ');
1870
1871 -- unlink all platforms
1872 counter := 1;
1873 FOR cur_row IN platform_ids_cur(x_set_id) LOOP
1874 l_old_set_platform_ids.extend;
1875 l_old_set_platform_ids(counter) := cur_row.platform_id;
1876 l_old_set_platform_org_ids.extend;
1877 l_old_set_platform_org_ids(counter) := cur_row.platform_org_id;
1878 counter := counter + 1;
1879 END LOOP;
1880
1881 cs_kb_assoc_pkg.add_link(p_item_id => l_old_set_platform_ids,
1882 p_org_id => l_old_set_platform_org_ids,
1883 p_set_id => x_set_id,
1884 p_link_type => 0,
1885 p_task => 0,
1886 p_result => l_return_val);
1887
1888 if (l_return_val < 0) then
1889 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1890 end if;
1891 -- dbms_output.put_line('unlinked platforms. ');
1892
1893 -- unlink categories
1894 FOR cur_row IN category_ids_cur(x_set_id) LOOP
1895 CS_KB_SOLN_CATEGORIES_PVT.removeSolutionFromCategory(
1896 p_api_version => 1.0,
1897 x_return_status => x_return_status,
1898 x_msg_count => x_msg_count,
1899 x_msg_data => x_msg_data,
1900 p_solution_id => x_set_id,
1901 p_category_id => cur_row.category_id
1902 );
1903 END LOOP;
1904 -- dbms_output.put_line('unlinked categories. ');
1905
1906 -- link set with statements, products, categories, platforms, etc.
1907 -- also create new statements if info provided
1908 l_return_val := Link_Soln_Attributes(
1909 p_validate_type => 'UPDATE',
1910 p_set_id => x_set_id,
1911 p_given_element_ids => x_ele_ids_upd_tbl,
1912 p_given_ele_nums => p_ele_nos_upd_tbl,
1913 p_given_ele_type_ids => l_ele_type_id_upd_tbl,
1914 p_given_ele_dist_ids => l_ele_dist_id_upd_tbl,
1915 p_given_ele_content_types => l_ele_conttype_codes_upd_tbl,
1916 p_given_ele_summaryies => p_ele_summary_upd_tbl,
1917 p_element_ids => l_element_ids,
1918 p_element_type_ids => l_element_type_ids,
1919 p_element_dist_ids => l_element_dist_ids,
1920 p_element_content_types => l_element_content_type_codes,
1921 p_element_summaries => p_ele_summary_tbl,
1922 p_element_dummy_detail => l_temp_clob,
1923 p_set_product_ids => l_set_product_ids,
1924 p_set_platform_ids => l_set_platform_ids,
1925 p_set_category_ids => l_set_category_ids,
1926 x_created_element_ids => x_created_ele_ids_tbl,
1927 x_return_status => x_return_status,
1928 x_msg_count => x_msg_count,
1929 x_msg_data => x_msg_data);
1930 -- dbms_output.put_line('linked everything. ');
1931 -- dbms_output.put_line('return from LINK='||l_return_val);
1932
1933 if( Get_Missing_Ele_Type( x_set_id ) = 'Y' ) then
1934 raise MANDATORY_STATEMENT_MISSING;
1935 end if;
1936
1937 -- Repopulate the Solution Content cache for this solution
1938 CS_KB_SYNC_INDEX_PKG.Populate_Soln_Content_Cache (x_set_id);
1939 CS_KB_SYNC_INDEX_PKG.Pop_Soln_Attach_Content_Cache (x_set_id); --12.1.3
1940
1941 -- Mark the Solution Version for indexing
1942 CS_KB_SYNC_INDEX_PKG.Mark_Idxs_on_Pub_Soln( p_set_number );
1943
1944 -- dbms_output.put_line('Update Set with Validation - END');
1945 RETURN OKAY_STATUS;
1946
1947 EXCEPTION
1948 WHEN VALIDATION_ERROR THEN
1949 RETURN ERROR_STATUS;
1950
1951 WHEN MANDATORY_STATEMENT_MISSING THEN
1952 ROLLBACK TO Update_Set;
1953 x_return_status := FND_API.G_RET_STS_ERROR;
1954 x_msg_count := 1;
1955 x_msg_data := 'Mandatory Statements Missing.';
1956
1957 RETURN ERROR_STATUS;
1958
1959 WHEN INVALID_SET_NUMBER THEN
1960 ROLLBACK TO Update_Set;
1961 x_return_status := FND_API.G_RET_STS_ERROR;
1962 x_msg_count := 1;
1963 x_msg_data := 'Invalid Solution Number: ' || p_set_number || '. Solutions to be updated should be valid and PUBLISHED.' ;
1964
1965 RETURN ERROR_STATUS;
1966
1967 WHEN INVALID_SET_TYPE_NAME THEN
1968 ROLLBACK TO Update_Set;
1969 x_return_status := FND_API.G_RET_STS_ERROR;
1970 x_msg_count := 1;
1971 x_msg_data := 'Invalid Set Type Name : ' || p_set_type_name;
1972
1973 RETURN ERROR_STATUS;
1974
1975 WHEN DUPLICATE_SET_NAME THEN
1976 ROLLBACK TO Update_Set;
1977 x_return_status := FND_API.G_RET_STS_ERROR;
1978 x_msg_count := 1;
1979 x_msg_data := 'Duplicate Solution Name:' || l_validate_buf;
1980
1981 RETURN ERROR_STATUS;
1982
1983 WHEN FND_API.G_EXC_ERROR THEN
1984 ROLLBACK TO Create_Set;
1985 x_return_status := FND_API.G_RET_STS_ERROR ;
1986 FND_MSG_PUB.Count_And_Get(
1987 p_encoded => FND_API.G_FALSE,
1988 p_count => x_msg_count,
1989 p_data => x_msg_data);
1990 RETURN ERROR_STATUS;
1991
1992 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1993 ROLLBACK TO Update_Set;
1994 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1995 FND_MSG_PUB.Count_And_Get(
1996 p_encoded => FND_API.G_FALSE,
1997 p_count => x_msg_count,
1998 p_data => x_msg_data);
1999 RETURN ERROR_STATUS;
2000
2001 WHEN OTHERS THEN
2002 ROLLBACK TO Update_Set;
2003 x_msg_data := 'Update Solution: ' || SQLERRM ;
2004 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2005 x_msg_count := 0;
2006 RETURN ERROR_STATUS;
2007
2008 END Update_Set_With_validation_3;
2009
2010 /*
2011 This is the same as the original validate_soln_attributes, except it takes a delimiter param
2012 */
2013 FUNCTION VALIDATE_SOLN_ATTRIBUTES_3
2014 (
2015 p_set_type_id IN NUMBER,
2016 p_visibility_name IN VARCHAR2,
2017 p_product_names IN JTF_VARCHAR2_TABLE_2000,
2018 p_product_segments IN JTF_VARCHAR2_TABLE_2000,
2019 p_platform_names IN JTF_VARCHAR2_TABLE_2000,
2020 p_platform_segments IN JTF_VARCHAR2_TABLE_2000,
2021 p_category_names IN JTF_VARCHAR2_TABLE_2000,
2022 p_category_last_names IN JTF_VARCHAR2_TABLE_2000,
2023 p_ele_nums IN JTF_VARCHAR2_TABLE_2000,
2024 p_ele_upd_nums IN JTF_VARCHAR2_TABLE_2000,
2025 p_ele_upd_content_types IN JTF_VARCHAR2_TABLE_2000,
2026 p_ele_upd_dist_names IN JTF_VARCHAR2_TABLE_2000,
2027 p_element_type_names IN JTF_VARCHAR2_TABLE_2000,
2028 p_ele_content_types IN JTF_VARCHAR2_TABLE_2000,
2029 p_ele_dist_names IN JTF_VARCHAR2_TABLE_2000,
2030 x_visibility_id OUT NOCOPY NUMBER,
2031 x_product_numbers OUT NOCOPY JTF_NUMBER_TABLE,
2032 x_platform_numbers OUT NOCOPY JTF_NUMBER_TABLE,
2033 x_category_numbers OUT NOCOPY JTF_NUMBER_TABLE,
2034 x_ele_ids OUT NOCOPY JTF_NUMBER_TABLE,
2035 x_ele_upd_ids OUT NOCOPY JTF_NUMBER_TABLE,
2036 x_element_type_ids OUT NOCOPY JTF_NUMBER_TABLE,
2037 x_ele_dist_ids OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
2038 x_ele_content_type_codes OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
2039 x_ele_upd_type_ids OUT NOCOPY JTF_NUMBER_TABLE,
2040 x_ele_upd_dist_ids OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
2041 x_ele_upd_content_type_codes OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
2042 x_return_status OUT NOCOPY varchar2,
2043 x_msg_count OUT NOCOPY number,
2044 x_msg_data OUT NOCOPY varchar2,
2045 p_delim IN VARCHAR2
2046
2047 ) RETURN NUMBER IS
2048 l_validate_buf VARCHAR2(1000);
2049 l_return_val NUMBER;
2050 l_return_status VARCHAR2(1);
2051 l_dup_found VARCHAR2(1);
2052 l_product_numbers JTF_NUMBER_TABLE;
2053 l_platform_numbers JTF_NUMBER_TABLE;
2054 BEGIN
2055
2056 x_element_type_ids := JTF_NUMBER_TABLE();
2057 x_product_numbers := JTF_NUMBER_TABLE();
2058 l_product_numbers := JTF_NUMBER_TABLE();
2059 x_platform_numbers := JTF_NUMBER_TABLE();
2060 l_platform_numbers := JTF_NUMBER_TABLE();
2061 x_category_numbers := JTF_NUMBER_TABLE();
2062 x_ele_ids := JTF_NUMBER_TABLE();
2063 x_ele_upd_ids := JTF_NUMBER_TABLE();
2064 x_element_type_ids := JTF_NUMBER_TABLE();
2065 x_ele_dist_ids := JTF_VARCHAR2_TABLE_2000();
2066 x_ele_content_type_codes := JTF_VARCHAR2_TABLE_2000();
2067 x_ele_upd_type_ids := JTF_NUMBER_TABLE();
2068 x_ele_upd_dist_ids := JTF_VARCHAR2_TABLE_2000();
2069 x_ele_upd_content_type_codes := JTF_VARCHAR2_TABLE_2000();
2070
2071 -- Validate Solution Visibility Level Name, get ID
2072 l_validate_buf := p_visibility_name;
2073 l_return_status := Validate_Visibility_Level( p_visibility_name,
2074 x_visibility_id );
2075 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2076 RAISE INVALID_VISIBILITY_LEVEL;
2077 END IF;
2078
2079
2080 -- Validate Set Product Names
2081
2082 l_product_numbers.extend(p_product_segments.count);
2083 for i in 1..p_product_segments.count loop
2084 if(p_product_segments(i) is not null) then
2085 l_validate_buf := p_product_segments(i);
2086 l_return_val := Validate_Product_Segment(p_product_segments(i),
2087 l_product_numbers(i));
2088 IF (l_return_val = ERROR_STATUS)
2089 THEN RAISE INVALID_PRODUCT_SEGMENT;
2090 END IF;
2091 else
2092 l_validate_buf := p_product_names(i);
2093 l_return_val := Validate_Product_Name(p_product_names(i),
2094 l_product_numbers(i));
2095 IF (l_return_val = ERROR_STATUS)
2096 THEN RAISE INVALID_PRODUCT_NAME;
2097 END IF;
2098 end if;
2099 end loop;
2100
2101 -- Filter duplicatoin
2102 for i in 1..l_product_numbers.count loop
2103 l_dup_found := 'N';
2104 for j in 1..x_product_numbers.count loop
2105 if l_product_numbers(i) = x_product_numbers(j) then
2106 l_dup_found := 'Y';
2107 exit;
2108 end if;
2109 end loop;
2110 if l_dup_found = 'N' then
2111 x_product_numbers.extend;
2112 x_product_numbers(x_product_numbers.count) := l_product_numbers(i);
2113 end if;
2114 end loop;
2115
2116 -- Validate Set Platform Names
2117 l_platform_numbers.extend(p_platform_segments.count);
2118 for i in 1..p_platform_segments.count loop
2119 if (p_platform_segments(i) is not null) then
2120 l_validate_buf := p_platform_segments(i);
2121 l_return_val := Validate_Platform_Segment(p_platform_segments(i),
2122 l_platform_numbers(i));
2123 IF (l_return_val = ERROR_STATUS)
2124 THEN RAISE INVALID_PLATFORM_SEGMENT;
2125 END IF;
2126 else
2127 l_validate_buf := p_platform_names(i);
2128 l_return_val := Validate_Platform_Name(p_platform_names(i),
2129 l_platform_numbers(i));
2130 IF (l_return_val = ERROR_STATUS)
2131 THEN RAISE INVALID_PLATFORM_NAME;
2132 END IF;
2133 end if;
2134 end loop;
2135
2136 -- Filter duplicatoin
2137 for i in 1..l_platform_numbers.count loop
2138 l_dup_found := 'N';
2139 for j in 1..x_platform_numbers.count loop
2140 if l_platform_numbers(i) = x_platform_numbers(j) then
2141 l_dup_found := 'Y';
2142 exit;
2143 end if;
2144 end loop;
2145 if l_dup_found = 'N' then
2146 x_platform_numbers.extend;
2147 x_platform_numbers(x_platform_numbers.count) := l_platform_numbers(i);
2148 end if;
2149 end loop;
2150
2151 -- Validate that there is at least one category
2152 if( p_category_names is null or p_category_names.count = 0)then
2153 RAISE MANDATORY_CATEGORY_MISSING;
2154 end if;
2155
2156 -- Validate Set Category Names
2157 x_category_numbers.extend(p_category_names.count);
2158 for i in 1..p_category_names.count loop
2159 l_validate_buf := p_category_names(i);
2160 l_return_val := Validate_Category_name_2(p_category_names(i),
2161 p_category_last_names(i),
2162 x_category_numbers(i),
2163 p_delim);
2164 IF (l_return_val = ERROR_STATUS)
2165 THEN RAISE INVALID_CATEGORY_NAME;
2166 END IF;
2167 end loop;
2168
2169 -- Validate Element no.
2170 x_ele_ids.extend(p_ele_nums.count);
2171 for i in 1..p_ele_nums.count loop
2172 l_validate_buf := p_ele_nums(i);
2173 l_return_val := Validate_Element_No(p_ele_nums(i),
2174 x_ele_ids(i));
2175 IF (l_return_val = ERROR_STATUS) THEN
2176 RAISE INVALID_ELEMENT_NUMBER;
2177 END IF;
2178 end loop;
2179
2180 -- Validate Element no. for global update
2181 x_ele_upd_ids.extend(p_ele_upd_nums.count);
2182 for i in 1..p_ele_upd_nums.count loop
2183
2184 l_validate_buf := p_ele_upd_nums(i);
2185 l_return_val := Validate_Element_No(p_ele_upd_nums(i),
2186 x_ele_upd_ids(i));
2187 IF (l_return_val = ERROR_STATUS)
2188 THEN RAISE INVALID_ELEMENT_NUMBER;
2189 END IF;
2190 end loop;
2191
2192 -- Validate each Element Type Name
2193 x_element_type_ids.extend(p_element_type_names.count);
2194 for i in 1..p_element_type_names.count loop
2195 l_validate_buf := p_element_type_names(i);
2196 l_return_val := Validate_Element_Type_Name(p_element_type_names(i),
2197 x_element_type_ids(i));
2198 IF (l_return_val = ERROR_STATUS)
2199 THEN RAISE INVALID_ELEMENT_TYPE_NAME;
2200 END IF;
2201 end loop;
2202
2203 -- Resolve each Element Type Name/ID for global update
2204 x_ele_upd_type_ids.extend(p_ele_upd_nums.count);
2205 for i in 1..p_ele_upd_nums.count loop
2206 l_validate_buf := p_ele_upd_nums(i);
2207 l_return_val := Resolve_Element_Type_ID(p_ele_upd_nums(i),
2208 x_ele_upd_type_ids(i));
2209 IF (l_return_val = ERROR_STATUS)
2210 THEN RAISE INVALID_ELEMENT_TYPE;
2211 END IF;
2212 end loop;
2213
2214 -- Validate Set Element Type Mapping
2215 l_return_val := Validate_Set_Element_Type_Ids(p_set_type_id,
2216 x_element_type_ids);
2217 IF (l_return_val = ERROR_STATUS)
2218 THEN RAISE INVALID_SET_ELEMENT_TYPE_MAP;
2219 END IF;
2220
2221 -- Validate Set Element Type Mapping for global update
2222 l_return_val := Validate_Set_Element_Type_Ids(p_set_type_id,
2223 x_ele_upd_type_ids);
2224 IF (l_return_val = ERROR_STATUS)
2225 THEN RAISE INVALID_SET_ELEMENT_TYPE_MAP;
2226 END IF;
2227
2228 -- Valdiate Each Element Content Type Name
2229 x_ele_content_type_codes.extend(p_ele_content_types.count);
2230 for i in 1..p_ele_content_types.count loop
2231 l_validate_buf := p_ele_content_types(i);
2232 l_return_val :=
2233 Validate_Element_Content_Type(p_ele_content_types(i),
2234 x_ele_content_type_codes(i));
2235 IF (l_return_val = ERROR_STATUS)
2236 THEN RAISE INVALID_ELEMENT_CONTENT_TYPE;
2237 END IF;
2238 end loop;
2239
2240 -- Valdiate Each Element Content Type Name for global update
2241 x_ele_upd_content_type_codes.extend(p_ele_upd_content_types.count);
2242 for i in 1..p_ele_upd_content_types.count loop
2243 l_validate_buf := p_ele_upd_content_types(i);
2244 l_return_val :=
2245 Validate_Element_Content_Type(p_ele_upd_content_types(i),
2246 x_ele_upd_content_type_codes(i));
2247 IF (l_return_val = ERROR_STATUS)
2248 THEN RAISE INVALID_ELEMENT_CONTENT_TYPE;
2249 END IF;
2250 end loop;
2251
2252 -- Validate Each Element distribution Name
2253 x_ele_dist_ids.extend(p_ele_dist_names.count);
2254 for i in 1..p_ele_dist_names.count loop
2255 l_validate_buf := p_ele_dist_names(i);
2256 l_return_val := Validate_Access_Level(p_ele_dist_names(i),
2257 x_ele_dist_ids(i));
2258
2259 IF (l_return_val = ERROR_STATUS) THEN
2260 RAISE INVALID_ACCESS_LEVEL;
2261 END IF;
2262
2263 end loop;
2264
2265 -- Validate Each Element distribution Name for global update
2266 x_ele_upd_dist_ids.extend(p_ele_upd_dist_names.count);
2267 for i in 1..p_ele_upd_dist_names.count loop
2268 l_validate_buf := p_ele_upd_dist_names(i);
2269 l_return_val := Validate_Access_Level(p_ele_upd_dist_names(i),
2270 x_ele_upd_dist_ids(i));
2271 IF (l_return_val = ERROR_STATUS) THEN
2272 RAISE INVALID_ACCESS_LEVEL;
2273 END IF;
2274
2275 end loop;
2276
2277 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2278 return OKAY_STATUS;
2279 EXCEPTION
2280 WHEN INVALID_VISIBILITY_LEVEL THEN
2281 x_return_status := FND_API.G_RET_STS_ERROR;
2282 x_msg_count := 1;
2283 x_msg_data := ' Invalid Visibility Name: ' || l_validate_buf;
2284
2285 RETURN ERROR_STATUS;
2286
2287 WHEN INVALID_ACCESS_LEVEL THEN
2288 x_return_status := FND_API.G_RET_STS_ERROR;
2289 x_msg_count := 1;
2290 x_msg_data := ' Invalid Distribution Name: ' || l_validate_buf;
2291
2292 RETURN ERROR_STATUS;
2293
2294 WHEN INVALID_ELEMENT_TYPE_NAME THEN
2295 x_return_status := FND_API.G_RET_STS_ERROR;
2296 x_msg_count := 1;
2297 x_msg_data := 'Invalid Element Type Name: ' || l_validate_buf;
2298
2299 RETURN ERROR_STATUS;
2300
2301 WHEN INVALID_ELEMENT_TYPE THEN
2302 x_return_status := FND_API.G_RET_STS_ERROR;
2303 x_msg_count := 1;
2304 x_msg_data := 'Invalid Element Type ID for Element Number: ' || l_validate_buf;
2305
2306 RETURN ERROR_STATUS;
2307
2308 WHEN INVALID_SET_ELEMENT_TYPE_MAP THEN
2309 x_return_status := FND_API.G_RET_STS_ERROR;
2310 x_msg_count := 1;
2311 x_msg_data := 'Invalid Set Element Type Mapping: ' || l_validate_buf;
2312
2313 RETURN ERROR_STATUS;
2314
2315 WHEN INVALID_ELEMENT_CONTENT_TYPE THEN
2316 x_return_status := FND_API.G_RET_STS_ERROR;
2317 x_msg_count := 1;
2318 x_msg_data := 'Invalid Element Content Type: ' || l_validate_buf;
2319
2320 RETURN ERROR_STATUS;
2321
2322 WHEN INVALID_ELEMENT_NUMBER THEN
2323 x_return_status := FND_API.G_RET_STS_ERROR;
2324 x_msg_count := 1;
2325 x_msg_data := 'This Statement No is invalid or in DRAFT status: ' || l_validate_buf;
2326
2327 RETURN ERROR_STATUS;
2328
2329 WHEN INVALID_PRODUCT_NAME THEN
2330 x_return_status := FND_API.G_RET_STS_ERROR;
2331 x_msg_count := 1;
2332 x_msg_data := 'Product Name is invalid: ' || l_validate_buf;
2333
2334 RETURN ERROR_STATUS;
2335
2336 WHEN INVALID_PRODUCT_SEGMENT THEN
2337 x_return_status := FND_API.G_RET_STS_ERROR;
2338 x_msg_count := 1;
2339 x_msg_data := 'Product Segment is invalid: ' || l_validate_buf;
2340
2341 RETURN ERROR_STATUS;
2342
2343 WHEN INVALID_PLATFORM_NAME THEN
2344 x_return_status := FND_API.G_RET_STS_ERROR;
2345 x_msg_count := 1;
2346 x_msg_data := 'Platform Name is Invalid: ' || l_validate_buf;
2347
2348 RETURN ERROR_STATUS;
2349
2350 WHEN INVALID_PLATFORM_SEGMENT THEN
2351 x_return_status := FND_API.G_RET_STS_ERROR;
2352 x_msg_count := 1;
2353 x_msg_data := 'Platform Segment is Invalid: ' || l_validate_buf;
2354
2355 RETURN ERROR_STATUS;
2356
2357 WHEN MANDATORY_CATEGORY_MISSING THEN
2358 x_return_status := FND_API.G_RET_STS_ERROR;
2359 x_msg_count := 1;
2360 x_msg_data := 'Mandatory category missing. Solution must belong to at least one category.';
2361
2362 RETURN ERROR_STATUS;
2363
2364 WHEN INVALID_CATEGORY_NAME THEN
2365 x_return_status := FND_API.G_RET_STS_ERROR;
2366 x_msg_count := 1;
2367 x_msg_data := 'Category Name is Invalid:' || l_validate_buf;
2368
2369 RETURN ERROR_STATUS;
2370
2371 WHEN OTHERS THEN
2372 x_msg_data := 'ERROR in validating solution attributes: ' || SQLERRM ;
2373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2374 x_msg_count := 0;
2375 RETURN ERROR_STATUS;
2376
2377 END VALIDATE_SOLN_ATTRIBUTES_3;
2378
2379
2380 FUNCTION Link_Soln_Attributes
2381 (
2382 p_validate_type IN VARCHAR2,
2383 p_set_id IN NUMBER,
2384 p_given_element_ids IN JTF_NUMBER_TABLE,
2385 p_given_ele_nums in JTF_VARCHAR2_TABLE_2000,
2386 p_given_ele_type_ids in JTF_NUMBER_TABLE,
2387 p_given_ele_dist_ids in JTF_VARCHAR2_TABLE_2000,
2388 p_given_ele_content_types in JTF_VARCHAR2_TABLE_2000,
2389 p_given_ele_summaryies in JTF_VARCHAR2_TABLE_2000,
2390 p_element_ids IN JTF_NUMBER_TABLE,
2391 p_element_type_ids IN JTF_NUMBER_TABLE,
2392 p_element_dist_ids IN JTF_VARCHAR2_TABLE_2000,
2393 p_element_content_types IN JTF_VARCHAR2_TABLE_2000,
2394 p_element_summaries IN JTF_VARCHAR2_TABLE_2000,
2395 p_element_dummy_detail IN CLOB,
2396 p_set_product_ids IN JTF_NUMBER_TABLE,
2397 p_set_platform_ids IN JTF_NUMBER_TABLE,
2398 p_set_category_ids IN JTF_NUMBER_TABLE,
2399 x_created_element_ids OUT NOCOPY JTF_NUMBER_TABLE,
2400 x_return_status OUT NOCOPY varchar2,
2401 x_msg_count OUT NOCOPY number,
2402 x_msg_data OUT NOCOPY varchar2
2403 ) RETURN NUMBER IS
2404 l_temp_element_no VARCHAR2(30);
2405 l_set_product_org_ids JTF_NUMBER_TABLE;
2406 l_set_platform_org_ids JTF_NUMBER_TABLE;
2407 l_temp_category_link_id number;
2408 l_temp_update_return number;
2409 l_elmt_status VARCHAR2(30);
2410
2411 l_validate_buf VARCHAR2(1000);
2412 l_return_val NUMBER;
2413 BEGIN
2414
2415 l_set_product_org_ids := JTF_NUMBER_TABLE();
2416 l_set_platform_org_ids := JTF_NUMBER_TABLE();
2417
2418 IF (p_validate_type = 'UPDATE') THEN
2419 l_elmt_status := 'PUBLISHED';
2420 ELSE
2421 l_elmt_status := null;
2422 END IF;
2423
2424
2425 -- Creating new elements
2426 x_created_element_ids := JTF_NUMBER_TABLE();
2427 x_created_element_ids.extend(p_element_type_ids.count);
2428 for i in 1..p_element_type_ids.count loop
2429 x_created_element_ids(i) := CS_KB_ELEMENTS_AUDIT_PKG.Create_Element_CLOB(
2430 p_element_type_id => p_element_type_ids(i),
2431 p_name => p_element_summaries(i),
2432 p_desc => p_element_dummy_detail,
2433 p_status => l_elmt_status,
2434 p_access_level => p_element_dist_ids(i),
2435 p_content_type => p_element_content_types(i));
2436
2437 IF (x_created_element_ids(i) = -3) then
2438 l_validate_buf := p_element_summaries(i);
2439 raise DUPLICATE_ELEMENT_NAME;
2440 -- should should get existing ID and link
2441 ELSIF (not x_created_element_ids(i) > 0) then
2442 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2443 end if;
2444 end loop;
2445
2446 -- link The Created element ids to set id;
2447 for i in 1..x_created_element_ids.count loop
2448 l_temp_element_no := cs_kb_elements_audit_pkg.Get_Element_Number(
2449 x_created_element_ids(i));
2450
2451 l_return_val := cs_knowledge_audit_pvt.Add_Element_To_Set(
2452 p_element_number => l_temp_element_no,
2453 p_set_id => p_set_id);
2454
2455 if (l_return_val = ERROR_STATUS) THEN
2456 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2457 end if;
2458 end loop;
2459
2460 -- link The provided element ids to set id;
2461 for i in 1..p_element_ids.count loop
2462 l_temp_element_no := cs_kb_elements_audit_pkg.Get_Element_Number(
2463 p_element_ids(i));
2464 l_return_val := cs_knowledge_audit_pvt.Add_Element_To_Set(
2465 p_element_number => l_temp_element_no,
2466 p_set_id => p_set_id);
2467
2468 if (l_return_val = ERROR_STATUS) THEN
2469 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2470 end if;
2471 end loop;
2472
2473 -- global updating given elements
2474 for i in 1..p_given_ele_type_ids.count loop
2475 l_temp_update_return := CS_KB_ELEMENTS_AUDIT_PKG.Update_Element_CLOB(
2476 p_element_id => p_given_element_ids(i),
2477 p_element_number => p_given_ele_nums(i),
2478 p_element_type_id => p_given_ele_type_ids(i),
2479 p_name => p_given_ele_summaryies(i),
2480 p_desc => p_element_dummy_detail,
2481 p_access_level => p_given_ele_dist_ids(i),
2482 p_content_type => p_given_ele_content_types(i),
2483 p_status => 'PUBLISHED');
2484
2485 IF (l_temp_update_return < 0) then
2486 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2487 end if;
2488
2489 -- Mark the text indexes for the updated statement
2490 cs_kb_sync_index_pkg.MARK_IDXS_ON_GLOBAL_STMT_UPD(p_given_element_ids(i));
2491 end loop;
2492
2493 -- link the updated given element nos to set id
2494
2495 for i in 1..p_given_ele_nums.count loop
2496 l_validate_buf := p_given_ele_nums(i);
2497 l_return_val := cs_knowledge_audit_pvt.Add_Element_To_Set(
2498 p_element_number => p_given_ele_nums(i),
2499 p_set_id => p_set_id);
2500 if (l_return_val = ERROR_STATUS) THEN
2501 raise SET_ELEMENT_LINK_ERROR;
2502 end if;
2503 end loop;
2504
2505 -- Populate org id table
2506 l_set_product_org_ids.extend(p_set_product_ids.count);
2507 for i in 1..l_set_product_org_ids.count loop
2508 l_set_product_org_ids(i) := cs_std.get_item_valdn_orgzn_id;
2509 end loop;
2510
2511 -- link the set to products
2512 cs_kb_assoc_pkg.add_link(p_item_id => p_set_product_ids,
2513 p_org_id => l_set_product_org_ids,
2514 p_set_id => p_set_id,
2515 p_link_type => 1,
2516 p_task => 1,
2517 p_result => l_return_val);
2518
2519 if (l_return_val = 0) THEN
2520 raise PRODUCT_LINK_ERROR;
2521 end if;
2522
2523 -- Populate org id table
2524 l_set_platform_org_ids.extend(p_set_platform_ids.count);
2525
2526 for i in 1..l_set_platform_org_ids.count loop
2527 l_set_platform_org_ids(i) := cs_std.get_item_valdn_orgzn_id;
2528 end loop;
2529
2530 -- link the set to platforms
2531 cs_kb_assoc_pkg.add_link(p_item_id => p_set_platform_ids,
2532 p_org_id => l_set_platform_org_ids,
2533 p_set_id => p_set_id,
2534 p_link_type => 0,
2535 p_task => 1,
2536 p_result => l_return_val);
2537
2538 if (l_return_val = 0) THEN
2539 raise PLATFORM_LINK_ERROR;
2540 end if;
2541
2542 -- link the set to categories
2543 for i in 1..p_set_category_ids.count loop
2544 CS_KB_SOLN_CATEGORIES_PVT.addSolutionToCategory(
2545 p_api_version => 1.0,
2546 x_return_status => x_return_status,
2547 x_msg_count => x_msg_count,
2548 x_msg_data => x_msg_data,
2549 p_solution_id => p_set_id,
2550 p_category_id => p_set_category_ids(i),
2551 x_soln_category_link_id => l_temp_category_link_id
2552 );
2553
2554 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2555 raise CATEGORY_LINK_ERROR;
2556 end if;
2557 end loop;
2558
2559 return OKAY_STATUS;
2560
2561 EXCEPTION
2562 WHEN PRODUCT_LINK_ERROR THEN
2563 IF (p_validate_type = 'CREATE') THEN
2564 ROLLBACK TO Create_Set;
2565 ELSIF (p_validate_type = 'UPDATE') THEN
2566 ROLLBACK TO Update_Set;
2567 END IF;
2568 x_return_status := FND_API.G_RET_STS_ERROR;
2569 x_msg_count := 1;
2570 x_msg_data := 'Unable to link to Product' ;
2571
2572 RETURN ERROR_STATUS;
2573
2574 WHEN PLATFORM_LINK_ERROR THEN
2575 IF (p_validate_type = 'CREATE') THEN
2576 ROLLBACK TO Create_Set;
2577 ELSIF (p_validate_type = 'UPDATE') THEN
2578 ROLLBACK TO Update_Set;
2579 END IF;
2580
2581 x_return_status := FND_API.G_RET_STS_ERROR;
2582 x_msg_count := 1;
2583 x_msg_data := 'Unable to link to Platform' ;
2584
2585 RETURN ERROR_STATUS;
2586
2587 WHEN CATEGORY_LINK_ERROR THEN
2588 IF (p_validate_type = 'CREATE') THEN
2589 ROLLBACK TO Create_Set;
2590 ELSIF (p_validate_type = 'UPDATE') THEN
2591 ROLLBACK TO Update_Set;
2592 END IF;
2593
2594 x_return_status := FND_API.G_RET_STS_ERROR;
2595 x_msg_count := 1;
2596 x_msg_data := 'Unable to link to Category' ;
2597 RETURN ERROR_STATUS;
2598
2599 WHEN SET_ELEMENT_LINK_ERROR THEN
2600 IF (p_validate_type = 'CREATE') THEN
2601 ROLLBACK TO Create_Set;
2602 ELSIF (p_validate_type = 'UPDATE') THEN
2603 ROLLBACK TO Update_Set;
2604 END IF;
2605
2606 x_return_status := FND_API.G_RET_STS_ERROR;
2607 x_msg_count := 1;
2608 x_msg_data := 'Error while linking to statement:' || l_validate_buf ;
2609 RETURN ERROR_STATUS;
2610
2611 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2612 IF (p_validate_type = 'CREATE') THEN
2613 ROLLBACK TO Create_Set;
2614 ELSIF (p_validate_type = 'UPDATE') THEN
2615 ROLLBACK TO Update_Set;
2616 END IF;
2617
2618 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2619 FND_MSG_PUB.Count_And_Get(
2620 p_count => x_msg_count,
2621 p_data => x_msg_data);
2622 RETURN ERROR_STATUS;
2623
2624 WHEN OTHERS THEN
2625 IF (p_validate_type = 'CREATE') THEN
2626 ROLLBACK TO Create_Set;
2627 ELSIF (p_validate_type = 'UPDATE') THEN
2628 ROLLBACK TO Update_Set;
2629 END IF;
2630
2631 x_msg_data := 'Error at Create Links: ' || SQLERRM ;
2632 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2633 x_msg_count := 1;
2634 RETURN ERROR_STATUS;
2635
2636 END Link_Soln_Attributes;
2637
2638
2639 FUNCTION Validate_Set_Type_Name_Create
2640 (
2641 p_set_type_name IN VARCHAR2,
2642 x_set_type_id OUT NOCOPY NUMBER
2643 ) RETURN NUMBER IS
2644 BEGIN
2645 select set_type_id
2646 into x_set_type_id from cs_kb_set_types_tl
2647 where language = userenv('LANG')
2648 and upper(name) = upper(p_set_type_name);
2649 RETURN OKAY_STATUS;
2650 EXCEPTION
2651 WHEN NO_DATA_FOUND THEN
2652 RETURN ERROR_STATUS;
2653 WHEN OTHERS THEN
2654 RETURN ERROR_STATUS;
2655
2656 END Validate_Set_Type_Name_Create;
2657
2658 FUNCTION Validate_Set_Type_Name_Update
2659 (
2660 p_set_id IN NUMBER,
2661 p_set_type_name IN VARCHAR2,
2662 x_set_type_id OUT NOCOPY NUMBER
2663 ) RETURN NUMBER IS
2664
2665 BEGIN
2666 select sets.set_type_id
2667 into x_set_type_id
2668 from cs_kb_set_types_tl type,
2669 cs_kb_sets_b sets
2670 where type.language = userenv('LANG')
2671 and upper(type.name) = upper(p_set_type_name)
2672 and sets.set_type_id = type.set_type_id
2673 and sets.set_id = p_set_id;
2674
2675 RETURN OKAY_STATUS;
2676 EXCEPTION
2677 WHEN NO_DATA_FOUND THEN
2678 RETURN ERROR_STATUS;
2679 WHEN OTHERS THEN
2680 RETURN ERROR_STATUS;
2681
2682 END Validate_Set_Type_Name_Update;
2683
2684 -- BugFix 3993200 - Sequence id fix
2685 -- Moved Query to Check_Flow Cursor
2686 -- removed max
2687 FUNCTION Validate_Flow
2688 (
2689 p_flow_name IN VARCHAR2,
2690 p_flow_step IN VARCHAR2,
2691 x_flow_details_id OUT NOCOPY NUMBER
2692 ) RETURN NUMBER IS
2693
2694 Cursor Check_Flow IS
2695 select flow_details_id
2696 from cs_kb_wf_flows_tl flow,
2697 cs_kb_wf_flow_details detail,
2698 cs_lookups lookup
2699 where flow.name = p_flow_name
2700 and flow.language = userenv('LANG')
2701 and flow.flow_id = detail.flow_id
2702 and detail.action = 'PUB'
2703 and detail.step = lookup.lookup_code
2704 and NVL(detail.END_DATE, sysdate) >= sysdate
2705 and lookup.lookup_type = 'CS_KB_STATUS'
2706 and lookup.meaning = p_flow_step;
2707
2708 BEGIN
2709 OPEN Check_Flow;
2710 FETCH Check_Flow INTO x_flow_details_id;
2711 CLOSE Check_Flow;
2712
2713 IF (x_flow_details_id IS NULL) THEN
2714 RETURN ERROR_STATUS;
2715 ELSE
2716 RETURN OKAY_STATUS;
2717 END IF;
2718 EXCEPTION
2719 WHEN OTHERS THEN
2720 RETURN ERROR_STATUS;
2721 END Validate_Flow;
2722
2723 -- BugFix 3993200 - Sequence id fix
2724 -- removed select max subquery and replaced with lvf
2725 -- exist and in PUB status
2726 FUNCTION Validate_Set_Number
2727 (
2728 p_set_number IN varchar2,
2729 x_set_id OUT NOCOPY NUMBER
2730 ) RETURN NUMBER IS
2731 BEGIN
2732 select set_id
2733 into x_set_id
2734 from cs_kb_sets_b a
2735 where a.set_number = p_set_number
2736 and a.status = 'PUB'
2737 and a.latest_version_flag = 'Y';
2738
2739 RETURN OKAY_STATUS;
2740 EXCEPTION
2741 WHEN NO_DATA_FOUND THEN
2742 RETURN ERROR_STATUS;
2743 WHEN OTHERS THEN
2744 RETURN ERROR_STATUS;
2745 END Validate_Set_Number;
2746
2747 FUNCTION Validate_Element_Type_Name
2748 (
2749 p_Element_type_name IN VARCHAR2,
2750 x_element_type_id OUT NOCOPY NUMBER
2751 ) RETURN NUMBER IS
2752
2753 BEGIN
2754
2755 select element_type_id
2756 into x_element_type_id from cs_kb_element_types_tl
2757 where language = userenv('LANG')
2758 and upper(name) = upper(p_element_type_name);
2759
2760 RETURN OKAY_STATUS;
2761
2762 EXCEPTION
2763
2764 WHEN NO_DATA_FOUND THEN
2765 RETURN ERROR_STATUS;
2766 WHEN OTHERS THEN
2767 RETURN ERROR_STATUS;
2768
2769 END Validate_Element_Type_Name;
2770
2771 FUNCTION Resolve_Element_Type_ID
2772 (
2773 p_Element_number IN VARCHAR2,
2774 x_element_type_id OUT NOCOPY NUMBER
2775 ) RETURN NUMBER IS
2776
2777 BEGIN
2778
2779 select element_type_id
2780 into x_element_type_id
2781 from cs_kb_elements_b
2782 where element_number = p_element_number;
2783
2784 RETURN OKAY_STATUS;
2785
2786 EXCEPTION
2787
2788 WHEN NO_DATA_FOUND THEN
2789 RETURN ERROR_STATUS;
2790 WHEN OTHERS THEN
2791 RETURN ERROR_STATUS;
2792
2793 END Resolve_Element_Type_ID;
2794
2795 FUNCTION Validate_Set_Element_Type_Ids
2796 (
2797 p_set_type_id IN NUMBER,
2798 p_element_type_ids IN JTF_NUMBER_TABLE
2799
2800 ) RETURN NUMBER IS
2801
2802 Type element_type_id_tab_type is
2803 TABLE OF CS_KB_ELEMENT_TYPES_B.ELEMENT_TYPE_ID%TYPE INDEX BY BINARY_INTEGER;
2804
2805 l_element_type_ids element_type_id_tab_type;
2806
2807 l_count NUMBER(15);
2808 l_exists boolean;
2809
2810 cursor get_elem_type_ids(c_set_type_id IN NUMBER) IS
2811 select element_type_id from cs_kb_set_ele_types
2812 where set_type_id = c_set_type_id;
2813
2814 BEGIN
2815
2816 Open get_elem_type_ids(p_set_type_id);
2817 l_count := 1;
2818 Loop
2819
2820 Fetch get_elem_type_ids INTO l_element_type_ids(l_count);
2821 EXIT WHEN get_elem_type_ids%NOTFOUND;
2822 l_count := l_count + 1;
2823
2824 End Loop;
2825
2826 close get_elem_type_ids;
2827
2828 for j in 1..p_element_type_ids.count
2829 loop
2830 l_exists := FALSE;
2831 for k in 1..l_element_type_ids.count
2832 loop
2833
2834 IF (l_element_type_ids(k) = p_element_type_ids(j))
2835 THEN
2836 l_exists := TRUE;
2837 EXIT;
2838 END IF;
2839
2840 end loop;
2841 IF (l_exists = FALSE)
2842 THEN
2843 RETURN ERROR_STATUS;
2844
2845 END IF;
2846
2847 end loop;
2848
2849 RETURN OKAY_STATUS;
2850 EXCEPTION
2851 WHEN OTHERS THEN
2852 RETURN ERROR_STATUS;
2853
2854 END Validate_Set_Element_Type_Ids;
2855
2856
2857 /*
2858 * Validate_visibility_level
2859 * Given the textual name of a visibility level,
2860 * validate that such a visibility level exists
2861 * and fetch the id. It returns either success or
2862 * error.
2863 * Parameters:
2864 * p_visibility_name - textual name of the visibility level to validate.
2865 * x_visibility_id - out the validated visibility level id. The value
2866 * is undefined if the function returns an error.
2867 * Return Value:
2868 * If there is a validation error, returns FND_API.G_RET_STS_ERROR.
2869 * If the validation succeeds, returns FND_API.G_RET_STS_SUCCESSFUL.
2870 */
2871 FUNCTION VALIDATE_VISIBILITY_LEVEL
2872 (
2873 p_visibility_name IN VARCHAR2,
2874 x_visibility_id OUT NOCOPY NUMBER
2875 ) RETURN VARCHAR2 IS
2876 cursor get_matching_visibility( c_visibility_name VARCHAR2 ) is
2877 select visibility_id
2878 from cs_kb_visibilities_vl v
2879 where upper(v.name) like upper(c_visibility_name);
2880 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
2881 BEGIN
2882
2883 -- Check input parameters
2884 if( p_visibility_name is null) then
2885 return FND_API.G_RET_STS_ERROR;
2886 end if;
2887
2888 -- Fetch the id of the visibility level
2889 open get_matching_visibility(p_visibility_name);
2890 fetch get_matching_visibility into x_visibility_id;
2891 if ( get_matching_visibility%NOTFOUND ) then
2892 l_return_status := FND_API.G_RET_STS_ERROR;
2893 else
2894 l_return_status := FND_API.G_RET_STS_SUCCESS;
2895 end if;
2896 close get_matching_visibility;
2897 return l_return_status;
2898 EXCEPTION
2899 WHEN OTHERS THEN
2900 RETURN FND_API.G_RET_STS_ERROR;
2901 END Validate_Visibility_Level;
2902
2903
2904 FUNCTION VALIDATE_ACCESS_LEVEL
2905 (
2906 p_access_level_name IN VARCHAR2,
2907 x_access_level_value OUT NOCOPY VARCHAR2
2908 ) RETURN NUMBER IS
2909 BEGIN
2910
2911 select lookup_code into x_access_level_value
2912 from cs_lookups where lookup_type = 'CS_KB_ACCESS_LEVEL'
2913 and upper(meaning) like upper(p_access_level_name);
2914
2915 if (x_access_level_value IS NULL)
2916 then
2917 return ERROR_STATUS;
2918 end if;
2919 return OKAY_STATUS;
2920 EXCEPTION
2921 WHEN NO_DATA_FOUND THEN
2922 RETURN ERROR_STATUS;
2923 WHEN OTHERS THEN
2924 RETURN ERROR_STATUS;
2925 END Validate_Access_Level;
2926
2927 FUNCTION VALIDATE_ELEMENT_CONTENT_TYPE
2928 (
2929 p_ele_content_type IN VARCHAR2,
2930 p_ele_content_type_code OUT NOCOPY VARCHAR2
2931 ) RETURN NUMBER IS
2932 BEGIN
2933
2934 select lookup_code
2935 INTO p_ele_content_type_code
2936 from cs_lookups
2937 where lookup_type = 'CS_KB_CONTENT_TYPE'
2938 and meaning = p_ele_content_type;
2939
2940 IF ( (p_ele_content_type_code <> 'TEXT/HTML') AND
2941 (p_ele_content_type_code <> 'TEXT/PLAIN') AND
2942 (p_ele_content_type_code <> 'TEXT/X-PLAIN') AND
2943 (p_ele_content_type_code <> 'TEXT/X-HTML') ) THEN
2944 RETURN ERROR_STATUS;
2945 ELSE
2946 return OKAY_STATUS;
2947 END IF;
2948 EXCEPTION
2949 WHEN OTHERS THEN
2950 RETURN ERROR_STATUS;
2951 END Validate_Element_Content_Type;
2952
2953
2954 FUNCTION VALIDATE_ELEMENT_NO
2955 (
2956 p_ele_no IN VARCHAR2,
2957 x_latest_id OUT NOCOPY NUMBER
2958 ) RETURN NUMBER IS
2959
2960 BEGIN
2961
2962 select element_id INTO x_latest_id
2963 from cs_kb_elements_b
2964 where element_number = p_ele_no
2965 and status = 'PUBLISHED';
2966
2967 RETURN OKAY_STATUS;
2968
2969 EXCEPTION
2970 WHEN NO_DATA_FOUND THEN
2971 RETURN ERROR_STATUS;
2972 WHEN OTHERS THEN
2973 RETURN ERROR_STATUS;
2974 END Validate_Element_No;
2975
2976
2977 FUNCTION VALIDATE_PRODUCT_NAME
2978 (
2979 p_name IN VARCHAR2,
2980 x_number OUT NOCOPY NUMBER
2981 ) RETURN NUMBER IS
2982
2983 l_query VARCHAR2(1000);
2984 l_org_id NUMBER;
2985 l_prof_val NUMBER;
2986
2987 l_cursor INTEGER;
2988 ignore INTEGER;
2989
2990 BEGIN
2991 l_query :=
2992 'SELECT it.inventory_item_id '||
2993 'FROM mtl_system_items_vl it, mtl_item_categories ic '||
2994 'where it.inventory_item_id = ic.inventory_item_id '||
2995 'and it.organization_id = ic.organization_id '||
2996 'and it.organization_id = :l_org_id '||
2997 'and ic.category_set_id = :l_prof_val '||
2998 'and upper(it.description) = upper(:l_name) ';
2999
3000 l_cursor := DBMS_SQL.OPEN_CURSOR;
3001 DBMS_SQL.PARSE(l_cursor, l_query, DBMS_SQL.NATIVE);
3002 DBMS_SQL.BIND_VARIABLE(l_cursor, ':l_org_id', cs_std.get_item_valdn_orgzn_id);
3003 DBMS_SQL.BIND_VARIABLE(l_cursor, ':l_prof_val', fnd_profile.value('CS_KB_PRODUCT_CATEGORY_SET'));
3004 DBMS_SQL.BIND_VARIABLE(l_cursor, ':l_name', p_name);
3005 DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, x_number);
3006 ignore := DBMS_SQL.EXECUTE(l_cursor);
3007
3008 IF DBMS_SQL.FETCH_ROWS(l_cursor)>0 THEN
3009 DBMS_SQL.COLUMN_VALUE(l_cursor, 1, x_number);
3010 DBMS_SQL.CLOSE_CURSOR(l_cursor);
3011 RETURN OKAY_STATUS;
3012 ELSE
3013 DBMS_SQL.CLOSE_CURSOR(l_cursor);
3014 RETURN ERROR_STATUS;
3015 END IF;
3016
3017 EXCEPTION
3018
3019 WHEN NO_DATA_FOUND THEN
3020 RETURN ERROR_STATUS;
3021 WHEN OTHERS THEN
3022 RETURN ERROR_STATUS;
3023
3024 END Validate_Product_Name;
3025
3026 FUNCTION VALIDATE_PRODUCT_SEGMENT
3027 (
3028 p_segment IN VARCHAR2,
3029 x_number OUT NOCOPY NUMBER
3030 ) RETURN NUMBER IS
3031 CURSOR cur_segments(cp_org_id NUMBER, cp_prof_val VARCHAR2, cp_segments VARCHAR2) IS
3032 SELECT it.inventory_item_id
3033 FROM mtl_system_items_vl it, mtl_item_categories ic
3034 where it.inventory_item_id = ic.inventory_item_id
3035 and it.organization_id = ic.organization_id
3036 and it.organization_id = cp_org_id
3037 and ic.category_set_id = cp_prof_val
3038 and upper(it.concatenated_segments) = upper(cp_segments) ;
3039 BEGIN
3040 open cur_segments(cs_std.get_item_valdn_orgzn_id,
3041 fnd_profile.value('CS_KB_PRODUCT_CATEGORY_SET'),
3042 p_segment);
3043 fetch cur_segments into x_number;
3044 if cur_segments%NOTFOUND then
3045 close cur_segments;
3046 RETURN ERROR_STATUS;
3047 end if;
3048 close cur_segments;
3049
3050 return OKAY_STATUS;
3051
3052 EXCEPTION
3053 WHEN OTHERS THEN
3054 RETURN ERROR_STATUS;
3055 END Validate_Product_Segment;
3056
3057
3058 FUNCTION VALIDATE_PLATFORM_NAME
3059 (
3060 p_name IN VARCHAR2,
3061 x_number OUT NOCOPY NUMBER
3062 ) RETURN NUMBER IS
3063
3064 l_query VARCHAR2(1000);
3065 l_org_id NUMBER;
3066 l_prof_val NUMBER;
3067
3068 l_cursor INTEGER;
3069 ignore INTEGER;
3070
3071 BEGIN
3072
3073 l_query :=
3074 'SELECT it.inventory_item_id '||
3075 'FROM mtl_system_items_vl it, mtl_item_categories ic '||
3076 'where it.inventory_item_id = ic.inventory_item_id '||
3077 'and it.organization_id = ic.organization_id '||
3078 'and it.organization_id = :l_org_id '||
3079 'and ic.category_set_id = :l_prof_val '||
3080 'and upper(it.description) = upper(:l_name) ';
3081
3082 l_cursor := DBMS_SQL.OPEN_CURSOR;
3083 DBMS_SQL.PARSE(l_cursor, l_query, DBMS_SQL.NATIVE);
3084 DBMS_SQL.BIND_VARIABLE(l_cursor, ':l_org_id', cs_std.get_item_valdn_orgzn_id);
3085 DBMS_SQL.BIND_VARIABLE(l_cursor, ':l_prof_val', fnd_profile.value('CS_SR_PLATFORM_CATEGORY_SET'));
3086 DBMS_SQL.BIND_VARIABLE(l_cursor, ':l_name', p_name);
3087 DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, x_number);
3088 ignore := DBMS_SQL.EXECUTE(l_cursor);
3089
3090 IF DBMS_SQL.FETCH_ROWS(l_cursor)>0 THEN
3091 DBMS_SQL.COLUMN_VALUE(l_cursor, 1, x_number);
3092 DBMS_SQL.CLOSE_CURSOR(l_cursor);
3093 RETURN OKAY_STATUS;
3094 ELSE
3095 DBMS_SQL.CLOSE_CURSOR(l_cursor);
3096 RETURN ERROR_STATUS;
3097 END IF;
3098
3099 EXCEPTION
3100
3101 WHEN NO_DATA_FOUND THEN
3102 RETURN ERROR_STATUS;
3103 WHEN OTHERS THEN
3104 RETURN ERROR_STATUS;
3105
3106 END Validate_Platform_Name;
3107
3108 FUNCTION VALIDATE_PLATFORM_SEGMENT
3109 (
3110 p_segment IN VARCHAR2,
3111 x_number OUT NOCOPY NUMBER
3112 ) RETURN NUMBER IS
3113 CURSOR cur_segments(cp_org_id NUMBER, cp_prof_val VARCHAR2, cp_segments VARCHAR2) IS
3114 SELECT it.inventory_item_id
3115 FROM mtl_system_items_vl it, mtl_item_categories ic
3116 where it.inventory_item_id = ic.inventory_item_id
3117 and it.organization_id = ic.organization_id
3118 and it.organization_id = cp_org_id
3119 and ic.category_set_id = cp_prof_val
3120 and upper(it.concatenated_segments) = upper(cp_segments) ;
3121
3122 BEGIN
3123 open cur_segments(cs_std.get_item_valdn_orgzn_id,
3124 fnd_profile.value('CS_SR_PLATFORM_CATEGORY_SET'),
3125 p_segment);
3126 fetch cur_segments into x_number;
3127 if cur_segments%NOTFOUND then
3128 close cur_segments;
3129 RETURN ERROR_STATUS;
3130 end if;
3131 close cur_segments;
3132
3133 return OKAY_STATUS;
3134
3135 EXCEPTION
3136 WHEN OTHERS THEN
3137 RETURN ERROR_STATUS;
3138 END Validate_Platform_Segment;
3139 /*
3140 * forwards to VALIDATE_CATEGORY_NAME_2
3141 */
3142 FUNCTION VALIDATE_CATEGORY_NAME
3143 (
3144 p_name IN VARCHAR2,
3145 p_last_name IN VARCHAR2,
3146 x_number OUT NOCOPY NUMBER
3147 ) RETURN NUMBER IS
3148
3149 BEGIN
3150
3151 RETURN VALIDATE_CATEGORY_NAME_2(p_name, p_last_name, x_number, '>');
3152
3153 END Validate_Category_Name;
3154
3155 /*
3156 * forwards to Get_Category_Name_2
3157 */
3158 -- Given the Category name in a bread crumb form
3159 -- Determine the last category name
3160 -- For example given A>B>C>D, return D
3161 FUNCTION Get_Category_Name
3162 (
3163 p_category_name IN varchar2
3164 ) RETURN VARCHAR2 IS
3165
3166 begin
3167 RETURN Get_Category_Name_2(p_category_name,'>');
3168 End Get_Category_Name;
3169
3170 FUNCTION Encode_Text(p_text IN VARCHAR2) RETURN VARCHAR2 IS
3171
3172 l_text VARCHAR2(32767);
3173 l_gt VARCHAR2(15);
3174 l_lt VARCHAR2(15);
3175 l_amp VARCHAR2(15);
3176 l_apos VARCHAR2(15);
3177 l_quot VARCHAR2(15);
3178
3179
3180
3181 BEGIN
3182
3183 l_gt := '&' || 'gt;';
3184 l_lt := '&' || 'lt;';
3185 l_amp := '&' || 'amp;';
3186 l_apos := '&' || 'apos;';
3187 l_quot := '&' || 'quot;';
3188
3189 l_text := replace(p_text, '&', l_amp);
3190 l_text := replace(l_text, '>', l_gt);
3191 l_text := replace(l_text, '<', l_lt);
3192 l_text := replace(l_text, '''', l_apos);
3193 l_text := replace(l_text, '"', l_quot);
3194
3195 return l_text;
3196
3197 End Encode_Text;
3198
3199 -- Break a CLOB into chunks of VARCHARs
3200 -- and write it to a file
3201
3202 PROCEDURE Write_CLOB_TO_File(p_clob IN CLOB,
3203 p_file IN NUMBER) IS
3204
3205 chunkSize INTEGER;
3206 chunkPos INTEGER;
3207 amt INTEGER;
3208
3209 x_Buf varchar2(32767);
3210 x_sourceLength number;
3211
3212 BEGIN
3213
3214 IF (p_clob IS NULL)
3215 THEN
3216 RETURN;
3217 END IF;
3218
3219 x_sourceLength := DBMS_LOB.getlength(p_clob);
3220
3221 IF (x_sourceLength = 0)
3222 THEN
3223 RETURN;
3224 END IF;
3225
3226 chunkSize := DBMS_LOB.GETCHUNKSIZE(p_clob);
3227 -- BugFix 3995241 21-Apr-2005 MK
3228 -- chunksize reduced to allow for expansion after Encode_Text api call
3229 IF (chunkSize > 6553)
3230 THEN
3231 chunkSize := 6553;
3232 END IF;
3233
3234 chunkPos :=1;
3235
3236 -- read in chunks
3237 WHILE (chunkPos<= x_sourceLength) AND
3238 ((x_sourceLength-chunkPos) >= chunkSize)
3239 LOOP
3240 DBMS_LOB.READ(p_clob, chunkSize, chunkPos, x_Buf);
3241 FND_FILE.PUT(p_file, Encode_Text(x_Buf));
3242 chunkPos := chunkPos + chunkSize;
3243 END LOOP;
3244
3245 -- read the rest of CLOB
3246 IF ((x_sourceLength-chunkPos) < chunkSize) THEN
3247 amt := x_sourceLength-chunkPos+1;
3248 DBMS_LOB.READ(p_clob, amt, chunkPos, x_Buf);
3249 FND_FILE.PUT(p_file, Encode_Text(x_Buf));
3250 END IF;
3251
3252 END Write_Clob_To_File;
3253
3254
3255 /*
3256 * Export_Solutions
3257 * This procedure is used by the XML Solution Import / Export
3258 * concurrent program. Depending on the export mode selected,
3259 * It can export either all published solutions or the
3260 * latest version of all solutions in a particular category.
3261 * Parameters:
3262 * p_category_name - This should be the full textual path
3263 * of the category for which solutions will be exported.
3264 * The individual category names should be separated by
3265 * a '>'. Example: 'Home>Desktop>Monitor'
3266 * p_sol_status - One of 2 mode values: ALL or PUB. This
3267 * determines whether only published solutions are exported
3268 * or the latest version of all non-obsoleted solutions
3269 * are exported.
3270 */
3271 /*
3272 * forwards to EXPORT_SOLUTIONS_2
3273 */
3274 PROCEDURE EXPORT_SOLUTIONS
3275 (
3276 errbuf OUT NOCOPY VARCHAR2,
3277 retcode OUT NOCOPY NUMBER,
3278 p_category_name IN VARCHAR2,
3279 p_sol_status IN VARCHAR2
3280 ) IS
3281
3282 BEGIN
3283 EXPORT_SOLUTIONS_2(errbuf, retcode, p_category_name, p_sol_status, '>');
3284
3285 END EXPORT_SOLUTIONS;
3286
3287
3288 PROCEDURE GET_USER_ACCESS_LEVEL(
3289 p_user_name IN VARCHAR2,
3290 x_access_level OUT NOCOPY NUMBER) IS
3291
3292 l_permission_name VARCHAR2(30);
3293 l_flag NUMBER(15);
3294 l_return_status VARCHAR2(30);
3295 BEGIN
3296
3297 --Default Access Level is only for external
3298 x_access_level := 3000;
3299
3300 l_permission_name := 'CS_Solution_View_Restricted';
3301
3302 JTF_AUTH_SECURITY_PKG.check_permission(
3303 x_flag => l_flag,
3304 x_return_status => l_return_status,
3305 p_user_name => p_user_name,
3306 p_permission_name => l_permission_name);
3307
3308 IF (l_flag = 1)
3309 THEN
3310 x_access_level := 900;
3311 RETURN;
3312 END IF;
3313
3314 l_permission_name := 'CS_Solution_View_Internal';
3315
3316 JTF_AUTH_SECURITY_PKG.check_permission(
3317 x_flag => l_flag,
3318 x_return_status => l_return_status,
3319 p_user_name => p_user_name,
3320 p_permission_name => l_permission_name);
3321
3322 IF (l_flag = 1)
3323 THEN
3324 x_access_level := 1000;
3325 RETURN;
3326 END IF;
3327
3328 l_permission_name := 'CS_Solution_View';
3329
3330 JTF_AUTH_SECURITY_PKG.check_permission(
3331 x_flag => l_flag,
3332 x_return_status => l_return_status,
3333 p_user_name => p_user_name,
3334 p_permission_name => l_permission_name);
3335
3336 IF (l_flag = 1)
3337 THEN
3338 x_access_level := 3000;
3339 RETURN;
3340 END IF;
3341
3342 EXCEPTION
3343
3344 WHEN OTHERS THEN
3345 x_access_level := 3000;
3346
3347
3348 END GET_USER_ACCESS_LEVEL;
3349
3350
3351 FUNCTION GET_USER_NAME (V_USER_ID NUMBER)
3352 RETURN VARCHAR2
3353 AS
3354
3355 Cursor Get_Emp_User IS
3356 SELECT P.FULL_NAME
3357 FROM FND_USER fu
3358 ,PER_ALL_PEOPLE_F P
3359 WHERE sysdate BETWEEN nvl(fu.start_date, sysdate-1)
3360 AND nvl(fu.end_date, sysdate+1)
3361 AND fu.employee_id = P.person_id
3362 AND TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE
3363 AND P.EFFECTIVE_END_DATE
3364 AND fu.User_id = V_USER_ID;
3365
3366 Cursor Get_B2C_User IS
3367 SELECT hp.party_name
3368 FROM hz_parties hp
3369 ,fnd_user fu
3370 WHERE hp.party_type = 'PERSON'
3371 AND sysdate BETWEEN nvl(fu.start_date, sysdate-1)
3372 AND nvl(fu.end_date, sysdate+1)
3373 AND fu.customer_id = hp.party_id
3374 AND fu.customer_id is not null
3375 AND fu.employee_id is null
3376 AND fu.User_id = V_USER_ID;
3377
3378 Cursor Get_B2B_User IS
3379 SELECT hp.party_name
3380 from hz_parties hp
3381 ,hz_relationships hr
3382 ,fnd_user fu
3383 WHERE hr.party_id = fu.customer_id
3384 AND hr.subject_id = hp.party_id
3385 AND hr.relationship_code in ('EMPLOYEE_OF', 'CONTACT_OF')
3386 AND hp.party_type = 'PERSON'
3387 AND hr.subject_table_name = 'HZ_PARTIES'
3388 AND hr.object_table_name = 'HZ_PARTIES'
3389 AND sysdate BETWEEN nvl(fu.start_date, sysdate-1)
3390 AND nvl(fu.end_date, sysdate+1)
3391 AND fu.customer_id is not null
3392 AND fu.employee_id is null
3393 AND fu.User_id = V_USER_ID;
3394
3395 CURSOR GET_FND_USER IS
3396 SELECT fu.user_name
3397 FROM FND_USER fu
3398 WHERE fu.User_id = V_USER_ID;
3399
3400 l_full_name VARCHAR2(200) := null;
3401 BEGIN
3402 OPEN GET_EMP_USER;
3403 FETCH GET_EMP_USER INTO l_full_name;
3404 CLOSE GET_EMP_USER;
3405
3406 IF l_full_name IS NULL THEN
3407 OPEN GET_B2C_USER;
3408 FETCH GET_B2C_USER INTO l_full_name;
3409 CLOSE GET_B2C_USER;
3410
3411 IF l_full_name IS NULL THEN
3412 OPEN GET_B2B_USER;
3413 FETCH GET_B2B_USER INTO l_full_name;
3414 CLOSE GET_B2B_USER;
3415
3416 IF l_full_name IS NULL THEN
3417 OPEN GET_FND_USER;
3418 FETCH GET_FND_USER INTO l_full_name;
3419 CLOSE GET_FND_USER;
3420 END IF;
3421 END IF;
3422
3423 END IF;
3424
3425 RETURN l_full_name;
3426 END;
3427
3428 -- (SRCHEFF)
3429 /*
3430 This program updates the usage score based on the usage. It consists of two
3431 sections: 1. update usage scores of those solutions that were published AFTER
3432 the (sysdate - time_span) date, 2. update usage scores of those solutions that
3433 were publishec BEFORE the (sysdate - time_span) date.
3434 In the first section, we compensate the score with an aging factor. The aging
3435 actor is calculated as:
3436 1 - (sysdate - last_update_date)/time_span. The agian factor should only
3437 range from 0 - 1, that is it is 1 if the last update date is the sysdate, and
3438 0 if it is (sysdate - time_span).
3439
3440 In both cases, we only look at those solutions of which the feedback or
3441 linkage were create AFTERthe cut-off date (sysdate - time_span).
3442 */
3443 PROCEDURE Update_Solution_Usage_Score (
3444 p_commit IN VARCHAR2 := FND_API.G_FALSE)
3445 IS
3446 -- Default time usage to 1 year if the profile is not set.
3447 CURSOR Get_Time_Usage_Csr IS
3448 Select nvl(fnd_profile.value('CS_KB_USAGE_TIME_SPAN'), 365) from dual;
3449
3450 -- Bug 32170161, replace sum with avg.
3451 CURSOR Get_Avg_Score_Csr(p_time_usge_span NUMBER) IS
3452 select avg(usage_score)/p_time_usge_span
3453 from cs_kb_sets_b
3454 where status = 'PUB';
3455
3456 CURSOR Get_Lower_Limit_Csr(p_coefficient NUMBER, p_time_usage_span NUMBER) IS
3457 select avg(usage_score) - (p_coefficient*stddev(usage_score)/sqrt(count(set_id)))
3458 from cs_kb_sets_b
3459 where status = 'PUB'
3460 and last_update_date > (sysdate - p_time_usage_span);
3461
3462 CURSOR Get_Upper_Limit_Csr(p_coefficient NUMBER, p_time_usage_span NUMBER) IS
3463 select avg(usage_score) + (p_coefficient*stddev(usage_score)/sqrt(count(set_id)))
3464 from cs_kb_sets_b
3465 where status = 'PUB'
3466 and last_update_date > (sysdate - p_time_usage_span);
3467
3468 CURSOR Get_Coefficient_Csr IS
3469 select fnd_profile.value('CS_KB_USAGE_LIMIT_FACTOR') from dual;
3470
3471 -- (4740480)
3472 CURSOR Get_Set_Count(p_time_usage_span NUMBER) IS
3473 SELECT count(set_id)
3474 FROM cs_kb_sets_b
3475 WHERE status = 'PUB'
3476 AND last_update_date > (SYSDATE - p_time_usage_span);
3477
3478 l_set_count NUMBER;
3479 -- 4740480_eof
3480
3481 l_time_usage NUMBER := 0;
3482 l_usage_limit_factor NUMBER := 0;
3483 l_avg_score NUMBER := 0;
3484 l_coefficient NUMBER := 0;
3485 l_lower NUMBER := 0;
3486 l_higher NUMBER := 0;
3487
3488 BEGIN
3489 Savepoint l_upd_usage_score_sav;
3490
3491 -- get profile values
3492 OPEN Get_Time_Usage_Csr;
3493 FETCH Get_Time_Usage_Csr INTO l_time_usage;
3494 CLOSE Get_Time_Usage_Csr;
3495
3496 If l_time_usage <= 0 Then
3497 RAISE INVALID_USAGE_TIME_SPAN_ERROR;
3498 End If;
3499
3500 -- 4740480
3501 OPEN get_set_count(l_time_usage);
3502 FETCH Get_Set_Count INTO l_set_count;
3503 CLOSE Get_Set_Count;
3504
3505 IF l_set_count > 0 THEN
3506 -- 4740480_eof
3507
3508 OPEN Get_Avg_Score_Csr(l_time_usage);
3509 FETCH Get_Avg_Score_Csr INTO l_avg_score;
3510 CLOSE Get_Avg_Score_Csr;
3511
3512 -- 1. Update usage scores of solutions that were published AFTER
3513 -- (sysadate - l_time_usage) based on the used history.
3514 -- 1.1 Get score from used history
3515 update cs_kb_sets_b c set usage_score =
3516 (
3517 select
3518 round(
3519 sum(to_number(cl.meaning)*(1-(sysdate-a.creation_date)/l_time_usage))
3520 +
3521 (l_avg_score * ( 1 - (sysdate - c.last_update_date)/l_time_usage) )
3522 )
3523 from cs_kb_set_used_hists a, cs_lookups cl
3524 where a.set_id = c.set_id
3525 and a.used_type = cl.lookup_code
3526 and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
3527 and a.creation_date >= (sysdate - l_time_usage)
3528 and c.last_update_date > (sysdate - l_time_usage)
3529 and c.status = 'PUB'
3530 group by c.set_id, c.last_update_date
3531 )
3532 where c.status = 'PUB'
3533 and c.last_update_date > (sysdate-l_time_usage)
3534 and exists (
3535 select null
3536 from cs_kb_set_used_hists a, cs_lookups cl
3537 where a.set_id = c.set_id
3538 and a.used_type = cl.lookup_code
3539 and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
3540 and a.creation_date >= (sysdate - l_time_usage)
3541 and c.last_update_date > (sysdate - l_time_usage)
3542 and c.status = 'PUB'
3543 );
3544
3545 --1.2 Update usage scores of solutions that were published AFTER
3546 -- sysdate - l_time_usage based on the solution linkage.
3547 update cs_kb_sets_b c set usage_score =
3548 (
3549 select
3550 round(
3551 sum(to_number(cl.meaning)*(1-(sysdate-a.creation_date)/l_time_usage))
3552 +
3553 (l_avg_score * ( 1 - (sysdate - c.last_update_date)/l_time_usage) )
3554 ) + c.usage_score
3555 from cs_kb_set_links a, cs_lookups cl
3556 where a.set_id = c.set_id
3557 and a.link_type = cl.lookup_code
3558 and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
3559 and a.creation_date >= (sysdate - l_time_usage)
3560 and c.last_update_date > (sysdate - l_time_usage)
3561 and c.status = 'PUB'
3562 group by c.set_id, c.last_update_date
3563 )
3564 where c.status = 'PUB'
3565 and c.last_update_date > (sysdate-l_time_usage)
3566 and exists (
3567 select null
3568 from cs_kb_set_links a, cs_lookups cl
3569 where a.set_id = c.set_id
3570 and a.link_type = cl.lookup_code
3571 and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
3572 and a.creation_date >= (sysdate - l_time_usage)
3573 and c.last_update_date > (sysdate - l_time_usage)
3574 and c.status = 'PUB'
3575 );
3576
3577 -- 2. Update usage scores of solutions that were published BEFORE
3578 -- sysdate - l_time_usage based on the used history. Aging factor
3579 -- compensation will not be added in this update.
3580 update cs_kb_sets_b c set usage_score =
3581 (
3582 select round(sum(to_number(cl.meaning)*(1-(sysdate-a.creation_date)/l_time_usage))
3583 )
3584 from cs_kb_set_used_hists a, cs_lookups cl
3585 where a.set_id = c.set_id
3586 and a.used_type = cl.lookup_code
3587 and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
3588 and a.creation_date >= (sysdate - l_time_usage)
3589 and c.last_update_date <= (sysdate - l_time_usage)
3590 and c.status = 'PUB'
3591 group by c.set_id, c.last_update_date
3592 )
3593 where c.status = 'PUB'
3594 and c.last_update_date <= (sysdate-l_time_usage)
3595 and exists (
3596 select null
3597 from cs_kb_set_used_hists a, cs_lookups cl
3598 where a.set_id = c.set_id
3599 and a.used_type = cl.lookup_code
3600 and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
3601 and a.creation_date >= (sysdate - l_time_usage)
3602 and c.last_update_date <= (sysdate - l_time_usage)
3603 and c.status = 'PUB'
3604 );
3605
3606 -- 2.1 Update usage scores of solutions that were published BEFORE
3607 -- sysdate - l_time_usage based on the solution linkage.
3608 update cs_kb_sets_b c set usage_score =
3609 (
3610 select round(sum(to_number(cl.meaning)*(1-(sysdate-a.creation_date)/l_time_usage))
3611 ) + c.usage_score
3612 from cs_kb_set_links a, cs_lookups cl
3613 where a.set_id = c.set_id
3614 and a.link_type = cl.lookup_code
3615 and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
3616 and a.creation_date >= (sysdate - l_time_usage)
3617 and c.last_update_date <= (sysdate - l_time_usage)
3618 and c.status = 'PUB'
3619 group by c.set_id, c.last_update_date
3620 )
3621 where c.status = 'PUB'
3622 and c.last_update_date <= (sysdate-l_time_usage)
3623 and exists (
3624 select null
3625 from cs_kb_set_links a, cs_lookups cl
3626 where a.set_id = c.set_id
3627 and a.link_type = cl.lookup_code
3628 and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
3629 and a.creation_date >= (sysdate - l_time_usage)
3630 and c.last_update_date <= (sysdate - l_time_usage)
3631 and c.status = 'PUB'
3632 );
3633
3634
3635 -- Update the normalized usage score column.
3636 Open Get_Coefficient_Csr;
3637 Fetch Get_Coefficient_Csr Into l_coefficient;
3638 Close Get_Coefficient_Csr;
3639
3640 If l_coefficient = 0 Then
3641 Raise INVALID_COEFFICIENT_FACTOR;
3642 End If;
3643
3644 Open Get_Lower_Limit_Csr(l_coefficient, l_time_usage);
3645 Fetch Get_Lower_Limit_Csr Into l_lower;
3646 Close Get_Lower_Limit_Csr;
3647
3648 Open Get_Upper_Limit_Csr(l_coefficient, l_time_usage);
3649 Fetch Get_Upper_Limit_Csr Into l_higher;
3650 Close Get_Upper_Limit_Csr;
3651
3652 -- update norm_usage_score
3653 If (l_higher - l_lower) <> 0 Then --5705547
3654 update cs_kb_sets_b set norm_usage_score = (
3655 ( decode(sign(decode(sign(usage_score - l_lower),
3656 -1, l_lower,
3657 usage_score) - l_higher),
3658 -1, decode(sign(usage_score - l_lower),
3659 -1, l_lower,
3660 usage_score),
3661 l_higher) - l_lower)/(l_higher - l_lower)*100 )
3662 where status = 'PUB';
3663 End If; --5705547
3664
3665 -- commit changes
3666 IF FND_API.to_Boolean( p_commit )
3667 THEN
3668 COMMIT WORK;
3669 END IF;
3670 END IF; -- end l_set_count > 0, 4740480
3671 EXCEPTION
3672 WHEN INVALID_USAGE_TIME_SPAN_ERROR THEN
3673 Rollback To l_upd_usage_score_sav;
3674 Raise;
3675 WHEN INVALID_COEFFICIENT_FACTOR THEN
3676 Rollback To l_upd_usage_score_sav;
3677 Raise;
3678 WHEN OTHERS THEN
3679 Rollback To l_upd_usage_score_sav;
3680 Raise;
3681 END Update_Solution_Usage_Score;
3682
3683 /*
3684 * Form the category's full path name based on category id
3685 * The path is delimited by the specified parameter.
3686 * - if p_verify is true,
3687 * then it will LOG a warning when category contains a '>'
3688 */
3689 FUNCTION Get_Category_Full_Name
3690 (
3691 p_catid IN NUMBER,
3692 p_delim IN VARCHAR2,
3693 p_verify IN BOOLEAN DEFAULT FALSE
3694 -- full_cat_name OUT VARCHAR2
3695 ) RETURN VARCHAR2 IS
3696 cursor c1 is
3697 select category_id
3698 from cs_kb_soln_categories_b
3699 start with category_id = p_catid
3700 connect by prior parent_category_id = category_id;
3701
3702 Type category_id_tab_type is TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
3703 category_id_tbl category_id_tab_type;
3704
3705 l_cat_name VARCHAR(2000);
3706
3707 j NUMBER(15);
3708 composite_cat_name VARCHAR2(2000);
3709
3710 contains_gt BOOLEAN;
3711
3712 BEGIN
3713 open c1;
3714
3715 j := 1;
3716 composite_cat_name := '';
3717
3718 contains_gt := FALSE;
3719
3720 loop
3721 fetch c1 INTO category_id_tbl(j);
3722 exit when c1%NOTFOUND;
3723
3724 select name into l_cat_name
3725 from cs_kb_soln_categories_tl
3726 where language = userenv('LANG') and category_id = category_id_tbl(j);
3727
3728 if(p_verify and 0 < instr(l_cat_name, '>')) then
3729 contains_gt := true;
3730 end if;
3731
3732 if (j = 1) then
3733 composite_cat_name := l_cat_name || composite_cat_name;
3734 else
3735 composite_cat_name := l_cat_name || p_delim || composite_cat_name;
3736 end if;
3737
3738 j := j+1;
3739 end loop;
3740
3741 close c1;
3742
3743 if(contains_gt) then
3744 FND_FILE.PUT_LINE(FND_FILE.LOG, fnd_message.GET_STRING('CS','CS_KB_EXPORT_INVCAT') || composite_cat_name);
3745 end if;
3746
3747 return composite_cat_name;
3748
3749
3750 END Get_Category_Full_Name;
3751
3752 /*
3753 This is the same as the original validate_category_name, except it takes a delimiter param
3754 */
3755 FUNCTION VALIDATE_CATEGORY_NAME_2
3756 (
3757 p_name IN VARCHAR2,
3758 p_last_name IN VARCHAR2,
3759 x_number OUT NOCOPY NUMBER,
3760 p_delim IN VARCHAR2
3761 ) RETURN NUMBER IS
3762
3763 Cursor c1 is
3764 select category_id from
3765 cs_kb_soln_categories_tl
3766 where upper(name) = upper(p_last_name)
3767 and language = userenv('LANG');
3768
3769 cursor c2(c_id IN NUMBER) is
3770 select tl.name
3771 from ( SELECT category_id, level lev
3772 FROM cs_kb_soln_categories_b
3773 START WITH category_id = c_id
3774 CONNECT BY prior parent_category_id = category_id
3775 ) b, cs_kb_soln_categories_tl tl
3776 where
3777 b.category_id = tl.category_id
3778 and tl.language = userenv( 'LANG' )
3779 order by b.lev;
3780
3781 l_count NUMBER(15);
3782 j NUMBER(15);
3783
3784 Type category_id_tab_type is TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
3785 Type categ_name_tab_type is TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
3786
3787 category_id_tbl category_id_tab_type;
3788 category_name_tbl categ_name_tab_type;
3789 tree_ids_tbl category_id_tab_type;
3790 tree_names_tbl categ_name_tab_type;
3791 composite_cat_name VARCHAR2(2000);
3792 valid_flag boolean;
3793
3794 BEGIN
3795
3796 -- Compare this path with p_name
3797 -- If there is a match then this is the one
3798
3799 l_count := 1;
3800
3801 open c1;
3802 Loop
3803
3804 Fetch c1 INTO category_id_tbl(l_count);
3805 EXIT WHEN c1%NOTFOUND;
3806 l_count := l_count + 1;
3807
3808 End Loop;
3809 close c1;
3810
3811 -- dbms_output.put_line('Number='||category_id_tbl.count);
3812
3813 -- loop thru the base level categories
3814 for i in 1..category_id_tbl.count loop
3815
3816 valid_flag := false;
3817 open c2(category_id_tbl(i));
3818
3819 j := 1;
3820 composite_cat_name := '';
3821 loop
3822 fetch c2 INTO tree_names_tbl(j);
3823
3824 exit when c2%NOTFOUND;
3825
3826 if (j = 1) then
3827 composite_cat_name := tree_names_tbl(j) || composite_cat_name;
3828 else
3829 composite_cat_name := tree_names_tbl(j) || p_delim || composite_cat_name;
3830 end if;
3831
3832 j := j+1;
3833 end loop;
3834
3835 close c2;
3836
3837 if (upper(composite_cat_name) = upper(p_name))
3838 THEN
3839 x_number := category_id_tbl(i);
3840 valid_flag := true;
3841 exit;
3842 end IF;
3843
3844
3845 end loop;
3846
3847 if (valid_flag = true)
3848 then
3849
3850 RETURN OKAY_STATUS;
3851
3852 else
3853
3854 return ERROR_STATUS;
3855 end if;
3856
3857 EXCEPTION
3858
3859 WHEN NO_DATA_FOUND THEN
3860 RETURN ERROR_STATUS;
3861 WHEN OTHERS THEN
3862 RETURN ERROR_STATUS;
3863
3864 END Validate_Category_Name_2;
3865
3866 /*
3867 This is the same as the original Get_Category_Name, except it takes a delimiter param
3868 */
3869 -- Given the Category name in a bread crumb form
3870 -- Determine the last category name
3871 -- For example given A>B>C>D, return D
3872 -- (where '>' is whatever char specified by p_delim)
3873
3874 FUNCTION Get_Category_Name_2
3875 (
3876 p_category_name IN varchar2,
3877 p_delim IN VARCHAR2
3878 ) RETURN VARCHAR2 IS
3879
3880 l_temp CS_KB_SOLN_CATEGORIES_TL.NAME%TYPE;
3881 l_start_loc number := 1;
3882 l_temp_loc number := 1;
3883 l_temp_loc_buf number := 1;
3884 l_length number;
3885 l_out CS_KB_SOLN_CATEGORIES_TL.NAME%TYPE;
3886
3887 begin
3888 --remove spaces
3889
3890 l_length := length(p_category_name);
3891
3892 WHILE (l_temp_loc < l_length) AND (l_temp_loc >0)
3893 LOOP
3894 l_temp_loc := INSTR(p_category_name, p_delim, l_start_loc, 1);
3895 IF (l_temp_loc > 0) THEN
3896 l_temp_loc_buf := l_temp_loc + 1;
3897 END IF;
3898 l_start_loc := l_temp_loc + 1;
3899 END LOOP;
3900
3901 l_out := SUBSTR(p_category_name, l_temp_loc_buf);
3902 l_out := RTRIM(l_out);
3903 l_out := LTRIM(l_out);
3904
3905 RETURN l_out;
3906
3907 End Get_Category_Name_2;
3908
3909 /*
3910 * Export_Solutions_2
3911 *
3912 * This procedure is used by the XML Solution Export
3913 * concurrent program. Depending on the export mode selected,
3914 * It can export either all published solutions or the
3915 * latest version of all solutions in a particular category.
3916 * The third parameter is the delimiter, which the concurrent
3917 * program defaults to '>'. However, the user may need to
3918 * specify a different delimiter, if any category has '>' its name
3919 *
3920 * Parameters:
3921 * p_category_name - This should be the full textual path
3922 * of the category for which solutions will be exported.
3923 * The individual category names should be separated by
3924 * the delimiter. Example: 'Home <delim> Desktop <delim>Monitor'
3925 * p_sol_status - One of 2 mode values: ALL or PUB. This
3926 * determines whether only published solutions are exported
3927 * or the latest version of all non-obsoleted solutions
3928 * are exported.
3929 * delim - The delimiter used
3930 */
3931 PROCEDURE EXPORT_SOLUTIONS_2
3932 (
3933 errbuf OUT NOCOPY VARCHAR2,
3934 retcode OUT NOCOPY NUMBER,
3935 p_category_name IN VARCHAR2,
3936 p_sol_status IN VARCHAR2,
3937 p_delim IN VARCHAR2
3938 ) IS
3939
3940
3941 -- Fetch info for the published version of all published solutions
3942 -- in a category
3943 CURSOR get_solution_info_pub(c_category_id IN NUMBER)
3944 IS
3945 select /*+ index(sc) */a.set_id, a.set_number, c.name, v.name, b.name, a.status
3946 from cs_kb_sets_b a, cs_kb_sets_tl b, cs_kb_set_types_vl c,
3947 cs_kb_visibilities_vl v, cs_kb_set_categories sc
3948 where a.set_type_id = c.set_type_id
3949 and a.set_id = sc.set_id and sc.category_id = c_category_id
3950 and a.set_id = b.set_id and b.language = userenv('LANG')
3951 and a.visibility_id = v.visibility_id
3952 and a.status = 'PUB';
3953
3954 -- Fetch info for the latest version of all non-obsolete solutions
3955 -- in a category
3956 CURSOR get_solution_info_all(c_category_id IN NUMBER)
3957 IS
3958 select /*+ index(sc) */ a.set_id, a.set_number, c.name, v.name, b.name, a.status
3959 from cs_kb_sets_b a, cs_kb_sets_tl b, cs_kb_set_types_vl c,
3960 cs_kb_visibilities_vl v, cs_kb_set_categories sc
3961 where a.set_type_id = c.set_type_id
3962 and a.set_id = sc.set_id and sc.category_id = c_category_id
3963 and a.set_id = b.set_id and b.language = userenv('LANG')
3964 and a.visibility_id = v.visibility_id
3965 and a.status <> 'OBS'
3966 and a.latest_version_flag = 'Y';
3967
3968
3969 -- Fetch info for all statements for a solution
3970 CURSOR get_element_info(c_set_id IN NUMBER) IS
3971 select a.element_number, d.name, e.meaning,
3972 NVL(a.content_type, 'TEXT/HTML'), b.name, b.description, a.status
3973 from cs_kb_elements_b a , cs_kb_elements_tl b, cs_kb_set_eles c,
3974 cs_kb_element_types_vl d, cs_lookups e
3975 where a.element_id = c.element_id and c.set_id = c_set_id
3976 and a.element_id = b.element_id and b.language = userenv('LANG')
3977 and a.element_type_id = d.element_type_id
3978 and a.access_level = e.lookup_code and e.lookup_type = 'CS_KB_ACCESS_LEVEL';
3979
3980 -- Fetch info for all products the solution links to
3981 CURSOR get_product_info(c_set_id IN NUMBER) IS
3982 SELECT it.description,it.concatenated_segments
3983 FROM mtl_system_items_vl it, mtl_item_categories ic, cs_kb_set_products a
3984 where it.inventory_item_id = ic.inventory_item_id
3985 and it.organization_id = ic.organization_id
3986 and it.organization_id = cs_std.get_item_valdn_orgzn_id
3987 and ic.category_set_id = fnd_profile.value('CS_KB_PRODUCT_CATEGORY_SET')
3988 and it.inventory_item_id = a.product_id
3989 and a.set_id = c_set_id;
3990
3991 -- Fetch info for all platforms the solution links to
3992 CURSOR get_platform_info(c_set_id IN NUMBER) IS
3993 SELECT it.description,it.concatenated_segments
3994 FROM mtl_system_items_vl it, mtl_item_categories ic, cs_kb_set_platforms a
3995 where it.inventory_item_id = ic.inventory_item_id
3996 and it.organization_id = ic.organization_id
3997 and it.organization_id = cs_std.get_item_valdn_orgzn_id
3998 and ic.category_set_id = fnd_profile.value('CS_SR_PLATFORM_CATEGORY_SET')
3999 and it.inventory_item_id = a.platform_id
4000 and a.set_id = c_set_id;
4001
4002 -- Fetch all category ids associated with this solution
4003 CURSOR get_category_ids(c_set_id IN NUMBER) IS
4004 Select a.category_id
4005 from cs_kb_set_categories a
4006 where a.set_id = c_set_id;
4007
4008 -- Declare some local temporary variables
4009 l_category_name CS_KB_SOLN_CATEGORIES_TL.NAME%TYPE;
4010 l_category_id NUMBER(15);
4011 l_ret_val NUMBER(15);
4012 l_gt VARCHAR2(15);
4013 l_elmt_content_type VARCHAR2(30);
4014
4015 l_soln_count NUMBER(15);
4016 l_elmt_count NUMBER(15);
4017 l_prod_count NUMBER(15);
4018 l_plat_count NUMBER(15);
4019
4020 l_soln_index NUMBER(15);
4021 l_elmt_index NUMBER(15);
4022 l_prod_index NUMBER(15);
4023 l_plat_index NUMBER(15);
4024
4025 l_cat_index NUMBER(15);
4026
4027 Type set_id_tab_type
4028 is TABLE OF CS_KB_SETS_B.SET_ID%TYPE INDEX BY BINARY_INTEGER;
4029 Type set_number_tab_type
4030 is TABLE OF CS_KB_SETS_B.SET_NUMBER%TYPE INDEX BY BINARY_INTEGER;
4031 Type set_type_name_tab_type
4032 is TABLE OF CS_KB_SET_TYPES_TL.NAME%TYPE INDEX BY BINARY_INTEGER;
4033 Type set_name_tab_type
4034 is TABLE OF CS_KB_SETS_TL.NAME%TYPE INDEX BY BINARY_INTEGER;
4035 Type set_vis_tab_type
4036 is TABLE OF CS_LOOKUPS.MEANING%TYPE INDEX BY BINARY_INTEGER;
4037 Type set_status_tab_type
4038 is TABLE OF CS_KB_SETS_B.STATUS%TYPE INDEX BY BINARY_INTEGER;
4039 Type cat_id_tab_type
4040 is TABLE OF CS_KB_SOLN_CATEGORIES_VL.CATEGORY_ID%TYPE INDEX BY BINARY_INTEGER;
4041
4042 l_set_ids set_id_tab_type;
4043 l_set_nos set_number_tab_type;
4044 l_set_type_names set_type_name_tab_type;
4045 l_set_names set_name_tab_type;
4046 l_set_vis set_vis_tab_type;
4047 l_set_status set_status_tab_type;
4048 l_cat_ids cat_id_tab_type;
4049
4050 Type elmt_number_tab_type
4051 is TABLE OF CS_KB_ELEMENTS_B.ELEMENT_NUMBER%TYPE INDEX BY BINARY_INTEGER;
4052 Type elmt_type_name_tab_type
4053 is TABLE OF CS_KB_ELEMENT_TYPES_TL.NAME%TYPE INDEX BY BINARY_INTEGER;
4054 Type elmt_name_tab_type
4055 is TABLE OF CS_KB_ELEMENTS_TL.NAME%TYPE INDEX BY BINARY_INTEGER;
4056 Type elmt_desc_tab_type
4057 is TABLE OF CS_KB_ELEMENTS_TL.DESCRIPTION%TYPE INDEX BY BINARY_INTEGER;
4058 Type elmt_dist_tab_type
4059 is TABLE OF CS_LOOKUPS.MEANING%TYPE INDEX BY BINARY_INTEGER;
4060 Type elmt_status_tab_type
4061 is TABLE OF CS_KB_ELEMENTS_B.STATUS%TYPE INDEX BY BINARY_INTEGER;
4062 Type elmt_ct_tab_type
4063 is TABLE OF CS_KB_ELEMENTS_B.CONTENT_TYPE%TYPE INDEX BY BINARY_INTEGER;
4064
4065 l_elmt_nos elmt_number_tab_type;
4066 l_elmt_type_names elmt_type_name_tab_type;
4067 l_elmt_names elmt_name_tab_type;
4068 l_elmt_descs elmt_desc_tab_type;
4069 l_elmt_dists elmt_dist_tab_type;
4070 l_elmt_stats elmt_status_tab_type;
4071 l_elmt_cts elmt_ct_tab_type;
4072
4073 Type prod_name_tab_type
4074 is TABLE OF MTL_SYSTEM_ITEMS_VL.description%TYPE INDEX BY BINARY_INTEGER;
4075 Type plat_name_tab_type
4076 is TABLE OF MTL_SYSTEM_ITEMS_VL.description%TYPE INDEX BY BINARY_INTEGER;
4077
4078 l_prod_names prod_name_tab_type;
4079 l_prod_segments MTL_SYSTEM_ITEMS_VL.concatenated_segments%TYPE;
4080 l_plat_names plat_name_tab_type;
4081 l_plat_segments MTL_SYSTEM_ITEMS_VL.concatenated_segments%TYPE;
4082
4083 --local delimiter
4084 l_delim VARCHAR2(10);
4085
4086 -- Cursor + Vars to Validate Stmt Content
4087 CURSOR Get_Stmts (v_set_id NUMBER) is
4088 SELECT
4089 eb.Element_Number,
4090 replace(
4091 replace(
4092 replace(
4093 replace(
4094 replace(et.description, '&','&'||'amp;' )
4095 , '>','&'||'gt;')
4096 , '<','&'||'lt;')
4097 , '''','&'||'apos;')
4098 , '"' ,'&'||'quot;') stmt
4099 FROM CS_KB_ELEMENTS_TL et,
4100 CS_KB_ELEMENTS_B eb,
4101 CS_KB_SET_ELES se
4102 WHERE et.language = userenv('LANG')
4103 AND eb.Element_Id = et.Element_Id
4104 AND eb.element_id = se.element_id
4105 AND se.set_id = v_set_id;
4106
4107 l_Num NUMBER;
4108 l_Start NUMBER;
4109 l_Limit NUMBER := 32000;
4110 l_Error VARCHAR2(1);
4111 l_success_solns NUMBER;
4112 l_Stmts VARCHAR2(2000);
4113
4114
4115 BEGIN
4116 -- trim and use only first character of the delimiter
4117 l_delim := p_delim;
4118 l_delim := RTRIM(l_delim);
4119 l_delim := LTRIM(l_delim);
4120 l_delim := substr(l_delim,1,1);
4121
4122 -- dbms_output.put_line('l_delim='|| l_delim);
4123
4124 -- Extract the Category Name from the Bread Crumb
4125 l_category_name := get_category_name_2(p_category_name, l_delim);
4126
4127 -- Determine the category id for the category name
4128 l_ret_val := Validate_category_Name_2(p_category_name,
4129 l_category_name,
4130 l_category_id, l_delim);
4131
4132 IF ( l_ret_val = ERROR_STATUS)
4133 THEN
4134 raise INVALID_CATEGORY_NAME;
4135 END IF;
4136
4137 --remove this, use encode_text instead
4138 l_gt := '&' || 'gt;';
4139
4140 /*******************************************************
4141 *
4142 * Query OUT All the set_ids matching the criteria
4143 *
4144 *******************************************************/
4145
4146 FND_FILE.PUT_LINE(FND_FILE.LOG, fnd_message.GET_STRING('CS','CS_KB_EXPORT_CAT')|| p_category_name);
4147 FND_FILE.PUT_LINE(FND_FILE.LOG, fnd_message.GET_STRING('CS','CS_KB_EXPORT_STATUS')|| p_sol_status);
4148
4149 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<?xml version="1.0"?>');
4150 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<!DOCTYPE solution_list SYSTEM "cskb_solution.dtd">');
4151
4152 IF (p_sol_status = 'ALL')
4153 THEN
4154 SELECT /*+ index(sc) */ count(a.set_number) INTO l_soln_count
4155 from cs_kb_sets_b a, cs_kb_sets_tl b, cs_kb_set_types_vl c,
4156 cs_kb_visibilities_vl v, cs_kb_set_categories sc
4157 where a.set_type_id = c.set_type_id
4158 and a.set_id = sc.set_id and sc.category_id = l_category_id
4159 and a.set_id = b.set_id and b.language = userenv('LANG')
4160 and a.visibility_id = v.visibility_id
4161 and a.status <> 'OBS'
4162 and a.latest_version_flag = 'Y';
4163 ELSE
4164 SELECT count(a.set_number) INTO l_soln_count
4165 from cs_kb_sets_b a, cs_kb_sets_tl b, cs_kb_set_types_vl c,
4166 cs_kb_visibilities_vl v, cs_kb_set_categories sc
4167 where a.set_type_id = c.set_type_id
4168 and a.set_id = sc.set_id and sc.category_id = l_category_id
4169 and a.set_id = b.set_id and b.language = userenv('LANG')
4170 and a.visibility_id = v.visibility_id
4171 and a.status = 'PUB';
4172 END IF;
4173
4174 IF (l_soln_count > 0) THEN
4175 FND_FILE.PUT_LINE(FND_FILE.LOG, fnd_message.GET_STRING('CS','CS_KB_EXPORT_ATTEMPT') || l_soln_count);
4176 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<solution_list>');
4177 END IF;
4178
4179 l_soln_index := 1;
4180 l_success_solns := 0;
4181
4182
4183 IF (p_sol_status = 'ALL')
4184 THEN
4185 open get_solution_info_all(l_category_id);
4186 ELSE
4187 open get_solution_info_pub(l_category_id);
4188 END IF;
4189
4190 loop
4191
4192 IF (p_sol_status = 'ALL')
4193 THEN
4194 fetch get_solution_info_all INTO l_set_ids(l_soln_index), l_set_nos(l_soln_index),
4195 l_set_type_names(l_soln_index),
4196 l_set_vis(l_soln_index),
4197 l_set_names(l_soln_index),
4198 l_set_status(l_soln_index);
4199
4200 exit when get_solution_info_all%NOTFOUND;
4201 ELSE
4202 fetch get_solution_info_pub INTO l_set_ids(l_soln_index), l_set_nos(l_soln_index),
4203 l_set_type_names(l_soln_index),
4204 l_set_vis(l_soln_index),
4205 l_set_names(l_soln_index),
4206 l_set_status(l_soln_index);
4207
4208 exit when get_solution_info_pub%NOTFOUND;
4209
4210 END IF;
4211
4212 -- Need to Validate Solution Content prior to dumping output
4213 -- Check Content of Clob to check if is valid
4214 -- Check content does not exceed 32K without a linebreak
4215 l_Error := 'N';
4216 l_Stmts := null;
4217 FOR StmtCheck IN Get_Stmts (l_set_ids(l_soln_index))LOOP
4218
4219 -- If the Stmt Summary is > 32K then we need to check it for Line Breaks
4220 IF length(StmtCheck.stmt) > l_limit THEN
4221 -- Set the Start Position
4222 l_Start := 1;
4223 -- Use a Loop to validate each 32K chunk of the Statement
4224 LOOP
4225 -- Find the position of the new Line (line break) character working
4226 -- backwards from the end of the stmt 32K chunk
4227 l_Num := instr(substr(StmtCheck.stmt,l_Start, l_Limit) , FND_GLOBAL.newline, -1, 1);
4228 -- Reset the start position to be the previous line break identified in the chunk
4229 l_Start := l_Start + l_Num;
4230
4231 -- If l_Num = 0 Then NO new line characters were found in the chunk
4232 IF l_Num = 0 THEN
4233 -- Validate if this is the last chunk (and that the size < 32K)
4234 -- If this is NOT the last chunk then this will cause an error
4235 -- when using FND_FILE, so through exception and do not process
4236 -- this Solution.
4237 IF l_Start < (length(StmtCheck.stmt) - l_Limit ) THEN
4238 l_Error := 'Y';
4239 IF l_Stmts is null THEN
4240 l_Stmts := StmtCheck.Element_Number;
4241 ELSE
4242 l_Stmts := l_Stmts||', '||StmtCheck.Element_Number;
4243 END IF;
4244
4245 END IF;
4246
4247 END IF;
4248
4249 EXIT WHEN l_Num = 0; -- 0 Signifies the end
4250 END LOOP;
4251
4252 END IF;
4253
4254 END LOOP;
4255
4256 IF l_error = 'Y' THEN
4257 -- If any of the statements failed validation add a message to the Log
4258 FND_MESSAGE.set_name('CS', 'CS_KB_EXPORT_STMT_32K');
4259 FND_MESSAGE.SET_TOKEN(TOKEN => 'SOLN_NUMBER',
4260 VALUE => l_set_nos(l_soln_index) );
4261 FND_MESSAGE.SET_TOKEN(TOKEN => 'STMT_NUMBER',
4262 VALUE => l_stmts );
4263 FND_FILE.PUT_LINE(FND_FILE.LOG, fnd_message.GET);
4264
4265 ELSIF l_error = 'N' THEN
4266
4267 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ' || '<solution>');
4268
4269 FND_FILE.PUT(FND_FILE.OUTPUT, ' ' || '<solution_number>');
4270 FND_FILE.PUT(FND_FILE.OUTPUT, l_set_nos(l_soln_index));
4271 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</solution_number>');
4272
4273 FND_FILE.PUT(FND_FILE.OUTPUT, ' ' || '<solution_type>');
4274 FND_FILE.PUT(FND_FILE.OUTPUT, l_set_type_names(l_soln_index));
4275 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</solution_type>');
4276
4277 FND_FILE.PUT(FND_FILE.OUTPUT, ' ' || '<solution_visibility>');
4278 FND_FILE.PUT(FND_FILE.OUTPUT, l_set_vis(l_soln_index));
4279 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</solution_visibility>');
4280
4281 FND_FILE.PUT(FND_FILE.OUTPUT, ' ' || '<title>');
4282 FND_FILE.PUT(FND_FILE.OUTPUT, Encode_Text(l_set_names(l_soln_index)));
4283 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</title>');
4284
4285 -- output all categories instead of just the one specified by parameter
4286 l_cat_index := 1;
4287 open get_category_ids(l_set_ids(l_soln_index));
4288
4289 loop
4290
4291 fetch get_category_ids INTO l_cat_ids(l_cat_index);
4292 exit when get_category_ids%NOTFOUND;
4293
4294 FND_FILE.PUT(FND_FILE.OUTPUT, ' ' || '<category>');
4295 FND_FILE.PUT(FND_FILE.OUTPUT, encode_text(get_category_full_name(l_cat_ids(l_cat_index), l_delim, true)));
4296 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</category>');
4297
4298 l_cat_index := l_cat_index + 1;
4299
4300 end loop;
4301
4302 close get_category_ids;
4303
4304 -- Start Querying out Products
4305
4306 l_prod_index := 1;
4307 open get_product_info(l_set_ids(l_soln_index));
4308
4309 loop
4310
4311 fetch get_product_info INTO l_prod_names(l_prod_index),l_prod_segments;
4312 exit when get_product_info%NOTFOUND;
4313
4314 FND_FILE.PUT(FND_FILE.OUTPUT, ' ' || '<product segments="'|| Encode_Text(l_prod_segments) ||'">');
4315 FND_FILE.PUT(FND_FILE.OUTPUT, Encode_Text(l_prod_names(l_prod_index)));
4316 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</product>');
4317
4318 l_prod_index := l_prod_index + 1;
4319
4320 end loop;
4321
4322 close get_product_info;
4323
4324 -- End Querying Products
4325
4326 -- Start Querying out Platforms
4327
4328
4329 l_plat_index := 1;
4330 open get_platform_info(l_set_ids(l_soln_index));
4331
4332 loop
4333
4334 fetch get_platform_info INTO l_plat_names(l_plat_index),l_plat_segments;
4335 exit when get_platform_info%NOTFOUND;
4336
4337 FND_FILE.PUT(FND_FILE.OUTPUT, ' ' || '<platform segments="'|| Encode_Text(l_plat_segments) ||'">');
4338 FND_FILE.PUT(FND_FILE.OUTPUT, Encode_Text(l_plat_names(l_plat_index)));
4339 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</platform>');
4340
4341 l_plat_index := l_plat_index + 1;
4342
4343 end loop;
4344
4345 close get_platform_info;
4346
4347
4348 -- End Querying Platforms
4349
4350
4351 -- Start Querying out Statements for this Solution
4352
4353 l_elmt_index := 1;
4354 open get_element_info(l_set_ids(l_soln_index));
4355
4356 loop
4357 fetch get_element_info INTO l_elmt_nos(l_elmt_index), l_elmt_type_names(l_elmt_index),
4358 l_elmt_dists(l_elmt_index),
4359 l_elmt_cts(l_elmt_index),
4360 l_elmt_names(l_elmt_index), l_elmt_descs(l_elmt_index),
4361 l_elmt_stats(l_elmt_index);
4362
4363 exit when get_element_info%NOTFOUND;
4364 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ' || '<statement_link_update>');
4365
4366 FND_FILE.PUT(FND_FILE.OUTPUT, ' ' || '<statement_no_upd>');
4367 FND_FILE.PUT(FND_FILE.OUTPUT, l_elmt_nos(l_elmt_index));
4368 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</statement_no_upd>');
4369
4370 FND_FILE.PUT(FND_FILE.OUTPUT, ' ' || '<statement_distribution_upd>');
4371 FND_FILE.PUT(FND_FILE.OUTPUT, l_elmt_dists(l_elmt_index));
4372 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</statement_distribution_upd>');
4373
4374 -- Determine the Value for the content type from lookups
4375
4376 select meaning
4377 INTO l_elmt_content_type
4378 from cs_lookups
4379 where lookup_type = 'CS_KB_CONTENT_TYPE'
4380 and lookup_code = l_elmt_cts(l_elmt_index);
4381
4382 FND_FILE.PUT(FND_FILE.OUTPUT, ' ' || '<content_type_upd>');
4383 FND_FILE.PUT(FND_FILE.OUTPUT, l_elmt_content_type);
4384 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</content_type_upd>');
4385
4386 FND_FILE.PUT(FND_FILE.OUTPUT, ' ' || '<summary_upd>');
4387 FND_FILE.PUT(FND_FILE.OUTPUT, Encode_Text(l_elmt_names(l_elmt_index)));
4388 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</summary_upd>');
4389
4390 FND_FILE.PUT(FND_FILE.OUTPUT, ' ' || '<detail_upd>');
4391 Write_Clob_To_File(l_elmt_descs(l_elmt_index), FND_FILE.OUTPUT);
4392 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</detail_upd>');
4393
4394 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ' || '</statement_link_update>');
4395
4396 l_elmt_index := l_elmt_index + 1;
4397
4398 end loop;
4399
4400 close get_element_info;
4401
4402 -- End Querying out Statements for this Solution
4403
4404 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ' || '</solution>');
4405
4406 l_success_solns := l_success_solns + 1;
4407
4408 --ELSE -- Solution Contains invalid content for export
4409
4410 END IF; -- Validate Soln Content
4411
4412 l_soln_index := l_soln_index + 1;
4413
4414 end loop; -- For Solutions Loop
4415
4416 IF (p_sol_status = 'ALL')
4417 THEN
4418 close get_solution_info_all;
4419 ELSE
4420 close get_solution_info_pub;
4421 END IF;
4422
4423 IF (l_soln_count > 0) THEN
4424 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</solution_list>');
4425 END IF;
4426
4427 l_soln_index := l_soln_index - 1;
4428
4429 FND_FILE.PUT_LINE(FND_FILE.LOG, fnd_message.GET_STRING('CS','CS_KB_EXPORT_COUNT') || l_success_solns); --l_soln_index);
4430
4431 EXCEPTION
4432
4433 WHEN INVALID_CATEGORY_NAME
4434
4435 THEN
4436
4437 FND_FILE.PUT_LINE(FND_FILE.LOG, fnd_message.GET_STRING('CS','CS_KB_EXPORT_INVCAT') || p_category_name);
4438 FND_FILE.PUT_LINE(FND_FILE.LOG, fnd_message.GET_STRING('CS','CS_KB_EXPORT_RETST')|| ERROR_STATUS);
4439 RETCODE := ERROR_STATUS;
4440
4441 WHEN OTHERS
4442
4443 THEN
4444
4445 FND_FILE.PUT_LINE(FND_FILE.LOG, fnd_message.GET_STRING('CS','CS_KB_EXPORT_UNEXP')||'-'||substrb(sqlerrm,1,100));
4446 FND_FILE.PUT_LINE(FND_FILE.LOG, fnd_message.GET_STRING('CS','CS_KB_EXPORT_RETST')|| ERROR_STATUS);
4447 RETCODE := ERROR_STATUS;
4448
4449 END EXPORT_SOLUTIONS_2;
4450
4451 PROCEDURE Clone_Soln_After_Import
4452 (
4453 x_return_status OUT NOCOPY varchar2,
4454 x_msg_count OUT NOCOPY number,
4455 x_msg_data OUT NOCOPY varchar2,
4456 p_set_flow_name IN VARCHAR2,
4457 p_set_flow_stepcode IN VARCHAR2,
4458 p_set_number IN VARCHAR2
4459 ) IS
4460
4461 l_set_id NUMBER;
4462 l_flow_details_id number := null;
4463 l_return_val NUMBER;
4464
4465 BEGIN
4466 SAVEPOINT Clone_Soln;
4467 -- validate flow info if provided
4468 IF (p_set_flow_name IS NOT NULL) AND (p_set_flow_stepcode IS NOT NULL) THEN
4469 l_return_val := Validate_Flow (
4470 p_flow_name => p_set_flow_name,
4471 p_flow_step => p_set_flow_stepcode,
4472 x_flow_details_id => l_flow_details_id);
4473
4474 IF (l_return_val = ERROR_STATUS)
4475 THEN RAISE INVALID_FLOW;
4476 END IF;
4477 END IF;
4478 IF (l_flow_details_id IS NOT NULL) THEN
4479 l_set_id := CS_KB_SOLUTION_PVT.clone_solution(p_set_number,
4480 'PUB',
4481 l_flow_details_id,
4482 null);
4483 END IF;
4484 x_return_status := fnd_api.g_ret_sts_success;
4485 x_msg_count := 0;
4486 x_msg_data := null;
4487 EXCEPTION
4488 WHEN INVALID_FLOW THEN
4489 ROLLBACK TO Clone_Soln;
4490 x_return_status := FND_API.G_RET_STS_ERROR;
4491 x_msg_count := 1;
4492 x_msg_data := 'Invalid flow: ' || p_set_flow_name ||
4493 ' or step: ' || p_set_flow_stepcode;
4494 WHEN OTHERS THEN
4495 ROLLBACK TO Clone_Soln;
4496 x_msg_data := 'Creating solution: ' || SQLERRM ;
4497 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4498 x_msg_count := 1;
4499 END Clone_Soln_After_Import;
4500 END CS_KNOWLEDGE_AUDIT_PVT;