Solved

How to find right values for API parameters

  • 25 September 2023
  • 27 replies
  • 409 views

Userlevel 7
Badge +22
  • Superhero (Customer)
  • 1015 replies

Dear experts,

i need to update some columns on the screen “Incoming Customer Order”.

And I use this API: EXTERNAL_CUST_ORDER_LINE_API.MODIFY__(info_ , objid_, objversion_, attr_, action_)

How can I set the parameters “info” and “objid”?

What values does it expect here?

I can’t find them in the table “EXTERNAL_CUST_ORDER_LINE_TAB”

icon

Best answer by hhy38 2 October 2023, 10:26

View original

27 replies

Userlevel 7
Badge +19

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.

Userlevel 7
Badge +22

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.

Userlevel 7
Badge +19

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

Userlevel 7
Badge +22

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

 

Userlevel 2
Badge +9

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

….

Userlevel 7
Badge +22

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

 

 

Userlevel 2
Badge +9

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

Userlevel 5
Badge +15

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;

 

 
Userlevel 5
Badge +15

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

 

 

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.

 
Userlevel 7
Badge +22

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

Userlevel 5
Badge +15

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

 

Userlevel 7
Badge +22

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?

Userlevel 5
Badge +15

@Link Hi,

 

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

Userlevel 7
Badge +22

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;

Userlevel 5
Badge +15

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

 

Userlevel 7
Badge +22

Hi @hhy38 

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

Userlevel 2
Badge +9

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
Userlevel 5
Badge +15

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;

 

 
Userlevel 7
Badge +22

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.

Userlevel 2
Badge +9

@Link 

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

Userlevel 5
Badge +15

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

 

 

 

 

Userlevel 7
Badge +22

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

Userlevel 7
Badge +22

@Link

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

Thank you @romsar I will test this process also.

Userlevel 5
Badge +15

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

Userlevel 5
Badge +15

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

Reply