EP_CMPL_CKPT_VW

(SQL View)
 Index Back

Completed/skipped checkpoints

Returns section ratings and comments for completed and skipped checkpoint steps and completed Finalize Criteria step. Finalize Criteria step = chkpt type "C" (there could be multiple rows if this step was reopened). Select first FC row if the FC step status is complete. Checkpoint step = chkpt type "S". ("Complete" or "Skipped" checkpoints are selected).

SELECT A.EP_APPRAISAL_ID , C.EP_SECTION_TYPE , B.EP_CHKPT_NBR , B.EP_CHKPT_TYPE , B.EP_CHKPT_COMPLETE , A.EP_EST_STATUS , A.EP_TRKPROG_STATUS , A.EP_TP_SUB_STATUS , C.EP_EE_REVW_RATING , C.EP_SEC_EE_CMNTS , C.EP_MGR_REVW_RATING , C.EP_SEC_MGR_CMNTS FROM PS_EP_APPR A , PS_EP_APPR_C D , PS_EP_APPR_CHECKPT B , PS_EP_APPR_C_SEC C WHERE D.EP_APPRAISAL_ID = A.EP_APPRAISAL_ID AND B.EP_APPRAISAL_ID = D.EP_APPRAISAL_ID AND B.EP_CHKPT_NBR = D.EP_CHKPT_NBR AND C.EP_APPRAISAL_ID = B.EP_APPRAISAL_ID AND C.EP_CHKPT_NBR = B.EP_CHKPT_NBR AND (((B.EP_CHKPT_COMPLETE = 'C' OR B.EP_CHKPT_COMPLETE = 'S') AND B.EP_CHKPT_TYPE = 'S') OR (B.EP_CHKPT_TYPE = 'C' AND B.EP_CHKPT_NBR = (select MAX(EP_CHKPT_NBR) from PS_EP_APPR_CHECKPT B1 where B1.EP_APPRAISAL_ID=B.EP_APPRAISAL_ID and EP_CHKPT_TYPE='C') and A.EP_TRKPROG_STATUS = 'CO' And A.EP_TP_SUB_STATUS = 'F'))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EP_APPRAISAL_ID Number(8,0) INTEGER NOT NULL Document ID
2 EP_SECTION_TYPE Character(8) VARCHAR2(8) NOT NULL Section type
3 EP_CHKPT_NBR Number(2,0) SMALLINT NOT NULL the number of the checkpoint during the track progress step
4 EP_CHKPT_TYPE Character(1) VARCHAR2(1) NOT NULL Checkpoint Type
A=Approved
C=Completed
S=Scheduled
5 EP_CHKPT_COMPLETE Character(1) VARCHAR2(1) NOT NULL Completed
C=Completed
S=Skipped
6 EP_EST_STATUS Character(4) VARCHAR2(4) NOT NULL Translate field that determines the state (status) of the establish criteria step.
CO=Completed
IP=In Progress
NA=Not Applicable
NS=Not Started
7 EP_TRKPROG_STATUS Character(4) VARCHAR2(4) NOT NULL Status of the Track Progress step in the process
CO=Completed
IP=In Progress
NA=Not Applicable
NS=Not Started
8 EP_TP_SUB_STATUS Character(2) VARCHAR2(2) NOT NULL Sub Status for the Track Progress step in the process. The value will be 1-11 or 99 for Finalize.
1=1
10=10
11=11
2=2
3=3
4=4
5=5
6=6
7=7
8=8
9=9
F=F
9 EP_EE_REVW_RATING Character(1) VARCHAR2(1) NOT NULL Review Rating
10 EP_SEC_EE_CMNTS Long Character CLOB Track progress document - EE comments at section level
11 EP_MGR_REVW_RATING Character(1) VARCHAR2(1) NOT NULL Review Rating
12 EP_SEC_MGR_CMNTS Long Character CLOB Track progress document - mgr comments at section level