PRIMARY_JOB_VW (SQL View)	Index Back
Primary Job View - HR PRIMARY_JOB_VW is used to return the most current Primary Job information at a certain Effective Date for an Employee. (1) This view returns the 'active' job with the lowest employment record number where the Job_Indicator is equal to "P"; (2) If no row found under (1), it will return the active job with the lowest empl_rcd#; (3) If no row found under (2), it will return the lowest empl_rcd# where the Job_Indicator is equal to "P"; (4) If no row found under (3), it will return the lowest empl_rcd#. Note: This view will always return 1 row per employee as there should be only one Primary Job.

PRIMARY_JOB_VW

(SQL View)

Primary Job View - HR

PRIMARY_JOB_VW is used to return the most current Primary Job information at a certain Effective Date for an Employee. (1) This view returns the 'active' job with the lowest employment record number where the Job_Indicator is equal to "P"; (2) If no row found under (1), it will return the active job with the lowest empl_rcd#; (3) If no row found under (2), it will return the lowest empl_rcd# where the Job_Indicator is equal to "P"; (4) If no row found under (3), it will return the lowest empl_rcd#. Note: This view will always return 1 row per employee as there should be only one Primary Job.

SELECT JOB.EMPLID ,JOB.EFFDT ,JOB.EMPL_RCD ,JOB.EFFSEQ ,JOB.DEPTID ,JOB.JOBCODE ,JOB.POSITION_NBR ,JOB.EMPL_STATUS ,JOB.LOCATION ,JOB.BUSINESS_UNIT ,JOB.JOB_INDICATOR ,JOB.COMPANY ,JOB.PAYGROUP ,JOB.TAX_LOCATION_CD ,REG_TEMP ,FULL_PART_TIME FROM PS_JOB JOB WHERE JOB.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = JOB.EMPLID AND EMPL_RCD = JOB.EMPL_RCD AND EFFDT = JOB.EFFDT) AND JOB.EMPL_RCD = ( SELECT MIN(JOB2.EMPL_RCD) FROM PS_JOB JOB2 WHERE JOB2.EMPLID = JOB.EMPLID AND JOB2.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID = JOB2.EMPLID AND EMPL_RCD = JOB2.EMPL_RCD AND EFFDT <= JOB.EFFDT) AND JOB2.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = JOB2.EMPLID AND EMPL_RCD = JOB2.EMPL_RCD AND EFFDT = JOB2.EFFDT) AND ((JOB2.EMPL_STATUS IN('A','L','P','S','W') AND JOB2.JOB_INDICATOR = 'P') OR NOT EXISTS ( SELECT 'X' FROM PS_JOB JOB3 WHERE JOB3.EMPLID = JOB2.EMPLID AND JOB3.EMPL_RCD <> JOB2.EMPL_RCD AND JOB3.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID = JOB3.EMPLID AND EMPL_RCD = JOB3.EMPL_RCD AND EFFDT <= JOB.EFFDT) AND JOB3.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = JOB3.EMPLID AND EMPL_RCD = JOB3.EMPL_RCD AND EFFDT = JOB3.EFFDT) AND ((JOB3.EMPL_STATUS IN('A','L','P','S','W') AND (JOB2.EMPL_STATUS NOT IN('A','L','P','S','W') OR (JOB3.JOB_INDICATOR = 'P' AND JOB2.JOB_INDICATOR <> 'P'))) OR (JOB3.JOB_INDICATOR = 'P' AND JOB2.JOB_INDICATOR <> 'P' AND JOB2.EMPL_STATUS NOT IN('A','L','P','S','W'))))))

Parent record: JOB_DATE_VW

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

2 EFFDT Date(10) DATE Effective Date

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

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

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

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

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

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

9 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

10 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

11 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

12 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

13 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group

14 TAX_LOCATION_CD Character(10) VARCHAR2(10) NOT NULL Tax Location Code

15 REG_TEMP Character(1) VARCHAR2(1) NOT NULL Regular/Temporary
R=Regular
T=Temporary

16 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
D=On Demand
F=Full-Time
P=Part-Time

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	EFFDT	Date(10)	DATE	Effective Date
3	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Rcd Nbr
4	EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Effective Sequence
5	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department
6	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code
7	POSITION_NBR	Character(8)	VARCHAR2(8) NOT NULL	Position Number
8	EMPL_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Payroll 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
9	LOCATION	Character(10)	VARCHAR2(10) NOT NULL	Location Code
10	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
11	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
12	COMPANY	Character(3)	VARCHAR2(3) NOT NULL	Company
13	PAYGROUP	Character(3)	VARCHAR2(3) NOT NULL	Pay Group
14	TAX_LOCATION_CD	Character(10)	VARCHAR2(10) NOT NULL	Tax Location Code
15	REG_TEMP	Character(1)	VARCHAR2(1) NOT NULL	Regular/Temporary R=Regular T=Temporary
16	FULL_PART_TIME	Character(1)	VARCHAR2(1) NOT NULL	Full/Part Time D=On Demand F=Full-Time P=Part-Time