Context
This article provides a list of SQL queries used to review spring awarding data in PeopleSoft.
Queries
- UDSFS_LOA_FALL_REVIEW_SPR: Used to identify active students that took a leave of absence in the fall semester prior. Identifies LOA students that should be reviewed for spring aid. Includes both winter enrollment and actual spring enrollment (not projected enrollment).
- UDSFS_LOA_FALL_REVIEW_SPR_PND: Takes the query from above but only shows those that are pending / have not been reviewed yet. Uses the review status field on the student aid attributes table to identify pending students. If that field is set to "complete" that student will no longer show up on this query.
- UDSFS_SPRING26_FEDAID_QC: Identifies Spring 2025–2026 spring-admit students with ISIR data on file but no associated packaging plan. Aimed to identify students who were reviewed for merit packaging prior to the receipt of an ISIR.
SQL
- UDSFS_LOA_FALL_REVIEW_SPR: SELECT A.EMPLID, A.PKG_PLAN_ID, TO_CHAR(CAST((A.PKG_LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), I.ACAD_CAREER, I.ACAD_PROG_PRIMARY, A.SAT_ACADEMIC_PRG, SUM( E.OFFER_AMOUNT), F.FISAP_TOT_INC, H.UNT_TAKEN_FA, C.UNT_TAKEN_PRGRSS
FROM (((((PS_STDNT_AID_ATRBT A LEFT OUTER JOIN PS_STDNT_AWARDS E ON A.EMPLID = E.EMPLID AND A.INSTITUTION = E.INSTITUTION AND A.AID_YEAR = E.AID_YEAR AND E.ACAD_CAREER = A.ACAD_CAREER ) LEFT OUTER JOIN PS_ISIR_STUDENT F ON A.EMPLID = F.EMPLID AND A.INSTITUTION = F.INSTITUTION AND A.AID_YEAR = F.AID_YEAR AND F.EFFDT =
(SELECT MAX(F_ED.EFFDT) FROM PS_ISIR_STUDENT F_ED
WHERE F.EMPLID = F_ED.EMPLID
AND F.INSTITUTION = F_ED.INSTITUTION
AND F.AID_YEAR = F_ED.AID_YEAR
AND F_ED.EFFDT <= SYSDATE)
AND F.EFFSEQ =
(SELECT MAX(F_ES.EFFSEQ) FROM PS_ISIR_STUDENT F_ES
WHERE F.EMPLID = F_ES.EMPLID
AND F.INSTITUTION = F_ES.INSTITUTION
AND F.AID_YEAR = F_ES.AID_YEAR
AND F.EFFDT = F_ES.EFFDT) ) LEFT OUTER JOIN PS_STDNT_FA_TERM H ON A.EMPLID = H.EMPLID AND A.INSTITUTION = H.INSTITUTION AND A.AID_YEAR = H.AID_YEAR AND H.EFFDT =
(SELECT MAX(H_ED.EFFDT) FROM PS_STDNT_FA_TERM H_ED
WHERE H.EMPLID = H_ED.EMPLID
AND H.INSTITUTION = H_ED.INSTITUTION
AND H.STRM = H_ED.STRM
AND H_ED.EFFDT <= SYSDATE)
AND H.EFFSEQ =
(SELECT MAX(H_ES.EFFSEQ) FROM PS_STDNT_FA_TERM H_ES
WHERE H.EMPLID = H_ES.EMPLID
AND H.INSTITUTION = H_ES.INSTITUTION
AND H.STRM = H_ES.STRM
AND H.EFFDT = H_ES.EFFDT) AND H.STRM = :2 + 3 ) LEFT OUTER JOIN PS_STDNT_FA_TERM I ON A.EMPLID = I.EMPLID AND A.INSTITUTION = I.INSTITUTION AND A.AID_YEAR = I.AID_YEAR AND I.EFFDT =
(SELECT MAX(I_ED.EFFDT) FROM PS_STDNT_FA_TERM I_ED
WHERE I.EMPLID = I_ED.EMPLID
AND I.INSTITUTION = I_ED.INSTITUTION
AND I.STRM = I_ED.STRM
AND I_ED.EFFDT <= SYSDATE)
AND I.EFFSEQ =
(SELECT MAX(I_ES.EFFSEQ) FROM PS_STDNT_FA_TERM I_ES
WHERE I.EMPLID = I_ES.EMPLID
AND I.INSTITUTION = I_ES.INSTITUTION
AND I.STRM = I_ES.STRM
AND I.EFFDT = I_ES.EFFDT) AND I.STRM = :2 ) LEFT OUTER JOIN PS_STDNT_CAR_TERM C ON A.EMPLID = C.EMPLID AND A.INSTITUTION = C.INSTITUTION AND C.ACAD_CAREER = A.ACAD_CAREER AND C.STRM = :2 + 5 )
WHERE ( A.INSTITUTION = 'UOD01'
AND A.AID_YEAR = :1
AND A.EMPLID IN (SELECT B.EMPLID
FROM PS_CLASS_TBL_SE_VW B
WHERE B.STRM = :2
AND B.CRSE_ID = '031133'
AND B.STDNT_ENRL_STATUS = 'E')
AND A.EMPLID IN (SELECT D.EMPLID
FROM PS_ADM_APPL_PROG D, PS_ADM_MAINT_SCTY D1
WHERE D.EMPLID = D1.EMPLID
AND D.ACAD_CAREER = D1.ACAD_CAREER
AND D.STDNT_CAR_NBR = D1.STDNT_CAR_NBR
AND D.ADM_APPL_NBR = D1.ADM_APPL_NBR
AND D.APPL_PROG_NBR = D1.APPL_PROG_NBR
AND D1.OPRCLASS = 'HCPPALL'
AND D1.OPRID = '702050948'
AND ( D.EFFDT =
(SELECT MAX(D_ED.EFFDT) FROM PS_ADM_APPL_PROG D_ED
WHERE D.EMPLID = D_ED.EMPLID
AND D.ACAD_CAREER = D_ED.ACAD_CAREER
AND D.STDNT_CAR_NBR = D_ED.STDNT_CAR_NBR
AND D.ADM_APPL_NBR = D_ED.ADM_APPL_NBR
AND D.APPL_PROG_NBR = D_ED.APPL_PROG_NBR
AND D_ED.EFFDT <= SYSDATE)
AND D.EFFSEQ =
(SELECT MAX(D_ES.EFFSEQ) FROM PS_ADM_APPL_PROG D_ES
WHERE D.EMPLID = D_ES.EMPLID
AND D.ACAD_CAREER = D_ES.ACAD_CAREER
AND D.STDNT_CAR_NBR = D_ES.STDNT_CAR_NBR
AND D.ADM_APPL_NBR = D_ES.ADM_APPL_NBR
AND D.APPL_PROG_NBR = D_ES.APPL_PROG_NBR
AND D.EFFDT = D_ES.EFFDT)
AND D.PROG_STATUS IN ('AC','AD','PM') )))
GROUP BY A.EMPLID, A.PKG_PLAN_ID, A.PKG_LASTUPDDTTM, I.ACAD_CAREER, I.ACAD_PROG_PRIMARY, A.SAT_ACADEMIC_PRG, F.FISAP_TOT_INC, H.UNT_TAKEN_FA, C.UNT_TAKEN_PRGRSS
- SELECT A.EMPLID, A.PKG_PLAN_ID, TO_CHAR(CAST((A.PKG_LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), I.ACAD_CAREER, I.ACAD_PROG_PRIMARY, A.SAT_ACADEMIC_PRG, SUM( E.OFFER_AMOUNT), F.FISAP_TOT_INC, H.UNT_TAKEN_FA, C.UNT_TAKEN_PRGRSS
FROM (((((PS_STDNT_AID_ATRBT A LEFT OUTER JOIN PS_STDNT_AWARDS E ON A.EMPLID = E.EMPLID AND A.INSTITUTION = E.INSTITUTION AND A.AID_YEAR = E.AID_YEAR AND E.ACAD_CAREER = A.ACAD_CAREER ) LEFT OUTER JOIN PS_ISIR_STUDENT F ON A.EMPLID = F.EMPLID AND A.INSTITUTION = F.INSTITUTION AND A.AID_YEAR = F.AID_YEAR AND F.EFFDT =
(SELECT MAX(F_ED.EFFDT) FROM PS_ISIR_STUDENT F_ED
WHERE F.EMPLID = F_ED.EMPLID
AND F.INSTITUTION = F_ED.INSTITUTION
AND F.AID_YEAR = F_ED.AID_YEAR
AND F_ED.EFFDT <= SYSDATE)
AND F.EFFSEQ =
(SELECT MAX(F_ES.EFFSEQ) FROM PS_ISIR_STUDENT F_ES
WHERE F.EMPLID = F_ES.EMPLID
AND F.INSTITUTION = F_ES.INSTITUTION
AND F.AID_YEAR = F_ES.AID_YEAR
AND F.EFFDT = F_ES.EFFDT) ) LEFT OUTER JOIN PS_STDNT_FA_TERM H ON A.EMPLID = H.EMPLID AND A.INSTITUTION = H.INSTITUTION AND A.AID_YEAR = H.AID_YEAR AND H.EFFDT =
(SELECT MAX(H_ED.EFFDT) FROM PS_STDNT_FA_TERM H_ED
WHERE H.EMPLID = H_ED.EMPLID
AND H.INSTITUTION = H_ED.INSTITUTION
AND H.STRM = H_ED.STRM
AND H_ED.EFFDT <= SYSDATE)
AND H.EFFSEQ =
(SELECT MAX(H_ES.EFFSEQ) FROM PS_STDNT_FA_TERM H_ES
WHERE H.EMPLID = H_ES.EMPLID
AND H.INSTITUTION = H_ES.INSTITUTION
AND H.STRM = H_ES.STRM
AND H.EFFDT = H_ES.EFFDT) AND H.STRM = :2 + 3 ) LEFT OUTER JOIN PS_STDNT_FA_TERM I ON A.EMPLID = I.EMPLID AND A.INSTITUTION = I.INSTITUTION AND A.AID_YEAR = I.AID_YEAR AND I.EFFDT =
(SELECT MAX(I_ED.EFFDT) FROM PS_STDNT_FA_TERM I_ED
WHERE I.EMPLID = I_ED.EMPLID
AND I.INSTITUTION = I_ED.INSTITUTION
AND I.STRM = I_ED.STRM
AND I_ED.EFFDT <= SYSDATE)
AND I.EFFSEQ =
(SELECT MAX(I_ES.EFFSEQ) FROM PS_STDNT_FA_TERM I_ES
WHERE I.EMPLID = I_ES.EMPLID
AND I.INSTITUTION = I_ES.INSTITUTION
AND I.STRM = I_ES.STRM
AND I.EFFDT = I_ES.EFFDT) AND I.STRM = :2 ) LEFT OUTER JOIN PS_STDNT_CAR_TERM C ON A.EMPLID = C.EMPLID AND A.INSTITUTION = C.INSTITUTION AND C.ACAD_CAREER = A.ACAD_CAREER AND C.STRM = :2 + 5 )
WHERE ( A.INSTITUTION = 'UOD01'
AND A.AID_YEAR = :1
AND A.EMPLID IN (SELECT B.EMPLID
FROM PS_CLASS_TBL_SE_VW B
WHERE B.STRM = :2
AND B.CRSE_ID = '031133'
AND B.STDNT_ENRL_STATUS = 'E')
AND A.EMPLID IN (SELECT D.EMPLID
FROM PS_ADM_APPL_PROG D, PS_ADM_MAINT_SCTY D1
WHERE D.EMPLID = D1.EMPLID
AND D.ACAD_CAREER = D1.ACAD_CAREER
AND D.STDNT_CAR_NBR = D1.STDNT_CAR_NBR
AND D.ADM_APPL_NBR = D1.ADM_APPL_NBR
AND D.APPL_PROG_NBR = D1.APPL_PROG_NBR
AND D1.OPRCLASS = 'HCPPALL'
AND D1.OPRID = '702050948'
AND ( D.EFFDT =
(SELECT MAX(D_ED.EFFDT) FROM PS_ADM_APPL_PROG D_ED
WHERE D.EMPLID = D_ED.EMPLID
AND D.ACAD_CAREER = D_ED.ACAD_CAREER
AND D.STDNT_CAR_NBR = D_ED.STDNT_CAR_NBR
AND D.ADM_APPL_NBR = D_ED.ADM_APPL_NBR
AND D.APPL_PROG_NBR = D_ED.APPL_PROG_NBR
AND D_ED.EFFDT <= SYSDATE)
AND D.EFFSEQ =
(SELECT MAX(D_ES.EFFSEQ) FROM PS_ADM_APPL_PROG D_ES
WHERE D.EMPLID = D_ES.EMPLID
AND D.ACAD_CAREER = D_ES.ACAD_CAREER
AND D.STDNT_CAR_NBR = D_ES.STDNT_CAR_NBR
AND D.ADM_APPL_NBR = D_ES.ADM_APPL_NBR
AND D.APPL_PROG_NBR = D_ES.APPL_PROG_NBR
AND D.EFFDT = D_ES.EFFDT)
AND D.PROG_STATUS IN ('AC','AD','PM') ))
AND A.SFA_REVIEW_STATUS <> 'C')
GROUP BY A.EMPLID, A.PKG_PLAN_ID, A.PKG_LASTUPDDTTM, I.ACAD_CAREER, I.ACAD_PROG_PRIMARY, A.SAT_ACADEMIC_PRG, F.FISAP_TOT_INC, H.UNT_TAKEN_FA, C.UNT_TAKEN_PRGRSS
- UDSFS_SPRING26_FEDAID_QC: SELECT A.EMPLID, A.PKG_PLAN_ID, D.EFC_STATUS, TO_CHAR(D.TRANS_PROCESS_DT,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD')
FROM PS_STDNT_AID_ATRBT A, PS_ISIR_CONTROL D
WHERE ( A.AID_YEAR = '2026'
AND A.SFA_REVIEW_STATUS = 'C'
AND A.EMPLID IN (SELECT B.EMPLID
FROM PS_ADM_APPL_PROG B, PS_ADM_MAINT_SCTY B1
WHERE B.EMPLID = B1.EMPLID
AND B.ACAD_CAREER = B1.ACAD_CAREER
AND B.STDNT_CAR_NBR = B1.STDNT_CAR_NBR
AND B.ADM_APPL_NBR = B1.ADM_APPL_NBR
AND B.APPL_PROG_NBR = B1.APPL_PROG_NBR
AND B1.OPRCLASS = 'HCPPALL'
AND B1.OPRID = '702050948'
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.PROG_STATUS IN ('AC','AD','PM')
AND B.ADMIT_TERM = '2263' ))
AND A.PKG_PLAN_ID IN (' ','SPRG_MERIT','TR_MERIT','MERIT_ONLY')
AND A.EMPLID = D.EMPLID
AND A.INSTITUTION = D.INSTITUTION
AND A.AID_YEAR = D.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)
AND D.EFC_STATUS <> 'R')