SELECT A.STDNT_GROUP, B.LAST_NAME, B.FIRST_NAME, A.EMPLID, C.ADMIT_TERM, E.SRVC_IND_CD, E.SRVC_IND_REASON, F.SRVC_IND_CD, F.SRVC_IND_REASON, I.ACAD_PLAN, J.ACAD_PLAN, K.ACAD_PLAN, D.TOT_PASSD_GPA, D.UNT_TAKEN_GPA, D.TOT_TAKEN_GPA, D.CUR_GPA, H.CUR_GPA, D.CUM_GPA, G.EMAIL_ADDR, B.EMAIL_ADDR
FROM PS_STDNT_GRPS_HIST A, PS_CU_BIO_VW B, PS_ACAD_PROG C, PS_STDNT_CAR_TERM D, PS_SRVC_IND_DATA E, PS_SRVC_IND_DATA F, PS_EMAIL_FERPA_VW G, PS_STDNT_CAR_TERM H, PS_ACAD_PLAN I, PS_ACAD_PLAN J, PS_ACAD_PLAN K
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.EFF_STATUS = 'A'
AND A.STDNT_GROUP IN ('MHC','BSCH','PROV')
AND A.EMPLID = B.EMPLID (+)
AND A.EMPLID = C.EMPLID
AND A.INSTITUTION = C.INSTITUTION
AND C.EFFDT =
(SELECT MAX(C_ED.EFFDT) FROM PS_ACAD_PROG C_ED
WHERE C.EMPLID = C_ED.EMPLID
AND C.ACAD_CAREER = C_ED.ACAD_CAREER
AND C.STDNT_CAR_NBR = C_ED.STDNT_CAR_NBR
AND C_ED.EFFDT <= SYSDATE)
AND C.EFFSEQ =
(SELECT MAX(C_ES.EFFSEQ) FROM PS_ACAD_PROG C_ES
WHERE C.EMPLID = C_ES.EMPLID
AND C.ACAD_CAREER = C_ES.ACAD_CAREER
AND C.STDNT_CAR_NBR = C_ES.STDNT_CAR_NBR
AND C.EFFDT = C_ES.EFFDT)
AND C.EMPLID = D.EMPLID
AND C.ACAD_CAREER = D.ACAD_CAREER
AND C.STDNT_CAR_NBR = D.STDNT_CAR_NBR
AND D.INSTITUTION = C.INSTITUTION
AND D.STRM = :2
AND A.EMPLID = E.EMPLID (+)
AND A.INSTITUTION = E.INSTITUTION (+)
AND 'ACP' = E.SRVC_IND_CD (+)
AND A.EMPLID = F.EMPLID (+)
AND A.INSTITUTION = F.INSTITUTION (+)
AND 'APS' = F.SRVC_IND_CD (+)
AND A.EMPLID = G.EMPLID (+)
AND 'CAMP' = G.E_ADDR_TYPE (+)
AND C.PROG_STATUS = 'AC'
AND D.EMPLID = H.EMPLID (+)
AND D.ACAD_CAREER = H.ACAD_CAREER (+)
AND D.INSTITUTION = H.INSTITUTION (+)
AND :3 = H.STRM (+)
AND C.EMPLID = I.EMPLID (+)
AND C.ACAD_CAREER = I.ACAD_CAREER (+)
AND C.STDNT_CAR_NBR = I.STDNT_CAR_NBR (+)
AND C.EFFSEQ = I.EFFSEQ (+)
AND C.EFFDT = I.EFFDT (+)
AND 10 = I.PLAN_SEQUENCE (+)
AND C.EMPLID = J.EMPLID (+)
AND C.ACAD_CAREER = J.ACAD_CAREER (+)
AND C.STDNT_CAR_NBR = J.STDNT_CAR_NBR (+)
AND C.EFFSEQ = J.EFFSEQ (+)
AND C.EFFDT = J.EFFDT (+)
AND 20 = J.PLAN_SEQUENCE (+)
AND C.EMPLID = K.EMPLID (+)
AND C.ACAD_CAREER = K.ACAD_CAREER (+)
AND C.STDNT_CAR_NBR = K.STDNT_CAR_NBR (+)
AND C.EFFSEQ = K.EFFSEQ (+)
AND C.EFFDT = K.EFFDT (+)
AND 30 = K.PLAN_SEQUENCE (+)
ORDER BY 2, 3
  • No labels