[Home] [Help]
24: l_api_version_number NUMBER:=1.0;
25: Type get_message_rec is REF CURSOR ;
26: email_dtl_cur get_message_rec;
27: l_post_mdts iem_queue_management_pvt.temp_message_type;
28: l_party_name hz_parties.party_name%type;
29: l_string varchar2(32767):='';
30: l_query_string1 varchar2(15000):='';
31: l_query_string2 varchar2(15000):='';
32: l_sort_column varchar2(500):='received_date'; -- default
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;
59: l_group_name jtf_rs_groups_tl.group_name%type;
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)';
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:
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)';
150:
151: elsif (p_customer_name is null and p_group_id = 0) then
152:
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
160: and a.group_id=0';
161: elsif (p_customer_name is null and p_group_id <> -1) then
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
171:
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
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):='';
373: l_query_string2 varchar2(15000):='';
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:
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)';