Skip to Content
0

How to get the last transaction of serila no

Jul 30, 2017 at 09:14 AM

115

avatar image

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,

capture.jpg (84.3 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Danilo Kasparian Jul 31, 2017 at 03:05 PM
0

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]

Show 5 Share
10 |10000 characters needed characters left characters exceeded

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
0

I updated the answer

1

Tanks a lot Dear Danilo Kasparian for your help.

0

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,

1

Good to know that it helped

1
Balaji Selvaraj Jul 31, 2017 at 07:20 AM
0

Hi,

Try the standard report --> Serial Number Transactions Report

Regards:

Balaji.S

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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).

0