How to find right values for API parameters

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.

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

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_);

….

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_ := ‘’ ;

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.

@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;

@Link Hi,

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

@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;

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

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.

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ı');

Thank you @romsar I will test this process also.

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