12-11-2018 2:20 PM
Hi Experts,
I request your collaboration indicating me the table or tables and fields to obtain the user who made each of the changes found in the difference table in the change log of business partners master data.
Thanks a lot.
Best Regards
Diego Achury.
12-11-2018 2:55 PM
12-11-2018 3:55 PM
Hi Tammy, thanks for answer.
I´ve got this error: Invalid object name CDPOS or CDHDR, triying to select * from CDPOS or select * from CDHDR in the SAP B1 database.
Could you tell me wich database do I have to query please?
Thanks.
12-11-2018 4:31 PM
12-11-2018 10:01 PM
12-28-2018 6:09 PM
Hello Diego,
You would need to use the tables of ORCD, ARCD (Change History for Business Partners) and OUSR in SAP B1. I do not have an SQL which does the review of Business Partners, but I do have an example for you of how One Customer requested I create an Alert Message for a change in status on the Item Master. SQL says show me the Item Number which has had a status change in the past 3 days. Again, just an example of one way it can be done since I am not sure which fields you want to zero in on (if any)
--A-AD Alert Mess Item Change Status Ver 1 ZP 2018 10 31
--DESCRIPTION: SQL is part of series to check changes made on Item Master Data Record in the past three days.
--USAGE: Alert Message
--AUTHOR(s):
--Version 1 Zal Parchem 31 October 2018
SELECT DISTINCT
'Status' AS 'Change Type',
T2.ItemCode,
T2.validFor AS 'Status Changed To',
T0.validFor AS 'Status Changed From'
FROM AITM T0
LEFT OUTER JOIN AITM T1
ON T1.ItemCode = T0.ItemCode
AND T1.LogInstanc = T0.LogInstanc - 1
LEFT OUTER JOIN OITM T2
ON T2.ItemCode = T0.ItemCode
WHERE
T2.validFor < > T0.validFor
AND T0.UpdateDate BETWEEN (GETDATE () - 3) AND GETDATE( )
FOR BROWSE
Take a look and see what you think...
Best Regards, Zal