Field level Audit

Field level audit track changes to one or more fields on a PeopleSoft record and stores the results in the delivered PSAUDIT table. You can enable as many as fields for auditing in a record. Field level audit only applicable to data updated through online and not directly through the database. So auditing will not apply to data updated by SQRs and SQL scripts.

How to enable Field Level Audit

To enable field level auditing, open the record containing the chosen field and go to the Record field properties of the field(s). In the Audit group box, Field Add, Field Delete, Field Change are the available options. Check the boxes to choose appropriate level of audit.

Type of Field Level Audit

Field Add option will track whenever a value is added to that selected field. 

Field Change option will create an entry whenever you change the value of the field.

Field Delete option, a new row is created in the audit record whenever the field value is deleted.

The record PSAUDIT stores the new and old value of the field along with the audit action. It also stores the record name and key fields of of row in the PeopleSoft record that was modified so that you can track back to it. These are the values that appear in the fields KEY1, KEY2, KEY3….

AUDIT_ACTN field stores what was the change made to the field. The values of this field can be interpreted as follows.

A – Added new value or row

C – Changed the existing value or row

D – Deleted the old value or row

K – Row updated, Old Value

N –Row Updated, New Value

O – Original Value

Query to check Field Level Audit

select * from psaudit where recname = '< Record Name>' and fieldname = '< Field Name>' and key1 = '<First Key Value>'

You can add KEY1, KEY2 on the basis of keys in your base record i.e.. the record on which field level audit is applied.

List of Common PeopleSoft Tables

Below are the commonly used PeopleSoft tables which i used in my daily work. Please comment if any important table is missed.

Pages

* PSPNLDEFN — Page header table

* PSPNLFIELD — Page controls (field types/FIELDTYPE)

* PSPNLHTMLAREA — Static HTML Areas on Pages

Fields

*PSDBFIELD — Fields in the system

* PSXLATITEM — Translate Values

Records

* PSRECDEFN — Record header table

* PSRECFIELD — Fields in the record (subrecords not expanded)

* PSRECFIELDALL — Fields in the record (subrecords expanded)

* PSKEYDEFN — Indexes

* PSTBLSPCCAT — Tablespaces

* PSRECTBLSPC — Records’ tablespace assignments

Components

* PSPNLGRPDEFN — Component header table

* PSPNLGROUP — Pages in the components

Projects

* PSPROJECTDEFN — Project header table

* PSPROJECTITEM — Definitions in the project

Component Interface

* PSBCDEFN — header record; one row for each component interface

* PSBCITEM — one row for each property

Menus

* PSMENUDEFN — Menu header table

* PSMENUITEM — Items (components) on the menu

Process Scheduler

* PS_PRCSDEFN — Process Definition Header

* PS_PRCSDEFNGRP — Process Group

* PS_PRCSDEFNPNL — Component

* PS_PRCSJOBDEFN — Job Header

* PSPRCSRQST — Process Request Instances

Security

* PSCLASSDEFN — Permission List header table

* PSAUTHITEM — Menu items granted security by permission lists

* PSROLEDEFN — Role header table

* PSROLECLASS — Permission Lists in roles

* PSOPRDEFN — User ID header table

* PSROLEUSER — Roles granted to users

Portal

* PSPRSMDEFN — Content References and Folders

* PSPRUHTABPGLT — Portal User HP Tab Pagelet

Change Control

* PSCHGCTLHIST — shows history of locked definitions with project name, incident, and description

* PSCHGCTLLOCK — shows definitions that are currently locked

Application Engine

* PSAEAPPLDEFN — header record; 1 row per app engine

* PSAEAPPLSTATE — state records assigned to app engines

* PSAEAPPLTEMPTBL — temp tables assigned to app engines

* PSAESECTDEFN — sections

* PSAESTEPDEFN — steps

* PSAESTEPMSGDEFN

* PSAESTMTDEFN — actions (action types)

HTML Definitions

* PSCONTDEFN — header record; last update time, etc.

* PSCONTENT — stores actual text in the HTML definition

SQL Definitions

* PSSQLDEFN — header record; last update time, etc.

* PSSQLTEXTDEFN — stores actual text in the SQL definition

File Layout Definitions

* PSFLDDEFN — header record; last update time, etc.

* PSFLDSEGDEFN — stores the segments for each layout

* PSFLDFIELDDEFN — stores the fields for each layout