HR_DRPT_2I_VW

(SQL View)
 Index Back

Direct Reports by Supervisor

Used internally by the HR Direct Reports class, this view returns a list of all employee/jobs for which a set of employees has access according to the Supervisor_Id field on Job, when one or more employees' Emplid is "joined" to this view. This is used to determine the number of Indirect Reports for one or more employees.

SELECT JOB.SUPERVISOR_ID , JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , JOB.EFFSEQ , JOB.EMPL_STATUS , JOB.HR_STATUS FROM PS_JOB JOB WHERE JOB.HR_STATUS = 'A' AND JOB.EFFSEQ=( SELECT MAX(JOB3.EFFSEQ) FROM PS_JOB JOB3 WHERE JOB.EMPLID=JOB3.EMPLID AND JOB.EMPL_RCD=JOB3.EMPL_RCD AND JOB.EFFDT=JOB3.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
4 EFFDT Date(10) DATE Effective Date

Default Value: %date

5 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
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 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
A=Active
I=Inactive