[Home] [Help]
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
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;
812: DBMS_SQL.COLUMN_VALUE(l_cursorID, 2, l_fetched_emails);
813: DBMS_SQL.COLUMN_VALUE(l_cursorID, 3, l_resource_name);
814:
815: select to_char(max(begin_date_time), 'MM/DD/RRRR HH24:MI:SS') into l_last_login_time
816: from ieu_sh_sessions where application_id=680 and resource_id=l_resource_id;
817:
818: x_resource_count(l_index).resource_id :=l_resource_id;
819: x_resource_count(l_index).resource_name := l_resource_name;
820: x_resource_count(l_index).email_count := l_fetched_emails;