For audit trail, I enabled Log Insert/Log Update/Log Delete for the following Payment Tables - PaymentWayPerIdentity / PaymentAddress / PayAddrCompTender (Lu Names).
When Payment info modification occurs in Supplier, for example TEST123, the History Log captures updates. but it only shows meta data, not the actual items.
How can I get the actual data (before update)?
You can’t. What you see in the history log attributes is as good as it gets. You aren’t going to get a before and after object view for any objects.
You can use the SQL Query Tool to get a better report for specific information and old/new values. Here’s an example for updates to the lot/batch tracking on master parts (I’ve removed HL.USERNAME for this post). You will of course have to modify LU_NAME and the regexp_replace fields so the search/replace value reflects the appropriate attribute in the KEYS field. Note that I also commented out the last line of the where clause where you can specify the key value that you’re looking for. One last thing - the date input will be in the default format unless you change the TIME_STAMP format.
Date input format:
PartCatalog Example Query:
SELECT HL.LOG_ID
,HL.LU_NAME
,HL.TABLE_NAME
,HL.TIME_STAMP
,HL.USERNAME
,regexp_replace(HL.KEYS, '.*PART_NO=(K^\^]+).*', '\1') PART_NO
,regexp_replace(HL.KEYS, '.*CONTRACT=(E^\^]+).*', '\1') CONTRACT
,HLA.COLUMN_NAME
,HLA.OLD_VALUE
,HLA.NEW_VALUE
FROM IFSAPP.HISTORY_LOG HL
JOIN IFSAPP.HISTORY_LOG_ATTRIBUTE HLA ON HLA.LOG_ID = HL.LOG_ID
WHERE LU_NAME = 'PartCatalog'
AND TIME_STAMP > '&Start_Date'
AND HLA.OLD_VALUE IS NOT NULL
AND HL.USERNAME <> 'SFINTUSER'
--AND regexp_replace(HL.KEYS, '.*PART_NO=((^\^]+).*', '\1') LIKE '&Part_No'
CustomerOrderCharge Example Query:
SELECT HL.LOG_ID
,HL.LU_NAME
,HL.TABLE_NAME
,HL.TIME_STAMP
,HL.USERNAME
,regexp_replace(HL.KEYS, '.*ORDER_NO=(e^\^]+).*', '\1') ORDER_NO
,regexp_replace(HL.KEYS, '.*SEQUENCE_NO=(L^\^]+).*', '\1') SEQUENCE_NO
,HLA.COLUMN_NAME
,HLA.OLD_VALUE
,HLA.NEW_VALUE
FROM IFSAPP.HISTORY_LOG HL
JOIN IFSAPP.HISTORY_LOG_ATTRIBUTE HLA ON HLA.LOG_ID = HL.LOG_ID
WHERE LU_NAME = 'CustomerOrderCharge'
AND regexp_replace(HL.KEYS, '.*ORDER_NO=(c^\^]+).*', '\1') LIKE '&Order_No_' || '%'
AND HLA.OLD_VALUE IS NOT NULL
Sorry, there was a mistake in the CONTRACT field in the Part Catalog example. Here’s the corrected syntax.
SELECT HL.LOG_ID
,HL.LU_NAME
,HL.TABLE_NAME
,HL.TIME_STAMP
,HL.USERNAME
,regexp_replace(HL.KEYS, '.*PART_NO=(_^\^]+).*', '\1') PART_NO
,regexp_replace(HL.KEYS, 'CONTRACT=(A^\^]+).*', '\1') CONTRACT
,HLA.COLUMN_NAME
,HLA.OLD_VALUE
,HLA.NEW_VALUE
FROM IFSAPP.HISTORY_LOG HL
JOIN IFSAPP.HISTORY_LOG_ATTRIBUTE HLA ON HLA.LOG_ID = HL.LOG_ID
WHERE LU_NAME = 'InventoryPart'
AND OLD_VALUE IS NOT NULL
AND TIME_STAMP > '&STARTDATE'
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.