Batch Bin Report - Lists Batches, their Bins, Qty, Cost, Dates and more!
This is a fantastic HANA query I adapted from the Wisys WMS solution that displays all the relevant details for Batch controlled items, their Bin Location and more. Running this query from B1 also provides for drill downs into the Item as well as the Batch.
Just copy and paste this query into the SAP Business One Query Generator.
Enjoy!
SELECT "OBBQ"."ItemCode",
"OITM"."ItemName",
"OITB"."ItmsGrpNam",
"OBBQ"."WhsCode",
"OWHS"."WhsName",
"OBIN"."BinCode",
"OBBQ"."OnHandQty",
"OBBQ"."SnBMDAbs" AS "Batch Key",
"OBTN"."DistNumber" AS "Batch Number",
"OBTN"."MnfSerial",
"OBTN"."LotNumber",
"OBTN"."MnfDate",
"OBTN"."ExpDate",
"OBTN"."InDate",
"OBTN"."CostTotal",
( CASE
WHEN "OBTN"."Status" = '0' THEN 'Released'
WHEN "OBTN"."Status" = '1' THEN 'NotAccessible'
WHEN "OBTN"."Status" = '2' THEN 'Locked'
ELSE 'Unknown'
END ) AS "BatchStatus",
"OBPL"."BPLName"
FROM "OBBQ"
INNER JOIN "OITM" ON "OITM"."ItemCode" = "OBBQ"."ItemCode"
LEFT JOIN "OWHS" ON "OWHS"."WhsCode" = "OBBQ"."WhsCode"
LEFT JOIN "OITB" ON "OITB"."ItmsGrpCod" = "OITM"."ItmsGrpCod"
LEFT JOIN "OIBQ" ON "OIBQ"."ItemCode" = "OBBQ"."ItemCode"
AND "OIBQ"."WhsCode" = "OBBQ"."WhsCode"
AND "OIBQ"."BinAbs" = "OBBQ"."BinAbs"
LEFT JOIN "OBTN" ON "OBTN"."ItemCode" = "OBBQ"."ItemCode"
AND "OBTN"."AbsEntry" = "OBBQ"."SnBMDAbs"
LEFT JOIN "OBIN" ON "OBIN"."AbsEntry" = "OBBQ"."BinAbs"
LEFT JOIN "OBPL" ON "OBPL"."BPLId" = "OWHS"."BPLid"
WHERE "OBBQ"."OnHandQty" > '0'