HRCD_JBTRPRM_VW

(SQL View)

SELECT DISTINCT T.TREE_NODE , T.EMPLID , T.EMPL_RCD , J.EFFDT , %Substring(N.NAME_DISPLAY, 1, 30) , J.HR_STATUS FROM PS_HRCD_JOB_TREE T , PS_PERSON_NAME N , PS_JOB J WHERE N.EMPLID = T.EMPLID AND J.EMPLID = T.EMPLID AND J.EMPL_RCD = T.EMPL_RCD AND (J.EFFDT = ( SELECT MAX (C.EFFDT) FROM PS_JOB C WHERE C.EMPLID = J.EMPLID AND C.EMPL_RCD = J.EMPL_RCD AND C.EFFDT <= %CurrentDateIn) OR J.EFFDT > %CurrentDateIn) 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 TREE_NODE Character(20) VARCHAR2(20) NOT NULL Node name

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

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

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

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

6 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
A=Active
I=Inactive

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	TREE_NODE	Character(20)	VARCHAR2(20) NOT NULL	Node name
2	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Prompt Table: PERSON
3	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record Prompt Table: PER_ORG_ASGN
4	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
5	DESCR	Character(30)	VARCHAR2(30) NOT NULL	Description
6	HR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	HR Status A=Active I=Inactive