eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IEM_MSG_STAT_PUB SQL Statements

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

Line: 22


  l_last_updated_by        NUMBER;

Line: 23


  l_last_update_login      NUMBER;

Line: 43


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

Line: 44


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

Line: 65


   select iem_outbound_msg_stats_s1.nextval into l_i_sequence from dual;

Line: 68


   insert into IEM_OUTBOUND_MSG_STATS
   (
     OUTBOUND_MSG_STATS_ID,
     MEDIA_ID,
     INBOUND_MEDIA_ID,
     USES_SUGGESTIONS_Y_N,
     AUTO_REPLIED_Y_N,
     USES_KB_DOCS_Y_N,
     AGENT_ID,
	OUTBOUND_METHOD,
	EMAIL_ACCOUNT_ID,
	CUSTOMER_ID,
	CONTACT_ID,
	DATE_SENT,
	EXPIRE_Y_N,
	CREATED_BY,
	CREATION_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_DATE,
	LAST_UPDATE_LOGIN
   )
   values
   (
     l_i_sequence,
     p_outBoundMediaID,
	p_inBoundMediaID,
     l_no,
     l_no,
     l_no,
     -1,
	0,
	-1,
	-1,
	-1,
     SYSDATE,
     l_no,
	l_created_by,
	SYSDATE,
	l_last_updated_by,
	SYSDATE,
	l_last_update_login
   );

Line: 167


  l_last_updated_by        NUMBER;

Line: 168


  l_last_update_login      NUMBER;

Line: 186


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

Line: 187


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

Line: 210


     select OUTBOUND_MSG_STATS_ID into l_outbound_msg_stats_id from IEM_OUTBOUND_MSG_STATS where
    	MEDIA_ID = p_outBoundMediaID and INBOUND_MEDIA_ID = p_inBoundMediaID;

Line: 213


     select count(DOC_USAGE_STATS_ID) into l_outbound_stats_count from IEM_DOC_USAGE_STATS where
	OUTBOUND_MSG_STATS_ID = l_outbound_msg_stats_id;

Line: 228


     UPDATE IEM_OUTBOUND_MSG_STATS SET
       AUTO_REPLIED_Y_N = l_no,
       AGENT_ID = p_agentID,
       OUTBOUND_METHOD = p_outBoundMethod,
       EMAIL_ACCOUNT_ID = p_accountID,
       CUSTOMER_ID = p_customerID,
       CONTACT_ID = p_contactID,
       DATE_SENT = SYSDATE,
	  LAST_UPDATED_BY = l_last_updated_by,
	  LAST_UPDATE_DATE = SYSDATE,
	  LAST_UPDATE_LOGIN = l_last_update_login
     WHERE MEDIA_ID = p_outBoundMediaID;

Line: 241


     UPDATE IEM_OUTBOUND_MSG_STATS SET
       AUTO_REPLIED_Y_N = l_no,
       AGENT_ID = p_agentID,
       OUTBOUND_METHOD = p_outBoundMethod,
       EMAIL_ACCOUNT_ID = p_accountID,
       CUSTOMER_ID = p_customerID,
       CONTACT_ID = p_contactID,
       DATE_SENT = SYSDATE,
	  LAST_UPDATED_BY = l_last_updated_by,
	  LAST_UPDATE_DATE = SYSDATE,
	  LAST_UPDATE_LOGIN = l_last_update_login
     WHERE INBOUND_MEDIA_ID = p_inBoundMediaID;

Line: 258


   UPDATE IEM_DOC_USAGE_STATS SET
	DATE_SENT = SYSDATE,
	SAVED_Y_N = l_no,
	LAST_UPDATED_BY = l_last_updated_by,
	LAST_UPDATE_DATE = SYSDATE,
	LAST_UPDATE_LOGIN = l_last_update_login
   WHERE OUTBOUND_MSG_STATS_ID = l_outbound_msg_stats_id;

Line: 298


PROCEDURE deleteMSGStat(
    p_api_version_number    IN   NUMBER,
    p_init_msg_list         IN   VARCHAR2,
    p_commit                IN   VARCHAR2,
    p_outBoundMediaID       IN   NUMBER,
    p_inBoundMediaID        IN   NUMBER,
    x_return_status         OUT  NOCOPY VARCHAR2,
    x_msg_count             OUT  NOCOPY NUMBER,
    x_msg_data              OUT  NOCOPY VARCHAR2
    ) IS

  l_api_name               VARCHAR2(255);

