EX_INACT_EE_VW

(SQL View)
 Index Back

Inactive employee transactions

Used in EX WorkCenter

SELECT A.EMPLID , B.EMPL_STATUS , B.EFFDT , B.BUSINESS_UNIT , B.DEPTID FROM PS_EX_SHEET_HDR A , PS_JOB B WHERE A.SHEET_STATUS NOT IN ('PD', 'CLS', 'ESC', 'DEN', 'DNA', 'DNU') AND A.EMPLID = B.EMPLID AND B.EMPL_STATUS IN ('D','I','R', 'Q','X', 'S','T','U','V') AND B.EFFDT = ( SELECT MAX(C.EFFDT) FROM PS_JOB C WHERE B.EMPLID = C.EMPLID ) AND B.EFFSEQ = ( SELECT MAX(D.EFFSEQ) FROM PS_JOB D WHERE B.EMPLID = D.EMPLID AND D.EFFDT = B.EFFDT ) UNION SELECT A.EMPLID , B.EMPL_STATUS , B.EFFDT , B.BUSINESS_UNIT , B.DEPTID FROM PS_EX_ADV_HDR A , PS_JOB B WHERE A.ADV_STATUS <> 'RCN' AND A.EMPLID = B.EMPLID AND B.EMPL_STATUS IN ('D','I','R', 'Q','X', 'S','T','U','V') AND B.EFFDT = ( SELECT MAX(C.EFFDT) FROM PS_JOB C WHERE B.EMPLID = C.EMPLID ) AND B.EFFSEQ = ( SELECT MAX(D.EFFSEQ) FROM PS_JOB D WHERE B.EMPLID = D.EMPLID AND D.EFFDT = B.EFFDT ) UNION SELECT A.EMPLID , B.EMPL_STATUS , B.EFFDT , B.BUSINESS_UNIT , B.DEPTID FROM PS_EX_TAUTH_HDR A , PS_JOB B WHERE A.TRAVEL_AUTH_STATUS NOT IN ('CLS', 'DEN', 'DNA', 'DNU') AND A.EMPLID = B.EMPLID AND B.EMPL_STATUS IN ('D','I','R', 'Q','X', 'S','T','U','V') AND B.EFFDT = ( SELECT MAX(C.EFFDT) FROM PS_JOB C WHERE B.EMPLID = C.EMPLID ) AND B.EFFSEQ = ( SELECT MAX(D.EFFSEQ) FROM PS_JOB D WHERE B.EMPLID = D.EMPLID AND D.EFFDT = B.EFFDT ) UNION SELECT A.EMPLID , B.EMPL_STATUS , B.EFFDT , B.BUSINESS_UNIT , B.DEPTID FROM PS_EX_TRANS A , PS_JOB B WHERE A.TXN_STATUS = 'U' AND A.EMPLID = B.EMPLID AND B.EMPL_STATUS IN ('D','I','R', 'Q','X', 'S','T','U','V') AND B.EFFDT = ( SELECT MAX(C.EFFDT) FROM PS_JOB C WHERE B.EMPLID = C.EMPLID ) AND B.EFFSEQ = ( SELECT MAX(D.EFFSEQ) FROM PS_JOB D WHERE B.EMPLID = D.EMPLID AND D.EFFDT = B.EFFDT ) UNION SELECT A.EMPLID , B.EMPL_STATUS , B.EFFDT , B.BUSINESS_UNIT , B.DEPTID FROM PS_EX_TIME_HDR A , PS_JOB B WHERE A.TIME_SHEET_STATUS NOT IN ('APR', 'DEN', 'DNA', 'DNU') AND A.EMPLID = B.EMPLID AND B.EMPL_STATUS IN ('D','I','R', 'Q','X', 'S','T','U','V') AND B.EFFDT = ( SELECT MAX(C.EFFDT) FROM PS_JOB C WHERE B.EMPLID = C.EMPLID ) AND B.EFFSEQ = ( SELECT MAX(D.EFFSEQ) FROM PS_JOB D WHERE B.EMPLID = D.EMPLID AND D.EFFDT = B.EFFDT )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PERSONAL_DATA

2 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
3 EFFDT Date(10) DATE Effective Date

Default Value: %date

4 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: SP_BURSHR_NONVW

5 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: DEPTID_NB_VW