PeopleSoft Auditing

Record Auditing:

select
    RECNAME,
    RECDESCR,
    AUDITRECNAME,
    case when bitand(RECUSE,1) > 0 then 'Y' else 'N' END AUDIT_ADD,
    case when bitand(RECUSE,2) > 0 then 'Y' else 'N' END AUDIT_CHANGE,
    case when bitand(RECUSE,4) > 0 then 'Y' else 'N' END AUDIT_DELETE,
    case when bitand(RECUSE,8) > 0 then 'Y' else 'N' END AUDIT_SELECTIVE
from PSRECDEFN
WHERE AUDITRECNAME != ' '
ORDER BY RECNAME;

Record Field Auditing:

select
    F.RECNAME,
    F.FIELDNUM,
    F.FIELDNAME,
    F.USEEDIT,
    case when bitand(F.USEEDIT,8) > 0 then 'Y' else 'N' end AUDIT_FIELD_ADD,
    case when bitand(F.USEEDIT,128) > 0 then 'Y' else 'N' end AUDIT_FIELD_CHANGE,
    case when bitand(F.USEEDIT,1024) > 0 then 'Y' else 'N' end AUDIT_FIELD_DELETE
from
    PSRECFIELD F
where
    F.FIELDNAME = (
        select
            case when (
                bitand(USEEDIT,8) > 0 or
                bitand(USEEDIT,128) > 0 or
                bitand(USEEDIT,1024) > 0
            ) then FIELDNAME else '' end as FIELD_AUDITED
        from PSRECFIELD
        where RECNAME = F.RECNAME
        and FIELDNAME = F.FIELDNAME
    )
order by F.RECNAME, F.FIELDNUM;

Refer to: http://peoplesoft.wikidot.com/what-is-audited
and to: http://xtrahot.chili-mango.net/uploaded_images/USEEDIT%20Flags-799884.jpg

Creating Database level Audit records in Peoplesoft

As we all aware, creating application level audit is just adding 3 extra fields in the audit record and placing it in main record properties.

But creating Database level audit is slightly different from it.

Please follow the below steps to do it.

1. Similar to application level audit, create a new record with addition fields AUDIT_OPRID, AUDIT_STAMP and  AUDIT_ACTN.
2. Create triggers based on your required operations like add, change and delete.
3. Run the trigger SQL script in the database.

How to create triggers:
If you are good in writing SQL in various database platforms, then you can write trigger code by your own.
if you are not familier with SQL on how to create triggers, then you can use the delivered functionality to generate the script in the following location.

Enter into the component using the main record on which you want to create the audit.
After entering into the component, Enter the audit record which was created and select the options based on your requirement.

Click on Generate code button, it will generate the script based on the type of database you use.
now save the component.

Execute Trigger Script in Database:
Now go to Perform Database level Audit component which is also in the same navigation to execute the script generated, in your database.

If you want to see the script, you can get it in process log files of that process.