[Home] [Help]
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,
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,
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,
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,
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
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
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;
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
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;
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';
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
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;
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;
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;
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;
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;
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;
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;
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;
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;
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);
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
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;
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;
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
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
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'
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;
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
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' ;
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;
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
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
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;
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
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
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
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