TL_OT_MGR_VW

(SQL View)
 Index Back

List of employees for the MGR


SELECT DISTINCT A.EMPLID , A.EMPL_RCD , B.NAME , C.EMPLID_TO , OPRJOB.JOBCODE ,D.DEPTID , D.DESCR ,J.DESCR FROM PS_TL_OT_DATA A , PS_PERSON_NAME B , PS_TL_OT_STATUS C , PS_JOB OPRJOB , PS_DEPT_TBL D , PS_JOBCODE_TBL J WHERE A.EMPLID=B.EMPLID AND A.EMPLID=C.EMPLID AND A.EMPL_RCD=C.EMPL_RCD AND A.TL_SEQ_NUM=C.TL_SEQ_NUM AND C.CURRENT_STEP > 0 AND OPRJOB.EMPLID = A.EMPLID AND OPRJOB.EMPL_RCD = A.EMPL_RCD AND OPRJOB.EFFDT= ( SELECT MAX(OPRJOB2.EFFDT) FROM PS_JOB OPRJOB2 WHERE OPRJOB.EMPLID=OPRJOB2.EMPLID AND OPRJOB.EMPL_RCD=OPRJOB2.EMPL_RCD AND OPRJOB2.EFFDT<=%CurrentDateIn) AND OPRJOB.EFFSEQ= ( SELECT MAX(OPRJOB3.EFFSEQ) FROM PS_JOB OPRJOB3 WHERE OPRJOB.EMPLID=OPRJOB3.EMPLID AND OPRJOB.EMPL_RCD=OPRJOB3.EMPL_RCD AND OPRJOB.EFFDT=OPRJOB3.EFFDT ) AND D.SETID = OPRJOB.SETID_DEPT AND D.DEPTID = OPRJOB.DEPTID AND D.EFFDT= ( SELECT MAX(D2.EFFDT) FROM PS_DEPT_TBL D2 WHERE D.DEPTID = D2.DEPTID AND D.SETID=D2.SETID AND D2.EFFDT<=%CurrentDateIn AND D2.EFF_STATUS = 'A') AND J.SETID = OPRJOB.SETID_JOBCODE AND J.JOBCODE = OPRJOB.JOBCODE AND J.EFFDT= ( SELECT MAX(J2.EFFDT) FROM PS_JOBCODE_TBL J2 WHERE J.JOBCODE = J2.JOBCODE AND J.SETID=J2.SETID AND J2.EFFDT<=%CurrentDateIn AND J2.EFF_STATUS = 'A')

# 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 NAME Character(50) VARCHAR2(50) NOT NULL Name
4 EMPLID_TO Character(11) VARCHAR2(11) NOT NULL Empl ID To
5 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
6 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
7 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
8 JOB_DESCR Character(30) VARCHAR2(30) NOT NULL Job Description