Updating PS Query References in the Tools Tables

I was recently working at one of my clients on some post production upgrade support. Before their upgrade to release 8.9, they had several tables that looked like the PS_EMPLOYEES table.

These had different version of data like terminated employees and employees active as of the end of the last month. These custom tables also had custom fields from other database tables and some calculated values that PeopleSoft does not include in their table. The client wanted to simplify their environments and create one custom table called EMPLOYEES_ALL to satisfy all their needs.

This can cause issues with PS Query references and updating queries can be a very tedious task. The public queries are easy to fix because you can access them. However, the user’s private queries cannot always be accessed. But who really wants to open 500 queries individually and made the record changes? This is very error prone and mind numbing.

Since PeopleSoft queries are all stored as meta-data in Tools tables, you can theoretically update these references with SQL from the back end.

I created some scripts that changed all references to the deleted EMPLOYEE_XX records to the new EMPLOYEES_ALL reference.

Note: This was tested in an 8.9 environment on Tools 8.46. Do not take these scripts on faith. In the example scenario, the change was an easy one to one mapping between tables where all the fields that were on the old deleted record existed on the new record. If you want to use these scripts please do thorough testing in a development and test environment before running it in any production environment.

In the example below, I am doing an update on all queries that have a name like “UPG_ME”.

Note: The OPRID field on the PSQRY% records designate if the query is public or private. If the fields is blank then it is a public field.

 

UPDATE PSQRYRECORD SET RECNAME = 'EMPLOYEES_ALL'  WHERE QRYNAME LIKE 'UPG_ME%' AND RECNAME = 'EMPLOYEES_XX'     UPDATE PSQRYFIELD SET RECNAME = 'EMPLOYEES_ALL'  WHERE QRYNAME LIKE 'UPG_ME%' AND RECNAME = 'EMPLOYEES_XX'