How to Find PeopleSoft Processes/PSJobs in a Recurrence

by Apurva T. on March 10, 2013

in PeopleSoft Administration

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
Also Read:  HC_HRS_JOB_POSTING_NON_GLOBAL Has Not Been Deployed (262,665)

{ 2 comments… read them below or add one }

Luis Verissimo June 20, 2014 at 6:23 am

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

Reply

Eva January 12, 2015 at 12:49 pm

Thank you for the correction Luis Verissimo

Reply

Cancel reply

Leave a Comment

Previous post:

Next post: