SETA East Conference

1. SETA EastConference Address Verification in Banner Kevin Runner Monday, September 19, 2005 Albany, NY

2. Session Rules of Etiquette • Please turn off your cell phone/beeper • If you must leave the session early, please do so as discretely as possible • Please avoid side conversation during the presentation Thank you for your cooperation!

3. Introduction • Overview of Address Verification • Benefits of Real-time Address Verification, including Return On Investment • Live Demos of Address Verification in Banner • Technical discussion of architecture and implementation • Integration steps to get Real-time and Batch Address Verification within Banner Forms and Web Self-service

4. Topics of Discussion • Address Verification Goals • Address Verification Definitions • Integration Approaches • CLEAN_Address Overview • Architecture • Integration with Banner • Reporting • Maintenance • Pricing • Future Enhancements

5. Address Verification Goals - the Benefits 1/3 • Improved data quality for all contact information • Addresses • Telephone Numbers • Names • Email Addresses • Speed up data entry time with fewer errors • Allow end-user self-service updates on the Web • Reduce centralized data entry • Reduce or eliminate undelivered/returned mail • Reduce costs of mailing • Faster mail delivery

6. Address Verification Goals - the Solution 2/3 • 100% integrated complete data quality solution • Address Verification, Standardization, Correction • Telephone Number Checking and Area Code Splits • Name Validation • Email Validation • ZIP Code and County table updates • Real-time notification of bad addresses • Integrated batch verification and reporting • Seamless user interface with no additional steps • No end-user training

7. Address Verification Goals - the Solution 3/3 • Minimal installation & integration effort • Fully pre-developed and supported solution • Parameter driven business rules • Pre- and Post- user exits for custom business rules • Table driven error messages • All integration source code • FREE Unlimited Technical Support • FREE Trial Evaluation • Easy subscription update process • Choice of Monthly or Bimonthly updates • Low annual subscription price

8. Who can benefit from improved Data Quality? • Recruitment / Admissions • Student Records • Alumni • Human Resources • Paycheck and benefits mailings • Accounts Receivable • Valid addresses for credit card transactions • Accounts Payable • Vendor addresses

9. 1-10-100 Data Quality Rule • It costs 1 unit to fix a bad address at the point of entry when customer is present • It costs 10 units to fix a bad address that is saved in the database and identified via batch • Running reports, proactive phone calls, user’s time • It costs 100 units to fix a bad address that was sent mail and the mail was returned • Processing returned mail, contacting customer via phone/email, updating address, resending mail • In general, dealing with exceptions

10. Case Study – ROI College • College with 5000 total students • 100,000 mailings per year • Grades: 15,000 Admissions: 30,000 • Alumni: 50,000 Miscellaneous: 5,000 • Assume best case of 15% bad addresses • 15,000 undeliverable mail pieces • 15K x $0.37/letter = $5,550 postage alone • Paper and printing = $5000 • Employee time dealing with returned mail: $9450 • Total cost of bad addresses: $20,000

11. Address Verification Definitions 1/3 • CASS – Coding Accuracy Support System • USPS Certification for a software program used for cleaning up, correcting, and standardizing an address • Ensures the address will be corrected in compliance with USPS guidelines • Prerequisite to Presorting for getting postage rate discounts • DPV – Delivery Point Validation • Provides a deliverable status down to the house number and suite/apartment number • Identifies missing and invalid apartment numbers

12. Address Verification Definitions 2/3 • GeoCoding • Identifies the Latitude and Longitude coordinates for an address • Several levels • 5-digit ZIP Code level • 9-digit ZIP+4 level • Near roof-top level • Used to compute distance, bearing, and perform radial searches • I.e. How many students live within 5 miles, 10 miles of the university?

13. Address Verification Definitions 3/3 • PAVE – Presort Accuracy Validation and Evaluation • USPS certification for getting the best postage rate discounts for an address list • Requires address to be CASS certified before presorting • NCOA – National Change Of Address • Provides move information for either 18 months (NCOALink) or 4 years • Primarily an outsourced service with export/import of your address data • 18-20% of the U.S. moves every year

14. Real-Time vs. Batch Verification • On average, databases without address verification will contain 15-22% undeliverable addresses • Batch Address Verification • Can correct a lot of these bad addresses • Will identify all of the bad records with a status code • Real-Time Address Verification • Corrects and validates addresses at point of entry • Empowers data owner to correct their own address • Notifies user of a bad address so they can confirm with the customer (student, employee, vendor) • Can reduce 15-22% down toward 0%

15. Integration Approaches 1/2 • Batch Update only • Verification program is outside of the database • Export addresses nightly, run through stand alone program, import to staging table, update address • Real-Time verification with Popup Window • Popup window where user enters address • Address verification can be bypassed by user • More complicated user interface • Extra steps, not seamless with application • Incorrect “valid” address could be selected • Now you have 2 problems: • an incorrect but valid address • you can’t identify it in the database

16. Integration Approaches 2/2 • Manual Real-Time Verification • Use a free web site to verify each address www.usps.govwww.clean-address.com • More steps, double entry, easily bypassed • Real-Time Seamless Integration • Address verification is done automatically without user intervention • User is notified real-time only if there is a bad address • No training is required • Integrated Batch Verification • Addresses are updated in the database directly • No exporting / importing

