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