Migrating Pre-Banner Hardcopy Forms into the Digital Age at Kent State University

Slide Note

Kent State University's Division of Information Technology presents a successful solution for migrating pre-Banner hardcopy forms into a digital format. Discover the process of creating pseudo IDs to handle document indexing without new Banner IDs. Learn about the technology used and the strategic approach taken in this innovative project.

aust_226 Follow

Uploaded on Sep 23, 2024 | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

E N D

Presentation Transcript

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

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

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 Office Learn 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 Language Pseudo ID Alternative Banner ID ApplicationXtender - Document Management Solution

Agenda Topics 1. Problem Statements 2. Success Story 3. PL/SQL Code (Packages, Sequence, and Triggers) 4. ApplicationXtender Process 5. 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 students If 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 tables A 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 application Copied AE_DAxxx, AE_DLxxx, AE_DTxxx, AE_RHxxx tables and modified 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 documents Allows 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 documents Kent 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 ID Pseudo 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 sync The 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); BEGIN IF 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); BEGIN IF 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 schema Name the new Sequence Start with: 1 Increment by: 1 Min Value: 1 Max Value: 9999999 Cache: 0 Cycle 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 icon Make sure Public is selected, then click the OK button Click 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 schema Name the new Trigger Schema: OTGMGR Table: 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 ROW BEGIN 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 schema Name the new Trigger PSEUDO_ID_ASSIGN_PI Schema: OTGMGR Table: 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 ROW BEGIN 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 application Index the document Type in PI in the Banner ID Type in PI and nothing else It is not case sensitive Fill in all required fields Fields with red asterisk Click 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 open You 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 button Type in PI*in Banner ID field Click 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 applications Add them to the existing PSEUDO_ID_ASSIGN package It s best practice to give each application s pseudo ID a unique ID Do not reuse PI for other applications Example: Recreational Services gets a RS number, College of Business gets a BS number, etcetera The 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 IDs New 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 application PL/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)