SELECT DISTINCT C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, B.EMPLID, D.SRVC_IND_CD, D.SRVC_IND_REASON FROM PS_TERM_TBL A, PS_STDNT_GRPS_HIST B, PS_CU_BIO_VW C, PS_SRVC_IND_DATA D WHERE A.INSTITUTION = :1 AND A.ACAD_CAREER = :2 AND A.STRM = :3 AND A.INSTITUTION = B.INSTITUTION AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_STDNT_GRPS_HIST B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.INSTITUTION = B_ED.INSTITUTION AND B.STDNT_GROUP = B_ED.STDNT_GROUP AND B_ED.EFFDT <= A.TERM_BEGIN_DT) AND B.EFF_STATUS = 'A' AND B.STDNT_GROUP = :4 AND B.EMPLID = C.EMPLID AND B.EMPLID = D.EMPLID (+) AND B.INSTITUTION = D.INSTITUTION (+) ORDER BY 1, 2, 3, 5, 6 |