Inventory Transfer with bin Locations

[former_member232853]

Hi guys,

I am trying to create a layout in crystal reports for an inventory transfer and need to reference FROM BIN and TO BIN.

at the moment i am joining like this

select * from OWTR inner join WTR1 on OWTR.DocEntry = WTR1.DocEntry

left outer join OILM on WTR1.LineNum = OILM.DocLineNum

left outer join OBTL on OILM.MessageID = OBTL.MessageID

left outer join OBIN on OBTL.BinAbs = OBIN.AbsEntry

where OILM.transtype = 67

has anyone done this sort of layout before?

[former_member227598 · Accepted solution]

Hi Ross,

Try Below Query  & Convert into the crystal reports format..........

SELECT  

T0.DocNum, T0.DocDate, T0.CardName,

T1.ItemCode, T1.Dscription, T1.Quantity,

T4.DistNumber, T0.Filler, T1.WhsCode,

dbo.OBIN.BinCode AS [To Bin Code]

FROM dbo.OBIN INNER JOIN

dbo.OBTL ON dbo.OBIN.AbsEntry = dbo.OBTL.BinAbs INNER JOIN

dbo.OWTR AS T0 INNER JOIN

dbo.WTR1 AS T1 ON T0.DocEntry = T1.DocEntry LEFT OUTER JOIN

dbo.OITL AS T2 ON T1.DocEntry = T2.ApplyEntry AND T1.LineNum = T2.ApplyLine AND T2.ApplyType = 67 INNER JOIN

dbo.ITL1 AS T3 ON T2.LogEntry = T3.LogEntry ON dbo.OBTL.ITLEntry = T3.LogEntry LEFT OUTER JOIN

dbo.OBTN AS T4 ON T3.ItemCode = T4.ItemCode AND T3.MdAbsEntry = T4.AbsEntry

GROUP BY T0.DocNum, T0.DocDate, T0.CardName, T1.ItemCode, T1.Dscription, T1.Quantity, T4.DistNumber, T0.Filler, T1.WhsCode, dbo.OBIN.BinCode

Rgds,

Kamlesh Naware

[kothandaraman_nagarajan]

You can also try below query,

SELECT T0.ObjType 

  , T0.DocEntry 

  , T1.DocLine 

  , T1.ItemCode ItemCode 

    , T3.DistNumber BatchNumber  

  , (T1.DocQty/Abs(T1.DocQty)) * T2.Quantity Quantity 

  , T2.BinAbs BinAbs 

  , T1.LocCode Whse 

  , T4.BinCode BinCode 

  , T1.LogEntry 

FROM OWTR T0 JOIN OITL T1 ON T0.DocEntry = T1.DocEntry AND T0.ObjType  = T1.DocType AND ISNULL(T1.DefinedQty,0)>0 

  JOIN OBTL T2 ON T1.LogEntry = T2.ITLEntry 

  LEFT JOIN OBTN T3 ON T2.SnBMDAbs = T3.AbsEntry 

  LEFT JOIN OBIN T4 ON T2.BinAbs = T4.AbsEntry 

WHERE  T0.DocEntry  = [%0]

ORDER BY T0.DocEntry, T1.DocLine, T1.LogEntry 

Thanks

[kothandaraman_nagarajan]

Hi,

Please check this thread,

Thanks