PeopleSoft SQR Upgrades

It has been really sometime since I have written something. I am dedicating most of my time to the Re-Implementation project planning phase, I seem to have taken it personally, and the main reason is – One of my previous colleagues from the PeopleSoft Solution Center said “You will need all the experience inside our solution center to pull this off”, by which he means at least 600 projects that was completed by the Solution center. He has a point and I respect his views, but a challenge is always exiting. So as per my expectations these long breaks between my posts is going to continue. In this post I would like to give some suggestions to achieve the SQR Upgrade during a PeopleSoft upgrade process and the kind of Tools that you could develop to achieve the process with ease. This post talks about how I would handle the project, most of it is based on what I learnt out of my previous experience. If I’m missing something kindly let me know, missing pieces are perfectly possible because I work from India with absolutely no client interaction and no knowledge of the Real Time issues with this process. The offshore process described will involve the Technical Upgrade of the SQRs and I believe the process defined for this specific portion is precise.

The PeopleSoft SQR Upgrade must be divided into three phases,
SQR Technical Upgrade
SQR Functional Upgrade
Testing and Issue Resolution

SQR Technical Upgrade
The SQR Technical Upgrade is further classified into,
SQR Upgrade for Custom Added SQRs
SQR Upgrade for Custom Modified SQRs

Each Custom Added SQR is made compatible with the new PeopleSoft instance, this being a Technical Upgrade we will only consider the effects of Table structure modifications which would prevent an SQR from execution, the results of the SQR can only be perfected after Functional Upgrade and Testing. The Table structure changes must be provided in a document to help the Technical Consultant achieve his goal. The usual Format of this document will contain the changes that must be incorporated into each of the SQRs (This document is the Technical Design document). The Technical Design document can be created by parsing each SQR for table names within the SQRs and printing all the differences in the table structure for these tables. The table structure differences are pulled out of the Meta Tables from our old and new release PeopleSoft databases, another important aspect which must not be overlooked is the impact of “Renamed” objects. The utility which creates the SQR TDD must also look into the path specific rename scripts. The path specific rename scripts are the scripts in your new release pshomes which will be executed for your upgrade path. So the inputs to our TDD utility are folder containing Custom SQRs, Old Release database, New Release Database and the rename scripts. Understanding the precise use of the TDD is very important, at no point in time should we forget the real objective of this stage – Make the old release SQRs executable in the new release. When the report points to a field which was deleted from a specific table, we must look into the data conversion programs to identify the replacement RecordField. When a new Field is added to a table we must look out for the INSERT statements in the SQR containing these tables, and add these newly added fields in the Insert statement, if the values being inserted can be identified easily for these new RecordFields the Technical Consultant can include the precise values else we either include a Default value for the field or data type specific defaults (for CHAR – ‘ ‘, for number – 0, for date – NULL). The values entered in these insert statements will be evaluated during the subsequent phases. Renames must be done depending on the TDD pointers. Finally, the PERSONAL_DATA changes must be made to the SQRs depending on the upgrade path – that is if the upgrade path has transferred all the data from PERSONAL_DATA to PERSON, ADDRESSES, PER_DATA_EFFDT etc. An example for this is – The Custom SQR which looks like this,
BEGIN-SELECT
EMPLID
NAME
ADDRESS
ADDRESS_OTHER
FROM PS_PERSONAL_DATA
WHERE EMPLID = ‘xxxx’

We will have to convert the program to this format in the new release,
BEGIN-SELECT
! Modified by UpgraderName for SQR Upgrade Project Name - Begins
A.EMPLID
B.NAME
C.ADDRESS1
D.ADDRESS1
FROM PS_PERSON A, PS_NAMES B, PS_ADDRESSES C, PS_ADDRESSES D
WHERE A.EMPLID = ‘xxxx’
AND B.EMPLID = A.EMPLID
AND B.NAME_TYPE = ‘PRI’
AND C.EMPLID = A.EMPLID
AND C.ADDRESS_TYPE = ‘HOME’
AND D.EMPLID = A.EMPLID
AND D.ADDRESS_TYPE = ‘MAIL’
! Modified by UpgraderName for SQR Upgrade Project Name – Ends

I guess I have covered the PERSONAL_DATA changes in detail. Look out for the comments these serve as perfect documentation trails within the SQRs. After we do this sort of Documentation whenever a change was made, we make sure the summary goes to the Top of the SQR like this,
!***********************************************************
! SQR Technical Upgrade by Co. Name on May 4, 2006
!
! + Renamed XLATTABLE to PSXLATITEM – 7 Occurrences
! + Implemented PERSONAL_DATA changes in the procedure
! Get-Personal-Info
!***********************************************************
The steps listed above must be performed to complete the SQR Upgrade for Custom Added tables. These steps can be automated; the tool must make all the desired changes, and make proper documentation in the SQR. There are number of instances to be considered, Renames must only be made to the Table or Column name and not to the variables that appear with the same names.

Technical Upgrade of Custom Modified SQRs are done with the help of Beyond Compare (or any such file comparison program) the Custom Modified SQR is compared with the Delivered SQR in the old release and the Customizations are identified. The Delivered SQRs between the Old and New releases are compared to identify the changes made by PeopleSoft. If the Customization can be carried forward it should be carried forward to the New Delivered SQR. This would have been my next objective had I stayed with my old team – Create a tool to automate this analysis of SQRs and then customize them with the help of a code.

All the Technically upgraded SQRs are compiled with the help of another tool – it does nothing more than pass the command line parameters using the common flags set.

SQR Functional Upgrade
The Technically upgraded SQRs are executed with Test data and the results are analyzed. The issue is identified as a business process issues which have come into effect as a result of an upgrade in this stage will be evaluated by the Functional Consultants to provide suitable solutions.

Testing and Issue Resolution
As always we finish our project with this phase.

I’ll probably upload all the Tools mentioned in this post in sometime and provide the links. Currently working on a Tool to get a complete Application Engine program from the back-end, this will include the exact sequence in which the Application Engines are being executed (Dynamic Call section must be handled, Application Designer doesn’t do it! Just trying my best will let you know how it goes – Required for Re-Implementation project.) and the contents (PeopleCode too (Yes!)) and properties of the various steps.