Hosted by: The University of Toledo - PowerPoint Presentation

Hosted by: The University of Toledo October 21, 2019 The Fawcett Center Columbus, Ohio

Laura Bowser – Applications DeveloperLynette Johnson – Associate Registrar lbowser@kent.edu; ljohnson@kent.edu Dust to Digital: Migrating Pre-Banner Hardcopy Forms into the Digital Age

About Us Kent State University is an 8-campus system in Northeast Ohio and is one the largest regional systems in the nation with 6 other U.S. locations and 4 worldwide locations. The Kent State University system serves nine local communities and a population of more than 38,000 students, 10,700+ employees, and 251,000+ alumni worldwide. The Division of Information Technology (IT) is responsible for the strategy, planning, and delivery of information technology across all eight Kent State University campuses and their respective satellite locations. As such, we are instrumental in enabling the day-to-day activities of our University community.

Introduction Imaging is a core function of the University eco-system as it ensures a culture of continuous improvement and the efficient stewardship of university resources and infrastructure. This presentation will describe how Kent State University found a solution to migrating pre-Banner hardcopy forms into an existing  ApplicationXtender application.Learn our process of creating pseudo IDs to handle indexing pre-Banner student documents without a Banner ID.

Agenda Hear our success story with the Registrar’s OfficeLearn our process of using a pseudo ID to index pre-Banner student documents without a Banner ID Technology Used:PL/SQL – Procedural Language/Structured Query LanguagePseudo ID – Alternative Banner IDApplicationXtender - Document Management Solution

Agenda Topics Problem Statements Success StoryPL/SQL Code (Packages, Sequence, and Triggers)ApplicationXtender Process How Pseudo IDs Work in ApplicationXtender

Problem Statement These hard copy student records could potentially date back to 1910.The personal identification indicator data on those records were not consistent Many of the hard copy records are for students who are deceased, so no need to create a new Banner ID for those studentsIf we had to create a new Banner ID on student records, we could potentially be increasing the number of students with multiple IDs

Problem Statement for I.T. We already had an existing 'PRE_1979_TRANSCRIPTS' application for students with Banner IDs; however, it was not using the Key Reference table nor was it built to follow the structure of the Key Reference table Unable to move fields around in AX Admin to retrofit the Key Reference table because there were already records in the tablesA new 'PRE-1979-TRANSCRIPTS' application was built to use the Key Reference Table Migrated existing records and documents from existing 'PRE_1979_TRANSCRIPTS' application into new applicationCopied AE_DAxxx ,  AE_DLxxx ,  AE_DTxxx ,  AE_RHxxx tables and m odified the export data in Excel to match the field format of the new application Copied the .bin files over to the new application Reset the DOCID sequence number in the 'AE_SEQ' table for the corresponding APPID to the last DOCID that was copied over from production

Success Story Benefits of creating a pseudo ID for pre-Banner documentsAllows the Registrar’s Office to index and archive older pre-Banner student documents Frees up office space – once the files have been digitally archived into ApplicationXtender, they can be destroyed which will eliminate the need for filing cabinets taking up office space with these old documentsKent State University’s Registrar’s Office currently has a large secured space dedicated to hard copy files; as well as secured office space at 7 regional campuses. We were able to extend an existing ApplicationXtender application ('PRE-1979-TRANSCRIPTS')

PL/SQL Code A package in Toad was created to keep track of pseudo IDs to assign to the ‘PRE-1979-TRANSCRIPTS’ ApplicationXtender application to add the ‘PI’ pseudo ID in place of Banner IDPseudo ID entries are added into both the respective ApplicationXtender application table and the ‘OTGMGR.RFSCT’ table (Key Reference table) Both tables need to create entries when a pseudo ID is used, and they must be in syncThe first table that calls the package will retrieve the next pseudo ID and set the holding value in v_pseudo_value. The second table that calls the package will grab that value. The boolean variables will control when a new value needs to be generated accounting for when only one table needs the value.

