SELECT C.g_plan_ctr, 
       C.g_department, 
       D.name, 
       A.emplid, 
       A.action, 
       B.deptid, 
       A.deptid, 
       To_char(A.effdt, 'YYYY-MM-DD'), 
       A.effseq, 
       To_char(A.action_dt, 'YYYY-MM-DD'), 
       A.jobcode, 
       E.descr, 
       To_char(A.job_entry_dt, 'YYYY-MM-DD'), 
       B.jobcode, 
       F.descr, 
       To_char(B.job_entry_dt, 'YYYY-MM-DD'), 
       A.empl_status, 
       G.empl_status, 
       To_char(G.action_dt, 'YYYY-MM-DD'), 
       To_char(G.effdt, 'YYYY-MM-DD'), 
       To_char(G.termination_dt, 'YYYY-MM-DD'), 
       To_char(C.effdt, 'YYYY-MM-DD') 
FROM   ps_job A, 
       ps_job B, 
       ps_dept_tbl C, 
       ps_personal_vw D, 
       ps_jobcode_tbl E, 
       ps_jobcode_tbl F, 
       ps_job G 
WHERE  ( A.effdt BETWEEN To_date('2013-01-01', 'YYYY-MM-DD') AND 
                                  To_date('2013-01-31', 'YYYY-MM-DD') 
         AND A.empl_status IN ( 'A', 'L' ) 
         AND A.company NOT IN ( 'PEN', 'GFR' ) 
         AND A.emplid = B.emplid 
         AND A.empl_rcd = B.empl_rcd 
         AND B.effdt = (SELECT Max(B_ED.effdt) 
                        FROM   ps_job B_ED 
                        WHERE  B.emplid = B_ED.emplid 
                               AND B.empl_rcd = B_ED.empl_rcd 
                               AND B_ED.effdt < A.effdt) 
         AND B.effseq = (SELECT Max(B_ES.effseq) 
                         FROM   ps_job B_ES 
                         WHERE  B.emplid = B_ES.emplid 
                                AND B.empl_rcd = B_ES.empl_rcd 
                                AND B.effdt = B_ES.effdt) 
         AND A.deptid <> B.deptid 
         AND C.deptid = A.deptid 
         AND C.effdt = (SELECT Max(C_ED.effdt) 
                        FROM   ps_dept_tbl C_ED 
                        WHERE  C.setid = C_ED.setid 
                               AND C.deptid = C_ED.deptid 
                               AND C_ED.effdt <= SYSDATE) 
         AND A.emplid = D.emplid )