Record Audit

To audit an already exisiting PeopleSoft record, we need to create a record definition and SQL table in which we store audit information. When creating the audit record, remove any attributes such as
PARENT records, Query Security Records, and PeopleCode. The easiest way to create an audit table is to open the record definition of the base record that you wish to audit. Save it as a new record, prefaced with AUDIT_.

Remove the all edit and key attributes from the newly saved record. Also remove any attributes such as
PARENT records, Query Security Records, and PeopleCode. Add to the top of the audit record the following three special audit-specific fields in the same order given below.

  • AUDIT_OPRID
  • AUDIT_STAMP
  • AUDIT_ACTN
  • AUDIT_RECNAME

In most cases you should include AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN. The AUDIT_STAMP must be given the attribute AUTOUPDATE. You might also add AUDIT_RECNAME if you are creating an audit table to audit more than one record definition.

Make these fields required and keys. Then build the table. Make sure you can query this table using sql editor.

The purpose of each audit specific field is explained below.

AUDIT_OPRID - Identifies the user who caused the system to trigger the audits—either by performing an add, change, or delete to an audited field.
AUDIT_STAMP - Identifies the date and time the audit was triggered.
AUDIT_ACTN  - Indicates the type of action that the system audited. Possible actions include:

  • A: Row inserted.
  • D: Row deleted.
  • C: Row changed (updated), but no key fields changed. The system writes old values to the audit table.
  • K: Row changed (updated), and at least one key field changed. The system writes old values to the audit table.
  • N: Row changed (updated), and at least one key field changed. The system writes new values to the audit table.

AUDIT_RECNAME -  Identifies the name of the record definition that was audited.

The audit table does not have to include all the columns of the base table. In fact, for performance reasons, it’s best to only include those fields in your audit record that are deemed

Open the record properties for the record you want to audit, Under the Record Audit, we have the following options

Record Name - Specify the user-defined audit record.

Audit Options - following are the audit options to choose for auditing the record.

  • Add -  Inserts an audit table row whenever a new row is added to the table underlying this record definition.
  • Change -  Inserts one or two audit table rows whenever a row is changed on the table underlying this record definition.
  • Selective -  Inserts one or two audit table rows whenever a field that is also included in the record definition for the audit table is changed.
  • Delete -  Inserts an audit table row whenever a row is deleted from the table underlying this record definition.

Now perform online transactions on the audited table, query the audit table to know what is changed and who changed it at what time.

Source: http://psadmin.wordpress.com/category/peopletools/

 

 

0 comments: