PC_WC_EMP_FL_VW

(SQL View)
 Index Back

View of Active Employees

Looks for all employees defined in PERSONAL_DATA and checks the employee status in the JOB record to retrieve the currently active, Leave With Pay,Leave of Absence and Short Work Break employees.This view is for Projects Fluid WorkCenter.

SELECT A.EMPLID , A.NAME , A.LAST_NAME_SRCH , A.NAME_PREFIX , A.ADDRESS1 , A.ADDRESS2 , A.ADDRESS3 , A.ADDRESS4 , A.CITY , A.STATE , A.COUNTRY , A.PHONE FROM PS_PERSONAL_DATA A , PS_JOB B WHERE A.EMPLID = B.EMPLID AND B.EMPL_STATUS IN ('A', 'P', 'L', 'W') AND B.EFFDT = ( SELECT MAX(B2.EFFDT) FROM PS_JOB B2 WHERE B.EMPLID = B2.EMPLID AND B.EMPL_RCD = B2.EMPL_RCD AND B2.EFFDT <= %CurrentDateIn) AND B.EFFSEQ = ( SELECT MAX(B3.EFFSEQ) FROM PS_JOB B3 WHERE B.EMPLID = B3.EMPLID AND B.EMPL_RCD = B3.EMPL_RCD AND B.EFFDT = B3.EFFDT) AND B.EMPL_RCD = ( SELECT MIN(B0.EMPL_RCD) FROM PS_JOB B0 WHERE B.EMPLID = B0.EMPLID AND B0.EMPL_STATUS IN ('A', 'P', 'L', 'W') AND B0.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_JOB B1 WHERE B1.EMPLID = B0.EMPLID AND B1.EMPL_RCD = B0.EMPL_RCD AND B1.EFFDT <= %CurrentDateIn) AND B0.EFFSEQ = ( SELECT MAX(B4.EFFSEQ) FROM PS_JOB B4 WHERE B4.EMPLID = B0.EMPLID AND B4.EMPL_RCD = B0.EMPL_RCD AND B4.EFFDT = B0.EFFDT))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 PROJECT_MANAGER Character(11) VARCHAR2(11) NOT NULL Project Manager
2 NAME Character(50) VARCHAR2(50) NOT NULL Name
3 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
4 NAME_PREFIX Character(4) VARCHAR2(4) NOT NULL Name Prefix
1=Mr.
2=Mrs.
3=Ms.
5 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
6 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
7 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
8 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
9 CITY Character(30) VARCHAR2(30) NOT NULL City
10 STATE Character(6) VARCHAR2(6) NOT NULL State
11 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
12 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone