SAP Business One Unpaid Invoices Query
SQL queries to find all open invoices with no payment received, including overdue status and days overdue calculations.
These are sample queries you can run on Manager Boost. Use them as-is, or edit and customize on our platform. Go custom or use our pre-built queries — you're in control.
Key Concepts
DocStatus = 'O' for Open
DocStatus indicates the document's payment status: 'O' = Open (not fully paid), 'C' = Closed (fully paid). Always filter DocStatus = 'O' to get unpaid invoices.
PaidToDate vs DocTotal
DocTotal is the invoice total including tax. PaidToDate is the cumulative amount paid against this invoice. The open balance is DocTotal - PaidToDate. Filter DocTotal > PaidToDate to ensure there's an unpaid balance.
Days Overdue Calculation
Use DATEDIFF(DAY, DocDueDate, GETDATE()) to calculate days overdue. If the due date is in the future, this returns a negative number. Filter DocDueDate < GETDATE() to show only overdue invoices.
Why Filter CANCELED?
SAP doesn't delete cancelled invoices — it marks them with CANCELED = 'Y'. Always filter CANCELED = 'N' to exclude cancelled documents from your unpaid invoice reports.
Combining Filters
For unpaid invoices, use all three filters together: DocStatus = 'O' AND CANCELED = 'N' AND DocTotal > PaidToDate. This ensures you get valid, open invoices with an unpaid balance.
Available Fields Reference
OINV (A/R Invoice Header)
DocEntry DocNum DocDate DocDueDate CardCode CardName DocTotal VatSum PaidToDate DocStatus CANCELED SlpCode
OCRD (Customer Master)
CardCode CardName CreditLine Balance Phone1 E_Mail
All Unpaid Invoices
Get all open invoices with an unpaid balance. Includes payment status and days overdue for collections tracking.
SELECT
T0.DocNum,
T0.DocDate,
T0.DocDueDate,
T0.CardCode,
T0.CardName,
T0.DocTotal,
T0.PaidToDate,
T0.DocTotal - T0.PaidToDate AS BalanceDue,
CASE
WHEN T0.DocTotal = T0.PaidToDate THEN 'Paid'
WHEN T0.PaidToDate > 0 THEN 'Partial'
ELSE 'Unpaid'
END AS PaymentStatus,
CASE
WHEN T0.DocDueDate < GETDATE()
THEN DATEDIFF(DAY, T0.DocDueDate, GETDATE())
ELSE 0
END AS DaysOverdue
FROM OINV T0
WHERE T0.DocStatus = 'O'
AND T0.CANCELED = 'N'
AND T0.DocTotal > T0.PaidToDate
ORDER BY T0.DocDueDate| DocNum | CardName | DocDueDate | DocTotal | BalanceDue | PaymentStatus | DaysOverdue |
|---|---|---|---|---|---|---|
| 12345 | Acme Corp | 2024-01-15 | 5250.00 | 5250.00 | Unpaid | 28 |
| 12350 | Global Supplies | 2024-02-01 | 3000.00 | 3000.00 | Unpaid | 11 |
Unpaid Invoices by Customer
Group unpaid invoices by customer with totals. Join OCRD to get customer credit limits and contact info for collections.
SELECT
T0.CardCode,
T0.CardName,
T1.CreditLine,
T1.Phone1,
T1.E_Mail,
COUNT(*) AS InvoiceCount,
SUM(T0.DocTotal) AS TotalInvoiceAmount,
SUM(T0.PaidToDate) AS TotalPaid,
SUM(T0.DocTotal - T0.PaidToDate) AS TotalBalanceDue,
MAX(CASE
WHEN T0.DocDueDate < GETDATE()
THEN DATEDIFF(DAY, T0.DocDueDate, GETDATE())
ELSE 0
END) AS MaxDaysOverdue
FROM OINV T0
INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
WHERE T0.DocStatus = 'O'
AND T0.CANCELED = 'N'
AND T0.DocTotal > T0.PaidToDate
AND T1.CardType = 'C'
GROUP BY T0.CardCode, T0.CardName, T1.CreditLine, T1.Phone1, T1.E_Mail
ORDER BY TotalBalanceDue DESC| CardCode | CardName | InvoiceCount | TotalBalanceDue | MaxDaysOverdue |
|---|---|---|---|---|
| C10000 | Acme Corp | 3 | 15250.00 | 73 |
| C10045 | Global Supplies | 2 | 8750.00 | 45 |
Overdue Invoices Only
Filter to only invoices that are past their due date. Sorted by days overdue, most overdue first. Essential for collections prioritization.
SELECT
T0.DocNum,
T0.DocDate,
T0.DocDueDate,
T0.CardCode,
T0.CardName,
T0.DocTotal - T0.PaidToDate AS BalanceDue,
DATEDIFF(DAY, T0.DocDueDate, GETDATE()) AS DaysOverdue,
CASE
WHEN DATEDIFF(DAY, T0.DocDueDate, GETDATE()) <= 30 THEN '1-30 Days'
WHEN DATEDIFF(DAY, T0.DocDueDate, GETDATE()) <= 60 THEN '31-60 Days'
WHEN DATEDIFF(DAY, T0.DocDueDate, GETDATE()) <= 90 THEN '61-90 Days'
ELSE '90+ Days'
END AS OverdueBucket
FROM OINV T0
WHERE T0.DocStatus = 'O'
AND T0.CANCELED = 'N'
AND T0.DocTotal > T0.PaidToDate
AND T0.DocDueDate < GETDATE()
ORDER BY DaysOverdue DESC, T0.CardCode| DocNum | CardName | DocDueDate | BalanceDue | DaysOverdue | OverdueBucket |
|---|---|---|---|---|---|
| 12350 | Acme Corp | 2023-12-01 | 4000.00 | 73 | 61-90 Days |
| 12345 | Acme Corp | 2024-01-15 | 5250.00 | 28 | 1-30 Days |
Tip: Remove the DocDueDate < GETDATE() filter if you want to include current (not yet due) invoices in your report.