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