Queries off the Change Log

[Former Member]

I am wanting to create a query off the change log differences screen. I have located the tables that I need for the change log itself, but when it comes to the differences I am having no luck. Have any of you done this? Please let me know if you know what the table name is for this, or how to work around that.

Thanks!

Monica

[Former Member · Accepted solution]

You could start with this query to find all fields for a specific Sales Order and go from there:

Select * from DBO.ADOC T0

INNER JOIN DBO.ADO1 T1 ON T1.DocEntry = T0.DocEntry

WHERE T0.ObjType = 17 AND T0.DocNum = [%0\]

Thanks,

Gordon

[Former Member]

Here is a query I made to monitor certain changes BP's as required by our sudit department - Hope it helps - Andy

SELECT T0.CreateDate, T0.Updatedate AS 'Date Of Change',T2.U_Name 'Changed By', T0.CardCode,T0.CardName AS 'New Name',T1.CardName AS 'Old Name',T0.Address AS 'New Address', T1.Address AS 'Old Address', T0.Country AS 'New Country', T1.Country AS 'Old Country', T0.Phone1 AS 'New Number',T1.Phone1 AS 'Old Number',T0.CreditLine AS 'New Credit Limit',T1.CreditLine AS 'Old Credit Limit',T3.Pymntgroup AS 'New Terms',T4.Pymntgroup AS 'Old Terms', T0.DebPayacct AS 'New Control Acct',T1.DebPayAcct AS 'Old Control Acct', T0.Deleted,T5.DocNum'Sales Orders Affected',T0.LogInstanc

FROM ACRD T0 inner join ACRD T1 on T0.CardCode = T1.CardCode and

T0.LogInstanc -1 = T1.LogInstanc and T0.CardCode = T1.CardCode and

T0.CardType = 'C'

INNER JOIN OUSR T2 ON T0.UserSign = T2.userid

INNER JOIN OCTG T3 ON T0.GroupNum = T3.GroupNum

INNER JOIN OCTG T4 ON T1.GroupNum = T4.GroupNum

INNER JOIN ORDR T5 ON T0.CardCode = T5.Cardcode

WHERE T0.UpdateDate >= '[%0]' and T0.UpdateDate <= '[%1]' and T5.DocDate >= '[%0]' AND T0.Cardname<> T1.CardName OR T0.UpdateDate >= '[%0]' and T0.UpdateDate <= '[%1]' and T5.DocDate >= '[%0]' and T0.Creditline <> T1.Creditline OR T0.UpdateDate >= '[%0]' and T0.UpdateDate <= '[%1]' and T5.DocDate >= '[%0]' and T0.GroupNum <> T1.GroupNum OR T0.UpdateDate >= '[%0]' and

T0.UpdateDate <= '[%1]' and T5.DocDate >= '[%0]' and T0.Address <> T1.Address OR T0.UpdateDate >= '[%0]' and T0.UpdateDate <= '[%1]' and T5.DocDate >= '[%0]' and T0.Phone1 <> T1.Phone1 OR

T0.UpdateDate >= '[%0]' and T0.UpdateDate <= '[%1]' and T5.DocDate >= '[%0]' and T1.Country <>T0.Country

[former_member187989]

hi monica,

Query like this

SELECT *  FROM ADOC T0 WHERE T0.ObjType  = 15

Jeyakanthan

[Former Member]

Monica,

The different documents have different tables to hold change logs. Which of documents are you interested in?

Thanks,

Gordon

[Former Member]

Hi Gordon,

I am doing this off of Sales Orders. I found the table ADOC for the change log, but no luck on the differences screen.

-Monica