EOAW_ESCAL_VW

(SQL View)
 Index Back

Escalation view for Approvals

This view identifies approval step instances that need to be escalated based on the path settings.

SELECT DISTINCT B.EOAWPRCS_ID , B.EOAWDEFN_ID , B.EFFDT , B.EOAWSTAGE_NBR , B.EOAWPATH_ID , B.EOAWSTEP_NBR , B.EOAWTHREAD_ID , B.EOAWSTEP_INSTANCE , C.EOAWUSTEP_INST_ID , C.OPRID , C.EOAWDTTM_MODIFIED , 'N' , A.EOAWACTUAL_APPR FROM PS_EOAW_TIMEOUT A , PS_EOAW_STEPINST B , PS_EOAW_USERINST C , PS_EOAW_PRCS D WHERE A.EOAWDEFN_ID = B.EOAWDEFN_ID AND A.EOAWPRCS_ID = B.EOAWPRCS_ID AND A.EFFDT = B.EFFDT AND A.EOAWDEFN_ID = D.EOAWDEFN_ID AND A.EOAWPRCS_ID = D.EOAWPRCS_ID AND A.EFFDT = D.EFFDT AND A.EOAWSTAGE_NBR = B.EOAWSTAGE_NBR AND A.EOAWPATH_ID = B.EOAWPATH_ID AND B.EOAWSTEP_INSTANCE = C.EOAWSTEP_INSTANCE AND (%DateTimeDiff(C.EOAWDTTM_MODIFIED,%CurrentDateTimeIn) - (A.EOAWNUMBER_HOURS + (A.EOAWNUMBER_DAYS *24))* 60) > 0 AND C.EOAWSTEP_STATUS = 'P' AND D.EOAW_CALENDAR = ' ' AND (A.EOAWNUMBER_HOURS > 0 OR A.EOAWNUMBER_DAYS > 0) UNION SELECT DISTINCT B.EOAWPRCS_ID , B.EOAWDEFN_ID , B.EFFDT , B.EOAWSTAGE_NBR , B.EOAWPATH_ID , B.EOAWSTEP_NBR , B.EOAWTHREAD_ID , B.EOAWSTEP_INSTANCE , C.EOAWUSTEP_INST_ID , C.OPRID , C.EOAWDTTM_MODIFIED , 'Y' , A.EOAWACTUAL_APPR FROM PS_EOAW_TIMEOUTDEF A , PS_EOAW_STEPINST B , PS_EOAW_USERINST C , PS_EOAW_PRCS D WHERE A.EOAWDEFN_ID = B.EOAWDEFN_ID AND A.EOAWPRCS_ID = B.EOAWPRCS_ID AND A.EFFDT = B.EFFDT AND A.EOAWDEFN_ID = D.EOAWDEFN_ID AND A.EOAWPRCS_ID = D.EOAWPRCS_ID AND A.EFFDT = D.EFFDT AND D.EOAW_CALENDAR = ' ' AND B.EOAWSTEP_INSTANCE = C.EOAWSTEP_INSTANCE AND (%DateTimeDiff(C.EOAWDTTM_MODIFIED,%CurrentDateTimeIn) - (A.EOAWNUMBER_HOURS + (A.EOAWNUMBER_DAYS *24))* 60) > 0 AND C.EOAWSTEP_STATUS = 'P' AND (A.EOAWNUMBER_HOURS > 0 OR A.EOAWNUMBER_DAYS > 0) AND NOT EXISTS ( SELECT 'X' FROM PS_EOAW_TIMEOUT F WHERE F.EOAWPRCS_ID = A.EOAWPRCS_ID AND F.EOAWDEFN_ID = A.EOAWDEFN_ID AND F.EFFDT = A.EFFDT AND F.EOAWSTAGE_NBR = B.EOAWSTAGE_NBR AND F.EOAWPATH_ID = B.EOAWPATH_ID AND (F.EOAWNUMBER_HOURS > 0 OR F.EOAWNUMBER_DAYS > 0))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EOAWPRCS_ID Character(30) VARCHAR2(30) NOT NULL Approval Process ID
2 EOAWDEFN_ID Character(30) VARCHAR2(30) NOT NULL Approval Framework Definition ID
3 EFFDT Date(10) DATE Effective Date

Default Value: %date

4 EOAWSTAGE_NBR Number(3,0) SMALLINT NOT NULL Approval Framework Stage Number
5 EOAWPATH_ID Character(30) VARCHAR2(30) NOT NULL Approval Path ID
6 EOAWSTEP_NBR Number(6,2) DECIMAL(5,2) NOT NULL Sequence number of step in its path
7 EOAWTHREAD_ID Number(15,0) DECIMAL(15) NOT NULL Approval Framework Thread ID
8 EOAWSTEP_INSTANCE Number(15,0) DECIMAL(15) NOT NULL Object id of a step instance. Generated off the main thread id for the approval process.
9 EOAWUSTEP_INST_ID Number(15,0) DECIMAL(15) NOT NULL Object id of a step instance. Generated off the main thread id for the approval process.
10 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
11 EOAWDTTM_MODIFIED DateTime(26) TIMESTAMP Datetime modified
12 EOAW_DEFLVL_ESC Character(1) VARCHAR2(1) NOT NULL Definition Level Escalation
13 EOAWACTUAL_APPR Character(1) VARCHAR2(1) NOT NULL Use Proxy Participant