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

Posted by Nitin Pai
Comments (0)
July 26th, 2007

Comments (0)