on 07-30-2017 10:14 AM
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,
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]
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
I updated the answer
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,
Good to know that it helped
Hi,
Try the standard report --> Serial Number Transactions Report
Regards:
Balaji.S
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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).
User | Count |
---|---|
95 | |
11 | |
10 | |
6 | |
5 | |
5 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.