eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IEM_QUEUE_MANAGEMENT_PVT dependencies on IEM_RT_PROC_EMAILS

Line 50: l_message_id iem_rt_proc_emails.message_id%type;

46:
47: l_index number := 1;
48: l_count number;
49:
50: l_message_id iem_rt_proc_emails.message_id%type;
51: l_email_account_id iem_rt_proc_emails.email_account_id%type;
52: l_sender_name iem_rt_proc_emails.from_address%type;
53: l_subject iem_rt_proc_emails.subject%type;
54: l_classification_name iem_route_classifications.name%type;

Line 51: l_email_account_id iem_rt_proc_emails.email_account_id%type;

47: l_index number := 1;
48: l_count number;
49:
50: l_message_id iem_rt_proc_emails.message_id%type;
51: l_email_account_id iem_rt_proc_emails.email_account_id%type;
52: l_sender_name iem_rt_proc_emails.from_address%type;
53: l_subject iem_rt_proc_emails.subject%type;
54: l_classification_name iem_route_classifications.name%type;
55: l_customer_name hz_parties.party_name%type;

Line 52: l_sender_name iem_rt_proc_emails.from_address%type;

48: l_count number;
49:
50: l_message_id iem_rt_proc_emails.message_id%type;
51: l_email_account_id iem_rt_proc_emails.email_account_id%type;
52: l_sender_name iem_rt_proc_emails.from_address%type;
53: l_subject iem_rt_proc_emails.subject%type;
54: l_classification_name iem_route_classifications.name%type;
55: l_customer_name hz_parties.party_name%type;
56: l_received_date varchar2(500);

Line 53: l_subject iem_rt_proc_emails.subject%type;

49:
50: l_message_id iem_rt_proc_emails.message_id%type;
51: l_email_account_id iem_rt_proc_emails.email_account_id%type;
52: l_sender_name iem_rt_proc_emails.from_address%type;
53: l_subject iem_rt_proc_emails.subject%type;
54: l_classification_name iem_route_classifications.name%type;
55: l_customer_name hz_parties.party_name%type;
56: l_received_date varchar2(500);
57: l_real_received_date iem_rt_proc_emails.received_date%type;

Line 57: l_real_received_date iem_rt_proc_emails.received_date%type;

53: l_subject iem_rt_proc_emails.subject%type;
54: l_classification_name iem_route_classifications.name%type;
55: l_customer_name hz_parties.party_name%type;
56: l_received_date varchar2(500);
57: l_real_received_date iem_rt_proc_emails.received_date%type;
58: l_message_uid iem_rt_proc_emails.message_id%type;
59: l_group_name jtf_rs_groups_tl.group_name%type;
60:
61: l_cursorID INTEGER;

Line 58: l_message_uid iem_rt_proc_emails.message_id%type;

54: l_classification_name iem_route_classifications.name%type;
55: l_customer_name hz_parties.party_name%type;
56: l_received_date varchar2(500);
57: l_real_received_date iem_rt_proc_emails.received_date%type;
58: l_message_uid iem_rt_proc_emails.message_id%type;
59: l_group_name jtf_rs_groups_tl.group_name%type;
60:
61: l_cursorID INTEGER;
62: l_dummy INTEGER;

Line 119: from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c

115: l_query_string1 := 'select a.message_id, a.email_account_id, a.from_address, a.subject, b.name as classification_name,
116: c.party_name as customer_name,
117: to_char(a.received_date, ''MM/DD/RRRR HH24:MI:SS'') as received_date, a.received_date as real_received_date, a.message_id,
118: replace(a.group_id, a.group_id, :all_groups) as group_name
119: from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c
120: where a.resource_id = 0 and a.email_account_id=:email_account_id
121: and a.rt_classification_id=b.route_classification_id and a.customer_id=c.party_id and a.group_id=0 ';
122:
123: l_customer_string2 := ' and UPPER(c.party_name) like UPPER(:customer_name)';

Line 130: from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c, jtf_rs_groups_tl d

