ODLN table in SAP Business One (Delivery (header))

ODLN key fields

How ODLN relates to other tables

ODLN is the header and DLN1 the lines, joined on DocEntry. A delivery copied from a sales order stores BaseType = 17 and the order keys on its DLN1 lines; an invoice copied from the delivery stores BaseType = 15 and BaseEntry = ODLN.DocEntry on its INV1 lines. DLN1.OpenQty is the quantity still open, not yet invoiced or returned.

Common joins

• ODLN T0 INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry (lines)
• ODLN T0 INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode (customer)
• DLN1 T1 INNER JOIN RDR1 T3 ON T1.BaseType = 17 AND T1.BaseEntry = T3.DocEntry AND T1.BaseLine = T3.LineNum (order line)
• INV1 T4 ON T4.BaseType = 15 AND T4.BaseEntry = T1.DocEntry AND T4.BaseLine = T1.LineNum (invoice from the delivery)

When you use the ODLN table

Use ODLN and DLN1 for "delivered but not invoiced" lists, dispatch history per customer or item, and proof of delivery checks. In companies that always invoice from deliveries, open deliveries are revenue that still has to be billed.

Example query: delivery lines not yet invoiced

SQL Server

SELECT T0.DocNum, T0.DocDate, T0.CardCode, T0.CardName,
  T1.ItemCode, T1.Dscription, T1.Quantity, T1.OpenQty, T1.WhsCode
FROM ODLN T0
INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.CANCELED = 'N' AND T1.LineStatus = 'O' AND T1.OpenQty > 0
ORDER BY T0.DocDate, T0.DocNum

SAP HANA (column names in double quotes)

SELECT T0."DocNum", T0."DocDate", T0."CardCode", T0."CardName",
  T1."ItemCode", T1."Dscription", T1."Quantity", T1."OpenQty", T1."WhsCode"
FROM ODLN T0
INNER JOIN DLN1 T1 ON T0."DocEntry" = T1."DocEntry"
WHERE T0."CANCELED" = 'N' AND T1."LineStatus" = 'O' AND T1."OpenQty" > 0
ORDER BY T0."DocDate", T0."DocNum"

Paste either version in Tools → Queries → Query Generator. Need the other dialect for your own query? Use the SQL to HANA converter.

Common pitfall: Counting deliveries and invoices as sales twice

When invoices are copied from deliveries, the same goods appear once in DLN1 and again in INV1. A sales report that adds ODLN and OINV doubles the figure. Report sales from OINV (minus ORIN) and use ODLN only for what is delivered but not yet invoiced, as in the query above. See Why SAP B1 sales totals do not match.

Related tables

ORDR (Sales Order (header)) · RDR1 (Sales Order (lines)) · OINV (A/R Invoice (header)) · OITW (Item stock per warehouse) · search all SAP Business One tables

Sources: SAP Business One documentation and SDK (DI API database tables reference), SAP Help Portal.