How to find serial no's

[Former Member]

Hi every one,

I write query for finding serial no in delivery document,

select odln.DocNum,dln1.ItemCode,osrn.DistNumber  from dln1

inner join Odln on dln1.DocEntry= odln.docentry

inner join OSRN on dln1.Itemcode=osrn.itemcode

But it returns all the serial of item.It is not not fetching deleverd serial no's.

Is there any changes to get the correct result.

Thanks and Regards

DEV

[former_member662620 · Accepted solution]

for better performance, please use below script

select odln.DocNum,dln1.ItemCode,osrn.DistNumber  from dln1

inner join Odln on dln1.DocEntry= odln.docentry

inner join OITL on dln1.DocEntry = OITL.ApplyEntry and dln1.LineNum = OITL.ApplyLine and OITL.ApplyType = 15

inner join ITL1 on OITL.LogEntry = ITL1.LogEntry

inner join OSRN on ITL1.Itemcode=osrn.itemcode and ITL1.MdAbsEntry = OSRN.AbsEntry

[Former Member · Accepted solution]

Thank you so Much Shao,

My issue resolved

Thanks and Regards

DEV

[kothandaraman_nagarajan · Accepted solution]

Hi Taylor,

Could you explain reason for using OITL instead of SRI1?

Thanks & Regards,

Nagarajan

[former_member662620 · Accepted solution]

Hi,

From 8.8, OITL table was introduced and SRI1 became a view for back compatible reason.

That view is very complex in order to make sure the compatiblity. But it has to sacrifice the performance.

So I suggest use the new table if you are developping new app or query

[kothandaraman_nagarajan · Accepted solution]

Hi,

Thanks for reply. Already found in SDK compatibility issue document.

Regards,

Nagarajan

[dilfaner]

Boa tarde.

Eu consegui desta forma:

SELECT

T0."CardCode",

T0."CardName",

T1."ItemCode",

T1."CFOPCode",

T0."Serial" AS "N° Nota Fiscal",

T1."DocEntry",

T2."SysNumber",

T2."AbsEntry",

T2."UserSign",

T2."DistNumber"

FROM ODLN T0

INNER JOIN DLN1 T1 ON T0."DocEntry" = T1."DocEntry"

LEFT JOIN OSRN T2 ON T0."DocNum" = T2."SysNumber" AND T1."ItemCode" = T2."ItemCode"

[Former Member]

Hi DEV RAJ,

try this,

/*SELECT FROM [dbo].[ODLN] T10*/

declare  @fromdate as datetime

/* WHERE */

set @fromdate = /* T10.DocDate */ '[%0]'

/*SELECT FROM [dbo].[ODLN] T11*/

declare  @ToDate as datetime

/* WHERE */

set @ToDate = /* T11.DocDate */ '[%1]'

SELECT T0.[DocNum], T0.[CardCode], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T3.[IntrSerial]

FROM ODLN T0

INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry

left join SRI1 T2 on T2.[BaseNum]  =  T0.[DocNum] and T2.[BaseLinNum]  = T1.[LineNum]

left JOIN OSRI T3 ON T2.SysSerial = T3.SysSerial and T3.[ItemCode] = T2.[ItemCode]

WHERE T0.[DocDate] between @fromdate and @todate

GROUP BY T0.[DocNum], T0.[CardCode], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T3.[IntrSerial]

regards,

Raviraj

[kothandaraman_nagarajan]

Hi DR,

Try this:

SELECT T0.[DocNum], T0.[CardCode], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T3.[IntrSerial] FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry left join SRI1 T2 on T2.[BaseNum]  =  T0.[DocNum] and T2.[BaseLinNum]  = T1.[LineNum] left JOIN OSRI T3 ON T2.SysSerial = T3.SysSerial and T3.[ItemCode] = T2.[ItemCode] WHERE T0.[DocDate] between [%0] and [%1] GROUP BY T0.[DocNum], T0.[CardCode], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T3.[IntrSerial]

Thanks & Regards,

Nagarajan

[Former Member]

Hi Nagarajan,

I add date parameters in sql

set @fromdate=convert(datetime,'20130401',112)

set @todate=convert(datetime,'20130917',112)

SELECT T0.[DocNum], T0.[CardCode], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T3.[IntrSerial]

FROM ODLN T0 

INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry

left join SRI1 T2 on T2.[BaseNum]  =  T0.[DocNum] and T2.[BaseLinNum]  = T1.[LineNum]

left JOIN OSRI T3 ON T2.SysSerial = T3.SysSerial and T3.[ItemCode] = T2.[ItemCode]

WHERE T0.[DocDate] between @fromdate and @todate GROUP BY T0.[DocNum], T0.[CardCode], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T3.[IntrSerial]

but it returns nothing.Any changes required in this query.?

Thanks and Regards

DEV

[kothandaraman_nagarajan]

Hi DR,

try this:

Declare @fromdate  as nvarchar(20)

Declare @todate  as nvarchar(20)

set @fromdate=convert(datetime,'20130401',112)

set @todate=convert(datetime,'20130917',112)

SELECT T0.[DocNum], T0.[CardCode], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T3.[IntrSerial]

FROM ODLN T0 

INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry

left join SRI1 T2 on T2.[BaseNum]  =  T0.[DocNum] and T2.[BaseLinNum]  = T1.[LineNum]

left JOIN OSRI T3 ON T2.SysSerial = T3.SysSerial and T3.[ItemCode] = T2.[ItemCode]

WHERE T0.[DocDate] between @fromdate and @todate GROUP BY T0.[DocNum], T0.[CardCode], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T3.[IntrSerial]

Thanks & Regards,

Nagarajan