Results 1 to 7 of 7

Thread: F0115(Phone number) Table audit track issue

  1. #1

    F0115(Phone number) Table audit track issue

    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.

  2. #2
    Senior Member peterbruce's Avatar
    Join Date
    Jan 2004
    Location
    NSW Australia
    Posts
    2,164
    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.
    Thanks, Peter

    "Give a person a fish, feed them for a day, teach them how to fish, feed them for a lifetime."

    E9.1 TR9.1.2.1, Enterprise Server: Sun, Database Server: Sun, Oracle DB: 11g, Weblogic.
    Create!form 7

  3. #3
    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

  4. #4
    Senior Member craig_welton's Avatar
    Join Date
    Oct 2000
    Location
    Litchfield, CT
    Posts
    1,042
    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
    Craig Welton
    PatWel Group Inc.
    http://www.patwel.com
    Home of the FREE JDE Object Browser, JDETrace, NERDup and BusBuild+ Tools

    E1 9.0 8.98.4.2 Wintel SQL 2008
    E1 9.2 9.2.1.4 iSeries

  5. #5
    Member
    Join Date
    Mar 2009
    Location
    Phoenix, AZ USA
    Posts
    68
    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.
    Regards,
    Kim Schmidt

    - E1 9.1.4 ,TR 9.2.2 , iSeries, DB2
    - formerly Xe, 8.0, SQL Server, Oracle

  6. #6
    Senior Member peterbruce's Avatar
    Join Date
    Jan 2004
    Location
    NSW Australia
    Posts
    2,164
    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, Peter

    "Give a person a fish, feed them for a day, teach them how to fish, feed them for a lifetime."

    E9.1 TR9.1.2.1, Enterprise Server: Sun, Database Server: Sun, Oracle DB: 11g, Weblogic.
    Create!form 7

  7. #7

    Unhappy

    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
    Attached Images Attached Images

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
The legal restrictions and terms of use applicable to this site are available here.
Use of this site signifies your agreement to the terms of use.
JDELIST is NOT affiliated with JD Edwards® & Company, Oracle or Peoplesoft. Contents of this site are neither endorsed nor approved by JD Edwards® & Company and, or Oracle.