SELECT A.EMPLID, B.NAME_PSFORMAT, COUNT( A.EMPLID)

FROM PS_JOB A, PS_EMPLMT_SRCH_QRY A1, PS_PERSON_NAME B

WHERE A.EMPLID = A1.EMPLID

AND A.EMPL_RCD = A1.EMPL_RCD

AND A1.OPRID = '23420211'

AND A.EFFDT =

(SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED

WHERE A.EMPLID = A_ED.EMPLID

AND A.EMPL_RCD = A_ED.EMPL_RCD

AND A_ED.EFFDT <= SYSDATE)

AND A.EFFSEQ =

(SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES

WHERE A.EMPLID = A_ES.EMPLID

AND A.EMPL_RCD = A_ES.EMPL_RCD

AND A.EFFDT = A_ES.EFFDT)

AND A.HR_STATUS = 'A'

AND A.EMPLID = B.EMPLID

GROUP BY A.EMPLID, B.NAME_PSFORMAT

HAVING COUNT( A.EMPLID) > =2

ORDER BY 1