WCS_SPRVSRID_VW (SQL View)	Index Back
Supervisor ID Relationship Vw This view is used to relate employees with their Supervisors, based on the SupervisorID stored on Job. This view is used as the hierarchy source. when building a tree. Since SupervisorID only records the supervising person, and not the supervising job, the view has some built-in assumptions regarding matching a supervisor is potential multiple jobs with the correct direct reports.

WCS_SPRVSRID_VW

(SQL View)

Supervisor ID Relationship Vw

This view is used to relate employees with their Supervisors, based on the SupervisorID stored on Job. This view is used as the hierarchy source. when building a tree. Since SupervisorID only records the supervising person, and not the supervising job, the view has some built-in assumptions regarding matching a supervisor is potential multiple jobs with the correct direct reports.

SELECT J.EMPL_ID_RCD , A.EMPLID , A.EMPL_RCD , A.EFFDT , A.EFFSEQ , A.DEPTID , A.COMPANY , A.HR_STATUS , A.PER_ORG , N.NAME_PSFORMAT , S.EMPLID , S.EMPL_RCD , S.DEPTID , S.EFFDT , S.EFFSEQ FROM PS_JOB S , PS_JOB A , PS_PERSON_NAME N , PS_WCS_ECM_JOB J WHERE A.EMPLID = J.EMPLID AND A.EMPL_RCD = J.EMPL_RCD AND A.EFFDT = J.EFFDT AND A.EFFSEQ = J.EFFSEQ AND A.SUPERVISOR_ID = S.EMPLID AND A.BUSINESS_UNIT = S.BUSINESS_UNIT AND A.COMPANY = S.COMPANY AND A.PER_ORG = 'EMP' AND N.EMPLID = A.EMPLID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPL_ID_RCD Character(20) VARCHAR2(20) NOT NULL Job concatenated key.
Prompt Table: HCSC_SPVRPT_VW

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 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
Prompt Table: DEPT_TBL

7 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

8 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
A=Active
I=Inactive
Default Value: A

9 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
Default Value: PER_ORG_ASGN.PER_ORG

10 NAME_PSFORMAT Character(50) VARCHAR2(50) NOT NULL A Person's name in the Peoplesoft Format (basically LN,FN MI

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

12 SUPERVISOR_ERN Number(3,0) SMALLINT NOT NULL This is used to hold the Employment Record Number of a supervisor.

13 DEPTID2 Character(10) VARCHAR2(10) NOT NULL Department

14 EFFDT_OTH Date(10) DATE Other Effective Date

15 JOB_EFFSEQ Number(3,0) SMALLINT NOT NULL Job Effective Sequence

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPL_ID_RCD	Character(20)	VARCHAR2(20) NOT NULL	Job concatenated key. Prompt Table: HCSC_SPVRPT_VW
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	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department Prompt Table: DEPT_TBL
7	COMPANY	Character(3)	VARCHAR2(3) NOT NULL	Company
8	HR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	HR Status A=Active I=Inactive Default Value: A
9	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 Default Value: PER_ORG_ASGN.PER_ORG
10	NAME_PSFORMAT	Character(50)	VARCHAR2(50) NOT NULL	A Person's name in the Peoplesoft Format (basically LN,FN MI
11	SUPERVISOR_ID	Character(11)	VARCHAR2(11) NOT NULL	Supervisor ID
12	SUPERVISOR_ERN	Number(3,0)	SMALLINT NOT NULL	This is used to hold the Employment Record Number of a supervisor.
13	DEPTID2	Character(10)	VARCHAR2(10) NOT NULL	Department
14	EFFDT_OTH	Date(10)	DATE	Other Effective Date
15	JOB_EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Job Effective Sequence