UM_STUD_EXT_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 an efficient view of the UM_STUD_EXTRACT, which is refreshed daily except for Sundays. It returns a vast array of student demographics, and is good for all current and historical reporting and research.

Detailed Information  

Data Systems

PeopleSoft Campus Solutions  CSPRD
PeopleSoft Campus Solutions Reporting CSRPT

View SQL Build Code

Download SQL Build Code

Note - code blocks defining Fields can be extracted in their entirety by selecting all the code in the cell, such as by clicking and dragging your cursor from top to bottom or using your keyboard, and then using control-C keyboard strokes to copy the code block and pasting it into your code editor of choice.

Key Record.Fieldname Format XLAT Heading Text Notes
ACADEMIC_LOAD - Academic Load Char1 N Acad Load UM_STUD_EXTRACT
ACADEMIC_LOAD_DT - Academic Load Change Date Date Load Date UM_STUD_EXTRACT
Key ACAD_CAREER - Academic Career Char4 N Career UM_STUD_EXTRACT
ACAD_CAREER_FIRST - First Time in Career Char1 First Time Car UM_STUD_EXTRACT
ACAD_GROUP - Academic Group Char5 Acad Group UM_STUD_EXTRACT
ACAD_GROUP_ADVIS - Academic Group of Advisor Char5 Group UM_STUD_EXTRACT
ACAD_LEVEL_BOT - Academic Level - Term Start Char3 Strt Level UM_STUD_EXTRACT
ACAD_LEVEL_EOT - Academic Level - Term End Char3 End Level UM_STUD_EXTRACT: Do not use
ACAD_LEVEL_PROJ - Academic Level - Projected Char3 Proj Level UM_STUD_EXTRACT
ACAD_LOAD_APPR - Approved Academic Load Char1 N Acad Load UM_STUD_EXTRACT
Key ACAD_PLAN - Academic Plan Char10 Acad Plan UM_STUD_EXTRACT
ACAD_PLAN _TYPE - Academic Plan Type Char3 N Plan Type UM_STUD_EXTRACT
Key ACAD_PROG - Academic Program Char5 Acad Prog UM_STUD_EXTRACT
ACAD_PROG_DESCR - Academic Program Char30 Program UM_STUD_EXTRACT
ACAD_PROG_DUAL - Dual Academic Program Char5 Dual Prog UM_STUD_EXTRACT
ACAD_PROG_PRIMARY - Primary Academic Program Char5 Prim Prog UM_STUD_EXTRACT
ACAD_SUBPLAN_TYPE - Academic Sub-Plan Type Char3 N Sub-Pl Typ UM_STUD_EXTRACT
ACAD_SUB_PLAN - Academic Sub-Plan Char10 Sub-Plan UM_STUD_EXTRACT
ACAD_YEAR - Academic Year Char4 Acad Year UM_STUD_EXTRACT
ACTION_DT - Action Date Date Action Date UM_STUD_EXTRACT
ADDRESS1 - Address Line 1 Char55 Address 1 UM_STUD_EXTRACT
ADDRESS2 - Address Line 2 Char55 Address 2 UM_STUD_EXTRACT
ADDRESS3 - Address Line 3 Char55 Address 3 UM_STUD_EXTRACT
ADDRESS4 - Address Line 4 Char55 Address 4 UM_STUD_EXTRACT
ADMISSION_EXCPT - Admission Residency Exception Char5 Adm Excpt UM_STUD_EXTRACT
ADMISSION_RES - Admissions Residency Char5 Admissions UM_STUD_EXTRACT
Key ADMIT_TERM - Admit Term Char4 Admit Term UM_STUD_EXTRACT
ADMIT_TYPE - Admit Type Char3 Admit Type UM_STUD_EXTRACT
ADM_APPL_CTR - Application Center Char4 Appl Ctr UM_STUD_EXTRACT
Key ADM_APPL_NBR - Application Nbr Char8 Appl Nbr UM_STUD_EXTRACT
ADVISOR_ID - Academic Advisor Char11 Advisor UM_STUD_EXTRACT

ADVIS_STATUS - Advisement Status

 Char4 N Status UM_STUD_EXTRACT
BILLING_CAREER - Billing Career Char4 Bill Car UM_STUD_EXTRACT
BIRTHDATE - Date of Birth Date Birthdate UM_STUD_EXTRACT
BIRTHPLACE - Birth Location Char30 Birthplace UM_STUD_EXTRACT
BIRTHSTATE - Birth State Char6 State UM_STUD_EXTRACT
BIRTH_COUNTRY - BIRTH_COUNTRY Char4 BIRTH_COUNTRY UM_STUD_EXTRACT
CAMPUS - Campus Char5 Campus UM_STUD_EXTRACT

CITIZENSHIP_STATUS - Citizenship Status

 Char1 Status UM_STUD_EXTRACT
CITY - City Char30 City UM_STUD_EXTRACT
CLASS_RANK_NBR - Class Rank Nbr Num5.0 Class Rank UM_STUD_EXTRACT
CLASS_RANK_TOT - Class Rank Total Num5.0 Of UM_STUD_EXTRACT
COMPLETION_TERM - Completion Term Char4 Compl Term UM_STUD_EXTRACT
COUNTRY - Country Char3 Country UM_STUD_EXTRACT
COUNTRY_DESCR1 - Country Description Char30 Country UM_STUD_EXTRACT
COUNTY - County Char30 County UM_STUD_EXTRACT
CUM_GPA - Cumulative GPA Num6.3 GPA UM_STUD_EXTRACT
CUM_RESIDENT_TERMS - Cumulative In Residence Terms Num4.3 Cum Res UM_STUD_EXTRACT
CUR_GPA - Current GPA Num6.3 GPA UM_STUD_EXTRACT
CUR_RESIDENT_TERMS - Current In Residence Terms Num4.3 Cur Res UM_STUD_EXTRACT
DECLARE_DT - Declare Date Date Decl Date UM_STUD_EXTRACT
DEGREE - Degree Char8 Degree UM_STUD_EXTRACT
DEGR_CHKOUT_STAT - Degree Checkout Status Char2 N ChkoutStat UM_STUD_EXTRACT
DT_OF_DEATH - Date of Death Date Death Date UM_STUD_EXTRACT
EDUCATION_LVL - Education Level Char2 N Education UM_STUD_EXTRACT
EFFECTIVE_TERM - Effective Term Char4 Effective UM_STUD_EXTRACT
ELIG_TO_ENROLL - Eligible to Enroll Char1 Elig Enrl UM_STUD_EXTRACT
EMAIL_ADDR - Email Address Char70 Email UM_STUD_EXTRACT
Key EMPLID - Empl ID Char11 ID UM_STUD_EXTRACT
END_DATE - End Date Date End Date UM_STUD_EXTRACT
ENRL_ON_TRANS_DT - Show Enrollment on Transcript Date Enrl Trans UM_STUD_EXTRACT
ETHNIC_CATEGORY - Ethnic Category Char1 N Ethnic Cat

UM_STUD_EXTRACT this field has no data
ETHNIC_GROUP - Ethnic Group Char1 N Ethnic Grp UM_STUD_EXTRACT
ETHNIC_GRP_CD - Ethnic Group Char8 Ethnic Grp UM_STUD_EXTRACT
EXP_GRAD_TERM - Expected Graduation Term Char4 Exp Grad UM_STUD_EXTRACT
EXT_ORG_ID - External Org ID Char11 Org ID UM_STUD_EXTRACT
FA_ELIGIBILITY - Program Eligibility Flag Char1 Eligible UM_STUD_EXTRACT
FA_LOAD - Financial Aid Load Char1 N FA Load UM_STUD_EXTRACT
FA_STATS_CALC_DTTM - FA Stats Calc Date Time DateTm FA DTTM UM_STUD_EXTRACT
FA_STATS_CALC_REQ - FA Stats Calculation Required Char1

FA Calc

 UM_STUD_EXTRACT
