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
 

  • 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

  • >