BEN_PRI_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#.

BEN_PRI_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 JOB1.EMPLID , JOB1.EFFDT , JOB1.EMPL_RCD , JOB1.EFFSEQ , JOB1.PER_ORG , JOB1.DEPTID , JOB1.JOBCODE , JOB1.POSITION_NBR , JOB1.EMPL_STATUS , JOB1.LOCATION , JOB1.REG_REGION , JOB1.BUSINESS_UNIT , JOB1.JOB_INDICATOR , JOB1.COMPANY , JOB1.PAYGROUP , JOB1.TAX_LOCATION_CD , JOB1.REG_TEMP , JOB1.FULL_PART_TIME , JOB1.HR_STATUS , JOB1.SETID_DEPT , JOB1.SETID_JOBCODE , JOB1.SETID_LOCATION , JOB1.POSITION_OVERRIDE , JOB1.SUPERVISOR_ID , JOB1.REPORTS_TO , JOB1.BAS_GROUP_ID , %Coalesce((SELECT B1.DESCR FROM PS_BAS_GROUP_TBL B1 WHERE B1.BAS_GROUP_ID = JOB1.BAS_GROUP_ID),'') , COALESCE(( SELECT DEPT1.DESCR FROM PS_DEPT_TBL DEPT1 WHERE JOB1.SETID_DEPT = DEPT1.SETID AND JOB1.DEPTID = DEPT1.DEPTID AND %EffdtCheck(DEPT_TBL DEPT11, DEPT1, %CurrentDateIn)),' '), COALESCE(( SELECT JCODE1.DESCR FROM PS_JOBCODE_TBL JCODE1 WHERE JOB1.SETID_DEPT = JCODE1.SETID AND JOB1.JOBCODE = JCODE1.JOBCODE AND %EffdtCheck(JOBCODE_TBL JCODE11, JCODE1, %CurrentDateIn)),' '), COALESCE(( SELECT LOCN1.DESCR FROM PS_LOCATION_TBL LOCN1 WHERE JOB1.SETID_DEPT = LOCN1.SETID AND JOB1.LOCATION = LOCN1.LOCATION AND %EffdtCheck(LOCATION_TBL LOCN11, LOCN1, %CurrentDateIn)),' '), COALESCE(( SELECT POSN1.DESCR FROM PS_POSITION_DATA POSN1 WHERE JOB1.POSITION_NBR = POSN1.POSITION_NBR AND %EffdtCheck(POSITION_DATA POSN11, POSN1, %CurrentDateIn)),' '), COALESCE(( SELECT BUSN1.DESCR FROM PS_BUS_UNIT_TBL_HR BUSN1 WHERE BUSN1.BUSINESS_UNIT = JOB1.BUSINESS_UNIT),' '), COALESCE(( SELECT COMP1.DESCR FROM PS_COMPANY_TBL COMP1 WHERE JOB1.COMPANY = COMP1.COMPANY AND %EffdtCheck(COMPANY_TBL COMP11, COMP1, %CurrentDateIn)),' '), COALESCE(( SELECT RR1.DESCR50 FROM PS_REG_REGION_TBL RR1 WHERE RR1.REG_REGION = JOB1.REG_REGION),' '), COALESCE(( SELECT JF1.DESCR FROM PS_JOBCODE_TBL JC1 , PS_JOB_FAMILY_EFVW JF1 WHERE JOB1.SETID_DEPT = JC1.SETID AND JOB1.JOBCODE = JC1.JOBCODE AND %EffdtCheck(JOBCODE_TBL JC11, JC1, %CurrentDateIn) AND JF1.JOB_FAMILY = JC1.JOB_FAMILY),' ') FROM PS_JOB JOB1 WHERE JOB1.EMPL_RCD = COALESCE( ( SELECT MIN(A.EMPL_RCD) FROM PS_JOB A WHERE A.EMPLID = JOB1.EMPLID AND A.EFFDT = ( SELECT MAX (A1.EFFDT) FROM PS_JOB A1 WHERE A1.EMPLID = A.EMPLID AND A1.EMPL_RCD = A.EMPL_RCD AND ((A1.EFFDT <= %CurrentDateIn) OR (A.EFFDT > %CurrentDateIn AND %CurrentDateIn < ( SELECT MIN(A2.EFFDT) FROM PS_JOB A2 WHERE A2.EMPLID = A.EMPLID AND A2.EMPL_RCD = A.EMPL_RCD)))) AND A.EFFSEQ = ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A3.EMPLID = A.EMPLID AND A3.EMPL_RCD = A.EMPL_RCD AND A3.EFFDT = A.EFFDT) AND A.HR_STATUS = 'A' AND A.JOB_INDICATOR = 'P') , ( SELECT MIN(B.EMPL_RCD) FROM PS_JOB B WHERE B.EMPLID = JOB1.EMPLID AND B.EFFDT = ( SELECT MAX (B1.EFFDT) FROM PS_JOB B1 WHERE B1.EMPLID = B.EMPLID AND B1.EMPL_RCD = B.EMPL_RCD AND ((B1.EFFDT <= %CurrentDateIn) OR (B.EFFDT > %CurrentDateIn AND %CurrentDateIn < ( SELECT MIN(B2.EFFDT) FROM PS_JOB B2 WHERE B2.EMPLID = B.EMPLID AND B2.EMPL_RCD = B.EMPL_RCD)))) AND B.EFFSEQ = ( SELECT MAX(B3.EFFSEQ) FROM PS_JOB B3 WHERE B3.EMPLID = B.EMPLID AND B3.EMPL_RCD = B.EMPL_RCD AND B3.EFFDT = B.EFFDT) AND B.HR_STATUS = 'A') , ( SELECT MIN(C.EMPL_RCD) FROM PS_JOB C WHERE C.EMPLID = JOB1.EMPLID AND C.EFFDT = ( SELECT MAX (C1.EFFDT) FROM PS_JOB C1 WHERE C1.EMPLID = C.EMPLID AND C1.EMPL_RCD = C.EMPL_RCD AND ((C1.EFFDT <= %CurrentDateIn) OR (C.EFFDT > %CurrentDateIn AND %CurrentDateIn < ( SELECT MIN(C2.EFFDT) FROM PS_JOB C2 WHERE C2.EMPLID = C.EMPLID AND C2.EMPL_RCD = C.EMPL_RCD)))) AND C.EFFSEQ = ( SELECT MAX(C3.EFFSEQ) FROM PS_JOB C3 WHERE C3.EMPLID = C.EMPLID AND C3.EMPL_RCD = C.EMPL_RCD AND C3.EFFDT = C.EFFDT) AND C.JOB_INDICATOR = 'P') , ( SELECT MIN(D.EMPL_RCD) FROM PS_JOB D WHERE D.EMPLID = JOB1.EMPLID ) ) AND JOB1.EFFSEQ = ( SELECT MAX(J1.EFFSEQ) FROM PS_JOB J1 WHERE J1.EMPLID = JOB1.EMPLID AND J1.EMPL_RCD = JOB1.EMPL_RCD AND J1.EFFDT = JOB1.EFFDT)

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 Record

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

