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.

Databases

• PeopleSoft/ MaineStreet Campus Solutions - CSPRD
• PeopleSoft/ MaineStreet Campus Solutions Reporting - CSRPT

Build Code

• The Code behind UM_STUD_EXTRACT page explains how each field was built.    
• Click here to jump to the Tables Referenced section
• For developers, the SQR name is UMSTUEXT.SQR.                                                  

Fields

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