🔥For a quick reference starting point on an SAP Business One query that includes BIN LOCATION and BATCH/SERIAL NUMBER information 🔥
I often need this requirement, and use this “boiler” plate code as the start point ✅
--Batch Number (OBBQ)
SELECT
'BATCH' AS "Type",
T0."ItemCode",
T1."ItemName" AS "Item Description",
T0."OnHandQty" AS "Quantity",
T2."BinCode" AS "Bin Location",
T3."DistNumber" AS "Batch | Serial Number",
T3."InDate" AS "Receipt Date"
FROM "OBBQ" T0
LEFT OUTER JOIN "OITM" T1 ON T0."ItemCode" = T1."ItemCode"
LEFT OUTER JOIN "OBIN" T2 ON T0."BinAbs" = T2."AbsEntry"
LEFT OUTER JOIN "OBTN" T3 ON T0."SnBMDAbs" = T3."AbsEntry"
UNION ALL
--Serial Number (OSBQ)
SELECT
'SERIAL' AS "Type",
T0."ItemCode",
T1."ItemName" AS "Item Description",
T0."OnHandQty" AS "Quantity",
T2."BinCode" AS "Bin Location",
T3."DistNumber" AS "Batch Number",
T3."InDate" AS "Receipt Date"
FROM "OSBQ" T0
LEFT OUTER JOIN "OITM" T1 ON T0."ItemCode" = T1."ItemCode"
LEFT OUTER JOIN "OBIN" T2 ON T0."BinAbs" = T2."AbsEntry"
LEFT OUTER JOIN "OSRN" T3 ON T0."SnBMDAbs" = T3."AbsEntry" Using this as a base you may wish to include stock that is not marked as BATCH/SERIAL managed.
You may also wish to include stock that is not currently in any BIN LOCATION (or any warehouse that is not managed by BIN LOCATION).
Signing out,
Dário 👍