Line: 311


  l_last_updated_by        NUMBER;

Line: 312


  l_last_update_login      NUMBER;

Line: 324


        SAVEPOINT deleteMSGStat_pvt;

Line: 327


  l_api_name               :='deleteMSGStat';

Line: 329


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

Line: 330


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

Line: 353


       SELECT OUTBOUND_MSG_STATS_ID INTO l_outbound_msg_stats_id
       FROM IEM_OUTBOUND_MSG_STATS WHERE MEDIA_ID = p_outBoundMediaID;

Line: 356


       SELECT OUTBOUND_MSG_STATS_ID INTO l_outbound_msg_stats_id
       FROM IEM_OUTBOUND_MSG_STATS WHERE INBOUND_MEDIA_ID = p_inBoundMediaID;

Line: 364


   DELETE FROM IEM_DOC_USAGE_STATS WHERE OUTBOUND_MSG_STATS_ID = l_outbound_msg_stats_id;

Line: 365


   DELETE FROM IEM_OUTBOUND_MSG_STATS WHERE OUTBOUND_MSG_STATS_ID = l_outbound_msg_stats_id;

Line: 375


          ROLLBACK TO deleteMSGStat_pvt;

Line: 386


          ROLLBACK TO deleteMSGStat_pvt;

Line: 397


          ROLLBACK TO deleteMSGStat_pvt;

Line: 410


END deleteMSGStat;

Line: 426


  l_last_updated_by        NUMBER;

Line: 427


  l_last_update_login      NUMBER;

Line: 443


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

Line: 444


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

Line: 469


       SELECT OUTBOUND_MSG_STATS_ID INTO l_outbound_msg_stats_id
       FROM IEM_OUTBOUND_MSG_STATS WHERE MEDIA_ID = p_outBoundMediaID;

Line: 472


       SELECT OUTBOUND_MSG_STATS_ID INTO l_outbound_msg_stats_id
       FROM IEM_OUTBOUND_MSG_STATS WHERE INBOUND_MEDIA_ID = p_inBoundMediaID;

Line: 480


   DELETE FROM IEM_DOC_USAGE_STATS WHERE OUTBOUND_MSG_STATS_ID = l_outbound_msg_stats_id
   AND SAVED_Y_N <> l_yes;

Line: 529


  l_last_updated_by        NUMBER;

Line: 530


  l_last_update_login      NUMBER;

Line: 547


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

Line: 548


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

Line: 571


       SELECT OUTBOUND_MSG_STATS_ID INTO l_outbound_msg_stats_id
       FROM IEM_OUTBOUND_MSG_STATS WHERE MEDIA_ID = p_outBoundMediaID;

Line: 574


       SELECT OUTBOUND_MSG_STATS_ID INTO l_outbound_msg_stats_id
       FROM IEM_OUTBOUND_MSG_STATS WHERE INBOUND_MEDIA_ID = p_inBoundMediaID;

Line: 582


   UPDATE IEM_DOC_USAGE_STATS SET
	SAVED_Y_N = 'Y',
     DATE_SENT = SYSDATE,
     LAST_UPDATED_BY = l_last_updated_by,
     LAST_UPDATE_DATE = SYSDATE,
	LAST_UPDATE_LOGIN = l_last_update_login
   WHERE OUTBOUND_MSG_STATS_ID = l_outbound_msg_stats_id;

Line: 628


