7 Steps to Setup A Record Level Audit in PeopleSoft

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 keys, 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

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)

                  

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.

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 MappingAdd – 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!

How to Resolve a Row Level Security Issue in PeopleSoft

Most people prefer using SQL queries if they need to find out what are all the users have access to a particular employee's data in PeopleSoft and that's because either they are extremely comfortable with SQL or they don't know how to do this through an inquiry page in PIA.

Let's say an HR user reported you an issue that they can't see one employee's data in Job Data page. I would say this is one of the most occurred issues a PeopleSoft security consultant deals with as part of their daily work.
Anyway, from further inquiry you came to know that the employee's data row wasn't future dated because if it was then there is a separate security setup has to be done in order to allow users to see future dated transaction data in Job Data page.

So what are we gonna do now ? well, there are ways to analyze and resolve the issue but I am gonna tell you few very simple steps to the same through PIA:

Step 1: Inquire the security data for the employee

Open the Security Data Inquiry page (Navigation:  Main > Setup HRMS > Security > Core Row Level Security > Security Data Inquiry) and enter the employee ID for which the issue has been reported and then click on the button 'Show Security Definitions' to open the security configuration.

Here you will see all the Security Types which have access to the employee's data records. Note that if employee has more than one EMPL_RCD then it will show the security access for all of them.

Select all the rows and click on the button 'Show Permission List' to open all the permission lists those have access to the selected Security Type.

Select all the permission lists and then click on the button 'Show Users' which will finally open the list of users who have above permission lists assigned in their user profile respectively. 

So what do we finally have with us ? 

We have list of users who have access to the employee data for which the issue has been reported. Now, we have to find out whether in this list the HR user who reported the issue, exists or not. If they doesn't exist then it means we have to update the security setup for this HR user so that they get access to the employee's data.
Click on Find link and search the HR user id if it exists.

In case, the HR user exists in the list then it means that they have access to the employee's data and it is perhaps a cache issue which could be resolved if we just log out of the system, clear the browser cache and log in back again. 
However, if the HR user is not there in the list then we have got to update the security configuration for the user in order to provide access to employee's data hence go to the step 2.

Step 2: Update the Row Level Security Configuration

Once we are certain that the HR user cannot see the employee's data in Job Data page because their row level security configuration is not updated properly then the only area we have to focus on is, how and where to update the row level security configuration. Please note that, the terms 'Row Level Security' and 'Data Security' both are same in this context.

I would like to underline this point that, the row level security configuration is assigned to a permission list and this permission list can be:

• The 'Row Security Permission List' which exists on the 'General' tab of User Profile page (Navigation:  Main > Setup HRMS > PeopleTools > Security > User Profiles > User Profile).
• Any permission list added to a particular role which exists on the 'Roles' tab of User Profile    page (Navigation:  Main > Setup HRMS > PeopleTools > Security > User Profiles > User Profile).

In either case, the security configuration on the permission list is added/updated from below two places:

 1. Security by DEPT tree (Navigation:  Main > Setup HRMS > Security > Core Row Level      Security > Security By Dept Tree) 

This page uses Department Tree to enforce the row level security in PeopleSoft. for this, the department tree has to be maintained properly i.e, it should be refreshed frequently so that it does reflect the actual department hierarchy of the organization.

 2. Security by permission list (Navigation:  Main > Setup HRMS > Security > Core Row Level  Security > Security By Permission List)

Security configuration in this page is done based on the Security Sets and their Security Types. In this page, we only setup what are all the Security Types under a particularity Security Set that a permission list will have access to. The further access to employee records by Security Types is defined in transaction SJT table SJT_PERSON.

Most organizations use this page to configure the data or row level security so I will take this as reference when explaining how to resolve the data security issue further.
Lets get back to the issue at hand

By the end of Step 2 we would be certain that the HR user doesn't have access to the employee' data and its not a cache issue.So, how to resolve this now ?  Let's go to Step 3.

Step 3: Assign the data/row security to the HR user

We got to be careful here while doing any changes. You must follow below steps:

• Find if there is any existing relevant role which has access to that employee and can be assigned to the HR user. If there is any such role then before assigning it to the HR user, make sure that the role doesn't have access to unintended data which HR user will also get access to if assigned. If everything looks green, assign the role to HR user.
• If there is no such role, then check if any Permission List exist which has the same access. Also, make sure it doesn't have access to unintended data just like the way we analysed for existing role in above step. If all OK then find out an existing role to which this permission list can be assigned. If such role is found then assign the permission list to the role and then assign the role to HR user. Again, before assigning the role to the user, make sure it doesn't impact the other users access.
• If there is no such permission list also, then as the last option we have to create a new permission list, then assign it to either existing or a new role then finally assign this role to the HR user.

All done, check if HR user got the required access.

However, I would like to reiterate few things here - Before making any changes in the security setup (As explained in the above steps), you must keep in mind that the changes shouldn't impact the existing data accesses i.e the data/row security access defined for other users shouldn't be impacted.

PeopleCode Event Sequence

Accounts Payable - Overview

PeopleSoft AP Integration with Other systems/module

PeopleSoft Payables Business Process

Voucher Entry & Approval

Voucher Build

Voucher Edit

Matching Process

Voucher Posting

Pay Cycle

Drafts

Payment Posting

Withholding

Archiving

PeopleSoft Payables tracks the stages using statuses of a voucher:

 - Entry status

 - Match status

 - Approval status -- A voucher has to be reviewed for approval, or unless it is pre-approved. You can post a voucher even though it has not been approved for payment, if your business unit definitions enable you to.

 - Budget status

 - Post status -- The system creates balanced accounting entries to record your liability and sets the post status to posted.  To change the numbers on a posted voucher, you must first unpost the voucher to create reversing entries. 

 - Payment status -- The payment status is unselected, selected for payment, or paid.

 - Payment post status

Voucher Build

Voucher Build is a batch program that builds voucher record sets from limited information entered online or retrieved from other procurement processes.

Voucher Edit

Voucher Edit is an Application Engine program that loads new voucher data from the Voucher Build temporary processing tables and/or any recycled vouchers from the online tables to the Voucher Edit work tables and performs the following:

-- Performs field-level, relational, and balancing edits for the voucher header, voucher line.

-- Calculates net payment dues dates, discount due dates, and discount amounts according to the payment terms.

Matching Process

Matching is a flexible process that compares vouchers with purchase orders and receiving documents to ensure that you  pay for what you ordered and received.

To run matching, you must have PeopleSoft Payables and PeopleSoft Purchasing installed on the same database. The system automatically does matching based on document characteristics.

Voucher Posting Process

Creates expense accrual accounting entries in accounts payable.  This process creates balanced voucher accounting lines entries on the voucher accounting line table.  The source for the voucher accounting entries are the expense distribution on the voucher (DR).  The  liability offset (CR) account value is populated from the default setup on the accounting entry template. 

Payment Posting Process

Creates voucher payments accounting entries in accounts payable.  This process creates balances payment accounting line entries on the voucher accounting line table.  The payment posting process used the voucher posting process and the accounting entry template as the source for the cash (CR) side of the accounting entry.  The offset (DR) side of the payment accounting entry is created by reversing the liability offset from the voucher accounting line entry. 

Journal Generator

This is the process that creates General Ledger journal entries from PS subsystems (modules).  For Accounts Payable, the journal generator selects rows from the voucher accounting line table that meet the defined selection criteria.  These rows are summarized and the data is sent to the general ledger in the form of a general ledger journal. 

AP Reports

Some major reports in PS payables :

Match Exception Report

    --Lists the match exceptions with voucher data, purchase order data, receiver data, and match error information.

AP/GL Open liability Report

 --Compares the AP Open Liability balance to the GL Open Liability balance Detail AP/GL Open Liability Account Reconciliation Report

 --Shows all accounting activity for accounts payable transactions for each voucher. Use the detail report to validate the AP Open Liability balance to the General Ledger

Aged Vendor Liability

 --Lists all vouchers that have not been paid by the As of Date. You can run the report in either summary or detail format for specific vendors

Vendor Aging Report

 --Lists all vouchers that have not been paid at the date you specify Vendor Witholding Balances Control Report

 --Lists withholding activity for internal audits. We recommend that you run the  report on a regular basis to check the withholding summaries

Outstanding Balance by Vendor

 --Lists the gross amount of all the outstanding vouchers for the specified vendor

Mouse Over Popup - PeopleSoft 9.1 Mouse

Below example shows what is mouse over popup. It quickly shows additional employee or applicant information by pausing on a person's name or ID that has a dotted line under it, as shown in this example.

Now, let us see how to implement this technically. PeopleSoft application delivers these mouse over IDs:

APPLICNT_INFO_1

APPLICNT_INFO_2

EMPLOYEE_INFO_1

EMPLOYEE_INFO_2

EMPLOYEE_INFO_3

Field-Level

Navigation ->

Set Up HRMS, Common Definitions, Mouse Over Popup, MouseOver Field Definition, MouseOver Field Definition

Component Level:

Set Up HRMS, Common Definitions, Mouse Over Popup, MouseOver Component Setup, MouseOver Component Setup

Note:  PeopleSoft delivers 31 fields in HCM 9.1 with this feature. Adding any other fields would be considered a customization and will require peoplecode modifications to the Application Package/Class - HR_MO_COMMON: SET_UP_COMMON, that is used to retrieve the data associated with any new fields.

Workflow Tables

APPR_RULE_DETL – Approval Rule Defn Details
APPR_RULE_FIELD – Approval Rule Defn Route Cntl
APPR_RULE_AMT – Approval Rule Amounts
RTE_CNTL_LN – Route Control Profile Line
RTE_CNTL_RUSER – RoleUser Route Cntl Profiles
RTE_CNTL_TYPE – Route Control Type
RTE_CNTL_HDR – Routing Control Type

PeopleSoft Security

PeopleSoft Security is organized through 3 components. This security is mainly about User profile and related items.
User Profile (Operator ID)
Roles
Permission Lists
User profile is attached to Roles. Roles will have one or more Permission Lists. Permission Lists are then attached to Components. Below hierarchy shows the link between them.

Users -> Roles -> Permission Lists -> Components / Menu


USER PROFILES:
PSOPRDEFN Stores all operators (users) in the PeopleSoft system. Also stores their employee ID (EMPLID), encrypted password, primary permission list, default navigator home page, process profile permission list and row security permission list.
PSROLEUSER The highest level of security access is defined by roles (think of them as groups). This table stores the roles the user belongs to.
PSOPRCLS Roles link together permission lists which are the security objects that define access to components, pages, and other areas of the system. This view returns the permission lists that a user has access to via their roles. Note that prior to PeopleTools 8, permission lists were synonymous with classes and most of the security tables still use this convention.
PSOPRALIAS Aliases can be mapped to a particular operator ID (user). The obvious alias is employee ID (EMPLID) but others include external organisation ID (EXT_ORG_ID) and customer ID (CUST_ID). All ways of referring to the same entity.
PSOPRALIASTYPE This is the setup table for operator aliases
PSOPRALIASFIELD This is the setup table that maps operator aliases to records & fields
PSUSERATTR User attributes store the a hint password question & response for a user (if this is enabled)
PSUSEREMAIL Email addresses for users.


ROLES:
PSROLEDEFN Stores roles and their properties. Roles can be assigned dynamically through Query, PeopleCode or LDAP. Roles are also used in conjunction with Workflow and routing.
PSROLECLASS Roles are made of up of one or permission lists, and this table links the two together. Very handy.


PERMISSION LISTS:
PSCLASSDEFN Permission lists are where the security really happens. They provide access to menus, components and pages and a host of other security including PeopleTools, Process security, Component Interfaces, Web Libraries, Web Services, Personalisations, Query and Mass Change.
PSAUTHITEM The link between permission lists and menus
PSAUTHBUSCOMP The link between permission lists and component interfaces and their methods
PSAUTHOPTN The link between permission lists and personalisations
PSAUTHPRCS The link between permission lists and process groups
PSAUTHSIGNON The link between permission lists and signon times
PSAUTHWEBLIBVW A view linking permission lists and access to web libraries (really just Menus in PSAUTHITEM that begin with WEBLIB_).
PSAUTHWS The link between permission lists and web services (service operations)
PS_SCRTY_ACC_GRP The link between permission lists, trees and query access groups
PS_MC_OPR_SECURITY The link between permission lists and mass change templates. This is an odd table, it uses the field OPRID but really it links permission lists


PORTAL:
PSPRSMDEFN Stores the structure of the portal registry. This data is stored in a hierarchical (tree) structure within the table. The field PORTAL_URI_SEG1 is the menu, PORTAL_URI_SEG2 is the component, and PORTAL_URI_SEG3 is the MARKET.
PSPRSMPERM Stores permission lists associated with access to everything within the portal registry

PeopleSoft Finance - CORE Tables for AP and GL

General Ledger Tables
PS_LEDGER: The general ledger data table.
PS_JRNL_HEADER: Stores general ledger journal header data.
PS_JRNL_LN: Stores general ledger journal line data.
ps_combo_rule_tbl: Stores ChartField combination rules.
ps_combo_group_tbl: Stores ChartField combination groups.
ps_combo_data_tbl: ChartField Combo data table.
ps_bu_led_comb_tbl: Business Unit CharField Combos for Ledger.
PS_GL_ACCOUNT_TBL: GL Accounts table.
Accounts Payable/Voucher Tables
PS_GRP_AP: Stores AP control groups.
PS_VOUCHER: AP voucher header table.
PS_VOUCHER_LINE: AP voucher line.
PS_DISTRIB_LINE: Voucher distribution table.
PS_PAYMENT_TBL: AP disbursements table.
PS_PYMNT_VCHR_XREF: Voucher Scheduled Payment.
PS_RT_RATE_TBL : MARKET Rate Data Tale. There is also the market rate default view RT_DFLT_VW.
PS_VCHR_ACCTG_LINE: AP accounting entries.
Voucher Build Staging Tables
PS_VCHR_HDR_STG
PS_VCHR_LINE_STG
PS_VCHR_DIST_STG
PS_VCHR_PYMT_STG

