How to make a PeopleSoft PRIVATE query PUBLIC?

Sometime back I had a ticket asking me to make a PRIVATE query PUBLIC. The query was created by a superuser who had quit the company and whose OPRID was deleted. Here are the SQL’s you need to execute if you need to make a PRIVATE query PUBLIC using a data fix. The other straightforward alternative is to log on as the owner of the PRIVATE query and save as PUBLIC (which is not applicable in this case).

Ps_query_private_to_public1

 

UPDATE PSQRYBIND SET OPRID = ‘ ‘ WHERE OPRID = ‘NPAI’ AND QRYNAME = ‘HX_CUSTOM_QUERY’;

UPDATE PSQRYBINDLANG SET OPRID = ‘ ‘ WHERE OPRID = ‘NPAI’ AND QRYNAME = ‘HX_CUSTOM_QUERY’;

UPDATE PSQRYCRITERIA SET OPRID = ‘ ‘ WHERE OPRID = ‘NPAI’ AND QRYNAME = ‘HX_CUSTOM_QUERY’;

UPDATE PSQRYDEFN SET OPRID = ‘ ‘ WHERE OPRID = ‘NPAI’ AND QRYNAME = ‘HX_CUSTOM_QUERY’;

UPDATE PSQRYDEFNLANG SET OPRID = ‘ ‘ WHERE OPRID = ‘NPAI’ AND QRYNAME = ‘HX_CUSTOM_QUERY’;

UPDATE PSQRYEXPR SET OPRID = ‘ ‘ WHERE OPRID = ‘NPAI’ AND QRYNAME = ‘HX_CUSTOM_QUERY’;

UPDATE PSQRYFIELD SET OPRID = ‘ ‘ WHERE OPRID = ‘NPAI’ AND QRYNAME = ‘HX_CUSTOM_QUERY’;

UPDATE PSQRYFIELDLANG SET OPRID = ‘ ‘ WHERE OPRID = ‘NPAI’ AND QRYNAME = ‘HX_CUSTOM_QUERY’;

UPDATE PSQRYRECORD SET OPRID = ‘ ‘ WHERE OPRID = ‘NPAI’ AND QRYNAME = ‘HX_CUSTOM_QUERY’;

UPDATE PSQRYSELECT SET OPRID = ‘ ‘ WHERE OPRID = ‘NPAI’ AND QRYNAME = ‘HX_CUSTOM_QUERY’;

Ps_query_private_to_public

Note:
Understanding how PeopleSoft differentiates PRIVATE versus PUBLIC query should help you resolve various similar requests like a few mentioned below.
- Create a PUBLIC query clone of the PRIVATE query
- Rename a PRIVATE or PUBLIC query
- Delete a query

Read More about  PeopleSoft