PROCEDURE insertDocUsageStat
  (p_api_version_number    IN   NUMBER,
   p_init_msg_list         IN   VARCHAR2,
   p_commit                IN   VARCHAR2,
   p_rt_mediaID            IN   NUMBER,
   p_reply_y_n             IN   VARCHAR2,
   p_kb_doc_ID             IN   NUMBER,
   p_template_y_n          IN   VARCHAR2,
   p_repository            IN   VARCHAR2,
   p_mes_category_ID       IN   NUMBER,
   p_inserted_y_n          IN   VARCHAR2,
   p_top_ranked_intent     IN   VARCHAR2,
   p_top_ranked_intent_ID  IN   NUMBER,
   p_suggested_y_n         IN   VARCHAR2,
   p_in_top_intent_y_n     IN   VARCHAR2,
   p_intent                IN   VARCHAR2,
   p_intent_ID             IN   NUMBER,
   p_intent_score          IN   NUMBER,
   p_intent_rank           IN   NUMBER,
   p_document_rank         IN   NUMBER,
   p_document_score        IN   NUMBER,
   p_email_account_ID      IN   NUMBER,
   p_auto_insert_y_n       IN   VARCHAR2,
   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: 663


  l_last_updated_by     NUMBER;

Line: 664


  l_last_update_login   NUMBER;

Line: 678


   SAVEPOINT insertDocUsageStat_pvt;

Line: 680


  l_api_name            := 'insertDocUsageStat';

Line: 683


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

Line: 684


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

Line: 707


    select rt_interaction_id into l_rt_interaction_id from iem_rt_media_items
      where rt_media_item_id = p_rt_mediaID;

Line: 711


    select media_id into l_outbound_media_ID from iem_rt_media_items where email_type=l_email_type and
         rt_interaction_id = l_rt_interaction_id;

Line: 721


    select media_id into l_inbound_media_ID from iem_rt_media_items where email_type=l_email_type and
         rt_interaction_id = l_rt_interaction_id;

Line: 729


    select OUTBOUND_MSG_STATS_ID into l_outbound_msg_stats_id
	 from IEM_OUTBOUND_MSG_STATS where MEDIA_ID = l_outbound_media_ID;

Line: 732


    select OUTBOUND_MSG_STATS_ID into l_outbound_msg_stats_id
	 from IEM_OUTBOUND_MSG_STATS where INBOUND_MEDIA_ID = l_inbound_media_ID;

Line: 736


  select iem_doc_usage_stats_s1.nextval into l_sequence from dual;

Line: 738


  insert into IEM_DOC_USAGE_STATS
  (
     DOC_USAGE_STATS_ID,
     OUTBOUND_MSG_STATS_ID,
     REPLY_Y_N,
     KB_DOC_ID,
     TEMPLATE_Y_N,
     REPOSITORY,
     MES_CATEGORY_ID,
	INSERTED_Y_N,
	TOP_RANKED_INTENT,
	TOP_RANKED_INTENT_ID,
	SUGGESTED_Y_N,
	IN_TOP_INTENT_Y_N,
	INTENT,
	INTENT_ID,
	INTENT_SCORE,
	INTENT_RANK,
	DOCUMENT_RANK,
	DOCUMENT_SCORE,
	DATE_INSERTED,
	SAVED_Y_N,
	EMAIL_ACCOUNT_ID,
	AUTO_INSERT_Y_N,
	CREATED_BY,
	CREATION_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_DATE,
	LAST_UPDATE_LOGIN
  )
  values
  (
     l_sequence,
     l_outbound_msg_stats_id,
	p_reply_y_n,
     p_kb_doc_ID,
     p_template_y_n,
     p_repository,
	p_mes_category_ID,
	p_inserted_y_n,
	p_top_ranked_intent,
	p_top_ranked_intent_ID,
     p_suggested_y_n,
     p_in_top_intent_y_n,
	p_intent,
	p_intent_ID,
	p_intent_score,
	p_intent_rank,
	p_document_rank,
	p_document_score,
	SYSDATE,
	l_no,
	p_email_account_ID,
	p_auto_insert_y_n,
	l_created_by,
	SYSDATE,
	l_last_updated_by,
	SYSDATE,
	l_last_update_login
  );

Line: 803


  UPDATE IEM_OUTBOUND_MSG_STATS SET
       USES_SUGGESTIONS_Y_N = p_suggested_y_n,
       USES_KB_DOCS_Y_N = l_uses_kb_docs_y_n,
	  LAST_UPDATED_BY = l_last_updated_by,
	  LAST_UPDATE_DATE = SYSDATE,
	  LAST_UPDATE_LOGIN = l_last_update_login
  WHERE OUTBOUND_MSG_STATS_ID = l_outbound_msg_stats_id;

Line: 823


          ROLLBACK TO insertDocUsageStat_pvt;

Line: 830


          ROLLBACK TO insertDocUsageStat_pvt;

Line: 837


          ROLLBACK TO insertDocUsageStat_pvt;

Line: 846


END insertDocUsageStat;