[Home] [Help]
265:
266: --l_current_user := 1001608;
267:
268: Begin
269: select a.agent_id into l_agent_account_id from iem_agents a, jtf_rs_resource_extns b
270: where a.resource_id=b.resource_id and b.user_id=l_current_user and a.email_account_id=l_email_account_id;
271: Exception
272: WHEN NO_DATA_FOUND THEN
273: l_agent_account_id := 0;
617:
618: l_string1 := 'select resource_id, resource_name, last_login_time, real_last_login_time from (
619: select a.resource_id, concat(concat(rs.source_last_name, '', ''), rs.source_first_name) as resource_name,
620: 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
621: from iem_agents a, jtf_rs_resource_extns rs, ieu_sh_sessions c,
622: fnd_responsibility resp, fnd_user_resp_groups respgrp, fnd_user fu
623: where a.resource_id = rs.resource_id and a.resource_id=c.resource_id
624: and a.email_account_id =:email_account_id and c.application_id=680
625: and rs.user_id=respgrp.user_id and respgrp.user_id=fu.user_id
640: l_string2 := ' group by a.resource_id, rs.source_last_name, rs.source_first_name
641: union all
642: select a.resource_id, concat(concat(rs.source_last_name, '', ''), rs.source_first_name) as resource_name
643: , '''' as last_login_time, to_date('''', ''dd-mon-yy'') as real_last_login_time
644: from iem_agents a, jtf_rs_resource_extns rs,
645: fnd_responsibility resp, fnd_user_resp_groups respgrp, fnd_user fu
646: where a.resource_id = rs.resource_id and a.email_account_id = :email_account_id
647: and rs.user_id=respgrp.user_id and respgrp.user_id=fu.user_id
648: and respgrp.responsibility_id=resp.responsibility_id and resp.application_id=680
659: and trunc(sysdate) between trunc(nvl(rel.start_date_active, sysdate))
660: and trunc(nvl(rel.end_date_active, sysdate)) ) ';
661:
662: l_string3 := ' and a.resource_id not in
663: (select a.resource_id from iem_agents a, ieu_sh_sessions b where b.application_id=680 and
664: a.resource_id=b.resource_id and a.email_account_id =:email_account_id ) ) order by real_last_login_time ';
665:
666: if (p_transferrer_id <> -1) then
667: -- l_string11 := ' and a.resource_id <> ' || p_transferrer_id;
733: end if;
734:
735: l_string1 := 'select resource_id, fetched_emails, resource_name from
736: ( SELECT agact.resource_id, count(*) fetched_emails, concat(concat(rs.source_last_name, '',''), rs.source_first_name) as resource_name
737: from IEM_AGENTS agact, iem_rt_proc_emails pm,
738: jtf_rs_resource_extns rs, fnd_responsibility resp,fnd_user_resp_groups respgrp, fnd_user fu
739: WHERE pm.resource_id=agact.resource_id
740: and agact.resource_id = rs.resource_id and rs.user_id=respgrp.user_id and respgrp.user_id=fu.user_id
741: and pm.email_account_id=agact.email_account_id and pm.queue_status is null
755: and trunc(nvl(rel.end_date_active, sysdate)) ) ';
756:
757: l_string2 := 'group by agact.resource_id, rs.source_last_name, rs.source_first_name
758: union all SELECT agact.resource_id, 0, concat(concat(rs.source_last_name, '', ''), rs.source_first_name) as resource_name
759: from IEM_AGENTS agact, jtf_rs_resource_extns rs, fnd_responsibility resp,fnd_user_resp_groups respgrp, fnd_user fu
760: WHERE agact.resource_id = rs.resource_id and rs.user_id=respgrp.user_id and respgrp.user_id=fu.user_id
761: and agact.email_account_id=:email_account_id
762: and respgrp.responsibility_id=resp.responsibility_id and resp.application_id=680
763: and resp.responsibility_key = :resource_role