[Home] [Help]
26: l_api_version_number NUMBER:=1.0;
27: Type get_message_rec is REF CURSOR ;
28: email_dtl_cur get_message_rec;
29: l_post_mdts iem_agent_inbox_mgmt_pvt.temp_message_type;
30: l_party_name hz_parties.party_name%type;
31: l_string varchar2(32767):='';
32: l_query_string1 varchar2(15000):='';
33: l_query_string2 varchar2(15000):='';
34: l_sort_column varchar2(500):='received_date'; -- default
54: l_email_account_id iem_rt_proc_emails.email_account_id%type;
55: l_sender_name iem_rt_proc_emails.from_address%type;
56: l_subject iem_rt_proc_emails.subject%type;
57: l_classification_name iem_route_classifications.name%type;
58: l_customer_name hz_parties.party_name%type;
59: l_received_date varchar2(500);
60: l_real_received_date iem_rt_proc_emails.received_date%type;
61: l_message_uid iem_rt_proc_emails.message_id%type;
62: l_resource_name jtf_rs_resource_extns_vl.resource_name%type;
133: l_query_string1 := 'select a.message_id, a.email_account_id, a.from_address, a.subject, b.name as classification_name,
134: c.party_name as customer_name,
135: to_char(a.received_date, ''MM/DD/RRRR HH24:MI:SS'') as received_date, a.received_date as real_received_date, a.message_id,
136: d.resource_name, e.rt_media_item_id, a.resource_id
137: from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c, jtf_rs_resource_extns_vl d, iem_rt_media_items e
138: where a.resource_id <> 0 and a.message_id=e.message_id and e.expire=''N'' and a.email_account_id=:email_account_id
139: and a.rt_classification_id=b.route_classification_id and a.customer_id=c.party_id and a.resource_id=d.resource_id ';
140:
141: l_customer_string2 := ' and UPPER(c.party_name) like UPPER(:customer_name)';
143:
144: else
145:
146: l_query_string1 := 'select a.message_id, a.email_account_id, a.from_address, a.subject, b.name as classification_name,
147: decode(a.customer_id, -1, '''', 0, '''', (select party_name from hz_parties where party_id=a.customer_id) ) as customer_name,
148: 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.resource_name,
149: e.rt_media_item_id, a.resource_id
150: from iem_rt_proc_emails a, iem_route_classifications b, jtf_rs_resource_extns_vl d, iem_rt_media_items e
151: where a.resource_id <> 0 and a.message_id=e.message_id and e.expire=''N'' and a.email_account_id=:email_account_id
348: Type get_message_rec is REF CURSOR ;
349: email_dtl_cur get_message_rec;
350: l_post_mdts iem_agent_inbox_mgmt_pvt.temp_message_type;
351: l_classification_name iem_route_classifications.name%type;
352: l_party_name hz_parties.party_name%type;
353: l_resource_name jtf_rs_resource_extns_vl.resource_name%type;
354: l_received_date varchar2(500);
355: l_string varchar2(32767):='';
356: l_query_string1 varchar2(15000):='';
396:
397: if (p_customer_name is not null) then
398:
399: l_query_string1 := 'select count(*)
400: from iem_rt_proc_emails a, iem_route_classifications b, hz_parties c, jtf_rs_resource_extns_vl d, iem_rt_media_items e
401: where a.resource_id <> 0 and a.message_id=e.message_id and e.expire=''N'' and a.email_account_id=:email_account_id
402: and a.rt_classification_id=b.route_classification_id and a.customer_id=c.party_id and a.resource_id=d.resource_id ';
403:
404: l_customer_string2 := ' and UPPER(c.party_name) like UPPER(:customer_name)';