1 package body IEM_SPV_MONITORING_PVT as
2 /* $Header: iemvspmb.pls 120.3 2006/05/01 15:20:14 chtang ship $*/
3 G_PKG_NAME varchar2(100):='IEM_SPV_MONITORING_PVT';
4
5 PROCEDURE get_email_activity (p_api_version_number IN NUMBER,
6 p_init_msg_list IN VARCHAR2,
7 p_commit IN VARCHAR2,
8 x_email_activity_tbl OUT NOCOPY email_activity_tbl,
9 x_return_status OUT NOCOPY VARCHAR2,
10 x_msg_count OUT NOCOPY NUMBER,
11 x_msg_data OUT NOCOPY VARCHAR2) IS
12
13 l_api_name VARCHAR2(255):='get_email_activity';
14 l_api_version_number NUMBER:=1.0;
15 l_index number := 1;
16 l_monitor_index number := 1;
17 i number := 1;
18 l_agent_acct_count number;
19 l_count number;
20 l_queue_count number;
21 l_queue_wait_time number;
22 l_queue_average_time number;
23 l_inbox_count number;
24 l_inbox_wait_time number;
25 l_inbox_average_time number;
26 l_total_count number;
27 l_string varchar2(32767):='';
28 Type email_activity_rec is REF CURSOR ;
29 email_activity_cur email_activity_rec;
30
31 l_email_account_id_tbl jtf_number_table:=jtf_number_table() ;
32 l_account_name_tbl jtf_varchar2_table_100:=jtf_varchar2_table_100() ;
33 l_queue_count_tbl jtf_number_table:=jtf_number_table() ;
34 l_wait_time_tbl jtf_number_table:=jtf_number_table() ;
35 l_class_id_tbl jtf_number_table:=jtf_number_table() ;
36 l_class_name_tbl jtf_varchar2_table_100:=jtf_varchar2_table_100() ;
37
38 l_current_user NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
39
40 BEGIN
41
42 SAVEPOINT get_email_activity_pvt;
43 -- Standard call to check for call compatibility.
44 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
45 p_api_version_number,
46 l_api_name,
47 G_PKG_NAME)
48 THEN
49 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
50 END IF;
51 -- Initialize message list if p_init_msg_list is set to TRUE.
52 IF FND_API.to_Boolean( p_init_msg_list )
53 THEN
54 FND_MSG_PUB.initialize;
55 END IF;
56
57 -- Initialize API return status to SUCCESS
58 x_return_status := FND_API.G_RET_STS_SUCCESS;
59
60 select a.email_account_id, a.from_name bulk collect into l_email_account_id_tbl, l_account_name_tbl
61 from iem_mstemail_accounts a, iem_agents b, jtf_rs_resource_extns c
62 where a.email_account_id=b.email_account_id and b.resource_id = c.resource_id and c.user_id = l_current_user
63 order by UPPER(a.from_name);
64
65
66 for l_index in l_email_account_id_tbl.FIRST..l_email_account_id_tbl.LAST LOOP
67 -- Queue statistics
68 select Count(*) Total, nvl(max(sysdate-a.received_date)*24*60,0) wait_time,
69 nvl(avg(sysdate-a.received_date)*24*60,0) average_time
70 into l_queue_count, l_queue_wait_time, l_queue_average_time
71 FROM iem_rt_proc_emails a
72 WHERE a.resource_id = 0 and
73 a.email_account_id=l_email_account_id_tbl(l_index);
74
75 --Agent Inbox statistics
76 select Count(*) Total, nvl(max(sysdate-a.received_date)*24*60,0) wait_time,
77 nvl(avg(sysdate-a.received_date)*24*60,0) average_time
78 into l_inbox_count, l_inbox_wait_time, l_inbox_average_time
79 FROM iem_rt_proc_emails a, iem_rt_media_items b
80 WHERE a.resource_id <> 0 and a.message_id=b.message_id and b.expire='N' and
81 a.email_account_id=l_email_account_id_tbl(l_index);
82
83 select count(*) into l_agent_acct_count from iem_agents where email_account_id=l_email_account_id_tbl(l_index);
84
85 select count(*) into l_total_count from iem_rt_proc_emails where email_account_id=l_email_account_id_tbl(l_index);
86
87 x_email_activity_tbl(l_monitor_index).email_account_id:=l_email_account_id_tbl(l_index);
88 x_email_activity_tbl(l_monitor_index).account_classification_name:=l_account_name_tbl(l_index);
89 x_email_activity_tbl(l_monitor_index).classification_id:=-1; -- All Classifications
90 x_email_activity_tbl(l_monitor_index).queue_count:=l_queue_count;
91 x_email_activity_tbl(l_monitor_index).queue_wait_time:=l_queue_wait_time;
92 x_email_activity_tbl(l_monitor_index).queue_average_time:=l_queue_average_time;
93 x_email_activity_tbl(l_monitor_index).inbox_count:=l_inbox_count;
94 x_email_activity_tbl(l_monitor_index).inbox_wait_time:=l_inbox_wait_time;
95 x_email_activity_tbl(l_monitor_index).inbox_average_time:=l_inbox_average_time;
96 x_email_activity_tbl(l_monitor_index).agent_count:=l_agent_acct_count;
97 x_email_activity_tbl(l_monitor_index).total_count:=l_total_count;
98
99 if (l_queue_count = 0) then
100 x_email_activity_tbl(l_monitor_index).queue_zero_flag:='true';
101 else
102 x_email_activity_tbl(l_monitor_index).queue_zero_flag:='false';
103 end if;
104 if (l_inbox_count = 0) then
105 x_email_activity_tbl(l_monitor_index).inbox_zero_flag:='true';
106 else
107 x_email_activity_tbl(l_monitor_index).inbox_zero_flag:='false';
108 end if;
109
110 l_monitor_index := l_monitor_index + 1;
111
112 select a.route_classification_id, a.name bulk collect into l_class_id_tbl, l_class_name_tbl
113 from iem_route_classifications a, iem_account_route_class b
114 where a.route_classification_id = b.route_classification_id and b.email_account_id=l_email_account_id_tbl(l_index) order by UPPER(a.name);
115
116 for i in l_class_id_tbl.FIRST..l_class_id_tbl.LAST LOOP
117 -- Queue statistics
118 select Count(*) Total, nvl(max(sysdate-a.received_date)*24*60,0) wait_time,
119 nvl(avg(sysdate-a.received_date)*24*60,0) average_time
120 into l_queue_count, l_queue_wait_time, l_queue_average_time
121 FROM iem_rt_proc_emails a,iem_mstemail_accounts b, iem_route_classifications c
122 WHERE a.email_account_id=b.email_account_id and a.rt_classification_id=c.route_classification_id
123 and a.resource_id=0 and a.email_account_id=l_email_account_id_tbl(l_index) and c.route_classification_id=l_class_id_tbl(i);
124
125 -- Agent Inbox statistics
126 select Count(*) Total, nvl(max(sysdate-a.received_date)*24*60,0) wait_time,
127 nvl(avg(sysdate-a.received_date)*24*60,0) average_time
128 into l_inbox_count, l_inbox_wait_time, l_inbox_average_time
129 FROM iem_rt_proc_emails a,iem_mstemail_accounts b, iem_route_classifications c, iem_rt_media_items d
130 WHERE a.email_account_id=b.email_account_id and a.rt_classification_id=c.route_classification_id
131 and a.resource_id<>0 and a.message_id=d.message_id and d.expire='N'
132 and a.email_account_id=l_email_account_id_tbl(l_index) and c.route_classification_id=l_class_id_tbl(i);
133
134 select count(*) into l_total_count from iem_rt_proc_emails where email_account_id=l_email_account_id_tbl(l_index)
135 and rt_classification_id=l_class_id_tbl(i);
136
137 x_email_activity_tbl(l_monitor_index).email_account_id:=l_email_account_id_tbl(l_index);
138 x_email_activity_tbl(l_monitor_index).account_classification_name:=l_class_name_tbl(i);
139 x_email_activity_tbl(l_monitor_index).classification_id:=l_class_id_tbl(i);
140 x_email_activity_tbl(l_monitor_index).queue_count:=l_queue_count;
141 x_email_activity_tbl(l_monitor_index).queue_wait_time:=l_queue_wait_time;
142 x_email_activity_tbl(l_monitor_index).queue_average_time:=l_queue_average_time;
143 x_email_activity_tbl(l_monitor_index).inbox_count:=l_inbox_count;
144 x_email_activity_tbl(l_monitor_index).inbox_wait_time:=l_inbox_wait_time;
145 x_email_activity_tbl(l_monitor_index).inbox_average_time:=l_inbox_average_time;
146 x_email_activity_tbl(l_monitor_index).agent_count:=-1; -- No Agent Count per classification
147 x_email_activity_tbl(l_monitor_index).total_count:=l_total_count;
148
149 if (l_queue_count = 0) then
150 x_email_activity_tbl(l_monitor_index).queue_zero_flag:='true';
151 else
152 x_email_activity_tbl(l_monitor_index).queue_zero_flag:='false';
153 end if;
154 if (l_inbox_count = 0) then
155 x_email_activity_tbl(l_monitor_index).inbox_zero_flag:='true';
156 else
157 x_email_activity_tbl(l_monitor_index).inbox_zero_flag:='false';
158 end if;
159
160 l_monitor_index := l_monitor_index + 1;
161 end loop;
162 end loop;
163
164 -- Standard Check Of p_commit.
165 IF FND_API.To_Boolean(p_commit) THEN
166 COMMIT WORK;
167 END IF;
168 -- Standard callto get message count and if count is 1, get message info.
169 FND_MSG_PUB.Count_And_Get
170 ( p_count => x_msg_count,
171 p_data => x_msg_data
172 );
173 EXCEPTION
174
175 WHEN FND_API.G_EXC_ERROR THEN
176 ROLLBACK TO get_email_activity_pvt;
177 x_return_status := FND_API.G_RET_STS_ERROR ;
178 FND_MSG_PUB.Count_And_Get
179 ( p_count => x_msg_count,
180 p_data => x_msg_data
181 );
182 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
183 ROLLBACK TO get_email_activity_pvt;
184 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
185 FND_MSG_PUB.Count_And_Get
186 ( p_count => x_msg_count,
187 p_data => x_msg_data
188 );
189 WHEN OTHERS THEN
190 ROLLBACK TO get_email_activity_pvt;
191 x_return_status := FND_API.G_RET_STS_ERROR;
192 IF FND_MSG_PUB.Check_Msg_Level
193 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
194 THEN
195 FND_MSG_PUB.Add_Exc_Msg
196 ( G_PKG_NAME,
197 l_api_name
198 );
199 END IF;
200 FND_MSG_PUB.Count_And_Get
201 (p_count => x_msg_count,
202 p_data => x_msg_data
203 );
204 END get_email_activity;
205
206 PROCEDURE get_agent_activity (p_api_version_number IN NUMBER,
207 p_init_msg_list IN VARCHAR2,
208 p_commit IN VARCHAR2,
209 p_resource_role IN NUMBER:=1,
210 p_resource_name IN VARCHAR2:=null,
211 x_agent_activity_tbl OUT NOCOPY agent_activity_tbl,
212 x_return_status OUT NOCOPY VARCHAR2,
213 x_msg_count OUT NOCOPY NUMBER,
214 x_msg_data OUT NOCOPY VARCHAR2) IS
215
216 l_api_name VARCHAR2(255):='get_agent_activity';
217 l_api_version_number NUMBER:=1.0;
218 l_index number := 1;
219 l_monitor_index number := 1;
220 i number := 1;
221 l_agent_acct_count number;
222 l_count number;
223 l_email_count number;
224 l_assigned_email_count number;
225 l_requeue_all_count number;
226 l_wait_time number;
227 l_average_time number;
228 l_total_count number;
229 l_last_login_time varchar2(500);
230 l_resource_role varchar2(30);
231 l_email_count_flag number;
232 l_string varchar2(32767):='';
233 l_where_clause varchar2(32767):='';
234 Type agent_activity_rec is REF CURSOR ;
235 agent_activity_cur agent_activity_rec;
236
237 l_resource_id_tbl jtf_number_table:=jtf_number_table() ;
238 l_resource_name_tbl jtf_varchar2_table_100:=jtf_varchar2_table_100() ;
239 l_email_count_tbl jtf_number_table:=jtf_number_table() ;
240 l_average_age_tbl jtf_number_table:=jtf_number_table() ;
241 l_oldest_age_tbl jtf_number_table:=jtf_number_table() ;
242 l_account_id_tbl jtf_number_table:=jtf_number_table() ;
243 l_account_name_tbl jtf_varchar2_table_100:=jtf_varchar2_table_100() ;
244
245 l_current_user NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
246
247 BEGIN
248
249 SAVEPOINT get_email_activity_pvt;
250 -- Standard call to check for call compatibility.
251 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
252 p_api_version_number,
253 l_api_name,
254 G_PKG_NAME)
255 THEN
256 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
257 END IF;
258 -- Initialize message list if p_init_msg_list is set to TRUE.
259 IF FND_API.to_Boolean( p_init_msg_list )
260 THEN
261 FND_MSG_PUB.initialize;
262 END IF;
263
264 -- Initialize API return status to SUCCESS
265 x_return_status := FND_API.G_RET_STS_SUCCESS;
266
267 if p_resource_role = 0 then -- All Agents
268 if (p_resource_name is not null) then
269 select unique res.resource_id,concat(concat(res.source_last_name, ', '), res.source_first_name) as resource_name
270 bulk collect into l_resource_id_tbl, l_resource_name_tbl from fnd_user_resp_groups respgrp,
271 jtf_rs_resource_extns res, fnd_responsibility resp where res.user_id=respgrp.user_id
272 and respgrp.responsibility_id=resp.responsibility_id and resp.application_id=680
273 and (resp.responsibility_key = 'EMAIL_CENTER_SUPERVISOR' or resp.responsibility_key = 'IEM_SA_AGENT')
274 and res.resource_id in ( select unique rel.role_resource_id from jtf_rs_role_relations rel where
275 rel.role_id in (28, 29, 30) and rel.delete_flag = 'N'
276 and rel.role_resource_type = 'RS_INDIVIDUAL'
277 -- and trunc(sysdate) between trunc(nvl(rel.start_date_active, sysdate))
278 -- and trunc(nvl(rel.end_date_active, sysdate))
279 )
280 and res.resource_id in (select resource_id from iem_agents)
281 and (upper(res.source_last_name) like upper(p_resource_name) or upper(res.source_first_name) like upper(p_resource_name)
282 or upper(res.user_name) like upper(p_resource_name))
283 order by resource_name;
284 else
285 select unique res.resource_id,concat(concat(res.source_last_name, ', '), res.source_first_name) as resource_name
286 bulk collect into l_resource_id_tbl, l_resource_name_tbl from fnd_user_resp_groups respgrp,
287 jtf_rs_resource_extns res, fnd_responsibility resp where res.user_id=respgrp.user_id
288 and respgrp.responsibility_id=resp.responsibility_id and resp.application_id=680
289 and (resp.responsibility_key = 'EMAIL_CENTER_SUPERVISOR' or resp.responsibility_key = 'IEM_SA_AGENT')
290 and res.resource_id in ( select unique rel.role_resource_id from jtf_rs_role_relations rel where
291 rel.role_id in (28, 29, 30) and rel.delete_flag = 'N'
292 and rel.role_resource_type = 'RS_INDIVIDUAL'
293 -- and trunc(sysdate) between trunc(nvl(rel.start_date_active, sysdate))
294 -- and trunc(nvl(rel.end_date_active, sysdate))
295 )
296 and res.resource_id in (select resource_id from iem_agents)
297 order by resource_name;
298 end if;
299 else
300 /* if p_resource_role = 2 then
301 l_resource_role := 23720; -- Supervisor
302 else
303 l_resource_role := 23107; -- Agent
304 end if;
305 */
306 if p_resource_role = 2 then
307 l_resource_role := 'EMAIL_CENTER_SUPERVISOR'; -- Supervisor
308 else
309 l_resource_role := 'IEM_SA_AGENT'; -- Agent
310 end if;
311
312 if (p_resource_name is not null) then
313 select res.resource_id,concat(concat(res.source_last_name, ', '), res.source_first_name) as resource_name
314 bulk collect into l_resource_id_tbl, l_resource_name_tbl from fnd_user_resp_groups respgrp,
315 jtf_rs_resource_extns res, fnd_responsibility resp where res.user_id=respgrp.user_id
316 and respgrp.responsibility_id=resp.responsibility_id and resp.application_id=680
317 and resp.responsibility_key = l_resource_role
318 and res.resource_id in ( select unique rel.role_resource_id from jtf_rs_role_relations rel where
319 rel.role_id in (28, 29, 30) and rel.delete_flag = 'N'
320 and rel.role_resource_type = 'RS_INDIVIDUAL'
321 and trunc(sysdate) between trunc(nvl(rel.start_date_active, sysdate))
322 and trunc(nvl(rel.end_date_active, sysdate)) )
323 and res.resource_id in (select resource_id from iem_agents)
324 and (upper(res.source_last_name) like upper(p_resource_name) or upper(res.source_first_name) like upper(p_resource_name)
325 or upper(res.user_name) like upper(p_resource_name))
326 order by resource_name;
327 else
328 select res.resource_id,concat(concat(res.source_last_name, ', '), res.source_first_name) as resource_name
329 bulk collect into l_resource_id_tbl, l_resource_name_tbl from fnd_user_resp_groups respgrp,
330 jtf_rs_resource_extns res, fnd_responsibility resp where res.user_id=respgrp.user_id
331 and respgrp.responsibility_id=resp.responsibility_id and resp.application_id=680
332 and resp.responsibility_key = l_resource_role
333 and res.resource_id in ( select unique rel.role_resource_id from jtf_rs_role_relations rel where
334 rel.role_id in (28, 29, 30) and rel.delete_flag = 'N'
335 and rel.role_resource_type = 'RS_INDIVIDUAL'
336 and trunc(sysdate) between trunc(nvl(rel.start_date_active, sysdate))
337 and trunc(nvl(rel.end_date_active, sysdate)) )
338 and res.resource_id in (select resource_id from iem_agents)
339 order by resource_name;
340 end if;
341 end if; -- if p_resource_role = 0
342
343 for l_index in l_resource_id_tbl.FIRST..l_resource_id_tbl.LAST LOOP
344 select Count(*) Total, nvl(max(sysdate-a.received_date)*24*60,0) wait_time,
345 nvl(avg(sysdate-a.received_date)*24*60,0) average_time
346 into l_email_count, l_wait_time, l_average_time
347 FROM iem_rt_proc_emails a, iem_rt_media_items b
348 WHERE a.message_id=b.message_id and b.expire='N'
349 and a.resource_id = l_resource_id_tbl(l_index);
350
351 select Count(*) Total into l_assigned_email_count
352 FROM iem_rt_proc_emails a, iem_rt_media_items b
353 WHERE a.message_id=b.message_id and b.expire='N'
354 and a.resource_id = l_resource_id_tbl(l_index) and a.email_account_id in
355 (select a.email_account_id from iem_agents a, jtf_rs_resource_extns b
356 where a.resource_id = b.resource_id and b.user_id=l_current_user);
357
358 select Count(*) Total into l_requeue_all_count
359 FROM iem_rt_proc_emails a, iem_rt_media_items b
360 WHERE a.message_id=b.message_id and b.expire='N'
361 and a.resource_id = l_resource_id_tbl(l_index) and a.email_account_id in
362 (select email_account_id from iem_agents c, jtf_rs_resource_extns d
363 where c.resource_id=d.resource_id and d.user_id=l_current_user);
364
365 select count(*) into l_agent_acct_count from iem_agents where resource_id=l_resource_id_tbl(l_index);
366
367 select to_char(max(begin_date_time), 'MM/DD/RRRR HH24:MI:SS') into l_last_login_time
368 from ieu_sh_sessions where application_id=680 and resource_id=l_resource_id_tbl(l_index);
369
370 x_agent_activity_tbl(l_monitor_index).resource_id:=l_resource_id_tbl(l_index);
371 x_agent_activity_tbl(l_monitor_index).resource_account_name:=l_resource_name_tbl(l_index);
372 x_agent_activity_tbl(l_monitor_index).email_account_id:=-1; -- All Accounts
373 x_agent_activity_tbl(l_monitor_index).email_count:=l_email_count;
374 x_agent_activity_tbl(l_monitor_index).assigned_email_count:=l_assigned_email_count;
375 x_agent_activity_tbl(l_monitor_index).oldest_age:=l_wait_time;
376 x_agent_activity_tbl(l_monitor_index).average_age:=l_average_time;
377 x_agent_activity_tbl(l_monitor_index).account_count:=l_agent_acct_count;
378 x_agent_activity_tbl(l_monitor_index).last_login_time:=l_last_login_time;
379
380 if (l_requeue_all_count = 0) then
381 x_agent_activity_tbl(l_monitor_index).requeue_all_flag:='false';
382 else
383 x_agent_activity_tbl(l_monitor_index).requeue_all_flag:='true';
384 end if;
385
386 l_monitor_index := l_monitor_index + 1;
387
388 select a.email_account_id, a.from_name bulk collect into l_account_id_tbl, l_account_name_tbl
389 from iem_mstemail_accounts a, iem_agents b
390 where a.email_account_id=b.email_account_id and b.resource_id=l_resource_id_tbl(l_index)
391 order by UPPER(a.from_name);
392
393 for i in l_account_id_tbl.FIRST..l_account_id_tbl.LAST LOOP
394 select Count(*) Total, nvl(max(sysdate-a.received_date)*24*60,0) wait_time,
395 nvl(avg(sysdate-a.received_date)*24*60,0) average_time
396 into l_email_count, l_wait_time, l_average_time
397 FROM iem_rt_proc_emails a, iem_rt_media_items b
398 WHERE a.message_id=b.message_id and b.expire='N'
399 and a.resource_id = l_resource_id_tbl(l_index)
400 and a.email_account_id=l_account_id_tbl(i);
401
402 select count(*) into l_email_count_flag from iem_agents a, jtf_rs_resource_extns b
403 where a.email_account_id=l_account_id_tbl(i)
404 and a.resource_id = b.resource_id and b.user_id=l_current_user;
405
406 if (l_email_count_flag = 0) then
407 x_agent_activity_tbl(l_monitor_index).zero_flag:='true';
408 else
409 x_agent_activity_tbl(l_monitor_index).zero_flag:='false';
410 end if;
411
412 x_agent_activity_tbl(l_monitor_index).resource_id:=l_resource_id_tbl(l_index);
413 x_agent_activity_tbl(l_monitor_index).resource_account_name:=l_account_name_tbl(i);
414 x_agent_activity_tbl(l_monitor_index).email_account_id:=l_account_id_tbl(i);
415 x_agent_activity_tbl(l_monitor_index).email_count:=l_email_count;
416 x_agent_activity_tbl(l_monitor_index).oldest_age:=l_wait_time;
417 x_agent_activity_tbl(l_monitor_index).average_age:=l_average_time;
418 x_agent_activity_tbl(l_monitor_index).account_count:=-1; -- No Account Count
419 x_agent_activity_tbl(l_monitor_index).last_login_time:='-1'; -- No Last Login Time
420
421 l_monitor_index := l_monitor_index + 1;
422
423 end loop;
424 end loop;
425
426 -- Standard Check Of p_commit.
427 IF FND_API.To_Boolean(p_commit) THEN
428 COMMIT WORK;
429 END IF;
430 -- Standard callto get message count and if count is 1, get message info.
431 FND_MSG_PUB.Count_And_Get
432 ( p_count => x_msg_count,
433 p_data => x_msg_data
434 );
435 EXCEPTION
436
437 WHEN FND_API.G_EXC_ERROR THEN
438 ROLLBACK TO get_email_activity_pvt;
439 x_return_status := FND_API.G_RET_STS_ERROR ;
440 FND_MSG_PUB.Count_And_Get
441 ( p_count => x_msg_count,
442 p_data => x_msg_data
443 );
444 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
445 ROLLBACK TO get_email_activity_pvt;
446 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
447 FND_MSG_PUB.Count_And_Get
448 ( p_count => x_msg_count,
449 p_data => x_msg_data
450 );
451 WHEN OTHERS THEN
452 ROLLBACK TO get_email_activity_pvt;
453 x_return_status := FND_API.G_RET_STS_ERROR;
454 IF FND_MSG_PUB.Check_Msg_Level
455 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
456 THEN
457 FND_MSG_PUB.Add_Exc_Msg
458 ( G_PKG_NAME,
459 l_api_name
460 );
461 END IF;
462 FND_MSG_PUB.Count_And_Get
463 (p_count => x_msg_count,
464 p_data => x_msg_data
465 );
466 END get_agent_activity;
467
468 end IEM_SPV_MONITORING_PVT ;
[Home] [Help]