Transcript Banner Data Outside Banner - SunGard Higher Education

Banner Data Outside Banner
David Calow
Senior Software Engineer
[email protected]
What we will cover today • When we export data from Banner.
• When we import data to Banner.
• Why we use these methods.
• Some detail of how we transfer the data.
• What technologies we use.
• The bits that didn’t work.
17 July 2015
2
What we will cover today • NTU Banner Reports.
• Data Extracts.
• Exports to Microsoft Excel.
• Imports from Microsoft Excel.
• Exports to Microsoft Word.
• HTML & Word Exports.
• User Defined Letters.
• NTU Mailmerge.
• Online Payments Data Transfers.
17 July 2015
3
NTU Banner Reports
• Oracle Reports.
• Main method of reporting used across the university.
• Covers all areas of our Banner system:–
–
–
–
–
–
Admissions.
Accommodation.
Enrolment.
Placements (VOCAP).
Exam Results.
Graduation.
• Used for both reports and simple letters.
• Approximately 100 Pre-defined Reports.
• Run via GJAPCTL.
17 July 2015
4
Process Submission Form - GJAPCTL
• Parameters match
PARM99 values in the
report.
• Report specific values
available – Lookups from STVxxxx.
– Freetext fields.
• Amended by NTU.
• New validation form
for report specific
values – Yes/No.
– Include/Exclude etc.
• Easy and flexible to
use.
17 July 2015
5
Data Extracts
• Regular or occasional use.
• Generally produce a .csv or .txt file.
• One form per Banner area.
17 July 2015
6
Sample Data Extract Form (SWADEXP)
• This one produces a
sequence listing for
conferment ceremony
programme data.
• Uses client_text_io to
write to client PC /
network drive.
• File location specified
using Oracle’s
client_get_file_name.
17 July 2015
7
Extracts to Microsoft Excel
• Fully integrated with Excel –
–
–
–
Open an Excel session
Write the data to a spreadsheet
Save the data (PC or network)
Make Excel visible to the user
• Exports (and imports) use client_ole2.
• Simpler text format for less advanced PCs –
–
–
–
–
Creates a text file (.csv)
Writes the data to the file.
Save the data (PC or network)
User can open file using Excel as before.
• Uses client_text_io.
17 July 2015
8
Forms Developer Sample
• Requires Webutil
library attaching and
object group
referencing.
• Creates a block and
canvas within the
form.
• Only since Banner 7
(Internet Native
Banner).
17 July 2015
9
Excel Extract (SXASLST)
• Creates spreadsheet
for marking results.
• Deals with a variable
number of Master and
Assessment CRNs.
• Works through the
screen a record at a
time.
• All performed from a
button on the form.
17 July 2015
10
Sample Spreadsheet
• Variable number of
CRNs.
• Shows “N” in row 6 to
indicate not to
import.
17 July 2015
11
Import from Excel
• Imports results data back from spreadsheet into Banner.
• Only imports required data (column x = “Y”).
• Performs validation checks on data prior to going into Banner–
–
–
–
–
–
–
–
–
–
ERROR: Spreadsheet Sheet 1 does not contain information.
ERROR: Spreadsheet does not relate to current modules / terms displayed.
WARNING: MRN not in exported list (Not Imported).
WARNING: MRN 99999 = N (No Import).
ERROR: Invalid Student ID
ERROR: Duplicate Student ID
ERROR: Student Name mismatch
Inactive or Invalid Grade Code
Unable to overwrite value (MRN 999)
• Produces a window in the form to display import success or failure.
17 July 2015
12
SXASLST Import Errors
• Import Error shown.
17 July 2015
13
Exports to Microsoft Word
• First used when producing Heads Book for degree ceremonies.
• Required more flexibility and formatting than Oracle Reports could
provide.
• Needed to compare current record with previous record for
formatting.
• Initial version formatted each line as it was sent over to Word –
– Memory problems (kept trying to save in background).
– Pitifully slow (10 minutes).
– Locked up the PC.
• New version writes style alongside the text.
17 July 2015
14
Exports to Microsoft Word (continued)
• Creates a simple text file (.txt) using client_text_io.
• Using a Word Template (.dot) applies formatting to the imported
text file.
• Finally, saves the new formatted document as a .doc file.
17 July 2015
15
Sample Text File
• 2 Sections per line –
– Word style required for
line. (FormatHeading2
etc.)
– Text to be printed.
(subject, student name
etc.)
17 July 2015
16
Sample Document
• Initially groups by
subject title.
• Splits the list of
students into groups of
4 – or after special
award.
17 July 2015
17
Word Template
• Autonew() macro within
template imports the
text file.
• Performs all possible
find and replace
operations on it
• Saves new document.
• Considerably quicker
– 4 seconds for 379 names.
17 July 2015
18
HTML and Word Exports
• Required a method of displaying large text items and formatted tables etc.
• First came about because of Paging issues – Oracle reports could split a line of text across pages e.g.
• Outputting to an HTML file for Internet Explorer was little better.
• We then decided to open the HTML file with Microsoft Word.
– Worked with large text fields
– Maintained formatting (using HTML tags)
– Kept paragraphs on a page successfully.
• Performed using standard client_text_io.
• Had to create HTML tags (<doctype>, <head> etc.) within text file.
17 July 2015
19
User Defined Letters
• Implementation of standard Banner letter generation.
• Originally used for Confirmation Letter for new students.
• Now used elsewhere within Banner – primarily within Placements system
(VOCAP).
• Uses GUAPARA, GUALETR and GLRVRBL.
• Additional NTU functionality to extract data from Banner.
• Functionality within report and database package.
17 July 2015
20
GURVRBL – Variables Definitions
• This is where runtime variables
(*YEAR) are defined.
• Notice &PIDM and
&TERM
– Defined by calling report
for each student.
– Format trigger in report
sets these variables.
– No practical limit to the
number of variables
used.
– The SELECT, FROM and
WHERE are combined to
return the value.
17 July 2015
21
GUAPARA – Paragraph setup
• Next step is to set up
each paragraph.
• Each text item (text
or variable) to go on a
separate line.
• Notice *YEAR defined
previously.
17 July 2015
22
GUALETR – Letter Process
• Combines paragraphs
together to form the
letter.
• Sequence number for
ordering the
paragraphs.
17 July 2015
23
GURVRBL – Variables Usage
• Report contains srw.set_field_char in the format trigger to produce each
paragraph.
– srw.set_field_char(0, szre027.create_para(:gubletr_para_code))
• Variable values (&PIDM, &TERM) stored in associative array within a
database package.
• EXECUTE IMMEDIATE sql_command INTO retval.
17 July 2015
24
NTU Mailmerge
• First used within Placements.
• Originally was intended to be a standard Word Mailmerge.
• Word could not easily work with lists.
• Original (non Banner) system could.
• Implement a local in-house mailmerge.
• Following requirements defined:
–
–
–
–
–
Must
Must
Must
Must
Must
be configurable by system users.
allow record selection.
be able to save selections.
produce Word standard letters.
be able to handle lists of data.
17 July 2015
25
SWALMRG – New NTU Form
• Works in 4 stages:
–
–
–
–
Retrieving and Saving stored selections.
Show selected output records.
Text editing section combined with fields from database.
Output to text document.
• Output text document to be processed by Word on user’s PC.
17 July 2015
26
SWALMRG – Stage 1
• Retrieving and Storing
searches.
• Selection Criteria
change depending on
“Level”.
• Filter used for further
selection (user
defined).
17 July 2015
27
SWALMRG – Stage 2
• Sample query run to
return the data.
• Query dependent
upon Level of report.
• Records can be
deleted from list if not
required.
17 July 2015
28
SWALMRS – Link and Token Setup
• Setup for different
levels.
• Defines the FROM
and WHERE clauses.
• List of tokens for
different available
fields.
• Requires some SQL
knowledge.
• Defines the next
stage.
17 July 2015
29
SWALMRG – Stage 3
• Letter text entry.
• Formatting available:
–
–
–
–
New Page
Right Justified
Bold
Italics
• Database links.
– Tables
• Database tokens.
– Fields
• Lists of data (+)
17 July 2015
30
SWALMRG – Stage 4
• Letter output.
• Creates text file on PC
/ Network.
• Text file then used by
Word template.
• Population process
uses REF Cursor and
cursor variable to find
field.
17 July 2015
31
Letter Output
• Variables added during
processing.
• List of visits.
17 July 2015
32
Word Template
• Inserts text file
• Find and Replace
• Saves final document.
17 July 2015
33
Online Payments Data Transfers
Online Enrolment,
Online Accom.
Payment Provider
(WPM Education)
Sports Channel
(& Graduation,
cashless campus)
Staff Channel
(Finance)
Immediate
Acknowledgement
Updates
Banner/NTU
Systems
Overnight
Confirmation
Updates
Banner/NTU
Systems
17 July 2015
34
Online Enrolment
• Final enrolment page
for student prior to
payment.
17 July 2015
35
Online Accommodation
• Final accommodation
page for student prior
to payment.
17 July 2015
36
Payment Types
• 3 types of payment –
• Immediate payments – One-off payment for tuition/accommodation deposits.
– Tuition/Accommodation fees.
– Payment for Graduation, student gym membership, cashless campus.
• Instalments – For Tuition and Accommodation fees.
– Used by students/parents wishing to pay by debit/credit card.
– Any debt over £150
• split into 3 pre-defined amounts.
• Direct debits – User for Tuition/Accommodation fees.
17 July 2015
37
Immediate Acknowledgement
• Every 5 minutes.
• Some database updating.
• Allow “instant” record of reservation/transaction.
17 July 2015
38
How does it happen?
• Process run at 5 minute intervals.
• Takes data passed by WPM into holding table.
• Trigger then acts on this data depending on transaction type
– Accommodation
– Graduation
• This prevents duplicates for rooms or seats.
• Acknowledgements are logged at NTU.
17 July 2015
39
Overnight Confirmation
• Nightly process.
• Back up in case of Immediate Acknowledgement errors.
• Further payment detail.
• Allows interface to Finance.
17 July 2015
40
How does it happen?
• Process run overnight.
• Loads data passed by WPM into holding tables using SQL*Loader.
• Database package then processes transaction – Enrolment (record made of payment setup allowing student to be FE)
• Transaction files produced for finance / sports department.
17 July 2015
41
Any questions?
17 July 2015
42