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