Skip to Content
0

SQL Query to get picklist based warehouse, Batch and Bin Location

Dec 06, 2016 at 05:37 AM

248

avatar image

We have a situation where we want to list down all pick list items based on Warehouse, Batch and bin location. Problem we are facing is that, based on query we have generated, We are getting duplicate data.

Below is our query

SELECT
DISTINCT IBT1."BatchNum",
OBIN."BinCode",
'' AS "Picture",
WTQ1."ItemCode",
PKL1."OrderLine",
WTQ1."Dscription",
OWHS."WhsCode",
OWHS."WhsName",
PKL1."RelQtty"
FROM OPKL
INNER JOIN PKL1 ON PKL1."AbsEntry" = OPKL."AbsEntry"
AND "PKL1"."BaseObject" = 1250000001
LEFT OUTER JOIN PKL2 ON PKL2."AbsEntry" = PKL1."AbsEntry"
AND PKL2."PickEntry" = PKL1."PickEntry"
LEFT OUTER JOIN OWTQ ON OWTQ."DocEntry" = PKL1."OrderEntry"
LEFT OUTER JOIN WTQ1 ON WTQ1."DocEntry" = OWTQ."DocEntry"
AND WTQ1."LineNum" = PKL1."OrderLine"
LEFT OUTER JOIN OITM ON OITM."ItemCode" = WTQ1."ItemCode"
AND "ManBtchNum" = 'Y'
LEFT OUTER JOIN IBT1 ON IBT1."ItemCode" = OITM."ItemCode"
AND IBT1."BaseEntry" = OWTQ."DocEntry"
AND IBT1."BaseLinNum" = WTQ1."LineNum"
LEFT OUTER JOIN OUOM ON OUOM."UomCode" = WTQ1."UomCode"
LEFT OUTER JOIN OWHS ON OWHS."WhsCode" = WTQ1."FromWhsCod"
LEFT OUTER JOIN UGP1 ON UGP1."UomEntry" = OUOM."UomEntry"
AND UGP1."UgpEntry" = OITM."UgpEntry"
LEFT OUTER JOIN OBIN ON OBIN."WhsCode" = OWHS."WhsCode"
AND OBIN."AbsEntry" = PKL2."BinAbs"
Where OPKL."AbsEntry" = 74;

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers