Categories
PeopleSoft Meta Data SQL Query Collection

PeopleSoft Meta Data SQL Query Collection

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
    )
  )
Please Use %CurrentDateIn Meta SQL instead of SYSDATE , for avoiding Database platform compatibility issue.
For More Information Please go through PeopleSoft AppEngine Meta SQL

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';
Please change FIELDNAME='OUTDESTFORMAT' as per your requirements.

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')
This Query works only Oracle Database Version >= 10g , As you can we have used Regular Expression Function Here.

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 )

2013-09-23_174910

Please Change Bind Variables UPPER(:1)) & this query works only for Oracle.