Getting Information about PeopleSoft Components from Database

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

SQLs below have proved to be great while working behind the scenes and creating report about PeopleSoft customizations. These are quick ways to get behind the scenes and see the objects the way PeopleSoft sees it.

Getting fields and records for a page:

select OCCURSLEVEL as level ,FIELDNUM as taborder, LBLTEXT as label ,
RECNAME as record ,FIELDNAME as field
from PSFSSYS.PSPNLFIELD
where PNLNAME ='PORTAL_HPCOMP'order by OCCURSLEVEL, FIELDNUM

Get information about Project Compares :

SELECT PROJECTNAME, VERSION, PROJECTDESCR, TGTSERVERNAME, TGTDBNAME, TGTOPRID, 
TGTOPRACCT, COMPRELEASE, SRCCOMPRELDTTM, TGTCOMPRELDTTM, COMPRELDTTM, KEEPTGT, 
TGTORIENTATION, COMPARETYPE, 
COMMITLIMIT, REPORTFILTER, MAINTPROJ, LASTUPDDTTM, LASTUPDOPRID, RELEASELABEL, 
RELEASEDTTM, OBJECTOWNERID, DESCRLONG FROM PSPROJECTDEFN

Get Record Audit Flags:

SELECT RECNAME, FIELDNUM, FIELDNAME, 
INTEGER(USEEDIT / 8) - INTEGER (USEEDIT / 16) * 2 as "ADD", 
INTEGER(USEEDIT / 128) - INTEGER(USEEDIT / 256)*2 as "CHANGE", 
INTEGER(USEEDIT / 1024) - INTEGER(USEEDIT/ 2048)*2  as "DELETE"  
FROM PSRECFIELD 
     WHERE  RECNAME = 'PSOPRDEFN' 
             and USEEDIT <> 0       
             AND (INTEGER (USEEDIT / 128) - INTEGER (USEEDIT / 256) * 2 <> 0
             OR INTEGER (USEEDIT / 8) - INTEGER (USEEDIT / 16) * 2 <> 0  
             OR INTEGER (USEEDIT / 1024) - INTEGER (USEEDIT / 2048) * 2 <> 0)
            ORDER BY RECNAME, FIELDNUM;

Get Record Field Types and Definitions:

