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