LM_MGR_TEAM_VW2

(SQL View)
 Index Back

Manager team member


SELECT B.OPRID , D.LM_MANAGER_ID , D.LM_PERSON_ID , E.FIRST_NAME , E.LAST_NAME ,D.LM_JOBCODE_ID , D.LM_JOB_TITLE , D.LM_HIRE_DT , E.LM_NAME_DISPLAY FROM PS_LM_PERSON_OPRID B , PS_LM_PERSON CC , PS_LM_PERSON_ATTRB D , PS_LM_PERSON_NAME E WHERE B.LM_PERSON_ID = D.LM_MANAGER_ID AND D.LM_ACTIVE='Y' AND D.LM_PERSON_ID = CC.LM_PERSON_ID AND %CurrentDateIn BETWEEN D.EFFDT AND D.LM_END_EFFDT AND D.LM_EMPL_RCD IN ( SELECT D1.LM_EMPL_RCD FROM PS_LM_PERSON_ATTRB D1 WHERE D1.LM_PERSON_ID = D.LM_PERSON_ID AND D1.EFFDT = D.EFFDT AND D1.LM_ACTIVE = 'Y') AND E.LM_DISPLAY_FLAG = 'Y' AND E.LM_PERSON_ID = CC.LM_PERSON_ID AND %CurrentDateIn BETWEEN E.EFFDT AND E.LM_END_EFFDT AND ((E.LM_NAME_TYPE = 'PRF') OR (E.LM_NAME_TYPE = 'PRI' AND NOT EXISTS ( SELECT LM_NAME_TYPE FROM PS_LM_PERSON_NAME WHERE LM_PERSON_ID = E.LM_PERSON_ID AND LM_NAME_TYPE = 'PRF' AND %CurrentDateIn BETWEEN EFFDT AND LM_END_EFFDT)))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
2 LM_MANAGER_ID Number(15,0) DECIMAL(15) NOT NULL Manager ID
3 LM_PERSON_ID Number(15,0) DECIMAL(15) NOT NULL ELM Person ID
4 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name field
5 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name Field
6 LM_JOBCODE_ID Number(15,0) DECIMAL(15) NOT NULL JobCode ID
7 LM_JOB_TITLE Character(30) VARCHAR2(30) NOT NULL Job Title
8 LM_HIRE_DT Date(10) DATE Hire Date
9 LM_NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Name Display field