LM_SRCH_DTTMV5

(SQL View)
 Index Back

Last update for Catalog Items

Join all tables to get the last update date stamp for all the catalog items only. These catalog items do not have any associated classes.

SELECT DM.LM_CI_ID , DM.LM_ROW_MAINT_DTTM FROM PS_LM_DMTHD_TBL DM WHERE %CurrentDateIn BETWEEN DM.EFFDT AND DM.LM_END_EFFDT AND NOT EXISTS ( SELECT 1 FROM PS_LM_ACT X WHERE X.LM_CI_ID = DM.LM_CI_ID) UNION SELECT CI.LM_CI_ID , CI.LM_ROW_MAINT_DTTM FROM PS_LM_CI_TBL CI WHERE %CurrentDateIn BETWEEN CI.EFFDT AND CI.LM_END_EFFDT AND NOT EXISTS ( SELECT 1 FROM PS_LM_ACT X WHERE X.LM_CI_ID = CI.LM_CI_ID) UNION SELECT DM.LM_CI_ID , DT.LM_ROW_MAINT_DTTM FROM PS_LM_DMTHD_TBL DM , PS_LM_DMTH_TYP_TBL DT WHERE %CurrentDateIn BETWEEN DM.EFFDT AND DM.LM_END_EFFDT AND DM.LM_DMTHD_TYPE_ID = DT.LM_DMTHD_TYPE_ID AND NOT EXISTS ( SELECT 1 FROM PS_LM_ACT X WHERE X.LM_CI_ID = DM.LM_CI_ID) UNION SELECT CTB.LM_CI_ID , CTB.LM_ROW_MAINT_DTTM FROM PS_LM_CI_TBL CTB WHERE %Sql(LM_ELIG_NOT_APPL_SQL,CTB) UNION SELECT ACT.LM_CI_ID , ACT.LM_ROW_MAINT_DTTM FROM PS_LM_ACT ACT WHERE LM_ACT_STATUS <> '1'

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