JDE Audit and Capturing USER ID

erodr154

Member
Hello,

I am analyzing how the USER ID and PROGRAM ID are captured by jde out of box audit.

Reading the trigger from the DB directly, i see that, for my scenarios, it would use the "audit type 1" which means the user ID is retrieved from "old.IOCFRUSER". This CFRUSER is the field itself on my adt table (i am looking at F4108_ADT, which has the trigger).

What i dont understand is how i manages to capture the correct user. If i insert a record on F4108 using P4108, field IOCFRUSER will have my ID. However somebody else deletes the record, "old.IOCFRUSER" should still be my id, because it is the one that was on the table im reading.

Am i missing something or is there something else done in the background? I see a field called "is delete" which im not certain how it is populated. Maybe hte table is updated before deleting? (thats not on the trigger).
 
It isn't obvious at first look. The trick that is being used is that the JDE middleware converts delete actions to updates passing a flag of ISDELETE=Y via that column. This allows the before and after to exist from the perspective of the trigger since instead of just deleting the row, the first trigger to fire is an update of the row. The trigger processing then does the eventual delete.

[Begin Long Explanation]

If we take F0101 as an example on an Oracle database (I don't have a SQL Server or DB/400 instance with CFR turned on handy. The overall trigger layout should be similar if you are not on an Oracle DB)

You will have the following triggers, where Rxx is a row level trigger and Sxx is a statement level trigger with the final letter A for After and B for Before:

F0101_CFRA_RDA
F0101_CFRA_RIA
F0101_CFRA_RUA
F0101_CFRA_SUA
F0101_CFRA_SUB

and a package called F0101_ADT_CFRA_PKG

The flow for a delete from JDE is:

-- User deletes record from the front-end

-- JDEBASE checks if the table is CFR activated and if so converts the DELETE to an UPDATE updating the record with the audit information for the user doing the delete and sets the ABISDELETE column to 'Y'. I'll call this a pseudo-delete.

-- On the pseudo-delete the following triggers fire in this order:

### This trigger is clearing the package variable which is session local so that any records from a previous delete are removed
#1 F0101_CFRA_SUB (Before Update Statement)
Code:
                      before update on CRPDTA.F0101_ADT
                        begin
                        -- clear the list of rowids
                        CRPDTA.F0101_ADT_CFRA_PKG.ids := CRPDTA.F0101_ADT_CFRA_PKG.empty;
                      end;


#2 F0101_CFRA_RUA (After Update on Row)
### This is the after update trigger which adds the ROWID to the session local package variable so that it can be used in another trigger to perform the actual delete
### you won't have the cfr_timestamp variable in my example. That is a customization I have done. The standard trigger only captures to the second which makes
### ordering CFR records done in batch at high speed impossible. I have used all the time precision Oracle can give which is to the nanosecond.
### We use CFR data as a poor mans data capture process and publish change events to an ESB to feed other systems. JDE tends to do a lot of repeated updates to the same
### record during certain batch process and often does these one after the other. Without a high precision timestamp we couldn't figure out the order of these updates.
Code:
      begin
         -- Set timestamp at beginning of trigger to assure a consistent, high resolution timestamp 
          -- for both before and after records.
          cfr_timestamp := to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS.FF6');

          if (:new.ABISDELETE = 'Y') then
             -- JDB delete
             -- add this rowid to the list
             CRPDTA.F0101_ADT_CFRA_PKG.ids(CRPDTA.F0101_ADT_CFRA_PKG.ids.count+1) := :new.rowid;                     <<---  It is adding the ROWID of the record being pseudo-deleted to the session variable
          else
             -- Don't log any thing if the audited columns did not change.
              -- As per the requirements of the FDA CFR project.
             << collapsed a bunch of code for actual updated where ABISDELETE = 'N' >>
          end if;
      end;

#3 F0101_CFRA_SUA (After Update Statement)

## This is where the delete actually happens by reading ROWID's from the package session variable and executing the deletes.
## This will usually just be a single row but depending on the JDE screen there might be a group of records that are pseudo-
## deleted
Code:
         begin
           -- JDB delete rows in list
           for num in 1 .. CRPDTA.F0101_ADT_CFRA_PKG.ids.count loop
              delete from CRPDTA.F0101_ADT where rowid = CRPDTA.F0101_ADT_CFRA_PKG.ids(num);              <<--- Each of these deletes fires off the After Delete For Each Row trigger.
           end loop;
         end;


#4 F0101_CFRA_RDA (After Delete Row)

### This delete trigger is fired after the record has been updated with the front-end user who initiated the pseudo-delete.
### You'll see another customization in my example. We use GUID's to line up before and after records and
### OW Without GUID gets logged when updates/deletes are done in certain ways from the front end.
### I am just generating a UUID here using a Java stored procedure call.

Code:
       begin

           if (:old.ABISDELETE = 'Y') then
             -- JDB delete
             guid := :old.ABCFRGUID;
             if (guid = 'OW Without GUID') then
             -- Custom
             -- Generate a GUID for updates done from a JDE Select button          
            guid := JDE.GenerateUUID();

            audittype := '2';
            user := substr(ora_login_user, 1, 10);
            workstation := substr(sys_context('userenv', 'host'), 1, 15);
            select program into programid from v$session where audsid = userenv('sessionid') and user in (select user from dual);
         else
           audittype := '1';
           user := :old.ABCFRUSER;                         <<-------------------  This is now the updated CFRUSER that reflects the JDE user who initiated the pseudo-delete from the front-end
           workstation := :old.ABCFRMKEY;
           programid := :old.ABCFRPID;
        end if;

I hope this helps.

As a side note, when OW Without GUID is referenced the CFR processing works a bit differently and you'll see that the code actually assigns the 'user' variable which gets put into the audit record by taking the ora_login_user variable. ora_login_user will just be the database proxy user and *not* the JDE application user. This doesn't apply to many applications but whenever OW Without GUID is used you won't be able to audit the actual JDE user who did the update/delete. This support document describes this a bit further E1: CFR11: OWNOGUID Shows in A43008 Audit Table (Doc ID 1171893.1)
 
Back
Top