PTIAAUJOBS_VW

(SQL View)

SELECT RUN.OPRID , RUN.RUN_CNTL_ID , RUN.PTIAAUJOBRUNMODE , STS.PTIAAUJOBID , CASE WHEN PRCS.RUNDTTM > %CurrentDateTimeIn THEN STS.PTIAAUJOBSTATE WHEN PRCS.RUNDTTM >= %COALESCE(STS.PTIA_DTTM_STAMP ,PRCS.RUNDTTM) THEN PRCS.RUNSTATUS ELSE STS.PTIAAUJOBSTATE END AS PTIAAUJOBSTATE , STS.PTIA_DTTM_STAMP ,CASE WHEN PRCS.RUNDTTM > %CurrentDateTimeIn THEN PRCS.ORIGPRCSINSTANCE ELSE PRCS.PRCSINSTANCE END AS PRCSINSTANCE , PRCS.RUNSTATUS , CASE WHEN RUN.PTIAAUJOBRUNMODE = 'S' AND PRCS.RUNDTTM > %CurrentDateTimeIn THEN PRCS.RUNDTTM END AS PTIAAUNEXTDTTM , PRCS.LASTUPDDTTM FROM PS_PTIAAURUNCNTL RUN LEFT JOIN ( SELECT a.PRCSINSTANCE , a.RUNCNTLID , a.RUNSTATUS , X.XLATSHORTNAME , a.OPRID , a.RUNDTTM , a.LASTUPDDTTM , a.BEGINDTTM , a.ENDDTTM , a.INITIATEDNEXT , a.ORIGPRCSINSTANCE , a.RECURORIGPRCSINST FROM PSPRCSRQST a ,PSXLATITEM X WHERE A.PRCSINSTANCE = ( SELECT MAX(B.PRCSINSTANCE) FROM PSPRCSRQST B WHERE a.RUNCNTLID = B.RUNCNTLID ) AND A.RUNSTATUS = X.FIELDVALUE AND X.FIELDNAME = 'RUNSTATUS' ) PRCS ON (RUN.OPRID = PRCS.OPRID AND RUN.RUN_CNTL_ID = PRCS.RUNCNTLID ) LEFT JOIN ( SELECT J.PTIAAUJOBID , J.PTIAAUJOBNAME , J.PTIAAUJOBSTATE , J.PTIA_DTTM_STAMP FROM PS_PTIAAUJOB J WHERE PTIA_DTTM_STAMP = ( SELECT MAX(PTIA_DTTM_STAMP) FROM PS_PTIAAUJOB J1 WHERE J1.PTIAAUJOBNAME = J.PTIAAUJOBNAME ) ) STS ON RUN.RUN_CNTL_ID = STS.PTIAAUJOBNAME

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).

2 PTIAAUJOBNAME Character(100) VARCHAR2(100) NOT NULL Job Name

3 PTIAAUJOBRUNMODE Character(1) VARCHAR2(1) NOT NULL Run Mode
A=Ad hoc
S=Scheduled

4 PTIAAUJOBID Character(100) VARCHAR2(100) NOT NULL Job ID

5 PTIAAUJOBSTATE Character(20) VARCHAR2(20) NOT NULL Job State

6 PTIAAU_DTTM_STAMP DateTime(26) TIMESTAMP Run Date Time

7 PRCSINSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance

8 RUNSTATUS Character(2) VARCHAR2(2) NOT NULL Process Scheduler Process Run Status
1=Cancel
10=Not Successful
11=Posted
12=Unable to post
13=Resend
14=Posting
15=Content Generated
16=Pending
17=Success With Warning
18=Blocked
19=Restart
2=Delete
3=Error
4=Hold
5=Queued
6=Initiated
7=Processing
8=Cancelled
9=Success

9 PTIAAUNEXTDTTM DateTime(26) TIMESTAMP Next Runtime

10 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	OPRID	Character(30)	VARCHAR2(30) NOT NULL	A user's ID (see PSOPRDEFN).
2	PTIAAUJOBNAME	Character(100)	VARCHAR2(100) NOT NULL	Job Name
3	PTIAAUJOBRUNMODE	Character(1)	VARCHAR2(1) NOT NULL	Run Mode A=Ad hoc S=Scheduled
4	PTIAAUJOBID	Character(100)	VARCHAR2(100) NOT NULL	Job ID
5	PTIAAUJOBSTATE	Character(20)	VARCHAR2(20) NOT NULL	Job State
6	PTIAAU_DTTM_STAMP	DateTime(26)	TIMESTAMP	Run Date Time
7	PRCSINSTANCE	Number(10,0)	DECIMAL(10) NOT NULL	Process Instance
8	RUNSTATUS	Character(2)	VARCHAR2(2) NOT NULL	Process Scheduler Process Run Status 1=Cancel 10=Not Successful 11=Posted 12=Unable to post 13=Resend 14=Posting 15=Content Generated 16=Pending 17=Success With Warning 18=Blocked 19=Restart 2=Delete 3=Error 4=Hold 5=Queued 6=Initiated 7=Processing 8=Cancelled 9=Success
9	PTIAAUNEXTDTTM	DateTime(26)	TIMESTAMP	Next Runtime
10	LASTUPDDTTM	DateTime(26)	TIMESTAMP	Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.