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
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
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
Thank you for the correction Luis Verissimo
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