Query - All Serial Numbers in Stock for an Item
This is a really simple but powerful query for SAP Business One. Have you ever just wanted a quick query to view all Serial Numbers in stock for an item? This query also makes a great Formatted Search from the Item Master Form as well. I have provided both examples, a query with a prompt for the Item Number, and the same query to be run from the Item Master form with an Item loaded.
SELECT
T0."ItemCode" "Item Code",
T0."ItemName" "Item Description",
T0."IntrSerial" "Serial No.",
T0."InDate" "Admission Date",
T0."PrdDate" "Manufactured Date",
T0."ExpDate" "Expiration Date"
FROM OSRI T0 LEFT JOIN OITM T1 ON T0."ItemCode" = T1."ItemCode"
WHERE T0."Status" <> 1
AND T1."ItemCode" =[%0]
Now, if you would like to run this query as a Formatted Search from the Item Master form, say from a Boyum Button or a User Defined field, simply change "AND T1."ItemCode" =[%0]" to "AND T1."ItemCode" = $[$5.0.0]"
So the Formatted Search version of the query would look like this below:
SELECT
T0."ItemCode" "Item Code",
T0."ItemName" "Item Description",
T0."IntrSerial" "Serial No.",
T0."InDate" "Admission Date",
T0."PrdDate" "Manufactured Date",
T0."ExpDate" "Expiration Date"
FROM OSRI T0 LEFT JOIN OITM T1 ON T0."ItemCode" = T1."ItemCode"
WHERE T0."Status" <> 1
AND T1."ItemCode" = $[$5.0.0]