select job.EMPLID,
job.EMPL_RCD,		
job.EFFDT,
job.EFFSEQ,
job.DEPTID,
job.SETID_DEPT,
job.LOCATION,
job.JOBCODE,
job.EMPL_STATUS,
DECODE(job.EMPL_STATUS, 'A', 'Active', 'T', 'Terminated', 'L', 'Leave',  'P', 'Leave W/Py', 'R', 'Retired', 'S', 'Suspended', 'Q', 'Ret w/Pay', 'U', 'Term w/Pay', 'V', 'Term w/Pen', 'X', 'Retired', 'D', 'Deceased'),
xlat.xlatshortname,
rsn.descrshort,
job.PAYGROUP,
job.SAL_ADMIN_PLAN,
job.ANNUAL_RT,
job.C1_LEAVE_TYPE,	
job.LEAVE_STATUS_DT,
job.CHANGE_AMT,
job.CURRENCY_CD,
job.GRADE,		
jcd.GRADE,		
job.POSITION_NBR,
job.SHIFT,
job.STEP,
job.STD_HOURS,
job.COMP_FREQUENCY,
job.HOURLY_RT,
job.REG_TEMP,
DECODE(job.REG_TEMP, 'R','REG', 'T', 'TEMP', 'C', 'COMP', 'B', 'BEN'),
job.ACTION,
job.ACTION_REASON
from
ps_job job,
PS_person per,
ps_jobcode_tbl jcd,
ps_actn_reason_tbl rsn,
xlattable xlat

WHERE job.emplid	 = per.emplid
  and job.effdt 	>= '01-JAN-2004' 
  and  job.action = rsn.action
  and job.action_reason = rsn.action_reason
  and jcd.jobcode	 = job.jobcode
  and jcd.setid		 = job.setid_jobcode
  and jcd.effdt		 = (select max(effdt)
  	  			      from ps_jobcode_tbl
					        where jobcode = jcd.jobcode
			       			and setid   = jcd.setid
							and effdt  <= job.effdt)
  and rsn.effdt		 = (select max(effdt)
			      from ps_actn_reason_tbl
			     where action        = rsn.action
			       and action_reason = rsn.action_reason
			       and effdt        <= sysdate)
  and xlat.fieldvalue	 = job.action
  and xlat.FIELDNAME	 = 'ACTION'
  and xlat.LANGUAGE_CD	 = 'ENG'
  and xlat.effdt	 = (select max(effdt)
  	  				   	      from xlattable
  							  where fieldvalue  = xlat.fieldvalue
 							  and fieldname   = xlat.fieldname
							  and language_cd = xlat.language_cd )