Package Spec for “PSEUDO_ID_ASSIGN” Declare your variables and functions******************************************************************************** v_pseudo_valPI VARCHAR2(9);v_grabPI BOOLEAN;v_rfsct_grabPI BOOLEAN;__________________________________________________________ FUNCTION f_dt594get_pseudoID (type_id IN VARCHAR)RETURN VARCHAR2;__________________________________________________________ FUNCTION f_rfsctget_pseudoID ( type_id IN VARCHAR) RETURN VARCHAR2; END; /

Package Body for “PSUEDO_ID_ASSIGN” Start your “f_dt594get_pseudoID ” function******************************************************************************************************************** FUNCTION f_dt594get_pseudoID (type_id IN VARCHAR) RETURN VARCHAR2 IS v_value VARCHAR2 (9); BEGINIF type_id = 'PI' THEN IF v_pseudo_valPI IS NULL OR ( v_grabPI AND v_rfsct_grabPI ) THEN v_grabPI := FALSE; v_rfsct_grabPI := FALSE; v_pseudo_valPI := NULL; END IF;

Package Body for “PSUEDO_ID_ASSIGN” “f_dt594get_pseudoID ” function continued:******************************************************************************************************************** IF (v_pseudo_valPI IS NULL) OR (v_grabPI) THEN v_max_pseudoID := 0; v_next_ID := 0; SELECT TO_NUMBER (REPLACE (MAX (field1), 'PI', '')) INTO v_max_pseudoID FROM OTGMGR.AE_DT594 WHERE SUBSTR (field1, 0, 2) = 'PI'; SELECT PSEUDOID_PI.NEXTVAL INTO v_next_ID FROM DUAL;

Package Body for “PSUEDO_ID_ASSIGN” “f_dt594get_pseudoID ” function continued:******************************************************************************************************************** WHILE v_next_ID <= v_max_pseudoID LOOP SELECT PSEUDOID_PI.NEXTVAL INTO v_next_ID FROM DUAL; END LOOP; v_value := 'PI' || LPAD (TO_CHAR (v_next_ID ), 7, '0'); v_pseudo_valPI := v_value ; v_grabPI := TRUE; ELSE v_value := v_pseudo_valPI ; v_grabPI := TRUE; END IF; END IF; RETURN v_value ; END f_dt594get_pseudoID;

Package Body for “PSUEDO_ID_ASSIGN” Start your “f_rfsctget_pseudoID ” function******************************************************************************************************************** FUNCTION f_rfsctget_pseudoID (type_id IN VARCHAR) RETURN VARCHAR2 IS v_value VARCHAR2 (9); BEGINIF type_id = 'PI' THEN IF v_pseudo_valPI IS NULL OR ( v_grabPI AND v_rfsct_grabPI ) THEN v_grabPI := FALSE; v_rfsct_grabPI := FALSE; v_pseudo_valPI := NULL; END IF;

Package Body for “PSUEDO_ID_ASSIGN” “f_rfsctget_pseudoID ” function continued:************************************************************************************************************** IF (v_pseudo_valPI IS NULL) OR (v_grabPI) THEN v_max_pseudoID := 0; v_next_ID := 0; SELECT TO_NUMBER (REPLACE (MAX (field1), 'PI', '')) INTO v_max_pseudoID FROM OTGMGR.AE_RFSCT WHERE SUBSTR (field1, 0, 2) = 'PI'; SELECT PSEUDOID_PI.NEXTVAL INTO v_next_ID FROM DUAL;

Package Body for “PSUEDO_ID_ASSIGN” “f_rfsctget_pseudoID ” function continued:******************************************************************************************************************** WHILE v_next_ID <= v_max_pseudoID LOOP SELECT PSEUDOID_PI.NEXTVAL INTO v_next_ID FROM DUAL; END LOOP; v_value := 'PI' || LPAD (TO_CHAR ( v_next_ID ), 7, '0'); v_pseudo_valPI := v_value ; v_grabPI := TRUE; ELSE v_value := v_pseudo_valPI ; v_grabPI := TRUE; END IF; END IF; RETURN v_value ; END f_rfsctget_pseudoID ; END PSEUDO_ID_ASSIGN; /

