Skip to Content
author's profile photo Pascale Khayeri Mwavaga
0
Pascale Khayeri Mwavaga

Item Master data - Change Log Query

Posted on Aug 11, 2016 at 10:42 AM 1.1k Views

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo Kennedy T
    Kennedy T
    Posted on Aug 11, 2016 at 10:57 AM
    1

    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!

    Add a comment
    10|10000 characters needed characters exceeded

    Comment on This Answer
  • author's profile photo Nagarajan K
    Nagarajan K
    Posted on Aug 11, 2016 at 02:55 PM
    0

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.