Inventory Transfer Details With Serial No

[Former Member]

How can i Write query for Inventory transfer Report With Serial No Details

[Former Member · Accepted solution]

Hi Rahesh,

This is your query:

select Distinct T0.BaseNum,T0.ItemCode,T1.Intrserial from SRI1 T0

inner join OSRI T1 on T0.ItemCode = T1.ItemCode

where T0.BaseType = '67'

So you can select columns more.

GBU

[Former Member]

Hi M Rahesh,

I have a query, pls check it.

DECLARE @FromDate DATETIME, @ToDate DATETIME

SET @FromDate = '20130101'

SET @ToDate = '20131231'

SELECT T0.DocEntry, T0.DocDate, T1.ItemCode, T1.Dscription, T2.IntrSerial, T0.Filler AS FromWarehouse, T1.WhsCode AS ToWarehouse

FROM dbo.OWTR T0 WITH(NOLOCK)

INNER JOIN dbo.WTR1 T1 WITH(NOLOCK) ON T1.DocEntry = T0.DocEntry

INNER JOIN dbo.OSRI T2 WITH(NOLOCK) ON T2.ItemCode = T1.ItemCode

INNER JOIN dbo.SRI1 T3 WITH(NOLOCK) ON T3.ItemCode = T2.ItemCode AND T3.SysSerial = T2.SysSerial AND T3.WhsCode = T2.WhsCode AND T3.BaseType = 67

WHERE T0.DocDate BETWEEN @FromDate AND @ToDate

I hope to help you fix this issue.

[Former Member]

Thanks For Your Reply.I had executed you query.but After fetching data it shows duplicate data on result pane.I herewith attached the screenshot for your reference.Please look in to this.

[Former Member]

Hi M Rahesh,

Sorry you because I forgot distinct in select clause.

DECLARE @FromDate DATETIME, @ToDate DATETIME

SET @FromDate = '20130101'

SET @ToDate = '20131231'

SELECT DISTINCT T0.DocEntry, T0.DocDate, T1.ItemCode, T1.Dscription, T2.IntrSerial, T0.Filler AS FromWarehouse, T1.WhsCode AS ToWarehouse

FROM dbo.OWTR T0 WITH(NOLOCK)

INNER JOIN dbo.WTR1 T1 WITH(NOLOCK) ON T1.DocEntry = T0.DocEntry

INNER JOIN dbo.OSRI T2 WITH(NOLOCK) ON T2.ItemCode = T1.ItemCode

INNER JOIN dbo.SRI1 T3 WITH(NOLOCK) ON T3.ItemCode = T2.ItemCode AND T3.SysSerial = T2.SysSerial AND T3.WhsCode = T2.WhsCode AND T3.BaseType = 67

WHERE T0.DocDate BETWEEN @FromDate AND @ToDate

[Former Member]

Hi,

Try this:

SELECT T4.[DocNum], T4.[DocDate], T4.[CardCode], T4.[CardName], T4.[Filler], T3.[ItemCode], T3.[Dscription], CASE When T2.AbsEntry is null Then T3.[Quantity] ELSE 1 END QTY, T3.[WhsCode], T2.DistNumber

FROM OITL T0

          INNER JOIN ITL1 T1 ON T0.LogEntry = T1.LogEntry

          left outer JOIN OSRN T2 on T1.MdAbsEntry = T2.AbsEntry

          INNER JOIN OWTR T4 ON T0.DocEntry = T4.DocEntry

          INNER JOIN WTR1 T3 ON T3.DocEntry = T4.DocEntry

WHERE T0.[DocType] = 67 and T0.ManagedBy = 10000045

Beni.