SR_JOBCODE_VW (SQL View)	Index Back
Job View - for Workload JOB is one of the core records in the database. Use it to record an employee's job history data such as actions taken, department, job code, location, and salary history. You can create multiple JOB records for an employee. This view will show current rows in the Job file.

SR_JOBCODE_VW

(SQL View)

Job View - for Workload

JOB is one of the core records in the database. Use it to record an employee's job history data such as actions taken, department, job code, location, and salary history. You can create multiple JOB records for an employee. This view will show current rows in the Job file.

SELECT A.EMPLID , A.EMPL_RCD , A.EFFDT , A.EFFSEQ , D.STRM , A.DEPTID , B.DESCR , A.JOBCODE , C.DESCR , A.FULL_PART_TIME , A.HR_STATUS , A.PER_ORG , A.EMPL_STATUS FROM PS_JOB A , PS_DEPT_TBL B , PS_JOBCODE_TBL C , PS_INSTRUCTOR_TERM D , PS_INSTR_TERM_DTL E , PS_CLASS_TBL F , PS_TERM_TBL G WHERE A.SETID_DEPT = B.SETID AND A.EMPLID = D.EMPLID AND A.EMPLID = E.EMPLID AND D.STRM = E.STRM AND F.CLASS_NBR = E.CLASS_NBR AND F.STRM = D.STRM AND F.INSTITUTION = E.INSTITUTION AND G.STRM = D.STRM AND G.INSTITUTION = E.INSTITUTION AND G.ACAD_CAREER = F.ACAD_CAREER AND A.DEPTID = B.DEPTID AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_DEPT_TBL B1 WHERE B1.SETID = B.SETID AND B1.DEPTID = B.DEPTID AND B1.EFF_STATUS = 'A' AND B1.EFFDT <= A.EFFDT) AND A.SETID_JOBCODE = C.SETID AND A.JOBCODE = C.JOBCODE AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_JOBCODE_TBL C1 WHERE C1.SETID = C.SETID AND C1.JOBCODE = C.JOBCODE AND C1.EFF_STATUS = 'A' AND C1.EFFDT <= A.EFFDT) AND A.EFFDT <= G.TERM_END_DT

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
Prompt Table: PEOPLE_SRCH

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr

3 EFFDT Date(10) DATE Effective Date

4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence

5 STRM Character(4) VARCHAR2(4) NOT NULL Term

6 DEPTID Character(10) VARCHAR2(10) NOT NULL Department ID

7 DESCR Character(30) VARCHAR2(30) NOT NULL Description

8 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

9 DESCR1 Character(30) VARCHAR2(30) NOT NULL Job Title

10 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
D=On Demand
F=Full-Time
P=Part-Time
Default Value: F

11 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
A=Active
I=Inactive

12 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

13 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Employee 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

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Prompt Table: PEOPLE_SRCH
2	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Rcd Nbr
3	EFFDT	Date(10)	DATE	Effective Date
4	EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Effective Sequence
5	STRM	Character(4)	VARCHAR2(4) NOT NULL	Term
6	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department ID
7	DESCR	Character(30)	VARCHAR2(30) NOT NULL	Description
8	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code
9	DESCR1	Character(30)	VARCHAR2(30) NOT NULL	Job Title
10	FULL_PART_TIME	Character(1)	VARCHAR2(1) NOT NULL	Full/Part Time D=On Demand F=Full-Time P=Part-Time Default Value: F
11	HR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	HR Status A=Active I=Inactive
12	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
13	EMPL_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Employee 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