SAP Business One Invoice Lookup Query
SQL queries to find A/R invoices by number or customer, with line item details and payment status.
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
DocNum vs DocEntry
DocNum is the invoice number users see (like INV-2024-1847). DocEntry is the internal database key. When joining OINV to INV1, always use DocEntry — it's indexed and guaranteed unique.
Why Filter on CANCELED?
SAP doesn't delete cancelled invoices — it marks them with CANCELED = 'Y'. The original invoice stays in the database for audit purposes. Always filter CANCELED = 'N' unless you specifically need cancelled documents.
DocTotal vs Line Totals
DocTotal on OINV is the invoice total including tax and freight. LineTotal on INV1 is the line amount before document-level discounts. For most reports, DocTotal is what you want.
Available Fields Reference
OINV (Header)
DocEntry DocNum DocDate DocDueDate TaxDate CardCode CardName DocTotal VatSum PaidToDate DocStatus CANCELED Comments SlpCode
INV1 (Lines)
DocEntry LineNum ItemCode Dscription Quantity Price LineTotal WhsCode TaxCode DiscPrcnt
Find Invoice by Number
Look up a specific invoice by its document number. We include PaidToDate to calculate the remaining balance — this field updates automatically when payments are applied.
SELECT
T0.DocNum,
T0.DocDate,
T0.DocDueDate,
T0.CardCode,
T0.CardName,
T0.DocTotal,
T0.VatSum,
T0.DocTotal - T0.VatSum AS NetTotal,
T0.PaidToDate,
T0.DocTotal - T0.PaidToDate AS BalanceDue,
CASE
WHEN T0.DocTotal = T0.PaidToDate THEN 'Paid'
WHEN T0.PaidToDate > 0 THEN 'Partial'
ELSE 'Open'
END AS PaymentStatus
FROM OINV T0
WHERE T0.DocNum = 12345
AND T0.CANCELED = 'N'| DocNum | DocDate | CardName | DocTotal | NetTotal | PaidToDate | BalanceDue | PaymentStatus |
|---|---|---|---|---|---|---|---|
| 12345 | 2024-01-15 | Acme Corp | 5250.00 | 4500.00 | 0.00 | 5250.00 | Open |
Invoice with Line Items
Join INV1 to get the individual line items. We use DocEntry for the join because it's the primary key. Dscription (note the spelling) contains the item name as it appeared on the invoice — this may differ from the current item master.
SELECT
T0.DocNum,
T0.DocDate,
T0.CardName,
T1.LineNum,
T1.ItemCode,
T1.Dscription AS ItemName,
T1.Quantity,
T1.Price,
T1.DiscPrcnt AS DiscountPct,
T1.LineTotal
FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.DocNum = 12345
AND T0.CANCELED = 'N'
ORDER BY T1.LineNum| DocNum | CardName | Line | ItemCode | ItemName | Quantity | Price | LineTotal |
|---|---|---|---|---|---|---|---|
| 12345 | Acme Corp | 1 | A00001 | Widget Pro | 10 | 450.00 | 4500.00 |
Tip: LineNum starts at 0, not 1. Add 1 if you want user-friendly line numbers.
All Invoices for a Customer
Pull all invoices for a customer code. The payment status logic compares DocTotal to PaidToDate. We use DATEDIFF to calculate days overdue from the due date.
SELECT
T0.DocNum,
T0.DocDate,
T0.DocDueDate,
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 'Open'
END AS Status,
CASE
WHEN T0.DocTotal > T0.PaidToDate
AND T0.DocDueDate < GETDATE()
THEN DATEDIFF(DAY, T0.DocDueDate, GETDATE())
ELSE 0
END AS DaysOverdue
FROM OINV T0
WHERE T0.CardCode = 'C10000'
AND T0.CANCELED = 'N'
ORDER BY T0.DocDate DESC| DocNum | DocDate | DocTotal | BalanceDue | Status | DaysOverdue |
|---|---|---|---|---|---|
| 12345 | 2024-01-15 | 5250.00 | 5250.00 | Open | 15 |
Open Invoices Only
Filter to unpaid or partially paid invoices. DocStatus = 'O' means open (not fully paid or closed). Combine with the balance check to catch edge cases.
SELECT
T0.DocNum,
T0.CardCode,
T0.CardName,
T0.DocDate,
T0.DocDueDate,
T0.DocTotal - T0.PaidToDate AS BalanceDue,
DATEDIFF(DAY, T0.DocDueDate, GETDATE()) AS DaysOverdue
FROM OINV T0
WHERE T0.DocStatus = 'O'
AND T0.CANCELED = 'N'
AND T0.DocTotal > T0.PaidToDate
ORDER BY T0.DocDueDate| DocNum | CardCode | CardName | BalanceDue | DaysOverdue |
|---|---|---|---|---|
| 12345 | C10045 | Acme Corp | 5250.00 | 15 |