HR_ACTIVEJOB_VW(SQL View) |
Index Back |
|---|---|
Active Job Vw - NO SECURITYNO SECURITY All Active Jobs, both current and future, as of or greater than the system date (%CurrentDateIn) HR_STATUS = 'A' |
| SELECT J.EMPLID , J.EMPL_RCD , J.EFFDT , N.NAME_DISPLAY , J.JOB_INDICATOR , J.BUSINESS_UNIT , J.COMPANY , J.SETID_JOBCODE , J.JOBCODE , J.SETID_DEPT , J.DEPTID , J.POSITION_NBR , J.PER_ORG , J.SUPERVISOR_ID , J.REPORTS_TO FROM PS_JOB J , PS_PERSON_NAME N WHERE J.EMPLID = N.EMPLID AND J.HR_STATUS = 'A' AND (J.EFFDT = ( SELECT MAX (C.EFFDT) FROM PS_JOB C WHERE C.EMPLID = J.EMPLID AND C.EMPL_RCD = J.EMPL_RCD AND C.EFFDT <= %CurrentDateIn) OR J.EFFDT > %CurrentDateIn) AND J.EFFSEQ = ( SELECT MAX(JE.EFFSEQ) FROM PS_JOB JE WHERE JE.EMPLID = J.EMPLID AND JE.EMPL_RCD = J.EMPL_RCD AND JE.EFFDT = J.EFFDT) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  EMPLID |
Character(11) | VARCHAR2(11) NOT NULL |
Employee ID
Prompt Table: PERSON_NAME |
| 2 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 3 | EFFDT |
Date(10) | DATE | Effective Date |
| 4 | NAME_DISPLAY | Character(50) | VARCHAR2(50) NOT NULL | Display Name - name formatted for Display based on the Country |
| 5 | 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 |
| 6 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: BUS_UNIT_TBL_HR |
| 7 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
| 8 | SETID_JOBCODE | Character(5) | VARCHAR2(5) NOT NULL |
Job Code Set ID
Prompt Table: SET_JOBC_VLD_VW |
| 9 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL |
Job Code
Prompt Table: SET_JOBCD_VW |
| 10 | SETID_DEPT | Character(5) | VARCHAR2(5) NOT NULL |
Department Set ID
Prompt Table: SET_DEPT_VLD_VW |
| 11 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL |
Department
Prompt Table: SET_DEPT_VW |
| 12 | POSITION_NBR | Character(8) | VARCHAR2(8) NOT NULL |
Position Number
Prompt Table: POSITION_DATA |
| 13 | 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 |
| 14 | SUPERVISOR_ID | Character(11) | VARCHAR2(11) NOT NULL | Supervisor ID |
| 15 | REPORTS_TO | Character(8) | VARCHAR2(8) NOT NULL | Reports To Position Number |