[Home] [Help]
PACKAGE BODY: APPS.IEM_EMAIL_PROC_PVT
Source
1 PACKAGE BODY IEM_EMAIL_PROC_PVT as
2 /* $Header: iemmprpb.pls 120.20.12010000.3 2008/11/26 00:26:51 rtripath ship $ */
3
4 G_PKG_NAME CONSTANT varchar2(30) :='IEM_EMAIL_PROC_PVT ';
5 g_statement_log boolean; -- Statement Level Logging
6 g_exception_log boolean; -- Statement Level Logging
7 g_error_log boolean; -- Statement Level Logging
8 g_contact_id number;
9 g_relation_id number;
10 g_topscore number;
11 g_topclass varchar2(100);
12 PROCEDURE PROC_EMAILS(ERRBUF OUT NOCOPY VARCHAR2,
13 ERRRET OUT NOCOPY VARCHAR2,
14 p_api_version_number in number:= 1.0,
15 p_init_msg_list IN VARCHAR2 ,
16 p_commit IN VARCHAR2 ,
17 p_count IN NUMBER
18 ) IS
19 l_post_rec iem_rt_preproc_emails%rowtype;
20 l_count number:=1;
21 l_index number:=0;
22 l_class number;
23 l_ret_status varchar2(10);
24 l_msg_data varchar2(300);
25 l_msg_count number;
26 l_media_id number;
27 l_group_id number;
28 l_rt_classification_id number;
29 l_stat varchar2(10);
30 l_out_text varchar2(2000):=' ';
31 l_routing_classification varchar2(300);
32 KeyValuePairs iem_route_pub.KeyVals_tbl_type;
33 l_api_name varchar2(100):='PROC_EMAILS';
34 l_api_version_number number:=1.0;
35 l_Error_Message VARCHAR2(2000);
36 l_call_status BOOLEAN;
37 l_class_val_tbl IEM_ROUTE_PUB.keyVals_tbl_type;
38 l_param_rec_tbl IEM_RULES_ENGINE_PUB.parameter_tbl_type;
39 l_tag_keyval IEM_TAGPROCESS_PUB.keyVals_tbl_type;
40 l_action varchar2(50);
41 l_counter number:=1;
42 l_uid number;
43 l_media_lc_rec JTF_IH_PUB.media_lc_rec_type;
44 l_milcs_id NUMBER;
45 l_mp_milcs_id NUMBER; -- MLCS ID for MAIL PROCESSINGS
46 l_message_id number;
47 l_email_rec iem_rt_preproc_emails%ROWTYPE;
48 x_stat varchar2(10);
49 l_status varchar2(10);
50 l_text varchar2(32767);
51 l_cust_stat varchar2(10);
52 l_process varchar2(10);
53 l_from_folder varchar2(50):='/Inbox';
54 l_folder_name varchar2(100);
55 l_f_name varchar2(100);
56 l_proc_stat varchar2(10);
57 l_autoproc_result varchar2(1);
58 l_contact_point_id number;
59 l_emp_flag varchar2(10):='N';
60 STOP_PROCESSING EXCEPTION;
61 ABORT_PROCESSING EXCEPTION;
62 ABORT_MOVE_PROCESSING EXCEPTION;
63 ERR_INSERTING EXCEPTION;
64 l_level varchar2(20):='STATEMENT';
65 l_logmessage varchar2(2000):=' ';
66 l_encrypted_id varchar2(500);
67 l_msgid1 number;
68 l_index1 number;
69 l_index2 number;
70 l_autoack_count number:=0;
71 l_autoack_flag varchar2(1):='N';
72 e_nowait EXCEPTION;
73 PRAGMA EXCEPTION_INIT(e_nowait, -54);
74 NO_ITEM_FOUND EXCEPTION;
75 NO_RECORD_TO_PROCESS EXCEPTION;
76 STOP_REDIRECT_PROCESSING EXCEPTION;
77 cursor c_class_id is
78 select classification_id from iem_email_classifications
79 where message_id=l_message_id
80 order by score desc;
81 l_intent_counter number;
82 l_start_search number:=0;
83 l_ih_subject varchar2(80);
84 l_search varchar2(100); -- search pattern in subject
85 l_doc_id number;
86 l_sr_id number;
87 l_note_type varchar2(100);
88 l_agentid number;
89 l_ih_id number;
90 l_dflt_agt_id number;
91 l_agt_count number;
92 l_tag_custid number; -- Customer Id in Tag
93 l_customer_id number;
94 l_enable varchar2(1);
95 l_auto_flag varchar2(1):='N';
96 l_noti_flag varchar2(1);
97 l_auto_value varchar2(20);
98 l_dflt_sender varchar2(200);
99 l_wf_custom_val varchar2(200);
100 l_media_rec JTF_IH_PUB.media_rec_type;
101 l_interaction_rec JTF_IH_PUB.interaction_rec_type;
102 l_activity_rec JTF_IH_PUB.activity_rec_type;
103 l_interaction_id number;
104 l_activity_id number;
105 l_param_index number;
106 l_cust_search_id number;
107 l_status_id number;
108 l_proc_name varchar2(100);
109 l_result varchar2(100);
110 l_run_proc_tbl IEM_TAGPROCESS_PUB.keyVals_tbl_type;
111 l_tbl_counter number;
112 l_redirect_id varchar2(240);
113 l_rt_media_item_id number;
114 l_rt_interaction_id number;
115 l_email_doc_tbl email_doc_tbl;
116 l_resource_id number;
117 l_qual_tbl IEM_OUTBOX_PROC_PUB.QualifierRecordList;
118 l_outbox_tbl IEM_OUTBOX_PROC_PUB.keyVals_tbl_type;
119 l_auto_reply_flag varchar2(1):='N';
120 l_auto_forward_flag varchar2(1);
121 l_action_id number;
122 l_outbox_item_id number;
123 l_cat_counter number;
124 l_search_type varchar2(10); -- ALL/MES/KM/CP -- Category Map
125 l_cm_cat_id number; -- store the category for MES category based mapping
126 l_kb_rank number;
127 l_contact_id number;
128 l_party_id number;
129 l_party_type varchar2(100);
130 l_id number;
131 l_category_id AMV_SEARCH_PVT.amv_number_varray_type:=AMV_SEARCH_PVT.amv_number_varray_type();
132 l_repos varchar2(100); -- MES/KM search repository
133 l_ext_address varchar2(250); --external email address for redirecting
134 l_ext_temp_id number; -- template id created to be used for redirecting external
135 l_ext_subject varchar2(500); -- prefix for redirect email subject
136 l_outbox_id number;
137 l_sender varchar2(200);
138 l_from1 number;
139 l_from2 number;
140 l_to_address varchar2(200);
141 l_redirect_flag varchar2(1):='N';
142 l_intent_flg number(15,0);
143 l_rule_id number;
144 l_acct_type iem_mstemail_accounts.account_type%type;
145 cursor c_item is select ib.item_id,ib.item_name,ib.last_update_date
146 from amv_c_chl_item_match cim,jtf_amv_items_vl ib
147 where cim.channel_category_id = l_cm_cat_id
148 and cim.channel_id is null
149 and cim.approval_status_type ='APPROVED'
150 and cim.table_name_code ='ITEM'
151 and cim.available_for_channel_date <= sysdate
152 and cim.item_id = ib.item_id
153 and nvl(ib.effective_start_date, sysdate) <= sysdate + 1
154 and nvl(ib.expiration_date, sysdate) >= sysdate;
155 -- Introduce additional key Intent String
156 l_top_intent varchar2(50);
157 l_top_score number;
158 cursor c_intent is select a.intent,b.score from
159 iem_intents a,iem_email_classifications b
160 where b.message_id=l_message_id
161 and a.intent_id=b.classification_id;
162 l_intent_str varchar2(700);
163 l_header_rec iem_ms_base_headers%rowtype;
164 l_email_user_name iem_mstemail_accounts.user_name%type;
165 l_email_address iem_mstemail_accounts.user_name%type;
166 l_email_domain_name varchar2(300);
167 l_auto_msgstatus varchar2(10); -- will store msg status for autoreply/autoxredirect;
168 l_cust_contact_id number;
169 STOP_AUTO_PROCESSING EXCEPTION;
170 begin
171 -- Standard call to check for call compatibility.
172 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
173 p_api_version_number,
174 l_api_name,
175 G_PKG_NAME)
176 THEN
177 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
178 END IF;
179 FND_LOG_REPOSITORY.init(null,null);
180 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
181 g_statement_log:=true;
182 end if;
183 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
184 g_exception_log:=true;
185 end if;
186 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
187 g_error_log:=true;
188 end if;
189 LOOP
190 SAVEPOINT process_emails_pvt;
191 BEGIN
192
193 /* Get the message in FIFO order to process from iem_rt_preproc_emails */
194
195 IEM_EMAIL_PROC_PVT.iem_returned_msg_rec(l_post_rec);
196 IF l_post_rec.message_id is null then
197 raise NO_RECORD_TO_PROCESS ;
198 END IF;
199 l_message_id:=l_post_rec.message_id;
200 select * into l_header_Rec
201 from iem_ms_base_headers
202 where message_id=l_message_id;
203 select user_name,kem_flag,email_address,account_type
204 into l_email_user_name,l_intent_flg,l_email_address,l_acct_type
205 from iem_mstemail_accounts
206 where email_account_id=l_post_rec.email_account_id;
207 l_email_domain_name:=substr(l_email_address,instr(l_email_address,'@',1)+1,length(l_email_address));
208 -- Do Intent Processing if Enabled for Intent
209 IF l_intent_flg>0 THEN -- intent is enabled for the account
210 iem_email_proc_pvt.iem_process_intent(l_post_rec.email_account_id,l_post_rec.message_id,l_ret_status,l_out_text);
211 IF l_ret_status<>'S' then
212 if g_error_log then
213 l_logmessage:='Error During Intent Processing.Not able to Map intent to inbound emails.Continued Processing ';
214 iem_logger(l_logmessage);
215 end if;
216 ELSE
217 if g_statement_log then
218 l_logmessage:='Success in Intent Processing ';
219 iem_logger(l_logmessage);
220 end if;
221 END IF;
222 END IF;
223
224 /* Find the profile value set for Processing Number of Intents for suggested Documents */
225
226 l_intent_counter:=FND_PROFILE.VALUE_SPECIFIC('IEM_INTENT_RESPONSE_NUM');
227 if l_intent_counter is null then
228 l_intent_counter:=1;
229 end if;
230 if g_statement_log then
231 l_logmessage:='Start Processing for message ID '||l_post_rec.message_id;
232 iem_logger(l_logmessage);
233 end if;
234 -- resetting the below two value to null to get rid of cache data
235 g_contact_id:=null;
236 g_relation_id:=null;
237
238 -- Check For TAG DATA in Inbound Emails
239
240 l_tag_keyval.delete;
241 IEM_EMAIL_PROC_PVT.IEM_RETURN_ENCRYPTID
242 (p_subject=>l_header_rec.subject,
243 x_id=>l_encrypted_id,
244 x_Status=>l_ret_status);
245 if l_ret_status='E' then
246 if g_statement_log then
247 l_logmessage:='Error While searching for Tag in Subject ';
248 iem_logger(l_logmessage);
249 end if;
250 raise ABORT_PROCESSING;
251 else
252 if l_encrypted_id is not null then
253 l_logmessage:='Found Encrypted Tag '||l_encrypted_id;
254 else
255 l_logmessage:='Inbound message does not contain any tag';
256 end if;
257
258 if g_statement_log then
259 iem_logger(l_logmessage);
260 end if;
261 end if;
262
263
264
265 -- CALLING TAGGING API TO RESET THE TAG DATA FOR REROUTED MESSAGE
266 IF l_post_rec.ih_media_item_id is not null then
267 if g_statement_log then
268 l_logmessage:='Resetting The Tag For Rerouted/Redirected Message';
269 iem_logger(l_logmessage);
270 end if;
271 IEM_ENCRYPT_TAGS_PVT.RESET_TAG
272 (p_api_version_number=>1.0,
273 p_message_id=>l_post_rec.message_id,
274 x_return_status=>l_ret_status ,
275 x_msg_count=>l_msg_count,
276 x_msg_data=>l_msg_data);
277
278 IF l_ret_status<>'S' THEN
279 if g_error_log then
280 l_logmessage:='Error while Resetting Tag For Rerouted Message';
281 iem_logger(l_logmessage);
282 end if;
283 raise abort_processing;
284 END IF;
285 END IF;
286
287 -- Calling the Tag Processing Api
288 if g_statement_log then
289 l_logmessage:='Calling Tag Processing Api ';
290 iem_logger(l_logmessage);
291 end if;
292 IF l_encrypted_id is not null then
293 IEM_TAGPROCESS_PUB.GETTAGVALUES
294 (p_Api_Version_Number=>1.0,
295 p_encrypted_id =>l_encrypted_id,
296 p_message_id=>l_post_rec.message_id,
297 x_key_value=>l_tag_keyval,
298 x_msg_count=>l_msg_count,
299 x_return_status=>l_ret_status,
300 x_msg_data=>l_msg_data);
301 IF l_ret_status<>'S' THEN
302 if g_error_log then
303 l_logmessage:='Error while Calling Tag Processing Api ';
304 iem_logger(l_logmessage);
305 end if;
306 raise abort_processing;
307 END IF;
308 END IF;
309
310 -- Retrieving Tag Data if Exists
311 l_auto_reply_flag:='N';
312
313 IF l_tag_keyval.count>0 THEN
314 for i in l_tag_keyval.FIRST..l_tag_keyval.LAST LOOP
315 IF l_tag_keyval(i).key='IEMNBZTSRVSRID' then
316 l_sr_id:=to_number(l_tag_keyval(i).value);
317 ELSIF l_tag_keyval(i).key='IEMNAGENTID' THEN
318 l_agentid:=to_number(l_tag_keyval(i).value);
319 ELSIF l_tag_keyval(i).key='IEMNINTERACTIONID' THEN
320 l_ih_id:=to_number(l_tag_keyval(i).value);
321 ELSIF l_tag_keyval(i).key='IEMNCUSTOMERID' THEN
322 l_tag_custid:=to_number(l_tag_keyval(i).value);
323 ELSIF l_tag_keyval(i).key='IEMSAUTOREPLY' THEN
324 l_auto_reply_flag:='Y';
325 ELSIF l_tag_keyval(i).key='IEMSAUTOFORWARD' THEN
326 l_auto_forward_flag:='Y';
327 ELSIF l_tag_keyval(i).key='IEMNCONTACTID' THEN
328 g_contact_id:=to_number(l_tag_keyval(i).value);
329 ELSIF l_tag_keyval(i).key='IEMNRELATIONSHIPID' THEN
330 g_relation_id:=to_number(l_tag_keyval(i).value);
331 END IF;
332 END LOOP;
333 END IF; -- End Of Retrieving Tag Data if Exists
334 /* If interaction id exists in pre mdt , then the current processing should have that Id as the parent IH Id */
335 If l_post_rec.ih_interaction_id is not null then
336 l_ih_id:=l_post_rec.ih_interaction_id;
337 end if;
338
339 -- Getting the customer Id for Interaction creation and passing it to all the rules engine. First we check
340 -- if the tag contain the customer id or not. If yes we used that first. Otherwise we check whether based
341 -- on email_address pre-processing able to retrieve a single hit customer id and use that.
342 -- Retrieve only the Email Address from From String in the BASE HEADER TABLE
343 l_from1:=instr(l_header_rec.from_str,'<',1,1);
344 l_from2:=instr(l_header_rec.from_str,'>',1,1);
345 IF l_from1>0 then -- From Address Contains Both Name and Address
346 l_sender:=substr(l_header_rec.from_Str,l_from1+1,l_from2-l_from1-1);
347 ELSE -- From Address contains only Address
348 l_sender:=l_header_rec.from_str;
349 END IF;
350 -- getting contact id for internal employee from the email address of
351 --the sender
352 if l_acct_Type='I' then
353 BEGIN
354 IEM_GETCUST_PVT.GETCUSTOMERID
355 (p_api_version_number=>1.0,
356 p_email=>l_sender,
357 p_party_id=>l_cust_contact_id,
358 X_MSG_COUNT=>l_msg_count,
359 X_RETURN_STATUS=>l_ret_status,
360 X_MSG_DATA=>l_msg_data);
361 EXCEPTION
362 WHEN OTHERS THEN NULL;
363 END;
364 if g_statement_log then
365 l_logmessage:='Get the emp contact Id '||l_cust_contact_id ||' For Sender '||l_sender ;
366 iem_logger(l_logmessage);
367 end if;
368 end if;
369 IF l_tag_custid is not null THEN
370 l_customer_id:=l_tag_custid;
371
372 ELSE
373 -- Get the Customer Id
374 -- GETTING CUSTOMER ID BASED ON EMAIL ADDRESS
375 BEGIN
376 IEM_GETCUST_PVT.GETCUSTOMERID
377 (p_api_version_number=>1.0,
378 p_email=>l_sender,
379 p_party_id=>l_customer_id,
380 X_MSG_COUNT=>l_msg_count,
381 X_RETURN_STATUS=>l_ret_status,
382 X_MSG_DATA=>l_msg_data);
383 EXCEPTION
384 WHEN OTHERS THEN NULL;
385 END;
386 if l_customer_id is null then
387 l_customer_id:=-1;
388 end if;
389 if g_statement_log then
390 l_logmessage:='Get the Customer Id '||l_customer_id ||' For Sender '||l_sender ;
391 iem_logger(l_logmessage);
392 end if;
393 END IF;
394
395
396 -- CALLING THE CUSTOMISED WORKFLOW
397 -- Removed the whole section of calling customized workflow --Ranjan
398 -- Calling Route Classification
399 For v1 in c_intent LOOP
400 l_intent_str:=nvl(l_intent_str,' ')||v1.intent;
401 if l_top_intent is null then
402 l_top_intent:=v1.intent;
403 l_top_score:=v1.score;
404 end if;
405 END LOOP;
406 -- Populating the Classification Engine input data
407 if g_statement_log then
408 l_logmessage:='Start Populating Key value pair for Classification and Routing Processing ';
409 iem_logger(l_logmessage);
410 end if;
411 l_counter:=1;
412 l_class_val_tbl.delete;
413 l_class_val_tbl(l_counter).key:='IEMNMESSAGESIZE';
414 l_class_val_tbl(l_counter).value:=l_header_rec.message_size;
415 l_class_val_tbl(l_counter).datatype:='N';
416 l_counter:=l_counter+1;
417 l_class_val_tbl(l_counter).key:='IEMSSENDERNAME';
418 l_class_val_tbl(l_counter).value:=l_sender;
419 l_class_val_tbl(l_counter).datatype:='S';
420 l_counter:=l_counter+1;
421 l_class_val_tbl(l_counter).key:='IEMSUSERACCTNAME';
422 l_class_val_tbl(l_counter).value:=l_email_user_name;
423 l_class_val_tbl(l_counter).datatype:='S';
424 l_counter:=l_counter+1;
425 l_class_val_tbl(l_counter).key:='IEMSDOMAINNAME';
426 l_class_val_tbl(l_counter).value:=l_email_domain_name;
427 l_class_val_tbl(l_counter).datatype:='S';
428 /* this is missing for the time being
429 l_counter:=l_counter+1;
430 l_class_val_tbl(l_counter).key:='IEMSPRIORITY';
431 l_class_val_tbl(l_counter).value:=l_post_rec.priority;
432 l_class_val_tbl(l_counter).datatype:='S';
433 */
434 l_counter:=l_counter+1;
435 l_class_val_tbl(l_counter).key:='IEMSSUBJECT';
436 l_class_val_tbl(l_counter).value:=l_header_rec.subject;
437 l_class_val_tbl(l_counter).datatype:='S';
438 l_counter:=l_counter+1;
439 l_class_val_tbl(l_counter).key:='IEMDRECEIVEDDATE';
440 l_class_val_tbl(l_counter).value:=to_char(l_post_rec.received_date,'YYYYMMDD');
441 l_class_val_tbl(l_counter).datatype:='D';
442 l_counter:=l_counter+1;
443 l_class_val_tbl(l_counter).key:='IEMTRECEIVEDTIME';
444 l_class_val_tbl(l_counter).value:=to_char(l_post_rec.received_date,'HH24:MI:SS');
445 l_class_val_tbl(l_counter).datatype:='T';
446 l_counter:=l_counter+1;
447 l_class_val_tbl(l_counter).key:='IEMSEMAILINTENT';
448 l_class_val_tbl(l_counter).value:=l_top_intent;
449 l_class_val_tbl(l_counter).datatype:='S';
450 l_counter:=l_counter+1;
451 l_class_val_tbl(l_counter).key:='IEMNSCOREPERCENT';
452 l_class_val_tbl(l_counter).value:=l_top_score;
453 l_class_val_tbl(l_counter).datatype:='N';
454 l_counter:=l_counter+1;
455 l_class_val_tbl(l_counter).key:='IEMSLANGUAGE';
456 l_class_val_tbl(l_counter).value:=l_header_rec.language;
457 l_class_val_tbl(l_counter).datatype:='S';
458 l_counter:=l_counter+1;
459 l_class_val_tbl(l_counter).key:='IEMSORGANIZATION';
460 l_class_val_tbl(l_counter).value:=l_header_rec.organization;
461 l_class_val_tbl(l_counter).datatype:='S';
462 l_counter:=l_counter+1;
463 l_class_val_tbl(l_counter).key:='IEMDSYSTEMDATE';
464 l_class_val_tbl(l_counter).value:=to_char(sysdate,'YYYYMMDD');
465 l_class_val_tbl(l_counter).datatype:='D';
466 l_counter:=l_counter+1;
467 l_class_val_tbl(l_counter).key:='IEMTSYSTEMTIME';
468 l_class_val_tbl(l_counter).value:=to_char(sysdate,'HH24:MI:SS');
469 l_class_val_tbl(l_counter).datatype:='T';
470 l_counter:=l_counter+1;
471 l_class_val_tbl(l_counter).key:='IEMSTOADDRESS';
472 l_class_val_tbl(l_counter).value:=l_header_rec.to_str;
473 l_class_val_tbl(l_counter).datatype:='S';
474
475 -- New KEYVALUE Pair Containing All Intents Changes MAde for MP-R By RT on 08/01/03
476 l_counter:=l_counter+1;
477 l_class_val_tbl(l_counter).key:='IEMSALLINTENTS';
478 l_class_val_tbl(l_counter).value:=l_intent_str;
479 l_class_val_tbl(l_counter).datatype:='S';
480
481 IF l_wf_custom_val is not null THEN
482 l_counter:=l_counter+1;
483 l_class_val_tbl(l_counter).key:='IEMSCUSTOMWFVAL';
484 l_class_val_tbl(l_counter).value:=l_wf_custom_val;
485 l_class_val_tbl(l_counter).datatype:='S';
486 END IF;
487 IF l_customer_id is not null THEN
488 l_counter:=l_counter+1;
489 l_class_val_tbl(l_counter).key:='IEMNCUSTOMERID';
490 l_class_val_tbl(l_counter).value:=l_customer_id;
491 l_class_val_tbl(l_counter).datatype:='N';
492 END IF;
493
494 IF l_tag_keyval.count>0 THEN
495 FOR j in l_tag_keyval.FIRST..l_tag_keyval.LAST LOOP
496 l_counter:=l_counter+1;
497 l_class_val_tbl(l_counter).key:=l_tag_keyval(j).key;
498 l_class_val_tbl(l_counter).value:=l_tag_keyval(j).value;
499 l_class_val_tbl(l_counter).datatype:=l_tag_keyval(j).datatype;
500 END LOOP;
501 END IF;
502 IF l_post_rec.rt_classification_id is null THEN
503 IEM_EMAIL_PROC_PVT.IEM_CLASSIFICATION_PROC(
504 p_email_account_id=>l_post_rec.email_account_id,
505 p_keyval=>l_class_val_tbl ,
506 x_rt_classification_id=>l_rt_classification_id,
507 x_status=>l_status,
508 x_out_text=>l_out_text) ;
509 IF l_status <>'S' THEN
510 if g_error_log then
511 l_logmessage:=l_out_text;
512 iem_logger(l_logmessage);
513 end if;
514 raise abort_processing;
515 END IF;
516 if g_statement_log then
517 l_logmessage:='classification engine return route classificaion id '||l_rt_classification_id;
518 iem_logger(l_logmessage);
519 end if;
520 ELSE
521 l_rt_classification_id:=l_post_rec.rt_classification_id;
522 if g_statement_log then
523 l_logmessage:='Use Old Classification Engine Value'||l_rt_classification_id||' did not call Classification Processing again';
524 iem_logger(l_logmessage);
525 end if;
526 END IF; -- End of if rt_classification_id is null
527
528 BEGIN
529 select name
530 into l_folder_name
531 from iem_route_classifications
532 where route_classification_id=l_rt_classification_id;
533 EXCEPTION
534 WHEN OTHERS THEN
535 if g_exception_log then
536 l_logmessage:='Error in getting folder name for the route classificaion id '||l_rt_classification_id||' and the error is '||sqlerrm;
537 iem_logger(l_logmessage);
538 end if;
539 raise ABORT_PROCESSING;
540 END;
541
542 l_counter:=l_counter+1;
543 l_class_val_tbl(l_counter).key:='IEMSROUTINGCLASSIFICATION';
544 l_class_val_tbl(l_counter).value:=l_folder_name;
545 l_class_val_tbl(l_counter).datatype:='S';
546
547 -- *** Open an Interaction *** --
548
549 l_interaction_rec.start_date_time := sysdate;
550 l_resource_id:=FND_PROFILE.VALUE_SPECIFIC('IEM_SRVR_ARES') ;
551 l_interaction_rec.resource_id:=l_resource_id ;
552
553 IF l_interaction_rec.resource_id is NULL THEN
554 l_logmessage:='Default Resource Id is Not Set For Creating Interaction';
555 raise ABORT_PROCESSING;
556 END IF;
557 l_interaction_rec.handler_id := 680; -- IEM APPL_ID
558 IF l_ih_id is not null then
559 l_interaction_rec.parent_id := l_ih_id;
560 end if;
561 if l_acct_type='I' then -- For internal account type it will come from profile
562 l_customer_id := FND_PROFILE.VALUE_SPECIFIC('CS_SR_DEFAULT_CUSTOMER_NAME');
563 end if;
564 if l_customer_id >0 then -- Tag Data or pre processing contain Customer Id
565 l_interaction_rec.party_id := l_customer_id;
566 else -- Pre processing/TAg data fails to retrun customer id
567 IEM_GETCUST_PVT.CustomerSearch(
568 P_Api_Version_Number=>1.0,
569 p_email=>l_sender,
570 x_party_id=>l_cust_search_id,
571 x_msg_count=>l_msg_count,
572 x_return_status=>l_ret_status,
573 x_msg_data=>l_msg_data);
574 l_interaction_rec.party_id := l_cust_search_id;
575 end if;
576 IF l_tag_custid>0 THEN
577 -- Tag DAta return customer contact details. So create the interaction by logging contact,primary party and
578 -- relation id.
579 BEGIN
580 select PARTY_TYPE into l_party_type from HZ_PARTIES
581 where party_id = l_tag_custid;
582 EXCEPTION WHEN OTHERS THEN
583 l_logmessage:='Error in getting Party Type for Party id '||l_tag_custid;
584 raise ABORT_PROCESSING;
585 END;
586 IF l_party_type='PERSON' THEN
587 l_interaction_rec.primary_party_id:=l_tag_custid;
588 l_interaction_rec.contact_party_id:=l_tag_custid;
589 l_interaction_rec.contact_rel_party_id:=null;
590 ELSIF ( l_party_type = 'ORGANIZATION') then
591 l_interaction_rec.primary_party_id:=l_tag_custid;
592 if ( g_contact_id > 0 ) then
593 if ( (g_relation_id < 0) OR (g_relation_id is null)) then
594 l_interaction_rec.contact_party_id:=null;
595 l_interaction_rec.contact_rel_party_id:=null;
596 else
597 l_interaction_rec.contact_rel_party_id:=g_relation_id;
598 l_interaction_rec.contact_party_id:=g_contact_id;
599 end if;
600 else
601 l_interaction_rec.contact_party_id:=null;
602 l_interaction_rec.contact_rel_party_id:=null;
603 end if;
604 ELSE --- For PARTY_RELATIONSHIP
605 l_interaction_rec.primary_party_id:=null;
606 l_interaction_rec.contact_party_id:=null;
607 l_interaction_rec.contact_rel_party_id:=null;
608 END IF;
609 END IF; -- end if for if l_tag_custid>0
610
611 -- if account type is internal use employee party id as contact id for
612 --creating interaction
613 if l_acct_type='I' then
614 l_interaction_rec.contact_party_id:=l_cust_contact_id;
615 l_interaction_rec.primary_party_id:=l_customer_id;
616 -- Ranjan for bug 7018980 so that outbound interaction
617 -- has employee id as contact id;
618 g_contact_id:=l_cust_contact_id;
619 end if;
620 IF l_post_rec.ih_interaction_id is not null and l_post_rec.msg_status='REDIRECT' THEN --chk auto redirected
621 l_interaction_id:=l_post_rec.ih_interaction_id;
622 if g_statement_log then
623 l_logmessage:='New Interaction is not created and use old one for Redirect message ';
624 iem_logger(l_logmessage);
625 end if;
626 ELSE
627 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
628 p_type=>'INTERACTION' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
629 p_action=>'ADD' , -- ADD/UPDATE/CLOSE
630 p_interaction_rec=>l_interaction_rec,
631 p_activity_rec=>l_activity_rec ,
632 p_media_lc_rec=>l_media_lc_Rec ,
633 p_media_rec=>l_media_rec ,
634 x_id=>l_interaction_id,
635 x_status=>l_stat ,
636 x_out_text=>l_out_text );
637 select contact_party_id into l_cust_contact_id from jtf_ih_interactions
638 where interaction_id=l_interaction_id;
639 IF l_stat<>'S' THEN
640 l_logmessage:=l_out_text;
641 raise ABORT_PROCESSING;
642 END IF;
643 END IF; -- End of chk auto redirected
644 -- Add Interaction ID as key value Pair
645 l_counter:=l_counter+1;
646 l_class_val_tbl(l_counter).key:='IEMNINTERACTIONID';
647 l_class_val_tbl(l_counter).value:=l_interaction_id;
648 l_class_val_tbl(l_counter).datatype:='N';
649 l_routing_classification:=l_folder_name;
650 IF lengthb(l_header_rec.subject)>80 then
651 l_ih_subject:=substrb(l_header_rec.subject,1,80);
652 ELSE
653 l_ih_subject:=l_header_rec.subject;
654 END IF;
655 IF l_post_rec.ih_media_item_id is null THEN -- Normal Mail Proc create media
656 l_media_rec.media_id := NULL;
657 l_media_rec.source_id := l_post_rec.email_account_id;
658 l_media_rec.direction:= 'INBOUND';
659 l_media_rec.start_date_time := sysdate;
660 l_media_rec.media_item_type := 'EMAIL';
661 l_media_rec.media_item_ref := l_post_rec.message_id; -- Change for 11iX
662 l_media_rec.media_data := l_ih_subject;
663 l_media_rec.classification := l_routing_classification;
664 l_media_rec.address := l_sender; --new on MP-R 07/23/03 by rtripath
665 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
666 p_type=>'MEDIA' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
667 p_action=>'ADD' , -- ADD/UPDATE/CLOSE
668 p_interaction_rec=>l_interaction_rec,
669 p_activity_rec=>l_activity_rec ,
670 p_media_lc_rec=>l_media_lc_Rec ,
671 p_media_rec=>l_media_rec ,
672 x_id=>l_media_id,
673 x_status=>l_stat ,
674 x_out_text=>l_out_text );
675 IF l_stat<>'S' THEN
676 l_logmessage:=l_out_text;
677 raise ABORT_PROCESSING;
678 END IF;
679 ELSE -- Rerouted Message Need to update the media item
680 l_media_rec.media_id:=l_post_rec.ih_media_item_id;
681 l_media_rec.source_id := l_post_rec.email_account_id;
682 l_media_rec.classification := l_routing_classification;
683 l_media_rec.address := l_sender; --new on MP-R 07/23/03 by rtripath
684
685 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
686 p_type=>'MEDIA' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
687 p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
688 p_interaction_rec=>l_interaction_rec,
689 p_activity_rec=>l_activity_rec ,
690 p_media_lc_rec=>l_media_lc_Rec ,
691 p_media_rec=>l_media_rec ,
692 x_id=>l_media_id,
693 x_status=>l_stat ,
694 x_out_text=>l_out_text );
695 IF l_stat<>'S' THEN
696 l_logmessage:=l_out_text;
697 raise ABORT_PROCESSING;
698 END IF;
699 l_media_id:=l_post_rec.ih_media_item_id;
700 END IF;
701 -- Add Media Id to the key value Pair
702 l_counter:=l_counter+1;
703 l_class_val_tbl(l_counter).key:='IEMNMEDIAID';
704 l_class_val_tbl(l_counter).value:=l_media_id;
705 l_class_val_tbl(l_counter).datatype:='N';
706
707 -- Take out SOURCEMESSAGEID in 11iX as an input to Rules/ClassificationEngine
708
709 l_counter:=l_counter+1;
710 l_class_val_tbl(l_counter).key:='IEMNMESSAGEID';
711 l_class_val_tbl(l_counter).value:=l_post_rec.message_id;
712 l_class_val_tbl(l_counter).datatype:='N';
713
714 -- Create a Media Life Cycle for Mail Preprocessing
715
716 l_media_lc_rec.media_id :=l_media_id ;
717 l_media_lc_rec.milcs_type_id := 16; --MAIL_PREPROCESSING
718 IF l_post_rec.ih_media_item_id is null THEN -- normal message
719 l_media_lc_rec.start_date_time := l_post_rec.received_date;
720 else
721 l_media_lc_rec.start_date_time :=sysdate; -- for reroute/redirected message
722 end if;
723 l_media_lc_rec.handler_id := 680;
724 l_media_lc_rec.type_type := 'Email, Inbound';
725
726 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
727 p_type=>'MLCS' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
728 p_action=>'ADD' , -- ADD/UPDATE/CLOSE
729 p_interaction_rec=>l_interaction_rec,
730 p_activity_rec=>l_activity_rec ,
731 p_media_lc_rec=>l_media_lc_Rec ,
732 p_media_rec=>l_media_rec ,
733 x_id=>l_milcs_id,
734 x_status=>l_stat ,
735 x_out_text=>l_out_text );
736
737 IF l_stat<>'S' THEN
738 l_logmessage:=l_out_text;
739 raise ABORT_PROCESSING;
740 END IF;
741
742 -- Update the Media Life Cycle for Mail Preprocessing
743 l_media_lc_rec.milcs_id:=l_milcs_id;
744
745 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
746 p_type=>'MLCS' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
747 p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
748 p_interaction_rec=>l_interaction_rec,
749 p_activity_rec=>l_activity_rec ,
750 p_media_lc_rec=>l_media_lc_Rec ,
751 p_media_rec=>l_media_rec ,
752 x_id=>l_milcs_id,
753 x_status=>l_stat ,
754 x_out_text=>l_out_text );
755
756 IF l_stat<>'S' THEN
757 l_logmessage:=l_out_text;
758 raise ABORT_PROCESSING;
759 END IF;
760
761 -- Create a Media Life Cycle for Mail Processing
762 l_media_lc_rec.media_id :=l_media_id ;
763 l_media_lc_rec.milcs_type_id := 17; --MAIL_PROCESSING
764 l_media_lc_rec.start_date_time := sysdate;
765 l_media_lc_rec.handler_id := 680;
766 l_media_lc_rec.type_type := 'Email, Inbound';
767
768 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
769 p_type=>'MLCS' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
770 p_action=>'ADD' , -- ADD/UPDATE/CLOSE
771 p_interaction_rec=>l_interaction_rec,
772 p_activity_rec=>l_activity_rec ,
773 p_media_lc_rec=>l_media_lc_Rec ,
774 p_media_rec=>l_media_rec ,
775 x_id=>l_mp_milcs_id,
776 x_status=>l_stat ,
777 x_out_text=>l_out_text );
778 IF l_stat<>'S' THEN
779 l_logmessage:=l_out_text;
780 raise ABORT_PROCESSING;
781 END IF;
782 -- this is the new default activity for each mail that undergoes processing. Introduced in 11510.
783 -- Add a Activity for EMAILPROCESSING
784 l_activity_rec.start_date_time := SYSDATE;
785 l_activity_rec.media_id := l_media_id;
786 l_activity_rec.action_id := 95; -- EMAILPROCESSED
787 l_activity_rec.interaction_id := l_interaction_id;
788 l_activity_rec.action_item_id := 45;-- EMAIL
789
790 IEM_EMAIl_PROC_PVT.IEM_PROC_IH(
791 p_type=>'ACTIVITY' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
792 p_action=>'ADD' , -- ADD/UPDATE/CLOSE
793 p_interaction_rec=>l_interaction_rec,
794 p_activity_rec=>l_activity_rec ,
795 p_media_lc_rec=>l_media_lc_Rec ,
796 p_media_rec=>l_media_rec ,
797 x_id=>l_activity_id,
798 x_status=>l_stat ,
799 x_out_text=>l_out_text );
800
801 IF l_stat<>'S' THEN
802 l_logmessage:=l_out_text;
803 raise ABORT_PROCESSING;
804 END IF;
805
806 -- Calling Auto Processing Engine For 'AUTODELETE'
807 iem_rules_engine_pub.auto_process_email(p_api_version_number=>1.0,
808 p_commit=>FND_API.G_FALSE,
809 p_rule_type=>'AUTODELETE',
810 p_keyvals_tbl=>l_class_val_tbl,
811 p_accountid=>l_post_rec.email_account_id,
812 x_result=>l_autoproc_result,
813 x_action=>l_action,
814 x_parameters=>l_param_rec_tbl,
815 x_return_status=>l_ret_status,
816 x_msg_count=>l_msg_count,
817 x_msg_data=>l_msg_data);
818 IF l_ret_status<>'S' THEN
819 l_logmessage:='Error While Calling rules Engine for Autodelete';
820 raise ABORT_PROCESSING;
821 END IF;
822 IF l_autoproc_result='T' THEN -- Delete the message
823 -- Create a New MLCS for AUTO_DELETE
824 l_media_lc_rec.media_id :=l_media_id ;
825 l_media_lc_rec.milcs_type_id := 42; --EMAIL_AUTO_DELETE
826 l_media_lc_rec.start_date_time := sysdate;
827 l_media_lc_rec.handler_id := 680;
828 l_media_lc_rec.type_type := 'Email, Inbound';
829
830 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
831 p_type=>'MLCS' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
832 p_action=>'ADD' , -- ADD/UPDATE/CLOSE
833 p_interaction_rec=>l_interaction_rec,
834 p_activity_rec=>l_activity_rec ,
835 p_media_lc_rec=>l_media_lc_Rec ,
836 p_media_rec=>l_media_rec ,
837 x_id=>l_milcs_id,
838 x_status=>l_stat ,
839 x_out_text=>l_out_text );
840 IF l_stat<>'S' THEN
841 l_logmessage:=l_out_text;
842 raise ABORT_PROCESSING;
843 END IF;
844 -- Update the Media Life Cycle for Auto Delete
845 l_media_lc_rec.milcs_id:=l_milcs_id;
846
847 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
848 p_type=>'MLCS' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
849 p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
850 p_interaction_rec=>l_interaction_rec,
851 p_activity_rec=>l_activity_rec ,
852 p_media_lc_rec=>l_media_lc_Rec ,
853 p_media_rec=>l_media_rec ,
854 x_id=>l_milcs_id,
855 x_status=>l_stat ,
856 x_out_text=>l_out_text );
857
858 IF l_stat<>'S' THEN
859 l_logmessage:=l_out_text;
860 raise ABORT_PROCESSING;
861 END IF;
862
863 -- Add a Activity for AUTO-DELETE
864 l_activity_rec.start_date_time := SYSDATE;
865 l_activity_rec.media_id := l_media_id;
866 l_activity_rec.action_id := 72; -- EMAIL AUTO _DELETED
867 l_activity_rec.interaction_id := l_interaction_id;
868 l_activity_rec.action_item_id := 45;-- EMAIL
869
870 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
871 p_type=>'ACTIVITY' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
872 p_action=>'ADD' , -- ADD/UPDATE/CLOSE
873 p_interaction_rec=>l_interaction_rec,
874 p_activity_rec=>l_activity_rec ,
875 p_media_lc_rec=>l_media_lc_Rec ,
876 p_media_rec=>l_media_rec ,
877 x_id=>l_activity_id,
878 x_status=>l_stat ,
879 x_out_text=>l_out_text );
880
881 IF l_stat<>'S' THEN
882 l_logmessage:=l_out_text;
883 raise ABORT_PROCESSING;
884 END IF;
885 IEM_RT_PROC_EMAILS_PVT.create_item (
886 p_api_version_number => 1.0,
887 p_init_msg_list=>'F' ,
888 p_commit=>'F',
889 p_message_id =>l_post_rec.message_id,
890 p_email_account_id =>l_post_rec.email_account_id,
891 p_priority =>l_post_rec.priority,
892 p_agent_id =>-1,
893 p_group_id =>-1,
894 p_sent_date =>l_header_Rec.sent_date,
895 p_received_date =>l_post_Rec.received_Date,
896 p_rt_classification_id =>l_rt_classification_id,
897 p_customer_id=>l_customer_id ,
898 p_contact_id=>g_contact_id ,
899 p_relationship_id=>g_relation_id ,
900 p_interaction_id=>l_interaction_id ,
901 p_ih_media_item_id=>l_media_id ,
902 p_msg_status=>l_post_rec.msg_status ,
903 p_mail_proc_status=>'D' ,
904 p_mail_item_status=>null ,
905 p_category_map_id=>null ,
906 p_rule_id=>l_rule_id,
907 p_subject=>l_header_rec.subject,
908 p_sender_address=>l_sender,
909 p_from_agent_id=>null,
910 x_return_status=>l_ret_status ,
911 x_msg_count=>l_msg_count ,
912 x_msg_data=>l_msg_data);
913
914 IF l_ret_status<>'S' THEN
915 l_logmessage:='AUTODELETE:Error While Inserting Record in Proc Emails Table ';
916 raise ABORT_PROCESSING;
917 END IF;
918
919 -- This wrapup procedure will close the interaction and media and Stamp the message as Deleted in Archived Table.
920 IEM_EMAIL_PROC_PVT.IEM_WRAPUP(p_interaction_id=>l_interaction_id,
921 p_media_id=>l_media_id ,
922 p_milcs_id=>l_mp_milcs_id,
923 p_action=>'D',
924 p_email_rec =>l_post_rec,
925 p_action_id=>72,
926 x_out_text=>l_out_text,
927 x_status=>l_stat );
928 IF l_stat<>'S' THEN
929 l_logmessage:='Error in Auto Delete '||l_out_text;
930 raise ABORT_PROCESSING;
931 ELSE -- success in deleting the message
932 raise STOP_PROCESSING;
933 END IF;
934 END IF;
935
936 -- Calling Rules Engine for autoacknowledge
937 IF l_post_rec.ih_media_item_id is not null then --chk autoack for rerouted message
938 l_autoack_count:=0;
939 select count(*) into l_autoack_count
940 from jtf_ih_media_item_lc_segs
941 where media_id=l_post_rec.ih_media_item_id
942 and MILCS_TYPE_ID=29;
943 END IF;
944 IF l_autoack_count=0 then --not auto acked yet for this message
945 l_autoack_flag:='N';
946 iem_rules_engine_pub.auto_process_email(p_api_version_number=>1.0,
947 p_commit=>FND_API.G_FALSE,
948 p_rule_type=>'AUTOACKNOWLEDGE',
949 p_keyvals_tbl=>l_class_val_tbl,
950 p_accountid=>l_post_rec.email_account_id,
951 x_result=>l_autoproc_result,
952 x_action=>l_action,
953 x_parameters=>l_param_rec_tbl,
954 x_return_status=>l_ret_status,
955 x_msg_count=>l_msg_count,
956 x_msg_data=>l_msg_data);
957 IF l_ret_status<>'S' THEN
958 l_logmessage:='Error While Calling rules Engine for Autoacknowledgement';
959 raise ABORT_PROCESSING;
960 END IF;
961 IF l_autoproc_result='T' THEN
962 FOR l_param_index in l_param_rec_tbl.FIRST..l_param_rec_tbl.LAST LOOP
963 l_doc_id:=l_param_rec_tbl(l_param_index).parameter2;
964 EXIT;
965 END LOOP;
966 l_autoack_flag:='Y';
967 END IF; -- For Autoack rule processing
968 END IF; -- for if autaock=0
969 -- Calling Rules Engine for AutoProcessing
970 l_param_rec_tbl.delete;
971 iem_rules_engine_pub.auto_process_email(p_api_version_number=>1.0,
972 p_commit=>FND_API.G_FALSE,
973 p_rule_type=>'AUTOPROCESSING',
974 p_keyvals_tbl=>l_class_val_tbl,
975 p_accountid=>l_post_rec.email_account_id,
976 x_result=>l_autoproc_result,
977 x_action=>l_action,
978 x_parameters=>l_param_rec_tbl,
979 x_return_status=>l_ret_status,
980 x_msg_count=>l_msg_count,
981 x_msg_data=>l_msg_data);
982 IF l_ret_status<>'S' THEN
983 l_logmessage:='Error While Calling rules Engine for Autoprocessing';
984 raise ABORT_PROCESSING;
985 END IF;
986
987 IF l_autoproc_result='T' THEN
988 IF l_action='EXECPROCEDURE' THEN
989 IF ((l_post_rec.ih_media_item_id is not null) and
990 (FND_PROFILE.VALUE_SPECIFIC('IEM_RERUN_CUSTOM_PROCEDURE')='N')) THEN
991 if g_statement_log then
992 l_logmessage:='Not Executing Custom Procedure Again ';
993 iem_logger(l_logmessage);
994 end if;
995 ELSE
996
997 FOR l_param_index in l_param_rec_tbl.FIRST..l_param_rec_tbl.LAST LOOP
998 l_proc_name:=l_param_rec_tbl(l_param_index).parameter1;
999 EXIT;
1000 END LOOP;
1001 IF l_proc_name is not null THEN
1002 l_run_proc_tbl.delete;
1003 l_tbl_counter:=1;
1004 FOR i in l_class_val_tbl.first..l_class_val_tbl.last LOOP
1005 l_run_proc_tbl(l_tbl_counter).key:=l_class_val_tbl(i).key;
1006 l_run_proc_tbl(l_tbl_counter).value:=l_class_val_tbl(i).value;
1007 l_run_proc_tbl(l_tbl_counter).datatype:=l_class_val_tbl(i).datatype;
1008 l_tbl_counter:=l_tbl_counter+1;
1009 END LOOP;
1010 IEM_TAG_RUN_PROC_PVT.run_Procedure (
1011 p_api_version_number=>1.0 ,
1012 p_procedure_name=>l_proc_name ,
1013 p_key_value =>l_run_proc_tbl,
1014 x_result=>l_result ,
1015 x_return_status=>l_ret_status ,
1016 x_msg_count=>l_msg_count,
1017 x_msg_data=>l_msg_data);
1018 IF l_ret_status='E' THEN
1019 l_logmessage:='Error While Executing Custom Procedure';
1020 raise ABORT_PROCESSING;
1021 ELSE
1022 IF l_result='N' THEN
1023 l_activity_rec.start_date_time := SYSDATE;
1024 l_activity_rec.media_id := l_media_id;
1025 l_activity_rec.action_id := 65; -- EMAIL Resolved No Reply
1026 l_activity_rec.interaction_id := l_interaction_id;
1027 l_activity_rec.action_item_id := 45;-- EMAIL
1028
1029 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
1030 p_type=>'ACTIVITY' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
1031 p_action=>'ADD' , -- ADD/UPDATE/CLOSE
1032 p_interaction_rec=>l_interaction_rec,
1033 p_activity_rec=>l_activity_rec ,
1034 p_media_lc_rec=>l_media_lc_Rec ,
1035 p_media_rec=>l_media_rec ,
1036 x_id=>l_activity_id,
1037 x_status=>l_stat ,
1038 x_out_text=>l_out_text );
1039
1040 IF l_stat<>'S' THEN
1041 l_logmessage:=l_out_text;
1042 raise ABORT_PROCESSING;
1043 END IF;
1044 l_media_lc_rec.media_id :=l_media_id ;
1045 l_media_lc_rec.milcs_type_id := 31; --EMAIL_AUTO_RESOLVED
1046 l_media_lc_rec.start_date_time := sysdate;
1047 l_media_lc_rec.handler_id := 680;
1048 l_media_lc_rec.type_type := 'Email, Inbound';
1049
1050 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
1051 p_type=>'MLCS' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
1052 p_action=>'ADD' , -- ADD/UPDATE/CLOSE
1053 p_interaction_rec=>l_interaction_rec,
1054 p_activity_rec=>l_activity_rec ,
1055 p_media_lc_rec=>l_media_lc_Rec ,
1056 p_media_rec=>l_media_rec ,
1057 x_id=>l_milcs_id,
1058 x_status=>l_stat ,
1059 x_out_text=>l_out_text );
1060 IF l_stat<>'S' THEN
1061 l_logmessage:=l_out_text;
1062 raise ABORT_PROCESSING;
1063 END IF;
1064 -- Update the Media Life Cycle for Auto Resolve
1065 l_media_lc_rec.milcs_id:=l_milcs_id;
1066
1067 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
1068 p_type=>'MLCS' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
1069 p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
1070 p_interaction_rec=>l_interaction_rec,
1071 p_activity_rec=>l_activity_rec ,
1072 p_media_lc_rec=>l_media_lc_Rec ,
1073 p_media_rec=>l_media_rec ,
1074 x_id=>l_milcs_id,
1075 x_status=>l_stat ,
1076 x_out_text=>l_out_text );
1077
1078 IF l_stat<>'S' THEN
1079 l_logmessage:=l_out_text;
1080 raise ABORT_PROCESSING;
1081 END IF;
1082 -- Create a Record in IEM_RT_PROC_EMAILS
1083 IEM_RT_PROC_EMAILS_PVT.create_item (
1084 p_api_version_number => 1.0,
1085 p_init_msg_list=>'F' ,
1086 p_commit=>'F',
1087 p_message_id =>l_post_rec.message_id,
1088 p_email_account_id =>l_post_rec.email_account_id,
1089 p_priority =>l_post_rec.priority,
1090 p_agent_id =>-1,
1091 p_group_id =>-1,
1092 p_sent_date =>l_header_Rec.sent_date,
1093 p_received_date =>l_post_Rec.received_Date,
1094 p_rt_classification_id =>l_rt_classification_id,
1095 p_customer_id=>l_customer_id ,
1096 p_contact_id=>g_contact_id ,
1097 p_relationship_id=>g_relation_id ,
1098 p_interaction_id=>l_interaction_id ,
1099 p_ih_media_item_id=>l_media_id ,
1100 p_msg_status=>l_post_rec.msg_status ,
1101 p_mail_proc_status=>'R' ,
1102 p_mail_item_status=>null ,
1103 p_category_map_id=>null ,
1104 p_rule_id=>l_rule_id,
1105 p_subject=>l_header_rec.subject,
1106 p_sender_address=>l_sender,
1107 p_from_agent_id=>null,
1108 x_return_status=>l_ret_status ,
1109 x_msg_count=>l_msg_count ,
1110 x_msg_data=>l_msg_data);
1111 IF l_ret_status<>'S' THEN
1112 l_logmessage:='EXECPROC:Error While Inserting Record in Proc Emails Table ';
1113 raise ABORT_PROCESSING;
1114 END IF;
1115 IEM_EMAIL_PROC_PVT.IEM_WRAPUP(p_interaction_id=>l_interaction_id,
1116 p_media_id=>l_media_id ,
1117 p_milcs_id=>l_mp_milcs_id,
1118 p_action=>'R',
1119 p_email_rec =>l_post_rec,
1120 p_action_id=>65,
1121 x_out_text=>l_out_text,
1122 x_status=>l_stat );
1123
1124 IF l_stat='E' THEN
1125 l_logmessage:=l_out_text;
1126 raise ABORT_PROCESSING;
1127 END IF;
1128 raise STOP_PROCESSING;
1129 END IF;
1130 END IF;
1131 END IF;
1132 END IF; -- end if for if media id is not null
1133 ELSIF l_action in ('AUTOCREATESR','UPDSERVICEREQID') then
1134 -- Populate l_email_doc_tbl for both autocreate and autoupdate SR
1135 FOR l_param_index in l_param_rec_tbl.FIRST..l_param_rec_tbl.LAST LOOP
1136 l_status_id:=to_number(l_param_rec_tbl(l_param_index).parameter1);
1137 l_email_doc_tbl(l_param_index).doc_id:=l_param_rec_tbl(l_param_index).parameter2;
1138 l_email_doc_tbl(l_param_index).type:='I';
1139 EXIT;
1140 END LOOP;
1141 l_qual_tbl.delete;
1142 IEM_EMAIL_PROC_PVT.IEM_GET_MERGEVAL(p_email_account_id=>l_post_rec.email_account_id ,
1143 p_mailer =>l_header_rec.from_str,
1144 p_dflt_sender=>l_sender ,
1145 p_subject=>l_header_rec.subject,
1146 x_qual_tbl=> l_qual_tbl,
1147 x_status=>l_status,
1148 x_out_text=>l_out_Text);
1149 IF l_action='AUTOCREATESR' THEN
1150 -- Retrieve Party Id
1151 if l_acct_type='I' then -- select the party id from profile
1152 l_emp_flag:='Y';
1153 BEGIN
1154 Select person_id into l_contact_id
1155 from per_workforce_current_x
1156 Where upper(email_address)=upper(l_sender);
1157 exception when others then
1158 l_contact_id:=null;
1159 END;
1160 l_party_id:=FND_PROFILE.VALUE_SPECIFIC('CS_SR_DEFAULT_CUSTOMER_NAME');
1161 else -- External Account
1162 l_emp_flag:='N';
1163 if l_customer_id>0 then
1164 -- Check to see if party identified is a contact or not
1165 select party_type into l_party_type from hz_parties
1166 where party_id=l_customer_id;
1167 if l_party_type='PARTY_RELATIONSHIP' then
1168 -- Try to identify the actual party here
1169 begin
1170 select object_id into l_party_id
1171 from HZ_RELATIONSHIPS where party_id= l_customer_id and
1172 (relationship_code='CONTACT_OF' or relationship_code='EMPLOYEE_OF')
1173 and status='A';
1174 l_contact_id:=l_customer_id;
1175 select contact_point_id into l_contact_point_id
1176 from hz_contact_points
1177 where owner_table_name='HZ_PARTIES'
1178 and owner_table_id=l_customer_id
1179 and contact_point_type='EMAIL'
1180 and upper(email_address)=upper(l_sender)
1181 and contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
1182 where contact_level_table='HZ_CONTACT_POINTS' and status='A');
1183 exception when others then
1184 l_party_id:=null;
1185 end;
1186 else
1187 l_party_id:=l_customer_id; -- Exact match
1188 l_Contact_id:=g_contact_id;
1189 end if;
1190 elsif l_customer_id=-1 then -- No customer match check profile
1191 -- for what to do next
1192 if fnd_profile.value_specific('IEM_SR_NO_CUST')='CREATESR' then
1193 l_party_id:=fnd_profile.value_specific('IEM_DEFAULT_CUSTOMER_ID');
1194 else
1195 l_party_id:=null;
1196 end if;
1197 else -- Multiple Customer Match.
1198 l_party_id:=null;
1199 end if;
1200
1201 end if;
1202 IF l_party_id is not null then -- Go ahead with creating service request
1203 -- Retrieve the note for the email
1204 iem_text_pvt.RETRIEVE_TEXT(p_message_id=>l_message_id,
1205 x_text=>l_text,
1206 x_status=>l_status);
1207 if l_status<>'S' then
1208 l_logmessage:='Error Encountered while Retrieving Notes';
1209 raise ABORT_PROCESSING;
1210 end if;
1211 l_note_type:=FND_PROFILE.VALUE_SPECIFIC('IEM_SR_NOTE_TYPE');
1212 IEM_SERVICEREQUEST_PVT.IEM_CREATE_SR(
1213 p_api_version => 1.0,
1214 p_init_msg_list => FND_API.G_TRUE,
1215 p_commit => FND_API.G_FALSE,
1216 p_message_id => l_message_id,
1217 p_note => l_text,
1218 p_party_id => l_party_id,
1219 p_sr_type_id => l_status_id,
1220 p_subject =>l_header_rec.subject,
1221 p_employee_flag =>l_emp_flag,
1222 p_note_type => l_note_type,
1223 p_contact_id => l_contact_id,
1224 p_contact_point_id =>l_contact_point_id,
1225 x_return_status => l_ret_status,
1226 x_msg_count => l_msg_count,
1227 x_msg_data => l_msg_data,
1228 x_request_id => l_sr_id);
1229 IF l_ret_status='S' then
1230 -- Add MLCS for Email Resolved as SR created
1231 l_media_lc_rec.media_id :=l_media_id ;
1232 l_media_lc_rec.milcs_type_id := 31;
1233 l_media_lc_rec.start_date_time := sysdate;
1234 l_media_lc_rec.handler_id := 680;
1235 l_media_lc_rec.type_type := 'Email, Inbound';
1236
1237 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
1238 p_type=>'MLCS' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
1239 p_action=>'ADD' , -- ADD/UPDATE/CLOSE
1240 p_interaction_rec=>l_interaction_rec,
1241 p_activity_rec=>l_activity_rec ,
1242 p_media_lc_rec=>l_media_lc_Rec ,
1243 p_media_rec=>l_media_rec ,
1244 x_id=>l_milcs_id,
1245 x_status=>l_stat ,
1246 x_out_text=>l_out_text );
1247 IF l_stat<>'S' THEN
1248 l_logmessage:=l_out_text;
1249 raise ABORT_PROCESSING;
1250 END IF;
1251 IEM_RT_PROC_EMAILS_PVT.create_item (
1252 p_api_version_number => 1.0,
1253 p_init_msg_list=>'F' ,
1254 p_commit=>'F',
1255 p_message_id =>l_post_rec.message_id,
1256 p_email_account_id =>l_post_rec.email_account_id,
1257 p_priority =>l_post_rec.priority,
1258 p_agent_id =>-1,
1259 p_group_id =>-1,
1260 p_sent_date =>l_header_Rec.sent_date,
1261 p_received_date =>l_post_Rec.received_Date,
1262 p_rt_classification_id =>l_rt_classification_id,
1263 p_customer_id=>l_customer_id ,
1264 p_contact_id=>g_contact_id ,
1265 p_relationship_id=>g_relation_id ,
1266 p_interaction_id=>l_interaction_id ,
1267 p_ih_media_item_id=>l_media_id ,
1268 p_msg_status=>l_post_rec.msg_status ,
1269 p_mail_proc_status=>'R' ,
1270 p_mail_item_status=>null ,
1271 p_category_map_id=>null ,
1272 p_rule_id=>l_rule_id,
1273 p_subject=>l_header_rec.subject,
1274 p_sender_address=>l_sender,
1275 p_from_agent_id=>null,
1276 x_return_status=>l_ret_status ,
1277 x_msg_count=>l_msg_count ,
1278 x_msg_data=>l_msg_data);
1279 IF l_ret_status<>'S' THEN
1280 l_logmessage:='AUTOCREATESR:Error While Inserting Record in Proc Emails Table ';
1281 raise ABORT_PROCESSING;
1282 END IF;
1283 -- Update the Media Life Cycle for Auto Create SR
1284 l_media_lc_rec.milcs_id:=l_milcs_id;
1285 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
1286 p_type=>'MLCS' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
1287 p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
1288 p_interaction_rec=>l_interaction_rec,
1289 p_activity_rec=>l_activity_rec ,
1290 p_media_lc_rec=>l_media_lc_Rec ,
1291 p_media_rec=>l_media_rec ,
1292 x_id=>l_id,
1293 x_status=>l_stat ,
1294 x_out_text=>l_out_text );
1295 IF l_stat<>'S' THEN
1296 l_logmessage:=l_out_text;
1297 raise ABORT_PROCESSING;
1298 END IF;
1299 -- Create Activity
1300 -- Add a Activity for AUTO-CREATE SR
1301 l_activity_rec.start_date_time := SYSDATE;
1302 l_activity_rec.media_id := l_media_id;
1303 l_activity_rec.action_id := 65; -- Email Resolved
1304 l_activity_rec.interaction_id := l_interaction_id;
1305 l_activity_rec.action_item_id := 45;-- EMAIL
1306 l_activity_rec.DOC_ID := l_sr_id;
1307 l_activity_rec.DOC_REF := 'SR';
1308 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
1309 p_type=>'ACTIVITY' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
1310 p_action=>'ADD' , -- ADD/UPDATE/CLOSE
1311 p_interaction_rec=>l_interaction_rec,
1312 p_activity_rec=>l_activity_rec ,
1313 p_media_lc_rec=>l_media_lc_Rec ,
1314 p_media_rec=>l_media_rec ,
1315 x_id=>l_activity_id,
1316 x_status=>l_stat ,
1317 x_out_text=>l_out_text );
1318
1319 IF l_stat<>'S' THEN
1320 l_logmessage:=l_out_text;
1321 raise ABORT_PROCESSING;
1322 END IF;
1323 -- Add a Activity for CREATE SR
1324 -- select result reason outcome for activity
1325 select wu.outcome_id, wu.result_id, wu.reason_id INTO
1326 l_activity_rec.outcome_id, l_activity_rec.result_id, l_activity_rec.reason_id
1327 from jtf_ih_action_action_items aa, jtf_ih_wrap_ups wu
1328 where aa.action_id =65
1329 and aa.action_item_id =45
1330 and aa.default_wrap_id = wu.wrap_id;
1331 l_activity_rec.start_date_time := SYSDATE;
1332 l_activity_rec.media_id := l_media_id;
1333 l_activity_rec.action_id := 13; -- Create SR
1334 l_activity_rec.interaction_id := l_interaction_id;
1335 l_activity_rec.action_item_id := 17;-- SR
1336 l_activity_rec.DOC_ID := l_sr_id;
1337 l_activity_rec.DOC_REF := 'SR';
1338 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
1339 p_type=>'ACTIVITY' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
1340 p_action=>'ADD' , -- ADD/UPDATE/CLOSE
1341 p_interaction_rec=>l_interaction_rec,
1342 p_activity_rec=>l_activity_rec ,
1343 p_media_lc_rec=>l_media_lc_Rec ,
1344 p_media_rec=>l_media_rec ,
1345 x_id=>l_activity_id,
1346 x_status=>l_stat ,
1347 x_out_text=>l_out_text );
1348
1349 IF l_stat<>'S' THEN
1350 l_logmessage:=l_out_text;
1351 raise ABORT_PROCESSING;
1352 END IF;
1353 -- Update the mail Processing Life Cycles
1354 l_media_lc_rec.milcs_id:=l_mp_milcs_id;
1355 l_media_lc_rec.milcs_type_id:=17;
1356 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
1357 p_type=>'MLCS' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
1358 p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
1359 p_interaction_rec=>l_interaction_rec,
1360 p_activity_rec=>l_activity_rec ,
1361 p_media_lc_rec=>l_media_lc_Rec ,
1362 p_media_rec=>l_media_rec ,
1363 x_id=>l_id,
1364 x_status=>l_stat ,
1365 x_out_text=>l_out_text );
1366
1367 IF l_stat<>'S' THEN
1368 l_logmessage:=l_out_text;
1369 raise ABORT_PROCESSING;
1370 END IF;
1371 -- Check for Sending out notifications
1372 l_noti_flag:=FND_PROFILE.VALUE_SPECIFIC('IEM_SR_CREATE_NOTI');
1373 if l_noti_flag='Y' then
1374 -- update the itneraction with result reason outcome
1375 select wu.outcome_id, wu.result_id, wu.reason_id INTO
1376 l_interaction_rec.outcome_id, l_interaction_rec.result_id, l_interaction_rec.reason_id
1377 from jtf_ih_action_action_items aa, jtf_ih_wrap_ups wu
1378 where aa.action_id =65
1379 and aa.action_item_id =45
1380 and aa.default_wrap_id = wu.wrap_id;
1381 l_interaction_rec.interaction_id:=l_interaction_id;
1382 select contact_party_id into l_cust_contact_id from jtf_ih_interactions
1383 where interaction_id=l_interaction_id;
1384 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
1385 p_type=>'INTERACTION' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
1386 p_action=>'CLOSE' , -- ADD/UPDATE/CLOSE
1387 p_interaction_rec=>l_interaction_rec,
1388 p_activity_rec=>l_activity_rec ,
1389 p_media_lc_rec=>l_media_lc_Rec ,
1390 p_media_rec=>l_media_rec ,
1391 x_id=>l_id,
1392 x_status=>l_stat ,
1393 x_out_text=>l_out_text );
1394
1395 IF l_stat<>'S' THEN
1396 l_logmessage:=l_out_text;
1397 raise ABORT_PROCESSING;
1398 END IF;
1399 IEM_AUTOREPLY(p_interaction_id=>l_interaction_id ,
1400 p_media_id=>l_media_id,
1401 p_post_rec=>l_post_rec,
1402 p_doc_tbl=>l_email_doc_tbl,
1403 p_subject=>l_header_rec.subject,
1404 P_TAG_KEY_VALUE_TBL=>l_outbox_tbl ,
1405 P_CUSTOMER_ID=>l_customer_id ,
1406 P_RESOURCE_ID=>l_resource_id,
1407 p_qualifiers =>l_qual_tbl,
1408 p_fwd_address=>null,
1409 p_fwd_doc_id=>l_sr_id, -- Pass SR id
1410 p_req_type=>'N', -- For autonotifications
1411 x_out_text=>l_out_text,
1412 x_status=>l_stat ) ;
1413
1414 IF l_stat<>'S' THEN
1415 l_logmessage:=l_out_text;
1416 raise ABORT_PROCESSING;
1417 END IF;
1418 l_autoack_flag:='N'; --so that no autoack is send.
1419 else
1420 -- Resolving the message without sending notifications
1421 IEM_EMAIL_PROC_PVT.IEM_WRAPUP(p_interaction_id=>l_interaction_id,
1422 p_media_id=>l_media_id ,
1423 p_milcs_id=>l_mp_milcs_id,
1424 p_action=>'R',
1425 p_email_rec =>l_post_rec,
1426 p_action_id=>65,
1427 x_out_text=>l_out_text,
1428 x_status=>l_stat );
1429 IF l_stat<>'S' THEN
1430 l_logmessage:=l_out_text;
1431 raise ABORT_PROCESSING;
1432 END IF;
1433 end if; -- for if l_noti_flag='Y'
1434 raise STOP_PROCESSING;
1435 else -- Sr creation fails so if it is internal type in that casecheck prfile value for next action. in case of external account it is always route.
1436 if l_acct_type='I' then
1437 if (fnd_profile.value_specific('IEM_SR_NOT_UPDATED'))='REDIRECT'
1438 then
1439 l_redirect_flag:='Y';
1440 end if;
1441 end if;
1442
1443 END IF; -- for if l_Ret_status='S' from create sr api
1444 end if; -- for if l_party_id is not null
1445 -- End of create Sr
1446 ELSE -- This is a update service request
1447 IF (l_status_id is not null) and (l_sr_id is not null) then
1448 IEM_EMAIL_PROC_PVT.IEM_SRSTATUS_UPDATE(p_sr_id=>l_sr_id ,
1449 p_status_id=>l_status_id,
1450 p_email_rec=>l_post_rec,
1451 x_status =>l_stat,
1452 x_out_text=>l_out_text) ;
1453 IF l_stat='S' then
1454 -- Add a Activity for AUTO-UPDATE OF SR
1455 l_activity_rec.start_date_time := SYSDATE;
1456 l_activity_rec.media_id := l_media_id;
1457 l_activity_rec.action_id := 75; -- Auto Update Of SR
1458 l_activity_rec.interaction_id := l_interaction_id;
1459 l_activity_rec.action_item_id := 45;-- EMAIL
1460 l_activity_rec.DOC_ID := l_sr_id;
1461 l_activity_rec.DOC_REF := 'SR';
1462 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
1463 p_type=>'ACTIVITY' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
1464 p_action=>'ADD' , -- ADD/UPDATE/CLOSE
1465 p_interaction_rec=>l_interaction_rec,
1466 p_activity_rec=>l_activity_rec ,
1467 p_media_lc_rec=>l_media_lc_Rec ,
1468 p_media_rec=>l_media_rec ,
1469 x_id=>l_activity_id,
1470 x_status=>l_stat ,
1471 x_out_text=>l_out_text );
1472
1473 IF l_stat<>'S' THEN
1474 l_logmessage:=l_out_text;
1475 raise ABORT_PROCESSING;
1476 END IF;
1477 --Update Intearctionwith result reason outcome
1478 -- Create a Media Life Cycle for Auto update of SR
1479 l_media_lc_rec.media_id :=l_media_id ;
1480 l_media_lc_rec.milcs_type_id := 40; --EMAIL_AUTO_UPDATED_SR
1481 l_media_lc_rec.start_date_time := sysdate;
1482 l_media_lc_rec.handler_id := 680;
1483 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
1484 p_type=>'MLCS' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
1485 p_action=>'ADD' , -- ADD/UPDATE/CLOSE
1486 p_interaction_rec=>l_interaction_rec,
1487 p_activity_rec=>l_activity_rec ,
1488 p_media_lc_rec=>l_media_lc_Rec ,
1489 p_media_rec=>l_media_rec ,
1490 x_id=>l_id,
1491 x_status=>l_stat ,
1492 x_out_text=>l_out_text );
1493 IF l_stat<>'S' THEN
1494 l_logmessage:=l_out_text;
1495 raise ABORT_PROCESSING;
1496 END IF;
1497 -- Update the Media Life Cycle for Auto Update of SR
1498 l_media_lc_rec.milcs_id:=l_id;
1499 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
1500 p_type=>'MLCS' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
1501 p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
1502 p_interaction_rec=>l_interaction_rec,
1503 p_activity_rec=>l_activity_rec ,
1504 p_media_lc_rec=>l_media_lc_Rec ,
1505 p_media_rec=>l_media_rec ,
1506 x_id=>l_id,
1507 x_status=>l_stat ,
1508 x_out_text=>l_out_text );
1509 IF l_stat<>'S' THEN
1510 l_logmessage:=l_out_text;
1511 raise ABORT_PROCESSING;
1512 END IF;
1513
1514 IEM_RT_PROC_EMAILS_PVT.create_item (
1515 p_api_version_number => 1.0,
1516 p_init_msg_list=>'F' ,
1517 p_commit=>'F',
1518 p_message_id =>l_post_rec.message_id,
1519 p_email_account_id =>l_post_rec.email_account_id,
1520 p_priority =>l_post_rec.priority,
1521 p_agent_id =>-1,
1522 p_group_id =>-1,
1523 p_sent_date =>l_header_Rec.sent_date,
1524 p_received_date =>l_post_Rec.received_Date,
1525 p_rt_classification_id =>l_rt_classification_id,
1526 p_customer_id=>l_customer_id ,
1527 p_contact_id=>g_contact_id ,
1528 p_relationship_id=>g_relation_id ,
1529 p_interaction_id=>l_interaction_id ,
1530 p_ih_media_item_id=>l_media_id ,
1531 p_msg_status=>l_post_rec.msg_status ,
1532 p_mail_proc_status=>'R' ,
1533 p_mail_item_status=>null ,
1534 p_category_map_id=>null ,
1535 p_rule_id=>l_rule_id,
1536 p_subject=>l_header_rec.subject,
1537 p_sender_address=>l_sender,
1538 p_from_agent_id=>null,
1539 x_return_status=>l_ret_status ,
1540 x_msg_count=>l_msg_count ,
1541 x_msg_data=>l_msg_data);
1542 IF l_ret_status<>'S' THEN
1543 l_logmessage:='AUTOUPDSR:Error While Inserting Record in Proc Emails Table ';
1544 raise ABORT_PROCESSING;
1545 END IF;
1546 -- Check for Sending Out Notifications
1547 l_noti_flag:=FND_PROFILE.VALUE_SPECIFIC('IEM_SR_UPDATE_NOTI');
1548 if l_noti_flag='Y' then -- Sends out notifications
1549
1550 -- update the itneraction with result reason outcome
1551 select wu.outcome_id, wu.result_id, wu.reason_id INTO
1552 l_interaction_rec.outcome_id, l_interaction_rec.result_id, l_interaction_rec.reason_id
1553 from jtf_ih_action_action_items aa, jtf_ih_wrap_ups wu
1554 where aa.action_id =75
1555 and aa.action_item_id =45
1556 and aa.default_wrap_id = wu.wrap_id;
1557 l_interaction_rec.interaction_id:=l_interaction_id;
1558 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
1559 p_type=>'INTERACTION' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
1560 p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
1561 p_interaction_rec=>l_interaction_rec,
1562 p_activity_rec=>l_activity_rec ,
1563 p_media_lc_rec=>l_media_lc_Rec ,
1564 p_media_rec=>l_media_rec ,
1565 x_id=>l_id,
1566 x_status=>l_stat ,
1567 x_out_text=>l_out_text );
1568
1569
1570 IF l_stat<>'S' THEN
1571 l_logmessage:=l_out_text;
1572 raise ABORT_PROCESSING;
1573 END IF;
1574 l_outbox_tbl.delete;
1575 IF l_tag_keyval.count>0 THEN
1576 FOR i IN l_tag_keyval.FIRST..l_tag_keyval.LAST LOOP
1577 l_outbox_tbl(i).key:=l_tag_keyval(i).key;
1578 l_outbox_tbl(i).value:=l_tag_keyval(i).value;
1579 l_outbox_tbl(i).datatype:=l_tag_keyval(i).datatype;
1580 END LOOP;
1581 END IF;
1582 IEM_AUTOREPLY(p_interaction_id=>l_interaction_id ,
1583 p_media_id=>l_media_id,
1584 p_post_rec=>l_post_rec,
1585 p_doc_tbl=>l_email_doc_tbl,
1586 p_subject=>l_header_rec.subject,
1587 P_TAG_KEY_VALUE_TBL=>l_outbox_tbl ,
1588 P_CUSTOMER_ID=>l_customer_id ,
1589 P_RESOURCE_ID=>l_resource_id,
1590 p_qualifiers =>l_qual_tbl,
1591 p_fwd_address=>null,
1592 p_fwd_doc_id=>l_sr_id, -- Pass the SR id
1593 p_req_type=>'N', -- For autonotifications
1594 x_out_text=>l_out_text,
1595 x_status=>l_stat ) ;
1596
1597 IF l_stat<>'S' THEN
1598 l_logmessage:=l_out_text;
1599 raise ABORT_PROCESSING;
1600 END IF;
1601 l_autoack_flag:='N';
1602 -- Update the mail Processing Life Cycles
1603 l_media_lc_rec.milcs_id:=l_mp_milcs_id;
1604 l_media_lc_rec.milcs_type_id:=17;
1605 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
1606 p_type=>'MLCS' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
1607 p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
1608 p_interaction_rec=>l_interaction_rec,
1609 p_activity_rec=>l_activity_rec ,
1610 p_media_lc_rec=>l_media_lc_Rec ,
1611 p_media_rec=>l_media_rec ,
1612 x_id=>l_id,
1613 x_status=>l_stat ,
1614 x_out_text=>l_out_text );
1615
1616 IF l_stat<>'S' THEN
1617 l_logmessage:=l_out_text;
1618 raise ABORT_PROCESSING;
1619 END IF;
1620 else -- notification flag is not set so resolve the message
1621 IEM_EMAIL_PROC_PVT.IEM_WRAPUP(p_interaction_id=>l_interaction_id,
1622 p_media_id=>l_media_id ,
1623 p_milcs_id=>l_mp_milcs_id,
1624 p_action=>'R',
1625 p_email_rec =>l_post_rec,
1626 p_action_id=>75,
1627 x_out_text=>l_out_text,
1628 x_status=>l_stat );
1629 IF l_stat<>'S' THEN
1630 l_logmessage:=l_out_text;
1631 raise ABORT_PROCESSING;
1632 END IF;
1633 end if; -- for if l_noti_flag='Y';
1634 raise STOP_PROCESSING;
1635 ELSE
1636 -- Sr updation fails so if it is internal type in that casecheck prfile value for next action. in case of external account it is always route.
1637 if l_acct_type='I' then
1638 if (fnd_profile.value_specific('IEM_SR_NOT_UPDATED'))='REDIRECT'
1639 then
1640 l_redirect_flag:='Y';
1641 end if;
1642 end if;
1643 END IF; -- if l_stat='S' from SR update api
1644 ELSE
1645 if g_statement_log then
1646 l_logmessage:='Status Id is not Set at Profile OR SR# is not present in TAG Not updating the SR';
1647 iem_logger(l_logmessage);
1648 end if;
1649 END IF; -- if l_status_id is not null
1650 END IF; -- indicual processing ends
1651 END IF; -- elsif l_Action in ('AUTOCREATESR','AUTOUPDATESR');
1652 END IF; -- if l_autoproc_result='T'
1653 -- Check for autoack Flag if set then send autoack.
1654 IF l_autoack_flag='Y' then
1655 if g_statement_log then
1656 l_logmessage:='Start Sending Out Autoacknowledgement' ;
1657 iem_logger(l_logmessage);
1658 end if;
1659 FND_MESSAGE.Set_Name('IEM','IEM_ADM_AUTO_ACK_CUSTOMER');
1660 FND_MSG_PUB.Add;
1661 l_dflt_sender := FND_MSG_PUB.GET(FND_MSG_pub.Count_Msg,FND_API.G_FALSE);
1662 IEM_EMAIL_PROC_PVT.IEM_AUTOACK(p_email_user=>l_email_user_name,
1663 p_mailer =>l_header_rec.from_str,
1664 p_sender=>l_sender,
1665 p_subject=>l_header_rec.subject,
1666 p_domain_name=>l_email_domain_name,
1667 p_document_id =>l_doc_id,
1668 p_dflt_sender=>l_dflt_sender,
1669 p_int_id=>l_interaction_id,
1670 p_master_account_id=>l_post_rec.email_account_id,
1671 x_status=>l_status,
1672 x_out_text=>l_out_text);
1673 l_media_lc_rec.media_id :=l_media_id ;
1674 l_media_lc_rec.milcs_type_id := 29; --MAIL_AUTOACKNOWLEDGED
1675 l_media_lc_rec.start_date_time := sysdate;
1676 l_media_lc_rec.handler_id := 680;
1677 l_media_lc_rec.type_type := 'Email, Inbound';
1678
1679 IF l_status<>'S' THEN -- Create MLCS after auto ack
1680 if g_error_log then
1681 l_logmessage:='Error In Autoack '||l_out_text;
1682 iem_logger(l_logmessage);
1683 end if;
1684 ELSE
1685 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
1686 p_type=>'MLCS' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
1687 p_action=>'ADD' , -- ADD/UPDATE/CLOSE
1688 p_interaction_rec=>l_interaction_rec,
1689 p_activity_rec=>l_activity_rec ,
1690 p_media_lc_rec=>l_media_lc_Rec ,
1691 p_media_rec=>l_media_rec ,
1692 x_id=>l_milcs_id,
1693 x_status=>l_stat ,
1694 x_out_text=>l_out_text );
1695
1696 l_media_lc_rec.milcs_id:=l_milcs_id;
1697 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
1698 p_type=>'MLCS' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
1699 p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
1700 p_interaction_rec=>l_interaction_rec,
1701 p_activity_rec=>l_activity_rec ,
1702 p_media_lc_rec=>l_media_lc_Rec ,
1703 p_media_rec=>l_media_rec ,
1704 x_id=>l_milcs_id,
1705 x_status=>l_stat ,
1706 x_out_text=>l_out_text );
1707 END IF; -- End of MLCS Creation
1708 END IF; -- End if for if l_autoack_flag='Y';
1709 -- Calling Rules Engine for AUTO-REDIRECT Type
1710 -- Prior to calling this check if the email is to be redirected on the profile
1711 -- set for autocreate/auto update SR fails for employee type
1712 if l_redirect_flag='N' then
1713 iem_rules_engine_pub.auto_process_email(p_api_version_number=>1.0,
1714 p_commit=>FND_API.G_FALSE,
1715 p_rule_type=>'AUTOREDIRECT',
1716 p_keyvals_tbl=>l_class_val_tbl,
1717 p_accountid=>l_post_rec.email_account_id,
1718 x_result=>l_autoproc_result,
1719 x_action=>l_action,
1720 x_parameters=>l_param_rec_tbl,
1721 x_return_status=>l_ret_status,
1722 x_msg_count=>l_msg_count,
1723 x_msg_data=>l_msg_data);
1724 IF l_ret_status<>'S' THEN
1725 l_logmessage:='Error While Calling rules Engine for AUTOREDIRECT';
1726 raise ABORT_PROCESSING;
1727 END IF;
1728 else -- As auto redirect flag is set for autoupdate/create SR
1729 l_autoproc_result:='T';
1730 l_action:='AUTOREDIRECT_EXTERNAL';
1731 l_ext_address:=fnd_profile.value_specific('IEM_SR_REDIRECT_EMAIL_ADDR');
1732 end if;
1733 if l_action='AUTOREDIRECT_EXTERNAL' and l_autoproc_result='T' THEN
1734 IF l_auto_forward_flag='Y' THEN --donot autoforward to a already autofwd message
1735 l_autoproc_result:='F';
1736 END IF;
1737 end if;
1738 IF ((l_autoproc_result='T') AND (l_action is not null)) THEN
1739 -- Create the activity and necessary MLCS for auto redirect
1740 IF l_action='AUTOREDIRECT_EXTERNAL' THEN
1741 l_media_lc_rec.milcs_type_id := 49; --EMAIL_AUTO_REDIRECTD_EXTERNAL
1742 ELSE
1743 l_media_lc_rec.milcs_type_id := 48; --EMAIL_AUTO_REDIRECTD_INTERNAL
1744 l_media_lc_rec.resource_id:=l_post_rec.email_account_id;
1745
1746 END IF;
1747 -- Create the MILCS
1748 l_media_lc_rec.media_id :=l_media_id ;
1749 l_media_lc_rec.start_date_time := sysdate;
1750 l_media_lc_rec.handler_id := 680;
1751 -- Create MLCS for Auto-Redirect
1752 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
1753 p_type=>'MLCS' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
1754 p_action=>'ADD' , -- ADD/UPDATE/CLOSE
1755 p_interaction_rec=>l_interaction_rec,
1756 p_activity_rec=>l_activity_rec ,
1757 p_media_lc_rec=>l_media_lc_Rec ,
1758 p_media_rec=>l_media_rec ,
1759 x_id=>l_milcs_id,
1760 x_status=>l_stat ,
1761 x_out_text=>l_out_text );
1762 IF l_stat<>'S' THEN
1763 l_logmessage:=l_out_text;
1764 raise ABORT_PROCESSING;
1765 END IF;
1766 -- Update the Media Life Cycle for Auto RRRR
1767 l_media_lc_rec.milcs_id:=l_milcs_id;
1768 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
1769 p_type=>'MLCS' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
1770 p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
1771 p_interaction_rec=>l_interaction_rec,
1772 p_activity_rec=>l_activity_rec ,
1773 p_media_lc_rec=>l_media_lc_Rec ,
1774 p_media_rec=>l_media_rec ,
1775 x_id=>l_milcs_id,
1776 x_status=>l_stat ,
1777 x_out_text=>l_out_text );
1778
1779 IF l_stat<>'S' THEN
1780 l_logmessage:=l_out_text;
1781 raise ABORT_PROCESSING;
1782 END IF;
1783 IF (l_action='AUTOREDIRECT_EXTERNAL') THEN
1784 if l_redirect_flag='N' then
1785 FOR l_param_index in l_param_rec_tbl.FIRST..l_param_rec_tbl.LAST LOOP
1786 l_ext_address:=l_param_rec_tbl(l_param_index).parameter1;
1787 l_ext_temp_id:=to_number(l_param_rec_tbl(l_param_index).type);
1788 EXIT;
1789 END LOOP;
1790 end if;
1791 -- Calling Outbox Processor API
1792
1793 l_outbox_tbl.delete;
1794 l_qual_tbl.delete;
1795 IF l_tag_keyval.count>0 THEN
1796 FOR i IN l_tag_keyval.FIRST..l_tag_keyval.LAST LOOP
1797 l_outbox_tbl(i).key:=l_tag_keyval(i).key;
1798 l_outbox_tbl(i).value:=l_tag_keyval(i).value;
1799 l_outbox_tbl(i).datatype:=l_tag_keyval(i).datatype;
1800 END LOOP;
1801 END IF;
1802 l_f_name:=substr(l_from_folder,2,length(l_from_folder));
1803 if l_customer_id>0 then
1804 l_cust_search_id:=l_customer_id;
1805 end if;
1806 IEM_AUTOREPLY(p_interaction_id=>l_interaction_id ,
1807 p_media_id=>l_media_id,
1808 p_post_rec=>l_post_rec,
1809 p_doc_tbl=>l_email_doc_tbl,
1810 p_subject=>l_header_rec.subject,
1811 P_TAG_KEY_VALUE_TBL=>l_outbox_tbl ,
1812 P_CUSTOMER_ID=>l_cust_search_id ,
1813 P_RESOURCE_ID=>l_resource_id,
1814 p_qualifiers =>l_qual_tbl,
1815 p_fwd_address=>l_ext_address,
1816 p_fwd_doc_id=>l_ext_temp_id,
1817 p_req_type=>'F', -- For autoforward
1818 x_out_text=>l_out_text,
1819 x_status=>l_stat ) ;
1820
1821 IF l_stat<>'S' THEN
1822 l_logmessage:=l_out_text;
1823 raise ABORT_PROCESSING;
1824 END IF;
1825 l_auto_msgstatus:='XREDIRECT'; -- Xternal redirect
1826 raise STOP_AUTO_PROCESSING;
1827 ELSIF (l_action='AUTOREDIRECT_INTERNAL') THEN
1828 FOR l_param_index in l_param_rec_tbl.FIRST..l_param_rec_tbl.LAST LOOP
1829 l_redirect_id:=l_param_rec_tbl(l_param_index).parameter1;
1830 EXIT;
1831 END LOOP;
1832 -- Need new code to complete this .
1833 -- Create a Record for New Email Accounts in PREPROC Tables and Stop Processing for the
1834 -- Current One
1835 delete from iem_email_classifications where message_id=l_post_rec.message_id;
1836 update iem_rt_preproc_emails
1837 set email_account_id=l_redirect_id,
1838 msg_status='REDIRECT',
1839 ih_media_item_id=l_media_id
1840 where message_id=l_post_rec.message_id;
1841
1842 IEM_EMAIL_PROC_PVT.IEM_WRAPUP(p_interaction_id=>l_interaction_id,
1843 p_media_id=>l_media_id ,
1844 p_milcs_id=>l_mp_milcs_id,
1845 p_action=>null,
1846 p_email_rec =>l_post_rec,
1847 p_action_id=>72,
1848 x_out_text=>l_out_text,
1849 x_status=>l_stat );
1850 IF l_stat<>'S' THEN
1851 l_logmessage:=l_out_text;
1852 raise ABORT_PROCESSING;
1853 END IF;
1854 raise STOP_REDIRECT_PROCESSING;
1855 END IF; -- End if for both redirect actions
1856 END IF; --End if for autoproc_result='T'
1857 -- Calling Rules Engine For Auto-Reply
1858 iem_rules_engine_pub.auto_process_email(p_api_version_number=>1.0,
1859 p_commit=>FND_API.G_FALSE,
1860 p_rule_type=>'AUTORRRS',
1861 p_keyvals_tbl=>l_class_val_tbl,
1862 p_accountid=>l_post_rec.email_account_id,
1863 x_result=>l_autoproc_result,
1864 x_action=>l_action,
1865 x_parameters=>l_param_rec_tbl,
1866 x_return_status=>l_ret_status,
1867 x_msg_count=>l_msg_count,
1868 x_msg_data=>l_msg_data);
1869 IF l_ret_status<>'S' THEN
1870 l_logmessage:='Error While Calling rules Engine for Auto-Reply';
1871 raise ABORT_PROCESSING;
1872 END IF;
1873 IF ((l_autoproc_result='T') AND (l_action is not null)) THEN
1874 IF ((l_action='AUTOREPLYSPECIFIEDDOC') AND (l_auto_reply_flag='N')) THEN
1875 FOR l_param_index in l_param_rec_tbl.FIRST..l_param_rec_tbl.LAST LOOP
1876 -- Add code for integration into Outbox Processing
1877 l_email_doc_tbl(l_param_index).doc_id:=l_param_rec_tbl(l_param_index).parameter2;
1878 l_email_doc_tbl(l_param_index).type:=l_param_rec_tbl(l_param_index).type;
1879 END LOOP;
1880 l_outbox_tbl.delete;
1881 IF l_tag_keyval.count>0 THEN
1882 FOR i IN l_tag_keyval.FIRST..l_tag_keyval.LAST LOOP
1883 l_outbox_tbl(i).key:=l_tag_keyval(i).key;
1884 l_outbox_tbl(i).value:=l_tag_keyval(i).value;
1885 l_outbox_tbl(i).datatype:=l_tag_keyval(i).datatype;
1886 END LOOP;
1887 END IF;
1888 if l_customer_id>0 then
1889 l_cust_search_id:=l_customer_id;
1890 end if;
1891
1892 IEM_AUTOREPLY(p_interaction_id=>l_interaction_id ,
1893 p_media_id=>l_media_id,
1894 p_post_rec=>l_post_rec,
1895 p_doc_tbl=>l_email_doc_tbl,
1896 p_subject=>l_header_rec.subject,
1897 P_TAG_KEY_VALUE_TBL=>l_outbox_tbl ,
1898 P_CUSTOMER_ID=>l_cust_search_id ,
1899 P_RESOURCE_ID=>l_resource_id,
1900 p_qualifiers =>l_qual_tbl,
1901 p_fwd_address=>null,
1902 p_fwd_doc_id=>null,
1903 p_req_type=>'R', --for autoreply
1904 x_out_text=>l_out_text,
1905 x_status=>l_stat ) ;
1906
1907 IF l_stat='S' THEN
1908 l_auto_msgstatus:='AUTOREPLY';
1909 raise STOP_AUTO_PROCESSING;
1910 ELSE
1911 l_logmessage:=l_out_text;
1912 END IF;
1913
1914 END IF; -- End if for l_action=AUTOREPLYSPECIFIED
1915 END IF; -- End if for autoproc_result='T'
1916 -- Calling DOCUMENT_RETRIEVAL 11.5.10 feature
1917 l_rule_id:=0;
1918 iem_rules_engine_pub.auto_process_email(p_api_version_number=>1.0,
1919 p_commit=>FND_API.G_FALSE,
1920 p_rule_type=>'DOCUMENTRETRIEVAL',
1921 p_keyvals_tbl=>l_class_val_tbl,
1922 p_accountid=>l_post_rec.email_account_id,
1923 x_result=>l_autoproc_result,
1924 x_action=>l_action,
1925 x_parameters=>l_param_rec_tbl,
1926 x_return_status=>l_ret_status,
1927 x_msg_count=>l_msg_count,
1928 x_msg_data=>l_msg_data);
1929 IF l_ret_status<>'S' THEN
1930 l_logmessage:='Error While Calling rules Engine for DOCUMENTRETRIEVAL';
1931 raise ABORT_PROCESSING;
1932 END IF;
1933 IF l_autoproc_result='T' THEN
1934 if l_action <> 'MES_CATEGORY_MAPPING' THEN
1935 l_search_type:=substr(l_action,15,length(l_action));
1936 -- identfiying the repository to search
1937 if l_search_type='MES' THEN
1938 l_repos:='MES';
1939 elsif l_search_type='KM' THEN
1940 l_repos:='SMS';
1941 elsif l_search_type='BOTH' THEN
1942 l_repos:='ALL';
1943 end if;
1944 l_cat_counter:=1;
1945 IF l_param_rec_tbl.count>0 THEN
1946 FOR l_param_index in l_param_rec_tbl.FIRST..l_param_rec_tbl.LAST LOOP
1947 IF l_param_rec_tbl(l_param_index).parameter1 <> to_char(-1) then
1948 IF l_param_rec_tbl(l_param_index).parameter1='RULE_ID' then
1949 l_rule_id:=l_param_rec_tbl(l_param_index).parameter2;
1950 ELSE
1951 l_category_id.extend;
1952 l_category_id(l_cat_counter):=l_param_rec_tbl(l_param_index).parameter1;
1953 l_cat_counter:=l_cat_counter+1;
1954 END IF;
1955 END IF;
1956 END LOOP;
1957 END IF;
1958 else
1959 l_search_type:='CM'; --Category based mapping
1960 FOR l_param_index in l_param_rec_tbl.FIRST..l_param_rec_tbl.LAST LOOP
1961 l_cm_cat_id:=l_param_rec_tbl(l_param_index).parameter1;
1962 EXIT;
1963 END LOOP;
1964 end if;
1965 else
1966 l_search_type:=null;
1967 l_cm_Cat_id:=0;
1968 end if ; -- end if for l_autoproc_result='T'
1969 -- CALLING ROUTING ----
1970 IEM_EMAIL_PROC_PVT.IEM_ROUTING_PROC(
1971 p_email_account_id=>l_post_rec.email_account_id,
1972 p_keyval=>l_class_val_tbl,
1973 x_routing_group_id=>l_group_id,
1974 x_status=>l_status,
1975 x_out_text=>l_out_text) ;
1976 IF l_status <>'S' THEN
1977 l_logmessage:=l_out_text;
1978 if g_error_log then
1979 iem_logger(l_logmessage);
1980 raise abort_processing;
1981 end if;
1982 END IF;
1983 IF l_group_id=-1 then
1984 -- pre 11510 group id for the auto routed message is determined by first group the agent belongs to.Because of
1985 -- supervisor agent inbox requeue message it is defaulted to 0 from 11.5.10.So that anybody can access it.
1986 l_group_id:=0;
1987 l_auto_flag:='Y';
1988 END IF;
1989
1990 IEM_RT_PROC_EMAILS_PVT.create_item (
1991 p_api_version_number => 1.0,
1992 p_init_msg_list=>'F' ,
1993 p_commit=>'F',
1994 p_message_id =>l_post_rec.message_id,
1995 p_email_account_id =>l_post_rec.email_account_id,
1996 p_priority =>l_post_rec.priority,
1997 p_agent_id =>0,
1998 p_group_id =>l_group_id,
1999 p_sent_date =>l_header_Rec.sent_date,
2000 p_received_date =>l_post_Rec.received_Date,
2001 p_rt_classification_id =>l_rt_classification_id,
2002 p_customer_id=>l_customer_id ,
2003 p_contact_id=>g_contact_id ,
2004 p_relationship_id=>g_relation_id ,
2005 p_interaction_id=>l_interaction_id ,
2006 p_ih_media_item_id=>l_media_id ,
2007 p_msg_status=>l_post_rec.msg_status ,
2008 p_mail_proc_status=>'P' ,
2009 p_mail_item_status=>'N' ,
2010 p_category_map_id=>l_cm_cat_id ,
2011 p_rule_id=>l_rule_id,
2012 p_subject=>l_header_rec.subject,
2013 p_sender_address=>l_sender ,
2014 p_from_agent_id=>null,
2015 x_return_status=>l_ret_status ,
2016 x_msg_count=>l_msg_count ,
2017 x_msg_data=>l_msg_data);
2018 IF l_ret_status='S' THEN
2019 IF l_auto_flag='Y' THEN -- auto Routing Processing
2020 SAVEPOINT auto_route_main;
2021 -- creating RT item bug 7428636
2022 IEM_CLIENT_PUB.createRTItem (p_api_version_number=>1.0,
2023 p_init_msg_list=>'F',
2024 p_commit=>'F',
2025 p_message_id =>l_post_rec.message_id,
2026 p_to_resource_id =>l_agentid,
2027 p_from_resource_id =>l_agentid,
2028 p_status =>'N',
2029 p_reason =>'O',
2030 p_interaction_id =>l_interaction_id,
2031 x_return_status =>l_ret_status,
2032 x_msg_count =>l_msg_count,
2033 x_msg_data =>l_msg_data,
2034 x_rt_media_item_id =>l_rt_media_item_id,
2035 x_rt_interaction_id =>l_rt_interaction_id);
2036 IF l_ret_status<>'S' THEN
2037 if g_error_log then
2038 l_logmessage:='Failed To Auto Route The Message due to error in create RT Item ';
2039 iem_logger(l_logmessage);
2040 end if;
2041 ELSE -- as part of bug fix 7428636
2042 -- Able to create RT Item So do autorouting
2043 update iem_rt_proc_emails
2044 set resource_id=l_agentid
2045 where message_id=l_post_rec.message_id;
2046 -- Create MLCS for Auto Routing
2047
2048 l_media_lc_rec.media_id :=l_media_id ;
2049 l_media_lc_rec.milcs_type_id := 30; --MAIL_AUTOROUTE
2050 l_media_lc_rec.start_date_time := sysdate;
2051 l_media_lc_rec.handler_id := 680;
2052 l_media_lc_rec.type_type := 'Email, Inbound';
2053 l_media_lc_rec.resource_id := l_agentid;
2054 l_stat:='S' ; -- reset to 'S' before starting any MLCS
2055 -- IH activity;
2056
2057 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
2058 p_type=>'MLCS' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
2059 p_action=>'ADD' , -- ADD/UPDATE/CLOSE
2060 p_interaction_rec=>l_interaction_rec,
2061 p_activity_rec=>l_activity_rec ,
2062 p_media_lc_rec=>l_media_lc_Rec ,
2063 p_media_rec=>l_media_rec ,
2064 x_id=>l_milcs_id,
2065 x_status=>l_stat ,
2066 x_out_text=>l_out_text );
2067 IF l_stat<>'S' THEN
2068 if g_error_log then
2069 l_logmessage:='Error while creating MLCS for Auto Route '||l_out_text;
2070 iem_logger(l_logmessage);
2071 rollback to auto_route_main;
2072 end if;
2073 END IF;
2074 -- Update the Media Life Cycle for Auto Routing
2075 if l_stat='S' then
2076 l_media_lc_rec.milcs_id:=l_milcs_id;
2077 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
2078 p_type=>'MLCS' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
2079 p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
2080 p_interaction_rec=>l_interaction_rec,
2081 p_activity_rec=>l_activity_rec ,
2082 p_media_lc_rec=>l_media_lc_Rec ,
2083 p_media_rec=>l_media_rec ,
2084 x_id=>l_milcs_id,
2085 x_status=>l_stat ,
2086 x_out_text=>l_out_text );
2087
2088 IF l_stat<>'S' THEN
2089 if g_error_log then
2090 l_logmessage:='Error while updating MLCS for Auto Route '||l_out_text;
2091 iem_logger(l_logmessage);
2092 rollback to auto_route_main;
2093 end if;
2094 END IF;
2095 END IF; -- End If for if l_Stat='S'
2096 if l_stat='S' then
2097 -- In case of autoroute update the interaction with resource id of the agent to which
2098 -- the message is autorouted to
2099 l_interaction_rec.interaction_id:=l_interaction_id;
2100 l_interaction_rec.resource_id:=l_agentid;
2101 JTF_IH_PUB.Update_Interaction( p_api_version => 1.1,
2102 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2103 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2104 p_user_id =>nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
2105 p_login_id =>TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
2106 x_return_status => l_ret_status,
2107 x_msg_count => l_msg_count,
2108 x_msg_data => l_msg_data,
2109 p_interaction_rec => l_interaction_rec
2110 );
2111 IF l_ret_status<>'S' THEN
2112 IF g_error_log then
2113 l_logmessage:='Error while updating Interactions for Auto Route ';
2114 iem_logger(l_logmessage);
2115 end if;
2116 rollback to auto_route;
2117 ELSE
2118 if g_statement_log then
2119 l_logmessage:='Successfully AutoRoute The Message ';
2120 iem_logger(l_logmessage);
2121 end if;
2122 END IF; -- End If for if l_ret_status='S'
2123 END IF; -- End if for if l_stat='S'
2124 END IF; -- For the ELSE part as part of bug fix 7428636
2125 END IF; -- End If for auto routing;
2126
2127 ELSE
2128
2129 raise ERR_INSERTING;
2130 END IF;
2131 -- Update the Media Life Cycle for Mail processing
2132 l_media_lc_rec.milcs_id:=l_mp_milcs_id;
2133 l_media_lc_rec.milcs_type_id := 17;
2134 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
2135 p_type=>'MLCS' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
2136 p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
2137 p_interaction_rec=>l_interaction_rec,
2138 p_activity_rec=>l_activity_rec ,
2139 p_media_lc_rec=>l_media_lc_Rec ,
2140 p_media_rec=>l_media_rec ,
2141 x_id=>l_milcs_id,
2142 x_status=>l_stat ,
2143 x_out_text=>l_out_text );
2144
2145 IF l_stat<>'S' THEN
2146 l_logmessage:=l_out_text;
2147 raise ABORT_PROCESSING;
2148 END IF;
2149 delete from iem_rt_preproc_emails
2150 where message_id=l_post_rec.message_id;
2151 -- Calling the specific search at the End
2152 if g_statement_log then
2153 l_logmessage:='Calling Specific Search API ' ;
2154 iem_logger(l_logmessage);
2155 end if;
2156 BEGIN
2157 IF l_search_type<>'CM' THEN -- Not a MES category based mapping
2158 l_start_search:=1;
2159 FOR v1 in c_class_id LOOP
2160 IEM_EMAIL_PROC_PVT.IEM_WF_SPECIFICSEARCH(
2161 l_post_rec.message_id ,
2162 l_post_rec.email_account_id ,
2163 v1.classification_id,
2164 l_category_id,
2165 l_repos,
2166 l_stat ,
2167 l_out_text);
2168 l_start_search:=l_start_search+1;
2169 EXIT when l_start_search>l_intent_counter;
2170 END LOOP;
2171 ELSIF nvl(l_search_type,' ')='CM' and l_cm_cat_id is not null then
2172 for v_item in c_item LOOP
2173 select count(*) into l_kb_rank
2174 from iem_doc_usage_stats
2175 where kb_doc_id=v_item.item_id;
2176 IEM_KB_RESULTS_PVT.create_item(p_api_version_number=>1.0,
2177 p_init_msg_list=>'F' ,
2178 p_commit=>'F' ,
2179 p_message_id =>l_post_rec.message_id,
2180 p_classification_id=>0,
2181 p_email_account_id=>l_post_rec.email_account_id ,
2182 p_document_id =>to_char(v_item.item_id),
2183 p_kb_repository_name =>'MES',
2184 p_kb_category_name =>'MES',
2185 p_document_title =>v_item.item_name,
2186 p_doc_last_modified_date=>v_item.last_update_date,
2187 p_score =>l_kb_rank,
2188 p_url =>' ',
2189 p_kb_delete=>'N',
2190 p_CREATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
2191 p_CREATION_DATE =>SYSDATE,
2192 p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
2193 p_LAST_UPDATE_DATE =>SYSDATE,
2194 p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
2195 p_ATTRIBUTE1 =>null,
2196 p_ATTRIBUTE2 =>null,
2197 p_ATTRIBUTE3 =>null,
2198 p_ATTRIBUTE4 =>null,
2199 p_ATTRIBUTE5 =>null,
2200 p_ATTRIBUTE6 =>null,
2201 p_ATTRIBUTE7 =>null,
2202 p_ATTRIBUTE8 =>null,
2203 p_ATTRIBUTE9 =>null,
2204 p_ATTRIBUTE10 =>null,
2205 p_ATTRIBUTE11 =>null,
2206 p_ATTRIBUTE12 =>null,
2207 p_ATTRIBUTE13 =>null,
2208 p_ATTRIBUTE14 =>null,
2209 p_ATTRIBUTE15 =>null,
2210 x_return_status=>l_ret_status,
2211 x_msg_count=>l_msg_count,
2212 x_msg_data=>l_msg_data);
2213 END LOOP;
2214 END IF; -- Endof search_type<>'CM'
2215 EXCEPTION WHEN OTHERS THEN
2216 NULL;
2217 END;
2218 if g_statement_log then
2219 l_logmessage:='End Of Calling Specific Search API and end of Processing for the message ' ;
2220 iem_logger(l_logmessage);
2221 end if;
2222 commit;
2223 EXCEPTION
2224 when STOP_AUTO_PROCESSING THEN
2225 -- Update the Media Life Cycle for Mail processing -- no need to call wrapup
2226 l_media_lc_rec.milcs_id:=l_mp_milcs_id;
2227 l_media_lc_rec.milcs_type_id := 17;
2228 iem_email_proc_pvt.IEM_PROC_IH(
2229 p_type=>'MLCS' ,-- MEDIA/ACTIVITY/MLCS/INTERACTION
2230 p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
2231 p_interaction_rec=>l_interaction_rec,
2232 p_activity_rec=>l_activity_rec ,
2233 p_media_lc_rec=>l_media_lc_Rec ,
2234 p_media_rec=>l_media_rec ,
2235 x_id=>l_milcs_id,
2236 x_status=>l_stat ,
2237 x_out_text=>l_out_text );
2238
2239 IF l_stat<>'S' THEN
2240 if g_error_log then
2241 l_logmessage:=l_out_text;
2242 l_Error_Message := 'Abort Processing '||l_logmessage;
2243 iem_logger(l_Error_Message);
2244 end if;
2245 ROLLBACK TO process_emails_pvt;
2246 -- Timestamp the message to sent it to back of queue
2247 update iem_rt_preproc_emails
2248 set creation_date=sysdate
2249 where message_id=l_post_rec.message_id;
2250 commit;
2251 ELSE
2252 -- Create a Record in IEM_RT_PROC_EMAILS_PVT
2253 IEM_RT_PROC_EMAILS_PVT.create_item (
2254 p_api_version_number => 1.0,
2255 p_init_msg_list=>'F' ,
2256 p_commit=>'F',
2257 p_message_id =>l_post_rec.message_id,
2258 p_email_account_id =>l_post_rec.email_account_id,
2259 p_priority =>l_post_rec.priority,
2260 p_agent_id =>-1,
2261 p_group_id =>-1,
2262 p_sent_date =>l_header_Rec.sent_date,
2263 p_received_date =>l_post_Rec.received_Date,
2264 p_rt_classification_id =>l_rt_classification_id,
2265 p_customer_id=>l_customer_id ,
2266 p_contact_id=>g_contact_id ,
2267 p_relationship_id=>g_relation_id ,
2268 p_interaction_id=>l_interaction_id ,
2269 p_ih_media_item_id=>l_media_id ,
2270 p_msg_status=>l_auto_msgstatus ,
2271 p_mail_proc_status=>'R' ,
2272 p_mail_item_status=>null ,
2273 p_category_map_id=>null ,
2274 p_rule_id=>l_rule_id,
2275 p_subject=>l_header_rec.subject,
2276 p_sender_address=>l_sender,
2277 p_from_agent_id=>null,
2278 x_return_status=>l_ret_status ,
2279 x_msg_count=>l_msg_count ,
2280 x_msg_data=>l_msg_data);
2281
2282 IF l_ret_status<>'S' THEN
2283 if g_error_log then
2284 l_logmessage:='AUTOREPLY:Error While Inserting Record in Proc Emails Table ';
2285 l_Error_Message := 'Abort Processing '||l_logmessage;
2286 iem_logger(l_Error_Message);
2287 end if;
2288 ROLLBACK TO process_emails_pvt;
2289 -- Timestamp the message to sent it to back of queue
2290 update iem_rt_preproc_emails
2291 set creation_date=sysdate
2292 where message_id=l_post_rec.message_id;
2293 commit;
2294 ELSE
2295 delete from iem_rt_preproc_emails where message_id=l_post_rec.message_id;
2296 commit;
2297 END IF;
2298 END IF;
2299 WHEN STOP_PROCESSING THEN
2300 delete from iem_rt_preproc_emails
2301 where message_id=l_post_rec.message_id;
2302 -- Check for autoack Flag if set then send autoack.
2303 IF l_autoack_flag='Y' then
2304 if g_statement_log then
2305 l_logmessage:='Start Sending Out Autoacknowledgement' ;
2306 iem_logger(l_logmessage);
2307 end if;
2308 FND_MESSAGE.Set_Name('IEM','IEM_ADM_AUTO_ACK_CUSTOMER');
2309 FND_MSG_PUB.Add;
2310 l_dflt_sender := FND_MSG_PUB.GET(FND_MSG_pub.Count_Msg,FND_API.G_FALSE);
2311 IEM_EMAIL_PROC_PVT.IEM_AUTOACK(p_email_user=>l_email_user_name,
2312 p_mailer =>l_header_rec.from_str,
2313 p_sender=>l_sender,
2314 p_subject=>l_header_rec.subject,
2315 p_domain_name=>l_email_domain_name,
2316 p_document_id =>l_doc_id,
2317 p_dflt_sender=>l_dflt_sender,
2318 p_int_id=>l_interaction_id,
2319 p_master_account_id=>l_post_rec.email_account_id,
2320 x_status=>l_status,
2321 x_out_text=>l_out_text);
2322 l_media_lc_rec.media_id :=l_media_id ;
2323 l_media_lc_rec.milcs_type_id := 29; --MAIL_AUTOACKNOWLEDGED
2324 l_media_lc_rec.start_date_time := sysdate;
2325 l_media_lc_rec.handler_id := 680;
2326 l_media_lc_rec.type_type := 'Email, Inbound';
2327
2328 IF l_status<>'S' THEN -- Create MLCS after auto ack
2329 if g_error_log then
2330 l_logmessage:='Error In Autoack '||l_out_text;
2331 iem_logger(l_logmessage);
2332 end if;
2333 ELSE
2334 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
2335 p_type=>'MLCS' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
2336 p_action=>'ADD' , -- ADD/UPDATE/CLOSE
2337 p_interaction_rec=>l_interaction_rec,
2338 p_activity_rec=>l_activity_rec ,
2339 p_media_lc_rec=>l_media_lc_Rec ,
2340 p_media_rec=>l_media_rec ,
2341 x_id=>l_milcs_id,
2342 x_status=>l_stat ,
2343 x_out_text=>l_out_text );
2344
2345 l_media_lc_rec.milcs_id:=l_milcs_id;
2346 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
2347 p_type=>'MLCS' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
2348 p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
2349 p_interaction_rec=>l_interaction_rec,
2350 p_activity_rec=>l_activity_rec ,
2351 p_media_lc_rec=>l_media_lc_Rec ,
2352 p_media_rec=>l_media_rec ,
2353 x_id=>l_milcs_id,
2354 x_status=>l_stat ,
2355 x_out_text=>l_out_text );
2356 END IF; -- End of MLCS Creation
2357 END IF; -- End if for if l_autoack_flag='Y';
2358 commit;
2359 if g_statement_log then
2360 l_Error_Message := 'stop Further Processing';
2361 iem_logger(l_Error_Message);
2362 end if;
2363 WHEN STOP_REDIRECT_PROCESSING THEN -- Here record can not be deleted from preproc_emails table
2364 if g_statement_log then
2365 l_Error_Message := 'stop Further Processing';
2366 iem_logger(l_Error_Message);
2367 end if;
2368 commit;
2369 WHEN ABORT_PROCESSING THEN
2370 if g_exception_log then
2371 l_Error_Message := 'Abort Processing Due to Oracle Error'||sqlerrm;
2372 iem_logger(l_Error_Message);
2373 end if;
2374 ROLLBACK TO process_emails_pvt;
2375 -- Timestamp the message to sent it to back of queue
2376 update iem_rt_preproc_emails
2377 set creation_date=sysdate
2378 where message_id=l_post_rec.message_id;
2379 commit;
2380
2381 WHEN ERR_INSERTING THEN
2382 if g_exception_log then
2383 l_logmessage := 'Unable To insert Record in Post MDT '||sqlerrm;
2384 iem_logger(l_logmessage);
2385 end if;
2386 ROLLBACK TO process_emails_pvt;
2387 update iem_rt_preproc_emails
2388 set creation_date=sysdate
2389 where message_id=l_post_rec.message_id;
2390 commit;
2391 WHEN NO_RECORD_TO_PROCESS THEN
2392 if g_statement_log then
2393 l_logmessage:='No Valid Record Found For Processing';
2394 iem_logger(l_logmessage);
2395 end if;
2396 WHEN OTHERS THEN
2397 if g_exception_log then
2398 l_logmessage:='Oracle Error Encountered in Processing'||sqlerrm;
2399 iem_logger(l_logmessage);
2400 end if;
2401 ROLLBACK TO process_emails_pvt;
2402 update iem_rt_preproc_emails
2403 set creation_date=sysdate
2404 where message_id=l_post_rec.message_id;
2405 commit;
2406 null;
2407 END;
2408 l_count:=l_count+1;
2409 EXIT when l_count>p_count;
2410 END LOOP;
2411 -- Standard Check Of p_commit.
2412 IF FND_API.To_Boolean(p_commit) THEN
2413 COMMIT WORK;
2414 END IF;
2415 EXCEPTION
2416 WHEN FND_API.G_EXC_ERROR THEN
2417 ROLLBACK TO process_emails_pvt;
2418 FND_MESSAGE.SET_NAME('IEM','IEM_RETRYPROCESS_EXEC_ERROR');
2419 l_Error_Message := FND_MESSAGE.GET;
2420 fnd_file.put_line(fnd_file.log, l_Error_Message);
2421 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_Error_Message);
2422 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2423 ROLLBACK TO process_emails_pvt;
2424 FND_MESSAGE.SET_NAME('IEM','IEM_RETRYPROCESS_UNXPTD_ERR');
2425 l_Error_Message := FND_MESSAGE.GET;
2426 fnd_file.put_line(fnd_file.log, l_Error_Message);
2427 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_Error_Message);
2428 WHEN OTHERS THEN
2429 ROLLBACK TO process_emails_pvt;
2430 FND_MESSAGE.SET_NAME('IEM','IEM_RETRYPROCESS_OTHER_ERR');
2431 l_Error_Message := SQLERRM;
2432 fnd_file.put_line(fnd_file.log, l_Error_Message);
2433 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_Error_Message);
2434
2435 END PROC_EMAILS;
2436
2437 PROCEDURE iem_logger(l_logmessage in varchar2) IS
2438 begin
2439 if g_statement_log THEN
2440 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2441 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'IEM.PLSQL.IEM_EMAIL_PROC_PVT',l_logmessage);
2442 end if;
2443 end if;
2444 if g_exception_log then
2445 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2446 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'IEM.PLSQL.IEM_EMAIL_PROC_PVT',l_logmessage);
2447 end if;
2448 end if;
2449 if g_error_log then
2450 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2451 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'IEM.PLSQL.IEM_EMAIL_PROC_PVT',l_logmessage);
2452 end if;
2453 end if;
2454 end iem_logger;
2455
2456 Procedure iem_returned_msg_rec(x_msg_rec out nocopy iem_rt_preproc_emails%ROWTYPE) is
2457 e_nowait EXCEPTION;
2458 PRAGMA EXCEPTION_INIT(e_nowait, -54);
2459 l_post_rec iem_rt_preproc_emails%rowtype;
2460 l_folder_name varchar2(20):='/Inbox';
2461 l_uid number;
2462 l_status varchar2(10);
2463 l_out_text varchar2(1000);
2464 BEGIN
2465 for x in ( select message_id
2466 from iem_rt_preproc_emails
2467 order by priority,creation_date)
2468 LOOP
2469 BEGIN
2470 select * into x_msg_rec from iem_rt_preproc_emails
2471 where message_id=x.message_id FOR UPDATE NOWAIT;
2472 exit;
2473 EXCEPTION when e_nowait then
2474 null;
2475 when others then
2476 null ;
2477 END;
2478 END LOOP;
2479 END;
2480 PROCEDURE IEM_AUTOACK(p_email_user in varchar2,
2481 p_mailer in varchar2,
2482 p_sender in varchar2,
2483 p_subject in varchar2,
2484 p_domain_name in varchar2,
2485 p_document_id in number,
2486 p_dflt_sender in varchar2,
2487 p_int_id in number,
2488 p_master_account_id in number,
2489 x_status OUT NOCOPY varchar2,
2490 x_out_text OUT NOCOPY varchar2) IS
2491 l_str varchar2(255);
2492 l_to_recip varchar2(240);
2493 l_cc_recip varchar2(240);
2494 l_index number;
2495 l_ret number;
2496 l_email_encrypt_tbl IEM_SENDMAIL_PVT.email_encrypt_tbl;
2497 l_ack_sub varchar2(250);
2498 l_status varchar2(10);
2499 l_text_data varchar2(500);
2500 l_reply_address varchar2(500);
2501 l_resource_id number;
2502 l_email_account_id number;
2503 l_qual_tbl IEM_OUTBOX_PROC_PUB.QualifierRecordList;
2504 ACK_FAILED EXCEPTION;
2505 l_ret_status varchar2(10);
2506 l_msg_count number;
2507 l_msg_data varchar2(500);
2508 l_outbox_id number;
2509 l_data varchar2(500);
2510 l_error_text varchar2(500);
2511 l_subject varchar2(500);
2512 l_msg_index_out number;
2513 BEGIN
2514 x_status:='S';
2515 IEM_EMAIL_PROC_PVT.IEM_GET_MERGEVAL(p_email_account_id=>p_master_account_id ,
2516 p_mailer=>p_mailer,
2517 p_dflt_sender=>p_dflt_sender ,
2518 p_subject=>p_subject,
2519 x_qual_tbl=> l_qual_tbl,
2520 x_status=>l_status,
2521 x_out_text=>l_text_data);
2522 if l_status<>'S' THEN
2523 raise ACK_FAILED;
2524 end if;
2525 -- Selecting Auto Ack. Subject which will be appended to original mail
2526 BEGIN
2527 select meaning||': '
2528 into l_ack_sub
2529 from fnd_lookups
2530 where lookup_type='IEM_AUTO_ACKNOWLEDGE'
2531 and lookup_code='SUBJECT';
2532 l_subject:=substr(l_ack_sub||p_subject,1,240);
2533 EXCEPTION WHEN OTHERS THEN
2534 null;
2535 END;
2536 -- Calling OP Api for sending Out Auto Acknowledgement
2537
2538 l_resource_id:=FND_PROFILE.VALUE_SPECIFIC('IEM_SRVR_ARES') ;
2539 IEM_OUTBOX_PROC_PUB.createOutboxMessage(p_api_version_number=>1.0,
2540 p_init_msg_list=>'F',
2541 p_commit=>'F',
2542 P_RESOURCE_ID=>l_resource_id,
2543 p_application_id=>680,
2544 p_responsibility_id=>null,
2545 P_MASTER_ACCOUNT_ID=>p_master_account_id,
2546 P_TO_ADDRESS_LIST=>p_sender,
2547 p_cc_address_list=>null ,
2548 p_bcc_address_list=>null,
2549 P_SUBJECT=>l_subject,
2550 P_SR_ID=>null,
2551 P_CUSTOMER_ID=>null,
2552 P_CONTACT_ID=>g_contact_id,
2553 P_INTERACTION_ID=>p_int_id,
2554 p_qualifiers =>l_qual_tbl ,
2555 P_MESSAGE_TYPE=>null,
2556 P_ENCODING=>null,
2557 P_CHARACTER_SET=>null,
2558 p_option=>'A',
2559 p_relationship_id=>g_relation_id,
2560 X_OUTBOX_ITEM_ID=>l_outbox_id,
2561 X_RETURN_STATUS=>l_ret_status,
2562 X_MSG_COUNT=>l_msg_count,
2563 X_MSG_DATA=>l_msg_data);
2564 if l_ret_status<>'S' THEN
2565 x_out_text:='Failed in createoutbox message '||l_text_data;
2566 raise ACK_FAILED;
2567 end if;
2568 IEM_OUTBOX_PROC_PUB.insertDocument(
2569 p_api_version_number=>1.0 ,
2570 p_outbox_item_id=>l_outbox_id,
2571 p_document_source=>'MES' ,
2572 p_document_id =>p_document_id ,
2573 X_RETURN_STATUS=>l_ret_status,
2574 X_MSG_COUNT=>l_msg_count,
2575 X_MSG_DATA=>l_msg_data);
2576 if l_ret_status<>'S' THEN
2577 x_out_text:='Failed in insert document '||l_text_data;
2578 raise ACK_FAILED;
2579 end if;
2580 IEM_OUTBOX_PROC_PUB.submitOutboxMessage(
2581 p_api_version_number=>1.0 ,
2582 p_init_msg_list=>'F',
2583 p_commit => 'F',
2584 p_outbox_item_id=>l_outbox_id ,
2585 p_preview_bool=>'N',
2586 X_RETURN_STATUS=>l_ret_status,
2587 X_MSG_COUNT=>l_msg_count,
2588 X_MSG_DATA=>l_msg_data);
2589 if l_ret_status<>'S' THEN
2590 x_out_text:='Failed in submit request '||l_text_data;
2591 raise ACK_FAILED;
2592 end if;
2593 x_status:='S';
2594 x_out_text:='Send Acknowledgement Successfully';
2595 EXCEPTION WHEN ACK_FAILED THEN
2596 x_status:='E';
2597 IF (l_msg_count >= 1) THEN
2598 --Only one error
2599 FND_MSG_PUB.Get(p_msg_index => FND_MSG_PUB.G_FIRST,
2600 p_encoded=>'F',
2601 p_data=>l_data,
2602 p_msg_index_out=>l_msg_index_out);
2603 l_error_text:= substr(l_data,1,500);
2604 If (l_msg_count > 1) THEN
2605 --Display all the error messages
2606 FOR j in 2..FND_MSG_PUB.Count_Msg LOOP
2607 FND_MSG_PUB.Get(p_msg_index => FND_MSG_PUB.G_NEXT,
2608 p_encoded=>'F',
2609 p_data=>l_data,
2610 p_msg_index_out=>l_msg_index_out);
2611 l_error_text:= l_error_text||substr(l_data,1,500);
2612 END LOOP;
2613 END IF;
2614 END IF;
2615 x_out_text:=x_out_text||l_error_text;
2616 When Others then
2617 x_out_text:='Oracle Error During sendmail Processings '||sqlerrm;
2618 x_status:='E';
2619 END;
2620 /* This Procedure is invoked for running the customised workflow.
2621 It return the following status .'Y' Process futher . 'N' Not required
2622 to process anymore . 'E' Returns an Error .
2623 */
2624 PROCEDURE IEM_INVOKE_WORKFLOW(p_message_id in number,
2625 p_source_message_id in number,
2626 p_message_size in number,
2627 p_sender_name in varchar2,
2628 p_user_name in varchar2,
2629 p_domain_name in varchar2,
2630 p_priority in varchar2,
2631 p_message_status in varchar2,
2632 p_email_account_id in number,
2633 x_wfoutval out NOCOPY varchar2,
2634 x_status out NOCOPY varchar2,
2635 x_out_text out NOCOPY varchar2) IS
2636
2637 PRAGMA autonomous_transaction;
2638 l_itemkey varchar2(30);
2639 l_process varchar2(1);
2640 l_class number;
2641 l_stat varchar2(10);
2642 l_outval varchar2(200);
2643 l_out_text varchar2(500);
2644 l_count number;
2645 l_comp_id number; -- rt comps id
2646 l_wf_value varchar2(500);
2647 l_ret_status varchar2(10);
2648 l_msg_count number;
2649 l_msg_data varchar2(500);
2650 l_uid number;
2651 l_status varchar2(100);
2652 l_category_id AMV_SEARCH_PVT.amv_number_varray_type:=AMV_SEARCH_PVT.amv_number_varray_type();
2653 CUSTOM_WF_EXCEP EXCEPTION;
2654 MOVE_MSG_EXCEP EXCEPTION;
2655 PROC_ERROR EXCEPTION;
2656 PROCESS_BEFORE EXCEPTION;
2657 BEGIN
2658 -- Check whether the Workflow is already called for this message or not.
2659 -- May be called by a profile value later
2660 begin
2661 select value into l_outval
2662 from iem_comp_rt_stats
2663 where type='WORKFLOW' AND param=to_char(p_message_id);
2664 raise PROCESS_BEFORE;
2665 exception when others then
2666 null;
2667 end;
2668 -- Call the specific search API
2669 BEGIN
2670 select classification_id into l_class
2671 from iem_email_classifications
2672 where message_id=p_message_id
2673 and score = (select max(score) from iem_email_classifications
2674 where message_id=p_message_id)
2675 and rownum=1;
2676 IEM_EMAIL_PROC_PVT.IEM_WF_SPECIFICSEARCH(
2677 p_message_id ,
2678 p_email_account_id ,
2679 l_class,
2680 l_category_id,
2681 null, -- KB search based on profile value
2682 l_stat ,
2683 l_out_text);
2684 EXCEPTION WHEN OTHERS THEN
2685 NULL;
2686 END;
2687 SELECT TO_CHAR(iem.IEM_MAILPREPROCWF_S1.nextval)
2688 INTO l_itemkey
2689 FROM dual;
2690
2691
2692 IEM_MAILPREPROCWF_PUB.IEM_STARTPROCESS(
2693 WorkflowProcess=>'MAILPREPROC',
2694 ItemType=>'IEM_MAIL',
2695 ItemKey=>l_itemkey,
2696 p_itemuserkey =>'iemmail_preproc',
2697 p_msgid =>p_message_id,
2698 p_msgsize =>p_message_size,
2699 p_sender=>p_sender_name,
2700 p_username =>p_user_name,
2701 p_domain=>p_domain_name,
2702 p_priority=>p_priority,
2703 p_msg_status =>p_message_status,
2704 p_email_account_id=>p_email_account_id,
2705 p_flow=>'N',
2706 x_outval=>l_outval,
2707 x_process=>l_process);
2708
2709 IF IEM_Mailpreprocwf_PUB.G_STAT='E' then
2710 raise CUSTOM_WF_EXCEP;
2711 end if;
2712
2713 IEM_COMP_RT_STATS_PVT.create_item(p_api_version_number =>1.0,
2714 p_init_msg_list => FND_API.G_FALSE,
2715 p_commit => FND_API.G_FALSE,
2716 p_type => 'WORKFLOW',
2717 p_param => p_message_id,
2718 p_value => l_outval,
2719 x_return_status => l_stat,
2720 x_msg_count => l_count,
2721 x_msg_data => l_msg_data
2722 );
2723 if l_stat<>'S' THEN
2724 x_out_text:='Error while logging WF Return Value';
2725 raise PROC_ERROR;
2726 end if;
2727 x_wfoutval:=l_outval;
2728 x_status:=l_process;
2729 x_out_text:='Complete WF Processing ';
2730 commit;
2731 IF l_process='N' THEN -- move the message to /Resolved folder
2732 null; -- incorporate appropriate changes later
2733 END IF;
2734 EXCEPTION WHEN CUSTOM_WF_EXCEP THEN
2735 x_status:='E';
2736 x_out_text:=' Workflow Process Returns Error '||sqlerrm;
2737 rollback;
2738 WHEN MOVE_MSG_EXCEP THEN
2739 x_status:='E';
2740 x_out_text:='Error while moving the message to Resolved Folder';
2741 rollback;
2742 WHEN PROC_ERROR THEN
2743 x_status:='E';
2744 rollback;
2745 WHEN PROCESS_BEFORE THEN
2746 x_status:='S';
2747 x_wfoutval:=l_outval;
2748 x_out_text:='Complete WF Processing ';
2749 commit;
2750 WHEN OTHERS THEN
2751 x_status:='E';
2752 x_out_text:=' Oracle Error Customise WF Processing '||sqlerrm;
2753 rollback;
2754 END;
2755
2756 PROCEDURE IEM_SRSTATUS_UPDATE(p_sr_id in number,
2757 p_status_id in number,
2758 p_email_rec in iem_rt_preproc_emails%rowtype,
2759 x_status out NOCOPY varchar2,
2760 x_out_text out NOCOPY varchar2) IS
2761
2762 l_service_request_rec CS_ServiceRequest_PUB.service_request_rec_type;
2763 l_request_id NUMBER;
2764 l_object_version_number NUMBER;
2765 l_request_number VARCHAR2(64);
2766 l_sr_number number;
2767 l_sr_status varchar2(100);
2768 l_status_id number;
2769 l_party_id number;
2770 l_ret_status varchar2(10);
2771 l_msg_count number;
2772 l_count number;
2773 l_interaction_id number;
2774 l_msg_data varchar2(500);
2775 l_out_text varchar2(500);
2776 l_uid number;
2777 l_status_flag varchar2(100);
2778 l_str varchar2(1000);
2779 Type get_data is REF CURSOR;
2780 c1 get_data;
2781 SR_STATUS_UPD_FAIL EXCEPTION;
2782 BEGIN
2783 -- code for auto sr update
2784 x_status:='S';
2785 -- select object version number
2786 BEGIN
2787 open c1 for
2788 'select object_version_number,status_flag from cs_incidents_all_b where incident_id=:sr_id' using p_sr_id;
2789 LOOP
2790 fetch c1 into l_object_version_number,l_status_flag;
2791 exit;
2792 end loop;
2793 EXCEPTION WHEN OTHERS THEN
2794 x_out_Text:='Oracle Error for SR# '||p_sr_id|| 'While selecting object version number '||sqlerrm;
2795 raise SR_STATUS_UPD_FAIL;
2796 END;
2797 IF (l_object_version_number is null) OR (l_status_flag is null) then
2798 x_out_text:='Invalid SR ID In the Tag Data # '||p_sr_id;
2799 raise SR_STATUS_UPD_FAIL;
2800 END IF;
2801 IF l_status_flag<>'C' THEN -- SR is not closed so we can update
2802 l_status_id:=p_status_id;
2803 IEM_ServiceRequest_PVT.Update_Status_Wrap
2804 ( p_api_version =>2.0,
2805 p_init_msg_list =>fnd_api.g_true,
2806 p_commit => fnd_api.g_false,
2807 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2808 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2809 p_user_id =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
2810 x_return_status =>l_ret_status,
2811 x_msg_count=>l_msg_count,
2812 x_msg_data =>l_msg_data,
2813 p_request_id =>p_sr_id,
2814 p_object_version_number =>l_object_version_number,
2815 p_status_id=>l_status_id,
2816 p_status=>l_sr_status,
2817 x_interaction_id=>l_interaction_id);
2818 IF l_ret_status<>'S' THEN
2819 x_out_text:='SR update Status Api fails for SR# '||p_sr_id;
2820 FND_MSG_PUB.Get(p_msg_index => FND_MSG_PUB.G_FIRST,
2821 p_encoded=>'F',
2822 p_data=>l_str,
2823 p_msg_index_out=>l_msg_count);
2824 raise SR_STATUS_UPD_FAIL;
2825 END IF;
2826 x_status:='S';
2827 x_out_text:='Successfully Update the SR '||p_sr_id;
2828 ELSE
2829 x_status:='E';
2830 x_out_text:='SR '||p_sr_id||' is Closed Hence Not updated ';
2831 END IF;
2832 EXCEPTION WHEN SR_STATUS_UPD_FAIL THEN
2833 x_status:='E';
2834 x_out_text:=l_str;
2835 WHEN OTHERS THEN
2836 x_out_text:='Error Encoutered While updating status of SR# '||p_sr_id||sqlerrm;
2837 x_status:='E';
2838 end ;
2839
2840 PROCEDURE IEM_CLASSIFICATION_PROC(
2841 p_email_account_id in number,
2842 p_keyval in iem_route_pub.keyVals_tbl_type,
2843 x_rt_classification_id out NOCOPY number,
2844 x_status out NOCOPY varchar2,
2845 x_out_text out NOCOPY varchar2) IS
2846
2847 l_ret_status varchar2(10);
2848 l_msg_count number;
2849 l_msg_data varchar2(500);
2850 CLASS_EXCEPTION EXCEPTION;
2851 BEGIN
2852 x_status:='S';
2853 IEM_ROUTE_CLASS_PUB.CLASSIFY(
2854 p_api_version_number=>1.0,
2855 p_keyVals_tbl=>p_keyval,
2856 p_accountId=>p_email_account_id,
2857 x_classificationId=>x_rt_classification_id,
2858 x_return_status=>l_ret_status,
2859 x_msg_count=>l_msg_count,
2860 x_msg_data=>l_msg_data);
2861 IF l_ret_status <>'S' THEN
2862 x_out_text:='classification engine return Error abandoning further Processing ';
2863 raise class_exception;
2864 END IF;
2865 x_status:='S';
2866 x_out_text:='Successfully Processed Classification Engine and Returned Classification Id '||x_rt_classification_id;
2867 EXCEPTION
2868 WHEN class_exception THEN
2869 x_status:='E';
2870 WHEN OTHERS THEN
2871 x_status:='E';
2872 x_out_text:='Classification Processing Encountered Oracle Error '||sqlerrm;
2873 END IEM_CLASSIFICATION_PROC;
2874
2875 PROCEDURE IEM_ROUTING_PROC(
2876 p_email_account_id in number,
2877 p_keyval in iem_route_pub.keyVals_tbl_type,
2878 x_routing_group_id out NOCOPY number,
2879 x_status out NOCOPY varchar2,
2880 x_out_text out NOCOPY varchar2) IS
2881 KeyValuePairs iem_route_pub.KeyVals_tbl_type;
2882 l_counter number;
2883 l_ret_status varchar2(10);
2884 l_msg_count number;
2885 l_msg_data varchar2(500);
2886 ROUTE_EXCEPTION EXCEPTION;
2887 BEGIN
2888 IEM_ROUTE_PUB.ROUTE(
2889 P_API_VERSION_NUMBER =>1.0,
2890 P_KEYVALS_TBL =>p_keyval,
2891 P_ACCOUNTID =>p_email_account_id,
2892 X_GROUPID =>x_routing_group_id,
2893 X_RETURN_STATUS =>l_ret_status,
2894 X_MSG_COUNT =>l_msg_count,
2895 X_MSG_DATA =>l_msg_data);
2896 IF l_ret_status <>'S' THEN
2897 x_out_text:='Routing engine return Error abandoning further Processing ';
2898 raise route_exception;
2899 END IF;
2900 x_status:='S';
2901 x_out_text:='Successfully Processed Routing Engine and Returned Group Id '||x_routing_group_id;
2902 EXCEPTION
2903 WHEN route_exception THEN
2904 x_status:='E';
2905 WHEN OTHERS THEN
2906 x_status:='E';
2907 x_out_text:='Routing Processing Encountered Oracle Error '||sqlerrm;
2908 END IEM_ROUTING_PROC;
2909
2910 PROCEDURE IEM_PROC_IH(
2911 p_type in varchar2, -- MEDIA/ACTIVITY/MLCS/INTERACTION
2912 p_action in varchar2, -- ADD/UPDATE/CLOSE
2913 p_interaction_rec IN JTF_IH_PUB.interaction_rec_type,
2914 p_activity_rec IN JTF_IH_PUB.activity_rec_type,
2915 p_media_lc_rec IN JTF_IH_PUB.media_lc_rec_type,
2916 p_media_rec IN JTF_IH_PUB.media_rec_type,
2917 x_id OUT NOCOPY NUMBER,
2918 x_status out NOCOPY varchar2,
2919 x_out_text out NOCOPY varchar2) IS
2920 l_media_id number;
2921 l_milcs_id number;
2922 l_interaction_id number;
2923 l_activity_rec JTF_IH_PUB.activity_rec_type;
2924 l_activity_id number;
2925 l_ret_status varchar2(10);
2926 l_msg_data varchar2(1500);
2927 l_data varchar2(1500);
2928 l_error_text varchar2(1500):=' ';
2929 l_msg_count number;
2930 l_msg_index_out number;
2931 IH_EXCEPTION EXCEPTION;
2932 BEGIN
2933 if p_type='MEDIA' THEN
2934 if p_action='ADD' THEN
2935 JTF_IH_PUB.Open_MediaItem(1.0,
2936 'T',
2937 'F',
2938 TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2939 TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2940 nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
2941 TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
2942 l_ret_status,
2943 l_msg_count,
2944 l_msg_data,
2945 p_media_rec,
2946 l_media_id);
2947 if l_ret_status<>'S' then
2948 x_out_text:='Error While Creating Media Item '||sqlerrm;
2949 raise IH_EXCEPTION;
2950 else
2951 x_id:=l_media_id;
2952 end if;
2953
2954 elsif p_action='UPDATE' THEN
2955
2956 JTF_IH_PUB.Update_MediaItem( 1.0,
2957 'T',
2958 'F',
2959 TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2960 TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2961 nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
2962 TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
2963 l_ret_status,
2964 l_msg_count,
2965 l_msg_data,
2966 p_media_rec);
2967 if l_ret_status<>'S' then
2968 x_out_text:='Error While Updating Media Item ';
2969 raise IH_EXCEPTION;
2970 end if;
2971 elsif p_action='CLOSE' THEN
2972
2973 JTF_IH_PUB.Close_MediaItem( 1.0,
2974 'T',
2975 'F',
2976 TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2977 TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2978 nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
2979 TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
2980 l_ret_status,
2981 l_msg_count,
2982 l_msg_data,
2983 p_media_rec);
2984 if l_ret_status<>'S' then
2985 x_out_text:='Error While Closing Media Item ';
2986 raise IH_EXCEPTION;
2987 end if;
2988 end if;
2989 elsif p_type='MLCS' THEN
2990 if p_action='ADD' THEN
2991 JTF_IH_PUB.Add_MediaLifeCycle( 1.0,
2992 'T',
2993 'F',
2994 TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
2995 TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
2996 nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
2997 TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
2998 l_ret_status,
2999 l_msg_count,
3000 l_msg_data,
3001 p_media_lc_rec,
3002 l_milcs_id);
3003
3004 if l_ret_status<>'S' then
3005 x_out_text:='Error While Creating Media Life Cycle ';
3006 raise IH_EXCEPTION;
3007 else
3008 x_id:=l_milcs_id;
3009 end if;
3010 elsif p_action='UPDATE' THEN
3011 JTF_IH_PUB.Update_MediaLifeCycle( 1.0,
3012 'T',
3013 'F',
3014 TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
3015 TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
3016 nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
3017 TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
3018 l_ret_status,
3019 l_msg_count,
3020 l_msg_data,
3021 p_media_lc_rec);
3022 if l_ret_status<>'S' then
3023 x_out_text:='Error While Updating Media Life Cycle ';
3024 raise IH_EXCEPTION;
3025 end if;
3026 end if;
3027 elsif p_type='INTERACTION' THEN
3028 IF p_action='ADD' THEN
3029
3030 JTF_IH_PUB.Open_Interaction( p_api_version => 1.1,
3031 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
3032 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
3033 p_user_id =>nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
3034 p_login_id =>TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
3035 x_return_status => l_ret_status,
3036 x_msg_count => l_msg_count,
3037 x_msg_data => l_msg_data,
3038 x_interaction_id => l_interaction_id,
3039 p_interaction_rec => p_interaction_rec
3040 );
3041 if l_ret_status<>'S' then
3042 x_out_text:='Error While Creating Interaction ';
3043 raise IH_EXCEPTION;
3044 else
3045 x_id:=l_interaction_id;
3046 end if;
3047 ELSIF p_action='UPDATE' THEN
3048 JTF_IH_PUB.Update_Interaction( p_api_version => 1.1,
3049 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
3050 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
3051 p_user_id =>nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
3052 p_login_id =>TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
3053 x_return_status => l_ret_status,
3054 x_msg_count => l_msg_count,
3055 x_msg_data => l_msg_data,
3056 p_interaction_rec => p_interaction_rec
3057 );
3058 IF l_ret_status<>'S' THEN
3059 x_out_text:='Error While Updating Interaction ';
3060 raise IH_EXCEPTION;
3061 END IF;
3062 ELSIF p_action='CLOSE' THEN
3063 JTF_IH_PUB.Close_Interaction( p_api_version => 1.1,
3064 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
3065 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
3066 p_user_id =>nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
3067 p_login_id =>TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
3068 x_return_status => l_ret_status,
3069 x_msg_count => l_msg_count,
3070 x_msg_data => l_msg_data,
3071 p_interaction_rec => p_interaction_rec);
3072 IF l_ret_status<>'S' THEN
3073 x_out_text:='Error While Closing Interaction ';
3074 raise IH_EXCEPTION;
3075 END IF;
3076 END IF;
3077 elsif p_type='ACTIVITY' THEN
3078 IF p_action='ADD' THEN
3079 l_activity_rec:=p_activity_rec;
3080 BEGIN
3081 select wu.outcome_id, wu.result_id, wu.reason_id INTO
3082 l_activity_rec.outcome_id, l_activity_rec.result_id, l_activity_rec.reason_id
3083 from jtf_ih_action_action_items aa, jtf_ih_wrap_ups wu
3084 where aa.action_id =l_activity_rec.action_id
3085 and aa.action_item_id = l_activity_rec.action_item_id
3086 and aa.default_wrap_id = wu.wrap_id;
3087 EXCEPTION WHEN OTHERS THEN
3088 NULL;
3089 END;
3090 JTF_IH_PUB.Add_Activity(p_api_version => 1.0,
3091 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
3092 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
3093 p_user_id =>nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
3094 p_login_id =>TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
3095 x_return_status => l_ret_status,
3096 x_msg_count => l_msg_count,
3097 x_msg_data => l_msg_data,
3098 p_activity_rec => l_activity_rec,
3099 x_activity_id => l_activity_id
3100 );
3101 if l_ret_status<>'S' then
3102 x_out_text:='Error While Creating Activity ';
3103 raise IH_EXCEPTION;
3104 else
3105 x_id:=l_activity_id;
3106 end if;
3107 END IF;
3108 end if;
3109 x_Status:='S';
3110 EXCEPTION
3111 WHEN IH_EXCEPTION THEN
3112 x_status:='E';
3113 IF (l_msg_count >= 1) THEN
3114 --Only one error
3115 FND_MSG_PUB.Get(p_msg_index => FND_MSG_PUB.G_FIRST,
3116 p_encoded=>'F',
3117 p_data=>l_data,
3118 p_msg_index_out=>l_msg_index_out);
3119 l_error_text:= substr(l_data,1,500);
3120 END IF;
3121 x_out_text:=x_out_text||l_error_text;
3122 WHEN OTHERS THEN
3123 x_status:='E';
3124 x_out_text:='Interaction History Processing Encountered Oracle Error '||sqlerrm;
3125 END IEM_PROC_IH;
3126
3127 PROCEDURE IEM_WRAPUP(p_interaction_id in number,
3128 p_media_id in number,
3129 p_milcs_id in number,
3130 p_action in varchar2,
3131 p_email_rec in iem_rt_preproc_emails%rowtype,
3132 p_action_id in number,
3133 x_out_text out NOCOPY varchar2,
3134 x_status out NOCOPY varchar2) IS
3135
3136 l_media_rec JTF_IH_PUB.media_rec_type;
3137 l_interaction_rec JTF_IH_PUB.interaction_rec_type;
3138 l_stat varchar2(10);
3139 l_out_text varchar2(500);
3140 l_uid number;
3141 l_milcs_id number;
3142 l_interaction_id number;
3143 l_media_lc_rec JTF_IH_PUB.media_lc_rec_type;
3144 l_action_id number;
3145 l_activity_rec JTF_IH_PUB.activity_rec_type;
3146 WRAPUP_ERROR EXCEPTION;
3147 l_ret_status varchar2(10);
3148 l_msg_data varchar2(300);
3149 l_msg_count number;
3150 begin
3151 -- Update the mail Processing Life Cycles
3152 l_media_lc_rec.milcs_id:=p_milcs_id;
3153 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
3154 p_type=>'MLCS' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
3155 p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
3156 p_interaction_rec=>l_interaction_rec,
3157 p_activity_rec=>l_activity_rec ,
3158 p_media_lc_rec=>l_media_lc_Rec ,
3159 p_media_rec=>l_media_rec ,
3160 x_id=>l_milcs_id,
3161 x_status=>l_stat ,
3162 x_out_text=>l_out_text );
3163
3164 IF l_stat<>'S' THEN
3165 x_out_text:=l_out_text;
3166 raise WRAPUP_ERROR;
3167 END IF;
3168 IF p_action is not null THEN
3169 -- incase of auto redirect this is set to null
3170 l_interaction_rec.interaction_id:=p_interaction_id;
3171 BEGIN
3172 select wu.outcome_id, wu.result_id, wu.reason_id into
3173 l_interaction_rec.outcome_id,
3174 l_interaction_rec.result_id,
3175 l_interaction_rec.reason_id
3176 from jtf_ih_action_action_items aa, jtf_ih_wrap_ups wu
3177 where aa.action_id =p_action_id
3178 and aa.action_item_id = 45
3179 and aa.default_wrap_id = wu.wrap_id;
3180 EXCEPTION WHEN OTHERS THEN
3181 null;
3182 END;
3183 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
3184 p_type=>'INTERACTION' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
3185 p_action=>'CLOSE' , -- ADD/UPDATE/CLOSE
3186 p_interaction_rec=>l_interaction_rec,
3187 p_activity_rec=>l_activity_rec ,
3188 p_media_lc_rec=>l_media_lc_Rec ,
3189 p_media_rec=>l_media_rec ,
3190 x_id=>l_interaction_id,
3191 x_status=>l_stat ,
3192 x_out_text=>l_out_text );
3193 IF l_stat<>'S' THEN
3194 x_out_text:=l_out_text;
3195 raise WRAPUP_ERROR;
3196 END IF;
3197 --Closing the media Item
3198 l_media_rec.media_id:=p_media_id;
3199 IEM_EMAIL_PROC_PVT.IEM_PROC_IH(
3200 p_type=>'MEDIA' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
3201 p_action=>'CLOSE' , -- ADD/UPDATE/CLOSE
3202 p_interaction_rec=>l_interaction_rec,
3203 p_activity_rec=>l_activity_rec ,
3204 p_media_lc_rec=>l_media_lc_Rec ,
3205 p_media_rec=>l_media_rec ,
3206 x_id=>l_interaction_id,
3207 x_status=>l_stat ,
3208 x_out_text=>l_out_text );
3209
3210 IF l_stat<>'S' THEN
3211 raise WRAPUP_ERROR;
3212 END IF;
3213
3214 IEM_MAILITEM_PUB.ResolvedMessage (p_api_version_number=>1.0 ,
3215 p_init_msg_list=>'F' ,
3216 p_commit=>'F' ,
3217 p_message_id=>p_email_rec.message_id,
3218 p_action_flag=>p_action ,
3219 x_return_status=>l_ret_status,
3220 x_msg_count=>l_msg_count ,
3221 x_msg_data=>l_msg_data);
3222 IF l_ret_status<>'S' THEN
3223 l_out_text:='Error in Moving Message '||sqlerrm;
3224 raise WRAPUP_ERROR;
3225 END IF;
3226 END IF; -- for p_action is not null
3227 x_status:='S';
3228 EXCEPTION WHEN WRAPUP_ERROR THEN
3229 x_out_text:=l_out_text;
3230 x_status:='E';
3231 WHEN OTHERS THEN
3232 x_out_text:='Oracle Error Encountered in Wrapup '||sqlerrm;
3233 x_status:='E';
3234 end IEM_WRAPUP;
3235
3236 PROCEDURE IEM_AUTOREPLY(p_interaction_id in number,
3237 p_media_id in number,
3238 p_post_rec in iem_rt_preproc_emails%rowtype,
3239 p_doc_tbl in email_doc_tbl,
3240 p_subject in varchar2,
3241 P_TAG_KEY_VALUE_TBL in IEM_OUTBOX_PROC_PUB.keyVals_tbl_type,
3242 P_CUSTOMER_ID in number,
3243 P_RESOURCE_ID in number,
3244 p_qualifiers in IEM_OUTBOX_PROC_PUB.QualifierRecordList,
3245 p_fwd_address in varchar2,
3246 p_fwd_doc_id in number,
3247 p_req_type in varchar2,
3248 x_out_text out NOCOPY varchar2,
3249 x_status out NOCOPY varchar2) IS
3250 l_outbox_id number;
3251 l_ret_status varchar2(100);
3252 l_msg_count number;
3253 l_msg_data varchar2(500);
3254 AUTOREPLY_ERROR EXCEPTION;
3255 l_folder_name varchar2(240);
3256 l_file_name varchar2(256);
3257 l_ext_subject varchar2(100);
3258 l_data varchar2(500);
3259 l_msg_index_out varchar2(10);
3260 l_qual_tbl IEM_OUTBOX_PROC_PUB.QualifierRecordList;
3261 l_error_text varchar2(1000);
3262 l_dflt_sender varchar2(250);
3263 l_sender varchar2(256);
3264 l_from1 number;
3265 l_from2 number;
3266 l_header_rec iem_ms_base_headers%rowtype;
3267 l_notification_id number;
3268 BEGIN
3269 select * into l_header_rec from iem_ms_base_headers
3270 where message_id=p_post_rec.message_id;
3271 l_from1:=instr(l_header_rec.from_str,'<',1,1);
3272 l_from2:=instr(l_header_rec.from_str,'>',1,1);
3273 IF l_from1>0 then -- From Address Contains Both Name and Address
3274 l_sender:=substr(l_header_rec.from_Str,l_from1+1,l_from2-l_from1-1);
3275 ELSE -- From Address contains only Address
3276 l_sender:=l_header_rec.from_str;
3277 END IF;
3278 if p_req_type='R' then -- Autoreply
3279 IEM_OUTBOX_PROC_PUB.createautoreply(p_api_version_number=>1.0,
3280 p_init_msg_list=>'F',
3281 p_commit=>'F',
3282 P_MEDIA_ID=>p_media_id, --- Then next 3 parameter will be null
3283 P_RFC822_MESSAGE_ID =>null,
3284 p_folder_name=>null,
3285 P_MESSAGE_UID =>null,
3286 P_MASTER_ACCOUNT_ID=>p_post_rec.email_account_id,
3287 P_TO_ADDRESS_LIST=>l_sender, -- need to pass sender name
3288 p_cc_address_list=>null ,
3289 p_bcc_address_list=>null,
3290 P_SUBJECT=>l_header_rec.subject,
3291 P_TAG_KEY_VALUE_TBL=>p_tag_key_value_tbl,
3292 P_CUSTOMER_ID=>p_customer_id,
3293 P_INTERACTION_ID=>p_interaction_id,
3294 P_RESOURCE_ID=>p_resource_id,
3295 p_qualifiers =>p_qualifiers ,
3296 p_contact_id=>g_contact_id,
3297 p_relationship_id=>g_relation_id,
3298 p_mdt_message_id=>p_post_rec.message_id,
3299 X_OUTBOX_ITEM_ID=>l_outbox_id,
3300 X_RETURN_STATUS=>l_ret_status,
3301 X_MSG_COUNT=>l_msg_count,
3302 X_MSG_DATA=>l_msg_data);
3303 IF l_ret_status<>'S' THEN
3304 x_out_text:='Error Encountered While Calling Create Autoreply';
3305 raise AUTOREPLY_ERROR;
3306 END IF;
3307 FOR i IN p_doc_tbl.FIRST..p_doc_tbl.LAST LOOP
3308 IF p_doc_tbl(i).type='I' THEN
3309 IEM_OUTBOX_PROC_PUB.insertDocument(
3310 p_api_version_number=>1.0 ,
3311 p_outbox_item_id=>l_outbox_id,
3312 p_document_source=>'MES' ,
3313 p_document_id =>p_doc_tbl(i).doc_id ,
3314 X_RETURN_STATUS=>l_ret_status,
3315 X_MSG_COUNT=>l_msg_count,
3316 X_MSG_DATA=>l_msg_data);
3317
3318 IF l_ret_status<>'S' THEN
3319 x_out_text:='Error Encountered While Calling Insert Document ';
3320 raise AUTOREPLY_ERROR;
3321 END IF;
3322 ELSIF p_doc_tbl(i).type='A' THEN
3323
3324 SELECT fl.file_name
3325 INTO l_file_name
3326 FROM jtf_amv_items_tl b ,jtf_amv_attachments a ,fnd_lobs fl
3327 WHERE b.item_id = a.attachment_used_by_id
3328 and a.attachment_used_by='ITEM'
3329 AND a.file_id = fl.file_id
3330 AND b.item_id=p_doc_tbl(i).doc_id
3331 AND b.language=USERENV('LANG')
3332 and rownum=1;
3333
3334 IEM_OUTBOX_PROC_PUB.attachDocument(p_api_version_number=>1.0,
3335 p_init_msg_list=>FND_API.G_FALSE,
3336 p_commit=>FND_API.G_TRUE,
3337 p_outbox_item_id=>l_outbox_id,
3338 p_document_source=>'MES',
3339 p_document_id=>p_doc_tbl(i).doc_id,
3340 p_binary_source=>NULL,
3341 p_attachment_name=>l_file_name,
3342 x_return_status=>l_ret_status,
3343 x_msg_count=>l_msg_count,
3344 x_msg_data=>l_msg_data
3345 );
3346 IF l_ret_status<>'S' THEN
3347 x_out_text:='Error Encountered While Calling Attach Document ';
3348 raise AUTOREPLY_ERROR;
3349 END IF;
3350 END IF;
3351 END LOOP;
3352 elsif p_req_type='N' then -- Auto Notifications
3353 if l_header_rec.reply_to_str is not null then
3354 l_from1:=instr(l_header_rec.reply_to_str,'<',1,1);
3355 l_from2:=instr(l_header_rec.reply_to_str,'>',1,1);
3356 IF l_from1>0 then -- From Address Contains Both Name and Address
3357 l_sender:=substr(l_header_rec.reply_to_str,l_from1+1,l_from2-l_from1-1);
3358 ELSE -- From Address contains only Address
3359 l_sender:=l_header_rec.reply_to_str;
3360 END IF;
3361 END IF;
3362 IEM_OUTBOX_PROC_PUB.createSRAutoNotification(p_api_version_number=>1.0,
3363 p_init_msg_list=>'F',
3364 p_commit=>'F',
3365 P_MEDIA_ID=>p_media_id,
3366 P_MASTER_ACCOUNT_ID=>p_post_rec.email_account_id,
3367 P_TO_ADDRESS_LIST=>l_sender, -- need to pass sender name
3368 p_cc_address_list=>null ,
3369 p_bcc_address_list=>null,
3370 P_SUBJECT=>l_header_rec.subject,
3371 P_TAG_KEY_VALUE_TBL=>p_tag_key_value_tbl,
3372 P_CUSTOMER_ID=>p_customer_id,
3373 P_INTERACTION_ID=>fnd_api.g_miss_num,
3374 P_RESOURCE_ID=>p_resource_id,
3375 p_qualifiers =>p_qualifiers ,
3376 p_contact_id=>g_contact_id,
3377 p_relationship_id=>g_relation_id,
3378 p_message_id=>p_post_rec.message_id,
3379 p_sr_id=>p_fwd_doc_id,
3380 X_OUTBOX_ITEM_ID=>l_outbox_id,
3381 X_RETURN_STATUS=>l_ret_status,
3382 X_MSG_COUNT=>l_msg_count,
3383 X_MSG_DATA=>l_msg_data);
3384 IF l_ret_status<>'S' THEN
3385 x_out_text:='Error Encountered While Calling Create Autonotification';
3386 FND_MSG_PUB.Get(p_msg_index => FND_MSG_PUB.G_FIRST,
3387 p_encoded=>'F',
3388 p_data=>l_data,
3389 p_msg_index_out=>l_msg_index_out);
3390 raise AUTOREPLY_ERROR;
3391 END IF;
3392 FOR i IN p_doc_tbl.FIRST..p_doc_tbl.LAST LOOP
3393 l_notification_id:=p_doc_tbl(i).doc_id;
3394 EXIT;
3395 END LOOP;
3396 IEM_OUTBOX_PROC_PUB.insertDocument(
3397 p_api_version_number=>1.0 ,
3398 p_outbox_item_id=>l_outbox_id,
3399 p_document_source=>'MES' ,
3400 p_document_id =>l_notification_id ,
3401 X_RETURN_STATUS=>l_ret_status,
3402 X_MSG_COUNT=>l_msg_count,
3403 X_MSG_DATA=>l_msg_data);
3404
3405 IF l_ret_status<>'S' THEN
3406 x_out_text:='Error Encountered While Calling Insert Document ';
3407 FND_MSG_PUB.Get(p_msg_index => FND_MSG_PUB.G_FIRST,
3408 p_encoded=>'F',
3409 p_data=>l_data,
3410 p_msg_index_out=>l_msg_index_out);
3411 raise AUTOREPLY_ERROR;
3412 END IF;
3413 else
3414 FND_MESSAGE.Set_Name('IEM','IEM_REDIRECT_EXT_HDR');
3415 FND_MSG_PUB.Add;
3416 l_ext_subject := FND_MSG_PUB.GET(FND_MSG_pub.Count_Msg,FND_API.G_FALSE);
3417 -- Get value for merge fields
3418 FND_MESSAGE.Set_Name('IEM','IEM_ADM_AUTO_ACK_CUSTOMER');
3419 FND_MSG_PUB.Add;
3420 l_dflt_sender := FND_MSG_PUB.GET(FND_MSG_pub.Count_Msg,FND_API.G_FALSE);
3421 IEM_EMAIL_PROC_PVT.IEM_GET_MERGEVAL(p_email_account_id=>p_post_rec.email_account_id,
3422 p_mailer=>null, -- need to pass from address
3423 p_dflt_sender=>l_dflt_sender ,
3424 p_subject=>null, -- pass the subject
3425 x_qual_tbl=> l_qual_tbl,
3426 x_status=>l_ret_status,
3427 x_out_text=>l_error_text);
3428 IEM_OUTBOX_PROC_PUB.autoForward(
3429 p_api_version_number=>1.0 ,
3430 p_init_msg_list=>'F' ,
3431 p_commit=>'F' ,
3432 p_media_id=>p_media_id,
3433 p_rfc822_message_id=>null,
3434 p_folder_name=>null ,
3435 p_message_uid=>null,
3436 p_master_account_id=>p_post_rec.email_account_id ,
3437 p_to_address_list=>p_fwd_address ,
3438 p_cc_address_list=>null ,
3439 p_bcc_address_list=>null ,
3440 p_subject=>l_ext_subject||p_subject ,
3441 p_tag_key_value_tbl=>p_tag_key_value_tbl ,
3442 P_CUSTOMER_ID=>p_customer_id,
3443 P_INTERACTION_ID=>p_interaction_id,
3444 P_RESOURCE_ID=>p_resource_id,
3445 p_qualifiers =>l_qual_tbl ,
3446 p_contact_id=>g_contact_id,
3447 p_relationship_id=>g_relation_id,
3448 p_attach_inb=>'A',
3449 p_mdt_message_id=>p_post_rec.message_id,
3450 X_OUTBOX_ITEM_ID=>l_outbox_id,
3451 X_RETURN_STATUS=>l_ret_status,
3452 X_MSG_COUNT=>l_msg_count,
3453 X_MSG_DATA=>l_msg_data);
3454 IF l_ret_status<>'S' THEN
3455 x_out_text:='Outbox Processing Return Error while creating a request for autoforward';
3456 raise AUTOREPLY_ERROR;
3457 END IF;
3458 IEM_OUTBOX_PROC_PUB.insertDocument(
3459 p_api_version_number=>1.0 ,
3460 p_outbox_item_id=>l_outbox_id,
3461 p_document_source=>'MES' ,
3462 p_document_id =>p_fwd_doc_id ,
3463 X_RETURN_STATUS=>l_ret_status,
3464 X_MSG_COUNT=>l_msg_count,
3465 X_MSG_DATA=>l_msg_data);
3466
3467 IF l_ret_status<>'S' THEN
3468 x_out_text:='Error Encountered While Calling Insert Document during autoforward';
3469 raise AUTOREPLY_ERROR;
3470 END IF;
3471 end if;
3472 --submitting the request
3473 IEM_OUTBOX_PROC_PUB.submitOutboxMessage(
3474 p_api_version_number=>1.0 ,
3475 p_init_msg_list=>'F',
3476 p_commit=>'F',
3477 p_outbox_item_id=>l_outbox_id ,
3478 p_preview_bool=>'N',
3479 X_RETURN_STATUS=>l_ret_status,
3480 X_MSG_COUNT=>l_msg_count,
3481 X_MSG_DATA=>l_msg_data);
3482 IF l_ret_status<>'S' THEN
3483 x_out_text:='Error Encountered While Submitting Outbox Request';
3484 FND_MSG_PUB.Get(p_msg_index => FND_MSG_PUB.G_FIRST,
3485 p_encoded=>'F',
3486 p_data=>l_data,
3487 p_msg_index_out=>l_msg_index_out);
3488 raise AUTOREPLY_ERROR;
3489 END IF;
3490 x_status:='S';
3491 x_out_text:='Submit Request For Autoreply Successfully';
3492 EXCEPTION WHEN AUTOREPLY_ERROR THEN
3493 x_status:='E';
3494 x_out_text:=x_out_text||l_data;
3495 WHEN OTHERS THEN
3496 x_out_text:='Oracle Error Encountered '||sqlerrm||' in autoreply processing ';
3497 x_status:='E';
3498 end IEM_AUTOREPLY;
3499
3500 PROCEDURE IEM_GET_MERGEVAL(p_email_account_id in number,
3501 p_mailer in varchar2,
3502 p_dflt_sender in varchar2,
3503 p_subject in varchar2,
3504 x_qual_tbl out nocopy IEM_OUTBOX_PROC_PUB.QualifierRecordList,
3505 x_status out nocopy varchar2,
3506 x_out_text out nocopy varchar2) IS
3507 cursor c1 is select lookup_code,meaning
3508 from fnd_lookups
3509 where enabled_flag = 'Y'
3510 AND NVL(start_date_active, SYSDATE) <= SYSDATE
3511 AND NVL(end_date_active,SYSDATE) >= SYSDATE
3512 AND lookup_type ='IEM_MERGE_FIELDS'
3513 ANd lookup_code like 'ACK%';
3514 l_index number;
3515 l_sender_name varchar2(250);
3516 l_qual_tbl IEM_OUTBOX_PROC_PUB.QualifierRecordList;
3517 l_from_name iem_email_accounts.from_name%type;
3518 l_reply_address iem_email_accounts.reply_to_address%type;
3519 begin
3520 select from_name,reply_to_address
3521 INTO l_from_name,l_reply_address
3522 from IEM_MSTEMAIL_ACCOUNTS
3523 where email_account_id=p_email_account_id;
3524 l_index:=instr(p_mailer,'<',1,1);
3525 IF l_index>0 then
3526 l_sender_name:=substr(p_mailer,1,l_index-1);
3527 l_sender_name:=replace(l_sender_name,'"','');
3528 else
3529 l_sender_name:=p_dflt_sender; -- need to be from profile
3530 end if;
3531 l_index:=0;
3532 x_qual_tbl.delete;
3533 FOR v1 IN c1 LOOP
3534 IF v1.lookup_code='ACK_SENDER_NAME' THEN
3535 l_index:=l_index+1;
3536 x_qual_tbl(l_index).qualifier_name:=v1.lookup_code;
3537 x_qual_tbl(l_index).qualifier_value:=l_sender_name;
3538 ELSIF v1.lookup_code='ACK_SUBJECT' THEN
3539 l_index:=l_index+1;
3540 x_qual_tbl(l_index).qualifier_name:=v1.lookup_code;
3541 x_qual_tbl(l_index).qualifier_value:=p_subject;
3542 ELSIF v1.lookup_code='ACK_RECEIVED_DATE' THEN
3543 l_index:=l_index+1;
3544 x_qual_tbl(l_index).qualifier_name:=v1.lookup_code;
3545 x_qual_tbl(l_index).qualifier_value:=to_char(sysdate,'DD-MON-YYYY');
3546 ELSIF v1.lookup_code='ACK_ACCT_FROM_NAME' THEN
3547 l_index:=l_index+1;
3548 x_qual_tbl(l_index).qualifier_name:=v1.lookup_code;
3549 x_qual_tbl(l_index).qualifier_value:=l_from_name;
3550 ELSIF v1.lookup_code='ACK_ACCT_EMAIL_ADDRESS' THEN
3551 l_index:=l_index+1;
3552 x_qual_tbl(l_index).qualifier_name:=v1.lookup_code;
3553 x_qual_tbl(l_index).qualifier_value:=l_reply_address;
3554 END IF;
3555 END LOOP;
3556 x_status:='S';
3557 exception when others then
3558 x_out_Text:='Error Occured While Retrieving Merge Data Value '||sqlerrm;
3559 x_status:='E';
3560 end IEM_GET_MERGEVAL;
3561 procedure IEM_PROCESS_INTENT(l_email_account_id in number,
3562 l_msg_id in number,
3563 l_theme_status out nocopy varchar2,
3564 l_out_text out nocopy varchar2)
3565
3566 is
3567
3568 l_ret number;
3569 l_ret1 number;
3570 l_gclassid number;
3571 l_theme varchar2(100);
3572 l_count number:=0;
3573 l_counter number:=0;
3574 l_tcount number;
3575 l_markup_count number;
3576 l_theme_count number;
3577 l_markupcount number;
3578 l_class_id number;
3579 l_classification_id number;
3580 l_tclassid number;
3581 l_part number;
3582 l_index number;
3583 l_flag number:=1;
3584 l_errtext varchar2(600);
3585 l_qstr varchar2(500);
3586 l_tstr varchar2(2000);
3587 l_tclstr varchar2(2000);
3588 l_class number;
3589 l_first varchar2(1);
3590 l_val number:=0;
3591 l_match varchar2(1):='F';
3592 l_msg_data varchar2(200);
3593 l_data iem_class_tbl_typ:=iem_class_tbl_typ();
3594 l_errmsg varchar2(200);
3595 l_str varchar2(200);
3596 l_class_scr varchar2(3500);
3597 l_wtot number:=0;
3598 theme_proc_excep EXCEPTION;
3599 theme_proc_excep1 EXCEPTION;
3600 l_text varchar2(100);
3601 l_status varchar2(100);
3602 l_class_str varchar2(32000);
3603 l_imt_string1 varchar2(4000);
3604 l_imt_string varchar2(32000);
3605 l_occur number;
3606 x_stat varchar2(10);
3607 l_lang varchar2(10);
3608 l_theme_code varchar2(50);
3609 l_logmessage varchar2(500);
3610 l_level varchar2(20):='STATEMENT';
3611 l_tweight number:=0;
3612 l_rms number;
3613 l_class_count number;
3614 l_theme_buf IEM_TEXT_PVT.theme_tAble;
3615 l_token_buf IEM_TEXT_PVT.token_table;
3616 cursor c1 is
3617 SELECT a.intent_id, a.keyword,a.weight
3618 from iem_intent_dtls a,iem_account_intents b
3619 WHERE b.email_account_id=l_email_account_id
3620 AND a.intent_id=b.intent_id
3621 AND QUERY_RESPONSE='Q'
3622 and weight>0
3623 order by 1;
3624 CURSOR get_theme_csr IS
3625 SELECT keyword,weight FROM iem_intent_dtls
3626 where intent_id=l_class_id
3627 AND QUERY_RESPONSE='Q'
3628 and weight>0
3629 order by 2 desc;
3630 begin
3631 l_theme_status:='S';
3632 select kem_flag,account_language
3633 INTO l_theme_code,l_lang
3634 FROM IEM_MSTEMAIL_ACCOUNTS
3635 WHERE EMAIL_ACCOUNT_ID=l_email_account_id;
3636 l_theme_buf.delete;
3637 l_token_buf.delete;
3638 IF l_theme_code=1 then -- Theme processing
3639 iem_text_pvt.getthemes(l_msg_id,null,l_theme_buf,l_errmsg);
3640 ELSIF l_theme_code=2 then -- Token Processing
3641 iem_text_pvt.gettokens(l_msg_id,null,l_lang,l_token_buf,l_errmsg);
3642 IF l_token_buf.count>0 THEN
3643 l_counter:=1;
3644 FOR i in l_token_buf.FIRST..l_token_buf.LAST LOOP
3645 l_theme_buf(l_counter).theme:=l_token_buf(i).token;
3646 l_theme_buf(l_counter).weight:=null;
3647 l_counter:=l_counter+1;
3648 END LOOP;
3649 END IF;
3650 END IF;
3651 -- Need to Handle Error Processing
3652 l_val:=0;
3653 -- Create the classification String from the first top 10 theme
3654 l_class_str:=' ';
3655
3656 IF l_theme_buf.count>0 then -- Theme API return themes
3657 if g_statement_log then
3658 l_logmessage:='Number of theme returned '||l_theme_buf.count ;
3659 iem_logger(l_logmessage);
3660 end if;
3661
3662 -- normalised using RMS --
3663 l_rms:=0;
3664 l_count:=0;
3665 FOR l_ind in 1.. l_theme_buf.count LOOP
3666 l_count:=l_count+1;
3667 l_rms:= l_rms+power(l_theme_buf(l_ind).weight,2);
3668 EXIT when l_count=10;
3669 END LOOP;
3670 l_rms:=sqrt(l_rms);
3671 FOR l_ind in 1.. l_theme_buf.count LOOP
3672 l_count:=l_count+1;
3673 l_theme_buf(l_ind).weight:=round(l_theme_buf(l_ind).weight/l_rms,2)*10;
3674 EXIT when l_count=10;
3675 END LOOP;
3676 -- End of normalisation Using RMS
3677 FOR l_ind in 1.. l_theme_buf.count LOOP
3678 l_class_str:=l_class_str||'about ('||l_theme_buf(l_ind).theme||'),';
3679 l_count:=l_count+1;
3680 END LOOP;
3681 ELSE
3682 if g_statement_log then
3683 l_logmessage:='No themes for OES Message '||l_msg_id;
3684 iem_logger(l_logmessage);
3685 end if;
3686 raise theme_proc_excep1; -- Theme API return zero Themes
3687 END IF;
3688 l_count:=0;
3689 FOR v1 in c1 loop
3690 FOR l_ind in l_theme_buf.FIRST .. l_theme_buf.LAST LOOP
3691 -- fix for 12.1.1 bug 7584830 to make intent case insensitive
3692 IF upper(v1.keyword)=upper(l_theme_buf(l_ind).theme) then
3693 IF (l_class<>v1.intent_id) and (l_class is not null) then
3694 l_data.extend;
3695 l_data(l_data.count):=iem_class_obj_typ(l_class,l_val);
3696 l_val:=0;
3697 END IF;
3698 l_val:=v1.weight+nvl(l_val,0);
3699 l_wtot:=l_wtot+l_val;
3700 l_class:=v1.intent_id;
3701 END IF;
3702 END LOOP;
3703 END LOOP;
3704 if l_val<>0 then
3705 l_data.extend;
3706 l_data(l_data.count):=iem_class_obj_typ(l_class,l_val);
3707 l_wtot:=l_wtot+l_val;
3708 end if;
3709 l_class_count:=1;
3710 g_topclass:=null;
3711 g_topscore:=null;
3712 l_wtot:=0;
3713 FOR x in (select * from the(select cast(l_data as iem_class_tbl_typ)
3714 from dual)a where score>0 and rownum<8 order by score desc)
3715 LOOP
3716 l_wtot:=l_wtot+x.score;
3717 END LOOP;
3718 FOR x in (select * from the(select cast(l_data as iem_class_tbl_typ)
3719 from dual)a where score>0 and rownum<8 order by score desc)
3720 LOOP
3721 x.score:=x.score*100/l_wtot;
3722 IF x.score > 0 THEN -- -ve Classification score not allowed Start
3723 IF x.score>100 THEN
3724 x.score:=100;
3725 END IF;
3726 IF l_gclassid is null then
3727 l_gclassid:=x.classification_id;
3728 g_topscore:=x.score;
3729 BEGIN
3730 select intent into g_topclass
3731 from iem_intents
3732 where intent_id=l_gclassid;
3733 EXCEPTION WHEN OTHERS THEN
3734 null;
3735 END;
3736 end if;
3737 l_classification_id:=x.classification_id;
3738 l_imt_string:=' ';
3739 l_count:=1;
3740 l_class_str:=substr(l_class_str,1,length(l_class_str)-1);
3741 iem_eml_classifications_pvt.create_item(
3742 p_api_version_number=>1.0,
3743 p_init_msg_list=>'F',
3744 p_commit=>'F',
3745 p_email_account_id=>l_email_account_id,
3746 p_classification_id=>x.classification_id,
3747 p_score=>x.score,
3748 p_message_id=>l_msg_id,
3749 p_class_string=>l_class_str,
3750 p_CREATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
3751 p_CREATION_DATE=> SYSDATE,
3752 p_LAST_UPDATED_BY=>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ,
3753 p_LAST_UPDATE_DATE=>SYSDATE,
3754 p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ,
3755 x_return_Status=>l_status,
3756 x_msg_count=>l_count,
3757 x_msg_data=>l_msg_data);
3758 l_class_count:=l_class_count+1;
3759 END IF; -- -ve Classification score not allowed end
3760 EXIT when l_class_count>7 ;
3761 END LOOP;
3762 l_theme_status:='S';
3763 exception
3764 when theme_proc_excep1 then
3765 l_theme_status:='S';
3766 when theme_proc_excep then
3767 l_theme_status:='E';
3768 when others then
3769 l_theme_status:='E';
3770 if g_exception_log then
3771 l_logmessage:='Oracle Error Encountered in IEM_THEMEPROC Procedure for OES Message '||l_msg_id||' '||sqlerrm;
3772 iem_logger(l_logmessage);
3773 end if;
3774 l_out_text:=l_out_text||' Oracle Error '||sqlerrm|| 'While theme classification processing for message id '||l_msg_id;
3775 end IEM_PROCESS_INTENT;
3776 PROCEDURE ReprocessAutoreply(p_api_version_number IN NUMBER,
3777 p_init_msg_list IN VARCHAR2 ,
3778 p_commit IN VARCHAR2 ,
3779 p_media_id in number,
3780 p_interaction_id in number,
3781 p_customer_id in number,
3782 p_contact_id in number,
3783 p_relationship_id in number,
3784 x_return_status OUT NOCOPY VARCHAR2,
3785 x_msg_count OUT NOCOPY NUMBER,
3786 x_msg_data OUT NOCOPY VARCHAR2) IS
3787
3788 l_header_rec iem_ms_base_headers%rowtype;
3789 l_mail_rec iem_rt_proc_emails%rowtype;
3790 l_class_val_tbl IEM_ROUTE_PUB.keyVals_tbl_type;
3791 l_counter number;
3792 l_message_id number;
3793 l_top_intent varchar2(50);
3794 l_top_score number;
3795 cursor c_intent is select a.intent,b.score from
3796 iem_intents a,iem_email_classifications b
3797 where b.message_id=l_message_id
3798 and a.intent_id=b.classification_id;
3799 g_statement_log boolean; -- Statement Level Logging
3800 l_intent_str varchar2(700);
3801 l_index1 number;
3802 l_index2 number;
3803 l_api_name varchar2(100):='ReprocessAutoreply';
3804 l_media_rec JTF_IH_PUB.media_rec_type;
3805 l_kb_rank number;
3806 l_api_version_number number;
3807 l_rt_media_item_id number;
3808 l_rt_interaction_id number;
3809 l_milcs_id number;
3810 l_intent_counter number;
3811 l_media_lc_rec JTF_IH_PUB.media_lc_rec_type;
3812 l_interaction_rec JTF_IH_PUB.interaction_rec_type;
3813 l_activity_rec JTF_IH_PUB.activity_rec_type;
3814 l_Stat varchar2(100);
3815 l_auto_flag varchar2(10);
3816 l_encrypted_id varchar2(100);
3817 l_ret_status varchar2(10);
3818 l_search varchar2(10);
3819 l_msg_count number;
3820 l_msg_data varchar2(255);
3821 l_tag_keyval IEM_TAGPROCESS_PUB.keyVals_tbl_type;
3822 l_folder_name varchar2(255);
3823 l_from_folder varchar2(255);
3824 l_agentid number;
3825 l_autoproc_result varchar2(1);
3826 l_param_rec_tbl IEM_RULES_ENGINE_PUB.parameter_tbl_type;
3827 l_action varchar2(50);
3828 l_rule_id number;
3829 l_search_type varchar2(100);
3830 l_repos varchar2(100);
3831 l_cm_cat_id number; -- store the category for MES category based mapping
3832 l_category_id AMV_SEARCH_PVT.amv_number_varray_type:=AMV_SEARCH_PVT.amv_number_varray_type();
3833 l_cat_counter number;
3834 l_group_id number;
3835 l_status varchar2(10);
3836 l_out_text varchar2(255);
3837 l_uid number;
3838 cursor c_class_id is
3839 select classification_id from iem_email_classifications
3840 where message_id=l_message_id
3841 order by score desc;
3842 l_start_search number;
3843 ABORT_REPROCESSING EXCEPTION;
3844 l_logmessage varchar2(1000);
3845 l_level varchar2(20);
3846 cursor c_item is select ib.item_id,ib.item_name,ib.last_update_date
3847 from amv_c_chl_item_match cim,jtf_amv_items_vl ib
3848 where cim.channel_category_id = l_cm_cat_id
3849 and cim.channel_id is null
3850 and cim.approval_status_type ='APPROVED'
3851 and cim.table_name_code ='ITEM'
3852 and cim.available_for_channel_date <= sysdate
3853 and cim.item_id = ib.item_id
3854 and nvl(ib.effective_start_date, sysdate) <= sysdate + 1
3855 and nvl(ib.expiration_date, sysdate) >= sysdate;
3856 l_email_user_name varchar2(500);
3857 l_email_domain_name varchar2(500);
3858 l_email_address varchar2(500);
3859 l_from1 number;
3860 l_from2 number;
3861 l_sender varchar2(500);
3862
3863 begin
3864 l_api_version_number:=1.0;
3865 -- Standard call to check for call compatibility.
3866 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
3867 p_api_version_number,
3868 l_api_name,
3869 G_PKG_NAME)
3870 THEN
3871 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3872 END IF;
3873 l_level:='STATEMENT';
3874 FND_LOG_REPOSITORY.init(null,null);
3875 g_statement_log:= fnd_log.test(FND_LOG.LEVEL_STATEMENT,'IEM.PLSQL.iem_email_proc_pvt');
3876 SAVEPOINT processautoreply;
3877 -- Find the record from iem_rt_proc_emails based on media id
3878 BEGIN
3879 select * into l_mail_rec from iem_rt_proc_emails
3880 where ih_media_item_id=p_media_id and msg_status='AUTOREPLY';
3881 EXCEPTION WHEN OTHERS THEN
3882 if g_exception_log then
3883 l_logmessage:='Error while selecting Message For re-processing '||sqlerrm;
3884 iem_logger(l_logmessage);
3885 end if;
3886 raise abort_reprocessing;
3887 END;
3888
3889 select * into l_header_Rec
3890 from iem_ms_base_headers
3891 where message_id=l_mail_Rec.message_id;
3892
3893 select user_name,email_address
3894 into l_email_user_name,l_email_address
3895 from iem_mstemail_accounts
3896 where email_account_id=l_mail_rec.email_account_id;
3897 l_email_domain_name:=substr(l_email_address,instr(l_email_address,'@',1)+1,length(l_email_address));
3898 l_from1:=instr(l_header_rec.from_str,'<',1,1);
3899 l_from2:=instr(l_header_rec.from_str,'>',1,1);
3900 IF l_from1>0 then -- From Address Contains Both Name and Address
3901 l_sender:=substr(l_header_rec.from_Str,l_from1+1,l_from2-l_from1-1);
3902 ELSE -- From Address contains only Address
3903 l_sender:=l_header_rec.from_str;
3904 END IF;
3905
3906 -- Create the set of Key Value pairs to be passed to Rules Engine for Document Retrieval and Routing.
3907 For v1 in c_intent LOOP
3908 l_intent_str:=nvl(l_intent_str,' ')||v1.intent;
3909 if l_top_intent is null then
3910 l_top_intent:=v1.intent;
3911 l_top_score:=v1.score;
3912 end if;
3913 END LOOP;
3914 l_counter:=1;
3915 l_class_val_tbl.delete;
3916 l_class_val_tbl(l_counter).key:='IEMNMESSAGESIZE';
3917 l_class_val_tbl(l_counter).value:=l_header_rec.message_size;
3918 l_class_val_tbl(l_counter).datatype:='N';
3919 l_counter:=l_counter+1;
3920 l_class_val_tbl(l_counter).key:='IEMSSENDERNAME';
3921 l_class_val_tbl(l_counter).value:=l_sender;
3922 l_class_val_tbl(l_counter).datatype:='S';
3923 l_counter:=l_counter+1;
3924 l_class_val_tbl(l_counter).key:='IEMSUSERACCTNAME';
3925 l_class_val_tbl(l_counter).value:=l_email_user_name;
3926 l_class_val_tbl(l_counter).datatype:='S';
3927 l_counter:=l_counter+1;
3928 l_class_val_tbl(l_counter).key:='IEMSDOMAINNAME';
3929 l_class_val_tbl(l_counter).value:=l_email_domain_name;
3930 l_class_val_tbl(l_counter).datatype:='S';
3931 /* this is missing for the time being
3932 l_counter:=l_counter+1;
3933 l_class_val_tbl(l_counter).key:='IEMSPRIORITY';
3934 l_class_val_tbl(l_counter).value:=l_post_rec.priority;
3935 l_class_val_tbl(l_counter).datatype:='S';
3936 */
3937 l_counter:=l_counter+1;
3938 l_class_val_tbl(l_counter).key:='IEMSSUBJECT';
3939 l_class_val_tbl(l_counter).value:=l_header_rec.subject;
3940 l_class_val_tbl(l_counter).datatype:='S';
3941 l_counter:=l_counter+1;
3942 l_class_val_tbl(l_counter).key:='IEMDRECEIVEDDATE';
3943 l_class_val_tbl(l_counter).value:=to_char(l_mail_rec.received_date,'YYYYMMDD');
3944 l_class_val_tbl(l_counter).datatype:='D';
3945 l_counter:=l_counter+1;
3946 l_class_val_tbl(l_counter).key:='IEMTRECEIVEDTIME';
3947 l_class_val_tbl(l_counter).value:=to_char(l_mail_rec.received_date,'HH24:MI:SS');
3948 l_class_val_tbl(l_counter).datatype:='T';
3949 l_counter:=l_counter+1;
3950 l_class_val_tbl(l_counter).key:='IEMSEMAILINTENT';
3951 l_class_val_tbl(l_counter).value:=l_top_intent;
3952 l_class_val_tbl(l_counter).datatype:='S';
3953 l_counter:=l_counter+1;
3954 l_class_val_tbl(l_counter).key:='IEMNSCOREPERCENT';
3955 l_class_val_tbl(l_counter).value:=l_top_score;
3956 l_class_val_tbl(l_counter).datatype:='N';
3957 l_counter:=l_counter+1;
3958 l_class_val_tbl(l_counter).key:='IEMSLANGUAGE';
3959 l_class_val_tbl(l_counter).value:=l_header_rec.language;
3960 l_class_val_tbl(l_counter).datatype:='S';
3961 l_counter:=l_counter+1;
3962 l_class_val_tbl(l_counter).key:='IEMSORGANIZATION';
3963 l_class_val_tbl(l_counter).value:=l_header_rec.organization;
3964 l_class_val_tbl(l_counter).datatype:='S';
3965 l_counter:=l_counter+1;
3966 l_class_val_tbl(l_counter).key:='IEMDSYSTEMDATE';
3967 l_class_val_tbl(l_counter).value:=to_char(sysdate,'YYYYMMDD');
3968 l_class_val_tbl(l_counter).datatype:='D';
3969 l_counter:=l_counter+1;
3970 l_class_val_tbl(l_counter).key:='IEMTSYSTEMTIME';
3971 l_class_val_tbl(l_counter).value:=to_char(sysdate,'HH24:MI:SS');
3972 l_class_val_tbl(l_counter).datatype:='T';
3973 l_counter:=l_counter+1;
3974 l_class_val_tbl(l_counter).key:='IEMSTOADDRESS';
3975 l_class_val_tbl(l_counter).value:=l_header_rec.to_str;
3976 l_class_val_tbl(l_counter).datatype:='S';
3977
3978 -- New KEYVALUE Pair Containing All Intents Changes MAde for MP-R By RT on 08/01/03
3979 l_counter:=l_counter+1;
3980 l_class_val_tbl(l_counter).key:='IEMSALLINTENTS';
3981 l_class_val_tbl(l_counter).value:=l_intent_str;
3982 l_class_val_tbl(l_counter).datatype:='S';
3983 -- Retrieving the Tag Data if any present ..
3984 l_tag_keyval.delete;
3985 l_search:='[REF:';
3986 l_index1:=instr(l_header_rec.subject,l_search,1,1);
3987 l_index2:=instr(substr(l_header_rec.subject,l_index1+length(l_search),length(l_header_rec.subject)-1),']',1,1);
3988 IF (l_index1 <> 0) and (l_index2<>0) THEN
3989 l_encrypted_id:=ltrim(substr(l_header_rec.subject,l_index1+length(l_search),l_index2-1));
3990 -- Reset the Tag
3991 IEM_ENCRYPT_TAGS_PVT.RESET_TAG
3992 (p_api_version_number=>1.0,
3993 p_message_id=>l_mail_rec.message_id,
3994 x_return_status=>l_ret_status ,
3995 x_msg_count=>l_msg_count,
3996 x_msg_data=>l_msg_data);
3997
3998 IF l_ret_status<>'S' THEN
3999 if g_error_log then
4000 l_logmessage:='Error while Resetting Tag For Rerouted Message';
4001 iem_logger(l_logmessage);
4002 end if;
4003 END IF;
4004 IEM_TAGPROCESS_PUB.GETTAGVALUES
4005 (p_Api_Version_Number=>1.0,
4006 p_encrypted_id =>l_encrypted_id,
4007 p_message_id=>l_mail_rec.message_id,
4008 x_key_value=>l_tag_keyval,
4009 x_msg_count=>l_msg_count,
4010 x_return_status=>l_ret_status,
4011 x_msg_data=>l_msg_data);
4012 IF l_ret_status<>'S' THEN
4013 if g_error_log then
4014 l_logmessage:='Error while Calling Tag Processing Api while reprocessing autoreplied message';
4015 iem_logger(l_logmessage);
4016 end if;
4017 raise abort_reprocessing;
4018 END IF;
4019 END IF;
4020 IF p_customer_id is not null THEN
4021 l_counter:=l_counter+1;
4022 l_class_val_tbl(l_counter).key:='IEMNCUSTOMERID';
4023 l_class_val_tbl(l_counter).value:=p_customer_id;
4024 l_class_val_tbl(l_counter).datatype:='N';
4025 END IF;
4026
4027 IF l_tag_keyval.count>0 THEN
4028 FOR j in l_tag_keyval.FIRST..l_tag_keyval.LAST LOOP
4029 l_counter:=l_counter+1;
4030 l_class_val_tbl(l_counter).key:=l_tag_keyval(j).key;
4031 l_class_val_tbl(l_counter).value:=l_tag_keyval(j).value;
4032 l_class_val_tbl(l_counter).datatype:=l_tag_keyval(j).datatype;
4033 -- Check for Agent Id if exists in TAG
4034 IF l_tag_keyval(l_counter).key='IEMNAGENTID' THEN
4035 l_agentid:=to_number(l_tag_keyval(l_counter).value);
4036 END IF;
4037 END LOOP;
4038 END IF;
4039
4040 BEGIN
4041 select name
4042 into l_folder_name
4043 from iem_route_classifications
4044 where route_classification_id=l_mail_Rec.rt_classification_id;
4045 EXCEPTION
4046 WHEN OTHERS THEN
4047 if g_exception_log then
4048 l_logmessage:='Error in getting folder name for the route classificaion id '||l_mail_rec.rt_classification_id||' and the error is '||sqlerrm;
4049 iem_logger(l_logmessage);
4050 end if;
4051 raise abort_reprocessing;
4052 END;
4053 l_counter:=l_counter+1;
4054 l_class_val_tbl(l_counter).key:='IEMSROUTINGCLASSIFICATION';
4055 l_class_val_tbl(l_counter).value:=l_folder_name;
4056 l_class_val_tbl(l_counter).datatype:='S';
4057
4058 l_counter:=l_counter+1;
4059 l_class_val_tbl(l_counter).key:='IEMNINTERACTIONID';
4060 l_class_val_tbl(l_counter).value:=p_interaction_id;
4061 l_class_val_tbl(l_counter).datatype:='N';
4062
4063 -- Add Media Id to the key value Pair
4064 l_counter:=l_counter+1;
4065 l_class_val_tbl(l_counter).key:='IEMNMEDIAID';
4066 l_class_val_tbl(l_counter).value:=p_media_id;
4067 l_class_val_tbl(l_counter).datatype:='N';
4068 l_counter:=l_counter+1;
4069 l_class_val_tbl(l_counter).key:='IEMNMESSAGEID';
4070 l_class_val_tbl(l_counter).value:=l_mail_rec.message_id;
4071 l_class_val_tbl(l_counter).datatype:='N';
4072
4073 -- Now Calling Document Retrieval Rule
4074 l_rule_id:=0;
4075 iem_rules_engine_pub.auto_process_email(p_api_version_number=>1.0,
4076 p_commit=>FND_API.G_FALSE,
4077 p_rule_type=>'DOCUMENTRETRIEVAL',
4078 p_keyvals_tbl=>l_class_val_tbl,
4079 p_accountid=>l_mail_rec.email_account_id,
4080 x_result=>l_autoproc_result,
4081 x_action=>l_action,
4082 x_parameters=>l_param_rec_tbl,
4083 x_return_status=>l_ret_status,
4084 x_msg_count=>l_msg_count,
4085 x_msg_data=>l_msg_data);
4086 IF l_ret_status<>'S' THEN
4087 l_logmessage:='Error While Calling rules Engine for DOCUMENTRETRIEVAL';
4088 END IF;
4089 IF l_autoproc_result='T' THEN
4090 if l_action <> 'MES_CATEGORY_MAPPING' THEN
4091 l_search_type:=substr(l_action,15,length(l_action));
4092 -- identfiying the repository to search
4093 if l_search_type='MES' THEN
4094 l_repos:='MES';
4095 elsif l_search_type='KM' THEN
4096 l_repos:='SMS';
4097 elsif l_search_type='BOTH' THEN
4098 l_repos:='ALL';
4099 end if;
4100 l_cat_counter:=1;
4101 IF l_param_rec_tbl.count>0 THEN
4102 FOR l_param_index in l_param_rec_tbl.FIRST..l_param_rec_tbl.LAST LOOP
4103 IF l_param_rec_tbl(l_param_index).parameter1 <> to_char(-1) then
4104 IF l_param_rec_tbl(l_param_index).parameter1='RULE_ID' then
4105 l_rule_id:=l_param_rec_tbl(l_param_index).parameter2;
4106 ELSE
4107 l_category_id.extend;
4108 l_category_id(l_cat_counter):=l_param_rec_tbl(l_param_index).parameter1;
4109 l_cat_counter:=l_cat_counter+1;
4110 END IF;
4111 END IF;
4112 END LOOP;
4113 END IF;
4114 else
4115 l_search_type:='CM'; --Category based mapping
4116 FOR l_param_index in l_param_rec_tbl.FIRST..l_param_rec_tbl.LAST LOOP
4117 l_cm_cat_id:=l_param_rec_tbl(l_param_index).parameter1;
4118 EXIT;
4119 END LOOP;
4120 end if;
4121 else
4122 l_search_type:=null;
4123 l_cm_Cat_id:=0;
4124 end if ; -- end if for l_autoproc_result='T'
4125 -- CALLING ROUTING ----
4126 iem_email_proc_pvt.IEM_ROUTING_PROC(
4127 p_email_account_id=>l_mail_rec.email_account_id,
4128 p_keyval=>l_class_val_tbl,
4129 x_routing_group_id=>l_group_id,
4130 x_status=>l_status,
4131 x_out_text=>l_out_text) ;
4132 IF l_status <>'S' THEN
4133 if g_error_log then
4134 l_logmessage:=l_out_text;
4135 iem_logger(l_logmessage);
4136 end if;
4137 raise abort_reprocessing;
4138 END IF;
4139 IF l_group_id=-1 then
4140 l_group_id:=0;
4141 l_auto_flag:='Y';
4142 END IF;
4143 update iem_rt_proc_emails
4144 set resource_id=0,
4145 group_id=l_group_id,
4146 customer_id=p_customer_id,
4147 contact_id=p_contact_id,
4148 relationship_id=p_relationship_id,
4149 msg_status=null,
4150 mail_proc_status='P',
4151 category_map_id=l_cm_cat_id
4152 where message_id=l_mail_rec.message_id;
4153
4154 IF l_auto_flag='Y' THEN -- auto Routing Processing
4155 -- Create a Record in RT Table
4156 BEGIN
4157 SAVEPOINT AUTO_ROUTE;
4158 IEM_CLIENT_PUB.createRTItem (p_api_version_number=>1.0,
4159 p_init_msg_list=>'F',
4160 p_commit=>'F',
4161 p_message_id =>l_mail_rec.message_id,
4162 p_to_resource_id =>l_agentid,
4163 p_from_resource_id =>l_agentid,
4164 p_status =>'N',
4165 p_reason =>'O',
4166 p_interaction_id =>p_interaction_id,
4167 x_return_status =>l_ret_status,
4168 x_msg_count =>l_msg_count,
4169 x_msg_data =>l_msg_data,
4170 x_rt_media_item_id =>l_rt_media_item_id,
4171 x_rt_interaction_id =>l_rt_interaction_id);
4172 IF l_ret_status<>'S' THEN
4173 if g_error_log then
4174 l_logmessage:='Failed To Auto Route The Message due to error in create RT Item ';
4175 iem_logger(l_logmessage);
4176 end if;
4177 rollback to auto_route;
4178 ELSE
4179 -- Create MLCS for Auto Routing
4180
4181 l_media_lc_rec.media_id :=p_media_id ;
4182 l_media_lc_rec.milcs_type_id := 30; --MAIL_AUTOROUTE
4183 l_media_lc_rec.start_date_time := sysdate;
4184 l_media_lc_rec.handler_id := 680;
4185 l_media_lc_rec.type_type := 'Email, Inbound';
4186 l_media_lc_rec.resource_id := l_agentid;
4187
4188 iem_email_proc_pvt.IEM_PROC_IH(
4189 p_type=>'MLCS' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
4190 p_action=>'ADD' , -- ADD/UPDATE/CLOSE
4191 p_interaction_rec=>l_interaction_rec,
4192 p_activity_rec=>l_activity_rec ,
4193 p_media_lc_rec=>l_media_lc_Rec ,
4194 p_media_rec=>l_media_rec ,
4195 x_id=>l_milcs_id,
4196 x_status=>l_stat ,
4197 x_out_text=>l_out_text );
4198 IF l_stat<>'S' THEN
4199 if g_error_log then
4200 l_logmessage:='Error while creating MLCS for Auto Route '||l_out_text;
4201 iem_logger(l_logmessage);
4202 end if;
4203 rollback to auto_route;
4204 ELSE
4205 -- Update the Media Life Cycle for Auto Routing
4206 l_media_lc_rec.milcs_id:=l_milcs_id;
4207 iem_email_proc_pvt.IEM_PROC_IH(
4208 p_type=>'MLCS' , -- MEDIA/ACTIVITY/MLCS/INTERACTION
4209 p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
4210 p_interaction_rec=>l_interaction_rec,
4211 p_activity_rec=>l_activity_rec ,
4212 p_media_lc_rec=>l_media_lc_Rec ,
4213 p_media_rec=>l_media_rec ,
4214 x_id=>l_milcs_id,
4215 x_status=>l_stat ,
4216 x_out_text=>l_out_text );
4217 END IF;
4218
4219 IF l_stat<>'S' THEN
4220 if g_error_log then
4221 l_logmessage:='Error while updating MLCS for Auto Route '||l_out_text;
4222 iem_logger(l_logmessage);
4223 end if;
4224 rollback to auto_route;
4225 END IF;
4226 -- In case of autoroute update the interaction with resource id of the agent to which
4227 -- the message is autorouted to
4228 l_interaction_rec.interaction_id:=p_interaction_id;
4229 l_interaction_rec.resource_id:=l_agentid;
4230 JTF_IH_PUB.Update_Interaction( p_api_version => 1.1,
4231 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
4232 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
4233 p_user_id =>nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
4234 p_login_id =>TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
4235 x_return_status => l_ret_status,
4236 x_msg_count => l_msg_count,
4237 x_msg_data => l_msg_data,
4238 p_interaction_rec => l_interaction_rec
4239 );
4240 IF l_ret_status<>'S' THEN
4241 if g_error_log then
4242 l_logmessage:='Error while updating Interactions for Auto Route ';
4243 iem_logger(l_logmessage);
4244 end if;
4245 rollback to auto_route;
4246 END IF;
4247 if g_statement_log then
4248 l_logmessage:='Successfully AutoRoute The Message ';
4249 iem_logger(l_logmessage);
4250 end if;
4251 END IF;
4252 EXCEPTION WHEN OTHERS THEN
4253 rollback to auto_route;
4254 END;
4255 END IF; -- End for Auto-Routing
4256 -- Calling the specific search at the End
4257 if g_statement_log then
4258 l_logmessage:='Calling Specific Search API ' ;
4259 iem_logger(l_logmessage);
4260 end if;
4261 BEGIN
4262 IF l_search_type<>'CM' THEN -- Not a MES category based mapping
4263 l_start_search:=1;
4264 FOR v1 in c_class_id LOOP
4265 IEM_EMAIL_PROC_PVT.IEM_WF_SPECIFICSEARCH(
4266 l_mail_rec.message_id ,
4267 l_mail_rec.email_account_id ,
4268 v1.classification_id,
4269 l_category_id,
4270 l_repos,
4271 l_stat ,
4272 l_out_text);
4273 l_start_search:=l_start_search+1;
4274 EXIT when l_start_search>l_intent_counter;
4275 END LOOP;
4276 ELSIF nvl(l_search_type,' ')='CM' and l_cm_cat_id is not null then
4277 for v_item in c_item LOOP
4278 select count(*) into l_kb_rank
4279 from iem_doc_usage_stats
4280 where kb_doc_id=v_item.item_id;
4281 IEM_KB_RESULTS_PVT.create_item(p_api_version_number=>1.0,
4282 p_init_msg_list=>'F' ,
4283 p_commit=>'F' ,
4284 p_message_id =>l_mail_rec.message_id,
4285 p_classification_id=>0,
4286 p_email_account_id=>l_mail_rec.email_account_id ,
4287 p_document_id =>to_char(v_item.item_id),
4288 p_kb_repository_name =>'MES',
4289 p_kb_category_name =>'MES',
4290 p_document_title =>v_item.item_name,
4291 p_doc_last_modified_date=>v_item.last_update_date,
4292 p_score =>l_kb_rank,
4293 p_url =>' ',
4294 p_kb_delete=>'N',
4295 p_CREATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
4296 p_CREATION_DATE =>SYSDATE,
4297 p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
4298 p_LAST_UPDATE_DATE =>SYSDATE,
4299 p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
4300 p_ATTRIBUTE1 =>null,
4301 p_ATTRIBUTE2 =>null,
4302 p_ATTRIBUTE3 =>null,
4303 p_ATTRIBUTE4 =>null,
4304 p_ATTRIBUTE5 =>null,
4305 p_ATTRIBUTE6 =>null,
4306 p_ATTRIBUTE7 =>null,
4307 p_ATTRIBUTE8 =>null,
4308 p_ATTRIBUTE9 =>null,
4309 p_ATTRIBUTE10 =>null,
4310 p_ATTRIBUTE11 =>null,
4311 p_ATTRIBUTE12 =>null,
4312 p_ATTRIBUTE13 =>null,
4313 p_ATTRIBUTE14 =>null,
4314 p_ATTRIBUTE15 =>null,
4315 x_return_status=>l_ret_status,
4316 x_msg_count=>l_msg_count,
4317 x_msg_data=>l_msg_data);
4318 END LOOP;
4319 END IF; -- Endof search_type<>'CM'
4320 EXCEPTION WHEN OTHERS THEN
4321 if g_exception_log then
4322 l_logmessage:='Error in calling Document Search while reprocessing auto-reply message'||sqlerrm ;
4323 iem_logger(l_logmessage);
4324 end if;
4325 END;
4326 if g_statement_log then
4327 l_logmessage:='End Of Calling Specific Search API and end of Processing for the message ' ;
4328 iem_logger(l_logmessage);
4329 end if;
4330 -- Standard Check Of p_commit.
4331 IF p_commit='T' THEN
4332 COMMIT WORK;
4333 END IF;
4334 -- Standard callto get message count and if count is 1, get message info.
4335 FND_MSG_PUB.Count_And_Get
4336 ( p_count => x_msg_count,
4337 p_data => x_msg_data
4338 );
4339 x_return_status:='S';
4340
4341 EXCEPTION WHEN ABORT_REPROCESSING THEN
4342 x_Return_status:='E';
4343 rollback to processautoreply;
4344 WHEN OTHERS THEN
4345 if g_exception_log then
4346 l_logmessage:='Error occur during Reprocessing autoreply message '||sqlerrm ;
4347 iem_logger(l_logmessage);
4348 end if;
4349 rollback to processautoreply;
4350 x_return_status := FND_API.G_RET_STS_ERROR;
4351 IF FND_MSG_PUB.Check_Msg_Level
4352 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4353 THEN
4354 FND_MSG_PUB.Add_Exc_Msg
4355 ( G_PKG_NAME ,
4356 l_api_name
4357 );
4358 END IF;
4359 FND_MSG_PUB.Count_And_Get
4360 ( p_count => x_msg_count ,
4361 p_data => x_msg_data
4362 );
4363 END REPROCESSAUTOREPLY;
4364
4365 procedure IEM_WF_SPECIFICSEARCH(
4366 l_msg_id in number,
4367 l_email_account_id in number,
4368 l_classification_id in number,
4369 l_category_id AMV_SEARCH_PVT.amv_number_varray_type,
4370 l_repos in varchar2,
4371 l_stat out nocopy varchar2,
4372 l_out_text out nocopy varchar2)
4373 is
4374 l_return_status VARCHAR2(20);
4375 l_msg_count NUMBER;
4376 l_msg_data VARCHAR2(400);
4377 l_doc_id number;
4378 l_kb_repos iem_kb_results.kb_repository_name%type;
4379 l_rows_returned cs_kb_number_tbl_type :=cs_kb_number_tbl_type();
4380 l_next_row_pos cs_kb_number_tbl_type :=cs_kb_number_tbl_type();
4381 l_total_row_cnt cs_kb_number_tbl_type :=cs_kb_number_tbl_type();
4382 l_logmessage varchar2(500);
4383 l_level varchar2(20):='STATEMENT';
4384 l_app_id number;
4385 l_part number;
4386 l_flag number:=1;
4387 l_ret number;
4388 l_search varchar2(100);
4389 l_theme varchar2(200);
4390 l_tstr varchar2(2000);
4391 l_errtext varchar2(200);
4392 l_score number;
4393 l_class NUMBER;
4394 l_count NUMBER;
4395 l_next_row_tbl cs_kb_number_tbl_type:=cs_kb_number_tbl_type();
4396 l_total_row_tbl cs_kb_number_tbl_type:=cs_kb_number_tbl_type();
4397 l_area_array AMV_SEARCH_PVT.amv_char_varray_type:=null;
4398 l_result_array cs_kb_result_varray_type;
4399 l_amv_result_array AMV_SEARCH_PVT.amv_searchres_varray_type;
4400 l_content_array AMV_SEARCH_PVT.amv_char_varray_type:=null;
4401 l_param_array AMV_SEARCH_PVT.amv_searchpar_varray_type;
4402 l_rep cs_kb_varchar100_tbl_type ;
4403 l_imt_string varchar2(4000);
4404 r_imt_string varchar2(4000);
4405 l_proc_name varchar2(30):='IEM_WF_SPECIFICSEARCH';
4406 --l_category_id AMV_SEARCH_PVT.amv_number_varray_type:=AMV_SEARCH_PVT.amv_number_varray_type();
4407 l_tag1 number;
4408 l_cnt number;
4409 l_res1 varchar2(10);
4410 l_res2 varchar2(10);
4411 l_search_repos varchar2(10);
4412 l_days number ;
4413 l_user_id number ;
4414 l_rows_req cs_kb_number_tbl_type ;
4415 l_rows number;
4416 l_start_row cs_kb_number_tbl_type:=cs_kb_number_tbl_type(1,1);
4417 l_sms_string varchar2(255);
4418 l_sms_count number;
4419 l_counter number:=1;
4420 g_app_id number;
4421 cursor c1 is select category_id from iem_account_categories
4422 where email_account_id=l_email_account_id;
4423 cursor c2 is
4424 select keyword,weight from iem_intent_dtls where intent_id=l_classification_id
4425 and query_response='R';
4426 cursor c_doc is
4427 select document_id,KB_REPOSITORY_NAME,score,kb_result_id from iem_kb_results
4428 where message_id=l_msg_id
4429 order by 2,1,score asc;
4430 begin
4431 select count(*) into l_cnt from iem_kb_results
4432 where message_id=l_msg_id
4433 and classification_id=l_classification_id
4434 and email_account_id=l_email_account_id;
4435 IF l_cnt=0 THEN
4436 if g_statement_log then
4437 l_logmessage:= 'Start Initializing for Specific search '||l_msg_id;
4438 iem_logger(l_logmessage);
4439 end if;
4440 -- Prepare the response String for Intent
4441 for v2 in c2 loop
4442 r_imt_string:=r_imt_string||'about ('||v2.keyword||')*'||v2.weight||',';
4443 end loop;
4444 if r_imt_string is not null then
4445 r_imt_string:=substr(r_imt_string,1,length(r_imt_string)-1);
4446 end if;
4447
4448 l_rows:=10; -- Number of Document Retrieved...
4449 l_rows_req :=cs_kb_number_tbl_type(l_rows,l_rows);
4450 G_APP_ID:=520;
4451 l_area_array := AMV_SEARCH_PVT.amv_char_varray_type();
4452 l_area_array.extend;
4453 l_area_array(1) := 'ITEM';
4454 l_content_array := AMV_SEARCH_PVT.amv_char_varray_type();
4455 l_content_array.extend;
4456 l_content_array(1) := 'CONTENT';
4457 l_content_array.extend;
4458 l_param_array := AMV_SEARCH_PVT.amv_searchpar_varray_type();
4459 l_rep :=cs_kb_varchar100_tbl_type() ;
4460 begin
4461 select classification_string into l_imt_string
4462 from IEM_EMAIL_CLASSIFICATIONS
4463 WHERE MESSAGE_ID=l_msg_id AND EMAIL_ACCOUNT_ID=l_email_account_id
4464 and classification_id=l_classification_id;
4465 IF l_repos is null then
4466 l_search_repos:=FND_PROFILE.VALUE_SPECIFIC('IEM_KNOWLEDGE_BASE');
4467 IF l_search_repos is null then
4468 l_search_repos:='MES';
4469 END IF;
4470 else
4471 l_search_repos:=l_repos;
4472 end if;
4473 IF (l_search_repos='MES') or (l_search_repos='ALL') Then
4474 if g_statement_log then
4475 l_logmessage:= 'Calling the MES Specific Search For Message Id '||l_msg_id;
4476 iem_logger(l_logmessage);
4477 end if;
4478 l_rep :=cs_kb_varchar100_tbl_type('MES') ;
4479 cs_knowledge_grp.Specific_Search(
4480 p_api_version => 1.0,
4481 p_init_msg_list => fnd_api.g_true,
4482 --p_validation_level => p_validation_level,
4483 x_return_status => l_return_status,
4484 x_msg_count => l_msg_count,
4485 x_msg_data => l_msg_data,
4486 p_repository_tbl => l_rep,
4487 p_search_string => l_imt_string,
4488 p_updated_in_days => l_days,
4489 p_check_login_user => FND_API.G_FALSE,
4490 p_application_id => G_APP_ID,
4491 p_area_array => l_area_array,
4492 p_content_array => l_content_array,
4493 p_param_array => l_param_array,
4494 p_user_id => l_user_id,
4495 p_category_id => l_category_id,
4496 p_include_subcats => FND_API.G_FALSE,
4497 p_external_contents => FND_API.G_TRUE,
4498 p_rows_requested_tbl => l_rows_req,
4499 p_start_row_pos_tbl => l_start_row,
4500 p_get_total_cnt_flag => 'T',
4501 x_rows_returned_tbl => l_rows_returned,
4502 x_next_row_pos_tbl => l_next_row_pos,
4503 x_total_row_cnt_tbl => l_total_row_cnt,
4504 x_result_array => l_result_array);
4505
4506 if g_statement_log then
4507 l_logmessage:= 'End Calling the Specific Search For Message Id '||l_msg_id||' No of document Returned '||l_result_array.count;
4508 iem_logger(l_logmessage);
4509 end if;
4510 -- Insert The Data into IEM_KB_RESULTS
4511
4512 FOR l_count IN 1..l_result_array.count LOOP
4513 IEM_KB_RESULTS_PVT.create_item(p_api_version_number=>1.0,
4514 p_init_msg_list=>'F' ,
4515 p_commit=>'F' ,
4516 p_message_id =>l_msg_id,
4517 p_classification_id=>l_classification_id,
4518 p_email_account_id=>l_email_account_id ,
4519 p_document_id =>to_char(l_result_array(l_count).id) ,
4520 p_kb_repository_name =>l_result_array(l_count).repository,
4521 p_kb_category_name =>l_result_array(l_count).repository,
4522 p_document_title =>l_result_array(l_count).title,
4523 p_doc_last_modified_date=>l_result_array(l_count).last_update_date,
4524 p_score =>to_char(l_result_array(l_count).score),
4525 p_url =>l_result_array(l_count).url_string,
4526 p_kb_delete=>'N',
4527 p_CREATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
4528 p_CREATION_DATE =>SYSDATE,
4529 p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
4530 p_LAST_UPDATE_DATE =>SYSDATE,
4531 p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
4532 p_ATTRIBUTE1 =>null,
4533 p_ATTRIBUTE2 =>null,
4534 p_ATTRIBUTE3 =>null,
4535 p_ATTRIBUTE4 =>null,
4536 p_ATTRIBUTE5 =>null,
4537 p_ATTRIBUTE6 =>null,
4538 p_ATTRIBUTE7 =>null,
4539 p_ATTRIBUTE8 =>null,
4540 p_ATTRIBUTE9 =>null,
4541 p_ATTRIBUTE10 =>null,
4542 p_ATTRIBUTE11 =>null,
4543 p_ATTRIBUTE12 =>null,
4544 p_ATTRIBUTE13 =>null,
4545 p_ATTRIBUTE14 =>null,
4546 p_ATTRIBUTE15 =>null,
4547 x_return_status=>l_return_status,
4548 x_msg_count=>l_msg_count,
4549 x_msg_data=>l_msg_data);
4550 END LOOP;
4551 -- Calling Search Api for Response String Separately
4552 IF r_imt_string is not null then
4553 cs_knowledge_grp.Specific_Search(
4554 p_api_version => 1.0,
4555 p_init_msg_list => fnd_api.g_true,
4556 --p_validation_level => p_validation_level,
4557 x_return_status => l_return_status,
4558 x_msg_count => l_msg_count,
4559 x_msg_data => l_msg_data,
4560 p_repository_tbl => l_rep,
4561 p_search_string => r_imt_string,
4562 p_updated_in_days => l_days,
4563 p_check_login_user => FND_API.G_FALSE,
4564 p_application_id => G_APP_ID,
4565 p_area_array => l_area_array,
4566 p_content_array => l_content_array,
4567 p_param_array => l_param_array,
4568 p_user_id => l_user_id,
4569 p_category_id => l_category_id,
4570 p_include_subcats => FND_API.G_FALSE,
4571 p_external_contents => FND_API.G_TRUE,
4572 p_rows_requested_tbl => l_rows_req,
4573 p_start_row_pos_tbl => l_start_row,
4574 p_get_total_cnt_flag => 'T',
4575 x_rows_returned_tbl => l_rows_returned,
4576 x_next_row_pos_tbl => l_next_row_pos,
4577 x_total_row_cnt_tbl => l_total_row_cnt,
4578 x_result_array => l_result_array);
4579
4580 if g_statement_log then
4581 l_logmessage:= 'End Calling the Specific Search For Message Id '||l_msg_id||' No of document Returned '||l_result_array.count;
4582 iem_logger(l_logmessage);
4583 end if;
4584 -- Insert The Data into IEM_KB_RESULTS
4585
4586 FOR l_count IN 1..l_result_array.count LOOP
4587 IEM_KB_RESULTS_PVT.create_item(p_api_version_number=>1.0,
4588 p_init_msg_list=>'F' ,
4589 p_commit=>'F' ,
4590 p_message_id =>l_msg_id,
4591 p_classification_id=>l_classification_id,
4592 p_email_account_id=>l_email_account_id ,
4593 p_document_id =>to_char(l_result_array(l_count).id) ,
4594 p_kb_repository_name =>l_result_array(l_count).repository,
4595 p_kb_category_name =>l_result_array(l_count).repository,
4596 p_document_title =>l_result_array(l_count).title,
4597 p_doc_last_modified_date=>l_result_array(l_count).last_update_date,
4598 p_score =>to_char(l_result_array(l_count).score),
4599 p_url =>l_result_array(l_count).url_string,
4600 p_kb_delete=>'N',
4601 p_CREATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
4602 p_CREATION_DATE =>SYSDATE,
4603 p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
4604 p_LAST_UPDATE_DATE =>SYSDATE,
4605 p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
4606 p_ATTRIBUTE1 =>null,
4607 p_ATTRIBUTE2 =>null,
4608 p_ATTRIBUTE3 =>null,
4609 p_ATTRIBUTE4 =>null,
4610 p_ATTRIBUTE5 =>null,
4611 p_ATTRIBUTE6 =>null,
4612 p_ATTRIBUTE7 =>null,
4613 p_ATTRIBUTE8 =>null,
4614 p_ATTRIBUTE9 =>null,
4615 p_ATTRIBUTE10 =>null,
4616 p_ATTRIBUTE11 =>null,
4617 p_ATTRIBUTE12 =>null,
4618 p_ATTRIBUTE13 =>null,
4619 p_ATTRIBUTE14 =>null,
4620 p_ATTRIBUTE15 =>null,
4621 x_return_status=>l_return_status,
4622 x_msg_count=>l_msg_count,
4623 x_msg_data=>l_msg_data);
4624 END LOOP;
4625 END IF; -- End if for if r_imt_string is not null
4626 END IF;
4627 IF (l_search_repos='SMS') or (l_search_repos='ALL') Then
4628 if g_statement_log then
4629 l_logmessage:= 'Calling the SMS Specific Search For Message Id '||l_msg_id;
4630 iem_logger(l_logmessage);
4631 end if;
4632 l_rep :=cs_kb_varchar100_tbl_type('SMS') ;
4633 -- Currently SMS has 255 character limitations . So we have to
4634 --truncate the SMS search string to 255 character length which
4635 --will removed later
4636 IF length(l_imt_string)>255 THEN
4637 l_sms_string:=substr(l_imt_string,1,255);
4638 l_sms_count:=instr(l_sms_string,',about',-1,1);
4639 l_imt_string:=substr(l_sms_string,1,l_sms_count-1);
4640 END IF;
4641 cs_knowledge_grp.Specific_Search(
4642 p_api_version => 1.0,
4643 p_init_msg_list => fnd_api.g_true,
4644 --p_validation_level => p_validation_level,
4645 x_return_status => l_return_status,
4646 x_msg_count => l_msg_count,
4647 x_msg_data => l_msg_data,
4648 p_repository_tbl => l_rep,
4649 p_search_string => l_imt_string,
4650 p_updated_in_days => l_days,
4651 p_check_login_user => FND_API.G_FALSE,
4652 p_application_id => G_APP_ID,
4653 p_area_array => l_area_array,
4654 p_content_array => l_content_array,
4655 p_param_array => l_param_array,
4656 p_user_id => l_user_id,
4657 p_category_id => l_category_id,
4658 p_include_subcats => FND_API.G_TRUE,
4659 p_external_contents => FND_API.G_TRUE,
4660 p_rows_requested_tbl => l_rows_req,
4661 p_start_row_pos_tbl => l_start_row,
4662 p_get_total_cnt_flag => 'T',
4663 x_rows_returned_tbl => l_rows_returned,
4664 x_next_row_pos_tbl => l_next_row_pos,
4665 x_total_row_cnt_tbl => l_total_row_cnt,
4666 x_result_array => l_result_array);
4667
4668 if g_statement_log then
4669 l_logmessage:= 'End Calling the Specific Search For Message Id '||l_msg_id||' No of document Returned '||l_result_array.count;
4670 iem_logger(l_logmessage);
4671 end if;
4672 -- Insert The Data into IEM_KB_RESULTS
4673
4674 FOR l_count IN 1..l_result_array.count LOOP
4675 IEM_KB_RESULTS_PVT.create_item(p_api_version_number=>1.0,
4676 p_init_msg_list=>'F' ,
4677 p_commit=>'F' ,
4678 p_message_id =>l_msg_id,
4679 p_classification_id=>l_classification_id,
4680 p_email_account_id=>l_email_account_id ,
4681 p_document_id =>to_char(l_result_array(l_count).id) ,
4682 p_kb_repository_name =>l_result_array(l_count).repository,
4683 p_kb_category_name =>l_result_array(l_count).repository,
4684 p_document_title =>l_result_array(l_count).title,
4685 p_doc_last_modified_date=>l_result_array(l_count).last_update_date,
4686 p_score =>to_char(l_result_array(l_count).score),
4687 p_url =>l_result_array(l_count).url_string,
4688 p_kb_delete=>'N',
4689 p_CREATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
4690 p_CREATION_DATE =>SYSDATE,
4691 p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
4692 p_LAST_UPDATE_DATE =>SYSDATE,
4693 p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
4694 p_ATTRIBUTE1 =>null,
4695 p_ATTRIBUTE2 =>null,
4696 p_ATTRIBUTE3 =>null,
4697 p_ATTRIBUTE4 =>null,
4698 p_ATTRIBUTE5 =>null,
4699 p_ATTRIBUTE6 =>null,
4700 p_ATTRIBUTE7 =>null,
4701 p_ATTRIBUTE8 =>null,
4702 p_ATTRIBUTE9 =>null,
4703 p_ATTRIBUTE10 =>null,
4704 p_ATTRIBUTE11 =>null,
4705 p_ATTRIBUTE12 =>null,
4706 p_ATTRIBUTE13 =>null,
4707 p_ATTRIBUTE14 =>null,
4708 p_ATTRIBUTE15 =>null,
4709 x_return_status=>l_return_status,
4710 x_msg_count=>l_msg_count,
4711 x_msg_data=>l_msg_data);
4712 END LOOP;
4713 IF r_imt_string is not null then
4714 -- Calling Specific Search for Response String
4715 cs_knowledge_grp.Specific_Search(
4716 p_api_version => 1.0,
4717 p_init_msg_list => fnd_api.g_true,
4718 --p_validation_level => p_validation_level,
4719 x_return_status => l_return_status,
4720 x_msg_count => l_msg_count,
4721 x_msg_data => l_msg_data,
4722 p_repository_tbl => l_rep,
4723 p_search_string => r_imt_string,
4724 p_updated_in_days => l_days,
4725 p_check_login_user => FND_API.G_FALSE,
4726 p_application_id => G_APP_ID,
4727 p_area_array => l_area_array,
4728 p_content_array => l_content_array,
4729 p_param_array => l_param_array,
4730 p_user_id => l_user_id,
4731 p_category_id => l_category_id,
4732 p_include_subcats => FND_API.G_FALSE,
4733 p_external_contents => FND_API.G_TRUE,
4734 p_rows_requested_tbl => l_rows_req,
4735 p_start_row_pos_tbl => l_start_row,
4736 p_get_total_cnt_flag => 'T',
4737 x_rows_returned_tbl => l_rows_returned,
4738 x_next_row_pos_tbl => l_next_row_pos,
4739 x_total_row_cnt_tbl => l_total_row_cnt,
4740 x_result_array => l_result_array);
4741
4742 if g_statement_log then
4743 l_logmessage:= 'End Calling the Specific Search For Message Id '||l_msg_id||' No of document Returned '||l_result_array.count;
4744 iem_logger(l_logmessage);
4745 end if;
4746 -- Insert The Data into IEM_KB_RESULTS
4747
4748 FOR l_count IN 1..l_result_array.count LOOP
4749 IEM_KB_RESULTS_PVT.create_item(p_api_version_number=>1.0,
4750 p_init_msg_list=>'F' ,
4751 p_commit=>'F' ,
4752 p_message_id =>l_msg_id,
4753 p_classification_id=>l_classification_id,
4754 p_email_account_id=>l_email_account_id ,
4755 p_document_id =>to_char(l_result_array(l_count).id) ,
4756 p_kb_repository_name =>l_result_array(l_count).repository,
4757 p_kb_category_name =>l_result_array(l_count).repository,
4758 p_document_title =>l_result_array(l_count).title,
4759 p_doc_last_modified_date=>l_result_array(l_count).last_update_date,
4760 p_score =>to_char(l_result_array(l_count).score),
4761 p_url =>l_result_array(l_count).url_string,
4762 p_kb_delete=>'N',
4763 p_CREATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
4764 p_CREATION_DATE =>SYSDATE,
4765 p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
4766 p_LAST_UPDATE_DATE =>SYSDATE,
4767 p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
4768 p_ATTRIBUTE1 =>null,
4769 p_ATTRIBUTE2 =>null,
4770 p_ATTRIBUTE3 =>null,
4771 p_ATTRIBUTE4 =>null,
4772 p_ATTRIBUTE5 =>null,
4773 p_ATTRIBUTE6 =>null,
4774 p_ATTRIBUTE7 =>null,
4775 p_ATTRIBUTE8 =>null,
4776 p_ATTRIBUTE9 =>null,
4777 p_ATTRIBUTE10 =>null,
4778 p_ATTRIBUTE11 =>null,
4779 p_ATTRIBUTE12 =>null,
4780 p_ATTRIBUTE13 =>null,
4781 p_ATTRIBUTE14 =>null,
4782 p_ATTRIBUTE15 =>null,
4783 x_return_status=>l_return_status,
4784 x_msg_count=>l_msg_count,
4785 x_msg_data=>l_msg_data);
4786 END LOOP;
4787 END IF; --End if for r_imt_string is not null
4788 END IF;
4789 -- Deleting duplicate rows from Document retrieval set
4790 l_doc_id:=0;
4791 l_kb_repos:=null;
4792 for v_doc in c_doc LOOP
4793 if (v_doc.document_id = l_doc_id and v_doc.kb_repository_name=l_kb_repos) then
4794 delete from iem_kb_results where message_id=l_msg_id and kb_result_id=v_doc.kb_result_id;
4795 end if;
4796 l_doc_id:=v_doc.document_id;
4797 l_kb_repos:=v_doc.kb_repository_name;
4798 END LOOP;
4799
4800 EXCEPTION when others then
4801 null;
4802 END;
4803 l_stat:='S';
4804 ELSE
4805 l_stat:='S';
4806 END IF;
4807 exception
4808 when others then
4809 if g_exception_log then
4810 l_logmessage:='Oracle Error in MES Search '||sqlerrm||' while processing for message id '||l_msg_id;
4811 iem_logger(l_logmessage);
4812 end if;
4813 l_stat:='E';
4814 l_out_text:='Oracle Error in Specific Search '||sqlerrm||' while processing for message id '||l_msg_id;
4815 end IEM_WF_SPECIFICSEARCH;
4816 procedure IEM_RETURN_ENCRYPTID
4817 (p_subject in varchar2,
4818 x_id out nocopy varchar2,
4819 x_Status out nocopy varchar2) IS
4820 l_search varchar2(10);
4821 l_index1 number;
4822 l_index2 number;
4823 l_encrypted_id varchar2(100);
4824 begin
4825 l_search:='[REF:';
4826 l_index1:=instr(p_subject,l_search,1,1);
4827 l_index2:=instr(substr(p_subject,l_index1+length(l_search),length(p_subject)-1),']',1,1);
4828 IF (l_index1 <> 0) and (l_index2<>0) THEN
4829 l_encrypted_id:=ltrim(substr(p_subject,l_index1+length(l_search),l_index2-1));
4830 END IF;
4831 x_id:=l_encrypted_id;
4832 x_status:='S';
4833 EXCEPTION WHEN OTHERS THEN
4834 x_status:='E';
4835 end IEM_RETURN_ENCRYPTID;
4836
4837
4838 end IEM_EMAIL_PROC_PVT;