[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_query_string1 := 'select a.message_id, a.email_account_id, a.from_address, a.subject, b.name as classification_name,
c.party_name as customer_name,
to_char(a.received_date, ''MM/DD/RRRR HH24:MI:SS'') as received_date, a.received_date as real_received_date, a.message_id,
replace(a.group_id, a.group_id, :all_groups) as group_name
from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c
where a.resource_id = 0 and a.email_account_id=:email_account_id
and a.rt_classification_id=b.route_classification_id and a.customer_id=c.party_id and a.group_id=0 ';
l_query_string1 := 'select a.message_id, a.email_account_id, a.from_address, a.subject, b.name as classification_name,
c.party_name as customer_name, to_char(a.received_date, ''MM/DD/RRRR HH24:MI:SS'') as received_date, a.received_date as real_received_date, a.message_id, d.group_name
from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c, jtf_rs_groups_tl d
where a.resource_id = 0 and a.email_account_id=:email_account_id
and a.rt_classification_id=b.route_classification_id and a.customer_id=c.party_id
and a.group_id=d.group_id and d.language=userenv(''LANG'') and d.group_id=:group_id';
l_query_string1 := 'select a.message_id, a.email_account_id, a.from_address, a.subject, b.name as classification_name,
c.party_name as customer_name,
to_char(a.received_date, ''MM/DD/RRRR HH24:MI:SS'') as received_date, a.received_date as real_received_date, a.message_id,
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
from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c
where a.resource_id = 0 and a.email_account_id=:email_account_id
and a.rt_classification_id=b.route_classification_id and a.customer_id=c.party_id ';
l_query_string1 := 'select a.message_id, a.email_account_id, a.from_address, a.subject, b.name as classification_name,
decode(a.customer_id, -1, '''', 0, '''', (select party_name from hz_parties where party_id=a.customer_id) ) as customer_name,
to_char(a.received_date, ''MM/DD/RRRR HH24:MI:SS'') as received_date, a.received_date as real_received_date, a.message_id,
replace(a.group_id, a.group_id, :all_groups) as group_name
from iem_rt_proc_emails a, iem_route_classifications b
where a.resource_id = 0 and a.email_account_id=:email_account_id
and a.rt_classification_id=b.route_classification_id
and a.group_id=0';
l_query_string1 := 'select a.message_id, a.email_account_id, a.from_address, a.subject, b.name as classification_name,
decode(a.customer_id, -1, '''', 0, '''', (select party_name from hz_parties where party_id=a.customer_id) ) as customer_name,
to_char(a.received_date, ''MM/DD/RRRR HH24:MI:SS'') as received_date, a.received_date as real_received_date, a.message_id, d.group_name
from iem_rt_proc_emails a, iem_route_classifications b, jtf_rs_groups_tl d
where a.resource_id = 0 and a.email_account_id=:email_account_id
and a.rt_classification_id=b.route_classification_id
and a.group_id=d.group_id and d.language=userenv(''LANG'') and d.group_id=:group_id';
l_query_string1 := 'select a.message_id, a.email_account_id, a.from_address, a.subject, b.name as classification_name,
decode(a.customer_id, -1, '''', 0, '''', (select party_name from hz_parties where party_id=a.customer_id) ) as customer_name,
to_char(a.received_date, ''MM/DD/RRRR HH24:MI:SS'') as received_date, a.received_date as real_received_date, a.message_id,
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
from iem_rt_proc_emails a, iem_route_classifications b
where a.resource_id = 0 and a.email_account_id=:email_account_id
and a.rt_classification_id=b.route_classification_id';
select a.agent_id into l_agent_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 and a.email_account_id=l_post_mdts.email_account_id;
l_query_string1 := 'select count(*)
from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c
where a.resource_id = 0 and a.email_account_id=:email_account_id
and a.rt_classification_id=b.route_classification_id and a.customer_id=c.party_id
and a.group_id=:group_id';
l_query_string1 := 'select count(*)
from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c
where a.resource_id = 0 and a.email_account_id=:email_account_id
and a.rt_classification_id=b.route_classification_id and a.customer_id=c.party_id ';
l_query_string1 := 'select count(*)
from iem_rt_proc_emails a, iem_route_classifications b
where a.resource_id = 0 and a.email_account_id=:email_account_id
and a.rt_classification_id=b.route_classification_id
and a.group_id=:group_id';
l_query_string1 := 'select count(*)
from iem_rt_proc_emails a, iem_route_classifications b
where a.resource_id = 0 and a.email_account_id=:email_account_id
and a.rt_classification_id=b.route_classification_id';
l_string := 'select resource_id, resource_name, last_login_time, real_last_login_time from (
select a.resource_id, concat(concat(rs.source_last_name, '', ''), rs.source_first_name) as resource_name,
to_char(max(c.begin_date_time), ''MM/DD/RRRR HH24:MI:SS'') as last_login_time, max(c.begin_date_time) as real_last_login_time
from iem_agents a, jtf_rs_resource_extns rs, ieu_sh_sessions c
where a.resource_id = rs.resource_id and a.resource_id=c.resource_id
and a.email_account_id =:email_account_id and c.application_id=680
and trunc(sysdate) between trunc(nvl(rs.start_date_active, sysdate))
and trunc(nvl(rs.end_date_active, sysdate))
and rs.user_id in
( select respgrp.user_id from fnd_user_resp_groups respgrp, fnd_user fu, fnd_responsibility resp
where respgrp.user_id = fu.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 trunc(sysdate) between trunc(nvl(respgrp.start_date, sysdate))
and trunc(nvl(respgrp.end_date, sysdate))
and trunc(sysdate) between trunc(nvl(fu.start_date, sysdate))
and trunc(nvl(fu.end_date, sysdate))
)
and rs.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)) ) ';
select a.resource_id, concat(concat(rs.source_last_name, '', ''), rs.source_first_name) as resource_name
, '''' as last_login_time, to_date('''', ''dd-mon-yy'') as real_last_login_time
from iem_agents a, jtf_rs_resource_extns rs
where a.resource_id = rs.resource_id and a.email_account_id = :email_account_id
and trunc(sysdate) between trunc(nvl(rs.start_date_active, sysdate))
and trunc(nvl(rs.end_date_active, sysdate))
and rs.user_id in
( select respgrp.user_id from fnd_user_resp_groups respgrp, fnd_user fu, fnd_responsibility resp
where respgrp.user_id = fu.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 trunc(sysdate) between trunc(nvl(respgrp.start_date, sysdate))
and trunc(nvl(respgrp.end_date, sysdate))
and trunc(sysdate) between trunc(nvl(fu.start_date, sysdate))
and trunc(nvl(fu.end_date, sysdate))
)
and rs.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 a.resource_id not in
(select a.resource_id from iem_agents a, ieu_sh_sessions b where b.application_id=680 and
a.resource_id=b.resource_id and a.email_account_id =:email_account_id ) ) order by real_last_login_time ';
select count(*) into l_fetched_emails from iem_rt_proc_emails a
where a.email_account_id=p_email_account_id and a.queue_status is null and a.resource_id = l_resource_id;
l_string := 'select resource_id, fetched_emails, resource_name from
( SELECT agact.resource_id, count(*) fetched_emails, concat(concat(rs.source_last_name, '',''), rs.source_first_name) as resource_name
from iem_agents agact, iem_rt_proc_emails pm, jtf_rs_resource_extns rs
WHERE pm.resource_id=agact.resource_id
and agact.resource_id = rs.resource_id
and pm.email_account_id=agact.email_account_id and pm.queue_status is null
and agact.email_account_id=:email_account_id
and trunc(sysdate) between trunc(nvl(rs.start_date_active, sysdate))
and trunc(nvl(rs.end_date_active, sysdate))
and rs.user_id in
( select respgrp.user_id from fnd_user_resp_groups respgrp, fnd_user fu, fnd_responsibility resp
where respgrp.user_id = fu.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 trunc(sysdate) between trunc(nvl(respgrp.start_date, sysdate))
and trunc(nvl(respgrp.end_date, sysdate))
and trunc(sysdate) between trunc(nvl(fu.start_date, sysdate))
and trunc(nvl(fu.end_date, sysdate))
)
and rs.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)) )
group by agact.resource_id, rs.source_last_name, rs.source_first_name ';
l_string2 := ' union all SELECT agact.resource_id, 0, concat(concat(rs.source_last_name, '', ''), rs.source_first_name) as resource_name
from iem_agents agact, jtf_rs_resource_extns rs
WHERE agact.resource_id = rs.resource_id
and agact.email_account_id=:email_account_id
and trunc(sysdate) between trunc(nvl(rs.start_date_active, sysdate))
and trunc(nvl(rs.end_date_active, sysdate))
and rs.user_id in
( select respgrp.user_id from fnd_user_resp_groups respgrp, fnd_user fu, fnd_responsibility resp
where respgrp.user_id = fu.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 trunc(sysdate) between trunc(nvl(respgrp.start_date, sysdate))
and trunc(nvl(respgrp.end_date, sysdate))
and trunc(sysdate) between trunc(nvl(fu.start_date, sysdate))
and trunc(nvl(fu.end_date, sysdate))
)
and rs.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 agact.resource_id not in (select pm.resource_id from iem_rt_proc_emails pm
where pm.email_account_id=:email_account_id and pm.queue_status is null) ) ';
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;
l_string := 'select group_id, group_name, email_count from(
select f.group_id, e.group_name, count(*) as email_count from iem_rt_proc_emails f, jtf_rs_groups_vl e where
trunc(sysdate) between trunc(nvl(e.start_date_active, sysdate))
and trunc(nvl(e.end_date_active, sysdate))
and f.group_id=e.group_id and f.resource_id=0 and f.email_account_id=:email_account_id and f.group_id in (
select unique c.group_id from
jtf_rs_group_members c, iem_agents d, jtf_rs_group_usages gu
where
c.delete_flag = ''N'' and c.resource_id = d.resource_id and d.email_account_id =:l_email_account_id
and c.group_id = gu.group_id and gu.usage = ''CALL''
) group by f.group_id, e.group_name
union all
select unique e.group_id, group_name, 0 from
jtf_rs_group_members c, iem_agents d, jtf_rs_groups_vl e,
jtf_rs_group_usages gu where
c.group_id = e.group_id
and c.delete_flag = ''N'' and c.resource_id = d.resource_id and d.email_account_id =:l_email_account_id
and trunc(sysdate) between trunc(nvl(e.start_date_active, sysdate))
and trunc(nvl(e.end_date_active, sysdate))
and c.group_id = gu.group_id and gu.usage = ''CALL''
and e.group_id not in (
select unique group_id from iem_rt_proc_emails where email_account_id=:email_account_id
and group_id<>0 and resource_id=0)
) order by email_count ';
--select count(*) into l_agent_count from jtf_rs_roles_tl a, jtf_rs_role_relations b, jtf_rs_group_members c, iem_agents d
--where a.role_id in (28, 29, 30) and a.language = USERENV ('LANG') and a.role_id = b.role_id and b.delete_flag = 'N'
--and b.role_resource_id = c.resource_id and c.delete_flag = 'N' and c.resource_id = d.resource_id
--and d.email_account_id = l_email_account_id and c.group_id = l_group_id;
SELECT count(*) into l_agent_count
FROM jtf_rs_group_members c, iem_agents d
WHERE c.resource_id IN
(select b.role_resource_id from jtf_rs_role_relations b
where b.role_resource_id=c.resource_id and b.role_id IN (28,29,30)
AND b.delete_flag='N'
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(b.start_date_active, sysdate))
AND TRUNC(NVL(b.end_date_active, sysdate)))
AND c.delete_flag = 'N'
AND c.resource_id = d.resource_id
AND d.email_account_id = l_email_account_id
AND c.group_id = l_group_id;
--l_string := 'select e.group_id, e.group_name, count(*) agent_count from
-- jtf_rs_role_relations b, jtf_rs_group_members c, iem_agents d, jtf_rs_groups_vl e, jtf_rs_resource_extns res, jtf_rs_group_usages gu
-- where b.delete_flag = ''N'' and b.role_id in (28, 29, 30)
-- and b.role_resource_id = c.resource_id and c.group_id = e.group_id
-- and c.resource_id=res.resource_id
-- and c.group_id = gu.group_id
-- and gu.usage = ''CALL''
-- and trunc(sysdate) between trunc(nvl(b.start_date_active, sysdate))
-- and trunc(nvl(b.end_date_active, sysdate))
-- and trunc(sysdate) between trunc(nvl(e.start_date_active, sysdate))
-- and trunc(nvl(e.end_date_active, sysdate))
-- and trunc(sysdate) between trunc(nvl(res.start_date_active, sysdate))
-- and trunc(nvl(res.end_date_active, sysdate))
-- and c.delete_flag = ''N''
-- and c.resource_id = d.resource_id and d.email_account_id = :l_email_account_id
-- group by e.group_id, e.group_name ';
l_string := 'SELECT e.group_id, e.group_name, COUNT(*) agent_count
FROM jtf_rs_group_members c, iem_agents d, jtf_rs_groups_vl e, jtf_rs_resource_extns res, jtf_rs_group_usages gu
WHERE c.group_id = e.group_id AND c.resource_id = res.resource_id
AND c.group_id = gu.group_id
AND c.resource_id in
(select b.role_resource_id from jtf_rs_role_relations b
where b.role_resource_id=c.resource_id and b.role_id IN (28,29,30) and b.delete_flag=''N''
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(b.start_date_active, sysdate))
AND TRUNC(NVL(b.end_date_active, sysdate)))
AND gu.usage = ''CALL''
AND c.delete_flag = ''N''
AND c.resource_id = d.resource_id
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(e.start_date_active, sysdate))
AND TRUNC(NVL(e.end_date_active, sysdate))
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(res.start_date_active, sysdate))
AND TRUNC(NVL(res.end_date_active, sysdate))
AND d.email_account_id = :l_email_account_id
GROUP BY e.group_id,e.group_name ';
select count(*) into l_email_count from iem_rt_proc_emails where resource_id=0
and email_account_id=p_email_account_id and group_id=l_group_id;