SELECT DISTINCT B.LAST_NAME, B.FIRST_NAME, B.MIDDLE_NAME, A.EMPLID, C.UNT_TAKEN_FA, E.ACAD_PROG, F.ACAD_PLAN, G.DESCR, C.CUM_GPA, B.EMAIL_ADDR,G.INSTITUTION,G.ACAD_PLAN,TO_CHAR(G.EFFDT,'YYYY-MM-DD') FROM PS_STDNT_GRPS_HIST A, PS_CU_BIO_VW B, PS_STDNT_CAR_TERM C, PS_ACAD_PROG E, PS_ACAD_PLAN F, PS_ACAD_PLAN_TBL G 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.EFF_STATUS = 'A' AND A.EMPLID = B.EMPLID AND A.EMPLID = C.EMPLID AND A.INSTITUTION = C.INSTITUTION AND C.STRM = :3 AND C.EMPLID = E.EMPLID AND C.ACAD_CAREER = E.ACAD_CAREER AND C.INSTITUTION = E.INSTITUTION AND E.STDNT_CAR_NBR = C.STDNT_CAR_NBR AND E.EFFDT = (SELECT MAX(E_ED.EFFDT) FROM PS_ACAD_PROG E_ED WHERE E.EMPLID = E_ED.EMPLID AND E.ACAD_CAREER = E_ED.ACAD_CAREER AND E.STDNT_CAR_NBR = E_ED.STDNT_CAR_NBR AND E_ED.EFFDT <= SYSDATE) AND E.EFFSEQ = (SELECT MAX(E_ES.EFFSEQ) FROM PS_ACAD_PROG E_ES WHERE E.EMPLID = E_ES.EMPLID AND E.ACAD_CAREER = E_ES.ACAD_CAREER AND E.STDNT_CAR_NBR = E_ES.STDNT_CAR_NBR AND E.EFFDT = E_ES.EFFDT) AND E.EMPLID = F.EMPLID AND E.ACAD_CAREER = F.ACAD_CAREER AND E.STDNT_CAR_NBR = F.STDNT_CAR_NBR AND E.EFFSEQ = F.EFFSEQ AND E.EFFDT = F.EFFDT AND F.ACAD_PLAN = G.ACAD_PLAN AND G.EFFDT = (SELECT MAX(G_ED.EFFDT) FROM PS_ACAD_PLAN_TBL G_ED WHERE G.INSTITUTION = G_ED.INSTITUTION AND G.ACAD_PLAN = G_ED.ACAD_PLAN AND G_ED.EFFDT <= SYSDATE) AND E.INSTITUTION = G.INSTITUTION ) ORDER BY 1, 2, 3 |