This is a view of the UM_STUD_CENSUS which is updated only on the census dates, February 15th, August 15th and October 15th, from the UM_STUD_EXT_ARC view. Since the UM_STUD_EXT_ARC is based off the UM_STUD_EXTRACT, the same tables are referenced.

This efficient view returns data more quickly than the UM_STUD_CENSUS.

*When using this table, please note that ROW_CHECK is always Y, and is returning only actively enrolled students. Students with ACAD_CAREER = 'NCRD' are excluded.

KeyRecord.FieldnameFormatXLATHeading TextNotes

ACADEMIC_LOAD - Academic LoadChar1NAcad LoadUM_STUD_CENSUS

ACADEMIC_LOAD_DT - Academic Load Change DateDate
Load DateUM_STUD_CENSUS
ACAD_CAREER - Academic CareerChar4NCareerUM_STUD_CENSUS

ACAD_CAREER_FIRST - First Time in CareerChar1
First Time CarUM_STUD_CENSUS

ACAD_GROUP - Academic GroupChar5
Acad GroupUM_STUD_CENSUS

ACAD_GROUP_ADVIS - Academic Group of AdvisorChar5
GroupUM_STUD_CENSUS

ACAD_LEVEL_BOT - Academic Level - Term StartChar3
Strt LevelUM_STUD_CENSUS

ACAD_LEVEL_EOT - Academic Level - Term EndChar3
End LevelUM_STUD_CENSUS: Do not use

ACAD_LEVEL_PROJ - Academic Level - ProjectedChar3
Proj LevelUM_STUD_CENSUS

ACAD_LOAD_APPR - Approved Academic LoadChar1NAcad LoadUM_STUD_CENSUS
ACAD_PLAN - Academic PlanChar10
Acad PlanUM_STUD_CENSUS

ACAD_PLAN_TYPE - Academic Plan TypeChar3NPlan TypeUM_STUD_CENSUS
ACAD_PROG - Academic ProgramChar5
Acad ProgUM_STUD_CENSUS

ACAD_PROG_DESCR - Academic ProgramChar30
ProgramUM_STUD_CENSUS

ACAD_PROG_DUAL - Dual Academic ProgramChar5
Dual ProgUM_STUD_CENSUS

ACAD_PROG_PRIMARY - Primary Academic ProgramChar5
Prim ProgUM_STUD_CENSUS

ACAD_SUBPLAN_TYPE - Academic Sub-Plan TypeChar3NSub-Pl TypUM_STUD_CENSUS

ACAD_SUB_PLAN - Academic Sub-PlanChar10
Sub-PlanUM_STUD_CENSUS

ACAD_YEAR - Academic YearChar4
Acad YearUM_STUD_CENSUS

ACTION_DT - Action DateDate
Action DateUM_STUD_CENSUS

ADDRESS1 - Address Line 1Char55
Address 1UM_STUD_CENSUS

ADDRESS2 - Address Line 2Char55
Address 2UM_STUD_CENSUS

ADDRESS3 - Address Line 3Char55
Address 3UM_STUD_CENSUS

ADDRESS4 - Address Line 4Char55
Address 4UM_STUD_CENSUS

ADMISSION_EXCPT - Admission Residency ExceptionChar5
Adm ExcptUM_STUD_CENSUS

ADMISSION_RES - Admissions ResidencyChar5
AdmissionsUM_STUD_CENSUS
ADMIT_TERM - Admit TermChar4
Admit TermUM_STUD_CENSUS

ADMIT_TYPE - Admit TypeChar3
Admit TypeUM_STUD_CENSUS

ADM_APPL_CTR - Application CenterChar4
Appl CtrUM_STUD_CENSUS
ADM_APPL_NBR - Application NbrChar8
Appl NbrUM_STUD_CENSUS

ADVISOR_ID - Academic AdvisorChar11
AdvisorUM_STUD_CENSUS

ADVIS_STATUS - Advisement StatusChar4NStatusUM_STUD_CENSUS

BILLING_CAREER - Billing CareerChar4
Bill CarUM_STUD_CENSUS

BIRTHDATE - Date of BirthDate
BirthdateUM_STUD_CENSUS

BIRTHPLACE - Birth LocationChar30
BirthplaceUM_STUD_CENSUS

BIRTHSTATE - Birth StateChar6
StateUM_STUD_CENSUS

BIRTH_COUNTRY - BIRTH_COUNTRYChar4
BIRTH_COUNTRYUM_STUD_CENSUS

CAMPUS - CampusChar5
CampusUM_STUD_CENSUS

CENSUS_DT - Census DateDate
Census DtUM_STUD_CENSUS

CITIZENSHIP_STATUS - Citizenship StatusChar1
StatusUM_STUD_CENSUS

CITY - CityChar30
CityUM_STUD_CENSUS

CLASS_RANK_NBR - Class Rank NbrNum5.0
Class RankUM_STUD_CENSUS

CLASS_RANK_TOT - Class Rank TotalNum5.0
OfUM_STUD_CENSUS

COMPLETION_TERM - Completion TermChar4
Compl TermUM_STUD_CENSUS

COUNTRY - CountryChar3
CountryUM_STUD_CENSUS

COUNTRY_DESCR1 - Country DescriptionChar30
CountryUM_STUD_CENSUS

COUNTY - CountyChar30
CountyUM_STUD_CENSUS

