HR_GSS_SUPV_VW

(SQL View)
 Index Back

Job and Name Info for all EEs

View that retrieves all employee rows from Job and person name.

SELECT A.EMPLID , A.EMPL_RCD , A.EFFDT , A.EMPL_STATUS , B.NAME , B.NAME_DISPLAY , B.LAST_NAME , B.FIRST_NAME , B.LAST_NAME_SRCH , B.SECOND_LAST_SRCH , B.FIRST_NAME_SRCH , B.SECOND_LAST_NAME , A.SETID_DEPT , A.DEPTID , C.DESCR FROM PS_JOB A , PS_PERSON_NAME B , PS_SET_DEPT_VW C WHERE A.EMPLID = B.EMPLID AND A.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = A.EMPLID AND EMPL_RCD = A.EMPL_RCD AND EFFDT = A.EFFDT) AND A.SETID_DEPT = C.SETID_DEPT AND A.DEPTID = C.DEPTID AND %EffdtCheck(SET_DEPT_VW DEPT_ED, C, %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 EFFDT Date(10) DATE Effective Date

Default Value: %date

4 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Payroll Status
A=Active
D=Deceased
L=Leave of Absence
P=Leave With Pay
Q=Retired With Pay
R=Retired
S=Suspended
T=Terminated
U=Terminated With Pay
V=Terminated Pension Pay Out
W=Short Work Break
X=Retired-Pension Administration
5 NAME Character(50) VARCHAR2(50) NOT NULL Name
6 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
7 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
8 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
9 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
10 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Last Name
11 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
12 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name
13 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
14 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
15 DESCR Character(30) VARCHAR2(30) NOT NULL Description