SELECT DISTINCT B.LAST_NAME, B.FIRST_NAME, B.MIDDLE_NAME, A.EMPLID, C.UNT_TAKEN_FA, E.ACAD_PROG, F.ACAD_PLAN, G.DESCR, C.CUM_GPA, B.EMAIL_ADDR,G.INSTITUTION,G.ACAD_PLAN,TO_CHAR(G.EFFDT,'YYYY-MM-DD')
FROM PS_STDNT_GRPS_HIST A, PS_CU_BIO_VW B, PS_STDNT_CAR_TERM C, PS_ACAD_PROG E, PS_ACAD_PLAN F, PS_ACAD_PLAN_TBL G
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.EMPLID = C.EMPLID
AND A.INSTITUTION = C.INSTITUTION
AND C.STRM = :3
AND C.EMPLID = E.EMPLID
AND C.ACAD_CAREER = E.ACAD_CAREER
AND C.INSTITUTION = E.INSTITUTION
AND E.STDNT_CAR_NBR = C.STDNT_CAR_NBR
AND E.EFFDT =
(SELECT MAX(E_ED.EFFDT) FROM PS_ACAD_PROG E_ED
WHERE E.EMPLID = E_ED.EMPLID
AND E.ACAD_CAREER = E_ED.ACAD_CAREER
AND E.STDNT_CAR_NBR = E_ED.STDNT_CAR_NBR
AND E_ED.EFFDT <= SYSDATE)
AND E.EFFSEQ =
(SELECT MAX(E_ES.EFFSEQ) FROM PS_ACAD_PROG E_ES
WHERE E.EMPLID = E_ES.EMPLID
AND E.ACAD_CAREER = E_ES.ACAD_CAREER
AND E.STDNT_CAR_NBR = E_ES.STDNT_CAR_NBR
AND E.EFFDT = E_ES.EFFDT)
AND E.EMPLID = F.EMPLID
AND E.ACAD_CAREER = F.ACAD_CAREER
AND E.STDNT_CAR_NBR = F.STDNT_CAR_NBR
AND E.EFFSEQ = F.EFFSEQ
AND E.EFFDT = F.EFFDT
AND F.ACAD_PLAN = G.ACAD_PLAN
AND G.EFFDT =
(SELECT MAX(G_ED.EFFDT) FROM PS_ACAD_PLAN_TBL G_ED
WHERE G.INSTITUTION = G_ED.INSTITUTION
AND G.ACAD_PLAN = G_ED.ACAD_PLAN
AND G_ED.EFFDT <= SYSDATE)
AND E.INSTITUTION = G.INSTITUTION )
ORDER BY 1, 2, 3
  • No labels