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