Body
Context
This article provides a list of SQL queries used to review summer awarding data in PeopleSoft.
Queries
- BPS_SUMMER_STDS_TO_PACKAGE: Used to identify students who have not been reviewed for summer aid. We use the SUMREV 3C comment to track/exclude students that have already been reviewed.
SQL
- SELECT DISTINCT A.EMPLID, A.STRM, A.ACAD_CAREER, A.ADMIT_TERM, A.ACAD_PROG_PRIMARY, A.UNT_TAKEN_FA, D.PELL_ELIGIBILITY
FROM PS_STDNT_FA_TERM A, PS_ISIR_CONTROL D
WHERE ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_STDNT_FA_TERM A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.INSTITUTION = A_ED.INSTITUTION
AND A.STRM = A_ED.STRM
AND A_ED.EFFDT <= SYSDATE)
AND A.EFFSEQ =
(SELECT MAX(A_ES.EFFSEQ) FROM PS_STDNT_FA_TERM A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.INSTITUTION = A_ES.INSTITUTION
AND A.STRM = A_ES.STRM
AND A.EFFDT = A_ES.EFFDT)
AND A.STRM = :1
AND A.UNT_TAKEN_FA > 0
AND A.AID_YEAR = :2
AND A.EMPLID NOT IN (SELECT B.COMMON_ID
FROM PS_VAR_DATA_FINA B, PS_PERSON_COMMENT C
WHERE B.AID_YEAR = :2
AND B.COMMON_ID = C.COMMON_ID
AND B.VAR_DATA_SEQ = C.VAR_DATA_SEQ
AND C.CMNT_CATEGORY = 'SUMREV')
AND A.EMPLID = D.EMPLID
AND A.INSTITUTION = D.INSTITUTION
AND D.AID_YEAR = A.AID_YEAR
AND D.EFFDT =
(SELECT MAX(D_ED.EFFDT) FROM PS_ISIR_CONTROL D_ED
WHERE D.EMPLID = D_ED.EMPLID
AND D.INSTITUTION = D_ED.INSTITUTION
AND D.AID_YEAR = D_ED.AID_YEAR
AND D_ED.EFFDT <= SYSDATE)
AND D.EFFSEQ =
(SELECT MAX(D_ES.EFFSEQ) FROM PS_ISIR_CONTROL D_ES
WHERE D.EMPLID = D_ES.EMPLID
AND D.INSTITUTION = D_ES.INSTITUTION
AND D.AID_YEAR = D_ES.AID_YEAR
AND D.EFFDT = D_ES.EFFDT))
ORDER BY 6 DESC, 1