Skip to Content
0

Resolving certain users activity in OITM/AITM

Jun 26, 2017 at 08:03 AM

35

avatar image
Former Member

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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
DIEGO LOTHER Jun 26, 2017 at 03:32 PM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Perfect..

Thankyou for replying..

0