Hi @johnw66 ,

Your logic is correct and this is one of the drawbacks of autonomous transaction that the second transaction can’t see the updated value of first transaction.

Implicit way to solve that is to find if there’s a database transaction happens after inserting the handling unit flow and add the validation there.

One example is customer order and order history (CustomerOrderHistory). When a new order is created or after status change, order history record is created. So instead of adding an event in Customer order, we can use order history to avoid the mutate problem. But this can only helpful if there are no commits during the code execution so have to be careful. Hope you got the concept :)

Cheers!

Damith

@dsj 

Damith,

Thank you so much for your reply, very informative.   

Basically, I have spoke to the user again last night to see whether I can use another trigger and agreed to perform the check when the user carries out RMB > Approve 

First thing this morning I revised my code and we have a solution:

DECLARE manual_net_weight_ NUMBER;                manual_gross_weight_ NUMBER;

PRAGMA AUTONOMOUS_TRANSACTION;

CURSOR get_data IS  SELECT cf$_manual_net_weight, manual_gross_weight FROM HANDLING_UNIT_CFV  WHERE shipment_id = '&NEW:SHIPMENT_ID' ;

BEGIN

OPEN get_data; FETCH get_data INTO manual_net_weight_, manual_gross_weight_; CLOSE get_data;

IF  manual_net_weight_ is null THEN      Error_SYS.Record_General('HandlingUnitHistory', 'CHECKWEIGHT:  The Manual Net Weight must be completed!' ); END IF;

IF  manual_gross_weight_ is null THEN      Error_SYS.Record_General('HandlingUnitHistory', 'CHECKWEIGHT:  The Manual Gross Weight must be completed!' ); END IF;

IF  manual_net_weight_ >= manual_gross_weight_ THEN      Error_SYS.Record_General('HandlingUnitHistory', 'CHECKWEIGHT:  The Manual Net Weight must be LESS than the Manual Gross Weight!  Please amend.' ); END IF;

END; 

However, as your advice was very informative I will select it as best answer.

John