APPS.IEM_OP_ADMIN_PUB SQL Statements

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

Line: 95

       str := 'select a.RT_MEDIA_ITEM_ID, a.ERROR_SUMMARY, a.ERROR_MESSAGE, a.CREATE_DATE, to_char(a.create_date, :1) FROM IEM_OUTBOX_ERRORS a
where a.outbox_error_id = (select max(b.outbox_error_id)
from IEM_OUTBOX_ERRORS b where a.rt_media_item_id = b.rt_media_item_id and b.expire <> :2 and b.create_date = (select max(b.create_date) from IEM_OUTBOX_ERRORS c where a.rt_media_item_id = c.rt_media_item_id)) order by rt_media_item_id asc';

Line: 99

       str := 'select a.RT_MEDIA_ITEM_ID, a.ERROR_SUMMARY, a.ERROR_MESSAGE, a.CREATE_DATE, to_char(a.create_date, :1) FROM IEM_OUTBOX_ERRORS a
where a.outbox_error_id = (select max(b.outbox_error_id)
from IEM_OUTBOX_ERRORS b where a.rt_media_item_id = b.rt_media_item_id and b.expire <> :2 and b.create_date = (select max(b.create_date) from IEM_OUTBOX_ERRORS c where a.rt_media_item_id = c.rt_media_item_id)) order by rt_media_item_id desc';

Line: 107

       str := 'select a.RT_MEDIA_ITEM_ID, a.ERROR_SUMMARY, a.ERROR_MESSAGE, a.CREATE_DATE, to_char(a.create_date, :1) FROM IEM_OUTBOX_ERRORS a
where a.outbox_error_id = (select max(b.outbox_error_id)
from IEM_OUTBOX_ERRORS b where a.rt_media_item_id = b.rt_media_item_id and b.expire <> :2 and b.create_date = (select max(b.create_date) from IEM_OUTBOX_ERRORS c where a.rt_media_item_id = c.rt_media_item_id)) order by error_summary asc';

Line: 111

       str := 'select a.RT_MEDIA_ITEM_ID, a.ERROR_SUMMARY, a.ERROR_MESSAGE, a.CREATE_DATE, to_char(a.create_date, :1) FROM IEM_OUTBOX_ERRORS a
where a.outbox_error_id = (select max(b.outbox_error_id)
from IEM_OUTBOX_ERRORS b where a.rt_media_item_id = b.rt_media_item_id and b.expire <> :2 and b.create_date = (select max(b.create_date) from IEM_OUTBOX_ERRORS c where a.rt_media_item_id = c.rt_media_item_id)) order by error_summary desc';

Line: 118

       str := 'select a.RT_MEDIA_ITEM_ID, a.ERROR_SUMMARY, a.ERROR_MESSAGE, a.CREATE_DATE, to_char(a.create_date, :1) FROM IEM_OUTBOX_ERRORS a
where a.outbox_error_id = (select max(b.outbox_error_id)
from IEM_OUTBOX_ERRORS b where a.rt_media_item_id = b.rt_media_item_id and b.expire <> :2 and b.create_date = (select max(b.create_date) from IEM_OUTBOX_ERRORS c where a.rt_media_item_id = c.rt_media_item_id)) order by error_message asc';

Line: 122

       str := 'select a.RT_MEDIA_ITEM_ID, a.ERROR_SUMMARY, a.ERROR_MESSAGE, a.CREATE_DATE, to_char(a.create_date, :1) FROM IEM_OUTBOX_ERRORS a
where a.outbox_error_id = (select max(b.outbox_error_id)
from IEM_OUTBOX_ERRORS b where a.rt_media_item_id = b.rt_media_item_id and b.expire <> :2 and b.create_date = (select max(b.create_date) from IEM_OUTBOX_ERRORS c where a.rt_media_item_id = c.rt_media_item_id)) order by error_message desc';

Line: 128

       str := 'select a.RT_MEDIA_ITEM_ID, a.ERROR_SUMMARY, a.ERROR_MESSAGE, a.CREATE_DATE, to_char(a.create_date, :1) FROM IEM_OUTBOX_ERRORS a
where a.outbox_error_id = (select max(b.outbox_error_id)
from IEM_OUTBOX_ERRORS b where a.rt_media_item_id = b.rt_media_item_id and b.expire <> :2 and b.create_date = (select max(b.create_date) from IEM_OUTBOX_ERRORS c where a.rt_media_item_id = c.rt_media_item_id)) order by create_date asc';

