Skip to Content
0

Pick List Query With it's Location

Jun 16, 2017 at 01:28 AM

88

avatar image

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.

:)

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

2 Answers

Best Answer
Balaji Selvaraj Jun 16, 2017 at 12:41 PM
0

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

Show 3 Share
10 |10000 characters needed characters left characters exceeded

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

0

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

0

Thank You Very Much It really works!

0
Nagarajan K Jun 16, 2017 at 03:06 AM
0

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

Show 5 Share
10 |10000 characters needed characters left characters exceeded

Hi Nagarajan

It say t4 specified multiple time in a FROM clause

how is this?

0

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

0

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

untitled.png (83.1 kB)
0

Can you check each bin location for qty?

0

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

1.png (75.1 kB)
2.png (77.3 kB)
3.png (61.2 kB)
0