How to get the last transaction of serila no

[tazoo08]

Hi All,

I have small issue, I need to create a query to get the last transaction in the (Serial no transaction report).
Or how can I know where's the serial available now?

I try using the Query:

SELECT T0.[ItemCode], T0.[DistNumber], T1.[WhsCode], T1.[LineNum], T1.[BaseType], T1.[BaseEntry], T1.[BaseNum], T1.[CardCode], T1.[Direction], T1.[BsDocType], T1.[BsDocEntry] FROM OSRN T0 Inner Join SRI1 T1 On T1.[ItemCode] = T0.[ItemCode] And T1.[SysSerial] = T0.[SysNumber] WHERE T0.[ItemCode] =[%0] And T0.[DistNumber]= [%1] ORDER BY T1.[LineNum]

any suggestion please.

Thanks,

[former_member233854 · Accepted solution]

SELECT T0.[ItemCode], T0.[DistNumber], T1.[WhsCode], T1.[LineNum], T1.[BaseType], T1.[BaseEntry], T1.[BaseNum], T1.[CardCode], T1.[Direction], T1.[BsDocType], T1.[BsDocEntry] FROM OSRN T0 Inner Join SRI1 T1 On T1.[ItemCode] = T0.[ItemCode] And T1.[SysSerial] = T0.[SysNumber] inner join ( SELECT itemCode, sysSerial, MAX(linenum) LineNum FROM sri1 GROUP BY sysSerial, itemCode) t on t1.sysSerial = t.SysSerial and t1.LineNum = t.LineNum and t1.ItemCode = t.ItemCode Where T1.CardCode = [%0]

[tazoo08 · Accepted solution]

Thanks Danilo Kasparian,

What if I need to showing all available serials for specified customer, and using the Customer Code as parameter, like:

SELECT TOP(1) T0.[ItemCode], T0.[DistNumber], T1.[WhsCode], T1.[LineNum], T1.[BaseType], T1.[BaseEntry], T1.[BaseNum], T1.[CardCode], T1.[Direction], T1.[BsDocType], T1.[BsDocEntry] FROM OSRN T0 Inner Join SRI1 T1 On T1.[ItemCode] = T0.[ItemCode] And T1.[SysSerial] = T0.[SysNumber] WHERE T1.[CardCode] =[%0] ORDER BY T1.[LineNum] DESC

[former_member233854 · Accepted solution]

I updated the answer

[tazoo08 · Accepted solution]

Tanks a lot Dear Danilo Kasparian for your help.

[tazoo08 · Accepted solution]

Your answer helped me to use the same way to get information from the Invoices:

SELECT T0.[ItemCode],T1.[ItemName],  T0.[DistNumber], T1.[WhsCode], T1.[LineNum], T1.[BaseType], T1.[BaseEntry], T1.[BaseNum], T1.[CardCode], T2.[CardName], T1.[CreateDate],T3.[DocDate], T3.[AcctName]
FROM OSRN T0 Inner Join SRI1 T1 On T1.[ItemCode] = T0.[ItemCode] And T1.[SysSerial] = T0.[SysNumber] 
inner join ( SELECT itemCode, sysSerial, MAX(linenum) LineNum FROM sri1 GROUP BY sysSerial, itemCode) t on t1.sysSerial = t.SysSerial and t1.LineNum = t.LineNum and t1.ItemCode = t.ItemCode INNER JOIN OCRD T2 ON T1.[CardCode] = T2.[CardCode] 
Left outer Join(SELECT x.[DocNum], x.[DocDate], x.[CardCode], y.[AcctName] FROM OINV x  INNER JOIN OACT y ON x.[CtlAccount] = y.[AcctCode] WHERE x.[Canceled]='N') T3 ON T1.[BaseNum]=T3.[DocNum] And T1.[CardCode]=T3.[CardCode] And T1.[BaseType]=13
WHERE T1.[WhsCode] LIKE '%-DISP%' AND T1.[CardCode]<>''

Thanks again,

[former_member233854 · Accepted solution]

Good to know that it helped

[former_member312729]

Hi,

Try the standard report --> Serial Number Transactions Report

Regards:

Balaji.S

[tazoo08]

Thanks Balaji Selvaraj,

the standered report give me the same result, but I need to use the Query to give me just the last Transaction for the specified Serial (As Pic. showing).