EP_NOM_BASE_VW

(SQL View)
 Index Back

Nominee base search rcd

This record is used as a search view for participants in the nomination process.

SELECT JOB.EMPLID ,JOB.EMPL_RCD ,NM.FIRST_NAME_SRCH ,NM.LAST_NAME_SRCH ,DEP.DESCR FROM PS_PERSON_NAME NM ,PS_JOB JOB ,PS_DEPT_TBL DEP WHERE NM.EMPLID = JOB.EMPLID AND JOB.EFFDT = ( SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE JOB.EMPLID=J2.EMPLID AND JOB.EMPL_RCD=J2.EMPL_RCD AND J2.EFFDT <=%CurrentDateIn) AND JOB.EFFSEQ = ( SELECT MAX(J3.EFFSEQ) FROM PS_JOB J3 WHERE JOB.EMPLID = J3.EMPLID AND JOB.EMPL_RCD = J3.EMPL_RCD AND JOB.EFFDT=J3.EFFDT) AND DEP.SETID = JOB.SETID_DEPT AND DEP.DEPTID = JOB.DEPTID AND DEP.EFFDT = ( SELECT MAX(DEP2.EFFDT) FROM PS_DEPT_TBL DEP2 WHERE DEP2.SETID=DEP.SETID AND DEP2.DEPTID=DEP.DEPTID AND DEP2.EFFDT<=JOB.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EP_REVIEWER_ID Character(11) VARCHAR2(11) NOT NULL Reviewer ID
2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
3 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
4 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
5 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description