APPS.IEM_QUEUE_MANAGEMENT_PVT dependencies on IEM_ROUTE_CLASSIFICATIONS

Line 54: l_classification_name iem_route_classifications.name%type;

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;
58: l_message_uid iem_rt_proc_emails.message_id%type;

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 368: l_classification_name iem_route_classifications.name%type;

364: l_api_version_number NUMBER:=1.0;
365: Type get_message_rec is REF CURSOR ;
366: email_dtl_cur get_message_rec;
367: l_post_mdts iem_queue_management_pvt.temp_message_type;
368: l_classification_name iem_route_classifications.name%type;
369: l_party_name hz_parties.party_name%type;
370: l_received_date varchar2(500);
371: l_string varchar2(32767):='';
372: l_query_string1 varchar2(15000):='';

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: