As PeopleSoft maintains critical enterprise data, having an audit trail on critical data is of paramount importance. This post will help you setup Record Level Auditing in 7 simple steps.
Record level Auditing
As the name suggests, record level auditing is the auditing mechanism enforced on a Record. To set up record level auditing, follow the steps below.
1. Choose the Record
Not all records in PeopleSoft need auditing. When there is such a business requirement, choose the record(s) that needs to be audited.
2. Create the Audit Record
The Audit record is essentially a copy of the record being audited. The easiest way to create a copy of your record is to do a Save As. Name the new record under standard record naming convention prefixed with AUDIT_
3. Remove the unwanted
An Audit record doesn’t need key attributes or edits, so remove them. The same rule goes with Related Language Records, Query security records and PeopleCode. Also, remove all other fields that you are not interested in. Just remove them all.
4. Add Audit Fields
Insert the below fields (also known as audit fields) at the top of the new record in the below order. Make them required and mark them as keys.
AUDIT_OPRID (Captures the User who performed an action)
AUDIT_STAMP (Captures the date and time when the action was performed)
AUDIT_ACTN (Captures the type of action performed)
AUDIT_RECNAME (Captures the name of the audited record definition. This field is used only when the same audit record is used for auditing multiple records)
Audit Fields
On the Record Field Properties for AUDIT_STAMP, we need to check the Auto-Update checkbox. This is essential to correctly populate the date-time stamp.
Auto Update property
AUDIT_ACTN can take up any of the below values based on the type of the action performed.
A – Row inserted
D – Row deleted.
C – Row changed (updated), but no key fields changed.
K – Row changed (updated), and at least one key field changed.
N – Row changed (updated), and at least one key field changed.
In case of C and K the system writes old values to the audit table. While for N, the new values are written into the audit table.
5. Build the audit record
Your Audit record must be a Database record and you should be able to access it using Query.
6. Link them up
Open the Base Record that is to be audited. Open its properties. On the Use tab, under the Record Audit, specify the name of the audit record that we just created as the Record Name. Choose Audit Options based on your requirement.
Audit Record Mapping
Add – An Audit table row is inserted when a row is inserted in the base record
Delete – An Audit table row is inserted when a row is deleted from the base record
Change – Audit table row(s) is inserted when a row changes in the base record
Selective – Audit table row(s) is inserted when common field (present in both base record and audit record) changes
7. Test the Auditing
Based on the Audit option that you have chosen, perform some transaction that can trigger the audit. Now query the audit record and check for new rows. Try to infer the meaning of each row that got added.
A delivered page to test this is the Department page. Navigate to Set Up Financials/Supply Chain > Common Definitions > Design ChartFields > Department. Add a new value.
SELECT * FROM PS_AUDIT_DEPT; |
Examine the audit rows.
That’s it on Audit Records in PeopleSoft!
I lost the audit link (in the step 6) in the base record property (tab Use) when migration from dev to test server. Could you help?
thanks.
Vera
Hi Vera,
While migrating the project from dev to tst, click the Options button in the Copy Database dialog. This will take you to the Upgrade Options settings.
Here, on the General options tab, select the “Set Target Audit Flags from Source” option in the Audit Flags on Records settings.
Click OK and proceed with the migration.
This should hopefully solve your problem; please let us know if it worked.
Is there a way where we can audit derived records? I have a situation where a users wants to audit when the National_Id change under Benefits–> Employee/Dependent Information Update Dependent/Beneficiary. I have looked at the field (NID_SPECIAL_CHAR) and the record is DERIVED_HR. I am not sure how to audit this one.
Hi David,
We cannot audit work/derived records. you can track or capture the data when it reaches the main table [physical] from the derived record once the page is saved.
Regards,
Raja
Hi,
I Created Audit record for PSOPRDEFN Table.If i changes perform any user profiles that changes are stored in audit record.But if i deleted any user profile the deleted details are not stored in audit record.How can i resolve this problem.
Thanks,
Naga.
HI Naga,
There must be a row for deletion with action set to ‘D’. A possible lapse would be in the audit record mapping, check whether you had checked the “delete” check box. [step number : 6 in the above article].
Thanks.
We are in version 9.1. Will this work for our version?
It works for people tools 8.50 , 8.51 … for other versions i have not tested out
How do I get a subrecord (that is a part of a record to audit)
to also bring values to the audit query, when the main record is updated, which includes fields in its subrecord?
A Main Record with a sub-record embedded with it when audited captures both the fields of sub record and main record. Please let me know if your question is answered ?
I have noticed that if I delete my audit record for my address data, then I get a sql error when I try to update/change the next vendor address record. Then I am unable to save the data. Is this one of the issues with creating these new audit records vs. just using delivered audit processes, which allows purging audit records?
I am trying to audit for Profile Management–> Person profiles.
Main record is JPM_JP_ITEMS.
Sub record is JPM_ITM_PRP_SBR
Page contains the Sub Record Field. When I assign Audit record to JPM_JP_ITEMS and save the component. Only main record fields only storing in Audit not the sub Record Fields. Is the functionality would be like this? or am I missing some thing?
I have the same issue with putting an audit on JPM_JP_ITEMS, did you ever get a resolution on this record? Thanks
In 9.1 we also have a delivered audit logging feature which can track most of the commonly used transaction tables.
Common Definitions> Audit Logging
Hi Sawrendra,
That would come in real handy, isn’t it?
PS user defines audit record captures add/change/delete performed by the PIA online page. Does PS user defined audit record capture add/change/delete performed by AE, SQR, COBOL?
Are you certain about the statement “3. Remove the unwanted An Audit record doesn’t need keys, so remove them. ”
Oracle documentation clearly states they are needed ” Remove all edit and key attributes from the newly saved record. Add to the top of the audit record the following audit-specific fields:
AUDIT_OPRID
AUDIT_STAMP
AUDIT_ACTN
Make these fields required and keys. The following table explains the purpose of each audit-specific field. ”
https://docs.oracle.com/cd/E91187_01/pt855pbr2/eng/pt/tadm/task_CreatingAuditRecordDefinitions-077a5e.html?pli=ul_d161e76_tadm
Perhaps you meant remove any additional keys beyond those three fields?
peace,
clark ‘the dragon’ willis
PSA SME
Thanks for pointing it out, clark ‘the dragon’ willis!
We were able to find delivered audit records which did not have these fields marked as keys.
That said, I’ve changed the text to be inline with what PeopleBooks recommend.