CUM_GPA - Cumulative GPANum6.3
GPAUM_STUD_CENSUS

CUM_RESIDENT_TERMS - Cumulative In Residence TermsNum4.3
Cum ResUM_STUD_CENSUS

CUR_GPA - Current GPANum6.3
GPAUM_STUD_CENSUS

CUR_RESIDENT_TERMS - Current In Residence TermsNum4.3
Cur ResUM_STUD_CENSUS

DECLARE_DT - Declare DateDate
Decl DateUM_STUD_CENSUS

DEGREE - DegreeChar8
DegreeUM_STUD_CENSUS

DEGR_CHKOUT_STAT - Degree Checkout StatusChar2NChkoutStatUM_STUD_CENSUS

DT_OF_DEATH - Date of DeathDate
Death DateUM_STUD_CENSUS

EDUCATION_LVL - Education LevelChar2NEducationUM_STUD_CENSUS

EFFECTIVE_TERM - Effective TermChar4
EffectiveUM_STUD_CENSUS

ELIG_TO_ENROLL - Eligible to EnrollChar1
Elig EnrlUM_STUD_CENSUS

EMAIL_ADDR - Email AddressChar70
EmailUM_STUD_CENSUS
EMPLID - Empl IDChar11
IDUM_STUD_CENSUS

END_DATE - End DateDate
End DateUM_STUD_CENSUS

ENRL_ON_TRANS_DT - Show Enrollment on TranscriptDate
Enrl TransUM_STUD_CENSUS

ETHNIC_CATEGORY - Ethnic CategoryChar1NEthnic Cat

(this field has no data)

UM_STUD_CENSUS


ETHNIC_GROUP - Ethnic GroupChar1NEthnic GrpUM_STUD_CENSUS

ETHNIC_GRP_CD - Ethnic GroupChar8
Ethnic GrpUM_STUD_CENSUS

EXP_GRAD_TERM - Expected Graduation TermChar4
Exp GradUM_STUD_CENSUS

EXT_ORG_ID - External Org IDChar11
Org IDUM_STUD_CENSUS

FA_ELIGIBILITY - Program Eligibility FlagChar1
EligibleUM_STUD_CENSUS

FA_LOAD - Financial Aid LoadChar1NFA LoadUM_STUD_CENSUS

FA_STATS_CALC_DTTM - FA Stats Calc Date TimeDateTm
FA DTTMUM_STUD_CENSUS

FA_STATS_CALC_REQ - FA Stats Calculation RequiredChar1
FA CalcUM_STUD_CENSUS

FIN_AID_FED_EXCPT - Fin Aid Fed Residency ExcptChar5
FA Fed Exc
SQL for some FIN_AID_ fields
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_CENSUS 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 ResidencyChar5
FinAid Fed
SQL for some FIN_AID_ fields
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_CENSUS 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 ExcptChar5
FA St Excp
SQL for some FIN_AID_ fields
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_CENSUS 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 ResidencyChar5
FA St Excp
SQL for some FIN_AID_ fields
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_CENSUS 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)

FIRST_NAME - First NameChar30
First NameUM_STUD_CENSUS

FORM_OF_STUDY - Form of StudyChar4NStudy FormUM_STUD_CENSUS

FTE - FTENum2.6
FTE
Calculation for FTE (Alias A= UM_STUD_CENSUS)
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 DateDate
Full EnrlUM_STUD_CENSUS

FULLY_GRADED_DT - Fully Graded DateDate
Full GrdedUM_STUD_CENSUS

GRADE_POINTS - Grade PointsNum7.3
Grd PointsUM_STUD_CENSUS

GRADE_POINTS_FA - Financial Aid Grade PointsNum7.3
FA Grd PtUM_STUD_CENSUS

GRADUATION_DT - Graduation DateDate
Grad Dt

High school graduation date

UM_STUD_CENSUS


HEADCOUNT - HeadcountNum6.0
Headcount1 (put '1' on each data)

HISP_LATINO - Person is Hispanic or LatinoChar1
Hisp or LatinUM_STUD_CENSUS
INSTITUTION - Academic InstitutionChar5
InstitutionUM_STUD_CENSUS

INSTITUTION_DESCR - InstitutionChar30
InstitutionUM_STUD_CENSUS

JOINT_PROG_APPR - Joint Program ApprovedChar1
Joint ProgramUM_STUD_CENSUS

LAST_DATE_ATTENDED - Last Date of AttendanceDate
Last DateUM_STUD_CENSUS

LAST_NAME - Last NameChar30
LastUM_STUD_CENSUS

LEVEL_LOAD_RULE - Academic Level RuleChar5
Level RuleUM_STUD_CENSUS

LOCK_IN_AMT - Lock In AmountSNm15.2
AmountUM_STUD_CENSUS

LOCK_IN_DT - Lock In DateDate
DateUM_STUD_CENSUS

MAR_STATUS - Marital StatusChar1NMar StatusUM_STUD_CENSUS

MAR_STATUS_DT - Marital Status DateDate
Mar StatDtUM_STUD_CENSUS

MAX_AUDIT_UNIT - Max Audit UnitsNum4.2
Max AuditUM_STUD_CENSUS

MAX_CRSE_COUNT - Max Total CoursesNum3.0
Total CrseUM_STUD_CENSUS

