[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,
d.resource_name, e.rt_media_item_id, a.resource_id
from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c, jtf_rs_resource_extns_vl d, iem_rt_media_items e
where a.resource_id <> 0 and a.message_id=e.message_id and e.expire=''N'' 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.resource_id=d.resource_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, d.resource_name,
e.rt_media_item_id, a.resource_id
from iem_rt_proc_emails a, iem_route_classifications b, jtf_rs_resource_extns_vl d, iem_rt_media_items e
where a.resource_id <> 0 and a.message_id=e.message_id and e.expire=''N'' and a.email_account_id=:email_account_id
and a.rt_classification_id=b.route_classification_id and a.resource_id=d.resource_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_email_account_id;
l_query_string1 := 'select count(*)
from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c, jtf_rs_resource_extns_vl d, iem_rt_media_items e
where a.resource_id <> 0 and a.message_id=e.message_id and e.expire=''N'' 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.resource_id=d.resource_id ';
l_query_string1 := 'select count(*)
from iem_rt_proc_emails a, iem_route_classifications b, jtf_rs_resource_extns_vl d, iem_rt_media_items e
where a.resource_id <> 0 and a.message_id=e.message_id and e.expire=''N'' and a.email_account_id=:email_account_id
and a.rt_classification_id=b.route_classification_id and a.resource_id=d.resource_id ';
l_string1 := '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,
fnd_responsibility resp, fnd_user_resp_groups respgrp, fnd_user fu
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 rs.user_id=respgrp.user_id and respgrp.user_id=fu.user_id
and respgrp.responsibility_id=resp.responsibility_id and resp.application_id=680
and resp.responsibility_key = :resource_role
and trunc(sysdate) between trunc(nvl(respgrp.start_date, sysdate))
and trunc(nvl(respgrp.end_date, sysdate))
and trunc(sysdate) between trunc(nvl(rs.start_date_active, sysdate))
and trunc(nvl(rs.end_date_active, 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,
fnd_responsibility resp, fnd_user_resp_groups respgrp, fnd_user fu
where a.resource_id = rs.resource_id and a.email_account_id = :email_account_id
and rs.user_id=respgrp.user_id and respgrp.user_id=fu.user_id
and respgrp.responsibility_id=resp.responsibility_id and resp.application_id=680
and resp.responsibility_key = :resource_role
and trunc(sysdate) between trunc(nvl(respgrp.start_date, sysdate))
and trunc(nvl(respgrp.end_date, sysdate))
and trunc(sysdate) between trunc(nvl(rs.start_date_active, sysdate))
and trunc(nvl(rs.end_date_active, 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 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 resource_id = l_resource_id;
l_string1 := '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, fnd_responsibility resp,fnd_user_resp_groups respgrp, fnd_user fu
WHERE pm.resource_id=agact.resource_id
and agact.resource_id = rs.resource_id and rs.user_id=respgrp.user_id and respgrp.user_id=fu.user_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 respgrp.responsibility_id=resp.responsibility_id and resp.application_id=680
and resp.responsibility_key = :resource_role
and trunc(sysdate) between trunc(nvl(respgrp.start_date, sysdate))
and trunc(nvl(respgrp.end_date, sysdate))
and trunc(sysdate) between trunc(nvl(rs.start_date_active, sysdate))
and trunc(nvl(rs.end_date_active, 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)) ) ';
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, fnd_responsibility resp,fnd_user_resp_groups respgrp, fnd_user fu
WHERE agact.resource_id = rs.resource_id and rs.user_id=respgrp.user_id and respgrp.user_id=fu.user_id
and agact.email_account_id=:email_account_id
and respgrp.responsibility_id=resp.responsibility_id and resp.application_id=680
and resp.responsibility_key = :resource_role
and trunc(sysdate) between trunc(nvl(respgrp.start_date, sysdate))
and trunc(nvl(respgrp.end_date, sysdate))
and trunc(sysdate) between trunc(nvl(rs.start_date_active, sysdate))
and trunc(nvl(rs.end_date_active, 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)) ) ';
l_string3 := ' 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;