SELECT E.LAST_NAME, E.FIRST_NAME, E.MIDDLE_NAME, A.EMPLID, A.ACAD_CAREER, A.ACAD_PROG, G.ACAD_PLAN, H.ACAD_PLAN, A.ADMIT_TERM, B.STRM, B.CUM_GPA, B.TOT_TAKEN_GPA, B.TOT_CUMULATIVE, E.PHONE, F.EMAIL_ADDR
FROM PS_ACAD_PROG A, PS_STDNT_CAR_TERM B, PS_CU_BIO_VW E, PS_EMAIL_FERPA_VW F, PS_ACAD_PLAN G, PS_ACAD_PLAN H
WHERE A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.ACAD_CAREER = A_ED.ACAD_CAREER
AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
AND A_ED.EFFDT <= SYSDATE)
AND A.EFFSEQ =
(SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.ACAD_CAREER = A_ES.ACAD_CAREER
AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
AND A.EFFDT = A_ES.EFFDT)
AND A.INSTITUTION = :1
AND A.ACAD_CAREER = 'UGRD'
AND A.ACAD_PROG = 'UGRD'
AND A.PROG_STATUS = 'AC'
AND A.ADMIT_TERM BETWEEN :2 AND :3
AND A.EMPLID = B.EMPLID
AND A.ACAD_CAREER = B.ACAD_CAREER
AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR
AND B.INSTITUTION = A.INSTITUTION
AND B.STRM = (SELECT max ( C.STRM)
FROM PS_STDNT_CAR_TERM C
WHERE C.EMPLID = A.EMPLID
AND C.ACAD_CAREER = B.ACAD_CAREER
AND C.INSTITUTION = B.INSTITUTION
AND C.STDNT_CAR_NBR = B.STDNT_CAR_NBR
AND C.STRM < :2)
AND B.CUM_GPA < 2.0
AND NOT EXISTS (SELECT D.EMPLID
FROM PS_STDNT_GRPS_HIST D
WHERE D.EFFDT =
(SELECT MAX(D_ED.EFFDT) FROM PS_STDNT_GRPS_HIST D_ED
WHERE D.EMPLID = D_ED.EMPLID
AND D.INSTITUTION = D_ED.INSTITUTION
AND D.STDNT_GROUP = D_ED.STDNT_GROUP
AND D_ED.EFFDT <= SYSDATE)
AND D.EMPLID = A.EMPLID
AND D.INSTITUTION = A.INSTITUTION
AND D.EFF_STATUS = 'A'
AND D.STDNT_GROUP IN ('BSCH','CPIN','MHC','SEEK'))
AND A.EMPLID = E.EMPLID
AND A.EMPLID = F.EMPLID (+)
AND 'CAMP' = F.E_ADDR_TYPE (+)
AND A.EMPLID = G.EMPLID
AND A.ACAD_CAREER = G.ACAD_CAREER
AND A.STDNT_CAR_NBR = G.STDNT_CAR_NBR
AND A.EFFSEQ = G.EFFSEQ
AND A.EFFDT = G.EFFDT
AND G.PLAN_SEQUENCE = 10
AND A.EMPLID = H.EMPLID (+)
AND A.ACAD_CAREER = H.ACAD_CAREER (+)
AND A.STDNT_CAR_NBR = H.STDNT_CAR_NBR (+)
AND A.EFFSEQ = H.EFFSEQ (+)
AND A.EFFDT = H.EFFDT (+)
AND 20 = H.PLAN_SEQUENCE (+)
AND B.TOT_TAKEN_GPA > 0
ORDER BY 1, 2, 3
  • No labels