APPS.IEM_OUTBOX_PROC_PUB SQL Statements

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

Line: 67

  l_last_updated_by        NUMBER:=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);

Line: 68

  l_last_update_login      NUMBER:= NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);

Line: 201

      UPDATE iem_rt_interactions SET status = 'S'   -- send
      WHERE rt_interaction_id = l_rt_interaction_id;

Line: 282

  l_last_updated_by        NUMBER:=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);

Line: 283

  l_last_update_login      NUMBER:= NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);

Line: 315

SELECT rt_interaction_id INTO l_rt_interaction_id
FROM iem_rt_media_items WHERE rt_media_item_id = p_outbox_item_id;

Line: 318

UPDATE iem_rt_media_items SET
  expire = G_EXPIRE,
  last_updated_by = l_last_updated_by,
  last_update_login = l_last_update_login
WHERE rt_interaction_id = l_rt_interaction_id;

Line: 324

UPDATE iem_rt_interactions SET
  expire = G_EXPIRE,
  last_updated_by = l_last_updated_by,
  last_update_login = l_last_update_login
WHERE rt_interaction_id = l_rt_interaction_id;

Line: 377

  l_last_updated_by        NUMBER:=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);

Line: 378

  l_last_update_login      NUMBER:= NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);

Line: 418

SELECT rt_interaction_id INTO l_rt_interaction_id
FROM iem_rt_media_items WHERE rt_media_item_id = p_outbox_item_id;

Line: 421

SELECT status, decode(action_id, NULL, -1, action_id),
       action_item_id, outcome_id, result_id, reason_id
INTO l_status, l_action_id, l_activity_type_id, l_outcome_id,  l_result_id,
     l_reason_id
FROM iem_rt_interactions
WHERE rt_interaction_id = l_rt_interaction_id;

Line: 510

  l_last_updated_by        NUMBER:=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);

Line: 511

  l_last_update_login      NUMBER:= NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);

Line: 543

select iem_outbox_errors_s1.nextval into l_error_id from dual;

Line: 545

INSERT INTO iem_outbox_errors
( OUTBOX_ERROR_ID,  RT_MEDIA_ITEM_ID,  ERROR_SUMMARY,
  ERROR_MESSAGE,  CREATE_DATE,  EXPIRE  )
VALUES ( l_error_id, p_rt_media_item_id, p_error_summary,
 p_error_msg, SYSDATE, G_ACTIVE );

Line: 615

  l_last_updated_by        NUMBER:=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);

Line: 616

  l_last_update_login      NUMBER:= NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);

Line: 758

   select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;

Line: 759

   INSERT INTO iem_rt_interactions (
                   rt_interaction_id, resource_id, customer_id, contact_id, type,
                   status, expire, created_by, creation_date, last_updated_by,
                   last_update_date, last_update_login, parent_interaction_id,
                   service_request_id, inb_tag_id, interaction_id,
                   mc_parameter_id, ih_creator, action_id, action_item_id,
                   outcome_id, relationship_id)
         VALUES (
                   l_i_sequence, l_resource_id, l_customer_id, l_contact_id,
                   G_INBOUND, 'S', G_ACTIVE, l_created_by,
                   SYSDATE, l_last_updated_by, SYSDATE, l_last_update_login,
                   l_parent_ih_id, l_sr_id, null, l_interaction_id,
                   l_mc_parameter_id, l_ih_creator, 74, 45, 53, l_relationship_id);

Line: 774

   select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;

Line: 775

   INSERT INTO iem_rt_media_items (
                   rt_interaction_id, rt_media_item_id, resource_id,
                   media_id, message_id, rfc822_message_id, folder_name,
                   folder_uid, email_account_id, db_server_id, email_type,
                   status, expire, version, created_by, creation_date,
                   last_updated_by, last_update_date, last_update_login )
          VALUES (
                   l_i_sequence, l_m_sequence, l_resource_id,
                   p_media_id,
                   p_mdt_message_id,
                   p_rfc822_message_id,
                   p_folder_name,
                   p_message_uid,
                   p_master_account_id,
                   null,
                   G_INBOUND, G_UNMOVED, G_ACTIVE,0, l_created_by, SYSDATE,
                   l_last_updated_by, SYSDATE, l_last_update_login );

Line: 883

PROCEDURE insertBodyText
  (p_api_version_number    IN   NUMBER,
   p_init_msg_list         IN   VARCHAR2,
   p_commit                IN   VARCHAR2,
   p_outbox_item_id        IN   NUMBER,
   p_text                  IN   BLOB,
   x_return_status         OUT  NOCOPY VARCHAR2,
   x_msg_count             OUT  NOCOPY NUMBER,
   x_msg_data              OUT  NOCOPY VARCHAR2
  ) AS

  l_msg_count           NUMBER(2);

Line: 898

  l_api_name    CONSTANT VARCHAR2(30) := 'InsertBodyText';

