Query for Change Log

[former_member202778]

Hi,

I want a query to show the changes done to the master data. Separate query for BP Master Data and Item Master Data..

Regrads

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query for item description change log. Same way you can try for other fields:

SELECT T0.[UpdateDate],T0.[ItemCode], T2.[ItemName] as Newvalue, T1.[ItemName]as oldvalue, t3.[U_Name] as CreatedUser, t4.[U_Name] FROM AITM T0 left join AITM T1 on t1.itemcode = t0.itemcode and t1.loginstanc= t0.loginstanc-1 left join OITM T2 on t2.itemcode = t0.itemcode left join  OUSR t3 on t3.userid = t2.usersign left join OUSR t4 on t4.userid =t2.usersign2 WHERE  T2.[ItemName] <> T1.[ItemName] and datediff(dd,T1.[UpdateDate],getdate()) <=0 group by T0.[UpdateDate],T0.[ItemCode], T2.[ItemName], T1.[ItemName],t3.[U_Name],t4.[U_Name] order by T0.[UpdateDate]

For Business partner change log, try this query:

SELECT
T2.[CardCode], T2.[CardName], T2.[GroupNum] as Newvalue,
T1.[GroupNum] as Oldvalue ,  T3.[U_NAME] as UpdatedUser , T4.[U_NAME]
as CreatedUser FROM ACRD T0 left join ACRD T1 on t0.[CardCode] =
T1.[CardCode] and t0.loginstanc = t1.loginstanc-1  left JOIN OCRD T2
ON T0.cardcode = T2.CardCode left join  OUSR T3 on t3.userid =
t0.usersign2 left JOIN OUSR T4 ON   T4.USERID = T1.userSign  left
JOIN OCTG T5 ON T0.GroupNum = T5.GroupNum WHERE T2.[GroupNum] <>
T1.[GroupNum] and datediff(dd, T1.[updateDate] ,getdate())<=0
GROUP BY T2.[CardCode], T2.[CardName], T2.[GroupNum], T1.[GroupNum] ,
T3.[U_NAME] , T4.[U_NAME]

Hope helpful.

Thanks & Regards,

Nagarajan

[former_member202778 · Accepted solution]

Hi Nagarajan,

Is there any possible way to pick the updated time for each instanc??

Thanks & Regards

[kothandaraman_nagarajan · Accepted solution]

Hi,

Not possible to get update time.

Thanks & Regards,

Nagarajan

[former_member202778 · Accepted solution]

Hi Nagarajan,

Can i have update date in this, when i put the update date its taking for all instances and cant group..

Thanks & Regards

[kothandaraman_nagarajan · Accepted solution]

Hi,

Do you want select update date in above queries?

Thanks & Regards,

Nagarajan

[former_member202778 · Accepted solution]

Hi Nagarajan,

Yes. I would like to create report which can be filtered according to update date

Thanks

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this for item master data:

SELECT T0.[UpdateDate],T0.[ItemCode], T2.[ItemName] as Newvalue, T1.[ItemName]as oldvalue, t3.[U_Name] as CreatedUser, t4.[U_Name] FROM AITM T0 left join AITM T1 on t1.itemcode = t0.itemcode and t1.loginstanc= t0.loginstanc-1 left join OITM T2 on t2.itemcode = t0.itemcode left join  OUSR t3 on t3.userid = t2.usersign left join OUSR t4 on t4.userid =t2.usersign2 WHERE  T2.[ItemName] <> T1.[ItemName] and T0.[UpdateDate] between  [%0] and [%1] group by T0.[UpdateDate],T0.[ItemCode], T2.[ItemName], T1.[ItemName],t3.[U_Name],t4.[U_Name] order by T0.[UpdateDate]

For BP:

SELECT

T2.[CardCode], T2.[CardName], T2.[GroupNum] as Newvalue,

T1.[GroupNum] as Oldvalue ,  T3.[U_NAME] as UpdatedUser , T4.[U_NAME]

as CreatedUser FROM ACRD T0 left join ACRD T1 on t0.[CardCode] =

T1.[CardCode] and t0.loginstanc = t1.loginstanc-1  left JOIN OCRD T2

ON T0.cardcode = T2.CardCode left join  OUSR T3 on t3.userid =

t0.usersign2 left JOIN OUSR T4 ON   T4.USERID = T1.userSign  left

JOIN OCTG T5 ON T0.GroupNum = T5.GroupNum WHERE T2.[GroupNum] <>

T1.[GroupNum] and T1.[UpdateDate] between [%0] and [%1]

GROUP BY T2.[CardCode], T2.[CardName], T2.[GroupNum], T1.[GroupNum] ,

T3.[U_NAME] , T4.[U_NAME]

Hope helpful.

Thanks & Regards,

Nagarajan

[nikunjmehta2290 · Accepted solution]

Hello,

I have used this one but it's gives me multiple records.

SELECT T2."CardCode", T2."CardName", --T2."GroupNum", T1."GroupNum", T2."HousBnkCry", T2."HouseBank", T2."HousBnkAct", T1."CreateDate", T1."HousBnkCry", T1."HouseBank", T1."HousBnkAct", T1."UpdateDate"

FROM ACRD T0 left join ACRD T1 on T0."CardCode" = T1."CardCode" and T0."LogInstanc" = T1."LogInstanc" -1

left JOIN OCRD T2 ON T0."CardCode" = T2."CardCode"

left join OUSR T3 on T3."USERID" = T0."UserSign2"

left JOIN OUSR T4 ON T4."USERID" = T1."UserSign"

left JOIN OCTG T5 ON T0."GroupNum" = T5."GroupNum"

WHERE T2."HouseBank" <> T1."HouseBank"

GROUP BY T2."CardCode", T2."CardName", T2."GroupNum", T1."GroupNum" , T2."HousBnkCry", T2."HouseBank", T2."HousBnkAct" ,T1."HousBnkCry", T1."HouseBank", T1."HousBnkAct" , T1."CreateDate", T1."UpdateDate", T0."CreateDate", T0."UpdateDate"

[Former Member]

Dear Ibrahim,

You can suffice your concern by Change Log an SAP B1 stand red function as below...

Open the BP/Item master data form...

Choose the concern master data and click on change log option from Tools menu which will reflect the each instance which you can see and choose to show difference in each and every instance.

This function makes the user notify about the entire change/update on the document.

Hope, it will help you.....

[Former Member]

You should query the following tables:

ACRD - for BP Master Data.

AITM - for Items Master Data.

Beni.