[Home] [Help]
PACKAGE BODY: APPS.IEM_KNOWLEDGEBASE_PUB
Source
1 PACKAGE BODY IEM_KnowledgeBase_PUB as
2 /* $Header: iemvknbb.pls 120.2 2005/12/27 17:27:52 sboorela noship $ */
3
4 G_PKG_NAME CONSTANT varchar2(30) :='IEM_KnowledgeBase_PUB ';
5
6 PROCEDURE Get_SuggResponse (p_api_version_number IN NUMBER,
7 p_init_msg_list IN VARCHAR2 ,
8 p_commit IN VARCHAR2 ,
9 p_EMAIL_ACCOUNT_ID IN NUMBER,
10 p_MESSAGE_ID IN VARCHAR2,
11 p_CLASSIFICATION_ID IN NUMBER,
12 x_return_status OUT NOCOPY VARCHAR2,
13 x_msg_count OUT NOCOPY NUMBER,
14 x_msg_data OUT NOCOPY VARCHAR2,
15 x_Email_SuggResp_tbl OUT NOCOPY EMSGRESP_tbl_type
16 ) IS
17 l_api_name VARCHAR2(255):='Get_SuggResponse';
18 l_api_version_number NUMBER:=1.0;
19 l_index number:=1;
20 l_stat varchar2(100);
21 l_out_text varchar2(1000);
22 cursor kb_results_csr is
23 SELECT DOCUMENT_ID,
24 SCORE,
25 KB_REPOSITORY_NAME,
26 KB_CATEGORY_NAME,
27 DOCUMENT_TITLE,
28 URL,
29 DOC_LAST_MODIFIED_DATE
30 FROM IEM_KB_RESULTS
31 WHERE (EMAIL_ACCOUNT_ID = p_EMAIL_ACCOUNT_ID)
32 AND (MESSAGE_ID = p_MESSAGE_ID)
33 AND classification_id = p_CLASSIFICATION_ID;
34 BEGIN
35 -- Standard Start of API savepoint
36 SAVEPOINT Get_SuggResponse_PUB;
37 -- Standard call to check for call compatibility.
38 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
39 p_api_version_number,
40 l_api_name,
41 G_PKG_NAME)
42 THEN
43 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
44 END IF;
45 -- Initialize message list if p_init_msg_list is set to TRUE.
46 IF FND_API.to_Boolean( p_init_msg_list )
47 THEN
48 FND_MSG_PUB.initialize;
49 END IF;
50 -- Initialize API return status to SUCCESS
51 x_return_status := FND_API.G_RET_STS_SUCCESS;
52 for c_kb_rec in kb_results_csr
53 LOOP
54 x_Email_SuggResp_tbl(l_index).document_id:=c_kb_rec.document_id;
55 x_Email_SuggResp_tbl(l_index).score:=c_kb_rec.score;
56 x_Email_SuggResp_tbl(l_index).kb_repository_name:=c_kb_rec.kb_repository_name;
57 x_Email_SuggResp_tbl(l_index).kb_category_name:=c_kb_rec.kb_category_name;
58 x_Email_SuggResp_tbl(l_index).document_title:=c_kb_rec.document_title;
59 x_Email_SuggResp_tbl(l_index).url:=c_kb_rec.url;
60 x_Email_SuggResp_tbl(l_index).document_last_modified_date:=c_kb_rec.doc_last_modified_date;
61 l_index:=l_index+1;
62 END LOOP;
63 -- Standard Check Of p_commit.
64 IF FND_API.To_Boolean(p_commit) THEN
65 COMMIT WORK;
66 END IF;
67 -- Standard callto get message count and if count is 1, get message info.
68 FND_MSG_PUB.Count_And_Get
69 ( p_count => x_msg_count,
70 p_data => x_msg_data
71 );
72 EXCEPTION
73 WHEN FND_API.G_EXC_ERROR THEN
74 ROLLBACK TO Get_SuggResponse_PUB;
75 x_return_status := FND_API.G_RET_STS_ERROR ;
76 FND_MSG_PUB.Count_And_Get
77 ( p_count => x_msg_count,
78 p_data => x_msg_data
79 );
80 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
81 ROLLBACK TO Get_SuggResponse_PUB;
82 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
83 FND_MSG_PUB.Count_And_Get
84 ( p_count => x_msg_count,
85 p_data => x_msg_data
86 );
87 WHEN OTHERS THEN
88 ROLLBACK TO Get_SuggResponse_PUB;
89 x_return_status := FND_API.G_RET_STS_ERROR;
90 IF FND_MSG_PUB.Check_Msg_Level
91 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
92 THEN
93 FND_MSG_PUB.Add_Exc_Msg
94 ( G_PKG_NAME ,
95 l_api_name
96 );
97 END IF;
98 FND_MSG_PUB.Count_And_Get
99 ( p_count => x_msg_count ,
100 p_data => x_msg_data
101 );
102
103 END Get_SuggResponse;
104
105 PROCEDURE Get_KBCategories (p_api_version_number IN NUMBER,
106 p_init_msg_list IN VARCHAR2 ,
107 p_commit IN VARCHAR2 ,
108 p_EMAIL_ACCOUNT_ID IN NUMBER,
109 p_LEVEL IN NUMBER := 1,
110 x_return_status OUT NOCOPY VARCHAR2,
111 x_msg_count OUT NOCOPY NUMBER,
112 x_msg_data OUT NOCOPY VARCHAR2,
113 x_KB_Cat_tbl OUT NOCOPY KBCAT_tbl_type
114 ) IS
115 CURSOR kb_cat_csr(p_cat_id number) IS
116 SELECT
117 display_name,
118 is_repository,
119 kb_category_id,
120 kb_parent_category_id,
121 category_order
122 FROM IEM_KB_CATEGORIES
123 WHERE LEVEL=p_LEVEL
124 CONNECT BY PRIOR kb_category_id=kb_parent_category_id
125 START WITH kb_category_id= p_cat_id;
126 CURSOR kb_catid_csr is
127 SELECT kb_category_id FROM IEM_EMAIL_CATEGORY_MAPS
128 WHERE EMAIL_ACCOUNT_ID=p_EMAIL_ACCOUNT_ID;
129
130 l_api_name VARCHAR2(255):='Get_KBCategories';
131 l_api_version_number NUMBER:=1.0;
132 l_cat_index number:=1;
133
134 BEGIN
135 --Standard Start of API savepoint
136 SAVEPOINT Get_KBCategories_PUB;
137 -- Standard call to check for call compatibility.
138 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
139 p_api_version_number,
140 l_api_name,
141 G_PKG_NAME)
142 THEN
143 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
144 END IF;
145 -- Initialize message list if p_init_msg_list is set to TRUE.
146 IF FND_API.to_Boolean( p_init_msg_list )
147 THEN
148 FND_MSG_PUB.initialize;
149 END IF;
150 -- Initialize API return status to SUCCESS
151 x_return_status := FND_API.G_RET_STS_SUCCESS;
152 /*
153 -- Populating The pl/sql Table With some dummy Data
154 x_KB_Cat_tbl(1).display_name:='Category Display Name';
155 x_KB_Cat_tbl(1).is_repository:='N';
156 x_KB_Cat_tbl(1).category_id:=1001;
157 x_KB_Cat_tbl(1).parent_cat_id:=1000;
158 x_KB_Cat_tbl(1).category_order:=1;
159 */
160 x_KB_Cat_tbl.Delete;
161 BEGIN
162 FOR l_kb_catid_rec IN kb_catid_csr
163 LOOP
164 OPEN kb_cat_csr(l_kb_catid_rec.kb_category_id);
165 LOOP
166 FETCH kb_cat_csr
167 INTO x_KB_cat_tbl(l_cat_index);
168 EXIT WHEN kb_cat_csr%NOTFOUND;
169 l_cat_index:=l_cat_index+1;
170 END LOOP;
171 CLOSE kb_cat_csr;
172 END LOOP;
173
174 EXCEPTION
175 WHEN OTHERS THEN NULL;
176 END;
177 -- Standard Check Of p_commit.
178 IF FND_API.To_Boolean(p_commit) THEN
179 COMMIT WORK;
180 END IF;
181 -- Standard callto get message count and if count is 1, get message info.
182 FND_MSG_PUB.Count_And_Get
183 ( p_count => x_msg_count,
184 p_data => x_msg_data
185 );
186 EXCEPTION
187 WHEN FND_API.G_EXC_ERROR THEN
188 ROLLBACK TO Get_KBCategories_PUB;
189 x_return_status := FND_API.G_RET_STS_ERROR ;
190 FND_MSG_PUB.Count_And_Get
191 ( p_count => x_msg_count,
192 p_data => x_msg_data
193 );
194 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
195 ROLLBACK TO Get_KBCategories_PUB;
196 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
197 FND_MSG_PUB.Count_And_Get
198 ( p_count => x_msg_count,
199 p_data => x_msg_data
200 );
201 WHEN OTHERS THEN
202 ROLLBACK TO Get_KBCategories_PUB;
203 x_return_status := FND_API.G_RET_STS_ERROR;
204 IF FND_MSG_PUB.Check_Msg_Level
205 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
206 THEN
207 FND_MSG_PUB.Add_Exc_Msg
208 ( G_PKG_NAME ,
209 l_api_name
210 );
211 END IF;
212 FND_MSG_PUB.Count_And_Get
213 ( p_count => x_msg_count ,
214 p_data => x_msg_data
215 );
216
217 END GET_KBCategories;
218
219 PROCEDURE Delete_ResultsCache ( p_api_version_number IN NUMBER,
220 p_init_msg_list IN VARCHAR2 ,
221 p_commit IN VARCHAR2 ,
222 p_EMAIL_ACCOUNT_ID IN NUMBER,
223 p_MESSAGE_ID IN VARCHAR2,
224 x_return_status OUT NOCOPY VARCHAR2,
225 x_msg_count OUT NOCOPY NUMBER,
226 x_msg_data OUT NOCOPY VARCHAR2) IS
227 l_api_name VARCHAR2(255):='Delete_ResultsCache';
228 l_api_version_number NUMBER:=1.0;
229
230 BEGIN
231 --Standard Start of API savepoint
232 SAVEPOINT Delete_ResultsCache_PUB;
233 -- Standard call to check for call compatibility.
234 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
235 p_api_version_number,
236 l_api_name,
237 G_PKG_NAME)
238 THEN
239 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
240 END IF;
241 -- Initialize message list if p_init_msg_list is set to TRUE.
242 IF FND_API.to_Boolean( p_init_msg_list )
243 THEN
244 FND_MSG_PUB.initialize;
245 END IF;
246 -- Initialize API return status to SUCCESS
247 x_return_status := FND_API.G_RET_STS_SUCCESS;
248 DELETE FROM IEM_KB_RESULTS
249 WHERE email_account_id=p_email_account_id
250 and message_id=p_message_id;
251 DELETE FROM IEM_email_classifications
252 WHERE email_account_id=p_email_account_id
253 and message_id=p_message_id;
254
255 -- Standard Check Of p_commit.
256 IF FND_API.To_Boolean(p_commit) THEN
257 COMMIT WORK;
258 END IF;
259 -- Standard callto get message count and if count is 1, get message info.
260 FND_MSG_PUB.Count_And_Get
261 ( p_count => x_msg_count,
262 p_data => x_msg_data
263 );
264 EXCEPTION
265 WHEN FND_API.G_EXC_ERROR THEN
266 ROLLBACK TO Delete_Resultscache_PUB;
267 x_return_status := FND_API.G_RET_STS_ERROR ;
268 FND_MSG_PUB.Count_And_Get
269 ( p_count => x_msg_count,
270 p_data => x_msg_data
271 );
272 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
273 ROLLBACK TO Delete_Resultscache_PUB;
274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
275 FND_MSG_PUB.Count_And_Get
276 ( p_count => x_msg_count,
277 p_data => x_msg_data
278 );
279 WHEN OTHERS THEN
280 ROLLBACK TO Delete_Resultscache_PUB;
281 x_return_status := FND_API.G_RET_STS_ERROR;
282 IF FND_MSG_PUB.Check_Msg_Level
283 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
284 THEN
285 FND_MSG_PUB.Add_Exc_Msg
286 ( G_PKG_NAME ,
287 l_api_name
288 );
289 END IF;
290 FND_MSG_PUB.Count_And_Get
291 ( p_count => x_msg_count ,
292 p_data => x_msg_data
293 );
294 END Delete_ResultsCache ;
295
296 -- THIS API IS NOT BEING CALLED NOW, MAY BE USEFUL LATER Currently
297 --get_suggresponse api is serving the purpose
298
299 PROCEDURE Get_KB_SuggResponse (p_api_version_number IN NUMBER,
300 p_init_msg_list IN VARCHAR2 ,
301 p_commit IN VARCHAR2 ,
302 p_EMAIL_ACCOUNT_ID IN NUMBER,
303 p_MESSAGE_ID IN VARCHAR2,
304 p_CLASSIFICATION_ID IN NUMBER,
305 x_return_status OUT NOCOPY VARCHAR2,
306 x_msg_count OUT NOCOPY NUMBER,
307 x_msg_data OUT NOCOPY VARCHAR2,
308 x_Email_SuggResp_tbl OUT NOCOPY EMSGRESP_tbl_type
309 ) IS
310 l_api_name VARCHAR2(255):='Get_KB_SuggResponse';
311 l_api_version_number NUMBER:=1.0;
312 l_index number:=1;
313 l_stat varchar2(100);
314 l_out_text varchar2(1000);
315 cursor kb_results_csr is
316 SELECT DOCUMENT_ID,
317 SCORE,
318 KB_REPOSITORY_NAME,
319 KB_CATEGORY_NAME,
320 DOCUMENT_TITLE,
321 URL,
322 DOC_LAST_MODIFIED_DATE
323 FROM IEM_KB_RESULTS
324 WHERE (EMAIL_ACCOUNT_ID = p_EMAIL_ACCOUNT_ID)
325 AND (MESSAGE_ID = p_MESSAGE_ID)
326 AND classification_id = p_CLASSIFICATION_ID;
327 BEGIN
328 -- Standard Start of API savepoint
329 SAVEPOINT Get_SuggResponse_PUB;
330 -- Standard call to check for call compatibility.
331 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
332 p_api_version_number,
333 l_api_name,
334 G_PKG_NAME)
335 THEN
336 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
337 END IF;
338 -- Initialize message list if p_init_msg_list is set to TRUE.
339 IF FND_API.to_Boolean( p_init_msg_list )
340 THEN
341 FND_MSG_PUB.initialize;
342 END IF;
343 -- Initialize API return status to SUCCESS
344 x_return_status := FND_API.G_RET_STS_SUCCESS;
345 -- iem_mailpreproc_pub.iem_wf_specificsearch(p_message_id,p_email_account_id,p_classification_id,l_stat,l_out_text);
346 for c_kb_rec in kb_results_csr
347 LOOP
348 x_Email_SuggResp_tbl(l_index).document_id:=c_kb_rec.document_id;
349 x_Email_SuggResp_tbl(l_index).score:=c_kb_rec.score;
350 x_Email_SuggResp_tbl(l_index).kb_repository_name:=c_kb_rec.kb_repository_name;
351 x_Email_SuggResp_tbl(l_index).kb_category_name:=c_kb_rec.kb_category_name;
352 x_Email_SuggResp_tbl(l_index).document_title:=c_kb_rec.document_title;
353 x_Email_SuggResp_tbl(l_index).url:=c_kb_rec.url;
354 x_Email_SuggResp_tbl(l_index).document_last_modified_date:=c_kb_rec.doc_last_modified_date;
355 l_index:=l_index+1;
356 END LOOP;
357 -- Standard Check Of p_commit.
358 IF FND_API.To_Boolean(p_commit) THEN
359 COMMIT WORK;
360 END IF;
361 -- Standard callto get message count and if count is 1, get message info.
362 FND_MSG_PUB.Count_And_Get
363 ( p_count => x_msg_count,
364 p_data => x_msg_data
365 );
366 EXCEPTION
367 WHEN FND_API.G_EXC_ERROR THEN
368 ROLLBACK TO Get_SuggResponse_PUB;
369 x_return_status := FND_API.G_RET_STS_ERROR ;
370 FND_MSG_PUB.Count_And_Get
371 ( p_count => x_msg_count,
372 p_data => x_msg_data
373 );
374 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
375 ROLLBACK TO Get_SuggResponse_PUB;
376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
377 FND_MSG_PUB.Count_And_Get
378 ( p_count => x_msg_count,
379 p_data => x_msg_data
380 );
381 WHEN OTHERS THEN
382 ROLLBACK TO Get_SuggResponse_PUB;
383 x_return_status := FND_API.G_RET_STS_ERROR;
384 IF FND_MSG_PUB.Check_Msg_Level
385 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
386 THEN
387 FND_MSG_PUB.Add_Exc_Msg
388 ( G_PKG_NAME ,
389 l_api_name
390 );
391 END IF;
392 FND_MSG_PUB.Count_And_Get
393 ( p_count => x_msg_count ,
394 p_data => x_msg_data
395 );
396
397 END Get_KB_SuggResponse;
398 -- This API is to be called for 11.5.6 New Flow .
399
400 PROCEDURE Get_SuggResponse_dtl(p_api_version_number IN NUMBER,
401 p_init_msg_list IN VARCHAR2 ,
402 p_commit IN VARCHAR2 ,
403 p_EMAIL_ACCOUNT_ID IN NUMBER,
404 p_MESSAGE_ID IN VARCHAR2,
405 p_CLASSIFICATION_ID IN NUMBER,
406 x_return_status OUT NOCOPY VARCHAR2,
407 x_msg_count OUT NOCOPY NUMBER,
408 x_msg_data OUT NOCOPY VARCHAR2,
409 x_Email_SuggResp_tbl OUT NOCOPY EMSGRESP_tbl_type) IS
410
411 l_api_name VARCHAR2(255):='Get_SuggResponse_dtl';
412 l_api_version_number NUMBER:=1.0;
413 l_index number:=1;
417 l_repos varchar2(100);
414 l_stat varchar2(100);
415 l_out_text varchar2(1000);
416 l_category_id AMV_SEARCH_PVT.amv_number_varray_type:=AMV_SEARCH_PVT.amv_number_varray_type();
418 l_action varchar2(100) ;
419 l_search_type varchar2(100) ;
420 l_doc_count number;
421 l_rule_id number;
422 l_cat_counter number;
423 cursor kb_results_csr is
424 SELECT a.DOCUMENT_ID,(select count(*) from iem_doc_usage_stats where kb_doc_id=a.document_id) rank,
425 a.SCORE,
426 a.KB_REPOSITORY_NAME,
427 a.KB_CATEGORY_NAME,
428 a.DOCUMENT_TITLE,
429 a.URL,
430 a.DOC_LAST_MODIFIED_DATE
431 FROM IEM_KB_RESULTS a
432 WHERE (a.EMAIL_ACCOUNT_ID = p_EMAIL_ACCOUNT_ID)
433 AND (a.MESSAGE_ID = p_MESSAGE_ID)
434 AND a.classification_id = p_CLASSIFICATION_ID
435 order by 2 desc,1 desc;
436 cursor c1 is select parameter1, parameter2 from iem_actions a, iem_action_dtls b
437 where a.emailproc_id=l_rule_id and a.action_id=b.action_id ;
438 l_doc_counter number;
439 BEGIN
440 -- Standard Start of API savepoint
441 SAVEPOINT Get_SuggResponse_PUB;
442 -- Standard call to check for call compatibility.
443 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
444 p_api_version_number,
445 l_api_name,
446 G_PKG_NAME)
447 THEN
448 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
449 END IF;
450 -- Initialize message list if p_init_msg_list is set to TRUE.
451 IF FND_API.to_Boolean( p_init_msg_list )
452 THEN
453 FND_MSG_PUB.initialize;
454 END IF;
455 -- Initialize API return status to SUCCESS
456 x_return_status := FND_API.G_RET_STS_SUCCESS;
457 select count(*) into l_doc_count
458 from iem_kb_results
459 WHERE EMAIL_ACCOUNT_ID = p_EMAIL_ACCOUNT_ID
460 AND MESSAGE_ID = p_MESSAGE_ID
461 AND classification_id = p_CLASSIFICATION_ID;
462 IF l_doc_count=0 then -- No document has retrieved .
463
464 select rule_id
465 into l_rule_id
466 from iem_rt_proc_emails where message_id=p_message_id;
467 BEGIN
468 select action into l_action from
469 iem_actions where emailproc_id=l_rule_id;
470 l_search_type:=substr(l_action,15,length(l_action));
471 if l_search_type='MES' THEN
472 l_repos:='MES';
473 elsif l_search_type='KM' THEN
474 l_repos:='SMS';
475 elsif l_search_type='BOTH' THEN
476 l_repos:='ALL';
477 end if;
478 l_cat_counter:=1;
479 FOR v1 in c1 LOOP
480 IF v1.parameter1 <> to_char(-1) then
481 l_category_id.extend;
482 l_category_id(l_cat_counter):=v1.parameter1;
483 l_cat_counter:=l_cat_counter+1;
484 END IF;
485 END LOOP;
486 EXCEPTION
487 when others then
488 null;
489 end;
490 IEM_EMAIL_PROC_PVT.IEM_WF_SPECIFICSEARCH(
491 p_message_id ,
492 p_email_account_id ,
493 p_classification_id,
494 l_category_id,
495 l_repos,
496 l_stat ,
497 l_out_text);
498 commit;
499 END IF; -- end if for if doc_count=0
500 l_doc_counter:=1;
501 for c_kb_rec in kb_results_csr
502 LOOP
503 x_Email_SuggResp_tbl(l_index).document_id:=c_kb_rec.document_id;
504 x_Email_SuggResp_tbl(l_index).score:=c_kb_rec.score;
505 x_Email_SuggResp_tbl(l_index).kb_repository_name:=c_kb_rec.kb_repository_name;
506 x_Email_SuggResp_tbl(l_index).kb_category_name:=c_kb_rec.kb_category_name;
507 x_Email_SuggResp_tbl(l_index).document_title:=c_kb_rec.document_title;
508 x_Email_SuggResp_tbl(l_index).url:=c_kb_rec.url;
509 x_Email_SuggResp_tbl(l_index).document_last_modified_date:=c_kb_rec.doc_last_modified_date;
510 l_index:=l_index+1;
511 l_doc_counter:=l_doc_counter+1;
512 EXIT when l_doc_counter>10;
513 END LOOP;
514 -- Standard Check Of p_commit.
515 IF FND_API.To_Boolean(p_commit) THEN
516 COMMIT WORK;
517 END IF;
518 -- Standard callto get message count and if count is 1, get message info.
519 FND_MSG_PUB.Count_And_Get
520 ( p_count => x_msg_count,
521 p_data => x_msg_data
522 );
523 EXCEPTION
524 WHEN FND_API.G_EXC_ERROR THEN
525 ROLLBACK TO Get_SuggResponse_PUB;
526 x_return_status := FND_API.G_RET_STS_ERROR ;
527 FND_MSG_PUB.Count_And_Get
528 ( p_count => x_msg_count,
529 p_data => x_msg_data
530 );
531 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
532 ROLLBACK TO Get_SuggResponse_PUB;
533 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
534 FND_MSG_PUB.Count_And_Get
535 ( p_count => x_msg_count,
536 p_data => x_msg_data
537 );
538 WHEN OTHERS THEN
539 ROLLBACK TO Get_SuggResponse_PUB;
540 x_return_status := FND_API.G_RET_STS_ERROR;
541 IF FND_MSG_PUB.Check_Msg_Level
542 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
543 THEN
544 FND_MSG_PUB.Add_Exc_Msg
545 ( G_PKG_NAME ,
546 l_api_name
547 );
548 END IF;
549 FND_MSG_PUB.Count_And_Get
550 ( p_count => x_msg_count ,
551 p_data => x_msg_data
552 );
553
554 END Get_SuggResponse_dtl;
555
556 -- This API is introduced in 11.5.0/MP-R. This will be called for showing alternate suggested response
557 -- Documents.
558 PROCEDURE Get_SuggResponse_dtl(p_api_version_number IN NUMBER,
559 p_init_msg_list IN VARCHAR2 ,
560 p_commit IN VARCHAR2 ,
561 p_EMAIL_ACCOUNT_ID IN NUMBER,
562 p_MESSAGE_ID IN VARCHAR2,
563 x_return_status OUT NOCOPY VARCHAR2,
564 x_msg_count OUT NOCOPY NUMBER,
565 x_msg_data OUT NOCOPY VARCHAR2,
566 x_Email_SuggResp_tbl OUT NOCOPY EMSGRESP_tbl_type
567 ) IS
568 l_api_name VARCHAR2(255):='Get_SuggResponse_dtl';
569 l_api_version_number NUMBER:=1.0;
570 l_index number:=1;
571 l_stat varchar2(100);
572 l_out_text varchar2(1000);
573 cursor kb_results_csr is
574 SELECT DOCUMENT_ID,
575 SCORE,
576 KB_REPOSITORY_NAME,
577 KB_CATEGORY_NAME,
578 DOCUMENT_TITLE,
579 URL,
580 DOC_LAST_MODIFIED_DATE
581 FROM IEM_KB_RESULTS
582 WHERE (EMAIL_ACCOUNT_ID = p_EMAIL_ACCOUNT_ID)
583 AND (MESSAGE_ID = p_MESSAGE_ID)
584 order by score desc;
585 BEGIN
586 -- Standard Start of API savepoint
587 SAVEPOINT Get_SuggResponse_PUB;
588 -- Standard call to check for call compatibility.
589 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
590 p_api_version_number,
591 l_api_name,
592 G_PKG_NAME)
593 THEN
594 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
595 END IF;
596 -- Initialize message list if p_init_msg_list is set to TRUE.
597 IF FND_API.to_Boolean( p_init_msg_list )
598 THEN
599 FND_MSG_PUB.initialize;
600 END IF;
601 -- Initialize API return status to SUCCESS
602 x_return_status := FND_API.G_RET_STS_SUCCESS;
603 for c_kb_rec in kb_results_csr
604 LOOP
605 x_Email_SuggResp_tbl(l_index).document_id:=c_kb_rec.document_id;
606 x_Email_SuggResp_tbl(l_index).score:=c_kb_rec.score;
607 x_Email_SuggResp_tbl(l_index).kb_repository_name:=c_kb_rec.kb_repository_name;
608 x_Email_SuggResp_tbl(l_index).kb_category_name:=c_kb_rec.kb_category_name;
609 x_Email_SuggResp_tbl(l_index).document_title:=c_kb_rec.document_title;
610 x_Email_SuggResp_tbl(l_index).url:=c_kb_rec.url;
611 x_Email_SuggResp_tbl(l_index).document_last_modified_date:=c_kb_rec.doc_last_modified_date;
612 l_index:=l_index+1;
613 END LOOP;
614 -- Standard Check Of p_commit.
615 IF FND_API.To_Boolean(p_commit) THEN
616 COMMIT WORK;
617 END IF;
618 -- Standard callto get message count and if count is 1, get message info.
619 FND_MSG_PUB.Count_And_Get
620 ( p_count => x_msg_count,
621 p_data => x_msg_data
622 );
623 EXCEPTION
624 WHEN FND_API.G_EXC_ERROR THEN
625 ROLLBACK TO Get_SuggResponse_PUB;
626 x_return_status := FND_API.G_RET_STS_ERROR ;
627 FND_MSG_PUB.Count_And_Get
628 ( p_count => x_msg_count,
629 p_data => x_msg_data
630 );
631 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
632 ROLLBACK TO Get_SuggResponse_PUB;
633 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
634 FND_MSG_PUB.Count_And_Get
635 ( p_count => x_msg_count,
636 p_data => x_msg_data
637 );
638 WHEN OTHERS THEN
639 ROLLBACK TO Get_SuggResponse_PUB;
640 x_return_status := FND_API.G_RET_STS_ERROR;
641 IF FND_MSG_PUB.Check_Msg_Level
642 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
643 THEN
644 FND_MSG_PUB.Add_Exc_Msg
645 ( G_PKG_NAME ,
646 l_api_name
647 );
648 END IF;
649 FND_MSG_PUB.Count_And_Get
650 ( p_count => x_msg_count ,
651 p_data => x_msg_data
652 );
653
654 END Get_SuggResponse_dtl;
655 END IEM_KnowledgeBase_PUB ;