Item Master data - Change Log Query

[former_member349215]

Hi Experts,

We created a query for a client that shows Item Master Data Changes done in the system. However the Client wants to see the specific field that was changed for a specific item and also wants to see the old value and new value of this field. More or less similar to the change log - Show differences window that shows Changed Field, Previous Value and New Value.

Is it possible to get a query to display the three (3) extra columns?

Here's the Original Query we created.

SELECT T0.ItemCode, T0.ItemName, T1.ItmsGrpNam, T0.DfltWH, T0.UpdateDate, T0.AvgPrice,  T0.FrozenFor as 'On Hold?', T0.Canceled, T2.U_NAME as 'Added By', T3.u_NAME AS 'Updated by', T0.logInstanc as 'No of Changes to date' FROM AITM T0

INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod

LEFT JOIN OUSR T3 ON T0.UserSign2 = T3.USERid

INNER JOIN OUSR T2 ON T0.userSign = T2.userid WHERE T0.UpdateDate >=[%0] AND  T0.UpdateDate <=[%1]

Reply will be highly appreciated. Thanks

Kind Regards,

Pascale.

[KennedyT21 · Accepted solution]

Hi

Yes it is possible you need to link AITM with  logInstanc  - 1 on the above query

SELECT T0.ItemCode, T0.ItemName, T1.ItmsGrpNam, T0.DfltWH, T0.UpdateDate, T0.AvgPrice,  T0.FrozenFor as 'On Hold?', T0.Canceled, T2.U_NAME as 'Added By', T3.u_NAME AS 'Updated by', T0.logInstanc as 'No of Changes to date'

FROM AITM T0

Inner Join AITM c on C.Itemcode=T0.itemcode and  T0.logInstanc  = c.logInstanc  - 1

INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod

LEFT JOIN OUSR T3 ON T0.UserSign2 = T3.USERid

INNER JOIN OUSR T2 ON T0.userSign = T2.userid WHERE T0.UpdateDate >=[%0] AND  T0.UpdateDate <=[%1]

Cheers!

[former_member349215 · Accepted solution]

Hi Kennedy,

Thank you for your reply. Highly appreciated. I have executed your query, however the Client wants to see the specific field that was changed, the old value of the field and the new value just like the way show differences shows the three columns.

How can I incoporate the three columns in my existing query? Seeing as the show differences doesn't have a table that stores the values?

Kind Regards,

Pascale

[KennedyT21 · Accepted solution]

What are the fields,

C.Itemcode is ols value

T0.Itemcode is the New value

You can add the fields in the query... if not let me know what fields you need in the report

Cheers!

[kothandaraman_nagarajan]

Hi,

Please try this query for item description change log,

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]

Thanks