SET scan off CREATE OR REPLACE PACKAGE CESResetPIN AS -- FILE NAME..: cesresetpin.sql -- RELEASE....: 5.4 -- OBJECT NAME: CESRESETPIN -- PRODUCT....: GENWEB -- RELEASED...: 6/2/04 -- USAGE......: CES BANNER Web Self Service PIN Reset Package Specification -- AUTHOR.....: Stephen Rea; Maristream, Inc. -- FUNCTION...: Reset the user's Self Service PIN to their birthdate (MMDDYY -- format), given the last 4 digits of their SSN and the first 5 characters -- of their last name. A reset key is generated and e-mailed to them (they -- must have a goremal record) to be used to then do the reset. -- -- The e-mailing requires either our email_files routine (see "E-Mailing From -- Oracle" on my web site, http://www.oracletips.info) or the Oracle daemon -- process to be running on a unix machine (see "Running System Commands from -- PL/SQL Using Pipes" on my web site) 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). -- -- Updates: -- 7/16/04 - Expanded PIN Reset Key e-mail message to include info about what -- the PIN is reset to and about changing that PIN after logging in. -- 7/28/04 - Change e-mail instructions to numbered steps. -- 12/13/04 - Put abbreviated instructions to the right of the fields. -- 1/5/05 - Changed e-mailing to use either our email_files routine -- (UTL_SMTP based) or the Oracle daemon program (UNIX mail based), -- defaulting to email_files in the code. -- NOTE: If you use this at your site, be sure to change my_name and my_email -- to your own name and e-mail address. I don't want to be receiving e-mails -- from your users! Also, be sure your SSN/name combinations are unique. If -- not, you will have to have some other criteria to uniquely identify the -- user. Use the following SQL and investigate the resulting names to see if -- they would cause a problem: -- select substr(spbpers_ssn,6),substr(spriden_last_name,1,5), -- count(*) from spriden,spbpers where spriden_change_ind is null -- and spriden_pidm = spbpers_pidm group by substr(spbpers_ssn,6), -- substr(spriden_last_name,1,5) having count(*) > 1; -- Declare the procedures called within the Web environment: the procedure that -- creates the Self Service PIN reset screen (P_DispPINReset) and the procedure -- that is called when the Submit button is pressed (P_ProcPINReset). -- -- These procedures must be declared to Web Tailor and marked as "Insecure -- Access Allowed". Below is how we did this at UACES. Your setup will be -- different (such as adding it to the Login screen). /* 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 Administration 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: New WebTailor Administration Customize a Web Menu or Procedure 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: Reset Self Service PIN Change Image to Key Click on Submit Changes button Add the new process procedure to WebTailor: New WebTailor Administration 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 */ PROCEDURE P_DispPINReset; PROCEDURE P_ProcPINReset( ssn4 varchar2, name5 varchar2, key gobtpac.gobtpac_pin%TYPE ); END CESResetPIN; / SHOW errors SET scan on SET scan off CREATE OR REPLACE PACKAGE BODY CESResetPIN AS -- Declare variables for the package's release version, the message text string -- and severity level (1 = Stop, 2 = Warning, 3 = OK), along with the last value -- entered for the SSN and last name, and the reset key. curr_release VARCHAR2(10) := '6.1'; my_name varchar2(30) := 'Steve Rea'; my_email varchar2(30) := 'srea@maristream.org'; msg_text VARCHAR2(1000) := NULL; msg_level NUMBER := 3; ssn4_prev varchar2(4) := ''; name5_prev varchar2(5) := ''; key gobtpac.gobtpac_pin%TYPE; -- Declare cursors used in the package. For CESRESETPIN, this includes the -- pidm, birthdate, e-mail address, PIN, PIN disabled flag, and employment -- status for the employee with the given last 4 digits of the SSN and the -- first 5 letters of the last name. CURSOR employee_PIN_cur(ssn4 varchar2, name5 varchar2) IS SELECT spriden_first_name || ' ' || spriden_last_name, spriden_pidm,spbpers_birth_date,goremal_email_address, gobtpac_pin,gobtpac_pin_disabled_ind,pebempl_empl_status FROM spriden,spbpers,goremal,gobtpac,pebempl WHERE spriden_pidm = spbpers_pidm AND spriden_change_ind is null AND SUBSTR(spbpers_ssn,6) = ssn4 AND LOWER(SUBSTR(spriden_last_name,1,5)) = LOWER(name5) AND spriden_pidm = goremal_pidm AND goremal_preferred_ind = 'Y' AND goremal_status_ind = 'A' AND goremal_emal_code = 'SMTP' AND spriden_pidm = gobtpac_pidm AND spriden_pidm = pebempl_pidm; -- Note: A REVERSE function is not available in PL/SQL, so, define my own. FUNCTION revers (str IN VARCHAR2) RETURN VARCHAR2 IS rev VARCHAR2(100); BEGIN rev := ''; FOR i IN REVERSE 1 .. LENGTH(str) LOOP rev := rev || SUBSTR(str,i,1); END LOOP; RETURN rev; END; -------------------------------------------------------------------------- -- Procedure P_DispPINReset (Display Self Service PIN Reset Screen) builds -- the HTML to display the fields to query on to get the associated employee -- PIN to reset, or to actually reset the employee's PIN. It includes -- creating fields and their associated variables for: -- The last 4 digits of the SSN (ssn4). -- The first 5 characters of the last name (name5). -- The PIN Reset Key e-mailed to the employee (key). -- Along with one button: -- A Submit button (which calls P_ProcPINReset to process the PIN query -- or reset, and display the result). -- -- This procedure must be declared to Web Tailor and marked as "Insecure -- Access Allowed". PROCEDURE P_DispPINReset IS BEGIN -- Open the form, showing the most recent message text, if any. twbkwbis.P_OpenDoc('cesresetpin.P_DispPINReset'); IF msg_text IS NOT NULL THEN twbkfrmt.P_PrintMessage('' || msg_text || '',TO_CHAR(msg_level)); msg_text := NULL; msg_level := 3; END IF; -- Print instructions. twbkfrmt.P_PrintMessage('To reset your PIN, follow the instructions to the right of the fields below:
'); /* twbkfrmt.P_PrintMessage('To reset your PIN, a 6-digit key will be e-mailed to your e-mail address currently stored in the database.  To Get Your PIN Reset Key: Enter your SSN and Name information, leaving the key field blank, and click Submit.  Then, To Reset Your PIN: Enter that PIN Reset Key and click Submit again (don''t erase the SSN or Last Name fields).'); */ -- Start building the form. twbkwbis.P_DispInfo('cesresetpin.P_DispPINReset','DEFAULT'); HTP.FormOpen('cesresetpin.P_ProcPINReset','post'); -- this happens when submit button pressed twbkfrmt.P_TableOpen('DATAENTRY', cattributes=>'Summary="This table allows resetting of the employee PIN."'); -- Define field for last 4 digits of SSN for query. twbkfrmt.P_TableRowOpen; twbkfrmt.P_TableDataLabel(twbkfrmt.F_FormLabel('Last 4 digits of your SSN: ', idname=>'ssn4_id')); twbkfrmt.P_TableData(htf.FormText('ssn4','6','4',ssn4_prev,cattributes=>'ID="ssn4_id"')); twbkfrmt.P_TableDataLabel(twbkfrmt.F_FormLabel('Fill in these first two fields first, then click Submit, and wait for the',idname=>'ssn4_id')); twbkfrmt.P_TableRowClose; -- Define field for first 5 characters of last name for query. twbkfrmt.P_TableRowOpen; twbkfrmt.P_TableDataLabel(twbkfrmt.F_FormLabel('First 5 characters of your last name: ', idname=>'name5_id')); twbkfrmt.P_TableData(htf.FormText('name5','6','5',name5_prev,cattributes=>'ID="name5_id"')); twbkfrmt.P_TableDataLabel(twbkfrmt.F_FormLabel('   e-mail it will send to you containing your PIN reset key.',idname=>'name5_id')); twbkfrmt.P_TableRowClose; -- Define field for PIN Reset Key for resetting the PIN. twbkfrmt.P_TableRowOpen; twbkfrmt.P_TableDataLabel(twbkfrmt.F_FormLabel('PIN Reset Key (e-mailed to you): ', idname=>'key_id')); twbkfrmt.P_TableData(htf.FormText('key','6','6',key,cattributes=>'ID="key_id"')); twbkfrmt.P_TableDataLabel(twbkfrmt.F_FormLabel('Fill in this field after the e-mail is received, and click Submit again.',idname=>'key_id')); twbkfrmt.P_TableRowClose; -- Finish specifying the data entry fields with a call to twbkfrmt.P_TableClose -- to match the starting twbkfrmt.P_TableOpen. twbkfrmt.P_TableClose; HTP.Para; -- Add the button. The HTP.FormSubmit button (labeled 'Submit') will call the -- "post" procedure (cesresetpin.P_ProcPINReset) specified in the HTP.FormOpen -- call above. HTP.FormSubmit(cvalue=>'Submit'); -- Finish building the form by closing the form and document. The value -- passed to twbkwbis.P_CloseDoc is usually the current release version -- of this form, and is placed at the bottom of the form. HTP.FormClose; twbkwbis.P_CloseDoc(curr_release); END; -------------------------------------------------------------------------- -- Procedure P_ProcPINReset (Process the Self Service PIN Reset) either -- does a query for the employee's PIN (given the last 4 digits of the -- SSN and the first 5 characters of the last name) and calculates the -- Reset Key value from it and e-mails the key to the employee when the -- PIN Reset Key field is blank, or resets the employee's PIN if the PIN -- Reset Key field has been filled in. -- -- This procedure must be declared to Web Tailor and marked as "Insecure -- Access Allowed". PROCEDURE P_ProcPINReset( ssn4 varchar2, name5 varchar2, key gobtpac.gobtpac_pin%TYPE ) IS name varchar2(60); pidm spriden.spriden_pidm%TYPE; birthdate spbpers.spbpers_birth_date%TYPE; email goremal.goremal_email_address%TYPE; current_key gobtpac.gobtpac_pin%TYPE; disabled gobtpac.gobtpac_pin_disabled_ind%TYPE; empl_status pebempl.pebempl_empl_status%TYPE; msg varchar2(2000); res number; BEGIN -- Get the employee pidm and PIN information for the given SSN and -- Last Name substrings. OPEN employee_PIN_cur(ssn4,name5); FETCH employee_PIN_cur INTO name,pidm,birthdate,email,current_key, disabled,empl_status; -- Calculate the PIN Reset Key that is e-mailed to the employee to be -- entered to reset the PIN. (I'm not sending the PIN itself because -- they might just use that without doing the reset.) IF employee_PIN_cur%FOUND AND current_key IS NOT NULL THEN current_key := rpad(substr(substr(current_key * revers(current_key), 1,6) + 67121,1,6),6,'0'); END IF; -- Check for no match for SSN/name combination, or no Self Service account, -- or no e-mail address. IF employee_PIN_cur%NOTFOUND OR current_key IS NULL OR email IS NULL THEN msg_text := 'No Employee Self Service account or e-mail was found for SSN ' || ssn4 || ' and Last Name ' || name5; msg_level := 1; -- Make sure this is an active employee (not terminated). ELSIF empl_status != 'A' THEN msg_text := 'You do not have an active employee record. Your PIN has not been reset.'; msg_level := 1; /* -- Don't allow disabled Self Service accounts to be reset. (Uncomment this -- if you want to do this check.) ELSIF disabled = 'Y' THEN msg_text := 'Your Self Service account has been disabled. Please contact ' || my_name || ' at ' || my_email || ' to reset it.'; msg_level := 1; */ -- E-mail the PIN Reset Key to the user's e-mail address in the database. ELSIF key IS NULL THEN msg_text := 'Your PIN Reset Key and instructions have been e-mailed to ' || email; msg_level := 2; ssn4_prev := ssn4; name5_prev := name5; if length(replace(translate(lower(name),' abcdefghijklmnopqrstuvwxyz', rpad('~',26,'~')),'~','')) > 0 then name := '"' || name || '"'; end if; msg := chr(10) || 'Please follow these instructions closely to ' || 'reset your Self Service PIN.' || chr(10) || chr(10) || 'Your Self Service PIN Reset Key is: ' || current_key || chr(10) || '(Note: This is NOT your actual Self Service PIN - ' || 'it is only used for resetting your PIN.)' || chr(10) || chr(10) || 'This key was just now requested in a Banner Self ' || 'Service session. To reset your Self Service PIN:' || chr(10) || ' 1) Put this key value into the "PIN Reset Key" field in the ' || '"Reset Self Service PIN" web page you are currently in,' || chr(10) || ' 2) Click on the Submit button to reset your PIN ' || '(your SSN and Last Name information should still be filled ' || 'in - if not, fill them in before clicking Submit.)' || chr(10) || chr(10) || 'Your PIN will then be reset to your 6-digit ' || 'birthdate (MMDDYY month, day, and year format). After your ' || 'PIN has been reset:' || chr(10) || ' 1) Log into Self Service, filling in the Employee ID field ' || 'and putting your birthdate PIN into the PIN field,' || chr(10) || ' 2) Enter your birthdate PIN into the "Re-enter Old PIN" ' || 'field of the "Login Verification Change PIN" screen that comes ' || 'up,' || chr(10) || ' 3) Enter a new 6-digit PIN into the "New PIN" and "Re-enter ' || 'new PIN" fields (which is the PIN you will use for subsequent ' || 'logins for Self Service),' || chr(10) || ' 4) Click on the Login button,' || chr(10) || 'then you can ' || 'use the Self Service screens.' || chr(10); -- Uncomment either the email_files call or the daemon call below, depending on -- which one you want to use. email_files(from_name => my_email, to_names => name || ' <' || email || '>', subject => 'Self Service PIN Reset Key', message => msg); /* res := common.daemon.execute_system( 'mail -s "Self Service PIN Reset Key" ' || email || ' <' || msg_text || ''); ELSE P_DispPINReset; END IF; EXCEPTION WHEN OTHERS THEN -- If the e-mail failed, display the Reset Key on this web page in place -- of e-mailing it. (This is less secure than e-mailing it, so, edit this -- if you don't want to display the key on the screen.) IF msg_level = 2 THEN msg_text := 'E-mail is not currently available. Your PIN Reset Key is ' || current_key; -- If a problem occurred while running the UPDATE command, the PIN wasn't -- reset. ELSIF msg_level = 3 THEN msg_text := 'Can not reset your PIN at this time.'; msg_level := 1; ELSE -- If some other error occurred, ask a person to reset the PIN, instead -- of using this web page. msg_text := 'A problem occurred with this page. Contact ' || my_name || ' (' || my_email || ') to reset your Self Service PIN.'; msg_level := 1; END IF; P_DispPINReset; END; END CESResetPIN; / SHOW errors SET scan on