UM_STUD_CENS_VW - Student Reporting Extract

WARNING: Field Name Definitions are Linked to the UMS Data Cookbook - this requires a University of Maine System ID and password

This is a view of the UM_STUD_CENSUS which is updated only on the student census dates, February 15th, August 15th and October 15th, from the UM_STUD_EXT_ARC view. Since the UM_STUD_EXT_ARC is a daily snapshot archive of the UM_STUD_EXTRACT, the same tables and fields are included.

Detailed Information 

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.

Please Note - Highlighting and copying everything you can see in a SQL or SQR snippet code block (manually, beginning to end - "select all" does not work here) in the "Notes" column and pasting the code into a code editing tool - Notepad++ for example - it will paste as the complete code even if it appears chopped off on the right side.

Databases

PeopleSoft Campus Solutions Reporting Database  CSRPT 

View SQL Build Code

Download SQL Build Code

Key Record.Fieldname Format XLAT Heading Text Notes
  ACADEMIC_LOAD - Academic Load Char1 N Acad Load UM_STUD_CENSUS
  ACADEMIC_LOAD_DT - Academic Load Change Date Date   Load Date UM_STUD_CENSUS
Key ACAD_CAREER - Academic Career Char4 N Career UM_STUD_CENSUS
  ACAD_CAREER_FIRST - First Time in Career Char1   First Time Car UM_STUD_CENSUS
  ACAD_GROUP - Academic Group Char5   Acad Group UM_STUD_CENSUS
  ACAD_GROUP_ADVIS - Academic Group of Advisor Char5   Group UM_STUD_CENSUS
  ACAD_LEVEL_BOT - Academic Level - Term Start Char3   Strt Level UM_STUD_CENSUS
  ACAD_LEVEL_EOT - Academic Level - Term End Char3   End Level UM_STUD_CENSUS: Do not use
  ACAD_LEVEL_PROJ - Academic Level - Projected Char3   Proj Level UM_STUD_CENSUS
  ACAD_LOAD_APPR - Approved Academic Load Char1 N Acad Load UM_STUD_CENSUS
Key ACAD_PLAN - Academic Plan Char10   Acad Plan UM_STUD_CENSUS
  ACAD_PLAN_TYPE - Academic Plan Type Char3 N Plan Type UM_STUD_CENSUS
Key ACAD_PROG - Academic Program Char5   Acad Prog UM_STUD_CENSUS
  ACAD_PROG_DESCR - Academic Program Char30   Program UM_STUD_CENSUS
  ACAD_PROG_DUAL - Dual Academic Program Char5   Dual Prog UM_STUD_CENSUS
  ACAD_PROG_PRIMARY - Primary Academic Program Char5   Prim Prog UM_STUD_CENSUS
  ACAD_SUBPLAN_TYPE - Academic Sub-Plan Type Char3 N Sub-Pl Typ UM_STUD_CENSUS
  ACAD_SUB_PLAN - Academic Sub-Plan Char10   Sub-Plan UM_STUD_CENSUS
  ACAD_YEAR - Academic Year Char4   Acad Year UM_STUD_CENSUS
  ACTION_DT - Action Date ( Data Entry Update Date) Date   Action Date UM_STUD_CENSUS
  ADDRESS1 - Address Line 1 Char55   Address 1 UM_STUD_CENSUS
  ADDRESS2 - Address Line 2 Char55   Address 2 UM_STUD_CENSUS
  ADDRESS3 - Address Line 3 Char55   Address 3 UM_STUD_CENSUS
  ADDRESS4 - Address Line 4 Char55   Address 4 UM_STUD_CENSUS
  ADMISSION_EXCPT - Admission Residency Exception Char5   Adm Excpt UM_STUD_CENSUS
  ADMISSION_RES - Admissions Residency Char5   Admissions UM_STUD_CENSUS
Key ADMIT_TERM - Admit Term Char4   Admit Term UM_STUD_CENSUS
  ADMIT_TYPE - Admit Type Char3   Admit Type UM_STUD_CENSUS
  ADM_APPL_CTR - Application Center Char4   Appl Ctr UM_STUD_CENSUS
