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.POSITION_NBR ,A.DEPTID ,A.JOBCODE ,A.GRADE ,A.BUSINESS_UNIT 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
Default Value: NEW
Prompt Table: PERSONAL_DATA

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

3 EFFDT Date(10) DATE NOT NULL Effective Date
Default Value: %date

4 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
Prompt Table: POSITION_DATA

5 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
Prompt Table: DEPT_TBL

6 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
Prompt Table: JOBCODE_TBL
Set Control Field: SETID_JOBCODE

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

8 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Prompt Table: SP_BURSHR_NONVW

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Default Value: NEW Prompt Table: PERSONAL_DATA
2	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Rcd Nbr
3	EFFDT	Date(10)	DATE NOT NULL	Effective Date Default Value: %date
4	POSITION_NBR	Character(8)	VARCHAR2(8) NOT NULL	Position Number Prompt Table: POSITION_DATA
5	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department Prompt Table: DEPT_TBL
6	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code Prompt Table: JOBCODE_TBL Set Control Field: SETID_JOBCODE
7	GRADE	Character(3)	VARCHAR2(3) NOT NULL	Salary Grade
8	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Prompt Table: SP_BURSHR_NONVW