Skip to Content
avatar image
Former Member

Resolving certain users activity in OITM/AITM

Hi, i am trying to figure out what a particular user has been modifying in OITM using the AITM table.

We have a UDF called U_Category

I am looking to create a query that will display the latest / most recent change to this UDF against the ItemCode and also display the previous value (what it was before it was modified by the particular user)

The output to look something like:-

Date Of Update, ItemCode, Previous Value, New Value, Modified By

26/06/2017, abc123, INV, RAN, ALAN

Please can anyone advise?

Thanks in advance.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Jun 26, 2017 at 03:32 PM

    Hi Alan,

    You can use this:

    SELECT 
    	T0.UpdateDate,
    	T0.ItemCode,
    	T1.U_Category AS 'Previous Value',
    	T0.U_Category AS 'New Value',
    	T2.USER_CODE
    FROM 
    	OITM T0
    	INNER JOIN AITM T1 ON T0.ItemCode = T1.ItemCode AND T1.LogInstanc = (SELECT MAX(LogInstanc) FROM AITM WHERE ItemCode = T0.ItemCode)
    	INNER JOIN OUSR T2 ON T0.UserSign2 = T2.INTERNAL_K
    WHERE 
    	T0.ItemCode = '[%0]'
    

    Hope it helps.

    Kind Regards,

    Diego Lother

    Add comment
    10|10000 characters needed characters exceeded