[Home] [Help]
PACKAGE BODY: APPS.CS_KNOWLEDGE_GRP
Source
1 PACKAGE BODY CS_Knowledge_Grp AS
2 /* $Header: csgkbb.pls 120.4 2011/06/23 09:43:24 gasankar 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,
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,
446 x_msg_count OUT NOCOPY number,
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(
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,
588 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
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,
734 P_ATTRIBUTE4 => p_set_link_rec.attribute4,
735 P_ATTRIBUTE5 => p_set_link_rec.attribute5,
736 P_ATTRIBUTE6 => p_set_link_rec.attribute6,
737 P_ATTRIBUTE7 => p_set_link_rec.attribute7,
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_SET_LINK_ID := l_id; -- Bug fix 12682708
752 X_RETURN_STATUS := l_return_status;
753 X_MSG_DATA := l_msg_data;
754 X_MSG_COUNT := l_msg_count;
755
756 IF FND_API.To_Boolean( p_commit ) AND
757 l_return_status = FND_API.G_RET_STS_SUCCESS THEN
758 COMMIT WORK;
759 END IF;
760
761 EXCEPTION
762 WHEN FND_API.G_EXC_ERROR THEN
763
764 ROLLBACK TO Create_Set_Link_Grp;
765
766 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR ;
767
768 FND_MSG_PUB.Count_And_Get(
769 p_encoded => FND_API.G_FALSE,
770 p_count => x_msg_count,
771 p_data => x_msg_data );
772
773 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
774
775 ROLLBACK TO Create_Set_Link_Grp;
776
777 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR ;
778
779 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
780 p_count => X_MSG_COUNT,
781 p_data => X_MSG_DATA);
782 WHEN OTHERS THEN
783
784 ROLLBACK TO Create_Set_Link_Grp;
785
786 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR ;
787
788 IF FND_MSG_PUB.Check_Msg_Level
789 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
790 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
791 END IF;
792
793 FND_MSG_PUB.Count_And_Get(
794 p_encoded => FND_API.G_FALSE ,
795 p_count => x_msg_count,
796 p_data => x_msg_data);
797
798 END Create_Set_Link;
799
800 PROCEDURE Update_Set_Link(
801 p_api_version in number,
802 p_init_msg_list in varchar2 := FND_API.G_FALSE,
803 p_commit in varchar2 := FND_API.G_FALSE,
804 p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
805 x_return_status OUT NOCOPY varchar2,
806 x_msg_count OUT NOCOPY number,
807 x_msg_data OUT NOCOPY varchar2,
808 p_set_link_rec in CS_KB_SET_LINKS%ROWTYPE
809 ) is
810 l_api_name CONSTANT varchar2(30) := 'Update_Set_Link';
811 l_api_version CONSTANT number := 1.0;
812 l_date date;
813 l_created_by number;
814 l_login number;
815 l_sta number(15);
816 begin
817 savepoint Update_Set_Link_Grp;
818
819 if not FND_API.Compatible_API_Call(
820 l_api_version,
821 p_api_version,
822 l_api_name,
823 G_PKG_NAME) then
824 raise FND_API.G_EXC_UNEXPECTED_ERROR;
825 end if;
826
827 if FND_API.to_Boolean(p_init_msg_list) then
828 FND_MSG_PUB.initialize;
829 end if;
830
831 x_return_status := FND_API.G_RET_STS_SUCCESS;
832
833 -- -- -- -- begin my code -- -- -- -- --
834
835 l_sta := CS_KB_SET_LINKS_PKG.Update_Set_Link(
836 p_set_link_rec.link_id,
837 p_set_link_rec.link_type,
838 p_set_link_rec.object_code,
839 p_set_link_rec.set_id,
840 p_set_link_rec.other_id,
841 p_set_link_rec.attribute_category,
842 p_set_link_rec.attribute1,
843 p_set_link_rec.attribute2,
844 p_set_link_rec.attribute3,
845 p_set_link_rec.attribute4,
846 p_set_link_rec.attribute5,
847 p_set_link_rec.attribute6,
848 p_set_link_rec.attribute7,
849 p_set_link_rec.attribute8,
850 p_set_link_rec.attribute9,
851 p_set_link_rec.attribute10,
852 p_set_link_rec.attribute11,
853 p_set_link_rec.attribute12,
854 p_set_link_rec.attribute13,
855 p_set_link_rec.attribute14,
856 p_set_link_rec.attribute15
857 );
858 if(l_sta<>CS_KB_SET_LINKS_PKG.OKAY_STATUS) then
859 raise FND_API.G_EXC_UNEXPECTED_ERROR;
860 end if;
861
862 -- -- -- -- end of code -- -- --
863
864 IF FND_API.To_Boolean( p_commit ) THEN
865 COMMIT WORK;
866 END IF;
867
868 -- Standard call to get message count. if count=1, get message info.
869 FND_MSG_PUB.Count_And_Get(
870 p_count => x_msg_count,
871 p_data => x_msg_data );
872
873 EXCEPTION
874 WHEN FND_API.G_EXC_ERROR THEN
875 ROLLBACK TO Update_Set_Link_Grp;
876 x_return_status := FND_API.G_RET_STS_ERROR ;
877 FND_MSG_PUB.Count_And_Get(
878 p_count => x_msg_count,
879 p_data => x_msg_data );
880 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
881 ROLLBACK TO Update_Set_Link_Grp;
882 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
883 FND_MSG_PUB.Count_And_Get(
884 p_count => x_msg_count,
885 p_data => x_msg_data);
886 WHEN OTHERS THEN
887 ROLLBACK TO Update_Set_Link_Grp;
888 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
889 IF FND_MSG_PUB.Check_Msg_Level
890 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
891 FND_MSG_PUB.Add_Exc_Msg(
892 G_PKG_NAME,
893 l_api_name);
894 END IF;
895 FND_MSG_PUB.Count_And_Get(
896 p_count => x_msg_count,
897 p_data => x_msg_data);
898 end Update_Set_Link;
899
900 PROCEDURE Specific_Search_Mes(
901 p_api_version IN NUMBER,
902 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
903 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
904 x_return_status OUT NOCOPY VARCHAR2,
905 x_msg_count OUT NOCOPY NUMBER,
906 x_msg_data OUT NOCOPY VARCHAR2,
907 p_search_string IN VARCHAR2 := FND_API.G_MISS_CHAR,
908 p_updated_in_days IN NUMBER := FND_API.G_MISS_NUM,
909 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
910 p_application_id IN NUMBER,
911 p_area_array IN AMV_SEARCH_PVT.amv_char_varray_type
912 := null, --amv_search_grp.Default_AreaArray,
913 p_content_array IN AMV_SEARCH_PVT.amv_char_varray_type
914 := null, --amv_search_grp.Default_ContentArray,
915 p_param_array IN AMV_SEARCH_PVT.amv_searchpar_varray_type,
916 p_user_id IN NUMBER := FND_API.G_MISS_NUM,
917 p_category_id IN AMV_SEARCH_PVT.amv_number_varray_type,
918 p_include_subcats IN VARCHAR2 := FND_API.G_FALSE,
919 p_external_contents IN VARCHAR2 := FND_API.G_FALSE,
920 p_rows_requested IN NUMBER,
921 p_start_row_pos IN NUMBER := 1,
922 p_get_total_cnt_flag IN VARCHAR2 := fnd_api.g_false,
923 x_rows_returned OUT NOCOPY NUMBER,
924 x_next_row_pos OUT NOCOPY NUMBER,
925 x_total_row_cnt OUT NOCOPY NUMBER,
926 x_result_array IN OUT NOCOPY cs_kb_result_varray_type,
927 x_amv_result_array OUT NOCOPY AMV_SEARCH_PVT.amv_searchres_varray_type
928 ) is
929 l_amv_req_obj AMV_SEARCH_PVT.amv_request_obj_type;
930 l_amv_ret_obj AMV_SEARCH_PVT.amv_return_obj_type;
931 l_amv_res_array AMV_SEARCH_PVT.amv_searchres_varray_type;
932 l_ret_cnt pls_integer :=0;
933
934 begin
935 -- x_result_array := cs_kb_result_varray_type();
936 null;
937
938 l_amv_req_obj.records_requested := p_rows_requested;
939 l_amv_req_obj.start_record_position :=p_start_row_pos;
940 l_amv_req_obj.return_total_count_flag :=p_get_total_cnt_flag;
941
942 AMV_SEARCH_GRP.Content_Search(
943 p_api_version => p_api_version,
944 p_init_msg_list => p_init_msg_list,
945 p_validation_level => p_validation_level,
946 x_return_status => x_return_status,
947 x_msg_count => x_msg_count,
948 x_msg_data => x_msg_data,
949 p_check_login_user => p_check_login_user,
950 p_application_id => p_application_id,
951 p_area_array => p_area_array,
952 p_content_array => p_content_array,
953 p_param_array => p_param_array,
954 p_imt_string => p_search_string,
955 p_days => p_updated_in_days,
956 p_user_id => p_user_id,
957 p_category_id => p_category_id,
958 p_include_subcats => p_include_subcats,
959 p_external_contents => p_external_contents,
960 p_request_obj => l_amv_req_obj,
961 x_return_obj => l_amv_ret_obj,
962 x_searchres_array => l_amv_res_array);
963
964 x_amv_result_array := l_amv_res_array;
965
966 if(x_return_status = FND_API.G_RET_STS_SUCCESS) then
967
968 x_rows_returned
969 := l_amv_ret_obj.returned_record_count;
970 x_next_row_pos
971 := l_amv_ret_obj.next_record_position;
972 x_total_row_cnt
973 := l_amv_ret_obj.total_record_count;
974
975 l_ret_cnt :=l_amv_ret_obj.returned_record_count;
976 if(l_ret_cnt> 0) then
977
978 x_result_array.EXTEND(l_ret_cnt);
979 for i in 1..l_ret_cnt loop
980 x_result_array(i) := cs_kb_result_obj_type(
981 l_amv_res_array(i).score,
982 l_amv_res_array(i).area_id,
983 l_amv_res_array(i).title,
984 -- 3430496
985 -- l_amv_res_array(i).user1,
986 -- user1 has a 'DD-MON-YYYY' format. Fine to use 'DD-MM-RRRR'
987 -- in to_date.
988 to_date(l_amv_res_array(i).user1, 'DD-MM-RRRR'),
989 l_amv_res_array(i).url_string,
990 l_amv_res_array(i).description, 'MES', null);
991 end loop;
992
993 end if;
994 end if;
995
996 end Specific_Search_Mes;
997
998 PROCEDURE Specific_Search_Sms(
999 p_api_version IN NUMBER,
1000 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1001 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1002 x_return_status OUT NOCOPY VARCHAR2,
1003 x_msg_count OUT NOCOPY NUMBER,
1004 x_msg_data OUT NOCOPY VARCHAR2,
1005 p_search_string IN VARCHAR2 := FND_API.G_MISS_CHAR,
1006 p_updated_in_days IN NUMBER := FND_API.G_MISS_NUM,
1007 p_rows_requested IN NUMBER,
1008 p_start_row_pos IN NUMBER := 1,
1009 p_get_total_cnt_flag IN VARCHAR2 := fnd_api.g_false,
1010 x_rows_returned OUT NOCOPY NUMBER,
1011 x_next_row_pos OUT NOCOPY NUMBER,
1012 x_total_row_cnt OUT NOCOPY NUMBER,
1013 x_result_array IN OUT NOCOPY cs_kb_result_varray_type,
1014 p_search_option IN NUMBER := null
1015 )is
1016 err_num number;
1017 err_msg varchar2(100);
1018 l_days_cond varchar2(100) := '';
1019 l_sms_res_tbl CS_Knowledge_PUB.set_res_tbl_type;
1020 l_total_cnt pls_integer :=0;
1021 l_next_pos pls_integer :=0;
1022 l_ret_cnt pls_integer :=0;
1023 begin
1024 --null;
1025
1026 -- x_result_array := cs_kb_result_varray_type();
1027
1028 if( p_search_string = FND_API.G_MISS_CHAR ) then
1029 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
1030 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
1031 fnd_msg_pub.Add;
1032 end if;
1033 raise FND_API.G_EXC_ERROR;
1034 end if;
1035
1036
1037 if( p_updated_in_days is not null and
1038 p_updated_in_days<FND_API.G_MISS_NUM and
1039 p_updated_in_days >= 0) then
1040 l_days_cond :=
1041 ' and cs_kb_sets_b.last_update_date >= (sysdate - :2 ) ';
1042 -- ' and cs_kb_sets_vl.last_update_date >= (sysdate - ' ||
1043 -- to_char(p_updated_in_days) || ') ';
1044
1045 CS_Knowledge_PVT.Find_Sets_Matching2(
1046 p_api_version => p_api_version,
1047 p_init_msg_list => p_init_msg_list,
1048 p_validation_level => p_validation_level,
1049 x_return_status => x_return_status,
1050 x_msg_count => x_msg_count,
1051 x_msg_data => x_msg_data,
1052 p_pos_query_str => p_search_string,
1053 --p_neg_query_str => null,
1054 p_type_id_tbl => null,
1055 p_other_criteria => l_days_cond,
1056 p_other_value => p_updated_in_days,
1057 p_rows => p_rows_requested +
1058 p_start_row_pos -1,
1059 p_start_row => 1,
1060 p_get_total_flag => p_get_total_cnt_flag,
1061 x_set_tbl => l_sms_res_tbl,
1062 x_total_rows => l_total_cnt,
1063 p_search_option => p_search_option);
1064
1065 else
1066
1067 CS_Knowledge_PVT.Find_Sets_Matching(
1068 p_api_version => p_api_version,
1069 p_init_msg_list => p_init_msg_list,
1070 p_validation_level => p_validation_level,
1071 x_return_status => x_return_status,
1072 x_msg_count => x_msg_count,
1073 x_msg_data => x_msg_data,
1074 p_pos_query_str => p_search_string,
1075 p_neg_query_str => null,
1076 p_type_id_tbl => null,
1077 p_other_criteria => l_days_cond,
1078 p_rows => p_rows_requested +
1079 p_start_row_pos -1,
1080 p_start_row => 1,
1081 p_get_total_flag => p_get_total_cnt_flag,
1082 x_set_tbl => l_sms_res_tbl,
1083 x_total_rows => l_total_cnt,
1084 p_search_option => p_search_option);
1085
1086 end if;
1087
1088 -- return x_row_return_obj and x_result_array
1089 if(x_return_status = FND_API.G_RET_STS_SUCCESS) then
1090
1091 l_ret_cnt := l_sms_res_tbl.COUNT;
1092 x_total_row_cnt := l_total_cnt;
1093 x_rows_returned := l_ret_cnt;
1094 if(l_ret_cnt < l_total_cnt) then
1095 l_next_pos := l_ret_cnt +1;
1096 else
1097 l_next_pos := 0;
1098 end if;
1099 x_next_row_pos := l_next_pos;
1100
1101
1102 if(l_ret_cnt > 0) then
1103
1104 x_result_array.EXTEND(x_rows_returned);
1105
1106 for i in 1..l_ret_cnt loop
1107 x_result_array(i) := cs_kb_result_obj_type(
1108 l_sms_res_tbl(i).score,
1109 l_sms_res_tbl(i).id,
1110 l_sms_res_tbl(i).name,
1111 l_sms_res_tbl(i).last_update_date,
1112 'cskmis03.jsp?setId='||to_char(l_sms_res_tbl(i).id),
1113 null, 'SMS', l_sms_res_tbl(i).solution_number);
1114
1115 --dbms_output.put_line(to_char(i)||':'||to_char(x_result_array(i).id));
1116 --dbms_output.put_line(to_char(i)||':'||to_char(x_result_array(i).score));
1117 end loop;
1118 end if;
1119
1120 end if;
1121
1122 end Specific_Search_Sms;
1123
1124
1125 --
1126 -- This api is called by java.
1127 -- It takes object params, convert to amv record types and call
1128 -- the record type api.
1129 --
1130 PROCEDURE Specific_Search(
1131 p_api_version IN NUMBER,
1132 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1133 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1134 x_return_status OUT NOCOPY VARCHAR2,
1135 x_msg_count OUT NOCOPY NUMBER,
1136 x_msg_data OUT NOCOPY VARCHAR2,
1137 p_repository_tbl IN cs_kb_varchar100_tbl_type,
1138 p_search_string IN VARCHAR2 := FND_API.G_MISS_CHAR,
1139 p_updated_in_days IN NUMBER := FND_API.G_MISS_NUM,
1140 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1141 p_application_id IN NUMBER,
1142 p_area_array IN JTF_VARCHAR2_TABLE_4000 := null,
1143 p_content_array IN JTF_VARCHAR2_TABLE_4000 := null,
1144 p_param_operator_array IN JTF_VARCHAR2_TABLE_100 := null,
1145 p_param_searchstring_array IN JTF_VARCHAR2_TABLE_400 := null,
1146 p_user_id IN NUMBER := FND_API.G_MISS_NUM,
1147 p_category_id IN JTF_NUMBER_TABLE,
1148 p_include_subcats IN VARCHAR2 := FND_API.G_FALSE,
1149 p_external_contents IN VARCHAR2 := FND_API.G_FALSE,
1150 p_rows_requested_tbl IN cs_kb_number_tbl_type,
1151 p_start_row_pos_tbl IN cs_kb_number_tbl_type,
1152 p_get_total_cnt_flag IN VARCHAR2 := fnd_api.g_true,
1153 x_rows_returned_tbl OUT NOCOPY cs_kb_number_tbl_type,
1154 x_next_row_pos_tbl OUT NOCOPY cs_kb_number_tbl_type,
1155 x_total_row_cnt_tbl OUT NOCOPY cs_kb_number_tbl_type,
1156 x_result_array OUT NOCOPY cs_kb_result_varray_type,
1157 p_search_option IN NUMBER := NULL
1158
1159 )is
1160 l_param_array AMV_SEARCH_PVT.amv_searchpar_varray_type
1161 := AMV_SEARCH_PVT.amv_searchpar_varray_type();
1162 l_area_array AMV_SEARCH_PVT.amv_char_varray_type
1163 := AMV_SEARCH_PVT.amv_char_varray_type();
1164 l_content_array AMV_SEARCH_PVT.amv_char_varray_type
1165 := AMV_SEARCH_PVT.amv_char_varray_type();
1166 l_category_id AMV_SEARCH_PVT.amv_number_varray_type
1167 := AMV_SEARCH_PVT.amv_number_varray_type();
1168 l_amv_result_array AMV_SEARCH_PVT.amv_searchres_varray_type;
1169 i1 pls_integer;
1170 begin
1171
1172 -- convert to amv record types
1173
1174 if(p_param_operator_array is not null and
1175 p_param_operator_array.COUNT>0) then
1176 i1 := p_param_operator_array.FIRST;
1177 while i1 is not null loop
1178 l_param_array.EXTEND;
1179 l_param_array(i1).operator := p_param_operator_array(i1);
1180 l_param_array(i1).search_string := p_param_searchstring_array(i1);
1181 i1 := p_param_operator_array.NEXT(i1);
1182 end loop;
1183 end if;
1184
1185 if(p_area_array is not null and p_area_array.COUNT>0) then
1186 i1 := p_area_array.FIRST;
1187 while i1 is not null loop
1188 l_area_array.EXTEND;
1189 l_area_array(i1) := p_area_array(i1);
1190 i1 := p_area_array.NEXT(i1);
1191 end loop;
1192 end if;
1193
1194 if(p_content_array is not null and p_content_array.COUNT>0) then
1195 i1 := p_content_array.FIRST;
1196 while i1 is not null loop
1197 l_content_array.EXTEND;
1198 l_content_array(i1) := p_content_array(i1);
1199 i1 := p_content_array.NEXT(i1);
1200 end loop;
1201 end if;
1202
1203 if(p_category_id is not null and p_category_id.COUNT>0) then
1204 i1 := p_category_id.FIRST;
1205 while i1 is not null loop
1206 l_category_id.EXTEND;
1207 l_category_id(i1) := p_category_id(i1);
1208 i1 := p_category_id.NEXT(i1);
1209 end loop;
1210 end if;
1211
1212 Specific_Search(
1213 p_api_version => p_api_version,
1214 p_init_msg_list => p_init_msg_list,
1215 p_validation_level => p_validation_level,
1216 x_return_status => x_return_status,
1217 x_msg_count => x_msg_count,
1218 x_msg_data => x_msg_data,
1219 p_repository_tbl => p_repository_tbl,
1220 p_search_string => p_search_string,
1221 p_updated_in_days => p_updated_in_days,
1222 p_check_login_user => p_check_login_user,
1223 p_application_id => p_application_id,
1224 p_area_array => l_area_array,
1225 p_content_array => l_content_array,
1226 p_param_array => l_param_array,
1227 p_user_id => p_user_id,
1228 p_category_id => l_category_id,
1229 p_include_subcats => p_include_subcats,
1230 p_external_contents => p_external_contents,
1231 p_rows_requested_tbl => p_rows_requested_tbl,
1232 p_start_row_pos_tbl => p_start_row_pos_tbl,
1233 p_get_total_cnt_flag => p_get_total_cnt_flag,
1234 x_rows_returned_tbl => x_rows_returned_tbl,
1235 x_next_row_pos_tbl => x_next_row_pos_tbl,
1236 x_total_row_cnt_tbl => x_total_row_cnt_tbl,
1237 x_result_array => x_result_array,
1238 x_amv_result_array => l_amv_result_array,
1239 p_search_option => p_search_option);
1240
1241 end Specific_Search;
1242
1243
1244 --
1245 -- This api has fewer params. It calls the main Specific Search.
1246 -- This api uses amv's record types, which used to be object types.
1247 --
1248 PROCEDURE Specific_Search(
1249 p_api_version IN NUMBER,
1250 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1251 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1252 x_return_status OUT NOCOPY VARCHAR2,
1253 x_msg_count OUT NOCOPY NUMBER,
1254 x_msg_data OUT NOCOPY VARCHAR2,
1255 p_repository_tbl IN cs_kb_varchar100_tbl_type,
1256 p_search_string IN VARCHAR2 := FND_API.G_MISS_CHAR,
1257 p_updated_in_days IN NUMBER := FND_API.G_MISS_NUM,
1258 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1259 p_application_id IN NUMBER,
1260 p_area_array IN AMV_SEARCH_PVT.amv_char_varray_type
1261 := AMV_SEARCH_PVT.amv_char_varray_type(),
1262 p_content_array IN AMV_SEARCH_PVT.amv_char_varray_type
1263 := AMV_SEARCH_PVT.amv_char_varray_type(),
1264 p_param_array IN AMV_SEARCH_PVT.amv_searchpar_varray_type
1265 := AMV_SEARCH_PVT.amv_searchpar_varray_type(),
1266 p_user_id IN NUMBER := FND_API.G_MISS_NUM,
1267 p_category_id IN AMV_SEARCH_PVT.amv_number_varray_type
1268 :=AMV_SEARCH_PVT.amv_number_varray_type(),
1269 p_include_subcats IN VARCHAR2 := FND_API.G_FALSE,
1270 p_external_contents IN VARCHAR2 := FND_API.G_FALSE,
1271 p_rows_requested_tbl IN cs_kb_number_tbl_type,
1272 p_start_row_pos_tbl IN cs_kb_number_tbl_type,
1273 p_get_total_cnt_flag IN VARCHAR2 := fnd_api.g_true,
1274 x_rows_returned_tbl OUT NOCOPY cs_kb_number_tbl_type,
1275 x_next_row_pos_tbl OUT NOCOPY cs_kb_number_tbl_type,
1276 x_total_row_cnt_tbl OUT NOCOPY cs_kb_number_tbl_type,
1277 x_result_array OUT NOCOPY cs_kb_result_varray_type,
1278 p_search_option IN NUMBER := NULL
1279
1280 )is
1281 l_amv_result_array AMV_SEARCH_PVT.amv_searchres_varray_type;
1282 begin
1283 Specific_Search(
1284 p_api_version => p_api_version,
1285 p_init_msg_list => p_init_msg_list,
1286 p_validation_level => p_validation_level,
1287 x_return_status => x_return_status,
1288 x_msg_count => x_msg_count,
1289 x_msg_data => x_msg_data,
1290 p_repository_tbl => p_repository_tbl,
1291 p_search_string => p_search_string,
1292 p_updated_in_days => p_updated_in_days,
1293 p_check_login_user => p_check_login_user,
1294 p_application_id => p_application_id,
1295 p_area_array => p_area_array,
1296 p_content_array => p_content_array,
1297 p_param_array => p_param_array,
1298 p_user_id => p_user_id,
1299 p_category_id => p_category_id,
1300 p_include_subcats => p_include_subcats,
1301 p_external_contents => p_external_contents,
1302 p_rows_requested_tbl => p_rows_requested_tbl,
1303 p_start_row_pos_tbl => p_start_row_pos_tbl,
1304 p_get_total_cnt_flag => p_get_total_cnt_flag,
1305 x_rows_returned_tbl => x_rows_returned_tbl,
1306 x_next_row_pos_tbl => x_next_row_pos_tbl,
1307 x_total_row_cnt_tbl => x_total_row_cnt_tbl,
1308 x_result_array => x_result_array,
1309 x_amv_result_array => l_amv_result_array,
1310 p_search_option => p_search_option);
1311
1312 end Specific_Search;
1313
1314 --
1315 -- Main Specific search
1316 --
1317 PROCEDURE Specific_Search(
1318 p_api_version IN NUMBER,
1319 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1320 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1321 x_return_status OUT NOCOPY VARCHAR2,
1322 x_msg_count OUT NOCOPY NUMBER,
1323 x_msg_data OUT NOCOPY VARCHAR2,
1324 p_repository_tbl IN cs_kb_varchar100_tbl_type,
1325 p_search_string IN VARCHAR2 := FND_API.G_MISS_CHAR,
1326 p_updated_in_days IN NUMBER := FND_API.G_MISS_NUM,
1327 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1328 p_application_id IN NUMBER,
1329 p_area_array IN AMV_SEARCH_PVT.amv_char_varray_type
1330 := AMV_SEARCH_PVT.amv_char_varray_type(),
1331 p_content_array IN AMV_SEARCH_PVT.amv_char_varray_type
1332 := AMV_SEARCH_PVT.amv_char_varray_type(),
1333 p_param_array IN AMV_SEARCH_PVT.amv_searchpar_varray_type
1334 := AMV_SEARCH_PVT.amv_searchpar_varray_type(),
1335 p_user_id IN NUMBER := FND_API.G_MISS_NUM,
1336 p_category_id IN AMV_SEARCH_PVT.amv_number_varray_type
1337 :=AMV_SEARCH_PVT.amv_number_varray_type(),
1338 p_include_subcats IN VARCHAR2 := FND_API.G_FALSE,
1339 p_external_contents IN VARCHAR2 := FND_API.G_FALSE,
1340 p_rows_requested_tbl IN cs_kb_number_tbl_type,
1341 p_start_row_pos_tbl IN cs_kb_number_tbl_type,
1342 p_get_total_cnt_flag IN VARCHAR2 := fnd_api.g_true,
1343 x_rows_returned_tbl OUT NOCOPY cs_kb_number_tbl_type,
1344 x_next_row_pos_tbl OUT NOCOPY cs_kb_number_tbl_type,
1345 x_total_row_cnt_tbl OUT NOCOPY cs_kb_number_tbl_type,
1346 x_result_array OUT NOCOPY cs_kb_result_varray_type,
1347 x_amv_result_array OUT NOCOPY AMV_SEARCH_PVT.amv_searchres_varray_type,
1348 p_search_option IN NUMBER := NULL
1349
1350 )is
1351 l_api_name CONSTANT varchar2(30) := 'Specific_Search';
1352 l_api_version CONSTANT number := 1.0;
1353 ind pls_integer; --index number
1354
1355 -- klou add search option
1356 l_search_option NUMBER := p_search_option;
1357 begin
1358 savepoint Specific_Search_GRP;
1359
1360 if not FND_API.Compatible_API_Call(
1361 l_api_version,
1362 p_api_version,
1363 l_api_name,
1364 G_PKG_NAME) then
1365 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1366 end if;
1367
1368 if FND_API.to_Boolean(p_init_msg_list) then
1369 FND_MSG_PUB.initialize;
1370 end if;
1371
1372 x_return_status := FND_API.G_RET_STS_SUCCESS;
1373 -- -- -- -- begin my code -- -- -- -- --
1374 -- 08/10/2004, klou, check search option
1375 if l_search_option is null then
1376 -- default to interMedia search, as this API is specifically for
1377 -- eMail center, and they always want interMedia search.
1378 l_search_option := CS_KNOWLEDGE_PUB.INTERMEDIA_SYNTAX;
1379 end if;
1380
1381 -- x_row_return_obj := cs_kb_rowret_obj_type(0, 0, 0);
1382
1383 -- this same array can be wrtten by both sms and mes search in out.
1384 x_result_array := cs_kb_result_varray_type();
1385 x_rows_returned_tbl := cs_kb_number_tbl_type();
1386 x_next_row_pos_tbl :=cs_kb_number_tbl_type();
1387 x_total_row_cnt_tbl :=cs_kb_number_tbl_type();
1388
1389
1390 if(p_repository_tbl is null) then
1391 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
1392 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
1393 fnd_msg_pub.Add;
1394 end if;
1395 raise FND_API.G_EXC_ERROR;
1396 end if;
1397
1398 ind := p_repository_tbl.FIRST;
1399 while ind is not null loop
1400
1401 if(p_repository_tbl(ind) = 'MES') then
1402
1403 x_rows_returned_tbl.EXTEND;
1404 x_next_row_pos_tbl.EXTEND;
1405 x_total_row_cnt_tbl.EXTEND;
1406
1407 Specific_Search_Mes(
1408 p_api_version => p_api_version,
1409 p_init_msg_list => p_init_msg_list,
1410 p_validation_level => p_validation_level,
1411 x_return_status => x_return_status,
1412 x_msg_count => x_msg_count,
1413 x_msg_data => x_msg_data,
1414 p_search_string => p_search_string,
1415 p_updated_in_days => p_updated_in_days,
1416 p_check_login_user => p_check_login_user,
1417 p_application_id => p_application_id,
1418 p_area_array => p_area_array,
1419 p_content_array => p_content_array,
1420 p_param_array => p_param_array,
1421 p_user_id => p_user_id,
1422 p_category_id => p_category_id,
1423 p_include_subcats => p_include_subcats,
1424 p_external_contents => p_external_contents,
1425 p_rows_requested => p_rows_requested_tbl(ind),
1426 p_start_row_pos => p_start_row_pos_tbl(ind),
1427 p_get_total_cnt_flag => p_get_total_cnt_flag,
1428 x_rows_returned => x_rows_returned_tbl(ind),
1429 x_next_row_pos => x_next_row_pos_tbl(ind),
1430 x_total_row_cnt => x_total_row_cnt_tbl(ind),
1431 x_result_array => x_result_array,
1432 x_amv_result_array => x_amv_result_array);
1433
1434 elsif(p_repository_tbl(ind) = 'SMS') then
1435
1436 x_rows_returned_tbl.EXTEND;
1437 x_next_row_pos_tbl.EXTEND;
1438 x_total_row_cnt_tbl.EXTEND;
1439
1440 Specific_Search_Sms(
1441 p_api_version => p_api_version,
1442 p_init_msg_list => p_init_msg_list,
1443 p_validation_level => p_validation_level,
1444 x_return_status => x_return_status,
1445 x_msg_count => x_msg_count,
1446 x_msg_data => x_msg_data,
1447 p_search_string => p_search_string,
1448 p_updated_in_days => p_updated_in_days,
1449 p_rows_requested => p_rows_requested_tbl(ind),
1450 p_start_row_pos => p_start_row_pos_tbl(ind),
1451 p_get_total_cnt_flag => p_get_total_cnt_flag,
1452 x_rows_returned => x_rows_returned_tbl(ind),
1453 x_next_row_pos => x_next_row_pos_tbl(ind),
1454 x_total_row_cnt => x_total_row_cnt_tbl(ind),
1455 x_result_array => x_result_array,
1456 p_search_option => l_search_option);
1457 return;
1458 end if;
1459 ind := p_repository_tbl.NEXT(ind);
1460 end loop;
1461
1462 if(p_repository_tbl.COUNT=1 and
1463 p_repository_tbl(p_repository_tbl.FIRST)='ALL') then
1464
1465 ind := p_repository_tbl.FIRST;
1466 x_rows_returned_tbl.EXTEND;
1467 x_next_row_pos_tbl.EXTEND;
1468 x_total_row_cnt_tbl.EXTEND;
1469
1470 Specific_Search_Mes(
1471 p_api_version => p_api_version,
1472 p_init_msg_list => p_init_msg_list,
1473 p_validation_level => p_validation_level,
1474 x_return_status => x_return_status,
1475 x_msg_count => x_msg_count,
1476 x_msg_data => x_msg_data,
1477 p_search_string => p_search_string,
1478 p_updated_in_days => p_updated_in_days,
1479 p_check_login_user => p_check_login_user,
1480 p_application_id => p_application_id,
1481 p_area_array => p_area_array,
1482 p_content_array => p_content_array,
1483 p_param_array => p_param_array,
1484 p_user_id => p_user_id,
1485 p_category_id => p_category_id,
1486 p_include_subcats => p_include_subcats,
1487 p_external_contents => p_external_contents,
1488 p_rows_requested => p_rows_requested_tbl(ind),
1489 p_start_row_pos => p_start_row_pos_tbl(ind),
1490 p_get_total_cnt_flag => p_get_total_cnt_flag,
1491 x_rows_returned => x_rows_returned_tbl(ind),
1492 x_next_row_pos => x_next_row_pos_tbl(ind),
1493 x_total_row_cnt => x_total_row_cnt_tbl(ind),
1494 x_result_array => x_result_array,
1495 x_amv_result_array => x_amv_result_array);
1496
1497 Specific_Search_Sms(
1498 p_api_version => p_api_version,
1499 p_init_msg_list => p_init_msg_list,
1500 p_validation_level => p_validation_level,
1501 x_return_status => x_return_status,
1502 x_msg_count => x_msg_count,
1503 x_msg_data => x_msg_data,
1504 p_search_string => p_search_string,
1505 p_updated_in_days => p_updated_in_days,
1506 p_rows_requested => p_rows_requested_tbl(ind),
1507 p_start_row_pos => p_start_row_pos_tbl(ind),
1508 p_get_total_cnt_flag => p_get_total_cnt_flag,
1509 x_rows_returned => x_rows_returned_tbl(ind),
1510 x_next_row_pos => x_next_row_pos_tbl(ind),
1511 x_total_row_cnt => x_total_row_cnt_tbl(ind),
1512 x_result_array => x_result_array,
1513 p_search_option => l_search_option);
1514
1515 end if; --end if all
1516
1517 -- -- -- -- end of code -- -- -- --
1518
1519 -- Standard call to get message count. if count=1, get message info.
1520 FND_MSG_PUB.Count_And_Get(
1521 p_count => x_msg_count,
1522 p_data => x_msg_data );
1523
1524 EXCEPTION
1525 WHEN FND_API.G_EXC_ERROR THEN
1526 ROLLBACK TO Specific_Search_GRP;
1527 x_return_status := FND_API.G_RET_STS_ERROR ;
1528 FND_MSG_PUB.Count_And_Get(
1529 p_count => x_msg_count,
1530 p_data => x_msg_data );
1531 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1532 ROLLBACK TO Specific_Search_GRP;
1533 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1534 FND_MSG_PUB.Count_And_Get(
1535 p_count => x_msg_count,
1536 p_data => x_msg_data);
1537
1538 WHEN OTHERS THEN
1539 ROLLBACK TO Specific_Search_GRP;
1540 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1541 IF FND_MSG_PUB.Check_Msg_Level
1542 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1543 FND_MSG_PUB.Add_Exc_Msg(
1544 G_PKG_NAME,
1545 l_api_name);
1546 END IF;
1547 FND_MSG_PUB.Count_And_Get(
1548 p_count => x_msg_count,
1549 p_data => x_msg_data);
1550
1551
1552 end Specific_Search;
1553
1554 -- Start of comments
1555 -- API name : Purge_Knowledge_Links
1556 -- Type : Group
1557 -- Pre-reqs : None
1558 -- Function : Performs a Purge of Knowledge Links for a given object
1559 -- Parameters :
1560 -- IN p_api_version Required
1561 -- p_init_msg_list Optional - Default False
1562 -- P_COMMIT Optional - Default False
1563 -- P_PROCESSING_SET_ID Required - Processing Set - identifies rows to be purged
1564 -- P_OBJECT_TYPE Required - The Object (FK Jtf_Objects_vl.object_code)
1565 -- OUT : x_return_status
1566 -- x_msg_count
1567 -- x_msg_data
1568 -- Version : Initial version 1.0
1569 -- Notes :
1570 -- End of comments
1571 PROCEDURE Purge_Knowledge_Links (
1572 P_API_VERSION IN NUMBER,
1573 P_INIT_MSG_LIST IN VARCHAR2,
1574 P_COMMIT IN VARCHAR2,
1575 P_PROCESSING_SET_ID IN NUMBER,
1576 P_OBJECT_TYPE IN VARCHAR2,
1577 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1578 X_MSG_COUNT OUT NOCOPY NUMBER,
1579 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
1580
1581 BEGIN
1582 SAVEPOINT Purge_Knowledge_Links_GRP;
1583 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1584
1585 IF FND_API.to_Boolean(P_INIT_MSG_LIST) THEN
1586 FND_MSG_PUB.initialize;
1587 END IF;
1588
1589 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1590 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KNOWLEDGE_GRP.Purge_Knowledge_Links.Start',
1591 'Purge_Knowledge_Links for - '||P_PROCESSING_SET_ID||' '||P_OBJECT_TYPE );
1592 END IF;
1593
1594 IF (P_PROCESSING_SET_ID IS NULL OR P_OBJECT_TYPE IS NULL) THEN
1595 FND_MESSAGE.set_name('CS', 'CS_KB_C_MISS_PARAM');
1596 FND_MSG_PUB.ADD;
1597 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1598 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1599 p_count => X_MSG_COUNT,
1600 p_data => X_MSG_DATA);
1601 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1602
1603 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1604
1605 'cs.plsql.CS_KNOWLEDGE_GRP.Purge_Knowledge_Links.InvalidParams',
1606
1607 'Purge_Knowledge_Links for - '||P_PROCESSING_SET_ID||' '||P_OBJECT_TYPE
1608 );
1609
1610 END IF;
1611 ELSE
1612
1613 DELETE /*+ index(l) */ FROM CS_KB_SET_LINKS l
1614 WHERE l.Object_Code = P_OBJECT_TYPE
1615 AND l.Other_id IN ( SELECT /*+ no_unnest no_semijoin cardinality(10) */ t.Object_id
1616 FROM JTF_OBJECT_PURGE_PARAM_TMP t
1617 WHERE nvl(t.purge_status, 'S') <> 'E'
1618 AND t.Processing_Set_Id = P_PROCESSING_SET_ID
1619 AND t.Object_Type = P_OBJECT_TYPE
1620 );
1621
1622 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1623
1624 IF FND_API.to_Boolean(P_COMMIT) THEN
1625 COMMIT;
1626 END IF;
1627
1628 END IF;
1629
1630 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1631 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KNOWLEDGE_GRP.Purge_Knowledge_Links.End',
1632 'Purge_Knowledge_Links Status - '||X_RETURN_STATUS );
1633 END IF;
1634
1635 EXCEPTION
1636 WHEN OTHERS THEN
1637 ROLLBACK TO Purge_Knowledge_Links_GRP;
1638 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1639
1640 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'cs.plsql.CS_KNOWLEDGE_GRP.Purge_Knowledge_Links.UNEXPECTED',
1641
1642 ' Error= '||sqlerrm);
1643
1644 END IF;
1645
1646 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR ;
1647 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1648 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
1649 'Purge_Knowledge_Links');
1650 END IF;
1651 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE ,
1652 p_count => x_msg_count,
1653 p_data => x_msg_data);
1654 END Purge_Knowledge_Links;
1655
1656
1657 end CS_Knowledge_Grp;