Statements of account in SAP Business One: from a monthly chore to a schedule

1. The monthly chore

In most SAP Business One companies the statement of account is produced by hand: a receivables clerk opens Business Partners › Business Partner Reports › Aging › Customer Receivables Aging or the account balance of a business partner, prints to PDF, finds the contact's address, writes the e-mail, attaches, sends, and moves to the next customer. With 300 active customers that is a day or two every month, and it is the same day the month-end close needs the same person. The statement also goes out at different dates to different customers, the attachment is whatever the printer produced, and nobody can later prove which version a customer received.

The job is a textbook candidate for automation because every piece of it is already in the database: the transactions, the business partner, the contact person and the address. What is missing is a loop that runs the same report once per customer with the right parameters, delivers each result to the right mailbox, and keeps a record.

2. Where the data lives

A statement of account is an extract of the general ledger filtered on one business partner, not a list of invoices. Three tables carry it:

Query JDT1, not the marketing documents. Manual journal entries, reconciliations, exchange-rate differences and payments on account appear only in the ledger; a statement built from OINV and ORCT alone will not agree with the balance the customer sees on their own books. TransType tells you what kind of document created each line (13 for an AR invoice, 14 for an AR credit memo, 24 for an incoming payment, 30 for a journal entry) and BaseRef carries its document number.

3. The query behind a running-balance statement

The classic statement shows an opening balance at the start of the period, every ledger line in the period with its document reference, and a running balance ending in today's balance. The lines part looks like this, written with the named variables a statement needs:

SELECT T0."RefDate", T0."DueDate", T0."TransType", T0."BaseRef", T0."LineMemo",
       T0."Debit", T0."Credit",
       T0."BalDueDeb" - T0."BalDueCred" AS "OpenAmount"
FROM JDT1 T0
WHERE T0."ShortName" = @BP
  AND T0."RefDate" BETWEEN @FromDate AND @ToDate
ORDER BY T0."RefDate", T0."TransId", T0."Line_ID"

And the opening balance is the same filter with RefDate < @FromDate and SUM(Debit - Credit). Three details make or break the result:

• Date column. RefDate is the posting date, which is what accountants reconcile on; DueDate is for aging. Do not mix them in one statement.
• Currency. Debit and Credit are in local currency; FCDebit and FCCredit hold the foreign-currency amounts for business partners with a fixed currency. A customer invoiced in EUR expects a EUR statement; make the currency mode a variable, not an afterthought.
• Quoting. SAP HANA column names are case-sensitive and quoted ("RefDate"); SQL Server accepts [RefDate] or bare names. A report meant for both platforms needs one SQL text per dialect, or a tool that rewrites the identifiers.

Bind the variables as real parameters. A statement query that pastes @BP into the SQL text as a string is a SQL injection waiting for a business partner code with a quote in it, and it is also slower, because the database re-plans every statement.

4. Who receives it

The recipient is the second half of the problem. OCRD.E_Mail is the business partner's general address, often the sales contact or nobody. The accounts-payable contact is usually a contact person in OCPR, and the right one is a matter of convention: a contact named "Accounts", a user-defined field such as U_StmtMail, or the default contact CntctPrsn. Decide the rule once and write it into the iteration query:

SELECT T0."CardCode" AS "Value", T0."CardName" AS "Label",
       COALESCE(T1."E_MailL", T0."E_Mail") AS "Recipient"
FROM OCRD T0
LEFT JOIN OCPR T1 ON T1."CardCode" = T0."CardCode" AND T1."Name" = T0."CntctPrsn"
WHERE T0."CardType" = 'C' AND T0."frozenFor" = 'N' AND T0."Balance" <> 0

Then decide what happens to the rows without an address: skip them, send them to an internal mailbox, or fail the run. Any of the three is defensible; silently dropping them is not. The run report must list them.

5. Three statements, one loop

The same iteration carries three reports that differ only in the query and the audience:

SAP Business One's Dunning Wizard decides the level and records the letters; a scheduled reminder report is not a replacement for it but the delivery it lacks: the same content, mailed to the contact on the day, with a copy in the business partner's attachments.

6. Checklist before the first mass run

• The query runs under a read-only database user and contains a single SELECT.
• Period bounds are relative to the run date (first and last day of the previous month), not typed in.
• The opening balance plus movements equals the closing balance for ten customers chosen at random, checked against Account Balance in the client.
• Foreign-currency customers get the amounts in their currency.
• Every row of the iteration has a recipient, or the policy for rows without one is set and the rows are listed.
• The mail server's rate limit is known and respected; 800 messages in one minute is how a sender domain gets blacklisted.
• A dry run to one internal mailbox happened first, with the real subject, body and file name.
• The PDF carries the company's name, the period, the business partner's code and a way to prove it is genuine.
• Someone receives a summary of the run: items delivered, skipped, failed, bounced.
• A copy of what was sent is kept for as long as the accounting records are.

7. How CertiReport does it

CertiReport ships the statement of account, open invoices, receivables aging and overdue reminder as starter reports for HANA and SQL Server, each with typed variables (BP on the BusinessPartner concept, FromDate, ToDate, a currency mode) and a running-balance PDF template. A schedule binds the dates with expressions such as @StartOfMonth(-1) and @EndOfMonth(-1) and iterates the business-partner slot over a query that yields the value, label, recipient address and recipient language per row, with a policy for rows without an address. Save dry-runs that query and reports the row count and the rows without a recipient before anything is enabled.

At fire time the engine runs the items in parallel up to the company's connection cap, renders Excel, CSV, a certified PDF or a Crystal layout per customer, and writes each e-mail into the outbox in the same transaction as the item's result. The mail profile's messages-per-minute limit is respected, transient failures are retried, bounces read from the sender mailbox are matched to the item, and the weekly digest summarises delivered, skipped, failed and bounced items. The PDF footer carries a verification code and QR code that the customer can check on your portal, and after delivery the file can be filed on the business partner's Attachments tab and logged as an activity through the Service Layer. See the SAP Business One page or book a demo with your own statement.

References

• SAP Business One Online Help: Customer Receivables Aging report, Account Balance, Dunning Wizard
• SAP Business One SDK help: tables OCRD, OCPR, JDT1, OINV and the object types list
• SAP Business One, version for SAP HANA: Query Manager and quoted identifiers