Line: 132

       str := 'select a.RT_MEDIA_ITEM_ID, a.ERROR_SUMMARY, a.ERROR_MESSAGE, a.CREATE_DATE, to_char(a.create_date, :1) FROM IEM_OUTBOX_ERRORS a
where a.outbox_error_id = (select max(b.outbox_error_id)
from IEM_OUTBOX_ERRORS b where a.rt_media_item_id = b.rt_media_item_id and b.expire <> :2 and b.create_date = (select max(b.create_date) from IEM_OUTBOX_ERRORS c where a.rt_media_item_id = c.rt_media_item_id)) order by create_date desc';

Line: 165

          select rt_interaction_id into l_rt_interaction_id
		from iem_rt_media_items
          where rt_media_item_id = l_op_rec.rt_media_item_id;

Line: 169

          select resource_id, status into l_resource_id, l_action
          from iem_rt_interactions
          where rt_interaction_id = l_rt_interaction_id;

Line: 180

            select user_name into l_name from jtf_rs_resource_extns
		  where resource_id = l_resource_id;

Line: 312

        delete from iem_outbox_errors;

Line: 315

        delete from iem_outbox_errors where rt_media_item_id = p_rt_media_item_id_array(i).num;

Line: 404

        update iem_rt_media_items set expire = G_EXPIRE
        where rt_interaction_id in
        (select rt_interaction_id from iem_rt_interactions
         where expire = G_PROCESSING);

Line: 409

        update iem_rt_interactions set expire = G_EXPIRE
        where expire = G_PROCESSING;

Line: 412

        update iem_outbox_errors set expire = G_EXPIRE;

Line: 417

        select rt_interaction_id into l_rt_interaction_id
          from iem_rt_media_items
          where rt_media_item_id = p_rt_media_item_id_array(i).num;

Line: 421

        update iem_rt_interactions set expire = G_EXPIRE where
          rt_interaction_id = l_rt_interaction_id;

Line: 424

        update iem_rt_media_items set expire = G_EXPIRE where
          rt_interaction_id = l_rt_interaction_id;

Line: 427

        update iem_outbox_errors set expire = G_EXPIRE where
          rt_media_item_id = p_rt_media_item_id_array(i).num;

Line: 544

select rt_interaction_id, resource_id, message_id, media_id,
       to_char(creation_date, DATE_FORMAT), status
into l_rt_interaction_id, v_resource_id, v_mdt_msg_id, v_media_id,
       v_create_date, v_rt_media_status
from iem_rt_media_items where rt_media_item_id = p_rt_media_item_id;

Line: 552

select interaction_id, mc_parameter_id, status, expire, to_resource_id
into v_interaction_id, v_mcp_id, l_ih_status, v_rt_ih_expire, l_to_resource_id
from iem_rt_interactions where rt_interaction_id = l_rt_interaction_id;

Line: 561

  select rt_media_item_id into v_outb_rt_media_item_id
  from iem_rt_media_items
  where rt_interaction_id = l_rt_interaction_id and email_type='O';

Line: 570

  select rt_media_item_id into v_inb_rt_media_item_id
  from iem_rt_media_items
  where rt_interaction_id = l_rt_interaction_id and email_type='I'
  and expire <> G_DORMANT;

Line: 580

  select message_id, media_id, to_char(creation_date, DATE_FORMAT), status
  into v_mdt_msg_id, v_media_id, v_create_date, v_rt_media_status
  from iem_rt_media_items where rt_media_item_id = v_inb_rt_media_item_id;

Line: 589

  select email_account_id, subject, from_address
  into v_master_acct_id, v_subject, v_sender
  from iem_rt_proc_emails where message_id = v_mdt_msg_id;

Line: 593

  select from_name into v_master_acct_name from iem_mstemail_accounts
  where email_account_id = v_master_acct_id;

Line: 606

  select email_account_id, agent_account_id
  into l_email_acct_id, l_agent_acct_id
  from iem_rt_media_items where rt_media_item_id = l_rt_media_item_id;

Line: 611

    select from_name into v_master_acct_name from iem_mstemail_accounts
    where email_account_id = l_email_acct_id;

Line: 616

    select a.from_name, a.email_account_id
    into v_master_acct_name, v_master_acct_id
    from iem_mstemail_accounts a, iem_agents b
    where a.email_account_id = b.email_account_id
    and b.agent_id = l_agent_acct_id;