-- GET RECORD NAME DEFINITION
  SELECT A.RECNAME, A.FIELDNAME, 
         CASE          
            WHEN B.FIELDTYPE = 0  THEN 'CHAR'
            WHEN B.FIELDTYPE = 1       THEN             'LONG CHAR'          
            WHEN B.FIELDTYPE = 2       THEN             'NUMBER'          
            WHEN B.FIELDTYPE = 3       THEN             'SIGNED NBR'          
            WHEN B.FIELDTYPE = 4       THEN             'DATE'          
            WHEN B.FIELDTYPE = 5       THEN             'TIME'          
            WHEN B.FIELDTYPE = 6       THEN             'DATETIME'          
            WHEN B.FIELDTYPE = 7 OR B.FIELDTYPE = 8       THEN   'IMAGE'          
            ELSE  NULL       
         END  AS FIELDTYPE, 
         CASE          
             WHEN B.FIELDTYPE = 2       
                  OR B.FIELDTYPE = 3  THEN  RTRIM (CHAR (B.LENGTH))|| '.'||CHAR (B.DECIMALPOS)          
             ELSE CHAR (B.LENGTH)       
         END  AS FLDLEN, 
         CASE          
            WHEN (A.USEEDIT/ 256)/ 2 <>  (A.USEEDIT/ 256)/ 2.0       
                  THEN 'YES' ELSE 'NO'       
         END  AS REQ, 
         CASE          
             WHEN A.USEEDIT/ 2 <> A.USEEDIT/ 2.0       
                  THEN             'KEY'          
             WHEN (A.USEEDIT/ 2)/ 2 <> (A.USEEDIT/ 2)/ 2.0       
                  THEN             'DUP'          
             WHEN (A.USEEDIT/ 16)/ 2 <>(A.USEEDIT/16)/ 2.0       
                  THEN 'ALT'          
             ELSE NULL       
        END  AS KEY_TYPE, 
        CASE          
            WHEN (A.USEEDIT / 64) / 2 <> (A.USEEDIT/64)/2.0       
                  THEN             'DESC'          
            WHEN (A.USEEDIT / 2 <> A.USEEDIT / 2.0       
                  OR (A.USEEDIT / 2) / 2 <> (A.USEEDIT / 2) / 2.0       
                  OR (A.USEEDIT / 16) / 2 <> (A.USEEDIT / 16) / 2.0)       
                  AND (A.USEEDIT / 64) / 2 = (A.USEEDIT / 64) / 2.0       
                  THEN             'ASC'          
           ELSE             NULL       
        END  AS DIR, 
        CASE          
            WHEN (A.USEEDIT / 2048) / 2 <> (A.USEEDIT / 2048) / 2.0       
                 THEN             'YES'          
            ELSE             'NO'       
        END  AS SRCH, 
        CASE          
           WHEN (A.USEEDIT / 32) / 2 <> (A.USEEDIT / 32) / 2.0       
                THEN             'YES'          
           ELSE             'NO'       
        END  AS LIST, 
        CASE          WHEN (A.USEEDIT / 4)/ 2 <>(A.USEEDIT / 4)/ 2.0       
            THEN             'YES'          
            ELSE             'NO'       
        END  AS SYS, 
        CASE          
            WHEN A.DEFRECNAME = ''       
             THEN             A.DEFFIELDNAME          
             ELSE             RTRIM(A.DEFRECNAME)|| '.'|| A.DEFFIELDNAME       
        END  AS DEFAULT_VALUE, 
        CASE          
            WHEN (A.USEEDIT / 8) / 2 <> (A.USEEDIT / 8) / 2.0 
                 AND (A.USEEDIT / 128) / 2 = (A.USEEDIT / 128) / 2.0       
                 AND (A.USEEDIT / 1024) / 2 = (A.USEEDIT / 1024) / 2.0       
            THEN             'A'          
            WHEN (A.USEEDIT / 8) / 2 <> (A.USEEDIT / 8) / 2.0       
                  AND (A.USEEDIT / 128) / 2 <> (A.USEEDIT / 128) / 2.0       
                  AND (A.USEEDIT / 1024) / 2 = (A.USEEDIT / 1024) / 2.0       
            THEN             'AC'          
            WHEN (A.USEEDIT / 8) / 2 <> (A.USEEDIT / 8) / 2.0       
                  AND (A.USEEDIT / 128) / 2 <> (A.USEEDIT / 128) / 2.0       
                  AND (A.USEEDIT / 1024) / 2 <> (A.USEEDIT / 1024) / 2.0       
            THEN             'ACD'          
            WHEN (A.USEEDIT / 8) / 2 = (A.USEEDIT / 8) / 2.0      
                  AND (A.USEEDIT / 128) / 2 <> (A.USEEDIT / 128) / 2.0       
                  AND (A.USEEDIT / 1024) / 2 = (A.USEEDIT / 1024) / 2.0      
            THEN             'C'          
            WHEN (A.USEEDIT / 8) / 2 = (A.USEEDIT / 8) / 2.0       
                 AND (A.USEEDIT / 128) / 2 <> (A.USEEDIT / 128) / 2.0       
                 AND (A.USEEDIT / 1024) / 2 <> (A.USEEDIT / 1024) / 2.0       
                 THEN             'CD'          
            WHEN (A.USEEDIT / 8) / 2 = (A.USEEDIT / 8) / 2.0       
                 AND (A.USEEDIT / 128) / 2 = (A.USEEDIT / 128) / 2.0      
                 AND (A.USEEDIT / 1024) / 2 <> (A.USEEDIT / 1024) / 2.0      
           THEN             'D'         
           ELSE             NULL       
           END  AS AUDT, CASE          WHEN (A.USEEDIT / 16384) / 2 <>          (A.USEEDIT / 16384) / 2.0       THEN             'PROMPT'          WHEN (A.USEEDIT / 512) / 2 <>          (A.USEEDIT / 512) / 2.0       THEN             'XLAT'          WHEN (A.USEEDIT / 8192) / 2 <>          (A.USEEDIT / 8192) / 2.0       THEN             'Y/N'          ELSE             NULL       END  AS EDIT, A.EDITTABLE  AS PROMPT_TABLE, A.SETCNTRLFLD  AS       SET_CONTROL_FLD, CASE          WHEN (A.USEEDIT / 4096) / 2 <>          (A.USEEDIT / 4096) / 2.0       THEN             'YES'          ELSE             'NO'       END  AS REASONABLE_DT, CASE          WHEN (A.USEEDIT       / 32768)       / 2 <>          (A.USEEDIT       / 32768)       / 2.0       THEN             'YES'          ELSE             'NO'       END  AS AUTO_UPDT, CASE          WHEN (A.USEEDIT / 262144) / 2 <> (A.USEEDIT / 262144) / 2.0       THEN             'FROM'          WHEN (A.USEEDIT / 524288) / 2 <> (A.USEEDIT / 524288) / 2.0       THEN             'THROUGH'          ELSE             NULL       END  AS SEARCH_FIELD, CASE          WHEN A.SUBRECORD = 'Y' THEN 'YES'          ELSE 'NO'       END  AS SUBRECORD, A.LASTUPDDTTM, A.LASTUPDOPRID  FROM PSRECFIELD A, PSDBFIELD B WHERE A.RECNAME = 'PSOPRDEFN' AND A.FIELDNAME = B.FIELDNAMEORDER BY FIELDNUM

Get Field Translate Values:

SELECT fieldvalue, eff_status, xlatlongname     FROM psxlatitem i     WHERE fieldname = 'RUNSTATUS'     AND effdt = (         SELECT max(effdt)         FROM   psxlatitem i1         WHERE  i1.fieldname = i.fieldname         AND    i1.fieldvalue = i.fieldvalue         AND    i1.effdt <= CURRENT DATE)    ORDER BY INTEGER(fieldvalue)

Get information about PS Queries:

-- Get Query DefinitionSELECT OPRID, QRYNAME, DESCR, VERSION, LASTUPDDTTM, LASTUPDOPRID, QRYAPPROVED, CREATEOPRID,       CREATEDTTM,  QRYDISABLED,       QRYFOLDER, DESCRLONG  FROM PSFSSYS.PSQRYDEFN    where QRYNAME = '?'    and oprid = '?'with ur;

-- Get Query Bind VariablesSELECT OPRID, QRYNAME, BNDNAME, BNDNUM, FIELDNAME, HDGTYPE, HEADING, FIELDTYPE       , LENGTH, DECIMALPOS, FORMAT, EDITTABLE, USECOUNT, USEEDIT  FROM PSFSSYS.PSQRYBIND    where QRYNAME like '?%'    and oprid = '?'    with ur;

-- Get Query CriteriaSELECT OPRID, QRYNAME, SELNUM, CRTNUM, COMBTYPE, NEGATION, LPARENLVL,       LCRTSELNUM, LCRTFLDNUM, CONDTYPE, EXPRTYPE, R1CRTSELNUM, R1CRTFLDNUM,       R1CRTEXPNUM, R2CRTSELNUM, R2CRTFLDNUM, R2CRTEXPNUM, RPARENLVL,       QRYOJSELNUM  FROM PSFSSYS.PSQRYCRITERIA    where QRYNAME = '?'    and oprid = '?'with ur;

-- Get Query Output Field DefinitionsSELECT OPRID, QRYNAME, QRYFLDNAME, SELNUM, FLDNUM, RECNAME, FIELDNAME,       FLDRCDNUM, FLDEXPNUM, HDGTYPE, HEADING, COLUMNNUM, GROUPBYNUM,       ORDERBYNUM, ORDERBYDIR, TTLTYPE, SUBTTLNUM, USECOUNT, XLATTYPE,       XLATEXPRTYPE, XLATFLDNUM, XLATEXPNUM, AGGREGATEFUNC  FROM PSFSSYS.PSQRYFIELD    where QRYNAME  like '?%'     with ur;

If you have more information about this kind of handy SQLs – please comment and I will try and keep this post up to date.

I really want to know what you think