Getting Information about PeopleSoft Components from Database

 

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 Query Definition
SELECT OPRID,
       QRYNAME,
       DESCR,
       VERSION,
       LASTUPDDTTM,
       LASTUPDOPRID,
       QRYAPPROVED,
       CREATEOPRID,
       CREATEDTTM,
       QRYDISABLED,
       QRYFOLDER,
       DESCRLONG
  FROM PSFSSYS.PSQRYDEFN
 where QRYNAME = '?'
   and oprid = '?' with ur;

-- Get Query Bind Variables
SELECT 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 Criteria
SELECT 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 Definitions
SELECT 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;