Transcript CCCApply Interface

Building a
CCCApply Interface


Creating a useful interface
Addressing special challenges for schools
using UNIX database servers
Rob Hagmaier, Information Services ([email protected])
A Simple CCCApply Load Process
Student enters
application on
the web
Done?
Trigger on holding table
loads applications into
Banner tables
BANNER
Some validation
of application
is performed
by XAP
Applications
are stored on
XAP servers
XAP Autodownloader is used to move apps
securely to local Windows NT database server
Database
Holding
Table
Some Windows process loads
application into database
“holding” table
WinNT
database
server
What is Missing?






“Functional”
Site-specific validation of application prior to load into
Banner tables
Duplicate checking
Mechanism to review questionable applications
“Technical”
Archive process for incoming applications
System error handling
Your UNIX database server!
Functional Improvements




Use the XAP Control Center to perform as
much validation/conversion as you can
Incorporate extensive validation of data in the
database trigger (consider MIS!)
Develop a concise plan to identify and handle
possible duplicate students
Provide a Banner form where applications that
don’t pass muster can be reviewed/corrected
Technical Improvements




Archive applications at all steps
Provide simple error recovery... once the
problem is fixed, the interface should cleanup
the backlog of applications automatically
Identify applications that should not be loaded,
and provide mechanism for users to review
problems, correct them, and load the student
Integrate your UNIX server
UNIX – What are the
Challenges?





XAP Autodownloader runs only on Windows, but your
database is on a UNIX server
How do you setup the Windows to UNIX interface so
that failures can be easily corrected?
Once the files are on your UNIX server, you need
some way to continue the process
Files need to be archived when interface is successful
Error detection and email notification?
Moving Applications to UNIX



Using XAP, you can either FTP the file, or call a
custom program when applications are downloaded
FTP by itself has no mechanism for error checking or
file archiving
Windows batch file is one solution




Need to add timestamp or “one up” number to filenames so
they don’t overwrite each other
Use FTP to move files to UNIX server
Check for FTP errors, send email as needed
Archive files to another directory when transfer is successful
A Windows Batch File
REM
call
set
set
Create a timestamp to be appended to filename
parsedate.bat
timestamp=%yy%%mm%%dd%%hh%%min%
filename=CCCApply%timestamp%.ftp.log
REM Transfer any files found to the UNIX database server
ftp -s:ftp.script our_database_server > %filename%
REM Examine FTP logfile for errors (Could be more robust)
FIND "Invalid" %filename% > nul
REM If error found, ERRORLEVEL will be 0
IF %ERRORLEVEL% == 1 GOTO SUCCESS
GOTO FAILURE
:SUCCESS
REM Email success notification (Move file to archive)
blat success.ntf -to [email protected] -subject "CCCApply download successful!"
move *.edi archive > nul
GOTO END
:FAILURE
REM Email failure notification (Do NOT move file to archive)
blat failure.ntf -to [email protected] -subject "CCCApply download failure!" -attach
%filename%
:END
Now what?


Now that your files are now on your UNIX
server, how do you load them into your
database?
Build a shell script that runs in the background
Checks “incoming” directory for new files every few
minutes throughout the day
 Uses SQL*Loader to put files into “holding” table
 Sends email if problems are encountered
 Archives files that have been successfully processed

UNIX shell script
while [ ${running} = 1 ] do
# terminate script at 11pm
currenthour=`date '+%H'`
if [ $currenthour = 23 ] then running=0 fi
#
Find and process input files
ls *.edi >> ${filelist}
while read afile
do
echo ${afile} >> ${sess_file}
echo $SATURNPW | sqlldr saturn control=cccapply_load.ctl data=${afile}
bad=${afile}.bad log=${afile}.log >>${sess_file} 2>>${sess_file}
# sqlldr error codes check
if [ $? != 0 ] then
add "An error loading ${afile} was encountered."
add "Logfile: ${afile}.log"
# send email
mailsess
fi
# move data files to archive directory
mv ${afile} archive/${afile}
done < ${filelist}
#
Wait for 5 minutes to start again
sleep 300
done
Enhanced CCCApply Load Process
Student enters
application on
the web
Some validation
of application
is performed
by XAP
XAP Autodownloader is used to move apps
securely to local Windows NT server
Done?
Trigger on holding table
loads applications into
Banner tables
BANNER
Applications
are stored on
XAP servers
Script looks for new files and uses
SQL*Loader to load applications into
database “holding” table
Database
Holding
Table
UNIX
shell script
UNIX
database
server
WinNT
server
FTP to UNIX
database server
What’s in a Trigger?



DUPLICATE CHECKING!
Additional validation required by Banner or sitespecific validation/conversion (remember MIS!)
Additional functionality
Email notifications
 Placing Signature Page holds



Robust error checking – Don’t load them if they
aren’t right
Generation of “Validation Codes”
Validation Codes




With each validation that the trigger performs, a
code should be stored that indicates if the
validation passed, or if it didn’t, why it didn’t
Warnings vs. Errors
Staff should have the capability to override
warnings, or change the data where it is in error
This means staff will need a mechanism to
review those that weren’t right, and do what is
needed to get them loaded.
CCCApply Review Form
Benefits & Feedback*

62% of applications require no intervention


Those that don’t load automatically can be reviewed/updated quickly and easily





Addresses now conform to USPS standards (thanks XAP!)
Requires less auditing of residency applications
MIS errors are more infrequent as data is validated better, reducing needed corrections
Fewer duplicate students to clean up
Faster service to students




4-5 minutes maximum review time, less than what it would take to enter it manually
Better, more consistent data


Saves time and money (no more manual entry)
Typically students can register within 24-hours of submitting an application
Students are able to do Assessment Testing and Counseling sooner
Financial Aid, EOPS and DSPS services are also available sooner
Drawback: Person that does the auditing must be very experienced
*Sources: Kris Burns, Fullerton College Registrar
Rena Negrete, Fullerton College Senior Account Technician
Summary

A successful interface requires planning and
involvement by both Technical and Functional staff







Know your assumptions
Be specific about your requirements
Avoid “project creep”
Think carefully about Duplicate Checking
Think carefully about MIS standards
Be prepared to test extensively (the XAP Beta site is a
great resource for this)
Use XAP website and staff to help, they are great!
Thanks
Bill Pearce and everyone at
Strata Information Group
who helped develop the
CCCApply Interface
Jessica Wagoner and everyone else at
XAP for developing a great website and
providing excellent documentation,
support and features
Questions?