Skip to main content

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

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;
 

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

 

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

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