Here is Collection of PeopleSoft Meta Data SQL Query

Getting Most Effective Dated Row in JOB Table with Active Employees

With Correlated Subquery

SELECT
  J.EMPLID,
  J.EMPL_RCD,
  J.EFFDT,
  J.EFFSEQ
FROM
  PS_JOB J
WHERE  J.HR_STATUS = 'A'
AND
  (
    J.EFFDT =
    (
      SELECT
        MAX(J1.EFFDT)
      FROM
        PS_JOB J1
      WHERE
        J.EMPLID      = J1.EMPLID
      AND J.EMPL_RCD  = J1.EMPL_RCD
      AND J1.EFFDT < = SYSDATE 
    )
  AND J.EFFSEQ =
    (
      SELECT
        MAX(J2.EFFSEQ)
      FROM
        PS_JOB J2
      WHERE
        J.EMPLID     = J2.EMPLID
      AND J.EMPL_RCD = J2.EMPL_RCD
      AND J.EFFDT    = J2.EFFDT
    )
  )

With Analytic function

SELECT
  J.EMPLID,
  J.EMPL_RCD,
  J.EFFDT,
  J.EFFSEQ
FROM
  (
    SELECT
      EMPLID,
      EMPL_RCD,
      EFFDT,
      EFFSEQ,
      MAX (EFFDT) OVER (PARTITION BY EMPLID, EMPL_RCD)         AS MAX_JOB_EFFDT,
      MAX (EFFSEQ) OVER (PARTITION BY EMPLID, EMPL_RCD, EFFDT) AS MAX_JOB_EFFSEQ
    FROM
      PS_JOB
    where
      EFFDT < = SYSDATE
  ) J
WHERE
  J.EFFDT    = J.MAX_JOB_EFFDT
AND j.EFFSEQ = J.MAX_JOB_EFFSEQ

Get Process Defination With Trace Value

SELECT PRCSNAME, PARMLIST FROM PS_PRCSDEFN WHERE UPPER(PARMLIST) LIKE '%TRACE%' AND PRCSTYPE = 'Application Engine';

Read Data from Translate Table ( PSXLATITEM ) with Effective Dated

SELECT DISTINCT
  FIELDVALUE,
  XLATSHORTNAME ,
  XLATLONGNAME
FROM
  PSXLATITEM
WHERE
  FIELDNAME='OUTDESTFORMAT'
AND EFFDT  =
  (
    SELECT
      MAX(PSXLATITEMTBL.EFFDT)
    FROM
      PSXLATITEM PSXLATITEMTBL
    WHERE
      PSXLATITEMTBL.FIELDVALUE  = PSXLATITEM.FIELDVALUE
    AND PSXLATITEMTBL.FIELDNAME = PSXLATITEM.FIELDNAME
    AND PSXLATITEMTBL.EFFDT    < = SYSDATE
  )
AND EFF_STATUS = 'A';

PeopleSoft Country Table

------ PeopleSoft Country Table ---------
SELECT DISTINCT COUNTRY, DESCR FROM PS_COUNTRY_TBL WHERE 1=1;

PeopleSoft State Table

----- PeopleSoft State Table -----------
SELECT DISTINCT STATE, DESCR FROM PS_STATE_TBL;

PeopleSoft Record Name With Record Type

SELECT RECNAME ,RECDESCR , DESCRLONG , 
CASE RECTYPE
  WHEN 0 THEN 'SQL TABLE'
  WHEN 1 THEN 'SQL VIEW'
  WHEN 2 THEN 'WORK RECORD'
  WHEN 3 THEN 'SUB RECORD'
  WHEN 5 THEN 'DYNAMIC VIEW'
  WHEN 6 THEN 'QUERY VIEW'
  WHEN 7 THEN 'TEMPORARY TABLE' 
END  AS "PPLSOFT RECORD TYPE"
FROM PSRECDEFN 
WHERE
  REGEXP_LIKE(RECNAME,'UD_TICK.*','i')

Get Portal Menu Navigation By Process Name

SELECT DISTINCT 'Portal Root -> ' || RTRIM( REVERSE ( SYS_CONNECT_BY_PATH(REVERSE (PORTAL_LABEL), ' >- ')) ,' >- ') "Navigation by Process Name"
      FROM PSPRSMDEFN
     WHERE PORTAL_NAME = 'EMPLOYEE'
       AND PORTAL_PRNTOBJNAME = 'PORTAL_ROOT_OBJECT'
START WITH PORTAL_URI_SEG2 IN (
                       SELECT DISTINCT PNLGRPNAME
                       FROM PS_PRCSDEFNPNL
                       WHERE PRCSNAME = UPPER(:1))
CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME;

Sample Output ( i have use HR_CMP015 App Engine inside of Bind Variable )