Created by Corina C Larsen, last modified by Miki Yanagi on Oct 12, 2021
View of each student enrolled in each class by institution and term. It is the view of UM_D_STD_ENRL table which is not available in Query Manager since this view (UM_D_STD_ENR_VW) contains the same fields as the UM_D_STD_ENRL table. Most data is from STDNT_ENRL - Student Enrollment Table and CLASS_TBL - Class Table.
Currently, the data is refreshed daily from STRM=1930 and forward in CSRPT.
This view also contains the UM_INST_HOST field used for Cross-listed Course Reporting.
| Key | Record.Fieldname | Format | XLAT | Heading Text | Technical Notes |
|---|---|---|---|---|---|
| → | ACAD_CAREER - Academic Career | Char4 | Career | ||
| ACAD_GROUP - Academic Group | Char5 | Acad Group | |||
| ACAD_ORG - Academic Organization | Char10 | Acad Org | |||
| ACAD_PROG - Academic Program | Char5 | Acad Prog | |||
| ASSOCIATED_CLASS - Associated Class | Num4.0 | Assoc | |||
| ASSOCIATION_99 - Association 99 | Char1 | Assoc 99 | |||
| AUDIT_GRADE_BASIS - Audit Grading Basis | Char1 | Audit | |||
| CAMPUS - Campus | Char5 | Campus | |||
| CATALOG_NBR - Catalog Nbr | Char10 | Catalog | CATALOG_NBR field values begin with a space (eg. ' 101'), most likely to insert a space between SUBJECT and CATALOG_NBR. (eg. 'ENG 101'). | ||
| → | CLASS_NBR - Class Nbr | Num5.0 | Class Nbr | ||
| → | CLASS_SECTION - Class Section | Char4 | Section | ||
| CLASS_STAT - Class Status | Char1 | N | Class Stat | ||
| CLASS_TYPE - Class Type | Char1 | N | Class Type | ||
| CRSE_CAREER - Course Career | Char4 | Course Car | |||
| CRSE_COUNT - Course Count | Num3.2 | Crse Count | |||
| CRSE_GRADE_INPUT - Grade Input | Char3 | Grade In | |||
| CRSE_GRADE_OFF - Official Grade | Char3 | Grade | |||
| → | CRSE_ID - Course ID | Char6 | Course ID | ||
| → | CRSE_OFFER_NBR - Course Offering Nbr | Num2.0 | Offer Nbr | ||
| DESCR - Description | Char30 | Descr | |||
| DESCR_LOCATION - Location Description | Char30 | Description | CASE WHEN C.EFF_STATUS = 'A' THEN C.DESCR ELSE C.DESCR||'-Inactive' END (C is LOCATION_TBL) | ||
| DROP_CLASS_IF_ENRL - Drop This Class if Enrolled | Num5.0 | Drop if Enroll | |||
| DYN_CLASS_NBR - Dynamic Class Nbr | Num5.0 | Dyn Class Nbr | |||
| EARN_CREDIT - Earn Credit | Char1 | EarnCredit | |||
| → | EMPLID - Empl ID | Char11 | ID | This is student's EMPLID from STDNT_ENRL. | |
| ENRL_ACTION_LAST - Last Enrollment Action | Char2 | Last Act | |||
| ENRL_ACTN_PRC_LAST - Last Enrollment Action Process | Char2 | Process | |||
| ENRL_ACTN_RSN_LAST - Last Enrl Action Reason | Char4 | Reason | |||
| ENRL_ADD_DT - Enrollment Add Date | Date | Add Dt | |||
| ENRL_CAP - Enrollment Capacity | Num4.0 | Cap Enrl | |||
| ENRL_DROP_DT - Enrollment Drop Date | Date | Drop Dt | |||
| ENRL_REQ_SOURCE - Enrollment Request Source | Char2 | N | Source | ||
| ENRL_STAT - Enrollment Status | Char1 | N | Enrl Stat | When students dropped after add/drop date, the status remains "E". Look at ENRL_DROP_DT and ENRL_STATUS_REASON. | |
| ENRL_STATUS_REASON - Enrollment Status Reason | Char4 | N | Reason | ||
| FIRST_NAME - First Name | Char30 | First Name | This is instructor's name from PS_PERSONAL_DATA where the EMPLID on PS_PERSONAL_DATA = INSTRUCTOR_ID. | ||
| GRADE_CATEGORY - Grade Category | Char4 | N | Grade Category | ||
| GRADE_DT - Grade Date | Date | Grade Dt | |||
| GRADE_POINTS - Grade Points | Num7.3 | Grd Points | |||
| GRADE_POINTS_FA - Financial Aid Grade Points | Num7.3 | FA Grd Pt | |||
| GRADING_BASIS_DT - Grading Basis Date | Date | Basis Dt | |||
| GRADING_BASIS_ENRL - Grading Basis | Char3 | Grade Base | |||
| GRADING_SCHEME_ENR - Field is not active yet | Char3 | Field not activ | |||
| GRD_PTS_PER_UNIT - Grade Points Per Unit | Num7.3 | Grd Pt/Unt | |||
| INCLUDE_IN_GPA - Include in GPA | Char1 | Incl GPA | |||
| → | INSTITUTION - Academic Institution | Char5 | Institution | ||
| INSTRUCTION_MODE - Instruction Mode | Char2 | Mode | |||
| INSTRUCTOR_ID - Instructor ID | Char11 | Instructor | EMPLID from PS_CLASS_INSTR table where MIN(INSTR_ROLE || INSTR_ASSIGN_SEQ) . | ||
| LAST_DROP_DT_STMP - Last Drop Date Stamp | Date | Last Drop Date | |||
| LAST_DROP_TM_STMP - Last Drop Time Stamp | Time | Last Drop Time | |||
| LAST_ENRL_DT_STMP - Last Enrollment Date Stamp | Date | Last Enrl Date | |||
| LAST_ENRL_TM_STMP - Last Enrollment Time Stamp | Time | Last Enrl Time | |||
| LAST_NAME - Last Name | Char30 | Last | This is instructor's name from PS_PERSONAL_DATA where the EMPLID on PS_PERSONAL_DATA = INSTRUCTOR_ID. | ||
| LAST_UPD_DT_STMP - Last Update Date Stamp | Date | Last Upd Date | |||
| LAST_UPD_ENREQ_SRC - Enrollment Request Source | Char2 | N | Source | ||
| LAST_UPD_TM_STMP - Last Update Time Stamp | Time | Last Upd Time | |||
| LOCATION - Location Code | Char10 | Location | |||
| MANDATORY_GRD_BAS - Mandatory Grading Basis | Char1 | Mandatory | |||
| MIDDLE_NAME - Middle Name | Char30 | Middle | This is instructor's name from PS_PERSONAL_DATA where the EMPLID on PS_PERSONAL_DATA = INSTRUCTOR_ID. | ||
| NAME_PREFIX - Name Prefix | Char4 | Prefix | This is instructor's name from PS_PERSONAL_DATA where the EMPLID on PS_PERSONAL_DATA = INSTRUCTOR_ID. | ||
| NAME_SUFFIX - Name Suffix | Char15 | Suffix | This is instructor's name from PS_PERSONAL_DATA where the EMPLID on PS_PERSONAL_DATA = INSTRUCTOR_ID. | ||
| NOTIFY_STDNT_CHNG - Notify Student of Change | Char2 | N | Notify Student | ||
| OVRD_GRADING_BASIS - Override Grading Basis | Char1 | Grading Basis | |||
| RELATE_CLASS_NBR_1 - Field is not active yet | Num5.0 | Field not activ | |||
| RELATE_CLASS_NBR_2 - Field is not active yet | Num5.0 | Field not activ | |||
| REPEAT_CANDIDATE - Repeat Candidate Flag | Char1 | Rpt Candidat | |||
| REPEAT_CODE - Repeat Code | Char4 | Repeat | |||
| REPEAT_DT - Repeat Date | Date | Repeat Dt | |||
| SCTN_COMBINED_ID - Combined Sections ID | Char4 | Comb Sects ID | from SCTN_CMBND table | ||
| SEL_GROUP - Tuition Group | Char10 | Group | |||
| SESSION_CODE - Session | Char3 | N | Session | ||
| SSR_COMPONENT - Course Component | Char3 | N | Component | The translation is on psxlatitem. (fieldname ='SSR_COMPONENT') | |
| STATUS_DT - Status Date | Date | Stat Dt | |||
| STDNT_ENRL_STATUS - Student Enrollment Status | Char2 | N | Status | ||
| STDNT_SPEC_PERM - Student Specific Permissions | Char1 | Stdnt Spec | |||
| → | STRM - Term | Char4 | Term | ||
| SUBJECT - Subject Area | Char8 | Subject | |||
| TSCRPT_NOTE_EXISTS - Transcript Note Exists Flag | Char1 | Tscrpt Ex | |||
| TSCRPT_NOTE_ID - Transcript Note ID | Char4 | Note ID | |||
| UM_ACAD_ORG_DESCR - Academic Organization Descript | Char30 | Acad Org Dsecr | CASE WHEN F.EFF_STATUS = 'A' THEN F.DESCR ELSE F.DESCR||'-Inactive' END (F is ACAD_ORG_TBL) | ||
| UM_CENTER_CD - Center Code | Char4 | Center | Please see Distance Education Course Coding - Current page for the code | ||
| UM_CENTER_DESCR - Center Description | Char25 | Center Descr | from UM_CENTER_TBL. Please see Distance Education Course Coding - Current page for the code | ||
| UM_CLASS_DLV_MODE - Class Delivery Mode | Char30 | Class Dlv Mode | Please see Distance Education Course Coding - Current page for the code | ||
| UM_CLASS_LEVEL - Class Level | Char1 | Class Level | |||
| UM_DISTANCE_FLAG - Distance Flag | Char2 | Distance Flag | from UM_D_STD_ENRL. Please see Distance Education Course Coding - Current page for the code | ||
| UM_DIST_ATTR_FLAG - Distance Attribute Flag | Char2 | N | Dist Attr Flag | Please see Distance Education Course Coding - Current page for the code | |
| UM_DIST_ATTR_F_DSC - Dist Attr Flag Description | Char30 | Dist Attr F Dsc | Please see Distance Education Course Coding - Current page for the code | ||
| UM_DIST_CLASS - Distance Class | Char10 | Distance Class | Please see Distance Education Course Coding - Current page for the code | ||
| UM_DIST_FLG_DESCR - Distribution Flag Description | Char30 | Dist Flg Descr | Please see Distance Education Course Coding - Current page for the code | ||
| UM_IMODE - Imode | Char10 | Imode | Please see Distance Education Course Coding - Current page for the code | ||
| UM_IMODE_DESCR - Instruction Mode Description | Char30 | IMODE Descr | |||
| UM_INSTRUCTOR_NAME - Instructor Name | Char50 | Instructor Name | |||
UM_INST_HOST - UM Host Institution (Added on September 2019) | Char5 | Host Inst | Created for reporting cross-listed course. Click here for the definitions, fields, and field values. | ||
| UM_INST_SHORT - Institution Short | Char5 | Inst Short | |||
| UM_IPEDS_DIST_CLSS - IPEDS Distribution Class | Char30 | IPEDS Dist Clss | Please see Distance Education Course Coding - Current page for the code | ||
| UM_LOCATION_DESCR - Location Description | Char30 | Location Descr | Please see Distance Education Course Coding - Current page for the code | ||
| UM_REGION - Region | Char30 | Region | Please see Distance Education Course Coding - Current page for the code | ||
| UM_SITE_CATEGORY - Site Category | Char20 | Site Category | Please see Distance Education Course Coding - Current page for the code | ||
| UM_STDNT_ENRL_STAT - Student Enroll Stat Decoded | Char30 | Stud Enr Stat | |||
| UM_UNIV_CENTER_DSC - University Center Description | Char30 | Univ Center Dsc | Please see Distance Education Course Coding - Current page for the code | ||
| UM_UNIV_CENTER_FLG - University Center Flag | Char1 | Univ Center Flg | Please see Distance Education Course Coding - Current page for the code | ||
| UNITS_ACAD_PROG - Academic Progress Units | Num4.2 | Prgrss Unt | from CRSE_CATALOG table | ||
| UNITS_ATTEMPTED - Units Attempted | Char1 | N | Units Att | ||
| UNITS_MAXIMUM - Maximum Units | Num4.2 | Max Units | from CRSE_CATALOG table | ||
| UNITS_MINIMUM - Minimum Units | Num4.2 | Min Units | from CRSE_CATALOG table | ||
| UNT_BILLING - Billing Units | Num4.2 | Bill Units | |||
| UNT_EARNED - Units Earned | Num4.2 | Units | |||
| UNT_PRGRSS - Units Taken-Academic Progress | Num4.2 | Progress | |||
| UNT_PRGRSS_FA - Units Taken-Fin Aid Progress | Num4.2 | FA Progress | |||
| UNT_TAKEN - Units Taken | SNm5.2 | Unit Taken | |||
| VALID_ATTEMPT - Valid Attempted Grade | Char1 | Valid Attempt |
Data Dictionary Links
Clicking 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.
Reporting Tip
Use the following criteria with this table to define an active student:
Stdnt Enrl Status = 'E'
AND (Earn Credit = 'Y'
OR (Units Attempted <> 'N' AND Audit Grade Basis = 'N'))
AND Enrl Drop Dt is null
AND Acad Career <> 'NCRD'
AND Unt Prgrss > 0.00
AND Strm =
AND Institution =
SQL for UM_D_STD_ENR_VW Expand source
WITH MORBO AS
(SELECT A.EMPLID,
A.ACAD_CAREER,
A.INSTITUTION,
A.STRM,
A.CLASS_NBR,
A.CRSE_CAREER,
A.SESSION_CODE,
A.STDNT_ENRL_STATUS,
A.ENRL_STATUS_REASON,
A.ENRL_ACTION_LAST,
A.ENRL_ACTN_RSN_LAST,
A.ENRL_ACTN_PRC_LAST,
A.STATUS_DT,
A.ENRL_ADD_DT,
A.ENRL_DROP_DT,
A.UNT_TAKEN,
A.UNT_PRGRSS,
A.UNT_PRGRSS_FA,
A.UNT_BILLING,
A.CRSE_COUNT,
A.GRADING_BASIS_ENRL,
A.GRADING_BASIS_DT,
A.OVRD_GRADING_BASIS,
A.CRSE_GRADE_OFF,
A.CRSE_GRADE_INPUT,
A.GRADE_DT,
A.REPEAT_CODE,
A.REPEAT_DT,
A.ASSOCIATED_CLASS,
A.AUDIT_GRADE_BASIS,
A.EARN_CREDIT,
A.INCLUDE_IN_GPA,
A.UNITS_ATTEMPTED,
A.GRADE_POINTS,
A.GRADE_POINTS_FA,
A.GRD_PTS_PER_UNIT,
A.MANDATORY_GRD_BAS,
A.INSTRUCTOR_ID,
A.DROP_CLASS_IF_ENRL,
A.ASSOCIATION_99,
A.TSCRPT_NOTE_ID,
A.TSCRPT_NOTE_EXISTS,
A.NOTIFY_STDNT_CHNG,
A.REPEAT_CANDIDATE,
A.VALID_ATTEMPT,
A.GRADE_CATEGORY,
A.SEL_GROUP,
A.DYN_CLASS_NBR,
A.UNT_EARNED,
A.LAST_UPD_DT_STMP,
A.LAST_UPD_TM_STMP,
A.LAST_ENRL_DT_STMP,
A.LAST_ENRL_TM_STMP,
A.LAST_DROP_DT_STMP,
A.LAST_DROP_TM_STMP,
A.ENRL_REQ_SOURCE,
A.LAST_UPD_ENREQ_SRC,
A.GRADING_SCHEME_ENR,
A.RELATE_CLASS_NBR_1,
A.RELATE_CLASS_NBR_2,
A.ACAD_PROG,
A.CRSE_ID,
A.CRSE_OFFER_NBR,
A.CLASS_SECTION,
A.ACAD_GROUP,
A.SUBJECT,
A.CATALOG_NBR,
A.DESCR,
A.SSR_COMPONENT,
A.ENRL_STAT,
A.CLASS_STAT,
A.CLASS_TYPE,
A.STDNT_SPEC_PERM,
A.CAMPUS,
A.LOCATION,
A.INSTRUCTION_MODE,
A.DESCR_LOCATION,
A.UM_DISTANCE_FLAG,
A.UM_CENTER_CD,
A.UM_CENTER_DESCR,
A.LAST_NAME,
A.FIRST_NAME,
A.MIDDLE_NAME,
A.NAME_PREFIX,
A.NAME_SUFFIX,
A.UM_DIST_ATTR_FLAG,
A.UM_IMODE,
A.ENRL_CAP,
A.ACAD_ORG,
A.UM_ACAD_ORG_DESCR,
A.SCTN_COMBINED_ID,
A.UNITS_MINIMUM,
A.UNITS_MAXIMUM,
A.UNITS_ACAD_PROG,
DECODE(A.INSTITUTION,
'UMS01',
'UMA',
'UMS02',
'UMF',
'UMS03',
'UMFK',
'UMS04',
'UMM',
'UMS07',
'UMPI',
'UMS05',
'UM',
'UMS06',
'USM',
'Other') INSTITUTION_SHORT,
CASE
WHEN A.STRM >= '2130' THEN
(CASE
WHEN A.INSTRUCTION_MODE = 'AR' THEN
'By Arrangement'
WHEN A.INSTRUCTION_MODE = 'DS' THEN
'Distance Synchronous Learning'
WHEN A.INSTRUCTION_MODE = 'HB' THEN
'Hybrid/Blended'
WHEN A.INSTRUCTION_MODE = 'HF' THEN
'Hyflex'
WHEN A.INSTRUCTION_MODE = 'P' THEN
'In-Person'
WHEN A.INSTRUCTION_MODE = 'RE' THEN
'Receive'
WHEN A.INSTRUCTION_MODE = 'SD' THEN
'Split Delivery'
WHEN A.INSTRUCTION_MODE = 'SL' THEN
'Service Learning'
WHEN A.INSTRUCTION_MODE = 'WW' THEN
'Online'
ELSE
'Unknown'
END)
WHEN A.STRM >= '2110' AND A.INSTITUTION = 'UMS07' THEN
(CASE
WHEN A.INSTRUCTION_MODE = 'P' THEN
'On-Site'
WHEN A.INSTRUCTION_MODE = 'BL' THEN
'Blended'
WHEN A.INSTRUCTION_MODE = 'OL' THEN
'Online Low Residency'
WHEN A.INSTRUCTION_MODE = 'OS' THEN
'Online Synchronous'
WHEN A.INSTRUCTION_MODE = 'OA' THEN
'Online Asynchronous'
WHEN A.INSTRUCTION_MODE = 'HF' THEN
'Hyflex'
ELSE
'Unknown'
END)
WHEN A.UM_DIST_ATTR_FLAG = 'Y' AND A.UM_DISTANCE_FLAG = 'R' AND
A.UM_IMODE = 'ITV' THEN
'Distance ITV'
WHEN A.UM_DIST_ATTR_FLAG = 'Y' AND A.UM_DISTANCE_FLAG = 'R' AND
A.UM_IMODE = 'VIDEOCONF' THEN
'Distance Video Conference'
WHEN A.UM_DIST_ATTR_FLAG = 'Y' AND A.UM_DISTANCE_FLAG = 'B' AND
A.UM_IMODE IN ('ITV', 'VIDEOCONF') AND (CASE
WHEN A.CAMPUS IN ('BATH',
'BBC',
'ELL',
'EML',
'FHC',
'HOU',
'HOULT',
'PEN',
'ROC',
'RUM',
'SAC',
'SACO',
'SOP') THEN
'Y'
WHEN A.LOCATION IN ('V01190',
'V01490',
'V01440',
'V01150',
'V01690',
'V01390',
'V01945',
'V01874',
'V01950',
'V01972') THEN
'Y'
ELSE
'N'
END) = 'Y' THEN
'Distance Onsite'
WHEN A.UM_DIST_ATTR_FLAG = 'Y' AND A.UM_IMODE = 'ONLINE' THEN
'Distance Online'
WHEN A.UM_DIST_ATTR_FLAG = 'Y' AND A.UM_IMODE = 'ONSITE' THEN
'Distance Onsite'
WHEN A.INSTRUCTION_MODE IN ('RE', 'TV', 'TW') THEN
'Distance ITV'
WHEN A.INSTRUCTION_MODE IN ('VC', 'VD') THEN
'Distance Video Conference'
WHEN A.INSTRUCTION_MODE IN ('VS', 'WB', 'WS') THEN
'Distance Online'
WHEN A.LOCATION = 'V02035' THEN
'Distance Online'
WHEN (CASE
WHEN A.CAMPUS IN ('BATH',
'BBC',
'ELL',
'EML',
'FHC',
'HOU',
'HOULT',
'PEN',
'ROC',
'RUM',
'SAC',
'SACO',
'SOP') THEN
'Y'
WHEN A.LOCATION IN ('V01190',
'V01490',
'V01440',
'V01150',
'V01690',
'V01390',
'V01945',
'V01874',
'V01950',
'V01972') THEN
'Y'
ELSE
'N'
END) = 'Y' THEN
'Distance Onsite'
ELSE
'Traditional Class '
END CLASS_DELIVERY_MODE,
CASE
WHEN A.CATALOG_NBR = ' ' THEN
0
WHEN SUBSTR(A.CATALOG_NBR, 1, 1) <> ' ' THEN
TO_NUMBER(SUBSTR(A.CATALOG_NBR, 1, 1))
WHEN SUBSTR(A.CATALOG_NBR, 2, 3) < '100' THEN
0
ELSE
TO_NUMBER(SUBSTR(A.CATALOG_NBR, 2, 1))
END CLASS_LEVEL,
CASE
WHEN A.UM_DIST_ATTR_FLAG = 'Y' THEN
'Distance Course'
ELSE
'Traditional Campus Course'
END DIST_ATTR_FLG_DESCR,
CASE
WHEN A.STRM >= '2130' AND
A.INSTRUCTION_MODE IN ('AR', 'HF', 'P', 'SD', 'SL') THEN
'NOT DIST'
WHEN A.STRM >= '2130' AND
A.INSTRUCTION_MODE IN ('DS', 'HB', 'RE', 'WW') THEN
'OTHER DIST'
WHEN A.STRM >= '2110' AND A.INSTITUTION = 'UMS07' AND
A.INSTRUCTION_MODE = 'P' THEN
'NOT DIST'
WHEN A.STRM >= '2110' AND A.INSTITUTION = 'UMS07' AND
A.INSTRUCTION_MODE IN ('BL', 'HF', 'OA', 'OL', 'OS') THEN
'OTHER DIST'
WHEN A.UM_DIST_ATTR_FLAG = 'Y' AND A.UM_DISTANCE_FLAG = 'R' AND
A.UM_IMODE IN ('ITV', 'VIDEOCONF') THEN
'UC DIST'
WHEN A.UM_DIST_ATTR_FLAG = 'Y' AND A.UM_DISTANCE_FLAG = 'B' AND
A.UM_IMODE IN ('ITV', 'VIDEOCONF') AND (CASE
WHEN A.CAMPUS IN ('BATH',
'BBC',
'ELL',
'EML',
'FHC',
'HOU',
'HOULT',
'PEN',
'ROC',
'RUM',
'SAC',
'SACO',
'SOP') THEN
'Y'
WHEN A.LOCATION IN ('V01190',
'V01490',
'V01440',
'V01150',
'V01690',
'V01390',
'V01945',
'V01874',
'V01950',
'V01972') THEN
'Y'
ELSE
'N'
END) = 'Y' THEN
'UC DIST'
WHEN A.UM_DIST_ATTR_FLAG = 'Y' AND
A.UM_IMODE IN ('ONLINE', 'ONSITE') THEN
'UC DIST'
WHEN A.INSTRUCTION_MODE IN
('RE', 'TV', 'TW', 'VC', 'VD', 'VS', 'WB', 'WS') THEN
'OTHER DIST'
WHEN A.LOCATION = 'V02035' THEN
'OTHER DIST'
WHEN (CASE
WHEN A.CAMPUS IN ('BATH',
'BBC',
'ELL',
'EML',
'FHC',
'HOU',
'HOULT',
'PEN',
'ROC',
'RUM',
'SAC',
'SACO',
'SOP') THEN
'Y'
WHEN A.LOCATION IN ('V01190',
'V01490',
'V01440',
'V01150',
'V01690',
'V01390',
'V01945',
'V01874',
'V01950',
'V01972') THEN
'Y'
ELSE
'N'
END) = 'Y' THEN
'OTHER DIST'
ELSE
'NOT DIST'
END DIST_CLASS,
DECODE(A.UM_DISTANCE_FLAG, 'B', 'Broadcast', 'R', 'Receive', ' ') DIST_FLG_DESCR,
DECODE(A.INSTRUCTION_MODE,
'BL',
'Blended',
'BR',
'Broadcast Class',
'OA',
'Online Asynchronous',
'OL',
'Online Low Residency',
'OS',
'Online Synchronous',
'RE',
'Receive Class',
'TR',
'Travel Class',
'TV',
'ITV Class',
'TW',
'ITV/Online Class',
'VC',
'Video Conference Class',
'VD',
'Video Tape Class',
'VS',
'Video Stream Class',
'WB',
'Online Class',
'AR',
'By Arrangement',
'DS',
'Distance Synchronous Learning',
'HB',
'Hybrid/Blended',
'HF',
'Hyflex',
'P',
'In-Person',
'RE',
'Receive',
'SD',
'Split Delivery',
'SL',
'Service Learning',
'WW',
'Online',
'WS',
'Web-Synchronous',
'Other') INSTRUCTION_MODE_DESCR,
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 INSTRUCTOR_NAME,
CASE
WHEN A.STRM >= '2130' AND
A.INSTRUCTION_MODE IN ('AR', 'HF', 'P', 'SD', 'SL') THEN
'NOT DIST'
WHEN A.STRM >= '2130' AND
A.INSTRUCTION_MODE IN ('DS', 'HB', 'RE', 'WW') THEN
'OTHER DIST'
WHEN A.STRM >= '2110' AND A.INSTITUTION = 'UMS07' AND
A.INSTRUCTION_MODE = 'P' THEN
'NOT DIST'
WHEN A.STRM >= '2110' AND A.INSTITUTION = 'UMS07' AND
A.INSTRUCTION_MODE IN ('BL', 'HF', 'OA', 'OL', 'OS') THEN
'OTHER DIST'
WHEN A.UM_DIST_ATTR_FLAG = 'Y' AND A.UM_DISTANCE_FLAG = 'R' AND
A.UM_IMODE = 'ITV' THEN
'UC DIST'
WHEN A.UM_DIST_ATTR_FLAG = 'Y' AND A.UM_DISTANCE_FLAG = 'R' AND
A.UM_IMODE = 'VIDEOCONF' THEN
'UC DIST'
WHEN A.UM_DIST_ATTR_FLAG = 'Y' AND A.UM_IMODE = 'ONLINE' THEN
'UC DIST'
WHEN A.INSTRUCTION_MODE IN
('RE', 'TV', 'TW', 'VC', 'VD', 'VS', 'WB', 'WS') THEN
'OTHER DIST'
WHEN A.LOCATION = 'V02035' THEN
'OTHER DIST'
ELSE
'NOT DIST'
END IPEDS_DIST_CLASS,
DECODE(A.STDNT_ENRL_STATUS,
'A',
'All',
'D',
'Dropped',
'E',
'Enrolled',
'W',
'Waiting',
'Other') UM_STDNT_ENRL_STAT,
CASE
WHEN A.CAMPUS = 'BATH' THEN
'Bath/Brunswick UC Center'
WHEN A.CAMPUS = 'BBC' THEN
'Bath/Brunswick UC Center'
WHEN A.CAMPUS = 'ELL' THEN
'Ellsworth UC Center'
WHEN A.CAMPUS = 'EML' THEN
'East Millinocket UC Center'
WHEN A.CAMPUS = 'FHC' THEN
'Hutchinson Center - Belfast'
WHEN A.CAMPUS = 'HOU' THEN
'Houlton UC Center'
WHEN A.CAMPUS = 'HOULT' THEN
'Houlton UC Center'
WHEN A.CAMPUS = 'PEN' THEN
'Penquis /Dover-Foxcroft UC Center'
WHEN A.CAMPUS = 'ROC' THEN
'Rockland UC Center'
WHEN A.CAMPUS = 'RUM' THEN
'Rumford/Mexico UC Center'
WHEN A.CAMPUS = 'SAC' THEN
'Saco UC Center'
WHEN A.CAMPUS = 'SACO' THEN
'Saco UC Center'
WHEN A.CAMPUS = 'SOP' THEN
'Norway/South Paris UC Center'
WHEN A.LOCATION = 'V01190' THEN
'Bath/Brunswick UC Center'
WHEN A.LOCATION = 'V01490' THEN
'Ellsworth UC Center'
WHEN A.LOCATION = 'V01440' THEN
'East Millinocket UC Center'
WHEN A.LOCATION = 'V01150' THEN
'Hutchinson Center - Belfast'
WHEN A.LOCATION = 'V01690' THEN
'Houlton UC Center'
WHEN A.LOCATION = 'V01390' THEN
'Penquis /Dover-Foxcroft UC Center'
WHEN A.LOCATION = 'V01945' THEN
'Rockland UC Center'
WHEN A.LOCATION = 'V01874' THEN
'Rumford/Mexico UC Center'
WHEN A.LOCATION = 'V01950' THEN
'Saco UC Center'
WHEN A.LOCATION = 'V01972' THEN
'Norway/South Paris UC Center'
ELSE
'Other'
END UNIV_CENTER_DESC,
CASE
WHEN A.CAMPUS = 'BATH' THEN
'Y'
WHEN A.CAMPUS = 'BBC' THEN
'Y'
WHEN A.CAMPUS = 'ELL' THEN
'Y'
WHEN A.CAMPUS = 'EML' THEN
'Y'
WHEN A.CAMPUS = 'FHC' THEN
'Y'
WHEN A.CAMPUS = 'HOU' THEN
'Y'
WHEN A.CAMPUS = 'HOULT' THEN
'Y'
WHEN A.CAMPUS = 'PEN' THEN
'Y'
WHEN A.CAMPUS = 'ROC' THEN
'Y'
WHEN A.CAMPUS = 'RUM' THEN
'Y'
WHEN A.CAMPUS = 'SAC' THEN
'Y'
WHEN A.CAMPUS = 'SACO' THEN
'Y'
WHEN A.CAMPUS = 'SOP' THEN
'Y'
WHEN A.LOCATION = 'V01190' THEN
'Y'
WHEN A.LOCATION = 'V01490' THEN
'Y'
WHEN A.LOCATION = 'V01440' THEN
'Y'
WHEN A.LOCATION = 'V01150' THEN
'Y'
WHEN A.LOCATION = 'V01690' THEN
'Y'
WHEN A.LOCATION = 'V01390' THEN
'Y'
WHEN A.LOCATION = 'V01945' THEN
'Y'
WHEN A.LOCATION = 'V01874' THEN
'Y'
WHEN A.LOCATION = 'V01950' THEN
'Y'
WHEN A.LOCATION = 'V01972' THEN
'Y'
ELSE
'N'
END UNIV_CENTER_FLAG
FROM SYSADM.PS_UM_D_STD_ENRL A)
SELECT MOB.EMPLID,
MOB.ACAD_CAREER,
MOB.INSTITUTION,
MOB.STRM,
MOB.CLASS_NBR,
MOB.CRSE_CAREER,
MOB.SESSION_CODE,
MOB.STDNT_ENRL_STATUS,
MOB.ENRL_STATUS_REASON,
MOB.ENRL_ACTION_LAST,
MOB.ENRL_ACTN_RSN_LAST,
MOB.ENRL_ACTN_PRC_LAST,
MOB.STATUS_DT,
MOB.ENRL_ADD_DT,
MOB.ENRL_DROP_DT,
MOB.UNT_TAKEN,
MOB.UNT_PRGRSS,
MOB.UNT_PRGRSS_FA,
MOB.UNT_BILLING,
MOB.CRSE_COUNT,
MOB.GRADING_BASIS_ENRL,
MOB.GRADING_BASIS_DT,
MOB.OVRD_GRADING_BASIS,
MOB.CRSE_GRADE_OFF,
MOB.CRSE_GRADE_INPUT,
MOB.GRADE_DT,
MOB.REPEAT_CODE,
MOB.REPEAT_DT,
MOB.ASSOCIATED_CLASS,
MOB.AUDIT_GRADE_BASIS,
MOB.EARN_CREDIT,
MOB.INCLUDE_IN_GPA,
MOB.UNITS_ATTEMPTED,
MOB.GRADE_POINTS,
MOB.GRADE_POINTS_FA,
MOB.GRD_PTS_PER_UNIT,
MOB.MANDATORY_GRD_BAS,
MOB.INSTRUCTOR_ID,
MOB.DROP_CLASS_IF_ENRL,
MOB.ASSOCIATION_99,
MOB.TSCRPT_NOTE_ID,
MOB.TSCRPT_NOTE_EXISTS,
MOB.NOTIFY_STDNT_CHNG,
MOB.REPEAT_CANDIDATE,
MOB.VALID_ATTEMPT,
MOB.GRADE_CATEGORY,
MOB.SEL_GROUP,
MOB.DYN_CLASS_NBR,
MOB.UNT_EARNED,
MOB.LAST_UPD_DT_STMP,
MOB.LAST_UPD_TM_STMP,
MOB.LAST_ENRL_DT_STMP,
MOB.LAST_ENRL_TM_STMP,
MOB.LAST_DROP_DT_STMP,
MOB.LAST_DROP_TM_STMP,
MOB.ENRL_REQ_SOURCE,
MOB.LAST_UPD_ENREQ_SRC,
MOB.GRADING_SCHEME_ENR,
MOB.RELATE_CLASS_NBR_1,
MOB.RELATE_CLASS_NBR_2,
MOB.ACAD_PROG,
MOB.CRSE_ID,
MOB.CRSE_OFFER_NBR,
MOB.CLASS_SECTION,
MOB.ACAD_GROUP,
MOB.SUBJECT,
MOB.CATALOG_NBR,
MOB.DESCR,
MOB.SSR_COMPONENT,
MOB.ENRL_STAT,
MOB.CLASS_STAT,
MOB.CLASS_TYPE,
MOB.STDNT_SPEC_PERM,
MOB.CAMPUS,
MOB.LOCATION,
MOB.INSTRUCTION_MODE,
MOB.DESCR_LOCATION,
MOB.UM_DISTANCE_FLAG,
MOB.UM_CENTER_CD,
MOB.UM_CENTER_DESCR,
MOB.LAST_NAME,
MOB.FIRST_NAME,
MOB.MIDDLE_NAME,
MOB.NAME_PREFIX,
MOB.NAME_SUFFIX,
MOB.UM_DIST_ATTR_FLAG,
MOB.UM_IMODE,
MOB.ENRL_CAP,
MOB.ACAD_ORG,
MOB.UM_ACAD_ORG_DESCR,
MOB.SCTN_COMBINED_ID,
MOB.UNITS_MINIMUM,
MOB.UNITS_MAXIMUM,
MOB.UNITS_ACAD_PROG,
MOB.INSTITUTION_SHORT,
MOB.CLASS_DELIVERY_MODE,
MOB.CLASS_LEVEL,
MOB.DIST_ATTR_FLG_DESCR,
MOB.DIST_CLASS,
MOB.DIST_FLG_DESCR,
MOB.INSTRUCTION_MODE_DESCR,
MOB.INSTRUCTOR_NAME,
MOB.IPEDS_DIST_CLASS,
CASE
WHEN MOB.CLASS_DELIVERY_MODE = 'Distance Online' THEN
'Distance Online'
WHEN MOB.LOCATION = 'V01190' THEN
'Bath/Brunswick UC Center'
WHEN MOB.LOCATION = 'V01525' THEN
'Falmouth - Baxter School'
WHEN MOB.LOCATION = 'V01820' THEN
'Lisbon Falls'
WHEN MOB.LOCATION = 'V02047' THEN
'Windham/Maine Correctional Center'
WHEN MOB.LOCATION = 'V02060' THEN
'Wiscasset'
WHEN MOB.LOCATION = 'V01440' THEN
'East Millinocket UC Center'
WHEN MOB.LOCATION = 'V01800' THEN
'Lincoln'
WHEN MOB.LOCATION = 'V01970' THEN
'Sherman Station'
WHEN MOB.LOCATION = 'V01490' THEN
'Ellsworth UC Center'
WHEN MOB.LOCATION = 'V01070' THEN
'Bangor - BMHI'
WHEN MOB.LOCATION = 'V01210' THEN
'Bucksport'
WHEN MOB.LOCATION = 'V01360' THEN
'Deer Isle'
WHEN MOB.LOCATION = 'V01450' THEN
'East Sullivan'
WHEN MOB.LOCATION = 'V01740' THEN
'Isle au Haut'
WHEN MOB.LOCATION = 'V01900' THEN
'Mount Desert'
WHEN MOB.LOCATION = 'V01980' THEN
'Swans Island'
WHEN MOB.LOCATION = 'V01690' THEN
'Houlton UC Center'
WHEN MOB.LOCATION = 'V01700' THEN
'Houlton'
WHEN MOB.LOCATION = 'V01250' THEN
'Caribou'
WHEN MOB.LOCATION = 'V01340' THEN
'Danforth'
WHEN MOB.LOCATION = 'V01540' THEN
'Fort Fairfield'
WHEN MOB.LOCATION = 'V01730' THEN
'Island Falls'
WHEN MOB.LOCATION = 'V01972' THEN
'Norway/South Paris UC Center'
WHEN MOB.LOCATION = 'V01160' THEN
'Bethel'
WHEN MOB.LOCATION = 'V01390' THEN
'Penquis/Dover-Foxcroft UC Center'
WHEN MOB.LOCATION = 'V01380' THEN
'Dexter'
WHEN MOB.LOCATION = 'V01640' THEN
'Greenville'
WHEN MOB.LOCATION = 'V01650' THEN
'Guilford'
WHEN MOB.LOCATION = 'V01670' THEN
'Hermon'
WHEN MOB.LOCATION = 'V01760' THEN
'Jackman'
WHEN MOB.LOCATION = 'V01910' THEN
'Newport'
WHEN MOB.LOCATION = 'V01945' THEN
'Rockland UC Center'
WHEN MOB.LOCATION = 'V01230' THEN
'Camden'
WHEN MOB.LOCATION = 'V01750' THEN
'Islesboro'
WHEN MOB.LOCATION = 'V01902' THEN
'Lincoln Academy (Newcastle)'
WHEN MOB.LOCATION = 'V01895' THEN
'Monhegan Island'
WHEN MOB.LOCATION = 'V02020' THEN
'Maine State Prison'
WHEN MOB.LOCATION = 'V02020' THEN
'Maine State Prison'
WHEN MOB.LOCATION = 'V01920 ' THEN
'North Haven'
WHEN MOB.LOCATION = 'V01982' THEN
'Maine State Prison'
WHEN MOB.LOCATION = 'V02010' THEN
'Vinalhaven'
WHEN MOB.LOCATION = 'V01874' THEN
'Rumford/Mexico UC Center'
WHEN MOB.LOCATION = 'V01170' THEN
'Bingham'
WHEN MOB.LOCATION = 'V01780' THEN
'Kingfield'
WHEN MOB.LOCATION = 'V01830' THEN
'Livermore Falls'
WHEN MOB.LOCATION = 'V01535' THEN
'Farmington - Mt. Blue'
WHEN MOB.LOCATION = 'V01940' THEN
'Rangeley'
WHEN MOB.LOCATION = 'V01950' THEN
'Saco UC Center'
WHEN MOB.LOCATION = 'V01954' THEN
'Sanford High School'
WHEN MOB.LOCATION = 'V01951' THEN
'Sanford UC Center'
WHEN MOB.LOCATION = 'V01955' THEN
'Scarborough'
WHEN MOB.LOCATION = 'V01973' THEN
'South Berwick'
WHEN MOB.LOCATION = 'V01928' THEN
'Orono UM Campus'
WHEN MOB.LOCATION = 'V01150' THEN
'Hutchinson Center - Belfast'
WHEN MOB.LOCATION = 'V01035' THEN
'Augusta UMA Campus'
WHEN MOB.LOCATION = 'V01075' THEN
'Bangor UMA Campus'
WHEN MOB.LOCATION = 'V01520' THEN
'Fairfield'
WHEN MOB.LOCATION = 'V01037' THEN
'Maine State Library'
WHEN MOB.LOCATION = 'V01040' THEN
'Riverview Psychiatric'
WHEN MOB.LOCATION = 'V01971' THEN
'Skowhegan'
WHEN MOB.LOCATION = 'V01990' THEN
'Thorndike'
WHEN MOB.LOCATION = 'V02050' THEN
'Winthrop'
WHEN MOB.LOCATION = 'V01530' THEN
'Farmington UMF Campus'
WHEN MOB.LOCATION = 'V02081' THEN
'Farmington UMF Campus'
WHEN MOB.LOCATION = 'V09998' THEN
'Farmington UMF Campus'
WHEN MOB.LOCATION = 'V01545' THEN
'Fort Kent UMFK Campus'
WHEN MOB.LOCATION = 'V02085' THEN
'Fort Kent UMFK Campus'
WHEN MOB.LOCATION = 'V01410' THEN
'Eagle Lake'
WHEN MOB.LOCATION = 'V01546' THEN
'Fort Kent'
WHEN MOB.LOCATION = 'V01860' THEN
'Madawaska'
WHEN MOB.LOCATION = 'V02000' THEN
'Van Buren'
WHEN MOB.LOCATION = 'V01855' THEN
'Machias UMM Campus'
WHEN MOB.LOCATION = 'V02081' THEN
'Machias UMM Campus'
WHEN MOB.LOCATION = 'V01215' THEN
'Calais'
WHEN MOB.LOCATION = 'V01470' THEN
'Eastport'
WHEN MOB.LOCATION = 'V01850' THEN
'Lubec'
WHEN MOB.LOCATION = 'V01939' THEN
'Presque Isle UMPI Campus'
WHEN MOB.LOCATION = 'V01941' THEN
'Presque Isle High School '
WHEN MOB.LOCATION = 'V01595' THEN
'Gorham USM Campus'
WHEN MOB.LOCATION = 'V01785' THEN
'Lewiston-Auburn USM Campus'
WHEN MOB.LOCATION = 'V01938' THEN
'Portland USM Campus'
WHEN MOB.LOCATION = 'V02082' THEN
'USM Campus'
WHEN MOB.LOCATION = 'V99099' THEN
'USM Campus'
WHEN MOB.CAMPUS = 'BATH' THEN
'Bath/Brunswick UC Center'
WHEN MOB.CAMPUS = 'BBC' THEN
'Bath/Brunswick UC Center'
WHEN MOB.CAMPUS = 'ELL' THEN
'Ellsworth UC Center'
WHEN MOB.CAMPUS = 'EML' THEN
'East Millinocket UC Center'
WHEN MOB.CAMPUS = 'FHC' THEN
'Hutchinson Center - Belfast'
WHEN MOB.CAMPUS = 'HOU' THEN
'Houlton UC Center'
WHEN MOB.CAMPUS = 'HOULT' THEN
'Houlton UC Center'
WHEN MOB.CAMPUS = 'PEN' THEN
'Penquis /Dover-Foxcroft UC Center'
WHEN MOB.CAMPUS = 'ROC' THEN
'Rockland UC Center'
WHEN MOB.CAMPUS = 'RUM' THEN
'Rumford/Mexico UC Center'
WHEN MOB.CAMPUS = 'SAC' THEN
'Saco UC Center'
WHEN MOB.CAMPUS = 'SACO' THEN
'Saco UC Center'
WHEN MOB.CAMPUS = 'SOP' THEN
'Norway/South Paris UC Center'
WHEN MOB.INSTITUTION = 'UMS01' THEN
'Augusta UMA Campus'
WHEN MOB.INSTITUTION = 'UMS02' THEN
'Farmington UMF Campus'
WHEN MOB.INSTITUTION = 'UMS03' THEN
'Fort Kent UMFK Campus'
WHEN MOB.INSTITUTION = 'UMS04' THEN
'Machias UMM Campus'
WHEN MOB.INSTITUTION = 'UMS05' THEN
'Orono UM Campus'
WHEN MOB.INSTITUTION = 'UMS06' THEN
'USM Campus'
WHEN MOB.INSTITUTION = 'UMS07' THEN
'Presque Isle UMPI Campus'
ELSE
'Other'
END LOC_DESCR,
CASE
WHEN MOB.CLASS_DELIVERY_MODE = 'Distance Online' THEN
'Distance Online'
WHEN MOB.LOCATION = 'V01190' THEN
'Bath/Brunswick UC Center'
WHEN MOB.LOCATION = 'V01525' THEN
'Bath/Brunswick UC Center'
WHEN MOB.LOCATION = 'V01820' THEN
'Bath/Brunswick UC Center'
WHEN MOB.LOCATION = 'V02047' THEN
'Bath/Brunswick UC Center'
WHEN MOB.LOCATION = 'V02060' THEN
'Bath/Brunswick UC Center'
WHEN MOB.LOCATION = 'V01440' THEN
'East Millinocket UC Center'
WHEN MOB.LOCATION = 'V01800' THEN
'East Millinocket UC Center'
WHEN MOB.LOCATION = 'V01970' THEN
'East Millinocket UC Center'
WHEN MOB.LOCATION = 'V01490' THEN
'Ellsworth UC Center'
WHEN MOB.LOCATION = 'V01070' THEN
'Ellsworth UC Center'
WHEN MOB.LOCATION = 'V01210' THEN
'Ellsworth UC Center'
WHEN MOB.LOCATION = 'V01360' THEN
'Ellsworth UC Center'
WHEN MOB.LOCATION = 'V01450' THEN
'Ellsworth UC Center'
WHEN MOB.LOCATION = 'V01740' THEN
'Ellsworth UC Center'
WHEN MOB.LOCATION = 'V01900' THEN
'Ellsworth UC Center'
WHEN MOB.LOCATION = 'V01980' THEN
'Ellsworth UC Center'
WHEN MOB.LOCATION = 'V01690' THEN
'Houlton UC Center'
WHEN MOB.LOCATION = 'V01700' THEN
'Houlton UC Center'
WHEN MOB.LOCATION = 'V01250' THEN
'Houlton UC Center'
WHEN MOB.LOCATION = 'V01340' THEN
'Houlton UC Center'
WHEN MOB.LOCATION = 'V01540' THEN
'Houlton UC Center'
WHEN MOB.LOCATION = 'V01730' THEN
'Houlton UC Center'
WHEN MOB.LOCATION = 'V01972' THEN
'Norway/South Paris UC Center'
WHEN MOB.LOCATION = 'V01160' THEN
'Norway/South Paris UC Center'
WHEN MOB.LOCATION = 'V01390' THEN
'Penquis /Dover-Foxcroft UC Center'
WHEN MOB.LOCATION = 'V01380' THEN
'Penquis /Dover-Foxcroft UC Center'
WHEN MOB.LOCATION = 'V01640' THEN
'Penquis /Dover-Foxcroft UC Center'
WHEN MOB.LOCATION = 'V01650' THEN
'Penquis /Dover-Foxcroft UC Center'
WHEN MOB.LOCATION = 'V01670' THEN
'Penquis /Dover-Foxcroft UC Center'
WHEN MOB.LOCATION = 'V01760' THEN
'Penquis /Dover-Foxcroft UC Center'
WHEN MOB.LOCATION = 'V01910' THEN
'Penquis /Dover-Foxcroft UC Center'
WHEN MOB.LOCATION = 'V01945' THEN
'Rockland UC Center'
WHEN MOB.LOCATION = 'V01230 ' THEN
'Rockland UC Center'
WHEN MOB.LOCATION = 'V01750' THEN
'Rockland UC Center'
WHEN MOB.LOCATION = 'V01902' THEN
'Rockland UC Center'
WHEN MOB.LOCATION = 'V01895' THEN
'Rockland UC Center'
WHEN MOB.LOCATION = 'V02020' THEN
'Rockland UC Center'
WHEN MOB.LOCATION = 'V02020' THEN
'Rockland UC Center'
WHEN MOB.LOCATION = 'V01920' THEN
'Rockland UC Center'
WHEN MOB.LOCATION = 'V01982' THEN
'Rockland UC Center'
WHEN MOB.LOCATION = 'V02010' THEN
'Rockland UC Center'
WHEN MOB.LOCATION = 'V01874' THEN
'Rumford/Mexico UC Center'
WHEN MOB.LOCATION = 'V01170' THEN
'Rumford/Mexico UC Center'
WHEN MOB.LOCATION = 'V01780' THEN
'Rumford/Mexico UC Center'
WHEN MOB.LOCATION = 'V01830' THEN
'Rumford/Mexico UC Center'
WHEN MOB.LOCATION = 'V01535' THEN
'Rumford/Mexico UC Center'
WHEN MOB.LOCATION = 'V01940' THEN
'Rumford/Mexico UC Center'
WHEN MOB.LOCATION = 'V01950' THEN
'Saco UC Center'
WHEN MOB.LOCATION = 'V01954' THEN
'Saco UC Center'
WHEN MOB.LOCATION = 'V01951' THEN
'Saco UC Center'
WHEN MOB.LOCATION = 'V01955' THEN
'Saco UC Center'
WHEN MOB.LOCATION = 'V01973' THEN
'Saco UC Center'
WHEN MOB.LOCATION = 'V01928' THEN
'UM Campus'
WHEN MOB.LOCATION = 'V01150' THEN
'Hutchinson Center - Belfast'
WHEN MOB.LOCATION = 'V01035' THEN
'UMA Campus'
WHEN MOB.LOCATION = 'V01075' THEN
'UMA Campus'
WHEN MOB.LOCATION = 'V01520' THEN
'UMA Campus'
WHEN MOB.LOCATION = 'V01037' THEN
'UMA Campus'
WHEN MOB.LOCATION = 'V01040' THEN
'UMA Campus'
WHEN MOB.LOCATION = 'V01971' THEN
'UMA Campus'
WHEN MOB.LOCATION = 'V01990' THEN
'UMA Campus'
WHEN MOB.LOCATION = 'V02050' THEN
'UMA Campus'
WHEN MOB.LOCATION = 'V01530' THEN
'UMF Campus'
WHEN MOB.LOCATION = 'V02081' THEN
'UMF Campus'
WHEN MOB.LOCATION = 'V09998' THEN
'UMF Campus'
WHEN MOB.LOCATION = 'V01545' THEN
'UMFK Campus'
WHEN MOB.LOCATION = 'V02085' THEN
'UMFK Campus'
WHEN MOB.LOCATION = 'V01410' THEN
'UMFK Campus'
WHEN MOB.LOCATION = 'V01546' THEN
'UMFK Campus'
WHEN MOB.LOCATION = 'V01860' THEN
'UMFK Campus'
WHEN MOB.LOCATION = 'V02000' THEN
'UMFK Campus'
WHEN MOB.LOCATION = 'V01855' THEN
'UMM Cam p u s'
WHEN MOB.LOCATION = 'V02081' THEN
'UMM Campus'
WHEN MOB.LOCATION = 'V01215' THEN
'UMM Campus'
WHEN MOB.LOCATION = 'V01470' THEN
'UMM Campus'
WHEN MOB.LOCATION = 'V01850' THEN
'UMM Campus'
WHEN MOB.LOCATION = 'V01939' THEN
'UMPI Campus'
WHEN MOB.LOCATION = 'V01941' THEN
'UMPI Campus'
WHEN MOB.LOCATION = 'V01595' THEN
'USM Campus'
WHEN MOB.LOCATION = 'V01785' THEN
'USM Campus'
WHEN MOB.LOCATION = 'V01938' THEN
'USM Campus'
WHEN MOB.LOCATION = 'V02082' THEN
'USM Campus'
WHEN MOB.LOCATION = 'V99099' THEN
'USM Campus'
WHEN MOB.CAMPUS = 'BATH' THEN
'Bath/Brunswick UC Center'
WHEN MOB.CAMPUS = 'BBC' THEN
'Bath/Brunswick UC Center'
WHEN MOB.CAMPUS = 'ELL' THEN
'Ellsworth UC Center'
WHEN MOB.CAMPUS = 'EML' THEN
'East Millinocket UC Center'
WHEN MOB.CAMPUS = 'FHC' THEN
'Hutchinson Center - Belfast'
WHEN MOB.CAMPUS = 'HOU' THEN
'Houlton UC Center'
WHEN MOB.CAMPUS = 'HOULT' THEN
'Houlton UC Center'
WHEN MOB.CAMPUS = 'PEN' THEN
'Penquis /Dover-Foxcroft UC Center'
WHEN MOB.CAMPUS = 'ROC' THEN
'Rockland UC Center'
WHEN MOB.CAMPUS = 'RUM' THEN
'Rumford/Mexico UC Center'
WHEN MOB.CAMPUS = 'SAC' THEN
'Saco UC Center'
WHEN MOB.CAMPUS = 'SACO' THEN
'Saco UC Center'
WHEN MOB.CAMPUS = 'SOP' THEN
'Norway/South Paris UC Center'
WHEN MOB.INSTITUTION = 'UMS01' THEN
'UMA Campus'
WHEN MOB.INSTITUTION = 'UMS02' THEN
'UMF Campus'
WHEN MOB.INSTITUTION = 'UMS03' THEN
'UMFK Campus'
WHEN MOB.INSTITUTION = 'UMS04' THEN
'UMM Campus'
WHEN MOB.INSTITUTION = 'UMS05' THEN
'UM Campus'
WHEN MOB.INSTITUTION = 'UMS06' THEN
'USM Campus'
WHEN MOB.INSTITUTION = 'UMS07' THEN
'UMPI Campus'
ELSE
'Other'
END REGION,
CASE
WHEN MOB.CLASS_DELIVERY_MODE = 'Distance Online' THEN
'Distance Online'
WHEN MOB.LOCATION = 'V01190' THEN
'UC Center'
WHEN MOB.LOCATION = 'V01525' THEN
'Site'
WHEN MOB.LOCATION = 'V01820' THEN
'Site'
WHEN MOB.LOCATION = 'V02047' THEN
'Site'
WHEN MOB.LOCATION = 'V02060' THEN
'Site'
WHEN MOB.LOCATION = 'V01440' THEN
'UC Center'
WHEN MOB.LOCATION = 'V01800' THEN
'Site'
WHEN MOB.LOCATION = 'V01970' THEN
'Site'
WHEN MOB.LOCATION = 'V01490' THEN
'UC Center'
WHEN MOB.LOCATION = 'V01070' THEN
'Site'
WHEN MOB.LOCATION = 'V01210' THEN
'Site'
WHEN MOB.LOCATION = 'V01360' THEN
'Site'
WHEN MOB.LOCATION = 'V01450' THEN
'Site'
WHEN MOB.LOCATION = 'V01740' THEN
'Site'
WHEN MOB.LOCATION = 'V01900' THEN
'Site'
WHEN MOB.LOCATION = 'V01980' THEN
'Site'
WHEN MOB.LOCATION = 'V01690' THEN
'UC Center'
WHEN MOB.LOCATION = 'V01700' THEN
'Site'
WHEN MOB.LOCATION = 'V01250' THEN
'Site'
WHEN MOB.LOCATION = 'V01340' THEN
'Site'
WHEN MOB.LOCATION = 'V01540' THEN
'Site'
WHEN MOB.LOCATION = 'V01730' THEN
'Site'
WHEN MOB.LOCATION = 'V01972' THEN
'UC Center'
WHEN MOB.LOCATION = 'V01160' THEN
'Site'
WHEN MOB.LOCATION = 'V01390' THEN
'UC Center'
WHEN MOB.LOCATION = 'V01380' THEN
'Site'
WHEN MOB.LOCATION = 'V01640' THEN
'Site'
WHEN MOB.LOCATION = 'V01650' THEN
'Site'
WHEN MOB.LOCATION = 'V01670' THEN
'Site'
WHEN MOB.LOCATION = 'V01760' THEN
'Site'
WHEN MOB.LOCATION = 'V01910' THEN
'Site'
WHEN MOB.LOCATION = 'V01945' THEN
'UC Center'
WHEN MOB.LOCATION = 'V01230' THEN
'Site'
WHEN MOB.LOCATION = 'V01750' THEN
'Site'
WHEN MOB.LOCATION = 'V01902' THEN
'Site'
WHEN MOB.LOCATION = 'V01895' THEN
'Site'
WHEN MOB.LOCATION = 'V02020' THEN
'Site'
WHEN MOB.LOCATION = 'V02020' THEN
'Site'
WHEN MOB.LOCATION = 'V01920' THEN
'Site'
WHEN MOB.LOCATION = 'V01982' THEN
'Site'
WHEN MOB.LOCATION = 'V02010' THEN
'Site'
WHEN MOB.LOCATION = 'V01874' THEN
'UC Center'
WHEN MOB.LOCATION = 'V01170' THEN
'Site'
WHEN MOB.LOCATION = 'V01780' THEN
'Site'
WHEN MOB.LOCATION = 'V01830' THEN
'Site'
WHEN MOB.LOCATION = 'V01535' THEN
'Site'
WHEN MOB.LOCATION = 'V01940' THEN
'Site'
WHEN MOB.LOCATION = 'V01950' THEN
'UC Center'
WHEN MOB.LOCATION = 'V01954' THEN
'Site'
WHEN MOB.LOCATION = 'V01951' THEN
'Site'
WHEN MOB.LOCATION = 'V01955' THEN
'Site'
WHEN MOB.LOCATION = 'V01973' THEN
'Site'
WHEN MOB.LOCATION = 'V01928' THEN
'Campus'
WHEN MOB.LOCATION = 'V01150' THEN
'Campus Center'
WHEN MOB.LOCATION = 'V01035' THEN
'Campus'
WHEN MOB.LOCATION = 'V01075' THEN
'Campus'
WHEN MOB.LOCATION = 'V01520' THEN
'Site'
WHEN MOB.LOCATION = 'V01037' THEN
'Site'
WHEN MOB.LOCATION = 'V01040' THEN
'Site'
WHEN MOB.LOCATION = 'V01971' THEN
'Site'
WHEN MOB.LOCATION = 'V01990' THEN
'Site'
WHEN MOB.LOCATION = 'V02050' THEN
'Site'
WHEN MOB.LOCATION = 'V01530' THEN
'Campus'
WHEN MOB.LOCATION = 'V02081' THEN
'Campus'
WHEN MOB.LOCATION = 'V09998' THEN
'Campus'
WHEN MOB.LOCATION = 'V01545' THEN
'Campus'
WHEN MOB.LOCATION = 'V02085' THEN
'Campus'
WHEN MOB.LOCATION = 'V01410' THEN
'Site'
WHEN MOB.LOCATION = 'V01546' THEN
'Site'
WHEN MOB.LOCATION = 'V01860' THEN
'Site'
WHEN MOB.LOCATION = 'V02000' THEN
'Site'
WHEN MOB.LOCATION = 'V01855' THEN
'Campus'
WHEN MOB.LOCATION = 'V02081' THEN
'Campus'
WHEN MOB.LOCATION = 'V01215' THEN
'Site'
WHEN MOB.LOCATION = 'V01470' THEN
'Site'
WHEN MOB.LOCATION = 'V01850' THEN
'Site'
WHEN MOB.LOCATION = 'V01939' THEN
'Campus'
WHEN MOB.LOCATION = 'V01941' THEN
'Site'
WHEN MOB.LOCATION = 'V01595' THEN
'Campus'
WHEN MOB.LOCATION = 'V01785' THEN
'Campus'
WHEN MOB.LOCATION = 'V01938' THEN
'Campus'
WHEN MOB.LOCATION = 'V02082' THEN
'Campus'
WHEN MOB.LOCATION = 'V99099' THEN
'Campus'
WHEN MOB.CAMPUS = 'BATH' THEN
'UC Center'
WHEN MOB.CAMPUS = 'BBC' THEN
'UC Center'
WHEN MOB.CAMPUS = 'ELL' THEN
'UC Center'
WHEN MOB.CAMPUS = 'EML' THEN
'UC Center'
WHEN MOB.CAMPUS = 'FHC' THEN
'Campus Center'
WHEN MOB.CAMPUS = 'HOU' THEN
'UC Center'
WHEN MOB.CAMPUS = 'HOULT' THEN
'UC Center'
WHEN MOB.CAMPUS = 'PEN' THEN
'UC Center'
WHEN MOB.CAMPUS = 'ROC' THEN
'UC Center'
WHEN MOB.CAMPUS = 'RUM' THEN
'UC Center'
WHEN MOB.CAMPUS = 'SAC' THEN
'UC Center'
WHEN MOB.CAMPUS = 'SACO' THEN
'UC Center'
WHEN MOB.CAMPUS = 'SOP' THEN
'UC Center'
ELSE
'Campus'
END SITE_CATEGORY,
MOB.UM_STDNT_ENRL_STAT,
MOB.UNIV_CENTER_DESC,
MOB.UNIV_CENTER_FLAG,
CASE
WHEN MOB.STRM < '1910' THEN
MOB.INSTITUTION
WHEN MOB.GRADING_BASIS_ENRL = 'NON' THEN
MOB.INSTITUTION
WHEN MOB.ACAD_GROUP IN
('FUMA', 'KUMA', 'MUMA', 'OUMA', 'PUMA', 'IUMA') THEN
'UMS01'
WHEN MOB.ACAD_GROUP IN
('AUMF', 'KUMF', 'MUMF', 'OUMF', 'PUMF', 'IUMF') THEN
'UMS02'
WHEN MOB.ACAD_GROUP IN
('AUMFK', 'FUMFK', 'MUMFK', 'OUMFK', 'PUMKF', 'IUMFK') THEN
'UMS03'
WHEN MOB.ACAD_GROUP IN
('AUMM', 'FUMM', 'KUMM', 'OUMM', 'PUMM', 'IUMM') THEN
'UMS04'
WHEN MOB.ACAD_GROUP IN ('AUM', 'FUM', 'KUM', 'MUM', 'PUM', 'IUM') THEN
'UMS05'
WHEN MOB.ACAD_GROUP IN
('AUSM', 'FUSM', 'KUSM', 'MUSM', 'OUSM', 'IUSM') THEN
'UMS06'
WHEN MOB.ACAD_GROUP IN
('AUMPI', 'FUMPI', 'KUMPI', 'MUMPI', 'OUMPI', 'PUMPI') THEN
'UMS07'
ELSE
MOB.INSTITUTION
END UM_INST_HOST
FROM MORBO MOB;
SQL for UM_STUCRSTAB (creates UM_D_STD_ENR) Expand source
SELECT STD.EMPLID
, %CurrentDateOut AS ARCHIVE_DT
, 'Y' AS ARCHIVE_FLAG
, 'N' AS CHANGE_FLG
, 'N' AS DELETE_FLAG
, 'N' AS ENROL_FLAG
, STD.ACAD_CAREER
, STD.INSTITUTION
, STD.STRM
, STD.CLASS_NBR
, STD.CRSE_CAREER
, STD.SESSION_CODE
, STD.STDNT_ENRL_STATUS
, STD.ENRL_STATUS_REASON
, STD.ENRL_ACTION_LAST
, STD.ENRL_ACTN_RSN_LAST
, STD.ENRL_ACTN_PRC_LAST
, %DateOut(STD.STATUS_DT)
, %DateOut(STD.ENRL_ADD_DT)
, %DateOut(STD.ENRL_DROP_DT)
, STD.UNT_TAKEN
, STD.UNT_PRGRSS
, STD.UNT_PRGRSS_FA
, STD.UNT_BILLING
, STD.CRSE_COUNT
, STD.GRADING_BASIS_ENRL
, %DateOut(STD.GRADING_BASIS_DT)
, STD.OVRD_GRADING_BASIS
, STD.CRSE_GRADE_OFF
, STD.CRSE_GRADE_INPUT
, %DateOut(STD.GRADE_DT)
, STD.REPEAT_CODE
, %DateOut(STD.REPEAT_DT)
, STD.ASSOCIATED_CLASS
, STD.AUDIT_GRADE_BASIS
, STD.EARN_CREDIT
, STD.INCLUDE_IN_GPA
, STD.UNITS_ATTEMPTED
, STD.GRADE_POINTS
, STD.GRADE_POINTS_FA
, STD.GRD_PTS_PER_UNIT
, STD.MANDATORY_GRD_BAS
, STD.INSTRUCTOR_ID
, STD.DROP_CLASS_IF_ENRL
, STD.ASSOCIATION_99
, STD.TSCRPT_NOTE_ID
, STD.TSCRPT_NOTE_EXISTS
, STD.NOTIFY_STDNT_CHNG
, STD.REPEAT_CANDIDATE
, STD.VALID_ATTEMPT
, STD.GRADE_CATEGORY
, STD.SEL_GROUP
, STD.DYN_CLASS_NBR
, STD.UNT_EARNED
, %DateOut(STD.LAST_UPD_DT_STMP)
, %TimeOut(STD.LAST_UPD_TM_STMP)
, %DateOut(STD.LAST_ENRL_DT_STMP)
, %TimeOut(STD.LAST_ENRL_TM_STMP)
, %DateOut(STD.LAST_DROP_DT_STMP)
, %TimeOut(STD.LAST_DROP_TM_STMP)
, STD.ENRL_REQ_SOURCE
, STD.LAST_UPD_ENREQ_SRC
, STD.GRADING_SCHEME_ENR
, STD.RELATE_CLASS_NBR_1
, STD.RELATE_CLASS_NBR_2
, STD.ACAD_PROG
, STD.CRSE_ID
, STD.CRSE_OFFER_NBR
, STD.CLASS_SECTION
, STD.ACAD_GROUP
, STD.SUBJECT
, STD.CATALOG_NBR
, STD.DESCR
, STD.SSR_COMPONENT
, STD.ENRL_STAT
, STD.CLASS_STAT
, STD.CLASS_TYPE
, STD.STDNT_SPEC_PERM
, STD.CAMPUS
, STD.LOCATION
, STD.INSTRUCTION_MODE
, STD.DESCR_LOCATION
, STD.UM_DISTANCE_FLAG
, STD.UM_CENTER_CD
, STD.UM_CENTER_DESCR
, STD.LAST_NAME
, STD.FIRST_NAME
, STD.MIDDLE_NAME
, STD.NAME_PREFIX
, STD.NAME_SUFFIX
, STD.UM_DIST_ATTR_FLAG
, STD.UM_IMODE
, STD.ENRL_CAP
, STD.ACAD_ORG
, STD.UM_ACAD_ORG_DESCR
, STD.SCTN_COMBINED_ID
, STD.UNITS_MINIMUM
, STD.UNITS_MAXIMUM
, STD.UNITS_ACAD_PROG
FROM (
SELECT A.EMPLID
, A.ACAD_CAREER
, A.INSTITUTION
, A.STRM
, A.CLASS_NBR
, A.CRSE_CAREER
, A.SESSION_CODE
, A.STDNT_ENRL_STATUS
, A.ENRL_STATUS_REASON
, A.ENRL_ACTION_LAST
, A.ENRL_ACTN_RSN_LAST
, A.ENRL_ACTN_PRC_LAST
, A.STATUS_DT
, A.ENRL_ADD_DT
, A.ENRL_DROP_DT
, A.UNT_TAKEN
, A.UNT_PRGRSS
, A.UNT_PRGRSS_FA
, A.UNT_BILLING
, A.CRSE_COUNT
, A.GRADING_BASIS_ENRL
, A.GRADING_BASIS_DT
, A.OVRD_GRADING_BASIS
, A.CRSE_GRADE_OFF
, A.CRSE_GRADE_INPUT
, A.GRADE_DT
, A.REPEAT_CODE
, A.REPEAT_DT
, A.ASSOCIATED_CLASS
, A.AUDIT_GRADE_BASIS
, A.EARN_CREDIT
, A.INCLUDE_IN_GPA
, A.UNITS_ATTEMPTED
, A.GRADE_POINTS
, A.GRADE_POINTS_FA
, A.GRD_PTS_PER_UNIT
, A.MANDATORY_GRD_BAS
, E.EMPLID AS INSTRUCTOR_ID
, A.DROP_CLASS_IF_ENRL
, A.ASSOCIATION_99
, A.TSCRPT_NOTE_ID
, A.TSCRPT_NOTE_EXISTS
, A.NOTIFY_STDNT_CHNG
, A.REPEAT_CANDIDATE
, A.VALID_ATTEMPT
, A.GRADE_CATEGORY
, A.SEL_GROUP
, A.DYN_CLASS_NBR
, A.UNT_EARNED
, A.LAST_UPD_DT_STMP
, A.LAST_UPD_TM_STMP
, A.LAST_ENRL_DT_STMP
, A.LAST_ENRL_TM_STMP
, A.LAST_DROP_DT_STMP
, A.LAST_DROP_TM_STMP
, A.ENRL_REQ_SOURCE
, A.LAST_UPD_ENREQ_SRC
, A.GRADING_SCHEME_ENR
, A.RELATE_CLASS_NBR_1
, A.RELATE_CLASS_NBR_2
, A.ACAD_PROG
, B.CRSE_ID
, B.CRSE_OFFER_NBR
, B.CLASS_SECTION
, B.ACAD_GROUP
, B.SUBJECT
, B.CATALOG_NBR
, B.DESCR
, B.SSR_COMPONENT
, B.ENRL_STAT
, B.CLASS_STAT
, B.CLASS_TYPE
, B.STDNT_SPEC_PERM
, B.CAMPUS
, B.LOCATION
, B.INSTRUCTION_MODE
, CASE WHEN C.EFF_STATUS = 'A' THEN C.DESCR ELSE C.DESCR||'-Inactive' END AS DESCR_LOCATION
, decode((
SELECT SUM(decode(z.crse_attr
,'DIST'
,1
,'BRD'
,2
,0))
FROM ps_class_attribute z
WHERE b.crse_id = z.crse_id
AND b.crse_offer_nbr = z.crse_offer_nbr
AND b.strm = z.strm
AND b.session_code = z.session_code
AND b.class_section = z.class_section
AND ((z.crse_attr = 'DIST'
AND (z.crse_attr_value = 'ITV'
OR z.crse_attr_value = 'VIDEOCONF'))
OR (z.crse_attr ='BRD' ))),1,'R',3,'B',' ') AS UM_DISTANCE_FLAG,H.UM_CENTER_CD,I.DESCR AS UM_CENTER_DESCR, D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME, D.NAME_PREFIX, D.NAME_SUFFIX, NVL((
SELECT DISTINCT 'Y'
FROM ps_class_attribute z
WHERE b.crse_id = z.crse_id
AND b.crse_offer_nbr = z.crse_offer_nbr
AND b.strm = z.strm
AND b.session_code = z.session_code
AND b.class_section = z.class_section
AND z.crse_attr IN ('DIST','DIST' )),'N') AS UM_DIST_ATTR_FLAG , decode ( (
SELECT MIN( decode(z.crse_attr_value
,'ITV'
,'1'
,'VIDEOCONF'
,'2'
,'ONLINE'
,'3'
,'ONSITE'
,'4'
,'BROADCAST'
,'5'
,'0') )
FROM ps_class_attribute z
WHERE b.crse_id = z.crse_id
AND b.crse_offer_nbr = z.crse_offer_nbr
AND b.strm = z.strm
AND b.session_code = z.session_code
AND b.class_section = z.class_section
AND ( ( z.crse_attr = 'DIST'
AND z.crse_attr_value = 'ITV' )
OR (z.crse_attr = 'DIST'
AND z.crse_attr_value = 'VIDEOCONF')
OR (z.crse_attr = 'DIST'
AND z.crse_attr_value = 'ONLINE')
OR (z.crse_attr = 'DIST'
AND z.crse_attr_value = 'ONSITE')
OR (z.crse_attr ='BRD'
AND z.crse_attr_value = 'BROADCAST') ) ),'1','ITV','2','VIDEOCONF','3','ONLINE','4','ONSITE','5','BROADCAST','0','?') AS UM_IMODE, B.ENRL_CAP, B.ACAD_ORG, CASE WHEN F.EFF_STATUS = 'A' THEN F.DESCR ELSE F.DESCR||'-Inactive' END AS UM_ACAD_ORG_DESCR, G.SCTN_COMBINED_ID, BC.UNITS_MINIMUM, BC.UNITS_MAXIMUM, BC.UNITS_ACAD_PROG
FROM PS_STDNT_ENRL A , PS_CLASS_TBL B , PS_LOCATION_TBL C, PS_PERSONAL_DATA D, PS_CLASS_INSTR E, PS_ACAD_ORG_TBL F, PS_SCTN_CMBND G , PS_CRSE_CATALOG BC, (
SELECT MAX(inh.um_center_cd) um_center_cd
, inh.location
FROM ps_um_location_dtl inh
WHERE inh.effdt = (
SELECT MAX(H1.EFFDT)
FROM PS_UM_LOCATION_DTL H1
WHERE INH.LOCATION = H1.LOCATION
AND INH.UM_CENTER_CD = H1.UM_CENTER_CD
AND H1.EFFDT <= %CurrentDateIn)
AND inh.eff_status = 'A'
GROUP BY inh.location) H, PS_UM_CENTER_TBL I
WHERE A.CLASS_NBR = B.CLASS_NBR(+)
AND A.STRM = B.STRM(+)
AND A.SESSION_CODE = B.SESSION_CODE(+)
AND A.INSTITUTION = B.INSTITUTION(+)
AND C.LOCATION(+) = B.LOCATION
AND C.SETID(+) = B.INSTITUTION
AND (C.EFFDT = (
SELECT MAX(C_ED.EFFDT)
FROM PS_LOCATION_TBL C_ED
WHERE C_ED.SETID = C.SETID
AND C_ED.LOCATION = C.LOCATION
AND C_ED.EFFDT <= %CurrentDateIn)
OR C.EFFDT IS NULL)
AND E.CRSE_ID(+) = B.CRSE_ID
AND E.CRSE_OFFER_NBR(+) = B.CRSE_OFFER_NBR
AND E.STRM(+) = B.STRM
AND E.SESSION_CODE(+) = B.SESSION_CODE
AND E.CLASS_SECTION(+) = B.CLASS_SECTION
AND ( E.INSTR_ROLE||E.INSTR_ASSIGN_SEQ = (
SELECT MIN(DD.INSTR_ROLE||DD.INSTR_ASSIGN_SEQ)
FROM PS_CLASS_INSTR DD
WHERE DD.CRSE_ID = E.CRSE_ID
AND DD.CRSE_OFFER_NBR = E.CRSE_OFFER_NBR
AND DD.STRM = E.STRM
AND DD.SESSION_CODE = E.SESSION_CODE
AND DD.CLASS_SECTION = E.CLASS_SECTION
AND DD.CLASS_MTG_NBR = E.CLASS_MTG_NBR)
OR E.INSTR_ASSIGN_SEQ IS NULL)
AND ( E.CLASS_MTG_NBR = (
SELECT MIN(DED.CLASS_MTG_NBR)
FROM PS_CLASS_INSTR DED
WHERE DED.CRSE_ID = E.CRSE_ID
AND DED.CRSE_OFFER_NBR = E.CRSE_OFFER_NBR
AND DED.STRM = E.STRM
AND DED.SESSION_CODE = E.SESSION_CODE
AND DED.CLASS_SECTION = E.CLASS_SECTION )
OR E.CLASS_MTG_NBR IS NULL)
AND E.EMPLID = D.EMPLID(+)
AND B.ACAD_ORG = F.ACAD_ORG(+)
AND (F.EFFDT = (
SELECT MAX(F_ED.EFFDT)
FROM PS_ACAD_ORG_TBL F_ED
WHERE F_ED.ACAD_ORG = F.ACAD_ORG
AND F_ED.EFFDT <= %CurrentDateIn)
OR F.EFFDT IS NULL)
AND G.INSTITUTION(+) = A.INSTITUTION
AND G.STRM(+) = A.STRM
AND G.SESSION_CODE(+) = A.SESSION_CODE
AND G.CLASS_NBR(+) = A.CLASS_NBR
AND B.CRSE_ID = BC.CRSE_ID(+)
AND (BC.EFFDT = (
SELECT MAX(BCD.EFFDT)
FROM PS_CRSE_CATALOG BCD
WHERE BCD.CRSE_ID = BC.CRSE_ID )
OR bc.effdt IS NULL)
AND H.UM_CENTER_CD = I.UM_CENTER_CD(+)
AND B.LOCATION = H.LOCATION(+)
AND (I.EFFDT = (
SELECT MAX(IYI.EFFDT)
FROM PS_UM_CENTER_TBL IYI
WHERE IYI.UM_CENTER_CD = I.UM_CENTER_CD
AND IYI.EFFDT <= %CurrentDateIn)
OR I.EFFDT IS NULL) ) STD, PSPRCSRQST AA, SYSADM.PS_UM_RUN_UMSTUEXT BB
WHERE AA.PRCSNAME = 'UM_STUCRSTAB'
AND BB.OPRID = AA.OPRID
AND BB.RUN_CNTL_ID = AA.RUNCNTLID
AND AA.PRCSINSTANCE = %Bind(PROCESS_INSTANCE)
AND STD.STRM >= BB.UM_BEGIN_TERM
AND STD.STRM <= BB.UM_END_TERM
Attachments:
UM_D_STD_ENR_VW.xlsx (application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
UM_D_STD_ENR_VW.xlsx (application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)