[Home] [Help]
PACKAGE BODY: APPS.IEM_MAILITEM_PUB
Source
1 package body IEM_MAILITEM_PUB as
2 /* $Header: iemclntb.pls 120.4 2007/11/07 20:20:01 kgscott ship $*/
3 G_PKG_NAME varchar2(100):='IEM_MAILITEM_PUB';
4 PROCEDURE GetMailItemCount (p_api_version_number IN NUMBER,
5 p_init_msg_list IN VARCHAR2 ,
6 p_commit IN VARCHAR2 ,
7 p_resource_id in number,
8 p_tbl in t_number_table:=NULL,
9 x_email_count out NOCOPY email_count_tbl,
10 x_return_status OUT NOCOPY VARCHAR2,
11 x_msg_count OUT NOCOPY NUMBER,
12 x_msg_data OUT NOCOPY VARCHAR2)
13
14 IS
15 l_tbl t_number_table:=t_number_table();
16 i_tbl jtf_number_table:=jtf_number_table();
17 CURSOR c1 IS
18 SELECT a.EMAIL_ACCOUNT_ID,a.RT_CLASSIFICATION_ID,
19 b.USER_NAME,c.name,count(*) Total,
20 nvl(max(sysdate-a.received_date)*24*60,0) wait_time
21 FROM iem_rt_proc_emails a,iem_mstemail_accounts b,
22 iem_route_classifications c,iem_agents d
23 WHERE a.resource_id=0
24 and a.email_account_id=b.email_account_id
25 and a.rt_classification_id=c.route_classification_id
26 AND a.email_account_id=d.email_account_id
27 AND d.resource_id=p_resource_id
28 AND a.group_id in (select * from TABLE(cast(i_tbl as jtf_number_table)))
29 and a.message_id not in (select message_id from iem_reroute_hists where agent_id=p_resource_id)
30 GROUP by a.email_account_id,a.rt_classification_id,b.USER_NAME,c.name;
31 CURSOR c_11 IS
32 SELECT a.EMAIL_ACCOUNT_ID,a.RT_CLASSIFICATION_ID,
33 b.USER_NAME,c.name,count(*) Total,
34 nvl(max(sysdate-a.received_date)*24*60,0) wait_time
35 FROM iem_rt_proc_emails a,iem_mstemail_accounts b,
36 iem_route_classifications c,iem_agents d
37 WHERE a.resource_id=0
38 and a.email_account_id=b.email_account_id
39 and a.rt_classification_id=c.route_classification_id
40 AND a.email_account_id=d.email_account_id
41 AND d.resource_id=p_resource_id
42 AND (a.group_id in (select group_id from jtf_rs_group_members where resource_id=p_resource_id
43 and delete_flag<>'Y')
44 or (a.group_id=0))
45 and a.message_id not in (select message_id from iem_reroute_hists where agent_id=p_resource_id)
46 GROUP by a.email_account_id,a.rt_classification_id,b.USER_NAME,c.name;
47 Cursor c2 IS
48 select a.email_account_id,a.rt_classification_id,
49 b.USER_NAME,c.name,Count(*) Total,
50 nvl(max(sysdate-a.received_date)*24*60,0) wait_time,
51 max(decode(a.mail_item_status,'A',1,'N',1,'T',1,0)) email_status
52 FROM iem_rt_proc_emails a,iem_mstemail_accounts b,
53 iem_route_classifications c
54 WHERE a.resource_id=p_resource_id
55 and a.email_account_id=b.email_account_id
56 and a.rt_classification_id=c.route_classification_id
57 and a.queue_status is null
58 GROUP by a.email_account_id,a.rt_classification_id,b.USER_NAME,c.name;
59
60 l_email_account_id number;
61 l_rt_classification_id number;
62 l_where varchar2(500);
63 l_index number:=1;
64 l_api_version_number number:=1.0;
65 l_api_name varchar2(30):='GetMailItemCount';
66 x_act_tbl t_number_table:=t_number_table() ;
67 x_rt_class_tbl t_number_table:=t_number_table() ;
68 x_rt_class_name_tbl jtf_varchar2_table_100:=jtf_varchar2_table_100() ;
69 x_acct_name_tbl jtf_varchar2_table_100:=jtf_varchar2_table_100() ;
70 x_count t_number_table:=t_number_table() ;
71 x_wait_time t_number_table:=t_number_table() ;
72 l_ret_status varchar2(10);
73 l_msg_count number;
74 l_msg_data varchar2(500);
75 l_acq_wait number;
76 l_match number:=0;
77 l_acq_count number;
78 l_count number;
79 IEM_NO_DATA EXCEPTION;
80 NOT_A_VALID_AGENT EXCEPTION;
81 BEGIN
82 -- Standard call to check for call compatibility.
83 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
84 p_api_version_number,
85 l_api_name,
86 G_PKG_NAME)
87 THEN
88 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
89 END IF;
90 SAVEPOINT select_mail_count_pvt;
91 x_return_status := FND_API.G_RET_STS_SUCCESS;
92
93 select count(*) into l_count
94 from jtf_rs_group_members
95 where resource_id=p_resource_id
96 and delete_flag<>'Y';
97 IF l_count>0 then -- It is a valid agent should get queue message if any
98 IF p_tbl is null then
99 open c_11;
100 fetch c_11 bulk collect into x_act_tbl,x_rt_class_tbl,x_acct_name_tbl,x_rt_class_name_tbl,x_count,x_wait_time;
101 close c_11;
102 ELSE
103 l_tbl:=p_tbl;
104 FOR j in l_tbl.FIRST..l_tbl.LAST LOOP
105 i_tbl.extend;
106 i_tbl(j):=l_tbl(j);
107 END LOOP;
108 open c1;
109 fetch c1 bulk collect into x_act_tbl,x_rt_class_tbl,x_acct_name_tbl,x_rt_class_name_tbl,x_count,x_wait_time;
110 close c1;
111 END IF;
112 END IF; -- End of It is a valid agent should get queue message if any
113 IF x_act_tbl.count>0 THEN
114 for l_index in x_act_tbl.FIRST..x_act_tbl.LAST LOOP
115 x_email_count(l_index).email_account_id:=x_act_tbl(l_index);
116 x_email_count(l_index).rt_classification_id:=x_rt_class_tbl(l_index);
117 x_email_count(l_index).rt_classification_name:=x_rt_class_name_tbl(l_index);
118 x_email_count(l_index).email_account_name:=x_acct_name_tbl(l_index);
119 x_email_count(l_index).email_que_count:=x_count(l_index);
120 x_email_count(l_index).email_acq_count:=0;
121 x_email_count(l_index).email_max_qwait:=x_wait_time(l_index);
122 x_email_count(l_index).email_max_await:=0;
123 x_email_count(l_index).email_status:=0;
124 end loop;
125 FOR v2 IN c2 LOOP
126 l_match:=0;
127 FOR l_index IN x_email_count.FIRST..x_email_count.LAST LOOP
128 IF (v2.email_account_id=x_email_count(l_index).email_account_id)
129 AND
130 (v2.rt_classification_id=x_email_count(l_index).rt_classification_id) THEN
131 l_match:=1;
132 x_email_count(l_index).email_acq_count:=v2.total;
133 x_email_count(l_index).email_max_await:=v2.wait_time;
134 x_email_count(l_index).email_status:=v2.email_status;
135 END IF;
136 EXIT when l_match=1;
137 END LOOP;
138 IF l_match=0 THEN -- Add New Record
139 l_index:=x_email_count.count+1;
140 x_email_count(l_index).email_account_id:=v2.email_account_id;
141 x_email_count(l_index).rt_classification_id:=v2.rt_classification_id;
142 x_email_count(l_index).rt_classification_name:=v2.name;
143 x_email_count(l_index).email_account_name:=v2.USER_NAME;
144 x_email_count(l_index).email_que_count:=0;
145 x_email_count(l_index).email_acq_count:=v2.total;
146 x_email_count(l_index).email_max_qwait:=0;
147 x_email_count(l_index).email_max_await:=v2.wait_time;
148 x_email_count(l_index).email_status:=v2.email_status;
149 END IF;
150 END LOOP; -- End of Main Loop
151 ELSE
152 FOR v2 in c2 LOOP
153 l_index:=x_email_count.count+1;
154 x_email_count(l_index).email_account_id:=v2.email_account_id;
155 x_email_count(l_index).rt_classification_id:=v2.rt_classification_id;
156 x_email_count(l_index).rt_classification_name:=v2.name;
157 x_email_count(l_index).email_account_name:=v2.USER_NAME;
158 x_email_count(l_index).email_que_count:=0;
159 x_email_count(l_index).email_acq_count:=v2.total;
160 x_email_count(l_index).email_max_qwait:=0;
161 x_email_count(l_index).email_max_await:=v2.wait_time;
162 x_email_count(l_index).email_status:=v2.email_status;
163 END LOOP;
164 END IF;
165 if x_email_count.count=0 THEN
166 raise IEM_NO_DATA;
167 end if;
168 commit;
169 -- Standard Check Of p_commit.
170 IF p_commit='T' THEN
171 COMMIT WORK;
172 END IF;
173 -- Standard callto get message count and if count is 1, get message info.
174 FND_MSG_PUB.Count_And_Get
175 ( p_count => x_msg_count,
176 p_data => x_msg_data
177 );
178 EXCEPTION
179
180 WHEN NOT_A_VALID_AGENT THEN
181 ROLLBACK TO select_mail_count_PVT;
182 x_return_status := FND_API.G_RET_STS_ERROR ;
183 FND_MESSAGE.SET_NAME('IEM', 'IEM_UNRECOGNIZED_AGENT');
184 FND_MSG_PUB.ADD;
185 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
186 WHEN IEM_NO_DATA THEN
187 ROLLBACK TO select_mail_count_PVT;
188 x_return_status := FND_API.G_RET_STS_ERROR ;
189 FND_MESSAGE.SET_NAME('IEM', 'IEM_NO_DATA');
190 FND_MSG_PUB.ADD;
191 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
192 WHEN FND_API.G_EXC_ERROR THEN
193 ROLLBACK TO select_mail_count_PVT;
194 x_return_status := FND_API.G_RET_STS_ERROR ;
195 FND_MSG_PUB.Count_And_Get
196 ( p_count => x_msg_count,
197 p_data => x_msg_data
198 );
199 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
200 ROLLBACK TO select_mail_count_PVT;
201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
202 FND_MSG_PUB.Count_And_Get
203 ( p_count => x_msg_count,
204 p_data => x_msg_data
205 );
206 WHEN OTHERS THEN
207 ROLLBACK TO select_mail_count_PVT;
208 x_return_status := FND_API.G_RET_STS_ERROR;
209 IF FND_MSG_PUB.Check_Msg_Level
210 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
211 THEN
212 FND_MSG_PUB.Add_Exc_Msg
213 ( G_PKG_NAME ,
214 l_api_name
215 );
216 END IF;
217 FND_MSG_PUB.Count_And_Get
218 ( p_count => x_msg_count ,
219 p_data => x_msg_data
220 );
221
222 END GetMailItemCount;
223
224 PROCEDURE GetMailItemCount (p_api_version_number IN NUMBER,
225 p_init_msg_list IN VARCHAR2 ,
226 p_commit IN VARCHAR2 ,
227 p_resource_id in number,
228 p_tbl in t_number_table:=NULL,
229 p_email_account_id in number,
230 x_class_bin out NOCOPY class_count_tbl,
231 x_return_status OUT NOCOPY VARCHAR2,
232 x_msg_count OUT NOCOPY NUMBER,
233 x_msg_data OUT NOCOPY VARCHAR2)
234
235 IS
236 l_tbl t_number_table:=t_number_table();
237 i_tbl jtf_number_table:=jtf_number_table();
238 CURSOR c1 IS
239 SELECT a.RT_CLASSIFICATION_ID,b.name,COUNT(*) TOTAL
240 FROM iem_rt_proc_emails a,iem_route_classifications b
241 where a.email_account_id=p_email_account_id
242 and a.resource_id =0
243 and a.rt_classification_id=b.route_classification_id
244 AND a.group_id in (select * from TABLE(cast(i_tbl as jtf_number_table)))
245 GROUP by a.rt_classification_id,b.name;
246 l_index number:=1;
247 l_api_version_number number:=1.0;
248 l_api_name varchar2(30):='GetMailItemCount';
249 x_rt_class_tbl t_number_table:=t_number_table() ;
250 x_rt_class_name_Tbl jtf_varchar2_table_100:=jtf_varchar2_table_100() ;
251 x_count t_number_table:=t_number_table() ;
252 l_ret_status varchar2(10);
253 l_msg_count number;
254 l_msg_data varchar2(500);
255 IEM_NO_DATA EXCEPTION;
256 begin
257 -- Standard call to check for call compatibility.
258 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
259 p_api_version_number,
260 l_api_name,
261 G_PKG_NAME)
262 THEN
263 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
264 END IF;
265 SAVEPOINT select_item_PVT;
266 x_return_status := FND_API.G_RET_STS_SUCCESS;
267 BEGIN
268 IF p_tbl.count=0 then
269 IEM_MAILITEM_PUB.getGroupDetails(p_api_version_number=>1.0,
270 p_init_msg_list=>'F',
271 p_commit=>'F',
272 p_resource_id =>p_resource_id,
273 x_tbl =>l_tbl,
274 x_return_status=>l_ret_status ,
275 x_msg_count=>l_msg_count,
276 x_msg_data=>l_msg_data);
277 ELSE
278 l_tbl:=p_tbl;
279 END IF;
280 EXCEPTION WHEN OTHERS THEN
281 IEM_MAILITEM_PUB.getGroupDetails(p_api_version_number=>1.0,
282 p_init_msg_list=>'F',
283 p_commit=>'F',
284 p_resource_id =>p_resource_id,
285 x_tbl =>l_tbl,
286 x_return_status=>l_ret_status ,
287 x_msg_count=>l_msg_count,
288 x_msg_data=>l_msg_data);
289 IF l_tbl.count=0 THEN
290 RAISE IEM_NO_DATA;
291 END IF;
292 END;
293 FOR j in l_tbl.FIRST..l_tbl.LAST LOOP
294 i_tbl.extend;
295 i_tbl(j):=l_tbl(j);
296 END LOOP;
297 open c1;
298 fetch c1 bulk collect into x_rt_class_tbl,x_rt_class_name_Tbl,x_count;
299 close c1;
300 IF x_rt_class_tbl.count=0 THEN
301 RAISE IEM_NO_DATA;
302 END IF;
303 FOR l_index in x_rt_class_tbl.FIRST..x_rt_class_tbl.LAST LOOP
304 x_class_bin(l_index).rt_classification_id:=x_rt_class_tbl(l_index);
305 x_class_bin(l_index).rt_classification_name:=x_rt_class_name_TbL(l_index);
306 x_class_bin(l_index).email_count:=x_count(l_index);
307 END LOOP;
308
309 -- Standard Check Of p_commit.
310 IF p_commit='T' THEN
311 COMMIT WORK;
312 END IF;
313 -- Standard callto get message count and if count is 1, get message info.
314 FND_MSG_PUB.Count_And_Get
315 ( p_count => x_msg_count,
316 p_data => x_msg_data
317 );
318 EXCEPTION
319 WHEN IEM_NO_DATA THEN
320 ROLLBACK TO select_item_PVT;
321 x_return_status := FND_API.G_RET_STS_ERROR ;
322 FND_MESSAGE.SET_NAME('IEM', 'IEM_NO_DATA');
323 FND_MSG_PUB.ADD;
324 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
325 WHEN FND_API.G_EXC_ERROR THEN
326 ROLLBACK TO select_item_PVT;
327 x_return_status := FND_API.G_RET_STS_ERROR ;
328 FND_MSG_PUB.Count_And_Get
329 ( p_count => x_msg_count,
330 p_data => x_msg_data
331 );
332 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
333 ROLLBACK TO select_item_PVT;
334 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
335 FND_MSG_PUB.Count_And_Get
336 ( p_count => x_msg_count,
337 p_data => x_msg_data
338 );
339 WHEN OTHERS THEN
340 ROLLBACK TO select_item_PVT;
341 x_return_status := FND_API.G_RET_STS_ERROR;
342 IF FND_MSG_PUB.Check_Msg_Level
343 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
344 THEN
345 FND_MSG_PUB.Add_Exc_Msg
346 ( G_PKG_NAME ,
347 l_api_name
348 );
349 END IF;
350 FND_MSG_PUB.Count_And_Get
351 ( p_count => x_msg_count ,
352 p_data => x_msg_data
353 );
354
355 END GetMailItemCount;
356
357 PROCEDURE GetMailItemCount (p_api_version_number IN NUMBER,
358 p_init_msg_list IN VARCHAR2 ,
359 p_commit IN VARCHAR2 ,
360 p_email_account_id in number,
361 x_class_bin out NOCOPY class_count_tbl,
362 x_return_status OUT NOCOPY VARCHAR2,
363 x_msg_count OUT NOCOPY NUMBER,
364 x_msg_data OUT NOCOPY VARCHAR2)
365
366 IS
367 cursor c2 is select a.rt_classification_id,b.name,count(*) total
368 from iem_rt_proc_emails a,iem_route_classifications b
369 where a.email_account_id=p_email_account_id
370 and a.resource_id=0
371 and a.rt_classification_id=b.route_classification_id
372 group by rt_classification_id,b.name;
373 l_index number:=1;
374 l_api_version_number number:=1.0;
375 l_api_name varchar2(30):='GetMailItemCount';
376 x_rt_class_tbl t_number_table:=t_number_table() ;
377 x_rt_class_name_Tbl jtf_varchar2_table_100:=jtf_varchar2_table_100() ;
378 x_count t_number_table:=t_number_table() ;
379 IEM_NO_DATA EXCEPTION;
380 begin
381 -- Standard call to check for call compatibility.
382 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
383 p_api_version_number,
384 l_api_name,
385 G_PKG_NAME)
386 THEN
387 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
388 END IF;
389 SAVEPOINT select_item_PVT;
390 x_return_status := FND_API.G_RET_STS_SUCCESS;
391 open c2;
392 fetch c2 bulk collect into x_rt_class_tbl,x_rt_class_name_Tbl,x_count;
393 close c2;
394 IF x_rt_class_tbl.count=0 THEN
395 RAISE IEM_NO_DATA;
396 END IF;
397 FOR l_index in x_rt_class_tbl.FIRST..x_rt_class_tbl.LAST LOOP
398 x_class_bin(l_index).rt_classification_id:=x_rt_class_tbl(l_index);
399 x_class_bin(l_index).rt_classification_name:=x_rt_class_name_TbL(l_index);
400 x_class_bin(l_index).email_count:=x_count(l_index);
401 END LOOP;
405 COMMIT WORK;
402
403 -- Standard Check Of p_commit.
404 IF p_commit='T' THEN
406 END IF;
407 -- Standard callto get message count and if count is 1, get message info.
408 FND_MSG_PUB.Count_And_Get
409 ( p_count => x_msg_count,
410 p_data => x_msg_data
411 );
412 EXCEPTION
413 WHEN IEM_NO_DATA THEN
414 ROLLBACK TO select_item_PVT;
415 x_return_status := FND_API.G_RET_STS_ERROR ;
416 FND_MESSAGE.SET_NAME('IEM', 'IEM_NO_DATA');
417 FND_MSG_PUB.ADD;
418 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
419 WHEN FND_API.G_EXC_ERROR THEN
420 ROLLBACK TO select_item_PVT;
421 x_return_status := FND_API.G_RET_STS_ERROR ;
422 FND_MSG_PUB.Count_And_Get
423 ( p_count => x_msg_count,
424 p_data => x_msg_data
425 );
426 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
427 ROLLBACK TO select_item_PVT;
428 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
429 FND_MSG_PUB.Count_And_Get
430 ( p_count => x_msg_count,
431 p_data => x_msg_data
432 );
433 WHEN OTHERS THEN
434 ROLLBACK TO select_item_PVT;
435 x_return_status := FND_API.G_RET_STS_ERROR;
436 IF FND_MSG_PUB.Check_Msg_Level
437 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
438 THEN
439 FND_MSG_PUB.Add_Exc_Msg
440 ( G_PKG_NAME ,
441 l_api_name
442 );
443 END IF;
444 FND_MSG_PUB.Count_And_Get
445 ( p_count => x_msg_count ,
446 p_data => x_msg_data
447 );
448
449 END GetMailItemCount;
450
451 PROCEDURE GetMailItemCount (p_api_version_number IN NUMBER,
452 p_init_msg_list IN VARCHAR2 ,
453 p_commit IN VARCHAR2 ,
454 p_resource_id in number,
455 p_tbl in t_number_table:=NULL,
456 p_email_account_id in number,
457 p_classification_id in number,
458 x_count out nocopy number,
459 x_return_status OUT NOCOPY VARCHAR2,
460 x_msg_count OUT NOCOPY NUMBER,
461 x_msg_data OUT NOCOPY VARCHAR2) IS
462 l_api_version_number number:=1.0;
463 l_api_name varchar2(30):='GetMailItemCount';
464 l_tbl t_number_table:=t_number_table();
465 i_tbl jtf_number_table:=jtf_number_table();
466 l_ret_status varchar2(10);
467 l_msg_count number;
468 l_msg_data varchar2(500);
469 IEM_NO_DATA EXCEPTION;
470 begin
471 -- Standard call to check for call compatibility.
472 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
473 p_api_version_number,
474 l_api_name,
475 G_PKG_NAME)
476 THEN
477 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
478 END IF;
479 SAVEPOINT select_item_PVT;
480 x_return_status := FND_API.G_RET_STS_SUCCESS;
481 BEGIN
482 IF p_tbl.count=0 then
483 IEM_MAILITEM_PUB.getGroupDetails(p_api_version_number=>1.0,
484 p_init_msg_list=>'F',
485 p_commit=>'F',
486 p_resource_id =>p_resource_id,
487 x_tbl =>l_tbl,
488 x_return_status=>l_ret_status ,
489 x_msg_count=>l_msg_count,
490 x_msg_data=>l_msg_data);
491 ELSE
492 l_tbl:=p_tbl;
493 END IF;
494 EXCEPTION WHEN OTHERS THEN
495 IEM_MAILITEM_PUB.getGroupDetails(p_api_version_number=>1.0,
496 p_init_msg_list=>'F',
497 p_commit=>'F',
498 p_resource_id =>p_resource_id,
499 x_tbl =>l_tbl,
500 x_return_status=>l_ret_status ,
501 x_msg_count=>l_msg_count,
502 x_msg_data=>l_msg_data);
503 IF l_tbl.count=0 THEN
504 RAISE IEM_NO_DATA;
505 END IF;
506 END;
507 FOR j in l_tbl.FIRST..l_tbl.LAST LOOP
508 i_tbl.extend;
509 i_tbl(j):=l_tbl(j);
510 END LOOP;
511 select COUNT(*)
512 INTO x_count
513 from iem_rt_proc_emails
514 where email_account_id=p_email_account_id
515 and rt_classification_id=p_classification_id
516 and resource_id=0
517 and group_id in (select * from TABLE(cast(i_tbl as jtf_number_table)));
518 IF x_count=0 THEN
519 RAISE IEM_NO_DATA;
520 END IF;
521 -- Standard Check Of p_commit.
522 IF p_commit='T' THEN
523 COMMIT WORK;
524 END IF;
525 -- Standard callto get message count and if count is 1, get message info.
526 FND_MSG_PUB.Count_And_Get
527 ( p_count => x_msg_count,
528 p_data => x_msg_data
529 );
530 EXCEPTION
531 WHEN IEM_NO_DATA THEN
532 ROLLBACK TO select_item_PVT;
533 x_return_status := FND_API.G_RET_STS_ERROR ;
534 FND_MESSAGE.SET_NAME('IEM', 'IEM_NO_DATA');
535 FND_MSG_PUB.ADD;
536 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
537 WHEN FND_API.G_EXC_ERROR THEN
538 ROLLBACK TO select_item_PVT;
539 x_return_status := FND_API.G_RET_STS_ERROR ;
540 FND_MSG_PUB.Count_And_Get
541 ( p_count => x_msg_count,
542 p_data => x_msg_data
543 );
544 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
545 ROLLBACK TO select_item_PVT;
546 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
547 FND_MSG_PUB.Count_And_Get
548 ( p_count => x_msg_count,
549 p_data => x_msg_data
553 x_return_status := FND_API.G_RET_STS_ERROR;
550 );
551 WHEN OTHERS THEN
552 ROLLBACK TO select_item_PVT;
554 IF FND_MSG_PUB.Check_Msg_Level
555 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
556 THEN
557 FND_MSG_PUB.Add_Exc_Msg
558 ( G_PKG_NAME ,
559 l_api_name
560 );
561 END IF;
562 FND_MSG_PUB.Count_And_Get
563 ( p_count => x_msg_count ,
564 p_data => x_msg_data
565 );
566
567 end GetMailItemcount;
568
569 -- Return POST MDT and TAg KEy values . Called by EMC Client
570
571 PROCEDURE GetMailItem (p_api_version_number IN NUMBER,
572 p_init_msg_list IN VARCHAR2 ,
573 p_commit IN VARCHAR2 ,
574 p_resource_id in number,
575 p_tbl in t_number_table:=NULL,
576 p_rt_classification in number,
577 p_account_id in number,
578 x_email_data out NOCOPY iem_rt_proc_emails%rowtype,
579 x_tag_key_value OUT NOCOPY keyVals_tbl_type,
580 x_encrypted_id OUT NOCOPY VARCHAR2,
581 x_return_status OUT NOCOPY VARCHAR2,
582 x_msg_count OUT NOCOPY NUMBER,
583 x_msg_data OUT NOCOPY VARCHAR2) IS
584 Type get_message_rec is REF CURSOR ;
585 email_dtl_cur get_message_rec;
586 l_id number;
587 l_index number;
588 l_date date;
589 l_api_version_number number:=1.0;
590 l_api_name varchar2(30):='GetMailItem';
591 l_tbl t_number_table:=t_number_table();
592 i_tbl jtf_number_table:=jtf_number_table();
593 l_ret_status varchar2(10);
594 l_where varchar2(255);
595 l_string varchar2(32000):='';
596 l_msg_count number;
597 l_count number;
598 l_msg_data varchar2(500);
599 l_encrypted_id varchar2(500);
600 IEM_NO_DATA EXCEPTION;
601 l_tag_key_value IEM_TAGPROCESS_PUB.keyVals_tbl_type;
602 l_interaction_rec JTF_IH_PUB.interaction_rec_type;
603 e_nowait EXCEPTION;
604 PRAGMA EXCEPTION_INIT(e_nowait, -54);
605 l_time number;
606 BEGIN
607 -- Standard call to check for call compatibility.
608 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
609 p_api_version_number,
610 l_api_name,
611 G_PKG_NAME)
612 THEN
613 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
614 END IF;
615 SAVEPOINT select_data_PVT;
616 x_return_status := FND_API.G_RET_STS_SUCCESS;
617 IF (p_tbl is null) and (nvl(p_account_id,FND_API.G_MISS_NUM)<> FND_API.G_MISS_NUM) and
618 (nvl(p_rt_classification,FND_API.G_MISS_NUM)<> FND_API.G_MISS_NUM) THEN
619 OPEN email_dtl_cur FOR
620 'select /*FIRST_ROWS*/ * from iem_rt_proc_emails p where message_id not in (select message_id from iem_reroute_hists where resource_id=:res1 ) and email_account_id=:id
621 and rt_classification_id=:rt
622 and resource_id=0
623 and ( p.group_id = 0
624 or exists (
625 select null
626 from jtf_rs_group_members gm
627 where resource_id=:res
628 and gm.group_id = p.group_id
629 and delete_flag <>''Y''
630 )
631 )
632 order by received_date for update skip locked'
633 using p_resource_id,p_account_id,p_rt_classification,p_resource_id;
634 LOOP
635 BEGIN
636 FETCH email_dtl_cur into x_email_data;
637 EXIT;
638 EXCEPTION when e_nowait then
639 null;
640 WHEN OTHERS then
641 null;
642 END;
643 END LOOP;
644 close email_dtl_cur;
645 ELSE
646 OPEN email_dtl_cur FOR
647 'select /*FIRST_ROWS*/ * from iem_rt_proc_emails p where message_id not in (select message_id from iem_reroute_hists where resource_id=:res1 ) and email_account_id=:id
648 and resource_id=0
649 and ( p.group_id = 0
650 or exists (
651 select null
652 from jtf_rs_group_members gm
653 where resource_id=:res
654 and gm.group_id = p.group_id
655 and delete_flag <>''Y''
656 )
657 )
658 order by received_date for update skip locked'
659 using p_resource_id,p_account_id,p_resource_id;
660 LOOP
661 BEGIN
662 FETCH email_dtl_cur into x_email_data;
663 EXIT;
664 EXCEPTION when e_nowait then
665 null;
666 WHEN OTHERS then
667 null;
668 END;
669 END LOOP;
670 close email_dtl_cur;
671 END IF;
672 IF x_email_data.message_id IS NOT NULL THEN
673 l_tag_key_value.delete;
674 IEM_TAGPROCESS_PUB.getTagValues_on_MsgId(
675 P_Api_Version_Number=>1.0,
676 p_message_id => x_email_data.message_id,
677 x_key_value=>l_tag_key_value,
678 x_encrypted_id=>l_encrypted_id,
679 x_msg_count=>l_msg_count,
680 x_return_status=>l_ret_status,
681 x_msg_data =>l_msg_data);
682 l_index:=1;
683 IF l_tag_key_value.count>0 THEN
684 x_encrypted_id:=l_encrypted_id;
685 FOR i in l_tag_key_value.FIRST..l_tag_key_value.LAST LOOP
686 x_tag_key_value(l_index).key:=l_tag_key_value(i).key;
687 x_tag_key_value(l_index).value:=l_tag_key_value(i).value;
688 x_tag_key_value(l_index).datatype:=l_tag_key_value(i).datatype;
689 l_index:=l_index+1;
690 END LOOP;
691 END IF;
692 UPDATE iem_rt_proc_emails
693 set resource_id=p_resource_id,
694 queue_status='G'
698 l_interaction_rec.resource_id:=p_resource_id;
695 where message_id=x_email_data.message_id ;
696 IF x_email_data.ih_interaction_id is not null then -- updating interaction with resource id
697 l_interaction_rec.interaction_id:=x_email_data.ih_interaction_id;
699 JTF_IH_PUB.Update_Interaction( p_api_version => 1.0,
700 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
701 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
702 p_user_id =>nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
703 p_login_id =>TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
704 x_return_status => l_ret_status,
705 x_msg_count => l_msg_count,
706 x_msg_data => l_msg_data,
707 p_interaction_rec => l_interaction_rec
708 );
709 END IF;
710 commit;
711 ELSE
712 RAISE IEM_NO_DATA;
713 END IF;
714 -- Standard Check Of p_commit.
715 IF p_commit='T' THEN
716 COMMIT WORK;
717 END IF;
718 -- Standard callto get message count and if count is 1, get message info.
719 FND_MSG_PUB.Count_And_Get
720 ( p_count => x_msg_count,
721 p_data => x_msg_data
722 );
723 EXCEPTION
724 WHEN IEM_NO_DATA THEN
725 ROLLBACK TO select_data_PVT;
726 x_return_status := FND_API.G_RET_STS_ERROR ;
727 FND_MESSAGE.SET_NAME('IEM', 'IEM_NO_DATA');
728 FND_MSG_PUB.ADD;
729 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
730 WHEN FND_API.G_EXC_ERROR THEN
731 ROLLBACK TO select_data_PVT;
732 x_return_status := FND_API.G_RET_STS_ERROR ;
733 FND_MSG_PUB.Count_And_Get
734 ( p_count => x_msg_count,
735 p_data => x_msg_data
736 );
737 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
738 ROLLBACK TO select_data_PVT;
739 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
740 FND_MSG_PUB.Count_And_Get
741 ( p_count => x_msg_count,
742 p_data => x_msg_data
743 );
744 WHEN NO_DATA_FOUND THEN
745 null;
746 WHEN OTHERS THEN
747 ROLLBACK TO select_data_PVT;
748 x_return_status := FND_API.G_RET_STS_ERROR;
749 IF FND_MSG_PUB.Check_Msg_Level
750 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
751 THEN
752 FND_MSG_PUB.Add_Exc_Msg
753 ( G_PKG_NAME ,
754 l_api_name
755 );
756 END IF;
757 FND_MSG_PUB.Count_And_Get
758 ( p_count => x_msg_count ,
759 p_data => x_msg_data
760 );
761
762 end GetMailItem;
763 PROCEDURE DisposeMailItem (p_api_version_number IN NUMBER,
764 p_init_msg_list IN VARCHAR2 ,
765 p_commit IN VARCHAR2 ,
766 p_message_id in number,
767 x_return_status OUT NOCOPY VARCHAR2,
768 x_msg_count OUT NOCOPY NUMBER,
769 x_msg_data OUT NOCOPY VARCHAR2) IS
770 l_api_name VARCHAR2(255):='DisposeMailItem';
771 l_api_version_number NUMBER:=1.0;
772 l_media_rec JTF_IH_PUB.media_rec_type;
773 l_media_data JTF_IH_MEDIA_ITEMS%ROWTYPE;
774 l_ret_status varchar2(10);
775 l_msg_data varchar2(300);
776 l_msg_count number;
777 l_media_id number;
778
779 BEGIN
780 -- Standard call to check for call compatibility.
781 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
782 p_api_version_number,
783 l_api_name,
784 G_PKG_NAME)
785 THEN
786 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
787 END IF;
788 SAVEPOINT dispose_mail_item_PVT;
789 x_return_status := FND_API.G_RET_STS_SUCCESS;
790 BEGIN -- Close the Media Item
791 SELECT IH_MEDIA_ITEM_ID into l_media_id
792 FROM iem_rt_proc_emails
793 WHERE MESSAGE_ID=p_message_id;
794 SELECT * into l_media_data
795 FROM JTF_IH_MEDIA_ITEMS
796 WHERE MEDIA_ID=l_media_id;
797 l_media_rec.media_id := l_media_id;
798 l_media_rec.source_id := l_media_data.source_id;
799 l_media_rec.direction:= l_media_data.direction;
800 l_media_rec.start_date_time := l_media_data.start_date_time;
801 l_media_rec.media_item_type := l_media_data.media_item_type;
802 l_media_rec.media_item_ref := l_media_data.media_item_ref;
803 l_media_rec.media_data := l_media_data.media_data;
804 JTF_IH_PUB.Close_MediaItem( 1.0,
805 'T',
806 'F',
807 TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
808 TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
809 nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
810 TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
811 l_ret_status,
812 l_msg_count,
813 l_msg_data,
814 l_media_rec);
815 EXCEPTION WHEN OTHERS THEN
816 NULL;
817 END ;
818 DELETE FROM iem_rt_proc_emails
819 WHERE MESSAGE_ID=p_message_id;
820 delete from iem_reroute_hists
821 where message_id=p_message_id;
822 delete from iem_kb_results where message_id=p_message_id;
823 delete from iem_email_classifications where message_id=p_message_id;
824 delete from iem_comp_rt_stats where type='WORKFLOW' and param=to_char(p_message_id);
828 where message_id=p_message_id;
825
826 /*
827 delete from iem_encrypted_tags
829 */
830 -- Standard Check Of p_commit.
831 IF p_commit='T' THEN
832 COMMIT WORK;
833 END IF;
834 -- Standard callto get message count and if count is 1, get message info.
835 FND_MSG_PUB.Count_And_Get
836 ( p_count => x_msg_count,
837 p_data => x_msg_data
838 );
839 EXCEPTION
840 WHEN FND_API.G_EXC_ERROR THEN
841 ROLLBACK TO dispose_mail_item_PVT;
842 x_return_status := FND_API.G_RET_STS_ERROR ;
843 FND_MSG_PUB.Count_And_Get
844 ( p_count => x_msg_count,
845 p_data => x_msg_data
846 );
847 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
848 ROLLBACK TO dispose_mail_item_PVT;
849 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
850 FND_MSG_PUB.Count_And_Get
851 ( p_count => x_msg_count,
852 p_data => x_msg_data
853 );
854 WHEN OTHERS THEN
855 ROLLBACK TO dispose_mail_item_PVT;
856 x_return_status := FND_API.G_RET_STS_ERROR;
857 IF FND_MSG_PUB.Check_Msg_Level
858 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
859 THEN
860 FND_MSG_PUB.Add_Exc_Msg
861 ( G_PKG_NAME ,
862 l_api_name
863 );
864 END IF;
865 FND_MSG_PUB.Count_And_Get
866 ( p_count => x_msg_count ,
867 p_data => x_msg_data
868 );
869
870 END DisposeMailItem;
871
872 PROCEDURE getGroupDetails(p_api_version_number IN NUMBER,
873 p_init_msg_list IN VARCHAR2 ,
874 p_commit IN VARCHAR2 ,
875 p_resource_id in number,
876 x_tbl out NOCOPY t_number_table,
877 x_return_status OUT NOCOPY VARCHAR2,
878 x_msg_count OUT NOCOPY NUMBER,
879 x_msg_data OUT NOCOPY VARCHAR2) IS
880
881 l_api_name VARCHAR2(255):='getGroupDetails';
882 l_api_version_number NUMBER:=1.0;
883 NOT_A_VALID_AGENT EXCEPTION;
884 BEGIN
885 -- Standard call to check for call compatibility.
886 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
887 p_api_version_number,
888 l_api_name,
889 G_PKG_NAME)
890 THEN
891 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
892 END IF;
893 SAVEPOINT getgroupdetails_PVT;
894 x_return_status := FND_API.G_RET_STS_SUCCESS;
895 select group_id bulk collect into x_tbl
896 from jtf_rs_group_members
897 where resource_id=p_resource_id
898 and delete_flag<>'Y';
899 IF x_tbl.count=0 then
900 raise NOT_A_VALID_AGENT;
901 END IF;
902 x_tbl.extend;
903 x_tbl(x_tbl.count):=0;
904 -- Standard Check Of p_commit.
905 IF p_commit='T' THEN
906 COMMIT WORK;
907 END IF;
908 -- Standard callto get message count and if count is 1, get message info.
909 FND_MSG_PUB.Count_And_Get
910 ( p_count => x_msg_count,
911 p_data => x_msg_data
912 );
913 EXCEPTION
914 WHEN NOT_A_VALID_AGENT THEN
915 ROLLBACK TO getgroupdetails_PVT;
916 x_return_status := FND_API.G_RET_STS_ERROR ;
917 FND_MESSAGE.SET_NAME('IEM', 'IEM_UNRECOGNIZED_AGENT');
918 FND_MSG_PUB.ADD;
919 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
920
921 WHEN FND_API.G_EXC_ERROR THEN
922 ROLLBACK TO getgroupdetails_PVT;
923 x_return_status := FND_API.G_RET_STS_ERROR ;
924 FND_MSG_PUB.Count_And_Get
925 ( p_count => x_msg_count,
926 p_data => x_msg_data
927 );
928 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
929 ROLLBACK TO getgroupdetails_PVT;
930 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
931 FND_MSG_PUB.Count_And_Get
932 ( p_count => x_msg_count,
933 p_data => x_msg_data
934 );
935 WHEN OTHERS THEN
936 ROLLBACK TO getgroupdetails_PVT;
937 x_return_status := FND_API.G_RET_STS_ERROR;
938 IF FND_MSG_PUB.Check_Msg_Level
939 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
940 THEN
941 FND_MSG_PUB.Add_Exc_Msg
942 ( G_PKG_NAME ,
943 l_api_name
944 );
945 END IF;
946 FND_MSG_PUB.Count_And_Get
947 ( p_count => x_msg_count ,
948 p_data => x_msg_data
949 );
950 END getGroupDetails;
951
952 PROCEDURE UpdateMailItem (p_api_version_number IN NUMBER,
953 p_init_msg_list IN VARCHAR2 ,
954 p_commit IN VARCHAR2 ,
955 p_email_data in iem_rt_proc_emails%rowtype,
956 x_return_status OUT NOCOPY VARCHAR2,
957 x_msg_count OUT NOCOPY NUMBER,
958 x_msg_data OUT NOCOPY VARCHAR2) IS
959
960 l_api_name VARCHAR2(255):='UpdateMailItem';
961 l_api_version_number NUMBER:=1.0;
962 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
963 l_LAST_UPDATE_DATE DATE:=SYSDATE;
964 l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
965
966 BEGIN
967 -- Standard call to check for call compatibility.
968 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
969 p_api_version_number,
970 l_api_name,
974 END IF;
971 G_PKG_NAME)
972 THEN
973 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
975 SAVEPOINT update_item_PVT;
976 x_return_status := FND_API.G_RET_STS_SUCCESS;
977 UPDATE iem_rt_proc_emails
978 SET
979 resource_id =p_email_data.resource_id,
980 PRIORITY =p_email_data.priority,
981 MSG_STATUS =p_email_data.msg_status,
982 SUBJECT =p_email_data.subject,
983 SENT_DATE =p_email_data.sent_date,
984 CUSTOMER_ID =p_email_data.customer_id,
985 CONTACT_ID =p_email_data.CONTACT_ID,
986 RELATIONSHIP_ID =p_email_data.RELATIONSHIP_ID,
987 RECEIVED_DATE =p_email_data.received_date,
988 MAIL_ITEM_STATUS =p_email_data.mail_item_status,
989 LAST_UPDATE_DATE = sysdate,
990 LAST_UPDATED_BY = decode(l_LAST_UPDATED_BY,null,-1,l_LAST_UPDATED_BY),
991 LAST_UPDATE_LOGIN =decode(l_LAST_UPDATE_LOGIN,null,-1,l_LAST_UPDATE_LOGIN),
992 ATTRIBUTE1 =p_email_data.attribute1,
993 ATTRIBUTE2 =p_email_data.attribute2,
994 ATTRIBUTE3 =p_email_data.attribute3,
995 ATTRIBUTE4 = p_email_data.attribute4,
996 ATTRIBUTE5 = p_email_data.attribute5,
997 ATTRIBUTE6 = p_email_data.attribute6,
998 ATTRIBUTE7 = p_email_data.attribute7,
999 ATTRIBUTE8 = p_email_data.attribute8,
1000 ATTRIBUTE9 = p_email_data.attribute9,
1001 ATTRIBUTE10 =p_email_data.attribute10,
1002 ATTRIBUTE11 = p_email_data.attribute11,
1003 ATTRIBUTE12 = p_email_data.attribute12,
1004 ATTRIBUTE13 = p_email_data.attribute13,
1005 ATTRIBUTE14 = p_email_data.attribute14,
1006 ATTRIBUTE15 = p_email_data.attribute15
1007 WHERE message_id=p_email_data.message_id;
1008
1009 IF p_commit='T' THEN
1010 COMMIT WORK;
1011 END IF;
1012
1013 -- Standard callto get message count and if count is 1, get message info.
1014 FND_MSG_PUB.Count_And_Get
1015 ( p_count => x_msg_count,
1016 p_data => x_msg_data
1017 );
1018 EXCEPTION
1019 WHEN FND_API.G_EXC_ERROR THEN
1020 ROLLBACK TO update_item_PVT;
1021 x_return_status := FND_API.G_RET_STS_ERROR ;
1022 FND_MSG_PUB.Count_And_Get
1023 ( p_count => x_msg_count,
1024 p_data => x_msg_data
1025 );
1026 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1027 ROLLBACK TO update_item_PVT;
1028 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1029 FND_MSG_PUB.Count_And_Get
1030 ( p_count => x_msg_count,
1031 p_data => x_msg_data
1032 );
1033 WHEN OTHERS THEN
1034 ROLLBACK TO update_item_PVT;
1035 x_return_status := FND_API.G_RET_STS_ERROR;
1036 IF FND_MSG_PUB.Check_Msg_Level
1037 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1038 THEN
1039 FND_MSG_PUB.Add_Exc_Msg
1040 ( G_PKG_NAME ,
1041 l_api_name
1042 );
1043 END IF;
1044 FND_MSG_PUB.Count_And_Get
1045 ( p_count => x_msg_count ,
1046 p_data => x_msg_data
1047 );
1048
1049 END UpdateMailItem;
1050
1051 PROCEDURE getMailItemInfo(p_api_version_number IN NUMBER,
1052 p_init_msg_list IN VARCHAR2 ,
1053 p_commit IN VARCHAR2 ,
1054 p_message_id in number,
1055 p_account_id in number,
1056 p_agent_id in number,
1057 x_email_data out NOCOPY iem_rt_proc_emails%rowtype,
1058 x_return_status OUT NOCOPY VARCHAR2,
1059 x_msg_count OUT NOCOPY NUMBER,
1060 x_msg_data OUT NOCOPY VARCHAR2) IS
1061
1062 l_api_name VARCHAR2(255):='GetMailItemInfo';
1063 l_api_version_number NUMBER:=1.0;
1064 BEGIN
1065 -- Standard call to check for call compatibility.
1066 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1067 p_api_version_number,
1068 l_api_name,
1069 G_PKG_NAME)
1070 THEN
1071 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1072 END IF;
1073 SAVEPOINT get_mail_iteminfo_pvt;
1074 x_return_status := FND_API.G_RET_STS_SUCCESS;
1075 IF p_agent_id<>0 THEN -- Not a supervisor mode
1076 BEGIN
1077 SELECT *
1078 INTO x_email_data
1079 FROM iem_rt_proc_emails
1080 WHERE message_id=p_message_id;
1081 EXCEPTION WHEN NO_DATA_FOUND THEN
1082 FND_MESSAGE.SET_NAME('IEM', 'IEM_NO_DATA');
1083 FND_MSG_PUB.ADD;
1084 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1085 raise;
1086 END;
1087 ELSE -- Supervisor Mode
1088 BEGIN
1089 SELECT *
1090 INTO x_email_data
1091 FROM iem_rt_proc_emails
1092 WHERE message_id=p_message_id;
1093 EXCEPTION WHEN NO_DATA_FOUND THEN
1094 FND_MESSAGE.SET_NAME('IEM', 'IEM_NO_DATA');
1095 FND_MSG_PUB.ADD;
1096 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1097 raise;
1098 END;
1099 END IF;
1100
1101
1102 -- Standard callto get message count and if count is 1, get message info.
1103 FND_MSG_PUB.Count_And_Get
1104 ( p_count => x_msg_count,
1105 p_data => x_msg_data
1106 );
1107 EXCEPTION
1108 WHEN FND_API.G_EXC_ERROR THEN
1109 ROLLBACK TO get_mail_iteminfo_pvt;
1110 x_return_status := FND_API.G_RET_STS_ERROR ;
1111 FND_MSG_PUB.Count_And_Get
1115 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1112 ( p_count => x_msg_count,
1113 p_data => x_msg_data
1114 );
1116 ROLLBACK TO get_mail_iteminfo_pvt;
1117 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1118 FND_MSG_PUB.Count_And_Get
1119 ( p_count => x_msg_count,
1120 p_data => x_msg_data
1121 );
1122 WHEN OTHERS THEN
1123 ROLLBACK TO get_mail_iteminfo_pvt;
1124 x_return_status := FND_API.G_RET_STS_ERROR;
1125 IF FND_MSG_PUB.Check_Msg_Level
1126 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1127 THEN
1128 FND_MSG_PUB.Add_Exc_Msg
1129 ( G_PKG_NAME ,
1130 l_api_name
1131 );
1132 END IF;
1133 FND_MSG_PUB.Count_And_Get
1134 ( p_count => x_msg_count ,
1135 p_data => x_msg_data
1136 );
1137 END getmailiteminfo;
1138 PROCEDURE getEmailHeaders(p_api_version_number IN NUMBER,
1139 p_init_msg_list IN VARCHAR2 ,
1140 p_commit IN VARCHAR2 ,
1141 p_resource_id in number,
1142 p_email_account_id in number,
1143 p_display_size in NUMBER,
1144 p_page_count in NUMBER,
1145 p_sort_by in VARCHAR2,
1146 p_sort_order in number,
1147 x_total_message out NOCOPY number,
1148 x_acq_email_data out NOCOPY acq_email_info_tbl,
1149 x_return_status OUT NOCOPY VARCHAR2,
1150 x_msg_count OUT NOCOPY NUMBER,
1151 x_msg_data OUT NOCOPY VARCHAR2) IS
1152
1153 l_api_name VARCHAR2(255):='getEmailHeaders';
1154 l_api_version_number NUMBER:=1.0;
1155 Type get_data is REF CURSOR;-- RETURN acq_email_info_tbl;
1156 email_cur get_data;
1157 l_counter number:=0;
1158 l_order_by varchar2(255);
1159 l_sort_order varchar2(100);
1160 l_where varchar2(255);
1161 l_temp_tbl acq_email_info_tbl;
1162 l_start_index number:=0;
1163 l_first_index number:=0;
1164 l_last_index number:=0;
1165 l_expire varchar2(1):='N';
1166 l_status_type varchar2(40):='IEM_MESSAGE_STATUS_TYPE';
1167
1168 BEGIN
1169 -- Standard call to check for call compatibility.
1170 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1171 p_api_version_number,
1172 l_api_name,
1173 G_PKG_NAME)
1174 THEN
1175 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1176 END IF;
1177 SAVEPOINT getemailheaders_pvt;
1178 x_return_status := FND_API.G_RET_STS_SUCCESS;
1179 select decode(p_sort_order,0,'ASC','DESC')
1180 into l_sort_order
1181 from dual;
1182 IF p_sort_by = FND_API.G_MISS_CHAR OR p_sort_by='D' THEN
1183 l_order_by:=' Order BY to_date(substr(a.sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'') '||l_sort_order;
1184 ELSIF p_sort_by='S' THEN
1185 l_order_by:='ORDER BY a.subject '||l_sort_order||',to_date(substr(a.sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'') '||l_sort_order;
1186 ELSIF p_sort_by='C' THEN
1187 l_order_by:='ORDER BY c.NAME '||l_sort_order||',to_date(substr(a.sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'') '||l_sort_order;
1188 ELSIF p_sort_by='F' THEN
1189 l_order_by:='ORDER BY a.from_address '||l_sort_order||',to_date(substr(a.sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'') '||l_sort_order;
1190 ELSIF p_sort_by='T' THEN
1191 l_order_by:='ORDER BY d.description '||l_sort_order||',to_date(substr(a.sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'') '||l_sort_order;
1192 ELSIF p_sort_by='R' THEN
1193 l_order_by:='ORDER BY read_status '||l_sort_order||',to_date(substr(a.sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'') '||l_sort_order;
1194 END IF;
1195 x_total_message:=0;
1196 OPEN email_cur FOR
1197 'SELECT a.message_id,a.rt_classification_id,c.name,b.rt_media_item_id,
1198 b.rt_interaction_id,
1199 a.email_account_id,a.message_flag,a.from_address,a.subject,a.priority,a.msg_status,
1200 to_char(to_date(substr(a.sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS''),''MM/DD/RRRR HH24:MI:SS''),a.mail_item_status,
1201 -- to_char(to_date(substr(a.sent_Date,5,length(a.sent_Date)-13)||substr(a.sent_date,25,4),''Mon DD hh24:mi:ssyyyy''),''MM/DD/RRRR HH24:MI:SS''),a.mail_item_status,
1202 a.from_resource_id,
1203 decode(a.mail_item_status,''R'',1,''S'',1,0) read_status,d.description
1204 FROM iem_rt_proc_emails a,
1205 IEM_RT_MEDIA_ITEMS b,
1206 IEM_ROUTE_CLASSIFICATIONS c,
1207 FND_LOOKUPS d
1208 WHERE A.RT_CLASSIFICATION_ID=C.ROUTE_CLASSIFICATION_ID AND B.EXPIRE=:expire AND A.MESSAGE_ID=B.MESSAGE_ID and a.resource_id=:id and a.email_account_id=:account_id and
1209 substr(a.mail_item_status,1,1)=d.lookup_code and d.lookup_type=:status_type '||l_order_by
1210 using l_expire,p_resource_id,p_email_account_id,l_status_type;
1211 l_temp_tbl.delete;
1212 l_counter:=1;
1213 LOOP
1214 FETCH email_cur INTO l_temp_tbl(l_counter);
1215 EXIT WHEN email_cur%NOTFOUND;
1216 l_counter:=l_counter+1;
1217 END LOOP;
1218 CLOSE email_cur;
1219 IF l_temp_tbl.count>0 THEN -- Data Selected Now implement Display Logic
1220 x_total_message:=l_temp_tbl.count;
1221 IF p_display_size=FND_API.G_MISS_NUM THEN
1222 x_acq_email_data:=l_temp_tbl; -- Return all data
1223 --incase of null display size
1224 ELSE
1225 IF p_page_count<>FND_API.G_MISS_NUM THEN
1226 l_first_index:=p_page_count*p_display_size - p_display_size+1;
1227 l_last_index:=p_page_count*p_display_size;
1228 ELSIF p_page_count=FND_API.G_MISS_NUM THEN
1232 IF l_last_index > x_total_message THEN
1229 l_first_index:=1;
1230 l_last_index:=p_display_size;
1231 END IF;
1233 l_last_index:=x_total_message;
1234 END IF;
1235 FOR l_index in l_first_index..l_last_index LOOP
1236 x_acq_email_data(l_index):=l_temp_tbl(l_index);
1237 END LOOP;
1238 END IF;
1239 END IF;
1240 IF p_commit='T' THEN
1241 COMMIT WORK;
1242 END IF;
1243
1244 -- Standard callto get message count and if count is 1, get message info.
1245 FND_MSG_PUB.Count_And_Get
1246 ( p_count => x_msg_count,
1247 p_data => x_msg_data
1248 );
1249 EXCEPTION
1250 WHEN FND_API.G_EXC_ERROR THEN
1251 ROLLBACK TO getemailheaders_pvt;
1252 x_return_status := FND_API.G_RET_STS_ERROR ;
1253 FND_MSG_PUB.Count_And_Get
1254 ( p_count => x_msg_count,
1255 p_data => x_msg_data
1256 );
1257 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1258 ROLLBACK TO getemailheaders_pvt;
1259 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1260 FND_MSG_PUB.Count_And_Get
1261 ( p_count => x_msg_count,
1262 p_data => x_msg_data
1263 );
1264 WHEN OTHERS THEN
1265 ROLLBACK TO getemailheaders_pvt;
1266 x_return_status := FND_API.G_RET_STS_ERROR;
1267 IF FND_MSG_PUB.Check_Msg_Level
1268 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1269 THEN
1270 FND_MSG_PUB.Add_Exc_Msg
1271 ( G_PKG_NAME ,
1272 l_api_name
1273 );
1274 END IF;
1275 FND_MSG_PUB.Count_And_Get
1276 ( p_count => x_msg_count ,
1277 p_data => x_msg_data
1278 );
1279 END getEmailHeaders;
1280 PROCEDURE GetQueueItemData (p_api_version_number IN NUMBER,
1281 p_init_msg_list IN VARCHAR2 ,
1282 p_commit IN VARCHAR2 ,
1283 p_message_id in number,
1284 p_from_agent_id in number,
1285 p_to_agent_id in number,
1286 p_mail_item_status in varchar2,
1287 x_email_data out NOCOPY iem_rt_proc_emails%rowtype,
1288 x_tag_key_value OUT NOCOPY keyVals_tbl_type,
1289 x_encrypted_id OUT NOCOPY VARCHAR2,
1290 x_return_status OUT NOCOPY VARCHAR2,
1291 x_msg_count OUT NOCOPY NUMBER,
1292 x_msg_data OUT NOCOPY VARCHAR2) IS
1293 l_api_version_number number:=1.0;
1294 l_api_name varchar2(30):='GetQueueItemData';
1295 l_tag_key_value IEM_TAGPROCESS_PUB.keyVals_tbl_type;
1296 l_msg_count number;
1297 l_msg_data varchar2(500);
1298 l_ret_status varchar2(50);
1299 l_encrypted_id varchar2(500);
1300 l_index number;
1301 BEGIN
1302 -- Standard call to check for call compatibility.
1303 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1304 p_api_version_number,
1305 l_api_name,
1306 G_PKG_NAME)
1307 THEN
1308 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1309 END IF;
1310 SAVEPOINT select_data_PVT;
1311 x_return_status := FND_API.G_RET_STS_SUCCESS;
1312 SELECT * INTO x_email_data
1313 FROM iem_rt_proc_emails
1314 WHERE message_id=p_message_id
1315 AND resource_id=0 for update;
1316 update iem_rt_proc_emails
1317 set from_resource_id=p_from_agent_id,
1318 resource_id=p_to_agent_id,
1319 mail_item_status=p_mail_item_status
1320 where message_id=p_message_id;
1321 l_tag_key_value.delete;
1322 IEM_TAGPROCESS_PUB.getTagValues_on_MsgId(
1323 P_Api_Version_Number=>1.0,
1324 p_message_id => x_email_data.message_id,
1325 x_key_value=>l_tag_key_value,
1326 x_encrypted_id=>l_encrypted_id,
1327 x_msg_count=>l_msg_count,
1328 x_return_status=>l_ret_status,
1329 x_msg_data =>l_msg_data);
1330 l_index:=1;
1331 IF l_tag_key_value.count>0 THEN
1332 x_encrypted_id:=l_encrypted_id;
1333 FOR i in l_tag_key_value.FIRST..l_tag_key_value.LAST LOOP
1334 x_tag_key_value(l_index).key:=l_tag_key_value(i).key;
1335 x_tag_key_value(l_index).value:=l_tag_key_value(i).value;
1336 x_tag_key_value(l_index).datatype:=l_tag_key_value(i).datatype;
1337 l_index:=l_index+1;
1338 END LOOP;
1339 END IF;
1340 IF p_commit='T' THEN
1341 COMMIT WORK;
1342 END IF;
1343 -- Standard callto get message count and if count is 1, get message info.
1344 FND_MSG_PUB.Count_And_Get
1345 ( p_count => x_msg_count,
1346 p_data => x_msg_data
1347 );
1348 EXCEPTION
1349 WHEN NO_DATA_FOUND THEN
1350 ROLLBACK TO select_data_PVT;
1351 x_return_status := 'N';
1352 FND_MESSAGE.SET_NAME('IEM', 'IEM_NO_DATA');
1353 FND_MSG_PUB.ADD;
1354 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1355 WHEN FND_API.G_EXC_ERROR THEN
1356 ROLLBACK TO select_data_PVT;
1357 x_return_status := FND_API.G_RET_STS_ERROR ;
1358 FND_MSG_PUB.Count_And_Get
1359 ( p_count => x_msg_count,
1360 p_data => x_msg_data
1361 );
1362 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1363 ROLLBACK TO select_data_PVT;
1364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1365 FND_MSG_PUB.Count_And_Get
1366 ( p_count => x_msg_count,
1367 p_data => x_msg_data
1368 );
1369 WHEN OTHERS THEN
1370 ROLLBACK TO select_data_PVT;
1374 THEN
1371 x_return_status := FND_API.G_RET_STS_ERROR;
1372 IF FND_MSG_PUB.Check_Msg_Level
1373 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1375 FND_MSG_PUB.Add_Exc_Msg
1376 ( G_PKG_NAME ,
1377 l_api_name
1378 );
1379 END IF;
1380 FND_MSG_PUB.Count_And_Get
1381 ( p_count => x_msg_count ,
1382 p_data => x_msg_data
1383 );
1384 end GetQueueItemData;
1385 PROCEDURE GetMailItem (p_api_version_number IN NUMBER,
1386 p_init_msg_list IN VARCHAR2 ,
1387 p_commit IN VARCHAR2 ,
1388 p_resource_id in number,
1389 p_tbl in t_number_table:=NULL,
1390 p_rt_classification in number,
1391 p_account_id in number,
1392 x_email_data out NOCOPY iem_rt_proc_emails%rowtype,
1393 x_return_status OUT NOCOPY VARCHAR2,
1394 x_msg_count OUT NOCOPY NUMBER,
1395 x_msg_data OUT NOCOPY VARCHAR2) IS
1396 begin
1397 null;
1398 end GetMailitem;
1399
1400 PROCEDURE GetMailItem(p_api_version_number IN NUMBER,
1401 p_init_msg_list IN VARCHAR2 ,
1402 p_commit IN VARCHAR2 ,
1403 p_resource_id in number,
1404 p_acct_rt_class_id in number,
1405 x_email_data out NOCOPY iem_rt_proc_emails%rowtype,
1406 x_return_status OUT NOCOPY VARCHAR2,
1407 x_msg_count OUT NOCOPY NUMBER,
1408 x_msg_data OUT NOCOPY VARCHAR2) IS
1409 begin
1410 null;
1411 end;
1412 PROCEDURE ResolvedMessage (p_api_version_number IN NUMBER,
1413 p_init_msg_list IN VARCHAR2 ,
1414 p_commit IN VARCHAR2 ,
1415 p_message_id in number,
1416 p_action_flag in varchar2,
1417 x_return_status OUT NOCOPY VARCHAR2,
1418 x_msg_count OUT NOCOPY NUMBER,
1419 x_msg_data OUT NOCOPY VARCHAR2) IS
1420 l_msg_rec iem_rt_proc_emails%rowtype;
1421 l_header_rec iem_ms_base_headers%rowtype;
1422 l_msg_text iem_ms_msgbodys.value%type;
1423 l_ret_status varchar2(10);
1424 l_msg_data varchar2(1000);
1425 l_msg_count number;
1426 l_out_message_id number;
1427 l_top_intent iem_classifications.classification%type;
1428 insert_arch_dtl_error EXCEPTION;
1429 cursor c1 is select a.classification,b.score from
1430 iem_classifications a,iem_email_classifications b
1431 where b.message_id=p_message_id
1432 and a.classification_id=b.classification_id
1433 order by score asc;
1434 l_api_version_number number:=1.0;
1435 l_api_name varchar2(30):='ResolvedMessage';
1436 l_media_rec JTF_IH_PUB.media_rec_type;
1437 l_media_data JTF_IH_MEDIA_ITEMS%ROWTYPE;
1438 ERROR_CLOSING_MEDIA EXCEPTION;
1439 BEGIN
1440 -- Standard call to check for call compatibility.
1441 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1442 p_api_version_number,
1443 l_api_name,
1444 G_PKG_NAME)
1445 THEN
1446 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1447 END IF;
1448 SAVEPOINT select_data_PVT;
1449 x_return_status := FND_API.G_RET_STS_SUCCESS;
1450 select * into l_msg_rec from iem_rt_proc_emails
1451 where message_id=p_message_id;
1452 -- Close The Media Item
1453 BEGIN
1454 SELECT * into l_media_data
1455 FROM JTF_IH_MEDIA_ITEMS
1456 WHERE MEDIA_ID=l_msg_rec.ih_media_item_id;
1457 l_media_rec.media_id := l_media_data.media_id;
1458 l_media_rec.source_id := l_media_data.source_id;
1459 l_media_rec.direction:= l_media_data.direction;
1460 l_media_rec.start_date_time := l_media_data.start_date_time;
1461 l_media_rec.media_item_type := l_media_data.media_item_type;
1462 l_media_rec.media_item_ref := l_media_data.media_item_ref;
1463 l_media_rec.media_data := l_media_data.media_data;
1464 JTF_IH_PUB.Close_MediaItem( 1.0,
1465 'T',
1466 'F',
1467 TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
1468 TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
1469 nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
1470 TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
1471 l_ret_status,
1472 l_msg_count,
1473 l_msg_data,
1474 l_media_rec);
1475 EXCEPTION WHEN OTHERS THEN
1476 raise ERROR_CLOSING_MEDIA;
1477 END ;
1478 select * into l_header_rec from iem_ms_base_headers
1479 where message_id=p_message_id;
1480 select value into l_msg_text from iem_ms_msgbodys
1481 where message_id=p_message_id and rownum=1;
1482 for v1 in c1 loop
1483 l_top_intent:=v1.classification;
1484 exit;
1485 end loop;
1486 -- Insert Record into IEM_ARCH_MSG_DTLS
1487 IEM_ARCH_MSGDTLS_PVT.create_item(
1488 P_API_VERSION_NUMBER=>1.0,
1489 P_INIT_MSG_LIST=>'F',
1490 P_COMMIT=>'F',
1491 P_message_id=>p_message_id,
1492 p_inbound_message_id=>null,
1493 P_EMAIL_ACCOUNT_ID=>l_msg_rec.email_account_id,
1494 P_MAILPROC_STATUS=>p_action_flag,
1495 P_RT_CLASSIFICATION_ID=>l_msg_rec.rt_classification_id,
1496 P_MAIL_TYPE=>0,
1497 P_FROM_STR=>l_header_rec.from_str,
1498 P_REPLY_TO_STR=>l_header_rec.reply_to_str,
1499 P_TO_STR=>l_header_rec.to_str,
1500 P_CC_STR=>l_header_rec.cc_str,
1501 P_BCC_STR=>null,
1502 P_SENT_DATE=>l_msg_rec.sent_date,
1503 P_RECEIVED_DATE=>l_msg_rec.received_date,
1504 P_SUBJECT=>l_msg_rec.subject,
1505 P_AGENT_ID=>l_msg_rec.resource_id,
1509 P_MESSAGE_SIZE=>null,
1506 P_GROUP_ID=>l_msg_rec.group_id,
1507 P_IH_MEDIA_ITEM_ID=>l_msg_rec.ih_media_item_id,
1508 P_CUSTOMER_ID=>l_msg_rec.customer_id,
1510 P_CONTACT_ID=>l_msg_rec.contact_id,
1511 P_RELATIONSHIP_ID=>l_msg_rec.relationship_id,
1512 P_TOP_INTENT=>l_top_intent,
1513 P_MESSAGE_TEXT=>l_msg_text,
1514 p_ATTRIBUTE1 =>null,
1515 p_ATTRIBUTE2 =>null,
1516 p_ATTRIBUTE3 =>null,
1517 p_ATTRIBUTE4 =>null,
1518 p_ATTRIBUTE5 =>null,
1519 p_ATTRIBUTE6 =>null,
1520 p_ATTRIBUTE7 =>null,
1521 p_ATTRIBUTE8 =>null,
1522 p_ATTRIBUTE9 =>null,
1523 p_ATTRIBUTE10 =>null,
1524 p_ATTRIBUTE11 =>null,
1525 p_ATTRIBUTE12 =>null,
1526 p_ATTRIBUTE13 =>null,
1527 p_ATTRIBUTE14 =>null,
1528 p_ATTRIBUTE15 =>null,
1529 x_message_id=>l_out_message_id,
1530 X_RETURN_STATUS=>l_ret_status,
1531 X_MSG_COUNT=>l_msg_count,
1532 X_MSG_DATA=>l_msg_data);
1533 IF l_ret_status<>'S' THEN
1534 raise insert_arch_dtl_error;
1535 END IF;
1536 -- Delete All RUN TIME DATA and MESSAGE DATA FROM PRIMARY STORE
1537 delete from iem_rt_proc_emails where message_id=p_message_id;
1538 delete from iem_email_classifications where message_id=p_message_id;
1539 delete from iem_kb_results where message_id=p_message_id;
1540 delete from iem_ms_base_headers where message_id=p_message_id;
1541 delete from iem_ms_msgbodys where message_id=p_message_id;
1542 delete from iem_ms_msgparts where message_id=p_message_id;
1543 delete from iem_ms_exthdrs where message_id=p_message_id;
1544
1545 -- Insert the MIME Message into Archived Message Stores
1546 insert into iem_arch_msgs(message_id,message_content,created_by,creation_date,last_updated_by,last_update_date,last_update_login)
1547 (
1548 select message_id,mime_msg,created_by,creation_date,last_updated_by,last_update_date,last_update_login from iem_ms_mimemsgs where message_id=p_message_id and draft_flag=0);
1549 delete from iem_ms_mimemsgs where message_id=p_message_id;
1550 EXCEPTION
1551 WHEN NO_DATA_FOUND THEN
1552 ROLLBACK TO resolve_data_pvt;
1553 x_return_status := 'N';
1554 FND_MESSAGE.SET_NAME('IEM', 'IEM_NO_DATA');
1555 FND_MSG_PUB.ADD;
1556 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1557 WHEN FND_API.G_EXC_ERROR THEN
1558 ROLLBACK TO resolve_data_pvt;
1559 x_return_status := FND_API.G_RET_STS_ERROR ;
1560 FND_MSG_PUB.Count_And_Get
1561 ( p_count => x_msg_count,
1562 p_data => x_msg_data
1563 );
1564 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1565 ROLLBACK TO resolve_data_pvt;
1566 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1567 FND_MSG_PUB.Count_And_Get
1568 ( p_count => x_msg_count,
1569 p_data => x_msg_data
1570 );
1571 WHEN ERROR_CLOSING_MEDIA THEN
1572 ROLLBACK TO resolve_data_pvt;
1573 x_return_status := FND_API.G_RET_STS_ERROR;
1574 WHEN OTHERS THEN
1575 ROLLBACK TO resolve_data_pvt;
1576 x_return_status := FND_API.G_RET_STS_ERROR;
1577 IF FND_MSG_PUB.Check_Msg_Level
1578 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1579 THEN
1580 FND_MSG_PUB.Add_Exc_Msg
1581 ( G_PKG_NAME ,
1582 l_api_name
1583 );
1584 END IF;
1585 FND_MSG_PUB.Count_And_Get
1586 ( p_count => x_msg_count ,
1587 p_data => x_msg_data
1588 );
1589 end ResolvedMessage;
1590 end IEM_MAILITEM_PUB ;