Create a new Sequence: “PSEUDOID_PI” Create a new Sequence in the OTGMGR schemaName the new SequenceStart with: 1Increment by: 1Min Value: 1 Max Value: 9999999Cache: 0Cycle and Order = No

Create Grants on Sequence: “PSEUDOID_PI” Click on ‘Grants’ tab, then click on ‘View/Edit Privileges’Click into the ‘User or Role Name’ list and type in ‘BANINST1’, then on the ‘Select’ column, select “Yes – g” (for Grant privileges) Click into the ‘User or Role Name’ list and type in ‘PUBLIC’, then on the ‘Select’ column, select “Yes”, then click the “Apply” button (NOTE: There are no Grant privileges on this role)Click the “OK” button for “Changes Applied”

Create Synonyms on Sequence: “PSUEDID_PI” Click on the “Synonyms” tab, and click the “Create Synonym” iconMake sure “Public” is selected, then click the “OK” buttonClick the “Private” radio button, then under “Synonym Owner”, select “BANINST1”, then click the “OK” button

“PSEUDOID_PI” Script will look like this: DROP SEQUENCE OTGMGR.PSEUDOID_PI;-- -- PSEUDOID_PI (Sequence) --CREATE SEQUENCE OTGMGR.PSEUDOID_PI START WITH 4 MAXVALUE 9999999 MINVALUE 1 NOCYCLE NOCACHE NOORDER NOKEEP GLOBAL;

“PSEUDOID_PI” Script will look like this (cont.) ---- PSEUDOID_PI (Synonym) --CREATE OR REPLACE SYNONYM BANINST1.PSEUDOID_PI FOR OTGMGR.PSEUDOID_PI;---- PSEUDOID_PI (Synonym) -- CREATE OR REPLACE PUBLIC SYNONYM PSEUDOID_PI FOR OTGMGR.PSEUDOID_PI;GRANT SELECT ON OTGMGR.PSEUDOID_PI TO BANINST1 WITH GRANT OPTION;GRANT SELECT ON OTGMGR.PSEUDOID_PI TO PUBLIC;

Create a new Trigger: “PSEUDO_ID_ASSIGN_PI” Create a new Trigger in the OTGMGR schemaName the new TriggerSchema: OTGMGRTable: click on the ellipses on Table to locate the appropriate 'AE_DT' table that you want to place the trigger upon. Once you've selected the table, then click the "OK" button Under the 'Basic Info/Fire Control' tab, 'Fire When' - select 'Before'; 'Fire On' - select 'Insert'; For Each: ‘Row’; 'Status' - keep as 'Enabled'; 'For Each; - keep as 'Row'; 'Follows' - there are no triggers to follow; 'Referencing' - keep as 'New As: New', 'Old As: Old', 'Parent As: Parent'; 'Cross Edition' - keep as 'No'; select 'Include Schema Name in SQL'

“PSUEDO_ID_ASSIGN_PI”Trigger will look like this: DROP TRIGGER OTGMGR.PSEUDO_ID_ASSIGN_PI;-- -- PSEUDO_ID_ASSIGN_PI (Trigger) --CREATE OR REPLACE TRIGGER OTGMGR.PSEUDO_ID_ASSIGN_PI BEFORE INSERT ON OTGMGR.AE_DT594 REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROWBEGIN IF :NEW.field1 = 'PI' THEN SELECT PSEUDO_ID_ASSIGN.f_dt594get_pseudoID(:NEW.field1) INTO :NEW.field1 FROM DUAL; END IF; END PSEUDO_ID_ASSIGN_PI; /

Create a new Trigger: “PSEUDO_ID_ASSIGN_PI” Create a new Trigger in the OTGMGR schemaName the new Trigger “PSEUDO_ID_ASSIGN_PI”Schema: OTGMGRTable: click on the ellipses on Table to locate the ‘AE_RFSCT' table to place the trigger upon IT. Once you've selected the table, then click the "OK" button Under the 'Basic Info/Fire Control' tab, 'Fire When' - select 'Before'; 'Fire On' - select 'Insert’; For Each: ‘Row’; 'Status' - keep as 'Enabled'; 'For Each; - keep as 'Row'; 'Follows' - there are no triggers to follow; 'Referencing' - keep as 'New As: New', 'Old As: Old', 'Parent As: Parent'; 'Cross Edition' - keep as 'No'; select 'Include Schema Name in SQL'

