APPS.IEM_MGINBOX_PUB SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 51

    select a.*
    from iem_post_mdts a, iem_agent_accounts b
    where a.email_account_id = p_email_account_id and a.agent_id =
      b.resource_id and b.agent_account_id = p_agent_account_id and
	 a.email_account_id = b.email_account_id
	 and a.message_id in (select message_id from iem_rt_media_items a,
	 iem_rt_interactions b where a.rt_interaction_id=b.rt_interaction_id
	 and a.email_type='I' and b.expire='N');

Line: 62

    select a.*
      from iem_post_mdts a, iem_agent_accounts b
      where a.email_account_id = p_email_account_id and a.agent_id =
      b.resource_id and b.agent_account_id = p_agent_account_id and
	    a.email_account_id = b.email_account_id
      and a.message_id not in (select message_id from iem_migration_store_temp
      where migration_id = p_inb_migration_id and message_type = 'I'
      and mig_status = 'R')
	 and a.message_id in (select message_id from iem_rt_media_items a,
	 iem_rt_interactions b where a.rt_interaction_id=b.rt_interaction_id
	 and a.email_type='I' and b.expire='N');

Line: 76

    select a.*
      from iem_post_mdts a, iem_agent_accounts b
      where a.email_account_id = p_email_account_id and a.agent_id =
      b.resource_id and b.agent_account_id = p_agent_account_id and
	    a.email_account_id = b.email_account_id
      and a.message_id not in (select message_id from iem_migration_store_temp
      where migration_id = p_inb_migration_id and message_type = 'O'
      and mig_status = 'R');

Line: 124

         select rtm.rt_media_item_id, rtm.rt_interaction_id,
             rtm.agent_account_id, rtm.resource_id, rtm.folder_name,
             rtm.folder_uid
             into l_rt_media_item_id, l_rt_interaction_id, l_agent_account_id,
             l_resource_id, l_folder_name, l_msg_uid
             from iem_rt_media_items rtm, iem_rt_interactions rti
             where rtm.message_id = mdts.message_id and rtm.email_type ='I'
             and rtm.rt_interaction_id = rti.rt_interaction_id
             and rti.expire = 'N';

Line: 147

             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 = mdts.email_account_id and a.is_admin='A';

Line: 156

             execute immediate 'select reply_to_str, cc_str, bcc_str from '
               ||'OM_HEADER@'||l_dblink||' where msg_id = '|| mdts.source_message_id
               into l_reply_to_str, l_cc_str, l_bcc_str;

Line: 172

           select IEM_MIGRATION_STORE_TEMP_S1.nextval into l_sequence from DUAL;

Line: 173

           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, created_by, creation_date, last_update_date,
             last_updated_by, last_update_login, mig_status, error_text)
           values (
             l_sequence, mdts.message_id, p_inb_migration_id, 'I', mdts.email_account_id,
             l_agent_account_id, l_resource_id, l_folder_name,
             l_rt_media_item_id, l_msg_uid, mdts.rfc822_message_id,
             mdts.sender_name, mdts.to_address, l_reply_to_str, l_cc_str,
             l_bcc_str, to_char(mdts.sent_date, 'DD-MON-YYYY HH24:MI:SS') || ' GMT', mdts.subject,
             mdts.ih_media_item_id, mdts.message_size,
             l_userid, sysdate, sysdate, l_userid, l_loginid, l_mig_status, l_err_msg
             );

Line: 193

           select rtm.rt_media_item_id, rtm.agent_account_id, rtm.folder_name,
             rtm.folder_uid into l_outb_rt_media_item_id,
             l_outb_agent_account_id, l_outb_folder_name, l_outb_msg_uid
           from iem_rt_media_items rtm, iem_msg_parts part
           where rtm.rt_interaction_id = l_rt_interaction_id
             and rtm.email_type = 'O' and rtm.folder_uid > 0
             and rtm.folder_name = 'Drafts'
             and part.ref_key = rtm.rt_media_item_id
             and part.part_type = 'HEADERS'
             and part.delete_flag <> 'Y';

