Sales Order Changes within Last 5 Days

[sean_martin_fl]

Hi Guys,

Has anyone done a query that shows changes made to a sales order within the last 5 days, for example if lines have been added or deleted, items changed, quantity changed or delivery date changed.

I suspect I need to bring in all the history tables but wondered if anyone has anything they have done previously.

Kind regards

Sean Martin

[sean_martin_fl · Accepted solution]

Hi Guys,

Thanks for that, I thought I was on the right path, building a bigger query now

Thanks again

[KennedyT21]

Hi Sean..

If you need by the sales order number wise try this

SELECT * from adoc t0 inner join ado1 t1 on t0.docentry=t1.docentry

WHERE t0.objtype='17' and t0.DocNum = [%0]

Regards

Kennedy

[KennedyT21]

Hi Sean Martin..

Hope doing Good..

Try This

Select T0.DocNum, Max(T1.UpdateDate) 'Last Update', T0.DocDueDate

from ADOC T0

JOIN ADOC T1 on T1.DocNum = T0.DocNum AND T1.ObjType = '17'

where T0.ObjType = '17'  and  DateDiff(d,T1.UpdateDate,GETDATE()) =5

Group By T0.DocNum, T0.DocDueDate

Hope helpful

Regards

Kennedy

[kothandaraman_nagarajan]

Hi,

Try this query for only for quantity change:

SELECT T2.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 DateDiff(d,T2.UpdateDate,GETDATE()) =5

Thanks & Regards,

Nagarajan