[Home] [Help]
PACKAGE BODY: APPS.IEM_MAILITEM_PUB
Source
1 package body IEM_MAILITEM_PUB as
2 /* $Header: iemclntb.pls 120.8.12020000.3 2013/01/23 17:40:28 lkullamb 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;
402
403 -- Standard Check Of p_commit.
404 IF p_commit='T' THEN
405 COMMIT WORK;
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
550 );
551 WHEN OTHERS THEN
552 ROLLBACK TO select_item_PVT;
553 x_return_status := FND_API.G_RET_STS_ERROR;
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'
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;
698 l_interaction_rec.resource_id:=p_resource_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);
825
826 /*
827 delete from iem_encrypted_tags
828 where message_id=p_message_id;
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,
971 G_PKG_NAME)
972 THEN
973 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
974 END IF;
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
1112 ( p_count => x_msg_count,
1113 p_data => x_msg_data
1114 );
1115 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
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
1183 -- Fix for bug 10417273 - sanjana rao,mandating to_date function to use English while
1184 -- converting the date, in order by and delect clause
1185 IF p_sort_by = FND_API.G_MISS_CHAR OR p_sort_by='D' THEN
1186 l_order_by:=' Order BY to_date(substr(a.sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'',''NLS_DATE_LANGUAGE=ENGLISH'') '||l_sort_order;
1187 ELSIF p_sort_by='S' THEN
1188 l_order_by:='ORDER BY a.subject '||l_sort_order||',to_date(substr(a.sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'',''NLS_DATE_LANGUAGE=ENGLISH'') '||l_sort_order;
1189 ELSIF p_sort_by='C' THEN
1190 l_order_by:='ORDER BY c.NAME '||l_sort_order||',to_date(substr(a.sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'',''NLS_DATE_LANGUAGE=ENGLISH'') '||l_sort_order;
1191 ELSIF p_sort_by='F' THEN
1192 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'',''NLS_DATE_LANGUAGE=ENGLISH'') '||l_sort_order;
1193 ELSIF p_sort_by='T' THEN
1194 l_order_by:='ORDER BY d.description '||l_sort_order||',to_date(substr(a.sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'',''NLS_DATE_LANGUAGE=ENGLISH'') '||l_sort_order;
1195 ELSIF p_sort_by='R' THEN
1196 l_order_by:='ORDER BY read_status '||l_sort_order||',to_date(substr(a.sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'',''NLS_DATE_LANGUAGE=ENGLISH'') '||l_sort_order;
1197 END IF;
1198
1199 x_total_message:=0;
1200 OPEN email_cur FOR
1201 'SELECT a.message_id,a.rt_classification_id,c.name,b.rt_media_item_id,
1202 b.rt_interaction_id,
1203 a.email_account_id,a.message_flag,a.from_address,a.subject,a.priority,a.msg_status,
1204 to_char(to_date(substr(a.sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'',''NLS_DATE_LANGUAGE=ENGLISH''),''MM/DD/RRRR HH24:MI:SS''),a.mail_item_status,
1205 -- 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,
1206 a.from_resource_id,
1207 decode(a.mail_item_status,''R'',1,''S'',1,0) read_status,d.description
1208 FROM iem_rt_proc_emails a,
1209 IEM_RT_MEDIA_ITEMS b,
1210 IEM_ROUTE_CLASSIFICATIONS c,
1211 FND_LOOKUPS d
1212 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
1213 substr(a.mail_item_status,1,1)=d.lookup_code and d.lookup_type=:status_type '||l_order_by
1214 using l_expire,p_resource_id,p_email_account_id,l_status_type;
1215 l_temp_tbl.delete;
1216 l_counter:=1;
1217 LOOP
1218 FETCH email_cur INTO l_temp_tbl(l_counter);
1219 EXIT WHEN email_cur%NOTFOUND;
1220 l_counter:=l_counter+1;
1221 END LOOP;
1222 CLOSE email_cur;
1223 IF l_temp_tbl.count>0 THEN -- Data Selected Now implement Display Logic
1224 x_total_message:=l_temp_tbl.count;
1225 IF p_display_size=FND_API.G_MISS_NUM THEN
1226 x_acq_email_data:=l_temp_tbl; -- Return all data
1227 --incase of null display size
1228 ELSE
1229 IF p_page_count<>FND_API.G_MISS_NUM THEN
1230 l_first_index:=p_page_count*p_display_size - p_display_size+1;
1231 l_last_index:=p_page_count*p_display_size;
1232 ELSIF p_page_count=FND_API.G_MISS_NUM THEN
1233 l_first_index:=1;
1234 l_last_index:=p_display_size;
1235 END IF;
1236 IF l_last_index > x_total_message THEN
1237 l_last_index:=x_total_message;
1238 END IF;
1239 FOR l_index in l_first_index..l_last_index LOOP
1240 x_acq_email_data(l_index):=l_temp_tbl(l_index);
1241 END LOOP;
1242 END IF;
1243 END IF;
1244 IF p_commit='T' THEN
1245 COMMIT WORK;
1246 END IF;
1247
1248 -- Standard callto get message count and if count is 1, get message info.
1249 FND_MSG_PUB.Count_And_Get
1250 ( p_count => x_msg_count,
1251 p_data => x_msg_data
1252 );
1253 EXCEPTION
1254 WHEN FND_API.G_EXC_ERROR THEN
1255 ROLLBACK TO getemailheaders_pvt;
1256 x_return_status := FND_API.G_RET_STS_ERROR ;
1257 FND_MSG_PUB.Count_And_Get
1258 ( p_count => x_msg_count,
1259 p_data => x_msg_data
1260 );
1261 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1262 ROLLBACK TO getemailheaders_pvt;
1263 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1264 FND_MSG_PUB.Count_And_Get
1265 ( p_count => x_msg_count,
1266 p_data => x_msg_data
1267 );
1268 WHEN OTHERS THEN
1269 ROLLBACK TO getemailheaders_pvt;
1270 x_return_status := FND_API.G_RET_STS_ERROR;
1271 IF FND_MSG_PUB.Check_Msg_Level
1272 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1273 THEN
1274 FND_MSG_PUB.Add_Exc_Msg
1275 ( G_PKG_NAME ,
1276 l_api_name
1277 );
1278 END IF;
1279 FND_MSG_PUB.Count_And_Get
1280 ( p_count => x_msg_count ,
1281 p_data => x_msg_data
1282 );
1283 END getEmailHeaders;
1284 -- 12.1.3 development Cherry Picking
1285 PROCEDURE getUnreadEmailHeaders(p_api_version_number IN NUMBER,
1286 p_init_msg_list IN VARCHAR2 ,
1287 p_commit IN VARCHAR2 ,
1288 p_resource_id IN number,
1289 p_email_account_id IN number,
1290 p_display_size IN NUMBER,
1291 p_page_count IN NUMBER,
1292 p_sort_by IN VARCHAR2,
1293 p_sort_order IN number,
1294 x_total_message OUT NOCOPY number,
1295 x_queue_email_data OUT NOCOPY queue_email_info_tbl,
1296 x_return_status OUT NOCOPY VARCHAR2,
1297 x_msg_count OUT NOCOPY NUMBER,
1298 x_msg_data OUT NOCOPY VARCHAR2) IS
1299
1300 l_api_name VARCHAR2(255):='getUnreadEmailHeaders';
1301 l_api_version_number NUMBER:=1.0;
1302 Type get_data is REF CURSOR;-- RETURN queue_email_info_tbl;
1303 email_cur get_data;
1304 l_counter number:=0;
1305 l_order_by varchar2(255);
1306 l_sort_order varchar2(100);
1307 l_where varchar2(255);
1308 l_temp_tbl queue_email_info_tbl;
1309 l_start_index number:=0;
1310 l_first_index number:=0;
1311 l_last_index number:=0;
1312 l_expire varchar2(1):='N';
1313 l_status_type varchar2(40):='IEM_MESSAGE_STATUS_TYPE';
1314 l_all_groups varchar2(40):=FND_MESSAGE.GET_STRING('IEM', 'IEM_ALL_GROUPS');
1315 l_service_request varchar2(40):=FND_MESSAGE.GET_STRING('IEM','IEM_SERVICE_REQUEST');
1316 l_sr_id varchar2(40):='IEMNBZTSRVSRID';
1317
1318 BEGIN
1319 -- Standard call to check for call compatibility.
1320 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1321 p_api_version_number,
1322 l_api_name,
1323 G_PKG_NAME)
1324 THEN
1325 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1326 END IF;
1327 SAVEPOINT getunreademailheaders_pvt;
1328 x_return_status := FND_API.G_RET_STS_SUCCESS;
1329
1330
1331 select decode(p_sort_order,0,'ASC','DESC')
1332 into l_sort_order
1333 from dual;
1334
1335 IF p_sort_by = FND_API.G_MISS_CHAR OR p_sort_by='D' THEN
1336 l_order_by:=' Order BY to_date(substr(a.sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'',''NLS_DATE_LANGUAGE=ENGLISH'') '||l_sort_order;
1337 ELSIF p_sort_by='S' THEN
1338 l_order_by:='ORDER BY a.subject '||l_sort_order||',to_date(substr(a.sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'',''NLS_DATE_LANGUAGE=ENGLISH'') '||l_sort_order;
1339 ELSIF p_sort_by='CL' THEN
1340 l_order_by:='ORDER BY c.NAME '||l_sort_order||',to_date(substr(a.sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'',''NLS_DATE_LANGUAGE=ENGLISH'') '||l_sort_order;
1341 ELSIF p_sort_by='F' THEN
1342 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'',''NLS_DATE_LANGUAGE=ENGLISH'') '||l_sort_order;
1343 ELSIF p_sort_by='C' THEN
1344 l_order_by:='ORDER BY p.party_name '||l_sort_order||',to_date(substr(a.sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'',''NLS_DATE_LANGUAGE=ENGLISH'') '||l_sort_order;
1345 ELSIF p_sort_by='RG' THEN
1346 l_order_by:='ORDER BY group_name '||l_sort_order||',to_date(substr(a.sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'',''NLS_DATE_LANGUAGE=ENGLISH'') '||l_sort_order;
1347 ELSIF p_sort_by='SO' THEN
1348 l_order_by:='ORDER BY source '||l_sort_order||',to_date(substr(a.sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'',''NLS_DATE_LANGUAGE=ENGLISH'') '||l_sort_order;
1349 ELSIF p_sort_by='NU' THEN
1350 l_order_by:='ORDER BY source_number '||l_sort_order||',to_date(substr(a.sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'',''NLS_DATE_LANGUAGE=ENGLISH'') '||l_sort_order;
1351 END IF;
1352 x_total_message:=0;
1353 OPEN email_cur FOR
1354 --siahmed removed the gorup_name and replaced it with decode for
1355 --for bug fix 1355374
1356 --replace(a.group_id, a.group_id, :all_groups) as group_name,
1357 -- sardas , fix for bug14379811, added where clause to exclude requeued messages by this user
1358
1359 'SELECT a.message_id,a.rt_classification_id,c.name,
1360 a.email_account_id,a.from_address,a.subject,
1361 to_char(to_date(substr(a.sent_Date,1,20),''DD-MON-YYYY HH24:MI:SS'',''NLS_DATE_LANGUAGE=ENGLISH''),''MM/DD/RRRR HH24:MI:SS''),
1362 a.from_resource_id,
1363 p.party_name, p.party_id, a.contact_id,
1364 -- replace(a.group_id, a.group_id, :all_groups) as group_name,
1365 decode(a.group_id, 0, :all_groups, ( select group_name from jtf_rs_groups_tl where language=userenv(''LANG'') and group_id= a.group_id) ) as group_name,
1366 decode( (SELECT decode(DTLS.value,null,0,DTLS.value) FROM IEM_ENCRYPTED_TAG_DTLS DTLS, IEM_ENCRYPTED_TAGS TAG
1367 WHERE DTLS.key = :sr_id
1368 and DTLS.encrypted_id = TAG.encrypted_id
1369 and TAG.message_id = a.message_id),null,null,:l_service_request) source,
1370 (SELECT incident_number FROM CS_INCIDENTS_ALL_B WHERE incident_id =
1371 (SELECT decode(DTLS.value,null,0,DTLS.value) FROM IEM_ENCRYPTED_TAG_DTLS DTLS, IEM_ENCRYPTED_TAGS TAG
1372 WHERE DTLS.key = ''IEMNBZTSRVSRID''
1373 and DTLS.encrypted_id = TAG.encrypted_id
1374 and TAG.message_id = a.message_id)
1375 )source_number
1376 from iem_rt_proc_emails a , IEM_ROUTE_CLASSIFICATIONS c,
1377 FND_LOOKUPS d , HZ_PARTIES p
1378 where a.RT_CLASSIFICATION_ID=c.ROUTE_CLASSIFICATION_ID
1379 and a.resource_id=0
1380 and a.email_account_id=:account_id
1381 and substr(a.mail_item_status,1,1)=d.lookup_code
1382 and d.lookup_type=:status_type
1383 and a.customer_id = p.party_id (+)
1384 AND a.message_id NOT IN
1385 (SELECT message_id
1386 FROM iem_reroute_hists
1387 WHERE agent_id =:resource_id)
1388 AND((a.group_id, a.resource_id) IN
1389 (SELECT group_id, resource_id
1390 FROM jtf_rs_group_members_vl
1391 WHERE delete_flag <> ''Y'') OR a.group_id = 0)
1392 ' ||l_order_by
1393 using l_all_groups,l_sr_id,l_service_request,p_email_account_id,l_status_type,p_resource_id;
1394 l_temp_tbl.delete;
1395 l_counter:=1;
1396 LOOP
1397 FETCH email_cur INTO l_temp_tbl(l_counter);
1398 EXIT WHEN email_cur%NOTFOUND;
1399 l_counter:=l_counter+1;
1400 END LOOP;
1401 CLOSE email_cur;
1402 IF l_temp_tbl.count>0 THEN -- Data Selected Now implement Display Logic
1403 x_total_message:=l_temp_tbl.count;
1404 IF p_display_size=FND_API.G_MISS_NUM THEN
1405
1406 x_queue_email_data:=l_temp_tbl; -- Return all data
1407 --incase of null display size
1408 ELSE
1409
1410 IF p_page_count<>FND_API.G_MISS_NUM THEN
1411 l_first_index:=p_page_count*p_display_size - p_display_size+1;
1412 l_last_index:=p_page_count*p_display_size;
1413 ELSIF p_page_count=FND_API.G_MISS_NUM THEN
1414 l_first_index:=1;
1415 l_last_index:=p_display_size;
1416 END IF;
1417 IF l_last_index > x_total_message THEN
1418 l_last_index:=x_total_message;
1419 END IF;
1420 FOR l_index in l_first_index..l_last_index LOOP
1421 x_queue_email_data(l_index):=l_temp_tbl(l_index);
1422 END LOOP;
1423 END IF;
1424 END IF;
1425
1426 IF p_commit='T' THEN
1427 COMMIT WORK;
1428 END IF;
1429 -- Standard callto get message count and if count is 1, get message info.
1430 FND_MSG_PUB.Count_And_Get
1431 ( p_count => x_msg_count,
1432 p_data => x_msg_data
1433 );
1434 EXCEPTION
1435 WHEN FND_API.G_EXC_ERROR THEN
1436 ROLLBACK TO getunreademailheaders_pvt;
1437 x_return_status := FND_API.G_RET_STS_ERROR ;
1438 FND_MSG_PUB.Count_And_Get
1439 ( p_count => x_msg_count,
1440 p_data => x_msg_data
1441 );
1442 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1443 ROLLBACK TO getunreademailheaders_pvt;
1444 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1445 FND_MSG_PUB.Count_And_Get
1446 ( p_count => x_msg_count,
1447 p_data => x_msg_data
1448 );
1449 WHEN OTHERS THEN
1450 ROLLBACK TO getunreademailheaders_pvt;
1451 x_return_status := FND_API.G_RET_STS_ERROR;
1452 IF FND_MSG_PUB.Check_Msg_Level
1453 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1454 THEN
1455 FND_MSG_PUB.Add_Exc_Msg
1456 ( G_PKG_NAME ,
1457 l_api_name
1458 );
1459 END IF;
1460 FND_MSG_PUB.Count_And_Get
1461 ( p_count => x_msg_count ,
1462 p_data => x_msg_data
1463 );
1464 END getUnreadEmailHeaders;
1465 -- End 12.1.3
1466 PROCEDURE GetQueueItemData (p_api_version_number IN NUMBER,
1467 p_init_msg_list IN VARCHAR2 ,
1468 p_commit IN VARCHAR2 ,
1469 p_message_id in number,
1470 p_from_agent_id in number,
1471 p_to_agent_id in number,
1472 p_mail_item_status in varchar2,
1473 x_email_data out NOCOPY iem_rt_proc_emails%rowtype,
1474 x_tag_key_value OUT NOCOPY keyVals_tbl_type,
1475 x_encrypted_id OUT NOCOPY VARCHAR2,
1476 x_return_status OUT NOCOPY VARCHAR2,
1477 x_msg_count OUT NOCOPY NUMBER,
1478 x_msg_data OUT NOCOPY VARCHAR2) IS
1479 l_api_version_number number:=1.0;
1480 l_api_name varchar2(30):='GetQueueItemData';
1481 l_tag_key_value IEM_TAGPROCESS_PUB.keyVals_tbl_type;
1482 l_msg_count number;
1483 l_msg_data varchar2(500);
1484 l_ret_status varchar2(50);
1485 l_encrypted_id varchar2(500);
1486 l_index number;
1487 BEGIN
1488 -- Standard call to check for call compatibility.
1489 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1490 p_api_version_number,
1491 l_api_name,
1492 G_PKG_NAME)
1493 THEN
1494 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1495 END IF;
1496 SAVEPOINT select_data_PVT;
1497 x_return_status := FND_API.G_RET_STS_SUCCESS;
1498 SELECT * INTO x_email_data
1499 FROM iem_rt_proc_emails
1500 WHERE message_id=p_message_id
1501 AND resource_id=0 for update;
1502 update iem_rt_proc_emails
1503 set from_resource_id=p_from_agent_id,
1504 resource_id=p_to_agent_id,
1505 mail_item_status=p_mail_item_status
1506 where message_id=p_message_id;
1507 l_tag_key_value.delete;
1508 IEM_TAGPROCESS_PUB.getTagValues_on_MsgId(
1509 P_Api_Version_Number=>1.0,
1510 p_message_id => x_email_data.message_id,
1511 x_key_value=>l_tag_key_value,
1512 x_encrypted_id=>l_encrypted_id,
1513 x_msg_count=>l_msg_count,
1514 x_return_status=>l_ret_status,
1515 x_msg_data =>l_msg_data);
1516 l_index:=1;
1517 IF l_tag_key_value.count>0 THEN
1518 x_encrypted_id:=l_encrypted_id;
1519 FOR i in l_tag_key_value.FIRST..l_tag_key_value.LAST LOOP
1520 x_tag_key_value(l_index).key:=l_tag_key_value(i).key;
1521 x_tag_key_value(l_index).value:=l_tag_key_value(i).value;
1522 x_tag_key_value(l_index).datatype:=l_tag_key_value(i).datatype;
1523 l_index:=l_index+1;
1524 END LOOP;
1525 END IF;
1526 IF p_commit='T' THEN
1527 COMMIT WORK;
1528 END IF;
1529 -- Standard callto get message count and if count is 1, get message info.
1530 FND_MSG_PUB.Count_And_Get
1531 ( p_count => x_msg_count,
1532 p_data => x_msg_data
1533 );
1534 EXCEPTION
1535 WHEN NO_DATA_FOUND THEN
1536 ROLLBACK TO select_data_PVT;
1537 x_return_status := 'N';
1538 FND_MESSAGE.SET_NAME('IEM', 'IEM_NO_DATA');
1539 FND_MSG_PUB.ADD;
1540 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1541 WHEN FND_API.G_EXC_ERROR THEN
1542 ROLLBACK TO select_data_PVT;
1543 x_return_status := FND_API.G_RET_STS_ERROR ;
1544 FND_MSG_PUB.Count_And_Get
1545 ( p_count => x_msg_count,
1546 p_data => x_msg_data
1547 );
1548 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1549 ROLLBACK TO select_data_PVT;
1550 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1551 FND_MSG_PUB.Count_And_Get
1552 ( p_count => x_msg_count,
1553 p_data => x_msg_data
1554 );
1555 WHEN OTHERS THEN
1556 ROLLBACK TO select_data_PVT;
1557 x_return_status := FND_API.G_RET_STS_ERROR;
1558 IF FND_MSG_PUB.Check_Msg_Level
1559 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1560 THEN
1561 FND_MSG_PUB.Add_Exc_Msg
1562 ( G_PKG_NAME ,
1563 l_api_name
1564 );
1565 END IF;
1566 FND_MSG_PUB.Count_And_Get
1567 ( p_count => x_msg_count ,
1568 p_data => x_msg_data
1569 );
1570 end GetQueueItemData;
1571 PROCEDURE GetMailItem (p_api_version_number IN NUMBER,
1572 p_init_msg_list IN VARCHAR2 ,
1573 p_commit IN VARCHAR2 ,
1574 p_resource_id in number,
1575 p_tbl in t_number_table:=NULL,
1576 p_rt_classification in number,
1577 p_account_id in number,
1578 x_email_data out NOCOPY iem_rt_proc_emails%rowtype,
1579 x_return_status OUT NOCOPY VARCHAR2,
1580 x_msg_count OUT NOCOPY NUMBER,
1581 x_msg_data OUT NOCOPY VARCHAR2) IS
1582 begin
1583 null;
1584 end GetMailitem;
1585
1586 PROCEDURE GetMailItem(p_api_version_number IN NUMBER,
1587 p_init_msg_list IN VARCHAR2 ,
1588 p_commit IN VARCHAR2 ,
1589 p_resource_id in number,
1590 p_acct_rt_class_id in number,
1591 x_email_data out NOCOPY iem_rt_proc_emails%rowtype,
1592 x_return_status OUT NOCOPY VARCHAR2,
1593 x_msg_count OUT NOCOPY NUMBER,
1594 x_msg_data OUT NOCOPY VARCHAR2) IS
1595 begin
1596 null;
1597 end;
1598 PROCEDURE ResolvedMessage (p_api_version_number IN NUMBER,
1599 p_init_msg_list IN VARCHAR2 ,
1600 p_commit IN VARCHAR2 ,
1601 p_message_id in number,
1602 p_action_flag in varchar2,
1603 x_return_status OUT NOCOPY VARCHAR2,
1604 x_msg_count OUT NOCOPY NUMBER,
1605 x_msg_data OUT NOCOPY VARCHAR2) IS
1606 l_msg_rec iem_rt_proc_emails%rowtype;
1607 l_header_rec iem_ms_base_headers%rowtype;
1608 l_msg_text iem_ms_msgbodys.value%type;
1609 l_ret_status varchar2(10);
1610 l_msg_data varchar2(1000);
1611 l_msg_count number;
1612 l_out_message_id number;
1613 l_top_intent iem_classifications.classification%type;
1614 insert_arch_dtl_error EXCEPTION;
1615 cursor c1 is select a.classification,b.score from
1616 iem_classifications a,iem_email_classifications b
1617 where b.message_id=p_message_id
1618 and a.classification_id=b.classification_id
1619 order by score asc;
1620 l_api_version_number number:=1.0;
1621 l_api_name varchar2(30):='ResolvedMessage';
1622 l_media_rec JTF_IH_PUB.media_rec_type;
1623 l_media_data JTF_IH_MEDIA_ITEMS%ROWTYPE;
1624 ERROR_CLOSING_MEDIA EXCEPTION;
1625 BEGIN
1626 -- Standard call to check for call compatibility.
1627 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1628 p_api_version_number,
1629 l_api_name,
1630 G_PKG_NAME)
1631 THEN
1632 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1633 END IF;
1634 SAVEPOINT select_data_PVT;
1635 x_return_status := FND_API.G_RET_STS_SUCCESS;
1636 select * into l_msg_rec from iem_rt_proc_emails
1637 where message_id=p_message_id;
1638 -- Close The Media Item
1639 BEGIN
1640 SELECT * into l_media_data
1641 FROM JTF_IH_MEDIA_ITEMS
1642 WHERE MEDIA_ID=l_msg_rec.ih_media_item_id;
1643 l_media_rec.media_id := l_media_data.media_id;
1644 l_media_rec.source_id := l_media_data.source_id;
1645 l_media_rec.direction:= l_media_data.direction;
1646 l_media_rec.start_date_time := l_media_data.start_date_time;
1647 l_media_rec.media_item_type := l_media_data.media_item_type;
1648 l_media_rec.media_item_ref := l_media_data.media_item_ref;
1649 l_media_rec.media_data := l_media_data.media_data;
1650 JTF_IH_PUB.Close_MediaItem( 1.0,
1651 'T',
1652 'F',
1653 TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
1654 TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
1655 nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
1656 TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
1657 l_ret_status,
1658 l_msg_count,
1659 l_msg_data,
1660 l_media_rec);
1661 EXCEPTION WHEN OTHERS THEN
1662 raise ERROR_CLOSING_MEDIA;
1663 END ;
1664 select * into l_header_rec from iem_ms_base_headers
1665 where message_id=p_message_id;
1666 select value into l_msg_text from iem_ms_msgbodys
1667 where message_id=p_message_id and rownum=1;
1668 for v1 in c1 loop
1669 l_top_intent:=v1.classification;
1670 exit;
1671 end loop;
1672 -- Insert Record into IEM_ARCH_MSG_DTLS
1673 IEM_ARCH_MSGDTLS_PVT.create_item(
1674 P_API_VERSION_NUMBER=>1.0,
1675 P_INIT_MSG_LIST=>'F',
1676 P_COMMIT=>'F',
1677 P_message_id=>p_message_id,
1678 p_inbound_message_id=>null,
1679 P_EMAIL_ACCOUNT_ID=>l_msg_rec.email_account_id,
1680 P_MAILPROC_STATUS=>p_action_flag,
1681 P_RT_CLASSIFICATION_ID=>l_msg_rec.rt_classification_id,
1682 P_MAIL_TYPE=>0,
1683 P_FROM_STR=>l_header_rec.from_str,
1684 P_REPLY_TO_STR=>l_header_rec.reply_to_str,
1685 P_TO_STR=>l_header_rec.to_str,
1686 P_CC_STR=>l_header_rec.cc_str,
1687 P_BCC_STR=>null,
1688 P_SENT_DATE=>l_msg_rec.sent_date,
1689 P_RECEIVED_DATE=>l_msg_rec.received_date,
1690 P_SUBJECT=>l_msg_rec.subject,
1691 P_AGENT_ID=>l_msg_rec.resource_id,
1692 P_GROUP_ID=>l_msg_rec.group_id,
1693 P_IH_MEDIA_ITEM_ID=>l_msg_rec.ih_media_item_id,
1694 P_CUSTOMER_ID=>l_msg_rec.customer_id,
1695 P_MESSAGE_SIZE=>null,
1696 P_CONTACT_ID=>l_msg_rec.contact_id,
1697 P_RELATIONSHIP_ID=>l_msg_rec.relationship_id,
1698 P_TOP_INTENT=>l_top_intent,
1699 P_MESSAGE_TEXT=>l_msg_text,
1700 p_ATTRIBUTE1 =>null,
1701 p_ATTRIBUTE2 =>null,
1702 p_ATTRIBUTE3 =>null,
1703 p_ATTRIBUTE4 =>null,
1704 p_ATTRIBUTE5 =>null,
1705 p_ATTRIBUTE6 =>null,
1706 p_ATTRIBUTE7 =>null,
1707 p_ATTRIBUTE8 =>null,
1708 p_ATTRIBUTE9 =>null,
1709 p_ATTRIBUTE10 =>null,
1710 p_ATTRIBUTE11 =>null,
1711 p_ATTRIBUTE12 =>null,
1712 p_ATTRIBUTE13 =>null,
1713 p_ATTRIBUTE14 =>null,
1714 p_ATTRIBUTE15 =>null,
1715 x_message_id=>l_out_message_id,
1716 X_RETURN_STATUS=>l_ret_status,
1717 X_MSG_COUNT=>l_msg_count,
1718 X_MSG_DATA=>l_msg_data);
1719 IF l_ret_status<>'S' THEN
1720 raise insert_arch_dtl_error;
1721 END IF;
1722 -- Delete All RUN TIME DATA and MESSAGE DATA FROM PRIMARY STORE
1723 delete from iem_rt_proc_emails where message_id=p_message_id;
1724 delete from iem_email_classifications where message_id=p_message_id;
1725 delete from iem_kb_results where message_id=p_message_id;
1726 delete from iem_ms_base_headers where message_id=p_message_id;
1727 delete from iem_ms_msgbodys where message_id=p_message_id;
1728 delete from iem_ms_msgparts where message_id=p_message_id;
1729 delete from iem_ms_exthdrs where message_id=p_message_id;
1730
1731 -- Insert the MIME Message into Archived Message Stores
1732 insert into iem_arch_msgs(message_id,message_content,created_by,creation_date,last_updated_by,last_update_date,last_update_login)
1733 (
1734 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);
1735 delete from iem_ms_mimemsgs where message_id=p_message_id;
1736 EXCEPTION
1737 WHEN NO_DATA_FOUND THEN
1738 ROLLBACK TO resolve_data_pvt;
1739 x_return_status := 'N';
1740 FND_MESSAGE.SET_NAME('IEM', 'IEM_NO_DATA');
1741 FND_MSG_PUB.ADD;
1742 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1743 WHEN FND_API.G_EXC_ERROR THEN
1744 ROLLBACK TO resolve_data_pvt;
1745 x_return_status := FND_API.G_RET_STS_ERROR ;
1746 FND_MSG_PUB.Count_And_Get
1747 ( p_count => x_msg_count,
1748 p_data => x_msg_data
1749 );
1750 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1751 ROLLBACK TO resolve_data_pvt;
1752 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1753 FND_MSG_PUB.Count_And_Get
1754 ( p_count => x_msg_count,
1755 p_data => x_msg_data
1756 );
1757 WHEN ERROR_CLOSING_MEDIA THEN
1758 ROLLBACK TO resolve_data_pvt;
1759 x_return_status := FND_API.G_RET_STS_ERROR;
1760 WHEN OTHERS THEN
1761 ROLLBACK TO resolve_data_pvt;
1762 x_return_status := FND_API.G_RET_STS_ERROR;
1763 IF FND_MSG_PUB.Check_Msg_Level
1764 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1765 THEN
1766 FND_MSG_PUB.Add_Exc_Msg
1767 ( G_PKG_NAME ,
1768 l_api_name
1769 );
1770 END IF;
1771 FND_MSG_PUB.Count_And_Get
1772 ( p_count => x_msg_count ,
1773 p_data => x_msg_data
1774 );
1775 end ResolvedMessage;
1776 end IEM_MAILITEM_PUB ;