cancel
Showing results for 
Search instead for 
Did you mean: 

How to query for last Bin Location that received Items?

JoseBrito
Explorer
0 Kudos
428

We are using SAP Business One SQL 10.0

We have a warehouse set up with bin locations, and for auto allocation on receipt we are using LAST BIN LOCATION THAT RECEIVED ITEMS.

We want to create a report based on A/P Reserve Invoice lines to tell the warehouse personnel where was the items last received  (Bin Location).

I am a novice on SQL and i don´t know were this information is stored, and how to get the last Bin location that received items from that table.

View Entire Topic
SonTran
Active Contributor
0 Kudos

Hi,

You can have a look at this topic for the same

https://forum.sapb1.vn/community/inventory/tips-cau-query-truy-van-thong-tin-bin-location-nhap-ma-ha...

Hope this helps,

Son Tran

JoseBrito
Explorer
0 Kudos
Thank you Son Tran, i will try to make it work using this.
JoseBrito
Explorer
0 Kudos
I managed to get the information that i need
JoseBrito
Explorer
0 Kudos
SELECT T5.[DocNum], T5.[CardName], T4.[ItemCode], T4.[Dscription], T4.[VendorNum], T4.[Quantity], T3.[BinCode] FROM OILM T0 INNER JOIN (SELECT ItemCode, Max(MessageID) AS "MaxMess" FROM OILM WHERE TransType in ('20', '67') AND ActionType in ('1', '19') AND LocCode = '01' GROUP BY ItemCode) T1 ON T0.ItemCode = T1.ItemCode AND T0.MessageID = T1.MaxMess INNER JOIN OBTL T2 ON T0.[MessageID] = T2.[MessageID] INNER JOIN OBIN T3 ON T2.[BinAbs] = T3.[AbsEntry] RIGHT JOIN PCH1 T4 ON T0.[ItemCode] = T4.[ItemCode] INNER JOIN OPCH T5 ON T4.[DocEntry] = T5.[DocEntry] WHERE T5.[DocNum] = [%0]
JoseBrito
Explorer
0 Kudos
This gets the last BIN location that received this item in warehouse 01
JoseBrito
Explorer
0 Kudos
Based on items from A/R reserved Invoice