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.OE_MSG_PUB SQL Statements

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

Line: 35


G_msg_tbl.DELETE;

Line: 38


G_msg_context_tbl.DELETE;

Line: 132


         SELECT header_id
         INTO   G_msg_context_tbl(G_msg_context_count).header_id
         FROM   oe_order_lines_all
         WHERE  line_id = p_line_id;

Line: 215


PROCEDURE Update_Msg_Context (
     p_entity_code                    IN  VARCHAR2  DEFAULT FND_API.G_MISS_CHAR
    ,p_entity_id                      IN  NUMBER    DEFAULT FND_API.G_MISS_NUM
    ,p_header_id                      IN  NUMBER    DEFAULT FND_API.G_MISS_NUM
    ,p_line_id                        IN  NUMBER    DEFAULT FND_API.G_MISS_NUM
    ,p_order_source_id                IN  NUMBER    DEFAULT FND_API.G_MISS_NUM
    ,p_orig_sys_document_ref          IN  VARCHAR2  DEFAULT FND_API.G_MISS_CHAR
    ,p_orig_sys_document_line_ref     IN  VARCHAR2  DEFAULT FND_API.G_MISS_CHAR
    ,p_orig_sys_shipment_ref          IN  VARCHAR2  DEFAULT FND_API.G_MISS_CHAR
    ,p_change_sequence                IN  VARCHAR2  DEFAULT FND_API.G_MISS_CHAR
    ,p_source_document_type_id        IN  NUMBER    DEFAULT FND_API.G_MISS_NUM
    ,p_source_document_id             IN  NUMBER    DEFAULT FND_API.G_MISS_NUM
    ,p_source_document_line_id        IN  NUMBER    DEFAULT FND_API.G_MISS_NUM
    ,p_attribute_code                 IN  VARCHAR2  DEFAULT FND_API.G_MISS_CHAR
    ,p_constraint_id                  IN  NUMBER    DEFAULT FND_API.G_MISS_NUM
--  ,p_process_activity               IN  NUMBER    DEFAULT FND_API.G_MISS_NUM
  ) IS
  --
  l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;

Line: 295


      oe_debug_pub.add('Error in procedure Update_Msg_Context ' || sqlerrm);

Line: 297


End Update_Msg_Context;

Line: 308


	  G_msg_context_tbl.delete(G_msg_context_count) ;

Line: 422


         Select type
         Into   l_type
         from   fnd_new_messages a,
                   fnd_application  b
         where a.application_id = b.application_id
         and   a.language_code = USERENV('LANG')
         and   a.message_name = l_message_name
         and   b.application_short_name = l_app_id;

Line: 572


PROCEDURE Delete_Msg
(   p_msg_index IN    NUMBER	:=  NULL
)
IS
l_msg_index	NUMBER;

Line: 584


	--  Delete the whole table.

	G_msg_tbl.DELETE;

Line: 623


		--  Delete the last message table entry.

		G_msg_tbl.DELETE(G_msg_count)	;

Line: 636


      oe_debug_pub.add('Error in Delete_msg ' || sqlerrm);

Line: 638


END Delete_Msg;

Line: 1088


     insert_message(I,p_request_id,p_message_source_code);

Line: 1118


     insert_message(I,p_request_id,p_message_source_code);

Line: 1153


procedure insert_message (
         p_msg_index           IN NUMBER
        ,p_request_id          IN NUMBER
        ,p_message_source_code IN VARCHAR2)
IS
l_msg_data                     VARCHAR2(2000);

Line: 1255


         SELECT order_number
           INTO l_order_number
           FROM oe_order_headers_all
          WHERE header_id = l_header_id;

Line: 1268


       oe_debug_pub.add('Error in Procedure insert_message  ' || sqlerrm);

Line: 1285


     SELECT  oe_msg_id_S.NEXTVAL
     INTO    l_transaction_id
     FROM    dual;

