Skip to main content

Hi guys,

I am trying to update the credit limit on screen Customer with a sql event and get the following error message:

 ORA-20115 - The "Customer Info" has already been removed by another user. 

Can anybody help?

How can I avoid it?

@Link could you post the PL/SQL code block? I think this would be useful in order to provide any guidance.


Hi Link,

 

usually, this removed by user error comes if it's unable to find the record correctly. please check your  update code is passed the keys correctly and not empty.

 

Best Regards,

Wasana


Dear @Marcel.Ausan 

below my SQL code:

DECLARE
attr_ VARCHAR2(3000);
sql_msg_ VARCHAR2(3000);
stmt_ VARCHAR2(6000);
job_id_ NUMBER;

BEGIN
stmt_ := '

DECLARE
attr_ VARCHAR2(3000);
info_ varchar2(2000) := NULL;
v_objversion varchar2(100);
v_rowid varchar2(100);

CURSOR get_all_credit_limits IS
SELECT cc.company, cc.identity, cc.party_type_db, cc.credit_limit, ci.CF$_C_CR_LIMIT
FROM ifsapp.CUSTOMER_CREDIT_INFO_CUST cc
left join ifsapp.customer_info_cfv ci
on cc.identity = ci.CUSTOMER_ID
WHERE cc.identity = ''4567''
and cc.company in (''COMP1'', ''COMP2'');

BEGIN
FOR record IN get_all_credit_limits
LOOP
ifsapp.Client_SYS.Clear_Attr(attr_);
ifsapp.Client_SYS.Add_To_Attr(name_ => ''CREDIT_LIMIT'', value_ => record.CF$_C_CR_LIMIT, attr_ => attr_);
IFSAPP.CUSTOMER_INFO_API.MODIFY__(info_, v_rowid, v_objversion, attr_, ''DO'');
END LOOP;

EXCEPTION
WHEN IFSAPP.Error_SYS.Err_Security_Checkpoint THEN
raise;
WHEN OTHERS THEN
rollback;
raise;
END;';

sql_msg_ := ifsapp.Message_SYS.Construct('UPDATE_CREDIT_LIMIT');
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_, 'CREDIT LIMIT Event');
END;

 


Hi @Link,

 

You will encounter such errors when the record does not exist or has changed(objversion).

Customer information may have been removed or changed before your background job was running.

 

Best regards.

Barış


Dear @baris.halici 

but there are some records. Maybe my API parameters don't match.

I will check that again.

Thank you a lot.


Hi @Link 

 

Am I mistaken or have you forgotten to fetch the objid and objversion from the view since I couldn’t find it in the code 😉?

I think this would work.

 SELECT cc.company, cc.identity, cc.party_type_db, cc.credit_limit, ci.CF$_C_CR_LIMIT, ci.objid, ci.objversion
FROM ifsapp.CUSTOMER_CREDIT_INFO_CUST cc
left join ifsapp.customer_info_cfv ci
on cc.identity = ci.CUSTOMER_ID
WHERE cc.identity = ''4567''
and cc.company in (''COMP1'', ''COMP2'');

 

FOR record IN get_all_credit_limits
LOOP
v_rowid := record.objid;
v_objversion := record.objversion;
...
...
END LOOP;

 

Hope it helps!


Hi @dsj 

thank you for reviewing my code.

Now I get this error message:

 


@Link agree with @dsj. It seems you’re passing NULL values into objid and objversion parameters, thus the system thinks your record that you wanna modify doesn’t exist. Populating those 2 values as suggested by Damith should fix your problem.


Hi @dsj 

thank you for reviewing my code.

Now I get this error message:

 

What’s the triggering point for your event? Is it some changes done on the CUstomerInfo?

If so, I believe the objversion that you are passing to the Background Job has already been changed after the update that triggered the event has been commited to the DB. By the time your Background Job runs there will be a different objversion for that objid.


Hi @Marcel.Ausan 

I process it directly from PL/SQL developer.

Yes, I want to update the field credit limit on screen customer (from a custom field). 

 


@Link agree with @dsj. It seems you’re passing NULL values into objid and objversion parameters, thus the system thinks your record that you wanna modify doesn’t exist. Populating those 2 values as suggested by Damith should fix your problem.

I did that.

But the error message is a little bit different than before.


@Link I read the code again. There’s no CREDIT_LIMIT in CUSTOMER_INFO, therefore IFSAPP.CUSTOMER_INFO_API.MODIFY__ will not do anything. If you wanna update the CREDIT_LIMIT field in the Credit tab of the Customer than you would need to execute IFSAPP.CUSTOMER_CREDIT_INFO_API.MODIFY__.

I think you would also need to specify the company in the attr since this tab is company specific. Also please don’t forget to get the objid and objversion from CUSTOMER_CREDIT_INFO_CUST.


@Link I read the code again. There’s no CREDIT_LIMIT in CUSTOMER_INFO, therefore IFSAPP.CUSTOMER_INFO_API.MODIFY__ will not do anything. If you wanna update the CREDIT_LIMIT field in the Credit tab of the Customer than you would need to execute IFSAPP.CUSTOMER_CREDIT_INFO_API.MODIFY__.

I think you would also need to specify the company in the attr since this tab is company specific. Also please don’t forget to get the objid and objversion from CUSTOMER_CREDIT_INFO_CUST.

Of course, you’re right. It is IFSAPP.CUSTOMER_CREDIT_INFO_API.MODIFY__. I was using the wrong API by mistake, sorry guys.

How do you use copany in attr?


@Link just add cc.company in your cursor and then add the value to attr_.


@Link just add cc.company in your cursor and then add the value to attr_.

Hi @Marcel.Ausan 

thank you a lo. I think it isn’t necessary. Now it works. 😊

Have a nice weekend.


Perfect @Link . Glad you managed to get this working.


Reply