In SAP B1, how do you create (mssql code) an alert when the Description in Foreign Lang. field is modified on the OITM table

[Former Member]

We use the Description in Foreign Lang. field on the Item master to store the item's revision number.  I need to create an alert for our cost accountant when that value is updated.  Preferably I'd like to display:  Item ID, previous value (Description in Foreign Lang.), new value (Description in Foreign Lang.), last update date.

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query:

SELECT T0.[UpdateDate],T0.[ItemCode], T2.[FrgnName] as Newvalue, T1.[FrgnName] as oldvalue, t3.[U_Name] as CreatedUser, t4.[U_Name],T0.[UpdateDate] FROM AITM T0 left join AITM T1 on t1.itemcode = t0.itemcode and t1.loginstanc = t0.loginstanc 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.[FrgnName] <> T1.[FrgnName] and datediff(dd,T1.[UpdateDate],getdate()) =0 group by T0.[UpdateDate],T0.[ItemCode], T2.[FrgnName], T1.[FrgnName],t3.[U_Name],t4.[U_Name],T0.[UpdateDate] order by T0.[UpdateDate]

Thanks & Regards,

Nagarajan

[Former Member]

Thank you both for your suggestions.  I appreciate your your time involved.

Regards,

Tony Banks

[Former Member]

Hi,

Try:

SELECT T0.[ItemCode], T1.[FrgnName] as Newvalue, T0.[FrgnName] as oldvalue,T0.[UpdateDate],MAX(T0.loginstanc)

FROM OITM T1

join AITM T0 on t1.itemcode = t0.itemcode

WHERE T1.[FrgnName] <> T0.[FrgnName]

group by T0.[ItemCode], T1.[FrgnName], T0.[FrgnName],T0.[UpdateDate]

Thanks,

Gordon