Updating PS Query References in the Tools Tables

By Chris Malek | Wed, Oct 13, 2010

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.


Article Categories
Author Info
Chris Malek

Chris Malek is a PeopleTools® Technical Consultant with two decades of experience working on PeopleSoft enterprise software projects. He is available for consulting engagements.

About Chris Work with Chris
Looking for pain-free PeopleSoft web services? 😀
PeopleSoft Simple Web Services (SWS)

Introducing a small but powerful PeopleSoft bolt-on that makes web services very easy. If you have a SQL statement, you can turn that into a web service in PeopleSoft in a few minutes.

Integration Broker - The Missing Manual

I am in the process of writing a book called "Integration Broker - The Missing Manual" that you can read online.