[Home] [Help]
PACKAGE BODY: APPS.IEM_MSG_STAT_PUB
Source
1 PACKAGE BODY IEM_MSG_STAT_PUB as
2 /* $Header: iemmsgstatb.pls 115.4 2004/07/21 16:30:02 txliu noship $*/
3
4 -- PACKAGE CONSTANTS NO LITERALS USED.
5 G_PKG_NAME CONSTANT varchar2(30) :='IEM_MSG_STAT_PUB';
6
7
8 PROCEDURE createMSGStat(
9 p_api_version_number IN NUMBER,
10 p_init_msg_list IN VARCHAR2,
11 p_commit IN VARCHAR2,
12 p_outBoundMediaID IN NUMBER,
13 p_inBoundMediaID IN NUMBER,
14 x_return_status OUT NOCOPY VARCHAR2,
15 x_msg_count OUT NOCOPY NUMBER,
16 x_msg_data OUT NOCOPY VARCHAR2
17 ) IS
18
19 l_api_name VARCHAR2(255);
20 l_api_version_number NUMBER;
21 l_created_by NUMBER;
22 l_last_updated_by NUMBER;
23 l_last_update_login NUMBER;
24
25 l_return_status VARCHAR2(300);
26 l_msg_count NUMBER;
27 l_msg_data VARCHAR2(300);
28
29 l_i_sequence NUMBER;
30 l_version NUMBER;
31 l_no VARCHAR2(1);
32
33 BEGIN
34
35
36 -- Standard Start of API savepoint
37 SAVEPOINT createMSGStat_pvt;
38
39 --Init values
40 l_api_name :='createMSGStat';
41 l_api_version_number :=1.0;
42 l_created_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
43 l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
44 l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
45
46 -- Standard call to check for call compatibility.
47 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
48 1.0,
49 l_api_name,
50 G_PKG_NAME)
51 THEN
52 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
53 END IF;
54
55 -- Initialize message list if p_init_msg_list is set to TRUE.
56 IF FND_API.to_Boolean( p_init_msg_list )
57 THEN
58 FND_MSG_PUB.initialize;
59 END IF;
60
61 -- Initialize API return status to SUCCESS
62 x_return_status := FND_API.G_RET_STS_SUCCESS;
63
64 -----------------------Code------------------------
65 select iem_outbound_msg_stats_s1.nextval into l_i_sequence from dual;
66
67 l_no := 'N';
68 insert into IEM_OUTBOUND_MSG_STATS
69 (
70 OUTBOUND_MSG_STATS_ID,
71 MEDIA_ID,
72 INBOUND_MEDIA_ID,
73 USES_SUGGESTIONS_Y_N,
74 AUTO_REPLIED_Y_N,
75 USES_KB_DOCS_Y_N,
76 AGENT_ID,
77 OUTBOUND_METHOD,
78 EMAIL_ACCOUNT_ID,
79 CUSTOMER_ID,
80 CONTACT_ID,
81 DATE_SENT,
82 EXPIRE_Y_N,
83 CREATED_BY,
84 CREATION_DATE,
85 LAST_UPDATED_BY,
86 LAST_UPDATE_DATE,
87 LAST_UPDATE_LOGIN
88 )
89 values
90 (
91 l_i_sequence,
92 p_outBoundMediaID,
93 p_inBoundMediaID,
94 l_no,
95 l_no,
96 l_no,
97 -1,
98 0,
99 -1,
100 -1,
101 -1,
102 SYSDATE,
103 l_no,
104 l_created_by,
105 SYSDATE,
106 l_last_updated_by,
107 SYSDATE,
108 l_last_update_login
109 );
110
111 -------------------End Code------------------------
112 -- Standard Check Of p_commit.
113 IF FND_API.To_Boolean(p_commit) THEN
114 COMMIT WORK;
115 END IF;
116
117 -- Standard callto get message count and if count is 1, get message info.
118 FND_MSG_PUB.Count_And_Get
119 ( p_count => x_msg_count,
120 p_data => x_msg_data
121 );
122 EXCEPTION
123 WHEN FND_API.G_EXC_ERROR THEN
124 ROLLBACK TO createMSGStat_pvt;
125 x_return_status := FND_API.G_RET_STS_ERROR ;
126 FND_MSG_PUB.Count_And_Get(
127 p_count => x_msg_count,
128 p_data => x_msg_data);
129
130 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
131 ROLLBACK TO createMSGStat_pvt;
132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
133 FND_MSG_PUB.Count_And_Get(
134 p_count => x_msg_count,
135 p_data => x_msg_data);
136 WHEN OTHERS THEN
137 ROLLBACK TO createMSGStat_pvt;
138 x_return_status := FND_API.G_RET_STS_ERROR;
139 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
140 THEN
141 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
142 END IF;
143 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
144 p_data => x_msg_data);
145
146 END createMSGStat;
147
148 PROCEDURE sendMSGStat(
149 p_api_version_number IN NUMBER,
150 p_init_msg_list IN VARCHAR2,
151 p_commit IN VARCHAR2,
152 p_outBoundMediaID IN NUMBER,
153 p_inBoundMediaID IN NUMBER,
154 p_autoReplied IN VARCHAR2,
155 p_agentID IN NUMBER,
156 p_outBoundMethod IN NUMBER,
157 p_accountID IN NUMBER,
158 p_customerID IN NUMBER,
159 p_contactID IN NUMBER,
160 x_return_status OUT NOCOPY VARCHAR2,
161 x_msg_count OUT NOCOPY NUMBER,
162 x_msg_data OUT NOCOPY VARCHAR2
163 ) IS
164
165 l_api_name VARCHAR2(255);
166 l_api_version_number NUMBER;
167 l_last_updated_by NUMBER;
168 l_last_update_login NUMBER;
169
170 l_return_status VARCHAR2(300);
171 l_msg_count NUMBER;
172 l_msg_data VARCHAR2(300);
173 l_outbound_msg_stats_id NUMBER;
174 l_outbound_stats_count NUMBER;
175 l_outBoundMethod NUMBER;
176 l_no VARCHAR2(1);
177
178 BEGIN
179
180 -- Standard Start of API savepoint
181 SAVEPOINT sendMSGStat_pvt;
182
183 -- Init values
184 l_api_name :='sendMSGStat';
185 l_api_version_number :=1.0;
186 l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
187 l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
188
189 -- Standard call to check for call compatibility.
190 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
191 1.0,
192 l_api_name,
193 G_PKG_NAME)
194 THEN
195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
196 END IF;
197
198 -- Initialize message list if p_init_msg_list is set to TRUE.
199 IF FND_API.to_Boolean( p_init_msg_list )
200 THEN
201 FND_MSG_PUB.initialize;
202 END IF;
203
204 -- Initialize API return status to SUCCESS
205 x_return_status := FND_API.G_RET_STS_SUCCESS;
206
207 -----------------------Code------------------------
208 l_outBoundMethod := p_outBoundMethod;
209 BEGIN
210 select OUTBOUND_MSG_STATS_ID into l_outbound_msg_stats_id from IEM_OUTBOUND_MSG_STATS where
211 MEDIA_ID = p_outBoundMediaID and INBOUND_MEDIA_ID = p_inBoundMediaID;
212
213 select count(DOC_USAGE_STATS_ID) into l_outbound_stats_count from IEM_DOC_USAGE_STATS where
214 OUTBOUND_MSG_STATS_ID = l_outbound_msg_stats_id;
215 EXCEPTION
216 WHEN OTHERS THEN
217 NULL;
218 END;
219
220 IF (l_outbound_stats_count > 0 and p_outBoundMethod = 1003)
221 THEN
222 l_outBoundMethod := 1002;
223 END IF;
224
225 l_no := 'N';
226 IF (p_outBoundMediaID > 0)
227 THEN
228 UPDATE IEM_OUTBOUND_MSG_STATS SET
229 AUTO_REPLIED_Y_N = l_no,
230 AGENT_ID = p_agentID,
231 OUTBOUND_METHOD = p_outBoundMethod,
232 EMAIL_ACCOUNT_ID = p_accountID,
233 CUSTOMER_ID = p_customerID,
234 CONTACT_ID = p_contactID,
235 DATE_SENT = SYSDATE,
236 LAST_UPDATED_BY = l_last_updated_by,
237 LAST_UPDATE_DATE = SYSDATE,
238 LAST_UPDATE_LOGIN = l_last_update_login
239 WHERE MEDIA_ID = p_outBoundMediaID;
240 ELSE
241 UPDATE IEM_OUTBOUND_MSG_STATS SET
242 AUTO_REPLIED_Y_N = l_no,
243 AGENT_ID = p_agentID,
244 OUTBOUND_METHOD = p_outBoundMethod,
245 EMAIL_ACCOUNT_ID = p_accountID,
246 CUSTOMER_ID = p_customerID,
247 CONTACT_ID = p_contactID,
248 DATE_SENT = SYSDATE,
249 LAST_UPDATED_BY = l_last_updated_by,
250 LAST_UPDATE_DATE = SYSDATE,
251 LAST_UPDATE_LOGIN = l_last_update_login
252 WHERE INBOUND_MEDIA_ID = p_inBoundMediaID;
253
254 END IF;
255
256
257 l_no := 'Y';
258 UPDATE IEM_DOC_USAGE_STATS SET
259 DATE_SENT = SYSDATE,
260 SAVED_Y_N = l_no,
261 LAST_UPDATED_BY = l_last_updated_by,
262 LAST_UPDATE_DATE = SYSDATE,
263 LAST_UPDATE_LOGIN = l_last_update_login
264 WHERE OUTBOUND_MSG_STATS_ID = l_outbound_msg_stats_id;
265 -------------------End Code------------------------
266 -- Standard Check Of p_commit.
267 IF FND_API.To_Boolean(p_commit) THEN
268 COMMIT WORK;
269 END IF;
270
271 EXCEPTION
272 WHEN FND_API.G_EXC_ERROR THEN
273 ROLLBACK TO sendMSGStat_pvt;
274 x_return_status := FND_API.G_RET_STS_ERROR ;
275 FND_MSG_PUB.Count_And_Get(
276 p_count => x_msg_count,
277 p_data => x_msg_data);
278
279 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
280 ROLLBACK TO sendMSGStat_pvt;
281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
282 FND_MSG_PUB.Count_And_Get(
283 p_count => x_msg_count,
284 p_data => x_msg_data);
285
286 WHEN OTHERS THEN
287 ROLLBACK TO sendMSGStat_pvt;
288 x_return_status := FND_API.G_RET_STS_ERROR;
289 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
290 THEN
291 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
292 END IF;
293 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
294 p_data => x_msg_data);
295
296 END sendMSGStat;
297
298 PROCEDURE deleteMSGStat(
299 p_api_version_number IN NUMBER,
300 p_init_msg_list IN VARCHAR2,
301 p_commit IN VARCHAR2,
302 p_outBoundMediaID IN NUMBER,
303 p_inBoundMediaID IN NUMBER,
304 x_return_status OUT NOCOPY VARCHAR2,
305 x_msg_count OUT NOCOPY NUMBER,
306 x_msg_data OUT NOCOPY VARCHAR2
307 ) IS
308
309 l_api_name VARCHAR2(255);
310 l_api_version_number NUMBER;
311 l_last_updated_by NUMBER;
312 l_last_update_login NUMBER;
313
314 l_return_status VARCHAR2(300);
315 l_msg_count NUMBER;
316 l_msg_data VARCHAR2(300);
317
318 l_status VARCHAR2(1);
319 l_outbound_msg_stats_id NUMBER;
320
321 BEGIN
322
323 -- Standard Start of API savepoint
324 SAVEPOINT deleteMSGStat_pvt;
325
326 -- Init values
327 l_api_name :='deleteMSGStat';
328 l_api_version_number :=1.0;
329 l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
330 l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
331
332 -- Standard call to check for call compatibility.
333 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
334 1.0,
335 l_api_name,
336 G_PKG_NAME)
337 THEN
338 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
339 END IF;
340
341 -- Initialize message list if p_init_msg_list is set to TRUE.
342 IF FND_API.to_Boolean( p_init_msg_list )
343 THEN
344 FND_MSG_PUB.initialize;
345 END IF;
346
347 -- Initialize API return status to SUCCESS
351 BEGIN
348 x_return_status := FND_API.G_RET_STS_SUCCESS;
349
350 -----------------------Code------------------------
352 IF (p_outBoundMediaID > 0) THEN
353 SELECT OUTBOUND_MSG_STATS_ID INTO l_outbound_msg_stats_id
354 FROM IEM_OUTBOUND_MSG_STATS WHERE MEDIA_ID = p_outBoundMediaID;
355 ELSE
356 SELECT OUTBOUND_MSG_STATS_ID INTO l_outbound_msg_stats_id
357 FROM IEM_OUTBOUND_MSG_STATS WHERE INBOUND_MEDIA_ID = p_inBoundMediaID;
358 END IF;
359 EXCEPTION
360 WHEN OTHERS THEN
361 NULL;
362 END;
363
364 DELETE FROM IEM_DOC_USAGE_STATS WHERE OUTBOUND_MSG_STATS_ID = l_outbound_msg_stats_id;
365 DELETE FROM IEM_OUTBOUND_MSG_STATS WHERE OUTBOUND_MSG_STATS_ID = l_outbound_msg_stats_id;
366
367 -------------------End Code------------------------
368 -- Standard Check Of p_commit.
369 IF FND_API.To_Boolean(p_commit) THEN
370 COMMIT WORK;
371 END IF;
372
373 EXCEPTION
374 WHEN FND_API.G_EXC_ERROR THEN
375 ROLLBACK TO deleteMSGStat_pvt;
376 x_return_status := FND_API.G_RET_STS_ERROR ;
377 FND_MSG_PUB.Count_And_Get(
378 p_count => x_msg_count,
379 p_data => x_msg_data);
380 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
381 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
382 x_msg_data := x_msg_data || ',' || l_msg_data;
383 END LOOP;
384
385 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
386 ROLLBACK TO deleteMSGStat_pvt;
387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
388 FND_MSG_PUB.Count_And_Get(
389 p_count => x_msg_count,
390 p_data => x_msg_data);
391 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
392 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
393 x_msg_data := x_msg_data || ',' || l_msg_data;
394 END LOOP;
395
396 WHEN OTHERS THEN
397 ROLLBACK TO deleteMSGStat_pvt;
398 x_return_status := FND_API.G_RET_STS_ERROR;
399 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
400 THEN
401 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
402 END IF;
403 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
404 p_data => x_msg_data);
405 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
406 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
407 x_msg_data := x_msg_data || ',' || l_msg_data;
408 END LOOP;
409
410 END deleteMSGStat;
411
412
413 PROCEDURE cancelMSGStat(
414 p_api_version_number IN NUMBER,
415 p_init_msg_list IN VARCHAR2,
416 p_commit IN VARCHAR2,
417 p_outBoundMediaID IN NUMBER,
418 p_inBoundMediaID IN NUMBER,
419 x_return_status OUT NOCOPY VARCHAR2,
420 x_msg_count OUT NOCOPY NUMBER,
421 x_msg_data OUT NOCOPY VARCHAR2
422 ) IS
423
424 l_api_name VARCHAR2(255);
425 l_api_version_number NUMBER;
426 l_last_updated_by NUMBER;
427 l_last_update_login NUMBER;
428
429 l_outbound_msg_stats_id NUMBER;
430 l_return_status VARCHAR2(300);
431 l_msg_count NUMBER;
432 l_msg_data VARCHAR2(300);
433 l_yes VARCHAR2(1);
434
435 BEGIN
436
437 -- Standard Start of API savepoint
438 SAVEPOINT cancelMSGStat_pvt;
439
440 -- Init values
441 l_api_name :='cancelMSGStat';
442 l_api_version_number :=1.0;
443 l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
444 l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
445 l_yes := 'Y';
446
447 -- Standard call to check for call compatibility.
448 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
449 1.0,
450 l_api_name,
451 G_PKG_NAME)
452 THEN
453 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
454 END IF;
455
456 -- Initialize message list if p_init_msg_list is set to TRUE.
457 IF FND_API.to_Boolean( p_init_msg_list )
458 THEN
459 FND_MSG_PUB.initialize;
460 END IF;
461
462 -- Initialize API return status to SUCCESS
463 x_return_status := FND_API.G_RET_STS_SUCCESS;
464
465 -----------------------Code------------------------
466
467 BEGIN
468 IF (p_outBoundMediaID > 0) THEN
469 SELECT OUTBOUND_MSG_STATS_ID INTO l_outbound_msg_stats_id
470 FROM IEM_OUTBOUND_MSG_STATS WHERE MEDIA_ID = p_outBoundMediaID;
471 ELSE
472 SELECT OUTBOUND_MSG_STATS_ID INTO l_outbound_msg_stats_id
473 FROM IEM_OUTBOUND_MSG_STATS WHERE INBOUND_MEDIA_ID = p_inBoundMediaID;
474 END IF;
475 EXCEPTION
476 WHEN OTHERS THEN
477 NULL;
478 END;
479
480 DELETE FROM IEM_DOC_USAGE_STATS WHERE OUTBOUND_MSG_STATS_ID = l_outbound_msg_stats_id
481 AND SAVED_Y_N <> l_yes;
482
483 -------------------End Code------------------------
484 -- Standard Check Of p_commit.
485 IF FND_API.To_Boolean(p_commit) THEN
486 COMMIT WORK;
487 END IF;
488
489 EXCEPTION
490 WHEN FND_API.G_EXC_ERROR THEN
491 ROLLBACK TO cancelMSGStat_pvt;
492 x_return_status := FND_API.G_RET_STS_ERROR ;
493 FND_MSG_PUB.Count_And_Get(
494 p_count => x_msg_count,
495 p_data => x_msg_data);
496
500 FND_MSG_PUB.Count_And_Get(
497 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
498 ROLLBACK TO cancelMSGStat_pvt;
499 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
501 p_count => x_msg_count,
502 p_data => x_msg_data);
503
504 WHEN OTHERS THEN
505 ROLLBACK TO cancelMSGStat_pvt;
506 x_return_status := FND_API.G_RET_STS_ERROR;
507 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
508 THEN
509 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
510 END IF;
511 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
512 p_data => x_msg_data);
513
514 END cancelMSGStat;
515
516 PROCEDURE saveMSGStat(
517 p_api_version_number IN NUMBER,
518 p_init_msg_list IN VARCHAR2,
519 p_commit IN VARCHAR2,
520 p_outBoundMediaID IN NUMBER,
521 p_inBoundMediaID IN NUMBER,
522 x_return_status OUT NOCOPY VARCHAR2,
523 x_msg_count OUT NOCOPY NUMBER,
524 x_msg_data OUT NOCOPY VARCHAR2
525 ) IS
526 l_api_name VARCHAR2(255);
527 l_api_version_number NUMBER;
528 l_created_by NUMBER;
529 l_last_updated_by NUMBER;
530 l_last_update_login NUMBER;
531
532 l_return_status VARCHAR2(300);
533 l_msg_count NUMBER;
534 l_msg_data VARCHAR2(300);
535
536 l_outbound_msg_stats_id NUMBER;
537
538 BEGIN
539
540 -- Standard Start of API savepoint
541 SAVEPOINT saveMSGStat_pvt;
542
543 -- Init values
544 l_api_name :='saveMSGStat';
545 l_api_version_number :=1.0;
546 l_created_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
547 l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
548 l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
549
550 -- Standard call to check for call compatibility.
551 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
552 1.0,
553 l_api_name,
554 G_PKG_NAME)
555 THEN
556 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
557 END IF;
558
559 -- Initialize message list if p_init_msg_list is set to TRUE.
560 IF FND_API.to_Boolean( p_init_msg_list )
561 THEN
562 FND_MSG_PUB.initialize;
563 END IF;
564
565 -- Initialize API return status to SUCCESS
566 x_return_status := FND_API.G_RET_STS_SUCCESS;
567
568 -----------------------Code------------------------
569 BEGIN
570 IF (p_outBoundMediaID > 0) THEN
571 SELECT OUTBOUND_MSG_STATS_ID INTO l_outbound_msg_stats_id
572 FROM IEM_OUTBOUND_MSG_STATS WHERE MEDIA_ID = p_outBoundMediaID;
573 ELSE
574 SELECT OUTBOUND_MSG_STATS_ID INTO l_outbound_msg_stats_id
575 FROM IEM_OUTBOUND_MSG_STATS WHERE INBOUND_MEDIA_ID = p_inBoundMediaID;
576 END IF;
577 EXCEPTION
578 WHEN OTHERS THEN
579 NULL;
580 END;
581
582 UPDATE IEM_DOC_USAGE_STATS SET
583 SAVED_Y_N = 'Y',
584 DATE_SENT = SYSDATE,
585 LAST_UPDATED_BY = l_last_updated_by,
586 LAST_UPDATE_DATE = SYSDATE,
587 LAST_UPDATE_LOGIN = l_last_update_login
588 WHERE OUTBOUND_MSG_STATS_ID = l_outbound_msg_stats_id;
589
590 -------------------End Code------------------------
591 -- Standard Check Of p_commit.
592 IF FND_API.To_Boolean(p_commit) THEN
593 COMMIT WORK;
594 END IF;
595
596 -- Standard callto get message count and if count is 1, get message info.
597 FND_MSG_PUB.Count_And_Get
598 ( p_count => x_msg_count,
599 p_data => x_msg_data
600 );
601 EXCEPTION
602 WHEN FND_API.G_EXC_ERROR THEN
603 ROLLBACK TO saveMSGStat_pvt;
604 x_return_status := FND_API.G_RET_STS_ERROR ;
605 FND_MSG_PUB.Count_And_Get(
606 p_count => x_msg_count,
607 p_data => x_msg_data);
608
609 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
610 ROLLBACK TO saveMSGStat_pvt;
611 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
612 FND_MSG_PUB.Count_And_Get(
613 p_count => x_msg_count,
614 p_data => x_msg_data);
615
616 WHEN OTHERS THEN
617 ROLLBACK TO saveMSGStat_pvt;
618 x_return_status := FND_API.G_RET_STS_ERROR;
619 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
620 THEN
621 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
622 END IF;
623 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
624 p_data => x_msg_data);
625
626 END saveMSGStat;
627
628 PROCEDURE insertDocUsageStat
629 (p_api_version_number IN NUMBER,
630 p_init_msg_list IN VARCHAR2,
631 p_commit IN VARCHAR2,
632 p_rt_mediaID IN NUMBER,
633 p_reply_y_n IN VARCHAR2,
634 p_kb_doc_ID IN NUMBER,
635 p_template_y_n IN VARCHAR2,
636 p_repository IN VARCHAR2,
637 p_mes_category_ID IN NUMBER,
638 p_inserted_y_n IN VARCHAR2,
639 p_top_ranked_intent IN VARCHAR2,
640 p_top_ranked_intent_ID IN NUMBER,
641 p_suggested_y_n IN VARCHAR2,
642 p_in_top_intent_y_n IN VARCHAR2,
643 p_intent IN VARCHAR2,
647 p_document_rank IN NUMBER,
644 p_intent_ID IN NUMBER,
645 p_intent_score IN NUMBER,
646 p_intent_rank IN NUMBER,
648 p_document_score IN NUMBER,
649 p_email_account_ID IN NUMBER,
650 p_auto_insert_y_n IN VARCHAR2,
651 x_return_status OUT NOCOPY VARCHAR2,
652 x_msg_count OUT NOCOPY NUMBER,
653 x_msg_data OUT NOCOPY VARCHAR2
654 ) AS
655
656 l_msg_count NUMBER(2);
657 l_msg_data VARCHAR2(2000);
658 l_sequence NUMBER;
659
660 l_api_name VARCHAR2(30);
661 l_api_version_number NUMBER;
662 l_created_by NUMBER;
663 l_last_updated_by NUMBER;
664 l_last_update_login NUMBER;
665
666 l_outbound_media_ID NUMBER := 0;
667 l_inbound_media_ID NUMBER := 0;
668 l_rt_interaction_id NUMBER;
669
670 l_outbound_msg_stats_id NUMBER;
671 l_uses_kb_docs_y_n VARCHAR2(1);
672 l_email_type VARCHAR2(1);
673 l_no VARCHAR2(1);
674
675 BEGIN
676
677 -- Standard Start of API savepoint
678 SAVEPOINT insertDocUsageStat_pvt;
679 -- Init values
680 l_api_name := 'insertDocUsageStat';
681 l_api_version_number := 1.0;
682 l_created_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
683 l_last_updated_by :=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
684 l_last_update_login := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
685 l_uses_kb_docs_y_n := 'N';
686
687 -- Standard call to check for call compatibility.
688 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
689 1.0,
690 l_api_name,
691 G_PKG_NAME)
692 THEN
693 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
694 END IF;
695
696 -- Initialize message list if p_init_msg_list is set to TRUE.
697 IF FND_API.to_Boolean( p_init_msg_list )
698 THEN
699 FND_MSG_PUB.initialize;
700 END IF;
701
702 -- Initialize API return status to SUCCESS
703 x_return_status := FND_API.G_RET_STS_SUCCESS;
704
705 -----------------------Code------------------------
706 BEGIN
707 select rt_interaction_id into l_rt_interaction_id from iem_rt_media_items
708 where rt_media_item_id = p_rt_mediaID;
709
710 l_email_type := 'O';
711 select media_id into l_outbound_media_ID from iem_rt_media_items where email_type=l_email_type and
712 rt_interaction_id = l_rt_interaction_id;
713 EXCEPTION
714 WHEN OTHERS THEN
715 NULL;
716 END;
717
718 BEGIN
719
720 l_email_type := 'I';
721 select media_id into l_inbound_media_ID from iem_rt_media_items where email_type=l_email_type and
722 rt_interaction_id = l_rt_interaction_id;
723 EXCEPTION
724 WHEN OTHERS THEN
725 NULL;
726 END;
727
728 IF (l_outbound_media_ID > 0) THEN
729 select OUTBOUND_MSG_STATS_ID into l_outbound_msg_stats_id
730 from IEM_OUTBOUND_MSG_STATS where MEDIA_ID = l_outbound_media_ID;
731 ELSE
732 select OUTBOUND_MSG_STATS_ID into l_outbound_msg_stats_id
733 from IEM_OUTBOUND_MSG_STATS where INBOUND_MEDIA_ID = l_inbound_media_ID;
734 END IF;
735
736 select iem_doc_usage_stats_s1.nextval into l_sequence from dual;
737 l_no := 'N';
738 insert into IEM_DOC_USAGE_STATS
739 (
740 DOC_USAGE_STATS_ID,
741 OUTBOUND_MSG_STATS_ID,
742 REPLY_Y_N,
743 KB_DOC_ID,
744 TEMPLATE_Y_N,
745 REPOSITORY,
746 MES_CATEGORY_ID,
747 INSERTED_Y_N,
748 TOP_RANKED_INTENT,
749 TOP_RANKED_INTENT_ID,
750 SUGGESTED_Y_N,
751 IN_TOP_INTENT_Y_N,
752 INTENT,
753 INTENT_ID,
754 INTENT_SCORE,
755 INTENT_RANK,
756 DOCUMENT_RANK,
757 DOCUMENT_SCORE,
758 DATE_INSERTED,
759 SAVED_Y_N,
760 EMAIL_ACCOUNT_ID,
761 AUTO_INSERT_Y_N,
762 CREATED_BY,
763 CREATION_DATE,
764 LAST_UPDATED_BY,
765 LAST_UPDATE_DATE,
766 LAST_UPDATE_LOGIN
767 )
768 values
769 (
770 l_sequence,
771 l_outbound_msg_stats_id,
772 p_reply_y_n,
773 p_kb_doc_ID,
774 p_template_y_n,
775 p_repository,
776 p_mes_category_ID,
777 p_inserted_y_n,
778 p_top_ranked_intent,
779 p_top_ranked_intent_ID,
780 p_suggested_y_n,
781 p_in_top_intent_y_n,
782 p_intent,
783 p_intent_ID,
784 p_intent_score,
785 p_intent_rank,
786 p_document_rank,
787 p_document_score,
788 SYSDATE,
789 l_no,
790 p_email_account_ID,
791 p_auto_insert_y_n,
792 l_created_by,
793 SYSDATE,
794 l_last_updated_by,
795 SYSDATE,
796 l_last_update_login
797 );
798
799 IF (p_kb_doc_ID > 0) THEN
800 l_uses_kb_docs_y_n := 'Y';
801 END IF;
802
803 UPDATE IEM_OUTBOUND_MSG_STATS SET
804 USES_SUGGESTIONS_Y_N = p_suggested_y_n,
805 USES_KB_DOCS_Y_N = l_uses_kb_docs_y_n,
806 LAST_UPDATED_BY = l_last_updated_by,
807 LAST_UPDATE_DATE = SYSDATE,
808 LAST_UPDATE_LOGIN = l_last_update_login
809 WHERE OUTBOUND_MSG_STATS_ID = l_outbound_msg_stats_id;
810 -------------------End Code------------------------
811 -- Standard Check Of p_commit.
812 IF FND_API.To_Boolean(p_commit) THEN
813 COMMIT WORK;
814 END IF;
815
816 -- Standard callto get message count and if count is 1, get message info.
817 FND_MSG_PUB.Count_And_Get
818 ( p_count => x_msg_count,
819 p_data => x_msg_data
820 );
821 EXCEPTION
822 WHEN FND_API.G_EXC_ERROR THEN
823 ROLLBACK TO insertDocUsageStat_pvt;
824 x_return_status := FND_API.G_RET_STS_ERROR ;
825 FND_MSG_PUB.Count_And_Get(
826 p_count => x_msg_count,
827 p_data => x_msg_data);
828
829 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
830 ROLLBACK TO insertDocUsageStat_pvt;
831 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
832 FND_MSG_PUB.Count_And_Get(
833 p_count => x_msg_count,
834 p_data => x_msg_data);
835
836 WHEN OTHERS THEN
837 ROLLBACK TO insertDocUsageStat_pvt;
838 x_return_status := FND_API.G_RET_STS_ERROR;
839 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
840 THEN
841 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
842 END IF;
843 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
844 p_data => x_msg_data);
845
846 END insertDocUsageStat;
847
848 END IEM_MSG_STAT_PUB;