SAP Business One Order Status Query

Key Concepts

DocStatus Field

DocStatus = 'O' means the order is open (not fully delivered/invoiced). DocStatus = 'C' means closed. An order closes when all lines are fully delivered or manually closed.

CANCELED vs Closed

CANCELED = 'Y' means the order was cancelled — different from closed. Cancelled orders were never fulfilled. Always filter CANCELED = 'N' unless you specifically need cancelled orders.

Quantity vs DelivrdQty

On order lines (RDR1), Quantity is what was ordered. DelivrdQty is what's been delivered via delivery notes. The difference is what's still pending fulfillment.

Line Status

Each line also has LineStatus — 'O' for open, 'C' for closed. A line closes when fully delivered or manually closed, even if the header is still open.

Available Fields Reference

Orders for a Customer

All orders for a specific customer with their status. We calculate days since order date to identify aging orders. The CASE statement converts status codes to readable labels.

SELECT 
    T0.DocNum,
    T0.DocDate,
    T0.DocDueDate,
    T0.DocTotal,
    CASE T0.DocStatus 
        WHEN 'O' THEN 'Open'
        WHEN 'C' THEN 'Closed'
    END AS Status,
    DATEDIFF(DAY, T0.DocDate, GETDATE()) AS DaysOld
FROM ORDR T0
WHERE T0.CardCode = 'C10000'
  AND T0.CANCELED = 'N'
ORDER BY T0.DocDate DESC

All Open Orders

Orders still waiting to be fulfilled across all customers. Filter DocStatus = 'O' for open orders. DATEDIFF shows how old each order is — useful for identifying orders stuck in processing.

SELECT 
    T0.DocNum,
    T0.DocDate,
    T0.CardCode,
    T0.CardName,
    T0.DocTotal,
    DATEDIFF(DAY, T0.DocDate, GETDATE()) AS DaysOld
FROM ORDR T0
WHERE T0.DocStatus = 'O'
  AND T0.CANCELED = 'N'
ORDER BY T0.DocDate

Order with Line Details

Get a specific order with all its line items and fulfillment status. Quantity - DelivrdQty shows what's still pending. Join on DocEntry — it's the internal key, not DocNum.

SELECT 
    T0.DocNum,
    T0.CardName,
    T1.LineNum + 1 AS Line,
    T1.ItemCode,
    T1.Dscription AS ItemName,
    T1.Quantity AS Ordered,
    T1.DelivrdQty AS Delivered,
    T1.Quantity - T1.DelivrdQty AS Remaining,
    T1.LineTotal,
    CASE T1.LineStatus WHEN 'O' THEN 'Open' ELSE 'Closed' END AS LineStatus
FROM ORDR T0
INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.DocNum = 12345
  AND T0.CANCELED = 'N'
ORDER BY T1.LineNum

Note: LineNum starts at 0 in SAP. We add 1 for user-friendly display.

All Unfulfilled Order Lines

Find all pending quantities across all open orders. This is useful for production planning or pick lists. Filter where Quantity > DelivrdQty to get only lines with pending fulfillment.

SELECT 
    T0.DocNum,
    T0.CardName,
    T1.ItemCode,
    T1.Dscription AS ItemName,
    T1.Quantity - T1.DelivrdQty AS Pending,
    T1.WhsCode,
    T0.DocDate,
    T0.DocDueDate,
    DATEDIFF(DAY, T0.DocDueDate, GETDATE()) AS DaysPastDue
FROM ORDR T0
INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.DocStatus = 'O'
  AND T0.CANCELED = 'N'
  AND T1.Quantity > T1.DelivrdQty
ORDER BY T0.DocDueDate