How to avoid mutating table error in IFS event actions

 

Mutating table error occurs when trying to read or write same table which the trigger was fired.

To avoid mutating table error we can use two options.

   1. Use 'PRAGMA AUTONOMOUS_TRANSATION' in event declaration.

DECLARE

PRAGMA AUTONOMOUS_TRANSATION

BEGIN

END;

This will work if your depending code is for reading data (e.g: select query), but if you're trying to Create, Update or Delete data you will end up with getting  ORA-06519: active autonomous transaction detected and rolled back.

   2. Create a background job with dynamic sql statement.

With background processing we can use standard call Transactions_SYS.Deferred_Call() if you have a API call to use, otherwise you can use dynamic sql statement to create the background job.

DECLARE

  attr_            VARCHAR2(32000);

  sql_msg_    VARCHAR2(32000);

  stmt_           VARCHAR2(32000);

  job_id_        NUMBER;

  error_text_   VARCHAR2(2000);       

BEGIN

stmt_ := '

 

DECLARE                                  

BEGIN

   //Add your code you here. 

END;';

  sql_msg_ := Message_SYS.Construct('UPD');

  Message_SYS.Add_Attribute(sql_msg_, 'SQL', stmt_);

  Client_SYS.Clear_Attr(attr_);

  Client_SYS.Add_To_Attr('SQL_DATA_', sql_msg_, attr_);

  Client_SYS.Add_To_Attr('MSG_', '', attr_);

  Transaction_SYS.Deferred_Call(job_id_,'Fnd_Event_Action_API.Action_Executeonlinesql','PARAMETER',attr_,'Update JtTask Table');    

END; 

Example with custom fields and parameters. Make sure to use double quote for the string values inside statement.

DECLARE

attr_b_         VARCHAR2(32000);

sql_msg_     VARCHAR2(32000);

stmt_           VARCHAR2(32000);

job_id_        NUMBER;

error_text_  VARCHAR2(2000);

BEGIN

stmt_ := '

 

DECLARE 

task_seq_             jt_task.task_seq%TYPE;

objid_                  VARCHAR2(32000); 

objversion_          VARCHAR2(32000); 

attr_                      VARCHAR2(32000); 

attr_cf_                 VARCHAR2(32000); 

info_                     VARCHAR2(32000); 

CURSOR get_obj_data(task_ in NUMBER) IS 

   SELECT objid, objversion 

   FROM   jt_task_cfv

   WHERE  task_seq = task_;

CURSOR get_task_seq IS 

   SELECT task_seq, site

   FROM   jt_task

   WHERE  objkey = ''&NEW:ROWKEY'';

                                 

BEGIN

   OPEN  get_task_seq ;

   FETCH get_task_seq INTO task_seq_ , contract_;

   CLOSE get_task_seq ;

OPEN  get_obj_data(task_seq_ ); 

FETCH get_obj_data INTO objid_, objversion_; 

IF get_obj_data%found THEN 

CLOSE get_obj_data; 

ELSE 

CLOSE get_obj_data; 

END IF; 

                   

IF objid_ IS NOT NULL AND objversion_ IS NOT NULL THEN 

Client_SYS.Clear_Attr(attr_); 

Client_SYS.Clear_Attr(attr_cf_); 

Client_SYS.Add_To_Attr(''CF$_C_STOCK_AVAILABILITY'', stock_available_ , attr_cf_); 

Client_SYS.Add_To_Attr(''CF$_C_STOCK_TYPE'', ''OSW''   , attr_cf_); 

Client_SYS.Add_To_Attr(''CF$_C_WAREHOUSE_ID'', warehouse_id_ , attr_cf_); 

                      

JT_TASK_CFP.CF_MODIFY__(info_, objid_, attr_cf_, attr_, ''DO''); 

END IF; 

END;';

sql_msg_ := Message_SYS.Construct('UPD');

Message_SYS.Add_Attribute(sql_msg_, 'SQL', stmt_);

Client_SYS.Clear_Attr(attr_b_);

Client_SYS.Add_To_Attr('SQL_DATA_', sql_msg_, attr_b_);

Client_SYS.Add_To_Attr('MSG_', '', attr_b_);

   Transaction_SYS.Deferred_Call(job_id_,'Fnd_Event_Action_API.Action_Executeonlinesql','PARAMETER',attr_b_,'Check ATP Stock Availability for On-Site Warehouse'); 

END;