EP_NOM_REQ_VW(SQL View) |
Index Back |
|---|---|
Nominate ParticipantsThis view is used to display the list of nominees which have been submitted as participants for a review and are in a pending status. This record is used during the View Pending Nominations process. |
| SELECT A.EP_APPRAISAL_ID ,A.EMPLID ,A.EMPL_RCD ,A.PERIOD_BEGIN_DT ,A.PERIOD_END_DT ,A.EP_REVIEW_TYPE ,A.EP_MANAGER_ID ,A.EP_NOM_DUE_DAYS ,A.EP_NOM_DUE_TYPE ,A.EP_PRT_STATUS ,B.SETID_JOBCODE ,B.JOBCODE ,NOM.EP_REVIEWER_ID ,NOM.EP_ROLE ,NOM.EP_APPR_NOM_STATUS ,NOM.EP_SUBMIT_NOM_DT ,NOM.EP_NOM_ADDED_BY ,NOM.EP_NOM_SUBMIT_EMPL ,NOM.EP_REVWR_DESCR ,NM.FIRST_NAME_SRCH ,NM.LAST_NAME_SRCH ,NM.NAME_DISPLAY ,C.EP_DOC_USAGE_IND ,AP.EP_DUE_DT ,NOM.EP_COMMENTS FROM PS_EP_APPR A , PS_JOB B , PS_EP_REVW_TYP_TBL C , PS_EP_APPR_NOMINEE NOM , PS_PERSON_NAME NM , PS_EP_APPR_PARTIC AP 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 A.EP_REVIEW_TYPE = C.EP_REVIEW_TYPE AND %EffdtCheck(EP_REVW_TYP_TBL C1, C, A.PERIOD_END_DT) AND A.EP_APPRAISAL_ID = NOM.EP_APPRAISAL_ID AND A.EMPLID = NM.EMPLID AND NOM.EP_APPR_NOM_STATUS = 'P' AND AP.EP_APPRAISAL_ID = NOM.EP_APPRAISAL_ID AND AP.EP_ROLE = NOM.EP_ROLE |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  EP_APPRAISAL_ID |
Number(8,0) | INTEGER NOT NULL |
Document ID
Prompt Table: EP_APPR |
| 2 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 3 | EMPL_RCD | Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 4 | PERIOD_BEGIN_DT | Date(10) | DATE | Period Begin Date |
| 5 | PERIOD_END_DT | Date(10) | DATE | Period End Date |
| 6 | EP_REVIEW_TYPE | Character(8) | VARCHAR2(8) NOT NULL | Review type |
| 7 | EP_MANAGER_ID | Character(11) | VARCHAR2(11) NOT NULL | Manager/Mentor ID. |
| 8 | EP_NOM_DUE_DAYS | Number(3,0) | SMALLINT NOT NULL | Amount of days in which the nominate participants process is due. |
| 9 | EP_NOM_DUE_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Type of due date - Before or After the period end date.
Used for the nominate participants process.
A=After Period End Date B=Before Period End Date |
| 10 | EP_PRT_STATUS | Character(4) | VARCHAR2(4) NOT NULL |
Translate field that determines the state (status) of the review participants feedback step.
CO=Completed IP=In Progress NA=Not Applicable NS=Not Started |
| 11 | SETID | Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 12 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL | Job Code |
| 13 | EP_REVIEWER_ID | Character(11) | VARCHAR2(11) NOT NULL | Reviewer ID |
| 14 | EP_ROLE | Character(8) | VARCHAR2(8) NOT NULL |
Role unique identifier
Prompt Table: EP_ROLE_TBL |
| 15 | EP_APPR_NOM_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Appraisal Nomination Status
A=Accepted C=Canceled D=Declined N=New P=Pending |
| 16 | EP_SUBMIT_NOM_DT | Date(10) | DATE | Date Submitted |
| 17 | EP_NOM_ADDED_BY | Character(11) | VARCHAR2(11) NOT NULL | Nomination Added by |
| 18 | EP_NOM_SUBMIT_EMPL | Character(11) | VARCHAR2(11) NOT NULL | Nomination Submitted by |
| 19 | EP_REVWR_DESCR | Character(60) | VARCHAR2(60) NOT NULL | Used by participants to capture project name. |
| 20 | EP_EE_FIRST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Work field used to search for a evaluatee's first name.. |
| 21 | EP_EE_LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Work field used to search for a evaluatee's last name.. |
| 22 | EP_EVALUATEE_NAME | Character(60) | VARCHAR2(60) NOT NULL | Used to display the evaluatee's name in ePerformance. |
| 23 | 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 |
| 24 | EP_DUE_DT | Date(10) | DATE | Date a review is due |
| 25 | EP_COMMENTS | Long Character | CLOB | Comments field used for documenting performance notes. |