HRCD_EMP_RCD_VW

(SQL View)
 Index Back

Co Directory-Tree Src View

Company Directory - Tree Source View

SELECT DISTINCT J.EMPLID , J.EMPL_RCD , N.NAME_DISPLAY , J.BUSINESS_UNIT , J.COMPANY , J.SETID_DEPT , J.DEPTID , J.SETID_JOBCODE , J.JOBCODE ,J.SETID_LOCATION ,J.LOCATION , J.POSITION_NBR FROM PSTREEDEFN TD , PSTREENODE TN , PS_HRCD_JOB_TREE T , PS_JOB J , PS_PERSON_NAME N WHERE TN.TREE_NODE = T.TREE_NODE AND TN.SETID = TD.SETID AND TN.SETCNTRLVALUE = TD.SETCNTRLVALUE AND TN.TREE_NAME = TD.TREE_NAME AND TN.EFFDT = TD.EFFDT AND T.EMPLID = N.EMPLID AND T.EMPLID = J.EMPLID AND T.EMPL_RCD = J.EMPL_RCD AND TD.EFFDT = ( SELECT MAX(TD1.EFFDT) FROM PSTREEDEFN TD1 WHERE TD.SETCNTRLVALUE = TD1.SETCNTRLVALUE AND TD.TREE_NAME = TD1.TREE_NAME) AND J.EFFDT = ( SELECT MAX(D.EFFDT) FROM PS_JOB D WHERE D.EMPLID = J.EMPLID AND D.EMPL_RCD = J.EMPL_RCD AND D.EFFDT <= TD.EFFDT) AND J.EFFSEQ = ( SELECT MAX(D.EFFSEQ) FROM PS_JOB D WHERE D.EMPLID = J.EMPLID AND D.EMPL_RCD = J.EMPL_RCD AND D.EFFDT = J.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PERSON

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
3 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
4 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: BUS_UNIT_TBL_HR

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

Prompt Table: COMPANY_TBL

6 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID

Prompt Table: SETID_TBL

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

Prompt Table: DEPT_TBL

8 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID

Prompt Table: SETID_TBL

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

Prompt Table: JOBCODE_TBL

10 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID

Prompt Table: SETID_TBL

11 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
12 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number

Prompt Table: POSITION_DATA