Importance of Database and System Audits in PeopleSoft

PeopleSoft provides Audit reports which includes DDDAUDIT, SYSAUDIT, Alter Audit to check the integrity of the PeopleSoft system especially after major changes to the system such an upgrade, major customization, application of bundles/ patches. It is also recommended to have regular maintenance runs of these audits to resolve integrity issues on a proactive basis. It is important to understand the purpose and the usage of these reports to make correct decisions for the objects in the system. There was an earlier article on the same topic with the focus on UPGCOUNT to report the counts of the tables. In this post, I’ll just highlight the other reports.

DDDAUDIT, the Database Audit report reports the inconsistencies between the PeopleSoft Record and Index objects and the Database objects. During an Upgrade, a significant number of objects get dropped or added newly in the latest version. It is important to review this report to filter such objects (TABLE-3 & VIEWS-2) and remove them from the database if they are dropped at the PS metadata tables. Likewise, new objects (TABLE-2 & VIEWS-1) need to be built correctly so as not to have any inconsistencies reported. Unique Indexes (INDEX-3) reported in this report after an Upgrade usually indicates that the base table has duplicate rows which requires validation of the upgrade and conversion scripts.

SYSAUDIT, the System Audit report reports orphan PeopleSoft objects & inconsistencies within the PeopleSoft metadata. Usually, this report runs into a number of pages in an Upgrade because of the large amount of changes between one version and another. However, one can fix the object inconsistencies during the development phase when most of the retrofit activity is completed which automatically resolves many of the issues. The error descriptions and their resolutions can be found in Peoplebooks at this location: Home > PeopleBooks > Enterprise PeopleTools 8.51 PeopleBook: Data Management > Ensuring Data Integrity.

Security Retrofit during an Upgrade can take inputs from SEC-28 section of the SYSAUDIT which reports invalid entries in PSAUTHITEM, one of the core tables for Permission lists. For PeopleSoft Systems which face App Server errors/ disruptions due to an invalid object load in the cache, the root cause might point to PSLOCK version integrity for which a version Application Engine may have to be run. For Systems where there are frequent Tree Level changes, the section on Tree Integrity queries can be referred to check and cleanup accordingly. There are more than 300 integrity checks and reported on individual section on the various object types that one can find in the SYSAudit report.

The Alter Audit helps bring synchronization between the structure of the PS record definition and the actual database table. This is to ensure that all data types, format, length and the structure match.
It is a good practice to regularly run these reports and ensure they are clean so that potential issues due to such inconsistencies can be avoided in the system. Keeping clean audits helps you to apply major bundles or maintenance packs or even prepare for Upgrades in a controlled environment.

Tips for handling Global Payroll Packages during PeopleSoft Upgrade

For customers who license Global Payroll for PeopleSoft, it is challenging to perform the steps related to GP in the Upgrade Process. This is especially true when you have customized a number of elements within the system. Here I just want to give a few pointers based on my own experience as an Upgrade consultant in a few of the projects.

In the 9.1 Upgrades, Oracle gives licensed as well as unlicensed GP Rule Packages which need to be copied to the system. This essentially means that you will have elements of all countries in the system. For the Licensed Rule package, there is a compare process which reports the customizations and you need to choose whether the element can be overwritten or not. Similarly, there is a package for Rule Deletes, which when compared shows obsolete elements that can be removed from the system.  The Non-Rule package containing the elements and records will be copied as per the delivered process. If there is license for a new country extension in the new version, a separate non-rule package for the same needs to be created and copied.

• Audit the GP Tables: While the Upgrade steps ask you to check if the last assigned pin number matches with that of the Installation table, there are a few more audits that can be run on the actual tables especially to check if there are discrepancies within the Pin Code, Pin Name, Pin Number on the core GP Tables. These may have been caused by an earlier incorrect patch application or package copy. This becomes relevant as some of the errors that occur during the compare or copy process may not directly show you the cause.
• Backup: Before starting on the GP Upgrade Steps, it is important to take a full database backup and also the tables involved in the process which can be usually found in the gp_cleanup.dms script. However in case of any error during Initial Move which requires a table restore, it is recommended to restore the full database as the process updates the pin numbers on the GP Tables.
• GP Cleanup: The GP compare process copies package elements to the target GP tables with pin_num > 50000000. So before any import process and after the upgrade process is done, it is recommended to run this script to ensure that you have only the elements of the package. Remember that this script should never be run before compare or during the upgrade process as it  wipes out the elements with pin_num > 50000000.
• Tables behind the scene: It is good to be aware of the following tables when debugging package compare, copy issues.

GP_PKG_ELEMENTS - Has complete information on the Package Element compare,
upgrade status. Can also be used to set flags for compare/upgrade if doing it through
PIA is cumbersome.
GP_PKG_RUNCTL - Has run control information for each of the package.
GP_PKG_DEFN - High Level definition for the package with version.
GP_PKG_CRIT1 and GP_PKG_CRIT2 - Various default criteria and conditions
defined for the package.
GP_PKG_PINMAP – GP - Pin Map Record.

• Impact of Applying MP/Tax Updates: There might be a few tax updates that need to be applied during the course of the project and you need to ensure that the package elements that come via the tax updates get updated at the source and independent tasks are followed to copy them and the relevant scripts run once the final package is done in the actual cutover tasks.
• Running from Command line: While Change Assistant offers automated way of importing, comparing and upgrade of GP Package, the same also be run from command line by invoking psae. Data Mover can be used for exporting, importing packages and for running the cleanup script. Before running from command line, an entry for the package needs to be available in GP_PKG_RUNCTL with the available options for compare or upgrade.
• Final Rule Package: Working with the GP Technical consultant, the final rule package will have to be created at the source after multiple rounds of testing.  This includes reapplying any customizations that was overwritten. This package will then be used during the successive test moves to be directly copied and upgraded on the target.

To summarize this, along with the delivered Oracle documentation for Upgrade, it is beneficial to keep above pointers in mind before upgrade of Global Payroll in the PeopleSoft System, as it would be helpful in better planning and avoid relevant issues.