Create Your Own Self-Service Pages

1
Create Your OwnSelf-Service Pages

• Presented by Robert Nitsos Jim Keene, SJ
• Loyola Marymount University

April 4, 2006 Evaluation Code 053
2
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!
3
Introduction

• Robert Nitsos
• Assistant Registrar, Student Records Systems
• Fr. Jim Keene, SJ
• ITS, Senior Analyst

4
Loyola Marymount University

• Private 4-Year Institution
• gt7000 FTE
• 5400 UG
• 1700 GR
• 21 EdD
• Self-Service Since Spring 2000
• Currently Banner 6.4

5
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.

6
Need for Capability

• Why would anyone want to do this?

7
Need for Capability

• Desired functionality does not exist in baseline.
• Most users do not have access to Banner
  Self-Service Only!
• Repeated requests for the same information.
• Make my life easier!

8
Creation of Package

• How do I get started?

9
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

10
Creation of Package

• Package Header
• Define Procedures/Functions to be Called
• Passed Parameters
• Package Body
• Main Procedure Code
• Additional Procedures/Functions/Cursors/etc.

11
Package Header

• create or replace package lmubwfkadvr is
• procedure LMU_P_Display_Advisors
• (stupidm IN SPRIDEN.SPRIDEN_PIDMTYPE DEFAULT
  NULL,
• term IN STVTERM.STVTERM_CODETYPE DEFAULT NULL,
• error_mess IN VARCHAR2 DEFAULT NULL)
• Other procedures/functions defined
• END lmubwfkadvr

12
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_pidmTYPE
• row_count NUMBER
• / LMU Show Advisors
  /
• procedure LMU_P_Display_Advisors
• (stupidm IN SPRIDEN.SPRIDEN_PIDMTYPE DEFAULT
  NULL,
• term IN STVTERM.STVTERM_CODETYPE DEFAULT NULL,
• error_mess IN VARCHAR2 DEFAULT NULL)
• is

13
Package Body (cont.)

• Variable Declarations
• curr_release varchar2(10) '6.3'
• term_rec stvtermrowtype
• term_desc stvterm.stvterm_descTYPE
• hold_term stvterm.stvterm_codeTYPE
• hold_stupidm spriden.spriden_pidmTYPE
• hold_stupidm_char varchar2(30) DEFAULT NULL
• reg_role varchar2(1)
• user_college stvcoll.stvcoll_codetype
• stu_levl sgbstdn.sgbstdn_levl_codetype
• stu_major sgbstdn.sgbstdn_majr_code_1type
• stu_major1 sgbstdn.sgbstdn_majr_code_1type
• stu_major2 sgbstdn.sgbstdn_majr_code_2type
• stu_minor1 sgbstdn.sgbstdn_majr_code_minr_1type
  
• stu_minor2 sgbstdn.sgbstdn_majr_code_minr_1_2ty
  pe
• mjr_coll stvcoll.stvcoll_codetype

14
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 lt 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

15
Package Body (cont.)

• Check the current users PIDM and determine Roles
• / check for valid user and define roles /
• IF NOT twbkwbis.F_ValidUser(pidm) THEN
• return
• END IF

16
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 gt
• 'lmubwfkadvr.LMU_P_Display_Advisors')
• RETURN
• END IF

17
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

18
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,'S
  TUPIDM')
