using the electronic campus data export process...the electronic campus data export process (zorsreb...

Using the Electronic Campus Data Export Process

Georgia Enhanced Banner Student User Documentation

Version 8.0

Office of Information and Instructional Technology

March 2010

This page left blank to facilitate front/back printing.

Using the Electronic Campus Data Export Process Office of Information Georgia Enhancements 8.0 and Instructional Technology

Georgia Enhancements 8.0 User Documentation Electronic Campus Data Export Process

Page iii March 2010

Table of Contents

Introduction ................................................................................................................... 1

Electronic Campus Data Export Process Flow ........................................................... 3

Using the Electronic Campus Data Export Process ................................................... 4

I. Setting Up Fees ............................................................................................. 4

II. Setting Up Translations ................................................................................ 5

III. Processing ..................................................................................................... 8

IV. Importing into Excel .................................................................................... 15

Appendix A: Document History ............................................................................... A-1

Appendix B: Process Flow ....................................................................................... B-1



Page iv March 2010

This page left blank to facilitate front/back printing.



Page 1 March 2010

Using the Georgia Enhanced Electronic Campus Data Export Process

Introduction

Purpose The Electronic Campus Data Export process (ZORSREB) pulls specific fields from Banner and arranges them for electronic transmission to Electronic Campus staff on a course by course basis. The output is provided in an XML format that can be imported in two separate Excel spreadsheets (one for the course data and one for course offering data) or separated in the same Excel workbook. All required fields must be provided for the feed to be accepted by Electronic Campus. This process is only for the Southern Regional Education Board (SREB) and Electronic Campus and has no correlation with Georgia ONmyLINE.

The Distance Education Flat Fee form (ZSAFFEE) houses tuition and fees (In-State, Out-of-State, Electronic Rate, and Out-of-District) for reporting to the Electronic Campus and Georgia ONmyLINE searchable databases. This form designates tuition and condenses all fees (i.e. Technology fees, Activity fees, Lab fees, etc.) into a single fee per hour of instruction for each of the four columns on ZSAFFEE. The fees presented on the form are intended to be estimates of all applicable fees.

Target Audience This documentation has been prepared for Registrars and staff responsible for maintaining distance education courses.

Purpose and Scope of Document

This document explains how to use the Electronic Campus Data Export process and outlines the expected results.

Graphics Graphic cues used in this document assist with labeling steps and items that are particularly important.

Steps for recovery or support.

For additional information, see references.

Exercise caution.

Important information.

On target: steps completed successfully.



Page 2 March 2010

Support For additional OIIT resources and support, contact the OIIT Helpdesk:

• Web support request self-service: http://www.usg.edu/customer_services

• E-mail: [email protected]

For emergency, business interruptions, or production down situations, call the OIIT Helpdesk immediately

• 706-583-2001 • Toll free within Georgia: 1-888-875-3697



Page 3 March 2010

Electronic Campus Data Export Process Flow

Description

The Electronic Campus Data Export process allows users to pull specific fields from Banner and arrange them for electronic transmission to Electronic Campus. The data can be either in two separate Excel spreadsheets, for course data and for course offering data, or in one Excel workbook. The Distance Education Flat Fee form (ZSAFFEE) houses flat rate fees for individual course hours and course level.

Overview

I. Setting Up Fees

Create Distance Education Flat Fee Rules ZSAFFEE

↓ II. Setting Up Translations

1. Setting up Crosswalk Validation GTVSDAX

2. Create Crosswalk Label STVXLBL

3. Crosswalk Level Type Codes SOAXREF

↓ III. Processing

1. Confirm setup SSASECT, SSASYLB, SCASYLB, SCADETL

2. Execute the Electronic Campus Data Export Process

ZORSREB

↓ IV. Importing files into Excel

1. Open a file

2. Select file type

3. Mark the file as delimited

4. Verify the format

5. Finish the import



Page 4 March 2010

Using the Electronic Campus Data Export Process I. Setting Up Fees

Introduction The Distance Education Flat Fee form (ZSAFFEE) is designed to house flat rate fees for a single hour of instruction. The Electronic Campus Data Export process (ZORSREB) multiplies the given values by the credit hours to provide Electronic Campus with an accurate total for In-State fees, Out-of-State fees, Electronic Rate fees, and Out-of-District fees based on course level.

Create Distance Education Flat Fee Rules (ZSAFFEE)

Enter the flat rate per single hour for tuition and fees in the In-State, Out-of-State, Electronic Rate, and Out-of-District on ZSAFFEE. ZORSREB will pull the fee based on level and multiply it by the section or course credit hours to calculate the correct amount and report it to the Electronic Campus.



Page 5 March 2010

Caution

All fee fields on ZSAFFEE must be completed. Use a zero dollar amount when fees are not applicable for a field. Electronic Campus does not report all fee data, even if all fees contain values in ZSAFFEE. The following guidelines provided by Electronic Campus will ensure accurate reporting:

If either In-State fee or Out-of-State fee fields have dollar amounts, verify the Electronic Rate fee field is set to a zero dollar amount. Conversely, if Electronic Rate fee field has a dollar amount, verify In-State fee and Out-of-State fee fields are set to a zero dollar amount. If Out-of-District fee field is blank, it should be set to a zero dollar amount. If all fields contain values on a ZSAFFEE row, ZORSREB will report In-State fees, Out-of-State fees, and Out-of-District fees. To report the Electronic Rate, both In-State fees and Out-of-State fees must be zero dollar amounts.

II. Setting Up Translations

Introduction The Electronic Campus requires a semester or quarter code to be reported for all courses. The USG defines the credit type by level code. A new crosswalk will need to be created on the EDI Verification Label Validation table (STVXLBL) and defined in the EDI Cross-Reference Rules form (SOAXREF) for all level values in the Level Code Validation table (STVLEVL). The new label will translate STVLEVL values to Semester or Quarter.

1. Setting up Crosswalk Validation (GTVSDAX)

An optional zorsreb_gtvsdax_insert.sql script has been delivered with ZORSREB. This script inserts four rows in the Crosswalk Validation form (GTXSDAX) with ZORSREBLVL in the internal code. These crosswalk entries are designed to translate campus level codes into standard USG level codes. The Translation Code should contain the campus code matching the USG standard for each level code in the External Code field. If the translation and the external codes would be the same, the row is not required.



Page 6 March 2010

2. Create Crosswalk Label (STVXLBL)

To create the new label, insert a new record on STVXLBL. The new record should have an EDI Label Code value of “LEVELTYP” with a Description of “Semester/Quarter Level Type.” Please note this is not a System Required value. The System Required indicator is only for use by SGHE for seed values.



Page 7 March 2010

3. Crosswalk Level Type Codes (SOAXREF)

Create the new LEVELTYP cross reference label in SOAXREF. This label will translate all of the institutional level codes from STVLEVL to Semester or Quarter.

Enter an Electronic Value of “Semester” or “Quarter”. Please note that when placing multiple rows on SOAXREF under the same Electronic Value, an Electronic Qualifier must be used to differentiate the codes.

After defining the Electronic Value, enter the corresponding level type from STVLEVL in the Banner Value column. Enter a description to denote the level type being translated. All level codes in STVLEVL should be entered under the LEVLTYP label to ensure proper translation.

Caution

The following error message will display if you try to enter multiple Quarter or Semester values without providing a qualifier:

FRM-40508: Oracle error: Unable to INSERT record.

Although required, the Electronic Qualifier is not used by the logic of ZORSREB.



Page 8 March 2010

III. Processing

Introduction The Electronic Campus Data Export process (ZORSREB) selects all of the distance education courses for a desired term and prepares them for electronic transmission to Electronic Campus. The output produces two XML files that can either be imported in separate Excel spreadsheets (one for the course data and one for the course offering data) or separated in one Excel workbook. All required field information must be provided for feed to be accepted by Electronic Campus.

Institutions providing this information will need to meet the following conditions (policy and formatting) in order to expedite the upload process.

• Approval forms from the State Coordinator need to be on file with Electronic Campus staff– prior to the course being included as part of a bulk upload.

• Each college or university submitting courses must have an Electronic Campus coordinator with an active Electronic Campus account.

• Colleges and universities must have an approved Electronic Campus transitional page residing on their server.

• College and university Electronic Campus coordinators agree to make any necessary edits via the Electronic Campus web interface. SREB will not accept courses from colleges and universities who do not make required edits for two consecutive feeds.

1. Confirm setup

ZORSREB will pull courses from the Course Schedule form (SSASECT) that have a value of F (Fully at a Distance) or P (Partially at a Distance) in the Instructional Method field ZORSREB first pulls course Long Title, Learning Objectives, Required Materials, and Technical Requirements from Section Syllabus form (SSASYLB). If no data is available on SSASYLB, ZORSREB will pull information from the Course Syllabus form (SCASYLB). All course prerequisites may be added to the Course Detail Information form (SCADETL) under Course Description to ensure they are properly captured and readily displayed. The prerequisites may include a specific grade for a prior course or minimum test scores to enroll in a particular course.

On Target

Utilize the following Banner functions to correctly enter all of the section syllabus information:

• Use the Copy From Course button on SSASYLB to copy any information found at the course level, such as the Long Course Title, to ensure that no abbreviations are included in the ZORSREB Courses output file.

• Use the Faculty Self-Service Syllabus Information page (bwlksybs.P_Fac_Syllabus) to allow faculty to enter or update syllabus information. This allows an instant edit of the



Page 9 March 2010

SSASYLB information. Please be aware that the Self-Service editor does not like special characters, like hard returns.

2. Execute the Electronic Campus Data Export Process (ZORSREB)

ZORSREB selects course data from Banner and arranges them for electronic transmission. This process should be run for every term that distance education courses are offered and still available for registration so they can be submitted to Electronic Campus.

Parameters for ZORSREB

No Name Required Description/Instructions Default Values

01 Term Yes Enter term to be processed.

02 Admission Required Yes Is admission to your institution is required prior to registration? Valid values are TRUE or FALSE.

03 Admission by Internet

Yes Is admission to your institution is available via the Internet? Valid values are TRUE or FALSE.

04 Internet Registration Allowed

Yes Is registration for your institution available via the Internet? Valid values are TRUE or FALSE.

05 Contact ID No Enter Contact ID provided by Electronic Campus.

Caution: Contact ID is not required for ZORSREB to run, however Electronic Campus does require this information to load the files.

Sample Files Samples of the .log, .lis, .courses, and .courseofferings files follow: Sample ZORSREB.lis



Page 10 March 2010

Sample ZORSREB.log



Page 11 March 2010

Sample ZORSREB courses file

Sample ZORSREB courseofferings file



Page 12 March 2010

Courses Table Layout

Position Electronic Campus Field Type Size Description

1 ID Integer 4 Course specific ID consisting of FICE, Term and CRN codes

2 ContactID Integer 4 Entered into ZORSREB by user 3 Approved Bit 1 Defaulted to FALSE 4 Status Bit 1 Defaulted to FALSE 5 Publicly Bit 1 Defaulted to TRUE 6 StatePublicly Bit 1 Defaulted to FALSE 7 InstitutionID Integer 4 Translates the FICE value from ZTVINST with

the Home Institution Indicator checked into the values expected from Electronic Campus.

8 StateID Integer 4 11 9 DegreeLvlID Integer 4 Cross-referenced on GTVSDAX and SOAXREF

using the STVLEVL label to define Graduate or Undergraduate

1 Undergraduate 2 Graduate 3 Other

10 CIPChoiceID Variable 8 First four characters of the CIP code from SCACRSE truncated and with a decimal in the middle

11 CourseDept Variable 10 Subject code for course from SSASECT 12 CourseNo Variable 30 Course number from SSASECT 13 SectionNo Variable 10 Course section from SSASECT 14 CourseTitle Variable 100 Course title from SSASECT 15 CourseDescription Text 16 Course description or Course Text from

SCADETL 16 Credits Variable 4 Low credit hours from SCACRSE 17 CreditType Bit 1 Credit type as crosswalked in SOAXREF using

LEVELTYP Semester/Quarter Level type 18 PDFID Integer 4 Translated from USG Contract Code values in

SSADETL to the table values below 1 CD-ROM 2 Compressed Video 3 Computer-aided instruction 4 E-mail 5 Open broadcast 6 Satellite (Analog) 7 Satellite (Digital) 8 Videotape 9 Internet 10 Voice mail 11 Audio 12 Cable TV 13 Other



Page 13 March 2010


19 SDF Variable 100 NULL 20 URL Variable 150 NULL 21 Email Bit 1 Defaulted to FALSE

22 Voice Bit 1 Defaulted to FALSE

23 Postal Bit 1 Defaulted to FALSE

24 Video Bit 1 Defaulted to FALSE

25 DiscussionBoard Bit 1 Defaulted to FALSE

26 ChatRoom Bit 1 Defaulted to FALSE

27 EConferencing Bit 1 Defaulted to FALSE

28 OnlineAssignment Bit 1 Defaulted to FALSE

29 Other Bit 1 Defaulted to FALSE 30 OtherMode Variable 30 NULL 31 CourseMgmtID Integer 4 Defaulted to 3 32 CourseMgmtOther Variable 150 NULL 33 EnrollCapacity Integer 2 Maximum enrollment count 34 EnrollAccess Text 16 NULL 35 CoursePrereqs Text 16 Course and/or test prerequisites as defined on

SSAPREQ or SCAPREQ 36 EnrollProc Text 16 NULL 37 EnrollSpecial Text 16 NULL 38 EnrollAttend Text 16 NULL 39 EnrollOther Text 16 NULL 40 Admitted Bit 1 TRUE or FALSE as entered into ZORSREB by

user 41 AdmissInternet Bit 1 TRUE or FALSE as entered into ZORSREB by

user

42 RegInternet Bit 1 TRUE or FALSE as entered into ZORSREB by user

43 InputDate Date/time 8 Sysdate 44 IP Variable 16 NULL 45 OnlineEntry Bit 1 NULL 46 NonCredit Bit 1 If Credits is null or zero, value is TRUE, else

FALSE 47 ActivationDate Date/time 8 NULL 48 ActivationBy Variable 20 NULL 49 ActivationComment Text 16 NULL 50 CourseComment Text 16 NULL 51 BulkUpload Bit 1 TRUE 52 Archive Bit 1 FALSE



Page 14 March 2010


53 SpecialType Variable 10 NULL 54 DateEdited Date/time 8 NULL 55 StatusComment Variable 50 NULL 56 StatusInitials Variable 5 NULL 57 StatusDate Variable 10 NULL CourseOffering Table Layout


1 COID Integer 4 Course specific ID compiled by FICE, Term, and CRN codes

2 CourseID_programID Integer 4 NULL 3 EnrollStart Date/time 8 If parameter 3 of ZORSREB is set to ‘TRUE’,

the EnrollStart field will pull the Web Registered (RW) status start date entered on the Course Registration Status form (SFARSTS). If ‘FALSE’, the Registered (RE) status start date will be pulled.

4 EnrollStartOpen Bit 1 NULL 5 EnrollEnd Date/time 8 If parameter 3 of ZORSREB is set to ‘TRUE’,

the EnrollEnd field will pull the Web Registered (RW) status end date entered on SFARSTS. If ‘FALSE’, the Registered (RE) status end date will be pulled.

6 EnrollEndOpen Bit 1 If EnrollEnd is NULL then TRUE, else FALSE 7 CourseStart Date/time 8 Part of term start date or Course start date 8 CSOpen Bit 1 If CourseStart is NULL then TRUE, else FALSE 9 CourseEnd Date/time 8 Part of term end date or course end date based

on duration 10 CEOpen Bit 1 If CourseEnd is NULL then TRUE, else FALSE 11 Offered Bit 1 Based on course status code, active set to

TRUE, else FALSE 12 SchoolTerm Variable 15 Translates last two digits of Term code using

the HOPECHCKPT label in GTVSDAX Translates again based on following values: 02 = Spring 05 = Summer 08 = Fall

13 TermYear Variable 4 Fist four digits of Term code



Page 15 March 2010


14 TermChoiceID Integer 4 Translates SchoolTerm and TermYear to numeric values based on the following numbering scheme devised by Electronic Campus

26 Spring 2006 27 Summer 2006 28 Fall 2006 29 Spring 2007 30 Summer 2007 31 Fall 2007

15 Tuition1 Money 8 In-state Tuition from ZSAFFEE multiplied by Credits

16 GSF1 Money 8 In-state Fee from ZSAFFEE multiplied by Credits

17 Tuition2 Money 8 Out-of-state Tuition from ZSAFFEE multiplied by Credits

18 GSF2 Money 8 Out-of-state Fee from ZSAFFEE multiplied by Credits

19 Tuition3e Money 8 Electronic Rate Tuition from ZSAFFEE multiplied by Credits

20 GSF3e Money 8 Electronic Rate Fee from ZSAFFEE multiplied by Credits

21 TuitionD Money 8 Out-of-District Tuition from ZSAFFEE multiplied by Credits

22 GSFD Money 8 Out-of-District Fee from ZSAFFEE multiplied by Credits

23 Materials Money 8 Defaulted to 0 24 DTS Date/time 8 Sysdate translated to MM/DD/YYYY format 25 Type Bit 1 Defaulted to C 26 CensusDate Integer 2 Census date from SSASECT minus start date 27 CensusEnroll Integer 2 NULL 28 CensusEnd Integer 2 NULL

IV. Importing into Excel

Introduction

ZORSREB creates two XML files to be loaded into Excel. The two files produced are the courses file and the courseoffering file, which contain all of the values required by Electronic Campus in the Courses Table and the CourseOffering Table, respectively.

Electronic Campus accepts the files separated into two Excel spreadsheets or inside the same workbook, but on different sheets.

1. Open a file

In Excel, open one of the text files created by ZORSREB. This should start the Text Import Wizard.



Page 16 March 2010

2. Select file type Click the Delimited Radial button to identify the file as an XML table. Click OK.

3. Finish the import Acknowledge that the XML schema does not already exist by clicking OK.

4. Verify the format

Warning

Click the Format Cells to verify the Column Data Format is in number format for fields with only numbers. Make sure that the decimal places are set to zero for fields without decimals. Failure to use the Number format may cause truncation of number fields.

5. Enter Required fields

All of the following fields are set to blank and should have header tags in the XML layout. These should import into Excel as empty cells so that campus users can enter data into Excel before sending to the SREB.

• URL – (Blank) This should be the campus Electronic Campus transitional page URL. Note that this will be a transitional web page of course offerings similar to Electronic Campus. It will not be an individual course link, Vista link, or Self-Service Banner link.

• EnrollAccess – (Blank) This should access learning and support services such as the library and bookstore. Institutional bookstore and library phone numbers are required. Also information about advising and mentoring should be included. This field may be the same for all courses.

• EnrollProc – (Blank) This should include data on how students get enrolled and must have a telephone number for an office that can help students with enrollment.

• EnrollSpecial – (Blank) This should include information about what type of special access to meet students’ needs, such as access to a special satellite or specialized network, is required to complete the course.

• EnrollAttend – (Blank) This should include information about whether a student needs to come to campus or other location to complete the course (reasons could include test proctoring, orientation, etc.). This may be a course by course announcement or could be designed as a general statement.

• EnrollOther – (Blank) This should include any additional helpful information for the student.



Page 17 March 2010

Sample Files

Sample of an Excel file for courses:

Sample of an Excel file for courseofferings:


Georgia Enhancements 8.0 User Documentation Campus Data Extraction Process

Page A-1 March 2010

Appendix A: Document History This section details the history of the document and updates made for each modification. Release and Date Page Update Description

Georgia Enhancements 7.12, May 30, 2008

NA Original

Georgia Enhancements 7.16 October 17, 2008

NA Corrected looping errors when pulling CLOB data from the course description in Sun Solaris environments. Fixed the ability to use many institution level codes translated to one USG level code on GTXSDAX.

Georgia Enhancements 8.0, March 31, 2010

All Updated for Georgia Enhancements 8.0 changes.


Georgia Enhancements 8.0 User Documentation Campus Data Extraction Process

Page B-1 March 2010

Appendix B: Process Flow

using the electronic campus data export process...the electronic campus data export process (zorsreb...

Documents