CAREER_SUMM_VW (SQL View)	Index Back
EE Career Summary Data CAREER_SUMM_VW is a view of an employee's JOB data and is used in the Career Planning module. The view essentially shows a summary of the employee's career to date. The view selects the first record where certain data, such as jobcode, department, position number, or grade, changes. So if an employee transfers to another department, for instance, the view selects that JOB record.

CAREER_SUMM_VW

(SQL View)

EE Career Summary Data

CAREER_SUMM_VW is a view of an employee's JOB data and is used in the Career Planning module. The view essentially shows a summary of the employee's career to date. The view selects the first record where certain data, such as jobcode, department, position number, or grade, changes. So if an employee transfers to another department, for instance, the view selects that JOB record.

SELECT A.EMPLID ,A.EMPL_RCD ,A.EFFDT ,A.EFFSEQ ,A.POSITION_NBR ,A.DEPTID ,A.JOBCODE ,A.GRADE ,A.BUSINESS_UNIT , A.SUPV_LVL_ID FROM PS_JOB A WHERE A.EFFDT= ( SELECT MIN(B.EFFDT) FROM PS_JOB B WHERE B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD AND B.POSITION_NBR = A.POSITION_NBR AND B.JOBCODE = A.JOBCODE AND B.DEPTID = A.DEPTID AND B.GRADE = A.GRADE AND B.BUSINESS_UNIT = A.BUSINESS_UNIT) AND A.EFFSEQ= ( SELECT MIN(C.EFFSEQ) FROM PS_JOB C WHERE C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.EFFDT = A.EFFDT AND C.POSITION_NBR = A.POSITION_NBR AND C.JOBCODE = A.JOBCODE AND C.DEPTID = A.DEPTID AND C.GRADE = A.GRADE AND C.BUSINESS_UNIT = A.BUSINESS_UNIT)

# 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 Rcd Nbr

3 EFFDT Date(10) DATE NOT NULL Effective Date

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

5 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number

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

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

8 GRADE Character(3) VARCHAR2(3) NOT NULL Salary Grade

9 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Prompt Table: BUS_UNIT_TBL_HR

10 SUPV_LVL_ID Character(8) VARCHAR2(8) NOT NULL Supervisor Level ID for use in capability grade system
Prompt Table: SUPVSR_LVL_TBL

#	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 Rcd Nbr
3	EFFDT	Date(10)	DATE NOT NULL	Effective Date
4	EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Effective Sequence
5	POSITION_NBR	Character(8)	VARCHAR2(8) NOT NULL	Position Number
6	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department
7	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code
8	GRADE	Character(3)	VARCHAR2(3) NOT NULL	Salary Grade
9	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Prompt Table: BUS_UNIT_TBL_HR
10	SUPV_LVL_ID	Character(8)	VARCHAR2(8) NOT NULL	Supervisor Level ID for use in capability grade system Prompt Table: SUPVSR_LVL_TBL