HR_CP_JOB_VW2 (SQL View)	Index Back
History of Primary Jobs This view is created to show the distinct job roles held by the employee. This view was cloned based on HR_CP_JOB_VW and CAREER_SUMM_VW. Note that the effective date subquery is based on CAREER_SUMM_VW.

HR_CP_JOB_VW2

(SQL View)

History of Primary Jobs

This view is created to show the distinct job roles held by the employee. This view was cloned based on HR_CP_JOB_VW and CAREER_SUMM_VW. Note that the effective date subquery is based on CAREER_SUMM_VW.

SELECT DISTINCT A.EMPLID ,A.BUSINESS_UNIT ,A.SETID_DEPT ,A.DEPTID ,A.SETID_LOCATION ,A.LOCATION ,A.SETID_JOBCODE ,A.JOBCODE ,A.EFFDT ,A.ASGN_END_DT 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 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT
Prompt Table: BUSUNIT_HR_VW

3 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID

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

5 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID

6 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
Prompt Table: LOCATION_TBL

7 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID

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

9 LST_ASGN_START_DT Date(10) DATE Lastest start date of the specific EMPL_RCD assignment. This is different from the Person's organizational instance Last Hire Date.

10 ASGN_END_DT Date(10) DATE End date of the specific EMPL_RCD assignment. This is different from the Person's Termination date.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT Prompt Table: BUSUNIT_HR_VW
3	SETID_DEPT	Character(5)	VARCHAR2(5) NOT NULL	Department Set ID
4	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department Prompt Table: DEPT_TBL
5	SETID_LOCATION	Character(5)	VARCHAR2(5) NOT NULL	Location Set ID
6	LOCATION	Character(10)	VARCHAR2(10) NOT NULL	Location Code Prompt Table: LOCATION_TBL
7	SETID_JOBCODE	Character(5)	VARCHAR2(5) NOT NULL	Job Code Set ID
8	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code
9	LST_ASGN_START_DT	Date(10)	DATE	Lastest start date of the specific EMPL_RCD assignment. This is different from the Person's organizational instance Last Hire Date.
10	ASGN_END_DT	Date(10)	DATE	End date of the specific EMPL_RCD assignment. This is different from the Person's Termination date.