HR_SRCH_CD_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.

HR_SRCH_CD_VW

(SQL View)

CD Job View

Used by the query HR_SRCH_COMPANY_DIRECTORY_DEL which deletes inactive job assignments in the Person index.

SELECT DISTINCT J.EMPLID ,J.EMPL_RCD ,J.EFFDT ,J.EFFSEQ ,J.HR_STATUS ,J.LASTUPDDTTM FROM PS_JOB J ,PSTREEDEFN T , PSTREENODE TN , PS_HRCD_JOB_TREE JT WHERE J.EFFDT=( SELECT MAX (J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID=J.EMPLID AND J1.EMPL_RCD=J.EMPL_RCD AND J1.EFFDT<=%CurrentDateIn ) AND J.EFFSEQ=( SELECT MAX(J2.EFFSEQ) FROM PS_JOB J2 WHERE J2.EMPLID=J.EMPLID AND J2.EMPL_RCD=J.EMPL_RCD AND J2.EFFDT=J.EFFDT) AND JT.EMPLID = J.EMPLID AND JT.EMPL_RCD = J.EMPL_RCD AND JT.TREE_NODE = TN.TREE_NODE AND T.SETID = TN.SETID AND T.TREE_NAME = TN.TREE_NAME AND T.EFFDT=TN.EFFDT AND T.EFFDT = ( SELECT MAX(T1.EFFDT) FROM PSTREEDEFN T1 WHERE T1.SETID=T.SETID AND T1.SETCNTRLVALUE=T.SETCNTRLVALUE AND T1.TREE_NAME=T.TREE_NAME AND T1.EFFDT <= %CurrentDateIn) AND 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') UNION ALL 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.