MAX_NOGPA_UNIT - Max No GPA UnitsNum4.2
Max Grd PtUM_STUD_CENSUS

MAX_TOTAL_UNIT - Max Total UnitsNum4.2
Max UnitsUM_STUD_CENSUS

MAX_WAIT_UNIT - Max Wait List UnitsNum4.2
Max WaitUM_STUD_CENSUS

MIDDLE_NAME - Middle NameChar30
MiddleUM_STUD_CENSUS

MIN_TOTAL_UNIT - Min Total UnitsNum4.2
Min UnitsUM_STUD_CENSUS

NAME - NameChar50
NameUM_STUD_CENSUS

NAME_PREFIX - Name PrefixChar4
PrefixUM_STUD_CENSUS

NAME_SUFFIX - Name SuffixChar15
SuffixUM_STUD_CENSUS

NSLDS_LOAN_YEAR - NSLDS Loan YearChar1NLoan YearUM_STUD_CENSUS

OVRD_ACAD_LVL_ALL - Override All Academic LevelsChar1
Ovrd LvlsUM_STUD_CENSUS

OVRD_ACAD_LVL_PROJ - Override Projected LevelChar1
Ovrd LevelUM_STUD_CENSUS

OVRD_BILL_UNITS - Override Billing UnitsChar1
Ovrd BillUM_STUD_CENSUS

OVRD_INIT_ADD_FEE - Override Initial Add FeesChar1
Override FeeUM_STUD_CENSUS

OVRD_INIT_ENR_FEE - Override Initial Enroll FeeChar1
Override FeeUM_STUD_CENSUS

OVRD_MAX_UNITS - Override Maximum UnitsChar1
Ovrd UnitsUM_STUD_CENSUS

OVRD_TUIT_GROUP - Override Tuition GroupChar10
Ov Tuit GpUM_STUD_CENSUS

OVRD_WDRW_SCHED - Override Withdrawal ScheduleChar8
Ov Wd SchdUM_STUD_CENSUS

PHONE - TelephoneChar24
PhoneUM_STUD_CENSUS

PIN_NUM - PIN NumberNum8.0
PIN NumberUM_STUD_CENSUS

PLAN_SEQUENCE - Plan SequenceNum2.0
Plan SeqUM_STUD_CENSUS

POSTAL - Postal CodeChar12
PostalUM_STUD_CENSUS

PROG_ACTION - Program ActionChar4NProg ActnUM_STUD_CENSUS

PROG_REASON - Action ReasonChar4
Action RsnUM_STUD_CENSUS

PROG_STATUS - Academic Program StatusChar4NStatusUM_STUD_CENSUS

PROJ_BILL_UNT - Projected Bill UnitsNum3.0
Bill UnitsUM_STUD_CENSUS

PRO_RATA_ELIGIBLE - Pro-Rata EligibleChar1
EligibleUM_STUD_CENSUS

REFUND_PCT - Refund PercentageNum4.2
Refund %UM_STUD_CENSUS

REFUND_SCHEME - Refund SchemeChar8
SchemeUM_STUD_CENSUS

REGISTERED - RegisteredChar1NRegisteredUM_STUD_CENSUS

REG_CARD_DATE - Registration Card DateDate
Reg CardUM_STUD_CENSUS

REQ_TERM - Requirement TermChar4
Req TermUM_STUD_CENSUS

RESET_CUM_STATS - Reset Cum Stats at Term StartChar1
Cum ResetUM_STUD_CENSUS

RESIDENCY - Tuition ResidencyChar5
Tuition ResidUM_STUD_CENSUS

RESIDENCY_DT - Residency DateDate
Res DateUM_STUD_CENSUS

RESPONSE_DT - Response DateDate
DateUM_STUD_CENSUS

RESPONSE_REASON - Response ReasonChar4
ReasonUM_STUD_CENSUS

ROW_CHECK - ROW CHECKChar1
ROW CHECKUM_STUD_CENSUS

SCHOOL_CODE - School CodeChar10
School Cd
Calculation for SCHOOL_CODE (Alias A= UM_STUD_CENSUS)
DECODE(A.Institution,
              'UMS01',
              '006760',
              'UMS02',
              '002040',
              'UMS03',
              '002041',
              'UMS04',
              '002055',
              'UMS05',
              '002053',
              'UMS06',
              '002054',
              'UMS07',
              '002033',
              ' ')

SEL_GROUP - Tuition GroupChar10
GroupUM_STUD_CENSUS

SEX - GenderChar1NSexUM_STUD_CENSUS

SSR_ACTIVATION_DT - Term Activation DateDate
Activation DateUM_STUD_CENSUS

START_DATE - Start Date for Gen Standing PODate
Start DateUM_STUD_CENSUS

STATE - StateChar6
StateUM_STUD_CENSUS

STATE_DESCR1 - State DescriptionChar30
StateUM_STUD_CENSUS

STATS_ON_TRANS_DT - Show Statistics on TranscriptDate
Stat TransUM_STUD_CENSUS
STDNT_CAR_NBR - Student Career NbrNum3.0
Career NbrUM_STUD_CENSUS

STDNT_DEGR - Student Degree NbrChar2
Degree NbrUM_STUD_CENSUS
STRM - TermChar4
TermUM_STUD_CENSUS

STUDY_AGREEMENT - Study AgreementChar10
AgreementUM_STUD_CENSUS

TC_UNITS_ADJUST - TC Units AdjustmentNum6.3
TC Units AdjustUM_STUD_CENSUS

TERM_TYPE - Term Unit TypeChar1NTerm TypeUM_STUD_CENSUS

TOT_AUDIT - Total AuditedNum6.3
AuditUM_STUD_CENSUS

TOT_CUMULATIVE - Total Cumulative UnitsNum6.3
TotalUM_STUD_CENSUS

TOT_GRADE_POINTS - Total Grade PointsNum7.3
Grade PointsUM_STUD_CENSUS

TOT_GRD_POINTS_FA - Total Fin Aid Grade PointsNum7.3
Tot FA GrdUM_STUD_CENSUS

TOT_INPROG_GPA - Total In Progress - GPANum6.3
In Pr GPAUM_STUD_CENSUS

TOT_INPROG_NOGPA - Total In Progress -Not for GPANum6.3
InPr NoGPAUM_STUD_CENSUS

TOT_OTHER - Total from Other CreditNum6.3
OtherUM_STUD_CENSUS

TOT_PASSD_FA - Total Fin Aid Units PassedNum6.3
Pass FAUM_STUD_CENSUS

TOT_PASSD_GPA - Total Passed Toward GPANum6.3
Pass GPAUM_STUD_CENSUS

TOT_PASSD_NOGPA - Total Passed Not Toward GPANum6.3
PassNoGPAUM_STUD_CENSUS

TOT_PASSD_PRGRSS - Total Passed for ProgressNum6.3
Pass PrgrsUM_STUD_CENSUS

TOT_TAKEN_FA - Total Fin Aid Units TakenNum6.3
Take FAUM_STUD_CENSUS

TOT_TAKEN_FA_GPA - Total Fin Aid Taken Toward GPANum6.3
Tot FA GPAUM_STUD_CENSUS

TOT_TAKEN_GPA - Total Taken Toward GPANum6.3
Take GPAUM_STUD_CENSUS

TOT_TAKEN_NOGPA - Total Taken Not Toward GPANum6.3
TakeNoGPAUM_STUD_CENSUS

TOT_TAKEN_PRGRSS - Total Taken for ProgressNum6.3
Take PrgrsUM_STUD_CENSUS

TOT_TEST_CREDIT - Total From Test CreditNum6.3
TestUM_STUD_CENSUS

TOT_TRNSFR - Total TransferredNum6.3
TransferUM_STUD_CENSUS

TRF_GRADE_POINTS - Transfer Grade PointsNum7.3
Trnsfr Grd PtsUM_STUD_CENSUS

TRF_PASSED_GPA - Transfer Passed for GPANum6.3
TrnPasGPAUM_STUD_CENSUS

TRF_PASSED_NOGPA - Transfer Passed Not for GPANum6.3
TrnPasNGPAUM_STUD_CENSUS

TRF_RESIDENT_TERMS - Transfer In Residence TermsNum4.3
Trf ResUM_STUD_CENSUS

TRF_TAKEN_GPA - Transfer Taken for GPANum6.3
TrnTakGPAUM_STUD_CENSUS

TRF_TAKEN_NOGPA - Transfer Taken Not for GPANum6.3
TrnTakNGPAUM_STUD_CENSUS

TUITION_EXCPT - Tuition Residency ExceptionChar5
Tuit Excpt
SQL for some FIN_AID_ fields
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_CENSUS 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 ResidencyChar5
Tuition
SQL for some FIN_AID_ fields
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_CENSUS 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)

TUITION_RES_TERMS - Tuition ResidencyNum4.3
Tuition Res.UM_STUD_CENSUS

TUIT_CALC_DTTM - Tuit Calc Date TimeDateTm
TuitDTTMUM_STUD_CENSUS

TUIT_CALC_REQ - Tuition Calc RequiredChar1
Calc ReqUM_STUD_CENSUS

UM_ACADEMIC_YR - Academic YearChar12
Academic Year
Calculation for UM_ACADEMIC_YR (Alias A = UM_STUD_CENSUS)
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 SortChar2
Academic Yr Srt
Calculation for UM_ACADEMIC_YR_SRT (Alias A = UM_STUD_CENSUS)
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 DescrChar30
Academic GroupUM_STUD_CENSUS

UM_ACAD_LOAD_RC - Calculated Academic LoadChar1NCalc Acad LoadUM_STUD_CENSUS

UM_ACAD_LVL_DESCR - Academic Level DescriptionChar30
Acad Level DescUM_STUD_CENSUS

UM_ACAD_PLAN_DESCR - Academic Plan DescriptionChar30
Acad Plan DescrUM_STUD_CENSUS

UM_ACAD_PROGP_DSC - Acad Prog Primary DescrChar30
Acd Prg Prm DscUM_STUD_CENSUS

UM_ACAD_SUBPLN_DSC - Academic Sub Plan DescriptionChar30
Acad Sub DescrUM_STUD_CENSUS

UM_ADMIT_TERM_YEAR - Admit Term YearChar4
Admit Term Year
Calculation for UM_ADMIT_TERM_YEAR (Alias A = UM_STUD_CENSUS)
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 DescriptionChar50
Admit Type Desc

Please go to Degree/Non-Degree Coding - Current page for the code


UM_ADMIT_TYPE_D_ND - Admit Type Descr Non DegreeChar50
Admit Type D ND

Please go to Degree/Non-Degree Coding - Current page for the code


UM_ADMIT_TYPE_SORT - Admit Type SortChar2
Admit Type Sort

Please go to Degree/Non-Degree Coding - Current page for the code


UM_ADVISOR_NAME_FM - Advisor Name FormattedChar50
Advisor Name F
Calculation for UM_ADVISOR_NAME_FM (Alias A = UM_STUD_CENSUS)
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 NameChar30
Advisr Fst NameUM_STUD_CENSUS

UM_ADVISR_LST_NAME - Advisor Last NameChar30
Advisr Lst NameUM_STUD_CENSUS

UM_ADVISR_MID_NAME - Advisor Middle NameChar30
Advisr Mid NameUM_STUD_CENSUS

UM_ADVISR_NAME_SFX - Advisor Name SuffixChar15
Advisr Name SfxUM_STUD_CENSUS

UM_ANY_COUNTRY - Any CountryChar50
Any Country
Calculation for UM_ANY_COUNTRY (Alias A = UM_STUD_CENSUS)
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 CountryChar50
Calc Country
Calculation for UM_CALC_COUNTRY (Alias A = UM_STUD_CENSUS)
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 CountyChar50
Calc County
Calculation for UM_CALC_COUNTY (Alias A = UM_STUD_CENSUS)
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 StateChar50
Calculate State
Calculation for UM_CALC_STATE (Alias A = UM_STUD_CENSUS)
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 CanadaChar50
UM Canada
Calculation for UM_CANADA (Alias A = UM_STUD_CENSUS)
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_CENSUS_AGE - Census AgeNum4.1
Census Age

The BIRTHDATE field is defaulted to 1901/01/01 or NULL which results in some students displaying as over 100 years old.

Calculation for UM_CENSUS_AGE (Alias A = UM_STUD_CENSUS)
TRUNC((A.CENSUS_DT - A.birthdate) / 365.25)

UM_CENSUS_AGE_PREC - Census Age PreciseNum4.5
Census Age Prec
Calculation for UM_CENSUS_AGE_PREC (Alias A = UM_STUD_CENSUS)
CASE
         WHEN A.birthdate IS NULL THEN
          NULL
         ELSE
          MONTHS_BETWEEN(A.CENSUS_DT, A.birthdate) / 12
       END

UM_CENSUS_AGE_RNG - Census Age RangeChar15
Census Age Rng
Calculation for UM_CENSUS_AGE_RNG (Alias A = UM_STUD_CENSUS)
CASE
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 14 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 17 THEN
          'Under 18'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 18 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 19 THEN
          '18 - 19'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 20 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 21 THEN
          '20 - 21'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 22 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 24 THEN
          '22 - 24'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 25 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 29 THEN
          '25 - 29'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 30 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 34 THEN
          '30 - 34'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 35 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 39 THEN
          '35 - 39'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 40 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 49 THEN
          '40 - 49'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 50 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 64 THEN
          '50 - 64'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 65 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 105 THEN
          '65 and Over'
         ELSE
          'Unknown'
       END

UM_CENSUS_AGE_RSRT - Census Age Range SortChar2
Census Age RSRT
Calculation for UM_CENSUS_AGE_RSRT (Alias A = UM_STUD_CENSUS)
CASE
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 14 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 17 THEN
          '01'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 18 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 19 THEN
          '02'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 20 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 21 THEN
          '03'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 22 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 24 THEN
          '04'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 25 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 29 THEN
          '05'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 30 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 34 THEN
          '06'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 35 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 39 THEN
          '07'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 40 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 49 THEN
          '08'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 50 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 64 THEN
          '09'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 65 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 105 THEN
          '11'
         ELSE
          '12'
       END

UM_CITIZEN_STAT_OT - Citizenship Status OtherChar1
Citizn Stat Oth

UM_STUD_CENSUS

This field is essentially unreliable for students with more than 1 foreign country listed in the CITIZENSHIP table.


UM_CITIZEN_ST_DESC - Citizenship Status DescriptionChar20
Citizen St Desc
Calculation for UM_CITIZEN_ST_DESC (Alias A = UM_STUD_CENSUS)
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 OtherChar3
Country OthUM_STUD_CENSUS

UM_COUNTRY_STATUS - Country StatusChar15
Country Status
Calculation for UM_COUNTRY_STATUS (Alias A = UM_STUD_CENSUS)
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 USAChar3
Country USA

UM_STUD_CENSUS

This field is essentially unreliable for students with more than 1 foreign country listed in the CITIZENSHIP table.


UM_CURRENT_AGE - Current AgeNum4.1
Current Age
Calculation for UM_CURRENT_AGE (Alias A = UM_STUD_CENSUS)
TRUNC((SYSDATE - A.BIRTHDATE) / 365.25)

UM_CURRENT_AGE_RNG - Current Age RangeChar15
Curr Age Range
Calculation for UM_CURRENT_AGE_RNG (Alias A = UM_STUD_CENSUS)
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 SortChar2
Curr Age Rsrt
Calculation for UM_CURRENT_AGE_RSRT (Alias A = UM_STUD_CENSUS)
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-DegreeChar15
Degree NonDeg

Please go to Degree/Non-Degree Coding - Current page for the code


UM_EMAIL_HOM_ADDR - Email Home AddressChar70
Email Home Addr

