HR_RW_JOB_VW(SQL View) |
Index Back |
|---|---|
Remote Worker Job DetailsRemote Worker Job Details for approvals |
| SELECT DISTINCT A.EMPLID , A.EMPL_RCD , A.START_DATE , JOB.JOBCODE , JCODE.DESCR , JOB.POSITION_NBR , POS.DESCR ,JOB.DEPTID , JOB.LOCATION ,LOC.DESCR , JOB.REG_REGION , JOB.FULL_PART_TIME , JCODE.AVAIL_TELEWORK ,POS.AVAIL_TELEWORK_POS , JCODE.AVAIL_TELEWORK ,POS.AVAIL_TELEWORK_POS ,JOB.HIRE_DT ,BUS.DESCR FROM PS_HR_RWORK_TBL A , PS_JOBCODE_TBL JCODE , PS_DEPT_TBL DEPT , PS_LOCATION_TBL LOC ,PS_BUS_UNIT_TBL_HR BUS , PS_JOB JOB LEFT OUTER JOIN ( SELECT P.POSITION_NBR ,P.DESCR ,P.AVAIL_TELEWORK_POS FROM PS_POSITION_DATA P WHERE P.EFFDT = ( SELECT MAX(POS1.EFFDT) FROM PS_POSITION_DATA POS1 WHERE POS1.POSITION_NBR =P.POSITION_NBR AND POS1.EFFDT <= %CurrentDateIn) AND P.EFF_STATUS ='A' )POS ON(POS.POSITION_NBR = JOB.POSITION_NBR ) WHERE JOB.EMPLID =A.EMPLID AND BUS.BUSINESS_UNIT=JOB.BUSINESS_UNIT AND BUS.ACTIVE_INACTIVE='A' 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.START_DATE ) 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 | START_DATE | Date(10) | DATE | Start Date for Gen Standing PO |
| 4 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL | Job Code |
| 5 | JOBCODE_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Job Code Description |
| 6 | POSITION | Number(2,0) | SMALLINT NOT NULL | Position |
| 7 | POSN_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Position Description |
| 8 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 9 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
| 10 | LOCATION_DESCR | Character(30) | VARCHAR2(30) NOT NULL |
Location Description
Prompt Table: LOCATION_TBL |
| 11 | REG_REGION | Character(5) | VARCHAR2(5) NOT NULL |
Regulatory Region
Prompt Table: REG_STANDARD_VW |
| 12 | FULL_PART_TIME | Character(1) | VARCHAR2(1) NOT NULL |
Full/Part Time
D=On Demand F=Full-Time P=Part-Time |
| 13 | AVAIL_TELEWORK | Character(1) | VARCHAR2(1) NOT NULL | Job Code Available for Telework |
| 14 | AVAIL_TELEWORK_POS | Character(1) | VARCHAR2(1) NOT NULL | Position Available for Telework |
| 15 | HR_RW_JOB_ELIGIBLE | Character(1) | VARCHAR2(1) NOT NULL |
Job Code Available for RemoteWork
N=No Y=Yes |
| 16 | HR_RW_POS_ELIGIBLE | Character(1) | VARCHAR2(1) NOT NULL |
Position Available for Remote Work
N=No Y=Yes |
| 17 | HIRE_DT | Date(10) | DATE | First Start Date |
| 18 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |