SELECT EMPLID

,EFFDT

,END_DT

,COMPANY

,DEPTID

,POSITION_NBR

,' '

,' '

FROM (

SELECT J1.EMPLID

,J1.EFFDT

, lag(EFFDT

,1

,'') over(

ORDER BY EFFDT DESC) AS END_DT

,GPS_UTIL.GET_DESCR_COMPANY( J1.COMPANY

,SYSDATE) AS COMPANY

,GPS_UTIL.GET_DESCR_DEPT_BY_SETID(J1.SETID_DEPT

,J1.DEPTID

,SYSDATE) AS DEPTID

, (

SELECT P.DESCR

FROM PS_POSITION_DATA P

WHERE P.POSITION_NBR=J1.POSITION_NBR

AND %EffdtCheck(POSITION_DATA P2,P,%currentdatein) )AS POSITION_NBR , ' ' AS DC_REFERENCE , ' ' AS PHONE ,J1.ACTION

FROM PS_JOB J1

WHERE J1.EMPLID=:1 )

WHERE ACTION<>'TER'

UNION

SELECT A.EMPLID

, A.START_DT

, A.END_DT

, A.EMPLOYER

,A.DC_DEPERTMENT

, A.ENDING_TITLE

,A.DC_RETERENCE

, A.PHONE

FROM PS_PRIORWORK_EXPER A

WHERE EMPLID=:1