Key ADM_APPL_NBR - Application Nbr Char8   Appl Nbr UM_STUD_CENSUS
  ADVISOR_ID - Academic Advisor Char11   Advisor UM_STUD_CENSUS
  ADVIS_STATUS - Advisement Status Char4 N Status UM_STUD_CENSUS
  BILLING_CAREER - Billing Career Char4   Bill Car UM_STUD_CENSUS
  BIRTHDATE - Date of Birth Date   Birthdate UM_STUD_CENSUS
  BIRTHPLACE - Birth Location Char30   Birthplace UM_STUD_CENSUS
  BIRTHSTATE - Birth State Char6   State UM_STUD_CENSUS
  BIRTH_COUNTRY - BIRTH_COUNTRY Char4   BIRTH_COUNTRY UM_STUD_CENSUS
  CAMPUS - Campus Char5   Campus UM_STUD_CENSUS
  CENSUS_DT - Census Date Date   Census Dt UM_STUD_CENSUS
  CITIZENSHIP_STATUS - Citizenship Status Char1   Status UM_STUD_CENSUS
  CITY - City Char30   City UM_STUD_CENSUS
  CLASS_RANK_NBR - Class Rank Nbr Num5.0   Class Rank UM_STUD_CENSUS
  CLASS_RANK_TOT - Class Rank Total Num5.0   Of UM_STUD_CENSUS
  COMPLETION_TERM - Completion Term Char4   Compl Term UM_STUD_CENSUS
  COUNTRY - Country Char3   Country UM_STUD_CENSUS
  COUNTRY_DESCR1 - Country Description Char30   Country UM_STUD_CENSUS
  COUNTY - County Char30   County UM_STUD_CENSUS
  CUM_GPA - Cumulative GPA Num6.3   GPA UM_STUD_CENSUS
  CUM_RESIDENT_TERMS - Cumulative In Residence Terms Num4.3   Cum Res UM_STUD_CENSUS
  CUR_GPA - Current GPA Num6.3   GPA UM_STUD_CENSUS
  CUR_RESIDENT_TERMS - Current In Residence Terms Num4.3   Cur Res UM_STUD_CENSUS
  DECLARE_DT - Declare Date Date   Decl Date UM_STUD_CENSUS
  DEGREE - Degree Char8   Degree UM_STUD_CENSUS
  DEGR_CHKOUT_STAT - Degree Checkout Status Char2 N ChkoutStat UM_STUD_CENSUS
  DT_OF_DEATH - Date of Death Date   Death Date UM_STUD_CENSUS
  EDUCATION_LVL - Education Level Char2 N Education UM_STUD_CENSUS
  EFFECTIVE_TERM - Effective Term Char4   Effective UM_STUD_CENSUS
  ELIG_TO_ENROLL - Eligible to Enroll Char1   Elig Enrl UM_STUD_CENSUS
  EMAIL_ADDR - Email Address Char70   Email UM_STUD_CENSUS
Key EMPLID - Empl ID Char11   ID UM_STUD_CENSUS
  END_DATE - End Date Date   End Date UM_STUD_CENSUS
  ENRL_ON_TRANS_DT - Show Enrollment on Transcript Date   Enrl Trans UM_STUD_CENSUS
ETHNIC_CATEGORY - Ethnic Category Char1 N Ethnic Cat

UM_STUD_CENSUS (this field has no data)
  ETHNIC_GROUP - Ethnic Group Char1 N Ethnic Grp UM_STUD_CENSUS
  ETHNIC_GRP_CD - Ethnic Group  Char8   Ethnic Grp UM_STUD_CENSUS
  EXP_GRAD_TERM - Expected Graduation Term Char4   Exp Grad UM_STUD_CENSUS
  EXT_ORG_ID - External Org ID Char11   Org ID UM_STUD_CENSUS
  FA_ELIGIBILITY - Program Eligibility Flag Char1   Eligible UM_STUD_CENSUS
  FA_LOAD - Financial Aid Load Char1 N FA Load UM_STUD_CENSUS
  FA_STATS_CALC_DTTM - FA Stats Calc Date Time DateTm   FA DTTM UM_STUD_CENSUS
  FA_STATS_CALC_REQ - FA Stats Calculation Required Char1   FA Calc UM_STUD_CENSUS
  FIN_AID_FED_EXCPT - Fin Aid Fed Residency Excpt Char5   FA Fed Exc 
 SQL for 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 Residency Char5   FinAid Fed 
SQL for 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 Residency Char5   FA St Excp 
 SQL for 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 Name Char30   First Name UM_STUD_CENSUS
  FORM_OF_STUDY - Form of Study Char4 N Study Form UM_STUD_CENSUS
  FTE - FTE Num2.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 Date Date   Full Enrl UM_STUD_CENSUS
  FULLY_GRADED_DT - Fully Graded Date Date   Full Grded UM_STUD_CENSUS
  GRADE_POINTS - Grade Points Num7.3   Grd Points UM_STUD_CENSUS
  GRADE_POINTS_FA - Financial Aid Grade Points Num7.3   FA Grd Pt UM_STUD_CENSUS
  GRADUATION_DT - Graduation Date Date   Grad Dt

