EP_NOM_JOB_V (SQL View)	Index Back
EP Nominee Primary Job View Used to return the most current Primary Job information at a certain Effective Date for an employee. This is a copy of view PRIMARY_JOB_VW but with a lot less fields.

EP_NOM_JOB_V

(SQL View)

EP Nominee Primary Job View

Used to return the most current Primary Job information at a certain Effective Date for an employee. This is a copy of view PRIMARY_JOB_VW but with a lot less fields.

SELECT J.EMPLID , J.EFFDT , J.EMPL_RCD , J.EFFSEQ , J.DEPTID , J.JOBCODE , J.LOCATION , J.POSITION_NBR , J.EMPL_STATUS , J.JOB_INDICATOR , J.HR_STATUS , J.SETID_DEPT , J.SETID_JOBCODE , J.SETID_LOCATION , LOC.CITY , LOC.STATE FROM PS_JOB J, PS_LOCATION_TBL LOC WHERE J.EMPL_RCD = COALESCE( ( SELECT MIN(A.EMPL_RCD) FROM PS_JOB A WHERE A.EMPLID = J.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 = J.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 = J.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 = J.EMPLID ) ) AND J.EFFSEQ = ( SELECT MAX(J1.EFFSEQ) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD AND J1.EFFDT = J.EFFDT) AND LOC.SETID = J.SETID_LOCATION AND LOC.LOCATION = J.LOCATION AND LOC.EFF_STATUS = 'A' AND LOC.EFFDT = ( SELECT MAX (L1.EFFDT) FROM PS_LOCATION_TBL L1 WHERE L1.SETID = J.SETID_LOCATION AND L1.LOCATION = J.LOCATION AND L1.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 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 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

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

7 LOCATION Character(10) VARCHAR2(10) NOT NULL Location 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 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

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

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

13 SETID Character(5) VARCHAR2(5) NOT NULL SetID

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

15 CITY Character(30) VARCHAR2(30) NOT NULL City

16 STATE Character(6) VARCHAR2(6) NOT NULL State

#	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	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department
6	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code
7	LOCATION	Character(10)	VARCHAR2(10) NOT NULL	Location 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	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
11	HR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	HR Status A=Active I=Inactive
12	SETID_DEPT	Character(5)	VARCHAR2(5) NOT NULL	Department Set ID
13	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID
14	SETID_LOCATION	Character(5)	VARCHAR2(5) NOT NULL	Location Set ID
15	CITY	Character(30)	VARCHAR2(30) NOT NULL	City
16	STATE	Character(6)	VARCHAR2(6) NOT NULL	State