Solved

Custom event using online sql

  • 28 April 2021
  • 4 replies
  • 1165 views
P
Rank
Userlevel 3
Badge +6

This seems like it should be easy but I cannot figure it out.  I want to create a custom event for the purchase_order_line_tab using  the execute online sql action.  The goal is when a PO line is entered, use the value from one field, use that in a function to return a value and insert that value into another field for the same PO line.  For a test I can just hard code the value for now.  If I was just creating a trigger I would do something like below.  Any suggestion on what I need to do to get this to work.

 

declare
  a_ varchar2(20);

begin

  a_ := 'Test Result';
  :NEW.part_revision := a_;

end; 

Thanks

Phil

icon

Best answer by asanka 29 April 2021, 00:37

View original
This topic has been closed for comments

4 replies

A
Rank
Userlevel 4
Badge +7

Hi,

Probably you will run in to table mutation error if you attempt to do this update while the record is being inserted. Think the safest bet is to trigger a background job that will execute a custom procedure to update your field. 

e.g.

DECLARE
  attr_ VARCHAR2(3000);
BEGIN
  client_sys.clear_attr(attr_);
  client_sys.add_to_attr('TRANSACTION_ID', '&NEW:TRANSACTION_ID', attr_);
  client_sys.add_to_attr('PART_NO', '&NEW:PART_NO', attr_);
  client_sys.add_to_attr('USERID', '&NEW:USERID', attr_);
  client_sys.add_to_attr('LOT_BATCH_NO', '&NEW:LOT_BATCH_NO', attr_);
  client_sys.add_to_attr('SERIAL_NO', '&NEW:SERIAL_NO', attr_);
  client_sys.add_to_attr('LOCATION_NO', '&NEW:LOCATION_NO', attr_);
  client_sys.add_to_attr('CONTRACT', '&NEW:CONTRACT', attr_);
  client_sys.add_to_attr('QUANTITY', &NEW:quantity, attr_);
  transaction_sys.deferred_call('<custom_package.custom_procedure>',
                                'ATTRIBUTE',
                                attr_,
                                '<Custom Job Description>',
                                lang_indep_                        => 'TRUE',
                                queue_id_                          => 4);

END;
 

~hope is a good thing, maybe the best of things, and no good thing ever dies...
AvinduHendawitharana
Rank
Userlevel 4
Badge +7

Hi,

This can be achieved using a trigger at the database level. Unfortunately, this requirement will not be able to achieve using the Event actions.

Event action type  ‘Execute online SQL’ can be used to run a procedure/function and insert/update values into a table. But if you try to insert/update the same table, as Asanka mentioned table mutation error will occur.

Best Regards,
Avindu

 

AvinduH
EntNadeeL
Rank
Userlevel 5
Badge +9

Hi @pshields ,

Use below way to post the event using a  background job so it will create the record and pass whatever the values you require to the event and trigger it via a background job..

Too many declarations of 'Deferred_Call' match this call | IFS Community

Nadeesha Liyanage - https://www.linkedin.com/in/nadeeshaliyanage/
P
Rank
Userlevel 3
Badge +6

Thanks everyone.  I used bits out of all of the posts.  The linked post uses client_sys.Get_Item_Value, which I forget about but is very useful.

Thanks

Phil

Terms & ConditionsCookie settings