[Home] [Help]
283:
284: -- l_current_user := 1000691;
285:
286: Begin
287: select a.agent_id into l_agent_account_id from iem_agents a, jtf_rs_resource_extns b
288: 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;
289: Exception
290: WHEN NO_DATA_FOUND THEN
291: l_agent_account_id := 0;
616:
617: l_string := 'select resource_id, resource_name, last_login_time, real_last_login_time from (
618: select a.resource_id, concat(concat(rs.source_last_name, '', ''), rs.source_first_name) as resource_name,
619: 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
620: from iem_agents a, jtf_rs_resource_extns rs, ieu_sh_sessions c
621: where a.resource_id = rs.resource_id and a.resource_id=c.resource_id
622: and a.email_account_id =:email_account_id and c.application_id=680
623: and trunc(sysdate) between trunc(nvl(rs.start_date_active, sysdate))
624: and trunc(nvl(rs.end_date_active, sysdate))
641: l_string2 := ' group by a.resource_id, rs.source_last_name, rs.source_first_name
642: union all
643: select a.resource_id, concat(concat(rs.source_last_name, '', ''), rs.source_first_name) as resource_name
644: , '''' as last_login_time, to_date('''', ''dd-mon-yy'') as real_last_login_time
645: from iem_agents a, jtf_rs_resource_extns rs
646: where a.resource_id = rs.resource_id and a.email_account_id = :email_account_id
647: and trunc(sysdate) between trunc(nvl(rs.start_date_active, sysdate))
648: and trunc(nvl(rs.end_date_active, sysdate))
649: and rs.user_id in
715: end if;
716:
717: l_string := 'select resource_id, fetched_emails, resource_name from
718: ( SELECT agact.resource_id, count(*) fetched_emails, concat(concat(rs.source_last_name, '',''), rs.source_first_name) as resource_name
719: from iem_agents agact, iem_rt_proc_emails pm, jtf_rs_resource_extns rs
720: WHERE pm.resource_id=agact.resource_id
721: and agact.resource_id = rs.resource_id
722: and pm.email_account_id=agact.email_account_id and pm.queue_status is null
723: and agact.email_account_id=:email_account_id
740: and trunc(nvl(rel.end_date_active, sysdate)) )
741: group by agact.resource_id, rs.source_last_name, rs.source_first_name ';
742:
743: l_string2 := ' union all SELECT agact.resource_id, 0, concat(concat(rs.source_last_name, '', ''), rs.source_first_name) as resource_name
744: from iem_agents agact, jtf_rs_resource_extns rs
745: WHERE agact.resource_id = rs.resource_id
746: and agact.email_account_id=:email_account_id
747: and trunc(sysdate) between trunc(nvl(rs.start_date_active, sysdate))
748: and trunc(nvl(rs.end_date_active, sysdate))
1000: end if;
1001:
1002: -- Start changes for bug no. 7418671
1003: --l_string := 'select e.group_id, e.group_name, count(*) agent_count from
1004: -- 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
1005: -- where b.delete_flag = ''N'' and b.role_id in (28, 29, 30)
1006: -- and b.role_resource_id = c.resource_id and c.group_id = e.group_id
1007: -- and c.resource_id=res.resource_id
1008: -- and c.group_id = gu.group_id
1016: -- and c.delete_flag = ''N''
1017: -- and c.resource_id = d.resource_id and d.email_account_id = :l_email_account_id
1018: -- group by e.group_id, e.group_name ';
1019: l_string := 'SELECT e.group_id, e.group_name, COUNT(*) agent_count
1020: FROM jtf_rs_group_members c, iem_agents d, jtf_rs_groups_vl e, jtf_rs_resource_extns res, jtf_rs_group_usages gu
1021: WHERE c.group_id = e.group_id AND c.resource_id = res.resource_id
1022: AND c.group_id = gu.group_id
1023: AND c.resource_id in
1024: (select b.role_resource_id from jtf_rs_role_relations b