HR_DRPT_7SA_VW

(SQL View)
Index Back

Supervisor for GroupBuild

Used internally by the HR Direct Reports class, this view returns information about the employee that is designated as the "Manager" of a specified Group Build GroupID. 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.EFFDT , GB.APPROVER_ID FROM PS_PERSON_NAME A , PS_JOB JOB , PS_GB_GROUP_TBL GB WHERE A.EMPLID = JOB.EMPLID AND JOB.EMPLID = GB.MANAGER_ID 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 Record
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 Set ID
9 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
10 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID
11 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
12 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID
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 GB_EFFDT Date(10) DATE Group As Of Date
26 APPROVER_ID Character(11) VARCHAR2(11) NOT NULL Approver Empl ID