How to Find PeopleSoft Processes/PSJobs in a Recurrence – PeopleSoft Tutorial
3

How to Find PeopleSoft Processes/PSJobs in a Recurrence

Do you want to find out all processes and jobs running under a particular recurrence? Use this SQL below:

SELECT process_job_name,  description, recurname
FROM   ps_prcsrecur a,
(SELECT p.prcsname  AS Process_Job_name, p.descr     AS Description, p.recurname AS recurname
FROM   ps_prcsdefn p
UNION
SELECT jp.prcsjobame AS Process_Job_name,
(SELECT j.descr
FROM   ps_prcsdefn j
WHERE  j.prcsname = jp.prcsname) AS Description, jp.recurname AS recurname
FROM   ps_prcsjobdefn j,  ps_prcsjobitem jp
WHERE  j.prcsjobname = jp.prcsjobname) b
WHERE b.recurname = a.recurname
AND a.recurname = :1
ORDER  BY 1
Apurva Tripathi
 

Apurva is a PeopleSoft consultant and a big advocate of everything PeopleSoft. He is also a technology enthusiast and loves learning and implementing newer and open source technologies. He spends his spare time updating this blog and likes to read books on self help and productivity.

  • Luis Verissimo says:

    There is some errors in the sql, this one should work.
    SELECT process_job_name, description, a.recurname
    FROM ps_prcsrecur a,
    (SELECT p.prcsname AS process_job_name,
    p.descr AS description,
    p.recurname AS recurname
    FROM ps_prcsdefn p
    UNION
    SELECT jp.prcsjobname AS process_job_name,
    (SELECT j.descr
    FROM ps_prcsdefn j
    WHERE j.prcsname = jp.prcsname)
    AS description
    ,j.recurname AS recurname
    FROM ps_prcsjobdefn j
    , ps_prcsjobitem jp
    WHERE j.prcsjobname = jp.prcsjobname) b
    WHERE b.recurname = a.recurname
    AND a.recurname = :1 /*name of de recurrency*/
    ORDER BY 1

  • Eva says:

    Thank you for the correction Luis Verissimo

  • SSS says:

    There is some errors in the sql, this one should work.
    SELECT process_job_name, description, a.recurname
    FROM ps_prcsrecur a,
    (SELECT p.prcsname AS process_job_name,
    p.descr AS description,
    p.recurname AS recurname
    FROM ps_prcsdefn p
    UNION
    SELECT jp.prcsjobname AS process_job_name,
    (SELECT j.descr
    FROM ps_prcsdefn j
    WHERE j.prcsname = jp.prcsname)
    AS description
    ,j.recurname AS recurname
    FROM ps_prcsjobdefn j
    , ps_prcsjobitem jp
    WHERE j.prcsjobname = jp.prcsjobname) b
    WHERE b.recurname = a.recurname
    AND a.recurname = :1 /*name of de recurrency*/
    ORDER BY 1

  • >

    Download Complete List of PeopleTools Tables List. Never Again Google It !