EO_PE_CDESRC_VW

(SQL View)
 Index Back

Comp Dir People Search View


SELECT NAM.EMPLID , JOB.EMPL_RCD , NAM.LAST_NAME_SRCH , NAM.FIRST_NAME_SRCH , NAM.SECOND_LAST_SRCH , NAM.PREF_FIRST_NAME , NAM.NAME_AC , NAM.NAME , DEP.DESCR , JOB.LOCATION , LOC.DESCR , NAM.ASOFDATE , JOB.SETID_LOCATION FROM PS_PERSON_NAME NAM , PS_JOB JOB , PS_LOCATION_TBL LOC , PS_DEPT_TBL DEP WHERE NAM.EMPLID = JOB.EMPLID AND JOB.EMPL_STATUS IN ('A','L','P','S','W') AND JOB.EFFDT = ( SELECT MAX(JJB.EFFDT) FROM PS_JOB JJB WHERE JJB.EMPLID = JOB.EMPLID AND JJB.EMPL_RCD = JOB.EMPL_RCD AND JJB.EFFDT <= %CurrentDateIn) AND JOB.EFFSEQ = ( SELECT MAX(JJS.EFFSEQ) FROM PS_JOB JJS WHERE JJS.EMPLID = JOB.EMPLID AND JJS.EMPL_RCD = JOB.EMPL_RCD AND JJS.EFFDT = JOB.EFFDT) AND JOB.LOCATION = LOC.LOCATION AND JOB.SETID_LOCATION = LOC.SETID AND LOC.EFFDT = ( SELECT MAX(LOC2.EFFDT) FROM PS_LOCATION_TBL LOC2 WHERE LOC2.SETID = JOB.SETID_LOCATION AND LOC2.LOCATION = JOB.LOCATION AND LOC2.EFFDT <= %CurrentDateIn) AND JOB.SETID_DEPT = DEP.SETID AND JOB.DEPTID = DEP.DEPTID AND DEP.EFFDT = ( SELECT MAX(DDP.EFFDT) FROM PS_DEPT_TBL DDP WHERE DDP.SETID = JOB.SETID_DEPT AND DDP.DEPTID = JOB.DEPTID AND DDP.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee 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 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Last Name
6 PREF_FIRST_NAME Character(30) VARCHAR2(30) NOT NULL Preferred First Name
7 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
8 NAME Character(50) VARCHAR2(50) NOT NULL Name
9 DEPTNAME Character(30) VARCHAR2(30) NOT NULL Department Name
10 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
11 DESCR Character(30) VARCHAR2(30) NOT NULL Description
12 EFFDT Date(10) DATE NOT NULL Effective Date

Default Value: %date

13 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID