Convert Private Queries from One ID to Another ID

Alot of times an employee will leave the company and there will be many good private queries under their PeopleSoft ID. Alot of people would like to know if it is possible to convert those queries to another PeopleSoft ID.

Prior to PeopleTools 8.44 there wasn’t a delivered way to convert queries from one ID to another ID. PeopleTools 8.44 introduced a new feature called Query Monitor that allows you to assign a query to another user.

Prior to PeopleTools 8.44 you had two choices. Sign on as the departed user and make their queries public and then perform a Save As on each query to make them private and afterwards you would delete the original query.

The second option would be to change the PSQRYxxxxx tables behind the scenes to reflect a new Operator ID. This would be something that customers are generally wary to do and there could be audit concerns as well since you are manipulating the data outside of the PeopleSoft application.

Here is an example of the second choice:

update psqrydefn set oprid = ‘new oprid’ where query_name = ‘name of query’ and oprid = ‘old oprid’;

If you’re going to move ALL the users private queries over, you can leave out the query name part of the above clause. You would need to perform the above update on the following tables:

PSQRYDEFN
PSQRYFIELD
PSQRYRECORD
PSQRYCRITERIA
PSQRYBIND
PSQRYEXPR
PSQRYSELECT

Note: In later releases of PeopleTools you would be best serve to use the delivered PeopleTools method. If you elect to manually update the tables with SQL be sure to make sure you research all of the Query Tables involved because new releases of PeopleTools can introduce tables and table structure changes.