LM_PG_LR_CRS_VW

(SQL View)
 Index Back

Manager team member


SELECT LRNR.LM_PERSON_ID ,LRNR.LM_ACT_ID , LRNR.LM_CI_ID FROM PS_LM_CURR_ACTD_VW LRNR WHERE lrnr.lm_enrl_dt >= ( SELECT MAX(ERL.lm_enrl_dt) FROM PS_LM_ENRLMT ERL WHERE LRNR.LM_CI_ID=ERL.LM_CI_ID AND LRNR.LM_PERSON_ID = ERL.LM_PERSON_ID) AND LRNR.LM_ENRLMT_ID=( SELECT MAX(ERL1.LM_ENRLMT_ID) FROM PS_LM_ENRLMT ERL1 WHERE LRNR.LM_CI_ID=ERL1.LM_CI_ID AND LRNR.LM_PERSON_ID = ERL1.LM_PERSON_ID AND lrnr.lm_enrl_dt=ERL1.lm_enrl_dt) AND LRNR.LM_ACT_ID<>0 AND lrnr.lm_enrl_dt >= CASE WHEN EXISTS( SELECT SUPP.lm_enrl_dt FROM PS_LM_SUPP_LRN_VW SUPP , PS_LM_ADHC_EQV_TBL EQV WHERE SUPP.LM_PERSON_ID =LRNR.LM_PERSON_ID AND LRNR.LM_CI_ID = EQV.LM_CI_ID AND SUPP.LM_LRN_ID = EQV.LM_ADHC_LRN_ID) THEN ( SELECT MAX(SUPP.lm_enrl_dt) FROM PS_LM_SUPP_LRN_VW SUPP , PS_LM_ADHC_EQV_TBL EQV WHERE SUPP.LM_PERSON_ID =LRNR.LM_PERSON_ID AND LRNR.LM_CI_ID = EQV.LM_CI_ID AND SUPP.LM_LRN_ID = EQV.LM_ADHC_LRN_ID GROUP BY SUPP.LM_PERSON_ID, EQV.LM_CI_ID) ELSE %DateIn('1900-01-01') END UNION SELECT LRNR.LM_PERSON_ID , LRNR.LM_ACT_ID ,LRNR.LM_CI_ID FROM PS_LM_LPLN_DTL_VW LRNR , PS_LM_ENRLMT ERL WHERE ERL.LM_ENRLMT_ID=LRNR.LM_ENRLMT_ID AND LRNR.LM_ACT_ID = 0 AND LRNR.LM_CI_ID > 0 AND ERL.lm_enrl_dt >= ( SELECT MAX(ERL1.lm_enrl_dt) FROM PS_LM_ENRLMT ERL1 WHERE LRNR.LM_CI_ID=ERL1.LM_CI_ID AND LRNR.LM_PERSON_ID = ERL1.LM_PERSON_ID) AND LRNR.LM_ENRLMT_ID=( SELECT MAX(ERL2.LM_ENRLMT_ID) FROM PS_LM_ENRLMT ERL2 WHERE LRNR.LM_CI_ID=ERL2.LM_CI_ID AND LRNR.LM_PERSON_ID = ERL2.LM_PERSON_ID AND ERL.lm_enrl_dt=ERL2.lm_enrl_dt) AND ( SELECT MAX(ERL3.lm_enrl_dt) FROM PS_LM_ENRLMT ERL3 WHERE LRNR.LM_CI_ID=ERL3.LM_CI_ID AND LRNR.LM_PERSON_ID = ERL3.LM_PERSON_ID) >= (CASE WHEN EXISTS( SELECT SUPP.lm_enrl_dt FROM PS_LM_SUPP_LRN_VW SUPP , PS_LM_ADHC_EQV_TBL EQV WHERE SUPP.LM_PERSON_ID =LRNR.LM_PERSON_ID AND LRNR.LM_CI_ID = EQV.LM_CI_ID AND SUPP.LM_LRN_ID = EQV.LM_ADHC_LRN_ID) THEN ( SELECT MAX(SUPP.lm_enrl_dt) FROM PS_LM_SUPP_LRN_VW SUPP , PS_LM_ADHC_EQV_TBL EQV WHERE SUPP.LM_PERSON_ID =LRNR.LM_PERSON_ID AND LRNR.LM_CI_ID = EQV.LM_CI_ID AND SUPP.LM_LRN_ID = EQV.LM_ADHC_LRN_ID GROUP BY SUPP.LM_PERSON_ID, EQV.LM_CI_ID) ELSE %DateIn('1900-01-01') END) UNION SELECT DISTINCT LRNR.LM_PERSON_ID , 0 ,CRSE.LM_CI_ID FROM PS_LM_PG_MGR_CRSE CRSE , PS_LM_PERSON LRNR WHERE NOT EXISTS ( SELECT ERL.lm_enrlmt_id FROM PS_LM_ENRLMT ERL WHERE CRSE.LM_CI_ID=ERL.LM_CI_ID AND LRNR.LM_PERSON_ID = ERL.LM_PERSON_ID) AND NOT EXISTS ( SELECT SUPP.lm_enrl_dt FROM PS_LM_SUPP_LRN_VW SUPP , PS_LM_ADHC_EQV_TBL EQV WHERE SUPP.LM_PERSON_ID =LRNR.LM_PERSON_ID AND CRSE.LM_CI_ID = EQV.LM_CI_ID AND SUPP.LM_LRN_ID = EQV.LM_ADHC_LRN_ID) AND CRSE.LM_CAT_FLG = 'Y'

# 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