HR_DRPT_3I_VW (SQL View)	Index Back
Direct Reports by Dept Mgr Used internally by the HR Direct Reports class, this view returns all employee/jobs that are assigned to a Department for which any in a set of target employees is listed as the manager (according to the Department table). This is used to determine the number of Indirect Reports for one or more employees.

HR_DRPT_3I_VW

(SQL View)

Direct Reports by Dept Mgr

Used internally by the HR Direct Reports class, this view returns all employee/jobs that are assigned to a Department for which any in a set of target employees is listed as the manager (according to the Department table). This is used to determine the number of Indirect Reports for one or more employees.

SELECT D.MANAGER_ID , JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , JOB.EFFSEQ , JOB.EMPL_STATUS , JOB.HR_STATUS , D.EFFDT , JOB.SETID_DEPT , JOB.DEPTID , JOB.PER_ORG FROM PS_JOB JOB , PS_DEPT_TBL D 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) AND D.DEPTID = JOB.DEPTID AND D.SETID = JOB.SETID_DEPT AND D.EFF_STATUS = 'A'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 MANAGER_ID Character(11) VARCHAR2(11) NOT NULL Manager ID

2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record

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

8 EFFDT_DEPT Date(10) DATE Dept Effective Date

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

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

11 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

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	MANAGER_ID	Character(11)	VARCHAR2(11) NOT NULL	Manager ID
2	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
3	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record
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
8	EFFDT_DEPT	Date(10)	DATE	Dept Effective Date
9	SETID_DEPT	Character(5)	VARCHAR2(5) NOT NULL	Department Set ID
10	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department
11	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