[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select a.email_account_id, a.from_name bulk collect into l_email_account_id_tbl, l_account_name_tbl
from iem_mstemail_accounts a, iem_agents b, jtf_rs_resource_extns c
where a.email_account_id=b.email_account_id and b.resource_id = c.resource_id and c.user_id = l_current_user
order by UPPER(a.from_name);
select Count(*) Total, nvl(max(sysdate-a.received_date)*24*60,0) wait_time,
nvl(avg(sysdate-a.received_date)*24*60,0) average_time
into l_queue_count, l_queue_wait_time, l_queue_average_time
FROM iem_rt_proc_emails a
WHERE a.resource_id = 0 and
a.email_account_id=l_email_account_id_tbl(l_index);
select Count(*) Total, nvl(max(sysdate-a.received_date)*24*60,0) wait_time,
nvl(avg(sysdate-a.received_date)*24*60,0) average_time
into l_inbox_count, l_inbox_wait_time, l_inbox_average_time
FROM iem_rt_proc_emails a, iem_rt_media_items b
WHERE a.resource_id <> 0 and a.message_id=b.message_id and b.expire='N' and
a.email_account_id=l_email_account_id_tbl(l_index);
select count(*) into l_agent_acct_count from iem_agents where email_account_id=l_email_account_id_tbl(l_index);
select count(*) into l_total_count from iem_rt_proc_emails where email_account_id=l_email_account_id_tbl(l_index);
select a.route_classification_id, a.name bulk collect into l_class_id_tbl, l_class_name_tbl
from iem_route_classifications a, iem_account_route_class b
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);
select Count(*) Total, nvl(max(sysdate-a.received_date)*24*60,0) wait_time,
nvl(avg(sysdate-a.received_date)*24*60,0) average_time
into l_queue_count, l_queue_wait_time, l_queue_average_time
FROM iem_rt_proc_emails a,iem_mstemail_accounts b, iem_route_classifications c
WHERE a.email_account_id=b.email_account_id and a.rt_classification_id=c.route_classification_id
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);
select Count(*) Total, nvl(max(sysdate-a.received_date)*24*60,0) wait_time,
nvl(avg(sysdate-a.received_date)*24*60,0) average_time
into l_inbox_count, l_inbox_wait_time, l_inbox_average_time
FROM iem_rt_proc_emails a,iem_mstemail_accounts b, iem_route_classifications c, iem_rt_media_items d
WHERE a.email_account_id=b.email_account_id and a.rt_classification_id=c.route_classification_id
and a.resource_id<>0 and a.message_id=d.message_id and d.expire='N'
and a.email_account_id=l_email_account_id_tbl(l_index) and c.route_classification_id=l_class_id_tbl(i);
select count(*) into l_total_count from iem_rt_proc_emails where email_account_id=l_email_account_id_tbl(l_index)
and rt_classification_id=l_class_id_tbl(i);
select unique res.resource_id,concat(concat(res.source_last_name, ', '), res.source_first_name) as resource_name
bulk collect into l_resource_id_tbl, l_resource_name_tbl from fnd_user_resp_groups respgrp,
jtf_rs_resource_extns res, fnd_responsibility resp where res.user_id=respgrp.user_id
and respgrp.responsibility_id=resp.responsibility_id and resp.application_id=680
and (resp.responsibility_key = 'EMAIL_CENTER_SUPERVISOR' or resp.responsibility_key = 'IEM_SA_AGENT')
and res.resource_id in ( select unique rel.role_resource_id from jtf_rs_role_relations rel where
rel.role_id in (28, 29, 30) and rel.delete_flag = 'N'
and rel.role_resource_type = 'RS_INDIVIDUAL'
-- and trunc(sysdate) between trunc(nvl(rel.start_date_active, sysdate))
-- and trunc(nvl(rel.end_date_active, sysdate))
)
and res.resource_id in (select resource_id from iem_agents)
and (upper(res.source_last_name) like upper(p_resource_name) or upper(res.source_first_name) like upper(p_resource_name)
or upper(res.user_name) like upper(p_resource_name))
order by resource_name;
select unique res.resource_id,concat(concat(res.source_last_name, ', '), res.source_first_name) as resource_name
bulk collect into l_resource_id_tbl, l_resource_name_tbl from fnd_user_resp_groups respgrp,
jtf_rs_resource_extns res, fnd_responsibility resp where res.user_id=respgrp.user_id
and respgrp.responsibility_id=resp.responsibility_id and resp.application_id=680
and (resp.responsibility_key = 'EMAIL_CENTER_SUPERVISOR' or resp.responsibility_key = 'IEM_SA_AGENT')
and res.resource_id in ( select unique rel.role_resource_id from jtf_rs_role_relations rel where
rel.role_id in (28, 29, 30) and rel.delete_flag = 'N'
and rel.role_resource_type = 'RS_INDIVIDUAL'
-- and trunc(sysdate) between trunc(nvl(rel.start_date_active, sysdate))
-- and trunc(nvl(rel.end_date_active, sysdate))
)
and res.resource_id in (select resource_id from iem_agents)
order by resource_name;
select res.resource_id,concat(concat(res.source_last_name, ', '), res.source_first_name) as resource_name
bulk collect into l_resource_id_tbl, l_resource_name_tbl from fnd_user_resp_groups respgrp,
jtf_rs_resource_extns res, fnd_responsibility resp where res.user_id=respgrp.user_id
and respgrp.responsibility_id=resp.responsibility_id and resp.application_id=680
and resp.responsibility_key = l_resource_role
and res.resource_id in ( select unique rel.role_resource_id from jtf_rs_role_relations rel where
rel.role_id in (28, 29, 30) and rel.delete_flag = 'N'
and rel.role_resource_type = 'RS_INDIVIDUAL'
and trunc(sysdate) between trunc(nvl(rel.start_date_active, sysdate))
and trunc(nvl(rel.end_date_active, sysdate)) )
and res.resource_id in (select resource_id from iem_agents)
and (upper(res.source_last_name) like upper(p_resource_name) or upper(res.source_first_name) like upper(p_resource_name)
or upper(res.user_name) like upper(p_resource_name))
order by resource_name;
select res.resource_id,concat(concat(res.source_last_name, ', '), res.source_first_name) as resource_name
bulk collect into l_resource_id_tbl, l_resource_name_tbl from fnd_user_resp_groups respgrp,
jtf_rs_resource_extns res, fnd_responsibility resp where res.user_id=respgrp.user_id
and respgrp.responsibility_id=resp.responsibility_id and resp.application_id=680
and resp.responsibility_key = l_resource_role
and res.resource_id in ( select unique rel.role_resource_id from jtf_rs_role_relations rel where
rel.role_id in (28, 29, 30) and rel.delete_flag = 'N'
and rel.role_resource_type = 'RS_INDIVIDUAL'
and trunc(sysdate) between trunc(nvl(rel.start_date_active, sysdate))
and trunc(nvl(rel.end_date_active, sysdate)) )
and res.resource_id in (select resource_id from iem_agents)
order by resource_name;
select Count(*) Total, nvl(max(sysdate-a.received_date)*24*60,0) wait_time,
nvl(avg(sysdate-a.received_date)*24*60,0) average_time
into l_email_count, l_wait_time, l_average_time
FROM iem_rt_proc_emails a, iem_rt_media_items b
WHERE a.message_id=b.message_id and b.expire='N'
and a.resource_id = l_resource_id_tbl(l_index);
select Count(*) Total into l_assigned_email_count
FROM iem_rt_proc_emails a, iem_rt_media_items b
WHERE a.message_id=b.message_id and b.expire='N'
and a.resource_id = l_resource_id_tbl(l_index) and a.email_account_id in
(select a.email_account_id from iem_agents a, jtf_rs_resource_extns b
where a.resource_id = b.resource_id and b.user_id=l_current_user);
select Count(*) Total into l_requeue_all_count
FROM iem_rt_proc_emails a, iem_rt_media_items b
WHERE a.message_id=b.message_id and b.expire='N'
and a.resource_id = l_resource_id_tbl(l_index) and a.email_account_id in
(select email_account_id from iem_agents c, jtf_rs_resource_extns d
where c.resource_id=d.resource_id and d.user_id=l_current_user);
select count(*) into l_agent_acct_count from iem_agents where resource_id=l_resource_id_tbl(l_index);
select to_char(max(begin_date_time), 'MM/DD/RRRR HH24:MI:SS') into l_last_login_time
from ieu_sh_sessions where application_id=680 and resource_id=l_resource_id_tbl(l_index);
select a.email_account_id, a.from_name bulk collect into l_account_id_tbl, l_account_name_tbl
from iem_mstemail_accounts a, iem_agents b
where a.email_account_id=b.email_account_id and b.resource_id=l_resource_id_tbl(l_index)
order by UPPER(a.from_name);
select Count(*) Total, nvl(max(sysdate-a.received_date)*24*60,0) wait_time,
nvl(avg(sysdate-a.received_date)*24*60,0) average_time
into l_email_count, l_wait_time, l_average_time
FROM iem_rt_proc_emails a, iem_rt_media_items b
WHERE a.message_id=b.message_id and b.expire='N'
and a.resource_id = l_resource_id_tbl(l_index)
and a.email_account_id=l_account_id_tbl(i);
select count(*) into l_email_count_flag from iem_agents a, jtf_rs_resource_extns b
where a.email_account_id=l_account_id_tbl(i)
and a.resource_id = b.resource_id and b.user_id=l_current_user;