Banner Data Outside Banner

Document Sample
Banner Data Outside Banner Powered By Docstoc
					Banner Data Outside Banner


David Calow
Senior Software Engineer
david.calow@ntu.ac.uk
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.




                                             2
20 April 2012
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.



                                    3
20 April 2012
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.


                                                         4
20 April 2012
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.

                                               5
20 April 2012
Data Extracts

• Regular or occasional use.
• Generally produce a .csv or .txt file.
• One form per Banner area.




                                           6
20 April 2012
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.




                                             7
20 April 2012
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.



                                                 8
20 April 2012
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).




                                          9
20 April 2012
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.




                                          10
20 April 2012
Sample Spreadsheet

                     • Variable number of
                       CRNs.
                     • Shows “N” in row 6 to
                       indicate not to
                       import.




                                    11
20 April 2012
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.


                                                                          12
20 April 2012
SXASLST Import Errors

                        • Import Error shown.




                                       13
20 April 2012
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.




                                                                14
20 April 2012
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.




                                                              15
20 April 2012
Sample Text File

                   • 2 Sections per line –
                    – Word style required for
                      line. (FormatHeading2
                      etc.)
                    – Text to be printed.
                      (subject, student name
                      etc.)




                                     16
20 April 2012
Sample Document

                  • Initially groups by
                    subject title.
                  • Splits the list of
                    students into groups of
                    4 – or after special
                    award.




                                   17
20 April 2012
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.




                                 18
20 April 2012
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.




                                                                        19
20 April 2012
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.




                                                                   20
20 April 2012
GURVRBL – Variables Definitions

                             • This is where run-
                               time 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.




                                                21
20 April 2012
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.




                                            22
20 April 2012
GUALETR – Letter Process

                           • Combines paragraphs
                             together to form the
                             letter.
                           • Sequence number for
                             ordering the
                             paragraphs.




                                          23
20 April 2012
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.




                                                                        24
20 April 2012
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   be configurable by system users.
 –   Must   allow record selection.
 –   Must   be able to save selections.
 –   Must   produce Word standard letters.
 –   Must   be able to handle lists of data.




                                                             25
20 April 2012
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.




                                                                26
20 April 2012
SWALMRG – Stage 1

                    • Retrieving and Storing
                      searches.
                    • Selection Criteria
                      change depending on
                      “Level”.
                    • Filter used for further
                      selection (user
                      defined).




                                     27
20 April 2012
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.




                                     28
20 April 2012
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.




                                           29
20 April 2012
SWALMRG – Stage 3

                    • Letter text entry.
                    • Formatting available:
                     –   New Page
                     –   Right Justified
                     –   Bold
                     –   Italics
                    • Database links.
                     – Tables
                    • Database tokens.
                     – Fields
                    • Lists of data (+)




                                           30
20 April 2012
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.




                                       31
20 April 2012
Letter Output

                • Variables added during
                  processing.
                • List of visits.




                                    32
20 April 2012
Word Template

                • Inserts text file
                • Find and Replace
                • Saves final document.




                                  33
20 April 2012
Online Payments Data Transfers

 Online Enrolment,
  Online Accom.

                     Payment Provider
                     (WPM Education)

  Sports Channel                                Immediate
  (& Graduation,                             Acknowledgement
 cashless campus)



                               Overnight                     Updates
    Staff Channel                                          Banner/NTU
      (Finance)               Confirmation
                                                            Systems


                                                Updates
                                              Banner/NTU
                                               Systems

                                                               34
20 April 2012
Online Enrolment

                   • Final enrolment page
                     for student prior to
                     payment.




                                  35
20 April 2012
Online Accommodation

                       • Final accommodation
                         page for student prior
                         to payment.




                                       36
20 April 2012
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.




                                                                      37
20 April 2012
Immediate Acknowledgement

• Every 5 minutes.
• Some database updating.
• Allow “instant” record of reservation/transaction.




                                                       38
20 April 2012
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.




                                                                 39
20 April 2012
Overnight Confirmation

• Nightly process.
• Back up in case of Immediate Acknowledgement errors.
• Further payment detail.
• Allows interface to Finance.




                                                         40
20 April 2012
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.




                                                                        41
20 April 2012
Any questions?




                 42
20 April 2012

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:5
posted:4/21/2012
language:English
pages:42