Session ST-13 CCCTran – Implementation and Integration with Banner Renee Cowing – San Mateo Community College District

1. Session ST-13 CCCTran – Implementation and Integration with Banner Renee Cowing – San Mateo Community College District Richard Oberlin – North Orange County Community College District

2. What is CCCTran? • California Community Colleges statewide internet-based system for requesting, transmitting, tracking, downloading, and viewing academic transcripts among authorized educational institutions and their trading partners.

3. CCCTran Benefits • Speed • Tracking • Security • Increase Productivity • Student Convenience • Lower Costs

4. CCCTran – Changes in the Last Year • Live CCCs increased from 2 to 26 • Now 10 Banner colleges • Live CSUs increased from 5 to 6 • Fullerton, Northridge, Sacramento, San Diego, San Francisco, San Jose • District-Wide transcripts supported • Mini-Grant extended to Feb 28, 2009 • $4000 to $1000 depending on the college’s go-live date

5. CCCTran – Upcoming Projects • HS Transcripts • Composite Transcript Request System

6. Banner CCCTran Live Colleges • Bakersfield College (Credentials) • Cerro Coso Community College (Credentials) • College of the Sequoias (Credentials) • Cypress College • Fullerton College • Moorpark College • Oxnard College • Porterville College (Credentials) • Skyline College • Ventura College

7. NOCCCD CCCTran Experience • Cypress College • Live Aug 7, 2007 (14 months) • 1548 transcripts (260 requests, 1288 SSB) • Fullerton College • Live June 11, 2008 (3 months) • 1272 transcripts (193 requests, 1079 SSB) • About 1% are partial transcripts (pre-1989). • About 1% of requests require offline search. • On SSB students select electronic over paper transcript about 90% of the time.

8. Possible alternatives • Credentials • Kern CCD, • College of the Sequoias • Ventura CCD (DwT) • Views instead of packages • Banner EDI? • Banner PESC/XML? • UDE (User Defined Extensions) • Now separate download from baseline (see FAQ 1-3Z61QF)

11. User Interfaces • Admissions and Records • XAP Control Center • Check status • View Offline Record Search Required • Banner • Partial Transcript Report – replace with XAP email to A&R staff • SHARQTC • Student • Self-Service Banner

12. Implementation Steps • First Steps • Learning about CCCTran • Decisions and Configuration • Programming • Testing, Testing, Testing • Document • Go LIVE

13. First Steps • CCCTran web site – www.ccc-tran.org • Download and sign “CCCTran CCC Institution Agreement” (under File Repository  Contracts & Agreements). • Schedule XAP Training • Control Center – administrator sets up access for others

14. Learning about CCCTran • CCCTran web site • Demonstrations • Implementation and Programming Guide • Data Dictionary • 2 volumes – about 550 pages • About 600 fields (we used about 50%) • Other

15. Decisions and Configuration • Decisions • Admissions and Records • IT Department • Configuration • Control Center • XAP Communicator • Banner

16. Programming – Windows Server • Directory structure • Batch files • FTP • WinSCP – free SFTP (SSH) and FTP client (winscp.net)

17. Programming – Request File Load • Unix shell script • Wakes up every XX minutes • Runs SQL*Loader to load local table • Easily converted to use External Table instead of SQL*Loader

18. Programming – Request Matching • Match against Banner as defined on Control Center • We use SSN/DOB, or ID/DOB, or Last/First/DOB • Send Response or Transcript • Transcript Holds?

19. Programming - Response • No match = Offline Record Search Required • Transcript Holds • Recycling (complex, but simple after set up) • Request Conditions • Example: hold until grades posted • We don’t accept (send response and transcript)

20. Programming - Transcript • What fields to include? Optional? • Include everything on your official transcript • Code translations • Example: EDI Grade Qualifier • Banner free text to CCCTran coded field • If variable may be difficult or impossible to translate

21. Programming - Transcript • Coding is mostly based on shrtrtc.pc (transcript print program) • Banner transcript type needed, but doesn’t use Transcript Type Rules • Parameter table

22. Programming – Self-Service • Cloned transcript package bwskwtrr • Select college to send to from drop down list • Also a few mods to bwskwtrr • For credit card processing • View status of transcript requests

23. Programming – INB (SHARQTC) • Database trigger initiates CCCTran transcript creation • Based on transcript type code • Separate trigger from no sleep/wake trigger • Which has small mod to exclude CCCTran transcript types

24. Testing, Testing, Testing • Beta Control Center/beta server allows complete testing • Test validation upload very helpful to the programmer • Admissions and Records should test all info that appears on your transcript • To/from your own college (transcript views) • With trading partners

