Transcript Slide 1
Create Your Own
Self-Service Pages
Presented by: Robert Nitsos & Jim Keene, SJ
Loyola Marymount University
April 4, 2006
Evaluation Code 053
April 2-5 Orlando, Florida
Session Rules of Etiquette
Please turn off your cell phone/pager
If you must leave the session early, please do so as
discreetly as possible
Please avoid side conversation during the session
Thank you for your cooperation!
Evaluation Code 053
053
Introduction
Robert Nitsos
Assistant Registrar, Student Records Systems
Fr. Jim Keene, SJ
ITS, Senior Analyst
Evaluation Code 053
053
Loyola Marymount University
Private 4-Year
Institution
>7000 FTE
5400 UG
1700 GR
21 EdD
Self-Service Since
Spring 2000
Currently Banner 6.4
Evaluation Code 053
053
Topics of Discussion
Need for Capability
Creation of Package
Creation of Custom Roles
Web Tailor
Live Demo (I Hope!)
Questions / Answers / Comments / Praise /
Donations / Accolades / etc.
Evaluation Code 053
053
Need for Capability
“Why would anyone want to do this?”
April 2-5 Orlando, Florida
Need for Capability
Desired functionality does not exist in baseline.
Most users do not have access to Banner – SelfService Only!
Repeated requests for the same information.
Make my life easier!
Evaluation Code 053
053
Creation of Package
“How do I get started?”
April 2-5 Orlando, Florida
Where to Start
Look at Other Pages
See what SSCT is doing
Copy/Paste/Modify
Read BINFO, BSTUDENT, BORACLE
Post to BINFO, BSTUDENT, BORACLE
Other User Websites
Evaluation Code 053
053
Creation of Package
Package Header
Define Procedures/Functions to be Called
Passed Parameters
Package Body
Main Procedure Code
Additional Procedures/Functions/Cursors/etc.
Evaluation Code 053
053
Package Header
create or replace package lmubwfkadvr is
procedure LMU_P_Display_Advisors
(stupidm IN SPRIDEN.SPRIDEN_PIDM%TYPE DEFAULT NULL,
term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL,
error_mess IN VARCHAR2 DEFAULT NULL);
…
Other procedures/functions defined
…
END lmubwfkadvr;
Evaluation Code 053
053
Package Body
CREATE OR REPLACE PACKAGE BODY lmubwfkadvr AS
/* make sure registered then continue to process */
/* Global type and variable declarations for package */
pidm spriden.spriden_pidm%TYPE;
row_count NUMBER;
/****************************** LMU Show Advisors ***********************/
procedure LMU_P_Display_Advisors
(stupidm IN SPRIDEN.SPRIDEN_PIDM%TYPE DEFAULT NULL,
term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL,
error_mess IN VARCHAR2 DEFAULT NULL)
is
Evaluation Code 053
053
Package Body (cont.)
Variable Declarations
curr_release
term_rec
term_desc
hold_term
hold_stupidm
hold_stupidm_char
…
reg_role
user_college
stu_levl
stu_major
stu_major1
stu_major2
stu_minor1
stu_minor2
mjr_coll
varchar2(10) := '6.3';
stvterm%rowtype;
stvterm.stvterm_desc%TYPE;
stvterm.stvterm_code%TYPE;
spriden.spriden_pidm%TYPE;
varchar2(30) DEFAULT NULL;
varchar2(1);
stvcoll.stvcoll_code%type;
sgbstdn.sgbstdn_levl_code%type;
sgbstdn.sgbstdn_majr_code_1%type;
sgbstdn.sgbstdn_majr_code_1%type;
sgbstdn.sgbstdn_majr_code_2%type;
sgbstdn.sgbstdn_majr_code_minr_1%type;
sgbstdn.sgbstdn_majr_code_minr_1_2%type;
stvcoll.stvcoll_code%type;
Evaluation Code 053
053
Package Body (cont.)
/* Cursor for information that is to be displayed */
cursor advr_info
is
select a1.sgradvr_pidm pidm,
a1.sgradvr_term_code_eff term_code_eff,
a1.sgradvr_advr_pidm advr_pidm,
a1.sgradvr_advr_code advr_code,
stvadvr_desc advr_desc,
a1.sgradvr_prim_ind prim_ind,
a1.sgradvr_activity_date activity_date
from sgradvr a1, stvadvr
where a1.sgradvr_pidm = hold_stupidm
and a1.sgradvr_term_code_eff =
(select max(a2.sgradvr_term_code_eff) from sgradvr a2
where a2.sgradvr_term_code_eff <= hold_term
and a1.sgradvr_pidm = a2.sgradvr_pidm)
and a1.sgradvr_advr_code = stvadvr_code(+)
order by nvl(a1.sgradvr_prim_ind, 'N') DESC, advr_desc;
a_rec advr_info%rowtype;
Evaluation Code 053
053
Package Body (cont.)
Check the current user’s PIDM and determine Roles
/* check for valid user and define roles */
IF NOT twbkwbis.F_ValidUser(pidm) THEN
return;
END IF;
Evaluation Code 053
053
Package Body (cont.)
If no Term was passed, get it from the user
IF TERM IS NULL THEN
hold_term := twbkwbis.F_GetParam(pidm,'TERM');
ELSE
twbkwbis.P_SetParam(pidm,'TERM',term);
hold_term := term;
END IF;
/* Make sure a term has been selected */
IF hold_term is null THEN
bwlkostm.P_FacSelTerm(calling_proc_name =>
'lmubwfkadvr.LMU_P_Display_Advisors');
RETURN;
END IF;
Evaluation Code 053
053
Package Body (cont.)
Check to see if user is a valid faculty member
IF NOT bwlkilib.F_ValidFac(hold_term, pidm) THEN
msg := 'You must be a valid faculty member to access ' ||
'this page.';
twbkfrmt.P_PrintMessage(msg, 'ERROR');
twbkfrmt.P_Paragraph(1);
twbkwbis.P_CloseDoc(curr_release);
RETURN;
ELSE
/* Indicate that user is a faculty member */
twbkwbis.P_SetParam(pidm, 'STUFAC_IND', 'FAC');
END IF;
Evaluation Code 053
053
Package Body (cont.)
If no student PIDM was passed, get it from the user
IF STUPIDM IS NULL THEN
hold_stupidm_char := twbkwbis.F_GetParam(pidm,'STUPIDM');
ELSE
twbkwbis.P_SetParam(pidm,'STUPIDM',to_char(STUPIDM,'999999999'));
hold_stupidm := STUPIDM;
END IF;
/* If stupidm came from the table, then change it to a number */
if hold_stupidm_char is not null then
hold_stupidm := to_number(hold_stupidm_char,'999999999');
end if;
/* Make sure a student PIDM has been selected */
IF hold_stupidm IS NULL THEN
bwlkoids.P_FacIDSel(hold_term,
calling_proc_name => 'lmubwfkadvr.LMU_P_Display_Advisors',
calling_proc_name2 => 'lmubwfkadvr.LMU_P_Display_Advisors');
RETURN;
END IF;
Evaluation Code 053
053
Package Body (cont.)
Open Page, Display Information
bwckfrmt.p_open_doc ('lmubwfkadvr.LMU_P_Display_Advisors', hold_term);
/* If the user is not a valid faculty member for the selected */
/* term, print a message, close the page, and exit.
*/
IF NOT bwlkilib.F_ValidFac (hold_term, pidm) THEN
msg := 'You must be a valid faculty member for the selected term to
access this page.';
twbkfrmt.p_printmessage (msg, 'ERROR');
twbkwbis.p_closedoc (curr_release);
RETURN;
END IF;
twbkwbis.P_DispInfo('lmubwfkadvr.LMU_P_Display_Advisors','GENERAL');
term_desc := nvl(f_get_desc_fnc('STVTERM', hold_term, 30), 'Term Unknown');
Evaluation Code 053
053
Package Body (cont.)
Get User’s Role, Get User’s Home College
/* See if User is member of custom Registrar role */
reg_role := 'N';
twbkslib.p_fetchroles(pidm);
FOR i IN 1 .. twbkslib.num_roles
LOOP
IF (twbkslib.role_table(i) = 'REGISTRAR') THEN
reg_role := 'Y';
END IF;
END LOOP;
/* Get User's Home College */
open user_coll;
fetch user_coll into uc_rec;
if user_coll%notfound then
user_college := null;
else
user_college := uc_rec.coll;
end if;
Evaluation Code 053
053
Package Body (cont.)
Get User’s Role, Get User’s Home College
/* Display Student Name & Link to Address Info */
bwcklibs.P_ConfidStudInfo (hold_stupidm, hold_term);
/* Get Student's Program of Study */
open stu_info;
fetch stu_info into s_rec;
if stu_info%notfound then
twbkwbis.P_DispInfo('lmubwfkadvr.LMU_P_Display_Advisors',
'NO_PROGRAM');
else
Evaluation Code 053
053
Package Body (cont.)
Display Student Program Data
twbkfrmt.p_paragraph (1);
twbkfrmt.P_TableOpen('DATADISPLAY',
cattributes => 'summary="This table displays program information for the selected student."',
ccaption => 'Program of Study - '||term_desc);
twbkfrmt.P_TableRowOpen;
twbkfrmt.P_TableDataHeader('Level:',calign=>'left');
twbkfrmt.P_TableData(s_rec.levl_desc,calign=>'left',ccolspan=>'2');
stu_levl := s_rec.levl_code;
twbkfrmt.P_TableRowClose;
twbkfrmt.P_TableRowOpen;
twbkfrmt.P_TableDataHeader('Class:',calign=>'left');
twbkfrmt.P_TableData(s_rec.class_desc,calign=>'left',ccolspan=>'2');
twbkfrmt.P_TableRowClose;
twbkfrmt.P_TableRowOpen;
…
twbkfrmt.P_TableRowClose;
twbkfrmt.P_TableClose;
twbkfrmt.p_paragraph (1);
end if;
Evaluation Code 053
053
Package Body (cont.)
Display Advisor Info
open advr_info;
fetch advr_info into a_rec;
if advr_info%notfound then
twbkwbis.P_DispInfo('lmubwfkadvr.LMU_P_Display_Advisors','NO_DATA');
if (s_rec.coll1 = user_college) or (reg_role = 'Y') then /* Display button to Insert Advisor */
htp.formOpen('lmubwfkadvr.LMU_P_Select_Advisor', 'post');
htp.formHidden('s_pidm', hold_stupidm);
htp.formHidden('cur_term', hold_term);
htp.formHidden('u_coll', user_college);
htp.formHidden('r_role', reg_role);
htp.formHidden('cur_advr_pidm', a_rec.advr_pidm);
htp.formHidden('cur_advr_code', a_rec.advr_code);
htp.formHidden('cur_prim_ind', a_rec.prim_ind);
twbkfrmt.P_TableOpen('DATADISPLAY');
twbkfrmt.P_TableRowOpen;
twbkfrmt.P_TableDataOpen(calign=>'center');
htp.formSubmit(null, 'Insert Advisor');
twbkfrmt.P_TableDataClose;
twbkfrmt.P_TableRowClose;
twbkfrmt.P_TableClose;
htp.formClose;
end if;
Evaluation Code 053
053
Package Body (cont.)
Display Advisor Info
else
rcount := 0;
LOOP
if rcount > 0 then
fetch advr_info into a_rec;
end if;
EXIT WHEN advr_info%notfound;
if rcount = 0 then
/* Open Table and Create Header Labels */
twbkfrmt.P_TableOpen('DATADISPLAY',
cattributes => 'summary="This table displays advisors
assigned to the selected student."',
ccaption
=> 'Assigned Advisor(s) - '||term_desc);
twbkfrmt.P_TableRowOpen;
twbkfrmt.P_TableDataHeader('Advisor Name',calign=>'center');
twbkfrmt.P_TableDataHeader('Advisor Code',calign=>'center');
twbkfrmt.P_TableDataHeader('Advisor Type',calign=>'center');
twbkfrmt.P_TableDataHeader('Prim',calign=>'center');
twbkfrmt.P_TableDataHeader('Action',calign=>'center');
twbkfrmt.P_TableRowClose;
end if;
Evaluation Code 053
053
Package Body (cont.)
Display Advisor Info
/* Format Advisor Name */
advisor_name := f_format_name (a_rec.advr_pidm, 'LFMI');
if a_rec.advr_code = 'MAJ1' then /* Get Major Coll for Major1 */
mjr_coll := null;
stu_major := stu_major1;
open majr_info;
fetch majr_info into m_rec;
if majr_info%notfound then
mjr_coll := null;
else
mjr_coll := m_rec.coll_code;
end if;
Evaluation Code 053
053
Package Body (cont.)
If user is member of college, allow update of existing advisor
if (reg_role = 'Y') or (mjr_coll = user_college) then /* Display Button to Allow Advisor Change */
htp.formOpen('lmubwfkadvr.LMU_P_Select_Advisor', 'post');
htp.formHidden('s_pidm', hold_stupidm);
htp.formHidden('cur_term', hold_term);
htp.formHidden('u_coll', user_college);
htp.formHidden('r_role', reg_role);
htp.formHidden('cur_advr_pidm', a_rec.advr_pidm);
htp.formHidden('cur_advr_code', a_rec.advr_code);
htp.formHidden('cur_prim_ind', a_rec.prim_ind);
twbkfrmt.P_TableRowOpen;
twbkfrmt.P_TableData(advisor_name,calign=>'left');
twbkfrmt.P_TableData(a_rec.advr_code,calign=>'left');
twbkfrmt.P_TableData(a_rec.advr_desc,calign=>'left');
twbkfrmt.P_TableData(a_rec.prim_ind,calign=>'left');
/* Add button for Advisor Update */
twbkfrmt.P_TableDataOpen(calign=>'center');
htp.formSubmit(null, 'Update Advisor');
twbkfrmt.P_TableDataClose;
twbkfrmt.P_TableRowClose;
htp.formClose;
Evaluation Code 053
053
Package Body (cont.)
If user is not member of college, just display advisor
else
twbkfrmt.P_TableRowOpen;
twbkfrmt.P_TableData(advisor_name,calign=>'left');
twbkfrmt.P_TableData(a_rec.advr_code,calign=>'left');
twbkfrmt.P_TableData(a_rec.advr_desc,calign=>'left');
twbkfrmt.P_TableData(a_rec.prim_ind,calign=>'left');
twbkfrmt.P_TableData(null,calign=>'left');
twbkfrmt.P_TableRowClose;
end if;
close majr_info;
elsif a_rec.advr_code = 'MAJ2' then
…
/* similar code for other major types */
…
END LOOP;
twbkfrmt.P_TableClose;
twbkfrmt.p_paragraph (1);
end if;
close advr_info;
Evaluation Code 053
053
Package Body (cont.)
Display footer text, close page and end procedure
twbkwbis.P_DispInfo('lmubwfkadvr.LMU_P_Display_Advisors','FOOTER');
twbkwbis.P_CloseDoc(curr_release);
end LMU_P_Display_Advisors;
Other procedures in file:
procedure LMU_P_Select_Advisor
procedure LMU_P_Update_Advisor
Evaluation Code 053
053
Synonyms & Grants
Create a Public Synonym and Grant Execute Privileges to WWW_USER
whenever sqlerror continue;
drop public synonym lmubwfkadvr;
whenever sqlerror exit rollback;
create public synonym lmubwfkadvr for lmubwfkadvr;
grant execute on lmubwfkadvr to WWW_USER;
Evaluation Code 053
053
Bonus Code: Download Data to
Excel
“You forgot this last year!”
April 2-5 Orlando, Florida
Download Data to Excel
Procedure to display data in Excel format
procedure LMU_DL_Course_Data (
term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL,
crn in sfrstcr.sfrstcr_crn%type default null)
is
cursor student_list
is
select stvterm_code term_code,
…
from stvterm, sfrstcr, …
where stvterm_code = term
…
order by name;
slist
student_list%rowtype;
Evaluation Code 053
053
Download Data to Excel (cont.)
Set Mime Type to be Excel and Create Table
begin
owa_util.mime_header('application/vnd.ms-excel');
htp.tableopen;
htp.tablerowopen;
htp.tableheader('ID');
htp.tableheader('CONF');
htp.tableheader('Last Name');
htp.tableheader('First Name');
…
htp.tableheader('City');
htp.tableheader('State');
htp.tableheader('Zip');
htp.tableheader('Nation');
htp.tableheader('Phone');
htp.tableheader('Email');
htp.tablerowclose;
Evaluation Code 053
053
Download Data to Excel (cont.)
For each record in Cursor, insert row
FOR slist IN student_list
LOOP
htp.tablerowopen;
htp.tabledata(slist.id);
htp.tabledata(slist.confid);
htp.tabledata(slist.lname);
htp.tabledata(slist.fname);
…
htp.tabledata(slist.city);
htp.tabledata(slist.state);
htp.tabledata(slist.zip);
htp.tabledata(slist.nation);
htp.tabledata(slist.phone);
htp.tabledata(slist.email);
htp.tablerowclose;
END LOOP;
htp.tableclose;
end LMUDLPhotoRoster;
Evaluation Code 053
053
Download Data to Excel (cont.)
Results
Evaluation Code 053
053
Create Custom Roles
“People other than Students, Faculty, Alumni,
Employees, etc.”
April 2-5 Orlando, Florida
Create Custom Roles
This optional step allows you to define a “custom” role
This role can then be assigned to users and pages
TWTVROLE Table
TWTVROLE_CODE
TWTVROLE_DESC
TWTVROLE_ACTIVITY_DATE
TWTVROLE_USER_DEFINED_IND
Insert into twtvrole
Values(‘ROLE_CODE’, ‘Role Description’, SYSDATE, ‘Y’);
Evaluation Code 053
053
Web Tailor
“How do I get my page to show up?”
April 2-5 Orlando, Florida
Web Tailor
Create Procedure
Add to Menu
Create Info Text
Assign Roles to
Users
Evaluation Code 053
053
Create a New Procedure
Click on the
Create
button to
add your
procedure
to the
database
Evaluation Code 053
053
Create Procedure
Enter Page Name
Enter Description
Select Module
Enable
Enter Page Title
Enter Header
Text
Evaluation Code 053
053
Create Procedure (cont.)
Enter Page CSS
URL and Help
Link (if any)
Enter Back Link
URL & Text (if
any)
Check Back Menu
box
Select Role(s)
Evaluation Code 053
053
Add Page to Menu
Select menu
to which the
page is to be
added
Click the
Customize
Menu Items
button
Evaluation Code 053
053
Add New Menu Item
Click the Add a New Menu Item button at the bottom
of the page
Evaluation Code 053
053
Create New Menu Item
Enter Sequence #
Enter the URL and Link Text
If you want additional text
displayed, enter a Link
Description
Enter Status Bar Text
Be sure that the Enabled
and Database Procedure
boxes are checked!
Evaluation Code 053
053
Customize Information Text
Select the
page to which
you want to
add/update
Information
Text
Click the
Customize
Information
Text button
Evaluation Code 053
053
Create Information Text
Click on the
Add a New
Information
Text Entry
button
Evaluation Code 053
053
Create Information Text (cont.)
Select the
Sequence Number
Enter (or Select) the
Label name
Enter the
Information Text
Select an Image
Evaluation Code 053
053
Assign Custom Role(s) to User(s)
Custom roles
may be
assigned to
users
Enter the User
ID
Click the
Submit button
Evaluation Code 053
053
Assign Custom Role(s) to User(s) (cont.)
Check the box next to
each role you wish to
assign
Student and Faculty roles
are dynamically assigned
by the system
Evaluation Code 053
053
Demo
“Show me the pages!”
April 2-5 Orlando, Florida
Evaluation Code 053
053
Summary
Learn by Doing
Start Simple
Ask for Help
Collaborate
Examine Existing Code
Read/Post to BINFO, BSTUDENT and BORACLE
Evaluation Code 053
053
Questions & Answers
Evaluation Code 053
053
Thank You!
Robert Nitsos
[email protected]
http://registrar.lmu.edu/Code/LMUBAnnerCode.htm
Please complete the on-line Evaluation Form
Evaluation Code 053
Send me to Las Vega$!
Evaluation Code 053
053