HCSC_SPVRJOB_VW (SQL View)	Index Back
Supervisor Tree Inquiry View This view is used to prompt for a new node when building a supervisor-based tree, and as such includes certain identifying data from the Department and Job Code tables. To accommodate the tree is node restrictions, this view has a composite key of EMPLID + EMPL_RCD

HCSC_SPVRJOB_VW

(SQL View)

Supervisor Tree Inquiry View

This view is used to prompt for a new node when building a supervisor-based tree, and as such includes certain identifying data from the Department and Job Code tables. To accommodate the tree is node restrictions, this view has a composite key of EMPLID + EMPL_RCD

SELECT W.EMPL_ID_RCD ,J.EFFDT ,J.EMPLID ,J.EMPL_RCD ,N.NAME_PSFORMAT ,%Substring(N.NAME_DISPLAY, 1, 30) ,J.DEPTID ,J.JOBCODE ,J.BUSINESS_UNIT ,J.COMPANY ,J.POSITION_NBR ,D.DESCR ,C.DESCR FROM PS_JOB J , PS_PERSON_NAME N , PS_DEPT_TBL D , PS_JOBCODE_TBL C , PS_WCS_ECM_JOB W WHERE J.EMPLID = W.EMPLID AND J.EMPL_RCD = W.EMPL_RCD AND J.EFFDT = W.EFFDT AND J.PER_ORG = 'EMP' AND J.HR_STATUS = 'A' AND N.EMPLID=J.EMPLID AND D.SETID=J.SETID_DEPT AND D.DEPTID=J.DEPTID AND D.EFFDT = ( SELECT MAX(X.EFFDT) FROM PS_DEPT_TBL X WHERE X.SETID=D.SETID AND X.DEPTID=D.DEPTID AND X.EFFDT <= %CurrentDateIn) AND C.SETID=J.SETID_JOBCODE AND C.JOBCODE=J.JOBCODE AND C.EFFDT = ( SELECT MAX(X.EFFDT) FROM PS_JOBCODE_TBL X WHERE X.SETID=C.SETID AND X.JOBCODE=C.JOBCODE AND X.EFFDT <= %CurrentDateIn)

# 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: WCS_ECM_JOB

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

3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
Prompt Table: PERSON

4 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
Prompt Table: PER_ORG_ASGN

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

6 DESCR Character(30) VARCHAR2(30) NOT NULL Description

7 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
Prompt Table: DEPT_TBL

8 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
Prompt Table: JOBCODE_TBL

9 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Prompt Table: BUS_UNIT_TBL_HR

10 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
Prompt Table: COMPANY_TBL

11 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
Prompt Table: POSITION_DATA

12 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description

13 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description

#	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: WCS_ECM_JOB
2	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
3	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Prompt Table: PERSON
4	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record Prompt Table: PER_ORG_ASGN
5	NAME_PSFORMAT	Character(50)	VARCHAR2(50) NOT NULL	A Person's name in the Peoplesoft Format (basically LN,FN MI
6	DESCR	Character(30)	VARCHAR2(30) NOT NULL	Description
7	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department Prompt Table: DEPT_TBL
8	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code Prompt Table: JOBCODE_TBL
9	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Prompt Table: BUS_UNIT_TBL_HR
10	COMPANY	Character(3)	VARCHAR2(3) NOT NULL	Company Prompt Table: COMPANY_TBL
11	POSITION_NBR	Character(8)	VARCHAR2(8) NOT NULL	Position Number Prompt Table: POSITION_DATA
12	DEPT_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Department Description
13	JOBCODE_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Job Code Description