1 package BODY IEM_MIGRATION_PVT as
2 /* $Header: iemvmgrb.pls 120.24.12010000.3 2008/11/06 00:26:05 rtripath ship $*/
3 g_statement_log boolean; -- Statement Level Logging
4 g_exception_log boolean; -- Statement Level Logging
5 g_error_log boolean; -- Statement Level Logging
6
7
8 procedure build_migration_queue(x_status out nocopy varchar2) IS
9 cursor c_account is select email_account_id,email_user,domain,EMAIL_PASSWORD,db_link from
10 iem_email_accounts a,iem_db_connections b
11 where a.db_server_id=b.db_server_id
12 and b.is_admin='A'
13 and upper(a.email_user)<>'INTENT';
14 l_email_account_id number;
15 cursor c_folder is
16 select name||','||a.route_classification_id name from iem_route_classifications a,iem_account_route_class b
17 where a.route_classification_id=b.route_classification_id
18 and b.email_account_id=l_email_account_id
19 union
20 select 'Inbox' from dual
21 union
22 select 'Drafts' from dual
23 union
24 select 'Resolved' from dual
25 union
26 select 'Sent' from dual
27 union
28 select 'Deleted' from dual
29 union
30 select 'Admin' from dual
31 union
32 select 'Retry' from dual;
33 l_total_count number;
34 l_str varchar2(255);
35 G_IM_LINK varchar2(255);
36 G_FOLDER varchar2(255);
37 l_ret number;
38 l_auth number;
39 l_class_id number;
40 l_folder_name varchar2(256);
41 l_folder_type varchar2(1);
42 l_msg_table iem_im_wrappers_pvt.msg_table;
43 l_status varchar2(1);
44 l_status_text varchar2(1000);
45 l_ack_flag number:=0;
46 cursor c_agent is select agent_account_id,resource_id from iem_agent_accounts
47 where email_account_id= l_email_account_id;
48 l_CREATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID'));
49 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
50 l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
51 l_mig_id number;
52 l_logmessage varchar2(1000);
53 x_folder_tbl jtf_varchar2_table_100:=jtf_varchar2_table_100() ;
54 Type get_folder_data is REF CURSOR;
55 arch_cur get_folder_data;
56 l_folder_count number;
57 l_arch_folder varchar2(100);
58 begin
59 -- Check Logging Enabled or Not...
60 FND_LOG_REPOSITORY.init(null,null);
61 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
62 g_exception_log:=true;
63 end if;
64 for v1 in c_account LOOP
65 l_email_account_id:=v1.email_account_id;
66 -- Authenticate into OES
67 G_IM_LINK:='@'||v1.db_link;
68 l_str:='begin :l_auth:=im_api.authenticate'||G_IM_LINK||'(:a_user,:a_domain,:a_password);end; ';
69 EXECUTE IMMEDIATE l_str using OUT l_auth,v1.email_user,v1.domain,v1.email_password;
70 open c_folder;
71 fetch c_folder bulk collect into x_folder_tbl;
72 close c_folder;
73
74 -- Check for archived folder
75 begin
76 open arch_cur for
77 'select arch_folder_name from iem_Archived_Folders where
78 email_account_id=:id ' using l_email_account_id;
79 l_folder_count:=x_folder_tbl.last;
80 LOOP
81 fetch arch_cur into l_arch_folder;
82 exit when arch_cur%notfound ;
83 if l_arch_folder is not null then
84 l_folder_count:=l_folder_count+1;
85 x_folder_tbl.extend;
86 x_folder_tbl(l_folder_count):=l_arch_folder;
87 end if;
88 END LOOP;
89 EXCEPTION WHEN OTHERS THEN
90 null;
91 END;
92 for i in x_folder_Tbl.first..x_folder_tbl.last LOOP
93 /* Check for ack account . We are only required to record for Sent folder of Ack account */
94 IF (upper(v1.email_user)='ACKNOWLEDGEMENTS') and x_folder_tbl(i) in ('Resolved','Deleted','Admin','Retry') then
95 l_ack_flag:=1;
96 END IF;
97 if l_ack_flag=0 then
98 l_status_text:=null;
99 l_status:='S';
100 if x_folder_tbl(i) in ('Inbox','Drafts') then
101 l_folder_name:=x_folder_tbl(i);
102 l_folder_type:= substr(x_folder_tbl(i),1,1);
103 l_status_text:='Succesfully Count for Folder ';
104 l_status:='S';
105 for v3 in c_agent LOOP
106 if l_folder_type='I' then
107 select count(*) into l_total_count
108 from iem_post_mdts where email_account_id=l_email_account_id and agent_id=v3.resource_id ;
109 else
110 select count(a.rt_media_item_id)
111 into l_total_count
112 from iem_rt_media_items a, iem_msg_parts part
113 where a.rt_interaction_id in (select rtm.rt_interaction_id
114 from iem_rt_media_items rtm, iem_rt_interactions rti
115 where rtm.message_id in (select message_id from iem_post_mdts
116 where email_account_id = l_email_account_id and agent_id = v3.resource_id)
117 and rtm.email_type ='I' and rtm.rt_interaction_id = rti.rt_interaction_id
118 and rti.expire = 'N') and a.email_type = 'O' and a.folder_uid > 0
119 and a.folder_name = 'Drafts' and part.ref_key = a.rt_media_item_id
120 and part.part_type = 'HEADERS' and part.delete_flag <> 'Y';
121 end if;
122 select IEM_MIGRATION_DETAILS_S1.nextval into l_mig_id from dual;
123 insert into IEM_MIGRATION_DETAILS
124 (migration_id,
125 agent_account_id,
126 email_account_id,
127 folder_name,
128 folder_type,
129 total_msg_count,
130 status,
131 status_text,
132 CREATED_BY ,
133 CREATION_DATE ,
134 LAST_UPDATED_BY ,
135 LAST_UPDATE_DATE ,
136 LAST_UPDATE_LOGIN )
137
138 values
139
140 (l_mig_id,v3.agent_Account_id,l_email_account_id,l_folder_name,l_folder_type,l_total_count,
141 l_status,l_status_text,l_created_by,sysdate,l_last_updated_by,sysdate,l_last_update_login);
142 END LOOP;
143 else
144 if ((x_folder_tbl(i) in ('Resolved','Sent','Deleted','Admin','Retry'))
145 OR (x_folder_tbl(i) like 'Arch%')) then
146 l_folder_name:=x_folder_tbl(i);
147 if x_folder_tbl(i) in ('Admin','Retry') then
148 l_folder_type:='N';
149 else
150 l_folder_type:='H';
151 end if;
152 if l_auth=0 then -- succesfully authenticated
153 G_FOLDER:='/'||x_folder_tbl(i);
154 l_total_count:=0;
155 l_ret:=iem_im_wrappers_pvt.openfolder(G_FOLDER,G_IM_LINK,l_msg_table);
156 if l_ret=0 then -- openfolder return no error
157 l_total_count:=l_msg_table.count;
158 l_folder_name:=x_folder_tbl(i);
159 l_status_text:='Succesfully Count for Folder ';
160 l_status:='S';
161 else
162 l_status_text:=' Open Folder Error for Folder '||x_folder_tbl(i)||'Error Code is '||l_ret ;
163 l_status:='E';
164 end if;
165 else
166 l_status_text:=' Error in Authentication '||' Error Code Is '||l_auth|| ' Can not retrieve Folder Count ';
167 l_status:='E';
168 end if;
169 else -- Classification Folders
170 l_class_id:=substr(x_folder_tbl(i),instr(x_folder_tbl(i),',',1)+1);
171 l_folder_name:=substr(x_folder_tbl(i),1,instr(x_folder_tbl(i),',',1)-1);
172 l_folder_type:='Q';
173 select count(*) into l_total_count
174 from iem_post_mdts where email_account_id=l_email_account_id
175 and rt_classification_id=l_class_id and agent_id=0;
176 l_status_text:='Succesfully Count for Folder ';
177 l_status:='S';
178 end if;
179 select IEM_MIGRATION_DETAILS_S1.nextval into l_mig_id from dual;
180 insert into IEM_MIGRATION_DETAILS
181 (migration_id,
182 email_account_id,
183 folder_name,
184 folder_type,
185 total_msg_count,
186 status,
187 status_text,
188 CREATED_BY ,
189 CREATION_DATE ,
190 LAST_UPDATED_BY ,
191 LAST_UPDATE_DATE ,
192 LAST_UPDATE_LOGIN )
193
194 values
195
196 (l_mig_id,l_email_account_id,l_folder_name,l_folder_type,l_total_count,l_status,l_status_text,l_created_by,
197 sysdate,l_last_updated_by,sysdate,l_last_update_login);
198 END IF; -- End if for all folders type
199 END IF; -- end if for ack flag..
200 l_ack_flag:=0;
201 END LOOP;
202 END LOOP; -- Account LOOP
203 x_status:='S';
204 EXCEPTION WHEN OTHERS THEN
205 if g_exception_log then
206 l_logmessage:='Oracle Error Encountered during Building Folder Counts '||sqlerrm;
207 iem_logger(l_logmessage);
208 x_Status:='E';
209 end if;
210 end build_migration_queue;
211
212 procedure start_postprocessing(p_migration_id in number, x_Status out nocopy varchar2) IS
213 cursor c1 is select * from iem_migration_store_temp
214 where migration_id=p_migration_id and mig_status='R' and dp_status='D';
215 l_media_id number;
216 l_contact_id number;
217 l_resource_id number;
218 l_relationship_id number;
219 l_party_id number;
220 l_CREATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID'));
221 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
222 l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
223 l_proc_status varchar2(100);
224 l_mail_type number;
225 l_received_Date date;
226 l_folder_type varchar2(10);
227 l_priority number;
228 l_post_rec iem_post_mdts%rowtype;
229 l_ret_status varchar2(10);
230 l_msg_count number;
231 l_msg_Data varchar2(250);
232 l_message_id number;
233 l_mig_status varchar2(1);
234 l_error_text varchar2(1000);
235 Type get_data is REF CURSOR;
236 ih_cur get_data;
237 begin
238 select folder_type into l_folder_type from iem_migration_details
239 where migration_id=p_migration_id;
240 if l_folder_type='H' then
241 for v1 in c1 LOOP
242 BEGIN
243 l_mail_type:=0; -- This is by default and will change based on folder name
244 if upper(v1.folder_name) like '%RESOLVED%' then
245 l_proc_status:='R';
246 elsif upper(v1.folder_name) like '%DELETE%' then
247 l_proc_status:='D';
248 else
249 l_proc_status:='S';
250 l_mail_type:=1;
251 end if;
252 -- Retrieve Resouce Party etc from IH
253 l_media_id:=v1.ih_media_item_id;
254 select creation_date into l_received_Date from jtf_ih_media_items
255 where media_id=l_media_id;
256 l_received_date:=sysdate;
257 select iem_ms_base_headers_s1.nextval into l_message_id from dual;
258
259 insert into iem_arch_msgdtls
260 (MESSAGE_ID ,
261 EMAIL_ACCOUNT_ID ,
262 MAILPROC_STATUS ,
263 MAIL_TYPE,
264 FROM_STR,
265 REPLY_TO_STR,
266 TO_STR,
267 CC_STR,
268 BCC_STR,
269 SENT_DATE,
270 RECEIVED_DATE ,
271 SUBJECT,
272 RESOURCE_ID ,
273 MESSAGE_SIZE ,
274 IH_MEDIA_ITEM_ID,
275 CUSTOMER_ID,
276 CONTACT_ID,
277 RELATIONSHIP_ID ,
278 MESSAGE_TEXT,
279 CREATED_BY,
280 CREATION_DATE,
281 LAST_UPDATED_BY,
282 LAST_UPDATE_DATE ,
283 LAST_UPDATE_LOGIN)
284 values
285 ( l_message_id,
286 v1.email_account_id,
287 l_proc_status,
288 l_mail_type,
289 v1.from_str,
290 v1.reply_to_str,
291 v1.to_str,
292 v1.cc_str,
293 v1.bcc_str,
294 v1.sent_date,
295 l_received_date,
296 v1.subject,
297 l_resource_id,
298 v1.message_size,
299 l_media_id,
300 l_party_id,
301 l_contact_id,
302 l_relationship_id,
303 v1.message_text,
304 nvl(l_created_by,-1),
305 sysdate,
306 nvl(l_last_updated_by,-1),
307 sysdate,
308 l_last_update_login);
309
310 -- Insert into IEM_ARCH_MESSAGES
311 insert into iem_arch_msgs
312 (MESSAGE_ID,
313 MESSAGE_CONTENT,
314 CREATED_BY,
315 CREATION_DATE,
316 LAST_UPDATED_BY,
317 LAST_UPDATE_DATE,
318 LAST_UPDATE_LOGIN)
319 (select decode(message_id,message_id,l_message_id),message_content,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN from
320 iem_migration_Store_temp where migration_id=p_migration_id and message_id=v1.message_id);
321
322 -- Update Media Items with new message Id
323 update jtf_ih_media_items
324 set media_item_ref=l_message_id
325 where media_id=v1.ih_media_item_id;
326 update iem_migration_store_temp
327 set mig_Status='M'
328 where migration_id=p_migration_id and message_id=v1.message_id;
329 EXCEPTION
330 WHEN OTHERS THEN
331 l_mig_status:='E';
332 l_error_text:=sqlerrm;
333
334 delete from iem_arch_msgs where message_id=l_message_id;
335 delete from iem_arch_msgdtls where message_id=l_message_id;
336
337 update iem_migration_store_temp
338 set mig_Status='E',
339 error_text=l_error_text
340 where migration_id=p_migration_id and message_id=v1.message_id;
341 END;
342
343 END LOOP;
344 elsif l_folder_type in ('Q','I') then -- Queued/Acquired Message
345 for v1 in c1 LOOP
346 BEGIN
347 select * into l_post_rec from iem_post_mdts
348 where message_id=v1.message_id;
349 if l_post_rec.priority='High' then
350 l_priority:=2;
351 elsif l_post_rec.priority='Low' then
352 l_priority:=0;
353 else
354 l_priority:=1;
355 end if;
356 IEM_RT_PROC_EMAILS_PVT.create_item (
357 p_api_version_number => 1.0,
358 p_init_msg_list=>'F' ,
359 p_commit=>'F',
360 p_message_id =>v1.message_id,
361 p_email_account_id =>v1.email_account_id,
362 p_priority =>l_priority,
363 p_agent_id =>l_post_rec.agent_id,
364 p_group_id =>l_post_rec.group_id,
365 p_sent_date =>v1.sent_date,
366 p_received_date =>l_post_rec.received_Date,
367 p_rt_classification_id =>l_post_rec.rt_classification_id,
368 p_customer_id=>l_party_id ,
369 p_contact_id=>l_contact_id ,
370 p_relationship_id=>l_relationship_id ,
371 p_interaction_id=>l_post_rec.ih_interaction_id ,
372 p_ih_media_item_id=>v1.ih_media_item_id ,
373 p_msg_status=>l_post_rec.msg_status ,
374 p_mail_proc_status=>'P' ,
375 p_mail_item_status=>l_post_rec.mail_item_status ,
376 p_category_map_id=>l_post_rec.category_map_id ,
377 p_rule_id=>l_post_rec.icenter_map_id,
378 p_subject=>v1.subject,
379 p_sender_address=>v1.from_str,
380 p_from_agent_id=>l_post_rec.from_agent_id,
381 x_return_status=>l_ret_status ,
382 x_msg_count=>l_msg_count ,
383 x_msg_data=>l_msg_data);
384 if l_ret_status='S' then
385 -- Update Message Flag
386 begin
387 if l_post_rec.message_flag is not null then
388 update iem_Rt_proc_emails
389 set message_flag=l_post_rec.message_flag
390 where message_id=l_post_rec.message_id;
391 end if;
392 exception when others then
393 null;
394 end;
395 -- Update Media Items with new message Id
396
397 update jtf_ih_media_items
398 set media_item_ref=v1.message_id
399 where media_id=v1.ih_media_item_id;
400 -- update MIG status to "M"
401 update iem_migration_store_temp
402 set mig_Status='M'
403 where migration_id=p_migration_id and message_id=v1.message_id;
404 end if;
405 EXCEPTION
406 WHEN OTHERS THEN
407 l_mig_status:='E';
408 l_error_text:=sqlerrm;
409 update iem_migration_Store_temp
410 set mig_Status=l_mig_status,
411 error_text=l_error_text
412 where migration_id=p_migration_id and message_id=v1.message_id;
413 END;
414 END LOOP;
415 end if;
416 update iem_migration_details
417 set folder_status='M'
418 where migration_id=p_migration_id;
419 commit;
420 EXCEPTION WHEN OTHERS THEN -- Folder level Error During Post Processing
421 update iem_migration_details
422 set folder_status='M',
423 status='E',
424 STATUS_TEXT='Error Encountered During Post Processing '
425 where migration_id=p_migration_id;
426 commit;
427
428 end start_postprocessing;
429 procedure create_worklist(p_migration_id in number,x_status out nocopy varchar2) IS
430 l_email_account_id number;
431 l_folder_name varchar2(128);
432 l_folder_type varchar2(10);
433 l_mig_rec iem_migration_store_temp%rowtype;
434 l_error_text varchar2(500);
435 cursor c_queue is
436 select a.* from iem_post_mdts a,iem_route_classifications b
437 where a.email_account_id=l_email_account_id and a.agent_id=0
438 and a.rt_classification_id=b.route_classification_id and
439 b.name=l_folder_name and a.message_id not in (select message_id from iem_migration_store_temp
440 where migration_id=p_migration_id)
441 union -- select records which are also errors out
442 select a.* from iem_post_mdts a,iem_route_classifications b
443 where a.email_account_id=l_email_account_id and a.agent_id=0
444 and a.rt_classification_id=b.route_classification_id and
445 b.name=l_folder_name and a.message_id in (select message_id from iem_migration_store_temp
446 where migration_id=p_migration_id and mig_status='E' and dp_status is null);
447 l_dblink varchar2(500);
448 l_user varchar2(500);
449 l_domain varchar2(500);
450 l_pass varchar2(100);
451 l_rec_counter number:=0;
452 l_str varchar2(1000);
453 l_msg_table iem_im_wrappers_pvt.msg_table;
454 l_folder_count number:=0;
455 l_folder varchar2(255);
456 l_uid number;
457 l_received_date date;
458 x_priority number;
459 l_read number;
460 l_expiration date;
461 l_ret number;
462 l_CREATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID'));
463 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
464 l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
465 l_hist_date date;
466 l_hist_count number;
467 l_mig_status varchar2(1);
468 l_content blob;
469 l_agent_account_id number;
470 INSERT_ERROR EXCEPTION;
471 OTHER_ERROR EXCEPTION;
472 AUTH_ERROR EXCEPTION;
473 OPEN_FOLDER_ERROR EXCEPTION;
474 cursor c_historical is
475 select message_id from iem_migration_store_temp
476 where migration_id=p_migration_id and
477 (mig_status='E' and DP_STATUS is null);
478 cursor c_normal is -- For Admin and Retry folder
479 select message_id from iem_migration_store_temp
480 where migration_id=p_migration_id and
481 (mig_status='E' and DP_STATUS is null);
482 l_ret_status varchar2(10);
483 l_msg_count number;
484 l_msg_data varchar2(100);
485 l_total_msg_count number;
486 INQ_EXCEPTION EXCEPTION;
487 UID_EXCEPTION EXCEPTION;
488 l_hist_flag varchar2(10);
489 l_inb_migration_id number;
490 l_outb_migration_id number;
491 l_type varchar2(10);
492 l_ag_count number;
493 l_qcount number;
494 l_disc_count number;
495 l_mig_id number;
496 l_source_message_id number;
497 l_error_counter number;
498 l_rerun varchar2(10);
499 cursor c_discp is
500 select mail_id,folder_name,email_account_id from iem_migration_store_temp
501 where migration_id=p_migration_id and substr(folder_name,1,1)<>'I';
502 begin
503 select email_account_id,folder_name,folder_type,agent_Account_id,total_msg_count into
504 l_email_account_id,l_folder_name,l_folder_type,l_agent_account_id,l_total_msg_count
505 from iem_migration_details
506 where migration_id=p_migration_id;
507 if l_folder_type='Q' THEN
508 BEGIN
509 select a.db_link into l_dblink
510 from iem_db_connections a, iem_email_accounts b
511 where a.db_server_id=b.db_Server_id
512 and b.email_account_id = l_email_account_id and a.is_admin='A';
513 -- Check normal processing or error Processing
514 select count(*) into l_qcount from iem_migration_store_temp
515 where migration_id=p_migration_id;
516 IF l_qcount>0 then -- Error Processing
517 delete from iem_migration_store_temp
518 where migration_id=p_migration_id and mig_status=null;
519 end if;
520 for v1 in c_queue LOOP
521 l_mig_Rec:=null;
522 l_mig_Rec.msg_uid:=v1.message_uid;
523 l_mig_rec.RFC822_MESSAGE_ID:=v1.rfc822_message_id;
524 l_mig_status:='R';
525 BEGIN
526 l_source_message_id:=v1.source_message_id;
527 execute immediate 'select reply_to, cc_str, bcc_str from '
528 ||'OM_HEADER@'||l_dblink||' where msg_id = :b1'
529 into l_mig_rec.reply_to_str,l_mig_rec.cc_str, l_mig_rec.bcc_str using l_source_message_id;
530 EXCEPTION when others then
531 l_mig_status:='E';
532 l_error_text:='Error in Retrieving Data from OES';
533 END;
534 l_mig_rec.sent_date:=v1.sent_date;
535 l_mig_rec.subject:=v1.subject;
536 l_mig_rec.message_id:=v1.message_id;
537 l_mig_rec.ih_media_item_id:=v1.ih_media_item_id;
538 l_mig_rec.to_str:=v1.to_address;
539 l_mig_rec.from_str:=v1.sender_name;
540 l_mig_rec.message_size:=v1.message_size;
541
542 -- Create Record into Worklist Queue.
543 select IEM_MIGRATION_STORE_TEMP_s1.nextval into l_mig_rec.mail_id from dual;
544 insert into iem_migration_store_temp
545 (MAIL_ID,
546 MESSAGE_ID ,
547 MIGRATION_ID,
548 MESSAGE_TYPE ,
549 EMAIL_ACCOUNT_ID ,
550 AGENT_ACCOUNT_ID ,
551 RESOURCE_ID ,
552 FOLDER_NAME ,
553 RT_MEDIA_ITEM_ID ,
554 MSG_UID ,
555 RFC822_MESSAGE_ID ,
556 FROM_STR ,
557 TO_STR ,
558 REPLY_TO_STR ,
559 CC_STR ,
560 BCC_STR ,
561 SENT_DATE ,
562 SUBJECT ,
563 IH_MEDIA_ITEM_ID ,
564 MESSAGE_SIZE ,
565 DP_STATUS ,
566 MIG_STATUS ,
567 ERROR_TEXT,
568 CREATED_BY ,
569 CREATION_DATE ,
570 LAST_UPDATED_BY ,
571 LAST_UPDATE_DATE ,
572 LAST_UPDATE_LOGIN )
573
574 VALUES
575 (l_mig_rec.mail_id,
576 l_mig_rec.message_id,
577 p_migration_id,
578 l_folder_type,
579 l_email_account_id,
580 l_mig_rec.agent_account_id,
581 l_mig_rec.resource_id,
582 l_folder_name,
583 null,
584 l_mig_rec.msg_uid,
585 l_mig_rec.RFC822_MESSAGE_ID,
586 l_mig_rec.from_Str,
587 l_mig_rec.to_str,
588 l_mig_rec.reply_to_str,
589 l_mig_rec.cc_str,
590 l_mig_rec.bcc_Str,
591 l_mig_rec.sent_Date,
592 l_mig_rec.subject,
593 l_mig_rec.ih_media_item_id,
594 l_mig_rec.MESSAGE_SIZE,
595 null,
596 l_mig_status,
597 l_error_text,
598 l_created_by,
599 sysdate,
600 l_last_updated_by,
601 sysdate,
602 l_last_update_login);
603 END LOOP;
604 select count(*) into l_rec_counter from iem_migration_Store_temp
605 where migration_id=p_migration_id ;
606 update iem_migration_details
607 set MSG_RECORD_COUNT=l_rec_counter
608 where migration_id=p_migration_id;
609 EXCEPTION WHEN OTHERS THEN
610 raise INQ_EXCEPTION;
611 END;
612 update iem_migration_details
613 set folder_status='R',
614 status='S'
615 where migration_id=p_migration_id;
616 elsif l_folder_type in ('I','D') THEN
617 l_inb_migration_id:=p_migration_id;
618 l_outb_migration_id:=null;
619 l_type:='I';
620 if l_folder_type='D' then
621 select migration_id into l_inb_migration_id from iem_migration_details
622 where email_account_id=l_email_Account_id and agent_account_id=l_agent_account_id
623 and folder_name='Inbox';
624 l_outb_migration_id:=p_migration_id;
625 l_type:='O';
626 end if;
627 -- Also check is this first run or retry run
628 select count(*) into l_ag_count from iem_migration_store_temp where migration_id=p_migration_id;
629 if l_ag_count>0 then
630 l_rerun:='Y';
631 else
632 l_rerun:='N';
633 end if;
634
635 -- Call Message Inbox api from Ting
636 iem_mginbox_pub.RUNINBOX(
637 P_API_VERSION_NUMBER=>1.0,
638 P_INIT_MSG_LIST=>'F',
639 P_COMMIT=>'T',
640 P_EMAIL_ACCOUNT_ID=>l_email_account_id,
641 P_AGENT_ACCOUNT_ID=>l_agent_account_id,
642 P_INB_MIGRATION_ID=>l_inb_migration_id,
643 P_OUTB_MIGRATION_ID=>l_outb_migration_id,
644 p_type=>l_type,
645 p_rerun=>l_rerun,
646 X_RETURN_STATUS=>l_ret_status,
647 x_msg_count=>l_msg_count,
648 x_msg_data=>l_msg_data);
649 if l_ret_Status='S' then
650 -- Fixed Data discrepancy
651 if l_folder_type='I' and l_rerun='N' then -- fixed discrepancy for first time
652 select count(*) into l_disc_count from iem_migration_Store_temp
653 where migration_id=p_migration_id and substr(folder_name,1,1) not in ('I');
654 IF l_disc_count >0 then -- There are discrepancy
655 for v1 in c_discp LOOP
656 select migration_id into l_mig_id from iem_migration_details
657 where email_account_id=v1.email_account_id and folder_name=v1.folder_name;
658 update iem_migration_store_temp
659 set message_type='Q',
660 migration_id=l_mig_id
661 where mail_id=v1.mail_id;
662 -- Update Count of Migration after pushing these inbox message into Queue Count
663 update iem_migration_details
664 set total_msg_count=nvl(total_msg_count,0)+1,
665 msg_record_count=nvl(msg_record_count,0)+1
666 where migration_id=l_mig_id;
667 END LOOP;
668 END IF;
669 end if ;
670 select count(*) into l_rec_counter from iem_migration_store_temp
671 where migration_id=p_migration_id ;
672 -- Find the error message only at the recording phase
673 select count(*) into l_error_counter from iem_migration_store_temp
674 where migration_id=p_migration_id and mig_status='E' and dp_status is null;
675 update iem_migration_details
676 set MSG_RECORD_COUNT=l_rec_counter-l_error_counter,
677 total_msg_count=l_rec_counter,
678 folder_status='R'
679 where migration_id=p_migration_id;
680 else -- l_ret_status<>'S' from Inbox creation api
681 update iem_migration_details
682 set folder_Status='R',
683 status='E',
684 status_text='Error While Creating Worklist Items'
685 where migration_id=p_migration_id;
686 end if;
687 elsif l_folder_type in ('H','N') THEN
688 select to_date(value,'YYYY/MM/DD HH24:MI:SS') into l_hist_date from
689 iem_comp_rt_Stats where type='HISTORICAL' and param='LASTRUN' ;
690 select a.email_user,a.domain,a.email_password,'@'||DB_LINK
691 into l_user,l_domain,l_pass,l_dblink
692 from iem_email_accounts a,iem_db_connections b
693 where a.email_account_id=l_email_account_id
694 and a.db_server_id=b.db_server_id
695 and b.is_admin='A';
696 l_mig_status:=null;
697 l_str:='begin :l_ret:=im_api.authenticate'||l_dblink||'(:a_user,:a_domain,:a_password);end; ';
698 EXECUTE IMMEDIATE l_str using OUT l_ret,l_user,l_domain,l_pass;
699 if l_ret<>0 then
700 update iem_migration_details
701 set status='E',
702 folder_status=null,
703 STATUS_TEXT='Unable to Authenticate USer '||l_user||' Error Code '||l_ret
704 where migration_id=p_migration_id;
705 x_Status:='E';
706 raise AUTH_ERROR;
707 end if;
708 -- Check whether this is processing the Error entry or these are processed for the first time
709 select count(*) into l_hist_count from iem_migration_store_temp
710 where migration_id=p_migration_id;
711
712 l_folder:='/'||l_folder_name;
713 IF l_hist_count=0 then -- Historical Records are created for first time
714
715 l_ret:=iem_im_wrappers_pvt.openfolder(l_folder,l_dblink,l_msg_table);
716 if l_ret<>0 then
717 update iem_migration_details
718 set status='E',
719 folder_status=null,
720 STATUS_TEXT='Unable to Open Folder'||l_folder||' Error Code '||l_ret
721 where migration_id=p_migration_id;
722 x_Status:='E';
723 raise OPEN_FOLDER_ERROR;
724 if l_folder_type='N' then
725 l_mig_status:='R'; -- Default value for Normal Message
726 end if;
727 end if;
728 else
729 l_hist_flag:='O'; -- that means running second time for historical folder
730 l_msg_table.delete;
731 if l_folder_type='H' then
732 l_mig_status:=null; -- Update it to "R" later based on date
733 open c_historical;
734 fetch c_historical bulk collect into l_msg_table;
735 close c_historical;
736 else -- For Admin/Retry Folder
737 l_mig_status:='R'; -- This is the default value
738 open c_normal;
739 fetch c_normal bulk collect into l_msg_Table;
740 close c_normal;
741 end if;
742
743 end if;
744 IF l_msg_table.count>0 THEN
745 for i in l_msg_table.first..l_msg_table.last LOOP
746 l_mig_Rec:=null;
747
748 /* Retrieve below information for only Historical messages */
749 IF l_folder_type='H' then
750 BEGIN
751 l_source_message_id:=l_msg_table(i);
752 execute immediate 'select subject,sent_date,to_str,from_str,reply_to, cc_str, bcc_str,msg_size from '
753 ||'OM_HEADER'||l_dblink||' where msg_id = :b1'
754 into l_mig_rec.subject,l_mig_rec.sent_date,l_mig_rec.to_str,l_mig_Rec.from_str,
755 l_mig_rec.reply_to_str, l_mig_rec.cc_str, l_mig_rec.bcc_str,l_mig_rec.message_size using l_source_message_id;
756 EXCEPTION WHEN OTHERS THEN
757 l_error_text:='Oracle Error Occured while selecting Header Information '||sqlerrm;
758 l_mig_Status:='E';
759 END;
760
761 -- Retrieve RFC822_MESSAGE_ID
762 BEGIN
763 l_source_message_id:=l_msg_table(i);
764 -- modified the query to make it case insensitive Ranjan 07/16/2008
765 execute immediate ' select value from om_ext_header'||l_dblink||' where msg_id =:b1 '||' AND upper(prompt)=''MESSAGE-ID:'' AND eh_type IN (80, 0)' into l_mig_rec.rfc822_message_id using l_source_message_id;
766 EXCEPTION WHEN OTHERS THEN
767 l_error_text:='Oracle Error Occured while selecting Extended Header Information '||sqlerrm;
768 l_mig_Status:='E';
769 END;
770 BEGIN
771 select media_id into l_mig_rec.ih_media_item_id
772 from jtf_ih_media_items
773 where media_item_type = 'EMAIL'
774 AND media_item_ref=l_mig_rec.rfc822_message_id
775 AND source_id=l_email_account_id;
776 EXCEPTION WHEN OTHERS THEN
777 l_error_text:='Error Encountered while selecting Media Info '||sqlerrm;
778 l_mig_Status:='E';
779 END;
780
781 END IF; -- end if for if folder_type='H'
782
783 -- Get MEssage UID for this Folder
784 BEGIN
785 l_str:='begin :l_ret:=im_api.getmessageprops'||l_dblink||'(:a_message,:a_folder,:a_uid,:a_priority,:a_received_date,:a_expiration,:a_read);end; ';
786 execute immediate l_str using out l_ret,l_msg_table(i),l_folder,out l_uid,out x_priority,out l_received_date,out l_expiration,out l_read;
787 if l_ret<>0 then
788 raise UID_EXCEPTION;
789 end if;
790 l_mig_rec.msg_uid:=l_uid;
791 EXCEPTION WHEN UID_EXCEPTION THEN
792 l_error_text:='Error Encountered while retrieving Message UID and error code is '||l_ret;
793 l_mig_Status:='E';
794
795 WHEN OTHERS THEN
796 l_error_text:='Oracle Error Encountered while retrieving Message UID and error is '||sqlerrm;
797 l_mig_Status:='E';
798 END;
799 if l_hist_flag='O' then -- To avoid duplicate
800 delete from iem_migration_Store_temp where migration_id=p_migration_id and message_id=l_msg_table(i);
801 end if;
802 l_mig_Rec.message_id:=l_msg_table(i);
803 select IEM_MIGRATION_STORE_TEMP_s1.nextval into l_mig_rec.mail_id from dual;
804 l_mig_rec.folder_name:=l_folder_name;
805 l_mig_rec.message_type:=l_folder_type;
806 l_mig_rec.email_account_id:=l_email_account_id;
807 l_content:=empty_blob();
808 insert into iem_migration_store_temp
809 (MAIL_ID,
810 MESSAGE_ID ,
811 MIGRATION_ID,
812 MESSAGE_TYPE ,
813 EMAIL_ACCOUNT_ID ,
814 AGENT_ACCOUNT_ID ,
815 RESOURCE_ID ,
816 FOLDER_NAME ,
817 RT_MEDIA_ITEM_ID ,
818 MSG_UID ,
819 RFC822_MESSAGE_ID ,
820 FROM_STR ,
821 TO_STR ,
822 REPLY_TO_STR ,
823 CC_STR ,
824 BCC_STR ,
825 SENT_DATE ,
826 SUBJECT ,
827 IH_MEDIA_ITEM_ID ,
828 MESSAGE_SIZE ,
829 DP_STATUS ,
830 MIG_STATUS ,
831 ERROR_TEXT,
832 message_content,
833 CREATED_BY ,
834 CREATION_DATE ,
835 LAST_UPDATED_BY ,
836 LAST_UPDATE_DATE ,
837 LAST_UPDATE_LOGIN
838 )
839 VALUES
840 (l_mig_rec.mail_id,
841 l_mig_rec.message_id,
842 p_migration_id,
843 l_folder_type,
844 l_mig_rec.email_account_id,
845 l_mig_rec.agent_account_id,
846 l_mig_rec.resource_id,
847 l_folder_name,
848 null,
849 l_mig_rec.msg_uid,
850 l_mig_rec.RFC822_MESSAGE_ID,
851 l_mig_rec.from_Str,
852 l_mig_rec.to_str,
853 l_mig_rec.reply_to_str,
854 l_mig_rec.cc_str,
855 l_mig_rec.bcc_Str,
856 l_mig_rec.sent_Date,
857 l_mig_rec.subject,
858 l_mig_rec.ih_media_item_id,
859 l_mig_rec.MESSAGE_SIZE,
860 null,
861 l_mig_status,
862 l_error_text,
863 l_content,
864 l_created_by,
865 sysdate,
866 l_last_updated_by,
867 sysdate,
868 l_last_update_login);
869 end loop;
870 END IF; -- End if for l_msg_tabl.count>0
871 if l_folder_type='H' then
872 -- Mark MIG_STAUS to "R" for messages that have sent_date < Historical message Date
873 update iem_migration_store_temp
874 set mig_status='R'
875 where mig_status is null and
876 sent_date>=l_hist_date;
877 end if;
878 select count(*) into l_rec_counter from iem_migration_Store_temp
879 where migration_id=p_migration_id and mig_Status is not null;
880 -- Find the error message only at the recording phase
881 select count(*) into l_error_counter from iem_migration_store_temp
882 where migration_id=p_migration_id and mig_status='E' and dp_status is null;
883 update iem_migration_details
884 set MSG_RECORD_COUNT=l_rec_counter-l_error_counter,
885 folder_status='R',
886 status='S'
887 where migration_id=p_migration_id;
888 end if; -- End if for message type in 'H'/'N'
889 x_status:='S';
890 commit;
891 EXCEPTION
892 WHEN INQ_EXCEPTION THEN
893 rollback;
894 l_error_text:='Oracle Error Occured During In queue Processing '||sqlerrm;
895 update iem_migration_details
896 set status='E',
897 folder_Status=null,
898 status_text=l_error_text
899 where migration_id=p_migration_id;
900 commit;
901
902 WHEN AUTH_ERROR THEN
903 commit;
904 WHEN OPEN_FOLDER_ERROR THEN
905 commit;
906 WHEN OTHERS THEN
907 update iem_migration_details
908 set status='E',
909 folder_Status=null,
910 STATUS_TEXT='Oracle Error occured During Worklist item creation '
911 where migration_id=p_migration_id;
912 commit;
913 end create_worklist;
914
915
916 PROCEDURE retry_folders(p_api_version_number IN NUMBER,
917 p_init_msg_list IN VARCHAR2 ,
918 p_commit IN VARCHAR2 ,
919 p_folders IN jtf_number_table,
920 x_return_status OUT NOCOPY VARCHAR2,
921 x_msg_count OUT NOCOPY NUMBER,
922 x_msg_data OUT NOCOPY VARCHAR2) IS
923
924 l_folder_status varchar2(10);
925 l_new_Status varchar2(10);
926 l_mig_Status varchar2(10);
927 l_id number;
928
929 cursor c1 is select * from iem_migration_store_temp
930 where migration_id=l_id and (dp_status='E' or mig_Status='E');
931 l_mig_count number;
932 cursor c_account is select email_account_id from iem_mstemail_accounts
933 where active_flag in ('Y','N') ;
934 begin
935
936 for i in p_folders.first..p_folders.last LOOP
937 select folder_Status into l_folder_status
938 from iem_migration_details
939 where migration_id=p_folders(i);
940 l_id:=p_folders(i);
941 if l_folder_Status in ('R','P') then
942 l_new_status:=null;
943 l_mig_status:=null;
944 elsif l_folder_status in ('D','U') then
945 l_new_status:='R';
946 l_mig_status:='R';
947 elsif l_folder_Status in ('V','M') then
948 l_new_status:='D';
949 l_mig_status:='D';
950 end if;
951 update iem_migration_details
952 set folder_Status=l_new_status,
953 status='S',
954 status_text=null
955 where migration_id=p_folders(i);
956 -- Update Status at message levels
957 for v1 in c1 LOOP
958 if v1.dp_status='E' then
959 update iem_migration_store_temp
960 set dp_status=null,
961 error_text=null
962 where mail_id=v1.mail_id;
963 elsif v1.mig_status='E' THEN
964 update iem_migration_store_temp
965 set mig_status=l_mig_status,
966 error_text=null
967 where mail_id=v1.mail_id;
968 end if;
969 END LOOP;
970 end loop;
971 x_return_status:='S';
972 -- Reset account flag to Migrated mode
973 for v1 in c_account LOOP
974 select count(*) into l_mig_count from iem_migration_details
975 where email_account_id=v1.email_account_id and nvl(folder_status,' ')<>'M'
976 and folder_type<>'H';
977 IF l_mig_count>0 then
978 update iem_mstemail_accounts
979 set active_flag='M'
980 where email_account_id=v1.email_Account_id;
981 END IF;
982 END LOOP;
983 commit;
984 EXCEPTION WHEN OTHERS THEN
985 x_return_status:='E';
986 end retry_folders;
987 PROCEDURE retry_messages(p_api_version_number IN NUMBER,
988 p_init_msg_list IN VARCHAR2 ,
989 p_commit IN VARCHAR2 ,
990 p_messages IN jtf_number_table,
991 x_return_status OUT NOCOPY VARCHAR2,
992 x_msg_count OUT NOCOPY NUMBER,
993 x_msg_data OUT NOCOPY VARCHAR2) IS
994 l_mig_status varchar2(10);
995 l_mignew_status varchar2(10);
996 l_dp_status varchar2(10);
997 l_folder_status varchar2(10);
998 l_mig_id number;
999 l_mig_count number;
1000 cursor c_account is select email_account_id from iem_mstemail_accounts
1001 where active_flag in ('Y','N') ;
1002 begin
1003 for i in p_messages.first..p_messages.last LOOP
1004 select migration_id,mig_status,dp_status into l_mig_id,l_mig_status,l_dp_status
1005 from iem_migration_store_temp
1006 where mail_id=p_messages(i);
1007 if l_dp_status='E' THEN
1008 update iem_migration_store_temp
1009 set dp_status=null,
1010 error_text=null
1011 where mail_id=p_messages(i);
1012 update iem_migration_details
1013 set folder_Status=null,
1014 status=null,
1015 status_text=null
1016 where migration_id=l_mig_id;
1017 elsif l_mig_status='E' THEN
1018 if l_dp_Status is not null then -- Post processing DRP
1019 l_mignew_Status:='R';
1020 l_folder_status:='D';
1021 else
1022 l_mignew_Status:=null; -- Pre Processing DRP
1023 l_folder_status:=null;
1024 end if;
1025
1026 update iem_migration_store_temp
1027 set mig_status=l_mignew_status
1028 where mail_id=p_messages(i);
1029 update iem_migration_details
1030 set folder_Status=null,
1031 status=null,
1032 status_text=null
1033 where migration_id=l_mig_id;
1034
1035 end if;
1036 end loop;
1037 -- Reset account flag to Migrated mode
1038 for v1 in c_account LOOP
1039 select count(*) into l_mig_count from iem_migration_details
1040 where email_account_id=v1.email_account_id and nvl(folder_status,' ')<>'M'
1041 and folder_type<>'H';
1042 IF l_mig_count>0 then
1043 update iem_mstemail_accounts
1044 set active_flag='M'
1045 where email_account_id=v1.email_Account_id;
1046 END IF;
1047 END LOOP;
1048 x_return_status:='S';
1049 commit;
1050 end retry_messages;
1051 PROCEDURE StartMigration(ERRBUF OUT NOCOPY VARCHAR2,
1052 RETCODE OUT NOCOPY VARCHAR2,
1053 p_hist_date in VARCHAR2,
1054 p_number_of_threads in NUMBER) IS
1055 l_stat varchar2(10);
1056 l_buildstat varchar2(10);
1057 l_count number;
1058 l_request_id number;
1059 l_msg_data varchar2(200);
1060 l_call_status boolean;
1061 l_error_message varchar2(1000);
1062 l_value varchar2(10);
1063 l_hist_Date date;
1064 l_mig_count number;
1065 l_id number;
1066 l_id1 number;
1067 WORKER_NOT_SUBMITTED EXCEPTION;
1068 cursor c_account is select email_account_id from iem_mstemail_accounts
1069 where active_flag in ('Y','N') ;
1070 begin
1071 SAVEPOINT start_migration;
1072 -- Create a record in IEM_COMP_RT_STATS
1073 select count(*) into l_count from iem_comp_rt_stats
1074 where type='MIGRATION' and param='STATUS' ;
1075 if l_count=0 then -- First Run
1076 -- Check Migration Pre requisite Condition like OP queue is null and pre processing Queue is null;
1077 select count(*) into l_mig_count from iem_pre_mdts;
1078 if l_mig_count>0 then
1079 l_error_message:='Please Clean up the Preprocessing Queue Before starting Migration ';
1080 raise WORKER_NOT_SUBMITTED;
1081 end if;
1082 select count(rt_interaction_id) into l_mig_count
1083 from iem_rt_interactions where expire <> 'N' AND expire <> 'Y';
1084 if l_mig_count>0 then
1085 l_error_message:='Please Clean up the Outbox Processing Queue Before starting Migration ';
1086 raise WORKER_NOT_SUBMITTED;
1087 end if;
1088
1089 IEM_COMP_RT_STATS_PVT.create_item(p_api_version_number =>1.0,
1090 p_init_msg_list => FND_API.G_FALSE,
1091 p_commit => FND_API.G_FALSE,
1092 p_type => 'MIGRATION',
1093 p_param => 'STATUS',
1094 p_value => 'Y', -- Start Migration
1095 x_return_status => l_stat,
1096 x_msg_count => l_count,
1097 x_msg_data => l_msg_data
1098 );
1099 if l_stat='S' then
1100 IEM_COMP_RT_STATS_PVT.create_item(p_api_version_number =>1.0,
1101 p_init_msg_list => FND_API.G_FALSE,
1102 p_commit => FND_API.G_FALSE,
1103 p_type => 'HISTORICAL',
1104 p_param => 'LASTRUN',
1105 p_value => p_hist_date, -- Start Migration
1106 x_return_status => l_stat,
1107 x_msg_count => l_count,
1108 x_msg_data => l_msg_data
1109 );
1110 if l_stat='S' then
1111 -- Migrate Config Data
1112 iem_migration_pvt.iem_config(l_stat);
1113 if l_stat<>'S' then
1114 l_error_message:='Error While Creating Configuration Data'||sqlerrm;
1115 raise WORKER_NOT_SUBMITTED;
1116 else
1117 IEM_MIGRATION_PVT.build_migration_queue(l_buildstat); --Build only Once
1118 end if;
1119 else
1120 l_error_message:='Error While Creating Config Historical Date Info in IEM_COMP_RT_STATS';
1121 raise WORKER_NOT_SUBMITTED;
1122 end if;
1123 if l_buildstat<>'S' then
1124 raise WORKER_NOT_SUBMITTED;
1125 else -- Mark folder as 'Migrated' if there are no messages
1126 update iem_migration_details
1127 set folder_Status='M' where total_msg_count=0 and status='S';
1128 -- Reset MEssage Id Sequence to the highest Post mdts message id
1129 -- fix by ranjan on 5th nov. use nvl where there is no
1130 -- record in iem_post_mdts.otherwise the loop will be never
1131 -- ending
1132 select nvl(max(message_id),0) into l_id from iem_post_mdts;
1133 LOOP
1134 select iem_ms_base_headers_s1.nextval into l_id1 from dual;
1135 exit when l_id1>l_id;
1136 END LOOP;
1137 end if;
1138 else
1139 l_error_message:='Error While Creating Config Data in IEM_COMP_RT_STATS';
1140 raise WORKER_NOT_SUBMITTED;
1141 end if;
1142 else
1143 update iem_comp_rt_Stats
1144 set value='Y' where type='MIGRATION' and param='STATUS' ;
1145 select to_date(value,'YYYY/MM/DD HH24:MI:SS') into l_hist_date from
1146 iem_comp_rt_Stats where type='HISTORICAL' and param='LASTRUN' ;
1147 if l_hist_date>to_date(p_hist_date ,'YYYY/MM/DD HH24:MI:SS') then
1148 update iem_comp_rt_Stats
1149 set value=p_hist_date where type='HISTORICAL' and param='LASTRUN' ;
1150 end if;
1151 -- Check if account have folders to migrated
1152 for v1 in c_account LOOP
1153 select count(*) into l_mig_count from iem_migration_details
1154 where email_account_id=v1.email_account_id and folder_status<>'M';
1155 IF l_mig_count>0 then
1156 update iem_mstemail_accounts
1157 set active_flag='M'
1158 where email_account_id=v1.email_Account_id;
1159 END IF;
1160 END LOOP;
1161 end if;
1162 FOR i in 1..p_number_of_threads loop
1163 l_request_id := fnd_request.submit_request('IEM', 'IEMMIGWW', '','',FALSE);
1164 if l_request_id = 0 then
1165 rollback;
1166 raise WORKER_NOT_SUBMITTED;
1167 end if;
1168 END LOOP;
1169 commit;
1170
1171 exception
1172 WHEN WORKER_NOT_SUBMITTED THEN
1173 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_WORKER_NOT_SUBMITTED');
1174 l_Error_Message := nvl(l_error_message,' ')||FND_MESSAGE.GET;
1175 fnd_file.put_line(fnd_file.log, l_Error_Message);
1176 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_Error_Message);
1177 rollback ;
1178
1179 WHEN OTHERS THEN
1180 l_error_message:='Oracle Error occured '||sqlerrm;
1181 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_WORKER_NOT_SUBMITTED');
1182 l_Error_Message := FND_MESSAGE.GET;
1183 fnd_file.put_line(fnd_file.log, l_Error_Message);
1184 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_Error_Message);
1185 rollback ;
1186
1187 end StartMigration;
1188
1189 PROCEDURE Start_worker(ERRBUF OUT NOCOPY VARCHAR2,
1190 RETCODE OUT NOCOPY VARCHAR2) IS
1191 l_folder_Rec iem_migration_details%rowtype;
1192 l_count number;
1193 l_status varchar2(10);
1194 l_migration_id number;
1195 e_nowait EXCEPTION;
1196 cursor c_dp_folder is
1197 select distinct b.migration_id from iem_migration_store_temp a,iem_migration_details b
1198 where a.migration_id=b.migration_id and b.folder_Status='M' and a.dp_status=null;
1199
1200 cursor c_mig_folder is
1201 select distinct migration_id,folder_Status from iem_migration_Details
1202 where migration_id in (select a.migration_id from iem_migration_store_temp a,iem_migration_details b
1203 where a.migration_id=b.migration_id and b.folder_Status='M' and a.mig_Status in (null,'D'));
1204 cursor c_account is select email_account_id from iem_mstemail_accounts
1205 where active_flag='M' ;
1206 l_mig_count number;
1207
1208 begin
1209 LOOP -- Loop For Worker which it will check after it has no folder to process
1210 select count(*) into l_count from iem_comp_rt_stats
1211 where type='MIGRATION' and param='STATUS' and value='Y';
1212 EXIT when l_count=0 ; -- Exit from Main Worker Loop
1213 -- Recording Phase loop. Will go to next loop after it record for all the folders
1214 LOOP
1215 select count(*) into l_count from iem_comp_rt_stats
1216 where type='MIGRATION' and param='STATUS' and value='Y';
1217 EXIT when l_count=0; -- Before start processing check migration status
1218 l_migration_id:=null;
1219 for x in ( select migration_id
1220 from iem_migration_details
1221 where folder_Status is null
1222 and total_msg_count>0
1223 order by decode(folder_type,'H',1,0))
1224 LOOP
1225 BEGIN
1226 select * into l_folder_rec from iem_migration_details
1227 where migration_id=x.migration_id FOR UPDATE NOWAIT;
1228 l_migration_id:=l_folder_rec.migration_id;
1229 exit;
1230 EXCEPTION when e_nowait then
1231 null;
1232 when others then
1233 null ;
1234 END;
1235 END LOOP;
1236 EXIT when l_migration_id is null; -- Exit from the Recording Loop
1237 update iem_migration_details
1238 set folder_status='P',
1239 status=null,
1240 status_Text=null
1241 where migration_id=l_migration_id;
1242 commit;
1243 -- Build WorkList Item For the Folders
1244 IEM_MIGRATION_PVT.create_worklist(l_migration_id,l_status);
1245 END LOOP; -- End Loop for all folders
1246
1247 -- PostProcessing Phase
1248 LOOP
1249 -- Before start processing check migration status
1250
1251 select count(*) into l_count from iem_comp_rt_stats
1252 where type='MIGRATION' and param='STATUS' and value='Y';
1253 EXIT when l_count=0;
1254 l_migration_id:=null;
1255 for x in ( select migration_id
1256 from iem_migration_details
1257 where folder_Status='D'
1258 order by decode(folder_type,'H',1,0))
1259 LOOP
1260 BEGIN
1261 select * into l_folder_rec from iem_migration_details
1262 where migration_id=x.migration_id FOR UPDATE NOWAIT;
1263 l_migration_id:=l_folder_rec.migration_id;
1264 exit;
1265 EXCEPTION when e_nowait then
1266 null;
1267 when others then
1268 null ;
1269 END;
1270 END LOOP;
1271 EXIT when l_migration_id is null ; -- Exit from the Postprocessing Loop
1272 if l_folder_rec.folder_type in ('N','D') then --just set the folder to migrated for Normal/Draft message no post processing
1273 update iem_migration_details
1274 set folder_status='M'
1275 where migration_id=l_migration_id;
1276 update iem_migration_Store_temp
1277 set mig_status='M'
1278 where migration_id=l_migration_id
1279 and dp_status='D';
1280 commit;
1281 else
1282 update iem_migration_details
1283 set folder_status='V'
1284 where migration_id=l_migration_id;
1285 commit;
1286 -- Build WorkList Item For the Folders
1287 IEM_MIGRATION_PVT.start_postprocessing(l_migration_id,l_status);
1288 end if;
1289 END LOOP; -- End Loop for all folders
1290 -- Check for all account to transfer them from Migrate mode to in active mode. WE make account
1291 --active even if it is doing historical email migration
1292 for v1 in c_account LOOP
1293 select count(*) into l_mig_count from iem_migration_details
1294 where email_account_id=v1.email_account_id and folder_status<>'M'
1295 and folder_type<>'H';
1296 IF l_mig_count=0 then
1297 update iem_mstemail_accounts
1298 set active_flag='N'
1299 where email_account_id=v1.email_Account_id;
1300 commit;
1301 END IF;
1302 END LOOP;
1303 END LOOP; -- End Loop for worker
1304 end Start_worker;
1305 PROCEDURE StopMigration(ERRBUF OUT NOCOPY VARCHAR2,
1306 RETCODE OUT NOCOPY VARCHAR2) IS
1307 BEGIN
1308 update iem_comp_rt_Stats
1309 set value='N' where type='MIGRATION' and param='STATUS' ;
1310 commit;
1311 END StopMigration;
1312 PROCEDURE iem_logger(l_logmessage in varchar2) IS
1313 begin
1314 if g_statement_log THEN
1315 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1316 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'IEM.PLSQL.IEM_EMAIL_PROC_PVT',l_logmessage);
1317 end if;
1318 end if;
1319 if g_exception_log then
1320 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1321 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'IEM.PLSQL.IEM_EMAIL_PROC_PVT',l_logmessage);
1322 end if;
1323 end if;
1324 if g_error_log then
1325 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1326 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'IEM.PLSQL.IEM_EMAIL_PROC_PVT',l_logmessage);
1327 end if;
1328 end if;
1329 end iem_logger;
1330
1331 PROCEDURE iem_config(x_Status OUT NOCOPY varchar2) IS
1332 l_email_account_id number;
1333 l_intent_id number;
1334 l_rule_id number;
1335 l_max_id number;
1336 l_val number;
1337 cursor c_account is select * from iem_email_accounts where email_account_id not in
1338 (select email_account_id from iem_mstemail_accounts);
1339 cursor c_agent is select * from iem_agent_Accounts where agent_account_id not in
1340 (select agent_id from iem_agents);
1341 cursor c_intent is
1342 select distinct a.classification_id,a.classification from
1343 iem_classifications a
1344 where a.email_account_id=l_email_account_id;
1345
1346 cursor c1 is select * from iem_themes where classification_id=l_intent_id
1347 and score>0;
1348
1349 l_template_profile number;
1350 l_sender_profile varchar2(100);
1351 l_in_host varchar2(256);
1352 l_out_host varchar2(256);
1353 l_in_port number;
1354 l_out_port number;
1355 l_mod NUMBER;
1356 l_CREATED_BY NUMBER;
1357 l_LAST_UPDATED_BY NUMBER ;
1358 l_LAST_UPDATE_LOGIN NUMBER;
1359 l_flag number;
1360 l_theme_enabled varchar2(10);
1361 l_acct_language varchar2(10);
1362 l_intent_dtl_id number;
1363 l_msg_count number;
1364 l_ret_status varchar2(10);
1365 l_msg_data varchar2(1000);
1366 l_deleted_flag varchar2(1);
1367 l_dblink iem_db_connections.db_link%type;
1368 l_weight number;
1369 l_sc_lang iem_mstemail_accounts.sc_lang%type;
1370 cursor c_rule is select email_user,domain from iem_email_accounts
1371 where upper(email_user) not in ('ACKNOWLEDGEMENTS');
1372 begin
1373 -- Migrate Email Account Config Data
1374 l_created_by:=nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1);
1375 l_LAST_UPDATED_BY:=nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1);
1376 l_last_update_login:=nvl(TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')),-1);
1377 for v1 in c_account LOOP
1378 l_email_Account_id:=v1.email_account_id;
1379 l_acct_language:=v1.acct_language;
1380 l_template_profile:=to_number(FND_PROFILE.VALUE_SPECIFIC('IEM_TEMPLATE_CATEGORY_ID')) ;
1381 select to_number(decode(FND_PROFILE.VALUE_SPECIFIC('IEM_ACCOUNT_SENDER_NAME'),'ACCOUNT',0,1)) into
1382 l_sender_profile from dual;
1383 select dns_name,port
1384 into l_in_host,l_in_port
1385 from iem_email_servers a,iem_email_server_types b
1386 where a.server_type_id=b.email_server_type_id and
1387 b.email_server_type='IMAP' and
1388 a.server_group_id=v1.server_group_id;
1389
1390 select dns_name,port
1391 into l_out_host,l_out_port
1392 from iem_email_servers a,iem_email_server_types b
1393 where a.server_type_id=b.email_server_type_id and
1394 b.email_server_type='SMTP' and
1395 a.server_group_id=v1.server_group_id;
1396 if l_dblink is null then -- not required to get the dblink repeatedly
1397 if upper(v1.email_user) not in ('ACKNOWLEDGEMENTS') then
1398 select a.db_link into l_dblink
1399 from iem_db_connections a, iem_email_accounts b
1400 where a.db_server_id=b.db_Server_id
1401 and b.email_account_id = l_email_account_id and a.is_admin='A';
1402 end if;
1403 end if;
1404 -- get the KEM flag /Intent Enabled etc ...for both 1159 and 11510
1405 IF v1.intent_enabled='Y' THEN
1406 BEGIN
1407 select kem_flag into l_flag from iem_email_Accounts
1408 where email_account_id=v1.email_Account_id;
1409 if l_flag is null then
1410 select decode(v1.acct_language,'GB',1,2) into l_flag from dual;
1411 end if;
1412
1413 EXCEPTION WHEN OTHERS THEN
1414 -- This is 1159 ..
1415 select decode(v1.acct_language,'GB',1,2) into l_flag from dual;
1416 END;
1417 ELSE
1418 l_flag:=0;
1419 END IF;
1420 if l_flag=1 then
1421 l_theme_enabled:='Y';
1422 else
1423 l_theme_enabled:='N';
1424 end if;
1425 if upper(v1.email_user) in ('INTENT','ACKNOWLEDGEMENTS') then
1426 l_deleted_flag:='Y';
1427 else
1428 l_deleted_flag:='N';
1429 end if;
1430 begin
1431 if v1.sc_lang is null then
1432 l_sc_lang:=FND_PROFILE.VALUE('IEM_SC_DEFAULT_LANG');
1433 else
1434 l_sc_lang:=v1.sc_lang;
1435 end if;
1436 exception when others then -- just incase the 1159 column is not present.
1437 l_sc_lang:=FND_PROFILE.VALUE('IEM_SC_DEFAULT_LANG');
1438 end;
1439 insert into iem_mstemail_accounts
1440 (EMAIL_ACCOUNT_ID,
1441 EMAIL_ADDRESS ,
1442 ACCOUNT_DESC,
1443 USER_NAME ,
1444 ACTIVE_FLAG ,
1445 DELETED_FLAG,
1446 TEMPLATE_CATEGORY,
1447 SENDER_FLAG,
1448 ACCOUNT_LANGUAGE ,
1449 REPLY_TO_ADDRESS,
1450 RETURN_ADDRESS,
1451 FROM_NAME ,
1452 IN_HOST ,
1453 OUT_HOST,
1454 IN_PORT ,
1455 OUT_PORT ,
1456 CUSTOM_ENABLED ,
1457 SC_LANG ,
1458 KEM_FLAG ,
1459 ACCOUNT_TYPE,
1460 CREATED_BY ,
1461 CREATION_DATE ,
1462 LAST_UPDATED_BY ,
1463 LAST_UPDATE_DATE ,
1464 LAST_UPDATE_LOGIN )
1465 VALUES
1466 (v1.email_Account_id,
1467 v1.reply_to_Address,
1468 v1.account_profile,
1469 v1.email_user,
1470 'M',
1471 l_deleted_flag,
1472 l_template_profile,
1473 l_sender_profile,
1474 v1.acct_language,
1475 v1.reply_to_address,
1476 v1.reply_to_address,
1477 v1.from_name,
1478 l_in_host,
1479 l_out_host,
1480 l_in_port,
1481 l_out_port,
1482 v1.custom_enabled,
1483 l_sc_lang,
1484 l_flag,
1485 'E',
1486 l_created_by,
1487 sysdate,
1488 l_last_updated_by,
1489 sysdate,
1490 l_last_update_login);
1491
1492 if upper(v1.email_user) in ('INTENT','ACKNOWLEDGEMENTS') then
1493 update iem_mstemail_accounts
1494 set deleted_flag='Y'
1495 where user_name=v1.email_user;
1496 end if;
1497
1498 -- Encrypt the Password
1499 IEM_MSTEMAIL_ACCOUNTS_PVT.encrypt_password(
1500 P_Api_Version_Number =>1.0,
1501 P_Init_Msg_List =>'F',
1502 P_Commit =>'F',
1503 p_email_account_id =>v1.email_Account_id,
1504 p_raw_data =>v1.email_password,
1505 x_msg_count =>l_msg_count,
1506 x_return_status =>l_ret_status,
1507 x_msg_data =>l_msg_data);
1508 -- Migrating Intent Data
1509 for v2 in c_intent LOOP
1510 l_intent_id:=v2.classification_id;
1511 insert into iem_intents
1512 (intent_id,
1513 intent,
1514 INTENT_LANG,
1515 THEME_ENABLED,
1516 CREATED_BY ,
1517 CREATION_DATE ,
1518 LAST_UPDATED_BY ,
1519 LAST_UPDATE_DATE ,
1520 LAST_UPDATE_LOGIN )
1521 VALUES
1522 (v2.classification_id,
1523 v2.classification,
1524 l_acct_language,
1525 l_theme_enabled,
1526 l_created_by,
1527 sysdate,
1528 l_last_updated_by,
1529 sysdate,
1530 l_last_update_login);
1531 -- Insert into iem_ACCOUNT_INTENTS
1532 insert into iem_account_intents
1533 (intent_id,
1534 email_account_id,
1535 CREATED_BY ,
1536 CREATION_DATE ,
1537 LAST_UPDATED_BY ,
1538 LAST_UPDATE_DATE ,
1539 LAST_UPDATE_LOGIN )
1540 VALUES
1541 (v2.classification_id,
1542 l_email_account_id,
1543 l_created_by,
1544 sysdate,
1545 l_last_updated_by,
1546 sysdate,
1547 l_last_update_login);
1548
1549 -- Insert into IEM_INTENT_DTLS
1550 for v3 in c1 LOOP
1551 select iem_intent_dtls_s1.nextval into l_intent_dtl_id from dual;
1552 l_mod:=mod(v3.score*100,10);
1553 if (l_mod=0 or l_mod>=5) then
1554 l_weight:=ceil(v3.score*10);
1555 else
1556 l_weight:=floor(v3.score*10);
1557 end if;
1558 insert into iem_intent_dtls
1559 (INTENT_DTL_ID,
1560 INTENT_ID,
1561 KEYWORD,
1562 WEIGHT,
1563 QUERY_RESPONSE,
1564 CREATED_BY ,
1565 CREATION_DATE ,
1566 LAST_UPDATED_BY ,
1567 LAST_UPDATE_DATE ,
1568 LAST_UPDATE_LOGIN )
1569 VALUES
1570 (l_intent_dtl_id,
1571 l_intent_id,
1572 v3.theme,
1573 l_weight,
1574 v3.query_Response,
1575 v3.created_by,
1576 sysdate,
1577 v3.last_updated_by,
1578 sysdate,
1579 v3.last_update_login);
1580
1581 END LOOP; -- End Loop for INTENT DETAILS
1582 END LOOP; -- End Loop For INTENT
1583 END LOOP; -- Edn Loop For Account
1584 for v4 in c_agent LOOP
1585 insert into IEM_AGENTS
1586 (AGENT_ID,
1587 email_account_id,
1588 RESOURCE_ID,
1589 signature,
1590 CREATED_BY ,
1591 CREATION_DATE ,
1592 LAST_UPDATED_BY ,
1593 LAST_UPDATE_DATE ,
1594 LAST_UPDATE_LOGIN )
1595 VALUES
1596 (v4.agent_account_id,
1597 v4.email_Account_id,
1598 v4.resource_id,
1599 v4.signature,
1600 l_created_by,
1601 sysdate,
1602 l_last_updated_by,
1603 sysdate,
1604 l_last_update_login);
1605 END LOOP;
1606 -- Update Deleted flag of IEM_ROUTE_CLASSFICATIONS
1607
1608 update iem_route_classifications
1609 set deleted_flag='N'
1610 where deleted_flag is null; -- So that can be re runnable..
1611 -- Reset the Sequence to have highest email account id id
1612 select nvl(max(email_account_id),0) into l_max_id from iem_mstemail_accounts;
1613 LOOP
1614 select iem_mstemail_accounts_s1.nextval into l_val from dual;
1615 exit when l_val>l_max_id;
1616 END LOOP;
1617
1618 -- Reset the Sequence to have highest agent id
1619
1620 select nvl(max(agent_id),0) into l_max_id from iem_agents;
1621 LOOP
1622 select iem_agents_s1.nextval into l_val from dual;
1623 exit when l_val>l_max_id;
1624 END LOOP;
1625
1626 -- Reset the Sequence to have highest intent id
1627
1628 select nvl(max(intent_id),0) into l_max_id from iem_intents;
1629 LOOP
1630 select iem_intents_s1.nextval into l_val from dual;
1631 exit when l_val>l_max_id;
1632 END LOOP;
1633 -- Deleting OES Rule
1634 for v1 in c_rule LOOP
1635 BEGIN
1636 execute immediate 'select rule_id from ds_account@'||l_dblink||' a,om_server_rules@'||l_dblink||' b,ds_domain@'||l_dblink||' c where a.objectid=b.account_id and a.domainid=c.objectid
1637 and upper(a.name)=:user1
1638 and upper(c.qualifiedname)=:name'
1639 into l_rule_id using upper(v1.email_user),upper(v1.domain);
1640 execute immediate 'delete from om_Server_rules@'||l_dblink||' where rule_id=:id' using l_rule_id;
1641 EXCEPTION when others then
1642 null;
1643 end;
1644 end loop;
1645 x_status:='S';
1646 EXCEPTION WHEN OTHERS THEN
1647 x_status:='E';
1648 end iem_config;
1649 end IEM_MIGRATION_PVT;
[Home] [Help]