Solved

Custom Event Custom Field Advice

  • 13 January 2021
  • 2 replies
  • 52 views

Userlevel 5
Badge +15

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

 

  1. 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;

PRAGMA AUTONOMOUS_TRANSACTION;

CURSOR get_data IS 

SELECT manual_gross_weight FROM HANDLING_UNIT_CFV  WHERE objkey = '&NEW:ROWKEY' ;

BEGIN

OPEN get_data;

FETCH get_data INTO manual_gross_weight_;

CLOSE get_data;

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.' );

END IF;

END;

 

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?

icon

Best answer by dsj 14 January 2021, 10:35

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

View original

2 replies

Userlevel 6
Badge +13

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

Userlevel 5
Badge +15

 

@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

 

Reply