SELECT A.SRVC_IND_CD, A.SRVC_IND_REASON, B.LAST_NAME, B.FIRST_NAME, B.MIDDLE_NAME, A.EMPLID, A.POS_SRVC_INDICATOR, C.ACAD_PROG, C.PROG_STATUS, D.ACAD_PLAN, B.PHONE, B.EMAIL_ADDR, TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM PS_SRVC_IND_DATA A, PS_CU_BIO_VW B, PS_ACAD_PROG C, PS_ACAD_PLAN D WHERE ( A.INSTITUTION = :1 AND A.SRVC_IND_CD = :2 AND A.EMPLID = B.EMPLID AND A.EMPLID = C.EMPLID AND C.EFFDT = (SELECT MAX(C_ED.EFFDT) FROM PS_ACAD_PROG C_ED WHERE C.EMPLID = C_ED.EMPLID AND C.ACAD_CAREER = C_ED.ACAD_CAREER AND C.STDNT_CAR_NBR = C_ED.STDNT_CAR_NBR AND C_ED.EFFDT <= SYSDATE) AND C.EFFSEQ = (SELECT MAX(C_ES.EFFSEQ) FROM PS_ACAD_PROG C_ES WHERE C.EMPLID = C_ES.EMPLID AND C.ACAD_CAREER = C_ES.ACAD_CAREER AND C.STDNT_CAR_NBR = C_ES.STDNT_CAR_NBR AND C.EFFDT = C_ES.EFFDT) AND A.INSTITUTION = C.INSTITUTION AND C.EMPLID = D.EMPLID AND C.ACAD_CAREER = D.ACAD_CAREER AND C.STDNT_CAR_NBR = D.STDNT_CAR_NBR AND C.EFFSEQ = D.EFFSEQ AND C.EFFDT = D.EFFDT AND D.ACAD_PLAN NOT LIKE '%-MIN' ) ORDER BY 1, 2, 3, 4, 5 | |||||||||||||||||||||