Line: 1291


   insert into OE_PROCESSING_MSGS
   (  Transaction_id
     ,request_Id
--     ,message_text
     ,entity_code
     ,entity_ref
     ,entity_id
     ,header_id
     ,line_id
     ,order_source_id
     ,original_sys_document_ref
     ,original_sys_document_line_ref
     ,orig_sys_shipment_ref
     ,change_sequence
     ,source_document_type_id
     ,source_document_id
     ,source_document_line_id
     ,attribute_code
     ,creation_date
     ,created_by
     ,last_update_date
     ,last_updated_by
     ,last_update_login
     ,program_application_id
     ,program_id
     ,program_update_date
     ,process_activity
     ,notification_flag
     ,type
     ,message_source_code
     ,message_status_code
     ,org_id
    ) VALUES
    ( l_transaction_id
     ,p_request_id
--     ,l_msg_data
     ,l_entity_code
     ,l_entity_ref
     ,l_entity_id
     ,l_header_id
     ,l_line_id
     ,l_order_source_id
     ,l_orig_sys_document_ref
     ,l_orig_sys_line_ref
     ,l_orig_sys_shipment_ref
     ,l_change_sequence
     ,l_source_document_type_id
     ,l_source_document_id
     ,l_source_document_line_id
     ,l_attribute_code
     ,sysdate
     ,FND_GLOBAL.USER_ID
     ,sysdate
     ,FND_GLOBAL.USER_ID
     ,decode(p_message_source_code,'C',FND_GLOBAL.CONC_LOGIN_ID,FND_GLOBAL.LOGIN_ID)
     ,NULL
     ,NULL
     ,NULL
     ,l_process_activity
     ,l_notification_flag
     ,l_type
     ,p_message_source_code
     ,'OPEN'
     ,nvl(l_org_id,MO_GLOBAL.get_current_org_id)
     );

Line: 1359


       INSERT INTO OE_PROCESSING_MSGS_TL
       (Transaction_id
       ,language
       ,source_lang
       ,message_text
       ,created_by
       ,creation_date
       ,last_updated_by
       ,last_update_date
       ,last_update_login
       )
       SELECT
        l_transaction_id
        ,l.language_code
        ,USERENV('LANG')
        ,l_msg_data
        ,FND_GLOBAL.USER_ID
        ,sysdate
        ,FND_GLOBAL.USER_ID
        ,sysdate
        ,decode(p_message_source_code,'C',FND_GLOBAL.CONC_LOGIN_ID,FND_GLOBAL.LOGIN_ID)
        FROM fnd_languages l
        WHERE l.installed_flag in ('I','B')
	   AND   language_code = USERENV('LANG')
        AND   not exists
              (SELECT null
               FROM  oe_processing_msgs_tl t
               WHERE t.transaction_id = l_transaction_id
               AND   t.language       = l.language_code);

Line: 1396


      oe_debug_pub.add('Error in Procedure insert_message ' || sqlerrm);

Line: 1398


End insert_message;

Line: 1541


PROCEDURE Update_Notification_Flag(p_transaction_id IN NUMBER)
IS
Pragma AUTONOMOUS_TRANSACTION;

Line: 1549


    UPDATE oe_processing_msgs
    SET    notification_flag = 'Y'
    WHERE  transaction_id = p_transaction_id;

Line: 1557


      oe_debug_pub.add('Error in Procedure Update_Notification_Flag ' || sqlerrm);

Line: 1559


END Update_Notification_Flag;

Line: 1570


PROCEDURE Update_UI_Notification_Flag(p_msg_ind IN NUMBER)
IS
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;

Line: 1581


      oe_debug_pub.add('Error in Procedure Update_UI_Notification_Flag ' || sqlerrm);

Line: 1584


END Update_UI_Notification_Flag;

Line: 1666


            Delete_msg;

Line: 1679


    Delete_msg;

Line: 1703


PROCEDURE DELETE_MESSAGE
          (p_message_source_code     IN VARCHAR2   DEFAULT  NULL
          ,p_request_id_from         IN NUMBER     DEFAULT  NULL
          ,p_request_id_to           IN NUMBER     DEFAULT  NULL
          ,p_order_number_from       IN NUMBER     DEFAULT  NULL
          ,p_order_number_to         IN NUMBER     DEFAULT  NULL
          ,p_creation_date_from      IN VARCHAR2       DEFAULT  NULL
          ,p_creation_date_to        IN VARCHAR2       DEFAULT  NULL
          ,p_program_id              IN NUMBER     DEFAULT  NULL
          ,p_process_activity_name   IN VARCHAR2   DEFAULT  NULL
          ,p_order_type_id           IN NUMBER     DEFAULT  NULL
          ,p_attribute_code          IN VARCHAR2   DEFAULT  NULL
          ,p_organization_id         IN NUMBER     DEFAULT  NULL
          ,p_created_by              IN NUMBER     DEFAULT  NULL)


IS
 /* These types and variables introduced to fix 1922443 */
 TYPE Transactionidtab is TABLE OF oe_processing_msgs.transaction_id%TYPE;

Line: 1739


         oe_debug_pub.add(  'IN OE_MSG_PUB.DELETE_MESSAGE' ) ;

Line: 1742


