F0115(Phone number) Table audit track issue

Dev raj

Member
Hi All,

I got a requirement on audit track(Add,Update,Delete) of around 7 tables. Add and Update is working perfectly for all the tables but Delete is not working for F0115 table. The same code i have written for F01151,F0111 tables also but these two tables are tracking Delete as expected. Is there any way to handle F0115 table audit tracking on Delete ? Please help me.

Thanks in advance!

Regards,
Deva.
 
Deva,

It may help if you could post your code.

How is the functionality enabled? JDE Table triggers? Database table triggers? Or something else?

If you're using JDE Table triggers, it is possible that not all the columns are being populated by the delete function.
 
Hi Peter,

Thanks for your reply and it's JDE Table trigger.

Please find the below code and let me know if am wrong.
=============================================
TBLE: F0115
Event: Before Record is Deleted
Event GUID: 59425975-4277-455c-ae4e-b5c56e9aaeca
=============================================
----------------------------------------
Event Level Variables
----------------------------------------
evt_szCacheActionCode_CACTN [CACTN]
evt_szUserId_USER [USER]
evt_jdDateUpdated_UPMJ [UPMJ]
evt_szTimeLastUpdated_UPMT [UPMT]
evt_szWorkStationId_JOBN [JOBN]
evt_szPhoneNumber_PH1 [PH1]
----------------------------------------
00001 GetAuditInfo(B9800100.GetAuditInfo)
VA evt_szUserId_USER [USER] <- szUserName [USER]
VA evt_jdDateUpdated_UPMJ [UPMJ] <- jdDate [DTE]
VA evt_szTimeLastUpdated_UPMT [UPMT] <- mnTime [TME0]
VA evt_szWorkStationId_JOBN [JOBN] <- szWorkstation_UserId [JOBN]
00002 VA evt_szCacheActionCode_CACTN [CACTN] = "D"
00003 F0115.FetchSingle [Index 1: Address #, Line #, +]
TK Address Number [AN8] = TK Address Number [AN8]
VA evt_szPhoneNumber_PH1 [PH1] <- TK Phone Number [PH1]
00004 F550115B.Insert [Index 1: AN8,UPMJ,UPMT,CACTN]
TK Address Number [AN8] -> TK Address Number [AN8]
VA evt_szPhoneNumber_PH1 [PH1] -> TK Phone Number [PH1]
VA evt_szUserId_USER [USER] -> TK User ID [USER]
TK Program ID [PID] -> TK Program ID [PID]
VA evt_jdDateUpdated_UPMJ [UPMJ] -> TK Date - Updated [UPMJ]
VA evt_szTimeLastUpdated_UPMT [UPMT] -> TK Time - Last Updated [UPMT]
VA evt_szCacheActionCode_CACTN [CACTN] -> TK Cache Action Code [CACTN]
VA evt_szWorkStationId_JOBN [JOBN] -> TK Work Station ID [JOBN]

The same code is working for F01151,F0116,F0101. I tried passing all TK values into table instead of fetching but no use .



Thanks,
Deva
 
I have seen the trigger not fire if the DELETE is for more than one record. i.e. not using the full primary key.

Craig
 
Something I would add in addition to Craig's suggestion:
Keep in mind that the primary key of F0115 is the four columns AN8, IDLN, CNLN, and RCK7. Your audit table's primary key should probably have those four, plus at least UPMJ and UPMT to prevent duplicate inserts. And if you want to use the F0115 Fetch Single call, make sure to map all four TK columns, not just the AN8.
 
Deva,

I agree with the comments that Craig and Kim have made.

Your F0115.Fetch could conceivably return one record of multiple records, as you only use one of the four unique index columns.

I meant to ask what the issues were that indicated the delete tracking was not working? Is it that there was no record created in your tracking table? Is it that the trigger was not run as shown in a debug log?

We track changes on a number of tables, recording the entire row as well as an action code (Insert-A; Change-O-Old and N-New; Delete-D) and a database date and time stamp, using database triggers. We don't record the user ID though as the purpose is data replication, not auditing. Depending on your database and how your users and system (database) users are set up, you could also record the user ID.

JDE table triggers are actually business functions called Table Event Rules (TER).
 
Thanks to All !

I tested in both ways 1) AN8 which is having only one Phone record 2) AN8 which is having multiple Phone records in F0115 table. Also i coded in 3 diff ways,thought at least one way will work :( . 1) passing all the Primary keys in Fetchsingle but no use 2) Passing only AN8 but no use 3) Hard coded all primary key values then tried to Fetch but no use. No Use/Working means, It's inserting a record into my table with only audit info(ER Variables by BSFN) because those are not TK values and am not fetching from anywhere. At least if my table has AN8 value for that record then i can consider my Fetch single is not working but my table has blank An8 values.
PFA for more info.

Please let me know your thoughts.

Thanks,
Deva
 

Attachments

  • Table Data.JPG
    Table Data.JPG
    32.9 KB · Views: 7
Back
Top