Start a project

Open A/R invoices by due date

Every open customer invoice ordered by due date, with the open amount and days until (or past) the due date.

By Zain Abbas, Founder & Lead SAP Business One / AI Engineer · Last updated

Tables: OINVUse: Cash planning, collection calls and weekly receivables review.

Tool

SELECT T0.DocNum, T0.CardCode, T0.CardName, T0.DocDate, T0.DocDueDate,
  DATEDIFF(day, GETDATE(), T0.DocDueDate) AS DaysToDue,
  T0.DocTotal, T0.PaidToDate, T0.DocTotal - T0.PaidToDate AS OpenAmount
FROM OINV T0
WHERE T0.DocStatus = 'O' AND T0.CANCELED = 'N'
ORDER BY T0.DocDueDate, T0.DocNum
Need your UDFs in this report, or a dashboard? We extend it for your company.Customize it →

What this query answers

The finance team plans next week's cash and wants every open customer invoice in due-date order. This returns each open, not cancelled A/R invoice with the customer, the open amount and the days to the due date. A negative number means the invoice is already overdue, so one list covers both what is coming in and what is late.

Sample output

Illustrative demo values. Your columns are the same; the numbers come from your company.

DocNumCardCodeCardNameDocDateDocDueDateDaysToDueDocTotalPaidToDateOpenAmount
1388C40000Blue Harbor Foods2026-09-012026-10-01-40460004600
1421C20000Acme Trading2026-10-052026-11-04-6530020003300
1460C30000Northwind Retail2026-10-202026-11-199875008750
1472C50000Summit Supplies2026-10-282026-11-2717315003150

SQL Server vs SAP HANA

DATEDIFF(day, GETDATE(), T0.DocDueDate) becomes DAYS_BETWEEN(CURRENT_TIMESTAMP, T0."DocDueDate"). The order of the two dates stays the same, so a negative result still means the invoice is past due.

Common errors and fixes

  • Totals do not match the customer balance: This reads open invoices only. Open credit memos, down payments and payments on account are not deducted; use the customer balances query for the account balance.
  • Foreign-currency invoices show the wrong open amount: DocTotal and PaidToDate are in local currency. Use DocTotalFC - PaidFC for the document currency.

Need this report built into SAP Business One?

A saved query answers the question once. We do SAP Business One development: add-ons, integrations and reports, from the database to the screen. Or hire a SAP Business One developer for the whole job, fixed price per phase.

Finance

Related queries

Customer aging report (AR aging)Open invoice balance per customer in 0–30, 31–60, 61–90 and 90+ day buckets by due date.Overdue customer invoicesOpen invoices past their due date, with days overdue and the customer phone number.Customer balancesAll customers with a non-zero account balance, with credit limit and phone.Cash flow by month (incoming vs outgoing)Incoming payments, outgoing payments and the net cash movement per month for a date range.Incoming payments by dateCustomer payments received in a period, split into cash, transfer, check and card.Outgoing payments by dateVendor payments made in a period, split into cash, transfer, check and card.

Table reference: OINV table (A/R Invoice (header))

How it works

Three steps, no signup

1

Copy the right version

Use the SQL Server tab for MS SQL databases and the HANA tab for SAP B1 on HANA.

2

Open Query Generator

In SAP Business One go to Tools → Queries → Query Generator, paste and click Execute.

3

Save and share

Click Save, pick a category and give the query a name. Your team can run it from Query Manager.

FAQ

Questions about this tool

How do I run the open a/r invoices by due date query in SAP Business One?

Copy the version that matches your database, open Tools → Queries → Query Generator, paste it in the SQL box and click Execute. Save it to reuse it later.

Which tables does this query use?

It reads OINV from the standard SAP Business One schema. No user-defined fields are needed.

Can this be turned into a dashboard or alert?

Yes. We turn queries into Crystal reports, Excel reports that refresh themselves, SAP B1 alerts or WhatsApp messages for managers.

More free tools

Built for SAP Business One teams

ERP selector: which ERP suits you?Answer 15 questions and compare ERPs on processes, size, budget, hosting and your country’s rules, with every claim sourced.SAP B1 dashboard builderDrag and drop SAP B1 dashboards for Sales, Purchasing, Inventory, Receivables and Finance with click-to-filter. Works offline, no developer needed.SQL to HANA query converterPaste a SQL Server query and get the SAP HANA version, with every change explained.SAP B1 table finderSearch any table: what it stores, its line tables and its key fields.

Need this inside SAP Business One?

We build add-ons, reports and integrations for SAP B1 on SQL Server and HANA. Tell us what you need and get a fixed price, usually within 24 hours.

Start a project Chat on WhatsApp