HR_DRPT_7A_VW (SQL View)	Index Back
Direct Reports by GroupBuild Used internally by the HR Direct Reports class, this view returns all employee/jobs for a particular Group Definition. This version of the view joins to the PERSON_NAME table to return employee name columns.

HR_DRPT_7A_VW

(SQL View)

Direct Reports by GroupBuild

Used internally by the HR Direct Reports class, this view returns all employee/jobs for a particular Group Definition. This version of the view joins to the PERSON_NAME table to return employee name columns.

SELECT A.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , JOB.EFFSEQ , JOB.EMPL_STATUS , JOB.HR_STATUS , JOB.FULL_PART_TIME , JOB.SETID_DEPT , JOB.DEPTID , JOB.SETID_LOCATION , JOB.LOCATION , JOB.SETID_JOBCODE , JOB.JOBCODE , JOB.POSITION_NBR , JOB.POSITION_OVERRIDE , JOB.SUPERVISOR_ID , A.NAME , A.NAME_DISPLAY , A.LAST_NAME_SRCH , A.NAME_AC , A.LAST_NAME , A.FIRST_NAME , JOB.PER_ORG , GB.GB_GROUP_ID , GB.VERSIONGBQDM FROM PS_PERSON_NAME A , PS_JOB JOB , PS_GB_GRP_RES_TBL GB WHERE A.EMPLID = JOB.EMPLID AND GB.EMPLID = JOB.EMPLID AND GB.EMPL_RCD = JOB.EMPL_RCD AND GB.JOB_EFFDT = JOB.EFFDT AND GB.JOB_EFFSEQ = JOB_EFFSEQ AND 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 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr

3 EFFDT Date(10) DATE Effective Date
Default Value: %date

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

5 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

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

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

8 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department SetID

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

10 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location SetID

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

12 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code SetID

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

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

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

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

17 NAME Character(50) VARCHAR2(50) NOT NULL Name

18 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country

19 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name

20 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name

21 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name

22 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name

23 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

24 GB_GROUP_ID Character(15) VARCHAR2(15) NOT NULL Group Build ID.

25 VERSIONGBQDM Number(10,0) DECIMAL(10) NOT NULL Version Number

#	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 Rcd Nbr
3	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
4	EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Effective Sequence
5	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
6	HR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	HR Status A=Active I=Inactive
7	FULL_PART_TIME	Character(1)	VARCHAR2(1) NOT NULL	Full/Part Time D=On Demand F=Full-Time P=Part-Time
8	SETID_DEPT	Character(5)	VARCHAR2(5) NOT NULL	Department SetID
9	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department
10	SETID_LOCATION	Character(5)	VARCHAR2(5) NOT NULL	Location SetID
11	LOCATION	Character(10)	VARCHAR2(10) NOT NULL	Location Code
12	SETID_JOBCODE	Character(5)	VARCHAR2(5) NOT NULL	Job Code SetID
13	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code
14	POSITION_NBR	Character(8)	VARCHAR2(8) NOT NULL	Position Number
15	POSITION_OVERRIDE	Character(1)	VARCHAR2(1) NOT NULL	Override Position Data
16	SUPERVISOR_ID	Character(11)	VARCHAR2(11) NOT NULL	Supervisor ID
17	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name
18	NAME_DISPLAY	Character(50)	VARCHAR2(50) NOT NULL	Display Name - name formatted for Display based on the Country
19	LAST_NAME_SRCH	Character(30)	VARCHAR2(30) NOT NULL	Last Name
20	NAME_AC	Character(50)	VARCHAR2(50) NOT NULL	Alternate Character Name
21	LAST_NAME	Character(30)	VARCHAR2(30) NOT NULL	Last Name
22	FIRST_NAME	Character(30)	VARCHAR2(30) NOT NULL	First Name
23	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
24	GB_GROUP_ID	Character(15)	VARCHAR2(15) NOT NULL	Group Build ID.
25	VERSIONGBQDM	Number(10,0)	DECIMAL(10) NOT NULL	Version Number