HCDL_ALL_JOB_VW(SQL View) |
Index Back |
|---|---|
Current Job for all PersonsHCDL_ALL_JOB_VW is a view of some of the fields of the curent Job record for delegation. |
| SELECT A.EMPLID ,A.EMPL_RCD ,A.EFFDT ,A.BUSINESS_UNIT ,A.PER_ORG ,A.EMPL_STATUS ,A.HR_STATUS ,A.DEPTID ,A.JOBCODE ,B.DESCR ,A.LOCATION ,A.POSITION_NBR ,A.REPORTS_TO ,A.SUPERVISOR_ID ,A.COMPANY ,A.SETID_DEPT ,A.SETID_JOBCODE ,A.SETID_LOCATION ,DEPT.DESCR FROM PS_JOB A , PS_JOBCODE_TBL_VW B , PS_DEPT_TBL_EFF_VW DEPT WHERE A.EFFDT = ( SELECT MAX (C.EFFDT) FROM PS_JOB C WHERE C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(D.EFFSEQ) FROM PS_JOB D WHERE D.EMPLID = A.EMPLID AND D.EMPL_RCD = A.EMPL_RCD AND D.EFFDT = A.EFFDT) AND A.SETID_JOBCODE = B.SETID AND A.JOBCODE = B.JOBCODE AND A.SETID_DEPT = DEPT.SETID AND A.DEPTID = DEPT.DEPTID |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  EMPLID |
Character(11) | VARCHAR2(11) NOT NULL |
Employee ID
Prompt Table: PERSON |
| 2 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL | Empl Rcd Nbr |
| 3 | EFFDT | Date(10) | DATE | Effective Date |
| 4 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: BUS_UNIT_TBL_HR |
| 5 | PER_ORG | Character(3) | VARCHAR2(3) NOT NULL |
Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest.
CWR=Contingent Worker EMP=Employee POI=Person of Interest |
| 6 | 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 |
| 7 | HR_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
HR Status
A=Active I=Inactive |
| 8 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL |
Department
Prompt Table: DEPT_TBL |
| 9 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL |
Job Code
Prompt Table: JOBCODE_TBL |
| 10 | JOBTITLE | Character(30) | VARCHAR2(30) NOT NULL | Job Title |
| 11 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
| 12 | POSITION_NBR | Character(8) | VARCHAR2(8) NOT NULL |
Position Number
Prompt Table: POSITION_DATA |
| 13 | REPORTS_TO | Character(8) | VARCHAR2(8) NOT NULL | Reports To Position Number |
| 14 | SUPERVISOR_ID | Character(11) | VARCHAR2(11) NOT NULL | Supervisor ID |
| 15 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
| 16 | SETID_DEPT | Character(5) | VARCHAR2(5) NOT NULL | Department SetID |
| 17 | SETID_JOBCODE | Character(5) | VARCHAR2(5) NOT NULL | Job Code SetID |
| 18 | SETID_LOCATION | Character(5) | VARCHAR2(5) NOT NULL | Location SetID |
| 19 | DEPTNAME | Character(30) | VARCHAR2(30) NOT NULL | Department |