LM_SRCH_DTTMV3

(SQL View)
 Index Back

Last update for Activities

Join all tables to get the last update date stamp for all the activities and related children tables.

SELECT ACT.LM_ACT_ID , ACT.LM_CI_ID , ACT.LM_ROW_MAINT_DTTM FROM PS_LM_ACT ACT UNION SELECT AC2A.LM_ACT_ID , AC2A.LM_CI_ID , V2A.LM_ROW_MAINT_DTTM FROM PS_LM_SRCH_DTTMV2 V2A , PS_LM_ACT AC2A WHERE AC2A.LM_CI_ID = V2A.LM_CI_ID UNION SELECT DISTINCT ACT.LM_ACT_ID , ACT.LM_CI_ID , FCL.LM_ROW_MAINT_DTTM FROM PS_LM_ACT ACT LEFT OUTER JOIN PS_LM_SES SES ON SES.LM_ACT_ID = ACT.LM_ACT_ID LEFT OUTER JOIN PS_LM_FCLTY_TBL FCL ON FCL.LM_FCLTY_ID = SES.LM_FCLTY_ID WHERE SES.LM_FCLTY_ID > 0 UNION SELECT ACT.LM_ACT_ID , ACT.LM_CI_ID , SES.LM_ROW_MAINT_DTTM FROM PS_LM_ACT ACT , PS_LM_SES SES WHERE ACT.LM_ACT_ID = SES.LM_ACT_ID UNION SELECT SEC.LM_ACT_ID , ACS.LM_CI_ID , SEC.LM_ROW_MAINT_DTTM FROM PS_LM_ACT_SEC SEC , PS_LM_ACT ACS WHERE ACS.LM_ACT_ID = SEC.LM_ACT_ID UNION SELECT RTG.LM_ACT_ID , RTG.LM_CI_ID , RTG.HCSC_RTG_DATE_STMP FROM PS_LM_RTG_LRN_XREF RTG WHERE RTG.HCSC_RTG_CD = 'LEARNING_RATINGS' AND RTG.LM_ACT_ID > 0 UNION SELECT ELG.LM_ACT_ID , ELG.LM_CI_ID , %CurrentDateTimeIn FROM PS_LM_SRCH_EVENTVW ELG WHERE ELG.LM_UPCOMING_DAYS > 0 AND NOT EXISTS ( SELECT 1 FROM PS_LM_SRCHLSTRUNVW SRC WHERE SRC.LM_ACT_ID = ELG.LM_ACT_ID AND SRC.LM_UPCOMING_DAYS = ELG.LM_UPCOMING_DAYS) UNION SELECT AC4A.LM_ACT_ID , AC4A.LM_CI_ID , V4A.LM_ROW_MAINT_DTTM FROM PS_LM_SRCH_DTTMV4 V4A , PS_LM_ACT AC4A WHERE AC4A.LM_CI_ID = V4A.LM_CI_ID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 LM_ACT_ID Number(10,0) DECIMAL(10) NOT NULL Activity ID
2 LM_CI_ID Number(10,0) DECIMAL(10) NOT NULL Catalog Item ID - System Generated ID Number associated with each Catalog Item
3 LM_ROW_MAINT_DTTM DateTime(26) TIMESTAMP NOT NULL Row Maintainted DateTime