Transcript Spriden ID Change - Mississippi Banner Users Group

Moving From Social
Security Numbers
Rickey Creel and
Phillip Brooks
The following code is provided "as-is" with no warranty, guarantee or any other promise.





Why
When
What
How
Questions
Why
Why

The Federal Trade Commission (FTC) requires that all
financial institutions establish policies and procedures for
safeguarding customer financial information. This
mandate requires that Educational Entities, as financial
institutions, must comply with the Gramm-Leach-Bliley
Act (GLBA). This legislation is composed of several parts,
including: the Privacy Rule and the Safeguards Rule. The
FTC has ruled that being in compliance with the Family
Educational Rights and Privacy Act (FERPA) satisfies the
privacy requirement of the GLBA. However, there is no
similar exception for the Safeguards Rule. Meridian
Community College must comply with the Safeguards
Rule.
Why



Ensure the security and confidentiality of
customer records and information
Protect against any anticipated threats to
the security or integrity of such records
Protect against unauthorized access to, or
use of, such records or information that
could result in substantial harm or
inconvenience to any customer
When
When

Institutions must implement an
information security program no later than
May 23, 2003
What





Social Security Numbers
Employee applications, transcripts, copies
of driver’s licenses and social security
cards
Employee contracts
Credit card forms
Class rolls, attendance sheets
What







Test results
Discipline records
Housing records
Transcripts and applications
Student statements
Credit card requests
Income and tax documents
What




Birth certificates, marriage licenses
Criminal background check data
Drug screen results
Child abuse registry checks and finger
print cards
Problems




Physical security – many areas which have
access to critical information are open to
the public and not secured by doors
Student workers
Shredding access is limited
Open verbal exchange of restricted
information
Problems


PC’s with access to Banner left unattended
Social Security numbers are used for ID
How
How



Employee training
Provide adequate shredders in all areas
Change ID to generated ID
Problems
Problems

Admissions
Problems

ID System
Problems

Payroll
Problems

Meal Plans
Problems

Library System
Problems

Records
Problems

When to make the change
Spriden ID Change
Spriden ID Change

Change Current Social Security Number ID’s to
Portal ID’s
Spriden ID Change


Change Current Social Security Number ID’s to
Portal ID’s
Change non Social security ID’s to a generated ID’s
Spriden ID Change



Change Current Social Security Number ID’s to
Portal ID’s
Change non Social security ID’s to a generated ID’s
Convert Current Generated ID’s from “@” ID’s to
“M” ID’s
Spriden ID Change




Change Current Social Security Number ID’s to
Portal ID’s
Change non Social security ID’s to a generated ID’s
Convert Current Generated ID’s from “@” ID’s to
“M” ID’s
Update Banner to use a “M” for generated ID’s in
the Future
Spriden ID Change





Change Current Social Security Number ID’s to
Portal ID’s
Change non Social security ID’s to a generated ID’s
Convert Current Generated ID’s from “@” ID’s to
“M” ID’s
Update Banner to use a “M” for generated ID’s in
the Future
Create Triggers
Spriden ID Change






Change Current Social Security Number ID’s to
Portal ID’s
Change non Social security ID’s to a generated ID’s
Convert Current Generated ID’s from “@” ID’s to
“M” ID’s
Update Banner to use a “M” for generated ID’s in
the Future
Create Triggers
Banner Examples
Spriden ID Change







Change Current Social Security Number ID’s to
Portal ID’s
Change non Social security ID’s to a generated ID’s
Convert Current Generated ID’s from “@” ID’s to
“M” ID’s
Update Banner to use a “M” for generated ID’s in
the Future
Create Triggers
Banner Examples
Change form for persons not receiving a Portal ID
Change Current Social Security
Number ID’s to Portal ID’s
The following code is provided "as-is" with no warranty, guarantee or any other promise.

