The accounting black hole nobody wants to face
In most companies running SAP Business One, bank reconciliation is that process everyone knows has to be done but nobody wants to do. The accounting team spends hours matching lines on the bank statement against system records. The result: delays in the monthly close, accumulated errors and a distorted financial picture.
SAP Business One includes tools for bank reconciliation that can automate much of this work. In this tutorial you'll learn to configure and optimize the process to shorten the close and improve the accuracy of your financial information. I've also included diagnostic queries in versions for SQL Server and SAP HANA.
The three types of bank reconciliation in SAP Business One
External reconciliation (Bank Reconciliation): Compares the movements in SAP B1 against the actual bank statement. It identifies differences between what your system says and what the bank says. It's the most common process and the one that eats the most time when done manually.
Internal reconciliation (Internal Reconciliation): Links related documents inside SAP B1, such as a customer invoice against its matching payment. It's essential for keeping clean balances by business partner. It also lets you identify unapplied payments or invoices that were already paid but never linked.
Automatic reconciliation by rules: SAP B1 can apply predefined rules to automatically reconcile transactions that meet certain criteria (exact amount, reference, date). As a result, the accounting team only steps in for the exceptions.
Step 1: Set up the bank accounts correctly
Before automating bank reconciliation in SAP Business One, you have to verify that the accounts are configured correctly. Go to Administration → Setup → Banking → House Bank Accounts and check:
Linked G/L account: Each bank account has to be tied to a specific G/L account. If you have several accounts at the same bank, each one needs its own G/L account.
Statement format: Define the format of the file you receive from the bank (BAI2, MT940, CSV, OFX). SAP B1 supports several formats, but you need to configure the field mapping correctly.
Currency: The bank account's currency has to match the statement's. If you work with multiple currencies, each account has to be properly defined to avoid exchange-rate differences.
Step 2: Configure Bank Statement Processing (BSP)
BSP is the module that turns your bank statement into movements SAP B1 can reconcile automatically. To configure it, go to Banking → Bank Statement Processing.
Field mapping: Define which column of the file corresponds to date, amount, reference and description. Correct mapping is the foundation of successful automated reconciliation.
Assignment rules: Create rules that automatically identify the transaction type. For example, if the description contains «TRANSFER», assign it as a transfer between accounts. Be specific in the rules, though, to avoid false positives.
Default offsetting accounts: For recurring transactions (bank fees, interest, taxes), define the offsetting account so they post automatically.
Step 3: The automated bank reconciliation flow
Once BSP is configured, the daily flow gets considerably simpler. First, import the bank statement in Banking → Bank Statement → Process Statement. SAP B1 applies the configured rules and generates automatic journal entries for the recognized transactions.
Second, review the unrecognized transactions. These are the ones that need manual intervention and represent the exceptions in the process. So your work comes down to handling exceptions instead of processing every line.
Third, run the reconciliation. Go to Banking → Bank Reconciliation, select the account and the period. SAP B1 proposes automatic matches based on amount, reference and date. Accept the correct ones and work manually only on the differences.
Step 4: Optimize internal reconciliation
Internal reconciliation cleans up open balances for customers and suppliers. Go to Banking → Internal Reconciliations → Reconciliation and select the business partner. SAP B1 shows every open transaction and proposes the links.
To automate it, use Internal Reconciliation → Reconciliation Wizard. This wizard can reconcile invoices against payments in bulk when the amounts match exactly. You can also configure tolerances for minor differences such as rounding cents.
Diagnostic query: bank reconciliation status by account
This query gives you an X-ray of the reconciliation status of each bank account, showing how many transactions are pending and their amounts. It's essential for prioritizing the accounting team's work.
SQL Server version
SELECT
T0.AcctCode AS 'Account',
T0.AcctName AS 'Account Name',
COUNT(CASE WHEN T1.IsCredit = 'C' THEN 1 END)
AS 'Open Credits',
COUNT(CASE WHEN T1.IsCredit = 'D' THEN 1 END)
AS 'Open Debits',
CAST(SUM(CASE WHEN T1.IsCredit = 'C' THEN T1.SrcSum ELSE 0 END) AS DECIMAL(15,2))
AS 'Total Credits',
CAST(SUM(CASE WHEN T1.IsCredit = 'D' THEN T1.SrcSum ELSE 0 END) AS DECIMAL(15,2))
AS 'Total Debits',
CAST(SUM(CASE WHEN T1.IsCredit = 'D' THEN T1.SrcSum ELSE -T1.SrcSum END) AS DECIMAL(15,2))
AS 'Net Difference',
MIN(T1.RefDate) AS 'Oldest Transaction',
DATEDIFF(DAY, MIN(T1.RefDate), GETDATE())
AS 'Days Unreconciled'
FROM OACT T0
INNER JOIN JDT1 T1 ON T1.Account = T0.AcctCode
LEFT JOIN ODSC T2 ON T2.AcctCode = T0.AcctCode
WHERE T0.AcctType = 'N'
AND T0.ExportCode LIKE 'NK%'
AND T1.MthDate IS NULL
GROUP BY T0.AcctCode, T0.AcctName
HAVING COUNT(*) > 0
ORDER BY COUNT(*) DESCSAP HANA version
SELECT
T0."AcctCode" AS "Account",
T0."AcctName" AS "Account Name",
COUNT(CASE WHEN T1."IsCredit" = 'C' THEN 1 END)
AS "Open Credits",
COUNT(CASE WHEN T1."IsCredit" = 'D' THEN 1 END)
AS "Open Debits",
CAST(SUM(CASE WHEN T1."IsCredit" = 'C' THEN T1."SrcSum" ELSE 0 END) AS DECIMAL(15,2))
AS "Total Credits",
CAST(SUM(CASE WHEN T1."IsCredit" = 'D' THEN T1."SrcSum" ELSE 0 END) AS DECIMAL(15,2))
AS "Total Debits",
CAST(SUM(CASE WHEN T1."IsCredit" = 'D' THEN T1."SrcSum" ELSE -T1."SrcSum" END) AS DECIMAL(15,2))
AS "Net Difference",
MIN(T1."RefDate") AS "Oldest Transaction",
DAYS_BETWEEN(MIN(T1."RefDate"), CURRENT_DATE)
AS "Days Unreconciled"
FROM "OACT" T0
INNER JOIN "JDT1" T1 ON T1."Account" = T0."AcctCode"
LEFT JOIN "ODSC" T2 ON T2."AcctCode" = T0."AcctCode"
WHERE T0."AcctType" = 'N'
AND T0."ExportCode" LIKE 'NK%'
AND T1."MthDate" IS NULL
GROUP BY T0."AcctCode", T0."AcctName"
HAVING COUNT(*) > 0
ORDER BY COUNT(*) DESCThe key differences between the two versions: SAP HANA uses DAYS_BETWEEN instead of DATEDIFF, Current date instead of GETDATE(), and every table and field name goes in double quotes. So make sure you use the right version for your database.
Best practices for bank reconciliation in SAP Business One
Reconcile daily: Don't let transactions pile up. A daily reconciliation takes 15 minutes; a monthly one can take days. Errors are also easier to catch when the information is fresh.
Standardize references: Define a reference convention that both your team and your customers use consistently. Don't rely on that alone, though: back it up with amount and date rules.
Review the tolerances: Define acceptable margins for minor differences (rounding cents, bank fees). As a result, the automatic process will resolve more cases without human intervention.
Monitor with the diagnostic query: Run the status query weekly. If unreconciled transactions are growing, you have a systemic problem to deal with.
Conclusion: bank reconciliation is the pulse of your financial health
Automating bank reconciliation in SAP Business One isn't just about saving time: it's about financial accuracy. Every unreconciled transaction is an unknown in your cash flow, an undetected fraud risk and a potential difference in your financial statements.
With the native SAP B1 tools, a well-configured BSP and the SQL Server and SAP HANA diagnostic queries presented here, you can turn a manual process that takes days into an automated one that takes minutes. Your accounting team will then be able to spend its time analyzing the information instead of just processing it.
