set scan off set echo on -- Created for Loyola Marymount University -- By Robert M. Nitsos -- 21-MAY-2001 -- -- This package creates a Banner Web page that displays High School information in the -- Faculty Module. Faculty can see the High School data of a selected student. -- -- Modified 24-FEB-2003 -- By Robert M. Nitsos -- Updated package to use FacWeb 5.3 UI and procedures. -- create or replace package lmuhwskhsch is TYPE varchar2_tabtype IS TABLE OF varchar2(26) INDEX BY BINARY_INTEGER; procedure LMUHighSchool (STUPIDM in spriden.spriden_pidm%type default null, term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, error_mess in varchar2 default null); END lmuhwskhsch; / show errors CREATE OR REPLACE PACKAGE BODY lmuhwskhsch AS /* make sure registered then continue to process */ /* Global type and variable declarations for package */ /* UI MOD */ /* The following broken line is required */ NEWLINE constant varchar2(1) := ' '; pidm spriden.spriden_pidm%TYPE; /****************************** LMU Show High School Info ***********************/ procedure LMUHighSchool (STUPIDM in spriden.spriden_pidm%type default null, term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, error_mess in varchar2 default null) is curr_release varchar2(10) := '3.2'; hold_term stvterm.stvterm_code%TYPE; hold_stupidm spriden.spriden_pidm%TYPE; hold_stupidm_char varchar2(30) DEFAULT NULL; confid_msg varchar2(30) default null; student_name varchar2(124); msg varchar2(255); term_rec stvterm%rowtype; rcount number; cursor hsinfo is select sorhsch_pidm hs_pidm, sorhsch_sbgi_code sbgi_code, stvsbgi_desc hs, sobsbgi_city hs_city, sobsbgi_stat_code hs_state, nvl(to_char(sorhsch_graduation_date, 'MM/DD/YYYY'), 'N/A') grad_date, nvl(to_char(sorhsch_trans_recv_date, 'MM/DD/YYYY'), 'N/A') trans_date, sorhsch_class_rank c_rank, sorhsch_class_size c_size, sorhsch_percentile pct, sorhsch_gpa gpa from sorhsch, stvsbgi, sobsbgi where sorhsch_pidm = hold_stupidm and sorhsch_sbgi_code=stvsbgi_code(+) and stvsbgi_code=sobsbgi_sbgi_code(+) order by hs; begin IF NOT twbkwbis.F_ValidUser(pidm) THEN return; END IF; /* If you came from the menu, try to select the term from the */ /* general table, gorwprm, by using F_GetParam. */ IF TERM IS NULL THEN hold_term := twbkwbis.F_GetParam(pidm,'TERM'); /* otherwise, you came from P_FacSelTerm, and need to write */ /* the param to the gorwprm table and set your local, hold_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=> 'lmuhwskhsch.LMUHighSchool'); RETURN; END IF; /* set paramater to indicate we are a faculty user */ twbkwbis.P_SetParam(pidm,'STUFAC_IND','FAC'); /* If stupidm has not been passed as a param, then try to get */ /* it from the general table, gorwprm */ IF STUPIDM IS NULL THEN hold_stupidm_char := twbkwbis.F_GetParam(pidm,'STUPIDM'); /* Otherwise, store the value of the param in GORWPRM */ ELSE twbkwbis.P_SetParam(pidm,'STUPIDM',to_char(STUPIDM,'999999999')); bwlkoids.P_FacResetPin(pidm); 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, 'lmuhwskhsch.LMUHighSchool'); RETURN; END IF; /* 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 twbkwbis.P_OpenDoc('lmuhwskhsch.LMUHighSchool',header_text=> '*** Invalid Faculty ID ***'); msg := 'You must be a valid faculty member for the selected term'|| ' to access this page.'; HTP.hr; twbkfrmt.P_PrintImage(twbklibs.twgbwrul_rec.twgbwrul_error_gif); HTP.bold(msg); HTP.para; twbkwbis.P_CloseDoc(curr_release); RETURN; END IF; BEGIN select count(*) into rcount from sortest where sortest_pidm = hold_stupidm; EXCEPTION WHEN OTHERS THEN rcount := 0; END; /* Display message if no degree records found */ IF rcount = 0 THEN twbkwbis.P_OpenDoc('lmuhwskhsch.LMUHighSchool',header_text=> '*** No High School Information Exists ***'); twbkwbis.P_DispInfo('lmuhwskhsch.LMUHighSchool','NO_HS'); twbkwbis.P_CloseDoc(curr_release); return; END IF; /* Show Tesc Score Information For Student */ twbkwbis.P_OpenDoc('lmuhwskhsch.LMUHighSchool',header_text=> 'For: '|| f_format_name(hold_stupidm, 'FMIL')); twbkwbis.P_DispInfo('lmuhwskhsch.LMUHighSchool','GENERAL'); twbkfrmt.P_TableOpen('DATADISPLAY'); twbkfrmt.P_TableRowOpen('left'); twbkfrmt.P_TableDataLabel('High School',calign=>'left',ccolspan=>'3'); twbkfrmt.P_TableDataLabel('City',calign=>'left',ccolspan=>'2'); twbkfrmt.P_TableDataLabel('State',calign=>'left'); twbkfrmt.P_TableRowClose; twbkfrmt.P_TableRowOpen('left'); twbkfrmt.P_TableDataLabel('Graduation Date',calign=>'left'); twbkfrmt.P_TableDataLabel('Transcript Date',calign=>'left'); twbkfrmt.P_TableDataLabel('Class Rank',calign=>'center'); twbkfrmt.P_TableDataLabel('Class Size',calign=>'center'); twbkfrmt.P_TableDataLabel('Percentile',calign=>'center'); twbkfrmt.P_TableDataLabel('GPA',calign=>'center'); twbkfrmt.P_TableRowClose; FOR mystuff IN hsinfo LOOP twbkfrmt.P_TableRowOpen('left'); twbkfrmt.P_TableData(mystuff.hs,calign=>'left',ccolspan=>'3'); twbkfrmt.P_TableData(mystuff.hs_city,calign=>'left',ccolspan=>'2'); twbkfrmt.P_TableData(mystuff.hs_state,calign=>'left'); twbkfrmt.P_TableRowClose; twbkfrmt.P_TableRowOpen('left'); twbkfrmt.P_TableData(mystuff.grad_date,calign=>'left'); twbkfrmt.P_TableData(mystuff.trans_date,calign=>'left'); twbkfrmt.P_TableData(mystuff.c_rank,calign=>'center'); twbkfrmt.P_TableData(mystuff.c_size,calign=>'center'); twbkfrmt.P_TableData(mystuff.pct,calign=>'center'); twbkfrmt.P_TableData(mystuff.gpa,calign=>'center'); twbkfrmt.P_TableRowClose; END LOOP; twbkfrmt.P_TableClose; twbkwbis.P_CloseDoc; end LMUHighSchool; end lmuhwskhsch; / show errors whenever sqlerror continue; drop public synonym lmuhwskhsch; whenever sqlerror exit rollback; create public synonym lmuhwskhsch for lmuhwskhsch; grant execute on lmuhwskhsch to public; set scan on