Friday, 6 June 2014

Auditing in JD Edwards–a quick guide

Here is a joke: 

Q:  When is a table not a table?

A:  When you’ve enabled 21CFR11 functionality in E1…

How funny was that?  So, this is the case.  When you enable auditing, the table becomes a view.  The view is based over the tableName_ADT and audit information is written to AtableNameNoF

Here the the SQL that is run, from this you can tell exactly what is going on.  This simple example activates auditing on the F43008 – a common requirement.

grant create procedure to dd910;

grant create trigger to dd910;

grant select on sys.v_$session to dd910;

grant create procedure to sy910;

grant create trigger to sy910;

grant select on sys.v_$session to sy910;

grant create procedure to testdta;

grant create trigger to testdta;

grant select on sys.v_$session to testdta;

Create System – 910NJ (this is Non Julian in the advanced options, also take off “OCM data source” in advanced options)

OCM Map F9500003, F9500004 and F9500006 to System – 910NJ

Generate above tables to System – 910NJ

Dbtemplates.exe from a fat client (this installs trigger templates into the database)

                This inserts the templates into INSERT INTO DVFINDTA.F986112 NOTE – business data.  You are not going to be able to activate in another environment!

clip_image002

P9500001 to add pathcode entries, do from fat client.

clip_image004

Add DV910 – enter the SY910 owner password

Then add a table setup.  Choose the columns next next

It’ll create the A version of the table

Keyed columns will be audited by default, add any additional columns you want in your audit trail table.

Okay, now the interesting part – enabling auditing on a table

Choose  a small table first, you’ll see the SQL it runs below – and you’ll realise why.

drop table DVFINDTA.F43008_ADT;

CREATE TABLE DVFINDTA.F43008_ADT (APDCTO NCHAR(2), APARTG NCHAR(12), APDL01 NCHAR(30), APALIM NUMBER, APRPER NUMBER, APATY NCHAR(1)) tablespace dvfindtat         ;                                 storage ( pctincrease 0 )

GRANT ALL ON DVFINDTA.F43008_ADT TO PUBLIC ;

CREATE  UNIQUE INDEX DVFINDTA.F43008_ADT_0 ON DVFINDTA.F43008_ADT (APDCTO ASC, APARTG ASC, APALIM ASC, APRPER ASC, APATY ASC) tablespace dvfindtai  storage ( pctincrease 0 );

ALTER TABLE DVFINDTA.F43008_ADT ADD CONSTRAINT F43008_ADT_PK PRIMARY KEY (APDCTO, APARTG, APALIM, APRPER, APATY) ;

CREATE  INDEX DVFINDTA.F43008_ADT_3 ON DVFINDTA.F43008_ADT (APRPER ASC, APDCTO ASC, APARTG ASC, APATY ASC) tablespace dvfindtai  storage ( pctincrease 0 );

CREATE  INDEX DVFINDTA.F43008_ADT_4 ON DVFINDTA.F43008_ADT (APARTG ASC) tablespace dvfindtai storage ( pctincrease 0 );

INSERT INTO DVFINDTA.F43008_ADT (APDCTO, APARTG, APDL01, APALIM, APRPER, APATY) SELECT APDCTO, APARTG, APDL01, APALIM, APRPER, APATY FROM DVFINDTA.F43008 ;

commit;

DROP TABLE DVFINDTA.F43008;

ALTER TABLE DVFINDTA.F43008_ADT ADD (APCFRGUID NCHAR(36) DEFAULT ' ' ) ;

ALTER TABLE DVFINDTA.F43008_ADT ADD (APCFRMKEY NCHAR(15) DEFAULT ' ' ) ;

ALTER TABLE DVFINDTA.F43008_ADT ADD (APCFRPID NCHAR(10) DEFAULT ' ' ) ;

ALTER TABLE DVFINDTA.F43008_ADT ADD (APCFRUSER NCHAR(10) DEFAULT ' ' ) ;

ALTER TABLE DVFINDTA.F43008_ADT ADD (APISDELETE NCHAR(1) DEFAULT ' ' ) ;