UM_STUD_CENSUS 
High School Graduation Date
  HEADCOUNT - Headcount Num6.0   Headcount 1 (puts '1' on each data row)
  HISP_LATINO - Person is Hispanic or Latino Char1   Hisp or Latin UM_STUD_CENSUS
Key INSTITUTION - Academic Institution Char5   Institution UM_STUD_CENSUS
  INSTITUTION_DESCR - Institution Char30   Institution UM_STUD_CENSUS
  JOINT_PROG_APPR - Joint Program Approved Char1   Joint Program UM_STUD_CENSUS
  LAST_DATE_ATTENDED - Last Date of Attendance Date   Last Date UM_STUD_CENSUS
  LAST_NAME - Last Name Char30   Last UM_STUD_CENSUS
  LEVEL_LOAD_RULE - Academic Level Rule Char5   Level Rule UM_STUD_CENSUS
  LOCK_IN_AMT - Lock In Amount SNm15.2   Amount UM_STUD_CENSUS
  LOCK_IN_DT - Lock In Date Date   Date UM_STUD_CENSUS
  MAR_STATUS - Marital Status Char1 N Mar Status UM_STUD_CENSUS
  MAR_STATUS_DT - Marital Status Date Date   Mar StatDt UM_STUD_CENSUS
  MAX_AUDIT_UNIT - Max Audit Units Num4.2   Max Audit UM_STUD_CENSUS
  MAX_CRSE_COUNT - Max Total Courses Num3.0   Total Crse UM_STUD_CENSUS
  MAX_NOGPA_UNIT - Max No GPA Units Num4.2   Max Grd Pt UM_STUD_CENSUS
  MAX_TOTAL_UNIT - Max Total Units Num4.2   Max Units UM_STUD_CENSUS
  MAX_WAIT_UNIT - Max Wait List Units Num4.2   Max Wait UM_STUD_CENSUS
  MIDDLE_NAME - Middle Name Char30   Middle UM_STUD_CENSUS
  MIN_TOTAL_UNIT - Min Total Units Num4.2   Min Units UM_STUD_CENSUS
  NAME - Name Char50   Name UM_STUD_CENSUS
  NAME_PREFIX - Name Prefix Char4   Prefix UM_STUD_CENSUS
  NAME_SUFFIX - Name Suffix Char15   Suffix UM_STUD_CENSUS
  NSLDS_LOAN_YEAR - NSLDS Loan Year Char1 N Loan Year UM_STUD_CENSUS
  OVRD_ACAD_LVL_ALL - Override All Academic Levels Char1   Ovrd Lvls UM_STUD_CENSUS
  OVRD_ACAD_LVL_PROJ - Override Projected Level Char1   Ovrd Level UM_STUD_CENSUS
  OVRD_BILL_UNITS - Override Billing Units Char1   Ovrd Bill UM_STUD_CENSUS
  OVRD_INIT_ADD_FEE - Override Initial Add Fees Char1   Override Fee UM_STUD_CENSUS
  OVRD_INIT_ENR_FEE - Override Initial Enroll Fee Char1   Override Fee UM_STUD_CENSUS
  OVRD_MAX_UNITS - Override Maximum Units Char1   Ovrd Units UM_STUD_CENSUS
  OVRD_TUIT_GROUP - Override Tuition Group Char10   Ov Tuit Gp UM_STUD_CENSUS
  OVRD_WDRW_SCHED - Override Withdrawal Schedule Char8   Ov Wd Schd UM_STUD_CENSUS
  PHONE - Telephone Char24   Phone UM_STUD_CENSUS
  PIN_NUM - PIN Number Num8.0   PIN Number UM_STUD_CENSUS
  PLAN_SEQUENCE - Plan Sequence Num2.0   Plan Seq UM_STUD_CENSUS
  POSTAL - Postal Code Char12   Postal UM_STUD_CENSUS
  PROG_ACTION - Program Action Char4 N Prog Actn UM_STUD_CENSUS
  PROG_REASON - Action Reason Char4   Action Rsn UM_STUD_CENSUS
  PROG_STATUS- Academic Program Status Char4 N Status UM_STUD_CENSUS
  PROJ_BILL_UNT - Projected Bill Units Num3.0   Bill Units UM_STUD_CENSUS
  PRO_RATA_ELIGIBLE - Pro-Rata Eligible Char1   Eligible UM_STUD_CENSUS
  REFUND_PCT - Refund Percentage Num4.2   Refund % UM_STUD_CENSUS
  REFUND_SCHEME - Refund Scheme Char8   Scheme UM_STUD_CENSUS
  REGISTERED - Registered Char1 N Registered UM_STUD_CENSUS
  REG_CARD_DATE - Registration Card Date Date   Reg Card UM_STUD_CENSUS
  REQ_TERM - Requirement Term Char4   Req Term UM_STUD_CENSUS
  RESET_CUM_STATS - Reset Cum Stats at Term Start Char1   Cum Reset UM_STUD_CENSUS
  RESIDENCY - Tuition Residency Char5   Tuition Resid UM_STUD_CENSUS
  RESIDENCY_DT - Residency Date Date   Res Date UM_STUD_CENSUS
  RESPONSE_DT - Response Date Date   Date UM_STUD_CENSUS
  RESPONSE_REASON - Response Reason Char4   Reason UM_STUD_CENSUS
  ROW_CHECK - ROW CHECK Char1   ROW CHECK UM_STUD_CENSUS
  SCHOOL_CODE - School Code Char10   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 Group Char10   Group UM_STUD_CENSUS
  SEX - Gender Char1 N Sex UM_STUD_CENSUS
  SSR_ACTIVATION_DT - Term Activation Date Date   Activation Date UM_STUD_CENSUS
  START_DATE - Start Date for Gen Standing PO Date   Start Date UM_STUD_CENSUS
  STATE - State Char6   State UM_STUD_CENSUS
  STATE_DESCR1 - State Description Char30   State UM_STUD_CENSUS
  STATS_ON_TRANS_DT - Show Statistics on Transcript Date   Stat Trans UM_STUD_CENSUS