UM_ETHNIC_DESCR - Ethnic DescriptionChar50
Ethic DescrUM_STUD_CENSUS

UM_ETHNIC_GRP_DESC - Ethnic Groupd DescriptionChar30
Ethnic Grp Desc

UM_ETHNIC_GRP_SORT - Ethnic Group SortChar2
Ethnic Grp Srt
Calculation for UM_ETHNIC_GRP_SORT (Alias A = UM_STUD_CENSUS)
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 DescrChar30
Eth Grp DescrUM_STUD_CENSUS

UM_EXTERNAL_ORG - External OrganizationChar30
External Org

Please go to Degree/Non-Degree Coding - Current page for the code


UM_EXTERNL_ORG_DTL - External Org DetailChar30
Externl Org Dtl

Please go to Degree/Non-Degree Coding - Current page for the code


UM_EXTERNL_ORG_SUM - External Organization SummaryChar30
Externl Org Sum

Please go to Degree/Non-Degree Coding - Current page for the code


UM_EXT_ORGID_DESCR - External Org ID DescriptionChar30
Ext Org ID DescUM_STUD_CENSUS

UM_EXT_ORG_INST - External Org InstitutionChar5
Ext Org Inst
Calculation for UM_EXT_ORG_INST (Alias A = UM_STUD_CENSUS)
DECODE(A.Ext_Org_Id,
              '0136250',
              'UMS05',
              '0136246',
              'UMS01',
              '0136247',
              'UMS02',
              '0136248',
              'UMS03',
              '0136249',
              'UMS04',
              '0136252',
              'UMS07',
              '0136392',
              'UMS06',
              'OTHER')

UM_FEES_PAID - Fees PaidSNm8.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_STUD_CENSUS


UM_FIRST_GEN - First GenChar1
First Gen

Calculation for First Generation


UM_FIRST_TERM - First TermChar1
First Term
Calculation for UM_FIRST_TERM (Alias A = UM_STUD_CENSUS)
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 TimeChar1
First Time

Please go to Degree/Non-Degree Coding - Current page for the code


UM_FIRST_TIME_NDEG - First Time Non-DegreeChar1
First Time NonD

Please go to Degree/Non-Degree Coding - Current page for the code


UM_FTE_DIVISOR - FTE DivisorNum3.0
FTE Divisor
SQL for UM_FTE_DIVISOR (alias A= UM_STUD_CENSUS)
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-TimeChar10
Full Part
Calculation for UM_FULL_PART (Alias A= UM_STUD_CENS)
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 - GenderChar10
Gender
Calculation for UM_GENDER (Alias A= UM_STUD_CENSUS)
CASE
         WHEN A.Sex = 'M' THEN
          'Male'
         ELSE
          'Female'
       END

UM_GRAD_DATE_YEAR - Graduation Date YearChar4
Grad Date Year
Calculation for UM_GRAD_DATE_YEAR (Alias A= UM_STUD_CENSUS)
TO_NUMBER(TO_CHAR(A.Graduation_Dt, 'YYYY'), '9999') 

UM_INST_SHORT - Institution ShortChar5
Inst Short
Calculation for UM_INST_SHORT (Alias A = UM_STUD_CENSUS)
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_StateChar15
In-State Out
Calculation for UM_IN_OUT (Alias A = UM_STUD_CENSUS and alias QB= RESIDENCY_OFF)
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 DescriptionChar30
Lst School Desc

UM_LAST_SCH_TYPE - Last School Attended TypeChar3
Last School Typ

UM_MAINE_COUNTY - Maine CountyChar1
Maine County
Calculation for UM_MAINE_COUNTY (Alias A = UM_STUD_CENSUS)
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 CompNum5.2
Max ACT Comp

- Limited to last 5 years from census date

UM_STUD_CENSUS


UM_MAX_ACT_ENGL - Maximum ACT EnglishNum5.2
Max ACT English

- Limited to last 5 years from census date

UM_STUD_CENSUS


UM_MAX_ACT_MATH - Maximum ACT MathNum5.2
Max ACT Math

- Limited to last 5 years from census date

UM_STUD_CENSUS


UM_MAX_GRE_QUAN - Maximum Quantitative GRENum5.2
Max GRE QUANUM_STUD_CENSUS

UM_MAX_GRE_VERB - Maximum GRE VerbalNum5.2
Max GRE VERBUM_STUD_CENSUS

UM_MAX_SAT_ERWS - Max SAT ERWSNum5.2
Max SAT ERWS

- Limited to last 5 years from census date

UM_STUD_CENSUS


UM_MAX_SAT_MATH - Maximum Sat MathNum5.2
Max Sat Math

- Limited to last 5 years from census date

UM_STUD_CENSUS


UM_MAX_SAT_MSS - Max SAT MSSNum5.2
Max SAT MSS

- Limited to last 5 years from census date

UM_STUD_CENSUS


UM_MAX_SAT_VERB - Maximum Sat VerbalNum5.2
Max Sat Verbal

- Limited to last 5 years from census date

UM_STUD_CENSUS


UM_MAX_SAT_WRIT - Maximum SAT WriteNum5.2
Max SAT Write

- Limited to last 5 years from census date

UM_STUD_CENSUS


UM_MAX_TOEFL_COMPC - Maximum TOEFL COMPCNum5.2
Max TOEFL COMPCUM_STUD_CENSUS

