OINV table in SAP Business One (A/R Invoice (header))
OINV is the SAP Business One table that stores the header of every A/R invoice: customer, dates, totals, tax, amount paid and status. Its lines are in INV1, joined on DocEntry, and its object type is 13.
OINV key fields
| Field | What it holds |
|---|---|
DocEntry | Internal key. Joins to INV1.DocEntry and is used by every document link. |
DocNum | Invoice number users see. Unique per numbering series, not across the table. |
DocDate | Posting date. Use it for sales reports by period. |
DocDueDate | Due date, calculated from the payment terms. Use it for aging. |
CardCode | Customer code. Joins to OCRD.CardCode. |
DocTotal | Total including tax and freight, after the header discount. |
VatSum | Tax amount. DocTotal - VatSum gives the net amount. |
PaidToDate | Amount already paid. DocTotal - PaidToDate is the open balance. |
DiscPrcnt | Header discount percent. Not included in INV1.LineTotal. |
DocStatus | O = open, C = closed (fully paid or closed manually). |
CANCELED | N = normal, Y = cancelled, C = the cancellation document itself. |
SlpCode | Sales employee. Joins to OSLP.SlpCode. |
How OINV relates to other tables
OINV is a header table. Each invoice has one row in OINV and one row per item or service line in INV1, both sharing the same DocEntry. Freight is in INV3, withholding tax in INV5 and installments in INV6. When an invoice is copied from a delivery or sales order, the link is stored on the lines (INV1.BaseType, BaseEntry, BaseLine), not on the header.
Common joins
OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry (lines)OINV T0 INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode (customer master)OINV T0 LEFT JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode (sales employee)OINV T0 LEFT JOIN OUSR T4 ON T0.UserSign = T4.USERID (user who created it)
When you use the OINV table
Almost every sales, collection and commission report starts from OINV: sales by customer or period, open invoices, overdue invoices and customer aging. For net sales, subtract A/R credit memos, which live in ORIN with the same column names, so the same query shape works with a UNION ALL.
Example query: open a/r invoices with balance
SQL Server
SELECT T0.DocNum, T0.DocDate, T0.DocDueDate, T0.CardCode, T0.CardName, 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
SAP HANA (column names in double quotes)
SELECT T0."DocNum", T0."DocDate", T0."DocDueDate", T0."CardCode", T0."CardName", 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"
Paste either version in Tools → Queries → Query Generator. Need the other dialect for your own query? Use the SQL to HANA converter.
Common pitfall: DocTotal vs. the sum of INV1.LineTotal
DocTotal already includes the header discount (DiscPrcnt), tax and freight. INV1.LineTotal does not include the header discount, so a sum of the lines is higher than the net invoice when a header discount is used. Apply the factor LineTotal * (1 - DiscPrcnt / 100) when you report from the lines, and filter CANCELED = 'N' so cancelled invoices and their cancellation documents do not double count. See Why SAP B1 sales totals do not match (header discount).
Ready-made queries that use OINV
- Sales by customer for a date range: Net sales per customer (invoices minus credit notes) between two dates, highest first.
- Sales by sales employee: Invoice count and net sales per sales employee for a period.
- Monthly sales trend: Net invoiced sales per month for the last 12 months.
- 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 invoices: Open invoices past their due date, with days overdue and the customer phone number.
- Invoices created per user today: How many A/R invoices each SAP B1 user created today, with the total value.
Related tables
INV1 (A/R Invoice (lines)) · OCRD (Business Partners) · ORDR (Sales Order (header)) · search all SAP Business One tables
Sources: SAP Business One documentation and SDK (DI API database tables reference), SAP Help Portal.
Ready to get started?
Tell us what you need. You get a clear technical answer, a fixed scope and a price, usually within 24 hours.