RS_WO_FU_JOB_VW

(SQL View)
 Index Back

Worker Future Job View

This view is used to display any future job changes recorded in Resoruce Management.

SELECT A.EMPLID , F.EMPL_RCD , A.EFFDT , F.BUSINESS_UNIT , F.DEPTID , F.LOCATION , F.EMPL_STATUS , F.JOBCODE FROM %Table(RS_WRKR_EFFDT) A , %Table(JOB) F WHERE A.EMPLID = F.EMPLID AND A.SYSTEM_SOURCE = 'RS' AND A.EMPL_RCD = F.EMPL_RCD AND A.EFFDT >= ( SELECT MAX(B.EFFDT) FROM %Table(RS_WRKR_EFFDT) B WHERE B.EMPLID = A.EMPLID AND B.SYSTEM_SOURCE = A.SYSTEM_SOURCE AND B.EFFDT <= %CurrentDateIn) AND EXISTS ( SELECT 'X' FROM %Table(RS_WRKR_EFFDT) C WHERE C.EMPLID = A.EMPLID AND C.EFFDT = ( SELECT MAX(D.EFFDT) FROM %Table(RS_WRKR_EFFDT) D WHERE D.EMPLID = C.EMPLID AND D.SYSTEM_SOURCE = C.SYSTEM_SOURCE AND D.EFFDT < A.EFFDT) AND C.EMPL_RCD <> A.EMPL_RCD) AND F.EFFDT = ( SELECT MAX(EFFDT) FROM %Table(JOB) J WHERE F.EMPLID = J.EMPLID AND F.EMPL_RCD = J.EMPL_RCD AND J.EMPL_STATUS = 'A' AND J.EFFDT <= A.EFFDT) AND F.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM %Table(JOB) K WHERE F.EMPLID = K.EMPLID AND F.EMPL_RCD = K.EMPL_RCD AND K.EMPL_STATUS = 'A' AND F.EFFDT = K.EFFDT) AND F.EMPL_STATUS = 'A'

# 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 EFFDT Date(10) DATE Effective Date
4 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
5 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
6 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
7 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
8 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code