krestensb,

I doubt it, as you only have to prepare a deferred job at the moment that you make a change to the customer order line itself. It is also quite logical as you want to update a customer order line and at the same time you want to make the change using the Modify__ procedure on the same row. That is problematic. First standard change should be applied and then yours. It sounds to me to open the Customer_Order_Line_api and program something just after the core call of the procedure.

I’ve never tried that as I think there will be problems when an update from IFS on Customer_Order_Line_api is done (your changes are gone (I hope I’m wrong on this)).

Steve

If I run Customer_Order_Line_API.Modify__() from an event started by change on Customer_Order_Line_Tab then I get problems with mutations.

But if it is done in background I can’t see that it would be a problem.

The code could be put into the API, but this is what I would like to avoid.

The problem is, that if I want to use Transaction_SYS.Deferred_Call(), all the arguments needs to go into the attr_ parameter. And for using the Modify__ procedure I allso need to put all the arguments into an attr_ parameter.

How can I put the arguments for the modify__ into an attr_ and then put that  into the next attr_ for the deferred_Call()

Does that make sense?

Kresten

How about a migration job? 

Hi Kresetenb,

You can potentially do this using an event action of type SQL. The trick is to not trigger the event action on CO line but use a existing background job that you can schedule. 

I’ve done something similar to this when customizing the code was not the desired method. My approach was as below. Not the prettiest one but it does work.

1. Create a new event action for the standard event  BACKGROUND_JOB_IS_PROCESSED with the Execute Online SQL type.
2. Set the Event to trigger only for the background jobs for the standard database task for  “Foundation1 Monitoring” setting a condition. This task is not scheduled by default.
3. Put my code to do what i need in the SQL block. I can pretty much do anything here as i have the freedom to use a full PL SQL block to get the records i need, validate, pack the attribute string and what not. You can even use a command_sys to notify you if there is an exception. 
4. Schedule the task “Foundation1 Monitoring” to run at what ever the interval i wanted it to. Idea is that the scheduled task will submit a job of that type and the event action will trigger the code to do what i need to do. 

Cheers

There is a workaround to the mutating table problem that I normally use.

You can use method Fnd_Event_Action_API.Action_Executeonlinesql and post that as a background job. You can send in any PL/SQL block into that method as a parameter so you can do almost anything in the backgroun job, if the code is not too long.

Below is an example (quickly written and might contain syntax errors etc.) of how to use it for updating CO lines as you wanted. (You write the PL/SQL block you want to run in the stmt_ variable.)

DECLARE
 attr_ VARCHAR2(32000);
 sql_msg_ VARCHAR2(32000);
 stmt_ VARCHAR2(32000);
BEGIN
 stmt_ := '
 DECLARE
 info_ VARCHAR2(32000);
 attr_ VARCHAR2(32000);
 objid_ VARCHAR2(32000);
 objversion_ VARCHAR2(32000);
 new_value_ VARCHAR2(5) := ''FALSE'';
 BEGIN
 SELECT objid, objversion
 INTO objid_ , objversion_
 FROM customer_order_line
 WHERE order_no = ''&NEW:ORDER_NO''
 AND line_no = ''&NEW:LINE_NO''
 AND rel_no = ''&NEW:REL_NO''
 AND line_item_no = &NEW:LINE_ITEM_NO;

 Client_SYS.Clear_Attr(attr_);
 Client_SYS.Add_To_Attr(''SOME_FIELD_TO_UPDATE'', new_value_, attr_);
 Customer_Order_Line_API.Modify__(info_ , objid_ , objversion_ , attr_ , ''DO'');
 END;';

 sql_msg_ := Message_SYS.Construct('UPDATECOLINE');
 Message_SYS.Add_Attribute(sql_msg_, 'SQL', stmt_);

 Client_SYS.Clear_Attr(attr_);
 Client_SYS.Add_To_Attr('SQL_DATA_', sql_msg_, attr_);
 Client_SYS.Add_To_Attr('MSG_', '', attr_);

 Transaction_SYS.Deferred_Call(
 'Fnd_Event_Action_API.Action_Executeonlinesql',
 'PARAMETER',
 attr_,
 Language_SYS.Translate_Constant('Event', 'NOTRANS: Event - Name of Event - Modify CO line &NEW:ORDER_NO - &NEW:LINE_NO - &NEW:REL_NO - &NEW:LINE_ITEM_NO', NULL, ));
