cancel
Showing results for 
Search instead for 
Did you mean: 

Activities log files for audit

too_muihwee
Participant
0 Kudos

Hi all,

I have got a request from customer that they need the following in SAP B1:

  • Account Activities (by user) ; showing changes/amendments and posts done by user, this includes those activities with no impacts to the figures in the book e.g. master data, setting etc.
  • Transactional log ; what has been posted to the books and by whom

Anyone have similar experience on extracting this information from SAP B1 tables? Thanks

Accepted Solutions (0)

Answers (2)

Answers (2)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

1.In general, the table starts with "A" holds history of main table. But B1 does not have history log for all table.

2. It is not easy capture in query to show all fields that has been changed by per user.

3. For example, you can query changes of item description from item master data.

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 datediff(dd,T1.[UpdateDate],getdate()) <=0
group by T0.[UpdateDate],T0.[ItemCode], T2.[ItemName],
T1.[ItemName],t3.[U_Name],t4.[U_Name] order by T0.[UpdateDate]

same way you can create other field as well.

Transaction log:

Try this query for posting of sales order and purchase order. same way you can create other document as well.

SELECT T0.[DocNum], T1.[ItemCode], T2.[U_NAME] FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OUSR T2 ON T0.UserSign = T2.USERID WHERE T0.[DocDate]  = CONVERT(VARCHAR(10),GETDATE(),110)

union all

SELECT T0.[DocNum], T1.[ItemCode], T2.[U_NAME] FROM OPOR T0  INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OUSR T2 ON T0.UserSign = T2.USERID WHERE T0.[DocDate]  = CONVERT(VARCHAR(10),GETDATE(),110)

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Hi,

You have to pull information from ADOC table .

This table contains all the change logs .

Plz check these links as well: