SELECT A.INSTITUTION, A.SRVC_IND_CD, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.EFF_STATUS, A.DESCR, A.DESCRSHORT, A.POS_SRVC_INDICATOR, A.SCC_HOLD_DISPLAY, A.SCC_SI_PERS, A.SCC_SI_ORG, A.SCC_DFLT_ACTDATE, A.SCC_DFLT_ACTTERM, A.DFLT_SRVC_IND_RSN, A.SRV_IND_DCSD_FLAG, B.SRVC_IND_REASON, B.DESCR, B.DESCRSHORT, B.SRVC_IN_REF_TYPE, B.DEPTID, B.POSITION_NBR, B.MULTPLE_OCCUR, B.DESCRLONG,B.INSTITUTION,B.SRVC_IND_CD,TO_CHAR(B.EFFDT,'YYYY-MM-DD') FROM PS_SRVC_IND_CD_TBL A, PS_SRVC_IN_RSN_TBL B 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 = :1 AND A.INSTITUTION = B.INSTITUTION AND A.SRVC_IND_CD = B.SRVC_IND_CD AND A.EFFDT = B.EFFDT |