UM_MAX_TOEFL_COMPI - Maximum TOEFL COMPINum5.2
Max TOEFL COMPIUM_STUD_CENSUS

UM_MAX_TOEFL_COMPP - Maximum TOEFL COMPPNum5.2
Max TOEFL COMPPUM_STUD_CENSUS

UM_MIN_ACAD_PLAN - Academic PlanChar10
Acad PlanUM_STUD_CENSUS

UM_MIN_ACAD_PROG - Academic ProgramChar5
Acad ProgUM_STUD_CENSUS

UM_MIN_ACAD_SUBPLN - Academic Sub-PlanChar10
Sub-PlanUM_STUD_CENSUS

UM_MULTIPLE_ETHNIC - Multiple Ethnic GroupsChar1NMutiple_EthnicUM_STUD_CENSUS

UM_NEASC_LEVEL - NEASC LevelChar50
NEASC Level

Please go to Degree/Non-Degree Coding - Current page for the code


UM_NEASC_LEVEL_SRT - NEASC Level SortChar2
NEASC Level Srt

Please go to Degree/Non-Degree Coding - Current page for the code


UM_NID_CAN - National ID CanadaChar20
NID CanadaUM_STUD_CENSUS

UM_NID_USA - National Id USAChar20
NID USAUM_STUD_CENSUS

UM_NURSING_FLAG - Nursing FlagChar1
Nursing Flag
Calculation for UM_NURSING_FLAG (Alias A = UM_STUD_CENSUS)
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_PLANChar10
ORIG_ACAD_PLANUM_STUD_CENSUS

UM_ORIG_ACAD_PROG - ORIGINAL_ACAD_PROGChar5
ORIG_ACAD_PROGUM_STUD_CENSUS

UM_PREVIOUS_NAME - Previous NameChar50
Prev NameUM_STUD_CENSUS

UM_RESIDENCY_DESCR - Residency DescriptionChar30
Residency Descr
Calculation for UM_RESIDENCY_DESCR (Alias A = UM_STUD_CENSUS)
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 SortChar2
Residency Sort
Calculation for UM_RESIDENCY_SORT (Alias A = UM_STUD_CENSUS)
DECODE(A.Residency,
              'IS', '10',
              'OS', '20',
             'CAN', '40',
           'NEBHE', '30',
           'INTNL', '50',
           'ONLIN', '60',
           'MEONL', '70'
                    '20'  )

UM_RSP_REASN_DESCR - Response Reason DescriptionsChar30
Resp Reason DesUM_STUD_CENSUS

UM_STD_LVL_SUM_SRT - Student Level Summary SortChar2
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 GradChar30
Std Lvl Sum UG

Please go to Degree/Non-Degree Coding - Current page for the code


UM_STRM_YEAR - Strm YearChar4
Strm Year
Calculation for UM_STRM_YEAR (Alias A = UM_STUD_CENSUS)
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 LevelChar50
Student Level

Please go to Degree/Non-Degree Coding - Current page for the code


UM_STUDENT_NAME - StudentChar50
Student
Calculation for UM_STUDENT_NAME (Alias A = UM_STUD_CENSUS)
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 SortChar2
Stud Level Sort

Please go to Degree/Non-Degree Coding - Current page for the code


UM_STUD_LEVEL_SUM - Student Level SummaryChar30
Stud Level Sum

Please go to Degree/Non-Degree Coding - Current page for the code


UM_TUITION_COUNTRY - Tuition CountryChar50
Tuition Country
Calculation for UM_TUITION_COUNTRY (Alias A = UM_STUD_CENSUS)
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 CountyChar50
Tuition County
Calculation for UM_TUITION_COUNTY (Alias A = UM_STUD_CENSUS)
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 PaidSNm8.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_STUD_CENSUS


UM_TUITION_STATE - Tuition StateChar50
Tuition State
Calculation for UM_TUITION_STATE (Alias A = UM_STUD_CENSUS)
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 FlagChar1
Ums01 FlagUM_STUD_CENSUS

UM_UMS01_UNITS - UMS01 UnitsNum6.3
UMS01 UnitsUM_STUD_CENSUS

UM_UMS02_FLG - UMS02 Registered FlagChar1
UMS02 FlagUM_STUD_CENSUS

UM_UMS02_UNITS - UMS02 UnitsNum6.3
UMS02 UnitsUM_STUD_CENSUS

UM_UMS03_FLG - Ums03 Registered FlagChar1
Ums03 FlagUM_STUD_CENSUS

UM_UMS03_UNITS - UMS03 UnitsNum6.3
Ums03 UnitsUM_STUD_CENSUS

UM_UMS04_FLG - UMS04 Registered FlagChar1
UMS04 FlagUM_STUD_CENSUS

UM_UMS04_UNITS - UMS04 UnitsNum6.3
UMS04 UnitsUM_STUD_CENSUS

UM_UMS05_FLG - Ums05 Registered FlagChar1
Ums05 FlagUM_STUD_CENSUS

UM_UMS05_UNITS - UMS05 UnitsNum6.3
UMS05 UnitsUM_STUD_CENSUS

UM_UMS06_FLG - Ums06 Registered FlagChar1
Ums06 FlagUM_STUD_CENSUS

