|
|
Cooperative Extension Service |
|
 |
||
|
|
||
|
Agricultural
Experiment Station |
||
 |
|
|
Our Drivers License update screen (cesdrlic_spbpers.sql for the student spbpers table, or cesdrlic_pprdlic.sql for the HR/payroll pprdlic table (which is what we use); if you want to use both of them, you will have to change the name of one of their packages) that I added to Self Service General can be used as-is for drivers license entry into Banner (drivers license record checking may be required by some insurance companies), or as a starting point for developing your own Banner Self Service pages. The code is heavily documented, describing all of the steps and procedure calls that I used in this application, and is an example of an HTML table with a single column per row. The resulting drivers license screen is shown below. For an example of multiple columns per row, see Adding Name and SSN to View Pay Stub Detail. We also have a Self Service PIN Reset web page, a Banner Password Reset web page, and a Timesheet Approvals web page (updated 9/25/07) below. There are also Generic Instructions for adding web pages to any Oracle database, not just for Banner.
If you implement this, you will need to do the following steps to add it to WebTailor to be able to access it from the Personal Information menu. Also, you'll probably need to change the default state code in the cesdrlic file to your own state (unless you are in Arkansas), along with the default nation code if you are outside the United States.
Compile the cesdrlic package as baninst1 (use either cesdrlic_spbpers.sql or cesdrlic_pprdlic.sql):
sqlplus as baninst1 @cesdrlic_pprdlic.sql
Set up all grants to schemas and roles:
create public synonym cesdrlic for cesdrlic; START gurgrtb cesdrlic START gurgrth cesdrlic
Add the new display procedure to WebTailor:
WebTailor Administrator
Customize a Web Menu or Procedure
Click on Create button and set:
Page Name: cesdrlic.P_DispDLView
Description: Update Drivers License
Module: Web for General
Page Title: Update Drivers License
Header Text: Update Drivers License
Back Link URL: bmenu.P_GenMnu
Back Link Text: Return to Menu
Back Link Menu Indicator: Yes
Associated Roles: Employee
Click on Submit Changes button
Add it to the Personal Information menu:
Back in Customize a Web Menu or Procedure screen
Select bmenu.P_GenMnu
Click on Customize a Web Menu or Procedure button
Click on Customize Menu Items button
Click on Add a New Menu Item and set:
URL: cesdrlic.P_DispDLView
Link Text: Update Drivers License
Database Procedure: Yes
Click on Submit Changes button
Add the new process procedure to WebTailor:
WebTailor Administrator
Customize a Web Menu or Procedure
Click on Create button and set:
Page Name: cesdrlic.P_ProcDLUpdate
Description: Process Update Drivers License
Module: Web for General
Associated Roles: Employee
Click on Submit Changes button
If you are trying to eliminate the costs associated with printing and mailing pay stubs, one problem you may come across is what to do for people who need a copy of their pay stub as proof of salary, such as may be needed to acquire a loan. A printout from the View Pay Stub Detail screen could probably be used for that; however, there is no name or SSN on that screen delivered with the standard Banner implementation. This is very easy to add to the $BANNER_HOME/payweb/dbprocs/bwpkhst1.sql file by defining a cursor and putting another HTML table row on the screen containing the name and SSN fetched using the cursor, then, running the bwpkhst1.sql file in sqlplus as baninst1 to put the changes into effect. Edit the bwpkhst1.sql file and put the following in:
Put the following cursor after the Get_Gross_NetC cursor (before the F_Check_if_adjusted function):
CURSOR Get_Name_SSNC (pidm number) IS SELECT SUBSTR(SPRIDEN_LAST_NAME || ', ' || SPRIDEN_FIRST_NAME || ' ' || SPRIDEN_MI,1,30),SPBPERS_SSN FROM SPRIDEN,SPBPERS WHERE SPRIDEN_PIDM = pidm AND SPRIDEN_CHANGE_IND IS NULL AND SPBPERS_PIDM = SPRIDEN_PIDM;
Put the following variable definitions after the net_amt declaration in the P_DispPayStubDtl procedure:
emp_name VARCHAR2(30); emp_ssn SPRIDEN.SPRIDEN_ID%type;
Put the following cursor fetch after the Get_Gross_NetC cursor fetch in the P_DispPayStubDtl procedure:
open Get_Name_SSNC (pidm);
fetch Get_Name_SSNC into emp_name,emp_ssn;
close Get_Name_SSNC;
--
Put the following HTML table row after the FIRST twbkfrmt.P_TableOpen call (or use twgkfrmt for hwpkhst1.sql) in the P_DispPayStubDtl procedure (note that this is an example of an HTML table row with multiple columns, since it has more than one set of P_TableDataLabel/P_TableData pairs defined within the P_TableRowOpen/P_TableRowClose grouping; also note that the employee name field spans 2 columns, and that the SSN is aligned on the right of its field):
twbkfrmt.P_TableRowOpen; twbkfrmt.P_TableDataLabel('Employee: '); twbkfrmt.P_TableData(emp_name,ccolspan=>'2'); twbkfrmt.P_TableDataLabel('SSN: '); twbkfrmt.P_TableData(emp_ssn,calign=>'right'); twbkfrmt.p_TableRowClose;
Save the bwpkhst1.sql changes. Then, compile the updated bwpkhstb package body in sqlplus (for each of your databases):
sqlplus as baninst1
@bwpkhst1.sql
Our Self Service PIN Reset screen (cesresetpin.sql) resets 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 (if this combination is not unique at your site, you will have to come up with some other criteria to uniquely identify the user, and change the screen appropriately). 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 birthdate PIN is pre-expired, so when they log into Self Service using that PIN, they are automatically taken to the Change PIN screen. Be sure to change my_name and my_email at the beginning of PACKAGE BODY CESResetPIN to contain your own name and e-mail address.
A link to this web page is placed on the Self Service User Login page through an Information Text record set up through WebTailor. The WebTailor setup is shown in the code itself, not here. What allows this screen to run without having to log into Self Service first is the "Non Secured Access Allowed" switch that can be set when we use the Create a Web Menu or Procedure screen in WebTailor (thanks to Perley Dexter of Norwich University for pointing this out to me). The screen is shown below. 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). Thanks to Steve Hall here at the Extension Service for this idea.
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 "PIN reset to" 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);
Our Banner Password Reset screen (cesresetpw.sql), similar to the Self Service PIN Reset screen shown above, resets the user's Banner Password to a pre-expired temporary 8-character alphanumeric value, given the last 4 digits of their SSN and the first 5 characters of their last name (if this combination is not unique at your site, you will have to come up with some other criteria to uniquely identify the user, and change the screen appropriately). The temporary password is e-mailed to them (they must have a goremal record) to be used to then do the reset. If their user ID is set to some profile other than default, the profile is switched temporarily to default to do the reset (this is to accomodate sites using Oracle's Password Management so that the temporary password doesn't get put into the USER_HISTORY$ table and isn't counted in the password changes; the default profile is assumed to be unlimited). Be sure to change my_name and my_email at the beginning of PACKAGE BODY CESResetPW to contain your own name and e-mail address, and change msg_text in procedure P_ProcPWReset to contain your own Banner INB/WebForms web URL address.
A link to this web page (such as http://yoursite/pls/yourdatabase/cesresetpw.P_DispPWReset) is placed on one of our internal web pages that our employees have access to. Additional setup in WebTailor is shown in the code itself, not here. What allows this screen to run without having to log into Self Service first is the "Non Secured Access Allowed" switch that can be set when we use the Create a Web Menu or Procedure screen in WebTailor (thanks to Perley Dexter of Norwich University for pointing this out to me). The screen is shown below. 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). Thanks, again, to Steve Hall here at the Extension Service for this idea.
Our Time Entry Approvals screen (cestimen.sql; updated 9/25/07), shown below, allows a manager to approve or return the timesheets for their people in any org that are currently awaiting approval in any pay period. Our pay periods are SemiMonthly (SM) for salaried employees, for whom leave time off (leave report) is recorded, and Bi-Monthly (BI) for hourly employees, for whom time off (time entry) is recorded. If your hourly pay period is not named "BI", you will need to change one line of code in cestimen.sql to match what your period is named (do a search for BI in that code). Also, if you want the manager to enter their Self Service PIN to verify the approvals, initialize request_pin to TRUE in the code. Finally, there is a Switch All button that is commented out in the code, but you can uncomment it if you want to use it. To install our CES Time Entry Approvals screen into Employee Self-Service:
Compile the cestimen package as baninst1:
sqlplus as baninst1
@/home/common/cestimen.sql
Set up all grants to schemas and roles:
create public synonym cestimen for cestimen;
START gurgrtb cestimen
START gurgrth cestimen
Add the new display procedure to WebTailor:
Customize a Web Menu or Procedure
Click on Create button and set:
Page Name: cestimen.P_DispTEView
Description: Time Sheet Approvals
Module: Employee Self-Service
Page Title: Time Sheet Approvals
Header Text: Time Sheet Approvals
Back Link URL: pmenu.P_MainMnu
Back Link Text: Return to Menu
Back Link Menu Indicator: Yes
Associated Roles: Employee
Click on Submit Changes button
Add it to the Personal Information menu:
Back in Customize a Web Menu or Procedure screen
Select pmenu.P_MainMnu
Click on Customize a Web Menu or Procedure button
Click on Customize Menu Items button
Click on Add a New Menu Item and set:
URL: cestimen.P_DispTEView
Link Text: Time Sheet Approvals
Database Procedure: Yes
Click on Submit Changes button
Reorder the menu items so that Time Entry Approvals comes after Time Sheet
Add the new process procedure to WebTailor:
WebTailor Administrator
Customize a Web Menu or Procedure
Click on Create button and set:
Page Name: cestimen.P_ProcTEUpdate
Description: Process Time Sheet Approvals
Module: Employee Self-Service
Associated Roles: Employee
Click on Submit Changes button
If you have an Oracle database (Banner or otherwise) that you want to add a web interface to, or create web-based reports, all you need (besides the web server software that you may already have installed on your server) is a Database Access Descriptor (DAD) to log you into the Oracle User ID that will contain the PL/SQL procedures that will build your dynamic web pages. http://orafaq.com/faqweb.htm contains examples of Oracle PL/SQL based web page development, such as the Hello World example shown below. In the following instructions in which we create a DAD and the Hello World procedure, change the your_dad, www.yoursite.edu, the port 7778 (if needed), yourID, yourpasswd, and your_connect_string values to match your site's values.
Add a DAD (Database Access Descriptor) for your_dad:
Go to http://www.yoursite.edu:7778/
Click on Mod_plsql Configuration Menu
Click on Gateway Database Access Descriptor Settings
Click on Add Default (blank configuration)
Enter:
Database Access Descriptor Name: your_dad
Schema Name: yourID
Oracle User Name: yourID
Oracle Password: yourpasswd
Oracle Connect String: your_connect_string
Click on Apply button
Click on OK button
Create the Hello World example in the yourID schema (the slash must be the first and only character on its line):
sqlplus yourID/yourpasswd@your_connect_string
CREATE OR REPLACE PROCEDURE HelloWorld AS
BEGIN
htp.htitle('My first dynamic Web page');
htp.print('Hello world');
htp.line;
END HelloWorld;
/
Bring up the Hello World example (note the difference in the URL from the orafaq.com example, which was written
for an earlier version of Oracle):
http://www.yoursite.edu:7778/pls/your_dad/HelloWorld
This Page was Last Updated on 09/25/07
You Are Visitor Number |
|
|
© 2006 |
|
|
University of Arkansas • Division of Agriculture |
Mission
•
Disclaimer
•
EEO
•
|
