DARTS : First Generation - prior to Fall 2021

–- The first part is for UMS01 and is looking for comments in the PS_PERSON_COMMENT table (PS_PERSON_COMMENT is used to record free-form comments made about a person. These comments may be shared or restricted based on user-defined Inquiry Groups)
CASE 
WHEN A.EMPLID IN (( 
 SELECT AA.COMMON_ID AS FGEN_EMPLID 
  FROM 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%') 
    
-– The second part looks in the student groups table for an FGEN student group  (PS_STDNT_GRPS holds user-defined student groups).
    
  UNION 
 SELECT BB.EMPLID 
  FROM PS_STDNT_GRPS BB 
 WHERE BB.STDNT_GROUP = 'FGEN' 
 
 -- The third part looks at parent financial aid data and their education level.
  UNION 
  SELECT CC.EMPLID 
  FROM PS_ISIR_PARENT CC 
 WHERE CC.EFFDT = ( 
 SELECT MAX(CC_ED.EFFDT) 
  FROM 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 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') 
   
-– The final bit looks at highest education level in the PS_RELATION_VW . The view gets highest_educ_level from the table PS_RELATIONSHIPS
  UNION 
 SELECT DD.EMPLID 
  FROM 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 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