PTAFAW_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.PTAFPRCS_ID , B.PTAFDEFN_ID , B.EFFDT , B.PTAFSTAGE_NBR , B.PTAFPATH_ID , B.PTAFSTEP_NBR , B.PTAFTHREAD_ID , B.PTAFSTEP_INSTANCE , C.PTAFUSTEP_INST_ID , C.OPRID , C.PTAFDTTM_MODIFIED FROM PS_PTAFAW_TIMEOUT A , PS_PTAFAW_STEPINST B , PS_PTAFAW_USERINST C WHERE A.PTAFDEFN_ID = B.PTAFDEFN_ID AND A.PTAFPRCS_ID = B.PTAFPRCS_ID AND A.EFFDT = B.EFFDT AND A.PTAFSTAGE_NBR = B.PTAFSTAGE_NBR AND A.PTAFPATH_ID = B.PTAFPATH_ID AND B.PTAFSTEP_INSTANCE = C.PTAFSTEP_INSTANCE AND (%DateTimeDiff(C.PTAFDTTM_MODIFIED,%CurrentDateTimeIn) - (A.PTAFNUMBER_HOURS + (A.PTAFNUMBER_DAYS *24))* 60) > 0 AND C.PTAFSTEP_STATUS = 'P' AND (A.PTAFNUMBER_HOURS > 0 OR A.PTAFNUMBER_DAYS > 0)

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

Default Value: %date

4 PTAFSTAGE_NBR Number(3,0) SMALLINT NOT NULL Stage Number
5 PTAFPATH_ID Character(30) VARCHAR2(30) NOT NULL Approval Path ID
6 PTAFSTEP_NBR Number(6,2) DECIMAL(5,2) NOT NULL Sequence number of step in its path
7 PTAFTHREAD_ID Number(10,0) DECIMAL(10) NOT NULL Thread ID
8 PTAFSTEP_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 PTAFUSTEP_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 PTAFDTTM_MODIFIED DateTime(26) TIMESTAMP Datetime modified