SELECT DISTINCT C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, B.EMPLID, D.ADMIT_TERM, D.EXP_GRAD_TERM, to_char ( G.UNT_TAKEN_FA, '999.9999'), to_char ( G.CUM_GPA, '99.9999'), D.PROG_STATUS, TO_CHAR(D.EFFDT,'YYYY-MM-DD'), D.ACAD_PROG, E.ACAD_PLAN, F.EMAIL_ADDR, ' ', ' ', ' ', ' ', ' '
FROM PS_TERM_TBL A, PS_STDNT_GRPS_HIST B, PS_CU_BIO_VW C, PS_ACAD_PROG D, PS_ACAD_PLAN E, PS_EMAIL_FERPA_VW F, PS_STDNT_CAR_TERM G
WHERE A.INSTITUTION = :1
AND A.ACAD_CAREER = :2
AND A.STRM = :3
AND B.INSTITUTION = A.INSTITUTION
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_STDNT_GRPS_HIST B_ED
WHERE B.EMPLID = B_ED.EMPLID
AND B.INSTITUTION = B_ED.INSTITUTION
AND B.STDNT_GROUP = B_ED.STDNT_GROUP
AND B_ED.EFFDT <= A.TERM_BEGIN_DT)
AND B.EFF_STATUS = 'A'
AND B.STDNT_GROUP = :4
AND B.EMPLID = C.EMPLID
AND B.EMPLID = D.EMPLID
AND B.INSTITUTION = D.INSTITUTION
AND D.EFFDT =
(SELECT MAX(D_ED.EFFDT) FROM PS_ACAD_PROG D_ED
WHERE D.EMPLID = D_ED.EMPLID
AND D.ACAD_CAREER = D_ED.ACAD_CAREER
AND D.STDNT_CAR_NBR = D_ED.STDNT_CAR_NBR
AND D_ED.EFFDT <= A.TERM_BEGIN_DT)
AND D.EFFSEQ =
(SELECT MAX(D_ES.EFFSEQ) FROM PS_ACAD_PROG D_ES
WHERE D.EMPLID = D_ES.EMPLID
AND D.ACAD_CAREER = D_ES.ACAD_CAREER
AND D.STDNT_CAR_NBR = D_ES.STDNT_CAR_NBR
AND D.EFFDT = D_ES.EFFDT)
AND D.EMPLID = E.EMPLID
AND D.ACAD_CAREER = E.ACAD_CAREER
AND D.STDNT_CAR_NBR = E.STDNT_CAR_NBR
AND D.EFFSEQ = E.EFFSEQ
AND D.EFFDT = E.EFFDT
AND B.EMPLID = F.EMPLID (+)
AND 'CAMP' = F.E_ADDR_TYPE (+)
AND D.EMPLID = G.EMPLID
AND D.ACAD_CAREER = G.ACAD_CAREER
AND D.STDNT_CAR_NBR = G.STDNT_CAR_NBR
AND G.INSTITUTION = D.INSTITUTION
AND A.STRM = G.STRM
UNION
SELECT DISTINCT J.LAST_NAME, J.FIRST_NAME, J.MIDDLE_NAME, I.EMPLID, ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', L.SUBJECT, L.CATALOG_NBR, L.CLASS_SECTION, L.SESSION_CODE, L.CRSE_GRADE_OFF
FROM PS_TERM_TBL H, PS_STDNT_GRPS_HIST I, PS_CU_BIO_VW J, PS_ACAD_PROG K, PS_CLASS_TBL_SE_VW L
WHERE H.INSTITUTION = :1
AND H.ACAD_CAREER = :2
AND H.STRM = :3
AND H.INSTITUTION = I.INSTITUTION
AND I.EFFDT =
(SELECT MAX(I_ED.EFFDT) FROM PS_STDNT_GRPS_HIST I_ED
WHERE I.EMPLID = I_ED.EMPLID
AND I.INSTITUTION = I_ED.INSTITUTION
AND I.STDNT_GROUP = I_ED.STDNT_GROUP
AND I_ED.EFFDT <= H.TERM_BEGIN_DT)
AND I.EFF_STATUS = 'A'
AND I.STDNT_GROUP = :4
AND I.EMPLID = J.EMPLID
AND I.EMPLID = K.EMPLID
AND I.INSTITUTION = K.INSTITUTION
AND K.EFFDT =
(SELECT MAX(K_ED.EFFDT) FROM PS_ACAD_PROG K_ED
WHERE K.EMPLID = K_ED.EMPLID
AND K.ACAD_CAREER = K_ED.ACAD_CAREER
AND K.STDNT_CAR_NBR = K_ED.STDNT_CAR_NBR
AND K_ED.EFFDT <= H.TERM_BEGIN_DT)
AND K.EFFSEQ =
(SELECT MAX(K_ES.EFFSEQ) FROM PS_ACAD_PROG K_ES
WHERE K.EMPLID = K_ES.EMPLID
AND K.ACAD_CAREER = K_ES.ACAD_CAREER
AND K.STDNT_CAR_NBR = K_ES.STDNT_CAR_NBR
AND K.EFFDT = K_ES.EFFDT)
AND K.EMPLID = L.EMPLID
AND K.ACAD_CAREER = L.ACAD_CAREER
AND L.INSTITUTION = K.INSTITUTION
AND L.STRM = H.STRM
AND L.STDNT_ENRL_STATUS = 'E'
ORDER BY 1, 2, 3, 14, 15, 16
  • No labels