Find Custom PeopleSoft Queries

The following SQL identifies custom queries created in your system from the PSQRYDEFN PeopleTools table. It includes the query type (and the operator ID who owns the query if it is private).

select
    QRYNAME as "Query Name",
    DESCR as "Query Description",
    CREATEOPRID as "Created By",
    CREATEDTTM as "Created On",
    LASTUPDDTTM as "Last Updated By",
    LASTUPDOPRID as "Last Updated On",
    case when OPRID != ' ' then 'Private - ' || OPRID else 'Public' end as "Query Type",
    VERSION as "Revisions",
    QRYAPPROVED as "Query Approved?",
    APPROVEOPRID as "Approved By",
    APPROVEDTTM as "Approved On"
from PSQRYDEFN
where LASTUPDOPRID != 'PPLSOFT'

Note to run on a SQL server database you'll need to change the || concatenation operator to a plus sign (+).