HCSC_CAL_JOB_VW(SQL View) |
Index Back |
|---|---|
Current Job Data for Team CalView of current job information to be displayed in Team Calendar |
| SELECT DISTINCT A.EMPLID , A.EMPL_RCD , A.EFFDT , A.JOB_INDICATOR , A.POSITION_NBR , A.HR_STATUS , A.EMPL_STATUS , A.FULL_PART_TIME , A.SETID_LOCATION , A.LOCATION , A.SETID_DEPT , A.DEPTID , A.SETID_JOBCODE , A.JOBCODE , ' ' , ' ' , ' ' , ' ' FROM PS_JOB A WHERE A.EFFDT= ( SELECT MAX(A2.EFFDT) FROM PS_JOB A2 WHERE A.EMPLID=A2.EMPLID AND A.EMPL_RCD=A2.EMPL_RCD AND A2.EFFDT<=%CurrentDateIn ) AND A.EFFSEQ= ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A.EMPLID=A3.EMPLID AND A.EMPL_RCD=A3.EMPL_RCD AND A.EFFDT=A3.EFFDT ) AND (HR_STATUS = 'A' OR (HR_STATUS = 'I' AND NOT EXISTS ( SELECT 'X' FROM PS_JOB B WHERE A.EMPLID = B.EMPLID AND B.EFFDT= ( SELECT MAX(B2.EFFDT) FROM PS_JOB B2 WHERE B.EMPLID=B2.EMPLID AND B.EMPL_RCD=B2.EMPL_RCD AND B2.EFFDT<=%CurrentDateIn ) AND B.EFFSEQ= ( SELECT MAX(B3.EFFSEQ) FROM PS_JOB B3 WHERE B.EMPLID=B3.EMPLID AND B.EMPL_RCD=B3.EMPL_RCD AND B.EFFDT=B3.EFFDT ) AND B.HR_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 | EFFDT |
Date(10) | DATE |
Effective Date
Default Value: %date |
| 4 | JOB_INDICATOR | Character(1) | VARCHAR2(1) NOT NULL |
This Indicator is used on JOB to specify which employment record is considered to be the primary one for an employee.
In case of Multiple Jobs per Employee, this property is being used to make a conscious decision in related features where only 1 Employment Record per Employee needs to be returned.
N=Not Applicable P=Primary Job S=Secondary Job |
| 5 | POSITION_NBR | Character(8) | VARCHAR2(8) NOT NULL | Position Number |
| 6 | HR_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
HR Status
A=Active I=Inactive |
| 7 | EMPL_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Payroll Status
A=Active D=Deceased L=Leave of Absence P=Leave With Pay Q=Retired With Pay R=Retired S=Suspended T=Terminated U=Terminated With Pay V=Terminated Pension Pay Out W=Short Work Break X=Retired-Pension Administration |
| 8 | FULL_PART_TIME | Character(1) | VARCHAR2(1) NOT NULL |
Full/Part Time
D=On Demand F=Full-Time P=Part-Time |
| 9 | SETID_LOCATION | Character(5) | VARCHAR2(5) NOT NULL | Location Set ID |
| 10 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
| 11 | SETID_DEPT | Character(5) | VARCHAR2(5) NOT NULL | Department Set ID |
| 12 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 13 | SETID_JOBCODE | Character(5) | VARCHAR2(5) NOT NULL | Job Code Set ID |
| 14 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL | Job Code |
| 15 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 16 | NAME_DISPLAY | Character(50) | VARCHAR2(50) NOT NULL | Display Name - name formatted for Display based on the Country |
| 17 | FIRST_NAME | Character(30) | VARCHAR2(30) NOT NULL | First Name |
| 18 | LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Last Name |