Within the HANDLING_UNIT_SHIPMENT (Shipment > Handling Unit Structure > Handling Units) there is a field MANUAL_GROSS_WEIGHT and we have created a custom field CF$_MANUAL_NET_WEIGHT
- The user will enter the Manual Gross Weight and the custom field Manual Net Weight values.
On saving the record we would like the custom event to check the custom field Manual Net Weight value against the Manual Gross Weight and if it is greater than or equal to the Manual Gross Weight then an error message must be displayed:
If the Manual Gross Weight already exists and the user only enters the custom field Manual Net Weight then there is no issues.
DECLARE manual_gross_weight_ NUMBER;
CURSOR get_data IS
SELECT manual_gross_weight FROM HANDLING_UNIT_CFV WHERE objkey = '&NEW:ROWKEY' ;
FETCH get_data INTO manual_gross_weight_;
IF '&NEW:CF$_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.' );
However, as the values are being entered at the same time then the Manual Gross Weight doesn’t really exist so cannot be verified against.
Am I missing anything?
Best answer by dsj
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 :)