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 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, D.TOT_PASSD_GPA, D.UNT_TAKEN_GPA, D.TOT_TAKEN_GPA, D.CUR_GPA, D.CUM_GPA, G.EMAIL_ADDR, B.EMAIL_ADDR, count( H.CLASS_NBR), count( I.CLASS_NBR)
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_CLASS_TBL_SE_VW H, PS_CLASS_TBL_SE_VW I
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 D.EMPLID = H.EMPLID
AND D.ACAD_CAREER = H.ACAD_CAREER
AND D.INSTITUTION = H.INSTITUTION
AND H.SUBJECT <> 'FRO'
AND H.CATALOG_NBR LIKE '%H'
AND H.STDNT_ENRL_STATUS = 'E'
AND H.GRADE_POINTS > 0
AND D.EMPLID = I.EMPLID
AND D.ACAD_CAREER = I.ACAD_CAREER
AND D.INSTITUTION = I.INSTITUTION
AND D.STRM = I.STRM
AND I.SUBJECT <> 'FRO'
AND I.CATALOG_NBR LIKE '%H'
AND I.STDNT_ENRL_STATUS = 'E'
GROUP BY 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, D.TOT_PASSD_GPA, D.UNT_TAKEN_GPA, D.TOT_TAKEN_GPA, D.CUR_GPA, D.CUM_GPA, G.EMAIL_ADDR, B.EMAIL_ADDR
ORDER BY 2, 3