HR_SRCH_HRCD_VW

(SQL View)
 Index Back

Gbl Srch Redirect-Co.Directory


SELECT JT.EMPLID , JT.EMPL_RCD , T.TREE_NAME , T.EFFDT , P.PORTAL_NAME , MIN(JT.TREE_NODE) FROM PSTREEDEFN T , PSTREENODE TN , PS_HRCD_JOB_TREE JT , PSPRDMDEFN P , PSPRSMDEFN P1 , PSPRSMSYSATTRVL A WHERE JT.TREE_NODE = TN.TREE_NODE AND T.TREE_NAME = TN.TREE_NAME AND T.EFFDT = TN.EFFDT AND T.TREE_STRCT_ID = 'COMPANY_DIRECTORY' AND T.EFF_STATUS = 'A' AND P.PORTAL_TYPE = ' ' AND P.PORTAL_ISFOLDERNAV = 1 AND P.PORTAL_NAME = P1.PORTAL_NAME AND P1.PORTAL_OBJNAME = 'HC_COMPANY_DIRECTORY' AND A.PORTAL_NAME = P.PORTAL_NAME AND A.PORTAL_OBJNAME = P1.PORTAL_OBJNAME AND A.PORTAL_ATTR_NAM = 'PORTAL_TREE_NAME' AND T.TREE_NAME = %TrimSubstr(%Sql(FUNCLIB_HR_CONVERT_TO_STRING,A.PORTAL_ATTR_VAL,18) ,1,18) AND ( T.EFFDT = (( SELECT %DateIn(%TrimSubstr(%Sql(FUNCLIB_HR_CONVERT_TO_STRING,X.PORTAL_ATTR_VAL,10),1,10)) FROM PSPRSMSYSATTRVL X WHERE X.PORTAL_NAME = P.PORTAL_NAME AND X.PORTAL_OBJNAME = P1.PORTAL_OBJNAME AND X.PORTAL_ATTR_NAM = 'PORTAL_TREE_EFF_DATE')) OR T.EFFDT = ( SELECT MAX(T1.EFFDT) FROM PSTREEDEFN T1 WHERE T1.TREE_NAME=T.TREE_NAME AND T1.EFFDT <= %CurrentDateIn) ) GROUP BY JT.EMPLID, JT.EMPL_RCD, T.TREE_NAME, T.EFFDT, P.PORTAL_NAME

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

Prompt Table: PERS_SRCH_GBL

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
3 TREE_NAME Character(18) VARCHAR2(18) NOT NULL Tree Name
4 TREE_EFFDT Date(10) DATE Effective date of the Tree
5 PORTAL_NAME Character(30) VARCHAR2(30) NOT NULL Portal Name (see PSPRDMDEFN).
6 TREE_NODE Character(20) VARCHAR2(20) NOT NULL Node name