LM_PERSON_IDLM_ACT_IDLM_CI_IDLM_ACT_CDN=No
Y=Yes
LM_PG_TM_CRS_VW(SQL View) |
Index Back |
|---|---|
Manager team member |
| SELECT A.LM_PERSON_ID , CRSE.LM_ACT_ID , CRSE.LM_CI_ID , CRSE.LM_ACT_CD , CRSE.LM_LRN_TYPE_DESC , DTL.LM_NAME_DISPLAY , ( SELECT C.LM_NAME_DISPLAY FROM PS_LM_PERSON_NAME C WHERE C.LM_PERSON_ID = A.LM_MANAGER_ID AND C.LM_DISPLAY_FLAG = 'Y' AND %CurrentDateIn BETWEEN C.EFFDT AND C.LM_END_EFFDT AND ((C.LM_NAME_TYPE = 'PRF') OR (C.LM_NAME_TYPE = 'PRI' AND NOT EXISTS ( SELECT LM_NAME_TYPE FROM PS_LM_PERSON_NAME WHERE LM_PERSON_ID = c.LM_PERSON_ID AND LM_NAME_TYPE = 'PRF' AND %CurrentDateIn BETWEEN EFFDT AND LM_END_EFFDT)))) , CRSE.OPRID , CRSE.LM_ACT_NAME , DTL.LM_ORG_DESCR , DTL.LM_JOB_TITLE , 'Y' FROM PS_LM_PG_MGR_CRSE CRSE , PS_LM_PERSON_ATTRB A , PS_LM_PERSON_OPRID B , PS_LM_PERS_DTL_VW DTL WHERE CRSE.OPRID = B.OPRID AND %CurrentDateIn BETWEEN A.EFFDT AND A.LM_END_EFFDT AND B.LM_PERSON_ID = A.LM_MANAGER_ID AND A.LM_MANAGER_ID <> 0 AND A.LM_ACTIVE = 'Y' AND CRSE.LM_CURR_USER_FLG <> 'Y' AND DTL.LM_PERSON_ID=A.LM_PERSON_ID AND CRSE.LM_CAT_FLG <>'Y' UNION ALL SELECT A.LM_PERSON_ID , CRSE.LM_ACT_ID , CRSE.LM_CI_ID , CRSE.LM_ACT_CD , CRSE.LM_LRN_TYPE_DESC , DTL.LM_NAME_DISPLAY , ( SELECT C.LM_NAME_DISPLAY FROM PS_LM_PERSON_NAME C WHERE C.LM_PERSON_ID = A.LM_MANAGER_ID AND C.LM_DISPLAY_FLAG = 'Y' AND %CurrentDateIn BETWEEN C.EFFDT AND C.LM_END_EFFDT AND ((C.LM_NAME_TYPE = 'PRF') OR (C.LM_NAME_TYPE = 'PRI' AND NOT EXISTS ( SELECT LM_NAME_TYPE FROM PS_LM_PERSON_NAME WHERE LM_PERSON_ID = c.LM_PERSON_ID AND LM_NAME_TYPE = 'PRF' AND %CurrentDateIn BETWEEN EFFDT AND LM_END_EFFDT)))) , CRSE.OPRID , CRSE.LM_ACT_NAME , DTL.LM_ORG_DESCR , DTL.LM_JOB_TITLE , 'Y' FROM PS_LM_PG_MGR_CRSE CRSE , PS_LM_PERSON_ATTRB A , PS_LM_PERSON_OPRID B , PS_LM_PERS_DTL_VW DTL WHERE CRSE.OPRID = B.OPRID AND %CurrentDateIn BETWEEN A.EFFDT AND A.LM_END_EFFDT AND B.LM_PERSON_ID = A.LM_MANAGER_ID AND A.LM_MANAGER_ID <> 0 AND A.LM_ACTIVE = 'Y' AND CRSE.LM_CURR_USER_FLG <> 'Y' AND DTL.LM_PERSON_ID=A.LM_PERSON_ID AND CRSE.LM_CAT_FLG = 'Y' AND CRSE.LM_ACT_ID= (%Coalesce((SELECT ERL.LM_ACT_ID FROM PS_LM_ENRLMT ERL WHERE A.LM_PERSON_ID =ERL.LM_PERSON_ID AND CRSE.LM_CI_ID = ERL.LM_CI_ID AND ERL.LM_ENRL_DT=(SELECT MAX(ERL1.LM_ENRL_DT) FROM PS_LM_ENRLMT ERL1 WHERE A.LM_PERSON_ID =ERL1.LM_PERSON_ID AND CRSE.LM_CI_ID = ERL1.LM_CI_ID) AND ERL.LM_ENRLMT_ID=(SELECT MAX(ERL2.LM_ENRLMT_ID) FROM PS_LM_ENRLMT ERL2 WHERE A.LM_PERSON_ID =ERL2.LM_PERSON_ID AND CRSE.LM_CI_ID = ERL2.LM_CI_ID AND ERL.LM_ENRL_DT=ERL2.LM_ENRL_DT)) ,'0')) UNION ALL SELECT PER.LM_PERSON_ID , CRSE.LM_ACT_ID , CRSE.LM_CI_ID , CRSE.LM_ACT_CD , CRSE.LM_LRN_TYPE_DESC , MN.LM_NAME_DISPLAY , ( SELECT C.LM_NAME_DISPLAY FROM PS_LM_PERSON_NAME C WHERE C.LM_PERSON_ID = PER.LM_MANAGER_ID AND C.LM_DISPLAY_FLAG = 'Y' AND %CurrentDateIn BETWEEN C.EFFDT AND C.LM_END_EFFDT AND ((C.LM_NAME_TYPE = 'PRF') OR (C.LM_NAME_TYPE = 'PRI' AND NOT EXISTS ( SELECT LM_NAME_TYPE FROM PS_LM_PERSON_NAME WHERE LM_PERSON_ID = c.LM_PERSON_ID AND LM_NAME_TYPE = 'PRF' AND %CurrentDateIn BETWEEN EFFDT AND LM_END_EFFDT)))) , CRSE.OPRID , CRSE.LM_ACT_NAME , MN.LM_ORG_DESCR , MN.LM_JOB_TITLE , CASE WHEN CRSE.OPRID = (%Coalesce((SELECT B.OPRID FROM PS_LM_PERSON_OPRID B WHERE B.LM_PERSON_ID = PER.LM_MANAGER_ID) ,' ')) THEN 'Y' ELSE 'N' END FROM PS_LM_PG_MGR_CRSE CRSE , PS_LM_PERSON_ATTRB PER , PS_LM_PERS_DTL_VW MN WHERE %CurrentDateIn BETWEEN PER.EFFDT AND PER.LM_END_EFFDT AND PER.LM_ACTIVE = 'Y' AND CRSE.LM_CURR_USER_FLG = 'Y' AND MN.LM_PERSON_ID=PER.LM_PERSON_ID AND CRSE.LM_CAT_FLG <>'Y' AND (EXISTS( SELECT D1.LM_PERSON_ID FROM PS_LM_ACT_SEC B1 , PS_LM_GROUP_PERSON D1 WHERE CRSE.LM_ACT_ID = B1.LM_ACT_ID AND B1.LM_LRNR_GROUP_ID = D1.LM_LRNR_GROUP_ID AND D1.LM_PERSON_ID = PER.LM_PERSON_ID AND CRSE.LM_ACT_ID<>0 AND CRSE.LM_CI_ID<>0) OR EXISTS( SELECT D1.LM_PERSON_ID FROM PS_LM_PRG_SEC B1 , PS_LM_GROUP_PERSON D1 WHERE CRSE.LM_ACT_ID = B1.LM_PRG_ID AND B1.LM_LRNR_GROUP_ID = D1.LM_LRNR_GROUP_ID AND D1.LM_PERSON_ID = PER.LM_PERSON_ID AND %CurrentDateIn BETWEEN B1.EFFDT AND B1.LM_END_EFFDT AND CRSE.LM_ACT_ID<>0 AND CRSE.LM_CI_ID=0)) UNION ALL SELECT PER.LM_PERSON_ID , CRSE.LM_ACT_ID , CRSE.LM_CI_ID , CRSE.LM_ACT_CD , CRSE.LM_LRN_TYPE_DESC , MN.LM_NAME_DISPLAY , ( SELECT C.LM_NAME_DISPLAY FROM PS_LM_PERSON_NAME C WHERE C.LM_PERSON_ID = PER.LM_MANAGER_ID AND C.LM_DISPLAY_FLAG = 'Y' AND %CurrentDateIn BETWEEN C.EFFDT AND C.LM_END_EFFDT AND ((C.LM_NAME_TYPE = 'PRF') OR (C.LM_NAME_TYPE = 'PRI' AND NOT EXISTS ( SELECT LM_NAME_TYPE FROM PS_LM_PERSON_NAME WHERE LM_PERSON_ID = c.LM_PERSON_ID AND LM_NAME_TYPE = 'PRF' AND %CurrentDateIn BETWEEN EFFDT AND LM_END_EFFDT)))) , CRSE.OPRID , CRSE.LM_ACT_NAME , MN.LM_ORG_DESCR , MN.LM_JOB_TITLE , 'Y' FROM PS_LM_PG_MGR_CRSE CRSE , PS_LM_PERSON_ATTRB PER , PS_LM_PERS_DTL_VW MN WHERE %CurrentDateIn BETWEEN PER.EFFDT AND PER.LM_END_EFFDT AND PER.LM_ACTIVE = 'Y' AND CRSE.LM_CURR_USER_FLG = 'Y' AND MN.LM_PERSON_ID=PER.LM_PERSON_ID AND CRSE.LM_CAT_FLG = 'Y' AND CRSE.LM_ACT_ID= (%Coalesce((SELECT ERL.LM_ACT_ID FROM PS_LM_ENRLMT ERL WHERE PER.LM_PERSON_ID =ERL.LM_PERSON_ID AND CRSE.LM_CI_ID = ERL.LM_CI_ID AND ERL.LM_ENRL_DT=(SELECT MAX(ERL1.LM_ENRL_DT) FROM PS_LM_ENRLMT ERL1 WHERE PER.LM_PERSON_ID =ERL1.LM_PERSON_ID AND CRSE.LM_CI_ID = ERL1.LM_CI_ID) AND ERL.LM_ENRLMT_ID=(SELECT MAX(ERL2.LM_ENRLMT_ID) FROM PS_LM_ENRLMT ERL2 WHERE PER.LM_PERSON_ID =ERL2.LM_PERSON_ID AND CRSE.LM_CI_ID = ERL2.LM_CI_ID AND ERL.LM_ENRL_DT=ERL2.LM_ENRL_DT)) ,'0')) AND (EXISTS( SELECT D1.LM_PERSON_ID FROM PS_LM_ACT_SEC B1 , PS_LM_GROUP_PERSON D1 WHERE CRSE.LM_ACT_ID = B1.LM_ACT_ID AND B1.LM_LRNR_GROUP_ID = D1.LM_LRNR_GROUP_ID AND D1.LM_PERSON_ID = PER.LM_PERSON_ID AND CRSE.LM_ACT_ID<>0 AND CRSE.LM_CI_ID<>0) OR EXISTS( SELECT D1.LM_PERSON_ID FROM PS_LM_CI_SEC_TBL B1 , PS_LM_GROUP_PERSON D1 WHERE CRSE.LM_CI_ID = B1.LM_CI_ID AND B1.LM_LRNR_GROUP_ID = D1.LM_LRNR_GROUP_ID AND D1.LM_PERSON_ID = PER.LM_PERSON_ID AND CRSE.LM_ACT_ID=0 AND CRSE.LM_CI_ID<>0)) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | LM_PERSON_ID |
Number(15,0) | DECIMAL(15) NOT NULL | ELM Person ID |
| 2 | LM_ACT_ID |
Number(10,0) | DECIMAL(10) NOT NULL | Activity ID |
| 3 | LM_CI_ID |
Number(10,0) | DECIMAL(10) NOT NULL | Catalog Item ID - System Generated ID Number associated with each Catalog Item |
| 4 | LM_ACT_CD |
Character(30) | VARCHAR2(30) NOT NULL | Activity Code |
| 5 | LM_LRN_TYPE_DESC | Character(15) | VARCHAR2(15) NOT NULL | Type |
| 6 | LM_NAME_DISPLAY | Character(50) | VARCHAR2(50) NOT NULL | Name Display field |
| 7 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 8 | OPRID | Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 9 | LM_ACT_NAME | Character(254) | VARCHAR2(254) NOT NULL | Activity Name |
| 10 | LM_ORG_DESCR | Character(50) | VARCHAR2(50) NOT NULL | Customer Description |
| 11 | LM_JOB_TITLE | Character(30) | VARCHAR2(30) NOT NULL | Job Title |
| 12 | LM_TEAM_FLG | Character(1) | VARCHAR2(1) NOT NULL |
Team
N=No Y=Yes |