Key STDNT_CAR_NBR - Student Career Nbr Num3.0   Career Nbr UM_STUD_CENSUS
  STDNT_DEGR - Student Degree Nbr Char2   Degree Nbr UM_STUD_CENSUS
Key STRM - Term Char4   Term UM_STUD_CENSUS
  STUDY_AGREEMENT - Study Agreement Char10   Agreement UM_STUD_CENSUS
  TC_UNITS_ADJUST - TC Units Adjustment Num6.3   TC Units Adjust UM_STUD_CENSUS
  TERM_TYPE - Term Unit Type Char1 N Term Type UM_STUD_CENSUS
  TOT_AUDIT - Total Audited Num6.3   Audit UM_STUD_CENSUS
  TOT_CUMULATIVE - Total Cumulative Units Num6.3   Total UM_STUD_CENSUS
  TOT_GRADE_POINTS - Total Grade Points Num7.3   Grade Points UM_STUD_CENSUS
  TOT_GRD_POINTS_FA - Total Fin Aid Grade Points Num7.3   Tot FA Grd UM_STUD_CENSUS
  TOT_INPROG_GPA - Total In Progress - GPA Num6.3   In Pr GPA UM_STUD_CENSUS
  TOT_INPROG_NOGPA - Total In Progress -Not for GPA Num6.3   InPr NoGPA UM_STUD_CENSUS
  TOT_OTHER - Total from Other Credit Num6.3   Other UM_STUD_CENSUS
  TOT_PASSD_FA - Total Fin Aid Units Passed Num6.3   Pass FA UM_STUD_CENSUS
  TOT_PASSD_GPA - Total Passed Toward GPA Num6.3   Pass GPA UM_STUD_CENSUS
  TOT_PASSD_NOGPA - Total Passed Not Toward GPA Num6.3   PassNoGPA UM_STUD_CENSUS
  TOT_PASSD_PRGRSS - Total Passed for Progress Num6.3   Pass Prgrs UM_STUD_CENSUS
  TOT_TAKEN_FA - Total Fin Aid Units Taken Num6.3   Take FA UM_STUD_CENSUS
  TOT_TAKEN_FA_GPA - Total Fin Aid Taken Toward GPA Num6.3   Tot FA GPA UM_STUD_CENSUS
  TOT_TAKEN_GPA - Total Taken Toward GPA Num6.3   Take GPA UM_STUD_CENSUS
  TOT_TAKEN_NOGPA - Total Taken Not Toward GPA Num6.3   TakeNoGPA UM_STUD_CENSUS
  TOT_TAKEN_PRGRSS - Total Taken for Progress Num6.3   Take Prgrs UM_STUD_CENSUS
  TOT_TEST_CREDIT - Total From Test Credit Num6.3   Test UM_STUD_CENSUS
  TOT_TRNSFR - Total Transferred Num6.3   Transfer UM_STUD_CENSUS
  TRF_GRADE_POINTS - Transfer Grade Points Num7.3   Trnsfr Grd Pts UM_STUD_CENSUS
  TRF_PASSED_GPA - Transfer Passed for GPA Num6.3   TrnPasGPA UM_STUD_CENSUS
  TRF_PASSED_NOGPA - Transfer Passed Not for GPA Num6.3   TrnPasNGPA UM_STUD_CENSUS
  TRF_RESIDENT_TERMS - Transfer In Residence Terms Num4.3   Trf Res UM_STUD_CENSUS
  TRF_TAKEN_GPA - Transfer Taken for GPA Num6.3   TrnTakGPA UM_STUD_CENSUS
  TRF_TAKEN_NOGPA - Transfer Taken Not for GPA Num6.3   TrnTakNGPA UM_STUD_CENSUS
  TUITION_EXCPT - Tuition Residency Exception Char5   Tuit Excpt 