Line: 904

  SAVEPOINT insertBodyText;

Line: 918

  select count(REF_KEY) into l_sequence from IEM_MSG_PARTS where REF_KEY = p_outbox_item_id and PART_TYPE = 'HTMLTEXT';

Line: 921

  insert into IEM_MSG_PARTS
  (
    REF_KEY,
    PART_TYPE,
    PART_NAME,
    PART_DATA,
    DELETE_FLAG,
    LAST_UPDATE_DATE
  )
  values
  (
    p_outbox_item_id,
    'HTMLTEXT',
    l_sequence,
    empty_blob(),
    'N',
    SYSDATE
  );

Line: 940

  update IEM_MSG_PARTS set PART_DATA = p_text where REF_KEY = p_outbox_item_id and PART_TYPE = 'HTMLTEXT' and PART_NAME = l_sequence;

Line: 997

END insertBodyText;

Line: 1000

PROCEDURE insertDocument
  (p_api_version_number    IN   NUMBER,
   p_init_msg_list         IN   VARCHAR2,
   p_commit                IN   VARCHAR2,
   p_outbox_item_id        IN   NUMBER,
   p_document_source       IN   VARCHAR2,
   p_document_id           IN   NUMBER,
   x_return_status         OUT  NOCOPY VARCHAR2,
   x_msg_count             OUT  NOCOPY NUMBER,
   x_msg_data              OUT  NOCOPY VARCHAR2
  ) AS

  l_msg_count           NUMBER(2);

Line: 1017

  l_api_name    CONSTANT VARCHAR2(30) := 'insertDocument';

Line: 1023

  SAVEPOINT insertDocument;

Line: 1039

  select count(REF_KEY) into l_sequence from IEM_MSG_PARTS where REF_KEY = p_outbox_item_id and PART_TYPE = 'HTMLTEXT';

Line: 1042

  insert into IEM_MSG_PARTS
  (
    REF_KEY,
    PART_TYPE,
    PART_NAME,
    PART_INFO,
    PART_DATA,
    DELETE_FLAG,
    LAST_UPDATE_DATE
  )
  values
  (
    p_outbox_item_id,
    'HTMLTEXT',
    l_sequence,
    l_part_info,
    empty_Blob(),
    'N',
    SYSDATE
  );

Line: 1119

END insertDocument;

Line: 1162

    insert into IEM_MSG_PARTS
    (
      REF_KEY,
      PART_TYPE,
      PART_NAME,
      PART_INFO,
      PART_DATA,
      DELETE_FLAG,
      LAST_UPDATE_DATE
    )
    values
    (
      p_outbox_item_id,
      'ATTACHMENT',
      p_attachment_name,
      NULL,
      empty_Blob(),
      'N',
      SYSDATE
    );

Line: 1183

    update IEM_MSG_PARTS set PART_DATA = p_binary_source where REF_KEY = p_outbox_item_id and PART_TYPE = 'ATTACHMENT' and  PART_NAME = p_attachment_name;

Line: 1188

    insert into IEM_MSG_PARTS
    (
      REF_KEY,
      PART_TYPE,
      PART_NAME,
      PART_INFO,
      PART_DATA,
      DELETE_FLAG,
      LAST_UPDATE_DATE
    )
    values
    (
      p_outbox_item_id,
      'ATTACHMENT',
      p_attachment_name,
      l_part_info,
      empty_Blob(),
      'N',
      SYSDATE
    );

Line: 1282

  l_last_updated_by        NUMBER:=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);

Line: 1283

  l_last_update_login      NUMBER:= NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);

Line: 1389

  l_last_updated_by        NUMBER:=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);

Line: 1390

  l_last_update_login      NUMBER:= NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);

Line: 1427

     select EMAIL_ACCOUNT_ID, RT_CLASSIFICATION_ID,
          IH_MEDIA_ITEM_ID
          into l_email_acct_id, l_classification_id,
          l_media_id
          from iem_rt_proc_emails where message_id = p_mdt_msg_id;

Line: 1441

   SELECT name INTO l_folder_name
   FROM   iem_route_classifications
   WHERE  ROUTE_CLASSIFICATION_ID = l_classification_id;

Line: 1445

   select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;

Line: 1446

   INSERT INTO iem_rt_interactions (
                   rt_interaction_id, resource_id, type,
                   status, expire, created_by, creation_date, last_updated_by,
                   last_update_date, last_update_login, to_resource_id
                   )
              VALUES (
                   l_i_sequence, p_resource_id, G_INBOUND,
                   G_REDIRECT, G_QUEUEOUT, l_created_by,
                   SYSDATE, l_last_updated_by, SYSDATE, l_last_update_login,
                   p_to_account_id
              );

Line: 1458

       select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;

