Solved

Custom Event Advice

  • 24 October 2020
  • 5 replies
  • 1741 views
R
Rank
Userlevel 7
Badge +18

I would like to create a custom event on the Order Quotation that triggers when the user attempts to release the quotation.

Condition for the Event Action:  

NEW:ROWSTATE = Released

 

I have created a custom field on the Order Quotation that is a calculation to display the margin percentage.

 

On clicking Release the event needs to check to see if this field is below a certain number.  If it is below the defined number then a message needs to pop up informing the user that they cannot release the order due to the margin percentage being too low.

 

I have tried creating a custom attribute on the custom event that uses an api call to retrieve the custom field value:

ifsapp.order_quotation_cfp.get_cf$_margin_percent(&NEW:QUOTATION_NO)

 

And in the Event Action tried something like:

BEGIN
IF '&MARGIN_PERCENT' < '30' THEN
                Error_SYS.Record_General('OrderQuotation', 'CHECKMARGIN: *ERR* The Sales Quotation Margin % is less than 40%.' );
END IF;
END;

 

However, I keep receiving:

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

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

 

Any ideas/advice would be much appreciated.

icon

Best answer by RutJWhalen 24 October 2020, 18:35

View original

5 replies

S
Rank
Userlevel 7
Badge +28

It is typically not possible to interrupt or stop the status progression of any object (order, quotation, etc) by an event or even through code.  This almost always will lead to problems or unexpected behavior.  The mutating trigger means that the insertion point of your event is attempting to detect or interrupt at a point that doesn’t have a determinate state.

We’ve never had success preventing the execution of a change in status based on data conditions beforehand.  The only way we’ve done something similar is by wholesale replacing the RMB function for the status change with our own custom API to do the same function plus the other details we want.  This is manageable on small objects like an MRB case for example where there are few tentacles to other objects, but then you have to modify all permissions to remove the standard function which in itself can be a fair bit of work.  But on the much larger core objects like orders and quotations, you are basically rewriting loads of code and functions that you still won’t likely get right because you can’t know all of the conditions where that status change may or may not be allowed to progress.

My advice is to avoid attempting to do what you are intending as it likely won’t work out the way you want or be 100% foolproof.  Just my 0.02 and experience.

R
Rank
Userlevel 7
Badge +18

@ShawnBerk I have done this before without any issues but that was on a customer order and on the custom event was checking the customer details. 

Anyway, thank you for your reply, much appreciated.

 

A quick update after doing some research I managed to get my custom event working by using:

 

PRAGMA AUTONOMOUS_TRANSACTION;
 

It now displays the message required and stops the quotation going to ‘Released’.

 

Thank you.

durette
Rank
Userlevel 7
Badge +18

If you try to read the _CFV view while the _TAB table is changing, you’ll get this error because the _CFV view references both the _TAB and _CFT tables.

 

When you see OBJKEY in the world of IFS views, that’s the same thing as the ROWKEY in the world of IFS tables.

 

One way around this is to get the ROWKEY from the trigger’s _TAB table (enabled in your event definition), then SELECT your CF$_ column from the _CFT table WHERE rowkey = ‘&NEW:ROWKEY’. (This forum automatically replaces fancy quotes. You’ll need to “straighten” those. 😉)

If you find this forum useful, please consider paying back to the community in your own areas of expertise. -- Kevin Durette - https://www.linkedin.com/in/kevindurette
R
Rank
Userlevel 7
Badge +18

@durette thank you for the advice I needed up not using the custom field but used the api call for the value.

 

Thank you so much for the advice, always useful for knowledge improvement.

tobese
Rank
Userlevel 3
Badge +7

Great tips with the Pragma, I have so far used a deferred job to avoid mutating.

Cheers /Tommy

Reply


Terms & ConditionsCookie settings