HR_RW_JOB_SR_VW(SQL View) |
Index Back |
|---|---|
Remote Worker Job SearchRemote Worker Job Search |
| SELECT JOB.EMPLID , JOB.EMPL_RCD , JOB.JOBCODE , JCODE.DESCR , JOB.POSITION_NBR , POS.DESCR , JOB.DEPTID , JOB.LOCATION , JOB.REG_REGION , JOB.FULL_PART_TIME , JCODE.AVAIL_TELEWORK , POS.AVAIL_TELEWORK_POS FROM PS_Job JOB , Ps_Jobcode_Tbl jcode , Ps_Dept_Tbl Dept , Ps_Location_Tbl loc , PS_POSITION_DATA POS WHERE 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.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' AND POS.POSITION_NBR = job.POSITION_NBR AND POS.effdt = ( SELECT MAX(POS1.effdt) FROM PS_POSITION_DATA POS1 WHERE POS1.POSITION_NBR =POS.POSITION_NBR AND POS1.effdt <= job.effdt ) AND POS.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 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL | Job Code |
| 4 | JOBCODE_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Job Code Description |
| 5 | POSITION | Number(2,0) | SMALLINT NOT NULL | Position |
| 6 | POSN_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Position Description |
| 7 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 8 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
| 9 | REG_REGION | Character(5) | VARCHAR2(5) NOT NULL |
Regulatory Region
Prompt Table: REG_STANDARD_VW |
| 10 | FULL_PART_TIME | Character(1) | VARCHAR2(1) NOT NULL |
Full/Part Time
D=On Demand F=Full-Time P=Part-Time |
| 11 | AVAIL_TELEWORK | Character(1) | VARCHAR2(1) NOT NULL | Job Code Available for Telework |
| 12 | AVAIL_TELEWORK_POS | Character(1) | VARCHAR2(1) NOT NULL | Position Available for Telework |