HR_CD_TEREMP_VW (SQL View)	Index Back
CD Job View Used by the query HR_SRCH_COMPANY_DIRECTORY_DEL which deletes inactive job assignments in the Person index. This query picks up the employees deleted from Job tree

HR_CD_TEREMP_VW

(SQL View)

CD Job View

Used by the query HR_SRCH_COMPANY_DIRECTORY_DEL which deletes inactive job assignments in the Person index. This query picks up the employees deleted from Job tree

SELECT DISTINCT J.EMPLID ,J.EMPL_RCD ,J.EFFDT ,J.EFFSEQ ,J.HR_STATUS ,J.LASTUPDDTTM FROM PS_JOB J WHERE J.EFFDT > ( SELECT MAX(EFFDT) FROM PSTREENODE T WHERE T.TREE_NAME IN ( SELECT %TrimSubstr(%Sql(FUNCLIB_HR_CONVERT_TO_STRING,X.PORTAL_ATTR_VAL,18) ,1,18) FROM PSPRSMSYSATTRVL X WHERE X.PORTAL_OBJNAME='HC_COMPANY_DIRECTORY' AND X.PORTAL_ATTR_NAM='PORTAL_TREE_NAME') AND T.TREE_NODE IN( SELECT TREE_NODE FROM PS_HRCD_JOB_TREE TR WHERE TR.EMPLID=J.EMPLID AND TR.EMPL_RCD=J.EMPL_RCD))

# 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
Default Value: %date

4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence

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

6 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.

#	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 Default Value: %date
4	EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Effective Sequence
5	HR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	HR Status A=Active I=Inactive
6	LASTUPDDTTM	DateTime(26)	TIMESTAMP	Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.