UM_STUD_EXTRACT - Student Reporting Extract

Extracted table of student information by institution, term, and student. This is the base extract for all other UM_STUD tables: 

Detailed Information 

This table was created by the University of Maine System - UMS to aid in student reporting by combining information from many tables, thus decreasing the number of joins and calculations required to retrieve the information you need.  It also contains calculated fields that can make reporting much easier, however it is still very large, so using a view is most often preferable. This table is refreshed daily except for Sundays and it continues to capture changes to students until 3 months after the term end date..

Selecting the linked (blue) fields in any table will take you to the Data Dictionary and provide you with more information about that field as well as field values if they are available.

Data Systems

• PeopleSoft Campus Solutions Production CSPRD
• PeopleSoft Campus Solutions Reporting CSRPT

View SQR Build Code

Download SQR Build Code

Tables Referenced

Code that builds the student extract explained. This will serve as a road map to recreating the extract as well as document the meaning of the fields
that make up the UM_STUD_EXTRACT table. The file is .txt. Open with Notepad or a similar tool to retain formatting.. If you have issues with
access, please contact DARTS@Maine.edu        
                                                               

UM_ACAD_LOAD_RC 
  Calculated Academic Load
Case 
when STDNT_ENRL.acad_career 
<> 'NCRD' 
  and STDNT_ENRL.UNT_PRGRSS 
>= 12 then 'F'
when STDNT_ENRL.acad_career 
<> 'NCRD' 
and STDNT_CAR_TERM.acad_level_bot 
in ('GR' , 'MAS', 'PHD') 
and STDNT_ENRL.UNT_PRGRSS 
>= 6 then 'F' else  'P' end

Procedure:Get_NID
select
SB4.NATIONAL_ID,
SB4.COUNTRY
if &SB4.COUNTRY = 'USA' 
and &SB4.NATIONAL_ID > ' '
Let $Nid_Usa 
= &SB4.NATIONAL_ID end-if
if &SB4.COUNTRY = 'CAN' 
and &SB4.NATIONAL_ID > ' '
Let $Nid_Can 
= &SB4.NATIONAL_ID end-if
FROM PS_PERS_NID SB4

Procedure: Get_NID
select
SB4.NATIONAL_ID,
SB4.COUNTRY
if &SB4.COUNTRY = 'USA' 
and &SB4.NATIONAL_ID > ' '
Let $Nid_Usa 
= &SB4.NATIONAL_ID end-if
if &SB4.COUNTRY 
= 'CAN' 
and &SB4.NATIONAL_ID > ' '
Let $Nid_Can 
= &SB4.NATIONAL_ID end-if
FROM PS_PERS_NID SB4

Procedure: 
Get_Previous_Name
select
SB1.Name
if &SB1.Name > ' ' 
Let $Previous_Name 
= &SB1.Name end-if
FROM PS_NAMES SB1
WHERE  
SB1.NAME_TYPE = 'FR1'
and SB1.EMPLID 
= &STA.EMPLID
 and SB1.EFFDT =
 (SELECT MAX
 (SB1_ED.EFFDT) 
 FROM PS_NAMES SB1_ED
 WHERE 
 SB1.EMPLID 
 = SB1_ED.EMPLID
 AND 
 SB1.NAME_TYPE 
 = SB1_ED.NAME_TYPE
 AND SB1_ED.EFFDT