Created by Miki Yanagi, last modified on Nov 03, 2021
Please go to First Generation page for most recent information. The current code is on the page.
The review of first generation coding was completed in Summer 2021. Undergraduate applicants to terms greater than or equal to Spring 2022 will be evaluated based on the new criteria. No historical First Generation data (applicants to earlier semesters than Spring 2022) will change as a result of this change.
Due to the sensitive nature of First Generation data, all reports using the UM_FIRST_GEN flag or student group 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.
In the new process, FGEN student group will be automatically loaded as applications are processed. The code for the daily batch load process is below. The UM_FIRST_GEN flag pulls directly from the FGEN student group for applicants to Spring 2022 and later semesters.
SQL for FGEN group load process Expand source
SELECT
DISTINCT
MY.EMPLID,
MY.INSTITUTION
FROM (Select
A.EMPLID,
A.INSTITUTION,
A.Admit_Term,
CASE WHEN A.EMPLID IN ((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_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.EFFDT = CC_ES.EFFDT)
AND CC.AID_YEAR = (SELECT MAX(CC_EA.AID_YEAR)
FROM SYSADM.PS_ISIR_PARENT CC_EA
WHERE CC.EMPLID = CC_EA.EMPLID
AND CC.EFFDT = CC_EA.EFFDT)
AND CC.FATHER_GRADE_LVL IN ('1', '2')
AND CC.MOTHER_GRADE_LVL IN ('1', '2'))
UNION
(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
(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', 'D', 'E', 'F')
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 FIRST_GEN_YES,
CASE WHEN A.EMPLID IN ((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_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.EFFDT = CC_ES.EFFDT)
AND CC.AID_YEAR = (SELECT MAX(CC_EA.AID_YEAR)
FROM SYSADM.PS_ISIR_PARENT CC_EA
WHERE CC.EMPLID = CC_EA.EMPLID
AND CC.EFFDT = CC_EA.EFFDT)
AND (CC.FATHER_GRADE_LVL = '3'
OR CC.MOTHER_GRADE_LVL = '3'))
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 ('G','H', 'I', 'J', 'K', 'L')
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 FIRST_GEN_NO
FROM SYSADM.PS_ADM_APPL_PROG A
WHERE A.Admit_Term >= '2220'
AND A.ACAD_CAREER = 'UGRD'
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)
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)) MY
WHERE MY.FIRST_GEN_YES = 'Y'
AND MY.FIRST_GEN_NO <> 'Y'
order by 1
;
The UM_FIRST_GEN field is available for reporting on UM_STUD_EXT_VW and UM_STUD_CENS_VW.
SQL for views Expand source
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.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 FIRST_GEN
Field Definitions
Archived page
First Generation Coding Data Issue Documentation
First Generation (before summer 2021 change)
JIRA ticket page