California Community College Banner Group Conference

1

California Community College Banner Group
Conference Change from SSN to Generated ID Number
2
CCSF SS Number Change

• Introductions
• James Hsiao, Director of Administrative
  Applications (jhsiao_at_ccsf.edu)
• Sophia Toney, Manager, Financial Aid
  (stoney_at_ccsf.edu)
• Rex Garner, Banner Project Manager, Senior
  Consultant with SIG (garner_at_sigcorp.com)

3
CCSF SS Number Change

• Problem Statement
• (How did we manage to get this project?)
• Use of the Social Security Number (SSN) as a
  primary key for accessing and storing information
  on students, faculty and staff
• Display/printing of the SSN on internal and
  external documents
• General security on dissemination of the SSN

4
CCSF SS Number Change

• Policy review by the ITPC (Information Technology
  Planning Committee)
• Recommendation to review project implications
• Prepare a project report and estimate of work
• Chancellor sent college wide email supporting the
  project
• Your Institution
• Utilize internal policy committee structure
• Involve top management and provide vehicle for
  internal communication

5
CCSF SS Number Change

• Policy review by the ITPC
• Make a recommendation to discontinue the use of
  the SS Number as a key wherever possible.
• Reached final agreement to move forward in
  December, 2001
• Serious Concerns Expressed by the college
  community
• Initial date set for mid April, 2002

6
CCSF SS Number Change

• Next Steps
• Determined timeline and project resources
• Design
• Programming
• Testing
• Implementation

7
CCSF SS Number Change

• Next Steps
• Created internal working committee
• Banner Data Managers
• Departmental managers
• Key departmental users
• IS Staff
• Key Stakeholders

8
CCSF SS Number Change

• Next Steps
• Performed Initial project scope analysis

9
CCSF SS Number Change

• Analysis
• Wrote several scripts to analyze the existing
  database of over 1 million SPRIDEN records
• Determine what the counts were for the first
  character
• Analyze the existing social security numbers
  residing in the SPAPERS table.
• Determine the conversion totals and processing
  estimates

10
CCSF SS Number Change
Results
11
CCSF SS Number Change

• Analysis/Strategy
• Determined that the W had only 10 entries
• W was a useful character
• Easy to remember
• Would represent the one-time conversions
• _at_ sign would be used after conversion
• Continuity for ongoing add process
• Easy symbol to search
• Your Legacy Issues
• Carefully analyze your database
• Review

12
CCSF SS Number Change

• Key Project Issues
• Banner
• Best method to provide a transition and minimize
  the need to issue a new generated id number to
  all of the students, faculty and staff.
• Convert/move the SSN to the prior ID field
• Web Access
• Training and ease of access for all staff
• Conversion timing
• End of Semester prior to new semester processing

13
CCSF SS Number Change

• Key Project Issues
• Impact on department schedules
• Best approach to arrive at signoff by key users
• Training
• Arrive at a consensus on the policies and
  procedures that the AR, Payroll and Finance
  departments would follow when using the Generated
  ID

14
CCSF SS Number Change

• Project Plan (Key components)
• Business Practices (Impact and Change)
• Sign-off from key manages and users
• Technical Challenges (Detailed Plan)
• Internal vs. External Usage
• Conversion and Timeline
• Training

15
CCSF SS Number Change

• Business Practices.
• The college will continue to collect the social
  security number.
• The social security number will be stored and a
  randomly generated identification number will
  replace the social security number as the key.
• When departments add new students, faculty and
  staff or vendors, they will continue to collect
  and store the SSN and generate a new
  identification number.

16
CCSF SS Number Change

• Signoff (Key ingredient to project success)
• How do we know that everyone is on board
• Identified the key users, managers and
  stakeholders
• Created a committee
• Set regular meetings
• Reviewed project plan

17
CCSF SS Number Change

• Signoff Document
• Key Managers
• Had to signoff, no exceptions
• Opportunity to review and adjust plan
• Benefits
• Early involvement and participation
• Ownership and responsibility for testing

