How to get a transaction history on bin locations

[Former Member]

We are trying to get a transaction history on bin locations. The need came about because we accidentally duplicated some production orders and we needed to work out which bins the stock came from in order to move them back to where they came without physically having to check all of the related bins.

We managed to work around using the following queries but we'd like to know how to build a query with the following information :

Item Code

Date

Quantity

Bin Location Taken From / Added to

Transaction Type (Ie picked for order, picked for production, goods receipt, credit note, invoice etc etc)

We used :

SELECT *  FROM OBTL T0

which gave useful fields :

Internal Number

Bin Internal Number

Quantity

SELECT T0."BinCode", T0."AbsEntry", T0."Descr" FROM OBIN T0

Bin Location Code

As a complete beginner to SQL and queries in general i'm not sure how to link everything together to get what we need. Could someone please give some guidance as i'm sure this would be a useful query not just to us but other users as well.

[KennedyT21]

Kindly confirm if this is resolved,  If your thread is closed then close the thread with correct or helpful answers.   Cheers!!!

[former_member794521]

Hey Kennedy would like some assistance regarding a query. Let me know if you would be able to assist.

https://answers.sap.com/questions/13612975/query-list-of-items-that-are-in-the-bin-location-o.html

[KennedyT21]

Hi John

Welcome to the Forum.

try this query

select t0.ItemCode , t5.ItemName,  t4.OnHandQty ,t2.BinCode, t2.sl1code, t2.SL2Code, t0.WhsCode

FROM  [dbo].[OITW] T0  left  JOIN [dbo].[OITM] T1  ON  T1.[ItemCode] = T0.[ItemCode] 

inner join OIBQ t4 on t0.ItemCode = t4.ItemCode and T0.WhsCode = t4.WhsCode

left JOIN [dbo].[OITW] T3  ON  T3.ItemCode = T0.ItemCode

inner  JOIN [dbo].[OBIN] T2  ON  t2.AbsEntry = t3.dftbinabs

inner join OITM t5 on t0.ItemCode = t5.ItemCode

WHERE T2.[SL1Code] Between '[%0]' AND '[%1]'

order by t2.sl1code, t2.SL2Code, t0.ItemCode

[Former Member]

Hi Kennedy,

Thank you for the welcome and your reply. I ran your code through the SQL to Hana conversion to get the code below.

SELECT t0."ItemCode", t5."ItemName", t4."OnHandQty", t2."BinCode", t2."sl1code", t2."SL2Code",

t0."WhsCode" FROM "OITW" T0

LEFT OUTER JOIN "OITM" T1 ON T1."ItemCode" = T0."ItemCode"

INNER JOIN "OIBQ" t4 ON t0."ItemCode" = t4."ItemCode" AND T0."WhsCode" = t4."WhsCode"

LEFT OUTER JOIN "OITW" T3 ON T3."ItemCode" = T0."ItemCode"

INNER JOIN "OBIN" T2 ON t2."AbsEntry" = t3."dftbinabs"

INNER JOIN "OITM" t5 ON t0."ItemCode" = t5."ItemCode" WHERE T2."SL1Code" BETWEEN '[%0]' AND '[%1]'  

ORDER BY t2."sl1code", t2."SL2Code", t0."ItemCode"

When executing it's come up with the error below.

Also, in the first line where you have t4."OnHandQty" will this return the quantity in stock ? as it is the quantity transacted, ie moved or sold our purchased that we're looking for in this report.

[KennedyT21]

Hi John

It is working in the SQl without error...

try this

SELECT t0."ItemCode", t5."ItemName", t4."OnHandQty", t2."BinCode", t2."sl1code", t2."SL2Code",

t0."WhsCode" FROM "OITW" T0

LEFT OUTER JOIN "OITM" T1 ON T1."ItemCode" = T0."ItemCode"

INNER JOIN "OIBQ" t4 ON t0."ItemCode" = t4."ItemCode" AND T0."WhsCode" = t4."WhsCode"

LEFT OUTER JOIN "OITW" T3 ON T3."ItemCode" = T0."ItemCode"

INNER JOIN "OBIN" T2 ON t2."AbsEntry" = t3."dftbinabs"

INNER JOIN "OITM" t5 ON t0."ItemCode" = t5."ItemCode"

ORDER BY t2."sl1code", t2."SL2Code", t0."ItemCode"

[Former Member]

Hi Kennedy,

Ok the first problem has been overcome. On Hana t3."dftbinabs" has to be written t3."DftBinAbs" I'm not sure if other versions are case sensitive.

Now there is a new error :

[KennedyT21]

Try this

SELECT t0."ItemCode", t5."ItemName", t4."OnHandQty", t2."BinCode", t2."SL1code", t2."SL2Code",

t0."WhsCode" FROM "OITW" T0

LEFT OUTER JOIN "OITM" T1 ON T1."ItemCode" = T0."ItemCode"

INNER JOIN "OIBQ" t4 ON t0."ItemCode" = t4."ItemCode" AND T0."WhsCode" = t4."WhsCode"

LEFT OUTER JOIN "OITW" T3 ON T3."ItemCode" = T0."ItemCode"

INNER JOIN "OBIN" T2 ON t2."AbsEntry" = t3."dftbinabs"

INNER JOIN "OITM" t5 ON t0."ItemCode" = t5."ItemCode"

ORDER BY t2."SL1code", t2."SL2Code", t0."ItemCode"

[Former Member]

Ok it moved passed that point. Now it has another error.

[KennedyT21]

try this

i have not used Hana so just a try from my side... hope helpful to you

SELECT t0."ItemCode", t5."ItemName", t4."OnHandQty", t2."BinCode", t2."SL1code", t2."SL2Code",

t0."WhsCode" FROM "OITW" T0

LEFT OUTER JOIN "OITM" T1 ON T1."ItemCode" = T0."ItemCode"

INNER JOIN "OIBQ" t4 ON t0."ItemCode" = t4."ItemCode" AND T0."WhsCode" = t4."WhsCode"

LEFT OUTER JOIN "OITW" T3 ON T3."ItemCode" = T0."ItemCode"

INNER JOIN "OBIN" T2 ON t2."AbsEntry" = t3."dftbinabs"

INNER JOIN "OITM" t5 ON t0."ItemCode" = t5."ItemCode"

[Former Member]

There must certainly be some differences.

Now we are back to this error.

[KennedyT21]

Ok get me the access to fix this