25. Documentation • Local Mods • Mods to Banner Baseline objects • Local only objects • Windows Server/XAP Communicator • Password changes!!! • Admissions and Records Procedures • XAP Control Center settings • Offline Record Search • Partial Transcripts

26. Go LIVE!!! • Contact XAP • Most Control Center settings will be moved over – and some will not • Move code into production • Change Process Code from T to P • Change XAP Communicator from Beta to Live • Notify other CCCTran Colleges

27. Session CCC – T CCCTran – How We Modified NOCCCD Software for our District Renee Cowing – San Mateo County Community College District

28. Major Steps to Installing CCCTRAN Software installation steps Document CCCTran Implementation for Banner Tables, create directories on UNIX server as well as XAP Communicator Szktout.sql and szktran.sql District-Wide Transcripts External Table set-up Transcript Testing XAP Control Center and XAP communicator Bugs

29. Software Installation (Part I) Create tables Szrgrde.sql Loaded from shrgrde with modifications Szrtint.sql Modifications for district-wide Szrtprm.sql Szrtreq.sql – xzrtreq.sql xzrtreq – working file to create transcript / response. szrtreq.sql - archive purposes only Create sequence sztseq.sql unique names for downloaded requests Create UTL_FILE directories

30. Software Installation (Part II) szktran.sql – Package to create transcript / response Analyze transcript data in reference to trading partner needs Dean’s list Partial transcripts Hold for grades etc. Fees – request initiated by trading partner vs. student IGETC/CSU Establish one main contact from SMCCCD for input as to data How do we handle ‘paper transcripts’ Degree/Certificates Academic renewal

31. Post - Installation Transcript testing meeting Include representatives of all 3 colleges Compare current transcript (Evisions) vs. CCCTran Collect feedback

32. Modifications from Testing Input Szktran.sql – modifications resulting from input from colleges Courses not rolled to history Repeat status on ‘old’ transcripts Grade scale code per trading partner Partial transcript shrgpac_term_code 000000 Honors courses Term_c cursor – terms with no course work NG grade – do not print Degree applicable Degree honors Term deans lists Academic renewal Not counted in GPA calc In progress – add units attempted ? ? ?

33. District Wide Transcripts Changes - Data Dictionary and Local Tables District Identity record – 03A Every transcript is district-wide 03A40 = ‘D’ College Identity Record – 03B One for each college that the student attended One for sending institution whether or not they took courses at that college Modify szrtint to include row for each college delineated by szrtint_levl_code SMCCCD has four level records, one for each college and one for the district

34. District Wide Transcriptsszktran.sql – Changes in data elements Cursor - Institution identity record – 03A Cursor - Colleges_attend_c Table shrtckn Change all references to GPA to be college specific e.g. shrtgpa to shrgpac Others . . .

35. District Wide Transcriptsszktran.sql – Changes in architecture Change in extracting course data Add outer for loop ‘for colleges_attend_rec’ encompassing term_c and course_c Pass levl code to term_c Pass levl code to course_c Change sort of course_c, add criteria - FICE

36. DwT szktra1.sql – for loop

37. External Tables Code to create external table from NOCCCD Query data from request as though it was a db table Create directory in OS and grant access Put external table data in directory Create directory in Oracle and grant access

38. External Tables (con’t)SMCCCD vs. NOCCCD SMCCCD External Tables Request pulled from server to external table on UNIX server Similar directory structure Request .dat file serves as data source Script to run every 5 mins Syqtreq.sql – analyzes request for match Update szrtreq with request data NOCCCD DB Table Request loaded into local table szrtreq Szrtreq (loaded via sqlloader) holds all request data Insert into szrtreq causes db trigger to fire Trigger analyzes request data for match

39. SYQTREQ.sql Replaces NOCCCD Trigger Reads request to find match Creates a response or transcript Inserts record in SHTTRAN only if transcript is created Inserts record in SZRTREQ for audit purposes

40. XAP Communicator Downloading files from control site same as NOCCCD Requests ‘pulled ‘ from UNIX Download to communicator same

41. Bugs – small – Oracle 10g only Parameter passed to term_c cursor Typecast as shrttrm_term_code%TYPE term_code is 6varchar Shrttrm_pidm is 8 numeric Result Error: ORA06502 PL/SQL: ‘numeric or value error: character string buffer too small’ Transcript bombed but only on newer transcripts

42. Server Architecture Script syqtreq.sql calls DB procedure szktra1 We use external table Key data and result code are loaded to szrtreq RESULT – CCCTran Transcript (or response)

43. Go Live September 30th 2008 – a gradual process!

44. Contact Information Richard Oberlin IT Project Leader - Information Services North Orange County Community College District Roberlin@NOCCCD.edu 714-808-4867 Renee Cowing Sr Programmer I San Mateo County Community College District cowing@smccd.edu 650-358-6741