Exporting Data for use with Bulk Mail, Address Certification, and other External Applications

1. Exporting Data for use with Bulk Mail, Address Certification, and otherExternal Applications By Indiana University of Pennsylvania Mary Mosholder

2. Presentation Goal To review the use of Banner to export data for use in a bulk mail system, an address verification system, and other external applications

3. About IUP • 14,000 students; 1,800 employees • Largest Member, SSHE • 3 campuses; 1 center; 1 academy • Doctoral I • Clock-hour programs

4. Banner at IUP • Implemented five baseline modules and three “Web For” products 1998-2000 • Banner 5.x (soon to be Banner 6) • Oracle 9i, OAS (soon to be 9IAS) • Sun Solaris

5. The Challenge Bulk Mail – Materials being sent out in a large quantity. Needed a procedure that is faster than using labels generated from Banner Letter Generation Address Validation – Incorrect addresses caused returned mail, and wasted postage. Needed a procedure to correct addresses

6. The Challenge Other External Applications – Some users use external (non-Banner) applications to evaluate data. Needed data from Banner in a format useable by other software applications and trackable within Banner

7. Solution Building Blocks Created Banner processes using Pro*C, and integrated existing letter generation processes

9. Topics • Bulk Mail • Population Selection Data Extract • Address Validation • CASS (Coding Accuracy Support System) • CASS process at IUP • Exporting data with Letter Generation

10. Bulk Mail • A mass mailing of over 200 Items • University magazine • Brochures for prospective students • Postalsoft DeskTop Mailer by First Logic sprays the addresses and bar code onto the mail piece • Printer connected to DeskTop Mailer located in Post Office that sends out bulk mailings.

11. Bulk Mail • One set of fields for all bulk mail • The addresses are saved in a tab delimited text file • This text file can be created through any software that allows users to save the data as tab delimited • The mail pieces must be sent to the Post Office with the DeskTop Mailer printer

12. Bulk Mail • The data file is placed in a “Bulk Mail” folder on the network • The “Bulk Mail” folder is purged on a regular basis • The bulk mail person receives an email when the file is ready • The data file is imported into DeskTop Mailer

13. Bulk Mail • The addresses are checked for validity (CASS certified addresses) • Correct addresses are printed on the mail pieces

14. Population Selection Data Extract • Users had existing population selections that they wanted to use for bulk mailings • Created a Pro*C program (GWPBULK) that pulls the address data for the people that are in the results of a population selection, and saves the data in a tab delimited file on the network • The addresses are pulled based on the address type parameters for GWPBULK

15. Population Selection Data Extract • GWPBULK Parameters: • Application Code • Population Selection ID • Creator ID • User ID • Address Type 1 • Address Type 2 • Address Type 3 • Address Type 4

16. Population Selection Data Extract • The population selection is pulled using the GLBDATA process • The results are reviewed on the GLIEXTR form, and/or updated on the GLAEXTR form. • The IUP created data extraction Banner process GWPBULK is run • The file can then be copied to the “Bulk Mail” folder when it is time to print

17. CASS • United States Postal Service (USPS) Coding Accuracy Support System Address Certification increases the accuracy and speeds the delivery of the mail pieces • Uncertified Addresses take longer be delivered to, or the mail piece may be sent back to IUP. • Corrects address spellings and abbreviations to the USPS standard

18. CASS • Can earn the best postal discounts when you process your address list using the most current information • USPS Upgrades are sent to IUP on a regular basis. • Available as yearly or bimonthly • The upgrades are applied to all the computers that run the software

19. CASS • Supplies Error codes for addresses that could not be corrected • E101, E213, E214, and E501 are all related to foreign addresses • E302 No primary address line found • E412 Street name not found in directory • E413 Possible street name matches too close to choose one (multiple street names found) • E420 Primary range is missing (missing street number)

20. CASS • E421 Primary range is invalid for the street/route/building (invalid street number) • E422 Predirectional needed, input is wrong or missing • E423 Suffix needed, input is wrong or missing • E427 Postdirectional needed, input is wrong or missing • E429 Bad city, can’t select an address match (ZIP code is valid for more than one city and each city has this street) • E600 Marked by USPS as unsuitable for delivery of mail (inactive address according to Post Office)

21. CASS • IUP certifies the Banner addresses twice a year (3rd week into Fall and Spring semesters) • A Pro*C program (SWPCASS) was created that can extract the addresses from population selection results, or update the Banner address information from a specific file • When updating the Banner addresses some CASS fields are truncated

