How best to capture the Before and After values of a field using Table Event Rules

sheeba

Reputable Poster
We have a need to audit changes to a table - F0401 ; How can I capture the before and after value using Table Event Rules on F0401 after an update to the F0401 table is done.

It seems like there is no easy way to capture 'the before value' in the 'After Record is updated' event in the Table Event Rules.

Any ideas anyone?
 

jemez842

Active Member
The easiest way for the master tables is to use the interoperability function. Go into the processing options of P0100043 (supplier master MBF) and turn on the interoperability, then there's also an option to write the before record. Then you reference this version of the MBF on the supplier master program and it will start writing records to the F0401Z1. You can also do the same type of thing with AB and customer master records.

This doesn't capture direct updates, like if you have a custom program directly updating the F0401, or SQL updates, but does handle pretty much everything else.
 

Larry_Jones

Legendary Poster
Here's an idea:
Don't use JDE TERs.

As you have discovered it doesn't work like a true database trigger in that you don't have all the field values populated that you may need. In addition table updates outside of JDE aren't captured.

The alternative is to use Database Triggers to capture your before and after values
 

peterbruce

Legendary Poster
Sheeba,

Notwithstanding Larry's sage advice (seriously consider using Database table triggers), You can do a fetch on the table in the before/after update/insert/delete event which will provide all the column values. The values available in the actual event are only those values populated by update/insert/delete event rules.

However seriously consider using Database table triggers.
 

sheeba

Reputable Poster
Reply to Jemez post : Yep - we have it working like this with the before and after records being populated in the Z tables just as you mentioned. But with this the Z tables are growing and purging them would become an issue too .

So we are trying to save off the before and after in a custom table on one record as opposed to 2 records in Z which the interoperability does.

thanks for your response thou.
 

sheeba

Reputable Poster
Reply to Larry's post : We are considering this option as well - but client wants a temporary workaround until then. Thanks for your response.
 

sheeba

Reputable Poster
Sheeba,

Notwithstanding Larry's sage advice (seriously consider using Database table triggers), You can do a fetch on the table in the before/after update/insert/delete event which will provide all the column values. The values available in the actual event are only those values populated by update/insert/delete event rules.

However seriously consider using Database table triggers.

Yes - I think I have it working this way now --- here's how I have it working
1. did a fetch using a view in the Before Update event to get the old value(so I don't have to use a handle for F0401 - stored it onto a temp table
2. Read this value in the After Update event
3. Deleted the temp table record
4. Inserted both the before and after values into a work table.

Not THE best approach - but I guess a workaround till we figure out about database triggers.

But just a quick question --- since I am using TK User,TK Program ID , TK date and TK time to write to temp table (Part of Primary Key values) --- I am assuming these TK values will always have non-blank or non-null values in them however way the F0401 is accessed right? or should I default TK User and TK PID to some values just in case they are null or blank some time? I guess I have to test this anyways...

Thoughts are welcome thou.
 

peterbruce

Legendary Poster
Sheeba,

As Chan said, use Unique Key id (UKID) as an index column in the temp table. I would not rely on the populating of the "audit" columns in the F0401. 99% of processes may update them, but there could be the odd, rarely used process that does not.
 

sheeba

Reputable Poster
Thanks Chan and Peter. Appreciate your response.

I finally have it working

1. Did a fetch to F0401 in Before Update Event to get the before value
2. Did a fetch to F0401 in After Update Event to get the true after value (even if nothing was changed)
3. Wrote the before and after values if changed only --- to the Audit Table

All good in Fat Windows Client , Fat Web Client
But on the true Web Client - the After Update Event doesn't seem to work or maybe TK values are coming in as blank. will have to check the logs now. :(

Any ideas why? Is this to do with JAS or JDBJ ini settings on the JAS server?
 

DSauve

Legendary Poster
Did you build and deploy an update package for the table object (F0401) to your server(s)?
Is your audit table in your regular business data schema, or in another schema? If in another schema, have you created and activated OCM mappings for not only your System but also for your server(s)?
 

sheeba

Reputable Poster
It works when I update the F0401 using P04012 app
But when you update using a Custom App - it either seems like After Update Event is not fired or may be it is returning TK blank values.

However, both the above cases work on the Fat Windows Client and Fat Web Client but not via the true jde Web Link.
 

sheeba

Reputable Poster
Got that working with help from Oracle.

This is a known bug : Oracle Support Bug 18163361 (TABLE WITH TER, SERIALIZED SPECS NOT CLEARED AFTER DEPLOY UPDATE PKG TO SERVER) can be found at: https://support.oracle.com/epmos/faces/BugDisplay?id=18163361

Issue: Table Trigger doesnot work as needed on Web (works on Fat Windows and Fat Web) after an update package is installed .

Workaround for the Table Trigger to work on the Web:
Developer:
1. Back up the table data
2 . Delete the dddict, ddtext and glbtbl – ddb and xdb files from the spec folder
3. Generate the Table using OMW
4. Reload the table data

CNC:
1. Delete the glbtbl – ddb and xdb files from the spec folder on the server.
2. Clear F989999 and F989998
• deleted the entries listed below from F989999
/*
DELETE FROM [JDE_DV900].[DV900].[F989999]
WHERE [WBOID] IN ('TABLE-F0401', 'TBLEDSTR-F0401', 'TER-F0401-CURRENCY','TER-F0401-CURRENCY', 'TER-F0401-INSERTAFTER', 'TER-F0401-UPDATEBEFORE',
'TER-F0401Z1-CURRENCY')
*/
• deleted the entries listed below from F989998
/*
DELETE FROM [JDE_DV900].[DV900].[F989998]
WHERE [WBJOBID] = 'TABLE-F0401'
*/
3. Clear cache for Serialized Objects (generate the serialize spec using eGen )

4. It will convert xml spec to serialized spec for web client

The Table Trigger works as needed after the above was done!

Thanks to all who tried to help out with this issue.

regards - sheeba
 
Last edited:

sheeba

Reputable Poster
An update - we saw the same issue when the Table Trigger was deployed to PY (update package) :

The below is all my CNC did for it to work in PY :

deleted relevant entries from the F989998 and F989999. also cleared Serialized Object Cache from all the Web server instances

FYI - We did not have to regenerate the table F0401 .

Just updated this issue and resolution up here in jdelist so it helps folks who runs into the same problem as us!.

regards.
 
Top