PC_ALLEMP_VW

(SQL View)
 Index Back

View of Active JOB employees

View of Active JOB employees AND personal_data employees and which are not in job record.

SELECT A.EMPLID , A.NAME , A.NAME_PREFIX , A.ADDRESS1 , A.ADDRESS2 , A.ADDRESS3 , A.ADDRESS4 , A.CITY , A.STATE , A.COUNTRY , A.PHONE FROM PS_PERSONAL_DATA A WHERE NOT EXISTS ( SELECT 'X' FROM PS_JOB B WHERE B.EMPLID = A.EMPLID ) UNION SELECT A.EMPLID , A.NAME , 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 NAME_PREFIX Character(4) VARCHAR2(4) NOT NULL Name Prefix
1=Mr.
2=Mrs.
3=Ms.
4 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
5 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
6 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
7 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
8 CITY Character(30) VARCHAR2(30) NOT NULL City
9 STATE Character(6) VARCHAR2(6) NOT NULL State
10 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
11 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone