Question

Custom event error ( Table is mutating )

  • 1 March 2022
  • 5 replies
  • 1043 views
K
Rank
Userlevel 2
Badge +4

I am trying to create a custom event on purchase order. 

 

First I have created two custom fields on purchase order header. 

  1. One numeric field to enter the Fund allocation (Numeric ) 
  2. To pick the total PO amount      

Expression Statement - Purchase_Order_API.Calc_Total_Gross_Incl_Chg(order_no)) 

Method signature Signature  - Purchase_Order_Cfp.Get_CF$_Po_Full_Amount(v.objkey)

 

Custom event -

Fund Allocation should not be grater than the total po amount. 

 

Online Sql Statement 

 

 

DECLARE 

BEGIN 

 IF ('&NEW:CF$_FUND_ALLOCATION' > '&PO_AMOUNT') THEN 
  IFSAPP.Error_SYS.Record_General('Error', 'Fund Allocation can not exceed the PO amount');
  END IF;

END; 

 

When I activated the event I get the below error. 

 

table IFSAPP.PURCHASE_ORDER_CFT is mutating, trigger/function may not see it

Failed executing statement (ORA-04091: table IFSAPP.PURCHASE_ORDER_CFT is mutating, trigger/function may not see it

 

 

 

Please Help me with this 

 

 

 

This topic has been closed for comments

5 replies

S
Rank
Userlevel 6
Badge +12

@Kavindu911 

 

Hope this gives you some idea.

 

Regards,

Sahan

B
Rank
Userlevel 5
Badge +13

Hi @Kavindu911,

Can you please share the screenshot of the implementation of the event ? In window “Event”.

Thanks & Best Regards,
Bhagya

K
Rank
Userlevel 2
Badge +4

Hi @Kavindu911,

Can you please share the screenshot of the implementation of the event ? In window “Event”.

Thanks & Best Regards,
Bhagya

 

 

T
Rank
Userlevel 7
Badge +19

Probably the custom field you are fetching by the method “Purchase_Order_Cfp.Get_CF$_Po_Full_Amount” is accessing the table PURCHASE_ORDER_CFT which you have added the event to and that is not allowed like this.

Can you show us the SQL for custom field “Po_Full_Amount” in LU PurchaseOrder? (or the reference if it has no SQL.) Maybe there is another way of fetching that value without accessing the table again.

N
Rank
Badge

You can trigger events without hitting irreverent mutations by using a AUTONOMOUS_TRANSACTION block. Here you have to manually handle commits and rollbacks as the block will not be impacted by the triggering transaction. 

 

Sample would be something like below.

 

DECLARE 
  PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN
    
 -- Some logic
 
  COMMIT; -- or rollback.. this would need to be handled manually within the autonomous block
END;

Terms & ConditionsCookie settings