FIRST_NAME - First Name Char30 First Name UM_STUD_EXTRACT
FIN_AID_FED_EXCPT - Fin Aid Fed Residency Excpt Char5 FA Fed Exc 
- FIN_AID_FED_EXCPT -
SQL for some FIN_AID_ fields
select 
qb.residency 
AS um_residency_reg,
qb.tuition_res,
qb.fin_aid_fed_res,
qb.fin_aid_st_res,
qb.fin_aid_fed_excpt,
qb.fin_aid_st_excpt,
qb.tuition_excpt
FROM 
PS_UM_STUD_EXTRACT A, 
ps_residency_off qb
WHERE a.emplid = qb.emplid(+)
AND a.acad_career = qb.acad_career(+)
AND a.institution = qb.institution(+)
AND ((QB.EFFECTIVE_TERM =
(SELECT MAX(EB9.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF EB9
WHERE EB9.EMPLID = QB.EMPLID
AND EB9.ACAD_CAREER = QB.ACAD_CAREER
AND EB9.INSTITUTION = QB.INSTITUTION
AND EB9.EFFECTIVE_TERM <= A.STRM) OR
(QB.EFFECTIVE_TERM =
(SELECT MAX(EC9.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF EC9
WHERE EC9.EMPLID = QB.EMPLID
AND EC9.ACAD_CAREER = EC9.ACAD_CAREER
AND EC9.INSTITUTION = QB.INSTITUTION) 
AND NOT EXISTS
 (SELECT 'X'
FROM PS_RESIDENCY_OFF ED9
WHERE ED9.EMPLID = QB.EMPLID
AND ED9.ACAD_CAREER = QB.ACAD_CAREER
AND ED9.INSTITUTION = QB.INSTITUTION
AND ED9.EFFECTIVE_TERM <= A.STRM))) OR
QB.EFFECTIVE_TERM IS NULL)
FIN_AID_FED_RES - Fin Aid Federal Residency Char5 FinAid Fed 
  - FIN_AID_FED_RES  -
SQL for some FIN_AID_ fields
select 
qb.residency 
AS um_residency_reg,
qb.tuition_res,
qb.fin_aid_fed_res,
qb.fin_aid_st_res,
qb.fin_aid_fed_excpt,
qb.fin_aid_st_excpt,
qb.tuition_excpt
FROM 
PS_UM_STUD_EXTRACT A, 
ps_residency_off qb
WHERE a.emplid = qb.emplid(+)
AND a.acad_career = qb.acad_career(+)
AND a.institution = qb.institution(+)
AND ((QB.EFFECTIVE_TERM =
(SELECT MAX(EB9.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF EB9
WHERE EB9.EMPLID = QB.EMPLID
AND EB9.ACAD_CAREER = QB.ACAD_CAREER
AND EB9.INSTITUTION = QB.INSTITUTION
AND EB9.EFFECTIVE_TERM <= A.STRM) OR
(QB.EFFECTIVE_TERM =
(SELECT MAX(EC9.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF EC9
WHERE EC9.EMPLID = QB.EMPLID
AND EC9.ACAD_CAREER = EC9.ACAD_CAREER
AND EC9.INSTITUTION = QB.INSTITUTION) 
AND NOT EXISTS
 (SELECT 'X'
FROM PS_RESIDENCY_OFF ED9
WHERE ED9.EMPLID = QB.EMPLID
AND ED9.ACAD_CAREER = QB.ACAD_CAREER
AND ED9.INSTITUTION = QB.INSTITUTION
AND ED9.EFFECTIVE_TERM <= A.STRM))) OR
QB.EFFECTIVE_TERM IS NULL)
FIN_AID_ST_EXCPT - Fin Aid St Residency Excpt Char5 FA St Excp 
 - FIN_AID_ST_EXCPT  -
SQL for some FIN_AID_ fields
select 
qb.residency 
AS um_residency_reg,
qb.tuition_res,
qb.fin_aid_fed_res,
qb.fin_aid_st_res,
qb.fin_aid_fed_excpt,
qb.fin_aid_st_excpt,
qb.tuition_excpt
FROM 
PS_UM_STUD_EXTRACT A, 
ps_residency_off qb
WHERE a.emplid = qb.emplid(+)
AND a.acad_career = qb.acad_career(+)
AND a.institution = qb.institution(+)
AND ((QB.EFFECTIVE_TERM =
(SELECT MAX(EB9.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF EB9
WHERE EB9.EMPLID = QB.EMPLID
AND EB9.ACAD_CAREER = QB.ACAD_CAREER
AND EB9.INSTITUTION = QB.INSTITUTION
AND EB9.EFFECTIVE_TERM <= A.STRM) OR
(QB.EFFECTIVE_TERM =
(SELECT MAX(EC9.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF EC9
WHERE EC9.EMPLID = QB.EMPLID
AND EC9.ACAD_CAREER = EC9.ACAD_CAREER
AND EC9.INSTITUTION = QB.INSTITUTION) 
AND NOT EXISTS
 (SELECT 'X'
FROM PS_RESIDENCY_OFF ED9
WHERE ED9.EMPLID = QB.EMPLID
AND ED9.ACAD_CAREER = QB.ACAD_CAREER
AND ED9.INSTITUTION = QB.INSTITUTION
AND ED9.EFFECTIVE_TERM <= A.STRM))) OR
QB.EFFECTIVE_TERM IS NULL)
FIN_AID_ST_RES - Fin Aid State Residency Char5 FA St Excp 
 - FIN_AID_ST_RES -
SQL for some FIN_AID_ fields
select 
qb.residency 
AS um_residency_reg,
qb.tuition_res,
qb.fin_aid_fed_res,
qb.fin_aid_st_res,
qb.fin_aid_fed_excpt,
qb.fin_aid_st_excpt,
qb.tuition_excpt
FROM 
PS_UM_STUD_EXTRACT A, 
ps_residency_off qb
WHERE a.emplid = qb.emplid(+)
AND a.acad_career = qb.acad_career(+)
AND a.institution = qb.institution(+)
AND ((QB.EFFECTIVE_TERM =
(SELECT MAX(EB9.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF EB9
WHERE EB9.EMPLID = QB.EMPLID
AND EB9.ACAD_CAREER = QB.ACAD_CAREER
AND EB9.INSTITUTION = QB.INSTITUTION
AND EB9.EFFECTIVE_TERM <= A.STRM) OR
(QB.EFFECTIVE_TERM =
(SELECT MAX(EC9.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF EC9
WHERE EC9.EMPLID = QB.EMPLID
AND EC9.ACAD_CAREER = EC9.ACAD_CAREER
AND EC9.INSTITUTION = QB.INSTITUTION) 
AND NOT EXISTS
 (SELECT 'X'
FROM PS_RESIDENCY_OFF ED9
WHERE ED9.EMPLID = QB.EMPLID
AND ED9.ACAD_CAREER = QB.ACAD_CAREER
AND ED9.INSTITUTION = QB.INSTITUTION
AND ED9.EFFECTIVE_TERM <= A.STRM))) OR
QB.EFFECTIVE_TERM IS NULL)
FORM_OF_STUDY - Form of Study Char4 N Study Form UM_STUD_EXTRACT
FTE - FTE Num2.6 FTE 
Calculation for FTE
(Alias A= UM_STUD_EXTRACT)
A.Um_Unt_Tak_Prg_Rc / (CASE            
WHEN A.Institution = 'UMS02' 
AND A.Acad_plan = 'PSY-MA' THEN 15
WHEN A.Acad_Level_Bot IN 
('GR', 'MAS', 'PHD') THEN 9 
WHEN A.Acad_Level_Bot IN 
('L1', 'L2', 'L3') THEN 15
WHEN A.Institution = 'UMS02' 
AND STRM < '2410' THEN 16
ELSE 15 END)
FULLY_ENRL_DT - Fully Enrolled Date Date Full Enrl UM_STUD_EXTRACT
FULLY_GRADED_DT - Fully Graded Date Date Full Grded UM_STUD_EXTRACT
GRADE_POINTS - Grade Points Num7.3 Grd Points UM_STUD_EXTRACT
GRADE_POINTS_FA - Financial Aid Grade Points Num7.3 FA Grd Pt UM_STUD_EXTRACT
GRADUATION_DT - Graduation Date Date Grad Dt

UM_STUD_EXTRACT High school graduation date
HEADCOUNT - Headcount Num6.0 Headcount 1 (put '1' on each UNIQUE data ROW)
HISP_LATINO - Person is Hispanic or Latino Char1 Hisp or Latin

UM_STUD_EXTRACT (Yes or No)
Key INSTITUTION - Academic Institution Char5 Institution UM_STUD_EXTRACT
INSTITUTION_DESCR - Institution Char30 Institution UM_STUD_EXTRACT
JOINT_PROG_APPR - Joint Program Approved Char1 Joint Program UM_STUD_EXTRACT
LAST_DATE_ATTENDED - Last Date of Attendance Date Last Date UM_STUD_EXTRACT
LAST_NAME - Last Name Char30 Last UM_STUD_EXTRACT
LEVEL_LOAD_RULE - Academic Level Rule Char5 Level Rule UM_STUD_EXTRACT
LOCK_IN_AMT - Lock In Amount SNm15.2 Amount UM_STUD_EXTRACT
LOCK_IN_DT - Lock In Date Date Date UM_STUD_EXTRACT
MAR_STATUS - Marital Status Char1 N Mar Status UM_STUD_EXTRACT
MAR_STATUS_DT - Marital Status Date Date Mar StatDt UM_STUD_EXTRACT
MAX_AUDIT_UNIT - Max Audit Units Num4.2 Max Audit UM_STUD_EXTRACT
MAX_CRSE_COUNT - Max Total Courses Num3.0 Total Crse UM_STUD_EXTRACT
MAX_NOGPA_UNIT - Max No GPA Units Num4.2 Max Grd Pt UM_STUD_EXTRACT
MAX_TOTAL_UNIT - Max Total Units Num4.2 Max Units UM_STUD_EXTRACT
MAX_WAIT_UNIT - Max Wait List Units Num4.2 Max Wait UM_STUD_EXTRACT
MIDDLE_NAME - Middle Name Char30 Middle UM_STUD_EXTRACT
MIN_TOTAL_UNIT - Min Total Units Num4.2 Min Units UM_STUD_EXTRACT
NAME - Name Char50 Name UM_STUD_EXTRACT
NAME_PREFIX - Name Prefix Char4 Prefix UM_STUD_EXTRACT
NAME_SUFFIX - Name Suffix Char15 Suffix UM_STUD_EXTRACT
NSLDS_LOAN_YEAR - NSLDS Loan Year Char1 N Loan Year UM_STUD_EXTRACT
OVRD_ACAD_LVL_ALL - Override All Academic Levels Char1 Ovrd Lvls UM_STUD_EXTRACT
OVRD_ACAD_LVL_PROJ - Override Projected Level Char1 Ovrd Level UM_STUD_EXTRACT
OVRD_BILL_UNITS - Override Billing Units Char1 Ovrd Bill UM_STUD_EXTRACT
OVRD_INIT_ADD_FEE - Override Initial Add Fees Char1 Override Fee UM_STUD_EXTRACT
OVRD_INIT_ENR_FEE - Override Initial Enroll Fee Char1 Override Fee UM_STUD_EXTRACT
OVRD_MAX_UNITS - Override Maximum Units Char1 Ovrd Units UM_STUD_EXTRACT
OVRD_TUIT_GROUP - Override Tuition Group Char10 Ov Tuit Gp UM_STUD_EXTRACT
OVRD_WDRW_SCHED - Override Withdrawal Schedule Char8 Ov Wd Schd UM_STUD_EXTRACT
PHONE - Telephone Char24 Phone UM_STUD_EXTRACT
PIN_NUM - PIN Number Num8.0 PIN Number UM_STUD_EXTRACT
PLAN_SEQUENCE - Plan Sequence Num2.0 Plan Seq UM_STUD_EXTRACT
POSTAL - Postal Code Char12 Postal UM_STUD_EXTRACT
PROG_ACTION - Program Action Char4 N Prog Actn UM_STUD_EXTRACT
PROG_REASON - Action Reason Char4 Action Rsn UM_STUD_EXTRACT
PROG_STATUS - Academic Program Status Char4 N Status UM_STUD_EXTRACT
PROJ_BILL_UNT - Projected Bill Units Num3.0 Bill Units UM_STUD_EXTRACT
PRO_RATA_ELIGIBLE - Pro-Rata Eligible Char1 Eligible UM_STUD_EXTRACT
REFUND_PCT - Refund Percentage Num4.2 Refund % UM_STUD_EXTRACT
REFUND_SCHEME - Refund Scheme Char8 Scheme UM_STUD_EXTRACT
REGISTERED - Registered Char1 N Registered UM_STUD_EXTRACT
REG_CARD_DATE - Registration Card Date Date Reg Card UM_STUD_EXTRACT
REQ_TERM - Requirement Term Char4 Req Term UM_STUD_EXTRACT
RESET_CUM_STATS - Reset Cum Stats at Term Start Char1 Cum Reset UM_STUD_EXTRACT
RESIDENCY - Tuition Residency Char5 Tuition Resid UM_STUD_EXTRACT
RESIDENCY_DT - Residency Date Date Res Date UM_STUD_EXTRACT
RESPONSE_DT - Response Date Date Date UM_STUD_EXTRACT
RESPONSE_REASON - Response Reason Char4 Reason UM_STUD_EXTRACT
ROW_CHECK - ROW CHECK Char1 ROW CHECK UM_STUD_EXTRACT
SCHOOL_CODE - School Code Char10 School Code 
Calculation for SCHOOL_CODE
 (Alias A= UM_STUD_EXTRACT)
DECODE(A.Institution,
'UMS01','006760',
'UMS02','002040',
'UMS03','002041',
'UMS04','002055',
'UMS05','002053',
'UMS06','002054',
'UMS07','002033',' ')
SEL_GROUP - Tuition Group Char10 Group UM_STUD_EXTRACT
SEX - Gender Char1 N Sex UM_STUD_EXTRACT
SSR_ACTIVATION_DT - Term Activation Date Date Activation Date UM_STUD_EXTRACT
START_DATE - Start Date for Gen Standing PO Date Start Date UM_STUD_EXTRACT
STATE - State Char6 State UM_STUD_EXTRACT
STATE_DESCR1 - State Description Char30 State UM_STUD_EXTRACT
STATS_ON_TRANS_DT - Show Statistics on Transcript Date Stat Trans UM_STUD_EXTRACT
Key STDNT_CAR_NBR - Student Career Nbr Num3.0 Career Nbr UM_STUD_EXTRACT
STDNT_DEGR - Student Degree Nbr Char2 Degree Nbr UM_STUD_EXTRACT
Key STRM - Term- Term Char4 Term UM_STUD_EXTRACT
STUDY_AGREEMENT - Study Agreement Char10 Agreement UM_STUD_EXTRACT
TC_UNITS_ADJUST - TC Units Adjustment Num6.3 TC Units Adjust UM_STUD_EXTRACT
TERM_TYPE - Term Unit Type Char1 N Term Type UM_STUD_EXTRACT
TOT_AUDIT - Total Audited Num6.3 Audit UM_STUD_EXTRACT
TOT_CUMULATIVE - Total Cumulative Units Num6.3 Total UM_STUD_EXTRACT
TOT_GRADE_POINTS - Total Grade Points Num7.3 Grade Points UM_STUD_EXTRACT
TOT_GRD_POINTS_FA - Total Fin Aid Grade Points Num7.3 Tot FA Grd UM_STUD_EXTRACT
TOT_INPROG_GPA - Total In Progress - GPA Num6.3 In Pr GPA UM_STUD_EXTRACT
TOT_INPROG_NOGPA - Total In Progress -Not for GPA Num6.3 InPr NoGPA UM_STUD_EXTRACT
TOT_OTHER - Total from Other Credit Num6.3 Other UM_STUD_EXTRACT
TOT_PASSD_FA - Total Fin Aid Units Passed Num6.3 Pass FA UM_STUD_EXTRACT
TOT_PASSD_GPA - Total Passed Toward GPA Num6.3 Pass GPA UM_STUD_EXTRACT
TOT_PASSD_NOGPA - Total Passed Not Toward GPA Num6.3 PassNoGPA UM_STUD_EXTRACT
TOT_PASSD_PRGRSS - Total Passed for Progress Num6.3 Pass Prgrs UM_STUD_EXTRACT
TOT_TAKEN_FA - Total Fin Aid Units Taken Num6.3 Take FA UM_STUD_EXTRACT
TOT_TAKEN_FA_GPA - Total Fin Aid Taken Toward GPA Num6.3 Tot FA GPA UM_STUD_EXTRACT
TOT_TAKEN_GPA - Total Taken Toward GPA Num6.3 Take GPA UM_STUD_EXTRACT
TOT_TAKEN_NOGPA - Total Taken Not Toward GPA Num6.3 TakeNoGPA UM_STUD_EXTRACT
TOT_TAKEN_PRGRSS - Total Taken for Progress Num6.3 Take Prgrs UM_STUD_EXTRACT
TOT_TEST_CREDIT - Total From Test Credit Num6.3 Test UM_STUD_EXTRACT
TOT_TRNSFR - Total Transferred Num6.3 Transfer UM_STUD_EXTRACT
TRF_GRADE_POINTS - Transfer Grade Points Num7.3 Trnsfr Grd Pts UM_STUD_EXTRACT
TRF_PASSED_GPA - Transfer Passed for GPA Num6.3 TrnPasGPA UM_STUD_EXTRACT
TRF_PASSED_NOGPA - Transfer Passed Not for GPA Num6.3 TrnPasNGPA UM_STUD_EXTRACT
TRF_RESIDENT_TERMS - Transfer In Residence Terms Num4.3 Trf Res UM_STUD_EXTRACT
TRF_TAKEN_GPA - Transfer Taken for GPA Num6.3 TrnTakGPA UM_STUD_EXTRACT
TRF_TAKEN_NOGPA - Transfer Taken Not for GPA Num6.3 TrnTakNGPA UM_STUD_EXTRACT
TUITION_RES_TERMS - Tuition Residency Num4.3 Tuition Res. UM_STUD_EXTRACT
TUIT_CALC_DTTM - Tuit Calc Date Time DateTm TuitDTTM UM_STUD_EXTRACT
TUIT_CALC_REQ - Tuition Calc Required Char1 Calc Req UM_STUD_EXTRACT
TUITION_EXCPT - Tuition Residency Exception Char5 Tuit Excpt 
 - TUITION_EXCPT -
 SQL for some FIN_AID_ fields
select 
qb.residency 
AS um_residency_reg,
qb.tuition_res,
qb.fin_aid_fed_res,
qb.fin_aid_st_res,
qb.fin_aid_fed_excpt,
qb.fin_aid_st_excpt,
qb.tuition_excpt
FROM 
PS_UM_STUD_EXTRACT A, 
ps_residency_off qb
WHERE a.emplid = qb.emplid(+)
AND a.acad_career = qb.acad_career(+)
AND a.institution = qb.institution(+)
AND ((QB.EFFECTIVE_TERM =
(SELECT MAX(EB9.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF EB9
WHERE EB9.EMPLID = QB.EMPLID
AND EB9.ACAD_CAREER = QB.ACAD_CAREER
AND EB9.INSTITUTION = QB.INSTITUTION
AND EB9.EFFECTIVE_TERM <= A.STRM) OR
(QB.EFFECTIVE_TERM =
(SELECT MAX(EC9.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF EC9
WHERE EC9.EMPLID = QB.EMPLID
AND EC9.ACAD_CAREER = EC9.ACAD_CAREER
AND EC9.INSTITUTION = QB.INSTITUTION) 
AND NOT EXISTS
 (SELECT 'X'
FROM PS_RESIDENCY_OFF ED9
WHERE ED9.EMPLID = QB.EMPLID
AND ED9.ACAD_CAREER = QB.ACAD_CAREER
AND ED9.INSTITUTION = QB.INSTITUTION
AND ED9.EFFECTIVE_TERM <= A.STRM))) OR
QB.EFFECTIVE_TERM IS NULL)
UM_TUITION_RES - Tuition Residency Char5 Tuition Res 
 - UM_TUITION_RES -
 SQL for some FIN_AID_ fields
select 
qb.residency 
AS um_residency_reg,
qb.tuition_res,
qb.fin_aid_fed_res,
qb.fin_aid_st_res,
qb.fin_aid_fed_excpt,
qb.fin_aid_st_excpt,
qb.tuition_excpt
FROM 
PS_UM_STUD_EXTRACT A, 
ps_residency_off qb
WHERE a.emplid = qb.emplid(+)
AND a.acad_career = qb.acad_career(+)
AND a.institution = qb.institution(+)
AND ((QB.EFFECTIVE_TERM =
(SELECT MAX(EB9.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF EB9
WHERE EB9.EMPLID = QB.EMPLID
AND EB9.ACAD_CAREER = QB.ACAD_CAREER
AND EB9.INSTITUTION = QB.INSTITUTION
AND EB9.EFFECTIVE_TERM <= A.STRM) OR
(QB.EFFECTIVE_TERM =
(SELECT MAX(EC9.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF EC9
WHERE EC9.EMPLID = QB.EMPLID
AND EC9.ACAD_CAREER = EC9.ACAD_CAREER
AND EC9.INSTITUTION = QB.INSTITUTION) 
AND NOT EXISTS
 (SELECT 'X'
FROM PS_RESIDENCY_OFF ED9
WHERE ED9.EMPLID = QB.EMPLID
AND ED9.ACAD_CAREER = QB.ACAD_CAREER
AND ED9.INSTITUTION = QB.INSTITUTION
AND ED9.EFFECTIVE_TERM <= A.STRM))) OR
QB.EFFECTIVE_TERM IS NULL)
UM_ACADEMIC_YR - Academic Year Char12 Academic Year 
Calculation for UM_ACADEMIC_YR
(Alias A = UM_STUD_EXTRACT)
CASE
WHEN A.Acad_Level_Bot = '10'
 THEN 'First Year'
WHEN A.Acad_Level_Bot = '01'
 THEN 'First Year'
WHEN A.Acad_Level_Bot = '20'
 THEN 'Second Year'
WHEN A.Acad_Level_Bot = '02'
 THEN 'Second Year'
WHEN A.Acad_Level_Bot = '30'
 THEN 'Third Year'
WHEN A.Acad_Level_Bot = '03'
 THEN 'Third Year'
WHEN A.Acad_Level_Bot = '40'
 THEN 'Fourth Year'
WHEN A.Acad_Level_Bot = '04'
 THEN 'Fourth Year'
WHEN A.Acad_Level_Bot = '50'
 THEN 'Fifth Year'
WHEN A.Acad_Level_Bot = '05'
 THEN 'Fifth Year'
WHEN A.Acad_Level_Bot = 'GR'
 THEN 'Graduate'
WHEN A.Acad_Level_Bot = 'MAS'
 THEN 'Graduate'
WHEN A.Acad_Level_Bot = 'PHD'
 THEN 'Graduate'
WHEN A.Acad_Level_Bot = 'L1'
 THEN 'Law First Year'
WHEN A.Acad_Level_Bot = 'L2' THEN
 'Law Second Year'
WHEN A.Acad_Level_Bot = 'L3' THEN
 'Law Third Year'
ELSE 'Other' END
UM_ACADEMIC_YR_SRT - Academic Year Sort Char2 Academic Yr Srt 
Calculation for UM_ACADEMIC_YR_SRT
(Alias A = UM_STUD_EXTRACT)
CASE
WHEN A.ACAD_LEVEL_BOT =
 '10' THEN '10'
WHEN A.ACAD_LEVEL_BOT =
 '01' THEN '10'
WHEN A.ACAD_LEVEL_BOT =
 '20' THEN '11'
WHEN A.ACAD_LEVEL_BOT =
 '02' THEN '11'
WHEN A.ACAD_LEVEL_BOT =
 '30' THEN '12'
WHEN A.ACAD_LEVEL_BOT =
 '03' THEN '12'
WHEN A.ACAD_LEVEL_BOT =
 '40' THEN '13'
WHEN A.ACAD_LEVEL_BOT =
 '04' THEN '13'
WHEN A.ACAD_LEVEL_BOT =
 '50' THEN '14'
WHEN A.ACAD_LEVEL_BOT =
 '05' THEN '14'
WHEN A.ACAD_LEVEL_BOT =
 'GR' THEN '15'
WHEN A.ACAD_LEVEL_BOT =
 'MAS' THEN '15'
WHEN A.ACAD_LEVEL_BOT =
 'PHD' THEN '15'
WHEN A.ACAD_LEVEL_BOT =
 'L1' THEN '16'
WHEN A.ACAD_LEVEL_BOT =
 'L2' THEN '17'
WHEN A.ACAD_LEVEL_BOT =
 'L3' THEN '18'
 ELSE '20' END
UM_ACAD_GRP_DESCR - Academic Group Descr Char30 Academic Group UM_STUD_EXTRACT
UM_ACAD_LOAD_RC - Calculated Academic Load Char1 N Calc Acad Load UM_STUD_EXTRACT
UM_ACAD_LVL_DESCR - Academic Level Description Char30 Acad Level Desc UM_STUD_EXTRACT
UM_ACAD_PLAN_DESCR - Academic Plan Description Char30 Acad Plan Descr UM_STUD_EXTRACT
UM_ACAD_PROGP_DSC - Acad Prog Primary Descr Char30 Acd Prg Prm Dsc UM_STUD_EXTRACT
UM_ACAD_SUBPLN_DSC - Academic Sub Plan Description Char30 Acad Sub Descr UM_STUD_EXTRACT
UM_ADMIT_TERM_YEAR - Admit Term Year Char4 Admit Term Year 
Calculation for UM_ADMIT_TERM_YEAR
 (Alias A = UM_STUD_EXTRACT)
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 for code
UM_ADMIT_TYPE_D_ND - Admit Type Descr Non Degree Char50 Admit Type D ND

Degree/Non-Degree Coding - Current for code
UM_ADMIT_TYPE_SORT - Admit Type Sort Char2 Admit Type Sort

Degree/Non-Degree Coding - Current for code
UM_ADVISOR_NAME_FM - Advisor Name Formatted Char50 Advisor Name Formatted 
Calculation for UM_ADVISOR_NAME_FM
(Alias A = UM_STUD_EXTRACT)
CASE
WHEN A.Um_Advisr_Name_Sfx <= ' '
 AND A.Um_Advisr_Lst_Name > ' '
 THEN A.Um_Advisr_Lst_Name ||
 ', ' ||
 A.Um_Advisr_Fst_Name ||
 '' ||
A.Um_Advisr_Mid_Name
WHEN A.Um_Advisr_Lst_Name 
> ' ' THEN
A.Um_Advisr_Lst_Name ||
 '' || A.Um_Advisr_Name_Sfx
ELSE '' || ', ' |
| A.Um_Advisr_Fst_Name ||
 '' || A.Um_Advisr_Mid_Name END
UM_ADVISR_FST_NAME - Advisor First Name Char30 Advisr Fst Name UM_STUD_EXTRACT
UM_ADVISR_LST_NAME - Advisor Last Name Char30 Advisr Lst Name UM_STUD_EXTRACT
UM_ADVISR_MID_NAME - Advisor Middle Name Char30 Advisr Mid Name UM_STUD_EXTRACT
UM_ADVISR_NAME_SFX - Advisor Name Suffix Char15 Advisr Name Sfx UM_STUD_EXTRACT
UM_ANY_COUNTRY - Any Country Char50 Any Country 
*Calculation for UM_ANY_COUNTRY
 (Alias A = UM_STUD_EXTRACT)
CASE
WHEN A.Citizenship_Status = '4'
AND  (A.Country_Descr1 <> 'United States'
 AND  A.Country_Descr1 > ' ')
 THEN  A.Country_Descr1
WHEN A.Citizenship_Status = '4'
AND A.Residency = 'CAN' 
 THEN 'Canada'
WHEN A.Citizenship_Status = '4'
AND  (A.Country_Descr1 = 'United States'
 AND A.Um_Country_Oth > ' ')
 THEN  A.Um_Country_Oth
WHEN A.Citizenship_Status = '4'
AND  (A.Country <> 'USA'
 AND A.Country > ' ')
 THEN  A.Country
WHEN A.Citizenship_Status = '4'
 THEN 'Other Country'
WHEN A.Residency = 'CAN'
 THEN 'Canada'
WHEN A.Residency = 'INTNL'
AND (A.Country_Descr1 <> 'United States'
 AND  A.Country_Descr1 > ' ')
 THEN  A.Country_Descr1
WHEN A.Residency = 'INTNL'
 AND  (A.Country_Descr1 = 'United States'
  AND A.Um_Country_Oth > ' ')
 THEN  A.Um_Country_Oth
WHEN A.Residency = 'INTNL' 
AND  (A.Country <> 'USA' 
 AND A.Country > ' ') 
 THEN  A.Country
WHEN A.Residency = 'INTNL' 
 THEN 'Other Country'
WHEN A.Residency = 'OS'
AND (A.Country_Descr1 <> 'United States'
 AND  A.Country_Descr1 > ' ')
 THEN  A.Country_Descr1
WHEN A.Residency = 'OS'
AND (A.Country <> 'USA'
 AND A.Country > ' ')
 THEN  A.Country
WHEN A.Residency = 'NEBHE'
AND (A.Country_Descr1 <> 'United States'
 AND  A.Country_Descr1 > ' ')
 THEN  Country_Descr1
WHEN A.Residency = 'NEBHE'
AND  (A.Country <> 'USA'
 AND A.Country > ' ')
 THEN  A.Country
WHEN A.Country <> 'USA'
AND A.Country > ' '
 THEN  A.Country
ELSE 'United States' END
UM_CALC_COUNTRY - Calculated Country Char50 Calc Country 
Calculation for UM_CALC_COUNTRY
 (Alias A = UM_STUD_EXTRACT)
CASE
WHEN A.Citizenship_Status = '1'
 THEN 'United States'
WHEN A.Citizenship_Status = '2'
 THEN 'United States'
WHEN A.Citizenship_Status = '3'
 THEN 'United States'
WHEN A.Citizenship_Status = '4' 
  AND (A.Country_Descr1 <> 'United States' 
  AND A.Country_Descr1 > ' ') 
  THEN A.Country_Descr1
WHEN A.Citizenship_Status = '4' 
  AND A.Residency = 'CAN' 
  THEN 'Canada'
WHEN A.Citizenship_Status = '4' 
  AND (A.Country_Descr1 = 'United States' 
  AND A.Um_Country_Oth > ' ') 
  THEN A.Um_Country_Oth
WHEN A.Citizenship_Status = '4' 
  AND (A.Country <> 'USA' 
  AND A.Country > ' ') 
  THEN A.Country
WHEN A.Citizenship_Status = '4' 
  THEN 'Other Country'
WHEN A.Residency = 'CAN' 
  THEN 'Canada'
WHEN A.Residency = 'INTNL' 
  AND (A.Country_Descr1 <> 'United States' 
  AND A.Country_Descr1 > ' ') 
  THEN A.Country_Descr1
WHEN A.Residency = 'INTNL' 
  AND (A.Country_Descr1 = 'United States' 
  AND A.Um_Country_Oth > ' ') 
  THEN A.Um_Country_Oth
WHEN A.Residency = 'INTNL' 
  AND (A.Country <> 'USA' 
  AND A.Country > ' ') 
  THEN A.Country
WHEN A.Residency = 'INTNL' 
  THEN 'Other Country'
WHEN A.Residency = 'OS' 
  AND (A.Country_Descr1 <> 'United States' 
  AND A.Country_Descr1 > ' ') 
  THEN A.Country_Descr1
WHEN A.Residency = 'OS' 
  AND (A.Country <> 'USA' 
  AND A.Country > ' ') 
  THEN A.Country
WHEN A.Residency = 'NEBHE' 
  AND (A.Country_Descr1 <> 'United States' 
  AND A.Country_Descr1 > ' ') 
  THEN A.Country_Descr1
WHEN A.Residency = 'NEBHE' 
  AND (A.Country <> 'USA' 
  AND A.Country > ' ') 
  THEN A.Country
WHEN A.Country <> 'USA' 
  AND A.Country > ' ' 
  THEN A.Country 
ELSE 'United States' END
UM_CALC_COUNTY - Calculated County Char50 Calc County 
*Calculation for UM_CALC_COUNTY
 (Alias A = UM_STUD_EXTRACT)
CASE
WHEN A.County <= ' ' 
  AND A.Institution = 'UMS01' 
  THEN 'Kennebec'
WHEN A.County <= ' ' 
  AND A.Institution = 'UMS02' 
  THEN 'Franklin'
WHEN A.County <= ' ' 
  AND A.Institution = 'UMS03' 
  THEN 'Aroostook'
WHEN A.County <= ' ' 
  AND A.Institution = 'UMS04' 
  THEN 'Washington'
WHEN A.County <= ' ' 
  AND A.Institution = 'UMS05' 
  THEN 'Penobscot'
WHEN A.County <= ' ' 
  AND A.Institution = 'UMS06' 
  THEN 'Cumberland'
WHEN A.County <= ' ' 
  AND A.Institution = 'UMS07' 
  THEN 'Aroostook'
WHEN A.County <= ' ' 
  THEN 'Unknown County'
WHEN LOWER(A.County) 
  LIKE '%dros%' 
  THEN 'Androscoggin'
WHEN LOWER(A.County) 
  LIKE '%aroos%' 
  THEN 'Aroostook'
WHEN LOWER(A.County) 
  LIKE '%cumber%' 
  THEN 'Cumberland'
WHEN LOWER(A.County) 
  LIKE '%frank%' 
  THEN 'Franklin'
WHEN LOWER(A.County) 
  LIKE '%hanc%' 
  THEN 'Hancock'
WHEN LOWER(A.County) 
  LIKE '%bec' 
  THEN 'Kennebec'
WHEN LOWER(A.County) 
  LIKE '%knox' 
  THEN 'Knox'
WHEN LOWER(A.County) 
  LIKE '%lincoln' 
  THEN 'Lincoln'
WHEN LOWER(A.County) 
  LIKE '%oxford' 
  THEN 'Oxford'
WHEN LOWER(A.County) 
  LIKE '%penob%' 
  THEN 'Penobscot'
WHEN LOWER(A.County) 
  LIKE '%piscat%' 
  THEN 'Piscataquis'
WHEN LOWER(A.County) 
  LIKE '%saga%' 
  THEN 'Sagadahoc'
WHEN LOWER(A.County) 
  LIKE '%somer%' 
  THEN 'Somerset'
WHEN LOWER(A.County) 
  LIKE '%waldo' 
  THEN 'Waldo'
WHEN LOWER(A.County) 
  LIKE '%wash%' 
  THEN 'Washington'
WHEN LOWER(A.County) 
  LIKE '%york' 
  THEN 'York'
WHEN A.County 
  NOT IN (
  'Androscoggin','Aroostook',
  'Cumberland','Franklin',
  'Hancock','Kennebec',
  'Knox','Lincoln',
  'Oxford','Penobscot',
'Piscataquis','Sagadahoc',
'Somerset','Waldo',
'Washington','York') 
THEN 'Unknown County' 
ELSE A.County END
UM_CALC_STATE - Calculated State Char50 Calculate State 
Calculation for UM_CALC_STATE
 (Alias A = UM_STUD_EXTRACT)
CASE WHEN (CASE
WHEN A.Citizenship_Status = '1' 
  THEN 'United States'
WHEN A.Citizenship_Status = '2' 
  THEN 'United States'
WHEN A.Citizenship_Status = '3' 
  THEN 'United States'
WHEN A.Citizenship_Status = '4' 
  AND  (A.Country_Descr1 <> 'United States' 
  AND A.Country_Descr1 > ' ') 
  THEN  A.Country_Descr1
WHEN A.Citizenship_Status = '4' 
  AND A.Residency = 'CAN' 
  THEN 'Canada'
WHEN A.Citizenship_Status = '4' 
  AND (A.Country_Descr1 = 'United States' 
  AND A.Um_Country_Oth > ' ') 
  THEN A.Um_Country_Oth
WHEN A.Citizenship_Status = '4' 
  AND (A.Country <> 'USA' 
  AND A.Country > ' ') 
  THEN  A.Country
WHEN A.Citizenship_Status = '4' 
  THEN 'Other Country'
WHEN A.Residency = 'CAN' 
  THEN 'Canada'
WHEN A.Residency = 'INTNL' 
  AND (A.Country_Descr1 <> 'United States' 
  AND A.Country_Descr1 > ' ') 
  THEN A.Country_Descr1
WHEN A.Residency = 'INTNL' 
  AND (A.Country_Descr1 = 'United States' 
  AND A.Um_Country_Oth > ' ') 
  THEN A.Um_Country_Oth
WHEN A.Residency = 'INTNL' 
  AND (A.Country <> 'USA' 
  AND A.Country > ' ') 
  THEN  A.Country
WHEN A.Residency = 'INTNL' 
  THEN 'Other Country'
WHEN A.Residency = 'OS' 
  AND (A.Country_Descr1 <> 'United States' 
  AND A.Country_Descr1 > ' ') 
  THEN A.Country_Descr1
WHEN A.Residency = 'OS' 
  AND (A.Country <> 'USA' 
  AND A.Country > ' ') 
  THEN A.Country
WHEN A.Residency = 'NEBHE' 
  AND (A.Country_Descr1 <> 'United States' 
  AND A.Country_Descr1 > ' ') 
  THEN A.Country_Descr1
WHEN A.Residency = 'NEBHE' 
  AND (A.Country <> 'USA' 
  AND A.Country > ' ') 
  THEN A.Country
WHEN A.Country <> 'USA' 
  AND A.Country > ' ' 
  THEN A.Country 
ELSE 'United States'END) <> 'United States' 
  THEN 'Non-USA'
WHEN A.Residency = 'OS' 
  AND A.State <= ' ' 
  THEN 'Other State'
WHEN A.Residency = 'NEBHE' 
  AND A.State <= ' ' 
  THEN 'Other State'
WHEN A.State <> 'ME' 
  AND A.State_Descr1 > ' '
  THEN  A.State_Descr1
WHEN A.State = 'NH' 
  THEN 'New Hampshire'
WHEN A.State = 'VT' 
  THEN 'Vermont'
WHEN A.State = 'MA' 
  THEN 'Massachusetts'
WHEN A.State = 'CT' 
  THEN 'Connecticut'
WHEN A.State = 'RI' 
  THEN 'Rhode Island'
WHEN A.State = 'ME' 
  THEN 'Maine'
WHEN A.State <> 'ME' 
  AND A.State_Descr1 <= ' ' 
  THEN 'Other State'
WHEN A.Residency = 'IS' 
  THEN 'Maine' 
ELSE 'Other State' END
UM_CANADA - UM Canada Char50 UM Canada 
Calculation for UM_CANADA
 (Alias A = UM_STUD_EXTRACT)
CASE
WHEN (CASE
WHEN A.Citizenship_Status = '1' 
  THEN 'United States'
WHEN A.Citizenship_Status = '2' 
  THEN 'United States'
WHEN A.Citizenship_Status = '3' 
  THEN 'United States'
WHEN A.Citizenship_Status = '4' 
  AND (A.Country_Descr1 <> 'United States' 
  AND A.Country_Descr1 > ' ') 
  THEN  A.Country_Descr1
WHEN A.Citizenship_Status = '4' 
  AND A.Residency = 'CAN' 
  THEN 'Canada'
WHEN A.Citizenship_Status = '4' 
  AND (A.Country_Descr1 = 'United States' 
  AND A.Um_Country_Oth > ' ') 
  THEN  A.Um_Country_Oth
WHEN A.Citizenship_Status = '4' 
  AND (A.Country <> 'USA' 
  AND A.Country > ' ') 
  THEN  A.Country
WHEN A.Citizenship_Status = '4' 
  THEN 'Other Country'
WHEN A.Residency = 'CAN' 
  THEN 'Canada'
WHEN A.Residency = 'INTNL' 
  AND (A.Country_Descr1 <> 'United States' 
  AND A.Country_Descr1 > ' ') 
  THEN  A.Country_Descr1
WHEN A.Residency = 'INTNL' 
  AND (A.Country_Descr1 = 'United States' 
  AND A.Um_Country_Oth > ' ') 
  THEN A.Um_Country_Oth
WHEN A.Residency = 'INTNL' 
  AND (A.Country <> 'USA' 
  AND A.Country > ' ') 
  THEN A.Country
WHEN A.Residency = 'INTNL' 
  THEN 'Other Country'
WHEN A.Residency = 'OS' 
  AND (A.Country_Descr1 <> 'United States' 
  AND A.Country_Descr1 > ' ') 
  THEN A.Country_Descr1
WHEN A.Residency = 'OS' 
  AND (A.Country <> 'USA' 
  AND A.Country > ' ') 
  THEN  A.Country
WHEN A.Residency = 'NEBHE' 
  AND (A.Country_Descr1 <> 'United States' 
  AND A.Country_Descr1 > ' ') 
  THEN A.Country_Descr1
WHEN A.Residency = 'NEBHE' 
  AND (A.Country <> 'USA' 
  AND A.Country > ' ') 
  THEN  A.Country
WHEN A.Country <> 'USA' 
  AND A.Country > ' ' 
  THEN  A.Country
 ELSE 'United States' END)
 IN ('Canada', 'CAN') 
 THEN (CASE
WHEN A.State = 'AB' 
  THEN 'Alberta'
WHEN A.State = 'BC' 
  THEN 'British Columbia'
WHEN A.State = 'MB' 
  THEN 'Manitoba'
WHEN A.State = 'NB'
   THEN 'New Brunswick'
WHEN A.State = 'NF' 
  THEN 'Newfoundland'
WHEN A.State = 'NL' 
  THEN 'Newfoundland'
WHEN A.State = 'NS' 
  THEN 'Nova Scotia'
WHEN A.State = 'NT' 
  THEN 'Northwest Territories'
WHEN A.State = 'NN' 
  THEN 'Nunavut'
WHEN A.State = 'NU' 
  THEN 'Nunavut'
WHEN A.State = 'ON' 
  THEN 'Ontario'
WHEN A.State = 'PE' 
  THEN 'Prince Edward Island'
WHEN A.State = 'QC' 
  THEN 'Quebec'
WHEN A.State = 'SK' 
  THEN 'Saskatchewan'
WHEN A.State = 'YT' 
  THEN 'Yukon' 
    ELSE 'Canada' END )
      ELSE 'Not Canada' END
UM_CITIZEN_STAT_OT - Citizenship Status Other Char1 Citizn Stat Oth

UM_STUD_EXTRACT 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_EXTRACT)
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_EXTRACT This field is unreliable for students with more than 1 foreign country listed in the CITIZENSHIP table.
UM_COUNTRY_STATUS - Country Status Char15 Country Status 
Calculation for UM_COUNTRY_STATUS
 (Alias A = UM_STUD_EXTRACT
CASE
WHEN A.Citizenship_Status = '1' 
  THEN 'USA'
WHEN A.Citizenship_Status = '2' 
  THEN 'USA'
WHEN A.Citizenship_Status = '3' 
  THEN 'USA'
WHEN A.Citizenship_Status = '4' 
  AND (A.Country_Descr1 <> 'United States' 
  AND A.Country_Descr1 > ' ') 
  THEN 'Non-USA'
WHEN A.Citizenship_Status = '4' 
  AND A.Residency = 'CAN' 
  THEN 'Canada'
WHEN A.Citizenship_Status = '4' 
  AND (A.Country_Descr1 = 'United States' 
  AND A.Um_Country_Oth > ' ') 
  THEN 'Non-USA'
WHEN A.Citizenship_Status = '4' 
  AND (A.Country <> 'USA' 
  AND A.Country > ' ') 
  THEN 'Non-USA'
WHEN A.Citizenship_Status = '4' 
  THEN 'Other Country'
WHEN A.Residency = 'CAN' 
  THEN 'Non-USA'
WHEN A.Residency = 'INTNL' 
  AND (A.Country_Descr1 <> 'United States' 
  AND A.Country_Descr1 > ' ') 
  THEN 'Non-USA'
WHEN A.Residency = 'INTNL' 
  AND (A.Country_Descr1 = 'United States' 
  AND A.Um_Country_Oth > ' ') 
  THEN 'Non-USA'
WHEN A.Residency = 'INTNL' 
  AND (A.Country <> 'USA' 
  AND A.Country > ' ') 
  THEN 'Non-USA'
WHEN A.Residency = 'INTNL' 
  THEN 'Non-USA'
WHEN A.Residency = 'OS' 
  AND (A.Country_Descr1 <> 'United States' 
  AND A.Country_Descr1 > ' ') 
  THEN 'Non-USA'
WHEN A.Residency = 'OS' 
  AND (A.Country <> 'USA' 
  AND A.Country > ' ') 
  THEN 'Non-USA'
WHEN A.Residency = 'NEBHE' 
  AND (A.Country_Descr1 <> 'United States' 
  AND A.Country_Descr1 > ' ') 
  THEN 'Non-USA'
WHEN A.Residency = 'NEBHE' 
  AND (A.Country <> 'USA' 
  AND A.Country > ' ') 
  THEN 'Non-USA'
WHEN A.Country <> 'USA' 
  AND A.Country > ' ' 
  THEN 'Non-USA' 
ELSE 'USA' END
UM_COUNTRY_USA - Country is USA Char3 Country USA UM_STUD_EXTRACT
UM_CURRENT_AGE - Current Age Num4.1 Current Age
Calculation for UM_CURRENT_AGE
 (Alias A = UM_STUD_EXTRACT)
TRUNC
((SYSDATE - A.BIRTHDATE) 
/ 365.25)
UM_CURRENT_AGE_RNG - Current Age Range Char15 Curr Age Range 
Calculation for UM_CURRENT_AGE_RNG
(Alias A = UM_STUD_EXTRACT)
CASE
WHEN TRUNC
((SYSDATE - A.BIRTHDATE) / 365.25) >= 14 
  AND TRUNC
((SYSDATE - A.BIRTHDATE) / 365.25) <= 17 
  THEN 'Under 18'
WHEN TRUNC
((SYSDATE - A.BIRTHDATE) / 365.25) >= 18
  AND TRUNC
((SYSDATE - A.BIRTHDATE) / 365.25) <= 19 
  THEN '18 - 19'
WHEN TRUNC
((SYSDATE - A.BIRTHDATE) / 365.25) >= 20 
  AND TRUNC
((SYSDATE - A.BIRTHDATE) / 365.25) <= 21 
  THEN '20 - 21'
WHEN TRUNC
((SYSDATE - A.BIRTHDATE) / 365.25) >= 22 
  AND TRUNC
((SYSDATE - A.BIRTHDATE) / 365.25) <= 24 
  THEN '22 - 24'
WHEN TRUNC
((SYSDATE - A.BIRTHDATE) / 365.25) >= 25 
  AND TRUNC
((SYSDATE - A.BIRTHDATE) / 365.25) <= 29 
  THEN '25 - 29'
WHEN TRUNC
((SYSDATE - A.BIRTHDATE) / 365.25) >= 30 
  AND TRUNC
((SYSDATE - A.BIRTHDATE) / 365.25) <= 34 
  THEN '30 - 34'
WHEN TRUNC
((SYSDATE - A.BIRTHDATE) / 365.25) >= 35 
  AND TRUNC
((SYSDATE - A.BIRTHDATE) / 365.25) <= 39
   THEN '35 - 39'
WHEN TRUNC
((SYSDATE - A.BIRTHDATE) / 365.25) >= 40 
  AND TRUNC
((SYSDATE - A.BIRTHDATE) / 365.25) <= 49 
  THEN '40 - 49'
WHEN TRUNC
((SYSDATE - A.BIRTHDATE) / 365.25) >= 50 
  AND TRUNC
((SYSDATE - A.BIRTHDATE) / 365.25) <= 64 
  THEN '50 - 64'
WHEN TRUNC
((SYSDATE - A.BIRTHDATE) / 365.25) >= 65 
  AND TRUNC
((SYSDATE - A.BIRTHDATE) / 365.25) <= 105 
  THEN '65 and Over'
ELSE 'Unknown' END
UM_CURRNT_AGE_RSRT - Current Age Range Sort Char2 Current Age RSRT 
Calculation for UM_CURRNT_AGE_RSRT
(Alias A = UM_STUD_EXTRACT)
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 Degree/Non-Degree Coding - Current for the code
UM_EMAIL_HOM_ADDR - Email Home Address Char70 Email Home Addr UM_STUD_EXTRACT
UM_ETHNIC_DESCR - Ethnic Description Char50 Ethic Descr UM_STUD_EXTRACT
UM_ETHNIC_GRP_DESC - Ethnic Groupd Description Char30 Ethnic Grp Desc Calculation for UM_ETHNIC_GRP_DESC field
UM_ETHNIC_GRP_SORT - Ethnic Group Sort Char2 Ethnic Grp Srt 
Calculation for UM_ETHNIC_GRP_SORT
 (Alias A = UM_STUD_EXTRACT)
CASE
WHEN A.Citizenship_Status = '4' 
THEN '7'
WHEN A.Ethnic_Group = '3' 
THEN '3'
WHEN A.Hisp_Latino = 'Y' 
THEN '3'
WHEN A.Um_Multiple_Ethnic = 'Y' 
THEN '8'
WHEN A.Ethnic_Group = '1' 
THEN '1'
WHEN A.Ethnic_Group = '2' 
THEN '2'
WHEN A.Ethnic_Group = '4' 
THEN '4'
WHEN A.Ethnic_Group = '5' 
THEN '5'
WHEN A.Ethnic_Group = '7' 
THEN '6'
WHEN A.Ethnic_Group = '6' 
THEN '9'
ELSE '9' END
UM_ETH_GRP_DESCR - Ethnic Group Descr Char30 Eth Grp Descr UM_STUD_EXTRACT
UM_EXTERNAL_ORG - External Organization Char30 External Org Degree/Non-Degree Coding - Current for code
UM_EXTERNL_ORG_DTL - External Org Detail Char30 Externl Org Dtl Degree/Non-Degree Coding - Current for code
UM_EXTERNL_ORG_SUM - External Organization Summary Char30 Externl Org Sum

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

Although this field says "Fees Paid," the values are actually charges calculated from the CHARGES tree per Darren Michael, 10/19/09.
UM_STUD_EXTRACT
UM_FIRST_GEN - First Gen Char1 First Gen Calculation for First Generation
UM_FIRST_TERM - First Term Char1 First Term 
Calculation for UM_FIRST_TERM
 (Alias A = UM_STUD_EXTRACT)
CASE
WHEN A.Admit_Term = A.Strm 
OR (A.STRM - CASE
WHEN A.Admit_Term = ' ' 
THEN '0000'
ELSEa.admit_term END) = 80 
THEN 'Y' ELSE 'N' END 
UM_FIRST_TIME - First Time Char1 First Time Degree/Non-Degree Coding - Current for code
UM_FIRST_TIME_NDEG - First Time Non-Degree Char1 First Time NonD

Degree/Non-Degree Coding - Current for code
UM_FTE_DIVISOR - FTE Divisor Num3.0 FTE Divisor 
SQL for UM_FTE_DIVISOR
 (alias A= UM_STUD_EXTRACT)
CASE 
WHEN A.Institution = 'UMS02' 
AND A.Acad_plan = 'PSY-MA' 
THEN 15
WHEN A.Acad_Level_Bot 
IN ('GR', 'MAS', 'PHD') 
THEN 9
WHEN A.Acad_Level_Bot 
IN ('L1', 'L2', 'L3') 
THEN 15
WHEN A.Institution = 'UMS02' 
AND STRM < '2410' THEN 16
ELSE 15 END 
UM_FULL_PART - Full-time Part-Time Char10

Full Part 

 
SQL for UM_FULL_PART
 (alias A= UM_STUD_EXTRACT table)
CASE
WHEN A.Acad_Level_Bot = 'GR' 
AND A.Um_Unt_Tak_Prg_Rc >= 6 
THEN 'Full-Time'
WHEN A.Acad_Level_Bot = 'MAS' 
AND A.Um_Unt_Tak_Prg_Rc >= 6 
THEN 'Full-Time'
WHEN A.Acad_Level_Bot = 'PHD' 
AND A.Um_Unt_Tak_Prg_Rc >= 6 
THEN 'Full-Time'
WHEN A.Um_Unt_Tak_Prg_Rc >= 12 
THEN 'Full-Time' 
ELSE 'Part-Time' END 
UM_GENDER - Gender Char10 Gender
Calculation for UM_GENDER
(Alias A= UM_STUD_EXTRACT)
CASE WHEN A.Sex = 'M' 
THEN 'Male' ELSE 'Female'END
UM_GRAD_DATE_YEAR - Graduation Date Year Char4 Grad Date Year
Calculation for UM_GRAD_DATE_YEAR
(Alias A= UM_STUD_EXTRACT)
TO_NUMBER(TO_CHAR
(A.Graduation_Dt,
'YYYY'), '9999')
UM_INST_SHORT - Institution Short Char5 Inst Short 
Calculation for UM_INST_SHORT
 (Alias A = UM_STUD_EXTRACT)
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_EXTRACT
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_EXTRACT
UM_LAST_SCH_TYPE - Last School Attended Type Char3 Last School Typ UM_STUD_EXTRACT
UM_MAINE_COUNTY - Maine County Char1 Maine County 
Calculation for UM_MAINE_COUNTY
 (Alias A = UM_STUD_EXTRACT)
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_STUD_EXTRACT Limited to last 5 years
UM_MAX_ACT_ENGL - Maximum ACT English Num5.2 Max ACT Englis

UM_STUD_EXTRACT Limited to last 5 years
UM_MAX_ACT_MATH - Maximum ACT Math Num5.2 Max ACT Math UM_STUD_EXTRACT Limited to last 5 years
UM_MAX_GRE_QUAN - Maximum Quantitative GRE Num5.2 Max GRE QUAN UM_STUD_EXTRACT
UM_MAX_GRE_VERB - Maximum GRE Verbal Num5.2 Max GRE VERB UM_STUD_EXTRACT
UM_MAX_SAT_ERWS - Max SAT ERWS Num5.2 Max SAT ERWS UM_STUD_EXTRACT
UM_MAX_SAT_MATH - Maximum Sat Math Num5.2 Max Sat Math UM_STUD_EXTRACT Limited to last 5 years
UM_MAX_SAT_MSS - Max SAT MSS Num5.2 Max SAT MSS UM_STUD_EXTRACT Limited to last 5 years
UM_MAX_SAT_VERB - Maximum Sat Verbal Num5.2 Max Sat Verbal

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

Degree/Non-Degree Coding - Current for code
UM_NEASC_LEVEL_SRT - NEASC Level Sort Char2 NEASC Level Srt

Degree/Non-Degree Coding - Current for code
UM_NID_CAN - National ID Canada Char20 NID Canada UM_STUD_EXTRACT
UM_NID_USA - National Id USA Char20 NID USA UM_STUD_EXTRACT
UM_NURSING_FLAG - Nursing Flag Char1 Nursing Flag
Calculation for UM_NURSING_FLAG
 (Alias A = UM_STUD_EXTRACT)
CASE
WHEN A.Acad_Plan 
LIKE 'NUR%' THEN 'Y'
WHEN A.Acad_Prog 
LIKE 'NUR%' THEN 'Y'
ELSE 'N' END
UM_ORIG_ACAD_PLAN - ORIGINAL_ACAD_PLAN Char10 ORIG_ACAD_PLAN UM_STUD_EXTRACT
UM_ORIG_ACAD_PROG - ORIGINAL_ACAD_PROG Char5 ORIG_ACAD_PROG UM_STUD_EXTRACT
UM_PREVIOUS_NAME - Previous Name Char50 Prev Name UM_STUD_EXTRACT
UM_RESIDENCY_DESCR - Residency Description Char30 Residency Descr 
Calculation for UM_RESIDENCY_DESCR
 (Alias A = UM_STUD_EXTRACT)
DECODE(A.Residency,
'IS','In-State',
'OS','Out-of-State',
'CAN','Canadian',
'NEBHE','NEBHE',
'INTNL','International',
'ONLIN','Online',
'MEONL','Academic Partnerships'
'Out-of-State'  )
UM_RESIDENCY_SORT - Residency Description Sort Char2 Residency Sort 
Calculation for UM_RESIDENCY_SORT
 (Alias A = UM_STUD_EXTRACT)
DECODE(A.Residency,
'IS', '10',
'OS', '20',
'CAN', '40',
'NEBHE', '30',
'INTNL', '50',
'ONLIN', '60',
'MEONL', '70'
 '20')
UM_RSP_REASN_DESCR - Response Reason Descriptions Char30 Resp Reason Des UM_STUD_EXTRACT
UM_STD_LVL_SUM_SRT - Student Level Summary Sort Char2 Std Lvl Sum Srt

Please go to Degree/Non-Degree Coding - Current for the code
UM_STD_LVL_SUM_UG - Student Level Sum UG Grad Char30 Std Lvl Sum UG

Please go to Degree/Non-Degree Coding - Current for the code
UM_STRM_YEAR - Strm Year Char4 Strm Year 
 Calculation for UM_STRM_YEAR
  (Alias A = UM_STUD_EXTRACT)
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

Degree/Non-Degree Coding - Current for code
UM_STUDENT_NAME - Student Char50 Student 
Calculation for UM_STUDENT_NAME
 (Alias A = UM_STUD_EXTRACT)
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 for code
UM_STUD_LEVEL_SUM - Student Level Summary Char30 Stud Level Sum

Degree/Non-Degree Coding - Current for code
UM_TUITION_COUNTRY - Tuition Country Char50 Tuition Coun 
Calculation for UM_TUITION_COUNTRY
 (Alias A = UM_STUD_EXTRACT)
CASE
WHEN A.Residency = 'IS' 
THEN 'USA'
WHEN A.Citizenship_Status = '4' 
AND A.Um_Country_Oth > ' ' 
THEN A.Um_Country_Oth
WHEN A.Citizenship_Status = '4' 
AND (A.Country <> 'USA' 
AND A.Country > ' ') 
THEN A.Country
WHEN A.Citizenship_Status = '4' 
AND A.Residency = 'CAN' 
THEN 'CAN'
WHEN A.Citizenship_Status = '4' 
THEN 'OTH' 
ELSE 'USA' END
UM_TUITION_COUNTY - Tuition County Char50 Tuition County 
Calculation for UM_TUITION_COUNTY
 (Alias A = UM_STUD_EXTRACT)
CASE
WHEN A.Residency = 'IS' 
AND LOWER(A.County) 
LIKE '%dros%' THEN 'Androscoggin'
WHEN A.Residency = 'IS' 
AND LOWER(A.County) 
LIKE '%aroos%' THEN 'Aroostook'
WHEN A.Residency = 'IS' 
AND LOWER(A.County) 
LIKE '%cumber%' THEN 'Cumberland'
WHEN A.Residency = 'IS' 
AND LOWER(A.County) 
LIKE '%frank%' THEN 'Franklin'
WHEN A.Residency = 'IS' 
AND LOWER(A.County) 
LIKE '%hanc%' THEN 'Hancock'
WHEN A.Residency = 'IS' 
AND LOWER(A.County) 
LIKE '%bec' THEN 'Kennebec'
WHEN A.Residency = 'IS' 
AND LOWER(A.County) 
LIKE '%knox' THEN 'Knox'
WHEN A.Residency = 'IS' 
AND LOWER(A.County) 
LIKE '%lincoln' THEN 'Lincoln'
WHEN A.Residency = 'IS' 
AND LOWER(A.County) 
LIKE '%oxford' THEN 'Oxford'
WHEN A.Residency = 'IS' 
AND LOWER(A.County) 
LIKE '%penob%' THEN 'Penobscot'
WHEN A.Residency = 'IS' 
AND LOWER(A.County) 
LIKE '%piscat%' THEN 'Piscataquis'
WHEN A.Residency = 'IS' 
AND LOWER(A.County) 
LIKE '%saga%' THEN 'Sagadahoc'
WHEN A.Residency = 'IS' 
AND LOWER(A.County) 
LIKE '%somer%' THEN 'Somerset'
WHEN A.Residency = 'IS' 
AND LOWER(A.County) 
LIKE '%waldo' THEN 'Waldo'
WHEN A.Residency = 'IS' 
AND LOWER(A.County) 
LIKE '%wash%' THEN 'Washington'
WHEN A.Residency = 'IS' 
AND LOWER(A.County) 
LIKE '%york' THEN 'York'
WHEN A.Residency = 'IS' 
AND A.Institution = 'UMS01'
THEN 'Kennebec'
WHEN A.Residency = 'IS' 
AND A.Institution = 'UMS02' 
THEN 'Franklin'
WHEN A.Residency = 'IS' 
AND A.Institution = 'UMS03' 
THEN 'Aroostook'
WHEN A.Residency = 'IS' 
AND A.Institution = 'UMS04' 
THEN 'Washington'
WHEN A.Residency = 'IS' 
AND A.Institution = 'UMS05' 
THEN 'Penobscot'
WHEN A.Residency = 'IS' 
AND A.Institution = 'UMS06' 
THEN 'Cumberland'
WHEN A.Residency = 'IS' 
AND A.Institution = 'UMS07' 
THEN 'Aroostook'
ELSE 'Non-Maine'END
UM_TUITION_PAID - Tuition Paid* SNm8.0 Tuition Paid

*Although this field says "Tuition Paid," the values are actually charges calculated from the CHARGES tree per Darren Michael, 10/19/09. 
UM_STUD_EXTRACT
UM_TUITION_STATE - Tuition State Char50 Tuition State 
Calculation for UM_TUITION_STATE
 (Alias A = UM_STUD_EXTRACT)
CASE
WHEN A.Residency = 'IS' THEN 'Maine'
WHEN A.State = 'AB' 
  AND A.Citizenship_Status = '4' 
  AND A.Residency = 'CAN' 
  THEN 'Alberta'
WHEN A.State = 'BC' 
  AND A.Citizenship_Status = '4' 
  AND A.Residency = 'CAN' 
  THEN 'British Columbia'
WHEN A.State = 'MB' 
  AND A.Citizenship_Status = '4' 
  AND A.Residency = 'CAN' 
  THEN 'Manitoba'
WHEN A.State = 'NB' 
  AND A.Citizenship_Status = '4' 
  AND A.Residency = 'CAN'
   THEN 'New Brunswick'
WHEN A.State = 'NF' 
  AND A.Citizenship_Status = '4' 
  AND A.Residency = 'CAN' 
  THEN 'Newfoundland'
WHEN A.State = 'NL' 
  AND A.Citizenship_Status = '4' 
  AND A.Residency = 'CAN' 
  THEN 'Newfoundland'
WHEN A.State = 'NS' 
  AND A.Citizenship_Status = '4' 
  AND A.Residency = 'CAN' 
  THEN 'Nova Scotia'
WHEN A.State = 'NT' 
  AND A.Citizenship_Status = '4' 
  AND A.Residency = 'CAN' 
  THEN 'Northwest Territories'
WHEN A.State = 'NN' 
  AND A.Citizenship_Status = '4' 
  AND A.Residency = 'CAN' 
  THEN 'Nunavut'
WHEN A.State = 'NU' 
  AND A.Citizenship_Status = '4' 
  AND A.Residency = 'CAN' 
  THEN 'Nunavut'
WHEN A.State = 'ON' 
  AND A.Citizenship_Status = '4' 
  AND A.Residency = 'CAN' 
  THEN 'Ontario'
WHEN A.State = 'PE' 
  AND A.Citizenship_Status = '4' 
  AND A.Residency = 'CAN' 
  THEN 'Prince Edward Island'
WHEN A.State = 'QC' 
  AND A.Citizenship_Status = '4' 
  AND A.Residency = 'CAN' 
  THEN 'Quebec'
WHEN A.State = 'SK' 
  AND A.Citizenship_Status = '4' 
  AND A.Residency = 'CAN' 
  THEN 'Saskatchewan'
WHEN A.State = 'YT' 
  AND A.Citizenship_Status = '4' 
  AND A.Residency = 'CAN' 
  THEN 'Yukon'
WHEN A.Citizenship_Status = '4' 
  AND A.Residency = 'CAN' 
  THEN 'Canada'
WHEN A.Citizenship_Status 
  = '4' THEN 'Non-USA'
WHEN A.State <> 'ME' 
  AND A.State_Descr1 > ' ' 
  THEN A.State_Descr1
WHEN A.State = 'NH' 
  THEN 'New Hampshire'
WHEN A.State = 'VT' 
  THEN 'Vermont'
WHEN A.State = 'MA' 
  THEN 'Massachusetts'
WHEN A.State = 'CT' 
  THEN 'Connecticut'
WHEN A.State = 'RI' 
  THEN 'Rhode Island'
ELSE 'Other State' END
UM_UMS01_FLG - Ums01 Registered Flag Char1 Ums01 Flag UM_STUD_EXTRACT
UM_UMS01_UNITS - UMS01 Units Num6.3 UMS01 Units UM_STUD_EXTRACT
UM_UMS02_FLG - UMS02 Registered Flag Char1 UMS02 Flag UM_STUD_EXTRACT
UM_UMS02_UNITS - UMS02 Units Num6.3 UMS02 Units UM_STUD_EXTRACT
UM_UMS03_FLG - Ums03 Registered Flag Char1 Ums03 Flag UM_STUD_EXTRACT
UM_UMS03_UNITS - UMS03 Units Num6.3 Ums03 Units UM_STUD_EXTRACT
UM_UMS04_FLG - UMS04 Registered Flag Char1 UMS04 Flag UM_STUD_EXTRACT
UM_UMS04_UNITS - UMS04 Units Num6.3 UMS04 Units UM_STUD_EXTRACT
UM_UMS05_FLG - Ums05 Registered Flag Char1 Ums05 Flag UM_STUD_EXTRACT
UM_UMS05_UNITS - UMS05 Units Num6.3 UMS05 Units UM_STUD_EXTRACT
UM_UMS06_FLG - Ums06 Registered Flag Char1 Ums06 Flag UM_STUD_EXTRACT
UM_UMS06_UNITS - UMS06 Units Num6.3 UMS06 Units UM_STUD_EXTRACT
UM_UMS07_FLG - Ums07 Registered Flag Char1 Ums07 Flag UM_STUD_EXTRACT
UM_UMS07_UNITS - UMS07 Units Num6.3 UMS07 Units UM_STUD_EXTRACT
UM_UNT_DIST - Units Distance Num10.3 Units Distance UM_STUD_EXTRACT
UM_UNT_ITV - Itv Receive Units Num10.3 Itv Receive Unt UM_STUD_EXTRACT
UM_UNT_TAK_CAREER - Units Taken For Career Num6.3 Unt Takn Career UM_STUD_EXTRACT
UM_UNT_TAK_PRG_RC - Units Taken Progress Row Check Num6.3 Units Taken Prg UM_STUD_EXTRACT
UM_UNT_WEB - Web Units Num10.3 Web Units UM_STUD_EXTRACT
UNIT_MULTIPLIER - Unit Multiplier Num5.4 Multiplier UM_STUD_EXTRACT
UNTPRG_CHG_NSLC_DT - Unit Progrss Changed Date NSC Date Unit ChgDt NSC UM_STUD_EXTRACT
UNT_AUDIT - Units Audited Num6.3 Audit UM_STUD_EXTRACT
UNT_INPROG_GPA - Units In Progress - GPA Num6.3 InPr GPA UM_STUD_EXTRACT
UNT_INPROG_NOGPA - Unit In Progress - Not for GPA Num6.3 InPr NoGPA UM_STUD_EXTRACT
UNT_OTHER - Units from Other Credit Num6.3 Other UM_STUD_EXTRACT
UNT_PASSD_FA - Fin Aid Progress Units Passed Num6.3 FA Passed UM_STUD_EXTRACT
UNT_PASSD_GPA - Units Passed Toward GPA Num6.3 Pass GPA UM_STUD_EXTRACT
UNT_PASSD_NOGPA - Units Passed Not Toward GPA Num6.3 PassNoGPA UM_STUD_EXTRACT
UNT_PASSD_PRGRSS - Units Passed for Progress Num6.3 Pass Prgrs UM_STUD_EXTRACT
UNT_TAKEN_FA - Fin Aid Progress Units Taken Num6.3 FA Taken UM_STUD_EXTRACT
UNT_TAKEN_FA_GPA - FA Units Taken Toward GPA Num6.3 FA Units T UM_STUD_EXTRACT
UNT_TAKEN_GPA - Units Taken Toward GPA Num6.3 Take GPA UM_STUD_EXTRACT
UNT_TAKEN_NOGPA - Units Taken Not Toward GPA Num6.3 TakeNoGPA UM_STUD_EXTRACT
UNT_TAKEN_PRGRSS - Units Taken for Progress Num6.3 Take Prgrs UM_STUD_EXTRACT
UNT_TERM_TOT - Total Term Units Num6.3 Total UM_STUD_EXTRACT
UNT_TEST_CREDIT - Units from Test Credit Num6.3 Test UM_STUD_EXTRACT
UNT_TRNSFR - Units Transferred Num6.3 Transfer UM_STUD_EXTRACT
VALIDATED - Election Validated Char1 Validated UM_STUD_EXTRACT
VALIDATED_DT - Date Data Validated Date Date Validated UM_STUD_EXTRACT
WITHDRAW_CODE - Withdrawal \ Cancel Char3 N Wdraw Code UM_STUD_EXTRACT
WITHDRAW_DATE - Withdrawal \ Cancel Date Date Wdraw Date UM_STUD_EXTRACT
WITHDRAW_REASON - Withdrawal \ Cancel Reason Char4 N WithdrlRsn UM_STUD_EXTRACT

Details

Details

Article ID: 158914
Created
Fri 5/10/24 4:28 AM
Modified
Fri 8/23/24 4:01 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_EXT_VW.txt Computer

Tue 8/20/24 9:46 AM
View
;