[Home] [Help]
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
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);
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
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
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:
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);
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
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);
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);
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: