The info_ parameter is only an OUT parameter meaning that it only returns a value so it should not have any value when you call it, make it 2000 characters long.

The objid_ parameter is the ROWID from the table, the unique identifier of the record you want to modify.

Hi @Tomas Ruderfelt 

that means info_ can be NULL?

But why doesn’t the column ROWID in the table “EXTERNAL_CUST_ORDER_LINE_TAB” exist?

Thank you.

Yes, you do not need to initialize the variable info_, just declare it like: info_ VARCHAR2(2000);

All tables have a ROWID pseudocolumn which is not shown when you describe it, try to do a query like this:

SELECT rowid

FROM external_cust_order_line_tab

More info from Oracle SQL documentation about ROWID:

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWID-Pseudocolumn.html#GUID-F6E0FBD2-983C-495D-9856-5E113A17FAF1

Hi @Tomas Ruderfelt 

thank you again for sharing your knowledge. You’re right.

How can I set the attribute?

EXTERNAL_CUST_ORDER_LINE_API.MODIFY__(info_ , objid_, objversion_, attr_, action_)

The debug console shows me a combination of the column name and the value (75).

Hello @Link 

attr_ VARCHAR2(32000);

Client_SYS.Clear_Attr(attr_); -- same as attr_:='';

Client_SYS.Add_To_Attr('ORDER_NO', '1234' , attr_); --on here, ORDER_NO is an SQL column and '1234' is the value

Client_SYS.Add_To_Attr('CONTRACT', 'ABC' , attr_);

….

Hi @romsar 

hi @Tomas Ruderfelt 

thank you a lot for support. I will test this approach. 

Client_SYS.Clear_Attr(attr_);

Client_SYS.Add_To_Attr('ORDER_NO', '1234' , attr_); 

Client_SYS.Clear_Attr(attr_); Do I need to use this again, or just once  at the beginning? 

Client_SYS.Add_To_Attr('CONTRACT', 'ABC' , attr_);

Hello @Link 

Be carreful, as metionned in my previous post as a comment, 

Client_SYS.Clear_Attr(attr_);

is here to erase the content of attr_, so it’s same as if you do

attr_ := ‘’ ;

Hi @Link 

To modify a record. You must know the objid and the obversion of the record. You can get this information from view. After that, fill the attr with columns and values. You can see an example below.

DECLARE
  	info_       VARCHAR2(32000);
	objid_      VARCHAR2(32000);
	objversion_ VARCHAR2(32000);
	attr_       VARCHAR2(32000);
BEGIN

	SELECT t.objid,
		   t.objversion
	INTO   objid_,
		   objversion_
	FROM   fnd_user t
	WHERE  t.identity = 'DUMMY_USER';

	client_sys.clear_attr(attr_);
	client_sys.add_to_attr('ACTIVE', 'FALSE', attr_); 
	fnd_user_api.modify__(info_, objid_, objversion_, attr_, 'DO');

END;

Just once at the beginning is going to be enough. If you clear again, you will lose the info before. However, If you change multiple records. You need the clear the attr.

Hi @hhy38 

it is a nice example.

client_sys.add_to_attr('ACTIVE', 'FALSE', attr_); 

For what purpose do I need this method?

What do the first and second parameters mean?

Just once at the beginning is going to be enough. If you clear again, you will lose the info before. However, If you change multiple records. You need the clear the attr.

My goal is to change multiple records. That means I need to clear the attr again.

Many thanks for the valuable information. :-)

@Link 

You are welcome

Let me explain simply. Attr is a varchar variable. It is used to send the data to the database. We fill the attr with column values with the add_to_attr procedure. After sending this. The APIs unpack the attr and insert it into tables.

The first parameter is the column in the table that you want to update.

The second parameter is the new value of the column. 

If you want to change multiple records. You should use a loop. There is an example below.

DECLARE
	info_       VARCHAR2(32000);
	objid_      VARCHAR2(32000);
	objversion_ VARCHAR2(32000);
	attr_       VARCHAR2(32000);
BEGIN
	FOR rec_ IN (SELECT t.objid,
						t.objversion
				 FROM   fnd_user t
				 WHERE  t.identity IN ('username1','username2','username3'))
	LOOP
		client_sys.clear_attr(attr_);
		client_sys.add_to_attr('ACTIVE', 'FALSE', attr_);
		fnd_user_api.modify__(info_, rec_.objid, rec_.objversion, attr_, 'DO');
	END LOOP;
END;

Hi guys,

many thanks for helping. The code works in PLSQL. But in IFS I get the following error message:

My code:

Why is the record locked?

@Link Hi,

Can you the plsql block? Writing from screenshots is hard. :)

Hi @hhy38 

sure.

DECLARE 

PRAGMA AUTONOMOUS_TRANSACTION;

  attr_                            varchar2(800);

  v_buy_qty_due           number(8,2) := (('&NEW:BUY_QTY_DUE' * '&LENGTH') / 1000);

  v_customer_quantity  number(8,2) := (('&NEW:CUSTOMER_QUANTITY' * '&LENGTH') / 1000);

  v_objversion               varchar2(100); 

  v_rowid                       varchar2(100); 

  info_                            varchar2(2000);

  

BEGIN

  info_ := NULL;

  attr_ := 0;

  

    SELECT objid, objversion

    INTO v_rowid, v_objversion 

    FROM IFSAPP.EXTERNAL_CUST_ORDER_LINE_ALL 

     WHERE message_id = '&NEW:MESSAGE_ID' 

     AND catalog_no = '&NEW:CATALOG_NO' 

     AND line_no = '&NEW:LINE_NO'; 

  

  ifsapp.Client_SYS.Clear_Attr(attr_);   

  ifsapp.Client_SYS.Add_To_Attr(name_  => 'BUY_QTY_DUE', value_ => v_buy_qty_due, attr_  => attr_);    

  IFSAPP.EXTERNAL_CUST_ORDER_LINE_API.MODIFY__(info_, v_rowid, v_objversion, attr_, 'DO');

  ifsapp.Client_SYS.Clear_Attr(attr_);   

  ifsapp.Client_SYS.Add_To_Attr(name_  => 'CUSTOMER_QUANTITY', value_ =>  v_customer_quantity, attr_  => attr_);    

 IFSAPP.EXTERNAL_CUST_ORDER_LINE_API.MODIFY__(info_, v_rowid, v_objversion, attr_, 'DO');

EXCEPTION

  WHEN IFSAPP.Error_SYS.Err_Security_Checkpoint THEN

    raise;

  WHEN OTHERS THEN

    rollback;

    raise;

COMMIT;

END;

@Link Can you try as below? If there is be mutation error. Then write “PRAGMA AUTONOMOUS_TRANSACTION;” otherwise you don't need it.

DECLARE
	attr_               VARCHAR2(800);
	v_buy_qty_due       NUMBER(8, 2) := (('&NEW:BUY_QTY_DUE' * '&LENGTH') / 1000);
	v_customer_quantity NUMBER(8, 2) := (('&NEW:CUSTOMER_QUANTITY' * '&LENGTH') / 1000);
	v_objversion        VARCHAR2(100);
	v_rowid             VARCHAR2(100);
	info_               VARCHAR2(2000);
BEGIN
	SELECT objid,
		   objversion
	INTO   v_rowid,
		   v_objversion
	FROM   ifsapp.external_cust_order_line_all
	WHERE  message_id = '&NEW:MESSAGE_ID'
		   AND catalog_no = '&NEW:CATALOG_NO'
		   AND line_no = '&NEW:LINE_NO';
	ifsapp.client_sys.clear_attr(attr_);
	ifsapp.client_sys.add_to_attr(name_ => 'BUY_QTY_DUE', value_ => v_buy_qty_due, attr_ => attr_);
	ifsapp.client_sys.add_to_attr(name_ => 'CUSTOMER_QUANTITY', value_ => v_customer_quantity, attr_ => attr_);
	ifsapp.external_cust_order_line_api.modify__(info_, v_rowid, v_objversion, attr_, 'DO');
END;

Hi @hhy38 

thank you for the new code but the same error message, unfortunately.

Heya.

I guess it’s better to make a deferred call in that case.

DECLARE

 Attr_cf_ VARCHAR2(32000);

BEGIN

    Client_SYS.Clear_Attr(Attr_cf_);

    Client_SYS.Add_To_Attr('MESSAGE_ID_', '&NEW:MESSAGE_ID' ,Attr_cf_);

    Client_SYS.Add_To_Attr('CATALOG_NO_' ,'&NEW:CATALOG_NO' ,Attr_cf_);

    Client_SYS.Add_To_Attr('LINE_NO_' ,   '&NEW:LINE_NO',Attr_cf_);

….

transaction_sys.deferred_call('MY_CUSTOM_PACKAGE_API.MY_PROCEDURE_NAME','PARAMETER',Attr_cf_ ,'A comment about the goal of my cutom procedure');

END;

