SELECT G.LAST_NAME, G.FIRST_NAME, G.MIDDLE_NAME, D.EMPLID, E.ACAD_PROG, D.CUM_GPA, E.ADMIT_TERM, D.CUM_RESIDENT_TERMS, G.PHONE, G.EMAIL_ADDR
FROM PS_TERM_TBL A, PS_TERM_TBL B, PS_STDNT_CAR_TERM D, PS_ACAD_PROG E, PS_CU_BIO_VW G, PS_STDNT_GRPS_HIST F
WHERE A.INSTITUTION = :1
AND A.ACAD_CAREER = :2
AND A.STRM = :3
AND A.INSTITUTION = B.INSTITUTION
AND A.ACAD_CAREER = B.ACAD_CAREER
AND B.STRM = (SELECT max ( C.STRM)
FROM PS_TERM_TBL C
WHERE C.INSTITUTION = B.INSTITUTION
AND C.ACAD_CAREER = B.ACAD_CAREER
AND C.STRM < A.STRM
AND substr ( C.STRM, 4, 1) <> '6')
AND A.INSTITUTION = D.INSTITUTION
AND A.ACAD_CAREER = D.ACAD_CAREER
AND A.STRM = D.STRM
AND D.EMPLID = E.EMPLID
AND D.ACAD_CAREER = E.ACAD_CAREER
AND D.INSTITUTION = E.INSTITUTION
AND E.STDNT_CAR_NBR = D.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 <= A.TERM_BEGIN_DT)
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.ACAD_PROG = 'UGRD'
AND E.PROG_STATUS = 'AC'
AND E.ADMIT_TERM <= B.STRM
AND D.EMPLID = G.EMPLID
AND ( EXISTS (SELECT H.EMPLID
FROM PS_STDNT_CAR_TERM H
WHERE H.EMPLID = E.EMPLID
AND H.ACAD_CAREER = E.ACAD_CAREER
AND H.STDNT_CAR_NBR = E.STDNT_CAR_NBR
AND H.INSTITUTION = E.INSTITUTION
AND H.STRM = B.STRM
AND H.UNT_TAKEN_GPA = 0
AND H.UNT_TAKEN_PRGRSS = 0)
OR NOT EXISTS (SELECT I.EMPLID
FROM PS_STDNT_CAR_TERM I
WHERE I.EMPLID = E.EMPLID
AND I.ACAD_CAREER = E.ACAD_CAREER
AND I.INSTITUTION = E.INSTITUTION
AND I.STDNT_CAR_NBR = E.STDNT_CAR_NBR
AND I.STRM = B.STRM))
AND ( D.UNT_TAKEN_PRGRSS > 0
OR D.UNT_TAKEN_GPA > 0)
AND E.EMPLID = F.EMPLID
AND F.INSTITUTION = E.INSTITUTION
AND F.EFFDT =
(SELECT MAX(F_ED.EFFDT) FROM PS_STDNT_GRPS_HIST F_ED
WHERE F.EMPLID = F_ED.EMPLID
AND F.INSTITUTION = F_ED.INSTITUTION
AND F.STDNT_GROUP = F_ED.STDNT_GROUP
AND F_ED.EFFDT <= A.TERM_BEGIN_DT)
AND F.EFF_STATUS = 'A'
AND F.STDNT_GROUP = :4
ORDER BY 1, 2, 3
  • No labels