EO_CDMGRPROF_VW

(SQL View)
 Index Back

Comp Dir Manager Detail View


SELECT C.EMPLID , C.EMPL_RCD , NM.NAME_AC , NM.NAME , B.POSITION_PHONE , B.BUSINESS_TITLE , D.DESCR , E.DESCR , C.LOCATION , C.EFFDT , C.REPORTS_TO , C.POSITION_NBR , C.COMPANY , E.MANAGER_ID FROM PS_PERSON_NAME NM , PS_PER_ORG_ASGN B , PS_JOB C , PS_JOBCODE_TBL D , PS_DEPT_TBL E WHERE B.PER_ORG = 'EMP' AND NM.EMPLID = B.EMPLID AND C.EMPLID = B.EMPLID AND C.EMPL_RCD = B.EMPL_RCD AND C.HR_STATUS = 'A' AND C.EFFDT = ( SELECT MAX(H.EFFDT) FROM PS_JOB H WHERE H.EMPLID = C.EMPLID AND H.EMPL_RCD = C.EMPL_RCD AND H.EFFDT <= %CurrentDateIn) AND C.EFFSEQ = ( SELECT MAX(I.EFFSEQ) FROM PS_JOB I WHERE I.EMPLID = C.EMPLID AND I.EMPL_RCD = C.EMPL_RCD AND I.EFFDT = C.EFFDT) AND D.JOBCODE = C.JOBCODE AND D.SETID = C.SETID_JOBCODE AND D.EFFDT = ( SELECT MAX(J.EFFDT) FROM PS_JOBCODE_TBL J WHERE J.JOBCODE = D.JOBCODE AND J.SETID = D.SETID AND J.EFFDT <= C.EFFDT) AND E.DEPTID = C.DEPTID AND E.SETID = C.SETID_DEPT AND E.EFFDT = ( SELECT MAX(K.EFFDT) FROM PS_DEPT_TBL K WHERE K.DEPTID = E.DEPTID AND K.SETID = E.SETID AND K.EFFDT <= C.EFFDT)

# 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 Rcd Nbr
3 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
4 NAME Character(50) VARCHAR2(50) NOT NULL Name
5 WORK_PHONE Character(24) VARCHAR2(24) NOT NULL Work Phone #
6 BUSINESS_TITLE Character(30) VARCHAR2(30) NOT NULL Business Title
7 JOBTITLE Character(30) VARCHAR2(30) NOT NULL Job Title
8 DEPTNAME Character(30) VARCHAR2(30) NOT NULL Department
9 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
10 EFFDT Date(10) DATE NOT NULL Effective Date

Default Value: %date

11 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number
12 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
13 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

Prompt Table: COMPANY_TBL

14 MANAGER_ID Character(11) VARCHAR2(11) NOT NULL Manager ID

Prompt Table: PERSON