PeopleSoft :: SQL query to retrieve current and previous department

SQL query to retrieve current and previous department

SELECT JOBS.EMPLID AS PSID,PER.NAME ,JOBS.DEPTID AS "Old Dept ID"
,DEP.DEPTID AS "New Dept ID",DEP.EFFDT AS "New Dept ID Effectice Date"
,JOBS.EFFDT AS "Old Dept ID Effectice Date",JOBS.EMPL_RCD
FROM JOBTABLE1 JOBS,NAMETABLE PER ,JOBTABLE2 DEP
WHERE JOBS.EMPLID=PER.EMPLID
AND JOBS.EMPLID=DEP.EMPLID
AND JOBS.EMPL_RCD=DEP.EMPL_RCD
AND DEP.EMPLID=PER.EMPLID
AND JOBS.EFFDT  =   (SELECT MAX(JOB1.EFFDT)                         -- Returning the previous Effective Date
                     FROM  JOBTABLE3 JOB1
                     WHERE JOB1.EMPLID=JOBS.EMPLID
                     AND JOB1.EMPL_RCD=JOBS.EMPL_RCD
                     AND JOB1.EFFDT < DEP.EFFDT)
AND JOBS.EFFSEQ   = (SELECT MAX(JOB2.EFFSEQ)
                    FROM JOBTABLE4 JOB2
                    WHERE JOB2.EMPLID=JOBS.EMPLID
                    AND JOB2.EMPL_RCD=JOBS.EMPL_RCD
                    AND JOB2.EFFDT = JOBS.EFFDT)
AND DEP.EFFDT  =   (SELECT MAX(JOB12.EFFDT)                         -- Returning the current Effective Date
                    FROM JOBTABLE5  JOB12
                    WHERE DEP.EMPLID=JOB12.EMPLID
                    AND DEP.EMPL_RCD=JOB12.EMPL_RCD
                    AND DEP.EFFDT <=SYSDATE)
AND DEP.EFFSEQ   = (SELECT MAX(JOB21.EFFSEQ)
                    FROM JOBTABLE6 JOB21
                    WHERE JOB21.EMPLID=DEP.EMPLID
                    AND JOB21.EMPL_RCD=DEP.EMPL_RCD
                    AND JOB21.EFFDT = DEP.EFFDT)
AND DEP.EMPL_STATUS IN ('A','L','P','S')  
AND JOBS.DEPTID <> DEP.DEPTID                                          --Selecting difference in department
AND JOBS.ACTION <>'TER'                                                           -- Termination
AND DEP.ACTION <> 'REH'                                                      -- Rehire
ORDER BY PER.NAME ASC;