EX_CA_MGRLST_VW(SQL View) |
Index Back |
|---|---|
CA Approval List ViewCA Approval List View |
| SELECT DISTINCT B.EOAWDEFN_ID AS SETID , B.EFFDT , B.OPRID , A.ADVANCE_ID , B.EOAWSTEP_STATUS , A.EOAWTHREAD_ID , A.EOAWPARENT_THREAD , C.APPRVR_TYPE , C.STAGE_ID , D.DESCR FROM PS_EX_ADV_AW A , PS_EOAW_STEP_VW B , PS_EX_TRANS_PROP C , PS_EX_APPRVR_TYPE D , PS_EOAW_STAGE E WHERE A.EOAWDEFN_ID = B.EOAWDEFN_ID AND B.EOAWDEFN_ID = C.SETID AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_EOAW_STEP_VW B1 WHERE B1.EOAWPRCS_ID = B.EOAWPRCS_ID AND B1.EOAWDEFN_ID = B.EOAWDEFN_ID AND B1.EFFDT <= %CurrentDateIn AND B1.EOAWSTAGE_NBR = B.EOAWSTAGE_NBR AND B1.EOAWPATH_ID = B.EOAWPATH_ID AND B1.EOAWSTEP_NBR = B.EOAWSTEP_NBR AND B1.EOAWTHREAD_ID = B.EOAWTHREAD_ID AND B1.EOAWSTEP_INSTANCE = B.EOAWSTEP_INSTANCE AND B1.EOAWUSTEP_INST_ID = B.EOAWUSTEP_INST_ID ) AND B.EOAWDEFN_ID = D.SETID AND A.EOAWTHREAD_STATUS = 'P' AND A.EOAWTHREAD_ID = B.EOAWTHREAD_ID AND A.EOAWPRCS_ID = B.EOAWPRCS_ID AND B.EOAWSTEP_STATUS IN ('P','H') AND B.EOAWDEFN_ID = E.EOAWDEFN_ID AND B.EOAWPRCS_ID = E.EOAWPRCS_ID AND B.EOAWSTAGE_NBR = E.EOAWSTAGE_NBR AND B.EFFDT = E.EFFDT AND E.EOAWPRCS_ID = C.EOAWPRCS_ID AND E.SEQ_NBR = C.STAGE_ID AND C.APPR_TRANS_TYPE = 'CA' AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_EX_TRANS_PROP C1 WHERE C.SETID = C1.SETID AND C.APPR_TRANS_TYPE = C1.APPR_TRANS_TYPE AND C.EFFDT <= %CurrentDateIn AND C.EOAWPRCS_ID = C1.EOAWPRCS_ID AND C.STAGE_ID = C1.STAGE_ID ) AND C.APPRVR_TYPE = D.APPRVR_TYPE |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  SETID |
Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 2 | EFFDT |
Date(10) | DATE |
Effective Date
Default Value: %date |
| 3 | OPRID |
Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 4 | ADVANCE_ID | Character(10) | VARCHAR2(10) NOT NULL | Advance ID |
| 5 | EOAWSTEP_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Step Instance Status
A=Approved B=Pushed back C=Auto Approved D=Denied E=Administrator denied F=Deleted G=Bypassed H=On Hold I=No Action Taken K=Skipped L=Terminated M=Administrator approved N=Not Active P=Pending R=No longer in role T=Terminated U=Auto approved X=Deleted Z=Pending Denial |
| 6 | EOAWTHREAD_ID | Number(15,0) | DECIMAL(15) NOT NULL | Approval Framework Thread ID |
| 7 | EOAWPARENT_THREAD | Number(15,0) | DECIMAL(15) NOT NULL | Parent Thread ID |
| 8 | APPRVR_TYPE | Character(10) | VARCHAR2(10) NOT NULL | Approver type used to cross reference an AWE process id and stage. |
| 9 | STAGE_ID | Number(15,0) | DECIMAL(15) NOT NULL | Unique stage identifier |
| 10 | APPRVR_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Approver Type Description |