EP_SEL_ROLE_VW

(SQL View)
 Index Back

Document Selection View

This view is used to display documents available for selection from the generic "Document Selection Page".

SELECT A.EP_APPRAISAL_ID , A.EMPLID , A.EMPL_RCD , A.PERIOD_BEGIN_DT , A.PERIOD_END_DT , A.EP_REVIEW_TYPE , A.EP_REVIEW_STATUS , R.EP_REVIEWER_ID , A.EP_APPROVAL_STATUS , A.EP_EE_REVIEW_FLG , A.EP_EMPL_ACK_IND , A.EP_EMPL_ACK_DT , A.RATING_MODEL , A.EP_PRE_REVW_RATING , A.EP_MANAGER_ID , B.SETID_JOBCODE , B.JOBCODE , C.FIRST_NAME_SRCH , C.LAST_NAME_SRCH , C.NAME_DISPLAY , D.FIRST_NAME_SRCH , D.LAST_NAME_SRCH , D.NAME_DISPLAY ,E.EP_DOC_USAGE_IND FROM PS_EP_APPR A , PS_EP_APPR_ROLE R , PS_JOB B , PS_PERSON_NAME C , PS_PERSON_NAME D , PS_EP_REVW_TYP_TBL E WHERE B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD AND ( B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_JOB B1 WHERE B1.EMPLID = B.EMPLID AND B1.EMPL_RCD = B.EMPL_RCD AND B1.EFFDT <= A.PERIOD_END_DT) OR ( NOT EXISTS ( SELECT 'X' FROM PS_JOB B2 WHERE B2.EMPLID = B.EMPLID AND B2.EMPL_RCD = B.EMPL_RCD AND B2.EFFDT <= A.PERIOD_END_DT) AND B.EFFDT = ( SELECT MIN(B3.EFFDT) FROM PS_JOB B3 WHERE B3.EMPLID = B.EMPLID AND B3.EMPL_RCD = B.EMPL_RCD) ) ) AND B.EFFSEQ = ( SELECT MAX(B2.EFFSEQ) FROM PS_JOB B2 WHERE B2.EMPLID = B.EMPLID AND B2.EMPL_RCD = B.EMPL_RCD AND B2.EFFDT = B.EFFDT) AND C.EMPLID = A.EMPLID AND D.EMPLID = A.EP_MANAGER_ID AND A.EP_REVIEW_TYPE=E.EP_REVIEW_TYPE AND E.EFFDT=( SELECT MAX(EFFDT) FROM PS_EP_REVW_TYP_TBL F WHERE F.EP_REVIEW_TYPE =E.EP_REVIEW_TYPE AND EFFDT <=%CurrentDateIn) AND A.EP_APPRAISAL_ID = R.EP_APPRAISAL_ID AND R.EP_ROLE NOT IN ('M', 'E')

# 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

Prompt Table: EP_REVW_TYP_VW

7 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
8 EP_PARTICIPANT_ID Character(11) VARCHAR2(11) NOT NULL Participant ID/Nominee
9 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
10 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
11 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
12 EP_EMPL_ACK_DT Date(10) DATE This field tracks the date that the employee acknowledged that a review has been held.
13 RATING_MODEL Character(4) VARCHAR2(4) NOT NULL Rating Model
14 EP_PRE_REVW_RATING Character(1) VARCHAR2(1) NOT NULL Preliminary Review Rating Code (character).

Prompt Table: REVW_RATING_TBL

15 EP_MANAGER_ID Character(11) VARCHAR2(11) NOT NULL Manager/Mentor ID.
16 SETID Character(5) VARCHAR2(5) NOT NULL SetID
17 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
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