Oracle ERP User Network Collaborate with Professionals working on Oracle ERP(Oracle Apps) including EBusiness Suite (R12, 11i), Fusion, Peoplesoft, JDE, Siebel, Hyperion, OBIEE, SOA etc.

Wendy I hope you have setup all the valid and possible bank transations codes in Cash Management.

Normally Bank transaction code you should have Issues (AP Cheques), Receipts (AR Receipts), also any misc issues like bank charges etc... or misc receipts like interest earn etc...

Also see if your deposit information coming from Bank is grouped or not (one line for all deposits made on a perticular day)

Bobby

Hi Wendy,

My experience was that AP transactions generally matched automatically - because the reference on the payment is what appears on the bank statement (assuming document numbering is set up and used correctly). However, the same could not be said for AR. It was rare to know what the bank statement reference was going to be at time of transaction entry so the receipts would not automatically reconcile.

Regards

Colin 

Fortunately, CM Module require pretty small amount of settings to be done.

Basically, you have to set the bank codes, check digits and have all dates on your transactions be in the same format.

The CTL file format is very important, for exsample, i use custom CTL file, because our bank has strange policy regarding their output file.

1.Upload the file to CE Interface tables (I use headers and lines Interface tables) using SQL*Loader or Concurrent Program from CM menu.

2.Open the file in CM's "Bank Statement Interface", veify all required fields are populated.

3.Run Concurrent Program "Bank Statement Import". If you receive OK report, then run the next Concurrent Program "AutoReconciliation".

4.Optional, after all these steps run "Archive/Purge Bank Statements" to clean/archive the uploaded bank file.

Regards,

Boris

Thanks for your replies. To add more detail... I have created a custom loading program that takes the lines downloaded from the bank and finds the matching transaction in GL or AR, then inserts into headers and lines interface tables updating the bank_trx_number to the JE Line Description for a GL match, or the Receipt Number for an AR match (no update is required for AP).

The seeded Import and Auto Reconciliation programs run fine and properly import the data and automatically match AP transactions. However the issues I am encountering are;

1. Journal entry lines with the same line description will not automatically reconcile. The system does not use amount for matching criteria but instead line description alone. Therefore my lines are considered duplicates. I have confirmed with Oracle Support that this is intended functionality. An enhancement request has been logged since 2006 but there has been no progress.

My workaround for this issue is to educate the users to ensure each JE line has a distinct description. Have any of you encountered this issue and how did you handle it?

2. The bank assigns transaction codes to each type of transaction that is downloaded. These codes are setup in Oracle for each bank account in AP and assigned a Trans Source; AP, AR, or GL.

I have several cases where a group of transactions downloaded from the bank are assigned the same code (i.e. 495), but in Oracle some of these transactions are in AP and some are in GL. So when I create the 495 code in Oracle and assign it a Trans Source of AP, none of the GL transactions are matched (because the system is looking for them in AP and they are not found).

Oracle setup will not allow duplicate transaction codes to be entered (one for AP and one for GL).

The only workaround I can come up with is to change our business process based on the transaction codes assigned by the bank and enter all 495 transactions through AP (for example), but this is obviously not a desired recommendation to make.

Has anyone encountered this issue and found a way around it?

3. I am considering having the reconciliation program create miscellaneous transactions such as bank fees upon the import and reconciliation of these. Has anyone implemented this? Are there any best practices and/or gotchas I should be aware of?

Thanks for your help
Wendy

Wendy, Did you get any solution as how to auto reconcile journal entries. We also get into a situation where we need to automatically reconcile journal entries with Cash statement.

Yes, my program is working beautifully now, reconciling all transactions where entered correctly and also generating miscellaneous transactions for bank charges.

For journal entries the line description must be unique, and you must update the bank_trx_number in the interface table to be the JE line description prior to importing and auto reconciling for a match to occur.

Hi Wendy,

I know it's been 2 years since you last worked on this but I was wondering if you had to reconcile any WIRE payment or credit type transactions like code 165-wire credit, 508-transfer debit.  Also, how did you handle the batch transactions that came from the bank like batch of AP expenses, since bank recon match by AP invoice number for AP.

Thanks in advance for any recommendations.

Kimberly

Hi Kimberly,
If you do not want to programm, please consider the APRO Banking Gateway. This easy to use product processes the electronic bank statements including recognition of payment batches, individual payments, miscellaenous payments like bank charges, miscellaeneous receipts like interest and also Customer Invoices/Consolidated Billing number/etc.. Next it automatically populates the required Oracle Open Interfaces starts the Oracle Interface processes. For example APRO sends the records to Oracle Cash Management Open Interface (and Oracle AR Lockbox Interface if you also have electronic payments of received from your customer). But also the interfaces of Oracle Cash Management and General Ledger can be used. Off course you can use the APRO Banking Gateway with Oracle EBS r11i and r12.

If you need more information please do not hesitate to ask.

Best regards,

Willem

I am working on R12.1.1 Would appreciate knowing if anybody has done the manual bank reconciliation. What are the reports to be fired for knowing the exceptions and getting a reconciliation output.

Parasuraman

9566182814