Context
Callable SQL used in merit packaging to determine the admitted students recruitment category.
SQL
SELECT C.RECRUITMENT_CAT
FROM PS_ADM_APPL_PROG A, PS_ADM_APPL_DATA B, PS_ADM_APPL_RCR_CA C
WHERE ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_ADM_APPL_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.ADM_APPL_NBR = A_ED.ADM_APPL_NBR
AND A.APPL_PROG_NBR = A_ED.APPL_PROG_NBR
AND A_ED.EFFDT <= SYSDATE)
AND A.EFFSEQ =
(SELECT MAX(A_ES.EFFSEQ) FROM PS_ADM_APPL_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.ADM_APPL_NBR = A_ES.ADM_APPL_NBR
AND A.APPL_PROG_NBR = A_ES.APPL_PROG_NBR
AND A.EFFDT = A_ES.EFFDT)
AND A.EMPLID = :1
AND A.ADMIT_TERM = :2
AND A.INSTITUTION = 'UOD01'
AND A.ACAD_CAREER = 'UGRD'
AND A.PROG_STATUS IN ('AC','AD','PM')
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.ADMIT_TYPE IN ('FF','FR')
AND B.ADM_APPL_CTR = 'UINT'
AND A.EMPLID = C.EMPLID
AND A.ACAD_CAREER = C.ACAD_CAREER
AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
AND A.ADM_APPL_NBR = C.ADM_APPL_NBR
AND C.INSTITUTION = A.INSTITUTION
AND C.RECRUITMENT_CAT LIKE :3)