SELECT D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME, A.EMPLID, F.CUM_GPA, B.ADMIT_TERM, H.ADMIT_TYPE, B.ACAD_CAREER, B.ACAD_PROG, C.ACAD_PLAN, E.DESCR, D.EMAIL_ADDR,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, (SELECT TH.ADMIT_TYPE, TH.EMPLID, TH.ACAD_CAREER, TH.STDNT_CAR_NBR, TH.ADM_APPL_NBR, TH.INSTITUTION
FROM PS_ADM_APPL_DATA TH,PS_ADM_APPL_SCTY H1 WHERE TH.EMPLID = H1.EMPLID AND TH.ACAD_CAREER = H1.ACAD_CAREER AND TH.ADM_APPL_NBR = H1.ADM_APPL_NBR AND H1.OPRCLASS = 'CUHCPPBAR01' AND H1.OPRID = '11002650' ) 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.EFF_STATUS = 'A'
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 = 'AC'
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 NOT EXISTS (SELECT G.EMPLID
FROM PS_STDNT_ENRL G
WHERE ( G.EMPLID = F.EMPLID
AND G.ACAD_CAREER = F.ACAD_CAREER
AND G.INSTITUTION = F.INSTITUTION
AND G.STRM = F.STRM
AND G.STDNT_ENRL_STATUS = 'E' ))
AND B.EMPLID = H.EMPLID (+)
AND B.ACAD_CAREER = H.ACAD_CAREER (+)
AND B.STDNT_CAR_NBR = H.STDNT_CAR_NBR (+)
AND B.ADM_APPL_NBR = H.ADM_APPL_NBR (+)
AND B.INSTITUTION = H.INSTITUTION (+) )
ORDER BY 1, 2, 3
  • No labels