DECLARE
v_stu_pidm
gobtpac.gobtpac_pidm%TYPE;
v_stu_ext_id gobtpac.gobtpac_external_user%TYPE;
v_stu_spr_id spriden.spriden_id%TYPE;
cursor W_stuid IS
SELECT A.GOBTPAC_PIDM,
UPPER(substr(a.gobtpac_external_user,1,9)),
spriden_id
FROM gobtpac A, spriden
WHERE A.GOBTPAC_PIDM = spriden_pidm
AND spriden_id <> UPPER(substr(a.gobtpac_external_user,1,9))
AND spriden_change_ind IS NULL
AND (spriden_id = &upd_spriden_id
OR &upd_spriden_id = 'ALL')
ORDER BY spriden_id;
BEGIN
OPEN W_stuid;
LOOP
FETCH W_stuid INTO v_stu_pidm, v_stu_ext_id, v_stu_spr_id;
INSERT INTO spriden
(SELECT cpy.spriden_pidm,
v_stu_ext_id,
cpy.spriden_last_name,
cpy.spriden_first_name,
cpy.spriden_mi,
NULL,
cpy.spriden_entity_ind,
SYSDATE,
'IDCONVCP',
'SPAIDEN',
cpy.spriden_search_last_name,
cpy.spriden_search_first_name,
cpy.spriden_search_mi,
cpy.spriden_soundex_last_name,
cpy.spriden_soundex_first_name,
cpy.spriden_ntyp_code,
'idconvcp',
SYSDATE,
cpy.spriden_data_origin
FROM spriden cpy
where cpy.spriden_pidm = v_stu_pidm
AND cpy.spriden_change_ind IS NULL);
COMMIT;
update spriden a
set a.spriden_change_ind = 'I'
where a.spriden_pidm = v_stu_pidm
AND a.spriden_id = v_stu_spr_id
AND spriden_change_ind IS NULL;
COMMIT;
EXIT WHEN W_stuid%NOTFOUND;
END LOOP; -- wstuid
CLOSE W_stuid;
COMMIT;
END;
Change non Social security ID’s to
a generated ID’s
The following code is provided "as-is" with no warranty, guarantee or any other promise.

DECLARE
v_stu_pidm
v_stu_ext_id
v_stu_spr_id
spriden.spriden_pidm%TYPE;
spriden.spriden_id%TYPE;
spriden.spriden_id%TYPE;
cursor W_stuid IS
SELECT a.spriden_pidm,
a.spriden_id
FROM spriden a
WHERE substr(a.spriden_id,1,1) IN ('1','2','3','4','5','6','7','8','9','0',' ')
AND spriden_change_ind IS NULL
AND (spriden_id = &upd_spriden_id
OR &upd_spriden_id = 'ALL')
ORDER BY a.spriden_id;
cursor W_gen_id IS
SELECT 'M'||lpad(seqt.sobseqn_maxseqno + 1,8,0)
INTO v_stu_ext_id
FROM sobseqn seqt
WHERE seqt.sobseqn_function = 'ID';
BEGIN
LOOP
OPEN W_stuid;
FETCH W_stuid INTO v_stu_pidm, v_stu_spr_id;
EXIT WHEN W_stuid%NOTFOUND;
OPEN w_gen_id;
FETCH W_gen_id INTO v_stu_ext_id;
CLOSE w_gen_id;
UPDATE sobseqn seqt2
SET seqt2.sobseqn_maxseqno = seqt2.sobseqn_maxseqno + 1
WHERE seqt2.sobseqn_function = 'ID';
COMMIT;
INSERT INTO spriden
(SELECT cpy.spriden_pidm,
v_stu_ext_id,
cpy.spriden_last_name,
cpy.spriden_first_name,
cpy.spriden_mi,
NULL,
cpy.spriden_entity_ind,
SYSDATE,
'IDCONVCP',
'SPAIDEN',
cpy.spriden_search_last_name,
cpy.spriden_search_first_name,
cpy.spriden_search_mi,
cpy.spriden_soundex_last_name,
cpy.spriden_soundex_first_name,
cpy.spriden_ntyp_code,
'idconvcp',
SYSDATE,
cpy.spriden_data_origin
FROM spriden cpy
where cpy.spriden_pidm = v_stu_pidm
AND cpy.spriden_change_ind IS NULL);
COMMIT;
update spriden a
set a.spriden_change_ind = 'I'
where a.spriden_pidm = v_stu_pidm
AND a.spriden_id = v_stu_spr_id
AND spriden_change_ind IS NULL;
COMMIT;
EXIT WHEN W_stuid%NOTFOUND;
CLOSE W_stuid;
END LOOP; -- wstuid
END;
Convert Current Generated ID’s
from “@” ID’s to “M” ID’s
The following code is provided "as-is" with no warranty, guarantee or any other promise.

