How to create a defered job to execute .modify__() ?

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

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

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

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

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