This page contains the code script for loading first generation student group and first generation field on reporting tables.
Detailed Information
Starting in Fall 2024, the source table for first-generation student data has been updated. The primary source of FGEN information comes from FAFSA records, which underwent significant changes in the 2024-2025 financial aid year. Consequently, the code used to load student groups for first-generation students required adjustments. Below is the revised code for the daily load process.
/*
Purpose: This query identifies first-generation students based on multiple criteria including:
- ISIR student data (financial aid information)
- Historical student group data
- Parent/guardian education levels
- Application data
Key Tables:
- ps_sfa_isir_stu: Student financial aid ISIR data
- ps_stdnt_grps_hist: Historical student group membership
- ps_relation_vw: Parent/guardian relationship information
- ps_adm_appl_prog: Admission application program data
- ps_adm_appl_data: Application completion data
*/
WITH first_gen_data AS (
SELECT
a.emplid,
a.institution,
a.admit_term,
-- Check if student qualifies as first generation
CASE WHEN a.emplid IN (
-- Check ISIR data for parent attendance codes 1 or 2
SELECT s.emplid
FROM sysadm.ps_sfa_isir_stu s
WHERE s.emplid = a.emplid
AND s.institution = a.institution
AND s.sfa_std_paratt IN ('1','2')
-- Get most recent effective dated row
AND s.effdt = (
SELECT MAX(s1.effdt)
FROM sysadm.ps_sfa_isir_stu s1
WHERE s1.emplid = s.emplid
AND s1.institution = s.institution
AND s1.aid_year = s.aid_year
AND s1.effdt <= SYSDATE
)
-- Get most recent sequence for the effective date
AND s.effseq = (
SELECT MAX(s2.effseq)
FROM sysadm.ps_sfa_isir_stu s2
WHERE s2.emplid = s.emplid
AND s2.institution = s.institution
AND s2.aid_year = s.aid_year
AND s2.effdt = s.effdt
)
-- Get most recent aid year
AND s.aid_year = (
SELECT MAX(s0.aid_year)
FROM sysadm.ps_sfa_isir_stu s0
WHERE s0.emplid = s.emplid
AND s0.institution = s.institution
AND s0.aid_year = s.aid_year
AND s0.effdt = s.effdt
)
UNION
-- Check historical student groups for FGEN flag
SELECT dts.emplid
FROM sysadm.ps_stdnt_grps_hist dts
WHERE dts.stdnt_group = 'FGEN'
AND dts.effdt >= TO_DATE('08/01/2021', 'MM/DD/YYYY')
AND dts.eff_status = 'A'
AND dts.emplid = a.emplid
UNION
-- Check parent/guardian education levels
SELECT dd.emplid
FROM sysadm.ps_relation_vw dd
WHERE (
dd.people_relation IN ('M', 'FA', 'PR', 'SF', 'SM') -- Parent relations
OR dd.guardian_relations IN ('LG', 'PG') -- Guardian relations
)
AND dd.highest_educ_lvl IN ('B', 'C', 'D', 'E', 'F') -- Lower education levels
-- Compare to find highest education level among all parents/guardians
AND DECODE(dd.highest_educ_lvl,
' ', 1, 'A', 2, 'B', 3, 'C', 4, 'D', 5,
'E', 6, 'F', 7, 'G', 8, 'H', 9, 'I', 10,
'J', 11, 'K', 12, 'L', 13) = (
SELECT MAX(DECODE(ee.highest_educ_lvl,
' ', 1, 'A', 2, 'B', 3, 'C', 4, 'D', 5,
'E', 6, 'F', 7, 'G', 8, 'H', 9, 'I', 10,
'J', 11, 'K', 12, 'L', 13))
FROM sysadm.ps_relation_vw ee
WHERE dd.emplid = ee.emplid
AND (ee.people_relation IN ('M', 'FA', 'PR', 'SF', 'SM')
OR ee.guardian_relations IN ('LG', 'PG'))
)
) THEN 'Y' ELSE 'N' END AS first_gen_yes,
-- Check if student is explicitly marked as non-first generation
CASE WHEN a.emplid IN (
-- Check ISIR data for parent attendance code 3
SELECT s.emplid
FROM sysadm.ps_sfa_isir_stu s
WHERE s.emplid = a.emplid
AND s.institution = a.institution
AND s.sfa_std_paratt = '3'
AND s.effdt = (
SELECT MAX(s1.effdt)
FROM sysadm.ps_sfa_isir_stu s1
WHERE s1.emplid = s.emplid
AND s1.institution = s.institution
AND s1.aid_year = s.aid_year
AND s1.effdt <= SYSDATE
)
AND s.effseq = (
SELECT MAX(s2.effseq)
FROM sysadm.ps_sfa_isir_stu s2
WHERE s2.emplid = s.emplid
AND s2.institution = s.institution
AND s2.aid_year = s.aid_year
AND s2.effdt = s.effdt
)
AND s.aid_year = (
SELECT MAX(s0.aid_year)
FROM sysadm.ps_sfa_isir_stu s0
WHERE s0.emplid = s.emplid
AND s0.institution = s.institution
AND s0.aid_year = s.aid_year
AND s0.effdt = s.effdt
)
UNION
-- Check for parents/guardians with higher education levels
SELECT dd.emplid
FROM sysadm.ps_relation_vw dd
WHERE (
dd.people_relation IN ('M', 'FA', 'PR', 'SF', 'SM')
OR dd.guardian_relations IN ('LG', 'PG')
)
AND dd.highest_educ_lvl IN ('G', 'H', 'I', 'J', 'K', 'L') -- Higher education levels
AND DECODE(dd.highest_educ_lvl,
' ', 1, 'A', 2, 'B', 3, 'C', 4, 'D', 5,
'E', 6, 'F', 7, 'G', 8, 'H', 9, 'I', 10,
'J', 11, 'K', 12, 'L', 13) = (
SELECT MAX(DECODE(ee.highest_educ_lvl,
' ', 1, 'A', 2, 'B', 3, 'C', 4, 'D', 5,
'E', 6, 'F', 7, 'G', 8, 'H', 9, 'I', 10,
'J', 11, 'K', 12, 'L', 13))
FROM sysadm.ps_relation_vw ee
WHERE dd.emplid = ee.emplid
AND (ee.people_relation IN ('M', 'FA', 'PR', 'SF', 'SM')
OR ee.guardian_relations IN ('LG', 'PG'))
)
) THEN 'Y' ELSE 'N' END AS first_gen_no
FROM sysadm.ps_adm_appl_prog a
WHERE a.admit_term >= '2220' -- Only recent terms
AND a.acad_career = 'UGRD' -- Only undergraduate students
-- Get most recent effective dated row
AND a.effdt = (
SELECT MAX(aa.effdt)
FROM sysadm.ps_adm_appl_prog aa
WHERE a.emplid = aa.emplid
AND a.acad_career = aa.acad_career
AND a.stdnt_car_nbr = aa.stdnt_car_nbr
AND a.adm_appl_nbr = aa.adm_appl_nbr
AND a.appl_prog_nbr = aa.appl_prog_nbr
)
-- Only include completed applications
AND EXISTS (
SELECT 'TheJab'
FROM sysadm.ps_adm_appl_data d
WHERE d.emplid = a.emplid
AND d.acad_career = a.acad_career
AND d.stdnt_car_nbr = a.stdnt_car_nbr
AND d.adm_appl_nbr = a.adm_appl_nbr
AND d.adm_appl_complete = 'Y'
AND d.adm_appl_cmplt_dt IS NOT NULL
)
-- Exclude students already in FGEN group
AND NOT EXISTS (
SELECT jaj.emplid
FROM sysadm.ps_stdnt_grps jaj
WHERE jaj.stdnt_group = 'FGEN'
AND jaj.emplid = a.emplid
AND jaj.institution = a.institution
)
)
-- Final selection: Include only confirmed first-generation students
SELECT distinct emplid,institution
FROM first_gen_data
WHERE first_gen_yes = 'Y' -- Must meet first-gen criteria
AND first_gen_no <> 'Y';
Due to the sensitive nature of frst generation data, all reports using the first generation data must receive approval (if not already approved) by completing our First Generation Data Usage Request form. There is an existing catalog of data systems/locations that have already been identified and approved.
The UM_FIRST_GEN field is available on UM_STUD_ARC_VW, UM_STUD_EXT_VW, and UM_STUD_CENS_VW to report first-generation data. The code for the field is below.
CASE
WHEN A.ADMIT_TERM >= '2220' AND
A.EMPLID IN (SELECT NC.EMPLID
FROM SYSADM.PS_STDNT_GRPS NC
WHERE NC.STDNT_GROUP = 'FGEN') THEN
'Y'
WHEN A.ADMIT_TERM < '2220' AND
A.EMPLID IN
((SELECT AA.COMMON_ID AS FGEN_EMPLID
FROM SYSADM.PS_PERSON_COMMENT AA
WHERE AA.INSTITUTION = 'UMS01'
AND AA.CMNT_CATEGORY = 'CS1'
AND (AA.COMMENTS LIKE '%1st gen%' OR
AA.COMMENTS LIKE '%1st Gen%')
UNION
SELECT BB.EMPLID
FROM SYSADM.PS_STDNT_GRPS BB
WHERE BB.STDNT_GROUP = 'FGEN'
UNION
SELECT CC.EMPLID
FROM SYSADM.PS_ISIR_PARENT CC
WHERE CC.EFFDT = (SELECT MAX(CC_ED.EFFDT)
FROM SYSADM.PS_ISIR_PARENT CC_ED
WHERE CC.EMPLID = CC_ED.EMPLID
AND CC.INSTITUTION = CC_ED.INSTITUTION
AND CC.AID_YEAR = CC_ED.AID_YEAR
AND CC_ED.EFFDT <= SYSDATE)
AND CC.EFFSEQ =
(SELECT MAX(CC_ES.EFFSEQ)
FROM SYSADM.PS_ISIR_PARENT CC_ES
WHERE CC.EMPLID = CC_ES.EMPLID
AND CC.INSTITUTION = CC_ES.INSTITUTION
AND CC.AID_YEAR = CC_ES.AID_YEAR
AND CC.EFFDT = CC_ES.EFFDT)
AND CC.FATHER_GRADE_LVL IN ('1', '2', '4')
AND CC.MOTHER_GRADE_LVL IN ('1', '2', '4')
UNION
SELECT DD.EMPLID
FROM SYSADM.PS_RELATION_VW DD
WHERE (DD.PEOPLE_RELATION IN ('M', 'FA', 'PR', 'SF', 'SM') OR
DD.GUARDIAN_RELATIONS IN ('LG', 'PG'))
AND DD.HIGHEST_EDUC_LVL IN ('B', 'C')
AND DECODE(DD.HIGHEST_EDUC_LVL,
' ',
1,
'A',
2,
'B',
3,
'C',
4,
'D',
5,
'E',
6,
'F',
7,
'G',
8,
'H',
9,
'I',
10,
'J',
11,
'K',
12,
'L',
13) =
(SELECT MAX(DECODE(EE.HIGHEST_EDUC_LVL,
' ',
1,
'A',
2,
'B',
3,
'C',
4,
'D',
5,
'E',
6,
'F',
7,
'G',
8,
'H',
9,
'I',
10,
'J',
11,
'K',
12,
'L',
13))
FROM SYSADM.PS_RELATION_VW EE
WHERE DD.EMPLID = EE.EMPLID
AND (EE.PEOPLE_RELATION IN
('M', 'FA', 'PR', 'SF', 'SM') OR
EE.GUARDIAN_RELATIONS IN ('LG', 'PG'))))) THEN
'Y'
ELSE
'N'
END
Environment
- MaineStreet Campus Solution (CSPRD)
- MaineStreet Campus Solution Reporting (CSRPT)