1/12/13

SQL Query to fetch list of all processes\jobs in a recurrence

One of my colleague asked me for a sql query to retrieve the list of all processes and jobs for a particular recurrence. I wrote the below SQL which gives a list of PeopleSoft processes running in Process Scheduler for a recurrence. This SQL also gives the individual processes associated with a PS Job scheduled in the same recurrence.
\


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.prcsjobname 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

3 comments:

  1. I'm getting this error

    ORA-00904: "JP"."RECURNAME": invalid identifier
    00904. 00000 - "%s: invalid identifier"
    *Cause:
    *Action:
    Error at Line: 13 Column: 4

    ReplyDelete
  2. line 13: select SELECT jp.prcsjobname ,its wrongly printed as prcsjobame

    ReplyDelete

Please refrain for marketing messages and unnecessary back links.

Workflow errors on New Hire and Add employment instance

User tries to hire a new person or tries to add a new organization relationship encounters a series of error messages related to workflow...

Trending in this Blog this month