DARTS : UM_RETENTION_FS

##UM_RETENTION_FS

%Select(STRM, AS_OF_DATE, INSTITUTION, ACAD_CAREER, EMPLID, STDNT_CAR_NBR,ACAD_PLAN, ACAD_PROG_PRIMARY, ADMIT_TERM, ADMIT_TYPE, BIRTHDATE, COMPLETION_TERM, 
DEGR_CONFER_DT, DEGR_STATUS_DATE, DEGREE, DESCRFORMAL, DT_OF_DEATH, EDUCATION_LVL, ETHNIC_GROUP, FERPA_FLAG, HISP_LATINO, LEVEL_LOAD_RULE, MAR_STATUS, NAME, 
UM_PLANNED_DEGREE, PROG_REASON, RESIDENCY, SEX, STDNT_DEGR, UM_CENSUS_AGE, UM_CENSUS_AGE_RNG, UM_COHORT_STRM, UM_COHORT_TYPE, UM_COHORT_TYP_DSCR, UM_DEGREE_NONDEG, 
UM_ETHNIC_GRP_DESC, UM_FALL_SPRNG_TRMS, UM_FIRST_GEN, UM_FIRST_TERM, UM_FIRST_TIME, UM_FULL_PART, UM_IN_OUT, UM_LAST_SCH_TYPE, UM_MULTIPLE_ETHNIC, UM_RESIDENCY_DESCR, 
UM_SENIOR_CITIZEN, UM_STUDENT_LEVEL, UM_TRAD_STUDENT, UM_COLLEGE_DESCR50, UM_RETURN_TERM2, UM_RETURN_YEAR2, UM_RETURN_YEAR3, UM_RETURN_YEAR4, UM_RETURN_YEAR5, 
UM_RETURN_YEAR6, UM_RETURN_YEAR7, UM_RETURN_YEAR8, UM_GRAD_IN_4_YRS, UM_GRAD_IN_5_YRS, UM_GRAD_IN_6_YRS, UM_GRAD_IN_7_YRS, UM_GRAD_IN_8_YRS) 

SELECT 
A.STRM 
, to_char(to_number(substr(A.STRM , 1 , 2)) + 1999 , '9999') || '-10-15' AS_OF_DATE 
, A.INSTITUTION 
, A.ACAD_CAREER 
, A.EMPLID 
, A.STDNT_CAR_NBR 
, A.ACAD_PLAN 
, A.ACAD_PROG_PRIMARY 
, A.ADMIT_TERM 
, A.ADMIT_TYPE 
, TO_CHAR(A.BIRTHDATE , 'YYYY-MM-DD') 
, TD.COMPLETION_TERM 
, TO_CHAR(TD.DEGR_CONFER_DT , 'YYYY-MM-DD') 
, TO_CHAR(TD.DEGR_STATUS_DATE , 'YYYY-MM-DD') 
, TD.DEGREE 
, TD.DESCRFORMAL 
, TO_CHAR(A.DT_OF_DEATH , 'YYYY-MM-DD') 
, TD.EDUCATION_LVL 
, A.ETHNIC_GROUP 
, NVL(DZ.ferpa , ' ') FERPA_FLAG 
, A.HISP_LATINO 
, A.LEVEL_LOAD_RULE 
, A.MAR_STATUS 
, A.NAME 
, TD.PLAN_DEGREE 
, A.PROG_REASON 
, A.RESIDENCY 
, A.SEX 
, A.STDNT_DEGR 
, A.UM_CENSUS_AGE 
, A.UM_CENSUS_AGE_RNG 
, SS.UM_COHORT_STRM 
, SS.UM_COHORT_TYPE 
, PZ.DESCR 
, A.UM_DEGREE_NONDEG 
, A.UM_ETHNIC_GRP_DESC 
, B.UM_FALL_SPRNG_TRMS 
, A.UM_FIRST_GEN 
, A.UM_FIRST_TERM 
, A.UM_FIRST_TIME 
, A.UM_FULL_PART 
, A.UM_IN_OUT 
, A.UM_LAST_SCH_TYPE 
, A.UM_MULTIPLE_ETHNIC 
, A.UM_RESIDENCY_DESCR 
, CASE WHEN A.UM_CENSUS_AGE >= 65 THEN 'Y' ELSE 'N' END UM_SENIOR_CITIZEN 
, A.UM_STUDENT_LEVEL 
, CASE WHEN A.UM_CENSUS_AGE < 25 THEN 'Y' ELSE 'N' END UM_TRAD_STUDENT 
, CASE WHEN SUBSTR(ACAD_PROG_PRIMARY , 1 , 1) = 'A' THEN 'College of Arts, Humanities, and Social Sciences' 
  WHEN SUBSTR(A.ACAD_PROG_PRIMARY , 1 , 1) = 'L'   THEN 'Lewiston-Auburn College' 
  WHEN SUBSTR(A.ACAD_PROG_PRIMARY , 1 , 1) = 'M' THEN 'College of Management and Human Service' 
  WHEN SUBSTR(A.ACAD_PROG_PRIMARY , 1 , 1) = 'N' THEN 'Non-Degree' WHEN SUBSTR(A.ACAD_PROG_PRIMARY , 1 , 1) = 'P' THEN 'USM-Other' 
  WHEN SUBSTR(A.ACAD_PROG_PRIMARY , 1 , 1) = 'S' THEN 'College of Science, Technology, and Health' 
  WHEN SUBSTR(A.ACAD_PROG_PRIMARY , 1 , 1) = 'W' THEN 'Maine School of Law' ELSE 'Other' END 
  FUBAR -->UM_COLLEGE_DESCR50
