SAP B1 SQL query for Bin locations

[former_member666435]

Hi all,

I have been trying to do a query to bring the First Bin field but i have had mixed results, the query below for some of the Items in the Production Order brings the default Bin Location instead of the First One, any advice on the query to achieve what i need?

Best Regards,

Daniel

select distinct
t0.ItemCode 

,t5.FisrtBin as Bin_Location
,t1.OnHand as 'Bin_Quantity'


FROM [dbo].[OITW] T0 

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

inner join dbo.[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 dbo.[WTR1] t5 on t5.ItemCode = t1.ItemCode


where  t4.OnHandQty  <> 0