Line: 1459

       INSERT INTO iem_rt_media_items (
                   rt_interaction_id, rt_media_item_id, resource_id,
                   media_id, message_id, rfc822_message_id, folder_name,
                   folder_uid, email_account_id, db_server_id, email_type,
                   status, expire, version, created_by, creation_date,
                   last_updated_by, last_update_date, last_update_login )
              VALUES (
                   l_i_sequence, l_m_sequence, p_resource_id,
                   l_media_id, p_mdt_msg_id, null,
                   l_folder_name, null, l_email_acct_id,
                   null, G_INBOUND, G_UNMOVED, G_ACTIVE,
                   0, l_created_by, SYSDATE,
                   l_last_updated_by, SYSDATE, l_last_update_login
              );

Line: 1556

  l_last_updated_by        NUMBER:=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);

Line: 1557

  l_last_update_login      NUMBER:= NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);

Line: 1708

   select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;

Line: 1709

   INSERT INTO iem_rt_interactions (
                   rt_interaction_id, resource_id, customer_id, contact_id, type,
                   status, expire, created_by, creation_date, last_updated_by,
                   last_update_date, last_update_login, parent_interaction_id,
                   service_request_id, inb_tag_id, interaction_id,
                   mc_parameter_id, ih_creator, action_id, action_item_id,
                   outcome_id, result_id, relationship_id)
         VALUES (
                   l_i_sequence, l_resource_id, l_customer_id, l_contact_id,
                   G_INBOUND, G_AUTOFORWAD_ACT, G_ACTIVE, l_created_by,
                   SYSDATE, l_last_updated_by, SYSDATE, l_last_update_login,
                   l_parent_ih_id, l_sr_id, null, l_interaction_id,
                   l_mc_parameter_id, l_ih_creator, 73, 45, -1, -1, l_relationship_id);

Line: 1724

   select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;

Line: 1725

   INSERT INTO iem_rt_media_items (
                   rt_interaction_id, rt_media_item_id, resource_id,
                   media_id, message_id, rfc822_message_id, folder_name,
                   folder_uid, email_account_id, db_server_id, email_type,
                   status, expire, version, created_by, creation_date,
                   last_updated_by, last_update_date, last_update_login,
                   edit_mode )
          VALUES (
                   l_i_sequence, l_m_sequence, l_resource_id,
                   p_media_id,
                   p_mdt_message_id,
                   p_rfc822_message_id,
                   p_folder_name,
                   p_message_uid,
                   p_master_account_id,
                   null,
                   G_INBOUND, G_UNMOVED, G_ACTIVE,0, l_created_by, SYSDATE,
                   l_last_updated_by, SYSDATE, l_last_update_login, p_attach_inb);

Line: 1867

  l_last_updated_by        NUMBER:=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);

Line: 1868

  l_last_update_login      NUMBER:= NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);

Line: 1964

       select result_id, reason_id, outcome_id
       into l_result_id, l_reason_id, l_outcome_id
       from jtf_ih_interactions
       where interaction_id = p_interaction_id;

Line: 2020

   select IEM_RT_INTERACTIONS_S1.nextval into l_i_sequence from DUAL;

Line: 2021

   INSERT INTO iem_rt_interactions (
                   rt_interaction_id, resource_id, customer_id, contact_id, type,
                   status, expire, created_by, creation_date, last_updated_by,
                   last_update_date, last_update_login, parent_interaction_id,
                   service_request_id, inb_tag_id, interaction_id,
                   mc_parameter_id, ih_creator, action_id, action_item_id,
                   relationship_id, result_id, reason_id, outcome_id)
         VALUES (
                   l_i_sequence, l_resource_id, l_customer_id, l_contact_id,
                   G_INBOUND, 'S', G_ACTIVE, l_created_by,
                   SYSDATE, l_last_updated_by, SYSDATE, l_last_update_login,
                   l_parent_ih_id, p_sr_id, null, l_interaction_id,
                   l_mc_parameter_id, l_ih_creator, 22, 45, l_relationship_id,
                   l_result_id, l_reason_id, l_outcome_id);

Line: 2036

   select IEM_RT_MEDIA_ITEMS_S1.nextval into l_m_sequence from DUAL;

Line: 2037

   INSERT INTO iem_rt_media_items (
                   rt_interaction_id, rt_media_item_id, resource_id,
                   media_id, message_id, rfc822_message_id, folder_name,
                   folder_uid, email_account_id, db_server_id, email_type,
                   status, expire, version, created_by, creation_date,
                   last_updated_by, last_update_date, last_update_login )
          VALUES (
                   l_i_sequence, l_m_sequence, l_resource_id,
                   p_media_id,
                   p_message_id,
                   null,
                   null,
                   null,
                   p_master_account_id,
                   null,
                   G_INBOUND, G_UNMOVED, G_ACTIVE,0, l_created_by, SYSDATE,
                   l_last_updated_by, SYSDATE, l_last_update_login );