SELECT A.SRVC_IND_CD, A.DESCR, A.POS_SRVC_INDICATOR, B.SERVICE_IMPACT, A.DESCRSHORT, C.DESCR, C.DESCRSHORT,A.INSTITUTION,TO_CHAR(A.EFFDT,'YYYY-MM-DD'),C.INSTITUTION,C.SERVICE_IMPACT,TO_CHAR(C.EFFDT,'YYYY-MM-DD') FROM PS_SRVC_IND_CD_TBL A, PS_SERVICE_IMPACT B, PS_SRVC_IMPACT_TBL C WHERE A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_SRVC_IND_CD_TBL A_ED WHERE A.INSTITUTION = A_ED.INSTITUTION AND A.SRVC_IND_CD = A_ED.SRVC_IND_CD AND A_ED.EFFDT <= SYSDATE) AND A.INSTITUTION = B.INSTITUTION AND A.SRVC_IND_CD = B.SRVC_IND_CD AND A.EFFDT = B.EFFDT AND B.INSTITUTION = C.INSTITUTION AND B.SERVICE_IMPACT = C.SERVICE_IMPACT AND C.EFFDT = (SELECT MAX(C_ED.EFFDT) FROM PS_SRVC_IMPACT_TBL C_ED WHERE C.INSTITUTION = C_ED.INSTITUTION AND C.SERVICE_IMPACT = C_ED.SERVICE_IMPACT AND C_ED.EFFDT <= SYSDATE) AND A.INSTITUTION = :1 |