1. Parameter 1 : your package name, then, your procedure name
2. Parameter 2 : no idea what it is about, I simply left PARAMETER
3. Parameter 3 : Attr_cf_
4. Parameter 4 : a message which describes the goal of your custom procedure

@Link You should send this task as a background job. There is a template below.

DECLARE
	attr_    VARCHAR2(32000);
	sql_msg_ VARCHAR2(32000);
	stmt_    VARCHAR2(32000);
	job_id_  NUMBER;
BEGIN
	stmt_    := '
BEGIN


END;';
	sql_msg_ := ifsapp.message_sys.construct('UPD');
	ifsapp.message_sys.add_attribute(sql_msg_, 'SQL', stmt_);
	ifsapp.client_sys.clear_attr(attr_);
	ifsapp.client_sys.add_to_attr('SQL_DATA_', sql_msg_, attr_);
	ifsapp.client_sys.add_to_attr('MSG_', '', attr_);
	ifsapp.transaction_sys.deferred_call(job_id_, 'Fnd_Event_Action_API.Action_Executeonlinesql', 'PARAMETER', attr_, 'Geri plan Tanımı');
END;

And for your scenario. You can use it like this.

DECLARE
	attr_    VARCHAR2(32000);
	sql_msg_ VARCHAR2(32000);
	stmt_    VARCHAR2(32000);
	job_id_  NUMBER;
BEGIN
	stmt_    := '
DECLARE
    attr_               VARCHAR2(800);
    v_buy_qty_due       NUMBER(8, 2) := ((''&NEW:BUY_QTY_DUE'' * ''&LENGTH'') / 1000);
    v_customer_quantity NUMBER(8, 2) := ((''&NEW:CUSTOMER_QUANTITY'' * ''&LENGTH'') / 1000);
    v_objversion        VARCHAR2(100);
    v_rowid             VARCHAR2(100);
    info_               VARCHAR2(2000);
BEGIN
    SELECT objid,
           objversion
    INTO   v_rowid,
           v_objversion
    FROM   ifsapp.external_cust_order_line_all
    WHERE  message_id = ''&NEW:MESSAGE_ID''
           AND catalog_no = ''&NEW:CATALOG_NO''
           AND line_no = ''&NEW:LINE_NO'';
    ifsapp.client_sys.clear_attr(attr_);
    ifsapp.client_sys.add_to_attr(name_ => ''BUY_QTY_DUE'', value_ => v_buy_qty_due, attr_ => attr_);
    ifsapp.client_sys.add_to_attr(name_ => ''CUSTOMER_QUANTITY'', value_ => v_customer_quantity, attr_ => attr_);
    ifsapp.external_cust_order_line_api.modify__(info_, v_rowid, v_objversion, attr_, ''DO'');
END;
';
	sql_msg_ := ifsapp.message_sys.construct('UPD');
	ifsapp.message_sys.add_attribute(sql_msg_, 'SQL', stmt_);
	ifsapp.client_sys.clear_attr(attr_);
	ifsapp.client_sys.add_to_attr('SQL_DATA_', sql_msg_, attr_);
	ifsapp.client_sys.add_to_attr('MSG_', '', attr_);
	ifsapp.transaction_sys.deferred_call(job_id_, 'Fnd_Event_Action_API.Action_Executeonlinesql', 'PARAMETER', attr_, 'Geri plan Tanımı');
END;

Hi @hhy38 

how can I send the above code as a background job?

I’ve intergrated the second code in my event. I don’t get any error messages but the values didn’t change.

@Link 

I explained everything in my post, you just have to transfer your code into your custom procedure

You already sent it with this code. If there is no change. There should be an error in background jobs. You can search with description. And check the error text.

ifsapp.transaction_sys.deferred_call(job_id_, 'Fnd_Event_Action_API.Action_Executeonlinesql', 'PARAMETER', attr_, 'Geri plan Tanımı');

You already sent it with this code. If there is no change. There should be an error in background jobs. You can search with description. And check the error text.

ifsapp.transaction_sys.deferred_call(job_id_, 'Fnd_Event_Action_API.Action_Executeonlinesql', 'PARAMETER', attr_, 'Geri plan Tanımı');

 

 

 

 

OK, I got it. But now I have > 100 jobs. Every second. And it calculate the value n times until zero. :-)

Thank you @romsar I will test this process also.

@Link Please disable the Event. After that, check one of them. This is an important point. If there are a lot of jobs in the background. Your system can slow down or even crash.

@Link You should do this in your test environment. Please don't do it in your production environment. :)