[Home] [Help]
PACKAGE BODY: APPS.CS_KNOWLEDGE_GRP
Source
1 PACKAGE BODY CS_Knowledge_Grp AS
2 /* $Header: csgkbb.pls 120.3 2006/01/20 16:53:53 mkettle ship $ */
3
4
5
6 --
7 -- PUBLIC
8 --
9 PROCEDURE Construct_Text_Query(
10 p_api_version in number,
11 p_init_msg_list in varchar2 := FND_API.G_FALSE,
12 p_commit in varchar2 := FND_API.G_FALSE,
13 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
14 x_return_status OUT NOCOPY varchar2,
15 x_msg_count OUT NOCOPY number,
16 x_msg_data OUT NOCOPY varchar2,
17 p_qry_string in varchar2,
18 p_search_option in number,
19 x_qry_string OUT NOCOPY varchar2
20 ) is
21 l_api_name CONSTANT varchar2(30) := 'Construct_Text_Query';
22 l_api_version CONSTANT number := 1.0;
23 l_ret number(5);
24 l_qry_string varchar2(30000) := p_qry_string;
25
26 begin
27 savepoint Const_Text_Qry_PUB;
28 if not FND_API.Compatible_API_Call(
29 l_api_version,
30 p_api_version,
31 l_api_name,
32 G_PKG_NAME) then
33 raise FND_API.G_EXC_UNEXPECTED_ERROR;
34 end if;
35
36 if FND_API.to_Boolean(p_init_msg_list) then
37 FND_MSG_PUB.initialize;
38 end if;
39 -- -- -- -- begin build_text_query -- -- -- -- --
40 x_qry_string := CS_KNOWLEDGE_PVT.Build_Simple_Text_Query(
41 p_qry_string => l_qry_string,
42 p_search_option => p_search_option);
43
44 EXCEPTION
45 WHEN FND_API.G_EXC_ERROR THEN
46 ROLLBACK TO Const_Text_Qry_PUB;
47 x_return_status := FND_API.G_RET_STS_ERROR ;
48 FND_MSG_PUB.Count_And_Get(
49 p_count => x_msg_count,
50 p_data => x_msg_data );
51 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
52 ROLLBACK TO Const_Text_Qry_PUB;
53 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
54 FND_MSG_PUB.Count_And_Get(
55 p_count => x_msg_count,
56 p_data => x_msg_data);
57 WHEN OTHERS THEN
58 ROLLBACK TO Const_Text_Qry_PUB;
59 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
60 IF FND_MSG_PUB.Check_Msg_Level
61 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
62 FND_MSG_PUB.Add_Exc_Msg(
63 G_PKG_NAME,
64 l_api_name);
65 END IF;
66 FND_MSG_PUB.Count_And_Get(
67 p_count => x_msg_count,
68 p_data => x_msg_data);
69 end Construct_Text_Query;
70
71
72 PROCEDURE Delete_Set_Link(
73 p_api_version in number,
74 p_init_msg_list in varchar2 := FND_API.G_FALSE,
75 p_commit in varchar2 := FND_API.G_FALSE,
76 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
77 x_return_status OUT NOCOPY varchar2,
78 x_msg_count OUT NOCOPY number,
79 x_msg_data OUT NOCOPY varchar2,
80 p_set_id in number,
81 p_object_code in varchar2,
82 p_other_id in number
83 ) is
84 l_api_name CONSTANT varchar2(30) := 'Delete_Set_Link';
85 l_api_version CONSTANT number := 1.0;
86 l_ret number(5);
87 begin
88 savepoint Del_Set_Link_PUB;
89 if not FND_API.Compatible_API_Call(
90 l_api_version,
91 p_api_version,
92 l_api_name,
93 G_PKG_NAME) then
94 raise FND_API.G_EXC_UNEXPECTED_ERROR;
95 end if;
96
97 if FND_API.to_Boolean(p_init_msg_list) then
98 FND_MSG_PUB.initialize;
99 end if;
100 -- -- -- -- begin delete set_link -- -- -- -- --
101 x_return_status := FND_API.G_RET_STS_SUCCESS;
102
103 l_ret := CS_KB_SET_LINKS_PKG.Delete_Set_Link_W_Obj_Code(
104 p_set_id, p_object_code, p_other_id);
105 -- -- -- -- end delete set_link -- -- --
106
107 FND_MSG_PUB.Count_And_Get(
108 p_count => x_msg_count,
109 p_data => x_msg_data );
110
111 IF FND_API.To_Boolean( p_commit ) THEN
112 COMMIT WORK;
113 END IF;
114
115
116 EXCEPTION
117 WHEN FND_API.G_EXC_ERROR THEN
118 ROLLBACK TO Del_Set_Link_PUB;
119 x_return_status := FND_API.G_RET_STS_ERROR;
120 FND_MSG_PUB.Count_And_Get(
121 p_count => x_msg_count,
122 p_data => x_msg_data );
123 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
124 ROLLBACK TO Del_Set_Link_PUB;
125 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
126 FND_MSG_PUB.Count_And_Get(
127 p_count => x_msg_count,
128 p_data => x_msg_data);
129 WHEN OTHERS THEN
130 ROLLBACK TO Del_Set_Link_PUB;
131 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
132 IF FND_MSG_PUB.Check_Msg_Level
133 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
134 FND_MSG_PUB.Add_Exc_Msg(
135 G_PKG_NAME,
136 l_api_name);
137 END IF;
138 FND_MSG_PUB.Count_And_Get(
139 p_count => x_msg_count,
140 p_data => x_msg_data);
141 end Delete_Set_Link;
142
143 PROCEDURE Delete_Element_Link(
144 p_api_version in number,
145 p_init_msg_list in varchar2 := FND_API.G_FALSE,
146 p_commit in varchar2 := FND_API.G_FALSE,
147 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
148 x_return_status OUT NOCOPY varchar2,
149 x_msg_count OUT NOCOPY number,
150 x_msg_data OUT NOCOPY varchar2,
151 p_element_id in number,
152 p_object_code in varchar2,
153 p_other_id in number
154 ) is
155 l_api_name CONSTANT varchar2(30) := 'Delete_Element_Link';
156 l_api_version CONSTANT number := 1.0;
157 l_ret number(5);
158 begin
159 savepoint Del_Element_Link_PUB;
160
161 if not FND_API.Compatible_API_Call(
162 l_api_version,
163 p_api_version,
164 l_api_name,
165 G_PKG_NAME) then
166 raise FND_API.G_EXC_UNEXPECTED_ERROR;
167 end if;
168
169 if FND_API.to_Boolean(p_init_msg_list) then
170 FND_MSG_PUB.initialize;
171 end if;
172
173 -- -- -- -- begin delete Element_link -- -- -- -- --
174 x_return_status := FND_API.G_RET_STS_SUCCESS;
175
176 l_ret := CS_KB_ELEMENT_LINKS_PKG.Delete_Element_Link_W_Obj_Code(
177 p_element_id, p_object_code, p_other_id);
178
179 -- -- -- -- end delete Element_link -- -- --
180 -- Standard call to get message count. if count=1, get message info.
181 FND_MSG_PUB.Count_And_Get(
182 p_count => x_msg_count,
183 p_data => x_msg_data );
184
185 IF FND_API.To_Boolean( p_commit ) THEN
186 COMMIT WORK;
187 END IF;
188
189 EXCEPTION
190 WHEN FND_API.G_EXC_ERROR THEN
191 ROLLBACK TO Del_Element_Link_PUB;
192 x_return_status := FND_API.G_RET_STS_ERROR ;
193 FND_MSG_PUB.Count_And_Get(
194 p_count => x_msg_count,
195 p_data => x_msg_data );
196 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
197 ROLLBACK TO Del_Element_Link_PUB;
198 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
199 FND_MSG_PUB.Count_And_Get(
200 p_count => x_msg_count,
201 p_data => x_msg_data);
202 WHEN OTHERS THEN
203 ROLLBACK TO Del_Element_Link_PUB;
204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
205 IF FND_MSG_PUB.Check_Msg_Level
206 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
207 FND_MSG_PUB.Add_Exc_Msg(
208 G_PKG_NAME,
209 l_api_name);
210 END IF;
211 FND_MSG_PUB.Count_And_Get(
212 p_count => x_msg_count,
213 p_data => x_msg_data);
214 end Delete_Element_Link;
215
216 --
217 -- Create_Set
218 -- returns set id or ERROR_STATUS
219 --
220
221 PROCEDURE Create_Set_And_Elements(
222 p_api_version in number,
223 p_init_msg_list in varchar2 := FND_API.G_FALSE,
224 p_commit in varchar2 := FND_API.G_FALSE,
225 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
226 x_return_status OUT NOCOPY varchar2,
227 x_msg_count OUT NOCOPY number,
228 x_msg_data OUT NOCOPY varchar2,
229 p_set_def_rec in CS_Knowledge_PUB.set_def_rec_type,
230 p_ele_def_tbl in CS_Knowledge_PUB.ele_def_tbl_type,
231 -- p_attrval_def_tbl in CS_Knowledge_PUB.attrval_def_tbl_type :=null,
232 x_set_id OUT NOCOPY number
233 )is
234 l_api_name CONSTANT varchar2(30) := 'Create_Set_And_Elements';
235 l_api_version CONSTANT number := 1.0;
236
237 begin
238
239 savepoint Create_Set_And_Elements_PUB;
240
241 if not FND_API.Compatible_API_Call(
242 l_api_version,
243 p_api_version,
244 l_api_name,
245 G_PKG_NAME) then
246 raise FND_API.G_EXC_UNEXPECTED_ERROR;
247 end if;
248
249 -- -- -- -- begin my code -- -- -- -- --
250 CS_Knowledge_PVT.Create_Set_And_Elements(
251 p_api_version => p_api_version,
252 p_init_msg_list => p_init_msg_list,
253 p_commit => p_commit,
254 p_validation_level => p_validation_level,
255 x_return_status => x_return_status,
256 x_msg_count => x_msg_count,
257 x_msg_data => x_msg_data,
258 p_set_def_rec => p_set_def_rec,
259 p_ele_def_tbl => p_ele_def_tbl,
260 -- p_attrval_def_tbl => p_attrval_def_tbl,
261 x_set_id => x_set_id);
262
263 EXCEPTION
264 WHEN FND_API.G_EXC_ERROR THEN
265 ROLLBACK TO Create_Set_And_Elements_PUB;
266 x_return_status := FND_API.G_RET_STS_ERROR ;
267 FND_MSG_PUB.Count_And_Get(
268 p_count => x_msg_count,
269 p_data => x_msg_data );
270 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
271 ROLLBACK TO Create_Set_And_Elements_PUB;
272 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
273 FND_MSG_PUB.Count_And_Get(
274 p_count => x_msg_count,
275 p_data => x_msg_data);
276 WHEN OTHERS THEN
277 ROLLBACK TO Create_Set_And_Elements_PUB;
278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
279 IF FND_MSG_PUB.Check_Msg_Level
280 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
281 FND_MSG_PUB.Add_Exc_Msg(
282 G_PKG_NAME,
283 l_api_name);
284 END IF;
285 FND_MSG_PUB.Count_And_Get(
286 p_count => x_msg_count,
287 p_data => x_msg_data);
288 end Create_Set_And_Elements;
289
290
291 PROCEDURE Create_Set_And_Elements(
292 p_api_version in number,
293 p_init_msg_list in varchar2 := FND_API.G_FALSE,
294 p_commit in varchar2 := FND_API.G_FALSE,
295 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
296 x_return_status OUT NOCOPY varchar2,
297 x_msg_count OUT NOCOPY number,
298 x_msg_data OUT NOCOPY varchar2,
299 p_set_def_rec in CS_Knowledge_PUB.set_def_rec_type,
300 p_ele_def_tbl in CS_Knowledge_PUB.ele_def_tbl_type,
301 x_set_id OUT NOCOPY number,
302 x_element_id_tbl OUT NOCOPY CS_Knowledge_PUB.number15_tbl_type
303 )
304 IS
305 l_api_name CONSTANT varchar2(30) := 'Create_Set_And_Elements';
306 l_api_version CONSTANT number := 1.0;
307 begin
308
309 savepoint Create_Set_And_Elements_PUB;
310
311 if not FND_API.Compatible_API_Call(
312 l_api_version,
313 p_api_version,
314 l_api_name,
315 G_PKG_NAME) then
316 raise FND_API.G_EXC_UNEXPECTED_ERROR;
317 end if;
318
319 -- -- -- -- begin my code -- -- -- -- --
320 CS_Knowledge_PVT.Create_Set_And_Elements(
321 p_api_version => p_api_version,
322 p_init_msg_list => p_init_msg_list,
323 p_commit => p_commit,
324 p_validation_level => p_validation_level,
325 x_return_status => x_return_status,
326 x_msg_count => x_msg_count,
327 x_msg_data => x_msg_data,
328 p_set_def_rec => p_set_def_rec,
329 p_ele_def_tbl => p_ele_def_tbl,
330 x_set_id => x_set_id,
331 x_element_id_tbl => x_element_id_tbl);
332
333 EXCEPTION
334 WHEN FND_API.G_EXC_ERROR THEN
335 ROLLBACK TO Create_Set_And_Elements_PUB;
336 x_return_status := FND_API.G_RET_STS_ERROR ;
337 FND_MSG_PUB.Count_And_Get(
338 p_count => x_msg_count,
339 p_data => x_msg_data );
340 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
341 ROLLBACK TO Create_Set_And_Elements_PUB;
342 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
343 FND_MSG_PUB.Count_And_Get(
344 p_count => x_msg_count,
345 p_data => x_msg_data);
346 WHEN OTHERS THEN
347 ROLLBACK TO Create_Set_And_Elements_PUB;
348 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
349 IF FND_MSG_PUB.Check_Msg_Level
350 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
351 FND_MSG_PUB.Add_Exc_Msg(
352 G_PKG_NAME,
353 l_api_name);
354 END IF;
355 FND_MSG_PUB.Count_And_Get(
356 p_count => x_msg_count,
357 p_data => x_msg_data);
358
359 end Create_Set_And_Elements;
360
361
362 --
363 -- Create a set for the given element_ids.
364 --
365 PROCEDURE Create_Set(
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_rec in CS_Knowledge_PUB.set_def_rec_type,
374 p_ele_id_tbl in CS_Knowledge_PUB.number15_tbl_type,
375 -- p_attrval_def_tbl in CS_Knowledge_PUB.attrval_def_tbl_type := null,
376 x_set_id OUT NOCOPY number
377 ) is
378 l_api_name CONSTANT varchar2(30) := 'Create_Set';
379 l_api_version CONSTANT number := 1.0;
380
381 begin
382
383 savepoint Create_Set_PUB;
384
385 if not FND_API.Compatible_API_Call(
386 l_api_version,
387 p_api_version,
388 l_api_name,
389 G_PKG_NAME) then
390 raise FND_API.G_EXC_UNEXPECTED_ERROR;
391 end if;
392
393 -- -- -- -- begin my code -- -- -- -- --
394 CS_Knowledge_PVT.Create_Set(
395 p_api_version => p_api_version,
396 p_init_msg_list => p_init_msg_list,
397 p_commit => p_commit,
398 p_validation_level => p_validation_level,
399 x_return_status => x_return_status,
400 x_msg_count => x_msg_count,
401 x_msg_data => x_msg_data,
402 p_set_def_rec => p_set_def_rec,
403 p_ele_id_tbl => p_ele_id_tbl,
404 -- p_attrval_def_tbl => p_attrval_def_tbl,
405 x_set_id => x_set_id);
406
407
408 EXCEPTION
409 WHEN FND_API.G_EXC_ERROR THEN
410 ROLLBACK TO Create_Set_PUB;
411 x_return_status := FND_API.G_RET_STS_ERROR ;
412 FND_MSG_PUB.Count_And_Get(
413 p_count => x_msg_count,
414 p_data => x_msg_data );
415 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
416 ROLLBACK TO Create_Set_PUB;
417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
418 FND_MSG_PUB.Count_And_Get(
419 p_count => x_msg_count,
420 p_data => x_msg_data);
421 WHEN OTHERS THEN
422 ROLLBACK TO Create_Set_PUB;
423 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
424 IF FND_MSG_PUB.Check_Msg_Level
425 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
426 FND_MSG_PUB.Add_Exc_Msg(
427 G_PKG_NAME,
428 l_api_name);
429 END IF;
430 FND_MSG_PUB.Count_And_Get(
431 p_count => x_msg_count,
432 p_data => x_msg_data);
433
434 end Create_Set;
435
436 --
437 -- Create ELement given ele_type_id and desc
438 -- Other params are not used for now.
439 --
440 PROCEDURE Create_Element(
441 p_api_version in number,
442 p_init_msg_list in varchar2 := FND_API.G_FALSE,
446 x_msg_count OUT NOCOPY number,
443 p_commit in varchar2 := FND_API.G_FALSE,
444 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
445 x_return_status OUT NOCOPY varchar2,
447 x_msg_data OUT NOCOPY varchar2,
448 p_ele_def_rec in CS_Knowledge_PUB.ele_def_rec_type,
449
450 x_element_id OUT NOCOPY number
451 ) is
452 l_api_name CONSTANT varchar2(30) := 'Create_Element';
453 l_api_version CONSTANT number := 1.0;
454 begin
455
456 savepoint Create_Element_PUB;
457
458 if not FND_API.Compatible_API_Call(
459 l_api_version,
460 p_api_version,
461 l_api_name,
462 G_PKG_NAME) then
463 raise FND_API.G_EXC_UNEXPECTED_ERROR;
464 end if;
465
466 -- -- -- -- begin my code -- -- -- -- --
467 CS_Knowledge_PVT.Create_Element(
468 p_api_version => p_api_version,
469 p_init_msg_list => p_init_msg_list,
470 p_commit => p_commit,
471 p_validation_level => p_validation_level,
472 x_return_status => x_return_status,
473 x_msg_count => x_msg_count,
474 x_msg_data => x_msg_data,
475 p_ele_def_rec => p_ele_def_rec,
476 x_element_id => x_element_id);
477
478 EXCEPTION
479 WHEN FND_API.G_EXC_ERROR THEN
480 ROLLBACK TO Create_Element_PUB;
481 x_return_status := FND_API.G_RET_STS_ERROR ;
482 FND_MSG_PUB.Count_And_Get(
483 p_count => x_msg_count,
484 p_data => x_msg_data );
485 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
486 ROLLBACK TO Create_Element_PUB;
487 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
488 FND_MSG_PUB.Count_And_Get(
489 p_count => x_msg_count,
490 p_data => x_msg_data);
491 WHEN OTHERS THEN
492 ROLLBACK TO Create_Element_PUB;
493 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
494 IF FND_MSG_PUB.Check_Msg_Level
495 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
496 FND_MSG_PUB.Add_Exc_Msg(
497 G_PKG_NAME,
498 l_api_name);
499 END IF;
500 FND_MSG_PUB.Count_And_Get(
501 p_count => x_msg_count,
502 p_data => x_msg_data);
503 end Create_Element;
504
505 PROCEDURE Find_Sets_Matching (
506 p_api_version in number,
507 p_init_msg_list in varchar2 := FND_API.G_FALSE,
508 --p_commit in varchar2 := FND_API.G_FALSE,
509 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
510 x_return_status OUT NOCOPY varchar2,
511 x_msg_count OUT NOCOPY number,
512 x_msg_data OUT NOCOPY varchar2,
513 --p_sets_c in OUT NOCOPY general_csr_type,
514 p_pos_query_str in varchar2,
515 p_neg_query_str in varchar2 := null,
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 x_set_tbl in OUT NOCOPY CS_Knowledge_PUB.set_res_tbl_type
522 )is
523 l_api_name CONSTANT varchar2(30) := 'Find_Sets_Matching';
524 l_api_version CONSTANT number := 1.0;
525 l_total_rows number :=0;
526 begin
527 savepoint Find_Sets_Matching_PUB;
528
529 if not FND_API.Compatible_API_Call(
530 l_api_version,
531 p_api_version,
532 l_api_name,
533 G_PKG_NAME) then
534 raise FND_API.G_EXC_UNEXPECTED_ERROR;
535 end if;
536
537 -- -- -- -- begin my code -- -- -- -- --
538 CS_Knowledge_PVT.Find_Sets_Matching(
539 p_api_version => p_api_version,
540 p_init_msg_list => p_init_msg_list,
541 p_validation_level => p_validation_level,
542 x_return_status => x_return_status,
543 x_msg_count => x_msg_count,
544 x_msg_data => x_msg_data,
545 p_pos_query_str =>p_pos_query_str,
546 p_neg_query_str =>p_neg_query_str,
547 p_type_id_tbl => p_type_id_tbl,
548 p_other_criteria => p_other_criteria,
549 p_rows => p_rows,
550 p_start_score =>p_start_score,
551 p_start_id => p_start_id,
552 p_start_row => 1,
553 p_get_total_flag => FND_API.G_FALSE,
554 x_set_tbl => x_set_tbl,
555 x_total_rows => l_total_rows);
556
557 EXCEPTION
558 WHEN FND_API.G_EXC_ERROR THEN
559 ROLLBACK TO Find_Sets_Matching_PUB;
560 x_return_status := FND_API.G_RET_STS_ERROR ;
561 FND_MSG_PUB.Count_And_Get(
562 p_count => x_msg_count,
563 p_data => x_msg_data );
564 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
565 ROLLBACK TO Find_Sets_Matching_PUB;
566 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
567 FND_MSG_PUB.Count_And_Get(
568 p_count => x_msg_count,
569 p_data => x_msg_data);
570 WHEN OTHERS THEN
571 ROLLBACK TO Find_Sets_Matching_PUB;
572 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
573 IF FND_MSG_PUB.Check_Msg_Level
574 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
575 FND_MSG_PUB.Add_Exc_Msg(
576 G_PKG_NAME,
577 l_api_name);
578 END IF;
579 FND_MSG_PUB.Count_And_Get(
580 p_count => x_msg_count,
581 p_data => x_msg_data);
582 end Find_Sets_Matching;
583
584 PROCEDURE Create_Element_Link(
588 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
585 p_api_version in number,
586 p_init_msg_list in varchar2 := FND_API.G_FALSE,
587 p_commit in varchar2 := FND_API.G_FALSE,
589 x_return_status OUT NOCOPY varchar2,
590 x_msg_count OUT NOCOPY number,
591 x_msg_data OUT NOCOPY varchar2,
592 p_element_link_rec in CS_KB_ELEMENT_LINKS%ROWTYPE,
593 x_element_link_id OUT NOCOPY number
594 ) is
595 l_api_name CONSTANT varchar2(30) := 'Create_Element_Link';
596 l_api_version CONSTANT number := 1.0;
597 l_date date;
598 l_created_by number;
599 l_login number;
600 l_id number(15);
601 begin
602 savepoint Create_Element_Link_Grp;
603
604 if not FND_API.Compatible_API_Call(
605 l_api_version,
606 p_api_version,
607 l_api_name,
608 G_PKG_NAME) then
609 raise FND_API.G_EXC_UNEXPECTED_ERROR;
610 end if;
611
612 if FND_API.to_Boolean(p_init_msg_list) then
613 FND_MSG_PUB.initialize;
614 end if;
615
616 x_return_status := FND_API.G_RET_STS_SUCCESS;
617
618 -- -- -- -- begin my code -- -- -- -- --
619
620 l_id := CS_KB_ELEMENT_LINKS_PKG.Create_Element_Link(
621 p_element_link_rec.link_type,
622 p_element_link_rec.object_code,
623 p_element_link_rec.element_id,
624 p_element_link_rec.other_id,
625 p_element_link_rec.attribute_category,
626 p_element_link_rec.attribute1,
627 p_element_link_rec.attribute2,
628 p_element_link_rec.attribute3,
629 p_element_link_rec.attribute4,
630 p_element_link_rec.attribute5,
631 p_element_link_rec.attribute6,
632 p_element_link_rec.attribute7,
633 p_element_link_rec.attribute8,
634 p_element_link_rec.attribute9,
635 p_element_link_rec.attribute10,
636 p_element_link_rec.attribute11,
637 p_element_link_rec.attribute12,
638 p_element_link_rec.attribute13,
639 p_element_link_rec.attribute14,
640 p_element_link_rec.attribute15
641 );
642 if(l_id>0) then
643 x_element_link_id := l_id;
644 else
645 raise FND_API.G_EXC_UNEXPECTED_ERROR;
646 end if;
647
648 -- -- -- -- end of code -- -- --
649
650 IF FND_API.To_Boolean( p_commit ) THEN
651 COMMIT WORK;
652 END IF;
653
654 -- Standard call to get message count. if count=1, get message info.
655 FND_MSG_PUB.Count_And_Get(
656 p_count => x_msg_count,
657 p_data => x_msg_data );
658
659 EXCEPTION
660 WHEN FND_API.G_EXC_ERROR THEN
661 ROLLBACK TO Create_Element_Link_Grp;
662 x_return_status := FND_API.G_RET_STS_ERROR ;
663 FND_MSG_PUB.Count_And_Get(
664 p_count => x_msg_count,
665 p_data => x_msg_data );
666 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
667 ROLLBACK TO Create_Element_Link_Grp;
668 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
669 FND_MSG_PUB.Count_And_Get(
670 p_count => x_msg_count,
671 p_data => x_msg_data);
672 WHEN OTHERS THEN
673 ROLLBACK TO Create_Element_Link_Grp;
674 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
675 IF FND_MSG_PUB.Check_Msg_Level
676 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
677 FND_MSG_PUB.Add_Exc_Msg(
678 G_PKG_NAME,
679 l_api_name);
680 END IF;
681 FND_MSG_PUB.Count_And_Get(
682 p_count => x_msg_count,
683 p_data => x_msg_data);
684 end Create_Element_Link;
685
686 PROCEDURE Create_Set_Link(
687 P_API_VERSION IN NUMBER,
688 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
689 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
690 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
691 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
692 X_MSG_COUNT OUT NOCOPY NUMBER,
693 X_MSG_DATA OUT NOCOPY VARCHAR2,
694 P_SET_LINK_REC IN CS_KB_SET_LINKS%ROWTYPE,
695 X_SET_LINK_ID OUT NOCOPY NUMBER
696 ) IS
697 l_api_name CONSTANT varchar2(30) := 'Create_Set_Link';
698 l_api_version CONSTANT number := 1.0;
699 l_date date;
700 l_created_by number;
701 l_login number;
702 l_id number(15);
703
704 l_return_status VARCHAR2(1);
705 l_msg_data VARCHAR2(2000);
706 l_msg_count NUMBER;
707
708 BEGIN
709 SAVEPOINT Create_Set_Link_Grp;
710
711 IF NOT FND_API.Compatible_API_Call(
712 l_api_version,
713 p_api_version,
714 l_api_name,
715 G_PKG_NAME) THEN
716 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
717 END IF;
718
719 IF FND_API.to_Boolean(p_init_msg_list) THEN
720 FND_MSG_PUB.initialize;
721 END IF;
722
723 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
724
725 CS_KB_SET_LINKS_PKG.Create_Set_Link(
726 P_LINK_TYPE => p_set_link_rec.link_type,
727 P_OBJECT_CODE => p_set_link_rec.object_code,
728 P_SET_ID => p_set_link_rec.set_id,
729 P_OTHER_ID => p_set_link_rec.other_id,
730 P_ATTRIBUTE_CATEGORY => p_set_link_rec.attribute_category,
731 P_ATTRIBUTE1 => p_set_link_rec.attribute1,
732 P_ATTRIBUTE2 => p_set_link_rec.attribute2,
733 P_ATTRIBUTE3 => p_set_link_rec.attribute3,
737 P_ATTRIBUTE7 => p_set_link_rec.attribute7,
734 P_ATTRIBUTE4 => p_set_link_rec.attribute4,
735 P_ATTRIBUTE5 => p_set_link_rec.attribute5,
736 P_ATTRIBUTE6 => p_set_link_rec.attribute6,
738 P_ATTRIBUTE8 => p_set_link_rec.attribute8,
739 P_ATTRIBUTE9 => p_set_link_rec.attribute9,
740 P_ATTRIBUTE10 => p_set_link_rec.attribute10,
741 P_ATTRIBUTE11 => p_set_link_rec.attribute11,
742 P_ATTRIBUTE12 => p_set_link_rec.attribute12,
743 P_ATTRIBUTE13 => p_set_link_rec.attribute13,
744 P_ATTRIBUTE14 => p_set_link_rec.attribute14,
745 P_ATTRIBUTE15 => p_set_link_rec.attribute15,
746 X_LINK_ID => l_id,
747 X_RETURN_STATUS => l_return_status,
748 X_MSG_DATA => l_msg_data,
749 X_MSG_COUNT => l_msg_count );
750
751 X_RETURN_STATUS := l_return_status;
752 X_MSG_DATA := l_msg_data;
753 X_MSG_COUNT := l_msg_count;
754
755 IF FND_API.To_Boolean( p_commit ) AND
756 l_return_status = FND_API.G_RET_STS_SUCCESS THEN
757 COMMIT WORK;
758 END IF;
759
760 EXCEPTION
761 WHEN FND_API.G_EXC_ERROR THEN
762
763 ROLLBACK TO Create_Set_Link_Grp;
764
765 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR ;
766
767 FND_MSG_PUB.Count_And_Get(
768 p_encoded => FND_API.G_FALSE,
769 p_count => x_msg_count,
770 p_data => x_msg_data );
771
772 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
773
774 ROLLBACK TO Create_Set_Link_Grp;
775
776 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR ;
777
778 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
779 p_count => X_MSG_COUNT,
780 p_data => X_MSG_DATA);
781 WHEN OTHERS THEN
782
783 ROLLBACK TO Create_Set_Link_Grp;
784
785 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR ;
786
787 IF FND_MSG_PUB.Check_Msg_Level
788 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
789 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
790 END IF;
791
792 FND_MSG_PUB.Count_And_Get(
793 p_encoded => FND_API.G_FALSE ,
794 p_count => x_msg_count,
795 p_data => x_msg_data);
796
797 END Create_Set_Link;
798
799 PROCEDURE Update_Set_Link(
800 p_api_version in number,
801 p_init_msg_list in varchar2 := FND_API.G_FALSE,
802 p_commit in varchar2 := FND_API.G_FALSE,
803 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
804 x_return_status OUT NOCOPY varchar2,
805 x_msg_count OUT NOCOPY number,
806 x_msg_data OUT NOCOPY varchar2,
807 p_set_link_rec in CS_KB_SET_LINKS%ROWTYPE
808 ) is
809 l_api_name CONSTANT varchar2(30) := 'Update_Set_Link';
810 l_api_version CONSTANT number := 1.0;
811 l_date date;
812 l_created_by number;
813 l_login number;
814 l_sta number(15);
815 begin
816 savepoint Update_Set_Link_Grp;
817
818 if not FND_API.Compatible_API_Call(
819 l_api_version,
820 p_api_version,
821 l_api_name,
822 G_PKG_NAME) then
823 raise FND_API.G_EXC_UNEXPECTED_ERROR;
824 end if;
825
826 if FND_API.to_Boolean(p_init_msg_list) then
827 FND_MSG_PUB.initialize;
828 end if;
829
830 x_return_status := FND_API.G_RET_STS_SUCCESS;
831
832 -- -- -- -- begin my code -- -- -- -- --
833
834 l_sta := CS_KB_SET_LINKS_PKG.Update_Set_Link(
835 p_set_link_rec.link_id,
836 p_set_link_rec.link_type,
837 p_set_link_rec.object_code,
838 p_set_link_rec.set_id,
839 p_set_link_rec.other_id,
840 p_set_link_rec.attribute_category,
841 p_set_link_rec.attribute1,
842 p_set_link_rec.attribute2,
843 p_set_link_rec.attribute3,
844 p_set_link_rec.attribute4,
845 p_set_link_rec.attribute5,
846 p_set_link_rec.attribute6,
847 p_set_link_rec.attribute7,
848 p_set_link_rec.attribute8,
849 p_set_link_rec.attribute9,
850 p_set_link_rec.attribute10,
851 p_set_link_rec.attribute11,
852 p_set_link_rec.attribute12,
853 p_set_link_rec.attribute13,
854 p_set_link_rec.attribute14,
855 p_set_link_rec.attribute15
856 );
857 if(l_sta<>CS_KB_SET_LINKS_PKG.OKAY_STATUS) then
858 raise FND_API.G_EXC_UNEXPECTED_ERROR;
859 end if;
860
861 -- -- -- -- end of code -- -- --
862
863 IF FND_API.To_Boolean( p_commit ) THEN
864 COMMIT WORK;
865 END IF;
866
867 -- Standard call to get message count. if count=1, get message info.
868 FND_MSG_PUB.Count_And_Get(
869 p_count => x_msg_count,
870 p_data => x_msg_data );
871
872 EXCEPTION
873 WHEN FND_API.G_EXC_ERROR THEN
874 ROLLBACK TO Update_Set_Link_Grp;
875 x_return_status := FND_API.G_RET_STS_ERROR ;
876 FND_MSG_PUB.Count_And_Get(
877 p_count => x_msg_count,
878 p_data => x_msg_data );
879 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
883 p_count => x_msg_count,
880 ROLLBACK TO Update_Set_Link_Grp;
881 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
882 FND_MSG_PUB.Count_And_Get(
884 p_data => x_msg_data);
885 WHEN OTHERS THEN
886 ROLLBACK TO Update_Set_Link_Grp;
887 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
888 IF FND_MSG_PUB.Check_Msg_Level
889 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
890 FND_MSG_PUB.Add_Exc_Msg(
891 G_PKG_NAME,
892 l_api_name);
893 END IF;
894 FND_MSG_PUB.Count_And_Get(
895 p_count => x_msg_count,
896 p_data => x_msg_data);
897 end Update_Set_Link;
898
899 PROCEDURE Specific_Search_Mes(
900 p_api_version IN NUMBER,
901 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
902 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
903 x_return_status OUT NOCOPY VARCHAR2,
904 x_msg_count OUT NOCOPY NUMBER,
905 x_msg_data OUT NOCOPY VARCHAR2,
906 p_search_string IN VARCHAR2 := FND_API.G_MISS_CHAR,
907 p_updated_in_days IN NUMBER := FND_API.G_MISS_NUM,
908 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
909 p_application_id IN NUMBER,
910 p_area_array IN AMV_SEARCH_PVT.amv_char_varray_type
911 := null, --amv_search_grp.Default_AreaArray,
912 p_content_array IN AMV_SEARCH_PVT.amv_char_varray_type
913 := null, --amv_search_grp.Default_ContentArray,
914 p_param_array IN AMV_SEARCH_PVT.amv_searchpar_varray_type,
915 p_user_id IN NUMBER := FND_API.G_MISS_NUM,
916 p_category_id IN AMV_SEARCH_PVT.amv_number_varray_type,
917 p_include_subcats IN VARCHAR2 := FND_API.G_FALSE,
918 p_external_contents IN VARCHAR2 := FND_API.G_FALSE,
919 p_rows_requested IN NUMBER,
920 p_start_row_pos IN NUMBER := 1,
921 p_get_total_cnt_flag IN VARCHAR2 := fnd_api.g_false,
922 x_rows_returned OUT NOCOPY NUMBER,
923 x_next_row_pos OUT NOCOPY NUMBER,
924 x_total_row_cnt OUT NOCOPY NUMBER,
925 x_result_array IN OUT NOCOPY cs_kb_result_varray_type,
926 x_amv_result_array OUT NOCOPY AMV_SEARCH_PVT.amv_searchres_varray_type
927 ) is
928 l_amv_req_obj AMV_SEARCH_PVT.amv_request_obj_type;
929 l_amv_ret_obj AMV_SEARCH_PVT.amv_return_obj_type;
930 l_amv_res_array AMV_SEARCH_PVT.amv_searchres_varray_type;
931 l_ret_cnt pls_integer :=0;
932
933 begin
934 -- x_result_array := cs_kb_result_varray_type();
935 null;
936
937 l_amv_req_obj.records_requested := p_rows_requested;
938 l_amv_req_obj.start_record_position :=p_start_row_pos;
939 l_amv_req_obj.return_total_count_flag :=p_get_total_cnt_flag;
940
941 AMV_SEARCH_GRP.Content_Search(
942 p_api_version => p_api_version,
943 p_init_msg_list => p_init_msg_list,
944 p_validation_level => p_validation_level,
945 x_return_status => x_return_status,
946 x_msg_count => x_msg_count,
947 x_msg_data => x_msg_data,
948 p_check_login_user => p_check_login_user,
949 p_application_id => p_application_id,
950 p_area_array => p_area_array,
951 p_content_array => p_content_array,
952 p_param_array => p_param_array,
953 p_imt_string => p_search_string,
954 p_days => p_updated_in_days,
955 p_user_id => p_user_id,
956 p_category_id => p_category_id,
957 p_include_subcats => p_include_subcats,
958 p_external_contents => p_external_contents,
959 p_request_obj => l_amv_req_obj,
960 x_return_obj => l_amv_ret_obj,
961 x_searchres_array => l_amv_res_array);
962
963 x_amv_result_array := l_amv_res_array;
964
965 if(x_return_status = FND_API.G_RET_STS_SUCCESS) then
966
967 x_rows_returned
968 := l_amv_ret_obj.returned_record_count;
969 x_next_row_pos
970 := l_amv_ret_obj.next_record_position;
971 x_total_row_cnt
972 := l_amv_ret_obj.total_record_count;
973
974 l_ret_cnt :=l_amv_ret_obj.returned_record_count;
975 if(l_ret_cnt> 0) then
976
977 x_result_array.EXTEND(l_ret_cnt);
978 for i in 1..l_ret_cnt loop
979 x_result_array(i) := cs_kb_result_obj_type(
980 l_amv_res_array(i).score,
981 l_amv_res_array(i).area_id,
982 l_amv_res_array(i).title,
983 -- 3430496
984 -- l_amv_res_array(i).user1,
985 -- user1 has a 'DD-MON-YYYY' format. Fine to use 'DD-MM-RRRR'
986 -- in to_date.
987 to_date(l_amv_res_array(i).user1, 'DD-MM-RRRR'),
988 l_amv_res_array(i).url_string,
989 l_amv_res_array(i).description, 'MES', null);
990 end loop;
991
992 end if;
993 end if;
994
995 end Specific_Search_Mes;
996
997 PROCEDURE Specific_Search_Sms(
998 p_api_version IN NUMBER,
999 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1000 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1001 x_return_status OUT NOCOPY VARCHAR2,
1002 x_msg_count OUT NOCOPY NUMBER,
1003 x_msg_data OUT NOCOPY VARCHAR2,
1004 p_search_string IN VARCHAR2 := FND_API.G_MISS_CHAR,
1008 p_get_total_cnt_flag IN VARCHAR2 := fnd_api.g_false,
1005 p_updated_in_days IN NUMBER := FND_API.G_MISS_NUM,
1006 p_rows_requested IN NUMBER,
1007 p_start_row_pos IN NUMBER := 1,
1009 x_rows_returned OUT NOCOPY NUMBER,
1010 x_next_row_pos OUT NOCOPY NUMBER,
1011 x_total_row_cnt OUT NOCOPY NUMBER,
1012 x_result_array IN OUT NOCOPY cs_kb_result_varray_type,
1013 p_search_option IN NUMBER := null
1014 )is
1015 err_num number;
1016 err_msg varchar2(100);
1017 l_days_cond varchar2(100) := '';
1018 l_sms_res_tbl CS_Knowledge_PUB.set_res_tbl_type;
1019 l_total_cnt pls_integer :=0;
1020 l_next_pos pls_integer :=0;
1021 l_ret_cnt pls_integer :=0;
1022 begin
1023 --null;
1024
1025 -- x_result_array := cs_kb_result_varray_type();
1026
1027 if( p_search_string = FND_API.G_MISS_CHAR ) then
1028 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
1029 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
1030 fnd_msg_pub.Add;
1031 end if;
1032 raise FND_API.G_EXC_ERROR;
1033 end if;
1034
1035
1036 if( p_updated_in_days is not null and
1037 p_updated_in_days<FND_API.G_MISS_NUM and
1038 p_updated_in_days >= 0) then
1039 l_days_cond :=
1040 ' and cs_kb_sets_b.last_update_date >= (sysdate - :2 ) ';
1041 -- ' and cs_kb_sets_vl.last_update_date >= (sysdate - ' ||
1042 -- to_char(p_updated_in_days) || ') ';
1043
1044 CS_Knowledge_PVT.Find_Sets_Matching2(
1045 p_api_version => p_api_version,
1046 p_init_msg_list => p_init_msg_list,
1047 p_validation_level => p_validation_level,
1048 x_return_status => x_return_status,
1049 x_msg_count => x_msg_count,
1050 x_msg_data => x_msg_data,
1051 p_pos_query_str => p_search_string,
1052 --p_neg_query_str => null,
1053 p_type_id_tbl => null,
1054 p_other_criteria => l_days_cond,
1055 p_other_value => p_updated_in_days,
1056 p_rows => p_rows_requested +
1057 p_start_row_pos -1,
1058 p_start_row => 1,
1059 p_get_total_flag => p_get_total_cnt_flag,
1060 x_set_tbl => l_sms_res_tbl,
1061 x_total_rows => l_total_cnt,
1062 p_search_option => p_search_option);
1063
1064 else
1065
1066 CS_Knowledge_PVT.Find_Sets_Matching(
1067 p_api_version => p_api_version,
1068 p_init_msg_list => p_init_msg_list,
1069 p_validation_level => p_validation_level,
1070 x_return_status => x_return_status,
1071 x_msg_count => x_msg_count,
1072 x_msg_data => x_msg_data,
1073 p_pos_query_str => p_search_string,
1074 p_neg_query_str => null,
1075 p_type_id_tbl => null,
1076 p_other_criteria => l_days_cond,
1077 p_rows => p_rows_requested +
1078 p_start_row_pos -1,
1079 p_start_row => 1,
1080 p_get_total_flag => p_get_total_cnt_flag,
1081 x_set_tbl => l_sms_res_tbl,
1082 x_total_rows => l_total_cnt,
1083 p_search_option => p_search_option);
1084
1085 end if;
1086
1087 -- return x_row_return_obj and x_result_array
1088 if(x_return_status = FND_API.G_RET_STS_SUCCESS) then
1089
1090 l_ret_cnt := l_sms_res_tbl.COUNT;
1091 x_total_row_cnt := l_total_cnt;
1092 x_rows_returned := l_ret_cnt;
1093 if(l_ret_cnt < l_total_cnt) then
1094 l_next_pos := l_ret_cnt +1;
1095 else
1096 l_next_pos := 0;
1097 end if;
1098 x_next_row_pos := l_next_pos;
1099
1100
1101 if(l_ret_cnt > 0) then
1102
1103 x_result_array.EXTEND(x_rows_returned);
1104
1105 for i in 1..l_ret_cnt loop
1106 x_result_array(i) := cs_kb_result_obj_type(
1107 l_sms_res_tbl(i).score,
1108 l_sms_res_tbl(i).id,
1109 l_sms_res_tbl(i).name,
1110 l_sms_res_tbl(i).last_update_date,
1111 'cskmis03.jsp?setId='||to_char(l_sms_res_tbl(i).id),
1112 null, 'SMS', l_sms_res_tbl(i).solution_number);
1113
1114 --dbms_output.put_line(to_char(i)||':'||to_char(x_result_array(i).id));
1115 --dbms_output.put_line(to_char(i)||':'||to_char(x_result_array(i).score));
1116 end loop;
1117 end if;
1118
1119 end if;
1120
1121 end Specific_Search_Sms;
1122
1123
1124 --
1125 -- This api is called by java.
1126 -- It takes object params, convert to amv record types and call
1127 -- the record type api.
1128 --
1129 PROCEDURE Specific_Search(
1130 p_api_version IN NUMBER,
1131 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1132 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1133 x_return_status OUT NOCOPY VARCHAR2,
1134 x_msg_count OUT NOCOPY NUMBER,
1135 x_msg_data OUT NOCOPY VARCHAR2,
1136 p_repository_tbl IN cs_kb_varchar100_tbl_type,
1137 p_search_string IN VARCHAR2 := FND_API.G_MISS_CHAR,
1138 p_updated_in_days IN NUMBER := FND_API.G_MISS_NUM,
1139 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1140 p_application_id IN NUMBER,
1141 p_area_array IN JTF_VARCHAR2_TABLE_4000 := null,
1142 p_content_array IN JTF_VARCHAR2_TABLE_4000 := null,
1146 p_category_id IN JTF_NUMBER_TABLE,
1143 p_param_operator_array IN JTF_VARCHAR2_TABLE_100 := null,
1144 p_param_searchstring_array IN JTF_VARCHAR2_TABLE_400 := null,
1145 p_user_id IN NUMBER := FND_API.G_MISS_NUM,
1147 p_include_subcats IN VARCHAR2 := FND_API.G_FALSE,
1148 p_external_contents IN VARCHAR2 := FND_API.G_FALSE,
1149 p_rows_requested_tbl IN cs_kb_number_tbl_type,
1150 p_start_row_pos_tbl IN cs_kb_number_tbl_type,
1151 p_get_total_cnt_flag IN VARCHAR2 := fnd_api.g_true,
1152 x_rows_returned_tbl OUT NOCOPY cs_kb_number_tbl_type,
1153 x_next_row_pos_tbl OUT NOCOPY cs_kb_number_tbl_type,
1154 x_total_row_cnt_tbl OUT NOCOPY cs_kb_number_tbl_type,
1155 x_result_array OUT NOCOPY cs_kb_result_varray_type,
1156 p_search_option IN NUMBER := NULL
1157
1158 )is
1159 l_param_array AMV_SEARCH_PVT.amv_searchpar_varray_type
1160 := AMV_SEARCH_PVT.amv_searchpar_varray_type();
1161 l_area_array AMV_SEARCH_PVT.amv_char_varray_type
1162 := AMV_SEARCH_PVT.amv_char_varray_type();
1163 l_content_array AMV_SEARCH_PVT.amv_char_varray_type
1164 := AMV_SEARCH_PVT.amv_char_varray_type();
1165 l_category_id AMV_SEARCH_PVT.amv_number_varray_type
1166 := AMV_SEARCH_PVT.amv_number_varray_type();
1167 l_amv_result_array AMV_SEARCH_PVT.amv_searchres_varray_type;
1168 i1 pls_integer;
1169 begin
1170
1171 -- convert to amv record types
1172
1173 if(p_param_operator_array is not null and
1174 p_param_operator_array.COUNT>0) then
1175 i1 := p_param_operator_array.FIRST;
1176 while i1 is not null loop
1177 l_param_array.EXTEND;
1178 l_param_array(i1).operator := p_param_operator_array(i1);
1179 l_param_array(i1).search_string := p_param_searchstring_array(i1);
1180 i1 := p_param_operator_array.NEXT(i1);
1181 end loop;
1182 end if;
1183
1184 if(p_area_array is not null and p_area_array.COUNT>0) then
1185 i1 := p_area_array.FIRST;
1186 while i1 is not null loop
1187 l_area_array.EXTEND;
1188 l_area_array(i1) := p_area_array(i1);
1189 i1 := p_area_array.NEXT(i1);
1190 end loop;
1191 end if;
1192
1193 if(p_content_array is not null and p_content_array.COUNT>0) then
1194 i1 := p_content_array.FIRST;
1195 while i1 is not null loop
1196 l_content_array.EXTEND;
1197 l_content_array(i1) := p_content_array(i1);
1198 i1 := p_content_array.NEXT(i1);
1199 end loop;
1200 end if;
1201
1202 if(p_category_id is not null and p_category_id.COUNT>0) then
1203 i1 := p_category_id.FIRST;
1204 while i1 is not null loop
1205 l_category_id.EXTEND;
1206 l_category_id(i1) := p_category_id(i1);
1207 i1 := p_category_id.NEXT(i1);
1208 end loop;
1209 end if;
1210
1211 Specific_Search(
1212 p_api_version => p_api_version,
1213 p_init_msg_list => p_init_msg_list,
1214 p_validation_level => p_validation_level,
1215 x_return_status => x_return_status,
1216 x_msg_count => x_msg_count,
1217 x_msg_data => x_msg_data,
1218 p_repository_tbl => p_repository_tbl,
1219 p_search_string => p_search_string,
1220 p_updated_in_days => p_updated_in_days,
1221 p_check_login_user => p_check_login_user,
1222 p_application_id => p_application_id,
1223 p_area_array => l_area_array,
1224 p_content_array => l_content_array,
1225 p_param_array => l_param_array,
1226 p_user_id => p_user_id,
1227 p_category_id => l_category_id,
1228 p_include_subcats => p_include_subcats,
1229 p_external_contents => p_external_contents,
1230 p_rows_requested_tbl => p_rows_requested_tbl,
1231 p_start_row_pos_tbl => p_start_row_pos_tbl,
1232 p_get_total_cnt_flag => p_get_total_cnt_flag,
1233 x_rows_returned_tbl => x_rows_returned_tbl,
1234 x_next_row_pos_tbl => x_next_row_pos_tbl,
1235 x_total_row_cnt_tbl => x_total_row_cnt_tbl,
1236 x_result_array => x_result_array,
1237 x_amv_result_array => l_amv_result_array,
1238 p_search_option => p_search_option);
1239
1240 end Specific_Search;
1241
1242
1243 --
1244 -- This api has fewer params. It calls the main Specific Search.
1245 -- This api uses amv's record types, which used to be object types.
1246 --
1247 PROCEDURE Specific_Search(
1248 p_api_version IN NUMBER,
1249 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1250 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1251 x_return_status OUT NOCOPY VARCHAR2,
1252 x_msg_count OUT NOCOPY NUMBER,
1253 x_msg_data OUT NOCOPY VARCHAR2,
1254 p_repository_tbl IN cs_kb_varchar100_tbl_type,
1255 p_search_string IN VARCHAR2 := FND_API.G_MISS_CHAR,
1256 p_updated_in_days IN NUMBER := FND_API.G_MISS_NUM,
1257 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1258 p_application_id IN NUMBER,
1259 p_area_array IN AMV_SEARCH_PVT.amv_char_varray_type
1260 := AMV_SEARCH_PVT.amv_char_varray_type(),
1261 p_content_array IN AMV_SEARCH_PVT.amv_char_varray_type
1262 := AMV_SEARCH_PVT.amv_char_varray_type(),
1266 p_category_id IN AMV_SEARCH_PVT.amv_number_varray_type
1263 p_param_array IN AMV_SEARCH_PVT.amv_searchpar_varray_type
1264 := AMV_SEARCH_PVT.amv_searchpar_varray_type(),
1265 p_user_id IN NUMBER := FND_API.G_MISS_NUM,
1267 :=AMV_SEARCH_PVT.amv_number_varray_type(),
1268 p_include_subcats IN VARCHAR2 := FND_API.G_FALSE,
1269 p_external_contents IN VARCHAR2 := FND_API.G_FALSE,
1270 p_rows_requested_tbl IN cs_kb_number_tbl_type,
1271 p_start_row_pos_tbl IN cs_kb_number_tbl_type,
1272 p_get_total_cnt_flag IN VARCHAR2 := fnd_api.g_true,
1273 x_rows_returned_tbl OUT NOCOPY cs_kb_number_tbl_type,
1274 x_next_row_pos_tbl OUT NOCOPY cs_kb_number_tbl_type,
1275 x_total_row_cnt_tbl OUT NOCOPY cs_kb_number_tbl_type,
1276 x_result_array OUT NOCOPY cs_kb_result_varray_type,
1277 p_search_option IN NUMBER := NULL
1278
1279 )is
1280 l_amv_result_array AMV_SEARCH_PVT.amv_searchres_varray_type;
1281 begin
1282 Specific_Search(
1283 p_api_version => p_api_version,
1284 p_init_msg_list => p_init_msg_list,
1285 p_validation_level => p_validation_level,
1286 x_return_status => x_return_status,
1287 x_msg_count => x_msg_count,
1288 x_msg_data => x_msg_data,
1289 p_repository_tbl => p_repository_tbl,
1290 p_search_string => p_search_string,
1291 p_updated_in_days => p_updated_in_days,
1292 p_check_login_user => p_check_login_user,
1293 p_application_id => p_application_id,
1294 p_area_array => p_area_array,
1295 p_content_array => p_content_array,
1296 p_param_array => p_param_array,
1297 p_user_id => p_user_id,
1298 p_category_id => p_category_id,
1299 p_include_subcats => p_include_subcats,
1300 p_external_contents => p_external_contents,
1301 p_rows_requested_tbl => p_rows_requested_tbl,
1302 p_start_row_pos_tbl => p_start_row_pos_tbl,
1303 p_get_total_cnt_flag => p_get_total_cnt_flag,
1304 x_rows_returned_tbl => x_rows_returned_tbl,
1305 x_next_row_pos_tbl => x_next_row_pos_tbl,
1306 x_total_row_cnt_tbl => x_total_row_cnt_tbl,
1307 x_result_array => x_result_array,
1308 x_amv_result_array => l_amv_result_array,
1309 p_search_option => p_search_option);
1310
1311 end Specific_Search;
1312
1313 --
1314 -- Main Specific search
1315 --
1316 PROCEDURE Specific_Search(
1317 p_api_version IN NUMBER,
1318 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1319 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1320 x_return_status OUT NOCOPY VARCHAR2,
1321 x_msg_count OUT NOCOPY NUMBER,
1322 x_msg_data OUT NOCOPY VARCHAR2,
1323 p_repository_tbl IN cs_kb_varchar100_tbl_type,
1324 p_search_string IN VARCHAR2 := FND_API.G_MISS_CHAR,
1325 p_updated_in_days IN NUMBER := FND_API.G_MISS_NUM,
1326 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1327 p_application_id IN NUMBER,
1328 p_area_array IN AMV_SEARCH_PVT.amv_char_varray_type
1329 := AMV_SEARCH_PVT.amv_char_varray_type(),
1330 p_content_array IN AMV_SEARCH_PVT.amv_char_varray_type
1331 := AMV_SEARCH_PVT.amv_char_varray_type(),
1332 p_param_array IN AMV_SEARCH_PVT.amv_searchpar_varray_type
1333 := AMV_SEARCH_PVT.amv_searchpar_varray_type(),
1334 p_user_id IN NUMBER := FND_API.G_MISS_NUM,
1335 p_category_id IN AMV_SEARCH_PVT.amv_number_varray_type
1336 :=AMV_SEARCH_PVT.amv_number_varray_type(),
1337 p_include_subcats IN VARCHAR2 := FND_API.G_FALSE,
1338 p_external_contents IN VARCHAR2 := FND_API.G_FALSE,
1339 p_rows_requested_tbl IN cs_kb_number_tbl_type,
1340 p_start_row_pos_tbl IN cs_kb_number_tbl_type,
1341 p_get_total_cnt_flag IN VARCHAR2 := fnd_api.g_true,
1342 x_rows_returned_tbl OUT NOCOPY cs_kb_number_tbl_type,
1343 x_next_row_pos_tbl OUT NOCOPY cs_kb_number_tbl_type,
1344 x_total_row_cnt_tbl OUT NOCOPY cs_kb_number_tbl_type,
1345 x_result_array OUT NOCOPY cs_kb_result_varray_type,
1346 x_amv_result_array OUT NOCOPY AMV_SEARCH_PVT.amv_searchres_varray_type,
1347 p_search_option IN NUMBER := NULL
1348
1349 )is
1350 l_api_name CONSTANT varchar2(30) := 'Specific_Search';
1351 l_api_version CONSTANT number := 1.0;
1352 ind pls_integer; --index number
1353
1354 -- klou add search option
1355 l_search_option NUMBER := p_search_option;
1356 begin
1357 savepoint Specific_Search_GRP;
1358
1359 if not FND_API.Compatible_API_Call(
1360 l_api_version,
1361 p_api_version,
1362 l_api_name,
1363 G_PKG_NAME) then
1364 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1365 end if;
1366
1367 if FND_API.to_Boolean(p_init_msg_list) then
1368 FND_MSG_PUB.initialize;
1369 end if;
1370
1371 x_return_status := FND_API.G_RET_STS_SUCCESS;
1372 -- -- -- -- begin my code -- -- -- -- --
1373 -- 08/10/2004, klou, check search option
1374 if l_search_option is null then
1375 -- default to interMedia search, as this API is specifically for
1376 -- eMail center, and they always want interMedia search.
1377 l_search_option := CS_KNOWLEDGE_PUB.INTERMEDIA_SYNTAX;
1378 end if;
1379
1383 x_result_array := cs_kb_result_varray_type();
1380 -- x_row_return_obj := cs_kb_rowret_obj_type(0, 0, 0);
1381
1382 -- this same array can be wrtten by both sms and mes search in out.
1384 x_rows_returned_tbl := cs_kb_number_tbl_type();
1385 x_next_row_pos_tbl :=cs_kb_number_tbl_type();
1386 x_total_row_cnt_tbl :=cs_kb_number_tbl_type();
1387
1388
1389 if(p_repository_tbl is null) then
1390 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
1391 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
1392 fnd_msg_pub.Add;
1393 end if;
1394 raise FND_API.G_EXC_ERROR;
1395 end if;
1396
1397 ind := p_repository_tbl.FIRST;
1398 while ind is not null loop
1399
1400 if(p_repository_tbl(ind) = 'MES') then
1401
1402 x_rows_returned_tbl.EXTEND;
1403 x_next_row_pos_tbl.EXTEND;
1404 x_total_row_cnt_tbl.EXTEND;
1405
1406 Specific_Search_Mes(
1407 p_api_version => p_api_version,
1408 p_init_msg_list => p_init_msg_list,
1409 p_validation_level => p_validation_level,
1410 x_return_status => x_return_status,
1411 x_msg_count => x_msg_count,
1412 x_msg_data => x_msg_data,
1413 p_search_string => p_search_string,
1414 p_updated_in_days => p_updated_in_days,
1415 p_check_login_user => p_check_login_user,
1416 p_application_id => p_application_id,
1417 p_area_array => p_area_array,
1418 p_content_array => p_content_array,
1419 p_param_array => p_param_array,
1420 p_user_id => p_user_id,
1421 p_category_id => p_category_id,
1422 p_include_subcats => p_include_subcats,
1423 p_external_contents => p_external_contents,
1424 p_rows_requested => p_rows_requested_tbl(ind),
1425 p_start_row_pos => p_start_row_pos_tbl(ind),
1426 p_get_total_cnt_flag => p_get_total_cnt_flag,
1427 x_rows_returned => x_rows_returned_tbl(ind),
1428 x_next_row_pos => x_next_row_pos_tbl(ind),
1429 x_total_row_cnt => x_total_row_cnt_tbl(ind),
1430 x_result_array => x_result_array,
1431 x_amv_result_array => x_amv_result_array);
1432
1433 elsif(p_repository_tbl(ind) = 'SMS') then
1434
1435 x_rows_returned_tbl.EXTEND;
1436 x_next_row_pos_tbl.EXTEND;
1437 x_total_row_cnt_tbl.EXTEND;
1438
1439 Specific_Search_Sms(
1440 p_api_version => p_api_version,
1441 p_init_msg_list => p_init_msg_list,
1442 p_validation_level => p_validation_level,
1443 x_return_status => x_return_status,
1444 x_msg_count => x_msg_count,
1445 x_msg_data => x_msg_data,
1446 p_search_string => p_search_string,
1447 p_updated_in_days => p_updated_in_days,
1448 p_rows_requested => p_rows_requested_tbl(ind),
1449 p_start_row_pos => p_start_row_pos_tbl(ind),
1450 p_get_total_cnt_flag => p_get_total_cnt_flag,
1451 x_rows_returned => x_rows_returned_tbl(ind),
1452 x_next_row_pos => x_next_row_pos_tbl(ind),
1453 x_total_row_cnt => x_total_row_cnt_tbl(ind),
1454 x_result_array => x_result_array,
1455 p_search_option => l_search_option);
1456 return;
1457 end if;
1458 ind := p_repository_tbl.NEXT(ind);
1459 end loop;
1460
1461 if(p_repository_tbl.COUNT=1 and
1462 p_repository_tbl(p_repository_tbl.FIRST)='ALL') then
1463
1464 ind := p_repository_tbl.FIRST;
1465 x_rows_returned_tbl.EXTEND;
1466 x_next_row_pos_tbl.EXTEND;
1467 x_total_row_cnt_tbl.EXTEND;
1468
1469 Specific_Search_Mes(
1470 p_api_version => p_api_version,
1471 p_init_msg_list => p_init_msg_list,
1472 p_validation_level => p_validation_level,
1473 x_return_status => x_return_status,
1474 x_msg_count => x_msg_count,
1475 x_msg_data => x_msg_data,
1476 p_search_string => p_search_string,
1477 p_updated_in_days => p_updated_in_days,
1478 p_check_login_user => p_check_login_user,
1479 p_application_id => p_application_id,
1480 p_area_array => p_area_array,
1481 p_content_array => p_content_array,
1482 p_param_array => p_param_array,
1483 p_user_id => p_user_id,
1484 p_category_id => p_category_id,
1485 p_include_subcats => p_include_subcats,
1486 p_external_contents => p_external_contents,
1487 p_rows_requested => p_rows_requested_tbl(ind),
1488 p_start_row_pos => p_start_row_pos_tbl(ind),
1489 p_get_total_cnt_flag => p_get_total_cnt_flag,
1490 x_rows_returned => x_rows_returned_tbl(ind),
1491 x_next_row_pos => x_next_row_pos_tbl(ind),
1492 x_total_row_cnt => x_total_row_cnt_tbl(ind),
1493 x_result_array => x_result_array,
1494 x_amv_result_array => x_amv_result_array);
1495
1496 Specific_Search_Sms(
1497 p_api_version => p_api_version,
1498 p_init_msg_list => p_init_msg_list,
1499 p_validation_level => p_validation_level,
1500 x_return_status => x_return_status,
1501 x_msg_count => x_msg_count,
1502 x_msg_data => x_msg_data,
1503 p_search_string => p_search_string,
1504 p_updated_in_days => p_updated_in_days,
1505 p_rows_requested => p_rows_requested_tbl(ind),
1509 x_next_row_pos => x_next_row_pos_tbl(ind),
1506 p_start_row_pos => p_start_row_pos_tbl(ind),
1507 p_get_total_cnt_flag => p_get_total_cnt_flag,
1508 x_rows_returned => x_rows_returned_tbl(ind),
1510 x_total_row_cnt => x_total_row_cnt_tbl(ind),
1511 x_result_array => x_result_array,
1512 p_search_option => l_search_option);
1513
1514 end if; --end if all
1515
1516 -- -- -- -- end of code -- -- -- --
1517
1518 -- Standard call to get message count. if count=1, get message info.
1519 FND_MSG_PUB.Count_And_Get(
1520 p_count => x_msg_count,
1521 p_data => x_msg_data );
1522
1523 EXCEPTION
1524 WHEN FND_API.G_EXC_ERROR THEN
1525 ROLLBACK TO Specific_Search_GRP;
1526 x_return_status := FND_API.G_RET_STS_ERROR ;
1527 FND_MSG_PUB.Count_And_Get(
1528 p_count => x_msg_count,
1529 p_data => x_msg_data );
1530 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1531 ROLLBACK TO Specific_Search_GRP;
1532 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1533 FND_MSG_PUB.Count_And_Get(
1534 p_count => x_msg_count,
1535 p_data => x_msg_data);
1536
1537 WHEN OTHERS THEN
1538 ROLLBACK TO Specific_Search_GRP;
1539 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1540 IF FND_MSG_PUB.Check_Msg_Level
1541 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1542 FND_MSG_PUB.Add_Exc_Msg(
1543 G_PKG_NAME,
1544 l_api_name);
1545 END IF;
1546 FND_MSG_PUB.Count_And_Get(
1547 p_count => x_msg_count,
1548 p_data => x_msg_data);
1549
1550
1551 end Specific_Search;
1552
1553 -- Start of comments
1554 -- API name : Purge_Knowledge_Links
1555 -- Type : Group
1556 -- Pre-reqs : None
1557 -- Function : Performs a Purge of Knowledge Links for a given object
1558 -- Parameters :
1559 -- IN p_api_version Required
1560 -- p_init_msg_list Optional - Default False
1561 -- P_COMMIT Optional - Default False
1562 -- P_PROCESSING_SET_ID Required - Processing Set - identifies rows to be purged
1563 -- P_OBJECT_TYPE Required - The Object (FK Jtf_Objects_vl.object_code)
1564 -- OUT : x_return_status
1565 -- x_msg_count
1566 -- x_msg_data
1567 -- Version : Initial version 1.0
1568 -- Notes :
1569 -- End of comments
1570 PROCEDURE Purge_Knowledge_Links (
1571 P_API_VERSION IN NUMBER,
1572 P_INIT_MSG_LIST IN VARCHAR2,
1573 P_COMMIT IN VARCHAR2,
1574 P_PROCESSING_SET_ID IN NUMBER,
1575 P_OBJECT_TYPE IN VARCHAR2,
1576 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1577 X_MSG_COUNT OUT NOCOPY NUMBER,
1578 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
1579
1580 BEGIN
1581 SAVEPOINT Purge_Knowledge_Links_GRP;
1582 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1583
1584 IF FND_API.to_Boolean(P_INIT_MSG_LIST) THEN
1585 FND_MSG_PUB.initialize;
1586 END IF;
1587
1588 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1589 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KNOWLEDGE_GRP.Purge_Knowledge_Links.Start',
1590 'Purge_Knowledge_Links for - '||P_PROCESSING_SET_ID||' '||P_OBJECT_TYPE );
1591 END IF;
1592
1593 IF (P_PROCESSING_SET_ID IS NULL OR P_OBJECT_TYPE IS NULL) THEN
1594 FND_MESSAGE.set_name('CS', 'CS_KB_C_MISS_PARAM');
1595 FND_MSG_PUB.ADD;
1596 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1597 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1598 p_count => X_MSG_COUNT,
1599 p_data => X_MSG_DATA);
1600 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1601
1602 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1603
1604 'cs.plsql.CS_KNOWLEDGE_GRP.Purge_Knowledge_Links.InvalidParams',
1605
1606 'Purge_Knowledge_Links for - '||P_PROCESSING_SET_ID||' '||P_OBJECT_TYPE
1607 );
1608
1609 END IF;
1610 ELSE
1611
1612 DELETE /*+ index(l) */ FROM CS_KB_SET_LINKS l
1613 WHERE l.Object_Code = P_OBJECT_TYPE
1614 AND l.Other_id IN ( SELECT /*+ no_unnest no_semijoin cardinality(10) */ t.Object_id
1615 FROM JTF_OBJECT_PURGE_PARAM_TMP t
1616 WHERE nvl(t.purge_status, 'S') <> 'E'
1617 AND t.Processing_Set_Id = P_PROCESSING_SET_ID
1618 AND t.Object_Type = P_OBJECT_TYPE
1619 );
1620
1621 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1622
1623 IF FND_API.to_Boolean(P_COMMIT) THEN
1624 COMMIT;
1625 END IF;
1626
1627 END IF;
1628
1629 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1630 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KNOWLEDGE_GRP.Purge_Knowledge_Links.End',
1631 'Purge_Knowledge_Links Status - '||X_RETURN_STATUS );
1632 END IF;
1633
1634 EXCEPTION
1635 WHEN OTHERS THEN
1636 ROLLBACK TO Purge_Knowledge_Links_GRP;
1637 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1638
1639 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'cs.plsql.CS_KNOWLEDGE_GRP.Purge_Knowledge_Links.UNEXPECTED',
1640
1641 ' Error= '||sqlerrm);
1642
1643 END IF;
1644
1645 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR ;
1646 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1647 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
1648 'Purge_Knowledge_Links');
1649 END IF;
1650 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE ,
1651 p_count => x_msg_count,
1652 p_data => x_msg_data);
1653 END Purge_Knowledge_Links;
1654
1655
1656 end CS_Knowledge_Grp;