22. CASS • SWPCASS parameters: • Mode (E = Extract data, M = Modify Addresses) • Address Type • Application Code • Population Selection ID • Creator ID • User ID

23. CASS process at IUP • A population selection of registered students is pulled using GLBDATA in the Banner client • SWPCASS is submitted in the Banner client with a mode of E, for the address type, and for the population selection • The data file is then imported into the CASS database portion of DeskTop Mailer

24. CASS process at IUP • The Correct Addresses process within the CASS database is ran • The addresses are then exported to a specific file on the IUP network • This file contains the error code from CASS for any addresses that it could not fix

25. CASS process at IUP • SWPCASS is run with a mode of M • Updates any addresses in Banner to the CASS corrected address within the file • The file is then imported into a Microsoft Access database • A query is ran to separate the international students addresses that had an error. These addresses are sent to IUP’s International Affairs office

26. CASS process at IUP • A query is run to select the remaining addresses that have error codes • The results of this query are divided up and sent to members of the Address Resolution Team (ART) • Members of this team have two weeks to correct as many addresses on their list as they can

27. CASS process at IUP • After the ART corrects the addresses that it can, then SWPCASS is run again with the mode of E, for the address type, and for the population selection • The data file is then imported into the CASS database portion of DeskTop Mailer • The Correct Addresses process within the CASS database is ran

28. CASS process at IUP • The addresses are then exported to a file on the IUP network • SWPCASS is run with a mode of M. • The file is then imported into a Microsoft Access database • A query is ran to separate the international students, and the remaining students with an error code are merged to an incorrect address letter

29. CASS process at IUP • A query is ran to pull the registered students that do not have an active address • A list of international students from these results is sent to the International Affairs office • The remaining students are merged to a no address letter • The no address and incorrect address letters are distributed to the student’s advisor

30. CASS process at IUP • The student receives their letter when they meet with their advisor to receive their Time Appointment for scheduling

31. CASS process at IUP • Last Spring (2003) • 178 addresses for the ART to correct • 77 letters sent • 29 Incorrect addresses • 48 no address • 16 International students with incorrect addresses • 19 International students without an address

32. CASS process at IUP • This Fall (2003) • 216 addresses for the ART to correct • 168 letters sent • 121 Incorrect addresses • 47 no address • 419 International students with incorrect addresses • 25 International students without an address

33. Exporting Data with Letter Generation • Export data for an external application, but also track who was exported on SUAMAIL form within Banner • SUAMAIL is updated by the GLRLETR letter generation process • Admissions sends a file of recruits that are juniors in high school that have the College of Business on their recruit record to the College of Business. The College of Business imports this file into Microsoft Access.

34. Exporting Data with Letter Generation • A population selection was created • A letter code was created for the file within Banner • Variables were created for any new fields • A new paragraph was created • The variables were added to a paragraph • Paragraph linked to the letter code.

35. Exporting Data with Letter Generation • The external application needed a header line in the data file, and the fields to be tab delimited • A Microsoft Word merge document was created so that when merged the fields names are listed at the top, and the rest of the document contains the data with a tab between the each field

36. Exporting Data with Letter Generation • Word merge header line: {IF {MERGEREC} = 1 “ field names with a tab between them and the merge fields” “the merge fields with a tab between them”}

37. Exporting Data with Letter Generation • A web letter entry was set up • IUP has a web letter generation system that takes the prompts, that are stored in a table and displayed on the web, and runs the Banner letter generation processes (GLBDATA, GLBLSEL with the repeating pending letter prompt skipped, and GLRLETR). It also sends the letter data to the ProdPC program that does the merge through an executable

38. Exporting Data with Letter Generation • The user submits the letter generation job so that the resulting document is stored on their local network drive, or emailed to them • Once the letter is complete, the user opens the document, performs a Save As, and saves it to whatever file extension is needed for the external program

41. Conclusion The bulk mail, and address certification programs needed to extract data from Banner. Pro*C programs that use population selections were created for the transfer of data from one application to another. When a SUAMAIL record is needed for data extracted to another application, a letter generation job can be set up with a Word merge document that produces a document in the format needed

42. Questions Mary MosholderTechnology Services CenterApplication Development G-14 Stright Hall, IUP Indiana, PA 15705(724) 357-4721msinger@iup.edu