Line: 217

             select IEM_MIGRATION_STORE_TEMP_S1.nextval into l_sequence from DUAL;

Line: 218

             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, created_by, creation_date,
               last_update_date, last_updated_by, last_update_login, mig_status, error_text)
             values (
               l_sequence, mdts.message_id, p_outb_migration_id, 'D', mdts.email_account_id,
               l_outb_agent_account_id, l_resource_id, l_outb_folder_name,
               l_outb_rt_media_item_id, l_outb_msg_uid,
               l_userid, sysdate, sysdate, l_userid, l_loginid, l_mig_status, l_err_msg
               );

Line: 245

             select rtm.rt_media_item_id, rtm.rt_interaction_id,
               rtm.agent_account_id, rtm.resource_id, rtm.folder_name,
               rtm.folder_uid
             into l_rt_media_item_id, l_rt_interaction_id, l_agent_account_id,
               l_resource_id, l_folder_name, l_msg_uid
             from iem_rt_media_items rtm, iem_rt_interactions rti
             where rtm.message_id = mdts.message_id and rtm.email_type ='I'
               and rtm.rt_interaction_id = rti.rt_interaction_id
               and rti.expire = 'N';

Line: 266

             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 = mdts.email_account_id and a.is_admin='A';

Line: 276

             execute immediate 'select reply_to_str, cc_str, bcc_str from '
               ||'OM_HEADER@'||l_dblink||' where msg_id = '||mdts.source_message_id
               into l_reply_to_str, l_cc_str, l_bcc_str;

Line: 291

           select IEM_MIGRATION_STORE_TEMP_S1.nextval into l_sequence from DUAL;

Line: 292

           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, created_by, creation_date, last_update_date,
             last_updated_by, last_update_login, mig_status, error_text)
           values (
             l_sequence, mdts.message_id, p_inb_migration_id, 'I', mdts.email_account_id,
             l_agent_account_id, l_resource_id, l_folder_name,
             l_rt_media_item_id, l_msg_uid, mdts.rfc822_message_id,
             mdts.sender_name, mdts.to_address, l_reply_to_str, l_cc_str,
             l_bcc_str,to_char(mdts.sent_date,'DD-MON-YYYY HH24:MI:SS') || ' GMT', mdts.subject,
             mdts.ih_media_item_id, mdts.message_size,
             l_userid, sysdate, sysdate, l_userid, l_loginid, l_mig_status, l_err_msg
             );

Line: 308

           delete from IEM_MIGRATION_STORE_TEMP where migration_id = p_inb_migration_id
             and message_id = mdts.message_id and message_type = 'I'
             and mig_status <> 'R' and mail_id <> l_sequence;

Line: 322

           select rtm.rt_media_item_id, rtm.agent_account_id, rtm.folder_name,
             rtm.folder_uid into l_outb_rt_media_item_id,
             l_outb_agent_account_id, l_outb_folder_name, l_outb_msg_uid
           from iem_rt_media_items rtm, iem_msg_parts part
           where rtm.rt_interaction_id = l_rt_interaction_id
             and rtm.email_type = 'O' and rtm.folder_uid > 0
             and rtm.folder_name = 'Drafts'
             and part.ref_key = rtm.rt_media_item_id
             and part.part_type = 'HEADERS'
             and part.delete_flag <> 'Y';

Line: 346

             select IEM_MIGRATION_STORE_TEMP_S1.nextval into l_sequence from DUAL;

Line: 347

             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, created_by, creation_date,
               last_update_date, last_updated_by, last_update_login, mig_status, error_text)
             values (
               l_sequence, mdts.message_id, p_outb_migration_id, 'D', mdts.email_account_id,
               l_outb_agent_account_id, l_resource_id, l_outb_folder_name,
               l_outb_rt_media_item_id, l_outb_msg_uid,
               l_userid, sysdate, sysdate, l_userid, l_loginid, l_mig_status, l_err_msg
               );

Line: 358

             delete from IEM_MIGRATION_STORE_TEMP where migration_id = p_outb_migration_id
               and message_id = mdts.message_id and message_type = 'O'
               and mig_status <> 'R' and mail_id <> l_sequence;