JOB_JOBCD_VW

(SQL View)
 Index Back

Job - Jobcode view

This view joins JOB and JOBCODE_TBL for some job code attributes.

SELECT JOB.EMPLID ,JOB.EMPL_RCD ,JOB.EFFDT ,JOB.EFFSEQ ,JOB.SETID_JOBCODE ,JOB.JOBCODE ,JC.JOB_FAMILY ,JC.JOB_FUNCTION ,JC.POSN_MGMT_INDC ,JC.MANAGER_LEVEL FROM PS_JOB JOB ,PS_JOBCODE_TBL JC WHERE JC.JOBCODE = JOB.JOBCODE AND JC.SETID = JOB.SETID_JOBCODE AND JC.EFF_STATUS = 'A' AND JC.EFFDT = ( SELECT MAX(JC1.EFFDT) FROM PS_JOBCODE_TBL JC1 WHERE JC1.JOBCODE = JC.JOBCODE AND JC1.SETID = JC.SETID AND JC1.EFFDT <= JOB.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
4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
5 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID

Prompt Table: SET_VLD_HR_02

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

Prompt Table: JOBCODE_TBL

7 JOB_FAMILY Character(6) VARCHAR2(6) NOT NULL Job Family

Prompt Table: JOB_FAMILY_TBL

8 JOB_FUNCTION Character(3) VARCHAR2(3) NOT NULL Job Function Code

Prompt Table: JOBFUNCTION_TBL

9 POSN_MGMT_INDC Character(1) VARCHAR2(1) NOT NULL Used by Position Management

Y/N Table Edit

10 MANAGER_LEVEL Character(2) VARCHAR2(2) NOT NULL Manager Level
0=Chief Operating Officer
1=Director
2=Senior Officer
3=Vice President
4=Senior Manager
5=Mid-Level Manager
6=First-Line Manager
7=Supervisor
8=All Other Positions
9=Non-Manager

Default Value: 8