LM_DIF_LCHRS_VW

(SQL View)
 Index Back

DIF LC Hours

This view has all the activity detail learning component hours for DIF. If not exists, the activity published hours are used.

SELECT ER1.LM_PERSON_ID , ER1.LM_ACT_ID , SUM(DR1.LM_TOTAL_DUR_HRS) FROM PS_LM_ENR_LC_DUR DR1 , PS_LM_ENR_LC_TBL LC1 , PS_LM_ENRLMT ER1 WHERE 1 = 1 AND ER1.LM_ACT_DIF_FLG = 'Y' AND ER1.LM_STTS IN ('WTLT', 'INPO', 'ENRL') AND ER1.LM_ENRLMT_ID = LC1.LM_ENRLMT_ID AND LC1.LM_HRS_TRACK_STTS = '2' AND LC1.LM_ENRLMT_ID = DR1.LM_ENRLMT_ID AND LC1.LM_LC_ID = DR1.LM_LC_ID AND DR1.LM_HOUR_TYPE IN ( SELECT HT1.LM_HOUR_TYPE FROM PS_LM_HOUR_TYPE HT1 WHERE HT1.LM_DIF_HR_FLG = 'Y') GROUP BY ER1.LM_PERSON_ID, ER1.LM_ACT_ID UNION SELECT ER2.LM_PERSON_ID , ER2.LM_ACT_ID , %Round(SUM((AC2.LM_DURN_DAYS * 24) + AC2.LM_DURN_HRS + (AC2.LM_DURN_MINS/60)), 3) FROM PS_LM_ENR_LC_TBL LC2 , PS_LM_ACT AC2 , PS_LM_ENRLMT ER2 WHERE 2 = 2 AND ER2.LM_ACT_DIF_FLG = 'Y' AND ER2.LM_STTS IN ('WTLT', 'INPO', 'ENRL') AND ER2.LM_ACT_ID = AC2.LM_ACT_ID AND ER2.LM_ENRLMT_ID = LC2.LM_ENRLMT_ID AND LC2.LM_HRS_TRACK_STTS = '2' AND NOT EXISTS ( SELECT 2 FROM PS_LM_ENR_LC_DUR DR2 , PS_LM_HOUR_TYPE HT2 WHERE HT2.LM_DIF_HR_FLG = 'Y' AND HT2.LM_HOUR_TYPE = DR2.LM_HOUR_TYPE AND DR2.LM_ENRLMT_ID = ER2.LM_ENRLMT_ID) GROUP BY ER2.LM_PERSON_ID, ER2.LM_ACT_ID

# 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_DIF_PLANNED_HRS Signed Number(13,3) DECIMAL(11,3) NOT NULL Planned DIF Balance