DECLARE
v_stu_pidm
gobtpac.gobtpac_pidm%TYPE;
v_stu_ext_id spriden.spriden_id%TYPE;
v_stu_spr_id spriden.spriden_id%TYPE;
cursor W_stuid IS
SELECT a.spriden_pidm,
a.spriden_id,
'M'||SUBSTR(A.SPRIDEN_ID,2,8)
FROM spriden a
WHERE substr(a.spriden_id,1,1) = '@'
AND spriden_change_ind IS NULL
AND (spriden_id = &upd_spriden_id
OR &upd_spriden_id = 'ALL');
BEGIN
OPEN W_stuid;
LOOP
FETCH W_stuid INTO v_stu_pidm, v_stu_spr_id, v_stu_ext_id;
EXIT WHEN W_stuid%NOTFOUND;
INSERT INTO spriden
(SELECT cpy.spriden_pidm,
'M'||substr(v_stu_spr_id,2,8),
cpy.spriden_last_name,
cpy.spriden_first_name,
cpy.spriden_mi,
NULL,
cpy.spriden_entity_ind,
SYSDATE,
'IDCONVCP',
'SPAIDEN',
cpy.spriden_search_last_name,
cpy.spriden_search_first_name,
cpy.spriden_search_mi,
cpy.spriden_soundex_last_name,
cpy.spriden_soundex_first_name,
cpy.spriden_ntyp_code,
'idconvcp',
SYSDATE,
cpy.spriden_data_origin
FROM spriden cpy
where cpy.spriden_pidm = v_stu_pidm
AND cpy.spriden_change_ind IS NULL);
COMMIT;
update spriden a
set a.spriden_change_ind = 'I'
where a.spriden_pidm = v_stu_pidm
AND a.spriden_id = v_stu_spr_id
AND spriden_change_ind IS NULL;
COMMIT;
EXIT WHEN W_stuid%NOTFOUND;
END LOOP; -- wstuid
CLOSE W_stuid;
END;
Update Banner to use a “M” for
generated ID’s in the Future
The following code is provided "as-is" with no warranty, guarantee or any other promise.

UPDATE sobseqn seqt
SET seqt.sobseqn_seqno_prefix = 'M'
WHERE seqt.sobseqn_function = 'ID'
Create Triggers


Trigger to process a newly created Portal
ID’s
Trigger to process updates to a Portal ID’s
Trigger to process a newly created Portal ID’s
The following code is provided "as-is" with no warranty, guarantee or any other promise.

Create or replace trigger "GENERAL".GOBTPAC_UPDATE_SPRIDEN_ID
after insert on GOBTPAC
for each row
declare
-- local variables here
BEGIN
IF :new.gobtpac_external_user IS NOT NULL THEN
INSERT INTO SATURN.spriden
(SELECT cpy.spriden_pidm,
SUBSTR(upper(:new.gobtpac_external_user),1,9),
cpy.spriden_last_name,
cpy.spriden_first_name,
cpy.spriden_mi,
NULL,
cpy.spriden_entity_ind,
SYSDATE,
'ID_GOBTPAC',
'GOATPAD',
cpy.spriden_search_last_name,
cpy.spriden_search_first_name,
cpy.spriden_search_mi,
cpy.spriden_soundex_last_name,
cpy.spriden_soundex_first_name,
cpy.spriden_ntyp_code,
'ID_GOBTPAC',
SYSDATE,
cpy.spriden_data_origin
FROM SATURN.spriden cpy
where cpy.spriden_pidm = :NEW.GOBTPAC_pidm
AND cpy.spriden_change_ind IS NULL);
update SATURN.spriden a
set a.spriden_change_ind = 'I'
where a.spriden_pidm = :NEW.GOBTPAC_pidm
AND a.spriden_id <> SUBSTR(upper(:new.gobtpac_external_user),1,9)
AND spriden_change_ind IS NULL;
END IF;
end GOBTPAC_insert_to_goremal;
Trigger to process updates to a Portal ID’s
The following code is provided "as-is" with no warranty, guarantee or any other promise.

create or replace trigger "GENERAL".GOBTPAC_UPDATE_SPRIDEN_ID2
after UPDATE on GOBTPAC
for each row
declare
-- local variables here
BEGIN
IF :new.gobtpac_external_user IS NOT NULL
AND :new.gobtpac_external_user <> :old.gobtpac_external_user THEN
INSERT INTO spriden
(SELECT cpy.spriden_pidm,
SUBSTR(upper(:new.gobtpac_external_user),1,9),
cpy.spriden_last_name,
cpy.spriden_first_name,
cpy.spriden_mi,
NULL,
cpy.spriden_entity_ind,
SYSDATE,
'ID_GOBTPAC',
'GOATPAD',
cpy.spriden_search_last_name,
cpy.spriden_search_first_name,
cpy.spriden_search_mi,
cpy.spriden_soundex_last_name,
cpy.spriden_soundex_first_name,
cpy.spriden_ntyp_code,
'ID_GOBTPAC',
SYSDATE,
cpy.spriden_data_origin
FROM spriden cpy
where cpy.spriden_pidm = :NEW.GOBTPAC_pidm
AND cpy.spriden_change_ind IS NULL);
update spriden a
set a.spriden_change_ind = 'I'
where a.spriden_pidm = :NEW.GOBTPAC_pidm
AND a.spriden_id <> SUBSTR(upper(:new.gobtpac_external_user),1,9)
AND spriden_change_ind IS NULL;
END IF;
end GOBTPAC_insert_to_goremal;
Spriden Rows
Spaiden form with social entered
Spaiden automatically converts to new id, by
using a alternate id search
Goatpad form for BBunny
Change form for persons not
receiving a Portal ID
Questions