5 PER_ORG Character(3) VARCHAR2(3) NOT NULL Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest.
CWR=Contingent Worker
EMP=Employee
POI=Person of Interest

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

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

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

9 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

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

11 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region

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

13 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

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

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

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

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

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

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

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

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

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

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

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

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

26 BAS_GROUP_ID Character(3) VARCHAR2(3) NOT NULL Identifies a user-defined Benefits Administration group used to facilitate benefits processing. A group can represent the entire participant population of the enterprise, or a specific group of participants. It enables you to control processing by, for example, setting up separate Open Enrollment schedules for each group. If used, a BAS Group ID is assigned to each participant in a benefit program.

27 BAS_GROUP_DESCR Character(30) VARCHAR2(30) NOT NULL Bas Group Description

28 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description

29 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description

30 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description

31 POSN_DESCR Character(30) VARCHAR2(30) NOT NULL Position Description

32 BUSINESS_DESCR Character(60) VARCHAR2(60) NOT NULL Business Description

33 COMPANY_DESCR Character(30) VARCHAR2(30) NOT NULL Company Descr

34 REG_REGION_DESCR Character(50) VARCHAR2(50) NOT NULL Regulatory Region

35 JOB_DESCR Character(30) VARCHAR2(30) NOT NULL Job Description

#	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 Record
4	EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Effective Sequence
5	PER_ORG	Character(3)	VARCHAR2(3) NOT NULL	Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest. CWR=Contingent Worker EMP=Employee POI=Person of Interest
6	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department
7	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code
8	POSITION_NBR	Character(8)	VARCHAR2(8) NOT NULL	Position Number
9	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
10	LOCATION	Character(10)	VARCHAR2(10) NOT NULL	Location Code
11	REG_REGION	Character(5)	VARCHAR2(5) NOT NULL	Regulatory Region
12	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
13	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
14	COMPANY	Character(3)	VARCHAR2(3) NOT NULL	Company
15	PAYGROUP	Character(3)	VARCHAR2(3) NOT NULL	Pay Group
16	TAX_LOCATION_CD	Character(10)	VARCHAR2(10) NOT NULL	Tax Location Code
17	REG_TEMP	Character(1)	VARCHAR2(1) NOT NULL	Regular/Temporary R=Regular T=Temporary
18	FULL_PART_TIME	Character(1)	VARCHAR2(1) NOT NULL	Full/Part Time D=On Demand F=Full-Time P=Part-Time
19	HR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	HR Status A=Active I=Inactive
20	SETID_DEPT	Character(5)	VARCHAR2(5) NOT NULL	Department Set ID
21	SETID_JOBCODE	Character(5)	VARCHAR2(5) NOT NULL	Job Code Set ID
22	SETID_LOCATION	Character(5)	VARCHAR2(5) NOT NULL	Location Set ID
23	POSITION_OVERRIDE	Character(1)	VARCHAR2(1) NOT NULL	Override Position Data
24	SUPERVISOR_ID	Character(11)	VARCHAR2(11) NOT NULL	Supervisor ID
25	REPORTS_TO	Character(8)	VARCHAR2(8) NOT NULL	Reports To Position Number
26	BAS_GROUP_ID	Character(3)	VARCHAR2(3) NOT NULL	Identifies a user-defined Benefits Administration group used to facilitate benefits processing. A group can represent the entire participant population of the enterprise, or a specific group of participants. It enables you to control processing by, for example, setting up separate Open Enrollment schedules for each group. If used, a BAS Group ID is assigned to each participant in a benefit program.
27	BAS_GROUP_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Bas Group Description
28	DEPT_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Department Description
29	JOBCODE_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Job Code Description
30	LOCATION_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Location Description
31	POSN_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Position Description
32	BUSINESS_DESCR	Character(60)	VARCHAR2(60) NOT NULL	Business Description
33	COMPANY_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Company Descr
34	REG_REGION_DESCR	Character(50)	VARCHAR2(50) NOT NULL	Regulatory Region
35	JOB_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Job Description