SELECT POPR.OPRID , ACT.LM_ACT_ID , CI.LANGUAGE_CD , CI.LM_CS_LONG_NM , DMTYP.LM_DM_LONG_NM FROM PS_LM_ACT ACT , PS_LM_ACT_LE LE , PS_LM_PERSON_OPRID POPR , PS_LM_PERSON_ADMIN PADM , PS_LM_DMTHD_TBL DM , PS_LM_CI_LANG CI , PS_LM_DMTH_TYP_LNG DMTYP WHERE ACT.LM_ACT_ID = LE.LM_ACT_ID AND LE.LM_LE_ID = PADM.LM_LE_ID AND PADM.LM_PERSON_ID = POPR.LM_PERSON_ID AND PADM.LM_PRIMARY = 'Y' AND ACT.LM_CI_ID = CI.LM_CI_ID AND CI.EFFDT = ( SELECT MAX(CI_ED.EFFDT) FROM PS_LM_CI_LANG CI_ED WHERE CI.LM_CI_ID = CI_ED.LM_CI_ID AND CI.LANGUAGE_CD = CI_ED.LANGUAGE_CD AND CI_ED.EFFDT <= %CurrentDateIn ) AND ACT.LM_DMTHD_TYPE_ID = DM.LM_DMTHD_TYPE_ID AND ACT.LM_CI_ID = DM.LM_CI_ID AND ACT.LM_DMTHD_ID = DM.LM_DMTHD_ID AND %CurrentDateIn BETWEEN DM.EFFDT AND DM.LM_END_EFFDT AND DMTYP.LM_DMTHD_TYPE_ID = DM.LM_DMTHD_TYPE_ID AND CI.LANGUAGE_CD = DMTYP.LANGUAGE_CD UNION SELECT POPR.OPRID , ACT.LM_ACT_ID , CI.LANGUAGE_CD , CI.LM_CS_LONG_NM , DMTYP.LM_DM_LONG_NM FROM PS_LM_ACT ACT , PS_LM_ACT_LE LE , PS_LM_PERSON_OPRID POPR , PS_LM_PERSON_ADMIN PADM , PS_LM_DMTHD_TBL DM , PS_LM_CI_LANG CI , PS_LM_DMTH_TYP_TBL DMTYP WHERE ACT.LM_ACT_ID = LE.LM_ACT_ID AND LE.LM_LE_ID = PADM.LM_LE_ID AND PADM.LM_PERSON_ID = POPR.LM_PERSON_ID AND PADM.LM_PRIMARY = 'Y' AND ACT.LM_CI_ID = CI.LM_CI_ID AND CI.EFFDT = ( SELECT MAX(CI_ED.EFFDT) FROM PS_LM_CI_LANG CI_ED WHERE CI.LM_CI_ID = CI_ED.LM_CI_ID AND CI.LANGUAGE_CD = CI_ED.LANGUAGE_CD AND CI_ED.EFFDT <= %CurrentDateIn ) AND ACT.LM_DMTHD_TYPE_ID = DM.LM_DMTHD_TYPE_ID AND ACT.LM_CI_ID = DM.LM_CI_ID AND ACT.LM_DMTHD_ID = DM.LM_DMTHD_ID AND %CurrentDateIn BETWEEN DM.EFFDT AND DM.LM_END_EFFDT AND DMTYP.LM_DMTHD_TYPE_ID = DM.LM_DMTHD_TYPE_ID AND NOT EXISTS ( SELECT 'X' FROM PS_LM_DMTH_TYP_LNG DMLANG WHERE DMLANG.LM_DMTHD_TYPE_ID = DMTYP.LM_DMTHD_TYPE_ID AND DMLANG.LANGUAGE_CD = CI.LANGUAGE_CD) UNION SELECT POPR.OPRID , ACT.LM_ACT_ID , DMTYP.LANGUAGE_CD , CI.LM_CS_LONG_NM , DMTYP.LM_DM_LONG_NM FROM PS_LM_ACT ACT , PS_LM_ACT_LE LE , PS_LM_PERSON_OPRID POPR , PS_LM_PERSON_ADMIN PADM , PS_LM_DMTHD_TBL DM , PS_LM_CI_TBL CI , PS_LM_DMTH_TYP_LNG DMTYP WHERE ACT.LM_ACT_ID = LE.LM_ACT_ID AND LE.LM_LE_ID = PADM.LM_LE_ID AND PADM.LM_PERSON_ID = POPR.LM_PERSON_ID AND PADM.LM_PRIMARY = 'Y' AND ACT.LM_CI_ID = CI.LM_CI_ID AND %CurrentDateIn BETWEEN CI.EFFDT AND CI.LM_END_EFFDT AND ACT.LM_DMTHD_TYPE_ID = DM.LM_DMTHD_TYPE_ID AND ACT.LM_CI_ID = DM.LM_CI_ID AND ACT.LM_DMTHD_ID = DM.LM_DMTHD_ID AND %CurrentDateIn BETWEEN DM.EFFDT AND DM.LM_END_EFFDT AND DMTYP.LM_DMTHD_TYPE_ID = DM.LM_DMTHD_TYPE_ID AND NOT EXISTS ( SELECT 'X' FROM PS_LM_CI_LANG CILANG WHERE CILANG.LM_CI_ID = CI.LM_CI_ID AND CILANG.LANGUAGE_CD = DMTYP.LANGUAGE_CD)
|