Created by Corina C Larsen, last modified by Miki Yanagi on Sep 13, 2023
This is an efficient view of the UM_STUD_EXTRACT, which is refreshed daily except for Sundays. It returns a vast array of student demographics, and is good for all current and historical reporting and research.
| Key | Record.Fieldname | Format | XLAT | Heading Text | Notes |
|---|---|---|---|---|---|
| ACADEMIC_LOAD - Academic Load | Char1 | N | Acad Load | UM_STUD_EXTRACT | |
| ACADEMIC_LOAD_DT - Academic Load Change Date | Date | Load Date | UM_STUD_EXTRACT | ||
| → | ACAD_CAREER - Academic Career | Char4 | N | Career | UM_STUD_EXTRACT |
| ACAD_CAREER_FIRST - First Time in Career | Char1 | First Time Car | UM_STUD_EXTRACT | ||
| ACAD_GROUP - Academic Group | Char5 | Acad Group | UM_STUD_EXTRACT | ||
| ACAD_GROUP_ADVIS - Academic Group of Advisor | Char5 | Group | UM_STUD_EXTRACT | ||
| ACAD_LEVEL_BOT - Academic Level - Term Start | Char3 | Strt Level | UM_STUD_EXTRACT | ||
| ACAD_LEVEL_EOT - Academic Level - Term End | Char3 | End Level | UM_STUD_EXTRACT: Do not use | ||
| ACAD_LEVEL_PROJ - Academic Level - Projected | Char3 | Proj Level | UM_STUD_EXTRACT | ||
| ACAD_LOAD_APPR - Approved Academic Load | Char1 | N | Acad Load | UM_STUD_EXTRACT | |
| → | ACAD_PLAN - Academic Plan | Char10 | Acad Plan | UM_STUD_EXTRACT | |
| ACAD_PLAN _TYPE - Academic Plan Type | Char3 | N | Plan Type | UM_STUD_EXTRACT | |
| → | ACAD_PROG - Academic Program | Char5 | Acad Prog | UM_STUD_EXTRACT | |
| ACAD_PROG_DESCR - Academic Program | Char30 | Program | UM_STUD_EXTRACT | ||
| ACAD_PROG_DUAL - Dual Academic Program | Char5 | Dual Prog | UM_STUD_EXTRACT | ||
| ACAD_PROG_PRIMARY - Primary Academic Program | Char5 | Prim Prog | UM_STUD_EXTRACT | ||
| ACAD_SUBPLAN_TYPE - Academic Sub-Plan Type | Char3 | N | Sub-Pl Typ | UM_STUD_EXTRACT | |
| ACAD_SUB_PLAN - Academic Sub-Plan | Char10 | Sub-Plan | UM_STUD_EXTRACT | ||
| ACAD_YEAR - Academic Year | Char4 | Acad Year | UM_STUD_EXTRACT | ||
| ACTION_DT - Action Date | Date | Action Date | UM_STUD_EXTRACT | ||
| ADDRESS1 - Address Line 1 | Char55 | Address 1 | UM_STUD_EXTRACT | ||
| ADDRESS2 - Address Line 2 | Char55 | Address 2 | UM_STUD_EXTRACT | ||
| ADDRESS3 - Address Line 3 | Char55 | Address 3 | UM_STUD_EXTRACT | ||
| ADDRESS4 - Address Line 4 | Char55 | Address 4 | UM_STUD_EXTRACT | ||
| ADMISSION_EXCPT - Admission Residency Exception | Char5 | Adm Excpt | UM_STUD_EXTRACT | ||
| ADMISSION_RES - Admissions Residency | Char5 | Admissions | UM_STUD_EXTRACT | ||
| → | ADMIT_TERM - Admit Term | Char4 | Admit Term | UM_STUD_EXTRACT | |
| ADMIT_TYPE - Admit Type | Char3 | Admit Type | UM_STUD_EXTRACT | ||
| ADM_APPL_CTR - Application Center | Char4 | Appl Ctr | UM_STUD_EXTRACT | ||
| → | ADM_APPL_NBR - Application Nbr | Char8 | Appl Nbr | UM_STUD_EXTRACT | |
| ADVISOR_ID - Academic Advisor | Char11 | Advisor | UM_STUD_EXTRACT | ||
ADVIS_STATUS - Advisement Status | Char4 | N | Status | UM_STUD_EXTRACT | |
| BILLING_CAREER - Billing Career | Char4 | Bill Car | UM_STUD_EXTRACT | ||
| BIRTHDATE - Date of Birth | Date | Birthdate | UM_STUD_EXTRACT | ||
| BIRTHPLACE - Birth Location | Char30 | Birthplace | UM_STUD_EXTRACT | ||
| BIRTHSTATE - Birth State | Char6 | State | UM_STUD_EXTRACT | ||
| BIRTH_COUNTRY - BIRTH_COUNTRY | Char4 | BIRTH_COUNTRY | UM_STUD_EXTRACT | ||
| CAMPUS - Campus | Char5 | Campus | UM_STUD_EXTRACT | ||
CITIZENSHIP_STATUS - Citizenship Status | Char1 | Status | UM_STUD_EXTRACT | ||
| CITY - City | Char30 | City | UM_STUD_EXTRACT | ||
| CLASS_RANK_NBR - Class Rank Nbr | Num5.0 | Class Rank | UM_STUD_EXTRACT | ||
| CLASS_RANK_TOT - Class Rank Total | Num5.0 | Of | UM_STUD_EXTRACT | ||
| COMPLETION_TERM - Completion Term | Char4 | Compl Term | UM_STUD_EXTRACT | ||
| COUNTRY - Country | Char3 | Country | UM_STUD_EXTRACT | ||
| COUNTRY_DESCR1 - Country Description | Char30 | Country | UM_STUD_EXTRACT | ||
| COUNTY - County | Char30 | County | UM_STUD_EXTRACT | ||
| CUM_GPA - Cumulative GPA | Num6.3 | GPA | UM_STUD_EXTRACT | ||
| CUM_RESIDENT_TERMS - Cumulative In Residence Terms | Num4.3 | Cum Res | UM_STUD_EXTRACT | ||
| CUR_GPA - Current GPA | Num6.3 | GPA | UM_STUD_EXTRACT | ||
| CUR_RESIDENT_TERMS - Current In Residence Terms | Num4.3 | Cur Res | UM_STUD_EXTRACT | ||
| DECLARE_DT - Declare Date | Date | Decl Date | UM_STUD_EXTRACT | ||
| DEGREE - Degree | Char8 | Degree | UM_STUD_EXTRACT | ||
| DEGR_CHKOUT_STAT - Degree Checkout Status | Char2 | N | ChkoutStat | UM_STUD_EXTRACT | |
| DT_OF_DEATH - Date of Death | Date | Death Date | UM_STUD_EXTRACT | ||
| EDUCATION_LVL - Education Level | Char2 | N | Education | UM_STUD_EXTRACT | |
| EFFECTIVE_TERM - Effective Term | Char4 | Effective | UM_STUD_EXTRACT | ||
| ELIG_TO_ENROLL - Eligible to Enroll | Char1 | Elig Enrl | UM_STUD_EXTRACT | ||
| EMAIL_ADDR - Email Address | Char70 | UM_STUD_EXTRACT | |||
| → | EMPLID - Empl ID | Char11 | ID | UM_STUD_EXTRACT | |
| END_DATE - End Date | Date | End Date | UM_STUD_EXTRACT | ||
| ENRL_ON_TRANS_DT - Show Enrollment on Transcript | Date | Enrl Trans | UM_STUD_EXTRACT | ||
| ETHNIC_CATEGORY - Ethnic Category | Char1 | N | Ethnic Cat | this field has no data | |
| ETHNIC_GROUP - Ethnic Group | Char1 | N | Ethnic Grp | UM_STUD_EXTRACT | |
| ETHNIC_GRP_CD - Ethnic Group | Char8 | Ethnic Grp | UM_STUD_EXTRACT | ||
| EXP_GRAD_TERM - Expected Graduation Term | Char4 | Exp Grad | UM_STUD_EXTRACT | ||
| EXT_ORG_ID - External Org ID | Char11 | Org ID | UM_STUD_EXTRACT | ||
| FA_ELIGIBILITY - Program Eligibility Flag | Char1 | Eligible | UM_STUD_EXTRACT | ||
| FA_LOAD - Financial Aid Load | Char1 | N | FA Load | UM_STUD_EXTRACT | |
| FA_STATS_CALC_DTTM - FA Stats Calc Date Time | DateTm | FA DTTM | UM_STUD_EXTRACT | ||
| FA_STATS_CALC_REQ - FA Stats Calculation Required | Char1 | FA Calc | UM_STUD_EXTRACT | ||
| FIRST_NAME - First Name | Char30 | First Name | UM_STUD_EXTRACT | ||
| FIN_AID_FED_EXCPT - Fin Aid Fed Residency Excpt | Char5 | FA Fed Exc |
SQL for some FIN_AID_ fields Expand source
select
qb.residency AS um_residency_reg,
qb.tuition_res,
qb.fin_aid_fed_res,
qb.fin_aid_st_res,
qb.fin_aid_fed_excpt,
qb.fin_aid_st_excpt,
qb.tuition_excpt
FROM PS_UM_STUD_EXTRACT A, ps_residency_off qb
WHERE a.emplid = qb.emplid(+)
AND a.acad_career = qb.acad_career(+)
AND a.institution = qb.institution(+)
AND ((QB.EFFECTIVE_TERM =
(SELECT MAX(EB9.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF EB9
WHERE EB9.EMPLID = QB.EMPLID
AND EB9.ACAD_CAREER = QB.ACAD_CAREER
AND EB9.INSTITUTION = QB.INSTITUTION
AND EB9.EFFECTIVE_TERM <= A.STRM) OR
(QB.EFFECTIVE_TERM =
(SELECT MAX(EC9.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF EC9
WHERE EC9.EMPLID = QB.EMPLID
AND EC9.ACAD_CAREER = EC9.ACAD_CAREER
AND EC9.INSTITUTION = QB.INSTITUTION) AND NOT EXISTS
(SELECT 'X'
FROM PS_RESIDENCY_OFF ED9
WHERE ED9.EMPLID = QB.EMPLID
AND ED9.ACAD_CAREER = QB.ACAD_CAREER
AND ED9.INSTITUTION = QB.INSTITUTION
AND ED9.EFFECTIVE_TERM <= A.STRM))) OR
QB.EFFECTIVE_TERM IS NULL)
| ||
| FIN_AID_FED_RES - Fin Aid Federal Residency | Char5 | FinAid Fed |
SQL for some FIN_AID_ fields Expand source
select
qb.residency AS um_residency_reg,
qb.tuition_res,
qb.fin_aid_fed_res,
qb.fin_aid_st_res,
qb.fin_aid_fed_excpt,
qb.fin_aid_st_excpt,
qb.tuition_excpt
FROM PS_UM_STUD_EXTRACT A, ps_residency_off qb
WHERE a.emplid = qb.emplid(+)
AND a.acad_career = qb.acad_career(+)
AND a.institution = qb.institution(+)
AND ((QB.EFFECTIVE_TERM =
(SELECT MAX(EB9.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF EB9
WHERE EB9.EMPLID = QB.EMPLID
AND EB9.ACAD_CAREER = QB.ACAD_CAREER
AND EB9.INSTITUTION = QB.INSTITUTION
AND EB9.EFFECTIVE_TERM <= A.STRM) OR
(QB.EFFECTIVE_TERM =
(SELECT MAX(EC9.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF EC9
WHERE EC9.EMPLID = QB.EMPLID
AND EC9.ACAD_CAREER = EC9.ACAD_CAREER
AND EC9.INSTITUTION = QB.INSTITUTION) AND NOT EXISTS
(SELECT 'X'
FROM PS_RESIDENCY_OFF ED9
WHERE ED9.EMPLID = QB.EMPLID
AND ED9.ACAD_CAREER = QB.ACAD_CAREER
AND ED9.INSTITUTION = QB.INSTITUTION
AND ED9.EFFECTIVE_TERM <= A.STRM))) OR
QB.EFFECTIVE_TERM IS NULL)
| ||
| FIN_AID_ST_EXCPT - Fin Aid St Residency Excpt | Char5 | FA St Excp |
SQL for some FIN_AID_ fields Expand source
select
qb.residency AS um_residency_reg,
qb.tuition_res,
qb.fin_aid_fed_res,
qb.fin_aid_st_res,
qb.fin_aid_fed_excpt,
qb.fin_aid_st_excpt,
qb.tuition_excpt
FROM PS_UM_STUD_EXTRACT A, ps_residency_off qb
WHERE a.emplid = qb.emplid(+)
AND a.acad_career = qb.acad_career(+)
AND a.institution = qb.institution(+)
AND ((QB.EFFECTIVE_TERM =
(SELECT MAX(EB9.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF EB9
WHERE EB9.EMPLID = QB.EMPLID
AND EB9.ACAD_CAREER = QB.ACAD_CAREER
AND EB9.INSTITUTION = QB.INSTITUTION
AND EB9.EFFECTIVE_TERM <= A.STRM) OR
(QB.EFFECTIVE_TERM =
(SELECT MAX(EC9.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF EC9
WHERE EC9.EMPLID = QB.EMPLID
AND EC9.ACAD_CAREER = EC9.ACAD_CAREER
AND EC9.INSTITUTION = QB.INSTITUTION) AND NOT EXISTS
(SELECT 'X'
FROM PS_RESIDENCY_OFF ED9
WHERE ED9.EMPLID = QB.EMPLID
AND ED9.ACAD_CAREER = QB.ACAD_CAREER
AND ED9.INSTITUTION = QB.INSTITUTION
AND ED9.EFFECTIVE_TERM <= A.STRM))) OR
QB.EFFECTIVE_TERM IS NULL)
| ||
| FIN_AID_ST_RES - Fin Aid State Residency | Char5 | FA St Excp |
SQL for some FIN_AID_ fields Expand source
select
qb.residency AS um_residency_reg,
qb.tuition_res,
qb.fin_aid_fed_res,
qb.fin_aid_st_res,
qb.fin_aid_fed_excpt,
qb.fin_aid_st_excpt,
qb.tuition_excpt
FROM PS_UM_STUD_EXTRACT A, ps_residency_off qb
WHERE a.emplid = qb.emplid(+)
AND a.acad_career = qb.acad_career(+)
AND a.institution = qb.institution(+)
AND ((QB.EFFECTIVE_TERM =
(SELECT MAX(EB9.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF EB9
WHERE EB9.EMPLID = QB.EMPLID
AND EB9.ACAD_CAREER = QB.ACAD_CAREER
AND EB9.INSTITUTION = QB.INSTITUTION
AND EB9.EFFECTIVE_TERM <= A.STRM) OR
(QB.EFFECTIVE_TERM =
(SELECT MAX(EC9.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF EC9
WHERE EC9.EMPLID = QB.EMPLID
AND EC9.ACAD_CAREER = EC9.ACAD_CAREER
AND EC9.INSTITUTION = QB.INSTITUTION) AND NOT EXISTS
(SELECT 'X'
FROM PS_RESIDENCY_OFF ED9
WHERE ED9.EMPLID = QB.EMPLID
AND ED9.ACAD_CAREER = QB.ACAD_CAREER
AND ED9.INSTITUTION = QB.INSTITUTION
AND ED9.EFFECTIVE_TERM <= A.STRM))) OR
QB.EFFECTIVE_TERM IS NULL)
| ||
| FORM_OF_STUDY - Form of Study | Char4 | N | Study Form | UM_STUD_EXTRACT | |
| FTE - FTE | Num2.6 | FTE |
Calculation for FTE (Alias A= UM_STUD_EXTRACT) Expand source
A.Um_Unt_Tak_Prg_Rc / (CASE
WHEN A.Institution = 'UMS02' AND A.Acad_plan = 'PSY-MA' THEN 15
WHEN A.Acad_Level_Bot IN ('GR', 'MAS', 'PHD') THEN 9
WHEN A.Acad_Level_Bot IN ('L1', 'L2', 'L3') THEN 15
WHEN A.Institution = 'UMS02' AND STRM < '2410' THEN 16
ELSE 15 END)
| ||
| FULLY_ENRL_DT - Fully Enrolled Date | Date | Full Enrl | UM_STUD_EXTRACT | ||
| FULLY_GRADED_DT - Fully Graded Date | Date | Full Grded | UM_STUD_EXTRACT | ||
| GRADE_POINTS - Grade Points | Num7.3 | Grd Points | UM_STUD_EXTRACT | ||
| GRADE_POINTS_FA - Financial Aid Grade Points | Num7.3 | FA Grd Pt | UM_STUD_EXTRACT | ||
| GRADUATION_DT - Graduation Date | Date | Grad Dt | High school graduation date | ||
| HEADCOUNT - Headcount | Num6.0 | Headcount | 1 (put '1' on each data) | ||
| HISP_LATINO - Person is Hispanic or Latino | Char1 | Hisp or Latin | Y or N | ||
| → | INSTITUTION - Academic Institution | Char5 | Institution | UM_STUD_EXTRACT | |
| INSTITUTION_DESCR - Institution | Char30 | Institution | UM_STUD_EXTRACT | ||
| JOINT_PROG_APPR - Joint Program Approved | Char1 | Joint Program | UM_STUD_EXTRACT | ||
| LAST_DATE_ATTENDED - Last Date of Attendance | Date | Last Date | UM_STUD_EXTRACT | ||
| LAST_NAME - Last Name | Char30 | Last | UM_STUD_EXTRACT | ||
| LEVEL_LOAD_RULE - Academic Level Rule | Char5 | Level Rule | UM_STUD_EXTRACT | ||
| LOCK_IN_AMT - Lock In Amount | SNm15.2 | Amount | UM_STUD_EXTRACT | ||
| LOCK_IN_DT - Lock In Date | Date | Date | UM_STUD_EXTRACT | ||
| MAR_STATUS - Marital Status | Char1 | N | Mar Status | UM_STUD_EXTRACT | |
| MAR_STATUS_DT - Marital Status Date | Date | Mar StatDt | UM_STUD_EXTRACT | ||
| MAX_AUDIT_UNIT - Max Audit Units | Num4.2 | Max Audit | UM_STUD_EXTRACT | ||
| MAX_CRSE_COUNT - Max Total Courses | Num3.0 | Total Crse | UM_STUD_EXTRACT | ||
| MAX_NOGPA_UNIT - Max No GPA Units | Num4.2 | Max Grd Pt | UM_STUD_EXTRACT | ||
| MAX_TOTAL_UNIT - Max Total Units | Num4.2 | Max Units | UM_STUD_EXTRACT | ||
| MAX_WAIT_UNIT - Max Wait List Units | Num4.2 | Max Wait | UM_STUD_EXTRACT | ||
| MIDDLE_NAME - Middle Name | Char30 | Middle | UM_STUD_EXTRACT | ||
| MIN_TOTAL_UNIT - Min Total Units | Num4.2 | Min Units | UM_STUD_EXTRACT | ||
| NAME - Name | Char50 | Name | UM_STUD_EXTRACT | ||
| NAME_PREFIX - Name Prefix | Char4 | Prefix | UM_STUD_EXTRACT | ||
| NAME_SUFFIX - Name Suffix | Char15 | Suffix | UM_STUD_EXTRACT | ||
| NSLDS_LOAN_YEAR - NSLDS Loan Year | Char1 | N | Loan Year | UM_STUD_EXTRACT | |
| OVRD_ACAD_LVL_ALL - Override All Academic Levels | Char1 | Ovrd Lvls | UM_STUD_EXTRACT | ||
| OVRD_ACAD_LVL_PROJ - Override Projected Level | Char1 | Ovrd Level | UM_STUD_EXTRACT | ||
| OVRD_BILL_UNITS - Override Billing Units | Char1 | Ovrd Bill | UM_STUD_EXTRACT | ||
| OVRD_INIT_ADD_FEE - Override Initial Add Fees | Char1 | Override Fee | UM_STUD_EXTRACT | ||
| OVRD_INIT_ENR_FEE - Override Initial Enroll Fee | Char1 | Override Fee | UM_STUD_EXTRACT | ||
| OVRD_MAX_UNITS - Override Maximum Units | Char1 | Ovrd Units | UM_STUD_EXTRACT | ||
| OVRD_TUIT_GROUP - Override Tuition Group | Char10 | Ov Tuit Gp | UM_STUD_EXTRACT | ||
| OVRD_WDRW_SCHED - Override Withdrawal Schedule | Char8 | Ov Wd Schd | UM_STUD_EXTRACT | ||
| PHONE - Telephone | Char24 | Phone | UM_STUD_EXTRACT | ||
| PIN_NUM - PIN Number | Num8.0 | PIN Number | UM_STUD_EXTRACT | ||
| PLAN_SEQUENCE - Plan Sequence | Num2.0 | Plan Seq | UM_STUD_EXTRACT | ||
| POSTAL - Postal Code | Char12 | Postal | UM_STUD_EXTRACT | ||
| PROG_ACTION - Program Action | Char4 | N | Prog Actn | UM_STUD_EXTRACT | |
| PROG_REASON - Action Reason | Char4 | Action Rsn | UM_STUD_EXTRACT | ||
| PROG_STATUS - Academic Program Status | Char4 | N | Status | UM_STUD_EXTRACT | |
| PROJ_BILL_UNT - Projected Bill Units | Num3.0 | Bill Units | UM_STUD_EXTRACT | ||
| PRO_RATA_ELIGIBLE - Pro-Rata Eligible | Char1 | Eligible | UM_STUD_EXTRACT | ||
| REFUND_PCT - Refund Percentage | Num4.2 | Refund % | UM_STUD_EXTRACT | ||
| REFUND_SCHEME - Refund Scheme | Char8 | Scheme | UM_STUD_EXTRACT | ||
| REGISTERED - Registered | Char1 | N | Registered | UM_STUD_EXTRACT | |
| REG_CARD_DATE - Registration Card Date | Date | Reg Card | UM_STUD_EXTRACT | ||
| REQ_TERM - Requirement Term | Char4 | Req Term | UM_STUD_EXTRACT | ||
| RESET_CUM_STATS - Reset Cum Stats at Term Start | Char1 | Cum Reset | UM_STUD_EXTRACT | ||
| RESIDENCY - Tuition Residency | Char5 | Tuition Resid | UM_STUD_EXTRACT | ||
| RESIDENCY_DT - Residency Date | Date | Res Date | UM_STUD_EXTRACT | ||
| RESPONSE_DT - Response Date | Date | Date | UM_STUD_EXTRACT | ||
| RESPONSE_REASON - Response Reason | Char4 | Reason | UM_STUD_EXTRACT | ||
| ROW_CHECK - ROW CHECK | Char1 | ROW CHECK | UM_STUD_EXTRACT | ||
| SCHOOL_CODE - School Code | Char10 | School Cd |
Calculation for SCHOOL_CODE (Alias A= UM_STUD_EXTRACT) Expand source
DECODE(A.Institution,
'UMS01',
'006760',
'UMS02',
'002040',
'UMS03',
'002041',
'UMS04',
'002055',
'UMS05',
'002053',
'UMS06',
'002054',
'UMS07',
'002033',
' ')
| ||
| SEL_GROUP - Tuition Group | Char10 | Group | UM_STUD_EXTRACT | ||
| SEX - Gender | Char1 | N | Sex | UM_STUD_EXTRACT | |
| SSR_ACTIVATION_DT - Term Activation Date | Date | Activation Date | UM_STUD_EXTRACT | ||
| START_DATE - Start Date for Gen Standing PO | Date | Start Date | UM_STUD_EXTRACT | ||
| STATE - State | Char6 | State | UM_STUD_EXTRACT | ||
| STATE_DESCR1 - State Description | Char30 | State | UM_STUD_EXTRACT | ||
| STATS_ON_TRANS_DT - Show Statistics on Transcript | Date | Stat Trans | UM_STUD_EXTRACT | ||
| → | STDNT_CAR_NBR - Student Career Nbr | Num3.0 | Career Nbr | UM_STUD_EXTRACT | |
| STDNT_DEGR - Student Degree Nbr | Char2 | Degree Nbr | UM_STUD_EXTRACT | ||
| → | STRM - Term | Char4 | Term | UM_STUD_EXTRACT | |
| STUDY_AGREEMENT - Study Agreement | Char10 | Agreement | UM_STUD_EXTRACT | ||
| TC_UNITS_ADJUST - TC Units Adjustment | Num6.3 | TC Units Adjust | UM_STUD_EXTRACT | ||
| TERM_TYPE - Term Unit Type | Char1 | N | Term Type | UM_STUD_EXTRACT | |
| TOT_AUDIT - Total Audited | Num6.3 | Audit | UM_STUD_EXTRACT | ||
| TOT_CUMULATIVE - Total Cumulative Units | Num6.3 | Total | UM_STUD_EXTRACT | ||
| TOT_GRADE_POINTS - Total Grade Points | Num7.3 | Grade Points | UM_STUD_EXTRACT | ||
| TOT_GRD_POINTS_FA - Total Fin Aid Grade Points | Num7.3 | Tot FA Grd | UM_STUD_EXTRACT | ||
| TOT_INPROG_GPA - Total In Progress - GPA | Num6.3 | In Pr GPA | UM_STUD_EXTRACT | ||
| TOT_INPROG_NOGPA - Total In Progress -Not for GPA | Num6.3 | InPr NoGPA | UM_STUD_EXTRACT | ||
| TOT_OTHER - Total from Other Credit | Num6.3 | Other | UM_STUD_EXTRACT | ||
| TOT_PASSD_FA - Total Fin Aid Units Passed | Num6.3 | Pass FA | UM_STUD_EXTRACT | ||
| TOT_PASSD_GPA - Total Passed Toward GPA | Num6.3 | Pass GPA | UM_STUD_EXTRACT | ||
| TOT_PASSD_NOGPA - Total Passed Not Toward GPA | Num6.3 | PassNoGPA | UM_STUD_EXTRACT | ||
| TOT_PASSD_PRGRSS - Total Passed for Progress | Num6.3 | Pass Prgrs | UM_STUD_EXTRACT | ||
| TOT_TAKEN_FA - Total Fin Aid Units Taken | Num6.3 | Take FA | UM_STUD_EXTRACT | ||
| TOT_TAKEN_FA_GPA - Total Fin Aid Taken Toward GPA | Num6.3 | Tot FA GPA | UM_STUD_EXTRACT | ||
| TOT_TAKEN_GPA - Total Taken Toward GPA | Num6.3 | Take GPA | UM_STUD_EXTRACT | ||
| TOT_TAKEN_NOGPA - Total Taken Not Toward GPA | Num6.3 | TakeNoGPA | UM_STUD_EXTRACT | ||
| TOT_TAKEN_PRGRSS - Total Taken for Progress | Num6.3 | Take Prgrs | UM_STUD_EXTRACT | ||
| TOT_TEST_CREDIT - Total From Test Credit | Num6.3 | Test | UM_STUD_EXTRACT | ||
| TOT_TRNSFR - Total Transferred | Num6.3 | Transfer | UM_STUD_EXTRACT | ||
| TRF_GRADE_POINTS - Transfer Grade Points | Num7.3 | Trnsfr Grd Pts | UM_STUD_EXTRACT | ||
| TRF_PASSED_GPA - Transfer Passed for GPA | Num6.3 | TrnPasGPA | UM_STUD_EXTRACT | ||
| TRF_PASSED_NOGPA - Transfer Passed Not for GPA | Num6.3 | TrnPasNGPA | UM_STUD_EXTRACT | ||
| TRF_RESIDENT_TERMS - Transfer In Residence Terms | Num4.3 | Trf Res | UM_STUD_EXTRACT | ||
| TRF_TAKEN_GPA - Transfer Taken for GPA | Num6.3 | TrnTakGPA | UM_STUD_EXTRACT | ||
| TRF_TAKEN_NOGPA - Transfer Taken Not for GPA | Num6.3 | TrnTakNGPA | UM_STUD_EXTRACT | ||
| TUITION_RES_TERMS - Tuition Residency | Num4.3 | Tuition Res. | UM_STUD_EXTRACT | ||
| TUIT_CALC_DTTM - Tuit Calc Date Time | DateTm | TuitDTTM | UM_STUD_EXTRACT | ||
| TUIT_CALC_REQ - Tuition Calc Required | Char1 | Calc Req | UM_STUD_EXTRACT | ||
| TUITION_EXCPT - Tuition Residency Exception | Char5 | Tuit Excpt |
SQL for some FIN_AID_ fields Expand source
select
qb.residency AS um_residency_reg,
qb.tuition_res,
qb.fin_aid_fed_res,
qb.fin_aid_st_res,
qb.fin_aid_fed_excpt,
qb.fin_aid_st_excpt,
qb.tuition_excpt
FROM PS_UM_STUD_EXTRACT A, ps_residency_off qb
WHERE a.emplid = qb.emplid(+)
AND a.acad_career = qb.acad_career(+)
AND a.institution = qb.institution(+)
AND ((QB.EFFECTIVE_TERM =
(SELECT MAX(EB9.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF EB9
WHERE EB9.EMPLID = QB.EMPLID
AND EB9.ACAD_CAREER = QB.ACAD_CAREER
AND EB9.INSTITUTION = QB.INSTITUTION
AND EB9.EFFECTIVE_TERM <= A.STRM) OR
(QB.EFFECTIVE_TERM =
(SELECT MAX(EC9.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF EC9
WHERE EC9.EMPLID = QB.EMPLID
AND EC9.ACAD_CAREER = EC9.ACAD_CAREER
AND EC9.INSTITUTION = QB.INSTITUTION) AND NOT EXISTS
(SELECT 'X'
FROM PS_RESIDENCY_OFF ED9
WHERE ED9.EMPLID = QB.EMPLID
AND ED9.ACAD_CAREER = QB.ACAD_CAREER
AND ED9.INSTITUTION = QB.INSTITUTION
AND ED9.EFFECTIVE_TERM <= A.STRM))) OR
QB.EFFECTIVE_TERM IS NULL)
| ||
| UM_TUITION_RES - Tuition Residency | Char5 | Tuition |
SQL for some FIN_AID_ fields Expand source
select
qb.residency AS um_residency_reg,
qb.tuition_res,
qb.fin_aid_fed_res,
qb.fin_aid_st_res,
qb.fin_aid_fed_excpt,
qb.fin_aid_st_excpt,
qb.tuition_excpt
FROM PS_UM_STUD_EXTRACT A, ps_residency_off qb
WHERE a.emplid = qb.emplid(+)
AND a.acad_career = qb.acad_career(+)
AND a.institution = qb.institution(+)
AND ((QB.EFFECTIVE_TERM =
(SELECT MAX(EB9.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF EB9
WHERE EB9.EMPLID = QB.EMPLID
AND EB9.ACAD_CAREER = QB.ACAD_CAREER
AND EB9.INSTITUTION = QB.INSTITUTION
AND EB9.EFFECTIVE_TERM <= A.STRM) OR
(QB.EFFECTIVE_TERM =
(SELECT MAX(EC9.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF EC9
WHERE EC9.EMPLID = QB.EMPLID
AND EC9.ACAD_CAREER = EC9.ACAD_CAREER
AND EC9.INSTITUTION = QB.INSTITUTION) AND NOT EXISTS
(SELECT 'X'
FROM PS_RESIDENCY_OFF ED9
WHERE ED9.EMPLID = QB.EMPLID
AND ED9.ACAD_CAREER = QB.ACAD_CAREER
AND ED9.INSTITUTION = QB.INSTITUTION
AND ED9.EFFECTIVE_TERM <= A.STRM))) OR
QB.EFFECTIVE_TERM IS NULL)
| ||
| UM_ACADEMIC_YR - Academic Year | Char12 | Academic Year |
Calculation for UM_ACADEMIC_YR (Alias A = UM_STUD_EXTRACT) Expand source
CASE
WHEN A.Acad_Level_Bot = '10' THEN
'First Year'
WHEN A.Acad_Level_Bot = '01' THEN
'First Year'
WHEN A.Acad_Level_Bot = '20' THEN
'Second Year'
WHEN A.Acad_Level_Bot = '02' THEN
'Second Year'
WHEN A.Acad_Level_Bot = '30' THEN
'Third Year'
WHEN A.Acad_Level_Bot = '03' THEN
'Third Year'
WHEN A.Acad_Level_Bot = '40' THEN
'Fourth Year'
WHEN A.Acad_Level_Bot = '04' THEN
'Fourth Year'
WHEN A.Acad_Level_Bot = '50' THEN
'Fifth Year'
WHEN A.Acad_Level_Bot = '05' THEN
'Fifth Year'
WHEN A.Acad_Level_Bot = 'GR' THEN
'Graduate'
WHEN A.Acad_Level_Bot = 'MAS' THEN
'Graduate'
WHEN A.Acad_Level_Bot = 'PHD' THEN
'Graduate'
WHEN A.Acad_Level_Bot = 'L1' THEN
'Law First Year'
WHEN A.Acad_Level_Bot = 'L2' THEN
'Law Second Year'
WHEN A.Acad_Level_Bot = 'L3' THEN
'Law Third Year'
ELSE
'Other'
END
| ||
| UM_ACADEMIC_YR_SRT - Academic Year Sort | Char2 | Academic Yr Srt |
Calculation for UM_ACADEMIC_YR_SRT (Alias A = UM_STUD_EXTRACT) Expand source
CASE
WHEN A.ACAD_LEVEL_BOT = '10' THEN
'10'
WHEN A.ACAD_LEVEL_BOT = '01' THEN
'10'
WHEN A.ACAD_LEVEL_BOT = '20' THEN
'11'
WHEN A.ACAD_LEVEL_BOT = '02' THEN
'11'
WHEN A.ACAD_LEVEL_BOT = '30' THEN
'12'
WHEN A.ACAD_LEVEL_BOT = '03' THEN
'12'
WHEN A.ACAD_LEVEL_BOT = '40' THEN
'13'
WHEN A.ACAD_LEVEL_BOT = '04' THEN
'13'
WHEN A.ACAD_LEVEL_BOT = '50' THEN
'14'
WHEN A.ACAD_LEVEL_BOT = '05' THEN
'14'
WHEN A.ACAD_LEVEL_BOT = 'GR' THEN
'15'
WHEN A.ACAD_LEVEL_BOT = 'MAS' THEN
'15'
WHEN A.ACAD_LEVEL_BOT = 'PHD' THEN
'15'
WHEN A.ACAD_LEVEL_BOT = 'L1' THEN
'16'
WHEN A.ACAD_LEVEL_BOT = 'L2' THEN
'17'
WHEN A.ACAD_LEVEL_BOT = 'L3' THEN
'18'
ELSE
'20'
END
| ||
| UM_ACAD_GRP_DESCR - Academic Group Descr | Char30 | Academic Group | UM_STUD_EXTRACT | ||
| UM_ACAD_LOAD_RC - Calculated Academic Load | Char1 | N | Calc Acad Load | UM_STUD_EXTRACT | |
| UM_ACAD_LVL_DESCR - Academic Level Description | Char30 | Acad Level Desc | UM_STUD_EXTRACT | ||
| UM_ACAD_PLAN_DESCR - Academic Plan Description | Char30 | Acad Plan Descr | UM_STUD_EXTRACT | ||
| UM_ACAD_PROGP_DSC - Acad Prog Primary Descr | Char30 | Acd Prg Prm Dsc | UM_STUD_EXTRACT | ||
| UM_ACAD_SUBPLN_DSC - Academic Sub Plan Description | Char30 | Acad Sub Descr | UM_STUD_EXTRACT | ||
| UM_ADMIT_TERM_YEAR - Admit Term Year | Char4 | Admit Term Year |
Calculation for UM_ADMIT_TERM_YEAR (Alias A = UM_STUD_EXTRACT) Expand source
CASE
WHEN A.ADMIT_TERM IS NULL THEN 0
WHEN SUBSTR(A.ADMIT_TERM, 1, 2) < '04' THEN 0
WHEN SUBSTR(A.ADMIT_TERM, 3, 1) = '1' THEN
TO_NUMBER('20' || SUBSTR(A.ADMIT_TERM, 1, 2), '9999') - 1
ELSE
TO_NUMBER('20' || SUBSTR(A.ADMIT_TERM, 1, 2), '9999')
END
| ||
| UM_ADMIT_TYPE_DESC - Admit Type Description | Char50 | Admit Type Desc | Please go to Degree/Non-Degree Coding - Current page for the code | ||
| UM_ADMIT_TYPE_D_ND - Admit Type Descr Non Degree | Char50 | Admit Type D ND | Please go to Degree/Non-Degree Coding - Current page for the code | ||
| UM_ADMIT_TYPE_SORT - Admit Type Sort | Char2 | Admit Type Sort | Please go to Degree/Non-Degree Coding - Current page for the code | ||
| UM_ADVISOR_NAME_FM - Advisor Name Formatted | Char50 | Advisor Name F |
Calculation for UM_ADVISOR_NAME_FM (Alias A = UM_STUD_EXTRACT) Expand source
CASE
WHEN A.Um_Advisr_Name_Sfx <= ' ' AND A.Um_Advisr_Lst_Name > ' ' THEN
A.Um_Advisr_Lst_Name || ', ' || A.Um_Advisr_Fst_Name || '' ||
A.Um_Advisr_Mid_Name
WHEN A.Um_Advisr_Lst_Name > ' ' THEN
A.Um_Advisr_Lst_Name || '' || A.Um_Advisr_Name_Sfx
ELSE
'' || ', ' || A.Um_Advisr_Fst_Name || '' || A.Um_Advisr_Mid_Name
END
| ||
| UM_ADVISR_FST_NAME - Advisor First Name | Char30 | Advisr Fst Name | UM_STUD_EXTRACT | ||
| UM_ADVISR_LST_NAME - Advisor Last Name | Char30 | Advisr Lst Name | UM_STUD_EXTRACT | ||
| UM_ADVISR_MID_NAME - Advisor Middle Name | Char30 | Advisr Mid Name | UM_STUD_EXTRACT | ||
| UM_ADVISR_NAME_SFX - Advisor Name Suffix | Char15 | Advisr Name Sfx | UM_STUD_EXTRACT | ||
| UM_ANY_COUNTRY - Any Country | Char50 | Any Country |
Calculation for UM_ANY_COUNTRY (Alias A = UM_STUD_EXTRACT) Expand source
CASE
WHEN A.Citizenship_Status = '4' AND
(A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Citizenship_Status = '4' AND A.Residency = 'CAN' THEN
'Canada'
WHEN A.Citizenship_Status = '4' AND
(A.Country_Descr1 = 'United States' AND A.Um_Country_Oth > ' ') THEN
A.Um_Country_Oth
WHEN A.Citizenship_Status = '4' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Citizenship_Status = '4' THEN
'Other Country'
WHEN A.Residency = 'CAN' THEN
'Canada'
WHEN A.Residency = 'INTNL' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Residency = 'INTNL' AND
(A.Country_Descr1 = 'United States' AND A.Um_Country_Oth > ' ') THEN
A.Um_Country_Oth
WHEN A.Residency = 'INTNL' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Residency = 'INTNL' THEN
'Other Country'
WHEN A.Residency = 'OS' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Residency = 'OS' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Residency = 'NEBHE' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
Country_Descr1
WHEN A.Residency = 'NEBHE' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Country <> 'USA' AND A.Country > ' ' THEN
A.Country
ELSE
'United States'
END
| ||
| UM_CALC_COUNTRY - Calculated Country | Char50 | Calc Country |
Calculation for UM_CALC_COUNTRY (Alias A = UM_STUD_EXTRACT) Expand source
CASE
WHEN A.Citizenship_Status = '1' THEN
'United States'
WHEN A.Citizenship_Status = '2' THEN
'United States'
WHEN A.Citizenship_Status = '3' THEN
'United States'
WHEN A.Citizenship_Status = '4' AND
(A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Citizenship_Status = '4' AND A.Residency = 'CAN' THEN
'Canada'
WHEN A.Citizenship_Status = '4' AND
(A.Country_Descr1 = 'United States' AND A.Um_Country_Oth > ' ') THEN
A.Um_Country_Oth
WHEN A.Citizenship_Status = '4' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Citizenship_Status = '4' THEN
'Other Country'
WHEN A.Residency = 'CAN' THEN
'Canada'
WHEN A.Residency = 'INTNL' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Residency = 'INTNL' AND
(A.Country_Descr1 = 'United States' AND A.Um_Country_Oth > ' ') THEN
A.Um_Country_Oth
WHEN A.Residency = 'INTNL' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Residency = 'INTNL' THEN
'Other Country'
WHEN A.Residency = 'OS' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Residency = 'OS' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Residency = 'NEBHE' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Residency = 'NEBHE' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Country <> 'USA' AND A.Country > ' ' THEN
A.Country
ELSE
'United States'
END
| ||
| UM_CALC_COUNTY - Calculated County | Char50 | Calc County |
Calculation for UM_CALC_COUNTY (Alias A = UM_STUD_EXTRACT) Expand source
CASE
WHEN A.County <= ' ' AND A.Institution = 'UMS01' THEN
'Kennebec'
WHEN A.County <= ' ' AND A.Institution = 'UMS02' THEN
'Franklin'
WHEN A.County <= ' ' AND A.Institution = 'UMS03' THEN
'Aroostook'
WHEN A.County <= ' ' AND A.Institution = 'UMS04' THEN
'Washington'
WHEN A.County <= ' ' AND A.Institution = 'UMS05' THEN
'Penobscot'
WHEN A.County <= ' ' AND A.Institution = 'UMS06' THEN
'Cumberland'
WHEN A.County <= ' ' AND A.Institution = 'UMS07' THEN
'Aroostook'
WHEN A.County <= ' ' THEN
'Unknown County'
WHEN LOWER(A.County) LIKE '%dros%' THEN
'Androscoggin'
WHEN LOWER(A.County) LIKE '%aroos%' THEN
'Aroostook'
WHEN LOWER(A.County) LIKE '%cumber%' THEN
'Cumberland'
WHEN LOWER(A.County) LIKE '%frank%' THEN
'Franklin'
WHEN LOWER(A.County) LIKE '%hanc%' THEN
'Hancock'
WHEN LOWER(A.County) LIKE '%bec' THEN
'Kennebec'
WHEN LOWER(A.County) LIKE '%knox' THEN
'Knox'
WHEN LOWER(A.County) LIKE '%lincoln' THEN
'Lincoln'
WHEN LOWER(A.County) LIKE '%oxford' THEN
'Oxford'
WHEN LOWER(A.County) LIKE '%penob%' THEN
'Penobscot'
WHEN LOWER(A.County) LIKE '%piscat%' THEN
'Piscataquis'
WHEN LOWER(A.County) LIKE '%saga%' THEN
'Sagadahoc'
WHEN LOWER(A.County) LIKE '%somer%' THEN
'Somerset'
WHEN LOWER(A.County) LIKE '%waldo' THEN
'Waldo'
WHEN LOWER(A.County) LIKE '%wash%' THEN
'Washington'
WHEN LOWER(A.County) LIKE '%york' THEN
'York'
WHEN A.County NOT IN ('Androscoggin',
'Aroostook',
'Cumberland',
'Franklin',
'Hancock',
'Kennebec',
'Knox',
'Lincoln',
'Oxford',
'Penobscot',
'Piscataquis',
'Sagadahoc',
'Somerset',
'Waldo',
'Washington',
'York') THEN
'Unknown County'
ELSE
A.County
END
| ||
| UM_CALC_STATE - Calculated State | Char50 | Calculate State |
Calculation for UM_CALC_STATE (Alias A = UM_STUD_EXTRACT) Expand source
CASE
WHEN CASE
WHEN A.Citizenship_Status = '1' THEN
'United States'
WHEN A.Citizenship_Status = '2' THEN
'United States'
WHEN A.Citizenship_Status = '3' THEN
'United States'
WHEN A.Citizenship_Status = '4' AND
(A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Citizenship_Status = '4' AND A.Residency = 'CAN' THEN
'Canada'
WHEN A.Citizenship_Status = '4' AND
(A.Country_Descr1 = 'United States' AND A.Um_Country_Oth > ' ') THEN
A.Um_Country_Oth
WHEN A.Citizenship_Status = '4' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Citizenship_Status = '4' THEN
'Other Country'
WHEN A.Residency = 'CAN' THEN
'Canada'
WHEN A.Residency = 'INTNL' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Residency = 'INTNL' AND
(A.Country_Descr1 = 'United States' AND A.Um_Country_Oth > ' ') THEN
A.Um_Country_Oth
WHEN A.Residency = 'INTNL' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Residency = 'INTNL' THEN
'Other Country'
WHEN A.Residency = 'OS' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Residency = 'OS' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Residency = 'NEBHE' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Residency = 'NEBHE' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Country <> 'USA' AND A.Country > ' ' THEN
A.Country
ELSE
'United States'
END <> 'United States' THEN
'Non-USA'
WHEN A.Residency = 'OS' AND A.State <= ' ' THEN
'Other State'
WHEN A.Residency = 'NEBHE' AND A.State <= ' ' THEN
'Other State'
WHEN A.State <> 'ME' AND A.State_Descr1 > ' ' THEN
A.State_Descr1
WHEN A.State = 'NH' THEN
'New Hampshire'
WHEN A.State = 'VT' THEN
'Vermont'
WHEN A.State = 'MA' THEN
'Massachusetts'
WHEN A.State = 'CT' THEN
'Connecticut'
WHEN A.State = 'RI' THEN
'Rhode Island'
WHEN A.State = 'ME' THEN
'Maine'
WHEN A.State <> 'ME' AND A.State_Descr1 <= ' ' THEN
'Other State'
WHEN A.Residency = 'IS' THEN
'Maine'
ELSE
'Other State'
END
| ||
| UM_CANADA - UM Canada | Char50 | UM Canada |
Calculation for UM_CANADA (Alias A = UM_STUD_EXTRACT) Expand source
CASE
WHEN CASE
WHEN A.Citizenship_Status = '1' THEN
'United States'
WHEN A.Citizenship_Status = '2' THEN
'United States'
WHEN A.Citizenship_Status = '3' THEN
'United States'
WHEN A.Citizenship_Status = '4' AND
(A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Citizenship_Status = '4' AND A.Residency = 'CAN' THEN
'Canada'
WHEN A.Citizenship_Status = '4' AND
(A.Country_Descr1 = 'United States' AND A.Um_Country_Oth > ' ') THEN
A.Um_Country_Oth
WHEN A.Citizenship_Status = '4' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Citizenship_Status = '4' THEN
'Other Country'
WHEN A.Residency = 'CAN' THEN
'Canada'
WHEN A.Residency = 'INTNL' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Residency = 'INTNL' AND
(A.Country_Descr1 = 'United States' AND A.Um_Country_Oth > ' ') THEN
A.Um_Country_Oth
WHEN A.Residency = 'INTNL' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Residency = 'INTNL' THEN
'Other Country'
WHEN A.Residency = 'OS' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Residency = 'OS' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Residency = 'NEBHE' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Residency = 'NEBHE' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Country <> 'USA' AND A.Country > ' ' THEN
A.Country
ELSE
'United States'
END IN ('Canada', 'CAN') THEN
CASE
WHEN A.State = 'AB' THEN
'Alberta'
WHEN A.State = 'BC' THEN
'British Columbia'
WHEN A.State = 'MB' THEN
'Manitoba'
WHEN A.State = 'NB' THEN
'New Brunswick'
WHEN A.State = 'NF' THEN
'Newfoundland'
WHEN A.State = 'NL' THEN
'Newfoundland'
WHEN A.State = 'NS' THEN
'Nova Scotia'
WHEN A.State = 'NT' THEN
'Northwest Territories'
WHEN A.State = 'NN' THEN
'Nunavut'
WHEN A.State = 'NU' THEN
'Nunavut'
WHEN A.State = 'ON' THEN
'Ontario'
WHEN A.State = 'PE' THEN
'Prince Edward Island'
WHEN A.State = 'QC' THEN
'Quebec'
WHEN A.State = 'SK' THEN
'Saskatchewan'
WHEN A.State = 'YT' THEN
'Yukon'
ELSE
'Canada'
END
ELSE
'Not Canada'
END
| ||
| UM_CITIZEN_STAT_OT - Citizenship Status Other | Char1 | Citizn Stat Oth | This field is essentially unreliable for students with more than 1 foreign country listed in the CITIZENSHIP table. | ||
| UM_CITIZEN_ST_DESC - Citizenship Status Description | Char20 | Citizen St Desc |
Calculation for UM_CITIZEN_ST_DESC (Alias A = UM_STUD_EXTRACT) Expand source
CASE
WHEN A.Citizenship_Status = '4' THEN
'Alien Temporary'
WHEN A.Citizenship_Status = '3' THEN
'Alien Permanent'
WHEN A.Citizenship_Status = '2' THEN
'Naturalized'
WHEN A.Citizenship_Status = '1' THEN
'Native'
WHEN A.Citizenship_Status = 'N' THEN
'Not Indicated'
ELSE
' '
END
| ||
| UM_COUNTRY_OTH - Country Other | Char3 | Country Oth | This field is essentially unreliable for students with more than 1 foreign country listed in the CITIZENSHIP table. | ||
| UM_COUNTRY_STATUS - Country Status | Char15 | Country Status |
Calculation for UM_COUNTRY_STATUS (Alias A = UM_STUD_EXTRACT Expand source
CASE
WHEN A.Citizenship_Status = '1' THEN
'USA'
WHEN A.Citizenship_Status = '2' THEN
'USA'
WHEN A.Citizenship_Status = '3' THEN
'USA'
WHEN A.Citizenship_Status = '4' AND
(A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
'Non-USA'
WHEN A.Citizenship_Status = '4' AND A.Residency = 'CAN' THEN
'Canada'
WHEN A.Citizenship_Status = '4' AND
(A.Country_Descr1 = 'United States' AND A.Um_Country_Oth > ' ') THEN
'Non-USA'
WHEN A.Citizenship_Status = '4' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
'Non-USA'
WHEN A.Citizenship_Status = '4' THEN
'Other Country'
WHEN A.Residency = 'CAN' THEN
'Non-USA'
WHEN A.Residency = 'INTNL' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
'Non-USA'
WHEN A.Residency = 'INTNL' AND
(A.Country_Descr1 = 'United States' AND A.Um_Country_Oth > ' ') THEN
'Non-USA'
WHEN A.Residency = 'INTNL' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
'Non-USA'
WHEN A.Residency = 'INTNL' THEN
'Non-USA'
WHEN A.Residency = 'OS' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
'Non-USA'
WHEN A.Residency = 'OS' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
'Non-USA'
WHEN A.Residency = 'NEBHE' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
'Non-USA'
WHEN A.Residency = 'NEBHE' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
'Non-USA'
WHEN A.Country <> 'USA' AND A.Country > ' ' THEN
'Non-USA'
ELSE
'USA'
END
| ||
| UM_COUNTRY_USA - Country is USA | Char3 | Country USA | UM_STUD_EXTRACT | ||
| UM_CURRENT_AGE - Current Age | Num4.1 | Current Age |
Calculation for UM_CURRENT_AGE (Alias A = UM_STUD_EXTRACT) Expand source
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) | ||
| UM_CURRENT_AGE_RNG - Current Age Range | Char15 | Curr Age Range |
Calculation for UM_CURRENT_AGE_RNG (Alias A = UM_STUD_EXTRACT) Expand source
CASE
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 14 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 17 THEN
'Under 18'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 18 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 19 THEN
'18 - 19'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 20 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 21 THEN
'20 - 21'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 22 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 24 THEN
'22 - 24'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 25 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 29 THEN
'25 - 29'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 30 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 34 THEN
'30 - 34'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 35 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 39 THEN
'35 - 39'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 40 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 49 THEN
'40 - 49'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 50 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 64 THEN
'50 - 64'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 65 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 105 THEN
'65 and Over'
ELSE
'Unknown'
END
| ||
| UM_CURRNT_AGE_RSRT - Current Age Range Sort | Char2 | Current Age RSRT |
Calculation for UM_CURRNT_AGE_RSRT (Alias A = UM_STUD_EXTRACT) Expand source
CASE
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 14 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 17 THEN
'01'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 18 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 19 THEN
'02'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 20 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 21 THEN
'03'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 22 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 24 THEN
'04'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 25 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 29 THEN
'05'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 30 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 34 THEN
'06'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 35 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 39 THEN
'07'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 40 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 49 THEN
'08'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 50 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 64 THEN
'09'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 65 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 105 THEN
'10'
ELSE
'11'
END
| ||
| UM_DEGREE_NONDEG - Degree or Non-Degree | Char15 | Degree NonDeg | Please go to Degree/Non-Degree Coding - Current page for the code | ||
| UM_EMAIL_HOM_ADDR - Email Home Address | Char70 | Email Home Addr | UM_STUD_EXTRACT | ||
| UM_ETHNIC_DESCR - Ethnic Description | Char50 | Ethic Descr | UM_STUD_EXTRACT | ||
| UM_ETHNIC_GRP_DESC - Ethnic Groupd Description | Char30 | Ethnic Grp Desc | Calculation for UM_ETHNIC_GRP_DESC field | ||
| UM_ETHNIC_GRP_SORT - Ethnic Group Sort | Char2 | Ethnic Grp Srt |
Calculation for UM_ETHNIC_GRP_SORT (Alias A = UM_STUD_EXTRACT) Expand source
CASE
WHEN A.Citizenship_Status = '4' THEN
'7'
WHEN A.Ethnic_Group = '3' THEN
'3'
WHEN A.Hisp_Latino = 'Y' THEN
'3'
WHEN A.Um_Multiple_Ethnic = 'Y' THEN
'8'
WHEN A.Ethnic_Group = '1' THEN
'1'
WHEN A.Ethnic_Group = '2' THEN
'2'
WHEN A.Ethnic_Group = '4' THEN
'4'
WHEN A.Ethnic_Group = '5' THEN
'5'
WHEN A.Ethnic_Group = '7' THEN
'6'
WHEN A.Ethnic_Group = '6' THEN
'9'
ELSE
'9'
END
| ||
| UM_ETH_GRP_DESCR - Ethnic Group Descr | Char30 | Eth Grp Descr | UM_STUD_EXTRACT | ||
| UM_EXTERNAL_ORG - External Organization | Char30 | External Org | Please go to Degree/Non-Degree Coding - Current page for the code | ||
| UM_EXTERNL_ORG_DTL - External Org Detail | Char30 | Externl Org Dtl | Please go to Degree/Non-Degree Coding - Current page for the code | ||
| UM_EXTERNL_ORG_SUM - External Organization Summary | Char30 | Externl Org Sum | Please go to Degree/Non-Degree Coding - Current page for the code | ||
| UM_EXT_ORGID_DESCR - External Org ID Description | Char30 | Ext Org ID Desc | UM_STUD_EXTRACT | ||
| UM_EXT_ORG_INST - External Org Institution | Char5 | Ext Org Inst |
Calculation for UM_EXT_ORG_INST (Alias A = UM_STUD_EXTRACT) Expand source
DECODE(A.Ext_Org_Id,
'0136250',
'UMS05',
'0136246',
'UMS01',
'0136247',
'UMS02',
'0136248',
'UMS03',
'0136249',
'UMS04',
'0136252',
'UMS07',
'0136392',
'UMS06',
'OTHER')
| ||
| UM_FEES_PAID - Fees Paid | SNm8.0 | Fees Paid | Although this field says "Fees Paid," the values are actually charges calculated from the CHARGES tree per Darren Michael, 10/19/09. | ||
| UM_FIRST_GEN - First Gen | Char1 | First Gen | Calculation for First Generation | ||
| UM_FIRST_TERM - First Term | Char1 | First Term |
Calculation for UM_FIRST_TERM (Alias A = UM_STUD_EXTRACT) Expand source
CASE
WHEN A.Admit_Term = A.Strm OR (A.STRM - CASE
WHEN A.Admit_Term = ' ' THEN
'0000'
ELSE
a.admit_term
END) = 80 THEN
'Y'
ELSE
'N'
END
| ||
| UM_FIRST_TIME - First Time | Char1 | First Time | Please go to Degree/Non-Degree Coding - Current page for the code | ||
| UM_FIRST_TIME_NDEG - First Time Non-Degree | Char1 | First Time NonD | Please go to Degree/Non-Degree Coding - Current page for the code | ||
| UM_FTE_DIVISOR - FTE Divisor | Num3.0 | FTE Divisor |
SQL for UM_FTE_DIVISOR (alias A= UM_STUD_EXTRACT) Expand source
CASE WHEN A.Institution = 'UMS02' AND A.Acad_plan = 'PSY-MA' THEN 15
WHEN A.Acad_Level_Bot IN ('GR', 'MAS', 'PHD') THEN 9
WHEN A.Acad_Level_Bot IN ('L1', 'L2', 'L3') THEN 15
WHEN A.Institution = 'UMS02' AND STRM < '2410' THEN 16
ELSE 15 END
| ||
| UM_FULL_PART - Full-time Part-Time | Char10 | Full Part |
SQL for UM_FULL_PART (alias A= UM_STUD_EXTRACT table) Expand source
CASE
WHEN A.Acad_Level_Bot = 'GR' AND A.Um_Unt_Tak_Prg_Rc >= 6 THEN
'Full-Time'
WHEN A.Acad_Level_Bot = 'MAS' AND A.Um_Unt_Tak_Prg_Rc >= 6 THEN
'Full-Time'
WHEN A.Acad_Level_Bot = 'PHD' AND A.Um_Unt_Tak_Prg_Rc >= 6 THEN
'Full-Time'
WHEN A.Um_Unt_Tak_Prg_Rc >= 12 THEN
'Full-Time'
ELSE
'Part-Time'
END
| ||
| UM_GENDER - Gender | Char10 | Gender |
Calculation for UM_GENDER (Alias A= UM_STUD_EXTRACT) Expand source
CASE WHEN A.Sex = 'M' THEN 'Male'
ELSE 'Female'
END
| ||
| UM_GRAD_DATE_YEAR - Graduation Date Year | Char4 | Grad Date Year |
Calculation for UM_GRAD_DATE_YEAR (Alias A= UM_STUD_EXTRACT) Expand source
TO_NUMBER(TO_CHAR(A.Graduation_Dt, 'YYYY'), '9999') | ||
| UM_INST_SHORT - Institution Short | Char5 | Inst Short |
Calculation for UM_INST_SHORT (Alias A = UM_STUD_EXTRACT) Expand source
DECODE(A.Institution,
'UMS01',
'UMA',
'UMS02',
'UMF',
'UMS03',
'UMFK',
'UMS04',
'UMM',
'UMS07',
'UMPI',
'UMS05',
'UM',
'UMS06',
'USM',
'Other')
| ||
| UM_IN_OUT - In-State Out-of_State | Char15 | In-State Out |
Calculation for UM_IN_OUT (Alias A = UM_STUD_EXTRACT and alias QB= RESIDENCY_OFF) Expand source
CASE WHEN A.RESIDENCY = 'MEONL' AND QB.RESIDENCY = 'IS' THEN 'In-State' WHEN A.Residency = 'IS' THEN 'In-State' ELSE 'Out-of-State' END | ||
| UM_LAST_SCH_DESCR - Last School Attend Description | Char30 | Lst School Desc | UM_STUD_EXTRACT | ||
| UM_LAST_SCH_TYPE - Last School Attended Type | Char3 | Last School Typ | UM_STUD_EXTRACT | ||
| UM_MAINE_COUNTY - Maine County | Char1 | Maine County |
Calculation for UM_MAINE_COUNTY (Alias A = UM_STUD_EXTRACT) Expand source
CASE
WHEN A.County <= ' ' AND A.Institution = 'UMS01' THEN
'Y'
WHEN A.County <= ' ' AND A.Institution = 'UMS02' THEN
'Y'
WHEN A.County <= ' ' AND A.Institution = 'UMS03' THEN
'Y'
WHEN A.County <= ' ' AND A.Institution = 'UMS04' THEN
'Y'
WHEN A.County <= ' ' AND A.Institution = 'UMS05' THEN
'Y'
WHEN A.County <= ' ' AND A.Institution = 'UMS06' THEN
'Y'
WHEN A.County <= ' ' AND A.Institution = 'UMS07' THEN
'Y'
WHEN A.County <= ' ' THEN
'N'
WHEN LOWER(A.County) LIKE '%dros%' THEN
'Y'
WHEN LOWER(A.County) LIKE '%aroos%' THEN
'Y'
WHEN LOWER(A.County) LIKE '%cumber%' THEN
'Y'
WHEN LOWER(A.County) LIKE '%frank%' THEN
'Y'
WHEN LOWER(A.County) LIKE '%hanc%' THEN
'Y'
WHEN LOWER(A.County) LIKE '%bec' THEN
'Y'
WHEN LOWER(A.County) LIKE '%knox' THEN
'Y'
WHEN LOWER(A.County) LIKE '%lincoln' THEN
'Y'
WHEN LOWER(A.County) LIKE '%oxford' THEN
'Y'
WHEN LOWER(A.County) LIKE '%penob%' THEN
'Y'
WHEN LOWER(A.County) LIKE '%piscat%' THEN
'Y'
WHEN LOWER(A.County) LIKE '%saga%' THEN
'Y'
WHEN LOWER(A.County) LIKE '%somer%' THEN
'Y'
WHEN LOWER(A.County) LIKE '%waldo' THEN
'Y'
WHEN LOWER(A.County) LIKE '%wash%' THEN
'Y'
WHEN LOWER(A.County) LIKE '%york' THEN
'Y'
WHEN A.County NOT IN ('Androscoggin',
'Aroostook',
'Cumberland',
'Franklin',
'Hancock',
'Kennebec',
'Knox',
'Lincoln',
'Oxford',
'Penobscot',
'Piscataquis',
'Sagadahoc',
'Somerset',
'Waldo',
'Washington',
'York') THEN
'N'
ELSE
'N'
END
| ||
| UM_MAX_ACT_COMP - Maximum ACT Comp | Num5.2 | Max ACT Comp | Limited to last 5 years | ||
| UM_MAX_ACT_ENGL - Maximum ACT English | Num5.2 | Max ACT English | Limited to last 5 years | ||
| UM_MAX_ACT_MATH - Maximum ACT Math | Num5.2 | Max ACT Math | Limited to last 5 years | ||
| UM_MAX_GRE_QUAN - Maximum Quantitative GRE | Num5.2 | Max GRE QUAN | UM_STUD_EXTRACT | ||
| UM_MAX_GRE_VERB - Maximum GRE Verbal | Num5.2 | Max GRE VERB | UM_STUD_EXTRACT | ||
| UM_MAX_SAT_ERWS - Max SAT ERWS | Num5.2 | Max SAT ERWS | UM_STUD_EXTRACT | ||
| UM_MAX_SAT_MATH - Maximum Sat Math | Num5.2 | Max Sat Math | Limited to last 5 years | ||
| UM_MAX_SAT_MSS - Max SAT MSS | Num5.2 | Max SAT MSS | Limited to last 5 years | ||
| UM_MAX_SAT_VERB - Maximum Sat Verbal | Num5.2 | Max Sat Verbal | Limited to last 5 years | ||
| UM_MAX_SAT_WRIT - Maximum SAT Write | Num5.2 | Max SAT Write | Limited to last 5 years | ||
| UM_MAX_TOEFL_COMPC - Maximum TOEFL COMPC | Num5.2 | Max TOEFL COMPC | UM_STUD_EXTRACT | ||
| UM_MAX_TOEFL_COMPI - Maximum TOEFL COMPI | Num5.2 | Max TOEFL COMPI | UM_STUD_EXTRACT | ||
| UM_MAX_TOEFL_COMPP - Maximum TOEFL COMPP | Num5.2 | Max TOEFL COMPP | UM_STUD_EXTRACT | ||
| UM_MIN_ACAD_PLAN - Academic Plan | Char10 | Acad Plan | UM_STUD_EXTRACT | ||
| UM_MIN_ACAD_PROG - Academic Program | Char5 | Acad Prog | UM_STUD_EXTRACT | ||
| UM_MIN_ACAD_SUBPLN - Academic Sub-Plan | Char10 | Sub-Plan | UM_STUD_EXTRACT | ||
| UM_MULTIPLE_ETHNIC - Multiple Ethnic Groups | Char1 | N | Mutiple_Ethnic | UM_STUD_EXTRACT | |
| UM_NEASC_LEVEL - NEASC Level | Char50 | NEASC Level | Please go to Degree/Non-Degree Coding - Current page for the code | ||
| UM_NEASC_LEVEL_SRT - NEASC Level Sort | Char2 | NEASC Level Srt | Please go to Degree/Non-Degree Coding - Current page for the code | ||
| UM_NID_CAN - National ID Canada | Char20 | NID Canada | UM_STUD_EXTRACT | ||
| UM_NID_USA - National Id USA | Char20 | NID USA | UM_STUD_EXTRACT | ||
| UM_NURSING_FLAG - Nursing Flag | Char1 | Nursing Flag |
Calculation for UM_NURSING_FLAG (Alias A = UM_STUD_EXTRACT) Expand source
CASE
WHEN A.Acad_Plan LIKE 'NUR%' THEN
'Y'
WHEN A.Acad_Prog LIKE 'NUR%' THEN
'Y'
ELSE
'N'
END
| ||
| UM_ORIG_ACAD_PLAN - ORIGINAL_ACAD_PLAN | Char10 | ORIG_ACAD_PLAN | UM_STUD_EXTRACT | ||
| UM_ORIG_ACAD_PROG - ORIGINAL_ACAD_PROG | Char5 | ORIG_ACAD_PROG | UM_STUD_EXTRACT | ||
| UM_PREVIOUS_NAME - Previous Name | Char50 | Prev Name | UM_STUD_EXTRACT | ||
| UM_RESIDENCY_DESCR - Residency Description | Char30 | Residency Descr |
Calculation for UM_RESIDENCY_DESCR (Alias A = UM_STUD_EXTRACT) Expand source
DECODE(A.Residency,
'IS', 'In-State',
'OS', 'Out-of-State',
'CAN', 'Canadian',
'NEBHE', 'NEBHE',
'INTNL', 'International',
'ONLIN', 'Online',
'MEONL', 'Academic Partnerships'
'Out-of-State' )
| ||
| UM_RESIDENCY_SORT - Residency Description Sort | Char2 | Residency Sort |
Calculation for UM_RESIDENCY_SORT (Alias A = UM_STUD_EXTRACT) Expand source
DECODE(A.Residency,
'IS', '10',
'OS', '20',
'CAN', '40',
'NEBHE', '30',
'INTNL', '50',
'ONLIN', '60',
'MEONL', '70'
'20' )
| ||
| UM_RSP_REASN_DESCR - Response Reason Descriptions | Char30 | Resp Reason Des | UM_STUD_EXTRACT | ||
| UM_STD_LVL_SUM_SRT - Student Level Summary Sort | Char2 | Std Lvl Sum Srt | Please go to Degree/Non-Degree Coding - Current page for the code | ||
| UM_STD_LVL_SUM_UG - Student Level Sum UG Grad | Char30 | Std Lvl Sum UG | Please go to Degree/Non-Degree Coding - Current page for the code | ||
| UM_STRM_YEAR - Strm Year | Char4 | Strm Year |
Calculation for UM_STRM_YEAR (Alias A = UM_STUD_EXTRACT) Expand source
CASE
WHEN SUBSTR(A.Strm, 3, 1) = '1' THEN
TO_NUMBER('20' || SUBSTR(A.Strm, 1, 2), '9999') - 1
ELSE
TO_NUMBER('20' || SUBSTR(A.Strm, 1, 2), '9999')
END
| ||
| UM_STUDENT_LEVEL - Student Level | Char50 | Student Level | Please go to Degree/Non-Degree Coding - Current page for the code | ||
| UM_STUDENT_NAME - Student | Char50 | Student |
Calculation for UM_STUDENT_NAME (Alias A = UM_STUD_EXTRACT) Expand source
CASE
WHEN A.Name_Suffix <= ' ' AND A.Last_Name > ' ' THEN
A.LAST_NAME || ', ' || A.FIRST_NAME || ' ' || A.Middle_Name
WHEN A.LAST_NAME > ' ' THEN
A.LAST_NAME || ' ' || A.Name_Suffix || ', ' || A.FIRST_NAME || ' ' ||
A.Middle_Name
ELSE
''
END
| ||
| UM_STUD_LEVEL_SORT - Student Level Sort | Char2 | Stud Level Sort | Please go to Degree/Non-Degree Coding - Current page for the code | ||
| UM_STUD_LEVEL_SUM - Student Level Summary | Char30 | Stud Level Sum | Please go to Degree/Non-Degree Coding - Current page for the code | ||
| UM_TUITION_COUNTRY - Tuition Country | Char50 | Tuition Country |
Calculation for UM_TUITION_COUNTRY (Alias A = UM_STUD_EXTRACT) Expand source
CASE
WHEN A.Residency = 'IS' THEN
'USA'
WHEN A.Citizenship_Status = '4' AND A.Um_Country_Oth > ' ' THEN
A.Um_Country_Oth
WHEN A.Citizenship_Status = '4' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Citizenship_Status = '4' AND A.Residency = 'CAN' THEN
'CAN'
WHEN A.Citizenship_Status = '4' THEN
'OTH'
ELSE
'USA'
END
| ||
| UM_TUITION_COUNTY - Tuition County | Char50 | Tuition County |
Calculation for UM_TUITION_COUNTY (Alias A = UM_STUD_EXTRACT) Expand source
CASE
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%dros%' THEN
'Androscoggin'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%aroos%' THEN
'Aroostook'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%cumber%' THEN
'Cumberland'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%frank%' THEN
'Franklin'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%hanc%' THEN
'Hancock'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%bec' THEN
'Kennebec'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%knox' THEN
'Knox'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%lincoln' THEN
'Lincoln'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%oxford' THEN
'Oxford'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%penob%' THEN
'Penobscot'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%piscat%' THEN
'Piscataquis'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%saga%' THEN
'Sagadahoc'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%somer%' THEN
'Somerset'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%waldo' THEN
'Waldo'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%wash%' THEN
'Washington'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%york' THEN
'York'
WHEN A.Residency = 'IS' AND A.Institution = 'UMS01' THEN
'Kennebec'
WHEN A.Residency = 'IS' AND A.Institution = 'UMS02' THEN
'Franklin'
WHEN A.Residency = 'IS' AND A.Institution = 'UMS03' THEN
'Aroostook'
WHEN A.Residency = 'IS' AND A.Institution = 'UMS04' THEN
'Washington'
WHEN A.Residency = 'IS' AND A.Institution = 'UMS05' THEN
'Penobscot'
WHEN A.Residency = 'IS' AND A.Institution = 'UMS06' THEN
'Cumberland'
WHEN A.Residency = 'IS' AND A.Institution = 'UMS07' THEN
'Aroostook'
ELSE
'Non-Maine'
END
| ||
| UM_TUITION_PAID - Tuition Paid | SNm8.0 | Tuition Paid | Although this field says "Tuition Paid," the values are actually charges calculated from the CHARGES tree per Darren Michael, 10/19/09. | ||
| UM_TUITION_STATE - Tuition State | Char50 | Tuition State |
Calculation for UM_TUITION_STATE (Alias A = UM_STUD_EXTRACT) Expand source
CASE
WHEN A.Residency = 'IS' THEN
'Maine'
WHEN A.State = 'AB' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Alberta'
WHEN A.State = 'BC' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'British Columbia'
WHEN A.State = 'MB' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Manitoba'
WHEN A.State = 'NB' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'New Brunswick'
WHEN A.State = 'NF' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Newfoundland'
WHEN A.State = 'NL' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Newfoundland'
WHEN A.State = 'NS' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Nova Scotia'
WHEN A.State = 'NT' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Northwest Territories'
WHEN A.State = 'NN' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Nunavut'
WHEN A.State = 'NU' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Nunavut'
WHEN A.State = 'ON' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Ontario'
WHEN A.State = 'PE' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Prince Edward Island'
WHEN A.State = 'QC' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Quebec'
WHEN A.State = 'SK' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Saskatchewan'
WHEN A.State = 'YT' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Yukon'
WHEN A.Citizenship_Status = '4' AND A.Residency = 'CAN' THEN
'Canada'
WHEN A.Citizenship_Status = '4' THEN
'Non-USA'
WHEN A.State <> 'ME' AND A.State_Descr1 > ' ' THEN
A.State_Descr1
WHEN A.State = 'NH' THEN
'New Hampshire'
WHEN A.State = 'VT' THEN
'Vermont'
WHEN A.State = 'MA' THEN
'Massachusetts'
WHEN A.State = 'CT' THEN
'Connecticut'
WHEN A.State = 'RI' THEN
'Rhode Island'
ELSE
'Other State'
END
| ||
| UM_UMS01_FLG - Ums01 Registered Flag | Char1 | Ums01 Flag | UM_STUD_EXTRACT | ||
| UM_UMS01_UNITS - UMS01 Units | Num6.3 | UMS01 Units | UM_STUD_EXTRACT | ||
| UM_UMS02_FLG - UMS02 Registered Flag | Char1 | UMS02 Flag | UM_STUD_EXTRACT | ||
| UM_UMS02_UNITS - UMS02 Units | Num6.3 | UMS02 Units | UM_STUD_EXTRACT | ||
| UM_UMS03_FLG - Ums03 Registered Flag | Char1 | Ums03 Flag | UM_STUD_EXTRACT | ||
| UM_UMS03_UNITS - UMS03 Units | Num6.3 | Ums03 Units | UM_STUD_EXTRACT | ||
| UM_UMS04_FLG - UMS04 Registered Flag | Char1 | UMS04 Flag | UM_STUD_EXTRACT | ||
| UM_UMS04_UNITS - UMS04 Units | Num6.3 | UMS04 Units | UM_STUD_EXTRACT | ||
| UM_UMS05_FLG - Ums05 Registered Flag | Char1 | Ums05 Flag | UM_STUD_EXTRACT | ||
| UM_UMS05_UNITS - UMS05 Units | Num6.3 | UMS05 Units | UM_STUD_EXTRACT | ||
| UM_UMS06_FLG - Ums06 Registered Flag | Char1 | Ums06 Flag | UM_STUD_EXTRACT | ||
| UM_UMS06_UNITS - UMS06 Units | Num6.3 | UMS06 Units | UM_STUD_EXTRACT | ||
| UM_UMS07_FLG - Ums07 Registered Flag | Char1 | Ums07 Flag | UM_STUD_EXTRACT | ||
| UM_UMS07_UNITS - UMS07 Units | Num6.3 | UMS07 Units | UM_STUD_EXTRACT | ||
| UM_UNT_DIST - Units Distance | Num10.3 | Units Distance | UM_STUD_EXTRACT | ||
| UM_UNT_ITV - Itv Receive Units | Num10.3 | Itv Receive Unt | UM_STUD_EXTRACT | ||
| UM_UNT_TAK_CAREER - Units Taken For Career | Num6.3 | Unt Takn Career | UM_STUD_EXTRACT | ||
| UM_UNT_TAK_PRG_RC - Units Taken Progress Row Check | Num6.3 | Units Taken Prg | UM_STUD_EXTRACT | ||
| UM_UNT_WEB - Web Units | Num10.3 | Web Units | UM_STUD_EXTRACT | ||
| UNIT_MULTIPLIER - Unit Multiplier | Num5.4 | Multiplier | UM_STUD_EXTRACT | ||
| UNTPRG_CHG_NSLC_DT - Unit Progrss Changed Date NSC | Date | Unit ChgDt NSC | UM_STUD_EXTRACT | ||
| UNT_AUDIT - Units Audited | Num6.3 | Audit | UM_STUD_EXTRACT | ||
| UNT_INPROG_GPA - Units In Progress - GPA | Num6.3 | InPr GPA | UM_STUD_EXTRACT | ||
| UNT_INPROG_NOGPA - Unit In Progress - Not for GPA | Num6.3 | InPr NoGPA | UM_STUD_EXTRACT | ||
| UNT_OTHER - Units from Other Credit | Num6.3 | Other | UM_STUD_EXTRACT | ||
| UNT_PASSD_FA - Fin Aid Progress Units Passed | Num6.3 | FA Passed | UM_STUD_EXTRACT | ||
| UNT_PASSD_GPA - Units Passed Toward GPA | Num6.3 | Pass GPA | UM_STUD_EXTRACT | ||
| UNT_PASSD_NOGPA - Units Passed Not Toward GPA | Num6.3 | PassNoGPA | UM_STUD_EXTRACT | ||
| UNT_PASSD_PRGRSS - Units Passed for Progress | Num6.3 | Pass Prgrs | UM_STUD_EXTRACT | ||
| UNT_TAKEN_FA - Fin Aid Progress Units Taken | Num6.3 | FA Taken | UM_STUD_EXTRACT | ||
| UNT_TAKEN_FA_GPA - FA Units Taken Toward GPA | Num6.3 | FA Units T | UM_STUD_EXTRACT | ||
| UNT_TAKEN_GPA - Units Taken Toward GPA | Num6.3 | Take GPA | UM_STUD_EXTRACT | ||
| UNT_TAKEN_NOGPA - Units Taken Not Toward GPA | Num6.3 | TakeNoGPA | UM_STUD_EXTRACT | ||
| UNT_TAKEN_PRGRSS - Units Taken for Progress | Num6.3 | Take Prgrs | UM_STUD_EXTRACT | ||
| UNT_TERM_TOT - Total Term Units | Num6.3 | Total | UM_STUD_EXTRACT | ||
| UNT_TEST_CREDIT - Units from Test Credit | Num6.3 | Test | UM_STUD_EXTRACT | ||
| UNT_TRNSFR - Units Transferred | Num6.3 | Transfer | UM_STUD_EXTRACT | ||
| VALIDATED - Election Validated | Char1 | Validated | UM_STUD_EXTRACT | ||
| VALIDATED_DT - Date Data Validated | Date | Date Validated | UM_STUD_EXTRACT | ||
| WITHDRAW_CODE - Withdrawal \ Cancel | Char3 | N | Wdraw Code | UM_STUD_EXTRACT | |
| WITHDRAW_DATE - Withdrawal \ Cancel Date | Date | Wdraw Date | UM_STUD_EXTRACT | ||
| WITHDRAW_REASON - Withdrawal \ Cancel Reason | Char4 | N | WithdrlRsn | UM_STUD_EXTRACT |
Data Dictionary Links
Clicking the linked (blue) fields in any table will take you to the Data Dictionary and provide you with more information about that field as well as field values if they are available.
SQL code for UM_STUD_EXT_VW Expand source
SELECT A.EMPLID,
A.LAST_NAME,
A.FIRST_NAME,
A.MIDDLE_NAME,
A.NAME_PREFIX,
A.NAME_SUFFIX,
A.NAME,
A.UM_PREVIOUS_NAME,
A.ADDRESS1,
A.ADDRESS2,
A.ADDRESS3,
A.ADDRESS4,
A.CITY,
A.STATE,
A.STATE_DESCR1,
A.POSTAL,
A.COUNTY,
A.COUNTRY,
A.COUNTRY_DESCR1,
A.EMAIL_ADDR,
A.PHONE,
A.SEX,
A.BIRTHDATE,
A.BIRTHPLACE,
A.BIRTHSTATE,
A.BIRTH_COUNTRY,
A.UM_NID_USA,
A.UM_NID_CAN,
A.ETHNIC_GRP_CD,
A.ETHNIC_CATEGORY,
A.UM_ETHNIC_DESCR,
A.MAR_STATUS,
A.MAR_STATUS_DT,
A.DT_OF_DEATH,
A.UM_COUNTRY_USA,
A.CITIZENSHIP_STATUS,
A.UM_COUNTRY_OTH,
A.UM_CITIZEN_STAT_OT,
A.EFFECTIVE_TERM,
A.ADMIT_TERM,
A.RESIDENCY,
A.RESIDENCY_DT,
A.ADMISSION_RES,
A.ADMISSION_EXCPT,
A.ACAD_CAREER,
A.STDNT_CAR_NBR,
A.ADM_APPL_NBR,
A.INSTITUTION,
A.INSTITUTION_DESCR,
A.ACAD_PROG,
A.PROG_STATUS,
A.PROG_ACTION,
A.ACTION_DT,
A.PROG_REASON,
A.EXP_GRAD_TERM,
A.REQ_TERM,
A.ACAD_LOAD_APPR,
A.COMPLETION_TERM,
A.ACAD_PROG_DUAL,
A.JOINT_PROG_APPR,
A.ACAD_PROG_DESCR,
A.ACAD_PLAN,
A.DECLARE_DT,
A.PLAN_SEQUENCE,
A.STDNT_DEGR,
A.DEGR_CHKOUT_STAT,
A.ADVIS_STATUS,
A.STRM,
A.REG_CARD_DATE,
A.WITHDRAW_CODE,
A.WITHDRAW_REASON,
A.WITHDRAW_DATE,
A.LAST_DATE_ATTENDED,
A.ACAD_PROG_PRIMARY,
A.NSLDS_LOAN_YEAR,
A.OVRD_ACAD_LVL_ALL,
A.OVRD_ACAD_LVL_PROJ,
A.ELIG_TO_ENROLL,
A.OVRD_MAX_UNITS,
A.MAX_TOTAL_UNIT,
A.MAX_NOGPA_UNIT,
A.MAX_AUDIT_UNIT,
A.MAX_WAIT_UNIT,
A.MIN_TOTAL_UNIT,
A.OVRD_BILL_UNITS,
A.PROJ_BILL_UNT,
A.UNT_TAKEN_GPA,
A.UNT_PASSD_GPA,
A.UNT_TAKEN_NOGPA,
A.UNT_PASSD_NOGPA,
A.UNT_INPROG_GPA,
A.UNT_INPROG_NOGPA,
A.UNT_AUDIT,
A.TRF_TAKEN_GPA,
A.TRF_TAKEN_NOGPA,
A.TRF_PASSED_GPA,
A.TRF_PASSED_NOGPA,
A.TRF_GRADE_POINTS,
A.UNT_TEST_CREDIT,
A.UNT_OTHER,
A.UNT_TAKEN_FA,
A.UNT_PASSD_FA,
A.UNT_TAKEN_FA_GPA,
A.GRADE_POINTS_FA,
A.RESET_CUM_STATS,
A.TOT_TAKEN_PRGRSS,
A.TOT_TAKEN_GPA,
A.TOT_PASSD_GPA,
A.TOT_TAKEN_NOGPA,
A.TOT_PASSD_NOGPA,
A.TOT_INPROG_GPA,
A.TOT_INPROG_NOGPA,
A.TOT_AUDIT,
A.TOT_TRNSFR,
A.TOT_TEST_CREDIT,
A.TOT_OTHER,
A.TOT_CUMULATIVE,
A.TOT_TAKEN_FA,
A.TOT_PASSD_FA,
A.TOT_TAKEN_FA_GPA,
A.TOT_GRD_POINTS_FA,
A.FORM_OF_STUDY,
A.TERM_TYPE,
A.CLASS_RANK_NBR,
A.CLASS_RANK_TOT,
A.SEL_GROUP,
A.TUIT_CALC_REQ,
A.TUIT_CALC_DTTM,
A.FA_STATS_CALC_REQ,
A.FA_STATS_CALC_DTTM,
A.FA_ELIGIBILITY,
A.BILLING_CAREER,
A.UNIT_MULTIPLIER,
A.ACAD_YEAR,
A.ACAD_GROUP_ADVIS,
A.CUR_RESIDENT_TERMS,
A.TRF_RESIDENT_TERMS,
A.CUM_RESIDENT_TERMS,
A.REFUND_PCT,
A.REFUND_SCHEME,
A.PRO_RATA_ELIGIBLE,
A.FULLY_ENRL_DT,
A.ENRL_ON_TRANS_DT,
A.STATS_ON_TRANS_DT,
A.FULLY_GRADED_DT,
A.STUDY_AGREEMENT,
A.START_DATE,
A.END_DATE,
A.MAX_CRSE_COUNT,
A.REGISTERED,
A.OVRD_TUIT_GROUP,
A.OVRD_WDRW_SCHED,
A.TUITION_RES_TERMS,
A.OVRD_INIT_ADD_FEE,
A.OVRD_INIT_ENR_FEE,
A.TC_UNITS_ADJUST,
A.LOCK_IN_AMT,
A.LOCK_IN_DT,
A.ACAD_CAREER_FIRST,
A.ACADEMIC_LOAD_DT,
A.UNTPRG_CHG_NSLC_DT,
A.SSR_ACTIVATION_DT,
A.UM_ACAD_PLAN_DESCR,
A.ACAD_PLAN_TYPE,
A.ACAD_GROUP,
A.UM_ACAD_GRP_DESCR,
A.DEGREE,
A.ACAD_SUB_PLAN,
A.UM_ACAD_SUBPLN_DSC,
A.ACAD_SUBPLAN_TYPE,
A.RESPONSE_REASON,
A.UM_RSP_REASN_DESCR,
A.RESPONSE_DT,
A.EXT_ORG_ID,
A.UM_EXT_ORGID_DESCR,
A.UM_MAX_SAT_MATH,
A.UM_MAX_SAT_VERB,
A.UM_MAX_GRE_QUAN,
A.UM_MAX_GRE_VERB,
A.UM_LAST_SCH_DESCR,
A.UM_LAST_SCH_TYPE,
A.UM_MAX_SAT_WRIT,
A.UM_MAX_ACT_MATH,
A.UM_MAX_ACT_ENGL,
A.UM_MAX_ACT_COMP,
A.UM_MAX_TOEFL_COMPI,
A.UM_MAX_TOEFL_COMPP,
A.UM_MAX_TOEFL_COMPC,
A.UM_MIN_ACAD_PROG,
A.UM_MIN_ACAD_PLAN,
A.UM_MIN_ACAD_SUBPLN,
A.CAMPUS,
A.UNT_TERM_TOT,
A.GRADE_POINTS,
A.CUR_GPA,
A.CUM_GPA,
A.ACAD_LEVEL_BOT,
A.ACAD_LEVEL_EOT,
A.ACAD_LEVEL_PROJ,
A.UM_ACAD_LVL_DESCR,
A.ACADEMIC_LOAD,
A.FA_LOAD,
A.UNT_TAKEN_PRGRSS,
A.UNT_PASSD_PRGRSS,
A.UNT_TRNSFR,
A.TOT_PASSD_PRGRSS,
A.TOT_GRADE_POINTS,
A.ROW_CHECK,
A.UM_TUITION_PAID,
A.UM_FEES_PAID,
A.UM_UNT_WEB,
A.UM_UNT_DIST,
A.UM_UNT_ITV,
A.ADM_APPL_CTR,
A.ADMIT_TYPE,
A.UM_UMS01_FLG,
A.UM_UMS02_FLG,
A.UM_UMS03_FLG,
A.UM_UMS04_FLG,
A.UM_UMS05_FLG,
A.UM_UMS06_FLG,
A.UM_UMS07_FLG,
A.LEVEL_LOAD_RULE,
A.ADVISOR_ID,
A.UM_ADVISR_LST_NAME,
A.UM_ADVISR_FST_NAME,
A.UM_ADVISR_MID_NAME,
A.UM_ADVISR_NAME_SFX,
A.EDUCATION_LVL,
A.UM_UMS01_UNITS,
A.UM_UMS02_UNITS,
A.UM_UMS03_UNITS,
A.UM_UMS04_UNITS,
A.UM_UMS05_UNITS,
A.UM_UMS06_UNITS,
A.UM_UMS07_UNITS,
A.UM_ACAD_PROGP_DSC,
A.PIN_NUM,
A.VALIDATED,
A.VALIDATED_DT,
A.UM_UNT_TAK_PRG_RC,
A.UM_EMAIL_HOM_ADDR,
A.UM_ACAD_LOAD_RC,
A.UM_ORIG_ACAD_PROG,
A.UM_ORIG_ACAD_PLAN,
A.ETHNIC_GROUP,
A.HISP_LATINO,
A.UM_MULTIPLE_ETHNIC,
A.UM_ETH_GRP_DESCR,
A.GRADUATION_DT,
A.UM_UNT_TAK_CAREER,
A.UM_MAX_SAT_ERWS,
A.UM_MAX_SAT_MSS,
CASE
WHEN A.Acad_Level_Bot = '10' THEN
'First Year'
WHEN A.Acad_Level_Bot = '01' THEN
'First Year'
WHEN A.Acad_Level_Bot = '20' THEN
'Second Year'
WHEN A.Acad_Level_Bot = '02' THEN
'Second Year'
WHEN A.Acad_Level_Bot = '30' THEN
'Third Year'
WHEN A.Acad_Level_Bot = '03' THEN
'Third Year'
WHEN A.Acad_Level_Bot = '40' THEN
'Fourth Year'
WHEN A.Acad_Level_Bot = '04' THEN
'Fourth Year'
WHEN A.Acad_Level_Bot = '50' THEN
'Fifth Year'
WHEN A.Acad_Level_Bot = '05' THEN
'Fifth Year'
WHEN A.Acad_Level_Bot = 'GR' THEN
'Graduate'
WHEN A.Acad_Level_Bot = 'MAS' THEN
'Graduate'
WHEN A.Acad_Level_Bot = 'PHD' THEN
'Graduate'
WHEN A.Acad_Level_Bot = 'L1' THEN
'Law First Year'
WHEN A.Acad_Level_Bot = 'L2' THEN
'Law Second Year'
WHEN A.Acad_Level_Bot = 'L3' THEN
'Law Third Year'
ELSE
'Other'
END ACADEMIC_YEAR,
CASE
WHEN A.ACAD_LEVEL_BOT = '10' THEN
'10'
WHEN A.ACAD_LEVEL_BOT = '01' THEN
'10'
WHEN A.ACAD_LEVEL_BOT = '20' THEN
'11'
WHEN A.ACAD_LEVEL_BOT = '02' THEN
'11'
WHEN A.ACAD_LEVEL_BOT = '30' THEN
'12'
WHEN A.ACAD_LEVEL_BOT = '03' THEN
'12'
WHEN A.ACAD_LEVEL_BOT = '40' THEN
'13'
WHEN A.ACAD_LEVEL_BOT = '04' THEN
'13'
WHEN A.ACAD_LEVEL_BOT = '50' THEN
'14'
WHEN A.ACAD_LEVEL_BOT = '05' THEN
'14'
WHEN A.ACAD_LEVEL_BOT = 'GR' THEN
'15'
WHEN A.ACAD_LEVEL_BOT = 'MAS' THEN
'15'
WHEN A.ACAD_LEVEL_BOT = 'PHD' THEN
'15'
WHEN A.ACAD_LEVEL_BOT = 'L1' THEN
'16'
WHEN A.ACAD_LEVEL_BOT = 'L2' THEN
'17'
WHEN A.ACAD_LEVEL_BOT = 'L3' THEN
'18'
ELSE
'20'
END ACADEMIC_YEAR_SORTED,
CASE
WHEN A.ADMIT_TERM IS NULL THEN
0
WHEN SUBSTR(A.ADMIT_TERM, 1, 2) < '04' THEN
0
WHEN SUBSTR(A.ADMIT_TERM, 3, 1) = '1' THEN
TO_NUMBER('20' || SUBSTR(A.ADMIT_TERM, 1, 2), '9999') - 1
ELSE
TO_NUMBER('20' || SUBSTR(A.ADMIT_TERM, 1, 2), '9999')
END ADMIT_TERM_YEAR,
CASE
WHEN (A.INSTITUTION = 'UMS02' AND A.DEGREE = 'CGS') OR
(A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CERT') OR
(A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CGS') THEN
CASE
WHEN (A.Admit_Term = Strm OR
(to_number(A.STRM) - to_number(ltrim(A.ADMIT_TERM, ' '))) = 80) THEN
CASE
WHEN A.Institution = 'UMS05' AND A.Prog_Reason = 'RADM' THEN
'Readmit Student'
WHEN A.ADMIT_TYPE IN ('FYR', 'EFY') AND
(A.ACAD_LEVEL_BOT IN ('01', '10') OR
A.Um_Last_Sch_Type IN ('SCD', 'SHS')) THEN
'First-Time Undergraduate Student'
WHEN A.ADMIT_TYPE IN ('GRD', 'GR6', 'GR2', 'DAT') THEN
'Graduate Student'
WHEN A.ADMIT_TYPE = 'LAW' AND A.ACAD_LEVEL_BOT = 'L1' THEN
'Law Student'
WHEN A.ADMIT_TYPE = 'PBA' THEN
'Post-Baccalaureate Student'
WHEN A.ADMIT_TYPE = 'REA' THEN
'Readmit Student'
WHEN A.ADMIT_TYPE = 'TRF' AND
A.Institution <> DECODE(A.Ext_Org_Id,
'0136250',
'UMS05',
'0136246',
'UMS01',
'0136247',
'UMS02',
'0136248',
'UMS03',
'0136249',
'UMS04',
'0136252',
'UMS07',
'0136392',
'UMS06',
'OTHER') THEN
'Transfer Student'
ELSE
'Continuing Student'
END
ELSE
'Continuing Student'
END
WHEN A.ACAD_PLAN = 'IEI' AND A.INSTITUTION = 'UMS05' THEN
'Non-Degree'
WHEN A.ACAD_PLAN LIKE 'VIS%' AND A.INSTITUTION = 'UMS05' AND
A.ACAD_CAREER IN ('GRAD', 'UGRD') THEN
'Non-Degree'
WHEN A.ACAD_PROG_PRIMARY IN ('NDEC',
'NECN',
'NDUG',
'DIST',
'P-NON',
'NONCR',
'NDPR',
'NDGR',
'GRNON',
'NDPN',
'NSEU',
'NDUMA') THEN
'Non-Degree'
WHEN A.ACAD_PROG_PRIMARY LIKE '%UN' THEN
'Non-Degree'
WHEN A.ACAD_PROG_PRIMARY LIKE '%GN' THEN
'Non-Degree'
WHEN A.ACAD_CAREER = 'NCRD' AND A.INSTITUTION = 'UMS07' THEN
'Non-Degree'
WHEN (A.Admit_Term = Strm OR
(to_number(A.STRM) - to_number(ltrim(A.ADMIT_TERM, ' '))) = 80) THEN
CASE
WHEN A.Institution = 'UMS05' AND A.Prog_Reason = 'RADM' THEN
'Readmit Student'
WHEN A.ADMIT_TYPE IN ('FYR', 'EFY') AND
(A.ACAD_LEVEL_BOT IN ('01', '10') OR
A.Um_Last_Sch_Type IN ('SCD', 'SHS')) THEN
'First-Time Undergraduate Student'
WHEN A.ADMIT_TYPE IN ('GRD', 'GR6', 'GR2', 'DAT') THEN
'Graduate Student'
WHEN A.ADMIT_TYPE = 'LAW' AND A.ACAD_LEVEL_BOT = 'L1' THEN
'Law Student'
WHEN A.ADMIT_TYPE = 'PBA' THEN
'Post-Baccalaureate Student'
WHEN A.ADMIT_TYPE = 'REA' THEN
'Readmit Student'
WHEN A.ADMIT_TYPE = 'TRF' AND
A.Institution <> DECODE(A.Ext_Org_Id,
'0136250',
'UMS05',
'0136246',
'UMS01',
'0136247',
'UMS02',
'0136248',
'UMS03',
'0136249',
'UMS04',
'0136252',
'UMS07',
'0136392',
'UMS06',
'OTHER') THEN
'Transfer Student'
ELSE
'Continuing Student'
END
ELSE
'Continuing Student'
END ADMIT_TYPE_DESCR,
CASE
WHEN (A.INSTITUTION = 'UMS02' AND A.DEGREE = 'CGS') OR
(A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CERT') OR
(A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CGS') THEN
'Continuing Student'
WHEN (A.ACAD_PLAN = 'IEI' AND A.INSTITUTION = 'UMS05') OR
(A.ACAD_PLAN LIKE 'VIS%' AND A.INSTITUTION = 'UMS05' AND
A.ACAD_CAREER IN ('GRAD', 'UGRD')) OR
(A.ACAD_PROG_PRIMARY IN ('NDEC',
'NECN',
'NDUG',
'DIST',
'P-NON',
'NONCR',
'NDPR',
'NDGR',
'GRNON',
'NDPN',
'NSEU',
'NDUMA')) OR
(A.ACAD_PROG_PRIMARY LIKE '%UN') OR
(A.ACAD_PROG_PRIMARY LIKE '%GN') OR
(A.ACAD_CAREER = 'NCRD' AND A.INSTITUTION = 'UMS07') THEN
CASE
WHEN (A.Admit_Term = Strm OR
(to_number(A.STRM) - to_number(ltrim(A.ADMIT_TERM, ' '))) = 80) THEN
CASE
WHEN A.Institution = 'UMS05' AND A.Prog_Reason = 'RADM' THEN
'Readmit Student'
WHEN A.ADMIT_TYPE IN ('FYR', 'EFY') AND
(A.ACAD_LEVEL_BOT IN ('01', '10') OR
A.Um_Last_Sch_Type IN ('SCD', 'SHS')) THEN
'First-Time Undergraduate Student'
WHEN A.ADMIT_TYPE IN ('GRD', 'GR6', 'GR2', 'DAT') THEN
'Graduate Student'
WHEN A.ADMIT_TYPE = 'LAW' AND A.ACAD_LEVEL_BOT = 'L1' THEN
'Law Student'
WHEN A.ADMIT_TYPE = 'PBA' THEN
'Post-Baccalaureate Student'
WHEN A.ADMIT_TYPE = 'REA' THEN
'Readmit Student'
WHEN A.ADMIT_TYPE = 'TRF' AND
A.Institution <> DECODE(A.Ext_Org_Id,
'0136250',
'UMS05',
'0136246',
'UMS01',
'0136247',
'UMS02',
'0136248',
'UMS03',
'0136249',
'UMS04',
'0136252',
'UMS07',
'0136392',
'UMS06',
'OTHER') THEN
'Transfer Student'
ELSE
'Continuing Student'
END
ELSE
'Continuing Student'
END
ELSE
'Continuing Student'
END ADMIT_TYPE_DESCR_NON_DEGREE,
CASE
WHEN (A.INSTITUTION = 'UMS02' AND A.DEGREE = 'CGS') OR
(A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CERT') OR
(A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CGS') THEN
CASE
WHEN (A.Admit_Term = Strm OR
(to_number(A.STRM) - to_number(ltrim(A.ADMIT_TERM, ' '))) = 80) THEN
CASE
WHEN A.Institution = 'UMS05' AND A.Prog_Reason = 'RADM' THEN
'08'
WHEN A.ADMIT_TYPE IN ('FYR', 'EFY') AND
(A.ACAD_LEVEL_BOT IN ('01', '10') OR
A.Um_Last_Sch_Type IN ('SCD', 'SHS')) THEN
'01'
WHEN A.ADMIT_TYPE IN ('GRD', 'GR6', 'GR2', 'DAT') THEN
'05'
WHEN A.ADMIT_TYPE = 'LAW' AND A.ACAD_LEVEL_BOT = 'L1' THEN
'06'
WHEN A.ADMIT_TYPE = 'PBA' THEN
'04'
WHEN A.ADMIT_TYPE = 'REA' THEN
'08'
WHEN A.ADMIT_TYPE = 'TRF' AND
A.Institution <> DECODE(A.Ext_Org_Id,
'0136250',
'UMS05',
'0136246',
'UMS01',
'0136247',
'UMS02',
'0136248',
'UMS03',
'0136249',
'UMS04',
'0136252',
'UMS07',
'0136392',
'UMS06',
'OTHER') THEN
'09'
ELSE
'10'
END
ELSE
'10'
END
WHEN A.ACAD_PLAN = 'IEI' AND A.INSTITUTION = 'UMS05' THEN
'11'
WHEN A.ACAD_PLAN LIKE 'VIS%' AND A.INSTITUTION = 'UMS05' AND
A.ACAD_CAREER IN ('GRAD', 'UGRD') THEN
'11'
WHEN A.ACAD_PROG_PRIMARY IN ('NDEC',
'NECN',
'NDUG',
'DIST',
'P-NON',
'NONCR',
'NDPR',
'NDGR',
'GRNON',
'NDPN',
'NSEU',
'NDUMA') THEN
'11'
WHEN A.ACAD_PROG_PRIMARY LIKE '%UN' THEN
'11'
WHEN A.ACAD_PROG_PRIMARY LIKE '%GN' THEN
'11'
WHEN A.ACAD_CAREER = 'NCRD' AND A.INSTITUTION = 'UMS07' THEN
'11'
WHEN (A.Admit_Term = Strm OR
(to_number(A.STRM) - to_number(ltrim(A.ADMIT_TERM, ' '))) = 80) THEN
CASE
WHEN A.Institution = 'UMS05' AND A.Prog_Reason = 'RADM' THEN
'08'
WHEN A.ADMIT_TYPE IN ('FYR', 'EFY') AND
(A.ACAD_LEVEL_BOT IN ('01', '10') OR
A.Um_Last_Sch_Type IN ('SCD', 'SHS')) THEN
'01'
WHEN A.ADMIT_TYPE IN ('GRD', 'GR6', 'GR2', 'DAT') THEN
'05'
WHEN A.ADMIT_TYPE = 'LAW' AND A.ACAD_LEVEL_BOT = 'L1' THEN
'06'
WHEN A.ADMIT_TYPE = 'PBA' THEN
'04'
WHEN A.ADMIT_TYPE = 'REA' THEN
'08'
WHEN A.ADMIT_TYPE = 'TRF' AND
A.Institution <> DECODE(A.Ext_Org_Id,
'0136250',
'UMS05',
'0136246',
'UMS01',
'0136247',
'UMS02',
'0136248',
'UMS03',
'0136249',
'UMS04',
'0136252',
'UMS07',
'0136392',
'UMS06',
'OTHER') THEN
'09'
ELSE
'10'
END
ELSE
'10'
END ADMIT_TYPE_SORT,
CASE
WHEN A.Um_Advisr_Name_Sfx <= ' ' AND A.Um_Advisr_Lst_Name > ' ' THEN
A.Um_Advisr_Lst_Name || ', ' || A.Um_Advisr_Fst_Name || '' ||
A.Um_Advisr_Mid_Name
WHEN A.Um_Advisr_Lst_Name > ' ' THEN
A.Um_Advisr_Lst_Name || '' || A.Um_Advisr_Name_Sfx
ELSE
'' || ', ' || A.Um_Advisr_Fst_Name || '' || A.Um_Advisr_Mid_Name
END ADVISOR_NAME_FORMATTED,
CASE
WHEN A.Citizenship_Status = '4' AND
(A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Citizenship_Status = '4' AND A.Residency = 'CAN' THEN
'Canada'
WHEN A.Citizenship_Status = '4' AND
(A.Country_Descr1 = 'United States' AND A.Um_Country_Oth > ' ') THEN
A.Um_Country_Oth
WHEN A.Citizenship_Status = '4' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Citizenship_Status = '4' THEN
'Other Country'
WHEN A.Residency = 'CAN' THEN
'Canada'
WHEN A.Residency = 'INTNL' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Residency = 'INTNL' AND
(A.Country_Descr1 = 'United States' AND A.Um_Country_Oth > ' ') THEN
A.Um_Country_Oth
WHEN A.Residency = 'INTNL' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Residency = 'INTNL' THEN
'Other Country'
WHEN A.Residency = 'OS' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Residency = 'OS' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Residency = 'NEBHE' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
Country_Descr1
WHEN A.Residency = 'NEBHE' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Country <> 'USA' AND A.Country > ' ' THEN
A.Country
ELSE
'United States'
END CALCULATED_ANY_COUNTRY,
CASE
WHEN A.Citizenship_Status = '1' THEN
'United States'
WHEN A.Citizenship_Status = '2' THEN
'United States'
WHEN A.Citizenship_Status = '3' THEN
'United States'
WHEN A.Citizenship_Status = '4' AND
(A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Citizenship_Status = '4' AND A.Residency = 'CAN' THEN
'Canada'
WHEN A.Citizenship_Status = '4' AND
(A.Country_Descr1 = 'United States' AND A.Um_Country_Oth > ' ') THEN
A.Um_Country_Oth
WHEN A.Citizenship_Status = '4' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Citizenship_Status = '4' THEN
'Other Country'
WHEN A.Residency = 'CAN' THEN
'Canada'
WHEN A.Residency = 'INTNL' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Residency = 'INTNL' AND
(A.Country_Descr1 = 'United States' AND A.Um_Country_Oth > ' ') THEN
A.Um_Country_Oth
WHEN A.Residency = 'INTNL' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Residency = 'INTNL' THEN
'Other Country'
WHEN A.Residency = 'OS' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Residency = 'OS' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Residency = 'NEBHE' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Residency = 'NEBHE' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Country <> 'USA' AND A.Country > ' ' THEN
A.Country
ELSE
'United States'
END CALCULATED_COUNTRY,
CASE
WHEN A.County <= ' ' AND A.Institution = 'UMS01' THEN
'Kennebec'
WHEN A.County <= ' ' AND A.Institution = 'UMS02' THEN
'Franklin'
WHEN A.County <= ' ' AND A.Institution = 'UMS03' THEN
'Aroostook'
WHEN A.County <= ' ' AND A.Institution = 'UMS04' THEN
'Washington'
WHEN A.County <= ' ' AND A.Institution = 'UMS05' THEN
'Penobscot'
WHEN A.County <= ' ' AND A.Institution = 'UMS06' THEN
'Cumberland'
WHEN A.County <= ' ' AND A.Institution = 'UMS07' THEN
'Aroostook'
WHEN A.County <= ' ' THEN
'Unknown County'
WHEN LOWER(A.County) LIKE '%dros%' THEN
'Androscoggin'
WHEN LOWER(A.County) LIKE '%aroos%' THEN
'Aroostook'
WHEN LOWER(A.County) LIKE '%cumber%' THEN
'Cumberland'
WHEN LOWER(A.County) LIKE '%frank%' THEN
'Franklin'
WHEN LOWER(A.County) LIKE '%hanc%' THEN
'Hancock'
WHEN LOWER(A.County) LIKE '%bec' THEN
'Kennebec'
WHEN LOWER(A.County) LIKE '%knox' THEN
'Knox'
WHEN LOWER(A.County) LIKE '%lincoln' THEN
'Lincoln'
WHEN LOWER(A.County) LIKE '%oxford' THEN
'Oxford'
WHEN LOWER(A.County) LIKE '%penob%' THEN
'Penobscot'
WHEN LOWER(A.County) LIKE '%piscat%' THEN
'Piscataquis'
WHEN LOWER(A.County) LIKE '%saga%' THEN
'Sagadahoc'
WHEN LOWER(A.County) LIKE '%somer%' THEN
'Somerset'
WHEN LOWER(A.County) LIKE '%waldo' THEN
'Waldo'
WHEN LOWER(A.County) LIKE '%wash%' THEN
'Washington'
WHEN LOWER(A.County) LIKE '%york' THEN
'York'
WHEN A.County NOT IN ('Androscoggin',
'Aroostook',
'Cumberland',
'Franklin',
'Hancock',
'Kennebec',
'Knox',
'Lincoln',
'Oxford',
'Penobscot',
'Piscataquis',
'Sagadahoc',
'Somerset',
'Waldo',
'Washington',
'York') THEN
'Unknown County'
ELSE
A.County
END CALCULATED_COUNTY,
CASE
WHEN CASE
WHEN A.Citizenship_Status = '1' THEN
'United States'
WHEN A.Citizenship_Status = '2' THEN
'United States'
WHEN A.Citizenship_Status = '3' THEN
'United States'
WHEN A.Citizenship_Status = '4' AND
(A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Citizenship_Status = '4' AND A.Residency = 'CAN' THEN
'Canada'
WHEN A.Citizenship_Status = '4' AND
(A.Country_Descr1 = 'United States' AND A.Um_Country_Oth > ' ') THEN
A.Um_Country_Oth
WHEN A.Citizenship_Status = '4' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Citizenship_Status = '4' THEN
'Other Country'
WHEN A.Residency = 'CAN' THEN
'Canada'
WHEN A.Residency = 'INTNL' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Residency = 'INTNL' AND
(A.Country_Descr1 = 'United States' AND A.Um_Country_Oth > ' ') THEN
A.Um_Country_Oth
WHEN A.Residency = 'INTNL' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Residency = 'INTNL' THEN
'Other Country'
WHEN A.Residency = 'OS' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Residency = 'OS' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Residency = 'NEBHE' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Residency = 'NEBHE' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Country <> 'USA' AND A.Country > ' ' THEN
A.Country
ELSE
'United States'
END <> 'United States' THEN
'Non-USA'
WHEN A.Residency = 'OS' AND A.State <= ' ' THEN
'Other State'
WHEN A.Residency = 'NEBHE' AND A.State <= ' ' THEN
'Other State'
WHEN A.State <> 'ME' AND A.State_Descr1 > ' ' THEN
A.State_Descr1
WHEN A.State = 'NH' THEN
'New Hampshire'
WHEN A.State = 'VT' THEN
'Vermont'
WHEN A.State = 'MA' THEN
'Massachusetts'
WHEN A.State = 'CT' THEN
'Connecticut'
WHEN A.State = 'RI' THEN
'Rhode Island'
WHEN A.State = 'ME' THEN
'Maine'
WHEN A.State <> 'ME' AND A.State_Descr1 <= ' ' THEN
'Other State'
WHEN A.Residency = 'IS' THEN
'Maine'
ELSE
'Other State'
END CALCULATED_STATE,
DECODE(A.Institution,
'UMS01',
'UMA',
'UMS02',
'UMF',
'UMS03',
'UMFK',
'UMS04',
'UMM',
'UMS07',
'UMPI',
'UMS05',
'UM',
'UMS06',
'USM',
'Other') Institution_SHORT,
CASE
WHEN CASE
WHEN A.Citizenship_Status = '1' THEN
'United States'
WHEN A.Citizenship_Status = '2' THEN
'United States'
WHEN A.Citizenship_Status = '3' THEN
'United States'
WHEN A.Citizenship_Status = '4' AND
(A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Citizenship_Status = '4' AND A.Residency = 'CAN' THEN
'Canada'
WHEN A.Citizenship_Status = '4' AND
(A.Country_Descr1 = 'United States' AND A.Um_Country_Oth > ' ') THEN
A.Um_Country_Oth
WHEN A.Citizenship_Status = '4' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Citizenship_Status = '4' THEN
'Other Country'
WHEN A.Residency = 'CAN' THEN
'Canada'
WHEN A.Residency = 'INTNL' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Residency = 'INTNL' AND
(A.Country_Descr1 = 'United States' AND A.Um_Country_Oth > ' ') THEN
A.Um_Country_Oth
WHEN A.Residency = 'INTNL' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Residency = 'INTNL' THEN
'Other Country'
WHEN A.Residency = 'OS' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Residency = 'OS' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Residency = 'NEBHE' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
A.Country_Descr1
WHEN A.Residency = 'NEBHE' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Country <> 'USA' AND A.Country > ' ' THEN
A.Country
ELSE
'United States'
END IN ('Canada', 'CAN') THEN
CASE
WHEN A.STATE = 'AB' THEN
'Alberta'
WHEN A.STATE = 'BC' THEN
'British Columbia'
WHEN A.STATE = 'MB' THEN
'Manitoba'
WHEN A.STATE = 'NB' THEN
'New Brunswick'
WHEN A.STATE = 'NF' THEN
'Newfoundland'
WHEN A.STATE = 'NL' THEN
'Newfoundland'
WHEN A.STATE = 'NS' THEN
'Nova Scotia'
WHEN A.STATE = 'NT' THEN
'Northwest Territories'
WHEN A.STATE = 'NN' THEN
'Nunavut'
WHEN A.STATE = 'NU' THEN
'Nunavut'
WHEN A.STATE = 'ON' THEN
'Ontario'
WHEN A.STATE = 'PE' THEN
'Prince Edward Island'
WHEN A.STATE = 'QC' THEN
'Quebec'
WHEN A.STATE = 'SK' THEN
'Saskatchewan'
WHEN A.STATE = 'YT' THEN
'Yukon'
ELSE
'Canada'
END
ELSE
'Not Canada'
END CANADA,
CASE
WHEN A.Citizenship_Status = '4' THEN
'Alien Temporary'
WHEN A.Citizenship_Status = '3' THEN
'Alien Permanent'
WHEN A.Citizenship_Status = '2' THEN
'Naturalized'
WHEN A.Citizenship_Status = '1' THEN
'Native'
WHEN A.Citizenship_Status = 'N' THEN
'Not Indicated'
ELSE
' '
END CITIZENSHIP_STATUS_DESCR,
CASE
WHEN A.Citizenship_Status = '1' THEN
'USA'
WHEN A.Citizenship_Status = '2' THEN
'USA'
WHEN A.Citizenship_Status = '3' THEN
'USA'
WHEN A.Citizenship_Status = '4' AND
(A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
'Non-USA'
WHEN A.Citizenship_Status = '4' AND A.Residency = 'CAN' THEN
'Canada'
WHEN A.Citizenship_Status = '4' AND
(A.Country_Descr1 = 'United States' AND A.Um_Country_Oth > ' ') THEN
'Non-USA'
WHEN A.Citizenship_Status = '4' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
'Non-USA'
WHEN A.Citizenship_Status = '4' THEN
'Other Country'
WHEN A.Residency = 'CAN' THEN
'Non-USA'
WHEN A.Residency = 'INTNL' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
'Non-USA'
WHEN A.Residency = 'INTNL' AND
(A.Country_Descr1 = 'United States' AND A.Um_Country_Oth > ' ') THEN
'Non-USA'
WHEN A.Residency = 'INTNL' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
'Non-USA'
WHEN A.Residency = 'INTNL' THEN
'Non-USA'
WHEN A.Residency = 'OS' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
'Non-USA'
WHEN A.Residency = 'OS' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
'Non-USA'
WHEN A.Residency = 'NEBHE' AND (A.Country_Descr1 <> 'United States' AND
A.Country_Descr1 > ' ') THEN
'Non-USA'
WHEN A.Residency = 'NEBHE' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
'Non-USA'
WHEN A.Country <> 'USA' AND A.Country > ' ' THEN
'Non-USA'
ELSE
'USA'
END COUNTRY_STATUS,
CASE
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 14 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 17 THEN
'Under 18'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 18 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 19 THEN
'18 - 19'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 20 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 21 THEN
'20 - 21'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 22 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 24 THEN
'22 - 24'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 25 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 29 THEN
'25 - 29'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 30 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 34 THEN
'30 - 34'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 35 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 39 THEN
'35 - 39'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 40 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 49 THEN
'40 - 49'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 50 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 64 THEN
'50 - 64'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 65 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 105 THEN
'65 and Over'
ELSE
'Unknown'
END CURRENT_AGE_RANGE,
CASE
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 14 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 17 THEN
'01'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 18 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 19 THEN
'02'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 20 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 21 THEN
'03'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 22 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 24 THEN
'04'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 25 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 29 THEN
'05'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 30 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 34 THEN
'06'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 35 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 39 THEN
'07'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 40 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 49 THEN
'08'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 50 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 64 THEN
'09'
WHEN TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) >= 65 AND
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) <= 105 THEN
'10'
ELSE
'11'
END CURRENT_AGE_RANGE_SORT,
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25) CURRENT_AGE,
CASE
WHEN A.INSTITUTION = 'UMS02' AND A.DEGREE = 'CGS' THEN
'Degree'
WHEN A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CERT' THEN
'Degree'
WHEN A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CGS' THEN
'Degree'
WHEN A.ACAD_PLAN = 'IEI' AND A.INSTITUTION = 'UMS05' THEN
'Non-Degree'
WHEN A.ACAD_PLAN LIKE 'VIS%' AND A.INSTITUTION = 'UMS05' THEN
'Non-Degree'
WHEN A.ACAD_PROG_PRIMARY = 'NDPR' AND A.Institution = 'UMS06' THEN
'Non-Degree'
WHEN A.ACAD_PROG_PRIMARY IN ('NDEC',
'NECN',
'NDUG',
'DIST',
'P-NON',
'NONCR',
'NDPR',
'NDGR',
'GRNON',
'NDPN',
'NSEU',
'NDUMA') THEN
'Non-Degree'
WHEN A.ACAD_PROG_PRIMARY LIKE '%UN' THEN
'Non-Degree'
WHEN A.ACAD_PROG_PRIMARY LIKE '%GN' THEN
'Non-Degree'
WHEN A.ACAD_CAREER = 'NCRD' AND A.INSTITUTION = 'UMS07' THEN
'Non-Degree'
ELSE
'Degree'
END DEGREE_NONDEGREE,
CASE
WHEN A.Citizenship_Status = '4' THEN
'Nonresident Alien'
WHEN A.Ethnic_Group = '3' THEN
'Hispanic/Latino'
WHEN A.Hisp_Latino = 'Y' THEN
'Hispanic/Latino'
WHEN A.Um_Multiple_Ethnic = 'Y' THEN
'2 or More Races'
WHEN A.Ethnic_Group = '1' THEN
'White'
WHEN A.Ethnic_Group = '2' THEN
'Black/African American'
WHEN A.Ethnic_Group = '4' THEN
'Asian'
WHEN A.Ethnic_Group = '5' THEN
'American Indian/Alaska Native'
WHEN A.Ethnic_Group = '7' THEN
'Native Hawaiian/Pacific Islander'
WHEN A.Ethnic_Group = '6' THEN
'Unknown'
ELSE
'Unknown'
END ETHNIC_GROUP_DESCR,
CASE
WHEN A.Citizenship_Status = '4' THEN
'7'
WHEN A.Ethnic_Group = '3' THEN
'3'
WHEN A.Hisp_Latino = 'Y' THEN
'3'
WHEN A.Um_Multiple_Ethnic = 'Y' THEN
'8'
WHEN A.Ethnic_Group = '1' THEN
'1'
WHEN A.Ethnic_Group = '2' THEN
'2'
WHEN A.Ethnic_Group = '4' THEN
'4'
WHEN A.Ethnic_Group = '5' THEN
'5'
WHEN A.Ethnic_Group = '7' THEN
'6'
WHEN A.Ethnic_Group = '6' THEN
'9'
ELSE
'9'
END ETHNIC_GROUP_SORT,
CASE
WHEN A.ADMIT_TYPE = 'TRF' AND
(A.Admit_Term = Strm OR
(to_number(A.STRM) - to_number(ltrim(A.ADMIT_TERM, ' '))) = 80) THEN
CASE
WHEN A.Ext_Org_Id IN ('0136246',
'0136247',
'0136248',
'0136249',
'0136250',
'0136252',
'0136392') THEN
'From UM System'
WHEN A.Ext_Org_Id IN ('0133747',
'0135621',
'0136836',
'0132691',
'0132013',
'0134784',
'0136593') THEN
'From ME CC System'
WHEN A.Ext_Org_Id = '0134088' THEN
'From ME Maritime Academy'
ELSE
'Other External'
END
ELSE
' '
END EXTERNAL_ORGANIZATION,
CASE
WHEN A.ADMIT_TYPE = 'TRF' AND
(A.Admit_Term = Strm OR
(to_number(A.STRM) - to_number(ltrim(A.ADMIT_TERM, ' '))) = 80) THEN
CASE
WHEN A.Ext_Org_Id = '0136246' THEN
'From UMA'
WHEN A.Ext_Org_Id = '0136247' THEN
'From UMF'
WHEN A.Ext_Org_Id = '0136248' THEN
'From UMFK'
WHEN A.Ext_Org_Id = '0136249' THEN
'From UMM'
WHEN A.Ext_Org_Id = '0136250' THEN
'From UM'
WHEN A.Ext_Org_Id = '0136252' THEN
'From UMPI'
WHEN A.Ext_Org_Id = '0136392' THEN
'From USM'
WHEN A.Ext_Org_Id = '0133747' THEN
'From KVCC'
WHEN A.Ext_Org_Id = '0135621' THEN
'From SMCC'
WHEN A.Ext_Org_Id = '0136836' THEN
'From YCCC'
WHEN A.Ext_Org_Id = '0132691' THEN
'From EMCC'
WHEN A.Ext_Org_Id = '0132013' THEN
'From CMCC'
WHEN A.Ext_Org_Id = '0134784' THEN
'From NMCC'
WHEN A.Ext_Org_Id = '0136593' THEN
'From WCCC'
WHEN A.Ext_Org_Id = '0134088' THEN
'From MMA'
ELSE
'Other External'
END
ELSE
' '
END EXTERNAL_ORGANIZATION_DTL,
CASE
WHEN A.ADMIT_TYPE = 'TRF' AND
(A.Admit_Term = Strm OR
(to_number(A.STRM) - to_number(ltrim(A.ADMIT_TERM, ' '))) = 80) THEN
CASE
WHEN A.Ext_Org_Id IN ('0136246',
'0136247',
'0136248',
'0136249',
'0136250',
'0136252',
'0136392') THEN
'Internal'
ELSE
'External'
END
ELSE
' '
END EXTERNAL_ORGANIZATION_SUM,
DECODE(A.Ext_Org_Id,
'0136250',
'UMS05',
'0136246',
'UMS01',
'0136247',
'UMS02',
'0136248',
'UMS03',
'0136249',
'UMS04',
'0136252',
'UMS07',
'0136392',
'UMS06',
'OTHER') EXT_ORG_INST,
CASE
WHEN A.Admit_Term = A.Strm OR (A.STRM - CASE
WHEN A.Admit_Term = ' ' THEN
'0000'
ELSE
a.admit_term
END) = 80 THEN
'Y'
ELSE
'N'
END FIRST_TERM,
CASE
WHEN (A.INSTITUTION = 'UMS02' AND A.DEGREE = 'CGS') OR
(A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CERT') OR
(A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CGS') THEN
CASE
WHEN (A.Admit_Term = Strm OR
(to_number(A.STRM) - to_number(ltrim(A.ADMIT_TERM, ' '))) = 80) THEN
CASE
WHEN A.Institution = 'UMS05' AND A.Prog_Reason = 'RADM' THEN
'Y'
WHEN A.ADMIT_TYPE IN ('FYR', 'EFY') AND
(A.ACAD_LEVEL_BOT IN ('01', '10') OR
A.Um_Last_Sch_Type IN ('SCD', 'SHS')) THEN
'Y'
WHEN A.ADMIT_TYPE IN ('GRD', 'GR6', 'GR2', 'DAT') THEN
'Y'
WHEN A.ADMIT_TYPE = 'LAW' AND A.ACAD_LEVEL_BOT = 'L1' THEN
'Y'
WHEN A.ADMIT_TYPE = 'PBA' THEN
'Y'
WHEN A.ADMIT_TYPE = 'REA' THEN
'Y'
WHEN A.ADMIT_TYPE = 'TRF' AND
A.Institution <> DECODE(A.Ext_Org_Id,
'0136250',
'UMS05',
'0136246',
'UMS01',
'0136247',
'UMS02',
'0136248',
'UMS03',
'0136249',
'UMS04',
'0136252',
'UMS07',
'0136392',
'UMS06',
'OTHER') THEN
'Y'
ELSE
'N'
END
ELSE
'N'
END
WHEN A.ACAD_PLAN = 'IEI' AND A.INSTITUTION = 'UMS05' THEN
'N'
WHEN A.ACAD_PLAN LIKE 'VIS%' AND A.INSTITUTION = 'UMS05' AND
A.ACAD_CAREER IN ('GRAD', 'UGRD') THEN
'N'
WHEN A.ACAD_PROG_PRIMARY IN ('NDEC',
'NECN',
'NDUG',
'DIST',
'P-NON',
'NONCR',
'NDPR',
'NDGR',
'GRNON',
'NDPN',
'NSEU',
'NDUMA') THEN
'N'
WHEN A.ACAD_PROG_PRIMARY LIKE '%UN' THEN
'N'
WHEN A.ACAD_PROG_PRIMARY LIKE '%GN' THEN
'N'
WHEN A.ACAD_CAREER = 'NCRD' AND A.INSTITUTION = 'UMS07' THEN
'N'
WHEN (A.Admit_Term = Strm OR
(to_number(A.STRM) - to_number(ltrim(A.ADMIT_TERM, ' '))) = 80) THEN
CASE
WHEN A.Institution = 'UMS05' AND A.Prog_Reason = 'RADM' THEN
'Y'
WHEN A.ADMIT_TYPE IN ('FYR', 'EFY') AND
(A.ACAD_LEVEL_BOT IN ('01', '10') OR
A.Um_Last_Sch_Type IN ('SCD', 'SHS')) THEN
'Y'
WHEN A.ADMIT_TYPE IN ('GRD', 'GR6', 'GR2', 'DAT') THEN
'Y'
WHEN A.ADMIT_TYPE = 'LAW' AND A.ACAD_LEVEL_BOT = 'L1' THEN
'Y'
WHEN A.ADMIT_TYPE = 'PBA' THEN
'Y'
WHEN A.ADMIT_TYPE = 'REA' THEN
'Y'
WHEN A.ADMIT_TYPE = 'TRF' AND
A.Institution <> DECODE(A.Ext_Org_Id,
'0136250',
'UMS05',
'0136246',
'UMS01',
'0136247',
'UMS02',
'0136248',
'UMS03',
'0136249',
'UMS04',
'0136252',
'UMS07',
'0136392',
'UMS06',
'OTHER') THEN
'Y'
ELSE
'N'
END
ELSE
'N'
END FIRST_TIME,
CASE
WHEN A.INSTITUTION = 'UMS02' AND A.DEGREE = 'CGS' THEN
'N'
WHEN A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CERT' THEN
'N'
WHEN A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CGS' THEN
'N'
WHEN ((A.ACAD_PLAN = 'IEI' AND A.INSTITUTION = 'UMS05') OR
(A.ACAD_PLAN LIKE 'VIS%' AND A.INSTITUTION = 'UMS05') OR
A.ACAD_PROG_PRIMARY IN ('NDEC',
'NECN',
'NDUG',
'DIST',
'P-NON',
'NONCR',
'NDPR',
'NDGR',
'GRNON',
'NDPN',
'NSEU',
'NDUMA') OR
A.ACAD_PROG_PRIMARY LIKE '%UN' OR
A.ACAD_PROG_PRIMARY LIKE '%GN' OR
(A.ACAD_CAREER = 'NCRD' AND A.INSTITUTION = 'UMS07')) THEN
CASE
WHEN (A.Admit_Term = Strm OR
(to_number(A.STRM) - to_number(ltrim(A.ADMIT_TERM, ' '))) = 80) THEN
CASE
WHEN A.Institution = 'UMS05' AND A.Prog_Reason = 'RADM' THEN
'Y'
WHEN A.ADMIT_TYPE IN ('FYR', 'EFY') AND
(A.ACAD_LEVEL_BOT IN ('01', '10') OR
A.Um_Last_Sch_Type IN ('SCD', 'SHS')) THEN
'Y'
WHEN A.ADMIT_TYPE IN ('GRD', 'GR6', 'GR2', 'DAT') THEN
'Y'
WHEN A.ADMIT_TYPE = 'LAW' AND A.ACAD_LEVEL_BOT = 'L1' THEN
'Y'
WHEN A.ADMIT_TYPE = 'PBA' THEN
'Y'
WHEN A.ADMIT_TYPE = 'REA' THEN
'Y'
WHEN A.ADMIT_TYPE = 'TRF' AND
A.Institution <> DECODE(A.Ext_Org_Id,
'0136250',
'UMS05',
'0136246',
'UMS01',
'0136247',
'UMS02',
'0136248',
'UMS03',
'0136249',
'UMS04',
'0136252',
'UMS07',
'0136392',
'UMS06',
'OTHER') THEN
'Y'
ELSE
'N'
END
ELSE
'N'
END
ELSE
'N'
END FIRST_TIME_NONDEGREE,
A.Um_Unt_Tak_Prg_Rc / (CASE
WHEN A.Institution = 'UMS02' AND A.Acad_plan = 'PSY-MA' THEN
15
WHEN A.Acad_Level_Bot IN ('GR', 'MAS', 'PHD') THEN
9
WHEN A.Acad_Level_Bot IN ('L1', 'L2', 'L3') THEN
15
WHEN A.Institution = 'UMS02' THEN
16
ELSE
15
END) FTE,
CASE
WHEN A.Institution = 'UMS02' AND A.Acad_plan = 'PSY-MA' THEN
15
WHEN A.Acad_Level_Bot IN ('GR', 'MAS', 'PHD') THEN
9
WHEN A.Acad_Level_Bot IN ('L1', 'L2', 'L3') THEN
15
WHEN A.Institution = 'UMS02' THEN
16
ELSE
15
END FTE_DIVISOR,
CASE
WHEN A.Acad_Level_Bot = 'GR' AND A.Um_Unt_Tak_Prg_Rc >= 6 THEN
'Full-Time'
WHEN A.Acad_Level_Bot = 'MAS' AND A.Um_Unt_Tak_Prg_Rc >= 6 THEN
'Full-Time'
WHEN A.Acad_Level_Bot = 'PHD' AND A.Um_Unt_Tak_Prg_Rc >= 6 THEN
'Full-Time'
WHEN A.Um_Unt_Tak_Prg_Rc >= 12 THEN
'Full-Time'
ELSE
'Part-Time'
END FULL_PART,
CASE
WHEN A.Sex = 'M' THEN
'Male'
ELSE
'Female'
END GENDER,
TO_NUMBER(TO_CHAR(A.Graduation_Dt, 'YYYY'), '9999') GRADUATION_DATE_YEAR,
1 HEADCOUNT,
CASE
WHEN A.RESIDENCY = 'MEONL' AND QB.RESIDENCY = 'IS' THEN
'In-State'
WHEN A.Residency = 'IS' THEN
'In-State'
ELSE
'Out-of-State'
END IN_OUT,
CASE
WHEN A.County <= ' ' AND A.Institution = 'UMS01' THEN
'Y'
WHEN A.County <= ' ' AND A.Institution = 'UMS02' THEN
'Y'
WHEN A.County <= ' ' AND A.Institution = 'UMS03' THEN
'Y'
WHEN A.County <= ' ' AND A.Institution = 'UMS04' THEN
'Y'
WHEN A.County <= ' ' AND A.Institution = 'UMS05' THEN
'Y'
WHEN A.County <= ' ' AND A.Institution = 'UMS06' THEN
'Y'
WHEN A.County <= ' ' AND A.Institution = 'UMS07' THEN
'Y'
WHEN A.County <= ' ' THEN
'N'
WHEN LOWER(A.County) LIKE '%dros%' THEN
'Y'
WHEN LOWER(A.County) LIKE '%aroos%' THEN
'Y'
WHEN LOWER(A.County) LIKE '%cumber%' THEN
'Y'
WHEN LOWER(A.County) LIKE '%frank%' THEN
'Y'
WHEN LOWER(A.County) LIKE '%hanc%' THEN
'Y'
WHEN LOWER(A.County) LIKE '%bec' THEN
'Y'
WHEN LOWER(A.County) LIKE '%knox' THEN
'Y'
WHEN LOWER(A.County) LIKE '%lincoln' THEN
'Y'
WHEN LOWER(A.County) LIKE '%oxford' THEN
'Y'
WHEN LOWER(A.County) LIKE '%penob%' THEN
'Y'
WHEN LOWER(A.County) LIKE '%piscat%' THEN
'Y'
WHEN LOWER(A.County) LIKE '%saga%' THEN
'Y'
WHEN LOWER(A.County) LIKE '%somer%' THEN
'Y'
WHEN LOWER(A.County) LIKE '%waldo' THEN
'Y'
WHEN LOWER(A.County) LIKE '%wash%' THEN
'Y'
WHEN LOWER(A.County) LIKE '%york' THEN
'Y'
WHEN A.County NOT IN ('Androscoggin',
'Aroostook',
'Cumberland',
'Franklin',
'Hancock',
'Kennebec',
'Knox',
'Lincoln',
'Oxford',
'Penobscot',
'Piscataquis',
'Sagadahoc',
'Somerset',
'Waldo',
'Washington',
'York') THEN
'N'
ELSE
'N'
END MAIN_COUNTY,
CASE
WHEN A.INSTITUTION = 'UMS02' AND A.DEGREE = 'CGS' THEN
'Masters'
WHEN A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CERT' THEN
'Baccalaureate'
WHEN A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CGS' THEN
'Masters'
WHEN A.INSTITUTION = 'UMS05' AND A.ACAD_PLAN = 'IEI' THEN
'Non-Degree Undergraduate'
WHEN A.INSTITUTION = 'UMS05' AND A.ACAD_PLAN LIKE 'VIS%' AND
A.ACAD_CAREER = 'GRAD' THEN
'Non-Degree Graduate'
WHEN A.INSTITUTION = 'UMS05' AND A.ACAD_PLAN LIKE 'VIS%' AND
A.ACAD_CAREER = 'UGRD' THEN
'Non-Degree Undergraduate'
WHEN A.INSTITUTION = 'UMS06' AND
A.ACAD_PROG_PRIMARY IN ('NDPR', 'NDPN') THEN
'Non-Degree Law'
WHEN A.ACAD_PROG_PRIMARY IN ('NDEC',
'NECN',
'NDUG',
'DIST',
'P-NON',
'NONCR',
'NDPR',
'NSEU',
'NDUMA') THEN
'Non-Degree Undergraduate'
WHEN A.ACAD_PROG_PRIMARY IN ('NDGR', 'GRNON') THEN
'Non-Degree Graduate'
WHEN A.ACAD_PROG_PRIMARY LIKE '%UN' THEN
'Non-Degree Undergraduate'
WHEN A.ACAD_PROG_PRIMARY LIKE '%GN' THEN
'Non-Degree Graduate'
WHEN A.LEVEL_LOAD_RULE IN ('UG2YR', 'UGAS', 'UGA', 'AA') THEN
'Associate'
WHEN A.LEVEL_LOAD_RULE IN ('UG4YR', 'UG') THEN
'Baccalaureate'
WHEN A.Acad_Level_Bot = 'PHD' THEN
'Doctorate'
WHEN A.LEVEL_LOAD_RULE IN ('GR', 'GRAD') THEN
'Masters'
WHEN A.Level_Load_Rule = 'LAW' THEN
'Law'
WHEN A.ACAD_CAREER = 'NCRD' AND A.INSTITUTION = 'UMS07' THEN
'Non-Degree Undergraduate'
WHEN A.Acad_Career = 'UGRD' THEN
'Baccalaureate'
WHEN A.Acad_Career = 'GRAD' THEN
'Masters'
WHEN A.Acad_Career = 'LAW' THEN
'Law'
WHEN A.Acad_Career = 'PBAC' THEN
'Baccalaureate'
ELSE
'Other'
END NEASC_LEVEL,
CASE
WHEN A.INSTITUTION = 'UMS02' AND A.DEGREE = 'CGS' THEN
'13'
WHEN A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CERT' THEN
'11'
WHEN A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CGS' THEN
'13'
WHEN A.INSTITUTION = 'UMS05' AND A.ACAD_PLAN = 'IEI' THEN
'12'
WHEN A.INSTITUTION = 'UMS05' AND A.ACAD_PLAN LIKE 'VIS%' AND
A.ACAD_CAREER = 'GRAD' THEN
'16'
WHEN A.INSTITUTION = 'UMS05' AND A.ACAD_PLAN LIKE 'VIS%' AND
A.ACAD_CAREER = 'UGRD' THEN
'12'
WHEN A.INSTITUTION = 'UMS06' AND
A.ACAD_PROG_PRIMARY IN ('NDPR', 'NDPN') THEN
'17'
WHEN A.ACAD_PROG_PRIMARY IN ('NDEC',
'NECN',
'NDUG',
'DIST',
'P-NON',
'NONCR',
'NDPR',
'NSEU',
'NDUMA') THEN
'12'
WHEN A.ACAD_PROG_PRIMARY IN ('NDGR', 'GRNON') THEN
'16'
WHEN A.ACAD_PROG_PRIMARY LIKE '%UN' THEN
'12'
WHEN A.ACAD_PROG_PRIMARY LIKE '%GN' THEN
'16'
WHEN A.LEVEL_LOAD_RULE IN ('UG2YR', 'UGAS', 'UGA', 'AA') THEN
'10'
WHEN A.LEVEL_LOAD_RULE IN ('UG4YR', 'UG') THEN
'11'
WHEN A.Acad_Level_Bot = 'PHD' THEN
'14'
WHEN A.LEVEL_LOAD_RULE IN ('GR', 'GRAD') THEN
'13'
WHEN A.Level_Load_Rule = 'LAW' THEN
'15'
WHEN A.ACAD_CAREER = 'NCRD' AND A.INSTITUTION = 'UMS07' THEN
'12'
WHEN A.Acad_Career = 'UGRD' THEN
'11'
WHEN A.Acad_Career = 'GRAD' THEN
'13'
WHEN A.Acad_Career = 'LAW' THEN
'15'
WHEN A.Acad_Career = 'PBAC' THEN
'11'
ELSE
'20'
END NEASC_LEVEL_SORT,
CASE
WHEN A.Acad_Plan LIKE 'NUR%' THEN
'Y'
WHEN A.Acad_Prog LIKE 'NUR%' THEN
'Y'
ELSE
'N'
END NURSING,
DECODE(A.Residency,
'IS',
'In-State',
'OS',
'Out-of-State',
'CAN',
'Canadian',
'NEBHE',
'NEBHE',
'INTNL',
'International',
'ONLIN',
'Online',
'MEONL',
'Academic Partnerships',
'Out-of-State') RESIDENCY_DESCR,
DECODE(A.Residency,
'IS',
'10',
'OS',
'20',
'CAN',
'40',
'NEBHE',
'30',
'INTNL',
'50',
'ONLIN',
'60',
'MEONL',
'70',
'20') RESIDENCY_DESCR_SORT,
CASE
WHEN SUBSTR(A.Strm, 3, 1) = '1' THEN
TO_NUMBER('20' || SUBSTR(A.Strm, 1, 2), '9999') - 1
ELSE
TO_NUMBER('20' || SUBSTR(A.Strm, 1, 2), '9999')
END STRM_YEAR,
CASE
WHEN A.Name_Suffix <= ' ' AND A.Last_Name > ' ' THEN
A.LAST_NAME || ', ' || A.FIRST_NAME || ' ' || A.Middle_Name
WHEN A.LAST_NAME > ' ' THEN
A.LAST_NAME || ' ' || A.Name_Suffix || ', ' || A.FIRST_NAME || ' ' ||
A.Middle_Name
ELSE
''
END STUDENT_NAME_FORMATTED,
CASE
WHEN A.INSTITUTION = 'UMS02' AND A.DEGREE = 'CGS' THEN
'Graduate'
WHEN A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CERT' THEN
'Baccalaureate'
WHEN A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CGS' THEN
'Graduate'
WHEN A.INSTITUTION = 'UMS05' AND A.ACAD_PLAN = 'IEI' THEN
'Non-Degree Undergraduate'
WHEN A.INSTITUTION = 'UMS05' AND A.ACAD_PLAN LIKE 'VIS%' AND
A.ACAD_CAREER = 'GRAD' THEN
'Non-Degree Graduate'
WHEN A.INSTITUTION = 'UMS05' AND A.ACAD_PLAN LIKE 'VIS%' AND
A.ACAD_CAREER = 'UGRD' THEN
'Non-Degree Undergraduate'
WHEN A.INSTITUTION = 'UMS06' AND
A.ACAD_PROG_PRIMARY IN ('NDPR', 'NDPN') THEN
'Non-Degree Law'
WHEN A.ACAD_PROG_PRIMARY IN ('NDEC',
'NECN',
'NDUG',
'DIST',
'P-NON',
'NONCR',
'NDPR',
'NSEU',
'NDUMA') THEN
'Non-Degree Undergraduate'
WHEN A.ACAD_PROG_PRIMARY IN ('NDGR', 'GRNON') THEN
'Non-Degree Graduate'
WHEN A.ACAD_PROG_PRIMARY LIKE '%UN' THEN
'Non-Degree Undergraduate'
WHEN A.ACAD_PROG_PRIMARY LIKE '%GN' THEN
'Non-Degree Graduate'
WHEN A.LEVEL_LOAD_RULE IN ('UG2YR', 'UGAS', 'UGA', 'AA') THEN
'Associate'
WHEN A.LEVEL_LOAD_RULE IN ('UG4YR', 'UG') THEN
'Baccalaureate'
WHEN A.Acad_Level_Bot = 'PHD' THEN
'Graduate'
WHEN A.LEVEL_LOAD_RULE IN ('GR', 'GRAD') THEN
'Graduate'
WHEN A.Level_Load_Rule = 'LAW' THEN
'Law'
WHEN A.ACAD_CAREER = 'NCRD' AND A.INSTITUTION = 'UMS07' THEN
'Non-Degree Undergraduate'
WHEN A.Acad_Career = 'UGRD' THEN
'Baccalaureate'
WHEN A.Acad_Career = 'GRAD' THEN
'Graduate'
WHEN A.Acad_Career = 'LAW' THEN
'Law'
WHEN A.Acad_Career = 'PBAC' THEN
'Baccalaureate'
ELSE
'Other'
END STUDENT_LEVEL,
CASE
WHEN A.INSTITUTION = 'UMS02' AND A.DEGREE = 'CGS' THEN
'13'
WHEN A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CERT' THEN
'11'
WHEN A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CGS' THEN
'13'
WHEN A.INSTITUTION = 'UMS05' AND A.ACAD_PLAN = 'IEI' THEN
'12'
WHEN A.INSTITUTION = 'UMS05' AND A.ACAD_PLAN LIKE 'VIS%' AND
A.ACAD_CAREER = 'GRAD' THEN
'14'
WHEN A.INSTITUTION = 'UMS05' AND A.ACAD_PLAN LIKE 'VIS%' AND
A.ACAD_CAREER = 'UGRD' THEN
'12'
WHEN A.INSTITUTION = 'UMS06' AND
A.ACAD_PROG_PRIMARY IN ('NDPR', 'NDPN') THEN
'16'
WHEN A.ACAD_PROG_PRIMARY IN ('NDEC',
'NECN',
'NDUG',
'DIST',
'P-NON',
'NONCR',
'NDPR',
'NSEU',
'NDUMA') THEN
'12'
WHEN A.ACAD_PROG_PRIMARY IN ('NDGR', 'GRNON') THEN
'14'
WHEN A.ACAD_PROG_PRIMARY LIKE '%UN' THEN
'12'
WHEN A.ACAD_PROG_PRIMARY LIKE '%GN' THEN
'14'
WHEN A.LEVEL_LOAD_RULE IN ('UG2YR', 'UGAS', 'UGA', 'AA') THEN
'10'
WHEN A.LEVEL_LOAD_RULE IN ('UG4YR', 'UG') THEN
'11'
WHEN A.Acad_Level_Bot = 'PHD' THEN
'13'
WHEN A.LEVEL_LOAD_RULE IN ('GR', 'GRAD') THEN
'13'
WHEN A.Level_Load_Rule = '15' THEN
'15'
WHEN A.ACAD_CAREER = 'NCRD' AND A.INSTITUTION = 'UMS07' THEN
'12'
WHEN A.Acad_Career = 'UGRD' THEN
'11'
WHEN A.Acad_Career = 'GRAD' THEN
'13'
WHEN A.Acad_Career = '15' THEN
'15'
WHEN A.Acad_Career = 'PBAC' THEN
'11'
ELSE
'20'
END STUDENT_LEVEL_SORT,
CASE
WHEN A.INSTITUTION = 'UMS02' AND A.DEGREE = 'CGS' THEN
'Graduate'
WHEN A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CERT' THEN
'Undergraduate'
WHEN A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CGS' THEN
'Graduate'
WHEN A.INSTITUTION = 'UMS05' AND A.ACAD_PLAN = 'IEI' THEN
'Undergraduate'
WHEN A.INSTITUTION = 'UMS05' AND A.ACAD_PLAN LIKE 'VIS%' AND
A.ACAD_CAREER = 'GRAD' THEN
'Graduate'
WHEN A.INSTITUTION = 'UMS05' AND A.ACAD_PLAN LIKE 'VIS%' AND
A.ACAD_CAREER = 'UGRD' THEN
'Undergraduate'
WHEN A.INSTITUTION = 'UMS06' AND
A.ACAD_PROG_PRIMARY IN ('NDPR', 'NDPN') THEN
'Law'
WHEN A.ACAD_PROG_PRIMARY IN ('NDEC',
'NECN',
'NDUG',
'DIST',
'P-NON',
'NONCR',
'NDPR',
'NSEU',
'NDUMA') THEN
'Undergraduate'
WHEN A.ACAD_PROG_PRIMARY IN ('NDGR', 'GRNON') THEN
'Graduate'
WHEN A.ACAD_PROG_PRIMARY LIKE '%UN' THEN
'Undergraduate'
WHEN A.ACAD_PROG_PRIMARY LIKE '%GN' THEN
'Graduate'
WHEN A.LEVEL_LOAD_RULE IN ('UG2YR', 'UGAS', 'UGA', 'AA') THEN
'Undergraduate'
WHEN A.LEVEL_LOAD_RULE IN ('UG4YR', 'UG') THEN
'Undergraduate'
WHEN A.Acad_Level_Bot = 'PHD' THEN
'Graduate'
WHEN A.LEVEL_LOAD_RULE IN ('GR', 'GRAD') THEN
'Graduate'
WHEN A.Level_Load_Rule = 'LAW' THEN
'Law'
WHEN A.ACAD_CAREER = 'NCRD' AND A.INSTITUTION = 'UMS07' THEN
'Undergraduate'
WHEN A.Acad_Career = 'UGRD' THEN
'Undergraduate'
WHEN A.Acad_Career = 'GRAD' THEN
'Graduate'
WHEN A.Acad_Career = 'LAW' THEN
'Law'
WHEN A.Acad_Career = 'PBAC' THEN
'Undergraduate'
ELSE
'Other'
END UM_STUD_LEVEL_SUM,
CASE
WHEN A.INSTITUTION = 'UMS02' AND A.DEGREE = 'CGS' THEN
'11'
WHEN A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CERT' THEN
'10'
WHEN A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CGS' THEN
'11'
WHEN A.INSTITUTION = 'UMS05' AND A.ACAD_PLAN = 'IEI' THEN
'10'
WHEN A.INSTITUTION = 'UMS05' AND A.ACAD_PLAN LIKE 'VIS%' AND
A.ACAD_CAREER = 'GRAD' THEN
'11'
WHEN A.INSTITUTION = 'UMS05' AND A.ACAD_PLAN LIKE 'VIS%' AND
A.ACAD_CAREER = 'UGRD' THEN
'10'
WHEN A.INSTITUTION = 'UMS06' AND
A.ACAD_PROG_PRIMARY IN ('NDPR', 'NDPN') THEN
'12'
WHEN A.ACAD_PROG_PRIMARY IN ('NDEC',
'NECN',
'NDUG',
'DIST',
'P-NON',
'NONCR',
'NDPR',
'NSEU',
'NDUMA') THEN
'10'
WHEN A.ACAD_PROG_PRIMARY IN ('NDGR', 'GRNON') THEN
'11'
WHEN A.ACAD_PROG_PRIMARY LIKE '%UN' THEN
'10'
WHEN A.ACAD_PROG_PRIMARY LIKE '%GN' THEN
'11'
WHEN A.LEVEL_LOAD_RULE IN ('UG2YR', 'UGAS', 'UGA', 'AA') THEN
'10'
WHEN A.LEVEL_LOAD_RULE IN ('UG4YR', 'UG') THEN
'10'
WHEN A.Acad_Level_Bot = 'PHD' THEN
'11'
WHEN A.LEVEL_LOAD_RULE IN ('GR', 'GRAD') THEN
'11'
WHEN A.Level_Load_Rule = '12' THEN
'12'
WHEN A.ACAD_CAREER = 'NCRD' AND A.INSTITUTION = 'UMS07' THEN
'10'
WHEN A.Acad_Career = 'UGRD' THEN
'10'
WHEN A.Acad_Career = 'GRAD' THEN
'11'
WHEN A.Acad_Career = '12' THEN
'12'
WHEN A.Acad_Career = 'PBAC' THEN
'10'
ELSE
'20'
END STUDENT_LEVEL_SUM_SORT,
CASE
WHEN A.INSTITUTION = 'UMS02' AND A.DEGREE = 'CGS' THEN
'Graduate'
WHEN A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CERT' THEN
'Undergraduate'
WHEN A.INSTITUTION = 'UMS06' AND A.ACAD_PLAN LIKE '%-CGS' THEN
'Graduate'
WHEN A.INSTITUTION = 'UMS05' AND A.ACAD_PLAN = 'IEI' THEN
'Undergraduate'
WHEN A.INSTITUTION = 'UMS05' AND A.ACAD_PLAN LIKE 'VIS%' AND
A.ACAD_CAREER = 'GRAD' THEN
'Graduate'
WHEN A.INSTITUTION = 'UMS05' AND A.ACAD_PLAN LIKE 'VIS%' AND
A.ACAD_CAREER = 'UGRD' THEN
'Undergraduate'
WHEN A.INSTITUTION = 'UMS06' AND
A.ACAD_PROG_PRIMARY IN ('NDPR', 'NDPN') THEN
'Graduate'
WHEN A.ACAD_PROG_PRIMARY IN ('NDEC',
'NECN',
'NDUG',
'DIST',
'P-NON',
'NONCR',
'NDPR',
'NSEU',
'NDUMA') THEN
'Undergraduate'
WHEN A.ACAD_PROG_PRIMARY IN ('NDGR', 'GRNON') THEN
'Graduate'
WHEN A.ACAD_PROG_PRIMARY LIKE '%UN' THEN
'Undergraduate'
WHEN A.ACAD_PROG_PRIMARY LIKE '%GN' THEN
'Graduate'
WHEN A.LEVEL_LOAD_RULE IN ('UG2YR', 'UGAS', 'UGA', 'AA') THEN
'Undergraduate'
WHEN A.LEVEL_LOAD_RULE IN ('UG4YR', 'UG') THEN
'Undergraduate'
WHEN A.Acad_Level_Bot = 'PHD' THEN
'Graduate'
WHEN A.LEVEL_LOAD_RULE IN ('GR', 'GRAD') THEN
'Graduate'
WHEN A.Level_Load_Rule = 'Graduate' THEN
'Graduate'
WHEN A.ACAD_CAREER = 'NCRD' AND A.INSTITUTION = 'UMS07' THEN
'Undergraduate'
WHEN A.Acad_Career = 'UGRD' THEN
'Undergraduate'
WHEN A.Acad_Career = 'GRAD' THEN
'Graduate'
WHEN A.Acad_Career = 'Graduate' THEN
'Graduate'
WHEN A.Acad_Career = 'PBAC' THEN
'Undergraduate'
ELSE
'Other'
END STUDENT_LEVEL_SUM_UG_GRAD,
CASE
WHEN A.Residency = 'IS' THEN
'USA'
WHEN A.Citizenship_Status = '4' AND A.Um_Country_Oth > ' ' THEN
A.Um_Country_Oth
WHEN A.Citizenship_Status = '4' AND
(A.Country <> 'USA' AND A.Country > ' ') THEN
A.Country
WHEN A.Citizenship_Status = '4' AND A.Residency = 'CAN' THEN
'CAN'
WHEN A.Citizenship_Status = '4' THEN
'OTH'
ELSE
'USA'
END TUITION_COUNTRY,
CASE
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%dros%' THEN
'Androscoggin'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%aroos%' THEN
'Aroostook'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%cumber%' THEN
'Cumberland'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%frank%' THEN
'Franklin'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%hanc%' THEN
'Hancock'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%bec' THEN
'Kennebec'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%knox' THEN
'Knox'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%lincoln' THEN
'Lincoln'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%oxford' THEN
'Oxford'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%penob%' THEN
'Penobscot'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%piscat%' THEN
'Piscataquis'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%saga%' THEN
'Sagadahoc'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%somer%' THEN
'Somerset'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%waldo' THEN
'Waldo'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%wash%' THEN
'Washington'
WHEN A.Residency = 'IS' AND LOWER(A.County) LIKE '%york' THEN
'York'
WHEN A.Residency = 'IS' AND A.Institution = 'UMS01' THEN
'Kennebec'
WHEN A.Residency = 'IS' AND A.Institution = 'UMS02' THEN
'Franklin'
WHEN A.Residency = 'IS' AND A.Institution = 'UMS03' THEN
'Aroostook'
WHEN A.Residency = 'IS' AND A.Institution = 'UMS04' THEN
'Washington'
WHEN A.Residency = 'IS' AND A.Institution = 'UMS05' THEN
'Penobscot'
WHEN A.Residency = 'IS' AND A.Institution = 'UMS06' THEN
'Cumberland'
WHEN A.Residency = 'IS' AND A.Institution = 'UMS07' THEN
'Aroostook'
ELSE
'Non-Maine'
END TUITION_COUNTY,
CASE
WHEN A.Residency = 'IS' THEN
'Maine'
WHEN A.State = 'AB' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Alberta'
WHEN A.State = 'BC' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'British Columbia'
WHEN A.State = 'MB' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Manitoba'
WHEN A.State = 'NB' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'New Brunswick'
WHEN A.State = 'NF' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Newfoundland'
WHEN A.State = 'NL' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Newfoundland'
WHEN A.State = 'NS' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Nova Scotia'
WHEN A.State = 'NT' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Northwest Territories'
WHEN A.State = 'NN' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Nunavut'
WHEN A.State = 'NU' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Nunavut'
WHEN A.State = 'ON' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Ontario'
WHEN A.State = 'PE' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Prince Edward Island'
WHEN A.State = 'QC' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Quebec'
WHEN A.State = 'SK' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Saskatchewan'
WHEN A.State = 'YT' AND A.Citizenship_Status = '4' AND
A.Residency = 'CAN' THEN
'Yukon'
WHEN A.Citizenship_Status = '4' AND A.Residency = 'CAN' THEN
'Canada'
WHEN A.Citizenship_Status = '4' THEN
'Non-USA'
WHEN A.State <> 'ME' AND A.State_Descr1 > ' ' THEN
A.State_Descr1
WHEN A.STATE = 'NH' THEN
'New Hampshire'
WHEN A.State = 'VT' THEN
'Vermont'
WHEN A.State = 'MA' THEN
'Massachusetts'
WHEN A.State = 'CT' THEN
'Connecticut'
WHEN A.State = 'RI' THEN
'Rhode Island'
ELSE
'Other STATE'
END TUITION_STATE,
DECODE(A.Institution,
'UMS01',
'006760',
'UMS02',
'002040',
'UMS03',
'002041',
'UMS04',
'002055',
'UMS05',
'002053',
'UMS06',
'002054',
'UMS07',
'002033',
' ') SCHOOL_CODE,
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,
qb.residency AS um_residency_reg,
qb.tuition_res,
qb.fin_aid_fed_res,
qb.fin_aid_st_res,
qb.fin_aid_fed_excpt,
qb.fin_aid_st_excpt,
qb.tuition_excpt,
(SELECT PREF.LAST_NAME || ',' || PREF.FIRST_NAME || ' ' ||
PREF.MIDDLE_NAME
FROM SYSADM.PS_NAMES PREF
WHERE PREF.EMPLID = A.EMPLID
AND DECODE(PREF.NAME_TYPE, 'PRF', 9, 'PRI', 8, 7) =
(SELECT MAX(DECODE(AM.NAME_TYPE, 'PRF', 9, 'PRI', 8, 7))
FROM SYSADM.PS_NAMES AM
WHERE am.emplid = PREF.emplid)
AND PREF.EFFDT = (SELECT MAX(A1.EFFDT)
FROM SYSADM.PS_NAMES A1
WHERE A1.EMPLID = PREF.EMPLID
AND A1.NAME_TYPE = PREF.NAME_TYPE
AND A1.EFFDT <= SYSDATE)) AS PREFERRED_NAME
FROM SYSADM.PS_UM_STUD_EXTRACT A
LEFT OUTER JOIN SYSADM.ps_residency_off QB
ON a.emplid = QB.emplid
AND a.acad_career = QB.acad_career
AND a.institution = QB.institution
WHERE ((QB.EFFECTIVE_TERM =
(SELECT MAX(EB9.EFFECTIVE_TERM)
FROM SYSADM.PS_RESIDENCY_OFF EB9
WHERE EB9.EMPLID = QB.EMPLID
AND EB9.ACAD_CAREER = QB.ACAD_CAREER
AND EB9.INSTITUTION = QB.INSTITUTION
AND EB9.EFFECTIVE_TERM <= A.STRM)) OR
(QB.EFFECTIVE_TERM =
(SELECT MAX(EC9.EFFECTIVE_TERM)
FROM SYSADM.PS_RESIDENCY_OFF EC9
WHERE EC9.EMPLID = QB.EMPLID
AND EC9.ACAD_CAREER = QB.ACAD_CAREER
AND EC9.INSTITUTION = QB.INSTITUTION) AND NOT EXISTS
(SELECT 'X'
FROM SYSADM.PS_RESIDENCY_OFF ED9
WHERE ED9.EMPLID = QB.EMPLID
AND ED9.ACAD_CAREER = QB.ACAD_CAREER
AND ED9.INSTITUTION = QB.INSTITUTION
AND ED9.EFFECTIVE_TERM <= A.STRM)))
OR (QB.EFFECTIVE_TERM IS NULL);
Attachments:
UM_STUD_EXT_VW.xlsx (application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
UM_STUD_EXT_VW.xlsx (application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)