Implementing Technology & Centralizing Transfer Credit Evaluation
Keep Those Evaluations Moving! Implementing Technology & Centralizing Transfer Credit Evaluation
Session ID: 1799.0 4.2.14 10:30 a.m. – 11:30 a.m.
Presented by: Mary Ellen Hanna Jennifer McMillan Denny Savage The University of Alabama
Increasing Enrollment Strategy 20,333
•Fall 2003 Enrollment
30,000
•Fall 2013 Enrollment Goal
30,232
•Fall 2010 Enrollment
40,000
•Fall 2020 Enrollment Goal
34,852
•Fall 2013 Enrollment
Transfer Student Applications
4293 3608 2405 • Fall 2003
• Fall 2006
• Fall 2011
Out-of-State New Freshmen
Fall 2003 776 Students
Fall 2006 1451 Students
Fall 2010 2310 Students
Fall 2013 3728 Students
The Problem • Wait time too long! – 60+ days for an evaluation
• Increased workload for staff – No staff dedicated to transfer credit evaluation
• Delayed response impacts student decisions – Commitment to UA – Advising – Registration
The Provost’s Mandate • All domestic lower-level incoming transfer credit be evaluated by the Registrar’s office. • Turnaround time should be 7 days or less!
The Old Way
The New Way
Staff • Assistant University Registrar – Position created 2010 – Oversees entire transfer credit process – Supervises TCEs and ASAs
• Transfer Credit Evaluators (TCE) – Position created in 2010 – Began as 2 full-time staff, currently hiring a 4th – Evaluate all incoming lower level domestic credit, facilitate all reevaluations, facilitate evaluations for prospective students, etc.
• Academic Services Associates (ASA) – Existing position – 3 full-time staff – Receive transcripts, post work to students records, build equivalencies in Banner, articulate, etc
New Technology • Scan & Verify – Implemented new scan & index software • Customizable at end-user level • Summer 2011
• Load Credit to Banner • Output coursework in a standardized XML format for nightly load to Banner • Fall 2011
New Technology • Auto Articulation – Custom program developed for UA • Runs nightly for all unarticulated coursework • Fall 2011
• Evaluation of Credit – Moved to Banner Workflow • One point of entry for each evaluation • Spring 2012
Nightly Load to Banner • Read XML file and loads transfer credit to top block of SHATRNS – SHRTRIT – SHRTRAM – SHRTRCR
• Compares coursework to existing SHATATR records. • Triggers Banner Workflow event for coursework that does not have an existing SHATATR record.
XML File for Load to Banner
Transfer Credit Load Program
Transfer Credit Load Program
Transfer Credit Load Program
Transfer Credit Load Program
Procedures Called by Load
Auto Articulation • Custom PL/SQL program written to perform automatic articulation for courses in top block of SHATRNS (SHRTRCR) with no equivalent course in the bottom block (SHRTRCE) – Un-rolls student’s work from history – Applies group codes to related courses – Mimics logic from SHATAEQ to create articulations from equivalencies in SHATATR – Rolls course work back to history
Generation of TCE Workflow • Custom PL/SQL program • Determine loaded transfer courses that do not have an equivalent in SHRTRCE For each course: – Verify that no equivalent exists on SHATATR – Trigger creation of TCE workflows
TCE Workflow Criteria
TCE Workflow Trigger
Set Event Code
Set Event Parameters
Post Event
Workflow
All workflows are triggered in nightly load to Banner. The team’s work is ready when they arrive in the morning!
Workflow for Evaluators
Workflow for Evaluators
Auto-Post to SHATATR Workflow Business Component calls PL/SQL procedure to insert row into SHBTATC/SHRTATC
Auto-Post to SHATATR PL/SQL procedure • Checks to see if course is valid in the catalog • Verifies a course equivalent does not already exists in SHATATR • Performs insert into SHBTATC/SHRTATC • Performs insert into SHRICMT (comments) • Returns message: Successful or Unsuccessful
Workflow for Evaluation
The Results Calendar Year 2013 3.08 days 21,595 evaluations completed 18,314 by TCEs
Reporting • Daily – Transfer Credit workflow generated previous day – Auto-articulation output – Transfer credit loaded/rejected – Groups with mismatched hours – Groups with mismatched grades
• Weekly – Unarticulated coursework older than 7 days – Transfer credit not equal to UA credit – Repeated courses with repeat indicators missing – Processing time summary
Daily Reports
Weekly Reports
Transfer Credit Not Equal to UA Credit
LAST_NA CWID ME 11591425 Lundquist 11135863 Mercier 11592991 Olive 11587093 Schwartz 11587093 Schwartz
FIRST_NA TRAM_SE ME TRIT_SEQ Q TRCR_SEQ TR_SUBJ TR_NUMB TR_HRS Nicole 2 1 1 ENG 001A Benjamin 1 2 7 FYS 102 Michelle 2 1 2 NSG 2211 James 1 4 12 PHY 231 James 1 4 13 PHY 231
MULTIPLI CONV_HR ER S UA_HRS 5 1 5 4 4 1 4 3 3 1 3 1.98 5 1 5 4 4 1 4 1
Transfer Credit Repeats STU_NA CWID ME PIDM SUBJ 1010708 Axen, 9 Heather 325902 EH 1010708 Axen, 9 Heather 325902 EH 1149763 Miller, 2 Charles 1573099 HY 1149763 Miller, 2 Charles 1573099 HY 1149763 Miller, 2 Charles 1573099 HY 1149763 Miller, 2 Charles 1573099 HY 1149763 Miller, 2 Charles 1573099 PSC 1149763 Miller, 2 Charles 1573099 PSC 1031664 Wallace, 1 William 216003 CE 1031664 Wallace, 1 William 216003 CE 1031664 Wallace, 1 William 216003 CE 1031664 Wallace, 1 William 216003 CE 1031664 Wallace, 1 William 216003 MA 1031664 Wallace, 1 William 216003 MA 1031664 Wallace, 1 William 216003 ME 1031664 Wallace, 1 William 216003 ME 1031664 Wallace, 1 William 216003 MH 1031664 Wallace, 1 William 216003 MH 1031664 Wallace, 1 William 216003 PH 1031664 Wallace, 1 William 216003 PH
NUMB
COURSE GRADE TERM
LEVEL_C TRAN_C UA_CRE ODE REDIT DIT TRIT
200 EH 200 P
198730 UG
3
0
200 EH 200 A
198810 UG
0
3
101 HY 101 D
200910 UG
3
0
101 HY 101 P
201410 UG
3
102 HY 102 A
200930 UG
102 HY 102 P
TRAM 1
LOCATIO MAX_RP HIGH_C TOTAL_ N T LOW_CR R CR 1 TRNS
3
3
3
6
INST
3
3
3
6
1
5 TRNS
3
0
3
6
0
2
1 TRNS
3
0
3
6
3
0
1
2 TRNS
3
0
3
6
201410 UG
3
0
2
1 TRNS
3
0
3
6
203 PSC 203 B
201010 UG
3
0
1
4 TRNS
3
3
3
6
203 PSC 203 P
201410 UG
3
0
2
1 TRNS
3
3
3
6
262 CE 262 D
198740 UG
0
3
INST
3
6
262 CE 262 C
198810 UG
0
3
INST
3
6
320 CE 320 D
198810 UG
0
3
INST
3
6
320 CE 320 C
198840 UG
0
3
INST
3
6
125 MA 125 D
198340 UG
0
4
INST
4
4
4
8
125 MA 125 C
198410 UG
0
4
INST
4
4
4
8
372 ME 372 D
198530 UG
0
3
INST
3
3
3
6
372 ME 372 B
198610 UG
0
3
INST
3
3
3
6
251 MH 251 D
198540 UG
0
1
INST
1
1
1
2
251 MH 251 B
198940 UG
0
1
INST
1
1
1
2
105 PH 105 D
198340 UG
0
3
INST
3
3
3
6
105 PH 105 C
198410 UG
0
3
INST
3
3
3
6
Transfer Evaluation Processing Time Total by ROWS College
Initial Review
College
Posting Equiv
Total Time
Number of Records
Value
Value
Value
Value
Value
AS
0.89
1.46
0.21
2.55
344
CB
0.75
2.00
0.22
2.95
105
CM
0.94
0.86
0.31
2.05
29
ED
0.47
1.46
1.24
2.98
9
EG
0.67
6.90
0.12
7.76
50
HS
0.96
4.17
0.11
5.26
55
SW
1.40
1.72
0.03
3.13
17
xOUR
0.95
0.00
0.22
0.97
4551
Total by COLUMNS
0.94
2.22
0.21
1.25
5160
Reporting from Workflow select distinct w.id ,w.pd_id transform epoch to date ,w.name ,w.current_state ,w.last_state ,(date '1970-01-01' + w.start_date / (1000 * 60 * 60 * 24) - (6 / 24)) wf_startdte ,(date '1970-01-01' + w.stop_date / (1000 * 60 * 60 * 24) - (6 / 24)) wf_stopdte . . . from workflow.eng_workflow w table for workflow instances where w.name like ('Transfer Evaluation%') and w.current_state = 'stopped.completed'
Reporting from Workflow (subselects for specific tasks) //getting date and time that the task was completed ,(select distinct (date '1970-01-01' + i.stop_date / (1000 * 60 * 60 * 24) - (6 / 24)) from workflow.eng_workitem i table for activities within the workflow where i.wf_id = w.id and i.pd_id = w.pd_id and i.role_id is not null and i.name like ('PostTransferCourseEquivalency')) Post_TrfrCrseEquiv_stopdte //getting user who completed the task ,(select distinct u.last_name || ', ' || u.first_name from workflow.eng_workitem i, workflow.wfuser u table for workflow users where i.wf_id = w.id and i.pd_id = w.pd_id and i.role_id is not null and i.performer_id = u.id and i.name like ('PostTransferCourseEquivalency')) Post_TrfrCrseEquiv_user
Reporting from Workflow • Main Tables – – – –
eng_workflow eng_workitem eng_itemvalues wfuser
• Things to watch out for – Date conversions – Loops in workflow model causes repeated tasks – Test your sql for an individual workflow/task
Questions?
Mary Ellen Hanna Senior Associate University Registrar [email protected]
Jennifer McMillan Enterprise Architect [email protected]
Denny Savage Senior Associate University Registrar [email protected]
Session ID: 1799.0 4.2.14 10:30 a.m. – 11:30 a.m.
Presented by: Mary Ellen Hanna Jennifer McMillan Denny Savage The University of Alabama
Increasing Enrollment Strategy 20,333
•Fall 2003 Enrollment
30,000
•Fall 2013 Enrollment Goal
30,232
•Fall 2010 Enrollment
40,000
•Fall 2020 Enrollment Goal
34,852
•Fall 2013 Enrollment
Transfer Student Applications
4293 3608 2405 • Fall 2003
• Fall 2006
• Fall 2011
Out-of-State New Freshmen
Fall 2003 776 Students
Fall 2006 1451 Students
Fall 2010 2310 Students
Fall 2013 3728 Students
The Problem • Wait time too long! – 60+ days for an evaluation
• Increased workload for staff – No staff dedicated to transfer credit evaluation
• Delayed response impacts student decisions – Commitment to UA – Advising – Registration
The Provost’s Mandate • All domestic lower-level incoming transfer credit be evaluated by the Registrar’s office. • Turnaround time should be 7 days or less!
The Old Way
The New Way
Staff • Assistant University Registrar – Position created 2010 – Oversees entire transfer credit process – Supervises TCEs and ASAs
• Transfer Credit Evaluators (TCE) – Position created in 2010 – Began as 2 full-time staff, currently hiring a 4th – Evaluate all incoming lower level domestic credit, facilitate all reevaluations, facilitate evaluations for prospective students, etc.
• Academic Services Associates (ASA) – Existing position – 3 full-time staff – Receive transcripts, post work to students records, build equivalencies in Banner, articulate, etc
New Technology • Scan & Verify – Implemented new scan & index software • Customizable at end-user level • Summer 2011
• Load Credit to Banner • Output coursework in a standardized XML format for nightly load to Banner • Fall 2011
New Technology • Auto Articulation – Custom program developed for UA • Runs nightly for all unarticulated coursework • Fall 2011
• Evaluation of Credit – Moved to Banner Workflow • One point of entry for each evaluation • Spring 2012
Nightly Load to Banner • Read XML file and loads transfer credit to top block of SHATRNS – SHRTRIT – SHRTRAM – SHRTRCR
• Compares coursework to existing SHATATR records. • Triggers Banner Workflow event for coursework that does not have an existing SHATATR record.
XML File for Load to Banner
Transfer Credit Load Program
Transfer Credit Load Program
Transfer Credit Load Program
Transfer Credit Load Program
Procedures Called by Load
Auto Articulation • Custom PL/SQL program written to perform automatic articulation for courses in top block of SHATRNS (SHRTRCR) with no equivalent course in the bottom block (SHRTRCE) – Un-rolls student’s work from history – Applies group codes to related courses – Mimics logic from SHATAEQ to create articulations from equivalencies in SHATATR – Rolls course work back to history
Generation of TCE Workflow • Custom PL/SQL program • Determine loaded transfer courses that do not have an equivalent in SHRTRCE For each course: – Verify that no equivalent exists on SHATATR – Trigger creation of TCE workflows
TCE Workflow Criteria
TCE Workflow Trigger
Set Event Code
Set Event Parameters
Post Event
Workflow
All workflows are triggered in nightly load to Banner. The team’s work is ready when they arrive in the morning!
Workflow for Evaluators
Workflow for Evaluators
Auto-Post to SHATATR Workflow Business Component calls PL/SQL procedure to insert row into SHBTATC/SHRTATC
Auto-Post to SHATATR PL/SQL procedure • Checks to see if course is valid in the catalog • Verifies a course equivalent does not already exists in SHATATR • Performs insert into SHBTATC/SHRTATC • Performs insert into SHRICMT (comments) • Returns message: Successful or Unsuccessful
Workflow for Evaluation
The Results Calendar Year 2013 3.08 days 21,595 evaluations completed 18,314 by TCEs
Reporting • Daily – Transfer Credit workflow generated previous day – Auto-articulation output – Transfer credit loaded/rejected – Groups with mismatched hours – Groups with mismatched grades
• Weekly – Unarticulated coursework older than 7 days – Transfer credit not equal to UA credit – Repeated courses with repeat indicators missing – Processing time summary
Daily Reports
Weekly Reports
Transfer Credit Not Equal to UA Credit
LAST_NA CWID ME 11591425 Lundquist 11135863 Mercier 11592991 Olive 11587093 Schwartz 11587093 Schwartz
FIRST_NA TRAM_SE ME TRIT_SEQ Q TRCR_SEQ TR_SUBJ TR_NUMB TR_HRS Nicole 2 1 1 ENG 001A Benjamin 1 2 7 FYS 102 Michelle 2 1 2 NSG 2211 James 1 4 12 PHY 231 James 1 4 13 PHY 231
MULTIPLI CONV_HR ER S UA_HRS 5 1 5 4 4 1 4 3 3 1 3 1.98 5 1 5 4 4 1 4 1
Transfer Credit Repeats STU_NA CWID ME PIDM SUBJ 1010708 Axen, 9 Heather 325902 EH 1010708 Axen, 9 Heather 325902 EH 1149763 Miller, 2 Charles 1573099 HY 1149763 Miller, 2 Charles 1573099 HY 1149763 Miller, 2 Charles 1573099 HY 1149763 Miller, 2 Charles 1573099 HY 1149763 Miller, 2 Charles 1573099 PSC 1149763 Miller, 2 Charles 1573099 PSC 1031664 Wallace, 1 William 216003 CE 1031664 Wallace, 1 William 216003 CE 1031664 Wallace, 1 William 216003 CE 1031664 Wallace, 1 William 216003 CE 1031664 Wallace, 1 William 216003 MA 1031664 Wallace, 1 William 216003 MA 1031664 Wallace, 1 William 216003 ME 1031664 Wallace, 1 William 216003 ME 1031664 Wallace, 1 William 216003 MH 1031664 Wallace, 1 William 216003 MH 1031664 Wallace, 1 William 216003 PH 1031664 Wallace, 1 William 216003 PH
NUMB
COURSE GRADE TERM
LEVEL_C TRAN_C UA_CRE ODE REDIT DIT TRIT
200 EH 200 P
198730 UG
3
0
200 EH 200 A
198810 UG
0
3
101 HY 101 D
200910 UG
3
0
101 HY 101 P
201410 UG
3
102 HY 102 A
200930 UG
102 HY 102 P
TRAM 1
LOCATIO MAX_RP HIGH_C TOTAL_ N T LOW_CR R CR 1 TRNS
3
3
3
6
INST
3
3
3
6
1
5 TRNS
3
0
3
6
0
2
1 TRNS
3
0
3
6
3
0
1
2 TRNS
3
0
3
6
201410 UG
3
0
2
1 TRNS
3
0
3
6
203 PSC 203 B
201010 UG
3
0
1
4 TRNS
3
3
3
6
203 PSC 203 P
201410 UG
3
0
2
1 TRNS
3
3
3
6
262 CE 262 D
198740 UG
0
3
INST
3
6
262 CE 262 C
198810 UG
0
3
INST
3
6
320 CE 320 D
198810 UG
0
3
INST
3
6
320 CE 320 C
198840 UG
0
3
INST
3
6
125 MA 125 D
198340 UG
0
4
INST
4
4
4
8
125 MA 125 C
198410 UG
0
4
INST
4
4
4
8
372 ME 372 D
198530 UG
0
3
INST
3
3
3
6
372 ME 372 B
198610 UG
0
3
INST
3
3
3
6
251 MH 251 D
198540 UG
0
1
INST
1
1
1
2
251 MH 251 B
198940 UG
0
1
INST
1
1
1
2
105 PH 105 D
198340 UG
0
3
INST
3
3
3
6
105 PH 105 C
198410 UG
0
3
INST
3
3
3
6
Transfer Evaluation Processing Time Total by ROWS College
Initial Review
College
Posting Equiv
Total Time
Number of Records
Value
Value
Value
Value
Value
AS
0.89
1.46
0.21
2.55
344
CB
0.75
2.00
0.22
2.95
105
CM
0.94
0.86
0.31
2.05
29
ED
0.47
1.46
1.24
2.98
9
EG
0.67
6.90
0.12
7.76
50
HS
0.96
4.17
0.11
5.26
55
SW
1.40
1.72
0.03
3.13
17
xOUR
0.95
0.00
0.22
0.97
4551
Total by COLUMNS
0.94
2.22
0.21
1.25
5160
Reporting from Workflow select distinct w.id ,w.pd_id transform epoch to date ,w.name ,w.current_state ,w.last_state ,(date '1970-01-01' + w.start_date / (1000 * 60 * 60 * 24) - (6 / 24)) wf_startdte ,(date '1970-01-01' + w.stop_date / (1000 * 60 * 60 * 24) - (6 / 24)) wf_stopdte . . . from workflow.eng_workflow w table for workflow instances where w.name like ('Transfer Evaluation%') and w.current_state = 'stopped.completed'
Reporting from Workflow (subselects for specific tasks) //getting date and time that the task was completed ,(select distinct (date '1970-01-01' + i.stop_date / (1000 * 60 * 60 * 24) - (6 / 24)) from workflow.eng_workitem i table for activities within the workflow where i.wf_id = w.id and i.pd_id = w.pd_id and i.role_id is not null and i.name like ('PostTransferCourseEquivalency')) Post_TrfrCrseEquiv_stopdte //getting user who completed the task ,(select distinct u.last_name || ', ' || u.first_name from workflow.eng_workitem i, workflow.wfuser u table for workflow users where i.wf_id = w.id and i.pd_id = w.pd_id and i.role_id is not null and i.performer_id = u.id and i.name like ('PostTransferCourseEquivalency')) Post_TrfrCrseEquiv_user
Reporting from Workflow • Main Tables – – – –
eng_workflow eng_workitem eng_itemvalues wfuser
• Things to watch out for – Date conversions – Loops in workflow model causes repeated tasks – Test your sql for an individual workflow/task
Questions?
Mary Ellen Hanna Senior Associate University Registrar [email protected]
Jennifer McMillan Enterprise Architect [email protected]
Denny Savage Senior Associate University Registrar [email protected]
