Versions Compared

Old Version 1

changes.mady.by.user Tabassum Lodhi

Saved on

compared with

New Version Current

changes.mady.by.user Tabassum Lodhi

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

 
SELECT DISTINCT E.CUM_GPA, D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME, A.EMPLID, H.SEX, A.COMPLETION_TERM, A.DEGR_CHKOUT_STAT, A.EXP_GRAD_TERM, E.TOT_PASSD_PRGRSS, B.ACAD_PLAN, C.ACAD_SUB_PLAN, I.ACAD_PLAN, K.ACAD_SUB_PLAN, J.ACAD_PLAN, L.ACAD_SUB_PLAN, TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD')
FROM PS_ACAD_PROG A, PS_ACAD_PLAN B, PS_NAME_CURR_VW D, PS_STDNT_CAR_TERM E, PS_SF_STDNT_PRS_VW H, PS_ACAD_SUBPLAN C, PS_ACAD_PLAN I, PS_ACAD_PLAN J, PS_ACAD_SUBPLAN K, PS_ACAD_SUBPLAN L
WHERE A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.ACAD_CAREER = A_ED.ACAD_CAREER
AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
AND A_ED.EFFDT <= SYSDATE)
AND A.EFFSEQ =
(SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.ACAD_CAREER = A_ES.ACAD_CAREER
AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
AND A.EFFDT = A_ES.EFFDT)
AND A.EMPLID = B.EMPLID
AND A.ACAD_CAREER = B.ACAD_CAREER
AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR
AND A.EFFSEQ = B.EFFSEQ
AND A.EFFDT = B.EFFDT
AND B.PLAN_SEQUENCE = 10
AND A.INSTITUTION = :1
AND ( A.ACAD_PROG IN ('MPA','MSED','ADVCT')
OR ( A.ACAD_PROG = 'UGRD'
AND EXISTS (SELECT DISTINCT G.EMPLID
FROM PS_SRVC_IND_DATA G
WHERE G.EMPLID = A.EMPLID
AND G.INSTITUTION = A.INSTITUTION
AND G.SRVC_IND_CD = 'ACP'
AND G.SRVC_IND_REASON = 'SPAF')))
AND (( A.PROG_STATUS = 'CM'
AND A.COMPLETION_TERM BETWEEN :2 AND :3)
OR ( A.PROG_STATUS = 'AC'
AND A.EXP_GRAD_TERM BETWEEN :2 AND :3))
AND A.EMPLID = D.EMPLID
AND A.EMPLID = E.EMPLID
AND A.ACAD_CAREER = E.ACAD_CAREER
AND A.STDNT_CAR_NBR = E.STDNT_CAR_NBR
AND E.INSTITUTION = A.INSTITUTION
AND E.STRM = (SELECT MAX ( F.STRM)
FROM PS_STDNT_CAR_TERM F
WHERE F.EMPLID = E.EMPLID
AND F.ACAD_CAREER = E.ACAD_CAREER
AND F.INSTITUTION = E.INSTITUTION
AND F.STDNT_CAR_NBR = E.STDNT_CAR_NBR)
AND A.EMPLID = H.EMPLID (+)
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.ACAD_PLAN = C.ACAD_PLAN (+)
AND B.EFFDT = C.EFFDT (+)
AND A.EMPLID = I.EMPLID (+)
AND A.ACAD_CAREER = I.ACAD_CAREER (+)
AND A.STDNT_CAR_NBR = I.STDNT_CAR_NBR (+)
AND A.EFFSEQ = I.EFFSEQ (+)
AND A.EFFDT = I.EFFDT (+)
AND 20 = I.PLAN_SEQUENCE (+)
AND I.EMPLID = K.EMPLID (+)
AND I.ACAD_CAREER = K.ACAD_CAREER (+)
AND I.STDNT_CAR_NBR = K.STDNT_CAR_NBR (+)
AND I.EFFSEQ = K.EFFSEQ (+)
AND I.ACAD_PLAN = K.ACAD_PLAN (+)
AND I.EFFDT = K.EFFDT (+)
AND A.EMPLID = J.EMPLID (+)
AND A.ACAD_CAREER = J.ACAD_CAREER (+)
AND A.STDNT_CAR_NBR = J.STDNT_CAR_NBR (+)
AND A.EFFSEQ = J.EFFSEQ (+)
AND A.EFFDT = J.EFFDT (+)
AND 30 = J.PLAN_SEQUENCE (+)
AND J.EMPLID = L.EMPLID (+)
AND J.ACAD_CAREER = L.ACAD_CAREER (+)
AND J.STDNT_CAR_NBR = L.STDNT_CAR_NBR (+)
AND J.EFFSEQ = L.EFFSEQ (+)
AND J.ACAD_PLAN = L.ACAD_PLAN (+)
AND J.EFFDT = L.EFFDT (+)
ORDER BY 1 DESC, 2, 3, 4