/*            select msg.rowid,
                   msg.transaction_id
            bulk collect
            into   transactionrowids,
                   transactionids
            from   oe_processing_msgs msg,
                   oe_order_headers hdr,
                   wf_process_activities wpa,
                   wf_activities_tl wa
            Where  msg.header_id = hdr.header_id (+)
            AND    msg.process_activity = wpa.instance_id(+)
            AND    wpa.activity_name = wa.name(+)
            AND    wpa.activity_item_type =wa.item_type(+)
            AND    wpa.process_version = wa.version(+)
            AND    wa.language(+) = USERENV('LANG')
            AND    nvl(msg.message_source_code,0)   = nvl(p_message_source_code
                                               ,nvl(msg.message_source_code,0))
            AND    nvl(msg.request_id,0)    between   nvl(p_request_id_from
                                               ,nvl(msg.request_id,0))
                                 and       nvl(p_request_id_to
                                               ,nvl(msg.request_id,0))
            AND    nvl(hdr.order_number,0)  between   nvl(p_order_number_from
                                               ,nvl(hdr.order_number,0))
                                 and       nvl(p_order_number_to
                                               ,nvl(hdr.order_number,0))
            AND    msg.creation_date between   nvl(p_creation_date_from
                                               ,msg.creation_date)
                                 and       nvl(p_creation_date_to
                                               ,msg.creation_date)
            AND    nvl(msg.program_id,0)            = nvl(p_program_id
                                               ,nvl(msg.program_id,0))
            AND    nvl(wa.display_name,0)         = nvl(p_process_activity_name
                                               ,nvl(wa.display_name,0))
            AND    nvl(hdr.order_type_id,0)         = nvl(p_order_type_id
                                               ,nvl(hdr.order_type_id,0))
            AND    nvl(msg.attribute_code,0)        = nvl(p_attribute_code
                                               ,nvl(msg.attribute_code,0))
            AND    nvl(hdr.sold_to_org_id,0)       = nvl(p_organization_id
                                               ,nvl(hdr.sold_to_org_id,0))
            AND    msg.created_by            = nvl(p_created_by
                                               ,msg.created_by);

Line: 1797


    l_stmt := 'select transaction_id ' ||
        ' from oe_processing_msgs msg';

Line: 1804


    l_stmt := 'select transaction_id ' ||
        ' from oe_processing_msgs msg, oe_order_headers_all hdr';

Line: 1810


    l_stmt := 'select transaction_id ' ||
        ' from oe_processing_msgs msg, oe_order_headers hdr' ||
         ',wf_process_activities wpa, wf_activities_tl wa ';

Line: 1881


   SELECT fnd_date.chardt_to_date(p_creation_date_from),
          fnd_date.chardt_to_date(p_creation_date_to)
   INTO   l_creation_date_from,l_creation_date_to
   FROM DUAL;

Line: 1965


   Delete
   from   oe_processing_msgs_tl
   Where  transaction_id = Transactionids(J);

Line: 1970


   Delete
   from   oe_processing_msgs
   Where  transaction_id = Transactionids(J);

Line: 1976


   Delete
   from   oe_processing_msgs
   Where  rowid = Transactionrowids(J);

Line: 1980


   Transactionids.DELETE;

Line: 1981


   Transactionrowids.DELETE;

Line: 1986


      oe_debug_pub.add(  'EXITING OE_MSG_PUB.DELETE_MESSAGE' ) ;

Line: 1995


END DELETE_MESSAGE;

Line: 1997


PROCEDURE DELETE_OI_MESSAGE
           (p_request_id                  IN NUMBER     DEFAULT  NULL
           ,p_order_source_id             IN NUMBER     DEFAULT  NULL
           ,p_orig_sys_document_ref       IN VARCHAR2   DEFAULT  NULL
           ,p_change_sequence             IN VARCHAR2   DEFAULT  NULL
           ,p_orig_sys_document_line_ref  IN VARCHAR2   DEFAULT  NULL
           ,p_orig_sys_shipment_ref       IN VARCHAR2   DEFAULT  NULL
           ,p_entity_code                 IN VARCHAR2   DEFAULT  NULL
           ,p_entity_ref                  IN VARCHAR2   DEFAULT  NULL
           ,p_org_id                      IN NUMBER     DEFAULT  NULL)

IS
 /* Replaced with the following to fix 1922443
 TYPE Transaction_tab is TABLE OF oe_processing_msgs.transaction_id%TYPE;

Line: 2032


         oe_debug_pub.add(  'IN OE_MSG_PUB.DELETE_OI_MESSAGE' ) ;

Line: 2035


            Select transaction_id
		  Bulk Collect Into  transactions_oi

            select rowid,
                   transaction_id
            bulk collect
            into   transactionrowids,
                   transactionids
            from   oe_processing_msgs
            Where  nvl(request_id,0) = nvl(p_request_id,
							    nvl(request_id,0))
            Removed nvl condition as we expect order_source_id and
              original_sys_document_ref to be passed # 2467558
            And    order_source_id = p_order_source_id
            And    original_sys_document_ref = p_orig_sys_document_ref
            And    nvl(Original_sys_document_line_ref,0) =
		                             nvl(p_Orig_sys_document_line_ref,
							    nvl(Original_sys_document_line_ref,0))
            And    nvl(orig_sys_shipment_ref,0) = nvl(p_orig_sys_shipment_ref,
							    nvl(orig_sys_shipment_ref,0))
            And    nvl(change_sequence,0) = nvl(p_change_sequence,
							    nvl(change_sequence,0))
            And    nvl(entity_code,0) = nvl(p_entity_code,
							    nvl(entity_code,0))
            And    nvl(entity_ref,0) = nvl(p_entity_ref,
							    nvl(entity_ref,0));

Line: 2064


	  Delete
	  from   oe_processing_msgs_tl
	  Where  transaction_id = Transactions_oi(J);

Line: 2070


	  Delete
	  from   oe_processing_msgs
	  Where  transaction_id = Transactions_oi(J);

Line: 2074


     Transactions_oi.DELETE;

Line: 2078


	  Delete
	  from   oe_processing_msgs_tl
	  Where  transaction_id = Transactionids(J);

Line: 2084


	  Delete
	  from   oe_processing_msgs
	  Where  rowid = Transactionrowids(J);

Line: 2089


     Transactionids.DELETE;

Line: 2090


     Transactionrowids.DELETE;

Line: 2098


    l_stmt := 'select transaction_id ' ||
        ' from oe_processing_msgs';

Line: 2207


   Delete
   from   oe_processing_msgs_tl
   Where  transaction_id = Transactionids(J);

Line: 2212


   Delete
   from   oe_processing_msgs
   Where  transaction_id = Transactionids(J);

Line: 2216


   Transactionids.DELETE;

Line: 2217


   Transactionrowids.DELETE;

Line: 2222


         oe_debug_pub.add(  'EXITING OE_MSG_PUB.DELETE_OI_MESSAGE' ) ;

Line: 2231


END DELETE_OI_MESSAGE;

Line: 2266


  fnd_msg_pub.delete_msg; -- Adding this call to fix 4642102.

Line: 2335


        insert_message(I,l_request_id,l_message_source_code);

Line: 2350


PROCEDURE Update_status_code(
     p_request_id                  IN  NUMBER      DEFAULT NULL
    ,p_org_id                      IN  NUMBER      DEFAULT NULL
    ,p_entity_code                 IN  VARCHAR2    DEFAULT NULL
    ,p_entity_id                   IN  NUMBER      DEFAULT NULL
    ,p_header_id                   IN  NUMBER      DEFAULT NULL
    ,p_line_id                     IN  NUMBER      DEFAULT NULL
    ,p_order_source_id             IN  NUMBER      DEFAULT NULL
    ,p_orig_sys_document_ref       IN  VARCHAR2    DEFAULT NULL
    ,p_orig_sys_document_line_ref  IN  VARCHAR2    DEFAULT NULL
    ,p_orig_sys_shipment_ref       IN  VARCHAR2    DEFAULT NULL
    ,p_change_sequence             IN  VARCHAR2    DEFAULT NULL
    ,p_source_document_type_id     IN  NUMBER      DEFAULT NULL
    ,p_source_document_id          IN  NUMBER      DEFAULT NULL
    ,p_source_document_line_id     IN  NUMBER      DEFAULT NULL
    ,p_attribute_code              IN  VARCHAR2    DEFAULT NULL
    ,p_constraint_id               IN  NUMBER      DEFAULT NULL
    ,p_process_activity            IN  NUMBER      DEFAULT NULL
    ,p_sold_to_org_id              IN  NUMBER      DEFAULT NULL
    ,p_status_code                 IN  Varchar2)
IS
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;

Line: 2395


    l_stmt := 'Select transaction_id from oe_processing_msgs Where 1 = 1';

Line: 2523


      Update oe_processing_msgs
      Set    message_status_code = p_status_code
      Where  transaction_id = l_transaction_id;

Line: 2532


      oe_debug_pub.add('Error in Procedure Update_status_code ' || sqlerrm);

Line: 2534


End Update_status_code;

Line: 2549


  G_msg_tbl_Copy.DELETE;