Creating Audit records - AUDIT_PERS_NID (sample)

I wanted to create a post about audits and how to work with audits within Peoplesoft. There are many different types of audits you can place within peoplesoft. I am going to discuss the main two.

(Database level audits and online audits)

Sample: Need to create a audit for when users change or update SSN (National_id)

Record that will be audited: PS_PERS_NID

Audit Record created: PS_AUDIT_PERS_NID
(Make sure all keys are removed from the audit record)

Add fields: AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN (Make fields required, check auto-update for AUDIT_STAMP)










Create Database Trigger:

CREATE OR REPLACE TRIGGER PERS_NID_TR
AFTER INSERT OR UPDATE OR DELETE ON PS_PERS_NID
FOR EACH ROW
DECLARE
V_AUDIT_OPRID VARCHAR2(64);
BEGIN
DBMS_APPLICATION_INFO.READ_CLIENT_INFO(V_AUDIT_OPRID);
IF INSERTING
THEN
INSERT INTO PS_AUDIT_PERS_NID
VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'A',:NEW.EMPLID,:NEW.COUNTRY,:NEW.NATIONAL_ID_TYPE,:NEW.NATIONAL_ID,:NEW.SSN_KEY_FRA,:NEW.PRIMARY_NID,:NEW.TAX_REF_ID_SGP,:NEW.LASTUPDDTTM,:NEW.LASTUPDOPRID);
ELSE
IF DELETING
THEN
INSERT INTO PS_AUDIT_PERS_NID
VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'D',:OLD.EMPLID,:OLD.COUNTRY,:OLD.NATIONAL_ID_TYPE,:OLD.NATIONAL_ID,:OLD.SSN_KEY_FRA,:OLD.PRIMARY_NID,:OLD.TAX_REF_ID_SGP,:OLD.LASTUPDDTTM,:OLD.LASTUPDOPRID);
ELSE
INSERT INTO PS_AUDIT_PERS_NID
VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'K',:OLD.EMPLID,:OLD.COUNTRY,:OLD.NATIONAL_ID_TYPE,:OLD.NATIONAL_ID,:OLD.SSN_KEY_FRA,:OLD.PRIMARY_NID,:OLD.TAX_REF_ID_SGP,:OLD.LASTUPDDTTM,:OLD.LASTUPDOPRID);
INSERT INTO PS_AUDIT_PERS_NID
VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'N',:NEW.EMPLID,:NEW.COUNTRY,:NEW.NATIONAL_ID_TYPE,:NEW.NATIONAL_ID,:NEW.SSN_KEY_FRA,:NEW.PRIMARY_NID,:NEW.TAX_REF_ID_SGP,:NEW.LASTUPDDTTM,:NEW.LASTUPDOPRID);
END IF;
END IF;
END PERS_NID_TR;
/


With a online audit, you would add this audit record to the record properties of PS_PERS_NID and migrate to PRD with those options you selected.