HR_EMPL_JOB_VW3 (SQL View)	Index Back
Direct Reports Job Data View of current job information to be displayed in the my team

HR_EMPL_JOB_VW3

(SQL View)

Direct Reports Job Data

View of current job information to be displayed in the my team

SELECT DISTINCT A.EMPLID , A.EMPL_RCD , A.EFFDT , A.SETID_JOBCODE , A.JOBCODE , A.JOB_INDICATOR FROM PS_JOB A WHERE A.EFFDT= ( SELECT MAX(A2.EFFDT) FROM PS_JOB A2 WHERE A.EMPLID=A2.EMPLID AND A.EMPL_RCD=A2.EMPL_RCD AND A2.EFFDT<=%CurrentDateIn ) AND A.EFFSEQ= ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A.EMPLID=A3.EMPLID AND A.EMPL_RCD=A3.EMPL_RCD AND A.EFFDT=A3.EFFDT ) AND (HR_STATUS = 'A' OR (HR_STATUS = 'I' AND NOT EXISTS ( SELECT 'X' FROM PS_JOB B WHERE A.EMPLID = B.EMPLID AND B.EFFDT= ( SELECT MAX(B2.EFFDT) FROM PS_JOB B2 WHERE B.EMPLID=B2.EMPLID AND B.EMPL_RCD=B2.EMPL_RCD AND B2.EFFDT<=%CurrentDateIn ) AND B.EFFSEQ= ( SELECT MAX(B3.EFFSEQ) FROM PS_JOB B3 WHERE B.EMPLID=B3.EMPLID AND B.EMPL_RCD=B3.EMPL_RCD AND B.EFFDT=B3.EFFDT ) AND B.HR_STATUS = 'A')))

# 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 EFFDT Date(10) DATE Effective Date
Default Value: %date

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

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

6 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

#	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	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
4	SETID_JOBCODE	Character(5)	VARCHAR2(5) NOT NULL	Job Code Set ID
5	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code
6	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