HR_RESIG_JOB_VW

(SQL View)
 Index Back

Employee job View

View used to fetch employee job details in fluid approval summary Page

SELECT A.EMPLID , A.EMPL_RCD , A.REQUEST_DT , A.EFFSEQ ,jcode.Descr , Dept.Descr , loc.Descr ,'EOAW_INFO_ICN' ,'N' ,'' FROM Ps_HR_RESIGN_Dat A , Ps_Job Job , PS_JOBCODE_TBL jcode , Ps_Dept_Tbl Dept ,Ps_Location_Tbl loc WHERE Job.Emplid =A.Emplid AND Job.Empl_Rcd=A.Empl_Rcd AND Job.EFFDT=( SELECT MAX(JOB_1.EFFDT) FROM ps_job job_1 WHERE job_1.emplid=job.emplid AND job_1.empl_rcd=job.empl_rcd AND job_1.effdt <= A.REQUEST_DT) AND job.effseq = ( SELECT MAX(job_2.effseq) FROM ps_job job_2 WHERE job_2.emplid=job.emplid AND job_2.empl_rcd=job.empl_rcd AND job_2.effdt = job.effdt) AND JCODE.SETID= job.setid_jobcode AND jcode.jobcode = job.jobcode AND jcode.effdt = ( SELECT MAX(jcode1.effdt) FROM PS_JOBCODE_TBL jcode1 WHERE jcode1.setid=jcode.setid AND jcode1.jobcode = jcode.jobcode AND jcode1.effdt <= job.effdt) AND jcode.eff_status ='A' AND dept.SETID= job.setid_dept AND dept.DEPTID = job.DEPTID AND dept.effdt = ( SELECT MAX(dept1.effdt) FROM ps_dept_tbl dept1 WHERE dept1.setid=dept.setid AND dept1.deptid = dept.deptid AND dept1.effdt <= job.effdt) AND dept.eff_status ='A' AND loc.SETID= job.setid_location AND loc.location = job.location AND loc.effdt = ( SELECT MAX(loc1.effdt) FROM Ps_Location_Tbl loc1 WHERE loc1.setid=loc.setid AND loc1.location = loc.location AND loc1.effdt <= job.effdt) AND loc.eff_status ='A'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
3 REQUEST_DT Date(10) DATE Request Date
4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
5 JOB_DESCR Character(30) VARCHAR2(30) NOT NULL Job Description
6 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
7 LOC_DESCR_SS Character(30) VARCHAR2(30) NOT NULL Location Description
8 EOPCIMAGE_ID Character(30) VARCHAR2(30) NOT NULL Image
9 EOPCUSE_MAP_DIR Character(1) VARCHAR2(1) NOT NULL Use MAP Image Directory
10 SUPERVISOR_NAME Character(50) VARCHAR2(50) NOT NULL Supervisor Name