1 / 54

Create Your Own Self-Service Pages

Create Your Own Self-Service Pages. Presented by: Robert Nitsos & Jim Keene, SJ Loyola Marymount University. April 4, 2006 Evaluation Code 053. 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

adelaide
Download Presentation

Create Your Own Self-Service Pages

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 • >7000 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_PIDM%TYPE DEFAULT NULL, term IN STVTERM.STVTERM_CODE%TYPE 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_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

  13. Package Body (cont.) • Variable Declarations curr_release varchar2(10) := '6.3'; term_rec stvterm%rowtype; term_desc stvterm.stvterm_desc%TYPE; hold_term stvterm.stvterm_code%TYPE; hold_stupidm spriden.spriden_pidm%TYPE; hold_stupidm_char varchar2(30) DEFAULT NULL; … reg_role varchar2(1); user_college stvcoll.stvcoll_code%type; stu_levl sgbstdn.sgbstdn_levl_code%type; stu_major sgbstdn.sgbstdn_majr_code_1%type; stu_major1 sgbstdn.sgbstdn_majr_code_1%type; stu_major2 sgbstdn.sgbstdn_majr_code_2%type; stu_minor1 sgbstdn.sgbstdn_majr_code_minr_1%type; stu_minor2 sgbstdn.sgbstdn_majr_code_minr_1_2%type; mjr_coll stvcoll.stvcoll_code%type;

  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 <= 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;

  15. 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;

  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 => '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,'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;

  19. 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');

  20. 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;

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

  22. 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;

  23. 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;

  24. 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;

  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_info%notfound 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,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;

  27. 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;

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

  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_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;

  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'); htp.tablerowclose;

  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; htp.tableclose; end LMUDLPhotoRoster;

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

More Related