• ELSE
• twbkwbis.P_SetParam(pidm,'STUPIDM',to_char(STUPI
  DM,'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,'999
  999999')
• end if
• / Make sure a student PIDM has been selected /
• IF hold_stupidm IS NULL THEN
• bwlkoids.P_FacIDSel(hold_term,
• calling_proc_name gt 'lmubwfkadvr.LMU_P_Display
  _Advisors',
• calling_proc_name2 gt 'lmubwfkadvr.LMU_P_Displa
  y_Advisors')
• RETURN

19
Package Body (cont.)

• Open Page, Display Information
• bwckfrmt.p_open_doc ('lmubwfkadvr.LMU_P_Display_A
  dvisors', 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_Ad
  visors','GENERAL')
• term_desc nvl(f_get_desc_fnc('STVTERM',
  hold_term, 30), 'Term Unknown')

20
Package Body (cont.)

• Get Users Role, Get Users 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_collnotfound then
• user_college null
• else
• user_college uc_rec.coll

21
Package Body (cont.)

• Get Users Role, Get Users 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_infonotfound then
• twbkwbis.P_DispInfo('lmubwfkadvr.LMU_P_Display_A
  dvisors',
• 'NO_PROGRAM')
• else

22
Package Body (cont.)

• Display Student Program Data
• twbkfrmt.p_paragraph (1)
• twbkfrmt.P_TableOpen('DATADISPLAY',
• cattributes gt 'summary"This table displays
  program information for the selected student."',
• ccaption gt 'Program of Study -
  'term_desc)
• twbkfrmt.P_TableRowOpen
• twbkfrmt.P_TableDataHeader('Level',caligngt'lef
  t')
• twbkfrmt.P_TableData(s_rec.levl_desc,caligngt'le
  ft',ccolspangt'2')
• stu_levl s_rec.levl_code
• twbkfrmt.P_TableRowClose
• twbkfrmt.P_TableRowOpen
• twbkfrmt.P_TableDataHeader('Class',caligngt'lef
  t')
• twbkfrmt.P_TableData(s_rec.class_desc,caligngt'l
  eft',ccolspangt'2')
• twbkfrmt.P_TableRowClose
• twbkfrmt.P_TableRowOpen
• twbkfrmt.P_TableRowClose

23
Package Body (cont.)

• Display Advisor Info
• open advr_info
• fetch advr_info into a_rec
• if advr_infonotfound then
• twbkwbis.P_DispInfo('lmubwfkadvr.LMU_P_Display_A
  dvisors','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(caligngt'center')
• htp.formSubmit(null, 'Insert Advisor')

24
Package Body (cont.)

• Display Advisor Info
• else
• rcount 0
• LOOP
• if rcount gt 0 then
• fetch advr_info into a_rec
• end if
• EXIT WHEN advr_infonotfound
• if rcount 0 then
• / Open Table and Create Header Labels /
• twbkfrmt.P_TableOpen('DATADISPLAY',
• cattributes gt 'summary"This table displays
  advisors
• assigned to the selected student."',
• ccaption gt 'Assigned Advisor(s) -
  'term_desc)
• twbkfrmt.P_TableRowOpen
• twbkfrmt.P_TableDataHeader('Advisor
  Name',caligngt'center')
• twbkfrmt.P_TableDataHeader('Advisor
  Code',caligngt'center')

25
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_infonotfound then
• mjr_coll null
• else
• mjr_coll m_rec.coll_code
• end if

26
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,caligngt'left'
  )
• twbkfrmt.P_TableData(a_rec.advr_code,caligngt'le
  ft')
• twbkfrmt.P_TableData(a_rec.advr_desc,caligngt'le
  ft')
• twbkfrmt.P_TableData(a_rec.prim_ind,caligngt'lef
  t')
• / Add button for Advisor Update /

27
Package Body (cont.)

• If user is not member of college, just display
  advisor
• else
• twbkfrmt.P_TableRowOpen
• twbkfrmt.P_TableData(advisor_name,caligngt'le
  ft')
• twbkfrmt.P_TableData(a_rec.advr_code,caligngt
  'left')
• twbkfrmt.P_TableData(a_rec.advr_desc,caligngt
  'left')
• twbkfrmt.P_TableData(a_rec.prim_ind,caligngt'
  left')
• twbkfrmt.P_TableData(null,caligngt'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

28
Package Body (cont.)

• Display footer text, close page and end procedure
• twbkwbis.P_DispInfo('lmubwfkadvr.LMU_P_Display_Ad
  visors','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

29
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

30
Bonus Code Download Data to Excel

• You forgot this last year!

31
Download Data to Excel

• Procedure to display data in Excel format
• procedure LMU_DL_Course_Data (
• term IN STVTERM.STVTERM_CODETYPE DEFAULT NULL,
• crn in sfrstcr.sfrstcr_crntype default null)
• is
• cursor student_list
• is
• select stvterm_code term_code,
• from stvterm, sfrstcr,
• where stvterm_code term
• order by name
• slist student_listrowtype

32
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')

33
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

34
Download Data to Excel (cont.)

• Results

35
Create Custom Roles

• People other than Students, Faculty, Alumni,
  Employees, etc.

36
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)

37
Web Tailor

• How do I get my page to show up?

38
Web Tailor

• Create Procedure
• Add to Menu
• Create Info Text
• Assign Roles to Users

39
Create a New Procedure

• Click on the Create button to add your procedure
  to the database

40
Create Procedure

• Enter Page Name
• Enter Description
• Select Module
• Enable
• Enter Page Title
• Enter Header Text

41
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)

42
Add Page to Menu

• Select menu to which the page is to be added
• Click the Customize Menu Items button

43
Add New Menu Item

• Click the Add a New Menu Item button at the
  bottom of the page

44
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!

45
Customize Information Text

• Select the page to which you want to add/update
  Information Text
• Click the Customize Information Text button

46
Create Information Text

• Click on the Add a New Information Text Entry
  button

47
Create Information Text (cont.)

• Select the Sequence Number
• Enter (or Select) the Label name
• Enter the Information Text
• Select an Image

48
Assign Custom Role(s) to User(s)

• Custom roles may be assigned to users
• Enter the User ID
• Click the Submit button

49
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

50
Demo

• Show me the pages!

51
(No Transcript)
52
Summary

• Learn by Doing
• Start Simple
• Ask for Help
• Collaborate
• Examine Existing Code
• Read/Post to BINFO, BSTUDENT and BORACLE

53
Questions Answers
54
Thank You!

• Robert Nitsos
• rnitsos_at_lmu.edu
• http//registrar.lmu.edu/Code/LMUBAnnerCode.htm
• Please complete the on-line Evaluation Form
• Evaluation Code 053
• Send me to Las Vega!