SAP B1 Production Order - Stock Pick List including Batch Codes and BBE Dates

[tinplate]

Hi, a question for the community.

I'd like to generate a report that creates a simple picking list from a production order, and not using the Generate pick List wizard!

The scenario is: currently the user is viewing the production order and looking at each individual component item to find the oldest stock (FIFO) and the stock locations in the warehouse, then writing the list by hand; this is quite timely and prone to error.

I'd like a report that can be run from the production order on a function button, that looks at all component (child) items from the production order and then shows the bin locations, batch codes and expiry date per item; all in one list

Has anyone done anything similar, or know if this is possible? It seems like it should be possible in a report but I'm struggling to bring it all together!

Feedback would be great 🙂

[ManishPant · Accepted solution]

Hi @tinplate ,

SELECT
T2."BinCode",
T1."ItemCode",
T3."DistNumber",
TO_VARCHAR(T3."ExpDate",'DD-MM-YY') AS "Expiry Date",
TO_VARCHAR(T3."InDate",'DD-MM-YY') AS "In Date",
SUM(T1."OnHandQty") AS "Quantity" 
FROM OBBQ T1 
INNER JOIN OBIN T2 ON T2."AbsEntry"=T1."BinAbs" 
INNER JOIN OBTN T3 ON T3."AbsEntry"=T1."SnBMDAbs" 
INNER JOIN OITM T4 ON T4."ItemCode"=T1."ItemCode" 
INNER JOIN OITB T5 ON T5."ItmsGrpCod"=T4."ItmsGrpCod" 
WHERE T1."OnHandQty" > 0 
AND T1."ItemCode" NOT LIKE '%TEST%' 
AND (T1."ItemCode" || '^' || T2."WhsCode")  IN (SELECT "ItemCode" || '^' || "wareHouse" FROM WOR1 WHERE "DocEntry" = (SELECT "DocEntry" FROM OWOR WHERE "DocNum" = 00000001 ) AND "ItemType"=4)
GROUP BY T2."BinCode",
T1."ItemCode",
T3."InDate",
T3."ExpDate",
T3."DistNumber" 
ORDER BY 
T1."ItemCode",
T3."InDate" ASC,
T3."DistNumber"

Thanks.

Manish

[tinplate · Accepted solution]

Thanks Manish 🙂