SQL for 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 Residency Char5   Tuition 
SQL for 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 Residency Num4.3   Tuition Res. UM_STUD_CENSUS
  TUIT_CALC_DTTM - Tuit Calc Date Time DateTm   TuitDTTM UM_STUD_CENSUS
  TUIT_CALC_REQ - Tuition Calc Required Char1   Calc Req UM_STUD_CENSUS
  UM_ACADEMIC_YR - Academic Year Char12   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 Sort Char2   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 Descr Char30   Academic Group UM_STUD_CENSUS
  UM_ACAD_LOAD_RC - Calculated Academic Load Char1 N Calc Acad Load UM_STUD_CENSUS
  UM_ACAD_LVL_DESCR - Academic Level Description Char30   Acad Level Desc UM_STUD_CENSUS
  UM_ACAD_PLAN_DESCR - Academic Plan Description Char30   Acad Plan Descr UM_STUD_CENSUS
  UM_ACAD_PROGP_DSC - Acad Prog Primary Descr Char30   Acd Prg Prm Dsc UM_STUD_CENSUS
  UM_ACAD_SUBPLN_DSC - Academic Sub Plan Description Char30   Acad Sub Descr UM_STUD_CENSUS
  UM_ADMIT_TERM_YEAR - Admit Term Year Char4   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 Description Char50   Admit Type Desc Degree / Non-Degree Coding - Current Code
  UM_ADMIT_TYPE_D_ND - Admit Type Descr Non Degree Char50   Admit Type D ND

Degree / Non-Degree Coding - Current Code
  UM_ADMIT_TYPE_SORT - Admit Type Sort Char2   Admit Type Sort Degree / Non-Degree Coding - Current Code
  UM_ADVISOR_NAME_FM - Advisor Name Formatted Char50   Advisor Name  
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 Name Char30   Advisr Fst Name UM_STUD_CENSUS
  UM_ADVISR_LST_NAME - Advisor Last Name Char30   Advisr Lst Name UM_STUD_CENSUS
  UM_ADVISR_MID_NAME - Advisor Middle Name Char30   Advisr Mid Name UM_STUD_CENSUS
  UM_ADVISR_NAME_SFX - Advisor Name Suffix Char15   Advisr Name Sfx UM_STUD_CENSUS
  UM_ANY_COUNTRY - Any Country Char50   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 Country Char50   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 County  Char50   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 State Char50   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 Canada Char50   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 Age Num4.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 Precise Num4.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 Range Char15   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  '65ANd Over'
ELSE 'Unknown' ENDT - A.birthdate) 
/ 365.25) <= 105 THEN  '65 and Over'
ELSE 'Unknown' END
  UM_CENSUS_AGE_RSRT - Census Age Range Sort Char2   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 Other Char1   Citizn Stat Oth

UM_STUD_CENSUS 
This field is unreliable for students with more than 1 foreign country listed in the CITIZENSHIP table.
  UM_CITIZEN_ST_DESC - Citizenship Status Description Char20   Citizen St Desc 