“RFSCT_PSEUDOID_ASSIGN ”Trigger will look like this:DROP TRIGGER OTGMGR.RFSCT_PSEUDOID_ASSIGN; ---- RFSCT_PSEUDOID_ASSIGN (Trigger) --CREATE OR REPLACE TRIGGER OTGMGR.RFSCT_PSEUDOID_ASSIGN BEFORE INSERT ON OTGMGR.AE_RFSCT REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROWBEGIN IF :NEW.field1 = 'PI' THEN SELECT PSEUDO_ID_ASSIGN.f_rfsctget_pseudoID (:NEW.field1) INTO :NEW.field1 FROM DUAL; END IF; END RFSCT_PSEUDOID_ASSIGN; /

Summary on code: Create the new Package: “PSEUDO_ID_ASSIGN”Be sure to compile the package (NOTE: You want to be sure that after compiling, it says “2 packages successfully compiled.”Create the new Sequence “PSEUDOID_PI”Create the new Triggers “PSEUDO_ID_ASSIGN_PI” and “RFSCT_PSEUDOID_ASSIGN” Once those are all in place, then you can test it in the application

ApplicationXtender application:‘PRE-1979-TRANSCRIPTS’ Fields are ordered to use the Key Reference table (AE_RFSCT)

How Pseudo ID Works ‘PRE-1979-TRANSCRIPTS’ application in ApplicationXtender

How Pseudo ID Works (cont.) Upload a New Document to the applicationIndex the documentType in ‘PI’ in the Banner IDType in ‘PI’ and nothing elseIt is not case sensitive Fill in all required fieldsFields with red asteriskClick the “Save” button

How Pseudo ID Works (cont.) New pseudo ID is created

Attaching Pages to an Existing Pseudo ID Index On the Index Panel, type in PI* in the ‘Banner ID’ field and click the ‘Select Index’ icon

Attaching Pages to an Existing Pseudo ID Index (cont.) A window with existing indexes will openYou can either choose to “Attach” to attach the page to the highlighted index or you can “Select” a particular index value

Query Search for PI Number On the root of the application, click the “NEW QUERY” buttonType in PI* in ‘Banner ID’ fieldClick the “RUN” button

Query Results for PI Number Query Results will bring back a list of Query Results of pseudo IDs (PI numbers)

Pseudo IDs are Indexed in the ‘PRE-1979-TRANSCRIPTS’ table Pseudo IDs get added in the ‘OTGMGR.AE_DT594’ table

Pseudo IDs Appended in the Key Reference table Pseudo IDs get added in the ‘OTGMGR.AE_RFSCT’ table

Creating Additional Pseudo IDs You can create additional pseudo IDs for other applicationsAdd them to the existing ‘PSEUDO_ID_ASSIGN’ packageIt’s best practice to give each application’s pseudo ID a unique ID Do not reuse ‘PI’ for other applicationsExample: Recreational Services gets a ‘RS’ number, College of Business gets a ‘BS’ number, etceteraThe reason to keep them separated is because in the Trigger statement, you’re calling for the trigger to fire on a particular table associated with the application (see slide 24)A new Sequence will need to be created for additional pseudo IDs A new Trigger will need to be created for additional pseudo IDsNew pseudo IDs will need to be added to trigger ‘RFSCT_PSEUDOID_ASSIGN’

Summary Pseudo IDs allow you to index pre-Banner ID student documents along with existing students in one applicationPL/SQL Package, Sequence, and Triggers are created to allow the use of pseudo ID in place of Banner ID Pseudo ID entries are added into both the respective ApplicationXtender application table and the ‘OTGMGR.RFSCT’ table (Key Reference table)

Questions

For more information, you may contact:lbowser@kent.edu ; ljohnson@kent.edu