[Home] [Help]
PACKAGE: APPS.CS_KNOWLEDGE_PVT
Source
1 PACKAGE CS_Knowledge_Pvt AS
2 /* $Header: csvkbs.pls 120.0 2005/06/01 10:12:31 appldev noship $ */
3
4 --
5 -- CONSTANTS
6 --
7
8 G_PKG_NAME CONSTANT VARCHAR2(50) := 'CS_Knowledge_PVT';
9
10 -- G_YES CONSTANT VARCHAR2(1) :='Y';
11 -- G_NO CONSTANT VARCHAR2(1) :='N';
12
13 G_PF CONSTANT VARCHAR2(2) :='PF'; -- triggered when user clicked "Yes", it solved my problem
14 G_NF CONSTANT VARCHAR2(2) :='NF'; -- triggered when user clicked "No", didn't solve my problem
15 G_VS CONSTANT VARCHAR2(2) :='VS'; -- triggered when solution is viewed
16
17 -- for return status
18 ERROR_STATUS CONSTANT NUMBER := -1;
19 OKAY_STATUS CONSTANT NUMBER := 0;
20
21 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
22 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
23 G_VALID_LEVEL_TYPESOK CONSTANT NUMBER := 50;
24
25 --
26 -- TYPES AND MISSING CONSTANTS
27 --
28 G_MISS_NUM15 CONSTANT NUMBER(15) := -9E14;
29
30 --
31 -- Procedures and Functions
32 --
33
34 --
35 -- Utility functions
36 --
37
38 FUNCTION Build_Solution_Text_Query
39 (
40 p_raw_text in varchar2,
41 p_solution_type_id_tbl in CS_Knowledge_PUB.number15_tbl_type
42 )
43 return varchar2;
44
45 FUNCTION Build_Solution_Text_Query
46 (
47 p_raw_text in varchar2,
48 p_solution_type_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
49 p_search_option in number
50 )
51 return varchar2;
52
53 FUNCTION Build_Solution_Text_Query
54 (
55 p_raw_text in varchar2,
56 p_solution_type_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
57 p_product_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
58 p_platform_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
59 p_search_option in number
60 )
61 return varchar2;
62
63 FUNCTION Build_Solution_Text_Query
64 (
65 p_raw_text in varchar2,
66 p_solution_type_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
67 p_product_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
68 p_platform_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
69 p_category_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
70 p_search_option in number
71 )
72 return varchar2;
73
74 FUNCTION Build_Solution_Text_Query
75 (
76 p_raw_text in varchar2,
77 p_solution_type_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
78 p_product_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
79 p_platform_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
80 p_category_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
81 p_statement_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
82 p_search_option in number
83 )
84 return varchar2;
85
86 FUNCTION Build_Simple_Text_Query
87 (
88 p_qry_string in varchar2,
89 p_search_option in number
90 )
91 return varchar2;
92
93
94 FUNCTION Build_Statement_Text_Query
95 (
96 p_raw_text in varchar2,
97 p_statement_type_id_tbl in CS_Knowledge_PUB.number15_tbl_type
98 )
99 return varchar2;
100
101 -- 3468629
102 FUNCTION Build_Statement_Text_Query
103 (
104 p_raw_text in varchar2,
105 p_statement_type_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
106 p_search_option in number
107 )
108 return varchar2;
109
110 -- Build_Keyword_Query is for KM internal use only
111 FUNCTION Build_Keyword_Query
112 (
113 p_string IN VARCHAR2,
114 p_search_option IN NUMBER
115 )
116 RETURN VARCHAR2;
117
118 FUNCTION Bind_Var_String(
119 p_start_num in number,
120 p_size in number
121 )return varchar2;
122
123
124 FUNCTION Concat_Ids(
125 p_id_tbl in cs_kb_number_tbl_type,
126 p_separator in varchar2
127 ) return varchar2;
128
129 FUNCTION Concat_Ids(
130 p_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
131 p_separator in varchar2
132 ) return varchar2;
133
134 FUNCTION Is_Set_Ele_Type_Valid(
135 p_set_id in number := null,
136 p_set_type_id in number :=null,
137 p_ele_id in number :=null,
138 p_ele_type_id in number :=null
139 ) return varchar2;
140
141 FUNCTION Does_Set_Type_Exist(
142 p_set_type_id in number
143 ) return varchar2;
144
145 FUNCTION Does_Element_Type_Exist(
146 p_ele_type_id in number
147 ) return varchar2;
148
149
150
151 PROCEDURE Get_Who(
152 x_sysdate OUT NOCOPY date,
153 x_user_id OUT NOCOPY number,
154 x_login_id OUT NOCOPY number
155 );
156
157
158 FUNCTION Do_Elements_Exist_In_Set (
159 p_ele_id_tbl in cs_kb_number_tbl_type
160 ) return varchar2;
161
162 FUNCTION Get_External_Obj_Names(
163 p_obj_code_tbl in jtf_varchar2_table_100,
164 p_sel_id_tbl in jtf_varchar2_table_100,
165 p_sel_name_tbl OUT NOCOPY jtf_varchar2_table_1000
166 ) return number;
167
168 --FUNCTION Move_Element_Order(
169 -- p_set_id in number,
170 -- p_ele_id in number,
171 -- p_mode in varchar2
172 --) return number;
173
174 --FUNCTION Change_Element_Assoc(
175 -- p_set_id in number,
176 -- p_ele_id_tbl in cs_kb_number_tbl_type,
177 -- p_assoc_tbl in cs_kb_varchar100_tbl_type --N = neg, P=positive
178 --) return number;
179
180 FUNCTION Del_Element_From_Set(
181 p_ele_id in number,
182 p_set_id in number,
183 p_update_sets_b in varchar2 default 'T'
184 ) return number;
185
186 --FUNCTION Add_Element_To_Set_Ord(
187 -- p_set_id in number,
188 -- p_ele_id in number,
189 -- p_ele_order in number,
190 -- p_assoc_degree in number := CS_Knowledge_PUB.G_POSITIVE_ASSOC
191 --) return number;
192
193
194 FUNCTION Add_Element_To_Set(
195 p_ele_id in number,
196 p_set_id in number,
197 p_assoc_degree in number := CS_Knowledge_PUB.G_POSITIVE_ASSOC,
198 p_update_sets_b in varchar2 default 'T'
199 ) return number;
200
201 PROCEDURE Add_External_Links(
202 p_api_version in number,
203 p_init_msg_list in varchar2, -- := FND_API.G_FALSE,
204 p_commit in varchar2, -- := FND_API.G_FALSE,
205 p_validation_level in number, -- := FND_API.G_VALID_LEVEL_FULL,
206 x_return_status OUT NOCOPY varchar2,
207 x_msg_count OUT NOCOPY number,
208 x_msg_data OUT NOCOPY varchar2,
209 p_usage_code in varchar2,
210 p_id in number,
211 p_object_code in varchar2,
212 p_other_id_tbl in cs_kb_number_tbl_type,
213 p_other_code_tbl in cs_kb_varchar100_tbl_type
214 );
215
216 --
217 -- Update or delete link
218 --
219 --PROCEDURE Change_Set_Type_Links(
220 -- p_api_version in number,
221 -- p_init_msg_list in varchar2 := FND_API.G_FALSE,
222 -- p_commit in varchar2 := FND_API.G_FALSE,
223 -- p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
224 -- x_return_status OUT NOCOPY varchar2,
225 -- x_msg_count OUT NOCOPY number,
226 -- x_msg_data OUT NOCOPY varchar2,
227 -- p_link_id_tbl in cs_kb_number_tbl_type,
228 -- p_set_type_id_tbl in cs_kb_number_tbl_type
229 --);
230
231 PROCEDURE Change_Ele_Type_Links(
232 p_api_version in number,
233 p_init_msg_list in varchar2 := FND_API.G_FALSE,
234 p_commit in varchar2 := FND_API.G_FALSE,
235 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
236 x_return_status OUT NOCOPY varchar2,
237 x_msg_count OUT NOCOPY number,
238 x_msg_data OUT NOCOPY varchar2,
239 p_link_id_tbl in cs_kb_number_tbl_type,
240 p_ele_type_id_tbl in cs_kb_number_tbl_type
241 );
242 --
243 -- Update or Delete setlinks
244 -- (used when del set)
245 --
246 PROCEDURE Change_Set_Links(
247 p_api_version in number,
248 p_init_msg_list in varchar2, -- := FND_API.G_FALSE,
249 p_commit in varchar2, -- := FND_API.G_FALSE,
250 p_validation_level in number, -- := FND_API.G_VALID_LEVEL_FULL,
251 x_return_status OUT NOCOPY varchar2,
252 x_msg_count OUT NOCOPY number,
253 x_msg_data OUT NOCOPY varchar2,
254 p_link_id_tbl in cs_kb_number_tbl_type,
255 p_set_id_tbl in cs_kb_number_tbl_type
256 );
257
258 PROCEDURE Change_Element_Links(
259 p_api_version in number,
260 p_init_msg_list in varchar2, -- := FND_API.G_FALSE,
261 p_commit in varchar2, -- := FND_API.G_FALSE,
262 p_validation_level in number, -- := FND_API.G_VALID_LEVEL_FULL,
263 x_return_status OUT NOCOPY varchar2,
264 x_msg_count OUT NOCOPY number,
265 x_msg_data OUT NOCOPY varchar2,
266 p_link_id_tbl in cs_kb_number_tbl_type,
267 p_element_id_tbl in cs_kb_number_tbl_type
268 );
269
270 PROCEDURE Change_Element_To_Sets(
271 p_api_version in number,
272 p_init_msg_list in varchar2, -- := FND_API.G_FALSE,
273 p_commit in varchar2, -- := FND_API.G_FALSE,
274 p_validation_level in number, -- := FND_API.G_VALID_LEVEL_FULL,
275 x_return_status OUT NOCOPY varchar2,
276 x_msg_count OUT NOCOPY number,
277 x_msg_data OUT NOCOPY varchar2,
278 p_element_id in number,
279 p_set_id_tbl in cs_kb_number_tbl_type,
280 p_new_ele_id_tbl in cs_kb_number_tbl_type
281 );
282 --
283 -- Creation APIs using records
284 --
285
286 -- Original (Pre 8/03/00) Contributed element ids not passed back
287 PROCEDURE Create_Set_And_Elements(
288 p_api_version in number,
289 p_init_msg_list in varchar2 := FND_API.G_FALSE,
290 p_commit in varchar2 := FND_API.G_FALSE,
291 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
292 x_return_status OUT NOCOPY varchar2,
293 x_msg_count OUT NOCOPY number,
294 x_msg_data OUT NOCOPY varchar2,
295 p_set_def_rec in CS_Knowledge_PUB.set_def_rec_type,
296 p_ele_def_tbl in CS_Knowledge_PUB.ele_def_tbl_type,
297 -- p_attrval_def_tbl in CS_Knowledge_PUB.attrval_def_tbl_type,
298 x_set_id OUT NOCOPY number
299 );
300
301 -- New (Post 8/03/00) Contributed element ids passed back
302 PROCEDURE Create_Set_And_Elements(
303 p_api_version in number,
304 p_init_msg_list in varchar2 := FND_API.G_FALSE,
305 p_commit in varchar2 := FND_API.G_FALSE,
306 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
307 x_return_status OUT NOCOPY varchar2,
308 x_msg_count OUT NOCOPY number,
309 x_msg_data OUT NOCOPY varchar2,
310 p_set_def_rec in CS_Knowledge_PUB.set_def_rec_type,
311 p_ele_def_tbl in CS_Knowledge_PUB.ele_def_tbl_type,
312 -- p_attrval_def_tbl in CS_Knowledge_PUB.attrval_def_tbl_type,
313 x_set_id OUT NOCOPY number,
314 x_element_id_tbl OUT NOCOPY CS_Knowledge_PUB.number15_tbl_type
315 );
316
317 PROCEDURE Create_Set(
318 p_api_version in number,
319 p_init_msg_list in varchar2 := FND_API.G_FALSE,
320 p_commit in varchar2 := FND_API.G_FALSE,
321 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
322 x_return_status OUT NOCOPY varchar2,
323 x_msg_count OUT NOCOPY number,
324 x_msg_data OUT NOCOPY varchar2,
325 p_set_def_rec in CS_Knowledge_PUB.set_def_rec_type,
326 p_ele_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
327 -- p_attrval_def_tbl in CS_Knowledge_PUB.attrval_def_tbl_type,
328 x_set_id OUT NOCOPY number
329 );
330
331 PROCEDURE Create_Element(
332 p_api_version in number,
333 p_init_msg_list in varchar2 := FND_API.G_FALSE,
334 p_commit in varchar2 := FND_API.G_FALSE,
335 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
336 x_return_status OUT NOCOPY varchar2,
337 x_msg_count OUT NOCOPY number,
338 x_msg_data OUT NOCOPY varchar2,
339 p_ele_def_rec in CS_Knowledge_PUB.ele_def_rec_type,
340 x_element_id OUT NOCOPY number
341 );
342
343
344 --
345 -- Creation APIs using objects
346 --
347
348 -- Original (Pre 8/03/00) Contributed element ids not passed back
349 PROCEDURE Create_Set_And_Elements(
350 p_api_version in number,
351 p_init_msg_list in varchar2 := FND_API.G_FALSE,
352 p_commit in varchar2 := FND_API.G_FALSE,
353 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
354 x_return_status OUT NOCOPY varchar2,
355 x_msg_count OUT NOCOPY number,
356 x_msg_data OUT NOCOPY varchar2,
357 p_set_def_obj in cs_kb_set_def_obj_type,
358 p_ele_def_tbl in cs_kb_ele_def_tbl_type,
359 -- p_attrval_def_tbl in cs_kb_attrval_def_tbl_type :=null,
360 p_ele_assoc_tbl in cs_kb_number_tbl_type :=null,
361 x_set_id OUT NOCOPY number
362 );
363
364 -- New (Post 8/03/00) Contributed element ids passed back
365 PROCEDURE Create_Set_And_Elements(
366 p_api_version in number,
367 p_init_msg_list in varchar2 := FND_API.G_FALSE,
368 p_commit in varchar2 := FND_API.G_FALSE,
369 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
370 x_return_status OUT NOCOPY varchar2,
371 x_msg_count OUT NOCOPY number,
372 x_msg_data OUT NOCOPY varchar2,
373 p_set_def_obj in cs_kb_set_def_obj_type,
374 p_ele_def_tbl in cs_kb_ele_def_tbl_type,
375 -- p_attrval_def_tbl in cs_kb_attrval_def_tbl_type :=null,
376 p_ele_assoc_tbl in cs_kb_number_tbl_type :=null,
377 x_set_id OUT NOCOPY number,
378 x_element_id_tbl OUT NOCOPY cs_kb_number_tbl_type
379 );
380
381 PROCEDURE Create_Set(
382 p_api_version in number,
383 p_init_msg_list in varchar2 := FND_API.G_FALSE,
384 p_commit in varchar2 := FND_API.G_FALSE,
385 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
386 x_return_status OUT NOCOPY varchar2,
387 x_msg_count OUT NOCOPY number,
388 x_msg_data OUT NOCOPY varchar2,
389 p_set_def_obj in cs_kb_set_def_obj_type,
390 p_ele_id_tbl in cs_kb_number_tbl_type,
391 -- p_attrval_def_tbl in cs_kb_attrval_def_tbl_type :=null,
392 p_ele_assoc_tbl in cs_kb_number_tbl_type :=null,
393 x_set_id OUT NOCOPY number
394 );
395
396 PROCEDURE Create_Element(
397 p_api_version in number,
398 p_init_msg_list in varchar2 := FND_API.G_FALSE,
399 p_commit in varchar2 := FND_API.G_FALSE,
400 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
401 x_return_status OUT NOCOPY varchar2,
402 x_msg_count OUT NOCOPY number,
403 x_msg_data OUT NOCOPY varchar2,
404 p_ele_def_obj in cs_kb_ele_def_obj_type,
405 x_element_id OUT NOCOPY number
406 );
407
408 --
409 -- API for recording a set is useful
410 --
411
412 PROCEDURE Incr_Set_Useful(
413 p_api_version in number,
414 p_init_msg_list in varchar2, -- := FND_API.G_FALSE,
415 p_commit in varchar2, -- := FND_API.G_FALSE,
416 p_validation_level in number, -- := FND_API.G_VALID_LEVEL_FULL,
417 x_return_status OUT NOCOPY varchar2,
418 x_msg_count OUT NOCOPY number,
419 x_msg_data OUT NOCOPY varchar2,
420 p_set_id in number,
421 p_user_id in number,
425
422 p_used_type in varchar2, -- := CS_KNOWLEDGE_PVT.G_PF,
423 p_session_id in number DEFAULT NULL
424 );
426 --
427 -- Search APIs
428 --
429 --PROCEDURE Find_Eles_Matching (
430 -- p_api_version in number,
431 -- p_init_msg_list in varchar2, -- := FND_API.G_FALSE,
432 -- p_validation_level in number, -- := FND_API.G_VALID_LEVEL_FULL,
433 -- x_return_status OUT NOCOPY varchar2,
434 -- x_msg_count OUT NOCOPY number,
435 -- x_msg_data OUT NOCOPY varchar2,
436 -- p_query_str in varchar2,
437 -- p_type_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
438 -- p_other_criteria in varchar2 := NULL,
439 -- p_rows in number,
440 -- p_start_score in number := null,
441 -- p_start_id in number := null,
442 -- p_start_row in number, -- := 1,
443 -- x_ele_tbl in OUT NOCOPY CS_Knowledge_PUB.ele_res_tbl_type,
444 -- p_search_option in number := null
445 --);
446
447 --PROCEDURE Find_Eles_Related (
448 -- p_api_version in number,
449 -- p_init_msg_list in varchar2, -- := FND_API.G_FALSE,
450 -- p_validation_level in number, -- := FND_API.G_VALID_LEVEL_FULL,
451 -- x_return_status OUT NOCOPY varchar2,
452 -- x_msg_count OUT NOCOPY number,
453 -- x_msg_data OUT NOCOPY varchar2,
454 -- p_ele_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
455 -- p_type_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
456 -- p_other_criteria in varchar2 := NULL,
457 -- p_rows in number,
458 -- p_start_score in number := null,
459 -- p_start_id in number := null,
460 -- p_start_row in number, -- := 1,
461 -- x_ele_tbl in OUT NOCOPY CS_Knowledge_PUB.ele_res_tbl_type
462 --);
463
464 PROCEDURE Find_Sets_Matching (
465 p_api_version in number,
466 p_init_msg_list in varchar2, -- := FND_API.G_FALSE,
467 p_validation_level in number, -- := FND_API.G_VALID_LEVEL_FULL,
468 x_return_status OUT NOCOPY varchar2,
469 x_msg_count OUT NOCOPY number,
470 x_msg_data OUT NOCOPY varchar2,
471 p_pos_query_str in varchar2,
472 p_neg_query_str in varchar2 := null,
473 p_type_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
474 p_other_criteria in varchar2 := NULL,
475 p_rows in number,
476 p_start_score in number := null,
477 p_start_id in number := null,
478 p_start_row in number, -- := 1,
479 p_get_total_flag in varchar2, -- := FND_API.G_FALSE,
480 x_set_tbl in OUT NOCOPY CS_Knowledge_PUB.set_res_tbl_type,
481 x_total_rows OUT NOCOPY number,
482 p_search_option in number := null
483 );
484
485 PROCEDURE Find_Sets_Matching2 (
486 p_api_version in number,
487 p_init_msg_list in varchar2, -- := FND_API.G_FALSE,
488 p_validation_level in number, -- := FND_API.G_VALID_LEVEL_FULL,
489 x_return_status OUT NOCOPY varchar2,
490 x_msg_count OUT NOCOPY number,
491 x_msg_data OUT NOCOPY varchar2,
492 p_pos_query_str in varchar2,
493 -- p_neg_query_str in varchar2 := null,
494 p_type_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
495 p_other_criteria in varchar2,
496 p_other_value in Number,
497 p_rows in number,
498 p_start_score in number := null,
499 p_start_id in number := null,
500 p_start_row in number, -- := 1,
501 p_get_total_flag in varchar2, -- := FND_API.G_FALSE,
502 x_set_tbl in OUT NOCOPY CS_Knowledge_PUB.set_res_tbl_type,
503 x_total_rows OUT NOCOPY number,
504 p_search_option in number := null
505 );
506
507 --PROCEDURE Find_Sets_Related (
508 -- p_api_version in number,
509 -- p_init_msg_list in varchar2, -- := FND_API.G_FALSE,
510 -- p_validation_level in number, -- := FND_API.G_VALID_LEVEL_FULL,
511 -- x_return_status OUT NOCOPY varchar2,
512 -- x_msg_count OUT NOCOPY number,
513 -- x_msg_data OUT NOCOPY varchar2,
514 -- p_pos_ele_tbl in CS_Knowledge_PUB.number15_tbl_type,
515 -- p_neg_ele_tbl in CS_Knowledge_PUB.number15_tbl_type,
516 -- p_type_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
517 -- p_other_criteria in varchar2 := NULL,
518 -- p_rows in number,
519 -- p_start_score in number := null,
520 -- p_start_id in number := null,
521 -- p_start_row in number, -- := 1,
522 -- x_set_tbl in OUT NOCOPY CS_Knowledge_PUB.set_res_tbl_type
523 --);
524
525
526 PROCEDURE Process_Frequency_Keyword (
527 p_query_str in out nocopy varchar2,
528 p_search_option in Number);
529
530
531 FUNCTION Build_Smart_Score_Query
532 (
533 p_current_query in varchar2,
534 p_product_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
535 p_platform_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
536 p_category_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
537 p_statement_id_tbl in CS_Knowledge_PUB.number15_tbl_type
538 )
539 return varchar2;
540
541 FUNCTION Build_Related_Stmt_Text_Query
542 ( p_statement_id_tbl in CS_Knowledge_PUB.number15_tbl_type )
543 Return VARCHAR2;
547 p_string in varchar2,
544
545 FUNCTION Build_SR_Text_Query
546 (
548 p_search_option in number
549 )
550 return varchar2;
551
552 -- Add item_id
553 FUNCTION Build_SR_Text_Query
554 (
555 p_string in VARCHAR2,
556 p_item_id in NUMBER,
557 p_search_option in NUMBER
558 )
559 return varchar2;
560
561 end CS_Knowledge_Pvt;