18
CCSF SS Number Change
19
CCSF SS Number Change

• Internal
• Transcript Data
• Financial Aid Application
• Application for Admission
• External
• Grants and Contracts (Title III, State, etc.)
• State Reports (MIS, CalGrant, etc.)
• Still using SSN to report (State reviewing)
• Federal Reporting (IRS, NSLDS, RFMS, etc.)
• Transcript (Include or exclude SSN)

20
CCSF SS Number Change

• Technical Challenges
• Conversion and Timeline
• Design Phase early January 2002
• Coding January 2002
• Testing January through March 2002
• Implementation March 11, 2002

21
CCSF SS Number Change

• Conversion and Timeline.
• When to convert the data.
• Moving target, testing uncovered additional areas
  to change.
• Coordination with training.
• Scheduled training sessions with key departments
  as the deadline approached.

22
CCSF SS Number Change

• Design Phase early January 2002.
• Define the starting range number and starting
  letter (C) or number (9) - find max. number been
  used on each first letter code.
• Define which current id is a social security
  number (all numeric number? Or any number that is
  less than 800-00-0000).
• Where should the SSN be stored? (SPBPERS_SSN ).

23
CCSF SS Number Change

• Coding January 2002.
• Write SQL scripts or SQR programs to convert the
  current id to the newly defined id.
  (Swugnid.sqr).
• Update the current id record (SPRIDEN) to a prior
  id.
• Update the table (SPBPERS) SSN field.

24
CCSF SS Number Change

• Testing January thru March 2002.
• Determine which test database to be used for the
  end-users to test the conversion results.
• Run the new scripts on the test database.
• Test on-line forms and batch processes -
  coordinate with all the power users on Finance,
  Payroll, Account Receivable, Financial Aid, Human
  Resources and Student A/R.
• Identify errors and correct it.

25
CCSF SS Number Change

• Implementation - Mar. 11, 2002.
• Run the modified new scripts on production
  database.
• Monitoring the new conversion.

26
CCSF SS Number Change

• Training Plan
• Needs
• Training document
• Documentation of the new procedures
• Result
• Identified process for adding NEW
• Students
• Employees
• Vendors
• Built a training document and scheduled multiple
  training sessions

27
CCSF SS Number Change

• Training Documents
• Name Search
• Adding New Students
• Adding New Employees
• Adding New Vendors
• Special Situation
• Student Refunds
• Send an email to garner_at_sigcorp.com

28
CCSF SS Number Change

• Project Summary
• Successful Project Ingredients
• Top Level Support
• Policy Committee Approvals
• Organized Working Committee
• Official Sign Off Document
• Correct Technical Solution
• Benefits of Project
• SSN is now very protected and not used as a
  primary key
• Better internal college awareness of privacy
  issues

29
CCSF SS Number Change

• Ongoing Issues/Concerns
• MIS Reporting
• External reporting in general
• Other Resources to Check
• Pacific Lutheran University http//www.plu.edu/s
  uttonlw/
• SCT Listserver using Search Archives

30
CCSF SS Number Change

• SCT Banner General 5.5.
• Enhancement to SSN/SIN/TFN Alternate ID Search
  Form (GUIALTI).
• The GUIALTI Form is modified so that queries on
  name or ID will return non-person information as
  well as person information, even if no SPBPERS
  data exists for the name/organization. Queries on
  SSN/SIN/TFN by itself still return data only if
  an SPBPERS record exists.

31
CCSF SS Number Change

• SCT Banner General 5.5.
• SSN/SIN History.
• This enhancement captures all changes made to a
  person or vendor SSN/SIN/TFN, regardless of the
  form used to make the change as long as the
  update is made to the SPBPERS_SSN field. A
  database trigger records all pertinent
  information and stores it to a new repeating
  table. A new form allows users to query the
  history of changes made to the SSN/SIN/TFN.

32
CCSF SS Number Change
Questions ?