The Base Cause of ” FRM-40654 “

The Base Cause of ” FRM-40654 “

http://www.oracleappshub.com/category/ebs-suite/page/2/

Very common problem your end user normally reports “FRM-40654: Record has been updated by another user. Re-query to see change“
…

Cause: Another user has updated this record since you performed a query and has changed at least one field in the record. Your actions have not changed the record in memory.

Action: You can update or delete this record now only if another user has restored the field values back to the way they were when you performed the query. Otherwise, you must re-query to fetch and display the new record into the form. before you can update or delete it.

This is very common issue after migration or with any external interfaces, If you have taken care by TRIM function , then there is nothing to bother, else your end user will report this, which need a proper investigation.

The reason and resolutions

This error is caused because of any of the following

1. FRM-40654 Caused by unnecessary foreign key fields
2. Trapping problem of some sort in Oracle Forms with triggers, execute_query
3. Record locked ie Another user changed the record and saved after you queried the record
4. Some of the columns have trailing spaces, and Forms by default truncates the trailing spaces and hence the database value and form. value will not match and you get the error.
5. Date field stored in database is with time stamp but form. showing only Date component and hence Form. value and database value will not match and hence you get the error. (you need to remove the time component in DB or show date time in Form)

Bottom line is the value in Database and value in form. are not matching, so you have to check for each column and see where is the difference. I know this is going to be painful checking out every field for changes, but there is no other options left.

What you have to do is to investigate the column first and then make a update with TRIM function as

update

set = trim();

Resolutions Example

Example 1

User reported the problem at suppliers site,

Taking which table hold the supplier data. Check a column value for a VARCHAR2 column. In this case VENDOR_SITE_CODE. or Address_line1
SELECT ‘['||vendor_site_code||']‘
FROM po_vendor_sites_all
WHERE vendor_id=; If the column has trailing spaces the output will looks very similar to

[SITE ]

Once you get the column , then use update script, with TRIM function, like

UPDATE po_vendor_sites
SET vendor_site_code = TRIM(vendor_site_code)
WHERE vendor_id = ;

Example 2 : Your bank statement Reconcile program ending up with error

In similar way you have to check like

1. select ‘['||trx_text||']‘ from ce_statement_lines_interface;
2. select ‘['||trx_text||']‘ from ce_statement_lines;

The above mentioned scripts need to be run against all varchar2 columns, not just trx_text, in either ce_statement_lines or ce_statement_lines_interface.

If trailing spaces are found, run the following scripts to correct them:

update ce_statement_lines_interface
set trx_text=trim(trx_text,’ ‘)

Change the column and table name to those with trailing spaces

Example 3:Invoice Through Auto invoice (External system)

Same way to find the column and apply by this kind of script
update ra_customer_trx_lines_all
set DESCRIPTION = trim(DESCRIPTION) ,
SALES_ORDER = trim(SALES_ORDER)
where customer_trx_id = ;

What if , not have “trailing space” Problem

Confirm with DBA , is table is having any lock or not . If there is no lock then you need to ask Oracle support .

Tips of Developing Conversion and Interface

Always use TRIM function in your insert portion of the program

Further Reading on metalink

246971.1 :FRM-40654 When Modifying Records 1003629.6 Troubleshooting FRM-40654

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15225049/viewspace-630961/，如需转载，请注明出处，否则将追究法律责任。

0

0

分享到：

上一篇： Native Dynamic SQL and Dynamic PL/SQL(6)

下一篇： Troubleshooting FRM-40654 [ID 1003629.6]

请登录后发表评论 登录

全部评论

<%=items[i].createtime%>

<%=items[i].content%>

<%if(items[i].items.items.length) { %>

<%for(var j=0;j

<%=items[i].items.items[j].createtime%> 回复

<%=items[i].items.items[j].username%>   回复   <%=items[i].items.items[j].tousername%>： <%=items[i].items.items[j].content%>

<%}%> <%if(items[i].items.total > 5) { %>

还有<%=items[i].items.total-5%>条评论) data-count=1 data-flag=true>点击查看

<%}%>

<%}%> <%}%>

susie0409

注册时间：2009-09-30

博文量

115

访问量

265649

最新文章

Project Cost Transactions Are Not Being Transferred To Project Costing website PO AP FA 采购到固定资产的疑问 Re-Open a Closed Inventory Accounting Period consign and vmi Create supplier in PO (R12) expense item Oracle Applications Global Accounting Engine po receive account CSTPACIN.COST_INV_TXN

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号：11010802021510

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

转载于:http://blog.itpub.net/15225049/viewspace-630961/