Building a CCCApply Interface

1. Building aCCCApply Interface Creating a useful interface Addressing special challenges for schools using UNIX database servers Rob Hagmaier, Information Services (rhagmaier@nocccd.cc.ca.us)

2. 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 Trigger on holding table loads applications into Banner tables Database Holding Table WinNT database server BANNER Some Windows process loads application into database “holding” table A Simple CCCApply Load Process Student enters application on the web Done?

3. 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!

4. 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

5. 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

6. 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?

7. 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

8. A Windows Batch File REM Create a timestamp to be appended to filename call parsedate.bat set timestamp=%yy%%mm%%dd%%hh%%min% set 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 name@domain.edu -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 name@domain.edu -subject "CCCApply download failure!" -attach %filename% :END

9. 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

10. 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

11. 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 server Trigger on holding table loads applications into Banner tables Script looks for new files and uses SQL*Loader to load applications into database “holding” table UNIX database server WinNT server Database Holding Table UNIX shell script FTP to UNIX database server BANNER Enhanced CCCApply Load Process Student enters application on the web Done?

12. What’s in a Trigger? • DUPLICATE CHECKING! • Additional validation required by Banner or site-specific 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”

13. 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.

14. CCCApply Review Form

15. Benefits & Feedback* • 62% of applications require no intervention • Saves time and money (no more manual entry) • Those that don’t load automatically can be reviewed/updated quickly and easily • 4-5 minutes maximum review time, less than what it would take to enter it manually • Better, more consistent data • 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 • 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

16. 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!

17. 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 Thanks

18. Questions?