UM_UMS06_UNITS - UMS06 UnitsNum6.3
UMS06 UnitsUM_STUD_CENSUS

UM_UMS07_FLG - Ums07 Registered FlagChar1
Ums07 FlagUM_STUD_CENSUS

UM_UMS07_UNITS - UMS07 UnitsNum6.3
UMS07 UnitsUM_STUD_CENSUS

UM_UNT_DIST - Units DistanceNum10.3
Units DistanceUM_STUD_CENSUS

UM_UNT_ITV - Itv Receive UnitsNum10.3
Itv Receive UntUM_STUD_CENSUS

UM_UNT_TAK_CAREER - Units Taken For CareerNum6.3
Unt Takn CareerUM_STUD_CENSUS

UM_UNT_TAK_PRG_RC - Units Taken Progress Row CheckNum6.3
Units Taken PrgUM_STUD_CENSUS

UM_UNT_WEB - Web UnitsNum10.3
Web UnitsUM_STUD_CENSUS

UNIT_MULTIPLIER - Unit MultiplierNum5.4
MultiplierUM_STUD_CENSUS

UNTPRG_CHG_NSLC_DT - Unit Progrss Changed Date NSCDate
Unit ChgDt NSCUM_STUD_CENSUS

UNT_AUDIT - Units AuditedNum6.3
AuditUM_STUD_CENSUS

UNT_INPROG_GPA - Units In Progress - GPANum6.3
InPr GPAUM_STUD_CENSUS

UNT_INPROG_NOGPA - Unit In Progress - Not for GPANum6.3
InPr NoGPAUM_STUD_CENSUS

UNT_OTHER - Units from Other CreditNum6.3
OtherUM_STUD_CENSUS

UNT_PASSD_FA - Fin Aid Progress Units PassedNum6.3
FA PassedUM_STUD_CENSUS

UNT_PASSD_GPA - Units Passed Toward GPANum6.3
Pass GPAUM_STUD_CENSUS

UNT_PASSD_NOGPA - Units Passed Not Toward GPANum6.3
PassNoGPAUM_STUD_CENSUS

UNT_PASSD_PRGRSS - Units Passed for ProgressNum6.3
Pass PrgrsUM_STUD_CENSUS

UNT_TAKEN_FA - Fin Aid Progress Units TakenNum6.3
FA TakenUM_STUD_CENSUS

UNT_TAKEN_FA_GPA - FA Units Taken Toward GPANum6.3
FA Units TUM_STUD_CENSUS

UNT_TAKEN_GPA - Units Taken Toward GPANum6.3
Take GPAUM_STUD_CENSUS

UNT_TAKEN_NOGPA - Units Taken Not Toward GPANum6.3
TakeNoGPAUM_STUD_CENSUS

UNT_TAKEN_PRGRSS - Units Taken for ProgressNum6.3
Take PrgrsUM_STUD_CENSUS

UNT_TERM_TOT - Total Term UnitsNum6.3
TotalUM_STUD_CENSUS

UNT_TEST_CREDIT - Units from Test CreditNum6.3
TestUM_STUD_CENSUS

UNT_TRNSFR - Units TransferredNum6.3
TransferUM_STUD_CENSUS

VALIDATED - Election ValidatedChar1
ValidatedUM_STUD_CENSUS

VALIDATED_DT - Date Data ValidatedDate
Date ValidatedUM_STUD_CENSUS

WITHDRAW_CODE - Withdrawal \ CancelChar3NWdraw CodeUM_STUD_CENSUS

WITHDRAW_DATE - Withdrawal \ Cancel DateDate
Wdraw DateUM_STUD_CENSUS

WITHDRAW_REASON - Withdrawal \ Cancel ReasonChar4NWithdrlRsnUM_STUD_CENSUS

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_CENS_VW
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,
       A.CENSUS_DT,
       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') CAMP,
       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,
       TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) CENSUS_AGE,
       CASE
         WHEN A.birthdate IS NULL THEN
          NULL
         ELSE
          MONTHS_BETWEEN(A.CENSUS_DT, A.birthdate) / 12
       END CENSUS_AGE_PRECISE,
       CASE
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 14 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 17 THEN
          'Under 18'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 18 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 19 THEN
          '18 - 19'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 20 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 21 THEN
          '20 - 21'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 22 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 24 THEN
          '22 - 24'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 25 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 29 THEN
          '25 - 29'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 30 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 34 THEN
          '30 - 34'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 35 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 39 THEN
          '35 - 39'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 40 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 49 THEN
          '40 - 49'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 50 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 64 THEN
          '50 - 64'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 65 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 105 THEN
          '65 and Over'
         ELSE
          'Unknown'
       END CENSUS_AGE_RANGE,
       CASE
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 14 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 17 THEN
          '01'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 18 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 19 THEN
          '02'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 20 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 21 THEN
          '03'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 22 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 24 THEN
          '04'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 25 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 29 THEN
          '05'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 30 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 34 THEN
          '06'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 35 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 39 THEN
          '07'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 40 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 49 THEN
          '08'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 50 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 64 THEN
          '09'
         WHEN TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) >= 65 AND
              TRUNC((A.CENSUS_DT - A.birthdate) / 365.25) <= 105 THEN
          '11'
         ELSE
          '12'
       END CENSUS_AGE_RANGE_SORT,
       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,
       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_CENSUS 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_CENS_VW.xlsx (application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)