Vendor Tables
VENDOR: Stores vendor related information.
VENDOR_ADDR: Vendor address related info.
VENDOR_LOC: Vendor locations.
VENDOR_PAY: Payment processing sepecifications such as payment method, payment handling code, bank code and so forth.
VNDR_ADDR_SCROL: Index to address table.
VNDR_CNTCT_SCR: Index to contact table.
VNDR_LOC_SCROL: Index to location table.
VENDOR_ID_NBRS: Additional ID numbers (Dun & Bradstreet numbers).
VENDOR_ADDR_PHN: Vendor phone numbers.
VENDOR_CNTCT: Vendor contacts.
VENDOR_CONVER: Vendor converstaions.
VENDOR_INVOICE: Stores vendor invoice related flags.
VENDOR_TYPE: Vendor type codes.
VNDR_BANK_ACCT: Vendor electronic payment information.
VNDR_CNTCT_PHN: Vendor contact phone numbers.
VNDR_IND_CLASS: Vendor Standard Industrial Classification (SIC) codes.
VNDR_URL: Vendor website address.
VNDR_VAT_RGSTRN: Vendor VAT information.
VENDOR_WTHD: Witholding data.
VENDOR_WTHD_JUR: Witholding type.

PeopleTools 8.54 – New functionality, New Browser Releases

https://blogs.oracle.com/peopletools/entry/peopletools_8_54_new_functionality

PeopleSoft Tracing & Logging

Tracing online People Code and SQL


1. Select Trace at the login screen or add “&trace=Y” to the login URL OR - To set trace flags, click here (use this link.

2. Choose the options in the tracing screen and login.
3. Perform the actions you wish to record.
4. Once complete logout and retrieve the file from the /appserv//LOGS directory. 
The trace file name will include your login name.


Tracing online PeopleCode and SQL (Alternative)

1. Login to PeopleSoft and navigate to PeopleTools > Debug.
2. Choose the options and save.
3. Perform the actions you wish to record.
4. Once complete logout and retrieve the file from the
/appserv//LOGS directory. The trace
file name will include your login name.


Application Engine Tracing
1. Open the process definition
2. Select the overrides tab.
3. Select Append on the parameters field.
4. Enter -TRACE 131 -TOOLSTRACEPC 3596 -TOOLSTRACESQL
143
5. Save the record.


SQR Tracing
1. Open the process definition
2. Select the overrides tab.
3. Select Append on the parameters field.
4. Enter –DEBUG[ABC]… (replace ABC with the debug letters
provided in the sqr, sqc source).
5. Save the record.


Cobol Tracing
1. Open the Cobol SQL process type for the platform you are running on.
2. Enter 255 in the command line field between two forward slashes “//”.
3. Save the record.
%%PRCSNAME%% %%DBTYPE%%/%%DBNAME%%/%%OPRID%%/%%OPRPSWD%%/%%RUNCNTLID%%/%%INSTANCE%%/255/%%DBFLAG%%
4. Run the process and retrieve the COBOL sqltrace from the process monitor.


Tracing Settings
1. Both the process scheduler (psprcs.cfg) and application server (psappsrv.cfg) config files allow for permanent tracing settings as well as setting up trace Masks. psprcs.cfg - /appserv/prcs// psappsrv.cfg - /appserv//

2. Trace Masks are setup to limit the amount of tracing allowed when a user wants to trace through the front end package (see all methods
above).

3. LogFence allows for lower and higher detail tracing with regards to application server standard logging. This is ideal for identifying problems on the application server.

4. To enable the trace link in the Sign on page.
    People-tools > Web-profile.
  
   Web profile > Debugging Tab -> Check the Option - Show Trace Link at Signon

Delivered Page to Search Component Navigation.

There was several solutions to find the navigation path for component and page.

There were several SQL statements for different databases.

Finally Oracle as developed page this purpose.

In the latest Ptools 8.50 and Application 9.1 application search is possible 
from two navigations.

1) Main Menu > Enterprise Components > Find Object Navigation

2) Setup HRMS > System Administration > Utilites > Portal Navigation Path

Search is possible with name of Component,Page,Secondary Page and 
Content Reference.

Search works fine with hidden components as well.