END;

That is a cool procedure! It does the job.

Does it use the message system somehow?

And what is the function of Message_SYS.Construct('UPDATECOLINE'); ?

It seams like it can be set to what ever you like.

BR Kresten

The use of Message_SYS package is only because the string parameter SQL_DATA_ to Fnd_Event_Action_API.Action_Executeonlinesql is supposed to be in a certain “Message Format”. It is similar to the attribute strings you use in normal LU’s.

The name in the Construct method can, as you say, be anything and does not have a meaning when used like this.

This is a great procedure I use it quite frequently with custom logical units. I have one issue that I cannot get around.

When doing an update on a custom logical unit using custom the logical package like this:… TI_FR_DATASHEET_CLP.MODIFY__(info_, objkey_, objversion_, inside_attr_ , action_);

I get this error:

“ object has been modified by another user. Please refresh the object and reenter your changes. “

I use the example from solution above with several event actions using CLP.NEW__ procedures but so far, for me, the same cannot be done for the CLP.MODIFY__

DECLARE
    attr_ varchar2(32676);
    sql_msg_ varchar(32676);
    stmt_ varchar(32676);
    queue_id_ number := 10;

BEGIN
	stmt_ := '
DECLARE
	inside_attr_ varchar2(32676);   
	info_ varchar2(32676);
	objkey_ varchar2(32676) := ''&NEW:ROWKEY'';
	objversion_ varchar2(32676) := ''&NEW:ROWVERSION'';
	action_ varchar2(10) := ''DO'';

	BEGIN

		CLIENT_SYS.Clear_Attr(inside_attr_);
		CLIENT_SYS.Add_To_Attr(''CF$_LAST_ACTIVITY'' , sysdate, inside_attr_);

		TI_FR_DATASHEET_CLP.MODIFY__(info_, objkey_, objversion_, inside_attr_ , action_);


		exception
		when others then
		Error_SYS.Record_General(''TI_OOPSIE'','' FR Datasheet Not Updated '' || sqlerrm);

	END;
	';

	sql_msg_ := Message_SYS.Construct('UPD');
	Message_SYS.Add_Attribute(sql_msg_, 'SQL', stmt_);
	CLIENT_SYS.Clear_Attr(attr_);
	CLIENT_SYS.Add_To_Attr('SQL_DATA_', sql_msg_, attr_);
	CLIENT_SYS.Add_To_Attr('MSG_', '', attr_);
	TRANSACTION_SYS.Deferred_Call('Fnd_Event_Action_API.Action_Executeonlinesql', 'PARAMETER', attr_, 'Modify FR Datasheet', sysdate, 'FALSE', queue_id_);

END;

The same error is the same if I monitor the same view I am updating or triggered by another view not directly related to the table I’m trying to update.

I know I can release it in our main database package, but I don’t always want to do that, if possible. Most of the heavy lifting can be done without needing to, but smaller modify blocks don’t seem to follow the same logic

This is probably due to the format of your objversion_ variable. If that is not exactly the same as in the database it will fail.

If you look in your view TI_FR_DATASHEET_CLV you see that OBJVERSION is coming from DATE column ROWVERSION but formatted to a string with a certain mask:

to_char(rowversion,'YYYYMMDDHH24MISS') objversion

So you need to use the same code to get the OBJVERSION:

objversion_ varchar2(32676) := to_char(''&NEW:ROWVERSION'',''YYYYMMDDHH24MISS'');

I finally got it to work.

If I just used the solution from @Tomas Ruderfelt . I get a conversion error (“numeric or value error: character to number conversion error”). I tried changing the Events Attribute data types to (string, datetime ) they gave the same error when looking through the arguments on the background job

The ROWVERSION  format being read by the event actions is like this: ‘2023-03-21 11.26.17’

When  the datatype is changed to number it is: ‘2023-03-21-11.26.17’

Tomas was absolute right that this was the issue, however in my case the only way I was able to convert this without a value error was to first take it to_date then to_char

Final solution for Modify Custom Logical Units in a background job:

objversion_ varchar2(32676) :=
TO_CHAR(
	TO_DATE(''&NEW:ROWVERSION'', ''YYYY-MM-DD-HH24.MI.SS''
),''YYYYMMDDHH24MISS'');

Just a note the (string, datetime, and number) in the Event Attributes all work the same without needing to change this solution