Calculation for UM_CITIZEN_ST_DESC
   (Alias A = UM_STUD_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 Other Char3   Country Oth UM_STUD_CENSUS
  UM_COUNTRY_STATUS - Country Status Char15   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 USA Char3   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 Age Num4.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 Range Char15   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 Sort Char2   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-Degree  Char15   Degree NonDeg
 
  UM_EMAIL_HOM_ADDR - Email Home Address Char70   Email Home Addr

UM_STUD_CENSUS
  UM_ETHNIC_DESCR - Ethnic Description Char50   Ethic Descr UM_STUD_CENSUS
  UM_ETHNIC_GRP_DESC - Ethnic Groupd Description Char30   Ethnic Grp Desc

Calculation for UM_ETHNIC_GRP_DESC field
  UM_ETHNIC_GRP_SORT - Ethnic Group Sort Char2   Ethnic Grp Srt 
Calculation for UM_ETHNIC_GRP_SORT
 (Alias A = UM_STUD_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 Descr Char30   Eth Grp Descr UM_STUD_CENSUS
  UM_EXTERNAL_ORG - External Organization Char30   External Org

Degree / Non-Degree Coding - Current Code
  UM_EXTERNL_ORG_DTL - External Org Detail Char30   Externl Org Dtl Degree / Non-Degree Coding - Current Code
  UM_EXTERNL_ORG_SUM - External Organization Summary Char30   Externl Org Sum Degree / Non-Degree Coding - Current Code
  UM_EXT_ORGID_DESCR - External Org ID Description Char30   Ext Org ID Desc UM_STUD_CENSUS
  UM_EXT_ORG_INST - External Org Institution Char5   Ext Org Inst 
Calculation for UM_EXT_ORG_INST
   (Alias A = UM_STUD_CENSUS)
  DECODE(A.Ext_Org_Id,
 '0136246', 'UMS01',
 '0136247', 'UMS02',
 '0136248', 'UMS03',
 '0136249', 'UMS04',
 '0136250', 'UMS05',
 '0136392', 'UMS06',
 '0136252', 'UMS07',
 'OTHER')
  UM_FEES_PAID - Fees Paid SNm8.0   Fees Paid

UM_STUD_CENSUS 
Although this field says "Fees Paid," the values are actually charges calculated from the CHARGES tree.
  UM_FIRST_GEN - First Gen  Char1   First Gen

Calculation for First Generation
  UM_FIRST_TERM - First Term Char1   First Term 
Calculation for UM_FIRST_TERM
 (Alias A = UM_STUD_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 Time Char1   First Time Degree / Non-Degree Coding - Current Code
  UM_FIRST_TIME_NDEG - First Time Non-Degree Char1   First Time NonD Degree / Non-Degree Coding - Current Code
  UM_FTE_DIVISOR - FTE Divisor Num3.0   FTE Divisor 
SQL for UM_FTE_DIVISOR
 (alias A= UM_STUD_CENSUS)
HERE
CASE 
WHEN A.Institution 
= 'UMS02' 
AND A.Acad_plan 
= 'PSY-MA' THEN 15
WHEN 
A.Acad_Level_Bot IN 
('GR', 'MAS', 'PHD') 
THEN 9
WHEN 
A.Acad_Level_Bot IN 
('L1', 'L2', 'L3') 
THEN 15
WHEN 
A.Institution 
= 'UMS02' 
AND STRM < '2410' 
THEN 16
ELSE 15 END 
  UM_FULL_PART - Full-time Part-Time Char10   Full Part 
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 - Gender Char10   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 Year Char4   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 Short Char5   Inst Short 
Calculation for UM_INST_SHORT
 (Alias A = UM_STUD_CENSUS)
DECODE
(A.Institution,
'UMS01','UMA',
'UMS02','UMF',
'UMS03','UMFK',
'UMS04','UMM',
'UMS05','UM',
'UMS06','USM',
'UMS07','UMPI',
'Other')
  UM_IN_OUT - In-State Out-of_State Char15   In-State Out 
Calculation for UM_IN_OUT
 (Alias A = UM_STUD_CENSUS
  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 Description Char30   Lst School Desc

UM_STUD_CENSUS
  UM_LAST_SCH_TYPE - Last School Attended Type Char3   Last School Typ

UM_STUD_CENSUS
  UM_MAINE_COUNTY - Maine County Char1   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 Comp Num5.2   Max ACT Comp

 
  UM_MAX_ACT_ENGL - Maximum ACT English Num5.2   Max ACT English

UM_STUD_CENSUS
 - Limited to last 5 years from census date
  UM_MAX_ACT_MATH - Maximum ACT Math Num5.2   Max ACT Math

UM_STUD_CENSUS
 - Limited to last 5 years from census date
  UM_MAX_GRE_QUAN - Maximum Quantitative GRE Num5.2   Max GRE QUAN UM_STUD_CENSUS
  UM_MAX_GRE_VERB - Maximum GRE Verbal Num5.2   Max GRE VERB UM_STUD_CENSUS
  UM_MAX_SAT_ERWS - Max SAT ERWS  Num5.2   Max SAT ERWS

UM_STUD_CENSUS 
-  Limited to last 5 years from census date
  UM_MAX_SAT_MATH - Maximum Sat Math Num5.2   Max Sat Math

UM_STUD_CENSUS
 - Limited to last 5 years from census date
  UM_MAX_SAT_MSS - Max SAT MSS Num5.2   Max SAT MSS

UM_STUD_CENSUS
 - Limited to last 5 years from census date
  UM_MAX_SAT_VERB - Maximum Sat Verbal Num5.2   Max Sat Verbal

UM_STUD_CENSUS
 - Limited to last 5 years from census date
  UM_MAX_SAT_WRIT - Maximum SAT Write Num5.2   Max SAT Write

UM_STUD_CENSUS
 - Limited to last 5 years from census date
  UM_MAX_TOEFL_COMPC - Maximum TOEFL COMPC Num5.2   Max TOEFL COMPC UM_STUD_CENSUS
  UM_MAX_TOEFL_COMPI - Maximum TOEFL COMPI Num5.2   Max TOEFL COMPI UM_STUD_CENSUS
  UM_MAX_TOEFL_COMPP - Maximum TOEFL COMPP Num5.2   Max TOEFL COMPP UM_STUD_CENSUS
  UM_MIN_ACAD_PLAN - Academic Plan Char10   Acad Plan UM_STUD_CENSUS
  UM_MIN_ACAD_PROG - Academic Program Char5   Acad Prog UM_STUD_CENSUS
  UM_MIN_ACAD_SUBPLN - Academic Sub-Plan Char10   Sub-Plan UM_STUD_CENSUS
  UM_MULTIPLE_ETHNIC - Multiple Ethnic Groups Char1 N Mutiple_Ethnic UM_STUD_CENSUS
  UM_NEASC_LEVEL - NEASC Level Char50   NEASC Level

Degree / Non-Degree Coding - Current Code
  UM_NEASC_LEVEL_SRT - NEASC Level Sort Char2   NEASC Level Srt Degree / Non-Degree Coding - Current Code
  UM_NID_CAN - National ID Canada Char20   NID Canada UM_STUD_CENSUS
  UM_NID_USA - National Id USA Char20   NID USA UM_STUD_CENSUS
  UM_NURSING_FLAG - Nursing Flag Char1   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_PLAN Char10   ORIG_ACAD_PLAN UM_STUD_CENSUS
  UM_ORIG_ACAD_PROG - ORIGINAL_ACAD_PROG Char5   ORIG_ACAD_PROG UM_STUD_CENSUS
  UM_PREVIOUS_NAME - Previous Name Char50   Prev Name UM_STUD_CENSUS
  UM_RESIDENCY_DESCR - Residency Description Char30   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 Sort Char2   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 Descriptions Char30   Resp Reason Des UM_STUD_CENSUS
  UM_STD_LVL_SUM_SRT - Student Level Summary Sort Char2   Std Lvl Sum Srt Degree / Non-Degree Coding - Current Code
  UM_STD_LVL_SUM_UG - Student Level Sum UG Grad Char30   Std Lvl Sum UG Degree / Non-Degree Coding - Current Code
  UM_STRM_YEAR - Strm Year Char4   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 Level Char50   Student Level

Please go to Degree/Non-Degree Coding - Current page for the code
  UM_STUDENT_NAME - Student Char50   Student 
Calculation for UM_STUDENT_NAME
 (Alias A = UM_STUD_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 Sort Char2   Stud Level Sort

Degree / Non-Degree Coding - Current Code
  UM_STUD_LEVEL_SUM - Student Level Summary Char30   Stud Level Sum

 Degree / Non-Degree Coding - Current Code
  UM_TUITION_COUNTRY - Tuition Country Char50   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 County Char50   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 Paid SNm8.0   Tuition Paid

UM_STUD_CENSUS - Although this field says "Tuition Paid," the values are actually charges calculated from the CHARGES tree 
  UM_TUITION_STATE - Tuition State Char50   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 Flag Char1   Ums01 Flag UM_STUD_CENSUS
  UM_UMS01_UNITS - UMS01 Units Num6.3   UMS01 Units UM_STUD_CENSUS
  UM_UMS02_FLG - UMS02 Registered Flag Char1   UMS02 Flag UM_STUD_CENSUS
  UM_UMS02_UNITS - UMS02 Units Num6.3   UMS02 Units UM_STUD_CENSUS
  UM_UMS03_FLG - Ums03 Registered Flag Char1   Ums03 Flag UM_STUD_CENSUS
  UM_UMS03_UNITS - UMS03 Units Num6.3   Ums03 Units UM_STUD_CENSUS
  UM_UMS04_FLG - UMS04 Registered Flag Char1   UMS04 Flag UM_STUD_CENSUS
  UM_UMS04_UNITS - UMS04 Units Num6.3   UMS04 Units UM_STUD_CENSUS
  UM_UMS05_FLG - Ums05 Registered Flag Char1   Ums05 Flag UM_STUD_CENSUS
  UM_UMS05_UNITS - UMS05 Units Num6.3   UMS05 Units UM_STUD_CENSUS
  UM_UMS06_FLG - Ums06 Registered Flag Char1   Ums06 Flag UM_STUD_CENSUS
  UM_UMS06_UNITS - UMS06 Units Num6.3   UMS06 Units UM_STUD_CENSUS
  UM_UMS07_FLG - Ums07 Registered Flag Char1   Ums07 Flag UM_STUD_CENSUS
  UM_UMS07_UNITS - UMS07 Units Num6.3   UMS07 Units UM_STUD_CENSUS
  UM_UNT_DIST - Units Distance Num10.3   Units Distance UM_STUD_CENSUS
  UM_UNT_ITV - Itv Receive Units Num10.3   Itv Receive Unt UM_STUD_CENSUS
  UM_UNT_TAK_CAREER - Units Taken For Career Num6.3   Unt Takn Career UM_STUD_CENSUS
  UM_UNT_TAK_PRG_RC - Units Taken Progress Row Check Num6.3   Units Taken Prg UM_STUD_CENSUS
  UM_UNT_WEB - Web Units Num10.3   Web Units UM_STUD_CENSUS
  UNIT_MULTIPLIER - Unit Multiplier Num5.4   Multiplier UM_STUD_CENSUS
  UNTPRG_CHG_NSLC_DT - Unit Progrss Changed Date NSC Date   Unit ChgDt NSC UM_STUD_CENSUS
  UNT_AUDIT - Units Audited Num6.3   Audit UM_STUD_CENSUS
  UNT_INPROG_GPA - Units In Progress - GPA Num6.3   InPr GPA UM_STUD_CENSUS
  UNT_INPROG_NOGPA - Unit In Progress - Not for GPA Num6.3   InPr NoGPA UM_STUD_CENSUS
  UNT_OTHER - Units from Other Credit Num6.3   Other UM_STUD_CENSUS
  UNT_PASSD_FA - Fin Aid Progress Units Passed Num6.3   FA Passed UM_STUD_CENSUS
  UNT_PASSD_GPA - Units Passed Toward GPA Num6.3   Pass GPA UM_STUD_CENSUS
  UNT_PASSD_NOGPA - Units Passed Not Toward GPA Num6.3   PassNoGPA UM_STUD_CENSUS
  UNT_PASSD_PRGRSS - Units Passed for Progress Num6.3   Pass Prgrs UM_STUD_CENSUS
  UNT_TAKEN_FA - Fin Aid Progress Units Taken Num6.3   FA Taken UM_STUD_CENSUS
  UNT_TAKEN_FA_GPA - FA Units Taken Toward GPA Num6.3   FA Units T UM_STUD_CENSUS
  UNT_TAKEN_GPA - Units Taken Toward GPA Num6.3   Take GPA UM_STUD_CENSUS
  UNT_TAKEN_NOGPA - Units Taken Not Toward GPA Num6.3   TakeNoGPA UM_STUD_CENSUS
  UNT_TAKEN_PRGRSS - Units Taken for Progress Num6.3   Take Prgrs UM_STUD_CENSUS
  UNT_TERM_TOT - Total Term Units Num6.3   Total UM_STUD_CENSUS
  UNT_TEST_CREDIT - Units from Test Credit Num6.3   Test UM_STUD_CENSUS
  UNT_TRNSFR - Units Transferred Num6.3   Transfer UM_STUD_CENSUS
  VALIDATED - Election Validated Char1   Validated UM_STUD_CENSUS
  VALIDATED_DT - Date Data Validated Date   Date Validated UM_STUD_CENSUS
  WITHDRAW_CODE - Withdrawal \ Cancel Char3 N Wdraw Code UM_STUD_CENSUS
  WITHDRAW_DATE - Withdrawal \ Cancel Date Date   Wdraw Date UM_STUD_CENSUS
  WITHDRAW_REASON - Withdrawal \ Cancel Reason Char4 N WithdrlRsn UM_STUD_CENSUS

Back to Top

Submit an Inquiry or Request to DARTS Print Article

Details

Article ID: 158911
Created
Fri 5/10/24 4:28 AM
Modified
Fri 8/23/24 12:15 PM
Applies To
Faculty
Staff
Community

Related Services / Offerings (1)

DARTS (UMS Data) Inquiry
Submit an inquiry about UMS Data (except Data Governance)