How to find right values for API parameters

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 everybody,

thank you a lot for supporting. Now it works perfectly. 

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

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).

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.

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

@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. :)

Don’t worry @hhy38. I know. And is already deactivated.  

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. :-)