Body
Context
Callable SQL used in merit packaging to pull a student's GPA / rating value.
SQL
SELECT DISTINCT A.RATING_CMP_VALUE
FROM PS_ADM_APPL_CMP A, PS_ADM_APPL_PROG B
WHERE ( A.EMPLID = :2
AND A.ACAD_CAREER = 'UGRD'
AND A.EVALUATION_CODE = :3
AND A.RATING_CMP = :4
AND A.EMPLID = B.EMPLID
AND A.ACAD_CAREER = B.ACAD_CAREER
AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR
AND A.ADM_APPL_NBR = B.ADM_APPL_NBR
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_ADM_APPL_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.ADM_APPL_NBR = B_ED.ADM_APPL_NBR
AND B.APPL_PROG_NBR = B_ED.APPL_PROG_NBR
AND B_ED.EFFDT <= SYSDATE)
AND B.EFFSEQ =
(SELECT MAX(B_ES.EFFSEQ) FROM PS_ADM_APPL_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.ADM_APPL_NBR = B_ES.ADM_APPL_NBR
AND B.APPL_PROG_NBR = B_ES.APPL_PROG_NBR
AND B.EFFDT = B_ES.EFFDT)
AND B.INSTITUTION = 'UOD01'
AND B.ADMIT_TERM = :1
AND B.PROG_STATUS IN ('AD','AC','PM')
AND A.APPL_EVAL_NBR = (SELECT DISTINCT MAX( C.APPL_EVAL_NBR)
FROM PS_ADM_APPL_CMP C
WHERE C.EMPLID = A.EMPLID
AND C.ACAD_CAREER = A.ACAD_CAREER
AND C.STDNT_CAR_NBR = A.STDNT_CAR_NBR
AND C.ADM_APPL_NBR = A.ADM_APPL_NBR
AND C.APPL_PROG_NBR = A.APPL_PROG_NBR
AND C.EVALUATION_CODE = A.EVALUATION_CODE
AND C.RATING_CMP = A.RATING_CMP) )