Line: 627

  select agent_account_id, resource_id into l_agent_acct_id, v_to_resource_id
  from iem_rt_media_items
  where media_id = v_media_id and rt_media_item_id <> p_rt_media_item_id
  and    expire <> G_EXPIRE
  and    rownum < 2;

Line: 633

  select email_account_id into v_to_master_acct_id from iem_agents
  where agent_id = l_agent_acct_id;

Line: 638

  select email_account_id, db_server_id
  into v_to_master_acct_id, l_reroute_type
  from iem_rt_media_items
  where rt_interaction_id = l_rt_interaction_id
  and status = G_NEWREROUTE and expire = G_DORMANT;

Line: 734

  l_con_update             VARCHAR2(80);

Line: 741

  l_th_update              VARCHAR2(80);

Line: 769

select a.CONTROLLER_ID, a.JSERV_ID, a.JSERV_PORT,
       a.APACHE_HOST, a.APACHE_PORT, to_char(a.START_TIME, DATE_FORMAT),
       to_char(a.LAST_UPDATE_DATE, DATE_FORMAT), a.FAILED_REASON
into l_con_id, l_jserv_id, l_jserv_post, l_host, l_apache_port,
     l_con_start, l_con_update, l_con_fail
from IEM_OP_CONTROLLER_STATS a order by APACHE_HOST, CONTROLLER_ID desc

select b.CONTROLLER_ID, b.THREAD_ID, b.PROCESSED_MSG_COUNT,
       b.THREAD_TYPE, to_char(b.START_TIME, DATE_FORMAT),
       to_char(b.LAST_UPDATE_DATE, DATE_FORMAT), b.FAILED_REASON
into l_th_con_id, l_th_id, l_th_start, l_msg_count, l_th_type,
     l_th_update, l_th_fail
from IEM_OP_THREAD_STATS b, IEM_OP_CONTROLLER_STATS a
where a.CONTROLLER_ID = b.CONTROLLER_ID
order by a.APACHE_HOST, a.CONTROLLER_ID desc, b.THREAD_TYPE desc



--------------------------
-- Standard Check Of p_commit.
     IF FND_API.To_Boolean(p_commit) THEN
          COMMIT WORK;

Line: 885

  select rt_interaction_id into l_rt_interaction_id
  from iem_rt_media_items
  where rt_media_item_id = p_rt_media_item_ids(i).num;

Line: 889

  select rti.mc_parameter_id, rti.interaction_id, rti.customer_id, rti.contact_id, rti.relationship_id
  into l_mcp_id, l_interaction_id, l_customer_id, l_contact_id, l_relationship_id
  from iem_rt_interactions rti
  where rti.rt_interaction_id = l_rt_interaction_id;

Line: 896

    select mcp.action into l_mcp_action
    from iem_mc_parameters mcp
    where mcp.mc_parameter_id = l_mcp_id;

Line: 905

    select media_id
    into l_inb_media_id
    from iem_rt_media_items
    where rt_interaction_id = l_rt_interaction_id
    and email_type = 'I';

Line: 928

    update iem_rt_media_items set expire = G_EXPIRE
    where rt_interaction_id = l_rt_interaction_id;

Line: 931

    update iem_rt_interactions set expire = G_EXPIRE
    where rt_interaction_id = l_rt_interaction_id;

Line: 934

    update iem_outbox_errors set expire = G_EXPIRE
    where rt_media_item_id in
    (select rt_media_item_id from iem_rt_media_items
    where rt_interaction_id = l_rt_interaction_id);

Line: 941

    select message_id into l_message_id
    from iem_rt_media_items
    where rt_media_item_id = p_rt_media_item_ids(i).num and email_type = 'I';

Line: 945

    update iem_rt_media_items set expire = G_ACTIVE, status = 'U'
    where rt_interaction_id = l_rt_interaction_id and expire <> 'D';

Line: 949

      update iem_rt_media_items set expire = G_EXPIRE
      where message_id = l_message_id and expire = 'D';

Line: 953

    update iem_rt_interactions set expire = G_ACTIVE
    where rt_interaction_id = l_rt_interaction_id;

Line: 957

      update iem_rt_proc_emails set queue_status = null
      where message_id = l_message_id;

Line: 961

    update iem_outbox_errors set expire = G_EXPIRE
    where rt_media_item_id in
    (select rt_media_item_id from iem_rt_media_items
    where rt_interaction_id = l_rt_interaction_id);