, CASE WHEN INSTR(UM_FALL_SPRNG_TRMS , LTRIM(TO_CHAR(TO_NUMBER(SS.UM_COHORT_STRM , '9999') + 10 , '9999'))) > 0 THEN 1 ELSE 0 END AS RET_TERM_2  
, CASE WHEN INSTR(UM_FALL_SPRNG_TRMS , LTRIM(TO_CHAR(TO_NUMBER(SS.UM_COHORT_STRM , '9999') + 100 , '9999'))) > 0 THEN 1 ELSE 0 END AS RET_YR_2 
, CASE WHEN INSTR(UM_FALL_SPRNG_TRMS , LTRIM(TO_CHAR(TO_NUMBER(SS.UM_COHORT_STRM , '9999') + 200 , '9999'))) > 0 THEN 1 ELSE 0 END AS RET_YR_3 
, CASE WHEN INSTR(UM_FALL_SPRNG_TRMS , LTRIM(TO_CHAR(TO_NUMBER(SS.UM_COHORT_STRM , '9999') + 300 , '9999'))) > 0 THEN 1 ELSE 0 END AS RET_YR_4 
, CASE WHEN INSTR(UM_FALL_SPRNG_TRMS , LTRIM(TO_CHAR(TO_NUMBER(SS.UM_COHORT_STRM , '9999') + 400 , '9999'))) > 0 THEN 1 ELSE 0 END AS RET_YR_5 
, CASE WHEN INSTR(UM_FALL_SPRNG_TRMS , LTRIM(TO_CHAR(TO_NUMBER(SS.UM_COHORT_STRM , '9999') + 500 , '9999'))) > 0 THEN 1 ELSE 0 END AS RET_YR_6 
, CASE WHEN INSTR(UM_FALL_SPRNG_TRMS , LTRIM(TO_CHAR(TO_NUMBER(SS.UM_COHORT_STRM , '9999') + 600 , '9999'))) > 0 THEN 1 ELSE 0 END AS RET_YR_7  
, CASE WHEN INSTR(UM_FALL_SPRNG_TRMS , LTRIM(TO_CHAR(TO_NUMBER(SS.UM_COHORT_STRM , '9999') + 700 , '9999'))) > 0 THEN 1 ELSE 0 END AS RET_YR_8  
, CASE WHEN ((TD.COMPLETION_TERM - SS.UM_COHORT_STRM)) <= 320 THEN 1 ELSE 0 END GRAD_IN_4  
, CASE WHEN ((TD.COMPLETION_TERM - SS.UM_COHORT_STRM)) <= 420 THEN 1 ELSE 0 END GRAD_IN_5 
, CASE WHEN ((TD.COMPLETION_TERM - SS.UM_COHORT_STRM)) <= 520 THEN 1 ELSE 0 END GRAD_IN_6  
, CASE WHEN ((TD.COMPLETION_TERM - SS.UM_COHORT_STRM)) <= 620 THEN 1 ELSE 0 END GRAD_IN_7  
, CASE WHEN ((TD.COMPLETION_TERM - SS.UM_COHORT_STRM)) <= 720 THEN 1 ELSE 0 END GRAD_IN_8 
  
FROM SYSADM.PS_UM_STUD_CENS_VW A 

, SYSADM.PS_UM_RETENT_FS_IN B 

, SYSADM.PS_UM_STDNT_COHORT SS 

, ( SELECT A.EMPLID 
  , A.ACAD_CAREER 
  , A.INSTITUTION 
  , CASE WHEN A.DEGR_STATUS_DATE < A.DEGR_CONFER_DT AND A.DEGR_STATUS_DATE IS NOT NULL THEN A.DEGR_STATUS_DATE ELSE A.DEGR_CONFER_DT END LEAST_DEGREE_DATE 
  , A.DEGR_STATUS_DATE 
  , A.DEGR_CONFER_DT 
  , A.COMPLETION_TERM 
  , A.STDNT_DEGR 
  , A.DEGREE AS DEGREE 
  , B.DEGREE AS PLAN_DEGREE 
  , B.STRM 
  , W.EDUCATION_LVL 
  , W.DESCRFORMAL 
  FROM SYSADM.PS_ACAD_DEGR A , SYSADM.PS_UM_STUD_CENS_VW B , PS_DEGREE_TBL W 
  WHERE A.EMPLID(+) = B.EMPLID 
  AND A.ACAD_CAREER(+) = B.ACAD_CAREER 
  AND A.INSTITUTION(+) = B.INSTITUTION 
  AND A.DEGREE = W.DEGREE 
  AND W.EFF_STATUS = 'A' 
  AND (W.EFFDT = ( SELECT MAX(W2.EFFDT) FROM PS_DEGREE_TBL W2 WHERE W2.DEGREE = W.DEGREE) OR W.EFFDT IS NULL) 
  AND A.ACAD_CAREER = 'UGRD' 
  AND ((B.LEVEL_LOAD_RULE IN ('UG2YR', 'UGAS', 'AA', 'UGA') OR (W.EDUCATION_LVL = '13' AND UPPER(W.DESCRFORMAL) NOT LIKE '%CERT%')) OR A.DEGREE IS NULL)
  ) TD
  
, SYSADM.PS_PERSON_SA DZ

, ((SELECT INSTITUTION, ADMIT_TYPE, EFFDT, EFF_STATUS, DESCR
    FROM SYSADM.PS_ADMIT_TYPE_TBL P
    WHERE P.EFFDT = (SELECT MAX(PE.EFFDT) FROM SYSADM.PS_ADMIT_TYPE_TBL PE
    WHERE PE.INSTITUTION = P.INSTITUTION AND PE.ADMIT_TYPE = P.ADMIT_TYPE AND PE.EFFDT <= SYSDATE)
	) 
	PZ
   )
  WHERE A.EMPLID = B.EMPLID 
  AND A.INSTITUTION = B.INSTITUTION 
  AND A.ACAD_CAREER = B.ACAD_CAREER 
  AND A.STRM = B.UM_1ST_DEGR_TERM 
  AND B.EMPLID = SS.EMPLID 
  AND B.INSTITUTION = SS.INSTITUTION 
  AND B.ACAD_CAREER = SS.ACAD_CAREER 
  AND B.UM_1ST_DEGR_TERM = SS.UM_COHORT_STRM 
  AND A.EMPLID = TD.EMPLID(+) 
  AND A.ACAD_CAREER = TD.ACAD_CAREER(+) 
  AND A.INSTITUTION = TD.INSTITUTION(+) 
  AND A.STRM = TD.STRM(+) 
  AND A.EMPLID = DZ.EMPLID(+) 
  AND SS.INSTITUTION = PZ.INSTITUTION(+) 
  AND SS.UM_COHORT_TYPE = PZ.ADMIT_TYPE(+) 
  AND A.WITHDRAW_CODE = 'NWD' 
  AND 
  (TD.COMPLETION_TERM || TD.STDNT_DEGR = 
   ( SELECT MIN(F.COMPLETION_TERM || F.STDNT_DEGR) 
     FROM 
     ( SELECT A.EMPLID , A.ACAD_CAREER , A.INSTITUTION , 
       CASE WHEN A.DEGR_STATUS_DATE < A.DEGR_CONFER_DT AND A.DEGR_STATUS_DATE IS NOT NULL THEN A.DEGR_STATUS_DATE ELSE A.DEGR_CONFER_DT END LEAST_DEGREE_DATE 
       , A.DEGR_STATUS_DATE , A.DEGR_CONFER_DT , A.COMPLETION_TERM , A.STDNT_DEGR , A.DEGREE AS DEGREE , B.DEGREE AS PLAN_DEGREE , B.STRM , W.EDUCATION_LVL , W.DESCRFORMAL 
       FROM SYSADM.PS_ACAD_DEGR A 
       , SYSADM.PS_UM_STUD_CENS_VW B
       , PS_DEGREE_TBL W 
       WHERE A.EMPLID(+) = B.EMPLID 
       AND A.ACAD_CAREER(+) = B.ACAD_CAREER 
       AND A.INSTITUTION(+) = B.INSTITUTION 
       AND A.DEGREE = W.DEGREE 
       AND W.EFF_STATUS = 'A' 
       AND (W.EFFDT = ( SELECT MAX(W2.EFFDT) FROM PS_DEGREE_TBL W2 WHERE W2.DEGREE = W.DEGREE) OR W.EFFDT IS NULL) 
       AND A.ACAD_CAREER = 'UGRD' 
       AND ((B.LEVEL_LOAD_RULE IN ('UG2YR', 'UGAS', 'AA', 'UGA') OR (W.EDUCATION_LVL = '13' AND UPPER(W.DESCRFORMAL) NOT LIKE '%CERT%')) OR A.DEGREE IS NULL)
      ) F 
        WHERE F.EMPLID = TD.EMPLID 
        AND F.ACAD_CAREER = TD.ACAD_CAREER 
        AND F.INSTITUTION = TD.INSTITUTION 
        AND F.COMPLETION_TERM >= SS.UM_COHORT_STRM
    ) OR TD.EMPLID IS NULL
  ) 
  AND SS.ACAD_CAREER = 'UGRD' 
  AND SUBSTR(SS.UM_COHORT_STRM, 3, 2) = '10' 
  AND ss.effdt = ( SELECT MAX(wc.effdt) FROM sysadm.ps_um_stdnt_cohort wc 
  WHERE wc.emplid = ss.emplid AND wc.institution = ss.institution AND wc.acad_career = ss.acad_career AND wc.um_cohort_code = ss.um_cohort_code 
  AND wc.um_cohort_type = ss.um_cohort_type ) 
  AND ss.effseq = ( SELECT MAX(ww.effseq) FROM sysadm.ps_um_stdnt_cohort ww WHERE ww.emplid = ss.emplid 
  AND ww.institution = ss.institution AND ww.acad_career = ss.acad_career AND ww.um_cohort_code = ss.um_cohort_code 
  AND ww.um_cohort_type = ss.um_cohort_type AND ww.effdt = ss.effdt) 
  AND 
  ((SS.UM_COHORT_TYPE NOT IN ('FYR', 'EFY')) 
    OR NOT EXISTS 
	( SELECT 'k' FROM SYSADM.PS_UM_STDNT_COHORT K 
      WHERE K.EMPLID = SS.EMPLID 
      AND K.ACAD_CAREER = SS.ACAD_CAREER 
      AND K.INSTITUTION = SS.INSTITUTION 
      AND K.UM_COHORT_STRM < SS.UM_COHORT_STRM 
      AND K.UM_COHORT_CODE = 'RETENTION' 
      AND (K.UM_COHORT_TYPE = 'FYR' OR K.UM_COHORT_TYPE = 'EFY') 
      AND k.effdt = ( SELECT MAX(wc.effdt) FROM sysadm.ps_um_stdnt_cohort wc WHERE wc.emplid = k.emplid 
      AND wc.institution = k.institution AND wc.acad_career = k.acad_career AND wc.um_cohort_code = k.um_cohort_code AND wc.um_cohort_type = k.um_cohort_type ) 
      AND k.effseq = ( SELECT MAX(ww.effseq) FROM sysadm.ps_um_stdnt_cohort ww WHERE ww.emplid = k.emplid AND ww.institution = k.institution AND ww.acad_career = k.acad_career 
      AND ww.um_cohort_code = k.um_cohort_code AND ww.um_cohort_type = k.um_cohort_type AND ww.effdt = k.effdt)
    )
  ) 
  AND SS.UM_COHORT_STRM >= %Bind(UM_BEGIN_TERM) 
  AND SS.UM_COHORT_CODE = 'RETENTION'