Attention Committee Members: The following presentation came from the Banner group at CCC. Check it out! Perhaps there are topics we can use from it. Slides 38 - PowerPoint PPT Presentation

1 / 55
About This Presentation
Title:

Attention Committee Members: The following presentation came from the Banner group at CCC. Check it out! Perhaps there are topics we can use from it. Slides 38

Description:

Title: Slide 1 Author: rlopes Last modified by: Vicki Larabell Created Date: 3/17/2005 1:40:15 AM Document presentation format: On-screen Show Company – PowerPoint PPT presentation

Number of Views:57
Avg rating:3.0/5.0
Slides: 56
Provided by: rlopes
Learn more at: http://www2.oakland.edu
Category:

less

Transcript and Presenter's Notes

Title: Attention Committee Members: The following presentation came from the Banner group at CCC. Check it out! Perhaps there are topics we can use from it. Slides 38


1
Attention Committee MembersThe following
presentation came from the Banner group at CCC.
Check it out! Perhaps there are topics we can use
from it.Slides 3855 contain samples of the
error messages Robin found when she tested the
software here.
2
ImplementingCLEAN_AddressAddress
ValidationSoftware
3
Topics
  • CLEAN_Address Overview
  • Benefits and features of CLEAN_Address
  • Error Messages
  • Demo/Examples

4
CLEAN_Address Overview
  • CLEAN_Address is a 100 integrated solution for
    Address Verification in Banner
  • Real-Time Seamless Integration
  • Banner Forms / INB (Internet Native Banner)
  • Web Self-Service
  • Integrated Batch Verification
  • Subscription updates for USPS data
  • Simple step-by-step integration guides

5
Address Validation - Benefits
  • Improved data quality for contact information
  • Addresses
  • Telephone Numbers
  • 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
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, users
    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

7
Hypothetical Cost Savings
  • 100,000 mailings per year
  • Assume best case of 15 bad addresses
  • 15,000 undeliverable mail pieces
  • 15K x 0.39/letter x 2 deliveries 11,700
    postage
  • Paper and printing x 2 5,000
  • Employee time dealing with returned mail 8,300
  • Total cost of bad addresses 25,000
  • Additional Postage Savings 16,000
  • First Class 0.39 postage down to 0.23 Standard

8
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

9
Integration Approaches
  • 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, mouse
    usage
  • Incorrect valid address could be selected
  • Now you have 2 problems
  • an incorrect but valid address
  • you cant identify it in the database

10
Integration Approaches
  • Manual Real-Time Verification
  • Use a free web site to verify each address
  • www.usps.gov www.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

CLEAN Address
CLEAN Address
11
Integration with Banner
  • CLEAN_Address provides several integration
    components for Banner
  • CLEAN_Address_Banner PL/SQL Package
  • Batch Address Verification for 10 address tables
  • Banner 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
  • Flat Text file address verification (CLEANFile)
  • Integration can be done in a few hours

12
CLEAN_Address_Banner package
  • PL/SQL stored package in the database
  • 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
  • Rules can be customized by address type, i.e.
  • expand the street suffix
  • ignore campus addresses, etc.

13
CLEAN_Address_Banner package
  • 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
  • Appends ZIP4 to ZIP Code
  • Stores verification date and address error code

14
Options
  • GeoCoder
  • Identifies the Latitude and Longitude coordinates
    for an address
  • Used to compute distance, bearing, and perform
    radial searches
  • I.e. How many students live within 5 miles, 10
    miles of the college?
  • Demographic Data
  • Mailers4

15
Implementation Steps
  • Implementation committee
  • Student
  • Payroll
  • Finance
  • Decided to use 5-digit FIPS County Codes
  • Cleared county codes from spraddr, sogsbgi
  • Ran sync_postal_codes (CLEAN_Address script) to
    populate gtvzipc and stvcnty

16
Implementation Steps
  • Decided to verify addresses in place
  • Batch_verify_address (SPRADDR)
  • Batch_verify_SOBSBGI
  • Error lists (based on CLEAN_Address script)
    distributed to users
  • Employees paid within last 2 years
  • Student registered in last 5 years
  • Vendors invoice in last 2 years

17
Implementation Steps
  • Inactivated addresses in error, excluding
  • Canadian
  • Early Warning System
  • Suite/Apartment errors
  • Payroll, if paid within 2 years

18
Banner Changes
  • All changes provided by Runner
  • Forms
  • Changed goqclib to add or modify
    WHEN_VALIDATE_ITEM triggers
  • Added 2 procedures (cut and paste) to SPAIDEN,
    PPAIDEN, FOAIDEN, SAAQUIK, and FTMVEND (not using
    RPAIDEN)
  • Self-Service
  • Modified bwgkoadr.sql, bwgkoad1.sql bwgkoga1.sql
  • Modified local address entry packages

19
CCC Apply Changes
  • Added delivery point, etc. to table and view
  • Changed script to set new address errors
  • E04 Mailing Clean Addr Err
  • E04 Permanent Clean Addr Err
  • E02 Phone Clean Address Err
  • Also added to GTVSDAX error list

20
CCC Apply Changes
  • Changed trigger to bypass address insert if Clean
    Address error
  • Changed SWACCAP to do CLEAN_Address real-time
    check (if address or phone is touched, errors
    are cleared)
  • Ran script to verify addresses for in-process
    records

21
Ongoing maintenance
  • Will run same batch process as above every 6
    months
  • Required by the USPS to qualify for bulk mailing
  • Havent implemented yet
  • Upgrade process is very smooth

22
ErrorMessages
23
(No Transcript)
24
(No Transcript)
25
(No Transcript)
26
Banner FormsExamples
27
Minimum data can be entered
3CBG Conference - October 23rd and 24th
28
If valid, remaining address is auto-completed to
USPS standard
29
Address error can be over-ridden in forms
30
Invalid address can be saved
31
Area code matched to zip code range is
customizable
32
Multiple matches will provide a suggestion list
33
Self-ServiceExamples
34
Partial address can be entered
35
Errors can not be overridden in Self-Service
36
If valid, remaining address is auto-completed to
USPS standard
37
Issues/Suggestions
  • Must keep overrides to a minimum procedural
    issue
  • Sometimes unavoidable because of Early warning
    system
  • Double-check at USPS or clean-address site
  • Keep abreast of patches

38
The following slides werecreated by Robin
whenshe tested the product
39
CLEAN ADDRESSExamples
40
SPAIDENFind personChoose Address Tab
41
Change Street Line 1 to 7335 and Enter or Save.
Error message Address out of Range
Click on OK for more options
More options
No specific address given as a choice just a
range. Click OK and 2nd box appears. Click OK
and both boxes disappear. Address is changed to
7335
42
Roll back and Do you want to save box appears.
Choose Yes.
43
Exit out and open SPAIDENIncorrect change was
saved. If choose No to saving changes, change
was not made.
44
Changed City to Clinton TownshipSave button
45
Change State to AZ. Click on Save
46
Exit out return to SPAIDENAZ is listed as the
state
47
Change Zip to 48309Click on Save. Banner
message appears. Click on OK and Save.
48
Exit out. SPAIDEN. All incorrect changes were
saved.
49
Change County to 009using the search button.
Click on OK and Save.
50
Click on SPAIDEN all changes saved.
51
Change Telephone fieldClick on Save. Change was
saved.
52
Incorrect area code added.Save. Change was
saved.
53
01 Permanent telephone change made
54
All changes made and saved even though they are
wrong
55
After changes were saved, changes not made to HR
address type
Write a Comment
User Comments (0)
About PowerShow.com