HR_DR_PR_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. The goal of this view is to find the most current active primary job row of an employee. If this employee does not have an active primary row the resultset can contain inactive rows. If more than just one job instance is defined as primary the resultset may consist of more than one row per EE. (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#.

HR_DR_PR_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. The goal of this view is to find the most current active primary job row of an employee. If this employee does not have an active primary row the resultset can contain inactive rows. If more than just one job instance is defined as primary the resultset may consist of more than one row per EE. (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#.

SELECT P.EMPLID , P.EMPL_RCD , P.DEPTID , P.JOBCODE , P.POSITION_NBR , P.EMPL_STATUS , P.LOCATION , P.BUSINESS_UNIT , P.JOB_INDICATOR , P.COMPANY , P.FULL_PART_TIME , P.HR_STATUS , P.SETID_DEPT , P.SETID_JOBCODE , P.SETID_LOCATION , P.POSITION_OVERRIDE , P.SUPERVISOR_ID , P.REPORTS_TO FROM PS_PRIMARY_JOB_VW P WHERE P.EFFDT = ( SELECT MAX(P2.EFFDT) FROM PS_PRIMARY_JOB_VW P2 WHERE P.EMPLID = P2.EMPLID AND P2.EFFDT <= %CurrentDateIn)

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 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record

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

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

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

6 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

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

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

9 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

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

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

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

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

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

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

16 POSITION_OVERRIDE Character(1) VARCHAR2(1) NOT NULL Override Position Data

17 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID

18 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number

#	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 Record
3	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department
4	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code
5	POSITION_NBR	Character(8)	VARCHAR2(8) NOT NULL	Position Number
6	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
7	LOCATION	Character(10)	VARCHAR2(10) NOT NULL	Location Code
8	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
9	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
10	COMPANY	Character(3)	VARCHAR2(3) NOT NULL	Company
11	FULL_PART_TIME	Character(1)	VARCHAR2(1) NOT NULL	Full/Part Time D=On Demand F=Full-Time P=Part-Time
12	HR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	HR Status A=Active I=Inactive
13	SETID_DEPT	Character(5)	VARCHAR2(5) NOT NULL	Department Set ID
14	SETID_JOBCODE	Character(5)	VARCHAR2(5) NOT NULL	Job Code Set ID
15	SETID_LOCATION	Character(5)	VARCHAR2(5) NOT NULL	Location Set ID
16	POSITION_OVERRIDE	Character(1)	VARCHAR2(1) NOT NULL	Override Position Data
17	SUPERVISOR_ID	Character(11)	VARCHAR2(11) NOT NULL	Supervisor ID
18	REPORTS_TO	Character(8)	VARCHAR2(8) NOT NULL	Reports To Position Number