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