This SQL is useful if you want to know which processes a user has recently run.
It shows the Process Name and Type, the Process Description, their Run Control ID, the number of runs, and a min/max on the Run date.
Note: This SQL may have limited data if the process request table gets purged often.
In this SQL, we chose to use the PS_PMN_PRCSLIST view. We also use a process to pull the description from the Process Defn or the Job Defn table (see alias C).
SELECT P.PRCSNAME, P.PRCSTYPE
, C.DESCR "Process Description"
, COUNT(*) "Number of Runs"
, MIN(P.RUNDTTM) "First Ran"
, MAX(P.RUNDTTM) "Last Ran"
FROM PS_PMN_PRCSLIST P
, (SELECT PRCSTYPE, PRCSNAME, DESCR FROM PS_PRCSDEFN UNION SELECT 'PSJob', PRCSJOBNAME, DESCR FROM PS_PRCSJOBDEFN) C
WHERE P.PRCSTYPE = C.PRCSTYPE
AND P.PRCSNAME = C.PRCSNAME
AND P.OPRID = 'VP1'
AND P.RUNDTTM > '07/01/2015'
--AND P.PRCSNAME <> 'FS_STREAMLN'
GROUP BY P.PRCSTYPE, P.PRCSNAME, P.RUNCNTLID, C.DESCR
ORDER BY 1,2,3,4