Transfer Private PS Queries From One User To Another

If any user leave the department and there are many Private queries which needs to be transferred to any new user, then there are 2 ways to do this.

Option1: Log in as the user who owns the query, go to Query Manager in PeopleSoft and change the queries from Private to Public. Now new user can copy those queries and create a private copy for himself/herself.

Option2: If the list of private PeopleSoft queries is huge or you can’t login as that old user then you can transfer private queries from one user to another by doing a datamod and updating the tables directly.

Below are 2 sets of SQLs, one for backup.dms and another one for datamod.dms which can be run to first take the backup of tables and then change the owner of private queries in Peoplesoft.

Datamover Commands for backup.dms

EXPORT PSQRYDEFN WHERE OPRID=’old_user’;
EXPORT PSQRYFIELD WHERE OPRID=’old_user’;
EXPORT PSQRYRECORD WHERE OPRID=’old_user’;
EXPORT PSQRYCRITERIA WHERE OPRID=’old_user’;
EXPORT PSQRYBIND WHERE OPRID=’old_user’;
EXPORT PSQRYEXPR WHERE OPRID=’old_user’;
EXPORT PSQRYSELECT WHERE OPRID=’old_user’;
EXPORT PSQRYBINDLANG WHERE OPRID=’old_user’;
EXPORT PSQRYDEFNLANG WHERE OPRID=’old_user’;
EXPORT PSQRYFIELDLANG WHERE OPRID=’old_user’;
EXPORT PSQRYDEL WHERE OPRID=’old_user’;
EXPORT PSQRYEXECLOG WHERE OPRID=’old_user’;
EXPORT PSQRYFAVORITES WHERE OPRID=’old_user’;
EXPORT PSQRYPREFS WHERE OPRID=’old_user’;
EXPORT PSQRYSTATS WHERE OPRID=’old_user’;
EXPORT PSQRYTRANS WHERE OPRID=’old_user’;

Datamover Commands for Datamod.dms

UPDATE PSQRYDEFN SET OPRID=’new_user’ WHERE OPRID=’old_user’;
UPDATE PSQRYFIELD SET OPRID=’new_user’ WHERE OPRID=’old_user’;
UPDATE PSQRYRECORD SET OPRID=’new_user’ WHERE OPRID=’old_user’;
UPDATE PSQRYCRITERIA SET OPRID=’new_user’ WHERE OPRID=’old_user’;
UPDATE PSQRYBIND SET OPRID=’new_user’ WHERE OPRID=’old_user’;
UPDATE PSQRYEXPR SET OPRID=’new_user’ WHERE OPRID=’old_user’;
UPDATE PSQRYSELECT SET OPRID=’new_user’ WHERE OPRID=’old_user’;
UPDATE PSQRYBINDLANG SET OPRID=’new_user’ WHERE OPRID=’old_user’ ;
UPDATE PSQRYDEFNLANG SET OPRID=’new_user’ WHERE OPRID=’old_user’;
UPDATE PSQRYFIELDLANG SET OPRID=’new_user’ WHERE OPRID=’old_user’;
UPDATE PSQRYDEL SET OPRID=’new_user’ WHERE OPRID=’old_user’;
UPDATE PSQRYEXECLOG SET OPRID=’new_user’ WHERE OPRID=’old_user’;
UPDATE PSQRYFAVORITES SET OPRID=’new_user’ WHERE OPRID=’old_user’;
UPDATE PSQRYPREFS SET OPRID=’new_user’ WHERE OPRID=’old_user’;
UPDATE PSQRYSTATS SET OPRID=’new_user’ WHERE OPRID=’old_user’;
UPDATE PSQRYTRANS SET OPRID=’new_user’ WHERE OPRID=’old_user’;