17. Demos • Banner Oracle Forms Integration • Banner Web Self-Service Integration • Batch Address Verification

18. CLEAN_Address Overview • CLEAN_Address is a 100% integrated solution for Address Verification in Banner • Real-Time Seamless Integration • Banner Oracle Forms / INB - Internet Native Banner • Web Self-Service • Integrated Batch Verification • Address, Telephone, Name, Email validation • Subscription updates for USPS data • Simple step-by-step integration guide in an easy copy-paste format

19. CLEAN_Address Architecture • CLEAN_Address has an Enterprise-Centralized Architecture • CLEAN_Address server connects to database on back-end • Clients do not connect to CLEAN_Address server directly • Enterprise Architecture • Seamless Fail-over • Redundancy • Load Balancing • Scalability • SOA – Service Oriented Architecture • Centralized Architecture • Multiple databases can be supported from one CLEAN_Address server installation • I.e. Development, Test, Production

20. Enterprise Architecture CLEAN_Address Server Oracle Database - Banner CLEAN_Address Server • Seamless Fail-over • Redundancy • Load Balancing • Scalability

21. Centralized Architecture Oracle Database - PeopleSoft Oracle Database - Development CLEAN_Address Server CLEAN_Address Server Oracle Database - Alumni Oracle Database - Production • Multiple databases can be supported from one CLEAN_Address server installation • SOA - Service Oriented Architecture • – Address Verification Servers are supplied as a service to any Oracle database that needs them

22. Database Architecture Oracle Database Banner Forms / Internet Native CLNADDR BANNER BWGKOADR Web Self-service CLEAN_Address_Banner_UE SPRADDR CLEAN_Address_Banner SPRTELE CLEAN_Address Server CLEAN_Address.Verify GTVZIPC STVCNTY CLN_Postal_Codes

23. CLEAN_AddressIntegration with Banner • CLEAN_Address provides several integration components for Banner • CLEAN_Address_Banner PL/SQL Package • Oracle Forms integration code • Web Self-Service integration code • Open PL/SQL interface for non-Banner systems (PeopleSoft, SAP, Oracle, custom) • Enables real-time and batch verification in any application that uses an Oracle database • Integration can be done in a few hours

24. CLEAN_Address_Banner Package 1/2 • PL/SQL stored package in the database • Integration code between Banner and CLEAN_Address package • Contains several procedures: • Batch Address Verification • Real-Time Address Verification APIs • Used for integrating into Forms and Web Self-Service • Batch / Real-time Telephone Verification • ZIP Code and County table synchronization • Error code description and help

25. CLEAN_Address_Banner Package 2/2 • Enforces the Banner Address “rules” • All business rules are stored centrally in this package • Pre- and Post- Verification User Exits for custom rules • Optional parameters – expand street suffix • 30 character Street limitation • 20 character City limitation • Puts apartment on same line as address • Assigns correct county code (FIPS) • Appends ZIP+4 to ZIP Code • Stores verification date and address error code

26. CLEAN_Address_Banner Batch Verification Procedure /****************************************************************************************** * Procedure Name : Batch_Verify * * Scope : PUBLIC * * Description : Batch Verify the addresses in the Banner Address table (SPRADDR) * * fn_max_verify * - maximum number of address to verify * * fb_update * - TRUE = update the record and show results * - FALSE = don't update, only show results * * fb_only_unverified * - TRUE = Only update unverified records * - FALSE = Update ALL records * * fv_address_type * - Banner Address Type - only verify this type * - NULL = to verify all address types * * fn_days_back * - Verify Addresses that were updated/created this many days back * - Note: You can enter fractional days such as 0.25, 0.5, etc * - NULL = all addresses ******************************************************************************************/PROCEDURE Batch_Verify ( fn_max_verify INNUMBERDEFAULT 1000000,fb_update INBOOLEANDEFAULTTRUE,fb_only_unverified INBOOLEANDEFAULTTRUE,fv_address_type INVARCHAR2DEFAULTNULL,fn_days_back INNUMBERDEFAULTNULL);

27. CLEAN_Address_Banner Real-Time Procedure APIs /****************************************************************************************** * Procedure Name : Verify * * Scope : PUBLIC * * Description : Verify a single address for Banner * * This call wraps the Address record implementation and excepts pure * VARCHAR2 IN/OUT parameters. * * address_date_and_error_code - YYYYMMDD:<error_code> * - address verified date concatenated with error code, separated by : ******************************************************************************************/PROCEDURE Verify ( address_1 INOUTVARCHAR2/* SPRADDR_STREET_LINE1 */,address_2 INOUTVARCHAR2/* SPRADDR_STREET_LINE2 */,address_3 INOUTVARCHAR2/* SPRADDR_STREET_LINE3 */,city INOUTVARCHAR2/* SPRADDR_CITY */,state INOUTVARCHAR2/* SPRADDR_STAT_CODE */,postal_code INOUTVARCHAR2/* SPRADDR_ZIP */,county_code INOUTVARCHAR2/* SPRADDR_CNTY_CODE */,country_code INVARCHAR2/* SPRADDR_NATN_CODE */,delivery_point OUTNUMBER/* SPRADDR_DELIVERY_POINT */,check_digit OUTNUMBER/* SPRADDR_CORRECTION_DIGIT */,carrier_route OUTVARCHAR2/* SPRADDR_CARRIER_ROUTE */,address_error_flag OUTVARCHAR2/* SPRADDR_REVIEWED_IND */,address_date_and_error_code OUTVARCHAR2/* SPRADDR_REVIEWED_USER */,address_error_text OUTVARCHAR2,fv_plsql_error OUTVARCHAR2);

28. Banner Oracle Forms Integration • Simple 4-Step process • Open form • Add two (2) Datablock Items • Copy-Paste Verify_Address Procedure • Copy-Paste When-Validate-Item trigger • Compile form and repeat for other forms

29. Banner Web Self-Service Integration /* BEGIN: CLEAN_Address Changes - call the address verify procedure */-- Initialize the SPRADDR record g_SPRADDR_rec := g_spraddr_rec_NULL;-- Assign the input to the SPRADDR record g_SPRADDR_rec.spraddr_street_line1 := str1; g_SPRADDR_rec.spraddr_street_line2 := str2; g_SPRADDR_rec.spraddr_street_line3 := str3; g_SPRADDR_rec.spraddr_city := city; g_SPRADDR_rec.spraddr_stat_code := stat; g_SPRADDR_rec.spraddr_zip := zip;-- Verify the address CLEAN_Address_Banner.Verify_SPRADDR_Record( f_SPRADDR_rec => g_SPRADDR_rec,address_error_text => gv_address_error_text,fv_plsql_error => gv_plsql_error);-- Show the address errorif NVL(upper(gv_address_error_text),'NO ERROR')!='NO ERROR'then msg_text := g$_nls.get ('BWGKOAD1-0550','SQL','%01%'||gv_address_error_text||'%02%', msg_text, HTF.br);endif;-- Remap the output to the address fields str1 := g_SPRADDR_rec.spraddr_street_line1; str2 := g_SPRADDR_rec.spraddr_street_line2; str3 := g_SPRADDR_rec.spraddr_street_line3; city := g_SPRADDR_rec.spraddr_city; stat := g_SPRADDR_rec.spraddr_stat_code; zip := g_SPRADDR_rec.spraddr_zip; cnty := g_SPRADDR_rec.spraddr_cnty_code;/* END: CLEAN_Address Changes - call the address verify procedure */ • 2 simple package modifications: • BWGKOGAD • BWGKOADR

30. Reporting • Several standard reports are supplied with the CLEAN_Address for Banner solution: • Address Error Summary • shows a summary of all address errors in SPRADDR • Address Error Detail • shows the detail of all addresses with deliverability errors • Duplicate Addresses • shows duplicate addresses after all records have been standardized • Compare Record Changes • shows before and after addresses after a batch verification

31. Address Error Summary Report Predictive Error Summary Report from Batch Verify procedure without updates: ----------------------------- ADDRESS ERROR SUMMARY -----------------------------Code Description Count---------- -------------------------------------------------- ----------. No Error 449U Unknown Street 16R Range Error 9N1 Suite/Apt Missing 5CC Suite/Apt Invalid 12M3 Street Number Invalid 7M Multiple Matches 1Z Invalid ZIP/Postal Code 1---------- -------------------------------------------------- ----------Total: 500 SQL Report for Address Error Summary “after” a batch update:

32. Compare Record Changes Report

33. Maintenance • A bimonthly subscription service keeps the USPS data updated every 2 months • A monthly subscription option is also available • About 1.5 GB of USPS data is shipped on 3 CDs • Zero downtime when applying updates • Existing address records should be verified at least every 6 months using the Banner batch address verification procedure • ZIP Codes, ZIP+4, and street names can change periodically

34. Pricing • Based on Address volume and options • Real-time (new) addresses per year • Number of existing addresses • 15% Educational Discount • For most small to mid-size institutions this is under $5,000 for an annual subscription • Includes 6 bimonthly subscription updates • Includes FREE technical support and maintenance • Includes all Banner Batch and Real-time interface packages and support

35. Future Enhancements • Global Web Service • Using the same CLEAN_Address server installed locally, global address requests will be made to our Web Service • Supports 240 countries and territories • Pricing based on smaller volume per year • I.e. 500, 1000 transactions per year • Real-time NCOA • National Change of Address on demand

36. Summary • Address verification will improve the quality of your database and save time and money by reducing or eliminating undeliverable mail • CLEAN_Address provides 100% seamless real-time and batch address verification for SCT Banner systems in less than a day • Priced so every college and university from small to large can afford it

37. Questions and Answers

38. Presenter Information Kevin Runner Runner Technologies, Inc. 6001 Broken Sound Pkwy NW Suite 620 Boca Raton, FL 33487-2766 561-395-9322 / 877-784-0003 krunner@RunnerTechnologies.com www.RunnerTechnologies.com