HR_I9_QRY_VW

(SQL View)
 Index Back

I9 Query

I9 Query

SELECT A.EMPLID ,A.EFFDT ,B.HIRE_DT ,E.TERMINATION_DT ,H.LAST_HIRE_DT FROM PS_HR_I9_PERSON A , PS_JOB B , PS_JOB E , PS_JOB H WHERE B.EMPLID = H.EMPLID AND B.EMPL_RCD = H.EMPL_RCD AND A.EMPLID = B.EMPLID AND B.HIRE_DT = ( SELECT MIN( C.HIRE_DT) FROM PS_JOB C WHERE C.EMPLID = B.EMPLID ) AND B.EMPL_RCD = ( SELECT MIN( D.EMPL_RCD) FROM PS_JOB D WHERE D.EMPLID = B.EMPLID AND D.HIRE_DT = B.HIRE_DT ) AND B.EFFDT = ( SELECT MAX( I.EFFDT) FROM PS_JOB I WHERE B.EMPLID = I.EMPLID AND B.EMPL_RCD = I.EMPL_RCD AND I.HIRE_DT = B.HIRE_DT ) AND B.EFFSEQ = ( SELECT MAX( J.EFFSEQ) FROM PS_JOB J WHERE B.EMPLID = J.EMPLID AND B.EMPL_RCD = J.EMPL_RCD AND B.EFFDT = J.EFFDT ) AND B.EMPLID = E.EMPLID AND B.EMPL_RCD = E.EMPL_RCD AND (( E.TERMINATION_DT = ( SELECT MAX( F.TERMINATION_DT) FROM PS_JOB F WHERE B.EMPLID = F.EMPLID AND B.EMPL_RCD = F.EMPL_RCD) ) OR (( NOT EXISTS ( SELECT G.TERMINATION_DT FROM PS_JOB G WHERE B.EMPLID = G.EMPLID AND B.EMPL_RCD = G.EMPL_RCD AND G.TERMINATION_DT IS NOT NULL ) AND E.EFFDT = B.EFFDT AND E.EFFSEQ = B.EFFSEQ))) AND H.LAST_HIRE_DT = ( SELECT MAX( K.LAST_HIRE_DT) FROM PS_JOB K WHERE H.EMPLID = K.EMPLID AND H.EMPL_RCD = K.EMPL_RCD ) AND H.EFFDT = ( SELECT MAX( L.EFFDT) FROM PS_JOB L WHERE H.EMPLID = L.EMPLID AND H.EMPL_RCD = L.EMPL_RCD AND H.LAST_HIRE_DT = L.LAST_HIRE_DT ) AND H.EFFSEQ = ( SELECT MAX( M.EFFSEQ) FROM PS_JOB M WHERE H.EMPLID = M.EMPLID AND H.EMPL_RCD = M.EMPL_RCD AND H.EFFDT = M.EFFDT )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EFFDT Date(10) DATE Effective Date

Default Value: %date

3 HIRE_DT Date(10) DATE First Start Date
4 TERMINATION_DT Date(10) DATE Termination Date
5 LAST_HIRE_DT Date(10) DATE Latest Start Date - The latest time that an Employee or a Contingent Worker has started - based on EMPLID/EMPL_RCD.