HRCD_TREE1_VW

(SQL View)
 Index Back

Fluid CD


SELECT TR.SETID , TR.SETCNTRLVALUE , TR.TREE_NAME , TR.EFFDT , EMP.EMPLID , EMP.EMPL_RCD , SUP.EMPLID , SUP.EMPL_RCD , TR.TREE_NODE , TR.PARENT_NODE_NAME , A.SETID_JOBCODE , A.JOBCODE , A.SETID_DEPT , A.DEPTID , A.SETID_LOCATION , A.LOCATION FROM PSTREENODE TR , PS_HRCD_JOB_TREE EMP , PS_HRCD_JOB_TREE SUP , PS_JOB A WHERE TR.TREE_NODE = EMP.TREE_NODE AND TR.PARENT_NODE_NAME = SUP.TREE_NODE AND A.EMPLID = EMP.EMPLID AND A.EMPL_RCD = EMP.EMPL_RCD AND A.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = A.EMPLID AND EMPL_RCD = A.EMPL_RCD AND EFFDT = A.EFFDT) AND A.EFFDT = ( SELECT MAX(E.EFFDT) FROM PS_JOB E WHERE E.EMPLID = A.EMPLID AND E.EMPL_RCD = A.EMPL_RCD AND E.EFFDT <= TR.EFFDT) UNION SELECT TR.SETID , TR.SETCNTRLVALUE , TR.TREE_NAME , TR.EFFDT , EMP.EMPLID , EMP.EMPL_RCD , SUP.EMPLID , SUP.EMPL_RCD , TR.TREE_NODE , TR.PARENT_NODE_NAME , ' ' , A.JOBCODE , ' ' , A.DEPTID , ' ' , A.LOCATION FROM PSTREENODE TR , PS_HRCD_JOB_TREE EMP , PS_HRCD_JOB_TREE SUP , PS_POSITION_DATA A WHERE TR.TREE_NODE = EMP.TREE_NODE AND TR.PARENT_NODE_NAME = SUP.TREE_NODE AND 'EP$' %Concat A.position_nbr = EMP.EMPLID AND A.EFFDT = ( SELECT MAX(E.EFFDT) FROM PS_POSITION_DATA E WHERE E.POSITION_NBR = A.POSITION_NBR AND E.EFFDT <= TR.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
2 SETCNTRLVALUE Character(20) VARCHAR2(20) NOT NULL Tree Definition User Key Value
3 TREE_NAME Character(18) VARCHAR2(18) NOT NULL Tree Name
4 EFFDT Date(10) DATE NOT NULL Effective Date

Default Value: %date

5 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
6 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
7 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
8 SUPERVISOR_ERN Number(3,0) SMALLINT NOT NULL This is used to hold the Employment Record Number of a supervisor.
9 TREE_NODE Character(20) VARCHAR2(20) NOT NULL Node name
10 PARENT_NODE_NAME Character(20) VARCHAR2(20) NOT NULL Name of parent node
11 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID
12 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
13 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
14 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
15 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID
16 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code