SELECT D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME, A.EMPLID, A.EFF_STATUS, F.CUM_GPA, C.COMPLETION_TERM, B.ACAD_CAREER, B.ACAD_PROG, C.ACAD_PLAN, E.DESCR, D.EMAIL_ADDR, H.ADDRESS1, H.ADDRESS2, H.CITY, H.STATE, H.POSTAL, TO_CHAR(SYSDATE,'YYYY-MM-DD'),E.INSTITUTION,E.ACAD_PLAN,TO_CHAR(E.EFFDT,'YYYY-MM-DD') FROM PS_STDNT_GRPS_HIST A, PS_ACAD_PROG B, PS_ACAD_PLAN C, PS_CU_BIO_VW D, PS_ACAD_PLAN_TBL E, PS_STDNT_CAR_TERM F, PS_ADDRESSES4_VW H WHERE A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_STDNT_GRPS_HIST A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.INSTITUTION = A_ED.INSTITUTION AND A.STDNT_GROUP = A_ED.STDNT_GROUP AND A_ED.EFFDT <= SYSDATE) AND A.INSTITUTION = :1 AND A.STDNT_GROUP = :2 AND A.EMPLID = B.EMPLID AND A.INSTITUTION = B.INSTITUTION AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_ACAD_PROG B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.ACAD_CAREER = B_ED.ACAD_CAREER AND B.STDNT_CAR_NBR = B_ED.STDNT_CAR_NBR AND B_ED.EFFDT <= SYSDATE) AND B.EFFSEQ = (SELECT MAX(B_ES.EFFSEQ) FROM PS_ACAD_PROG B_ES WHERE B.EMPLID = B_ES.EMPLID AND B.ACAD_CAREER = B_ES.ACAD_CAREER AND B.STDNT_CAR_NBR = B_ES.STDNT_CAR_NBR AND B.EFFDT = B_ES.EFFDT) AND B.PROG_STATUS = 'CM' AND B.EMPLID = C.EMPLID AND B.ACAD_CAREER = C.ACAD_CAREER AND B.STDNT_CAR_NBR = C.STDNT_CAR_NBR AND B.EFFSEQ = C.EFFSEQ AND B.EFFDT = C.EFFDT AND A.EMPLID = D.EMPLID AND C.ACAD_PLAN = E.ACAD_PLAN AND E.EFFDT = (SELECT MAX(E_ED.EFFDT) FROM PS_ACAD_PLAN_TBL E_ED WHERE E.INSTITUTION = E_ED.INSTITUTION AND E.ACAD_PLAN = E_ED.ACAD_PLAN AND E_ED.EFFDT <= SYSDATE) AND B.INSTITUTION = E.INSTITUTION AND B.EMPLID = F.EMPLID AND B.ACAD_CAREER = F.ACAD_CAREER AND B.STDNT_CAR_NBR = F.STDNT_CAR_NBR AND F.INSTITUTION = B.INSTITUTION AND F.STRM = :3 AND C.COMPLETION_TERM = :3 AND A.EMPLID = H.EMPLID (+) AND 'HOME' = H.ADDRESS_TYPE (+) ORDER BY 1, 2, 3 |