EP_NOM_SRCH_VW

(SQL View)
 Index Back

Nominee Search Results Vw

This view is used to display the results from the nominee search.

SELECT JOB.EMPLID ,JOB.EMPL_RCD ,NM.LAST_NAME_SRCH ,NM.FIRST_NAME_SRCH ,NM.LAST_NAME ,NM.FIRST_NAME ,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 Record
3 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
4 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
5 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
6 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
7 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description