on 06-12-2014 3:10 AM
Hi all,
I have got a request from customer that they need the following in SAP B1:
Anyone have similar experience on extracting this information from SAP B1 tables? Thanks
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
89 | |
7 | |
6 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.