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

Hosted by:

The University of Toledo

October 21, 2019

The Fawcett Center

Columbus, Ohio

Laura Bowser – Applications Developer

Lynette Johnson – Associate Registrar

ljohnson@kent.edu lbowser@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 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 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 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

•

‘

’

‘

’

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

•

‘

’

•

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)

Questions

For more information, you may contact:

lbowser@kent.edu

ljohnson@kent.edu

Slide Note

Embed Share

Download

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)