ALTER TABLE DVFINDTA.F43008_ADT ADD (APCFRSEQN NUMBER DEFAULT 0 ) ;

--Note all of the above could take ages, if you make them “NOT NULL”, they should run a lot quicker!

CREATE VIEW DVFINDTA.F43008 AS SELECT APDCTO , APARTG , APDL01 , APALIM , APRPER , APATY  FROM DVFINDTA.F43008_ADT;

GRANT ALL ON DVFINDTA.F43008 TO PUBLIC;

DROP PACKAGE DVFINDTA.F43008_CFRA_PKG;

create or replace package DVFINDTA.F43008_ADT_CFRA_PKG

                as

type ridArray is table of rowid index by binary_integer;

  ids ridArray;

  empty ridArray;

end;

--Now we get into the trigger body

--remember grants to ROLES do not apply in trigger BODY – so you must run the specific grants to sys.v$session to the data owner.  Don’t

--rely on something nice and fancy that your DBA did, it must be specific to that object.

5 triggers and a package are created

create or replace trigger DVFINDTA.F43008_CFRA_RDA

after delete on DVFINDTA.F43008_ADT

for each row

declare

  guid char(36);

  user char(10);

  workstation char(15);

  programid varchar2(48);

  audittype char(1) := '1';

begin

  if (:old.APISDELETE = 'Y') then

    -- JDB delete

    guid := :old.APCFRGUID;

    if (guid = 'OW Without GUID') then

      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.APCFRUSER;

      workstation := :old.APCFRMKEY;

      programid := :old.APCFRPID;

    end if;

  else

    -- 3rd party delete

    audittype := '3';

    guid := 'Third Party';

    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);

  end if;

  -- write audit record for delete

  insert into DVFINDTA.A43008 (APCFRGUID, APDCTO ,APARTG ,APALIM ,APRPER ,APATY , APB4ORAFTR, APCFRACTION,

    APDL01,

    APAUDITTYPE, APCFRUSER, APCFRMKEY, APDATETIME, APCFRPID) values

    (guid, :old.APDCTO ,:old.APARTG ,:old.APALIM ,:old.APRPER ,:old.APATY , 'B', '3',

    :old.APDL01,

    audittype, user, workstation,

    to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')||'.000000', substr(programid, 1, 10));

end;

create or replace trigger DVFINDTA.F43008_CFRA_RIA

before insert on DVFINDTA.F43008_ADT

for each row

declare

  guid char(36);

  user char(10);

  workstation char(15);

  programid varchar2(48);

  audittype char(1) := '1';

begin

  if (:new.APCFRSEQN = 1) then

    -- JDB insert

    guid := :new.APCFRGUID;

    if (guid = 'OW Without GUID') then

      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);

      :new.APCFRUSER := user;

      :new.APCFRMKEY := workstation;

      :new.APCFRPID  := substr(programid, 1, 10);

    else

      audittype := '1';

      user := :new.APCFRUSER;

      workstation := :new.APCFRMKEY;

      programid := :new.APCFRPID;

    end if;

  else

    -- 3rd party insert

    audittype := '3';

    guid := 'Third Party';

    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);

    :new.APCFRGUID := guid;

    :new.APCFRUSER := user;

    :new.APCFRMKEY := workstation;

    :new.APCFRPID  := substr(programid, 1, 10);

  end if;

  -- write audit record for insert

  insert into DVFINDTA.A43008 (APCFRGUID, APDCTO ,APARTG ,APALIM ,APRPER ,APATY , APB4ORAFTR, APCFRACTION,

    APDL01,

    APAUDITTYPE, APCFRUSER, APCFRMKEY, APDATETIME, APCFRPID) values

    (guid, :new.APDCTO ,:new.APARTG ,:new.APALIM ,:new.APRPER ,:new.APATY , 'A', '1',

    :new.APDL01,

    audittype, user, workstation,

    to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')||'.000000', substr(programid, 1, 10));

end;

create or replace trigger DVFINDTA.F43008_CFRA_RUA

before update of APDCTO ,APARTG ,APALIM ,APRPER ,APATY , APDL01,  APISDELETE on DVFINDTA.F43008_ADT

for each row

declare

  guid char(36);

  user char(10);

  workstation char(15);

  programid varchar2(48);

  audittype char(1) := '1';

begin

  if (:new.APISDELETE = 'Y') then

    -- JDB delete

    -- add this rowid to the list

    DVFINDTA.F43008_ADT_CFRA_PKG.ids(DVFINDTA.F43008_ADT_CFRA_PKG.ids.count+1) := :new.rowid;

  else

    -- Don't log any thing if the audited columns did not change.

    -- As per the requirements of the FDA CFR project.

    if (:old.APDCTO <> :new.APDCTO or :old.APARTG <> :new.APARTG or :old.APALIM <> :new.APALIM or :old.APRPER <> :new.APRPER or :old.APATY <> :new.APATY )

       or (:old.APDL01 <> :new.APDL01)

       or (:old.APDL01 is null and :new.APDL01 is not null)

       or (:old.APDL01 is not null and :new.APDL01 is null)

    then

      -- update, not delete

      if (:new.APCFRSEQN = :old.APCFRSEQN) then

        -- 3rd party update

        audittype := '3';

        guid := 'Third Party';

        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);

        :new.APCFRGUID := guid;

        :new.APCFRUSER := user;

        :new.APCFRMKEY := workstation;

        :new.APCFRPID  := substr(programid, 1, 10);

      else

        -- JDB update

        guid := :new.APCFRGUID;

        if (guid = 'OW Without GUID') then

          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);

          :new.APCFRUSER := user;

          :new.APCFRMKEY := workstation;

          :new.APCFRPID  := substr(programid, 1, 10);

        else

          audittype := '1';

          user := :new.APCFRUSER;

          workstation := :new.APCFRMKEY;

          programid := :new.APCFRPID;

        end if;

      end if;

      -- write audit record for update - "before" data

      insert into DVFINDTA.A43008 (APCFRGUID, APDCTO ,APARTG ,APALIM ,APRPER ,APATY ,APB4ORAFTR, APCFRACTION,

        APDL01,

        APAUDITTYPE, APCFRUSER,APCFRMKEY, APDATETIME, APCFRPID) values

        (guid, :old.APDCTO ,:old.APARTG ,:old.APALIM ,:old.APRPER ,:old.APATY , 'B', '2',

        :old.APDL01,

        audittype,:old.APCFRUSER, :old.APCFRMKEY,

        to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')||'.000000', :old.APCFRPID);

      -- write audit record for update - "after" data

      insert into DVFINDTA.A43008 (APCFRGUID, APDCTO ,APARTG ,APALIM ,APRPER ,APATY ,APB4ORAFTR, APCFRACTION,

        APDL01,

        APAUDITTYPE, APCFRUSER,APCFRMKEY, APDATETIME, APCFRPID) values

        (guid, :new.APDCTO ,:new.APARTG ,:new.APALIM ,:new.APRPER ,:new.APATY , 'A', '2',

        :new.APDL01,

        audittype, user, workstation,

        to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')||'.000000', substr(programid, 1, 10));

    end if;

  end if;

end;

create or replace trigger DVFINDTA.F43008_CFRA_SUA

after update on DVFINDTA.F43008_ADT

begin

  -- JDB delete rows in list

  for num in 1 .. DVFINDTA.F43008_ADT_CFRA_PKG.ids.count loop

    delete from DVFINDTA.F43008_ADT where rowid = DVFINDTA.F43008_ADT_CFRA_PKG.ids(num);

  end loop;

end;

create or replace trigger DVFINDTA.F43008_CFRA_SUB

before update on DVFINDTA.F43008_ADT

begin

  -- clear the list of rowids

  DVFINDTA.F43008_ADT_CFRA_PKG.ids := DVFINDTA.F43008_ADT_CFRA_PKG.empty;

end;

To Copy the audit information to the J environments…  or any other environments… Log into the J environment

P9500003

Copy

Open F9500001 in UTB to verify

Remember that the table needs to be checked in to come up in the list.

No comments:

Extending JDE to generative AI