[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
cursor c_account is select email_account_id,email_user,domain,EMAIL_PASSWORD,db_link from
iem_email_accounts a,iem_db_connections b
where a.db_server_id=b.db_server_id
and b.is_admin='A'
and upper(a.email_user)<>'INTENT';
select name||','||a.route_classification_id name from iem_route_classifications a,iem_account_route_class b
where a.route_classification_id=b.route_classification_id
and b.email_account_id=l_email_account_id
union
select 'Inbox' from dual
union
select 'Drafts' from dual
union
select 'Resolved' from dual
union
select 'Sent' from dual
union
select 'Deleted' from dual
union
select 'Admin' from dual
union
select 'Retry' from dual;
cursor c_agent is select agent_account_id,resource_id from iem_agent_accounts
where email_account_id= l_email_account_id;
l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
'select arch_folder_name from iem_Archived_Folders where
email_account_id=:id ' using l_email_account_id;
IF (upper(v1.email_user)='ACKNOWLEDGEMENTS') and x_folder_tbl(i) in ('Resolved','Deleted','Admin','Retry') then
l_ack_flag:=1;
select count(*) into l_total_count
from iem_post_mdts where email_account_id=l_email_account_id and agent_id=v3.resource_id ;
select count(a.rt_media_item_id)
into l_total_count
from iem_rt_media_items a, iem_msg_parts part
where a.rt_interaction_id in (select rtm.rt_interaction_id
from iem_rt_media_items rtm, iem_rt_interactions rti
where rtm.message_id in (select message_id from iem_post_mdts
where email_account_id = l_email_account_id and agent_id = v3.resource_id)
and rtm.email_type ='I' and rtm.rt_interaction_id = rti.rt_interaction_id
and rti.expire = 'N') and a.email_type = 'O' and a.folder_uid > 0
and a.folder_name = 'Drafts' and part.ref_key = a.rt_media_item_id
and part.part_type = 'HEADERS' and part.delete_flag <> 'Y';
select IEM_MIGRATION_DETAILS_S1.nextval into l_mig_id from dual;
insert into IEM_MIGRATION_DETAILS
(migration_id,
agent_account_id,
email_account_id,
folder_name,
folder_type,
total_msg_count,
status,
status_text,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN )
values
(l_mig_id,v3.agent_Account_id,l_email_account_id,l_folder_name,l_folder_type,l_total_count,
l_status,l_status_text,l_created_by,sysdate,l_last_updated_by,sysdate,l_last_update_login);
if ((x_folder_tbl(i) in ('Resolved','Sent','Deleted','Admin','Retry'))
OR (x_folder_tbl(i) like 'Arch%')) then
l_folder_name:=x_folder_tbl(i);
G_MSG_TABLE.DELETE;
select count(*) into l_total_count
from iem_post_mdts where email_account_id=l_email_account_id
and rt_classification_id=l_class_id and agent_id=0;
select IEM_MIGRATION_DETAILS_S1.nextval into l_mig_id from dual;
insert into IEM_MIGRATION_DETAILS
(migration_id,
email_account_id,
folder_name,
folder_type,
total_msg_count,
status,
status_text,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN )
values
(l_mig_id,l_email_account_id,l_folder_name,l_folder_type,l_total_count,l_status,l_status_text,l_created_by,
sysdate,l_last_updated_by,sysdate,l_last_update_login);
cursor c1 is select * from iem_migration_store_temp
where migration_id=p_migration_id and mig_status='R' and dp_status='D';
l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
select folder_type into l_folder_type from iem_migration_details
where migration_id=p_migration_id;
elsif upper(v1.folder_name) like '%DELETE%' then
l_proc_status:='D';
select media_id, creation_date INTO l_media_id, l_received_date
from jtf_ih_media_items
where media_item_type = 'EMAIL'
AND media_item_ref=v1.rfc822_message_id
AND source_id=v1.email_account_id
and rownum = 1;
UPDATE iem_migration_store_temp
set ih_media_item_id = l_media_id
WHERE mail_id = v1.mail_id
AND ih_media_item_id IS NULL;
l_error_text:='Error duing selecting media_id and date- l_media_id:'|| l_media_id ||' l_received_Date:'|| l_received_Date;
select creation_date into l_received_Date from jtf_ih_media_items
where media_id=l_media_id;
l_error_text:='Error duing selecting date- l_media_id:'|| l_media_id ||' l_received_Date:'|| l_received_Date;
select iem_ms_base_headers_s1.nextval into l_message_id from dual;
insert into iem_arch_msgdtls
(MESSAGE_ID ,
EMAIL_ACCOUNT_ID ,
MAILPROC_STATUS ,
MAIL_TYPE,
FROM_STR,
REPLY_TO_STR,
TO_STR,
CC_STR,
BCC_STR,
SENT_DATE,
RECEIVED_DATE ,
SUBJECT,
RESOURCE_ID ,
MESSAGE_SIZE ,
IH_MEDIA_ITEM_ID,
CUSTOMER_ID,
CONTACT_ID,
RELATIONSHIP_ID ,
MESSAGE_TEXT,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN)
values
( l_message_id,
v1.email_account_id,
l_proc_status,
l_mail_type,
v1.from_str,
v1.reply_to_str,
v1.to_str,
v1.cc_str,
v1.bcc_str,
v1.sent_date,
l_received_date,
v1.subject,
l_resource_id,
v1.message_size,
l_media_id,
l_party_id,
l_contact_id,
l_relationship_id,
v1.message_text,
nvl(l_created_by,-1),
sysdate,
nvl(l_last_updated_by,-1),
sysdate,
l_last_update_login);
-- Insert into IEM_ARCH_MESSAGES
insert into iem_arch_msgs
(MESSAGE_ID,
MESSAGE_CONTENT,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
(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
iem_migration_Store_temp where migration_id=p_migration_id and message_id=v1.message_id);
-- Update Media Items with new message Id
/* commented by siahmed
update jtf_ih_media_items
set media_item_ref=l_message_id
where media_id=v1.ih_media_item_id;
update jtf_ih_media_items
set media_item_ref=l_message_id
where media_id=l_media_id;
update iem_migration_store_temp
set mig_Status='M'
where migration_id=p_migration_id and message_id=v1.message_id;
delete from iem_arch_msgs where message_id=l_message_id;
delete from iem_arch_msgdtls where message_id=l_message_id;
l_error_text:=l_error_text ||'-Other exception during delete sqlerrm:'||sqlerrm;
update iem_migration_store_temp
set mig_Status='E',
error_text=l_error_text
where migration_id=p_migration_id and message_id=v1.message_id;
l_error_text:=l_error_text ||'-Other exception during update sqlerrm:'||sqlerrm;
select * into l_post_rec from iem_post_mdts
where message_id=v1.message_id;
-- Update Message Flag
begin
if l_post_rec.message_flag is not null then
update iem_Rt_proc_emails
set message_flag=l_post_rec.message_flag
where message_id=l_post_rec.message_id;
-- Update Media Items with new message Id
update jtf_ih_media_items
set media_item_ref=v1.message_id
where media_id=v1.ih_media_item_id;
-- update MIG status to "M"
update iem_migration_store_temp
set mig_Status='M'
where migration_id=p_migration_id and message_id=v1.message_id;
update iem_migration_Store_temp
set mig_Status=l_mig_status,
error_text=l_error_text
where migration_id=p_migration_id and message_id=v1.message_id;
update iem_migration_details
set folder_status='M'
where migration_id=p_migration_id;
update iem_migration_details
set folder_status='M',
status='E',
STATUS_TEXT='Error Encountered During Post Processing '
where migration_id=p_migration_id;
select a.* from iem_post_mdts a,iem_route_classifications b
where a.email_account_id=l_email_account_id and a.agent_id=0
and a.rt_classification_id=b.route_classification_id and
b.name=l_folder_name and a.message_id not in (select message_id from iem_migration_store_temp
where migration_id=p_migration_id)
union -- select records which are also errors out
select a.* from iem_post_mdts a,iem_route_classifications b
where a.email_account_id=l_email_account_id and a.agent_id=0
and a.rt_classification_id=b.route_classification_id and
b.name=l_folder_name and a.message_id in (select message_id from iem_migration_store_temp
where migration_id=p_migration_id and mig_status='E' and dp_status is null);
l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
INSERT_ERROR EXCEPTION;
select message_id from iem_migration_store_temp
where migration_id=p_migration_id and
(mig_status='E' and DP_STATUS is null);
select message_id from iem_migration_store_temp
where migration_id=p_migration_id and
(mig_status='E' and DP_STATUS is null);
select mail_id,folder_name,email_account_id from iem_migration_store_temp
where migration_id=p_migration_id and substr(folder_name,1,1)<>'I';
select email_account_id,folder_name,folder_type,agent_Account_id,total_msg_count into
l_email_account_id,l_folder_name,l_folder_type,l_agent_account_id,l_total_msg_count
from iem_migration_details
where migration_id=p_migration_id;
select a.db_link into l_dblink
from iem_db_connections a, iem_email_accounts b
where a.db_server_id=b.db_Server_id
and b.email_account_id = l_email_account_id and a.is_admin='A';
select count(*) into l_qcount from iem_migration_store_temp
where migration_id=p_migration_id;
delete from iem_migration_store_temp
where migration_id=p_migration_id and mig_status=null;
execute immediate 'select reply_to, cc_str, bcc_str from '
||'OM_HEADER@'||l_dblink||' where msg_id = :b1'
into l_mig_rec.reply_to_str,l_mig_rec.cc_str, l_mig_rec.bcc_str using l_source_message_id;
select IEM_MIGRATION_STORE_TEMP_s1.nextval into l_mig_rec.mail_id from dual;
insert into iem_migration_store_temp
(MAIL_ID,
MESSAGE_ID ,
MIGRATION_ID,
MESSAGE_TYPE ,
EMAIL_ACCOUNT_ID ,
AGENT_ACCOUNT_ID ,
RESOURCE_ID ,
FOLDER_NAME ,
RT_MEDIA_ITEM_ID ,
MSG_UID ,
RFC822_MESSAGE_ID ,
FROM_STR ,
TO_STR ,
REPLY_TO_STR ,
CC_STR ,
BCC_STR ,
SENT_DATE ,
SUBJECT ,
IH_MEDIA_ITEM_ID ,
MESSAGE_SIZE ,
DP_STATUS ,
MIG_STATUS ,
ERROR_TEXT,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN )
VALUES
(l_mig_rec.mail_id,
l_mig_rec.message_id,
p_migration_id,
l_folder_type,
l_email_account_id,
l_mig_rec.agent_account_id,
l_mig_rec.resource_id,
l_folder_name,
null,
l_mig_rec.msg_uid,
l_mig_rec.RFC822_MESSAGE_ID,
l_mig_rec.from_Str,
l_mig_rec.to_str,
l_mig_rec.reply_to_str,
l_mig_rec.cc_str,
l_mig_rec.bcc_Str,
l_mig_rec.sent_Date,
l_mig_rec.subject,
l_mig_rec.ih_media_item_id,
l_mig_rec.MESSAGE_SIZE,
null,
l_mig_status,
l_error_text,
l_created_by,
sysdate,
l_last_updated_by,
sysdate,
l_last_update_login);
select count(*) into l_rec_counter from iem_migration_Store_temp
where migration_id=p_migration_id ;
update iem_migration_details
set MSG_RECORD_COUNT=l_rec_counter
where migration_id=p_migration_id;
update iem_migration_details
set folder_status='R',
status='S'
where migration_id=p_migration_id;
select migration_id into l_inb_migration_id from iem_migration_details
where email_account_id=l_email_Account_id and agent_account_id=l_agent_account_id
and folder_name='Inbox';
select count(*) into l_ag_count from iem_migration_store_temp where migration_id=p_migration_id;
select count(*) into l_disc_count from iem_migration_Store_temp
where migration_id=p_migration_id and substr(folder_name,1,1) not in ('I');
select migration_id into l_mig_id from iem_migration_details
where email_account_id=v1.email_account_id and folder_name=v1.folder_name;
update iem_migration_store_temp
set message_type='Q',
migration_id=l_mig_id
where mail_id=v1.mail_id;
-- Update Count of Migration after pushing these inbox message into Queue Count
update iem_migration_details
set total_msg_count=nvl(total_msg_count,0)+1,
msg_record_count=nvl(msg_record_count,0)+1
where migration_id=l_mig_id;
select count(*) into l_rec_counter from iem_migration_store_temp
where migration_id=p_migration_id ;
select count(*) into l_error_counter from iem_migration_store_temp
where migration_id=p_migration_id and mig_status='E' and dp_status is null;
update iem_migration_details
set MSG_RECORD_COUNT=l_rec_counter-l_error_counter,
total_msg_count=l_rec_counter,
folder_status='R'
where migration_id=p_migration_id;
update iem_migration_details
set folder_Status='R',
status='E',
status_text='Error While Creating Worklist Items'
where migration_id=p_migration_id;
select to_date(value,'YYYY/MM/DD HH24:MI:SS') into l_hist_date from
iem_comp_rt_Stats where type='HISTORICAL' and param='LASTRUN' ;
select a.email_user,a.domain,a.email_password,'@'||DB_LINK
into l_user,l_domain,l_pass,l_dblink
from iem_email_accounts a,iem_db_connections b
where a.email_account_id=l_email_account_id
and a.db_server_id=b.db_server_id
and b.is_admin='A';
update iem_migration_details
set status='E',
folder_status=null,
STATUS_TEXT='Unable to Authenticate USer '||l_user||' Error Code '||l_ret
where migration_id=p_migration_id;
select count(*) into l_hist_count from iem_migration_store_temp
where migration_id=p_migration_id;
update iem_migration_details
set status='E',
folder_status=null,
STATUS_TEXT='Unable to Open Folder'||l_folder||' Error Code '||l_ret
where migration_id=p_migration_id;
l_msg_table.delete;
l_mig_status:=null; -- Update it to "R" later based on date
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 '
||'OM_HEADER'||l_dblink||' where msg_id = :b1'
into l_mig_rec.subject,l_mig_rec.sent_date,l_mig_rec.to_str,l_mig_Rec.from_str,
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;
l_error_text:='Oracle Error Occured while selecting Header Information '||sqlerrm;
--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 ;
execute immediate ' select value from om_ext_header'||l_dblink||' where msg_id =:b1 '
||' 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;
l_error_text:='Oracle Error Occured while selecting Extended Header Information '||sqlerrm;
select media_id into l_mig_rec.ih_media_item_id
from jtf_ih_media_items
where media_item_type = 'EMAIL'
and direction=l_direction
AND source_id=l_email_account_id
AND media_item_ref=l_mig_rec.rfc822_message_id;
-- and media information can be updated later rtripath 10/31/2011 can be updated manually
-- l_mig_Status:='E';
delete from iem_migration_Store_temp where migration_id=p_migration_id and message_id=l_msg_table(i);
select IEM_MIGRATION_STORE_TEMP_s1.nextval into l_mig_rec.mail_id from dual;
insert into iem_migration_store_temp
(MAIL_ID,
MESSAGE_ID ,
MIGRATION_ID,
MESSAGE_TYPE ,
EMAIL_ACCOUNT_ID ,
AGENT_ACCOUNT_ID ,
RESOURCE_ID ,
FOLDER_NAME ,
RT_MEDIA_ITEM_ID ,
MSG_UID ,
RFC822_MESSAGE_ID ,
FROM_STR ,
TO_STR ,
REPLY_TO_STR ,
CC_STR ,
BCC_STR ,
SENT_DATE ,
SUBJECT ,
IH_MEDIA_ITEM_ID ,
MESSAGE_SIZE ,
DP_STATUS ,
MIG_STATUS ,
ERROR_TEXT,
message_content,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(l_mig_rec.mail_id,
l_mig_rec.message_id,
p_migration_id,
l_folder_type,
l_mig_rec.email_account_id,
l_mig_rec.agent_account_id,
l_mig_rec.resource_id,
l_folder_name,
null,
l_mig_rec.msg_uid,
l_mig_rec.RFC822_MESSAGE_ID,
l_mig_rec.from_Str,
l_mig_rec.to_str,
l_mig_rec.reply_to_str,
l_mig_rec.cc_str,
l_mig_rec.bcc_Str,
l_mig_rec.sent_Date,
l_mig_rec.subject,
l_mig_rec.ih_media_item_id,
l_mig_rec.MESSAGE_SIZE,
null,
l_mig_status,
l_error_text,
l_content,
l_created_by,
sysdate,
l_last_updated_by,
sysdate,
l_last_update_login);
update iem_migration_store_temp
set mig_status='R'
where mig_status is null and
to_date(substr(sent_Date,1,20),'DD-MM-YYYY HH24:MI:SS','NLS_DATE_LANGUAGE=ENGLISH')>=l_hist_date;
select count(*) into l_rec_counter from iem_migration_Store_temp
where migration_id=p_migration_id and mig_Status is not null;
select count(*) into l_error_counter from iem_migration_store_temp
where migration_id=p_migration_id and mig_status='E' and dp_status is null;
update iem_migration_details
set MSG_RECORD_COUNT=l_rec_counter-l_error_counter,
folder_status='R',
status='S'
where migration_id=p_migration_id;
update iem_migration_details
set status='E',
folder_Status=null,
status_text=l_error_text
where migration_id=p_migration_id;
update iem_migration_details
set status='E',
folder_Status=null,
--STATUS_TEXT='Oracle Error occured During Worklist item creation: '
--added by siahmed
STATUS_TEXT=l_error_text
where migration_id=p_migration_id;
cursor c1 is select * from iem_migration_store_temp
where migration_id=l_id and (dp_status='E' or mig_Status='E');
cursor c_account is select email_account_id from iem_mstemail_accounts
where active_flag in ('Y','N') ;
select folder_Status into l_folder_status
from iem_migration_details
where migration_id=p_folders(i);
update iem_migration_details
set folder_Status=l_new_status,
status='S',
status_text=null
where migration_id=p_folders(i);
-- Update Status at message levels
for v1 in c1 LOOP
if v1.dp_status='E' then
update iem_migration_store_temp
set dp_status=null,
error_text=null
where mail_id=v1.mail_id;
update iem_migration_store_temp
set mig_status=l_mig_status,
error_text=null
where mail_id=v1.mail_id;
select count(*) into l_mig_count from iem_migration_details
where email_account_id=v1.email_account_id and nvl(folder_status,' ')<>'M'
and folder_type<>'H';
update iem_mstemail_accounts
set active_flag='M'
where email_account_id=v1.email_Account_id;
cursor c_account is select email_account_id from iem_mstemail_accounts
where active_flag in ('Y','N') ;
select migration_id,mig_status,dp_status into l_mig_id,l_mig_status,l_dp_status
from iem_migration_store_temp
where mail_id=p_messages(i);
update iem_migration_store_temp
set dp_status=null,
error_text=null
where mail_id=p_messages(i);
update iem_migration_details
set folder_Status=null,
status=null,
status_text=null
where migration_id=l_mig_id;
update iem_migration_store_temp
set mig_status=l_mignew_status
where mail_id=p_messages(i);
update iem_migration_details
set folder_Status=null,
status=null,
status_text=null
where migration_id=l_mig_id;
select count(*) into l_mig_count from iem_migration_details
where email_account_id=v1.email_account_id and nvl(folder_status,' ')<>'M'
and folder_type<>'H';
update iem_mstemail_accounts
set active_flag='M'
where email_account_id=v1.email_Account_id;
cursor c_account is select email_account_id from iem_mstemail_accounts
where active_flag in ('Y','N') ;
select count(*) into l_count from iem_comp_rt_stats
where type='MIGRATION' and param='STATUS' ;
select count(*) into l_mig_count from iem_pre_mdts;
select count(rt_interaction_id) into l_mig_count
from iem_rt_interactions where expire <> 'N' AND expire <> 'Y';
update iem_migration_details
set folder_Status='M' where total_msg_count=0 and status='S';
select nvl(max(message_id),0) into l_id from iem_post_mdts;
select iem_ms_base_headers_s1.nextval into l_id1 from dual;
update iem_comp_rt_Stats
set value='Y' where type='MIGRATION' and param='STATUS' ;
select to_date(value,'YYYY/MM/DD HH24:MI:SS') into l_hist_date from
iem_comp_rt_Stats where type='HISTORICAL' and param='LASTRUN' ;
update iem_comp_rt_Stats
set value=p_hist_date where type='HISTORICAL' and param='LASTRUN' ;
select count(*) into l_mig_count from iem_migration_details
where email_account_id=v1.email_account_id and folder_status<>'M';
update iem_mstemail_accounts
set active_flag='M'
where email_account_id=v1.email_Account_id;
select distinct b.migration_id from iem_migration_store_temp a,iem_migration_details b
where a.migration_id=b.migration_id and b.folder_Status='M' and a.dp_status=null;
select distinct migration_id,folder_Status from iem_migration_Details
where migration_id in (select a.migration_id from iem_migration_store_temp a,iem_migration_details b
where a.migration_id=b.migration_id and b.folder_Status='M' and a.mig_Status in (null,'D'));
cursor c_account is select email_account_id from iem_mstemail_accounts
where active_flag='M' ;
select count(*) into l_count from iem_comp_rt_stats
where type='MIGRATION' and param='STATUS' and value='Y';
select count(*) into l_count from iem_comp_rt_stats
where type='MIGRATION' and param='STATUS' and value='Y';
for x in ( select migration_id
from iem_migration_details
where folder_Status is null
and total_msg_count>0
order by decode(folder_type,'H',1,0))
LOOP
BEGIN
select * into l_folder_rec from iem_migration_details
where migration_id=x.migration_id FOR UPDATE NOWAIT;
update iem_migration_details
set folder_status='P',
status=null,
status_Text=null
where migration_id=l_migration_id;
select count(*) into l_count from iem_comp_rt_stats
where type='MIGRATION' and param='STATUS' and value='Y';
for x in ( select migration_id
from iem_migration_details
where folder_Status='D'
order by decode(folder_type,'H',1,0))
LOOP
BEGIN
select * into l_folder_rec from iem_migration_details
where migration_id=x.migration_id FOR UPDATE NOWAIT;
update iem_migration_details
set folder_status='M'
where migration_id=l_migration_id;
update iem_migration_Store_temp
set mig_status='M'
where migration_id=l_migration_id
and dp_status='D';
update iem_migration_details
set folder_status='V'
where migration_id=l_migration_id;
select count(*) into l_mig_count from iem_migration_details
where email_account_id=v1.email_account_id and folder_status<>'M'
and folder_type<>'H';
update iem_mstemail_accounts
set active_flag='N'
where email_account_id=v1.email_Account_id;
update iem_comp_rt_Stats
set value='N' where type='MIGRATION' and param='STATUS' ;
cursor c_account is select * from iem_email_accounts where email_account_id not in
(select email_account_id from iem_mstemail_accounts);
cursor c_agent is select * from iem_agent_Accounts where agent_account_id not in
(select agent_id from iem_agents);
select distinct a.classification_id,a.classification from
iem_classifications a
where a.email_account_id=l_email_account_id;
cursor c1 is select * from iem_themes where classification_id=l_intent_id
and score>0;
l_LAST_UPDATED_BY NUMBER ;
l_LAST_UPDATE_LOGIN NUMBER;
l_deleted_flag varchar2(1);
cursor c_rule is select email_user,domain from iem_email_accounts
where upper(email_user) not in ('ACKNOWLEDGEMENTS');
l_LAST_UPDATED_BY:=nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1);
l_last_update_login:=nvl(TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')),-1);
select to_number(decode(FND_PROFILE.VALUE_SPECIFIC('IEM_ACCOUNT_SENDER_NAME'),'ACCOUNT',0,1)) into
l_sender_profile from dual;
select dns_name,port
into l_in_host,l_in_port
from iem_email_servers a,iem_email_server_types b
where a.server_type_id=b.email_server_type_id and
b.email_server_type='IMAP' and
a.server_group_id=v1.server_group_id;
select dns_name,port
into l_out_host,l_out_port
from iem_email_servers a,iem_email_server_types b
where a.server_type_id=b.email_server_type_id and
b.email_server_type='SMTP' and
a.server_group_id=v1.server_group_id;
select a.db_link into l_dblink
from iem_db_connections a, iem_email_accounts b
where a.db_server_id=b.db_Server_id
and b.email_account_id = l_email_account_id and a.is_admin='A';
select kem_flag into l_flag from iem_email_Accounts
where email_account_id=v1.email_Account_id;
select decode(v1.acct_language,'GB',1,2) into l_flag from dual;
select decode(v1.acct_language,'GB',1,2) into l_flag from dual;
l_deleted_flag:='Y';
l_deleted_flag:='N';
insert into iem_mstemail_accounts
(EMAIL_ACCOUNT_ID,
EMAIL_ADDRESS ,
ACCOUNT_DESC,
USER_NAME ,
ACTIVE_FLAG ,
DELETED_FLAG,
TEMPLATE_CATEGORY,
SENDER_FLAG,
ACCOUNT_LANGUAGE ,
REPLY_TO_ADDRESS,
RETURN_ADDRESS,
FROM_NAME ,
IN_HOST ,
OUT_HOST,
IN_PORT ,
OUT_PORT ,
CUSTOM_ENABLED ,
SC_LANG ,
KEM_FLAG ,
ACCOUNT_TYPE,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN )
VALUES
(v1.email_Account_id,
v1.reply_to_Address,
v1.account_profile,
v1.email_user,
'M',
l_deleted_flag,
l_template_profile,
l_sender_profile,
v1.acct_language,
v1.reply_to_address,
v1.reply_to_address,
v1.from_name,
l_in_host,
l_out_host,
l_in_port,
l_out_port,
v1.custom_enabled,
l_sc_lang,
l_flag,
'E',
l_created_by,
sysdate,
l_last_updated_by,
sysdate,
l_last_update_login);
update iem_mstemail_accounts
set deleted_flag='Y'
where user_name=v1.email_user;
insert into iem_intents
(intent_id,
intent,
INTENT_LANG,
THEME_ENABLED,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN )
VALUES
(v2.classification_id,
v2.classification,
l_acct_language,
l_theme_enabled,
l_created_by,
sysdate,
l_last_updated_by,
sysdate,
l_last_update_login);
-- Insert into iem_ACCOUNT_INTENTS
insert into iem_account_intents
(intent_id,
email_account_id,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN )
VALUES
(v2.classification_id,
l_email_account_id,
l_created_by,
sysdate,
l_last_updated_by,
sysdate,
l_last_update_login);
-- Insert into IEM_INTENT_DTLS
for v3 in c1 LOOP
select iem_intent_dtls_s1.nextval into l_intent_dtl_id from dual;
insert into iem_intent_dtls
(INTENT_DTL_ID,
INTENT_ID,
KEYWORD,
WEIGHT,
QUERY_RESPONSE,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN )
VALUES
(l_intent_dtl_id,
l_intent_id,
v3.theme,
l_weight,
v3.query_Response,
v3.created_by,
sysdate,
v3.last_updated_by,
sysdate,
v3.last_update_login);
insert into IEM_AGENTS
(AGENT_ID,
email_account_id,
RESOURCE_ID,
signature,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN )
VALUES
(v4.agent_account_id,
v4.email_Account_id,
v4.resource_id,
v4.signature,
l_created_by,
sysdate,
l_last_updated_by,
sysdate,
l_last_update_login);
update iem_route_classifications
set deleted_flag='N'
where deleted_flag is null; -- So that can be re runnable..
select nvl(max(email_account_id),0) into l_max_id from iem_mstemail_accounts;
select iem_mstemail_accounts_s1.nextval into l_val from dual;
select nvl(max(agent_id),0) into l_max_id from iem_agents;
select iem_agents_s1.nextval into l_val from dual;
select nvl(max(intent_id),0) into l_max_id from iem_intents;
select iem_intents_s1.nextval into l_val from dual;
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
and upper(a.name)=:user1
and upper(c.qualifiedname)=:name'
into l_rule_id using upper(v1.email_user),upper(v1.domain);
execute immediate 'delete from om_Server_rules@'||l_dblink||' where rule_id=:id' using l_rule_id;
l_msg_table.delete;
l_messages.delete;
G_MSG_TABLE.DELETE;