Important PeopleSoft Queries Part I

Some of the PeopleSoft PeopleTools Queries that someone might find useful;

How to find database tables (i.e SQL records) attached to a page?

SELECT A.LBLTEXT, A.RECNAME, A.FIELDNAME, A.PNLNAME
FROM PSPNLFIELD A
WHERE A,PNLNAME = :1 --Page Name
AND A.RECNAME NOT LIKE '%XLAT%'
AND A.RECNAME NOT LIKE '%WRK%'
AND A.RECNAME NOT LIKE '%DERIVED%'
AND A.FIELDNAME <> ' '
ORDER BY 2;

How to find objectownerid of a record definition?

SELECT A.RECNAME, A.RECDESCR, A.OBJECTOWNERID, A.DESCRLONG
FROM PSRECDEFN A
WHERE A.RECNAME = :1 --Record Name

How to find list of record definitions (tables) the belong to a particular object owner ?

SELECT A.RECNAME, A.RECDESCR, A.OBJECTOWNERID, A.DESCRLONG
FROM PSRECDEFN A
WHERE A.OBJECTOWNERID= :1 --Object owner id

How to find PS Queries that select data from specific table

SELECT A.OPRID, A.QRYNAME, A.RECNAME, A.FIELDNAME
FROM PSQRYFIELD A
WHERE A.RECNAME LIKE :1 -- Record Name

How to find PS Queries that use specific field

SELECT A.OPRID, A.QRYNAME, A.RECNAME, A.FIELDNAME
FROM PSQRYFIELD A
WHERE A.FIELDNAME LIKE :1 -- Field Name

How to find Queries that were not delivered by PeopleSoft

SELECT A.OPRID, A.QRYNAME, A.DESCR
FROM PSQRYDEFN A
WHERE A.LASTUPDOPRID <> 'PPLSOFT'

SHARE

Ayesha Wee

• 
• 
• 
• 
•