EP_APPR_SELI_VW

(SQL View)
 Index Back

Document Selection View

This view is used to display documents available for selection from the Manager's "Item Copy Page". This view selects from "base" tables. And we don't need a corresponding view from "main" tables because "base" tables are always populated even when the process doen't include EC or TP steps.

SELECT A.EP_APPRAISAL_ID , A.EMPLID , A.EMPL_RCD , A.PERIOD_BEGIN_DT , A.PERIOD_END_DT , A.EP_REVIEW_TYPE , A.EP_TEMPLATE_ID , A.EP_TMPL_EFFDT , A.EP_REVIEW_STATUS , A.EP_APPROVAL_STATUS , A.EP_EE_REVIEW_FLG , A.EP_EMPL_ACK_IND , A.EP_EMPL_ACK_DT , A.RATING_MODEL , A.REVIEW_RATING , A.EP_PRE_REVW_RATING , A.EP_MANAGER_ID , B.FIRST_NAME_SRCH , B.LAST_NAME_SRCH , B.NAME_DISPLAY , C.FIRST_NAME_SRCH , C.LAST_NAME_SRCH , C.NAME_DISPLAY , D.EP_DOC_USAGE_IND , S.EP_SECTION_TYPE , S.EP_J_ITEM_CAT_TYPE , A.EP_EST_STATUS , A.EP_TP_SUB_STATUS , A.EP_TRKPROG_STATUS FROM PS_EP_APPR A , PS_EP_APPR_B_SEC S , PS_PERSON_NAME B , PS_NAMES C , PS_EP_REVW_TYP_TBL D WHERE A.EMPLID = B.EMPLID AND A.EP_APPRAISAL_ID = S.EP_APPRAISAL_ID AND A.EP_MANAGER_ID = C.EMPLID AND C.NAME_TYPE = 'PRI' AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_NAMES C1 WHERE C1.EMPLID = C.EMPLID AND C1.NAME_TYPE = C.NAME_TYPE AND C1.EFFDT <= %CurrentDateIn ) AND A.EP_REVIEW_TYPE = D.EP_REVIEW_TYPE AND D.EFFDT = ( SELECT MAX(EFFDT) FROM PS_EP_REVW_TYP_TBL F WHERE F.EP_REVIEW_TYPE = D.EP_REVIEW_TYPE AND EFFDT <=%CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EP_APPRAISAL_ID Number(8,0) INTEGER NOT NULL Document ID
2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PERSON

3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
4 PERIOD_BEGIN_DT Date(10) DATE NOT NULL Period Begin Date
5 PERIOD_END_DT Date(10) DATE NOT NULL Period End Date
6 EP_REVIEW_TYPE Character(8) VARCHAR2(8) NOT NULL Review type
7 EP_TEMPLATE_ID Character(8) VARCHAR2(8) NOT NULL Template unique identifier
8 EP_TMPL_EFFDT Date(10) DATE the effdt of the Template
9 EP_REVIEW_STATUS Character(4) VARCHAR2(4) NOT NULL This field tracks the status of a review document through it's life cycle. This is separate from the approval status of the review, which is tracked separately.
AK=Acknowledged
AR=Shared with Employee
CA=Canceled
CO=Completed
EC=Define Criteria
IP=Evaluation in Progress
NS=Not Started
PA=Approval
RH=Pending Acknowledgement
TP=Track Progress
10 EP_APPROVAL_STATUS Character(4) VARCHAR2(4) NOT NULL This field tracks the current approval status of a review. This is separate from the review's lifecycle status, which is tracked separately.
APRV=Approved
DENY=Denied
ERRO=Approval Error
NREQ=Not Required
OPEN=Not Submitted
SUBM=Submitted
11 EP_EE_REVIEW_FLG Character(2) VARCHAR2(2) NOT NULL Field used to determine the process flow for a performance review.
01=Apvl Before Review-5 Steps
02=Apvl After Review-4 Steps
03=No Apvl with Review-4 Steps
04=No Apvl No Review
05=Apvl No Review
06=No Apvl with Review-2 Steps
07=Apvl Before Review-3 Steps
08=Apvl After Review-3 Steps
12 EP_EMPL_ACK_IND Character(4) VARCHAR2(4) NOT NULL This field tracks whether an employee has acknowledged that a review has been held. Note that it does not indicate whether the employee agrees with the content of the review.
ACKN=Employee Acknowledged
NREQ=Not Required
OPEN=Open
OVRD=Manager Override
RFSD=Employee Refused
13 EP_EMPL_ACK_DT Date(10) DATE This field tracks the date that the employee acknowledged that a review has been held.
14 RATING_MODEL Character(4) VARCHAR2(4) NOT NULL Rating Model
15 REVIEW_RATING Character(1) VARCHAR2(1) NOT NULL Review Rating
16 EP_PRE_REVW_RATING Character(1) VARCHAR2(1) NOT NULL Preliminary Review Rating Code (character).
17 EP_MANAGER_ID Character(11) VARCHAR2(11) NOT NULL Manager/Mentor ID.
18 EP_EE_FIRST_NAME Character(30) VARCHAR2(30) NOT NULL Work field used to search for a evaluatee's first name..
19 EP_EE_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Work field used to search for a evaluatee's last name..
20 EP_EVALUATEE_NAME Character(60) VARCHAR2(60) NOT NULL Used to display the evaluatee's name in ePerformance.
21 EP_MGR_FIRST_NAME Character(30) VARCHAR2(30) NOT NULL Work field used to search for a evaluatee's first name..
22 EP_MGR_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Work field used to search for a manager's last name..
23 EP_MANAGER_NAME Character(60) VARCHAR2(60) NOT NULL Used to display the manager/mentor's name in ePerformance.
24 EP_DOC_USAGE_IND Character(1) VARCHAR2(1) NOT NULL Indicator used to determine what document types are available for performance assessments and which are available for development assessments.
D=Development
P=Performance
25 EP_SECTION_TYPE Character(8) VARCHAR2(8) NOT NULL Section type
26 EP_J_ITEM_CAT_TYPE Character(12) VARCHAR2(12) NOT NULL ePerformance specific version of the JPM Catalog Type field.
27 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
28 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
29 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