Pick List Query With it's Location

[Former Member]

Hi Guys,

How can I show all bin location per item in my pick list query. any idea with this bec. my client wants me to generate with its bin location so they can easily get the items they want to deliver.

This is my Query

SELECT SUM(T1.PickQtty) AS 'QTY.',T2.unitMsr as 'OuM',T2.Dscription As 'SKU' FROM OPKL T0 INNER JOIN PKL1 T1 ON T0.AbsEntry = T1.AbsEntry INNER JOIN RDR1 T2 on T2.PickIdNo = T0.AbsEntry and T1.OrderLine = T2.LineNum AND T1.OrderEntry = T2.DocEntry INNER JOIN ORDR T3 ON T2.DocEntry = T3.DocEntry and T3.ObjType = T1.BaseObject INNER JOIN OITM T4 ON T2.ItemCode = T4.ItemCode WHERE T0.AbsEntry = '[%0]' and T0.U_uPickListNo = '[%1]' and T0.U_uDriver = '[%2]' and T0.U_uLocation = '[%3]' and T0.U_uPlateNumber = '[%4]' GROUP BY T2.Dscription,T2.unitMsr ORDER BY T2.Dscription

Thanks in Advance Totally no idea at all.

🙂

[former_member312729 · Accepted solution]

Hi ,

Try this

SELECT SUM(T1.PickQtty) AS 'QTY.',T2.unitMsr as 'OuM',T2.Dscription As 'SKU', T6.[BinCode] FROM OPKL T0 INNER JOIN PKL1 T1 ON T0.AbsEntry = T1.AbsEntry INNER JOIN RDR1 T2 on T2.PickIdNo = T0.AbsEntry and T1.OrderLine = T2.LineNum AND T1.OrderEntry = T2.DocEntry INNER JOIN ORDR T3 ON T2.DocEntry = T3.DocEntry and T3.ObjType = T1.BaseObject INNER JOIN OITM T4 ON T2.ItemCode = T4.ItemCode INNER JOIN PKL2 T5 ON T0.[AbsEntry] = T5.[AbsEntry] and T1.[PickEntry] = T5.[PickEntry] INNER JOIN OBIN T6 ON T5.[BinAbs] = T6.[AbsEntry] WHERE T0.AbsEntry = '[%0]' and T0.U_uPickListNo = '[%1]' and T0.U_uDriver = '[%2]' and T0.U_uLocation = '[%3]' and T0.U_uPlateNumber = '[%4]' GROUP BY T2.Dscription,T2.unitMsr,T6.[BinCode] ORDER BY T2.Dscription

Regards:

Balaji.S

[Former Member · Accepted solution]

Hi Balaji Selvaraj,

Thanks for this but there seem to be a little problem.

the the sum po the pick qnty is not the same with the actual count that was pick in the picklist.

how can we solve it sir?

thanks in advance sir

[former_member312729 · Accepted solution]

SELECT SUM(T1.PickQtty) AS 'QTY.',SUM(T5.PickQtty) AS 'Actual QTY.',T2.unitMsr as 'OuM',T2.Dscription As 'SKU', T6.[BinCode] FROM OPKL T0 INNER JOIN PKL1 T1 ON T0.AbsEntry = T1.AbsEntry INNER JOIN RDR1 T2 on T2.PickIdNo = T0.AbsEntry and T1.OrderLine = T2.LineNum AND T1.OrderEntry = T2.DocEntry INNER JOIN ORDR T3 ON T2.DocEntry = T3.DocEntry and T3.ObjType = T1.BaseObject INNER JOIN OITM T4 ON T2.ItemCode = T4.ItemCode INNER JOIN PKL2 T5 ON T0.[AbsEntry] = T5.[AbsEntry] and T1.[PickEntry] = T5.[PickEntry] INNER JOIN OBIN T6 ON T5.[BinAbs] = T6.[AbsEntry] WHERE T0.AbsEntry = '[%0]' and T0.U_uPickListNo = '[%1]' and T0.U_uDriver = '[%2]' and T0.U_uLocation = '[%3]' and T0.U_uPlateNumber = '[%4]' GROUP BY T2.Dscription,T2.unitMsr,T6.[BinCode] ORDER BY T2.Dscription

[Former Member · Accepted solution]

Thank You Very Much It really works!

[kothandaraman_nagarajan]

Hi,

Try this query,

SELECT SUM(T1.PickQtty) AS 'QTY.',T2.unitMsr as 'OuM',T2.Dscription As 'SKU', T5.[BinCode]

FROM OPKL T0 INNER JOIN PKL1 T1 ON T0.AbsEntry = T1.AbsEntry INNER JOIN RDR1 T2 on T2.PickIdNo = T0.AbsEntry and T1.OrderLine = T2.LineNum AND T1.OrderEntry = T2.DocEntry INNER JOIN ORDR T3 ON T2.DocEntry = T3.DocEntry and T3.ObjType = T1.BaseObject INNER JOIN OITM T4 ON T2.ItemCode = T4.ItemCode INNER JOIN PKL2 T4 ON T0.[AbsEntry] = T4.[AbsEntry] INNER JOIN OBIN T5 ON T4.[BinAbs] = T5.[AbsEntry]

WHERE T0.AbsEntry = '[%0]' and T0.U_uPickListNo = '[%1]' and T0.U_uDriver = '[%2]' and T0.U_uLocation = '[%3]' and T0.U_uPlateNumber = '[%4]'

GROUP BY T2.Dscription,T2.unitMsr ORDER BY T2.Dscription,T5.[BinCode]

Regards,

Nagarajan

[Former Member]

Hi Nagarajan

It say t4 specified multiple time in a FROM clause

how is this?

[kothandaraman_nagarajan]

Try this,

SELECT SUM(T1.PickQtty) AS 'QTY.',T2.unitMsr as 'OuM',T2.Dscription As 'SKU', T6.[BinCode]

FROM OPKL T0 INNER JOIN PKL1 T1 ON T0.AbsEntry = T1.AbsEntry INNER JOIN RDR1 T2 on T2.PickIdNo = T0.AbsEntry and T1.OrderLine = T2.LineNum AND T1.OrderEntry = T2.DocEntry INNER JOIN ORDR T3 ON T2.DocEntry = T3.DocEntry and T3.ObjType = T1.BaseObject INNER JOIN OITM T4 ON T2.ItemCode = T4.ItemCode INNER JOIN PKL2 T5 ON T0.[AbsEntry] = T5.[AbsEntry] INNER JOIN OBIN T6 ON T5.[BinAbs] = T6.[AbsEntry]

WHERE T0.AbsEntry = '[%0]' and T0.U_uPickListNo = '[%1]' and T0.U_uDriver = '[%2]' and T0.U_uLocation = '[%3]' and T0.U_uPlateNumber = '[%4]'

GROUP BY T2.Dscription,T2.unitMsr,T6.[BinCode] ORDER BY T2.Dscription

[Former Member]

Hi sir,

The query works:) nice 🙂 but seems there's a little bit problem.

It show difference Bin Locations

Like this

Like ALFONSO LITE 1L It should be PWH8-ALF-01 there so many bin that appears.

Thanks

[kothandaraman_nagarajan]

Can you check each bin location for qty?

[Former Member]

HERE SIR ALL MY ALFONSO IS AT PWH8-ALF-01