Created by Corina C Larsen, last modified by Miki Yanagi on Nov 03, 2021
The review of first generation coding was completed in Summer 2021. Undergraduate applicants for terms Spring 2022 or later will be evaluated based on the new process. Applicants to earlier semesters than Spring 2022 is subjected to the original code.
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.
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. The code for the views is below.
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
Related Page
First Generation Coding Data Issue Documentation
Field Definitions
Click the field names below for definitions and values for some fields which are used in the First Gen calculations.