UM_STUD_CENS_VW - Student Reporting Extract

Body

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.

Detailed Information 

Database

PeopleSoft/ MaineStreet Campus Solutions Reporting - CSRPT

Overview

This view returns more data than the UM_STUD_CENSUS and returns actively enrolled students only. The ROW_CHECK field is always Y. Students with ACAD_CAREER = 'NCRD' are excluded. 

Build Code

Click here to view SQL Build Code

Click here to download SQL Build Code

Fields

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.

Key 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

Details

Details

Article ID: 158911
Created
Fri 5/10/24 4:28 AM
Modified
Tue 10/22/24 2:03 PM
Applies To
Faculty
Staff
Community

Related Services / Offerings

Related Services / Offerings (1)

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

Attachments

Attachments (1) Sort By: Sort Attachments ByName Sort Attachments ByDate

txt

UM_STUD_CENS_VW SQL Build Code.txt Computer

Fri 8/23/24 11:59 AM
View
;