WTR1 table in SAP Business One (Inventory Transfer (rows))

WTR1 key fields

How WTR1 relates to other tables

WTR1 rows belong to one OWTR header (DocEntry). BaseType, BaseEntry and BaseLine point to the document the row was copied from, most often a transfer request (BaseType 1250000001, OWTQ and its rows WTQ1).

Common joins

• WTR1 T0 INNER JOIN OWTR T1 ON T1.DocEntry = T0.DocEntry (header)
• WTR1 T0 LEFT JOIN OWTQ T2 ON T2.DocEntry = T0.BaseEntry AND T0.BaseType = 1250000001 (transfer request)
• WTR1 T0 INNER JOIN OWHS T3 ON T3.WhsCode = T0.FromWhsCod (from warehouse name)

When you use the WTR1 table

Use WTR1 for quantity moved per item and warehouse pair, to trace a transfer back to its request, and to total stock movements between two warehouses for a period.

Example query: quantity moved per item from one warehouse to another this month

SQL Server

SELECT T0.ItemCode, T0.FromWhsCod, T0.WhsCode AS ToWhs, SUM(T0.Quantity) AS QtyMoved
FROM WTR1 T0
INNER JOIN OWTR T1 ON T1.DocEntry = T0.DocEntry
WHERE T1.CANCELED = 'N' AND YEAR(T1.DocDate) = YEAR(GETDATE()) AND MONTH(T1.DocDate) = MONTH(GETDATE())
GROUP BY T0.ItemCode, T0.FromWhsCod, T0.WhsCode
ORDER BY T0.ItemCode

SAP HANA (column names in double quotes)

SELECT T0."ItemCode", T0."FromWhsCod", T0."WhsCode" AS "ToWhs", SUM(T0."Quantity") AS "QtyMoved"
FROM WTR1 T0
INNER JOIN OWTR T1 ON T1."DocEntry" = T0."DocEntry"
WHERE T1."CANCELED" = 'N' AND YEAR(T1."DocDate") = YEAR(CURRENT_TIMESTAMP) AND MONTH(T1."DocDate") = MONTH(CURRENT_TIMESTAMP)
GROUP BY T0."ItemCode", T0."FromWhsCod", T0."WhsCode"
ORDER BY T0."ItemCode"

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

Common pitfall: BaseEntry without BaseType

BaseEntry is only a DocEntry number. A transfer row can come from a transfer request, a goods receipt PO or a goods receipt, so always filter BaseType too, or the join finds a different document with the same DocEntry. See OWTQ table (inventory transfer request).

Ready-made queries that use WTR1

• Stock by warehouse: On hand, committed, ordered and available quantity per item and warehouse.

Questions about the WTR1 table

Which column is the to warehouse in WTR1?

WhsCode. FromWhsCod is the from warehouse.

How do I link WTR1 to its transfer request?

WTR1.BaseType = 1250000001 and WTR1.BaseEntry = OWTQ.DocEntry; BaseLine is the request row.

What is InvQty in WTR1?

The quantity in the inventory unit of measure, next to Quantity in the row unit.

Related tables

OWTR (Inventory Transfer (header)) · OWTQ (Inventory Transfer Request (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.