We seem to have electronic bank statement reconciliation working pretty well. We are getting automatic clearing for all of our checks and wires but we are having problems with clearing of ACH payments.
The problem is that when A/P sends the payment files (IDOC) to the bank (BOA), the files can have 100 or more individual vendor ACH payment items on them but the bank only sends a single debit transaction on the bank statement for the total amount in the file. Automatic reconciliation does not take place so we are stuck every time manually matching and clearing the single bank statement item up against the 100 or so open payment items from that payment run.
Please advice how to implement this in SAP?
You can try putting a common batch number or something in the Assignment field of the payment lines for ACH payments. Later when you receive the Bank statement, you can add the same for that bank statement line (totalled item). After this you can clear the items with F.13 automatically based on Batch number in the Assignment field.
We have a similar scenario.
Our solution was to add logic to the preprocessor program that we run all EBS files through before they are processed by FF.5. The program queries REGUT for the amount of the ACH transaction in the bank statement. It then adds the RENUM value in to the reference field on the bank statement transaction. We have the external transaction mapped in the EBS config with interpretation algorithm 019: Reference no. (DME Management). The algorithm uses the reference number from the transaction to find all the individual payment documents to clear.
If you can find a way to get the RENUM value sent with the ACH files such that your bank returns the value in the reference field or the note to payee of the bank statement, then you could use the 019 algorithm without the custom code. (If the value is in the note to payee, you may need a search string to extract it).
Another option would be to use the EBS user exit - enhancement FEB00001, function module EXIT_RFEBBU10_001, include ZXF01U01. In that user exit, you can use your own logic to find line items to be cleared by a transaction and add them to table T_FEBCL. For example, you could query REGUT based on amount and get the run date and ID for the payment run. Then query REGUH for the individual document numbers. Add the documents into T_FEBCL and SAP will clear them with the bank statement transaction.
Hope this helps!
Edited by: Shannon Moberg on Aug 24, 2010 8:39 AM
Thanks for your reply. If this can be done without any user exit or programming, then it will be nice.
Based on your reply on option 2:
"If you can find a way to get the RENUM value sent with the ACH files such that your bank returns the value in the reference field or the note to payee of the bank statement, then you could use the 019 algorithm without the custom code. (If the value is in the note to payee, you may need a search string to extract it)."
I actually did sent the RENUM value in IDOC ACH Payment to BOA. You say that asking the bank to returns the RENUM value in reference field. What do you mean by reference field? So the reference field you referring to is inside the PDR or Entry Detail List Report?
In our system right now, the bank (BOA) returns us the PDR (Previous Day Report) file. The PDR shows the previous day debits and credits - ACH debit would be bulk debit on the statement. Our FF.5 currently is reading the PDR file in BAI format. And it is not working for ACH Payments because the bank only sends a single debit transaction on the bank statement for the total amount in the file.
BOA said they also have Entry Detail List Report which have the detail individual ACH transactions. Bank say they can produce the Entry Detail List Report in NACHA or BAI format. Do you hear the Entry Detail List Report before? Not sure will this help us?
Entry Detail List Report from BOA:
CR01230 DIRECT BANK OF AMERICA, N.A. PAGE 1
SITE: CA ACH SERVICES DATE/TIME: 08/19/10 14:54:00
ENTRY DETAIL LIST ** CONFIDENTIAL **
RTN: 122000030 ACCT #: 00000008765100676
POINT: EEORIGIN1 ORIGIN ID: 9003005399 ORIGIN NAME: EDI-ECS-ORIGINATIONS FILE ID MOD: G
APPL: XXXXXX3 CO ID: 3770016691 CO NAME: XXXXXX CORPORATI ENTRY DESC: CORP PYMNT SEC: PPD EFF DATE: 08/19/10
TYPE: FILE #: 10231002004 BATCH #: 0000077 CO DISC DATA:
TRACE NUMBER TC RTN ACCOUNT NUMBER AMOUNT INDIVIDUAL ID INDIVIDUAL NAME DESTINATION
071923280007084 22 011401533 3300293245 422.67 6000031384 XXDSDSD TRANSIT
071923280007085 22 021200025 1010060046129 753.99 6000031362 sfsdfsdf TRANSIT
071923280007086 22 011302603 100365485 325.57 6000031355 bvbvbvb
Hope to get a reply from you soon.
I would suggest editing your previous post to remove the confidential bank account numbers. As these posts are public, it is not a good idea to include real numbers in your posts. Always mask out things like bank accounts.
I'm guessing the "Entry Detail Report" is specific to BOA. We don't get EBS files from BOA, so I haven't worked with that report specifically. However, the BAI format that they can give you is an industry standard.
In the BAI format, the Reference field that I'm referring to is the Customer Reference field on the 16 (Transaction Detail) record. It is the 3rd field after the amount (6th field overall). If they don't put the RENUM in the Reference field, but do put it in the note to payee, 019 may still work. Depending on how they format the number, you may need a search string to extract and/or add leading zeros to get 019 to work. Search strings are standard configuration and do not require custom code.
If you have your bank switch to sending individual ACH transactions then you won't need the 019 algorithm or any custom code. SAP should be able to match the transactions in the bank statement based on amount to the postings in the ledger and clear that way.
I am not familiar with BOA's PDR report or Entry Detail report, so I cannot say if the Reference field is available in those. I can only say where it exists in the BAI format.
If the RENUM value is available in either the reference field (on the 16 record of the BAI) or in the note to payee - and it is formatted with the correct number of leading zeros, then algorithm 019 will automatically clear the payments associated with that RENUM.
When we implemented EBS, the RENUM was not coming through anywhere in the bank statement. Rather than make changes to the ACH send process to get the RENUM to come through on the bank statement, we decided to add logic to the EBS pre-processor program we were creating anyway for other reasons. Either way it was going to involve customization for us, so we decided to leave the existing ACH process alone and add the customization to the EBS pre-processor.
The pre-processor program reads the BAI file we receive from our bank, makes updates where needed and creates an output file (still in the BAI format) that is processed by FF.5. When the program reads a 16 record, it uses the value in the second field (BAI code) to determine the type of the transaction. If it is an ACH payment, then the program takes the amount from the 3rd field and divides by 100 (the BAI amount has an implied decimal). It also takes the statement date (from the 02 record) and determines the ACH issue date - 2 banking days prior (using a factory calendar we maintain with banking holidays). Then using the date and amount, the program queries REGUT. If it finds a match on REGUT, then it adds the RENUM into the customer reference field on the 16 record of the output file. If no match is found, then the reference field is left as is.
Note - I will not be emailing you directly as others may be interested in this subject and the information I can provide.
We work with BOA and they have the ability to report back each ACH payment on their BAI2 report. They call it 'itemized posting'. We also use Idoc's to send the payments to BOA. On their BAI2 files, they report the payment document number for clearing purposes.
Hi Vishal - We have itemized posting from BOA and is receiving the payment reference number back in the BAI statements. Is there any system changes (configuration or ABAP) that we have to do to process this statement using the payment document number instead of checks. We have standard EBS setup and processing so let me know if there is anything else we need to do for this.