126: elsif (p_customer_name is not null and p_group_id <> -1) then
127:
128: l_query_string1 := 'select a.message_id, a.email_account_id, a.from_address, a.subject, b.name as classification_name,
129: 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
130: from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c, jtf_rs_groups_tl d
131: where a.resource_id = 0 and a.email_account_id=:email_account_id
132: and a.rt_classification_id=b.route_classification_id and a.customer_id=c.party_id
133: and a.group_id=d.group_id and d.language=userenv(''LANG'') and d.group_id=:group_id';
134:

Line 144: from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c

140: l_query_string1 := 'select a.message_id, a.email_account_id, a.from_address, a.subject, b.name as classification_name,
141: c.party_name as customer_name,
142: to_char(a.received_date, ''MM/DD/RRRR HH24:MI:SS'') as received_date, a.received_date as real_received_date, a.message_id,
143: 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
144: from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c
145: where a.resource_id = 0 and a.email_account_id=:email_account_id
146: and a.rt_classification_id=b.route_classification_id and a.customer_id=c.party_id ';
147:
148: l_customer_string2 := ' and UPPER(c.party_name) like UPPER(:customer_name)';

Line 157: from iem_rt_proc_emails a, iem_route_classifications b

153: l_query_string1 := 'select a.message_id, a.email_account_id, a.from_address, a.subject, b.name as classification_name,
154: decode(a.customer_id, -1, '''', 0, '''', (select party_name from hz_parties where party_id=a.customer_id) ) as customer_name,
155: to_char(a.received_date, ''MM/DD/RRRR HH24:MI:SS'') as received_date, a.received_date as real_received_date, a.message_id,
156: replace(a.group_id, a.group_id, :all_groups) as group_name
157: from iem_rt_proc_emails a, iem_route_classifications b
158: where a.resource_id = 0 and a.email_account_id=:email_account_id
159: and a.rt_classification_id=b.route_classification_id
160: and a.group_id=0';
161: elsif (p_customer_name is null and p_group_id <> -1) then

Line 166: from iem_rt_proc_emails a, iem_route_classifications b, jtf_rs_groups_tl d

162:
163: l_query_string1 := 'select a.message_id, a.email_account_id, a.from_address, a.subject, b.name as classification_name,
164: decode(a.customer_id, -1, '''', 0, '''', (select party_name from hz_parties where party_id=a.customer_id) ) as customer_name,
165: 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
166: from iem_rt_proc_emails a, iem_route_classifications b, jtf_rs_groups_tl d
167: where a.resource_id = 0 and a.email_account_id=:email_account_id
168: and a.rt_classification_id=b.route_classification_id
169: and a.group_id=d.group_id and d.language=userenv(''LANG'') and d.group_id=:group_id';
170: else

Line 176: from iem_rt_proc_emails a, iem_route_classifications b

172: l_query_string1 := 'select a.message_id, a.email_account_id, a.from_address, a.subject, b.name as classification_name,
173: decode(a.customer_id, -1, '''', 0, '''', (select party_name from hz_parties where party_id=a.customer_id) ) as customer_name,
174: to_char(a.received_date, ''MM/DD/RRRR HH24:MI:SS'') as received_date, a.received_date as real_received_date, a.message_id,
175: 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
176: from iem_rt_proc_emails a, iem_route_classifications b
177: where a.resource_id = 0 and a.email_account_id=:email_account_id
178: and a.rt_classification_id=b.route_classification_id';
179: end if;
180:

Line 416: from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c

412: x_return_status := FND_API.G_RET_STS_SUCCESS;
413:
414: if (p_customer_name is not null and p_group_id <> -1) then
415: l_query_string1 := 'select count(*)
416: from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c
417: where a.resource_id = 0 and a.email_account_id=:email_account_id
418: and a.rt_classification_id=b.route_classification_id and a.customer_id=c.party_id
419: and a.group_id=:group_id';
420:

Line 426: from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c

422: l_query_string1 := l_query_string1 || l_customer_string2;
423:
424: elsif (p_customer_name is not null and p_group_id = -1) then
425: l_query_string1 := 'select count(*)
426: from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c
427: where a.resource_id = 0 and a.email_account_id=:email_account_id
428: and a.rt_classification_id=b.route_classification_id and a.customer_id=c.party_id ';
429:
430: l_customer_string2 := ' and UPPER(c.party_name) like UPPER(:customer_name)';

Line 435: from iem_rt_proc_emails a, iem_route_classifications b

431: l_query_string1 := l_query_string1 || l_customer_string2;
432:
433: elsif (p_customer_name is null and p_group_id <> -1) then
434: l_query_string1 := 'select count(*)
435: from iem_rt_proc_emails a, iem_route_classifications b
436: where a.resource_id = 0 and a.email_account_id=:email_account_id
437: and a.rt_classification_id=b.route_classification_id
438: and a.group_id=:group_id';
439: else

Line 441: from iem_rt_proc_emails a, iem_route_classifications b

437: and a.rt_classification_id=b.route_classification_id
438: and a.group_id=:group_id';
439: else
440: l_query_string1 := 'select count(*)
441: from iem_rt_proc_emails a, iem_route_classifications b
442: where a.resource_id = 0 and a.email_account_id=:email_account_id
443: and a.rt_classification_id=b.route_classification_id';
444: end if;
445:

Line 691: select count(*) into l_fetched_emails from iem_rt_proc_emails a

687: DBMS_SQL.COLUMN_VALUE(l_cursorID, 2, l_resource_name);
688: DBMS_SQL.COLUMN_VALUE(l_cursorID, 3, l_last_login_time);
689: DBMS_SQL.COLUMN_VALUE(l_cursorID, 4, l_real_last_login_time);
690:
691: select count(*) into l_fetched_emails from iem_rt_proc_emails a
692: where a.email_account_id=p_email_account_id and a.queue_status is null and a.resource_id = l_resource_id;
693:
694: x_resource_count(l_index).resource_id :=l_resource_id;
695: x_resource_count(l_index).resource_name := l_resource_name;

Line 719: from iem_agents agact, iem_rt_proc_emails pm, jtf_rs_resource_extns rs

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

Line 764: and agact.resource_id not in (select pm.resource_id from iem_rt_proc_emails pm

760: rel.role_id in (28, 29, 30) and rel.delete_flag = ''N''
761: and rel.role_resource_type = ''RS_INDIVIDUAL''
762: and trunc(sysdate) between trunc(nvl(rel.start_date_active, sysdate))
763: and trunc(nvl(rel.end_date_active, sysdate)) )
764: and agact.resource_id not in (select pm.resource_id from iem_rt_proc_emails pm
765: where pm.email_account_id=:email_account_id and pm.queue_status is null) ) ';
766:
767: l_string := l_string || l_string2 || l_order_by;
768:

Line 912: select f.group_id, e.group_name, count(*) as email_count from iem_rt_proc_emails f, jtf_rs_groups_vl e where

908:
909: if (p_sort_column = 1) then
910:
911: l_string := 'select group_id, group_name, email_count from(
912: select f.group_id, e.group_name, count(*) as email_count from iem_rt_proc_emails f, jtf_rs_groups_vl e where
913: trunc(sysdate) between trunc(nvl(e.start_date_active, sysdate))
914: and trunc(nvl(e.end_date_active, sysdate))
915: 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 (
916: select unique c.group_id from

Line 932: select unique group_id from iem_rt_proc_emails where email_account_id=:email_account_id

928: and trunc(sysdate) between trunc(nvl(e.start_date_active, sysdate))
929: and trunc(nvl(e.end_date_active, sysdate))
930: and c.group_id = gu.group_id and gu.usage = ''CALL''
931: and e.group_id not in (
932: select unique group_id from iem_rt_proc_emails where email_account_id=:email_account_id
933: and group_id<>0 and resource_id=0)
934: ) order by email_count ';
935:
936: l_string := l_string || l_sort_order;

Line 1061: select count(*) into l_email_count from iem_rt_proc_emails where resource_id=0

1057: DBMS_SQL.COLUMN_VALUE(l_cursorID, 1, l_group_id);
1058: DBMS_SQL.COLUMN_VALUE(l_cursorID, 2, l_group_name);
1059: DBMS_SQL.COLUMN_VALUE(l_cursorID, 3, l_agent_count);
1060:
1061: select count(*) into l_email_count from iem_rt_proc_emails where resource_id=0
1062: and email_account_id=p_email_account_id and group_id=l_group_id;
1063:
1064: x_resource_group_count(l_index).group_id :=l_group_id;
1065: x_resource_group_count(l_index).group_name := l_group_name;