Skip to Content
Jul 31, 2020 at 08:05 AM

ADOC and ADO1 Query



I created the query to pull qty change on Sales orders. My criteria to pull only open Orders. Query result is showing he status "Open", but the order is actually closed. How do I fix that?

SELECTT2.DocNum,T2.[DocStatus], T2.[CardName],T2.[NumAtCard], T1.LineNum+1 as 'Line', 'Qty' as 'Field',cast(T1.Quantity as varchar(18)) as 'OldValue', cast(T0.Quantity as varchar(18)) as 'NewValue', T2.UpdateDate, T4.[U_Name] as 'UpdatedBy', T2.[DocTime] FROM ADOC T2 JOIN ADO1 T1 ON T2.docentry = T1.docentry AND T2.Objtype = T1.Objtype and T1.Loginstanc = T2.LogInstanc-1 JOIN ADO1 T0 ON T2.docentry = T0.docentry AND T2.Objtype = T0.Objtype AND T1.LineNum = T0.LineNum INNER JOIN OUSR T4 ON T2.UserSign2 = T4.INTERNAL_K AND T0.LogInstanc = T2.Loginstanc WHERE T0.Quantity<>T1.Quantity AND T2.[DocStatus] = 'O' and T2.ObjType= 17 and T0.[LineStatus] = 'O' and T0.ObjType= 17

Thank you