SELECT A.LM_ACT_ID , A.LM_CI_ID , A.LM_ROW_MAINT_DTTM FROM PS_LM_ACT A , PS_LM_CI_TBL D WHERE ( D.LM_CI_ID = A.LM_CI_ID AND D.EFFDT = ( SELECT MAX(D_ED.EFFDT) FROM PS_LM_CI_TBL D_ED WHERE D.LM_CI_ID = D_ED.LM_CI_ID AND D_ED.EFFDT <= %CurrentDateIn) AND ( A.LM_ACT_STATUS NOT IN ('1','3','5') OR D.LM_CI_STATUS <> '10')) UNION SELECT B.LM_ACT_ID , B.LM_CI_ID , B.LM_ROW_ADDED_DTTM FROM PS_LM_ACT_DEL_TBL B UNION SELECT C.LM_ACT_ID , C.LM_CI_ID , C.LM_ROW_ADDED_DTTM FROM PS_LM_SRCH_CIDELVW C UNION SELECT E.LM_ACT_ID , E.LM_CI_ID , E.LM_ROW_MAINT_DTTM FROM PS_LM_ACT E WHERE ( NOT EXISTS ( SELECT F.LM_ACT_ID FROM PS_LM_ACT_SEC F WHERE E.LM_ACT_ID = F.LM_ACT_ID)) UNION SELECT G.LM_ACT_ID , G.LM_CI_ID , %DateTimeNull FROM PS_LM_ACT G WHERE ( G.LM_END_DT IS NOT NULL AND G.LM_END_DT < %CurrentDateIn AND G.LM_ACT_STATUS = '1') UNION SELECT H.LM_ACT_ID , H.LM_CI_ID , H.LM_ROW_MAINT_DTTM FROM PS_LM_SRCH_DTTMV7 H
|