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
  • No labels