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. -- 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 twgkwbis.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 := TWGKWBIS.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 TWGKWBIS.P_SetParam(pidm,'TERM',term); hold_term := term; END IF; /* Make sure a term has been selected */ IF hold_term is null THEN hwskostm.P_FacSelTerm(calling_proc_name=> 'lmuhwskhsch.LMUHighSchool'); RETURN; END IF; /* set paramater to indicate we are a faculty user */ twgkwbis.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 := twgkwbis.F_GetParam(pidm,'STUPIDM'); /* Otherwise, store the value of the param in GORWPRM */ ELSE twgkwbis.P_SetParam(pidm,'STUPIDM',to_char(STUPIDM,'999999999')); hwskoids.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 hwskoids.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 HWSKILIB.F_ValidFac(hold_term,pidm) THEN twgkwbis.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; twgkfrmt.P_PrintImage(twgklibs.twgbwrul_rec.twgbwrul_error_gif); HTP.bold(msg); HTP.para; TWGKWBIS.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 twgkwbis.P_OpenDoc('lmuhwskhsch.LMUHighSchool',header_text=> '*** No High School Information Exists ***'); twgkwbis.P_DispInfo('lmuhwskhsch.LMUHighSchool','NO_HS'); twgkwbis.P_CloseDoc(curr_release); return; END IF; /* Show Tesc Score Information For Student */ twgkwbis.P_OpenDoc('lmuhwskhsch.LMUHighSchool',header_text=> 'For: '|| f_format_name(hold_stupidm, 'FMIL')); twgkwbis.P_DispInfo('lmuhwskhsch.LMUHighSchool','GENERAL'); twgkfrmt.P_TableOpen('DATADISPLAY'); twgkfrmt.P_TableRowOpen('left'); twgkfrmt.P_TableDataLabel('High School',calign=>'left',ccolspan=>'3'); twgkfrmt.P_TableDataLabel('City',calign=>'left',ccolspan=>'2'); twgkfrmt.P_TableDataLabel('State',calign=>'left'); twgkfrmt.P_TableRowClose; twgkfrmt.P_TableRowOpen('left'); twgkfrmt.P_TableDataLabel('Graduation Date',calign=>'left'); twgkfrmt.P_TableDataLabel('Transcript Date',calign=>'left'); twgkfrmt.P_TableDataLabel('Class Rank',calign=>'center'); twgkfrmt.P_TableDataLabel('Class Size',calign=>'center'); twgkfrmt.P_TableDataLabel('Percentile',calign=>'center'); twgkfrmt.P_TableDataLabel('GPA',calign=>'center'); twgkfrmt.P_TableRowClose; FOR mystuff IN hsinfo LOOP twgkfrmt.P_TableRowOpen('left'); twgkfrmt.P_TableData(mystuff.hs,calign=>'left',ccolspan=>'3'); twgkfrmt.P_TableData(mystuff.hs_city,calign=>'left',ccolspan=>'2'); twgkfrmt.P_TableData(mystuff.hs_state,calign=>'left'); twgkfrmt.P_TableRowClose; twgkfrmt.P_TableRowOpen('left'); twgkfrmt.P_TableData(mystuff.grad_date,calign=>'left'); twgkfrmt.P_TableData(mystuff.trans_date,calign=>'left'); twgkfrmt.P_TableData(mystuff.c_rank,calign=>'center'); twgkfrmt.P_TableData(mystuff.c_size,calign=>'center'); twgkfrmt.P_TableData(mystuff.pct,calign=>'center'); twgkfrmt.P_TableData(mystuff.gpa,calign=>'center'); twgkfrmt.P_TableRowClose; END LOOP; twgkfrmt.P_TableClose; twgkwbis.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