Production Monitoring SQL for PeopleSoft

Below SQL is used to monitor the production jobs in your production environment, It lists the jobs based on the job statuses

1)Error for 4 days

2)Cancelled for 4 days

3)Deleted for 4 days

4)Queued for last 99 days

5)Success  for 4 days

 

 

SELECT
  /*+ index (PSPRCSRQST PSBPSPRCSRQST) */
DECODE(R.RUNSTATUS,16,'333',5,'333',4,'333', 10,'111',3,'111',17,'111',1,'111',8,'111',6,'222',7,'222',9,'444',2,'000','555')
||DECODE(R.RUNSTATUS,1,'Cancel',10,'NOT Successful',16,'Pending',17,'Warning',18,'Blocked'
,19,'Restart',2,'Delete',3,'Error',4,'Hold',5,'Queued',6,'Initiated',7,'Processing',8,'Cancelled',9,'Success','N/A') AS STATUS,SERVERNAMERUN
,R.RUNDTTM AS RUNDTTM
,(CASE WHEN R.RUNSTATUS IN (16,5,4) THEN
to_char(floor(((RUNDTTM-SYSDATE)*60*24/60)/24))||'d'|| to_char(MOD(floor( (RUNDTTM-SYSDATE)*60*24/60),24)) ||'h'||to_char(MOD(FLOOR((RUNDTTM-SYSDATE)*60*24),60))||'m'
WHEN R.RUNSTATUS IN (10,3,17,1,8) THEN '0'
WHEN R.RUNSTATUS IN (6,7) THEN to_char(CEIL((SYSDATE-BEGINDTTM)*60*24))
WHEN R.RUNSTATUS IN (9) THEN to_char(CEIL((ENDDTTM-BEGINDTTM)*60*24)) ELSE TO_CHAR(R.RUNDTTM,'mm-Mon-yyyy') END) AS TTE_ETA
,DECODE(R.PRCSTYPE,'Application Engine','AE','SQR Process','SQR P','SQR Report','SQR R',R.PRCSTYPE) AS  PRCSTYPE
,R.PRCSNAME,NVL(P.DESCR,J.DESCR) AS PRCS_DESCR
,R.RUNCNTLID AS RUNCNTLID
,R.RECURNAME AS RECURNAME
,R.PRCSINSTANCE AS PI
,R.RQSTDTTM AS RQSTDTTM
,SYSDATE AS CURDTTM
,ENDDTTM
FROM
PSPRCSRQST R LEFT OUTER JOIN PS_PRCSJOBDEFN J
ON R.MAINJOBNAME = J.PRCSJOBNAME LEFT OUTER JOIN  PS_PRCSDEFN P
ON R.PRCSTYPE = P.PRCSTYPE AND R.PRCSNAME = P.PRCSNAME
WHERE
R.OPRID IN ('TC0996')
AND R.RQSTDTTM BETWEEN SYSDATE-DECODE(R.RUNSTATUS,16,99,5,99,4) AND SYSDATE
AND R.PRCSTYPE LIKE '%'
AND R.PRCSJOBSEQ=0
AND R.RUNSTATUS IN (16,5,4,10,3,17,1,8,6,7,9,3)
ORDER BY 1 ASC,RUNDTTM ASC;

Code for calling the run control process selection page from the pushbutton - field change event

Below code is used to call the run control process selection page from the field change event,

This can be used instead of adding the run control page and using the RUN push button in the page

 

Declare Function LaunchProcessRequestDlg PeopleCode PRCSRQSTDLG_WRK.LOADPRCSRQSTDLGPB FieldFormula;

 

LaunchProcessRequestDlg(GetRowset().GetRow(1).GetRecord(1), GetRowset().GetRow(1).GetRecord(1).RUN_CNTL_ID.Value, " ", True);