[Home] [Help]
PACKAGE BODY: APPS.CS_KNOWLEDGE_PVT
Source
1 PACKAGE BODY CS_KNOWLEDGE_PVT AS
2 /* $Header: csvkbb.pls 120.4 2006/07/26 22:58:57 klou noship $ */
3
4 /*
5 *
6 * +======================================================================+
7 * | Copyright (c) 1999 Oracle Corporation |
8 * | Redwood Shores, California, USA |
9 * | All rights reserved. |
10 * +======================================================================+
11 *
12 * FILENAME
13 *
14 * PURPOSE
15 * Creates the package body for CS_Knowledge_Pvt
16 * NOTES
17 * Usage: start
18 * HISTORY
19 * 18-OCT-1999 A. WONG Created
20 * 18-DEC-2001 hali Modified
21 * 26-FEB-2003 BAYU Fix bug 2821275 KMR9INT : EXCEPTION ON SEARCH
22 * WITH PARTICULAR KEYWORD STRING
23 * 02-APR-2003 DTIAN Fixed bug 2885439 EMAIL CENTER TO KM SEARCH
24 * INTEGRATION RETURNS NO SEARCH RESULTS
25 * 22-JUL-2003 SPENG For 11.5.10 - Added handling for Exact Phrase
26 * search method in Text query rewrite routines.
27 * Also cleaned up formatting and implementation
28 * of some query rewrite related routine
29 * 08-AUG-2003 klou (LEAK)
30 * 1. Fix security problem that the security check is only
31 * in the text section. Secruity check should embrace the
32 * entire query text.
33 * 2. Fix malformat query text when search text is null.
34 * 26-Aug-2003 MKETTLE Changed to use CS_KB_SOLUTION_PVT.Create_Solution
35 * 26-Aug-2003 klou (TEXTNUM)
36 * 1. Modify Build_Solution_Text_Query to merge the NUMBER
37 * section in the text section.
38 * 27-Aug-2003 klou (SRCHEFF)
39 * 1. Add Process_Frequency_Keyword procedure.
40 * 2. Modify Build_Intermedia_Query to incorporate the use of
41 * keyword frequency profile.
42 * 08-Sep-2003 klou (PRODFILTER)
43 * 1. Filter generic solutions if products are used.
44 * 20-Oct-2003 klou (SMARTSCORE)
45 * 1. Add logic to include score from product/platfor/category filters.
46 * 27-oct-2003 klou
47 * 1. Fix bug 3217731.
48 * 03-Nov-2003 klou
49 * 1. Fix bug 3231550: solution search using statements returns
50 * solutions outside of the security group.
51 * 03-Dec-2003 klou
52 * 1. Fix bug 3209009: handling special character %.
53 * 10-Dec-2003 MKETTLE
54 * Added changes for Create_Set_And_Elements for Public Create api
55 * to make it compliant with security for 11.5.10
56 * 12-Jan-2004 KLOU
57 * Increate varchar size in Remove_Braces, Remove_Parenthesis,
58 * to avoid the error of buffer string too small.
59 * 23-Jan-2004 ALAWANG
60 * 1> Fix bug 3328595
61 * 02-Feb-2004 KLOU (3398078)
62 * 1. Remove extra filtering condition in Find_Sets_Matching.
63 * 18-Feb-2004 KLOU (3341248)
64 * 1. Add implementation for Build_Related_Stmt_Text_Query.
65 * 01-Mar-2004 KLOU (3468629)
66 * 1. Modify Build_Statement_Text_Query such that it does not
67 * call Build_Solution_Text_Query. Instead, it should have
68 * its own implementation.
69 * 06-Apr-2004 KLOU (3534598)
70 * 1. Modify Process_Frequency_Keyword to handle
71 * nls_numeric_characters format.
72 * 05-24-2004 KLOU
73 * 1. Add Build_SR_Text_Query.
74 * 09-02-2004 KLOU
75 * 1. Fix bug 3832320.
76 * 09-04-2004 KLOU
77 * 1. Add implementation for overloaded Build_SR_Text_Query.
78 * 04-05-2005 HMEI
79 * 1. Add exact phrase (" ") syntax processing:
80 * Build_Keyword_Query, Parse_Keywords, Append_Query_Term
81 * 04-05-2005 mkettle Added Find_Sets_Matching2 for bugfix 4304939
82 * 17-May-2005 mkettle Reomved obs ele_eles code
83 * 18-May-2005 MKETTLE Cleanup - removed unused apis and cursors
84 * Apis removed in 115.130:
85 * Move_Element_Order
86 * Change_Element_Assoc
87 * Change_Set_Type_Links
88 * Add_Element_To_Set_Ord
89 * Find_Eles_Matching
90 * Find_Eles_Related
91 * Find_Sets_Related
92 * 29-Jul-2005 speng - R12. Modifed Find_Sets_Matching and
93 * Find_Sets_Matching2 to add an additional set_number
94 * column to the search query select list.
95 * 25-Oct-2005 klou (3983696)
96 * - Fix bug 3983696.
97 * 19-May-2006 klou (5217204)
98 * - Escape % that is prefixed or postfixed with by a symbol that
99 * will be parsed as blank by the text parser. This fix only supports
100 * the out-of-box symbols and has a drawback to ignore wildcard
101 * expansion if customers define a symobol as printjoins character,
102 * e.g. we will escape .% to .\% because the dot (.)
103 * will be parsed as blank by the text parser. But, if it is
104 * printjoin character, the parser will consider it as alphanumeric;
105 * thus, .% is actually meant for searching any word starting with
106 * a dot. With this fix, this feature (non out-of-box) will be
107 * ignored.
108 * 26-JUL-2006 klou (5412688)
109 * - Fix bug 5412688 that was caused by bug fix for 5217204.
110 */
111
112
113 Type WeakCurType IS REF CURSOR;
114
115 --
116 -- Check if required element type is missing for given set type
117 -- returns 'T' if error
118 --
119 FUNCTION Is_Required_Type_Missing(
120 p_set_type_id in number,
121 p_ele_def_tbl in cs_kb_ele_def_tbl_type
122 ) return varchar2 is
123 l_types_tbl cs_kb_number_tbl_type := cs_kb_number_tbl_type();
124 i1 pls_integer;
125 i2 pls_integer;
126 l_count pls_integer;
127 l_type_id number;
128 cursor l_types_csr is
129 select element_type_id
130 from cs_kb_set_ele_types
131 where set_type_id = p_set_type_id
132 and optional_flag = 'N';
133 begin
134 if(p_set_type_id is null or p_ele_def_tbl is null) then
135 goto error_found;
136 end if;
137
138 -- get required types
139 i1 := 1;
140 for recType in l_types_csr loop
141 l_types_tbl.EXTEND;
142 l_types_tbl(i1) := recType.element_type_id;
143 i1 := i1 + 1;
144 end loop;
145
146 -- for each required type
147 i2 := l_types_tbl.FIRST;
148 while i2 is not null loop
149 --for i in l_types_tbl.FIRST..l_types_tbl.LAST loop
150
151 -- if found, check it and continue
152 -- if not found, set error and exit
153 l_count := 0;
154 i1 := p_ele_def_tbl.FIRST;
155 while i1 is not null loop
156
157 if(p_ele_def_tbl(i1).element_id is not null) then
158 select element_type_id into l_type_id
159 from cs_kb_elements_b
160 where element_id = p_ele_def_tbl(i1).element_id;
161 if(l_types_tbl(i2)=l_type_id) then
162 l_count := 1;
163 end if;
164
165 elsif(p_ele_def_tbl(i1).element_type_id is not null) then
166 if(p_ele_def_tbl(i1).element_type_id=l_types_tbl(i2)) then
167 l_count := 1;
168 end if;
169 else
170 fnd_message.set_name('CS','CS_KB_C_MISS_PARAM');
171 return FND_API.G_TRUE;
172 end if;
173 i1 := p_ele_def_tbl.NEXT(i1);
174 end loop;
175 if(l_count = 0) then
176 return FND_API.G_TRUE;
177 end if;
178
179 i2 := l_types_tbl.NEXT(i2);
180 end loop;
181
182
183 return FND_API.G_FALSE;
184
185 <<error_found>>
186 return FND_API.G_TRUE;
187
188 end Is_Required_Type_Missing;
189
190 /*
191 --
192 -- Set fnd.missing char to null
193 --
194 PROCEDURE Miss_Char_To_Null(
195 p_char in varchar2,
196 x_char OUT NOCOPY varchar2
197 ) is
198 begin
199 if(p_char =FND_API.G_MISS_CHAR) then
200 x_char := null;
201 else
202 x_char := p_char;
203 end if;
204 end Miss_Char_To_Null;
205 */
206
207
208 --
209 -- Given a table of num 15,
210 -- return a string of the numbers separated by p_separator
211 --
212 FUNCTION Concat_Ids(
213 p_id_tbl in cs_kb_number_tbl_type,
214 p_separator in varchar2
215 ) return varchar2 is
216 l_str varchar2(1990) := null;
217 i1 pls_integer;
218 begin
219
220 if p_id_tbl is not null and p_id_tbl.COUNT > 0 then
221 i1 := p_id_tbl.FIRST;
222 while i1 is not null loop
223
224 if i1= p_id_tbl.FIRST then
225 l_str := to_char(p_id_tbl(i1));
226 else
227 l_str := l_str|| p_separator|| to_char(p_id_tbl(i1));
228 end if;
229 i1 := p_id_tbl.NEXT(i1);
230 end loop;
231 end if;
232 return l_str;
233
234 end Concat_Ids;
235
236 FUNCTION Concat_Ids(
237 p_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
238 p_separator in varchar2
239 ) return varchar2 is
240 l_str varchar2(1990) := null;
241 i1 pls_integer;
242 begin
243
244 if p_id_tbl is not null and p_id_tbl.COUNT > 0 then
245 i1 := p_id_tbl.FIRST;
246 while i1 is not null loop
247
248 if i1= p_id_tbl.FIRST then
249 l_str := to_char(p_id_tbl(i1));
250 else
251 l_str := l_str|| p_separator|| to_char(p_id_tbl(i1));
252 end if;
253 i1 := p_id_tbl.NEXT(i1);
254 end loop;
255 end if;
256 return l_str;
257
258 end Concat_Ids;
259 --
260 -- Check set_type - element_type is valid
261 -- Valid params:
262 -- (set id, null, ele id, null)
263 -- (set id, null, null, ele type)
264 -- (null, set type, ele id, null)
265 -- (null, set type, null, ele type)
266 --
267 FUNCTION Is_Set_Ele_Type_Valid(
268 p_set_id in number := null,
269 p_set_type_id in number :=null,
270 p_ele_id in number :=null,
271 p_ele_type_id in number :=null
272 ) return varchar2 is
273 l_count pls_integer;
274 begin
275
276 if(p_set_id > 0) then
277 if(p_ele_id > 0) then
278 select count(*) into l_count
279 from cs_kb_set_ele_types se,
280 cs_kb_sets_b s,
281 cs_kb_elements_b e
282 where se.set_type_id = s.set_type_id
283 and se.element_type_id = e.element_type_id
284 and s.set_id = p_set_id
285 and e.element_id = p_ele_id;
286
287 elsif(p_ele_type_id > 0) then
288 select count(*) into l_count
289 from cs_kb_set_ele_types se,
290 cs_kb_sets_b s
291 where se.set_type_id = s.set_type_id
292 and s.set_id = p_set_id
293 and se.element_type_id = p_ele_type_id;
294 end if;
295
296 elsif(p_set_type_id >0) then
297 if(p_ele_id >0) then
298 select count(*) into l_count
299 from cs_kb_set_ele_types se,
300 cs_kb_elements_b e
301 where se.set_type_id = p_set_type_id
302 and e.element_id = p_ele_id
303 and se.element_type_id = e.element_type_id;
304
305 elsif(p_ele_type_id >0) then
306 select count(*) into l_count
307 from cs_kb_set_ele_types se
308 where se.set_type_id = p_set_type_id
309 and se.element_type_id = p_ele_type_id;
310 end if;
311 end if;
312
313 if(l_count >0) then return G_TRUE;
314 else return G_FALSE;
315 end if;
316 end Is_Set_Ele_Type_Valid;
317
318
319 --
320 -- Check if set type exist
321 --
322 FUNCTION Does_Set_Type_Exist(
323 p_set_type_id in number
324 ) return varchar2 is
325 l_count pls_integer;
326 begin
327 select count(*) into l_count
328 from cs_kb_set_types_b
329 where set_type_id = p_set_type_id;
330 if(l_count <1) then return G_FALSE;
331 else return G_TRUE;
332 end if;
333 end Does_Set_Type_Exist;
334
335 --
336 -- Does ele type exist
337 --
338 FUNCTION Does_Element_Type_Exist(
339 p_ele_type_id in number
340 ) return varchar2 is
341 l_count pls_integer;
342 begin
343 -- if type exists
344 select count(*) into l_count
345 from cs_kb_element_types_b
346 where element_type_id = p_ele_type_id;
347 if(l_count <1) then return G_FALSE;
348 else return G_TRUE;
349 end if;
350 end Does_Element_Type_Exist;
351
352 --
353 -- Get sysdate, fnd user and login
354 --
355 PROCEDURE Get_Who(
356 x_sysdate OUT NOCOPY date,
357 x_user_id OUT NOCOPY number,
358 x_login_id OUT NOCOPY number
359 ) is
360 begin
361 x_sysdate := sysdate;
362 x_user_id := fnd_global.user_id;
363 x_login_id := fnd_global.login_id;
364 end Get_Who;
365
366 --
367 -- return ":a1,:a2,:a3"
368 --
369 FUNCTION Bind_Var_String(
370 p_start_num in number,
371 p_size in number
372 ) return varchar2 is
373 i1 pls_integer;
374 l_end_num pls_integer;
375 l_string varchar2(1000):=null;
376 begin
377
378 l_end_num := p_start_num + p_size -1;
379
380 for i1 in p_start_num..l_end_num loop
381 if(i1=p_start_num) then
382 l_string := ':a'||to_char(i1);
383 else
384 l_string := l_string || ',:a'||to_char(i1);
385 end if;
386 end loop;
387 return l_string;
388
389 exception
390 when others then
391 return null;
392 end Bind_Var_String;
393
394
395 --
396 -- Simply check if given elements
397 -- already exist in any larger set.
398 --
399
400 /* New - uses bind variables */
401 FUNCTION Do_Elements_Exist_In_Set (
402 p_ele_id_tbl in cs_kb_number_tbl_type)
403 return varchar2 is
404 l_csr CS_Knowledge_PUB.general_csr_type;
405 l_sid number(15);
406 l_total pls_integer :=0;
407 l_count pls_integer := 0;
408 l_sqlstr1 varchar2(100) :=
409 ' select set_id, count(*) count from cs_kb_set_eles c '||
410 ' where element_id in (';
411 l_eids varchar2(1000);
412 l_sqlstr2 varchar2(100) := ') group by set_id ';
413 l_bind_ids varchar2(1000) := null;
414 l_csr_num integer;
415 i1 pls_integer; -- temporary variable
416 BEGIN
417
418 -- l_eids := Concat_Ids(p_ele_id_tbl, ',');
419 -- if l_eids is null then
420 -- return G_TRUE; --i.e. should abort insert/create set.
421 -- end if;
422 if ( p_ele_id_tbl is null OR p_ele_id_tbl.COUNT<=0 ) then
423 return G_TRUE; --i.e. should abort insert/create set.
424 end if;
425
426 -- convert element_ids into bind vars
427 l_bind_ids := Bind_Var_String(1, p_ele_id_tbl.COUNT);
428
429 -- open cursor
430 l_csr_num := dbms_sql.open_cursor;
431
432 -- parse dynamic sql
433 dbms_sql.parse(l_csr_num,
434 l_sqlstr1 || l_bind_ids || l_sqlstr2,
435 dbms_sql.NATIVE);
436
437 -- define return columns from dynamic sql cursor select
438 dbms_sql.define_column(l_csr_num, 1, l_sid);
439 dbms_sql.define_column(l_csr_num, 2, l_count);
440
441 -- Bind element_ids to bind variables in dynamic sql
442 if(p_ele_id_tbl is not null and p_ele_id_tbl.COUNT>0) then
443 for i in 1..p_ele_id_tbl.COUNT loop
444 dbms_sql.bind_variable(l_csr_num, ':a'||to_char(i), p_ele_id_tbl(i));
445 end loop;
446 end if;
447
448 -- Execute dynamic sql
449 i1 := dbms_sql.execute(l_csr_num);
450
451 l_total := 0;
452 WHILE( dbms_sql.fetch_rows(l_csr_num)>0)
453 LOOP
454 dbms_sql.column_value(l_csr_num, 1, l_sid);
455 dbms_sql.column_value(l_csr_num, 2, l_count);
456 if(l_count >= p_ele_id_tbl.COUNT) then
457 l_total := l_total + 1;
458 end if;
459 END LOOP;
460 dbms_sql.close_cursor(l_csr_num);
461
462
463 -- OPEN l_csr FOR l_sqlstr1 || l_eids || l_sqlstr2;
464 -- LOOP
465 -- FETCH l_csr INTO l_sid, l_count;
466 -- EXIT when l_csr%NOTFOUND;
467 -- if(l_count >= p_ele_id_tbl.COUNT) then
468 -- l_total := l_total + 1;
469 -- end if;
470 -- END LOOP;
471
472 -- CLOSE l_csr;
473
474 if(l_total<1) then --set not exist yet
475 return G_FALSE;
476 else
477 return G_TRUE;
478 end if;
479
480
481 END Do_Elements_Exist_In_Set;
482
483
484 --
485 -- Given table of object_code and select_ids
486 -- return table of select name in sel_name_tbl
487 -- return null in the entry if cannot find specified object
488 -- return OKAY_STATUS if okay, ERROR_STATUS if error
489 --
490 FUNCTION Get_External_Obj_Names(
491 p_obj_code_tbl in jtf_varchar2_table_100,
492 p_sel_id_tbl in jtf_varchar2_table_100,
493 p_sel_name_tbl OUT NOCOPY jtf_varchar2_table_1000
494 ) return number is
495 l_sel_name_tbl jtf_varchar2_table_1000
496 := jtf_varchar2_table_1000();
497 l_query varchar2(1000);
498 l_csr WeakCurType;
499 l_id varchar2(300);
500 l_name varchar2(1000);
501 i1 pls_integer;
502 cursor l_jtfobj_csr(c_objcode in varchar2) is
503 select select_id, select_name, from_table, where_clause
504 from jtf_objects_vl
505 where object_code = c_objcode;
506 l_jtfobj_rec l_jtfobj_csr%ROWTYPE;
507 begin
508 -- Check params
509 if(p_obj_code_tbl is null or p_sel_id_tbl is null or
510 p_obj_code_tbl.COUNT<=0 or
511 p_sel_id_tbl.COUNT < p_obj_code_tbl.COUNT) then
512 return ERROR_STATUS;
513 end if;
514
515 l_sel_name_tbl.EXTEND(p_obj_code_tbl.COUNT);
516 i1 := p_obj_code_tbl.FIRST;
517 while i1 is not null loop
518 --for i1 in p_obj_code_tbl.FIRST..p_obj_code_tbl.LAST loop
519
520 --select jtf object definition
521 open l_jtfobj_csr(p_obj_code_tbl(i1));
522 fetch l_jtfobj_csr into l_jtfobj_rec;
523 close l_jtfobj_csr;
524
525 --construct query
526 l_query := 'select distinct ' || l_jtfobj_rec.select_id || ', '||
527 l_jtfobj_rec.select_name || ' from ' ||
528 l_jtfobj_rec.from_table || ' where '||
529 l_jtfobj_rec.select_id || '=:1';
530
531 --||FND_GLOBAL.local_chr(39)||p_sel_id_tbl(i1) || FND_GLOBAL.local_chr(39);
532
533 if(l_jtfobj_rec.where_clause is not null) then
534 l_query := l_query || ' and '||l_jtfobj_rec.where_clause;
535 end if;
536
537 --query name where id = given id
538 open l_csr for l_query using p_sel_id_tbl(i1);
539 fetch l_csr into l_id, l_name;
540 close l_csr;
541 /*
542 execute immediate l_query
543 into l_id, l_name
544 using p_sel_id_tbl(i1);
545 */
546 --add name to table
547 l_sel_name_tbl(i1) := l_name;
548
549 i1 := p_obj_code_tbl.NEXT(i1);
550 end loop;
551 p_sel_name_tbl := l_sel_name_tbl;
552 --dbms_output.put_line(to_char(p_sel_name_tbl.COUNT));
553 return OKAY_STATUS;
554
555 end Get_External_Obj_Names;
556
557 --
558 -- Add external link
559 --
560 PROCEDURE Add_External_Links(
561 p_api_version in number,
562 p_init_msg_list in varchar2, -- := FND_API.G_FALSE,
563 p_commit in varchar2, -- := FND_API.G_FALSE,
564 p_validation_level in number, -- := FND_API.G_VALID_LEVEL_FULL,
565 x_return_status OUT NOCOPY varchar2,
566 x_msg_count OUT NOCOPY number,
567 x_msg_data OUT NOCOPY varchar2,
568 p_usage_code in varchar2,
569 p_id in number,
570 p_object_code in varchar2,
571 p_other_id_tbl in cs_kb_number_tbl_type,
572 p_other_code_tbl in cs_kb_varchar100_tbl_type
573 ) is
574 l_api_name CONSTANT varchar2(30) := 'Add_External_Links';
575 l_api_version CONSTANT number := 1.0;
576 l_ele_type_id number;
577 i1 pls_integer;
578 l_count pls_integer;
579 l_id number;
580 begin
581 savepoint Add_External_Links_Pvt;
582
583 if not FND_API.Compatible_API_Call(
584 l_api_version,
585 p_api_version,
586 l_api_name,
587 G_PKG_NAME) then
588 raise FND_API.G_EXC_UNEXPECTED_ERROR;
589 end if;
590
591 if FND_API.to_Boolean(p_init_msg_list) then
592 FND_MSG_PUB.initialize;
593 end if;
594
595 x_return_status := FND_API.G_RET_STS_SUCCESS;
596 -- -- -- -- begin my code -- -- -- -- --
597
598 -- Check params
599 if(p_usage_code is null or p_id is null or
600 p_object_code is null or
601 p_other_id_tbl is null or p_other_code_tbl is null or
602 p_other_id_tbl.COUNT <> p_other_code_tbl.COUNT) then
603
604 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
605 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
606 fnd_msg_pub.Add;
607 end if;
608 raise FND_API.G_EXC_ERROR;
609 end if;
610
611 i1 := p_other_id_tbl.FIRST;
612 while i1 is not null loop
613
614 if(p_other_id_tbl(i1) is not null or p_other_code_tbl(i1) is not null)
615 then
616
617 if( p_usage_code = 'CS_KB_SET' ) then
618 l_id := CS_KB_SET_LINKS_PKG.Create_Set_Link(
619 null, --link type
620 p_object_code,
621 p_id,
622 p_other_id_tbl(i1));
623
624 -- elsif(p_usage_code = 'CS_KB_SET_TYPE' ) then
625 -- l_id := CS_KB_SET_TYPE_LINKS_PKG.Create_Set_Type_Link(
626 -- null, --link type
627 -- p_object_code,
628 -- p_id,
629 -- p_other_id_tbl(i1),
630 -- p_other_code_tbl(i1));
631 elsif(p_usage_code = 'CS_KB_ELEMENT' ) then
632 l_id := CS_KB_ELEMENT_LINKS_PKG.Create_Element_Link(
633 null, --link type
634 p_object_code,
635 p_id,
636 p_other_id_tbl(i1));
637
638 elsif(p_usage_code = 'CS_KB_ELEMENT_TYPE' ) then
639 l_id := CS_KB_ELE_TYPE_LINKS_PKG.Create_Element_Type_Link(
640 null, --link type
641 p_object_code,
642 p_id,
643 p_other_id_tbl(i1),
644 p_other_code_tbl(i1));
645 end if;
646 end if;
647
648 if(not l_id>0) then
649 raise FND_API.G_EXC_UNEXPECTED_ERROR;
650 end if;
651
652 i1 := p_other_id_tbl.NEXT(i1);
653 end loop;
654
655
656 -- -- -- -- end of code -- -- --
657
658 IF FND_API.To_Boolean( p_commit ) THEN
659 COMMIT WORK;
660 END IF;
661
662 -- Standard call to get message count. if count=1, get message info.
663 FND_MSG_PUB.Count_And_Get(
664 p_count => x_msg_count,
665 p_data => x_msg_data );
666
667 EXCEPTION
668 WHEN FND_API.G_EXC_ERROR THEN
669 ROLLBACK TO Add_External_Links_PVT;
670 x_return_status := FND_API.G_RET_STS_ERROR ;
671 FND_MSG_PUB.Count_And_Get(
672 p_count => x_msg_count,
673 p_data => x_msg_data );
674 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
675 ROLLBACK TO Add_External_Links_PVT;
676 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
677 FND_MSG_PUB.Count_And_Get(
678 p_count => x_msg_count,
679 p_data => x_msg_data);
680 WHEN OTHERS THEN
681 ROLLBACK TO Add_External_Links_PVT;
682 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
683 IF FND_MSG_PUB.Check_Msg_Level
684 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
685 FND_MSG_PUB.Add_Exc_Msg(
686 G_PKG_NAME,
687 l_api_name);
688 END IF;
689 FND_MSG_PUB.Count_And_Get(
690 p_count => x_msg_count,
691 p_data => x_msg_data);
692
693 end Add_External_Links;
694
695
696 --
697 -- Delete or update rows in Ele Type Links table
698 -- Given linkids, new ele type ids.
699 -- If new id <= 0, remove link
700 --
701 PROCEDURE Change_Ele_Type_Links(
702 p_api_version in number,
703 p_init_msg_list in varchar2 := FND_API.G_FALSE,
704 p_commit in varchar2 := FND_API.G_FALSE,
705 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
706 x_return_status OUT NOCOPY varchar2,
707 x_msg_count OUT NOCOPY number,
708 x_msg_data OUT NOCOPY varchar2,
709 p_link_id_tbl in cs_kb_number_tbl_type,
710 p_ele_type_id_tbl in cs_kb_number_tbl_type
711 )is
712 l_api_name CONSTANT varchar2(30) := 'Change_Ele_Type_Links';
713 l_api_version CONSTANT number := 1.0;
714 l_ele_type_id number;
715 i1 pls_integer;
716 l_count pls_integer;
717 begin
718 savepoint Change_Ele_Type_Links_PVT;
719
720 if not FND_API.Compatible_API_Call(
721 l_api_version,
722 p_api_version,
723 l_api_name,
724 G_PKG_NAME) then
725 raise FND_API.G_EXC_UNEXPECTED_ERROR;
726 end if;
727
728 if FND_API.to_Boolean(p_init_msg_list) then
729 FND_MSG_PUB.initialize;
730 end if;
731
732 x_return_status := FND_API.G_RET_STS_SUCCESS;
733 -- -- -- -- begin my code -- -- -- -- --
734
735 -- Check params
736 if(p_link_id_tbl is null or p_ele_type_id_tbl is null or
737 p_link_id_tbl.COUNT =0 or p_ele_type_id_tbl.COUNT =0 or
738 p_ele_type_id_tbl.COUNT < p_link_id_tbl.COUNT) then
739
740 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
741 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
742 fnd_msg_pub.Add;
743 end if;
744 raise FND_API.G_EXC_ERROR;
745
746 end if;
747
748 i1 := p_link_id_tbl.FIRST;
749 while i1 is not null loop
750 l_ele_type_id := p_ele_type_id_tbl(i1);
751
752 if(p_link_id_tbl(i1) is not null) then
753 if(l_ele_type_id is not null and l_ele_type_id>0) then
754 select count(*) into l_count
755 from cs_kb_element_types_b
756 where element_type_id = l_ele_type_id;
757 if(l_count<1) then
758
759 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
760 fnd_message.set_name('CS', 'CS_KB_C_INVALID_ELE_TYPE_ID');
761 fnd_msg_pub.Add;
762 end if;
763
764 raise FND_API.G_EXC_ERROR;
765 end if;
766
767 update cs_kb_ele_type_links set
768 element_type_id = l_ele_type_id
769 where link_id = p_link_id_tbl(i1);
770 else
771 delete from cs_kb_ele_type_links
772 where link_id = p_link_id_tbl(i1);
773 end if;
774 end if;
775 i1 := p_link_id_tbl.NEXT(i1);
776 end loop;
777
778 -- -- -- -- end of code -- -- --
779
780 IF FND_API.To_Boolean( p_commit ) THEN
781 COMMIT WORK;
782 END IF;
783
784 -- Standard call to get message count. if count=1, get message info.
785 FND_MSG_PUB.Count_And_Get(
786 p_count => x_msg_count,
787 p_data => x_msg_data );
788
789 EXCEPTION
790 WHEN FND_API.G_EXC_ERROR THEN
791 ROLLBACK TO Change_Ele_Type_Links_PVT;
792 x_return_status := FND_API.G_RET_STS_ERROR ;
793 FND_MSG_PUB.Count_And_Get(
794 p_count => x_msg_count,
795 p_data => x_msg_data );
796 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
797 ROLLBACK TO Change_Ele_Type_Links_PVT;
798 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
799 FND_MSG_PUB.Count_And_Get(
800 p_count => x_msg_count,
801 p_data => x_msg_data);
802 WHEN OTHERS THEN
803 ROLLBACK TO Change_Ele_Type_Links_PVT;
804 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
805 IF FND_MSG_PUB.Check_Msg_Level
806 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
807 FND_MSG_PUB.Add_Exc_Msg(
808 G_PKG_NAME,
809 l_api_name);
810 END IF;
811 FND_MSG_PUB.Count_And_Get(
812 p_count => x_msg_count,
813 p_data => x_msg_data);
814
815 end Change_Ele_Type_Links;
816
817 --
818 -- Delete or update rows in Set Links table
819 -- Given linkids, new setids.
820 -- If new set id <= 0, remove link
821 --
822 PROCEDURE Change_Set_Links(
823 p_api_version in number,
824 p_init_msg_list in varchar2, -- := FND_API.G_FALSE,
825 p_commit in varchar2, -- := FND_API.G_FALSE,
826 p_validation_level in number, -- := FND_API.G_VALID_LEVEL_FULL,
827 x_return_status OUT NOCOPY varchar2,
828 x_msg_count OUT NOCOPY number,
829 x_msg_data OUT NOCOPY varchar2,
830 p_link_id_tbl in cs_kb_number_tbl_type,
831 p_set_id_tbl in cs_kb_number_tbl_type
832 )is
833 l_api_name CONSTANT varchar2(30) := 'Change_Set_Links';
834 l_api_version CONSTANT number := 1.0;
835 l_set_id number;
836 i1 pls_integer;
837 l_count pls_integer;
838 begin
839 savepoint Change_Set_Links_PVT;
840
841 if not FND_API.Compatible_API_Call(
842 l_api_version,
843 p_api_version,
844 l_api_name,
845 G_PKG_NAME) then
846 raise FND_API.G_EXC_UNEXPECTED_ERROR;
847 end if;
848
849 if FND_API.to_Boolean(p_init_msg_list) then
850 FND_MSG_PUB.initialize;
851 end if;
852
853 x_return_status := FND_API.G_RET_STS_SUCCESS;
854 -- -- -- -- begin my code -- -- -- -- --
855
856 -- Check params
857 if(p_link_id_tbl is null or p_set_id_tbl is null or
858 p_link_id_tbl.COUNT =0 or p_set_id_tbl.COUNT =0 or
859 p_set_id_tbl.COUNT < p_link_id_tbl.COUNT) then
860
861 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
862 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
863 fnd_msg_pub.Add;
864 end if;
865 raise FND_API.G_EXC_ERROR;
866 end if;
867
868 i1 := p_link_id_tbl.FIRST;
869 while i1 is not null loop
870 l_set_id := p_set_id_tbl(i1);
871
872 if(p_link_id_tbl(i1) is not null) then
873 if(l_set_id is not null and l_set_id>0) then
874 select count(*) into l_count
875 from cs_kb_sets_b
876 where set_id = l_set_id;
877 if(l_count<1) then
878
879 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
880 fnd_message.set_name('CS', 'CS_KB_C_INVALID_SET_ID');
881 fnd_msg_pub.Add;
882 end if;
883 raise FND_API.G_EXC_ERROR;
884 end if;
885
886 update cs_kb_set_links set
887 set_id = l_set_id
888 where link_id = p_link_id_tbl(i1);
889 else
890 delete from cs_kb_set_links
891 where link_id = p_link_id_tbl(i1);
892 end if;
893 end if;
894 i1 := p_link_id_tbl.NEXT(i1);
895 end loop;
896
897 -- -- -- -- end of code -- -- --
898
899 IF FND_API.To_Boolean( p_commit ) THEN
900 COMMIT WORK;
901 END IF;
902
903 -- Standard call to get message count. if count=1, get message info.
904 FND_MSG_PUB.Count_And_Get(
905 p_count => x_msg_count,
906 p_data => x_msg_data );
907
908 EXCEPTION
909 WHEN FND_API.G_EXC_ERROR THEN
910 ROLLBACK TO Change_Set_Links_PVT;
911 x_return_status := FND_API.G_RET_STS_ERROR ;
912 FND_MSG_PUB.Count_And_Get(
913 p_count => x_msg_count,
914 p_data => x_msg_data );
915 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
916 ROLLBACK TO Change_Set_Links_PVT;
917 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
918 FND_MSG_PUB.Count_And_Get(
919 p_count => x_msg_count,
920 p_data => x_msg_data);
921 WHEN OTHERS THEN
922 ROLLBACK TO Change_Set_Links_PVT;
923 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
924 IF FND_MSG_PUB.Check_Msg_Level
925 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
926 FND_MSG_PUB.Add_Exc_Msg(
927 G_PKG_NAME,
928 l_api_name);
929 END IF;
930 FND_MSG_PUB.Count_And_Get(
931 p_count => x_msg_count,
932 p_data => x_msg_data);
933
934 end Change_Set_Links;
935
936 --
937 -- Delete link to set or change link to new element id
938 --
939 PROCEDURE Change_Element_To_Sets(
940 p_api_version in number,
941 p_init_msg_list in varchar2, -- := FND_API.G_FALSE,
942 p_commit in varchar2, -- := FND_API.G_FALSE,
943 p_validation_level in number, -- := FND_API.G_VALID_LEVEL_FULL,
944 x_return_status OUT NOCOPY varchar2,
945 x_msg_count OUT NOCOPY number,
946 x_msg_data OUT NOCOPY varchar2,
947 p_element_id in number,
948 p_set_id_tbl in cs_kb_number_tbl_type,
949 p_new_ele_id_tbl in cs_kb_number_tbl_type
950 )is
951 l_api_name CONSTANT varchar2(30) := 'Change_Element_To_Sets';
952 l_api_version CONSTANT number := 1.0;
953 l_element_id number;
954 i1 pls_integer;
955 l_count pls_integer;
956 l_retnum number(5);
957
958 begin
959 savepoint Change_Element_To_Sets_PVT;
960
961 if not FND_API.Compatible_API_Call(
962 l_api_version,
963 p_api_version,
964 l_api_name,
965 G_PKG_NAME) then
966 raise FND_API.G_EXC_UNEXPECTED_ERROR;
967 end if;
968
969 if FND_API.to_Boolean(p_init_msg_list) then
970 FND_MSG_PUB.initialize;
971 end if;
972
973 x_return_status := FND_API.G_RET_STS_SUCCESS;
974 -- -- -- -- begin my code -- -- -- -- --
975
976 -- Check params
977 if(p_set_id_tbl is null or p_new_ele_id_tbl is null or
978 p_set_id_tbl.COUNT =0 or p_new_ele_id_tbl.COUNT =0 or
979 p_new_ele_id_tbl.COUNT < p_set_id_tbl.COUNT) then
980
981 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
982 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
983 fnd_msg_pub.Add;
984 end if;
985
986 raise FND_API.G_EXC_ERROR;
987 end if;
988
989 i1 := p_set_id_tbl.FIRST;
990 while i1 is not null loop
991 l_element_id := p_new_ele_id_tbl(i1);
992
993 if(p_set_id_tbl(i1) is not null) then
994
995 if(l_element_id is not null and l_element_id>0) then
996
997 --valid new element id
998 select count(*) into l_count
999 from cs_kb_elements_b
1000 where element_id = l_element_id;
1001 if(l_count<1) then
1002 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
1003 fnd_message.set_name('CS', 'CS_KB_C_INVALID_ELE_ID');
1004 fnd_msg_pub.Add;
1005 end if;
1006 raise FND_API.G_EXC_ERROR;
1007 end if;
1008
1009 -- new ele and old ele cannot be in same set
1010 select count(*) into l_count
1011 from cs_kb_set_eles
1012 where set_id = p_set_id_tbl(i1)
1013 and element_id = l_element_id;
1014 if(l_count>0) then
1015 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
1016 fnd_message.set_name('CS', 'CS_KB_C_INVALID_ELE_ID');
1017 fnd_msg_pub.Add;
1018 end if;
1019 raise FND_API.G_EXC_ERROR;
1020 end if;
1021
1022 -- add new element, then delete old ele.
1023 -- checking of type compatibility and required types handled by apis
1024
1025 l_retnum := Add_Element_To_Set(
1026 p_ele_id => l_element_id,
1027 p_set_id => p_set_id_tbl(i1));
1028 if(l_retnum <> OKAY_STATUS) then
1029 fnd_msg_pub.Add;
1030 raise FND_API.G_EXC_ERROR;
1031 end if;
1032
1033 l_retnum := Del_Element_From_Set(
1034 p_ele_id => p_element_id,
1035 p_set_id => p_set_id_tbl(i1));
1036 if(l_retnum <> OKAY_STATUS) then
1037 fnd_msg_pub.Add;
1038 raise FND_API.G_EXC_ERROR;
1039 end if;
1040
1041
1042 -- update
1043 --update cs_kb_set_eles set
1044 -- element_id = l_element_id
1045 -- where set_id = p_set_id_tbl(i1);
1046 else
1047 l_retnum := CS_Knowledge_Pvt.Del_Element_From_Set(
1048 p_ele_id => p_element_id,
1049 p_set_id => p_set_id_tbl(i1));
1050 if(l_retnum = ERROR_STATUS) then
1051 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1052 end if;
1053 end if;
1054
1055 end if;
1056
1057 i1 := p_set_id_tbl.NEXT(i1);
1058 end loop;
1059
1060 -- -- -- -- end of code -- -- --
1061
1062 IF FND_API.To_Boolean( p_commit ) THEN
1063 COMMIT WORK;
1064 END IF;
1065
1066 -- Standard call to get message count. if count=1, get message info.
1067 FND_MSG_PUB.Count_And_Get(
1068 p_count => x_msg_count,
1069 p_data => x_msg_data );
1070
1071 EXCEPTION
1072 WHEN FND_API.G_EXC_ERROR THEN
1073 ROLLBACK TO Change_Element_To_Sets_PVT;
1074 x_return_status := FND_API.G_RET_STS_ERROR ;
1075 FND_MSG_PUB.Count_And_Get(
1076 p_count => x_msg_count,
1077 p_data => x_msg_data );
1078 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1079 ROLLBACK TO Change_Element_To_Sets_PVT;
1080 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1081 FND_MSG_PUB.Count_And_Get(
1082 p_count => x_msg_count,
1083 p_data => x_msg_data);
1084 WHEN OTHERS THEN
1085 ROLLBACK TO Change_Element_To_Sets_PVT;
1086 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1087 IF FND_MSG_PUB.Check_Msg_Level
1088 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1089 FND_MSG_PUB.Add_Exc_Msg(
1090 G_PKG_NAME,
1091 l_api_name);
1092 END IF;
1093 FND_MSG_PUB.Count_And_Get(
1094 p_count => x_msg_count,
1095 p_data => x_msg_data);
1096
1097 end Change_Element_To_Sets;
1098
1099 --
1100 -- Delete or update rows in Element Links table
1101 -- Given linkids, new elementids.
1102 -- If new element id <= 0, remove link
1103 --
1104 PROCEDURE Change_Element_Links(
1105 p_api_version in number,
1106 p_init_msg_list in varchar2, -- := FND_API.G_FALSE,
1107 p_commit in varchar2, -- := FND_API.G_FALSE,
1108 p_validation_level in number, -- := FND_API.G_VALID_LEVEL_FULL,
1109 x_return_status OUT NOCOPY varchar2,
1110 x_msg_count OUT NOCOPY number,
1111 x_msg_data OUT NOCOPY varchar2,
1112 p_link_id_tbl in cs_kb_number_tbl_type,
1113 p_element_id_tbl in cs_kb_number_tbl_type
1114 )is
1115 l_api_name CONSTANT varchar2(30) := 'Change_Element_Links';
1116 l_api_version CONSTANT number := 1.0;
1117 l_element_id number;
1118 i1 pls_integer;
1119 l_count pls_integer;
1120 begin
1121 savepoint Change_Element_Links_PVT;
1122
1123 if not FND_API.Compatible_API_Call(
1124 l_api_version,
1125 p_api_version,
1126 l_api_name,
1127 G_PKG_NAME) then
1128 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1129 end if;
1130
1131 if FND_API.to_Boolean(p_init_msg_list) then
1132 FND_MSG_PUB.initialize;
1133 end if;
1134
1135 x_return_status := FND_API.G_RET_STS_SUCCESS;
1136 -- -- -- -- begin my code -- -- -- -- --
1137
1138 -- Check params
1139 if(p_link_id_tbl is null or p_element_id_tbl is null or
1140 p_link_id_tbl.COUNT =0 or p_element_id_tbl.COUNT =0 or
1141 p_element_id_tbl.COUNT < p_link_id_tbl.COUNT) then
1142
1143 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
1144 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
1145 fnd_msg_pub.Add;
1146 end if;
1147 raise FND_API.G_EXC_ERROR;
1148 end if;
1149
1150 i1 := p_link_id_tbl.FIRST;
1151 while i1 is not null loop
1152 l_element_id := p_element_id_tbl(i1);
1153
1154 if( p_link_id_tbl(i1) is not null) then
1155 if(l_element_id is not null and l_element_id>0) then
1156
1157 select count(*) into l_count
1158 from cs_kb_elements_b
1159 where element_id = l_element_id;
1160 if(l_count<1) then
1161 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
1162 fnd_message.set_name('CS', 'CS_KB_C_INVALID_ELE_ID');
1163 fnd_msg_pub.Add;
1164 end if;
1165 raise FND_API.G_EXC_ERROR;
1166 end if;
1167
1168 update cs_kb_element_links set
1169 element_id = l_element_id
1170 where link_id = p_link_id_tbl(i1);
1171 else
1172 delete from cs_kb_element_links
1173 where link_id = p_link_id_tbl(i1);
1174 end if;
1175 end if;
1176 i1 := p_link_id_tbl.NEXT(i1);
1177 end loop;
1178
1179 -- -- -- -- end of code -- -- --
1180
1181 IF FND_API.To_Boolean( p_commit ) THEN
1182 COMMIT WORK;
1183 END IF;
1184
1185 -- Standard call to get message count. if count=1, get message info.
1186 FND_MSG_PUB.Count_And_Get(
1187 p_count => x_msg_count,
1188 p_data => x_msg_data );
1189
1190 EXCEPTION
1191 WHEN FND_API.G_EXC_ERROR THEN
1192 ROLLBACK TO Change_Element_Links_PVT;
1193 x_return_status := FND_API.G_RET_STS_ERROR ;
1194 FND_MSG_PUB.Count_And_Get(
1195 p_count => x_msg_count,
1196 p_data => x_msg_data );
1197 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1198 ROLLBACK TO Change_Element_Links_PVT;
1199 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1200 FND_MSG_PUB.Count_And_Get(
1201 p_count => x_msg_count,
1202 p_data => x_msg_data);
1203 WHEN OTHERS THEN
1204 ROLLBACK TO Change_Element_Links_PVT;
1205 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1206 IF FND_MSG_PUB.Check_Msg_Level
1207 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1208 FND_MSG_PUB.Add_Exc_Msg(
1209 G_PKG_NAME,
1210 l_api_name);
1211 END IF;
1212 FND_MSG_PUB.Count_And_Get(
1213 p_count => x_msg_count,
1214 p_data => x_msg_data);
1215
1216 end Change_Element_Links;
1217
1218 FUNCTION Del_Element_From_Set(
1219 p_ele_id in number,
1220 p_set_id in number,
1221 p_update_sets_b in varchar2
1222 ) return number is
1223 l_date date;
1224 l_user number;
1225 l_login number;
1226
1227
1228 l_retnum number;
1229 l_ele_type_id number;
1230 l_set_type_id number;
1231 l_optional_flag varchar2(1);
1232 l_count pls_integer;
1233
1234 cursor cur_eles( c_sid in number) is
1235 select element_id
1236 from cs_kb_set_eles
1237 where set_id = c_sid;
1238
1239 cursor cur_set is
1240 select s.set_type_id
1241 from cs_kb_sets_vl s
1242 where s.set_id = p_set_id;
1243 l_set_rec cur_set%ROWTYPE;
1244
1245 begin
1246 -- Check params
1247 if( not p_set_id > 0 ) or (not p_ele_id > 0) then
1248 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
1249 goto error_found;
1250 end if;
1251
1252
1253 --if ele is the only required type in set, cannot delete
1254
1255 select element_type_id into l_ele_type_id
1256 from cs_kb_elements_b
1257 where element_id = p_ele_id;
1258
1259 open cur_set;
1260 fetch cur_set into l_set_rec;
1261 close cur_set;
1262
1263 begin
1264
1265 select optional_flag into l_optional_flag
1266 from cs_kb_set_ele_types
1267 where set_type_id = l_set_rec.set_type_id
1268 and element_type_id = l_ele_type_id;
1269
1270 exception
1271 when NO_DATA_FOUND THEN
1272 NULL;
1273 end;
1274
1275
1276
1277
1278
1279
1280 if(l_optional_flag = 'N') then
1281 select count(*) into l_count
1282 from cs_kb_set_eles se, cs_kb_elements_b e
1283 where se.set_id = p_set_id
1284 and se.element_id = e.element_id
1285 and e.element_type_id = l_ele_type_id;
1286 if(l_count <=1) then
1287 fnd_message.set_name('CS', 'CS_KB_C_REQ_TYPE_ERR');
1288 goto error_found;
1289 end if;
1290 end if;
1291
1292 --delete a row in set_eles
1293 delete from cs_kb_set_eles
1294 where element_id = p_ele_id
1295 and set_id = p_set_id;
1296
1297 -- change update date of set
1298 -- and update change_history of set
1299
1300
1301 Get_Who(l_date, l_user, l_login);
1302
1303 if(p_update_sets_b = 'T') then
1304
1305 update cs_kb_sets_b set
1306 last_update_date = l_date,
1307 last_updated_by = l_user,
1308 last_update_login = l_login
1309 where set_id = p_set_id;
1310 end if;
1311
1312 -- touch related sets to update interMedia index
1313 update cs_kb_sets_tl set
1314 positive_assoc_index = 'c',
1315 negative_assoc_index = 'c',
1316 composite_assoc_index = 'c'
1317 where set_id = p_set_id;
1318
1319
1320 return OKAY_STATUS;
1321 <<error_found>>
1322 return ERROR_STATUS;
1323
1324 end Del_Element_From_Set;
1325
1326 -- Add element
1327 FUNCTION Add_Element_To_Set(
1328 p_ele_id in number,
1329 p_set_id in number,
1330 p_assoc_degree in number := CS_Knowledge_PUB.G_POSITIVE_ASSOC,
1331 p_update_sets_b in varchar2
1332 ) return number is
1333 l_count pls_integer;
1334 l_date date;
1335 l_created_by number;
1336 l_login number;
1337 l_order number(15);
1338 cursor cur_eles( c_sid in number) is
1339 select element_id
1340 from cs_kb_set_eles
1341 where set_id = c_sid; /* can add: and element_id <> p_ele_id */
1342 begin
1343
1344 -- Check params
1345 if( not p_set_id > 0 ) or (not p_ele_id > 0) then
1346 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
1347 goto error_found;
1348 end if;
1349
1350 -- check if element exists
1351 select count(*) into l_count
1352 from cs_kb_elements_b
1353 where element_id = p_ele_id;
1354 if(l_count=0) then
1355 fnd_message.set_name('CS', 'CS_KB_C_INVALID_ELE_ID');
1356 goto error_found;
1357 end if;
1358
1359 -- check if row already exists
1360 select count(*) into l_count
1361 from cs_kb_set_eles
1362 where set_id = p_set_id
1363 and element_id = p_ele_id;
1364 if(l_count>0) then
1365 fnd_message.set_name('CS', 'CS_KB_C_ELE_EXIST_ERR');
1366 goto error_found;
1367 end if;
1368
1369 --check set ele type match
1370 if( Is_Set_Ele_Type_Valid(
1371 p_set_id => p_set_id,
1372 p_ele_id => p_ele_id)
1373 = G_FALSE) then
1374 fnd_message.set_name('CS', 'CS_KB_C_INCOMPATIBLE_TYPES');
1375 goto error_found;
1376 end if;
1377
1378 -- prepare data to insert
1379 Get_Who(l_date, l_created_by, l_login);
1380
1381 select max(element_order) into l_order
1382 from cs_kb_set_eles
1383 where set_id = p_set_id;
1384 if( not l_order > 0) then
1385 l_order :=1;
1386 else
1387 l_order := l_order + 1;
1388 end if;
1389
1390
1391 -- insert into set_ele
1392 insert into cs_kb_set_eles (
1393 set_id, element_id, element_order, assoc_degree,
1394 creation_date, created_by,
1395 last_update_date, last_updated_by, last_update_login)
1396 values(
1397 p_set_id, p_ele_id, l_order, p_assoc_degree,
1398 l_date, l_created_by, l_date, l_created_by, l_login);
1399
1400
1401 if(p_update_sets_b = 'T') then
1402
1403 update cs_kb_sets_b set
1404 last_update_date = l_date,
1405 last_updated_by = l_created_by,
1406 last_update_login = l_login
1407 where set_id = p_set_id;
1408 end if;
1409
1410 -- touch related sets to update interMedia index
1411 update cs_kb_sets_tl set
1412 positive_assoc_index = 'c',
1413 negative_assoc_index = 'c',
1414 composite_assoc_index = 'c'
1415 where set_id = p_set_id;
1416
1417 return OKAY_STATUS;
1418
1419 <<error_found>>
1420 return ERROR_STATUS;
1421 end Add_Element_To_Set;
1422
1423
1424 --
1425 -- -- -- Copy from records to objects -- -- -- --
1426 --
1427
1428 PROCEDURE Copy_Eledef_To_Obj(
1429 p_ele_def_rec in CS_Knowledge_PUB.ele_def_rec_type,
1430 x_ele_def_obj OUT NOCOPY cs_kb_ele_def_obj_type
1431 ) is
1432 begin
1433
1434 if(p_ele_def_rec.element_id is null and
1435 p_ele_def_rec.element_type_id is null) then
1436 return;
1437 end if;
1438
1439 x_ele_def_obj := cs_kb_ele_def_obj_type(
1440 p_ele_def_rec.element_id,
1441 p_ele_def_rec.element_type_id,
1442 p_ele_def_rec.name,
1443 p_ele_def_rec.description,
1444 null);
1445
1446 if( p_ele_def_rec.attribute_category is not null) then
1447 x_ele_def_obj.dff_obj := cs_kb_dff_obj_type(
1448 p_ele_def_rec.attribute_category,
1449 p_ele_def_rec.attribute1,
1450 p_ele_def_rec.attribute2,
1451 p_ele_def_rec.attribute3,
1452 p_ele_def_rec.attribute4,
1453 p_ele_def_rec.attribute5,
1454 p_ele_def_rec.attribute6,
1455 p_ele_def_rec.attribute7,
1456 p_ele_def_rec.attribute8,
1457 p_ele_def_rec.attribute9,
1458 p_ele_def_rec.attribute10,
1459 p_ele_def_rec.attribute11,
1460 p_ele_def_rec.attribute12,
1461 p_ele_def_rec.attribute13,
1462 p_ele_def_rec.attribute14,
1463 p_ele_def_rec.attribute15);
1464 end if;
1465 end Copy_Eledef_To_Obj;
1466
1467 --
1468 -- Copy set def from record to object
1469 --
1470 PROCEDURE Copy_Setdef_To_Obj(
1471 p_set_def_rec in CS_Knowledge_PUB.set_def_rec_type,
1472 x_set_def_obj OUT NOCOPY cs_kb_set_def_obj_type
1473 ) is
1474 begin
1475
1476 if(p_set_def_rec.set_id is null and
1477 p_set_def_rec.set_type_id is null) then return; end if;
1478
1479 x_set_def_obj := cs_kb_set_def_obj_type(
1480 p_set_def_rec.set_id,
1481 p_set_def_rec.set_type_id,
1482 p_set_def_rec.name,
1483 p_set_def_rec.description,
1484 p_set_def_rec.status,
1485 null);
1486
1487 if( p_set_def_rec.attribute_category is not null) then
1488 x_set_def_obj.dff_obj := cs_kb_dff_obj_type(
1489 p_set_def_rec.attribute_category,
1490 p_set_def_rec.attribute1,
1491 p_set_def_rec.attribute2,
1492 p_set_def_rec.attribute3,
1493 p_set_def_rec.attribute4,
1494 p_set_def_rec.attribute5,
1495 p_set_def_rec.attribute6,
1496 p_set_def_rec.attribute7,
1497 p_set_def_rec.attribute8,
1498 p_set_def_rec.attribute9,
1499 p_set_def_rec.attribute10,
1500 p_set_def_rec.attribute11,
1501 p_set_def_rec.attribute12,
1502 p_set_def_rec.attribute13,
1503 p_set_def_rec.attribute14,
1504 p_set_def_rec.attribute15);
1505 end if;
1506 end Copy_Setdef_To_Obj;
1507
1508 --
1509 -- -- -- -- Provided in PUB package -- -- -- -- --
1510 --
1511
1512 --
1513 -- Create_Set_And_Elements (1)- Using RECORDs
1514 -- Wrapper on top of (2)
1515 -- Original (Pre 8/03/00) Contributed element ids not passed back
1516 --
1517 PROCEDURE Create_Set_And_Elements(
1518 p_api_version in number,
1519 p_init_msg_list in varchar2 := FND_API.G_FALSE,
1520 p_commit in varchar2 := FND_API.G_FALSE,
1521 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
1522 x_return_status OUT NOCOPY varchar2,
1523 x_msg_count OUT NOCOPY number,
1524 x_msg_data OUT NOCOPY varchar2,
1525 p_set_def_rec in CS_Knowledge_PUB.set_def_rec_type,
1526 p_ele_def_tbl in CS_Knowledge_PUB.ele_def_tbl_type,
1527 -- p_attrval_def_tbl in CS_Knowledge_PUB.attrval_def_tbl_type,
1528 x_set_id OUT NOCOPY number
1529 )is
1530 l_element_id_tbl CS_Knowledge_PUB.number15_tbl_type;
1531 i1 pls_integer;
1532 begin
1533
1534 Create_Set_And_Elements(
1535 p_api_version => p_api_version,
1536 p_init_msg_list => p_init_msg_list,
1537 p_commit => p_commit,
1538 p_validation_level => p_validation_level,
1539 x_return_status => x_return_status,
1540 x_msg_count => x_msg_count,
1541 x_msg_data => x_msg_data,
1542 p_set_def_rec => p_set_def_rec,
1543 p_ele_def_tbl => p_ele_def_tbl,
1544 x_set_id => x_set_id,
1545 x_element_id_tbl => l_element_id_tbl);
1546
1547 end Create_Set_And_Elements;
1548
1549 -- Create_Set_And_Elements (2) - Using RECORDs
1550 -- Wrapper - calls (4)
1551 -- New (Post 8/03/00) Contributed element ids passed back
1552 PROCEDURE Create_Set_And_Elements(
1553 p_api_version in number,
1554 p_init_msg_list in varchar2 := FND_API.G_FALSE,
1555 p_commit in varchar2 := FND_API.G_FALSE,
1556 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
1557 x_return_status OUT NOCOPY varchar2,
1558 x_msg_count OUT NOCOPY number,
1559 x_msg_data OUT NOCOPY varchar2,
1560 p_set_def_rec in CS_Knowledge_PUB.set_def_rec_type,
1561 p_ele_def_tbl in CS_Knowledge_PUB.ele_def_tbl_type,
1562 x_set_id OUT NOCOPY number,
1563 x_element_id_tbl OUT NOCOPY CS_Knowledge_PUB.number15_tbl_type
1564 )
1565 is
1566 l_set_def_obj cs_kb_set_def_obj_type;
1567 l_ele_def_tbl cs_kb_ele_def_tbl_type;
1568 i1 pls_integer;
1569 i2 pls_integer;
1570 l_element_id_tbl cs_kb_number_tbl_type;
1571 l_ele_assoc_tbl cs_kb_number_tbl_type;
1572 begin
1573
1574 if(p_ele_def_tbl is not null) then
1575 l_ele_def_tbl := cs_kb_ele_def_tbl_type();
1576 l_ele_def_tbl.EXTEND(p_ele_def_tbl.COUNT);
1577 i1 := l_ele_def_tbl.FIRST;
1578 while i1 is not null loop
1579 Copy_EleDef_To_Obj(p_ele_def_tbl(i1),
1580 l_ele_def_tbl(i1));
1581 i1 := l_ele_def_tbl.NEXT(i1);
1582 end loop;
1583 end if;
1584
1585 Copy_SetDef_To_Obj(p_set_def_rec, l_set_def_obj);
1586
1587 Create_Set_And_Elements(
1588 p_api_version => p_api_version,
1589 p_init_msg_list => p_init_msg_list,
1590 p_commit => p_commit,
1591 p_validation_level => p_validation_level,
1592 x_return_status => x_return_status,
1593 x_msg_count => x_msg_count,
1594 x_msg_data => x_msg_data,
1595 p_set_def_obj => l_set_def_obj,
1596 p_ele_def_tbl => l_ele_def_tbl,
1597 x_set_id => x_set_id,
1598 x_element_id_tbl => l_element_id_tbl);
1599
1600
1601 if (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1602 then
1603 raise FND_API.G_EXC_ERROR;
1604 end if;
1605
1606 -- Create new element id table out param if it's not there
1607 if ( x_element_id_tbl is null) then
1608 x_element_id_tbl := cs_knowledge_pub.number15_tbl_type();
1609 end if;
1610
1611 -- Copy the resulting element id's from obj to out record
1612 if ( l_element_id_tbl is not null ) then
1613 i2 := l_element_id_tbl.FIRST;
1614 while i2 is not null loop
1615 x_element_id_tbl.EXTEND(1);
1616 x_element_id_tbl(x_element_id_tbl.LAST) := l_element_id_tbl(i2);
1617 i2 := l_element_id_tbl.NEXT(i2);
1618 end loop;
1619 end if;
1620
1621 exception
1622 WHEN FND_API.G_EXC_ERROR THEN
1623 x_return_status := FND_API.G_RET_STS_ERROR ;
1624 FND_MSG_PUB.Count_And_Get(
1625 p_encoded => FND_API.G_FALSE ,
1626 p_count => x_msg_count,
1627 p_data => x_msg_data );
1628 end Create_Set_And_Elements;
1629
1630
1631 --
1632 -- Create_Set_And_Elements (3) - Using OBJECTs
1633 -- Wrapper on top of Create_Set_and_elements (4)
1634 -- Original (Pre 8/03/00) Contributed element ids not passed back
1635 --
1636 PROCEDURE Create_Set_And_Elements(
1637 p_api_version in number,
1638 p_init_msg_list in varchar2 := FND_API.G_FALSE,
1639 p_commit in varchar2 := FND_API.G_FALSE,
1640 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
1641 x_return_status OUT NOCOPY varchar2,
1642 x_msg_count OUT NOCOPY number,
1643 x_msg_data OUT NOCOPY varchar2,
1644 p_set_def_obj in cs_kb_set_def_obj_type,
1645 p_ele_def_tbl in cs_kb_ele_def_tbl_type,
1646 -- p_attrval_def_tbl in cs_kb_attrval_def_tbl_type :=null,
1647 p_ele_assoc_tbl in cs_kb_number_tbl_type :=null,
1648 x_set_id OUT NOCOPY number
1649 )is
1650 l_api_name CONSTANT varchar2(30) := 'Create_Set_And_Elements';
1651 l_api_version CONSTANT number := 1.0;
1652
1653 l_ele_id number(15);
1654 l_rowid varchar2(30);
1655 l_date date;
1656 l_created_by number;
1657 l_login number;
1658 j pls_integer;
1659 i1 pls_integer;
1660 l_ele_id_tbl cs_kb_number_tbl_type := cs_kb_number_tbl_type();
1661
1662 begin
1663 --if ele tab valid
1664 --insert elements if any
1665
1666 -- if set type exists
1667 -- if empty set or check_set_exists = N
1668 -- insert new set
1669 -- insert new set_ele 's
1670 -- insert new set_attrval 's
1671 -- insert new ele_ele for all eles in the set if not exists
1672 -- incr_ele_ele for all possible links
1673
1674 savepoint Create_Set_And_Elements_PVT;
1675
1676 if not FND_API.Compatible_API_Call(
1677 l_api_version,
1678 p_api_version,
1679 l_api_name,
1680 G_PKG_NAME) then
1681 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1682 end if;
1683
1684 if FND_API.to_Boolean(p_init_msg_list) then
1685 FND_MSG_PUB.initialize;
1686 end if;
1687
1688 x_return_status := FND_API.G_RET_STS_SUCCESS;
1689
1690
1691 -- -- -- -- begin my code -- -- -- -- --
1692
1693 Create_Set_And_Elements(
1694 p_api_version => p_api_version,
1695 p_init_msg_list => p_init_msg_list,
1696 p_commit => p_commit,
1697 p_validation_level => p_validation_level,
1698 x_return_status => x_return_status,
1699 x_msg_count => x_msg_count,
1700 x_msg_data => x_msg_data,
1701 p_set_def_obj => p_set_def_obj,
1702 p_ele_def_tbl => p_ele_def_tbl,
1703 p_ele_assoc_tbl => p_ele_assoc_tbl,
1704 x_set_id => x_set_id,
1705 x_element_id_tbl => l_ele_id_tbl);
1706
1707
1708 -- -- -- -- end of code -- -- --
1709
1710 IF FND_API.To_Boolean( p_commit ) THEN
1711 COMMIT WORK;
1712 END IF;
1713
1714 -- Standard call to get message count. if count=1, get message info.
1715 FND_MSG_PUB.Count_And_Get(
1716 p_count => x_msg_count,
1717 p_data => x_msg_data );
1718
1719 EXCEPTION
1720 WHEN FND_API.G_EXC_ERROR THEN
1721 ROLLBACK TO Create_Set_And_Elements_PVT;
1722 x_return_status := FND_API.G_RET_STS_ERROR ;
1723 FND_MSG_PUB.Count_And_Get(
1724 p_count => x_msg_count,
1725 p_data => x_msg_data );
1726 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1727 ROLLBACK TO Create_Set_And_Elements_PVT;
1728 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1729 FND_MSG_PUB.Count_And_Get(
1730 p_count => x_msg_count,
1731 p_data => x_msg_data);
1732 WHEN OTHERS THEN
1733 ROLLBACK TO Create_Set_And_Elements_PVT;
1734 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1735 IF FND_MSG_PUB.Check_Msg_Level
1736 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1737 FND_MSG_PUB.Add_Exc_Msg(
1738 G_PKG_NAME,
1739 l_api_name);
1740 END IF;
1741 FND_MSG_PUB.Count_And_Get(
1742 p_count => x_msg_count,
1743 p_data => x_msg_data);
1744 end Create_Set_And_Elements;
1745
1746 --
1747 -- Create_Set_And_Elements (4) - Using OBJECTs
1748 --
1749 -- New (Post 8/03/00) Contributed element ids passed back
1750 --
1751 PROCEDURE Create_Set_And_Elements(
1752 p_api_version in number,
1753 p_init_msg_list in varchar2 := FND_API.G_FALSE,
1754 p_commit in varchar2 := FND_API.G_FALSE,
1755 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
1756 x_return_status OUT NOCOPY varchar2,
1757 x_msg_count OUT NOCOPY number,
1758 x_msg_data OUT NOCOPY varchar2,
1759 p_set_def_obj in cs_kb_set_def_obj_type,
1760 p_ele_def_tbl in cs_kb_ele_def_tbl_type,
1761 -- p_attrval_def_tbl in cs_kb_attrval_def_tbl_type :=null,
1762 p_ele_assoc_tbl in cs_kb_number_tbl_type :=null,
1763 x_set_id OUT NOCOPY number,
1764 x_element_id_tbl OUT NOCOPY cs_kb_number_tbl_type
1765 )
1766 IS
1767 l_api_name CONSTANT varchar2(30) := 'Create_Set_And_Elements';
1768 l_api_version CONSTANT number := 1.0;
1769
1770 l_ele_id number(15);
1771 l_rowid varchar2(30);
1772 l_date date;
1773 l_created_by number;
1774 l_login number;
1775 j pls_integer;
1776 i1 pls_integer;
1777 l_user NUMBER := FND_GLOBAL.user_id;
1778 l_user_login NUMBER := FND_GLOBAL.login_id;
1779
1780 CURSOR Get_Defaulted_Category IS
1781 SELECT category_id
1782 FROM CS_KB_SOLN_CATEGORIES_B
1783 WHERE category_id = to_number(fnd_profile.value('CS_KB_CAT_FOR_INT_CREATE_API'));
1784
1785 l_defaulted_category NUMBER;
1786
1787 CURSOR Get_Profile_Name IS
1788 SELECT user_profile_option_name
1789 FROM FND_PROFILE_OPTIONS_VL
1790 WHERE profile_option_name = 'CS_KB_CAT_FOR_INT_CREATE_API';
1791
1792 l_profile_name VARCHAR2(240);
1793
1794
1795 CURSOR Check_Element (v_ele_id NUMBER) IS
1796 SELECT count(*)
1797 FROM CS_KB_ELEMENTS_B
1798 WHERE Element_Id = v_ele_id
1799 AND status = 'PUBLISHED';
1800
1801 l_ele_check NUMBER;
1802
1803 begin
1804 --if ele tab valid
1805 --insert elements if any
1806
1807 -- if set type exists
1808 -- if empty set or check_set_exists = N
1809 -- insert new set
1810 -- insert new set_ele 's
1811 -- insert new set_attrval 's
1812 -- insert new ele_ele for all eles in the set if not exists
1813 -- incr_ele_ele for all possible links
1814
1815
1816 savepoint Create_Set_And_Elements_PVT;
1817
1818 if not FND_API.Compatible_API_Call(
1819 l_api_version,
1820 p_api_version,
1821 l_api_name,
1822 G_PKG_NAME) then
1823 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1824 end if;
1825
1826 if FND_API.to_Boolean(p_init_msg_list) then
1827 FND_MSG_PUB.initialize;
1828 end if;
1829
1830 x_return_status := FND_API.G_RET_STS_SUCCESS;
1831
1832
1833 -- -- -- -- begin my code -- -- -- -- --
1834
1835 -- Create output element id list
1836 x_element_id_tbl := cs_kb_number_tbl_type();
1837
1838 -- Check params
1839 if(p_set_def_obj.set_type_id is null or
1840 p_set_def_obj.set_type_id <=0 or
1841 p_set_def_obj.name is null) then
1842
1843 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
1844 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
1845 fnd_msg_pub.Add;
1846 end if;
1847 raise FND_API.G_EXC_ERROR;
1848
1849 end if;
1850
1851 IF(p_set_def_obj.status is not null AND
1852 p_set_def_obj.status <> 'SAV' )THEN
1853
1854 -- This Create api only creates Draft Solutions
1855 IF fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
1856
1857 fnd_message.set_name('CS', 'CS_KB_INV_API_STATUS');
1858 fnd_msg_pub.Add;
1859
1860 END IF;
1861 RAISE FND_API.G_EXC_ERROR;
1862
1863 END IF;
1864
1865 OPEN Get_Defaulted_Category;
1866 FETCH Get_Defaulted_Category INTO l_defaulted_category;
1867 CLOSE Get_Defaulted_Category;
1868
1869 IF l_defaulted_category IS NULL THEN
1870 IF fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
1871
1872 OPEN Get_Profile_Name;
1873 FETCH Get_Profile_Name INTO l_profile_name;
1874 CLOSE Get_Profile_Name;
1875
1876 fnd_message.set_name('CS', 'CS_KB_INV_CAT_PROFILE');
1877 FND_MESSAGE.SET_TOKEN(TOKEN => 'PROFILE',
1878 VALUE => l_profile_name,
1879 TRANSLATE => true);
1880 fnd_msg_pub.Add;
1881
1882 END IF;
1883 RAISE FND_API.G_EXC_ERROR;
1884 END IF;
1885
1886 if(p_ele_def_tbl is null or p_ele_def_tbl.COUNT <= 0) then
1887
1888 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
1889 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
1890 fnd_msg_pub.Add;
1891 end if;
1892 raise FND_API.G_EXC_ERROR;
1893
1894 end if;
1895
1896 CS_Knowledge_PVT.Get_Who(l_date, l_created_by, l_login);
1897
1898 --Process ele_tab
1899 x_element_id_tbl.EXTEND(p_ele_def_tbl.COUNT);
1900
1901 j:=1;
1902 i1 := p_ele_def_tbl.FIRST;
1903 while i1 is not null loop
1904
1905 if p_ele_def_tbl(i1).element_id is null then
1906 --create element and store ele_id
1907
1908 l_ele_id := CS_KB_ELEMENTS_AUDIT_PKG.Create_Element(
1909 p_element_type_id => p_ele_def_tbl(i1).element_type_id,
1910 p_desc => p_ele_def_tbl(i1).description,
1911 p_name => p_ele_def_tbl(i1).name,
1912 p_status => 'DRAFT',
1913 p_access_level => 1000,
1914 p_attribute_category => p_ele_def_tbl(i1).dff_obj.attribute_category,
1915 p_attribute1 => p_ele_def_tbl(i1).dff_obj.attribute1,
1916 p_attribute2 => p_ele_def_tbl(i1).dff_obj.attribute2,
1917 p_attribute3 => p_ele_def_tbl(i1).dff_obj.attribute3,
1918 p_attribute4 => p_ele_def_tbl(i1).dff_obj.attribute4,
1919 p_attribute5 => p_ele_def_tbl(i1).dff_obj.attribute5,
1920 p_attribute6 => p_ele_def_tbl(i1).dff_obj.attribute6,
1921 p_attribute7 => p_ele_def_tbl(i1).dff_obj.attribute7,
1922 p_attribute8 => p_ele_def_tbl(i1).dff_obj.attribute8,
1923 p_attribute9 => p_ele_def_tbl(i1).dff_obj.attribute9,
1924 p_attribute10 => p_ele_def_tbl(i1).dff_obj.attribute10,
1925 p_attribute11 => p_ele_def_tbl(i1).dff_obj.attribute11,
1926 p_attribute12 => p_ele_def_tbl(i1).dff_obj.attribute12,
1927 p_attribute13 => p_ele_def_tbl(i1).dff_obj.attribute13,
1928 p_attribute14 => p_ele_def_tbl(i1).dff_obj.attribute14,
1929 p_attribute15 => p_ele_def_tbl(i1).dff_obj.attribute15);
1930
1931 if not (l_ele_id > 0)
1932 then
1933 raise FND_API.G_EXC_ERROR;
1934 else
1935 x_element_id_tbl(j) := l_ele_id;
1936 end if;
1937
1938 else --if ele id not null
1939
1940 -- Validate the Element Id to ensure the value passed is a valid element
1941 OPEN Check_Element( p_ele_def_tbl(i1).element_id );
1942 FETCH Check_Element INTO l_ele_check;
1943 CLOSE Check_Element;
1944
1945 IF l_ele_check = 1 THEN
1946 x_element_id_tbl(j) := p_ele_def_tbl(i1).element_id;
1947 ELSE
1948 FND_MESSAGE.set_name('CS', 'CS_KB_INV_API_ELE_ID');
1949 FND_MSG_PUB.Add;
1950 RAISE FND_API.G_EXC_ERROR;
1951 END IF;
1952
1953 end if;
1954
1955 j := j+1;
1956 i1 := p_ele_def_tbl.NEXT(i1);
1957 end loop;
1958
1959 Create_Set(
1960 p_api_version => p_api_version,
1961 p_init_msg_list => p_init_msg_list,
1962 p_commit => p_commit,
1963 p_validation_level => p_validation_level,
1964 x_return_status => x_return_status,
1965 x_msg_count => x_msg_count,
1966 x_msg_data => x_msg_data,
1967 p_set_def_obj => p_set_def_obj,
1968 p_ele_id_tbl => x_element_id_tbl,
1969 p_ele_assoc_tbl => p_ele_assoc_tbl,
1970 x_set_id => x_set_id);
1971
1972 if not (x_set_id > 0)
1973 then
1974 raise FND_API.G_EXC_ERROR;
1975 ELSE --Create_Set was successful
1976
1977 INSERT INTO CS_KB_SET_CATEGORIES (
1978 SET_ID,
1979 CATEGORY_ID,
1980 CREATION_DATE,
1981 CREATED_BY,
1982 LAST_UPDATE_DATE,
1983 LAST_UPDATED_BY,
1984 LAST_UPDATE_LOGIN )
1985 VALUES (x_set_id,
1986 l_defaulted_category,
1987 sysdate,
1988 l_user,
1989 sysdate,
1990 l_user,
1991 l_user_login);
1992
1993 end if;
1994
1995 -- -- -- -- end of code -- -- --
1996
1997 IF FND_API.To_Boolean( p_commit ) THEN
1998 COMMIT WORK;
1999 END IF;
2000
2001 -- Standard call to get message count. if count=1, get message info.
2002 FND_MSG_PUB.Count_And_Get(
2003 p_count => x_msg_count,
2004 p_data => x_msg_data );
2005
2006 EXCEPTION
2007 WHEN FND_API.G_EXC_ERROR THEN
2008 ROLLBACK TO Create_Set_And_Elements_PVT;
2009 x_return_status := FND_API.G_RET_STS_ERROR ;
2010 FND_MSG_PUB.Count_And_Get(
2011 p_encoded => FND_API.G_FALSE ,
2012 p_count => x_msg_count,
2013 p_data => x_msg_data );
2014
2015
2016 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2017 ROLLBACK TO Create_Set_And_Elements_PVT;
2018 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2019 FND_MSG_PUB.Count_And_Get(
2020 p_count => x_msg_count,
2021 p_data => x_msg_data);
2022 WHEN OTHERS THEN
2023 ROLLBACK TO Create_Set_And_Elements_PVT;
2024 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2025 IF FND_MSG_PUB.Check_Msg_Level
2026 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2027 FND_MSG_PUB.Add_Exc_Msg(
2028 G_PKG_NAME,
2029 l_api_name);
2030 END IF;
2031 FND_MSG_PUB.Count_And_Get(
2032 p_count => x_msg_count,
2033 p_data => x_msg_data);
2034 END Create_Set_And_Elements;
2035
2036 --
2037 -- wrapper using records
2038 -- done
2039 --
2040 PROCEDURE Create_Set(
2041 p_api_version in number,
2042 p_init_msg_list in varchar2 := FND_API.G_FALSE,
2043 p_commit in varchar2 := FND_API.G_FALSE,
2044 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
2045 x_return_status OUT NOCOPY varchar2,
2046 x_msg_count OUT NOCOPY number,
2047 x_msg_data OUT NOCOPY varchar2,
2048 p_set_def_rec in CS_Knowledge_PUB.set_def_rec_type,
2049 p_ele_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
2050 x_set_id OUT NOCOPY number
2051 ) is
2052 l_set_def_obj cs_kb_set_def_obj_type;
2053 l_ele_id_tbl cs_kb_number_tbl_type
2054 := cs_kb_number_tbl_type();
2055 i1 pls_integer;
2056 begin
2057
2058 if(p_ele_id_tbl is null ) then
2059
2060 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
2061 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
2062 fnd_msg_pub.Add;
2063 end if;
2064 raise FND_API.G_EXC_ERROR;
2065 end if;
2066
2067 Copy_SetDef_To_Obj(p_set_def_rec, l_set_def_obj);
2068
2069 l_ele_id_tbl.EXTEND(p_ele_id_tbl.COUNT);
2070 i1 := p_ele_id_tbl.FIRST;
2071 while i1 is not null loop
2072 l_ele_id_tbl(i1) := p_ele_id_tbl(i1);
2073 i1 := p_ele_id_tbl.NEXT(i1);
2074 end loop;
2075
2076
2077 Create_Set(
2078 p_api_version => p_api_version,
2079 p_init_msg_list => p_init_msg_list,
2080 p_commit => p_commit,
2081 p_validation_level => p_validation_level,
2082 x_return_status => x_return_status,
2083 x_msg_count => x_msg_count,
2084 x_msg_data => x_msg_data,
2085 p_set_def_obj => l_set_def_obj,
2086 p_ele_id_tbl => l_ele_id_tbl,
2087 x_set_id => x_set_id);
2088
2089
2090 end Create_Set;
2091
2092 --
2093 -- Create a set for the given element_ids.
2094 --
2095 PROCEDURE Create_Set(
2096 p_api_version in number,
2097 p_init_msg_list in varchar2 := FND_API.G_FALSE,
2098 p_commit in varchar2 := FND_API.G_FALSE,
2099 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
2100 x_return_status OUT NOCOPY varchar2,
2101 x_msg_count OUT NOCOPY number,
2102 x_msg_data OUT NOCOPY varchar2,
2103 p_set_def_obj in cs_kb_set_def_obj_type,
2104 p_ele_id_tbl in cs_kb_number_tbl_type,
2105 p_ele_assoc_tbl in cs_kb_number_tbl_type :=null,
2106 x_set_id OUT NOCOPY number
2107 ) is
2108 l_api_name CONSTANT varchar2(30) := 'Create_Set';
2109 l_api_version CONSTANT number := 1.0;
2110
2111 l_set_id number(15);
2112 l_count pls_integer;
2113 l_rowid varchar2(30);
2114 l_date date;
2115 l_created_by number;
2116 l_login number;
2117 i1 pls_integer;
2118 i2 pls_integer;
2119 i3 pls_integer;
2120 l_ele_def_tbl cs_kb_ele_def_tbl_type;
2121 l_assoc number(15);
2122 l_set_number varchar2(30);
2123
2124 CURSOR Get_Min_Visibility IS
2125 SELECT visibility_id
2126 FROM cs_kb_visibilities_b
2127 WHERE position = ( SELECT min(position)
2128 FROM cs_kb_visibilities_b
2129 WHERE sysdate BETWEEN nvl(start_date_active, sysdate-1)
2130 AND nvl(end_date_active, sysdate+1));
2131 l_visibility_id NUMBER;
2132 l_return_status VARCHAR2(1);
2133 l_msg_data VARCHAR2(2000);
2134 l_msg_count NUMBER;
2135
2136
2137 CURSOR Check_Dup_Ele_Insert (v_set_id NUMBER,
2138 v_ele_id NUMBER ) IS
2139 SELECT count(*)
2140 FROM CS_KB_SET_ELES
2141 WHERE set_id = v_set_id
2142 AND element_id = v_ele_id;
2143
2144 l_set_ele_count NUMBER;
2145
2146 begin
2147 -- if type exists
2148 -- if empty set or check_set_exists = N
2149 -- N/A in this api-- insert new elements
2150 -- insert new set
2151 -- insert new set_ele 's
2152 -- insert new set_attr 's
2153 -- insert new ele_ele for all eles in the set if not exists
2154 -- incr_ele_ele for all possible links
2155
2156 savepoint Create_Set_PVT;
2157
2158 if not FND_API.Compatible_API_Call(
2159 l_api_version,
2160 p_api_version,
2161 l_api_name,
2162 G_PKG_NAME) then
2163 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2164 end if;
2165
2166 if FND_API.to_Boolean(p_init_msg_list) then
2167 FND_MSG_PUB.initialize;
2168 end if;
2169
2170 x_return_status := FND_API.G_RET_STS_SUCCESS;
2171 -- -- -- -- begin my code -- -- -- -- --
2172 -- Check params
2173 if(p_set_def_obj.set_type_id is null or
2174 p_set_def_obj.set_type_id <= 0 or
2175 p_set_def_obj.name is null
2176 ) then
2177
2178 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
2179 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
2180 fnd_msg_pub.Add;
2181 end if;
2182 RAISE FND_API.G_EXC_ERROR; -- goto error_found;
2183 end if;
2184
2185 if(p_ele_id_tbl is null or p_ele_id_tbl.COUNT <= 0) then
2186
2187 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
2188 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
2189 fnd_msg_pub.Add;
2190 end if;
2191 raise FND_API.G_EXC_ERROR;
2192 end if;
2193
2194 --check types compatible
2195 i3 := p_ele_id_tbl.FIRST;
2196 while i3 is not null loop
2197
2198 if( Is_Set_Ele_Type_Valid(
2199 p_set_type_id => p_set_def_obj.set_type_id,
2200 p_ele_id => p_ele_id_tbl(i3))
2201 = G_FALSE) then
2202 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
2203 fnd_message.set_name('CS', 'CS_KB_C_INCOMPATIBLE_TYPES');
2204 fnd_msg_pub.Add;
2205
2206 end if;
2207 RAISE FND_API.G_EXC_ERROR;
2208 end if;
2209 i3 := p_ele_id_tbl.NEXT(i3);
2210 end loop;
2211
2212 -- Check required types
2213 l_ele_def_tbl := cs_kb_ele_def_tbl_type();
2214 l_ele_def_tbl.EXTEND(p_ele_id_tbl.COUNT);
2215 i3 := p_ele_id_tbl.FIRST;
2216 while i3 is not null loop
2217 l_ele_def_tbl(i3) := cs_kb_ele_def_obj_type(
2218 p_ele_id_tbl(i3),
2219 null, null, null, null);
2220 i3 := p_ele_id_tbl.NEXT(i3);
2221 end loop;
2222 if(Is_Required_Type_Missing(
2223 p_set_def_obj.set_type_id,l_ele_def_tbl)=FND_API.G_TRUE) then
2224 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
2225 fnd_message.set_name('CS', 'CS_KB_C_MISS_REQ_TYPE');
2226 fnd_msg_pub.Add;
2227
2228 end if;
2229 raise FND_API.G_EXC_ERROR;
2230 end if;
2231
2232 OPEN Get_Min_Visibility;
2233 FETCH Get_Min_Visibility INTO l_visibility_id;
2234 CLOSE Get_Min_Visibility;
2235
2236 CS_KB_SOLUTION_PVT.Create_Solution( x_set_id => l_set_id,
2237 p_set_type_id => p_set_def_obj.set_type_id,
2238 p_name => p_set_def_obj.name,
2239 p_status => p_set_def_obj.status,
2240 p_attribute_category => p_set_def_obj.dff_obj.ATTRIBUTE_CATEGORY,
2241 p_attribute1 => p_set_def_obj.dff_obj.ATTRIBUTE1,
2242 p_attribute2 => p_set_def_obj.dff_obj.ATTRIBUTE2,
2243 p_attribute3 => p_set_def_obj.dff_obj.ATTRIBUTE3,
2244 p_attribute4 => p_set_def_obj.dff_obj.ATTRIBUTE4,
2245 p_attribute5 => p_set_def_obj.dff_obj.ATTRIBUTE5,
2246 p_attribute6 => p_set_def_obj.dff_obj.ATTRIBUTE6,
2247 p_attribute7 => p_set_def_obj.dff_obj.ATTRIBUTE7,
2248 p_attribute8 => p_set_def_obj.dff_obj.ATTRIBUTE8,
2249 p_attribute9 => p_set_def_obj.dff_obj.ATTRIBUTE9,
2250 p_attribute10 => p_set_def_obj.dff_obj.ATTRIBUTE10,
2251 p_attribute11 => p_set_def_obj.dff_obj.ATTRIBUTE11,
2252 p_attribute12 => p_set_def_obj.dff_obj.ATTRIBUTE12,
2253 p_attribute13 => p_set_def_obj.dff_obj.ATTRIBUTE13,
2254 p_attribute14 => p_set_def_obj.dff_obj.ATTRIBUTE14,
2255 p_attribute15 => p_set_def_obj.dff_obj.ATTRIBUTE15,
2256 x_set_number => l_set_number,
2257 x_return_status => l_return_status,
2258 x_msg_data => l_msg_data,
2259 x_msg_count => l_msg_count,
2260 p_visibility_id => l_visibility_id);
2261
2262
2263 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2264 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2265 END IF;
2266
2267 x_set_id := l_set_id;
2268
2269 CS_Knowledge_PVT.Get_Who(l_date, l_created_by, l_login);
2270
2271 --insert set_ele
2272 -- schema changes: element order column
2273 if p_ele_id_tbl is not null and p_ele_id_tbl.COUNT > 0 then
2274
2275 i1 := p_ele_id_tbl.FIRST;
2276 while i1 is not null loop
2277 --validate element
2278 if(p_ele_id_tbl(i1) is null or p_ele_id_tbl(i1) < 0) then
2279 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
2280 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
2281 fnd_msg_pub.Add;
2282 end if;
2283 RAISE FND_API.G_EXC_ERROR;
2284 end if;
2285
2286
2287 if(p_ele_assoc_tbl is null or p_ele_assoc_tbl.count<1 or
2288 p_ele_assoc_tbl(i1) is null or
2289 p_ele_assoc_tbl(i1)=CS_Knowledge_PUB.G_POSITIVE_ASSOC) then
2290 l_assoc := CS_Knowledge_PUB.G_POSITIVE_ASSOC;
2291 else
2292 l_assoc := CS_Knowledge_PUB.G_NEGATIVE_ASSOC;
2293 end if;
2294
2295 OPEN Check_Dup_Ele_Insert( l_set_id, p_ele_id_tbl(i1) );
2296 FETCH Check_Dup_Ele_Insert INTO l_set_ele_count;
2297 CLOSE Check_Dup_Ele_Insert;
2298
2299 IF l_set_ele_count = 0 THEN
2300
2301 insert into cs_kb_set_eles (
2302 set_id, element_id, element_order, assoc_degree,
2303 creation_date, created_by,
2304 last_update_date, last_updated_by, last_update_login)
2305 values(
2306 l_set_id, p_ele_id_tbl(i1), i1, l_assoc,
2307 l_date, l_created_by, l_date, l_created_by, l_login);
2308
2309 END IF;
2310
2311 i1 := p_ele_id_tbl.NEXT(i1);
2312
2313 end loop;
2314 end if;
2315
2316 -- -- -- -- end of code -- -- --
2317
2318 IF FND_API.To_Boolean( p_commit ) THEN
2319 COMMIT WORK;
2320 END IF;
2321
2322 -- Standard call to get message count. if count=1, get message info.
2323 FND_MSG_PUB.Count_And_Get(
2324 p_count => x_msg_count,
2325 p_data => x_msg_data );
2326
2327 EXCEPTION
2328 WHEN FND_API.G_EXC_ERROR THEN
2329 ROLLBACK TO Create_Set_PVT;
2330 x_set_id := -1;
2331 x_return_status := FND_API.G_RET_STS_ERROR ;
2332 FND_MSG_PUB.Count_And_Get(
2333 p_count => x_msg_count,
2334 p_data => x_msg_data );
2335
2336 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2337 ROLLBACK TO Create_Set_PVT;
2338 x_set_id := -1;
2339 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2340 FND_MSG_PUB.Count_And_Get(
2341 p_count => x_msg_count,
2342 p_data => x_msg_data);
2343 WHEN OTHERS THEN
2344 ROLLBACK TO Create_Set_PVT;
2345 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2346 IF FND_MSG_PUB.Check_Msg_Level
2347 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2348 FND_MSG_PUB.Add_Exc_Msg(
2349 G_PKG_NAME,
2350 l_api_name);
2351 END IF;
2352 FND_MSG_PUB.Count_And_Get(
2353 p_count => x_msg_count,
2354 p_data => x_msg_data);
2355
2356 end Create_Set;
2357
2358
2359 /*
2360 wrapper using records
2361 */
2362 PROCEDURE Create_Element(
2363 p_api_version in number,
2364 p_init_msg_list in varchar2 := FND_API.G_FALSE,
2365 p_commit in varchar2 := FND_API.G_FALSE,
2366 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
2367 x_return_status OUT NOCOPY varchar2,
2368 x_msg_count OUT NOCOPY number,
2369 x_msg_data OUT NOCOPY varchar2,
2370 p_ele_def_rec in CS_Knowledge_PUB.ele_def_rec_type,
2371 x_element_id OUT NOCOPY number
2372 ) is
2373 l_ele_def_obj cs_kb_ele_def_obj_type;
2374
2375 begin
2376
2377 Copy_EleDef_To_Obj(p_ele_def_rec, l_ele_def_obj);
2378
2379 Create_Element(
2380 p_api_version => p_api_version,
2381 p_init_msg_list => p_init_msg_list,
2382 p_commit => p_commit,
2383 p_validation_level => p_validation_level,
2384 x_return_status => x_return_status,
2385 x_msg_count => x_msg_count,
2386 x_msg_data => x_msg_data,
2387 p_ele_def_obj => l_ele_def_obj,
2388 x_element_id => x_element_id
2389 );
2390
2391 end Create_Element;
2392
2393
2394 --
2395 -- Create ELement given ele_type_id and desc
2396 -- Other params are not used for now.
2397 --
2398 PROCEDURE Create_Element(
2399 p_api_version in number,
2400 p_init_msg_list in varchar2 := FND_API.G_FALSE,
2401 p_commit in varchar2 := FND_API.G_FALSE,
2402 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
2403 x_return_status OUT NOCOPY varchar2,
2404 x_msg_count OUT NOCOPY number,
2405 x_msg_data OUT NOCOPY varchar2,
2406 p_ele_def_obj in cs_kb_ele_def_obj_type,
2407 x_element_id OUT NOCOPY number
2408 ) is
2409 l_api_name CONSTANT varchar2(30) := 'Create_Element';
2410 l_api_version CONSTANT number := 1.0;
2411
2412 l_ele_id number;
2413 l_date date;
2414 l_created_by number;
2415 l_login number;
2416 l_rowid varchar2(30);
2417 l_ele_def_obj cs_kb_ele_def_obj_type;
2418
2419 begin
2420
2421 savepoint Create_Element_PVT;
2422
2423 if not FND_API.Compatible_API_Call(
2424 l_api_version,
2425 p_api_version,
2426 l_api_name,
2427 G_PKG_NAME) then
2428 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2429 end if;
2430
2431 if FND_API.to_Boolean(p_init_msg_list) then
2432 FND_MSG_PUB.initialize;
2433 end if;
2434
2435 x_return_status := FND_API.G_RET_STS_SUCCESS;
2436
2437
2438 -- -- -- -- begin my code -- -- -- -- --
2439 -- if type exists
2440 -- insert element
2441
2442
2443 -- Check params
2444 if(p_ele_def_obj.element_type_id is null
2445 ) then
2446 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
2447 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
2448 fnd_msg_pub.Add;
2449 end if;
2450
2451 raise FND_API.G_EXC_ERROR; -- goto error_found;
2452 end if;
2453
2454 l_ele_id := CS_KB_ELEMENTS_AUDIT_PKG.Create_Element(
2455 p_element_type_id => p_ele_def_obj.element_type_id,
2456 p_desc => p_ele_def_obj.description,
2457 p_name => p_ele_def_obj.name,
2458 p_attribute_category => p_ele_def_obj.dff_obj.attribute_category,
2459 p_attribute1 => p_ele_def_obj.dff_obj.attribute1,
2460 p_attribute2 => p_ele_def_obj.dff_obj.attribute2,
2461 p_attribute3 => p_ele_def_obj.dff_obj.attribute3,
2462 p_attribute4 => p_ele_def_obj.dff_obj.attribute4,
2463 p_attribute5 => p_ele_def_obj.dff_obj.attribute5,
2464 p_attribute6 => p_ele_def_obj.dff_obj.attribute6,
2465 p_attribute7 => p_ele_def_obj.dff_obj.attribute7,
2466 p_attribute8 => p_ele_def_obj.dff_obj.attribute8,
2467 p_attribute9 => p_ele_def_obj.dff_obj.attribute9,
2468 p_attribute10 => p_ele_def_obj.dff_obj.attribute10,
2469 p_attribute11 => p_ele_def_obj.dff_obj.attribute11,
2470 p_attribute12 => p_ele_def_obj.dff_obj.attribute12,
2471 p_attribute13 => p_ele_def_obj.dff_obj.attribute13,
2472 p_attribute14 => p_ele_def_obj.dff_obj.attribute14,
2473 p_attribute15 => p_ele_def_obj.dff_obj.attribute15);
2474
2475 x_element_id := l_ele_id;
2476
2477 if not (l_ele_id>0) then
2478 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
2479 fnd_msg_pub.Add;
2480 end if;
2481 raise FND_API.G_EXC_ERROR;
2482 end if;
2483
2484 <<end_proc>>
2485 null;
2486 -- -- -- -- end of code -- -- --
2487
2488 IF FND_API.To_Boolean( p_commit ) THEN
2489 COMMIT WORK;
2490 END IF;
2491
2492 -- Standard call to get message count. if count=1, get message info.
2493 FND_MSG_PUB.Count_And_Get(
2494 p_count => x_msg_count,
2495 p_data => x_msg_data );
2496
2497 EXCEPTION
2498 WHEN FND_API.G_EXC_ERROR THEN
2499 ROLLBACK TO Create_Element_PVT;
2500 x_return_status := FND_API.G_RET_STS_ERROR ;
2501 FND_MSG_PUB.Count_And_Get(
2502 p_count => x_msg_count,
2503 p_data => x_msg_data );
2504 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2505 ROLLBACK TO Create_Element_PVT;
2506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2507 FND_MSG_PUB.Count_And_Get(
2508 p_count => x_msg_count,
2509 p_data => x_msg_data);
2510 WHEN OTHERS THEN
2511 ROLLBACK TO Create_Element_PVT;
2512 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2513 IF FND_MSG_PUB.Check_Msg_Level
2514 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2515 FND_MSG_PUB.Add_Exc_Msg(
2516 G_PKG_NAME,
2517 l_api_name);
2518 END IF;
2519 FND_MSG_PUB.Count_And_Get(
2520 p_count => x_msg_count,
2521 p_data => x_msg_data);
2522 end Create_Element;
2523
2524 --
2525 -- Incr_Set_Useful
2526 -- When a set is found useful, update usefulness history table.
2527 -- Should I incr (each) individual ele_ele's? No.
2528 -- wt_code indicates which column in ele_ele to update. - just use set_count.
2529 --
2530 PROCEDURE Incr_Set_Useful(
2531 p_api_version in number,
2532 p_init_msg_list in varchar2, -- := FND_API.G_FALSE,
2533 p_commit in varchar2, -- := FND_API.G_FALSE,
2534 p_validation_level in number, -- := FND_API.G_VALID_LEVEL_FULL,
2535 x_return_status OUT NOCOPY varchar2,
2536 x_msg_count OUT NOCOPY number,
2537 x_msg_data OUT NOCOPY varchar2,
2538 p_set_id in number,
2539 p_user_id in number,
2540 p_used_type in varchar2, -- := CS_KNOWLEDGE_PVT.G_PF,
2541 p_session_id in number
2542 ) is
2543 l_api_name CONSTANT varchar2(30) := 'Incr_Set_Useful';
2544 l_api_version CONSTANT number := 1.0;
2545 l_hist_id number(15);
2546 l_date date;
2547 l_created_by number;
2548 l_login number;
2549 l_rowid varchar2(30);
2550 begin
2551 savepoint Incr_Set_Useful_PVT;
2552
2553 if not FND_API.Compatible_API_Call(
2554 l_api_version,
2555 p_api_version,
2556 l_api_name,
2557 G_PKG_NAME) then
2558 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2559 end if;
2560
2561 if FND_API.to_Boolean(p_init_msg_list) then
2562 FND_MSG_PUB.initialize;
2563 end if;
2564
2565 x_return_status := FND_API.G_RET_STS_SUCCESS;
2566
2567 -- -- -- -- begin my code -- -- -- -- --
2568 -- Check params
2569 if(p_set_id is null) then
2570 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
2571 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
2572 fnd_msg_pub.Add;
2573 end if;
2574 raise FND_API.G_EXC_ERROR; --goto error_found;
2575 end if;
2576
2577 -- incr history tables
2578 -- insert into histories table (who, when)
2579 -- insert or update cs_kb_set_used_hists(set, usedtype, histid)
2580
2581 --prepare data, then insert new ele
2582 select cs_kb_histories_s.nextval into l_hist_id from dual;
2583 CS_Knowledge_PVT.Get_Who(l_date, l_created_by, l_login);
2584
2585 CS_KB_HISTORIES_PKG.Insert_Row(
2586 X_Rowid => l_rowid,
2587 X_History_Id => l_hist_id,
2588 X_History_Name => null,
2589 X_User_Id => p_user_id,
2590 X_Entry_Date => l_date,
2591 X_Name => null,
2592 X_Description => null,
2593 X_Creation_Date => l_date,
2594 X_Created_By => l_created_by,
2595 X_Last_Update_Date => l_date,
2596 X_Last_Updated_By => l_created_by,
2597 X_Last_Update_Login => l_login);
2598
2599 insert into cs_kb_set_used_hists(
2600 set_id, history_id,
2601 creation_date, created_by, last_update_date,
2602 last_updated_by, last_update_login,used_type, session_id)
2603 values(
2604 p_set_id, l_hist_id, l_date, l_created_by, l_date,
2605 l_created_by, l_login, p_used_type, p_session_id );
2606
2607 -- -- -- -- end of code -- -- --
2608
2609 IF FND_API.To_Boolean( p_commit ) THEN
2610 COMMIT WORK;
2611 END IF;
2612
2613 -- Standard call to get message count. if count=1, get message info.
2614 FND_MSG_PUB.Count_And_Get(
2615 p_count => x_msg_count,
2616 p_data => x_msg_data );
2617
2618 EXCEPTION
2619 WHEN FND_API.G_EXC_ERROR THEN
2620 ROLLBACK TO Incr_Set_Useful_PVT;
2621 x_return_status := FND_API.G_RET_STS_ERROR ;
2622 FND_MSG_PUB.Count_And_Get(
2623 p_count => x_msg_count,
2624 p_data => x_msg_data );
2625 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2626 ROLLBACK TO Incr_Set_Useful_PVT;
2627 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2628 FND_MSG_PUB.Count_And_Get(
2629 p_count => x_msg_count,
2630 p_data => x_msg_data);
2631 WHEN OTHERS THEN
2632 ROLLBACK TO Incr_Set_Useful_PVT;
2633 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2634 IF FND_MSG_PUB.Check_Msg_Level
2635 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2636 FND_MSG_PUB.Add_Exc_Msg(
2637 G_PKG_NAME,
2638 l_api_name);
2639 END IF;
2640 FND_MSG_PUB.Count_And_Get(
2641 p_count => x_msg_count,
2642 p_data => x_msg_data);
2643 end Incr_Set_Useful;
2644
2645 -- ************************************************************
2646 -- START: Oracle Text Query Rewrite Routines
2647 -- ************************************************************
2648
2649 --
2650 -- Private Utility function
2651 -- remove ( and ) from p_string by replacing them with space characters
2652 --
2653 FUNCTION Remove_Parenthesis
2654 ( p_string in varchar2 )
2655 return varchar2
2656 is
2657 l_string varchar2(32000) := p_string;
2658 begin
2659 l_string := replace(l_string, '(', ' ');
2660 l_string := replace(l_string, ')', ' ');
2661 l_string := replace(l_string, '[', ' ');
2662 l_string := replace(l_string, ']', ' ');
2663 return l_string;
2664 end Remove_Parenthesis;
2665
2666 --
2667 -- Private Utility function
2668 -- remove } and { from p_string by replacing them with space characters
2669 --
2670 FUNCTION Remove_Braces
2671 ( p_string in varchar2 )
2672 return varchar2
2673 is
2674 l_string varchar2(32000) := p_string;
2675 begin
2676 l_string := replace(l_string, '}', ' ');
2677 l_string := replace(l_string, '{', ' ');
2678 return l_string;
2679 end Remove_Braces;
2680
2681 --
2682 -- Private Utility function
2683 -- replace white-space characters
2684 --
2685 FUNCTION Replace_Whitespace
2686 ( p_string in varchar2,
2687 p_search_option in number )
2688 return varchar2
2689 is
2690 lenb INTEGER;
2691 len INTEGER;
2692 l_criteria_word VARCHAR2(2000);
2693 q_word VARCHAR2(32000);
2694 l_string varchar2(32000) := p_string;
2695 first_word boolean := TRUE;
2696 l_operator varchar2(4);
2697
2698 begin
2699
2700 -- First convert multi-byte space character to single byte space
2701 -- so that later on, when we a parsing for the space character, it
2702 -- will be found
2703 lenb := lengthb(l_string);
2704 len := length(l_string);
2705 if(lenb<>len) then
2706 l_string := replace(l_string, to_multi_byte(' '), ' ');
2707 end if;
2708 lenb := lengthb(l_string);
2709 len := length(l_string);
2710 -- Pad the criteria string with blanks so that
2711 -- the parse algorithm will not miss the last word
2712 l_string := rpad(l_string, lenb+1);
2713 l_string := ltrim(l_string,' ');
2714
2715 -- Initialize some variables
2716 first_word := TRUE;
2717 len := instr(l_string, ' '); -- position of first space character
2718
2719 -- Loop through the criteria string, parse to get a single criteria word
2720 -- token at a time. Between each word, insert the proper Oracle Text
2721 -- operator (e.g. AND, OR, ACCUM, etc.) depending on the search method
2722 -- chosen.
2723 while (len > 0) LOOP
2724 l_criteria_word :=
2725 substr(l_string, 1, len-1); --from beg till char before space
2726
2727 if (first_word = TRUE)
2728 then
2729 if (p_search_option = CS_KNOWLEDGE_PUB.FUZZY) --FUZZY
2730 then
2731 q_word := '?'''||l_criteria_word||'''';
2732 else
2733 q_word := ''''||l_criteria_word||'''';
2734 end if;
2735 else
2736 if (p_search_option = CS_KNOWLEDGE_PUB.MATCH_ALL)
2737 then
2738 l_operator := ' & ';
2739 elsif (p_search_option = CS_KNOWLEDGE_PUB.MATCH_ANY)
2740 then
2741 l_operator := ' | ';
2742 elsif (p_search_option = CS_KNOWLEDGE_PUB.FUZZY)
2743 then
2744 l_operator := ' , ?';
2745 elsif (p_search_option = CS_KNOWLEDGE_PUB.MATCH_ACCUM)
2746 then
2747 l_operator := ' , ';
2748 elsif (p_search_option = CS_KNOWLEDGE_PUB.MATCH_PHRASE)
2749 then
2750 l_operator := ' ';
2751 else -- if other cases
2752 l_operator := ' , ';
2753 end if;
2754 q_word := q_word||l_operator||''''||l_criteria_word||'''';
2755 end if;
2756
2757 first_word := FALSE;
2758
2759 -- Get the rest of the criteria string and trim off beginning whitespace
2760 -- This will now be the beginning of the next criteria token
2761 l_string := substr(l_string,len);
2762 l_string := LTRIM(l_string, ' ');
2763 -- Find the position of the next space. This will now be the end of the
2764 -- next criteria token
2765 len:= instr(l_string, ' '); -- find the position of the next space
2766 end loop;
2767 return q_word;
2768 end Replace_Whitespace;
2769
2770
2771 --
2772 -- Private Utility function
2773 -- Handle special characters for Text query
2774 --
2775 FUNCTION Escape_Special_Char( p_string in varchar2 )
2776 return varchar2
2777 is
2778 l_string varchar2(32000) := p_string;
2779
2780 --5217204
2781 l_symbol_regexp_pattern VARCHAR2(100);
2782 l_symbol_idx NUMBER;
2783 l_final_start NUMBER;
2784 l_final_str VARCHAR2(32000);
2785 l_symbol_str VARCHAR2(32000);
2786
2787 --5217204_eof
2788 begin
2789 --5217204
2790 -- define regular expression pattern.
2791 -- We do not neex to include () and [] in the pattern because they should
2792 -- be removed before processing.
2793 -- This pattern should find the pattern in which a % sign is prefixed, postfixed,
2794 -- or in between a symbol or a space.
2795 -- e.g $% $%$ %%%%^ %%%*
2796 l_symbol_regexp_pattern :=
2797 '([''<>\.!@#$^&*\(-\)+=_\?/ ])([%]+)([''<>\.!@#$^&*\(-\)+=_|\?/ ])';
2798 l_symbol_idx := 0;
2799 --5217204_eof
2800
2801 -- Remove Grouping and Escaping characters
2802 l_string := Remove_Parenthesis(l_string);
2803 l_string := Remove_Braces(l_string);
2804
2805 -- replace all the other special reserved characters
2806 l_string := replace(l_string, FND_GLOBAL.LOCAL_CHR(39),
2807 FND_GLOBAL.LOCAL_CHR(39)||FND_GLOBAL.LOCAL_CHR(39)); -- quote ' to ''
2808 l_string := replace(l_string, '\', '\\'); -- back slash (escape char)
2809 l_string := replace(l_string, ',', '\,'); -- accumulate
2810 l_string := replace(l_string, '&', '\&'); -- and
2811 l_string := replace(l_string, '=', '\='); -- equivalance
2812 l_string := replace(l_string, '?', '\?'); -- fussy
2813 l_string := replace(l_string, '-', '\-'); -- minus
2814 l_string := replace(l_string, ';', '\;'); -- near
2815 l_string := replace(l_string, '~', '\~'); -- not
2816 l_string := replace(l_string, '|', '\|'); -- or
2817 l_string := replace(l_string, '$', '\$'); -- stem
2818 l_string := replace(l_string, '!', '\!'); -- soundex
2819 l_string := replace(l_string, '>', '\>'); -- threshold
2820 l_string := replace(l_string, '*', '\*'); -- weight
2821 l_string := replace(l_string, '_', '\_'); -- single char wildcard
2822
2823 --bug 3209009
2824 -- to make sure we will not miss '% test and %%'
2825 -- l_string := ' '||l_string||' ';
2826 -- l_string := replace(l_string, ' % ', ' \% ');
2827 -- l_string := replace(l_string, ' %% ', ' \%\% ');
2828 -- l_string := trim(l_string);
2829
2830 -- bug 5217204
2831 -- Make sure we will not miss '% test and %%
2832 l_final_str := l_string;
2833 l_string := ' ' || l_string || ' ';
2834 l_symbol_idx := regexp_instr(l_string, l_symbol_regexp_pattern);
2835
2836 l_final_start := 1;
2837
2838 if l_symbol_idx > 0 then
2839 l_final_str := '';
2840 --5412688
2841 else -- if nothing to process
2842 return l_final_str;
2843 --5412688_eof
2844 end if;
2845
2846 while l_symbol_idx > 0 loop
2847 -- l_symbol_idx is the position of the first character of the pattern
2848 -- in l_string.
2849 l_final_str := l_final_str || substrb(l_string,
2850 l_final_start,
2851 l_symbol_idx - l_final_start);
2852
2853 l_symbol_str := regexp_substr(l_string,
2854 l_symbol_regexp_pattern,
2855 l_final_start);
2856
2857 if l_symbol_str is not null then
2858 -- Update l_final_start position. It must come before the replace
2859 -- command.
2860 -- If the last character of l_symbol_str is a space, then we need
2861 -- to move back the index by 1. This is to plug the problem like
2862 -- this phrase .% %%% in this case both ".%" and "%%%" relies on
2863 -- the space between for the regular expression to successfully
2864 -- match the pattern.
2865 if regexp_instr(l_symbol_str, ' $') = 0 then
2866 l_final_start := l_symbol_idx + length(l_symbol_str);
2867 else
2868 l_final_start := l_symbol_idx + length(l_symbol_str) - 1;
2869 end if;
2870 l_final_str := l_final_str || replace(l_symbol_str, '%', '\%');
2871 end if;
2872
2873 -- Starting from the l_final_start, look for next pattern
2874 l_symbol_idx := regexp_instr(l_string, l_symbol_regexp_pattern, l_final_start);
2875
2876 end loop;
2877
2878 -- get the rest of the string
2879 l_final_str := l_final_str || substrb(l_string, l_final_start);
2880
2881 -- return l_string;
2882 return l_final_str;
2883 -- 5217204 -eof
2884
2885
2886 end Escape_Special_Char;
2887
2888 --
2889 -- Private Utility function
2890 -- Add the next term to the query string according to search option
2891 -- Parameters:
2892 -- p_string VARCHAR2: the running keyword string
2893 -- p_term VARCHAR2: the term to append
2894 -- p_search_option NUMBER: search option, as defined in CS_KNOWLEDGE_PUB
2895 -- Returns:
2896 -- Query string with the term appended using the appropriate search operator
2897 -- Since 12.0
2898 --
2899 FUNCTION Append_Query_Term
2900 ( p_string IN VARCHAR2,
2901 p_term IN VARCHAR2,
2902 p_search_option IN NUMBER )
2903 return varchar2
2904 is
2905 l_string varchar2(32000) := p_string;
2906 l_operator varchar2(4);
2907 begin
2908 if( trim(p_term) is null )
2909 then
2910 return p_string;
2911 end if;
2912
2913 if( trim(l_string) is null ) -- first term
2914 then
2915 if (p_search_option = CS_KNOWLEDGE_PUB.FUZZY)
2916 then
2917 l_string := '?'''|| p_term ||'''';
2918 else
2919 l_string := p_term;
2920 end if;
2921 else -- subsequent terms
2922 if (p_search_option = CS_KNOWLEDGE_PUB.MATCH_ALL)
2923 then
2924 l_operator := ' & ';
2925 elsif (p_search_option = CS_KNOWLEDGE_PUB.MATCH_ANY)
2926 then
2927 l_operator := ' | ';
2928 elsif (p_search_option = CS_KNOWLEDGE_PUB.FUZZY)
2929 then
2930 l_operator := ' , ?';
2931 elsif (p_search_option = CS_KNOWLEDGE_PUB.MATCH_ACCUM)
2932 then
2933 l_operator := ' , ';
2934 elsif (p_search_option = CS_KNOWLEDGE_PUB.MATCH_PHRASE)
2935 then
2936 l_operator := ' ';
2937 else -- if other cases
2938 l_operator := ' , ';
2939 end if;
2940
2941 l_string := l_string || l_operator|| p_term ;
2942 end if;
2943
2944 return l_string;
2945 end Append_Query_Term;
2946
2947 --
2948 -- Private Utility function
2949 -- This method parses the keywords based on the search syntax rule.
2950 -- We support the syntax of exact phrase in the keywords (" ").
2951 --
2952 -- Parameters:
2953 -- p_string VARCHAR2: keywords to be processed
2954 -- p_search_option NUMBER: Must be one of the search option
2955 -- defined in CS_K NOWLEDGE_PUB.
2956 -- Returns:
2957 -- The processed keyword query
2958 -- Since 12.0
2959 --
2960 FUNCTION Parse_Keywords
2961 ( p_string IN VARCHAR2,
2962 p_search_option IN NUMBER )
2963 RETURN VARCHAR2
2964 is
2965 l_left_quote INTEGER := 0; -- position of left quote
2966 l_right_quote INTEGER := 0; -- position of right quote
2967 l_qnum INTEGER := 0; -- number of double quotes found so far
2968 l_phrase Varchar2(32000); -- extracted phrase
2969 l_unquoted Varchar2(32000) := ''; -- all unquoted text
2970 l_len integer;
2971 TYPE String_List IS TABLE OF VARCHAR2(32000) INDEX BY PLS_INTEGER;
2972 l_phrase_list String_List; -- list of extracted phrases
2973 l_counter INTEGER;
2974 l_processed_keyword VARCHAR(32000) := ''; --final processed keyword string
2975 begin
2976
2977 l_left_quote := instr(p_string, '"', 1, l_qnum + 1);
2978
2979 if(l_left_quote = 0) -- no quotes
2980 then
2981 l_unquoted := p_string;
2982 end if;
2983
2984 while (l_left_quote > 0) LOOP
2985 --add unquoted portion to the unquoted string (exclude ")
2986 --assert: left quote (current) > right quote (prev)
2987 l_len := l_left_quote - l_right_quote - 1;
2988 l_unquoted := l_unquoted || ' ' ||
2989 substr(p_string, l_right_quote + 1, l_len);
2990
2991 --is there a close quote?
2992 l_right_quote := instr(p_string,'"', 1, l_qnum + 2);
2993 if(l_right_quote > 0) -- add the quote
2994 then
2995 l_len := l_right_quote - l_left_quote - 1;
2996 l_phrase := substr(p_string, l_left_quote + 1, l_len);
2997 if( trim (l_phrase) is not null)
2998 then
2999 --add the quote to the list
3000 l_phrase_list(l_left_quote) := l_phrase;
3001 --dbms_output.put_line('phrase:' || '[' || l_phrase || ']');
3002 end if;
3003 else -- add the remaining text (last quote was an open quote)
3004 l_unquoted := l_unquoted || ' ' || substr(p_string, l_left_quote + 1);
3005 end if;
3006
3007 -- now process the next phrase, try to find the open quote
3008 l_qnum := l_qnum + 2;
3009 l_left_quote := instr(p_string, '"', 1, l_qnum + 1);
3010 end loop;
3011
3012 -- add the remaining text (last quote was close quote)
3013 if(l_right_quote > 0)
3014 then
3015 l_unquoted := l_unquoted || ' ' || substr(p_string, l_right_quote + 1);
3016 end if;
3017
3018 --add unquoted text first to final keyword string
3019 if(length( trim (l_unquoted) ) > 0)
3020 then
3021 l_processed_keyword := l_unquoted;
3022 l_processed_keyword := Escape_Special_Char(l_processed_keyword);
3023 l_processed_keyword :=
3024 Replace_Whitespace(l_processed_keyword, p_search_option);
3025 end if;
3026
3027 -- loop and add all the phrases
3028 l_counter := l_phrase_list.FIRST;
3029 WHILE l_counter IS NOT NULL
3030 LOOP
3031 --dbms_output.put_line('Phrase[' || l_counter || '] = ' || l_phrase_list(l_counter));
3032 --process each phrase as an exact phrase
3033 l_phrase := Escape_Special_Char( l_phrase_list(l_counter) );
3034 l_phrase := Replace_Whitespace(l_phrase, CS_KNOWLEDGE_PUB.MATCH_PHRASE);
3035 l_phrase := '(' || l_phrase || ')';
3036 l_processed_keyword :=
3037 Append_Query_Term(l_processed_keyword, l_phrase, p_search_option);
3038 l_counter := l_phrase_list.NEXT(l_counter);
3039 END LOOP;
3040
3041 -- Note some calling procedures do not properly handle an empty query
3042 -- For now, simply return ' ', which will match nothing
3043 if( trim (l_processed_keyword) is null)
3044 then
3045 l_processed_keyword := ' '' '' ';
3046 end if;
3047
3048 return l_processed_keyword;
3049 end Parse_Keywords;
3050
3051 --
3052 -- Private Utility function
3053 -- Convert Text query critiera string into keyword query
3054 -- with special characters handled
3055 -- Since 12.0, delegates to Parse_Keywords
3056 --
3057 FUNCTION Build_Keyword_Query(
3058 p_string in varchar2,
3059 p_search_option in number
3060 ) return varchar2
3061 is
3062 --l_string varchar2(32000) := p_string;
3063 begin
3064 --l_string := Escape_Special_Char(l_string);
3065 --return Replace_Whitespace(l_string, p_search_option);
3066 return parse_keywords(p_string, p_search_option);
3067 end Build_Keyword_Query;
3068
3069 --
3070 -- Private Utility function
3071 -- This function build the theme query component of a search
3072 -- This is essentially wrapping the keywords with a 'about()'
3073 -- intermedia function call.
3074 -- The string parameter passed into the intermedia 'about()'
3075 -- function has a limit of 255 characters. This function gets
3076 -- around that limit by breaking the query string up into < 255
3077 -- character chunks, wrapping each chunk with a separate 'about()'
3078 -- function and accumulating the theme search chunks together.
3079 function Build_Intermedia_Theme_Query( p_raw_query_keywords in varchar2 )
3080 return varchar2
3081 is
3082 l_theme_querystring varchar2(30000);
3083 l_chunksize integer := 245;
3084 l_pos_raw integer;
3085 l_pos_endchunk integer;
3086 l_len_raw integer;
3087 l_chunk_count integer := 0;
3088 begin
3089 l_len_raw := length(p_raw_query_keywords);
3090 l_pos_raw := 1;
3091
3092 while( l_pos_raw < l_len_raw ) loop
3093 l_chunk_count := l_chunk_count + 1;
3094
3095 -- Set end position of next chunck
3096 if( l_pos_raw + l_chunksize - 1 > l_len_raw ) then
3097 l_pos_endchunk := l_len_raw;
3098 else
3099 l_pos_endchunk := l_pos_raw + l_chunksize - 1;
3100 -- adjust the endchunk to the last word boundary
3101 l_pos_endchunk := instr( p_raw_query_keywords, ' ',
3102 -(l_len_raw-l_pos_endchunk+1) );
3103 end if;
3104
3105 -- wrap next chunk with 'about()' and append to
3106 -- the theme query string buffer with accumulate.
3107 if( l_chunk_count > 1 ) then
3108 l_theme_querystring := l_theme_querystring || ',';
3109 end if;
3110
3111 l_theme_querystring := l_theme_querystring || 'about(' ||
3112 substr(p_raw_query_keywords,
3113 l_pos_raw,
3114 l_pos_endchunk - l_pos_raw + 1)||')';
3115
3116 l_pos_raw := l_pos_endchunk + 1;
3117 end loop;
3118 return l_theme_querystring;
3119 end Build_Intermedia_Theme_Query;
3120
3121 --
3122 -- Private Utility function
3123 -- This is the main query-rewrite function. Given a raw
3124 -- user-entered keyword string and the search method chosen,
3125 -- this function will construct the appropriate Oracle Text
3126 -- query string. This is independent of whether the search
3127 -- is for solutions or statements or anything else.
3128 -- NOTE: This function does NOT incorporate product, platform,
3129 -- category, or other metadata information into the Text query.
3130 -- Those predicates are left to the caller to append.
3131 FUNCTION Build_Intermedia_Query
3132 ( p_string in varchar2,
3133 p_search_option in number )
3134 return varchar2
3135 is
3136 l_about_query varchar2(32000) := p_string;
3137 l_keyword_query varchar2(32000) := p_string;
3138 l_iQuery_str varchar2(32000); -- final intermedia query string
3139 lenb integer;
3140 len integer;
3141 begin
3142
3143 -- If the Search option chosen is THEME Search or if there is
3144 -- no search option chosen, then rewrite the raw text query
3145 -- with the theme search query and concatenate it with a regular
3146 -- non-theme based rewritten query
3147 if (p_search_option = CS_KNOWLEDGE_PUB.THEME_BASED or
3148 p_search_option is null) --DEFAULT
3149 then
3150 l_keyword_query :=
3151 Build_Keyword_Query
3152 ( p_string => l_keyword_query,
3153 p_search_option=> null);
3154 l_about_query :=
3155 Build_Intermedia_Theme_Query( Escape_Special_Char(l_about_query) );
3156 l_iQuery_str := '('||l_about_query||' OR '||l_keyword_query||')';
3157 else
3158 -- Else just build the standard, non-theme based rewritten query
3159 l_keyword_query :=
3160 Build_Keyword_Query
3161 ( p_string => l_keyword_query,
3162 p_search_option => p_search_option );
3163
3164 --(SRCHEFF)
3165 Process_Frequency_Keyword(l_keyword_query, p_search_option);
3166
3167 l_iQuery_str := '( ' || l_keyword_query || ' )';
3168 end if;
3169
3170 -- Return the rewritten text query criteria
3171 return l_iQuery_str;
3172
3173 end Build_Intermedia_Query;
3174
3175
3176 -- WRAPPER
3177 -- Constructs the Text query that should be used in the
3178 -- CONTAINS predicate for a solution search
3179 -- (1) -calls (2)
3180 --
3181 FUNCTION Build_Solution_Text_Query
3182 ( p_raw_text in varchar2,
3183 p_solution_type_id_tbl in CS_Knowledge_PUB.number15_tbl_type )
3184 return varchar2
3185 is
3186 l_number number;
3187 begin
3188 return Build_Solution_Text_Query(p_raw_text,
3189 p_solution_type_id_tbl, l_number);
3190 end Build_Solution_Text_Query;
3191
3192 -- WRAPPER
3193 -- Constructs the Text query that should be used in the
3194 -- CONTAINS predicate for a solution search
3195 -- (2) calls (3)
3196 FUNCTION Build_Solution_Text_Query
3197 ( p_raw_text in varchar2,
3198 p_solution_type_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
3199 p_search_option in number )
3200 return varchar2
3201 is
3202 l_product_id_tbl CS_Knowledge_PUB.number15_tbl_type;
3203 l_platform_id_tbl CS_Knowledge_PUB.number15_tbl_type;
3204 begin
3205 return Build_Solution_Text_Query(
3206 p_raw_text, p_solution_type_id_tbl,
3207 l_product_id_tbl, l_platform_id_tbl, p_search_option);
3208 end Build_Solution_Text_Query;
3209
3210 -- WRAPPER
3211 -- Constructs the Text query that should be used in the
3212 -- CONTAINS predicate for a solution search
3213 -- (3) calls (4)
3214 FUNCTION Build_Solution_Text_Query
3215 ( p_raw_text in varchar2,
3216 p_solution_type_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
3217 p_product_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
3218 p_platform_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
3219 p_search_option in number )
3220 return varchar2
3221 is
3222 l_category_id_tbl CS_Knowledge_PUB.number15_tbl_type;
3223 begin
3224 return Build_Solution_Text_Query(
3225 p_raw_text, p_solution_type_id_tbl,
3226 p_product_id_tbl, p_platform_id_tbl, l_category_id_tbl, p_search_option);
3227 end Build_Solution_Text_Query;
3228
3229 -- WRAPPER
3230 -- Constructs the Text query that should be used in the
3231 -- CONTAINS predicate for a solution search
3232 -- (4) calls (5)
3233 FUNCTION Build_Solution_Text_Query
3234 ( p_raw_text in varchar2,
3235 p_solution_type_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
3236 p_product_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
3237 p_platform_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
3238 p_category_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
3239 p_search_option in number )
3240 return varchar2
3241 is
3242 l_statement_id_tbl CS_Knowledge_PUB.number15_tbl_type;
3243 begin
3244 return Build_Solution_Text_Query(
3245 p_raw_text, p_solution_type_id_tbl,
3246 p_product_id_tbl, p_platform_id_tbl, p_category_id_tbl,
3247 l_statement_id_tbl, p_search_option);
3248 end Build_Solution_Text_Query;
3249
3250 -- Constructs the Text query that should be used in the
3251 -- CONTAINS predicate for a solution search
3252 -- (5)
3253 -- Handles keywords, solution type, products, platforms, category,
3254 -- solution number and statement id
3255 FUNCTION Build_Solution_Text_Query
3256 ( p_raw_text in varchar2,
3257 p_solution_type_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
3258 p_product_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
3259 p_platform_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
3260 p_category_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
3261 p_statement_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
3262 p_search_option in number )
3263 return varchar2
3264 is
3265 l_query_str varchar2(30000) := p_raw_text;
3266 l_raw_text varchar2(30000) := p_raw_text;
3267 l_custom_query_str varchar2(30000);
3268 l_lang varchar2(4);
3269 l_lang_cond varchar2(2000);
3270 l_types varchar2(2000);
3271 l_type_cond varchar2(2000);
3272 l_type_is_all varchar2(1);
3273 l_type_ids_index number;
3274
3275 l_product_cond varchar2(2000);
3276 l_products varchar2(2000);
3277
3278 l_platform_cond varchar2(2000);
3279 l_platforms varchar2(2000);
3280
3281 l_category_cond varchar2(2000);
3282 l_categories varchar2(2000);
3283
3284 l_sol_number_cond varchar2(2000);
3285 l_statement_ids varchar2(30000);
3286 l_statement_ids_cond varchar2(30000);
3287
3288 l_return_status VARCHAR2(50);
3289 l_msg_count NUMBER;
3290 l_msg_data VARCHAR2(5000);
3291
3292 l_weight1 NUMBER;
3293 l_weight2 NUMBER;
3294 MAX_SET_NUMBER_LENGTH NUMBER := 30;
3295
3296 l_category_group_id NUMBER;
3297 l_soln_visibility_position NUMBER;
3298 l_catgrp_vis varchar2(2000);
3299
3300 -- klou (LEAK)
3301 l_open_text VARCHAR2(1) := '(';
3302 l_close_text VARCHAR2(1) := ')';
3303 l_and_operator VARCHAR2(10) := ' ';
3304 -- klou (PRODFILTER)
3305 Cursor get_product_filter_csr Is
3306 Select nvl(fnd_profile.value('CS_KB_NO_GENERIC_SOLN_IN_PROD_SRCH'), 'N')
3307 From dual;
3308 l_exclude_generic_soln VARCHAR(1) := 'Y';
3309 -- klou (SMARTSCORE)
3310 l_query_filter VARCHAR2(30000) := '';
3311 l_query_keyword VARCHAR2(30000) := '';
3312 l_smart_score_flag VARCHAR2(1) := 'Y';
3313 l_statement_ids_keyword VARCHAR2(30000) ;
3314
3315 Cursor visibility_csr Is
3316 SELECT VISIBILITY_ID
3317 FROM CS_KB_VISIBILITIES_B
3318 WHERE VISIBILITY_ID =
3319 fnd_profile.value('CS_KB_ASSIGNED_SOLUTION_VISIBILITY_LEVEL')
3320 AND sysdate BETWEEN nvl(Start_Date_Active, sysdate-1)
3321 AND nvl(End_Date_Active, sysdate+1);
3322
3323 l_soln_visibility_id NUMBER;
3324
3325 -- 39836966_cursor
3326 CURSOR Get_Magic_Word_Csr IS
3327 SELECT fnd_profile.value('CS_KB_SEARCH_NONEXIST_KEYWORD') FROM dual;
3328
3329 l_magic_word VARCHAR2(255);
3330 -- 39836966_cursor_eof
3331 begin
3332 -- Goal: make a query text that consists of score_section + filter_section.
3333
3334 -- Get security.
3335 Open visibility_csr;
3336 Fetch visibility_csr Into l_soln_visibility_id;
3337 Close visibility_csr;
3338 If l_soln_visibility_id Is Null Then
3339 l_soln_visibility_id := 0;
3340 End If;
3341
3342 l_category_group_id :=
3343 CS_KB_SECURITY_PVT.Get_Category_Group_Id();
3344 l_catgrp_vis := TO_CHAR(l_category_group_id) || 'a'
3345 ||to_char(l_soln_visibility_id);
3346
3347 --1. Clean up the p_raw_text.
3348 l_raw_text := Remove_Braces(p_raw_text);
3349 l_raw_text := trim(l_raw_text);
3350 l_query_str := l_raw_text;
3351 If l_raw_text is null or l_raw_text = '' then
3352 --(SMARTSCORE), remove all logic in this block.
3353 null;
3354 Else -- when p_raw_text is not null
3355
3356 If (p_search_option <> CS_KNOWLEDGE_PUB.INTERMEDIA_SYNTAX)
3357 then
3358 l_query_str := Build_Intermedia_Query( l_query_str, p_search_option);
3359 end if;
3360
3361 -- Call Customer User Hook to customize the Intermedia query string
3362 CS_KNOWLEDGE_CUHK.Text_Query_Rewrite_Post
3363 (
3364 p_api_version => 1.0,
3365 p_init_msg_list => FND_API.G_TRUE,
3366 p_commit => FND_API.G_FALSE,
3367 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3368 x_return_status => l_return_status,
3369 x_msg_count => l_msg_count,
3370 x_msg_data => l_msg_data,
3371 p_raw_query_text => p_raw_text,
3372 p_processed_text_query => l_query_str,
3373 p_search_option => p_search_option,
3374 x_custom_text_query => l_custom_query_str
3375 );
3376 if( l_return_status = FND_API.G_RET_STS_SUCCESS AND
3377 l_custom_query_str is not null )
3378 then
3379 l_query_str := l_custom_query_str;
3380 end if;
3381
3382 -- (TEXTNUM)
3383 if (length(l_raw_text) > MAX_SET_NUMBER_LENGTH)
3384 then
3385 l_sol_number_cond := NULL;
3386 else
3387 l_sol_number_cond := ' or ({a' || l_raw_text || 'a} within NUMBER)*10*10 ';
3388 end if;
3389
3390 if (l_sol_number_cond is not null)
3391 then
3392 l_query_str := '(' || l_query_str || l_sol_number_cond || ')';
3393 end if;
3394 End If; -- End l_raw_text checkl
3395 -- (SMARTSCORE)
3396
3397 l_query_keyword := l_query_str;
3398
3399 -- 2. At this point the l_query_keyword contributes the score due to the raw text.
3400 -- Next, we will include the scores from the filter of product, platfom, category,
3401 -- or statement fitlers only if the smart score mode is ON.
3402 l_smart_score_flag := fnd_profile.value('CS_KB_SMART_SCORING');
3403 If l_smart_score_flag is null or l_smart_score_flag = 'Y' Then
3404 l_query_keyword := Build_Smart_Score_Query(
3405 l_query_keyword,
3406 p_product_id_tbl,
3407 p_platform_id_tbl,
3408 p_category_id_tbl ,
3409 p_statement_id_tbl);
3410 End If;
3411
3412 -- 3. At this point, the format of the l_query_keyword should be correct even in the
3413 -- case that the p_raw_text is empty and the smart score mode is on. It is
3414 -- because the Build_Smart_Score_Query takes care of an empty p_raw_text.
3415 -- Next we will construct the filter section for the query text.
3416 -- (SMARTSCORE)
3417
3418 --3832320
3419 l_lang := userenv('LANG');
3420 l_query_filter := '((a'||l_lang||'a) within LANG) ';
3421
3422
3423 -- 3.1 Generic filters.
3424 -- Bug 3328595 : If any solution type ID is -1, we should not use it a
3425 -- criteria at all
3426 If (p_solution_type_id_tbl is not null and p_solution_type_id_tbl.COUNT > 0) then
3427 -- Check if the ids contains -1, which means search all types.
3428 l_type_is_all := 'N';
3429 l_type_ids_index := p_solution_type_id_tbl.FIRST;
3430 while l_type_ids_index is not null loop
3431 if p_solution_type_id_tbl(l_type_ids_index) = -1 then
3432 l_type_is_all := 'Y';
3433 exit;
3434 end if;
3435 l_type_ids_index := p_solution_type_id_tbl.NEXT(l_type_ids_index);
3436 end loop;
3437 -- Only when we are not searching all, we will add this condition.
3438 if l_type_is_all = 'N' then
3439 l_types := Concat_Ids(p_solution_type_id_tbl, 'a|a');
3440 -- (SMARTSCORE)
3441 l_type_cond := ' AND ((a'|| l_types ||'a) within TYPE)';
3442 end if;
3443 End If;
3444 -- End Bug 3328595
3445
3446 If (l_type_cond is not null)
3447 then
3448 l_query_filter := l_query_filter || l_type_cond;
3449 End if;
3450
3451 If (p_statement_id_tbl is not null and p_statement_id_tbl.COUNT > 0)
3452 then
3453 l_statement_ids := Concat_Ids(p_statement_id_tbl, 'a|a');
3454 --(SMARTSCORE)
3455 l_statement_ids_cond := '((a'|| l_statement_ids ||'a) within STATEMENTS)';
3456 l_statement_ids_keyword := '((a'||Concat_Ids(p_statement_id_tbl, 'a,a')
3457 ||'a) within STATEMENTS)';
3458 End If;
3459
3460 -- Bug 3328595 : The statements condition should have been connected to
3461 -- others by AND instead of OR
3462 If (l_statement_ids_cond is not null)
3463 then
3464 l_query_filter := l_query_filter||' AND '|| l_statement_ids_cond;
3465 End if;
3466 -- End Bug 3328595
3467
3468 -- Bug 3217731, if l_smart_score_flag is on, ignore the rest filters because the only
3469 -- filter we need is (keyword And Lang And security)
3470 If l_smart_score_flag = 'N' Then
3471
3472 If (p_product_id_tbl is not null and p_product_id_tbl.COUNT > 0)
3473 then
3474 l_products := Concat_Ids(p_product_id_tbl, 'a|a');
3475 -- (PRODFILTER)
3476 Open get_product_filter_csr;
3477 Fetch get_product_filter_csr Into l_exclude_generic_soln;
3478 Close get_product_filter_csr;
3479 l_products := 'a' || l_products||'a';
3480 if l_exclude_generic_soln <> 'Y' then
3481 l_products := l_products || '|a000a';
3482 end if;
3483 -- (SMARTSCORE)
3484 l_product_cond := ' AND (('|| l_products ||') within PRODUCTS)';
3485 End If;
3486
3487 If (p_platform_id_tbl is not null and p_platform_id_tbl.COUNT > 0)
3488 then
3489 l_platforms := Concat_Ids(p_platform_id_tbl, 'a|a');
3490 l_platforms := 'a' || l_platforms || 'a|a000a';
3491 -- (SMARTSCORE)
3492 l_platform_cond := ' AND (('|| l_platforms ||') within PLATFORMS)';
3493 End if;
3494
3495 If (p_category_id_tbl is not null and
3496 p_category_id_tbl.COUNT > 0)
3497 then
3498 l_categories := Concat_Ids(p_category_id_tbl, 'a|a');
3499 -- (SMARTSCORE)
3500 l_category_cond := ' AND ((a'|| l_categories ||'a) within CATEGORIES) ';
3501 End If;
3502
3503 If (l_product_cond is not null)
3504 then
3505 l_query_filter := l_query_filter || l_product_cond;
3506 End If;
3507
3508 If (l_platform_cond is not null)
3509 then
3510 l_query_filter := l_query_filter || l_platform_cond;
3511 End If;
3512
3513 If (l_category_cond is not null)
3514 then
3515 l_query_filter := l_query_filter || l_category_cond;
3516 End If;
3517 End If; -- end Bug 3217731 fix
3518
3519 l_query_filter := l_open_text||l_query_filter; -- first level bracket
3520 l_query_filter := l_open_text || l_query_filter||l_close_text ;
3521 l_query_filter := l_query_filter || ' & '|| l_open_text; -- security bracket
3522 l_query_filter:= l_query_filter || '(( ' || l_catgrp_vis ;
3523 l_query_filter:= l_query_filter || ' ) within CATEGORYGROUPS)';
3524 l_query_filter := l_query_filter || l_close_text; -- close security bracket
3525 l_query_filter := l_query_filter || l_close_text; -- close first level bracket
3526 -- End bug 3231550
3527
3528 -- 5. Combine the l_queryt_keyword and the l_query_filter.
3529 -- 5.1 If the l_query_keywrod part is null, we end up returning the l_query_filter. If
3530 -- this is the case, we don't use 100 as the term weight.
3531 -- Q: In what condition will this happen?
3532 -- A: when raw text is empty, product/plaform/category/statement filters are empty
3533 -- and the solution type filter is/is not present. In this case the queryt text must
3534 -- have the <LANG> and <CATEGORYGROUPS> sections, and/or the <TYPE>
3535 -- section.
3536 If l_query_keyword is null OR l_query_keyword = '' Then
3537 l_query_str := l_query_filter||'*10*10';
3538 ELSE
3539 -- 39836966
3540 l_query_filter := l_query_filter||'*10*10';
3541
3542 OPEN Get_Magic_Word_Csr;
3543 FETCH Get_Magic_Word_Csr INTO l_magic_word;
3544 CLOSE Get_Magic_Word_Csr;
3545
3546 l_query_keyword := l_open_text||l_query_keyword||l_close_text||'|('''||l_magic_word||''')';
3547
3548 -- 39836966_eof
3549 l_query_str := l_open_text||l_query_keyword||l_close_text||' & '||l_query_filter;
3550 End If;
3551 Return l_query_str;
3552 End Build_Solution_Text_Query;
3553
3554 -- Constructs the intermedia query that should be used in the
3555 -- CONTAINS predicate for a solution search
3556 --
3557 -- Not includes products and platforms
3558
3559 FUNCTION Build_Simple_Text_Query
3560 (
3561 p_qry_string in varchar2,
3562 p_search_option in number
3563 )
3564 return varchar2
3565 is
3566 l_query_str varchar2(30000) := p_qry_string;
3567
3568 begin
3569
3570 if (p_search_option = CS_KNOWLEDGE_PUB.INTERMEDIA_SYNTAX) -- Intermedia Syntax
3571 then
3572 return l_query_str;
3573 end if;
3574
3575 l_query_str := Build_Intermedia_Query( l_query_str, p_search_option);
3576
3577 return l_query_str;
3578 end Build_Simple_Text_Query;
3579
3580
3581 -- (3468629)
3582 -- Make the old API backward compatible.
3583 FUNCTION Build_Statement_Text_Query
3584 ( p_raw_text in varchar2,
3585 p_statement_type_id_tbl in CS_Knowledge_PUB.number15_tbl_type
3586 )
3587 return varchar2
3588 is
3589 begin
3590 Return Build_Statement_Text_Query (
3591 p_raw_text,
3592 p_statement_type_id_tbl,
3593 CS_KNOWLEDGE_PUB.MATCH_ACCUM -- default to Accumulate
3594 );
3595 end Build_Statement_Text_Query;
3596
3597
3598 FUNCTION Build_Statement_Text_Query
3599 ( p_raw_text in varchar2,
3600 p_statement_type_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
3601 p_search_option in number
3602 )
3603 return varchar2
3604 is
3605 l_query_str varchar2(30000) := p_raw_text;
3606 l_raw_text varchar2(30000) := p_raw_text;
3607 l_lang varchar2(4);
3608
3609 l_types varchar2(2000);
3610 l_type_cond varchar2(2000);
3611 l_type_is_all varchar2(1);
3612 l_type_ids_index number;
3613
3614 l_number_cond varchar2(2000);
3615
3616 l_return_status VARCHAR2(50);
3617 l_msg_count NUMBER;
3618 l_msg_data VARCHAR2(5000);
3619
3620 MAX_SET_NUMBER_LENGTH NUMBER := 30;
3621
3622 l_category_group_id NUMBER;
3623 l_soln_visibility_position NUMBER;
3624 l_catgrp_vis varchar2(2000);
3625
3626
3627 l_query_filter VARCHAR2(30000) := '';
3628 l_query_keyword VARCHAR2(30000) := '';
3629
3630 l_statement_ids_keyword VARCHAR2(30000) ;
3631
3632 Cursor visibility_csr Is
3633 SELECT VISIBILITY_ID
3634 FROM CS_KB_VISIBILITIES_B
3635 WHERE VISIBILITY_ID =
3636 fnd_profile.value('CS_KB_ASSIGNED_SOLUTION_VISIBILITY_LEVEL')
3637 AND sysdate BETWEEN nvl(Start_Date_Active, sysdate-1)
3638 AND nvl(End_Date_Active, sysdate+1);
3639
3640 l_soln_visibility_id NUMBER;
3641
3642 l_stmt_visibility NUMBER;
3643 l_access_level_filter VARCHAR2(500);
3644
3645 -- 39836966_cursor
3646 CURSOR Get_Magic_Word_Csr IS
3647 SELECT fnd_profile.value('CS_KB_SEARCH_NONEXIST_KEYWORD') FROM dual;
3648
3649 l_magic_word VARCHAR2(255);
3650 -- 39836966_cursor_eof
3651 begin
3652 -- Goal: make a query text that consists of
3653 -- score_section + filter_section.
3654
3655 -- 1. Get security.
3656 Open visibility_csr;
3657 Fetch visibility_csr Into l_soln_visibility_id;
3658 Close visibility_csr;
3659 If l_soln_visibility_id Is Null Then
3660 l_soln_visibility_id := 0;
3661 End If;
3662
3663 l_category_group_id :=
3664 CS_KB_SECURITY_PVT.Get_Category_Group_Id();
3665 l_catgrp_vis := TO_CHAR(l_category_group_id) || 'a'
3666 ||to_char(l_soln_visibility_id)||
3667 ' within CATEGORYGROUPS ';
3668
3669 --2. Clean up the p_raw_text.
3670 l_raw_text := Remove_Braces(p_raw_text);
3671 l_raw_text := trim(l_raw_text);
3672 l_query_str := l_raw_text;
3673
3674 --3. Process l_raw_text
3675 If l_query_str is not null Then
3676 If (p_search_option <> CS_KNOWLEDGE_PUB.INTERMEDIA_SYNTAX)
3677 Then
3678 l_query_str := Build_Intermedia_Query(
3679 l_query_str,
3680 p_search_option);
3681 -- At this point, l_query_str should contain
3682 -- searching method operators.
3683 End If;
3684
3685 -- 3.1 Check if we should use the search string
3686 -- add a number.
3687 if (length(l_raw_text) > MAX_SET_NUMBER_LENGTH)
3688 then
3689 l_number_cond := NULL;
3690 else
3691 l_number_cond := ' or ({a' || l_raw_text
3692 || 'a} within NUMBER)*10*10 ';
3693 end if;
3694
3695 if (l_number_cond is not null)
3696 then
3697 l_query_str := '(' || l_query_str || l_number_cond || ')';
3698 end if;
3699 End If; -- End l_raw_text check
3700
3701 l_query_keyword := l_query_str;
3702
3703 -- 4. Add non-scoreable filters: filters should not contribute
3704 -- to the score.
3705 l_query_filter := '(a'||userenv('LANG')||'a within LANG)';
3706
3707 If (p_statement_type_id_tbl is not null
3708 and p_statement_type_id_tbl.COUNT > 0) then
3709 -- Check if the ids contains -1,
3710 -- which means search for all types.
3711 l_type_is_all := 'N';
3712 l_type_ids_index := p_statement_type_id_tbl.FIRST;
3713 while l_type_ids_index is not null loop
3714 if p_statement_type_id_tbl(l_type_ids_index) = -1 then
3715 l_type_is_all := 'Y';
3716 exit;
3717 end if;
3718 l_type_ids_index := p_statement_type_id_tbl.NEXT(l_type_ids_index);
3719 end loop;
3720 if l_type_is_all = 'N' then
3721 l_types := Concat_Ids(p_statement_type_id_tbl, 'a|a');
3722 l_type_cond := ' & ((a'|| l_types ||'a) within TYPE)';
3723 end if;
3724 End If; -- end p_statement_type_id_tbl check
3725
3726 If (l_type_cond is not null)
3727 then
3728 l_query_filter := l_query_filter || l_type_cond;
3729 End if;
3730
3731 l_stmt_visibility :=
3732 CS_KB_SECURITY_PVT.Get_Stmt_Visibility_Position();
3733 l_access_level_filter := '(a'||to_char(l_stmt_visibility)
3734 ||'a within ACCESS)';
3735 l_query_filter := l_query_filter ||' & '||
3736 l_access_level_filter;
3737
3738 -- 5. Add security filter
3739 l_query_filter :=l_query_filter || ' & ('||l_catgrp_vis||')';
3740
3741 -- 6. Combine the l_query_keyword and the l_query_filter.
3742 -- 6.1 If the l_query_keyword is null, we end up returning
3743 --- the l_query_filter. If this is the case, we use 100
3744 -- as the term weight.
3745 If l_query_keyword is null OR l_query_keyword = '' Then
3746 l_query_str := '('||l_query_filter||')*10*10';
3747 ELSE
3748
3749 -- 39836966
3750 OPEN Get_Magic_Word_Csr;
3751 FETCH Get_Magic_Word_Csr INTO l_magic_word;
3752 CLOSE Get_Magic_Word_Csr;
3753
3754 l_query_keyword := '('||l_query_keyword||')'||'|('''||l_magic_word||''')';
3755
3756 -- 39836966_eof
3757 l_query_str := '('||l_query_keyword||')'||' & '
3758 ||'('||l_query_filter||')*10*10';
3759 End If;
3760
3761 Return l_query_str;
3762 End Build_Statement_Text_Query;
3763 -- end 3468629
3764
3765 -- NOTE: This api code has been duplicated to Find_Sets_Matching2 for bug 4304939
3766 -- Any changes made to this api should also be replicated to the new Find_Sets_Matching2
3767 PROCEDURE Find_Sets_Matching (
3768 p_api_version in number,
3769 p_init_msg_list in varchar2, -- := FND_API.G_FALSE,
3770 p_validation_level in number, -- := FND_API.G_VALID_LEVEL_FULL,
3771 x_return_status OUT NOCOPY varchar2,
3772 x_msg_count OUT NOCOPY number,
3773 x_msg_data OUT NOCOPY varchar2,
3774 p_pos_query_str in varchar2,
3775 p_neg_query_str in varchar2 := null,
3776 p_type_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
3777 p_other_criteria in varchar2 := NULL,
3778 p_rows in number,
3779 p_start_score in number := null,
3780 p_start_id in number := null,
3781 p_start_row in number, -- := 1,
3782 p_get_total_flag in varchar2, -- := FND_API.G_FALSE,
3783 x_set_tbl in OUT NOCOPY CS_Knowledge_PUB.set_res_tbl_type,
3784 x_total_rows OUT NOCOPY number,
3785 p_search_option in number := null
3786 )is
3787 l_api_name CONSTANT varchar2(30) := 'Find_Sets_Matching';
3788 l_api_version CONSTANT number := 1.0;
3789 l_type_cond varchar2(500) := null;
3790 l_types varchar2(1000);
3791 l_qstr varchar2(1990);
3792 l_sets_csr CS_Knowledge_PUB.general_csr_type;
3793 l_set_rec CS_Knowledge_PUB.set_res_rec_type;
3794 l_score_cond varchar2(100) := null;
3795 l_id_cond varchar2(100) := null;
3796 l_end_row number;
3797 --l_str_p varchar2(2000) := p_pos_query_str;
3798 --l_str_n varchar2(2000) := p_neg_query_str;
3799
3800 l_sql_srows varchar2(30) :=
3801 ' select count(*) ';
3802
3803 l_sql_s varchar2(500) :=
3804 ' select /*+ FIRST_ROWS */ cs_kb_sets_tl.set_id id, score(10) score, cs_kb_sets_b.set_type_id,'||
3805 ' cs_kb_sets_tl.name, cs_kb_sets_tl.description, '||
3806 ' cs_kb_sets_b.creation_date, cs_kb_sets_b.created_by,'||
3807 ' cs_kb_sets_b.last_update_date, cs_kb_sets_b.last_updated_by, '||
3808 ' cs_kb_sets_b.last_update_login, cs_kb_set_types_tl.name, '||
3809 ' cs_kb_sets_b.set_number ';
3810 l_sql_f1 varchar2(100) :=
3811 ' from cs_kb_sets_tl, cs_kb_sets_b, cs_kb_set_types_tl ';
3812
3813 l_sql_w1 varchar2(200) :=
3814 ' where contains(cs_kb_sets_tl.composite_assoc_index, :a1, 10)>0 ';
3815 l_sql_wrows varchar2(200) :=
3816 ' where contains(cs_kb_sets_tl.composite_assoc_index, :a1)>0 ';
3817 l_sql_w varchar2(500) :=
3818 -- 3398078
3819 ' and cs_kb_sets_tl.set_id = cs_kb_sets_b.set_id '||
3820 -- ' and cs_kb_sets_tl.language=userenv(''LANG'') ' ||
3821 ' and cs_kb_set_types_tl.set_type_id = cs_kb_sets_b.set_type_id '||
3822 ' and cs_kb_set_types_tl.language=userenv(''LANG'') '; -- ||
3823
3824 l_sql_o varchar2(100) := ' order by score desc ';
3825
3826 l_sql_contains varchar2(2000);
3827 l_lang varchar2(4);
3828 l_escaped_query_str varchar2(2000);
3829
3830 l_sqlerr_pos pls_integer;
3831
3832 begin
3833 savepoint Find_Sets_Matching_PVT;
3834
3835 if not FND_API.Compatible_API_Call(
3836 l_api_version,
3837 p_api_version,
3838 l_api_name,
3839 G_PKG_NAME) then
3840 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3841 end if;
3842
3843 if FND_API.to_Boolean(p_init_msg_list) then
3844 FND_MSG_PUB.initialize;
3845 end if;
3846
3847 x_return_status := FND_API.G_RET_STS_SUCCESS;
3848
3849 -- -- -- -- begin my code -- -- -- -- --
3850
3851 if(x_set_tbl is null) then
3852 x_set_tbl := cs_knowledge_pub.set_res_tbl_type();
3853 end if;
3854
3855 --process strings
3856 if(p_pos_query_str is not null) then
3857 l_sql_contains := p_pos_query_str;
3858 end if;
3859 if(p_neg_query_str is not null) then
3860 l_sql_contains := l_sql_contains ||' '||p_neg_query_str;
3861 end if;
3862
3863
3864 if l_sql_contains is null then
3865 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
3866 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
3867 fnd_msg_pub.Add;
3868 end if;
3869 raise FND_API.G_EXC_ERROR; --goto error_found;
3870 end if;
3871
3872 l_sql_contains := Build_Solution_Text_Query
3873 ( p_raw_text => l_sql_contains,
3874 p_solution_type_id_tbl => p_type_id_tbl,
3875 p_search_option => p_search_option);
3876
3877 -- (cancel) get score and id conditions
3878
3879 OPEN l_sets_csr FOR
3880 l_sql_s || l_sql_f1 ||l_sql_w1 || l_sql_w ||
3881 p_other_criteria ||l_sql_o
3882 USING l_sql_contains;
3883
3884 l_end_row := p_start_row + p_rows -1;
3885 for i in 1..l_end_row loop
3886 fetch l_sets_csr into l_set_rec;
3887 exit when l_sets_csr%NOTFOUND;
3888 if(i>=p_start_row) then
3889 x_set_tbl.EXTEND(1);
3890 x_set_tbl(x_set_tbl.LAST) := l_set_rec;
3891 end if;
3892 end loop;
3893 close l_sets_csr;
3894
3895 -- if get total rowcount
3896 if(p_get_total_flag = FND_API.G_TRUE) then
3897
3898 OPEN l_sets_csr FOR
3899 l_sql_srows || l_sql_f1 ||l_sql_w1 || l_sql_w ||
3900 p_other_criteria
3901 USING l_sql_contains;
3902 fetch l_sets_csr into x_total_rows;
3903 close l_sets_csr;
3904
3905 end if;
3906 -- -- -- -- end of code -- -- --
3907
3908 -- Standard call to get message count. if count=1, get message info.
3909 FND_MSG_PUB.Count_And_Get(
3910 p_count => x_msg_count,
3911 p_data => x_msg_data );
3912
3913 EXCEPTION
3914 WHEN FND_API.G_EXC_ERROR THEN
3915 ROLLBACK TO Find_Sets_Matching_PVT;
3916 x_return_status := FND_API.G_RET_STS_ERROR ;
3917 FND_MSG_PUB.Count_And_Get(
3918 p_count => x_msg_count,
3919 p_data => x_msg_data );
3920 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3921 ROLLBACK TO Find_Sets_Matching_PVT;
3922 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3923 FND_MSG_PUB.Count_And_Get(
3924 p_count => x_msg_count,
3925 p_data => x_msg_data);
3926 WHEN OTHERS THEN
3927
3928 -- trap interMedia query exceptions and default to keyword searc
3929 l_sqlerr_pos := instr(SQLERRM, 'DRG-11440', 1);
3930
3931 --dbms_output.put_line('_'||substr(SQLERRM, 1, 240));
3932 --dbms_output.put_line(substr(SQLERRM, 241, 240)||'-');
3933 --dbms_output.put_line('EXCEPTION: 123-'||to_char(l_sqlerr_pos)||'-');
3934
3935 if(l_sqlerr_pos>0) then
3936
3937 --dbms_output.put_line('can run alternative query here');
3938 Find_Sets_Matching(
3939 p_api_version => p_api_version,
3940 p_init_msg_list => p_init_msg_list,
3941 p_validation_level => p_validation_level,
3942 x_return_status => x_return_status,
3943 x_msg_count => x_msg_count,
3944 x_msg_data => x_msg_data,
3945 p_pos_query_str => p_pos_query_str,
3946 p_neg_query_str => p_neg_query_str,
3947 p_type_id_tbl => p_type_id_tbl,
3948 p_other_criteria => p_other_criteria,
3949 p_rows => p_rows,
3950 p_start_score => p_start_score,
3951 p_start_id => p_start_id,
3952 p_start_row => p_start_row,
3953 p_get_total_flag => p_get_total_flag,
3954 x_set_tbl => x_set_tbl,
3955 x_total_rows => x_total_rows,
3956 p_search_option => CS_KNOWLEDGE_PUB.MATCH_ANY);
3957 else
3958 --no data to rollback in query api ROLLBACK TO Find_Sets_Matching_PVT;
3959 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3960 IF FND_MSG_PUB.Check_Msg_Level
3961 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3962 FND_MSG_PUB.Add_Exc_Msg(
3963 G_PKG_NAME,
3964 l_api_name);
3965 END IF;
3966 FND_MSG_PUB.Count_And_Get(
3967 p_count => x_msg_count,
3968 p_data => x_msg_data);
3969 end if;
3970
3971 end Find_Sets_Matching;
3972
3973 -- NOTE: This api code has been duplicated from Find_Sets_Matching for bug 4304939
3974 -- Any changes made to this api should also be replicated to the new Find_Sets_Matching
3975 PROCEDURE Find_Sets_Matching2 (
3976 p_api_version in number,
3977 p_init_msg_list in varchar2, -- := FND_API.G_FALSE,
3978 p_validation_level in number, -- := FND_API.G_VALID_LEVEL_FULL,
3979 x_return_status OUT NOCOPY varchar2,
3980 x_msg_count OUT NOCOPY number,
3981 x_msg_data OUT NOCOPY varchar2,
3982 p_pos_query_str in varchar2,
3983 --p_neg_query_str in varchar2 := null,
3984 p_type_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
3985 p_other_criteria in varchar2,
3986 p_other_value in number,
3987 p_rows in number,
3988 p_start_score in number := null,
3989 p_start_id in number := null,
3990 p_start_row in number, -- := 1,
3991 p_get_total_flag in varchar2, -- := FND_API.G_FALSE,
3992 x_set_tbl in OUT NOCOPY CS_Knowledge_PUB.set_res_tbl_type,
3993 x_total_rows OUT NOCOPY number,
3994 p_search_option in number := null
3995 )is
3996 l_api_name CONSTANT varchar2(30) := 'Find_Sets_Matching';
3997 l_api_version CONSTANT number := 1.0;
3998 l_type_cond varchar2(500) := null;
3999 l_types varchar2(1000);
4000 l_qstr varchar2(1990);
4001 l_sets_csr CS_Knowledge_PUB.general_csr_type;
4002 l_set_rec CS_Knowledge_PUB.set_res_rec_type;
4003 l_score_cond varchar2(100) := null;
4004 l_id_cond varchar2(100) := null;
4005 l_end_row number;
4006 --l_str_p varchar2(2000) := p_pos_query_str;
4007 --l_str_n varchar2(2000) := p_neg_query_str;
4008
4009 l_sql_srows varchar2(30) :=
4010 ' select count(*) ';
4011
4012 l_sql_s varchar2(500) :=
4013 ' select /*+ FIRST_ROWS */ cs_kb_sets_tl.set_id id, score(10) score, cs_kb_sets_b.set_type_id,'||
4014 ' cs_kb_sets_tl.name, cs_kb_sets_tl.description, '||
4015 ' cs_kb_sets_b.creation_date, cs_kb_sets_b.created_by,'||
4016 ' cs_kb_sets_b.last_update_date, cs_kb_sets_b.last_updated_by, '||
4017 ' cs_kb_sets_b.last_update_login, cs_kb_set_types_tl.name ' ||
4018 ' cs_kb_sets_b.set_number ';
4019 l_sql_f1 varchar2(100) :=
4020 ' from cs_kb_sets_tl, cs_kb_sets_b, cs_kb_set_types_tl ';
4021
4022 l_sql_w1 varchar2(200) :=
4023 ' where contains(cs_kb_sets_tl.composite_assoc_index, :1, 10)>0 ';
4024 l_sql_w varchar2(500) :=
4025 -- 3398078
4026 ' and cs_kb_sets_tl.set_id = cs_kb_sets_b.set_id '||
4027 ' and cs_kb_set_types_tl.set_type_id = cs_kb_sets_b.set_type_id '||
4028 ' and cs_kb_set_types_tl.language=userenv(''LANG'') ';
4029
4030 l_sql_o varchar2(100) := ' order by score desc ';
4031
4032 l_sql_contains varchar2(2000);
4033 l_lang varchar2(4);
4034 l_escaped_query_str varchar2(2000);
4035
4036 l_sqlerr_pos pls_integer;
4037
4038 begin
4039 savepoint Find_Sets_Matching_PVT;
4040
4041 if not FND_API.Compatible_API_Call(
4042 l_api_version,
4043 p_api_version,
4044 l_api_name,
4045 G_PKG_NAME) then
4046 raise FND_API.G_EXC_UNEXPECTED_ERROR;
4047 end if;
4048
4049 if FND_API.to_Boolean(p_init_msg_list) then
4050 FND_MSG_PUB.initialize;
4051 end if;
4052
4053 x_return_status := FND_API.G_RET_STS_SUCCESS;
4054
4055 -- -- -- -- begin my code -- -- -- -- --
4056
4057 if(x_set_tbl is null) then
4058 x_set_tbl := cs_knowledge_pub.set_res_tbl_type();
4059 end if;
4060
4061 --process strings
4062 if(p_pos_query_str is not null) then
4063 l_sql_contains := p_pos_query_str;
4064 end if;
4065
4066 if l_sql_contains is null then
4067 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
4068 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
4069 fnd_msg_pub.Add;
4070 end if;
4071 raise FND_API.G_EXC_ERROR; --goto error_found;
4072 end if;
4073
4074 l_sql_contains := Build_Solution_Text_Query
4075 ( p_raw_text => l_sql_contains,
4076 p_solution_type_id_tbl => p_type_id_tbl,
4077 p_search_option => p_search_option);
4078
4079 -- (cancel) get score and id conditions
4080 OPEN l_sets_csr FOR
4081 l_sql_s || l_sql_f1 ||l_sql_w1 || l_sql_w ||
4082 p_other_criteria ||l_sql_o
4083 USING l_sql_contains, p_other_value;
4084
4085 l_end_row := p_start_row + p_rows -1;
4086 for i in 1..l_end_row loop
4087 fetch l_sets_csr into l_set_rec;
4088 exit when l_sets_csr%NOTFOUND;
4089 if(i>=p_start_row) then
4090 x_set_tbl.EXTEND(1);
4091 x_set_tbl(x_set_tbl.LAST) := l_set_rec;
4092 end if;
4093 end loop;
4094 close l_sets_csr;
4095
4096 -- if get total rowcount
4097 if(p_get_total_flag = FND_API.G_TRUE) then
4098
4099 OPEN l_sets_csr FOR
4100 l_sql_srows || l_sql_f1 ||l_sql_w1 || l_sql_w ||
4101 p_other_criteria
4102 USING l_sql_contains, p_other_value;
4103 fetch l_sets_csr into x_total_rows;
4104 close l_sets_csr;
4105
4106 end if;
4107 -- -- -- -- end of code -- -- --
4108
4109 -- Standard call to get message count. if count=1, get message info.
4110 FND_MSG_PUB.Count_And_Get(
4111 p_count => x_msg_count,
4112 p_data => x_msg_data );
4113
4114 EXCEPTION
4115 WHEN FND_API.G_EXC_ERROR THEN
4116 ROLLBACK TO Find_Sets_Matching_PVT;
4117 x_return_status := FND_API.G_RET_STS_ERROR ;
4118 FND_MSG_PUB.Count_And_Get(
4119 p_count => x_msg_count,
4120 p_data => x_msg_data );
4121 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4122
4123 ROLLBACK TO Find_Sets_Matching_PVT;
4124 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4125 FND_MSG_PUB.Count_And_Get(
4126 p_count => x_msg_count,
4127 p_data => x_msg_data);
4128 WHEN OTHERS THEN
4129
4130 -- trap interMedia query exceptions and default to keyword searc
4131 l_sqlerr_pos := instr(SQLERRM, 'DRG-11440', 1);
4132
4133 --dbms_output.put_line('_'||substr(SQLERRM, 1, 240));
4134 --dbms_output.put_line(substr(SQLERRM, 241, 240)||'-');
4135 --dbms_output.put_line('EXCEPTION: 123-'||to_char(l_sqlerr_pos)||'-');
4136
4137 if(l_sqlerr_pos>0) then
4138
4139 --dbms_output.put_line('can run alternative query here');
4140 Find_Sets_Matching2(
4141 p_api_version => p_api_version,
4142 p_init_msg_list => p_init_msg_list,
4143 p_validation_level => p_validation_level,
4144 x_return_status => x_return_status,
4145 x_msg_count => x_msg_count,
4146 x_msg_data => x_msg_data,
4147 p_pos_query_str => p_pos_query_str,
4148 --p_neg_query_str => p_neg_query_str,
4149 p_type_id_tbl => p_type_id_tbl,
4150 p_other_criteria => p_other_criteria,
4151 p_other_value => p_other_value,
4152 p_rows => p_rows,
4153 p_start_score => p_start_score,
4154 p_start_id => p_start_id,
4155 p_start_row => p_start_row,
4156 p_get_total_flag => p_get_total_flag,
4157 x_set_tbl => x_set_tbl,
4158 x_total_rows => x_total_rows,
4159 p_search_option => CS_KNOWLEDGE_PUB.MATCH_ANY);
4160 else
4161 --no data to rollback in query api ROLLBACK TO Find_Sets_Matching_PVT;
4162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4163 IF FND_MSG_PUB.Check_Msg_Level
4164 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4165 FND_MSG_PUB.Add_Exc_Msg(
4166 G_PKG_NAME,
4167 l_api_name);
4168 END IF;
4169 FND_MSG_PUB.Count_And_Get(
4170 p_count => x_msg_count,
4171 p_data => x_msg_data);
4172 end if;
4173
4174 end Find_Sets_Matching2;
4175
4176 --(SRCHEFF)
4177 PROCEDURE Process_Frequency_Keyword (
4178 p_query_str in out nocopy varchar2,
4179 p_search_option in Number) IS
4180 l_magic_word VARCHAR2(240) := null;
4181 l_not_use_freq VARCHAR2(1) := 'Y';
4182 Cursor Get_Frequency_Csr Is
4183 Select nvl(fnd_profile.value('CS_KB_SEARCH_FREQUENCY_MODE'), 'Y') from dual;
4184
4185 Cursor Get_Magic_Word_Csr Is
4186 Select fnd_profile.value('CS_KB_SEARCH_NONEXIST_KEYWORD') from dual;
4187
4188 l_query VARCHAR2(32000) := p_query_str;
4189
4190 --3534598. We can use substr because value from
4191 --v$nl_parameters is always single-byte.
4192 Cursor get_numeric_decimal Is
4193 select substr(value, 1,1) from v$nls_parameters
4194 where parameter = 'NLS_NUMERIC_CHARACTERS';
4195
4196 l_decimal VARCHAR2(1);
4197 l_freq_term_weight VARCHAR2(30);
4198
4199 BEGIN
4200 Open Get_Frequency_Csr;
4201 Fetch Get_Frequency_Csr Into l_not_use_freq;
4202 Close Get_Frequency_Csr;
4203
4204 If l_not_use_freq = 'N' Then
4205 If p_search_option = CS_KNOWLEDGE_PUB.MATCH_ACCUM Then
4206 -- For Accum operator, add magicword as
4207 -- e.g. (A, B, C, magicword*0.1*0.1*0.1)
4208 Open Get_Magic_Word_Csr;
4209 Fetch Get_Magic_Word_Csr Into l_magic_word;
4210 Close Get_Magic_Word_Csr;
4211
4212 If l_magic_word Is Not Null Then
4213 -- 3534598
4214 -- Process term weight.
4215 Open get_numeric_decimal;
4216 Fetch get_numeric_decimal Into l_decimal;
4217 Close get_numeric_decimal;
4218
4219 If l_decimal is Null Then
4220 l_decimal := '.';
4221 End If;
4222
4223 -- Since Escape_Special_Char does not take care
4224 -- of the space, we need to handle it separately.
4225 If l_decimal = ' ' Then
4226 l_freq_term_weight := '0\ 1';
4227 Else
4228 l_freq_term_weight := '0'||l_decimal||'1';
4229 l_freq_term_weight := Escape_Special_Char(l_freq_term_weight);
4230 End If;
4231
4232 l_freq_term_weight:= l_freq_term_weight||'*'||l_freq_term_weight
4233 ||'*'||l_freq_term_weight;
4234
4235 p_query_str := p_query_str||', '''||l_magic_word
4236 ||'''*'||l_freq_term_weight;
4237 End If;
4238
4239 Elsif p_search_option = CS_KNOWLEDGE_PUB.MATCH_ALL
4240 OR p_search_option = CS_KNOWLEDGE_PUB.MATCH_PHRASE Then
4241 -- For ALL operator and Exact Phrase, amplify score by 100
4242 p_query_str := '('||p_query_str||')*10*10';
4243 Else
4244 null;
4245
4246 End If;
4247 End If;
4248 Exception
4249 WHEN OTHERS THEN
4250 p_query_str := l_query;
4251 END;
4252
4253 -- (SMARTCODE)
4254 -- Construct a text query that accumulates scores from each filter.
4255 -- This function also takes care the empty p_current_query case.
4256 FUNCTION Build_Smart_Score_Query
4257 (
4258 p_current_query in varchar2,
4259 p_product_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
4260 p_platform_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
4261 p_category_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
4262 p_statement_id_tbl in CS_Knowledge_PUB.number15_tbl_type
4263 ) return varchar2
4264 Is
4265 l_final_query VARCHAR2(4000) := '';
4266 l_temp_query VARCHAR2(2000) := '';
4267 l_first VARCHAR2(1) := 'Y';
4268 l_connector VARCHAR2(1) := ',';
4269 Begin
4270
4271 If p_current_query is null Or p_current_query = '' Then
4272 l_connector := '';
4273 Else
4274 l_final_query := p_current_query;
4275 End If;
4276
4277 If (p_product_id_tbl is not null and p_product_id_tbl.COUNT > 0)
4278 Then
4279 l_temp_query := Concat_Ids(p_product_id_tbl, 'a,a');
4280 l_final_query :=l_final_query|| l_connector||' ((a'|| l_temp_query ||'a) within PRODUCTS)';
4281 -- after use the l_connector, always set it to ','
4282 l_connector := ',';
4283 End If;
4284
4285 If (p_platform_id_tbl is not null and p_platform_id_tbl.COUNT > 0)
4286 then
4287 l_temp_query := Concat_Ids(p_platform_id_tbl, 'a,a');
4288 l_final_query := l_final_query||l_connector||' ((a'|| l_temp_query ||'a) within PLATFORMS)';
4289 -- after use the l_connector, always set it to ','
4290 l_connector := ',';
4291 End if;
4292
4293 If (p_category_id_tbl is not null and p_category_id_tbl.COUNT > 0)
4294 then
4295 l_temp_query := Concat_Ids(p_category_id_tbl, 'a,a');
4296 l_final_query :=l_final_query||l_connector
4297 || ' ((a'|| l_temp_query ||'a) within CATEGORIES) ';
4298 -- after use the l_connector, always set it to ','
4299 l_connector := ',';
4300 End If;
4301
4302 If (p_statement_id_tbl is not null and p_statement_id_tbl.COUNT > 0)
4303 then
4304 l_temp_query := Concat_Ids(p_statement_id_tbl, 'a,a');
4305 l_final_query :=l_final_query||l_connector
4306 || ' ((a'|| l_temp_query ||'a) within STATEMENTS) ';
4307 -- after use the l_connector, always set it to ','
4308 l_connector := ',';
4309 End If;
4310
4311 Return l_final_query;
4312 Exception
4313 WHEN OTHERS THEN
4314 return p_current_query;
4315 End;
4316
4317 -- 3341248
4318 -- Constructs the Text query that should be used in the
4319 -- CONTAINS predicate for a related statement search
4320 -- Handles statement id.
4321 -- By default, it generates an Accumulate string if more
4322 -- than one statement ids are given.
4323 FUNCTION Build_Related_Stmt_Text_Query
4324 ( p_statement_id_tbl in CS_Knowledge_PUB.number15_tbl_type )
4325 return varchar2
4326 is
4327 l_lang_str varchar2(200);
4328
4329 l_statement_ids varchar2(30000);
4330 l_statement_ids_cond varchar2(30000);
4331
4332 l_category_group_id NUMBER;
4333 l_soln_visibility_id NUMBER;
4334 -- l_soln_visibility VARCHAR2(30);
4335 l_stmt_visibility NUMBER;
4336 l_catgrp_vis varchar2(2000);
4337 l_query_str VARCHAR2(30000) := '';
4338
4339
4340 l_stmt_ids_exclude VARCHAR2(30000) ;
4341 l_stmt_ids_exclude_cond VARCHAR2(30000);
4342 l_access_level_filter VARCHAR2(500);
4343
4344 Cursor visibility_csr Is
4345 SELECT VISIBILITY_ID
4346 FROM CS_KB_VISIBILITIES_B
4347 WHERE VISIBILITY_ID =
4348 fnd_profile.value('CS_KB_ASSIGNED_SOLUTION_VISIBILITY_LEVEL')
4349 AND sysdate BETWEEN nvl(Start_Date_Active, sysdate-1)
4350 AND nvl(End_Date_Active, sysdate+1);
4351
4352 begin
4353 -- Get security.
4354 Open visibility_csr;
4355 Fetch visibility_csr Into l_soln_visibility_id;
4356 Close visibility_csr;
4357 If l_soln_visibility_id Is Null Then
4358 l_soln_visibility_id := 0;
4359 End If;
4360
4361 l_category_group_id :=
4362 CS_KB_SECURITY_PVT.Get_Category_Group_Id();
4363 l_catgrp_vis := '('||TO_CHAR(l_category_group_id) || 'a'
4364 ||to_char(l_soln_visibility_id)||' within CATEGORYGROUPS )';
4365 l_stmt_visibility := CS_KB_SECURITY_PVT.Get_Stmt_Visibility_Position();
4366
4367 If (p_statement_id_tbl is not null
4368 and p_statement_id_tbl.COUNT > 0)
4369 then
4370 l_statement_ids := Concat_Ids(p_statement_id_tbl, ' , ');
4371 l_stmt_ids_exclude := Concat_Ids(p_statement_id_tbl, 'a|a');
4372 l_statement_ids_cond :=
4373 '(('||l_statement_ids ||') within RELATEDSTMTS)';
4374 l_stmt_ids_exclude_cond :=
4375 '(a'||l_stmt_ids_exclude||'a) within STATEMENTID ';
4376
4377 end if;
4378
4379 l_lang_str := '(a'||userenv('LANG')||'a within LANG)';
4380 l_access_level_filter := '(a'||to_char(l_stmt_visibility)
4381 ||'a within ACCESS)';
4382
4383 If l_statement_ids_cond is not null AND
4384 l_stmt_ids_exclude_cond is not null
4385 then
4386 l_query_str :=
4387 l_statement_ids_cond ||
4388 '~ ('||l_stmt_ids_exclude_cond||')';
4389 l_query_str := '('||l_query_str ||') AND '||
4390 '('||l_lang_str||' AND '||l_access_level_filter||
4391 ' AND '||l_catgrp_vis||')*10*10*10*10';
4392
4393 end if;
4394
4395 Return l_query_str;
4396 End Build_Related_Stmt_Text_Query;
4397
4398 -- end 3341248
4399
4400 -- Build the SR text quert.
4401 -- Build a text query for cs_incidents_all_tl.text_index column.
4402 FUNCTION Build_SR_Text_Query
4403 ( p_string in varchar2,
4404 p_search_option in number )
4405 return varchar2
4406 is
4407 l_about_query varchar2(32000) := p_string;
4408 l_keyword_query varchar2(32000) := p_string;
4409 l_iQuery_str varchar2(32000); -- final intermedia query string
4410 lenb integer;
4411 len integer;
4412 begin
4413
4414 Return Build_SR_Text_Query(p_string, null, p_search_option);
4415
4416 end Build_SR_Text_Query;
4417
4418
4419 -- Build the SR text quert.- 2
4420 -- Build a text query for cs_incidents_all_tl.text_index column.
4421 -- The query will use the following sections: PRODUCT, LANG, and TYPE.
4422 FUNCTION Build_SR_Text_Query
4423 ( p_string in varchar2,
4424 p_item_id in NUMBER,
4425 p_search_option in number )
4426 return varchar2
4427 is
4428 l_about_query varchar2(32000) := p_string;
4429 l_keyword_query varchar2(32000) := p_string;
4430 l_iQuery_str varchar2(32000); -- final intermedia query string
4431 lenb integer;
4432 len integer;
4433
4434 l_filter varchar2(32000);
4435
4436 Cursor get_system_security_csr is
4437 select sr_agent_security from CS_SYSTEM_OPTIONS
4438 where rownum = 1;
4439
4440 Cursor get_secured_SR_types_csr Is
4441 SELECT incident_type_id
4442 FROM cs_sr_type_mapping csmap
4443 WHERE csmap.responsibility_id = fnd_global.resp_id
4444 AND trunc(sysdate)between trunc(nvl(csmap.start_date, sysdate))
4445 AND trunc(nvl(csmap.end_date,sysdate));
4446
4447 l_security_state VARCHAR2(200);
4448 l_security_str VARCHAR2(32000);
4449 l_first boolean;
4450
4451 -- 39836966_cursor
4452 CURSOR Get_Magic_Word_Csr IS
4453 SELECT fnd_profile.value('CS_KB_SEARCH_NONEXIST_KEYWORD') FROM dual;
4454
4455 l_magic_word VARCHAR2(255);
4456 -- 39836966_cursor_eof
4457 begin
4458
4459 -- If the Search option chosen is THEME Search or if there is
4460 -- no search option chosen, then rewrite the raw text query
4461 -- with the theme search query and concatenate it with a regular
4462 -- non-theme based rewritten query
4463 if (p_search_option = CS_KNOWLEDGE_PUB.THEME_BASED or
4464 p_search_option is null) --DEFAULT
4465 then
4466 l_keyword_query :=
4467 Build_Keyword_Query
4468 ( p_string => l_keyword_query,
4469 p_search_option=> null);
4470 l_about_query :=
4471 Build_Intermedia_Theme_Query( Escape_Special_Char(l_about_query) );
4472 l_iQuery_str := '('||l_about_query||' OR '||l_keyword_query||')';
4473 else
4474 -- Else just build the standard, non-theme based rewritten query
4475 l_keyword_query :=
4476 Build_Keyword_Query
4477 ( p_string => l_keyword_query,
4478 p_search_option => p_search_option );
4479
4480 l_iQuery_str := '( ' || l_keyword_query || ' )';
4481 end if;
4482
4483 -- always add language
4484 l_filter := '((a'||userenv('LANG')||'a) within LANG)';
4485
4486 if p_item_id is not null then
4487 l_filter := l_filter ||' & (('||to_char(p_item_id)||') within ITEM)';
4488 end if;
4489
4490 -- Check security setup
4491 Open get_system_security_csr;
4492 Fetch get_system_security_csr Into l_security_state;
4493 Close get_system_security_csr;
4494
4495 -- There are 3 security states:
4496 -- 'BSTANDARD' = standard security
4497 -- 'CCUSTOM' = custom security
4498 -- 'ANONE' = no security
4499 If l_security_state is not null AND
4500 l_security_state = 'BSTANDARD' Then
4501 -- Get a list of SR types linked to the current resp.
4502 l_first := true;
4503 for r1 in get_secured_SR_types_csr loop
4504 if l_first then
4505 l_first := false;
4506 else
4507 l_security_str := l_security_str||'|';
4508 end if;
4509
4510 l_security_str := l_security_str ||to_char(r1.incident_type_id);
4511 end loop;
4512
4513 if l_security_str is not null then
4514 l_filter := l_filter ||'&(('||l_security_str||') within SRTYPE)';
4515 end if;
4516 End If;
4517
4518 -- 39836966
4519 OPEN Get_Magic_Word_Csr;
4520 FETCH Get_Magic_Word_Csr INTO l_magic_word;
4521 CLOSE Get_Magic_Word_Csr;
4522
4523 l_iQuery_str := '(('||l_iQuery_str||')'||'|('''||l_magic_word||'''))';
4524
4525 -- 39836966_eof
4526
4527 l_iQuery_str := l_iQuery_str||'&('||l_filter||')*10*10';
4528
4529 -- Return the rewritten text query criteria
4530 return l_iQuery_str;
4531
4532 end Build_SR_Text_Query;
4533 end CS_Knowledge_Pvt;