|
|
Cooperative Extension Service |
|
 |
||
|
|
||
|
Agricultural
Experiment Station |
||
 |
|
|
Below are the steps we did to convert our employee ID's from SSN to generated ID's. For the generated ID's, we used a 6-digit ID based on PIDM ("9" followed by 5-digit left zero filled pidm). I've gone back through my notes to find all that I did for it. I think I've got most of it below. This is very long and detailed. Be sure to change the names and e-mail addresses below to your own support people's names and e-mail addresses - don't point them to me! Installing the Employee ID Lookup screen (below) and the Self Service PIN Reset screen (in the Adding Web Pages to the Self Service Products web page) are optional.
1) Generated the new spriden records with the new employee ID's for all current and former employees. Flagged the old spriden records as having their ID numbers changed. Sent out e-mails to all current employees who have an e-mail in Banner with their new Employee ID. (Note: Use s2 instead of s1 in the update command if you are running this in a test database and want to keep the old activity date for the SSN ID records.) SQL shown below:
insert into spriden (select spriden_pidm,
'9' || lpad(to_char(spriden_pidm),5,'0'),
spriden_last_name,spriden_first_name,spriden_mi,null,
spriden_entity_ind,sysdate,'EMPIDCONV',spriden_origin,
spriden_search_last_name,spriden_search_first_name,
spriden_search_mi,spriden_soundex_last_name,
spriden_soundex_first_name,spriden_ntyp_code
from spriden where spriden_change_ind is null
and spriden_entity_ind = 'P'
and length(spriden_id) = 9
and substr(spriden_id,1,1) between '0' and '9'
and spriden_id != '000000000'
and exists (select 'x' from spbpers
where spbpers_pidm = spriden_pidm
and spbpers_ssn = spriden_id));
update spriden s1 set (spriden_change_ind,spriden_user,spriden_activity_date) =
(select 'I',nvl(s1.spriden_user,'') || '_EMPIDCONV',s1.spriden_activity_date
-- or s2.spriden_activity_date
from spriden s2 where s1.spriden_pidm = s2.spriden_pidm
and nvl(s2.spriden_user,'~') = 'EMPIDCONV')
where spriden_change_ind is null
and spriden_entity_ind = 'P'
and length(spriden_id) = 9
and substr(spriden_id,1,1) between '0' and '9'
and spriden_id != '000000000'
and exists (select 'x' from spbpers
where spbpers_pidm = spriden_pidm
and spbpers_ssn = spriden_id);
set linesize 90 trimspool on heading off feedback off echo off pagesize 0 recsep off
spool employee_id_mail.shl
select 'mail -s "Your New Employee ID Number" ' || goremal_email_address ||
' <<EOF' || chr(10) ||
'To protect your personal information, we have changed from social security' || chr(10) ||
'number to a generated employee ID as the primary identifier in Banner. You' || chr(10) ||
'should begin immediately to use this ID on forms and to log into Banner Self' || chr(10) ||
'Service to do your time sheets (your PIN will be the same).' || chr(10) || chr(10) ||
'This change will not affect the username you use to log into Banner/WebForms,' || chr(10) ||
'e-Print and sqlplus, which is still your first initial and last name in most cases.' || chr(10) ||
'Please make a note of your new employee ID number, as shown below:' || chr(10) || chr(10) ||
' The Employee ID for ' || spriden_first_name || ' ' || spriden_last_name ||
' is ' || spriden_id || chr(10) || chr(10) ||
'Please do not reply to this e-mail. It was generated by a computer program.' || chr(10) ||
'If you need further information, please contact <YOUR NAME> (<YOUR EMAIL>)' || chr(10) ||
'or <MANAGER NAME> (<MANAGER EMAIL>).' || chr(10) || 'EOF'
from spriden,goremal,pebempl
where spriden_pidm = goremal_pidm
and spriden_pidm = pebempl_pidm
and spriden_change_ind is null
and spriden_user = 'EMPIDCONV'
and pebempl_empl_status = 'A'
and goremal_emal_code = 'SMTP';
spool off
set linesize 80 trimspool off heading on feedback on echo on pagesize 24 recsep wrapped
While testing the generated ID's in a test database, you can switch the active ID from the generated ID to SSN by doing the following:
update spriden set spriden_change_ind = 'X' where spriden_change_ind is null and nvl(spriden_user,'~') = 'EMPIDCONV'; update spriden s1 set spriden_change_ind = null where spriden_change_ind = 'I' and nvl(spriden_user,'~') like '%_EMPIDCONV'; update spriden set spriden_change_ind = 'I' where spriden_change_ind = 'X';
Or, you can switch the active ID from SSN to the generated ID by doing the following:
update spriden set spriden_change_ind = 'X' where spriden_change_ind = 'I' and nvl(spriden_user,'~') = 'EMPIDCONV'; update spriden s1 set spriden_change_ind = 'I' where spriden_change_ind is null and nvl(spriden_user,'~') like '%_EMPIDCONV'; update spriden set spriden_change_ind = null where spriden_change_ind = 'X';
Or, you can remove the generated ID's and switch the active ID to SSN (note that the spriden_activity_date doesn't go back to it's original value - but see comment above about using s2 instead of s1):
delete from spriden where nvl(spriden_user,'~') = 'EMPIDCONV'; update spriden set spriden_user = replace(spriden_user,'_EMPIDCONV'), spriden_change_ind = null where nvl(spriden_user,'~') like '%_EMPIDCONV';
2) Modified PPAIDEN to handle the generated employee ID's. Below are the changes we made to ppaiden.fmb (version 5.4.0.1, plus additional Banner version 6 or version 7 changes below) to convert it from SSN to PIDM-based ID's (6 digits, with "9" followed by 5-digit left zero filled PIDM), so that:
Clicking on Next Block when the ID field is empty will put GENERATE in the ID fields (PPAIDEN gives an error).
Putting an SSN of an existing employee into the ID field and pressing Tab or Next Block will change the ID field to the employee's pidm-based ID.
Putting an SSN of an employee not in Banner yet into the ID field and pressing Next Block will change the ID fields to GENERATE and will copy the SSN to the SSN/SIN/TFN field.
Clicking on the Save button when GENERATE is in the ID fields will change the ID fields to the pidm-based employee ID (instead of the SOBSEQN-based ID sequence number).
For Banner 5 and 6:
Data blocks | KEY_BLOCK | Items | ID | Triggers | WHEN-VALIDATE-ITEM
Added at beginning between "end if;" and "IF (:ID = 'GENERATED') THEN":
For Banner 7:
Data blocks | KEY_BLOCK | Items | ID | Triggers | VALIDATE_ID
Added at beginning between ":ENTITY_IND := '';" and "IF :KEY_BLOCK.ID = 'NEXT' THEN":
/* This was changed to generate a 6-digit ID beginning with "9" followed by the pidm
(5 digits), instead of using the ID sequence number in SOBSEQN. This first block
changes the ID to the SPRIDEN_ID if an existing SSN is entered. The next block forces
the ID to be generated if the given ID isn't found in SPRIDEN. (by SREA, 4/9/04) */
DECLARE
CURSOR PTI_CURSOR IS
SELECT SPRIDEN_ID
FROM SPRIDEN,SPBPERS
WHERE SPRIDEN_PIDM = SPBPERS_PIDM
AND SPRIDEN_CHANGE_IND IS NULL
AND SPBPERS_SSN = :KEY_BLOCK.ID;
ssn_id VARCHAR2(9) := '';
BEGIN
IF (NVL(:GLOBAL.FORM_WAS_CALLED, 'N') = 'N') THEN
-- ppaiden is called directly
IF (:KEY_BLOCK.ID IS NOT NULL) THEN
OPEN PTI_CURSOR ;
FETCH PTI_CURSOR INTO ssn_id;
CLOSE PTI_CURSOR ;
END IF;
IF ssn_id IS NOT NULL AND :ID != ssn_id THEN
MESSAGE('*WARNING* Previous ID (SSN) replaced with current ID.');
:ID := ssn_id;
END IF;
END IF;
END;
DECLARE
CURSOR PTI_CURSOR IS
SELECT COUNT(*)
FROM SPRIDEN
WHERE SPRIDEN_ID = :KEY_BLOCK.ID;
temp_count integer := 0;
BEGIN
IF (:KEY_BLOCK.ID IS NOT NULL) AND (:KEY_BLOCK.ID != 'GENERATED')
AND (NVL(:GLOBAL.FORM_WAS_CALLED, 'N') = 'N') THEN
-- ID is not null and ppaiden is called directly
OPEN PTI_CURSOR ;
FETCH PTI_CURSOR INTO temp_count;
CLOSE PTI_CURSOR ;
IF temp_count = 0 THEN -- ID does not exists in spriden
:SOCIAL_SECURITY_NUMBER := :ID;
:GLOBAL.PRE_SSN := :SOCIAL_SECURITY_NUMBER;
:ID := 'GENERATED';
:ENTITY_IND := ''; -- Comment out for Banner 7
:KEY_BLOCK.PIDM := ''; -- Comment out for Banner 7
:PIDM := '';
:FULL_NAME := ''; -- Comment out for Banner 7
:GLOBAL.NEW_ID := 'Y';
:KEY_BLOCK.PREVIOUS_ID_FOUND := ''; -- Comment out for Banner 7
RETURN ;
END IF;
END IF ;
END ;
Data blocks | KEY_BLOCK | Items | ID | Triggers | KEY-NEXT-ITEM:
Changed the code to:
DECLARE
save_id VARCHAR2(9) := '';
BEGIN
save_id := :ID;
G$_IDNAME_SEARCH.ID_SEARCH('ID');
/* Check and warn about previous ID replaced with current ID, to match what happens
when a previous ID is selected from the Name dropdown list. (by SREA, 4/9/04) */
IF save_id IS NOT NULL AND :ID != save_id THEN
MESSAGE('*WARNING* Previous ID replaced with current ID.');
END IF;
IF :ENTITY_IND = '' THEN
GO_BLOCK('SPRIDENT_CURRENT');
CLEAR_BLOCK;
GO_BLOCK('KEY_BLOCK');
END IF;
END;
Data Blocks | KEY_BLOCK | Triggers | KEY-NXTBLK:
Added at end before "NEXT_BLOCK;":
/* This was changed to generate a 6-digit ID beginning with "9" followed by the pidm
(5 digits), instead of using the ID sequence number in SOBSEQN. This block forces
the ID to be generated if no ID is given. (by SREA, 4/9/04) */
IF (:ID IS NULL) THEN
:ID := 'GENERATED';
:ENTITY_IND := '';
:GLOBAL.PRE_SSN := '';
:SOCIAL_SECURITY_NUMBER := '';
:KEY_BLOCK.PIDM := '';
:FULL_NAME := '';
:GLOBAL.NEW_ID := 'Y';
:KEY_BLOCK.PREVIOUS_ID_FOUND := ''; -- Comment out for Banner 7
END IF ;
Program Units | SELECT_SOBSEQN_1 (Banner 5 and 6 only):
Added at beginning between "BEGIN" and "OPEN PTI_CURSOR":
/* This was changed to generate a 6-digit ID beginning with "9" followed by the pidm
(5 digits) for persons, instead of using the ID sequence number in SOBSEQN (by SREA,
4/9/04) */
IF :ENTITY_IND = 'P' THEN
:SPRIDEN_CURRENT.SPRIDEN_ID :=
'9' || LPAD(TO_CHAR(:SPRIDEN_CURRENT.SPRIDEN_PIDM),5,'0');
RETURN FALSE;
END IF;
Program Units | UPDATE_SOBSEQN_3 (Banner 5 and 6 only):
Added at beginning between "BEGIN" and "UPDATE SOBSEQN":
/* This was changed to generate a 6-digit ID beginning with "9" followed by the pidm
(5 digits) for persons, instead of using the ID sequence number in SOBSEQN, so the
ID sequence number update isn't needed for persons (by SREA, 4/9/04) */
IF IVAR_1 = 'ID' AND :ENTITY_IND = 'P' THEN
RETURN;
END IF;
Data Blocks | SPRIDEN_CURRENT | Items | SPRIDEN_ID | Triggers | PRE-TEXT-ITEM:
Added this trigger containing:
/* Don't allow updates of generated ID's (person or non-person). (by SREA, 4/9/04) */
DECLARE
ID_PREFIX SOBSEQN.SOBSEQN_SEQNO_PREFIX%TYPE := NULL;
CURSOR PTI_CURSOR IS
SELECT SOBSEQN_SEQNO_PREFIX
FROM SOBSEQN
WHERE SOBSEQN_FUNCTION = 'ID';
BEGIN
G$_CHECK_QUERY_MODE;
IF (:GLOBAL.QUERY_MODE != '1') THEN
OPEN PTI_CURSOR;
FETCH PTI_CURSOR INTO ID_PREFIX;
CLOSE PTI_CURSOR;
IF (:SPRIDEN_CURRENT.SPRIDEN_ID IS NOT NULL
AND ((SUBSTR(:SPRIDEN_CURRENT.SPRIDEN_ID,1,1) = '9'
AND LENGTH(:SPRIDEN_CURRENT.SPRIDEN_ID) = 6)
OR (:SPRIDEN_CURRENT.SPRIDEN_ID = 'GENERATED')
OR (ID_PREFIX IS NOT NULL
AND SUBSTR(:SPRIDEN_CURRENT.SPRIDEN_ID,1,1) = ID_PREFIX))) THEN
SET_ITEM_PROPERTY('SPRIDEN_CURRENT.SPRIDEN_ID',INSERT_ALLOWED,PROPERTY_FALSE);
SET_ITEM_PROPERTY('SPRIDEN_CURRENT.SPRIDEN_ID',UPDATE_ALLOWED,PROPERTY_FALSE);
:GLOBAL.QUERY_MODE := '9'; -- Flag for POST-TEXT-ITEM
END IF;
END IF ;
END;
Data Blocks | SPRIDEN_CURRENT | Items | SPRIDEN_ID | Triggers | POST-TEXT-ITEM:
Added this trigger containing:
/* Reverse PRE-TEXT-ITEM setting. (by SREA, 4/9/04) */
IF (:GLOBAL.QUERY_MODE = '9') THEN
SET_ITEM_PROPERTY('SPRIDEN_CURRENT.SPRIDEN_ID',INSERT_ALLOWED,PROPERTY_TRUE);
SET_ITEM_PROPERTY('SPRIDEN_CURRENT.SPRIDEN_ID',UPDATE_ALLOWED,PROPERTY_TRUE);
:GLOBAL.QUERY_MODE := '0';
END IF ;
Additional Banner 6 (only) changes (which replaced calls to UPDATE_SOBSEQN_3 and SELECT_SOBSEQN_1 with :SPRIDEN_CURRENT.SPRIDEN_ID := F_GENERATE_SOBSEQN('ID');):
Data Blocks | SPRIDEN_CURRENT | Triggers | PRE-INSERT and
Data Blocks | SPRIDEN_CURRENT | Triggers | PRE-UPDATE:
Changed:
-- This function retrieves the generated ID from SOBSEQN
BEGIN
:SPRIDEN_CURRENT.SPRIDEN_ID := F_GENERATE_SOBSEQN('ID');
END;
To:
-- This function retrieves the generated ID from SOBSEQN
BEGIN
/* This was changed to generate a 6-digit ID beginning with "9" followed by the pidm
(5 digits) for persons, instead of using the ID sequence number in SOBSEQN (by SREA,
10/11/04) */
IF :ENTITY_IND = 'P' THEN
:SPRIDEN_CURRENT.SPRIDEN_ID :=
'9' || LPAD(TO_CHAR(:SPRIDEN_CURRENT.SPRIDEN_PIDM),5,'0');
ELSE
:SPRIDEN_CURRENT.SPRIDEN_ID := F_GENERATE_SOBSEQN('ID');
END IF;
END;
Additional Banner 7 (only) changes (which removed calls to F_GENERATE_SOBSEQN):
Data Blocks | SPRIDEN_CURRENT | Triggers | PRE-UPDATE:
Changed:
:SPRIDEN_CURRENT.SPRIDEN_ID := GB_COMMON.F_GENERATE_ID;
To:
/* This was changed to generate a 6-digit ID beginning with "9" followed by the pidm (5 digits)
for persons, instead of using the ID sequence number (by SREA, 10/11/04) */
IF :ENTITY_IND = 'P' THEN
:SPRIDEN_CURRENT.SPRIDEN_ID := '9' || LPAD(TO_CHAR(:SPRIDEN_CURRENT.SPRIDEN_PIDM),5,'0');
ELSE
:SPRIDEN_CURRENT.SPRIDEN_ID := GB_COMMON.F_GENERATE_ID;
END IF;
(Note: Don't change: Data Blocks | SPRIDEN_CURRENT | Triggers | PRE-INSERT, since the
p_create_first_ever_record routine (called by P_CREATE) expects SPRIDEN_ID to have the
value 'GENERATED'.)
(end of changes to ppaiden.fmb)
For Banner 7 (only), also changed p_create_first_ever_record routine in $BANNER_LINKS/gokb_ident1.sql (called through P_CREATE in the ON-INSERT trigger of SPRIDEN_CURRENT), first swapping the ID and PIDM generation sections, then using the generated PIDM to create a generated ID for person records:
Changed:
PROCEDURE p_create_first_ever_record
IS
BEGIN
IF (p_id_inout = 'GENERATED') THEN
p_id_inout := gb_common.f_generate_id;
ELSIF (gb_common.f_id_exists (p_id_inout) = 'Y')
THEN
-- If id passed in, make sure it doesn't already exist.
p_raise_error ('ID_EXISTS');
END IF;
-- If no pidm, generate it, or make sure it doesn't exist (first record).
IF (p_pidm_inout IS NULL)
THEN
p_pidm_inout := gb_common.f_generate_pidm;
ELSE
IF (gb_common.f_pidm_exists(p_pidm_inout) = 'Y') THEN
p_raise_error('CHANGE_IND_REQUIRED');
END IF;
END IF;
END p_create_first_ever_record;
To:
PROCEDURE p_create_first_ever_record
IS
BEGIN
-- If no pidm, generate it, or make sure it doesn't exist (first record).
IF (p_pidm_inout IS NULL)
THEN
p_pidm_inout := gb_common.f_generate_pidm;
ELSE
IF (gb_common.f_pidm_exists(p_pidm_inout) = 'Y') THEN
p_raise_error('CHANGE_IND_REQUIRED');
END IF;
END IF;
IF (p_id_inout = 'GENERATED') THEN
/* This was changed to generate a 6-digit ID beginning with "9" followed by
the pidm (5 digits) for person entries in PPAIDEN (after swapping the ID
and PIDM generation sections in this routine), instead of using the ID
sequence number (by SREA, 10/14/05; updated to check for p_origin along
with p_entity_ind on 11/22/05, since both PPAIDEN and FTMVEND can pass
'P' or 'C' indicators) */
IF (p_entity_ind = 'P' AND p_origin = 'PPAIDEN') THEN
p_id_inout := '9' || LPAD(TO_CHAR(p_pidm_inout),5,'0');
IF (gb_common.f_id_exists (p_id_inout) = 'Y') THEN
p_raise_error ('ID_EXISTS');
END IF;
ELSE
p_id_inout := gb_common.f_generate_id;
END IF;
ELSIF (gb_common.f_id_exists (p_id_inout) = 'Y')
THEN
-- If id passed in, make sure it doesn't already exist.
p_raise_error ('ID_EXISTS');
END IF;
END p_create_first_ever_record;
Then, log into sqlplus as user BANINST1 and recompile gokb_ident1.sql and its dependencies:
sqlplus baninst1
@gokb_ident1.sql
exit
sqlplus /nolog @gurutlrp
Note: PPAIDEN may get an error when you click on Save the first time (this probably just
happens if you haven't recompiled its dependencies) - just click Save again, and it should
work that time.
3) Installed our CES Employee ID Lookup screen into WebTailor (see below). The package definition and body are in cesgetid.sql.
Compile the cesgetid package as baninst1:
sqlplus as baninst1
@cesgetid.sql
Set up all grants to schemas and roles:
create public synonym cesgetid for cesgetid;
START gurgrtb cesgetid
START gurgrth cesgetid
Add the new display procedure to WebTailor:
New WebTailor Administrator
Customize a Web Menu or Procedure
Click on Create button and set:
Page Name: cesgetid.P_DispIDQuery
Description: Get Employee ID
Module: Web for General
Non Secured Access Allowed: checked
Page Title: Get Employee ID
Header Text: Get Employee ID
Associated Roles: Employee
(Note: No Back Link can be done in this page)
Click on Submit Changes button
Add it to the Login screen and change the text of the screen to match:
Back in Customize a Web Menu or Procedure screen
Select twbkwbis.P_WWWLogin
Click on Customize a Web Menu or Procedure button
Click on Customize Information Text button
Click on sequence 1 DEFAULT and change the Information Text to:
Please enter your Employee ID and your Personal Identification Number (PIN)
and click Login. When you are finished, please Exit and close your browser
to protect your privacy.
Click on Submit Changes button
Click on sequence 2 DEFAULT and change the Information Text to:
<a href="cesgetid.P_DispIDQuery"><b>Look Up Employee ID</b></a>
Change Image to Help
Click on Submit Changes button
Add the new process procedure to WebTailor:
New WebTailor Administrator
Customize a Web Menu or Procedure
Click on Create button and set:
Page Name: cesgetid.P_ProcIDQuery
Description: Process Employee ID Query
Module: Web for General
Non Secured Access Allowed: checked
Associated Roles: Employee
Click on Submit Changes button
4) Installed our CES Self Service PIN Reset screen into WebTailor. The package definition and body are in cesresetpin.sql. A description of it is in "Adding Web Pages to the Self Service Products". The e-mailing requires either our email_files routine (see "E-Mailing From Oracle") or the Oracle daemon process to be running on a unix machine (see "Running System Commands from PL/SQL Using Pipes") to send the e-mails. Uncomment either the email_files call or the daemon call in the code to use whichever one you want. It defaults to using the email_files routine (UTL_SMTP based).
Compile the cesresetpin package as baninst1:
sqlplus as baninst1
@cesresetpin.sql
Set up all grants to schemas and roles:
create public synonym cesresetpin for cesresetpin;
START gurgrtb cesresetpin
START gurgrth cesresetpin
Add the new display procedure to WebTailor:
New WebTailor Administrator
Customize a Web Menu or Procedure
Click on Create button and set:
Page Name: cesresetpin.P_DispPINReset
Description: Reset Self Service PIN
Module: Web for General
Non Secured Access Allowed: checked
Page Title: Reset Self Service PIN
Header Text: Reset Self Service PIN
Associated Roles: Employee
(Note: No Back Link can be done in this page)
Click on Submit Changes button
Add it to the Login screen and change the text of the screen to match:
Back in Customize a Web Menu or Procedure screen
Select twbkwbis.P_WWWLogin
Click on Customize a Web Menu or Procedure button
Click on Customize Information Text button
Click on sequence 3 DEFAULT and change the Information Text to:
<a href="cesresetpin.P_DispPINReset"><b>Reset Self Service PIN</b>
Change Image to Key
Click on Submit Changes button
Add the new process procedure to WebTailor:
New WebTailor Administrator
Customize a Web Menu or Procedure
Click on Create button and set:
Page Name: cesresetpin.P_ProcPINReset
Description: Process Self Service PIN Reset
Module: Web for General
Non Secured Access Allowed: checked
Associated Roles: Employee
Click on Submit Changes button
5) Created spbpers_ssn_id_trigger which creates a spriden record with spriden_id set to spbpers_ssn and with spriden_change_ind set to "I" so that you can enter either the person's pidm-based employee ID or their SSN (which will switch to employee ID) for fields in Banner forms requiring a person's ID. Our gen_ssn_id_trigger.sql is shown below. To compile it in an sqlplus session, log in as user saturn, and enter @gen_ssn_id_trigger.sql
drop trigger saturn.spbpers_ssn_id_trigger;
--
-- Create a spriden record with spriden_id set to spbpers_ssn and with
-- spriden_change_ind set to "I" so that you can enter either the person's
-- pidm-based employee ID or their SSN (which will switch to employee ID)
-- for fields in Banner forms requiring a person's ID.
--
-- Author: Stephen Rea, University of Arkansas Cooperative Extension Service
-- Released: 6/29/04
--
-- Updates:
-- 9/3/04 - Added 3 new columns for SPRIDEN in Banner 6 (spriden_create_user,
-- spriden_create_date, and spriden_data_origin).
-- 1/7/05 - Update the alternate SSN-based SPRIDEN_ID to the new SPBPERS_SSN
-- if an update is being done (so can always do lookup on current SSN).
--
create trigger saturn.spbpers_ssn_id_trigger
after insert or update on saturn.spbpers for each row
declare
id spriden.spriden_id%TYPE;
cnt number;
rec spriden%ROWTYPE;
begin
-- Update the alternate SSN-based SPRIDEN_ID to the new SPBPERS_SSN,
-- and return, if an update is being done.
if UPDATING then
begin
update spriden set spriden_id = :new.spbpers_ssn
where spriden_pidm = :new.spbpers_pidm
and spriden_id = :old.spbpers_ssn
and spriden_change_ind = 'I'
and spriden_user like '%_EMPIDCONV';
return;
end;
end if;
-- Check to see if this is a generated employee ID and get the spriden
-- record for it if it is; otherwise, return.
id := '9' || lpad(:new.spbpers_pidm,5,'0');
begin
select * into rec from spriden
where spriden_pidm = :new.spbpers_pidm
and spriden_id = id
and spriden_change_ind is null;
exception
when no_data_found
then
return;
end;
-- If there is already a spriden record with an SSN-based employee ID for
-- this employee, return.
select count(*) into cnt from spriden
where spriden_pidm = :new.spbpers_pidm
and spriden_id = :new.spbpers_ssn;
if cnt > 0 then
return;
end if;
-- Create a spriden record with an SSN-based employee ID for this employee.
rec.spriden_id := :new.spbpers_ssn;
rec.spriden_change_ind := 'I';
rec.spriden_user := rec.spriden_user || '_EMPIDCONV';
insert into spriden values (rec.spriden_pidm,rec.spriden_id,
rec.spriden_last_name,rec.spriden_first_name,rec.spriden_mi,
rec.spriden_change_ind,rec.spriden_entity_ind,rec.spriden_activity_date,
rec.spriden_user,rec.spriden_origin,rec.spriden_search_last_name,
rec.spriden_search_first_name,rec.spriden_search_mi,
rec.spriden_soundex_last_name,rec.spriden_soundex_first_name,
rec.spriden_ntyp_code,rec.spriden_create_user,rec.spriden_create_date,
rec.spriden_data_origin);
-- Note: For Banner 5, change the above two lines to:
-- rec.spriden_ntyp_code);
end;
/
(end of gen_ssn_id_trigger.sql)
6) Then, created SSN-based spriden_id records for employees entered between the employee ID conversion process and the addition of gen_ssn_id_trigger.sql:
insert into spriden select spriden_pidm,spbpers_ssn,spriden_last_name,
spriden_first_name,spriden_mi,'I',spriden_entity_ind,
spriden_activity_date,spriden_user || '_EMPIDCONV',spriden_origin,
spriden_search_last_name,spriden_search_first_name,spriden_search_mi,
spriden_soundex_last_name,spriden_soundex_first_name,spriden_ntyp_code
from spriden s1,spbpers
where spriden_pidm = spbpers_pidm
and spriden_change_ind is null
and substr(spriden_id,1,1) = '9'
and length(spriden_id) = 6
and spbpers_ssn is not null
and not exists (select 'x' from spriden s2
where s2.spriden_id = spbpers_ssn);
Our Employee ID Lookup screen (cesgetid.sql) is shown below (the installation is shown in #3 above). We have a link to it set up in WebTailor on the Self Service login screen, which also contains a link to the Reset Self Service PIN screen (see the Self Service PIN Reset Screen topic in the Adding Web Pages to the Self Service Products web page for further information).
NOTE: If you have applied patch 99881 and/or 100441 (Self Service login page security updates), you will need to add the following in twbkwbi1.sql after the first "END IF;" after the "ELSIF (msg = 'LDAPATTMAPERROR') THEN" line in PROCEDURE p_wwwlogin, and recompile twbkwbi1.sql when logged into sqlplus as wtailor, in order for the employee ID message to show up on the Self Service login screen:
--
Output the msg string if it doesn't contain a left bracket (<) as
-- ASCII or HEX, which indicates an HTML
tag (possibly a script or
-- other executable), but allowing bold,
italic, underline, break,
-- and mailto tags
ELSIF (instr(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(lower(msg),' ',''),'%20',''),
'%3c','<'),'<b>',''),'</b>',''),'<i>',''),'</i>',''),
'<u>',''),'</u>',''),'<br>',''),'<ahref="mailto:',''),'</a>',''),
'<') = 0) THEN
twbkfrmt.p_printmessage(msg);
This Page was Last Updated on 06/15/06
You Are Visitor Number |
|
|
© 2006 |
|
|
University of Arkansas • Division of Agriculture |
Mission
•
Disclaimer
•
EEO
•
|
