EX_EE_ORG_DT_VW

(SQL View)
 Index Back

Personnel Data View

View of data from various personnel tables.

SELECT A.EMPLID , B.EMPL_RCD , A.PER_STATUS , D.EMPL_STATUS , D.DEPTID , B.DFLT_EE_PROF_FLG , B.BUSINESS_UNIT_GL , B.ADV_MAX_AMT , B.LOC_AMT_EXCL_FLG , D.SUPERVISOR_ID , C.PHONE , D.REPORTS_TO FROM PS_PERSONAL_DATA A , PS_EX_EE_ORG_DTL B , PS_EMPLOYMENT C , PS_JOB D WHERE B.EMPLID = A.EMPLID AND C.EMPLID = B.EMPLID AND C.EMPL_RCD = B.EMPL_RCD AND D.EMPLID = C.EMPLID AND D.EMPL_RCD = C.EMPL_RCD AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_JOB D1 WHERE D1.EMPLID = D.EMPLID AND D1.EMPL_RCD = D.EMPL_RCD AND D1.EFFDT <= %CurrentDateIn) AND D.EFFSEQ = ( SELECT MAX(D2.EFFSEQ) FROM PS_JOB D2 WHERE D2.EMPLID = D.EMPLID AND D2.EMPL_RCD = D.EMPL_RCD AND D2.EFFDT = D.EFFDT)

# 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 Rcd Nbr
3 PER_STATUS Character(1) VARCHAR2(1) NOT NULL Personnel Status
E=Employee
N=Non-Employee
4 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Employee Status
A=Active
D=Deceased
I=Inactive
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 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
6 DFLT_EE_PROF_FLG Character(1) VARCHAR2(1) NOT NULL Default Employee Profile Flag

Y/N Table Edit

7 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit
8 ADV_MAX_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Cash Advance Maximum
9 LOC_AMT_EXCL_FLG Character(1) VARCHAR2(1) NOT NULL Ignore Authorized Amounts

Y/N Table Edit

10 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
11 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
12 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number