11i to R12 / CE: Bank Statement Line Errors FRM-40654, Work Around, Afchrchk.Sql (Doc ID 758964.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Cash Management - Version 11.5.10.2 to 12.2.4 [Release 11.5 to 12.2]
Oracle Receivables - Version 11.5.10.2 to 12.2.4 [Release 11.5.10 to 12.2]
Information in this document applies to any platform.
Information in this document applies to any platform.

Goal

Q1. Why is the "Bad Character" left out in the script afchrchk.sql when Run for Bank Statement Data file in Cash Management?

Bank Data Feed has Hex 00 (NULL) or CHR(0) values in the data. Using afchrchk.sql script does not
find or fix these as CHR(0) was specifically left out. Will adding CHR(0) cause any issues in the
afchrchk.sql script?
Seems the Bank Interface data included the CHR(0) "NULL" in the TRX_TEXT field.

Tested both patches below and still had an issue.
<Patch:6157160> - 1OFF:5714795:CE.K:11I.FIN_PF.G:11.5.10.2:RECONCILIATION ERROR
FRM-40654 REQUERY

<Patch:6901144> - 1OFF:6883057:11IFIN_PF.G:11.5.10.2:BANK STATEMENT LOADER DOES NOT KICK OFF IMPOR

Found using DUMP(TRX_TEXT) field that the NULL CHR(0) characters were there. The Patches did not remove these characters and we ran the AFCHRCHK.SQL script to correct the records already loaded. Need to check with development on this script $FND_TOP/sql/afchrchk.sql

Details
---------
Modify the script to include CHR(0) in the list of badchars. When using DUMP(TRX_TEXT) field that the NULL CHR(0) characters were there.

Have modified the script and included CHR(0) in badchars list, ran the script on TRX_TEXT column,and then were able to modify the records in Cash Management form.
Need to have development review and approve of the changes and release a patch for the latest code.

Q2. WORKAROUNDS: Modify the script and add the Bad Character into it.

Q3: Unable to Reconcile Receipts and Payments in Cash Management due to Locks. Shows message "FRM-40654: Record has been updated. Requery block to see change"

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms