set scan off set echo on -- Created for Loyola Marymount University -- By Robert M. Nitsos -- 21-MAR-2001 -- -- This package creates a Banner Web page that displays degree information in the -- Student Module. Students can see the status of their degree (Pending or Awarded), -- the Diploma Name we will use, whether the diploma has been ordered, and the -- degree and major(s) that are being used. -- create or replace package lmuhwskdegr is TYPE varchar2_tabtype IS TABLE OF varchar2(26) INDEX BY BINARY_INTEGER; procedure LMUDegreeInfo (stu_pidm in spriden.spriden_pidm%type default null, error_mess in varchar2 default null); END lmuhwskdegr; / show errors CREATE OR REPLACE PACKAGE BODY lmuhwskdegr 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 Degree Information ***********************/ procedure LMUDegreeInfo (stu_pidm in spriden.spriden_pidm%type default null, error_mess in varchar2 default null) is grad_term STVTERM.STVTERM_CODE%TYPE; my_stu_pidm spriden.spriden_pidm%type; dcount number; cursor degreeinfo is select spriden_id id, spriden_last_name||', '||spriden_first_name||' '||spriden_mi name, shbdipl_name diploma_name, shrdgmr_term_code_completed term_code, stvterm_desc term, shrdgmr_seq_no seqno, shrdgmr_degs_code degs, stvdegs_desc dstatus, shrdgmr_levl_code levl, shrdgmr_degc_code degc, stvdegc_desc degree, m.stvmajr_desc major, shrdgmr_coll_code_1 coll1, c1.stvcoll_desc college1, shrdgmr_majr_code_1 major1, m1.stvmajr_desc major_desc1, shrdgmr_majr_code_conc_1 conc1, cn1.stvmajr_desc conc_desc1, shrdgmr_majr_code_conc_1_2 conc1_2, cn1_2.stvmajr_desc conc_desc1_2, shrdgmr_majr_code_minr_1 minor1, mn1.stvmajr_desc minor_desc1, shrdgmr_majr_code_minr_1_2 minor1_2, mn1_2.stvmajr_desc minor_desc1_2, shrdgmr_coll_code_2 coll2, c2.stvcoll_desc college2, shrdgmr_majr_code_2 major2, m2.stvmajr_desc major_desc2, shrdgmr_majr_code_conc_2 conc2, cn2.stvmajr_desc conc_desc2, shrdgmr_majr_code_conc_2_2 conc2_2, cn2_2.stvmajr_desc conc_desc2_2, shrdgmr_majr_code_minr_2 minor2, mn2.stvmajr_desc minor_desc2, shrdgmr_majr_code_minr_2_2 minor2_2, mn2_2.stvmajr_desc minor_desc2_2, stvhonr_desc honors, to_char(shrdgmr_grad_date, 'Month DD, YYYY') grad_date, to_char(shbdipl_order_date, 'Month DD, YYYY') order_date from shrdgmr, stvterm, stvdegs, stvdegc, stvmajr m, shbdipl, shrdgih, stvhonr, spriden, stvcoll c1, stvcoll c2, stvmajr m1, stvmajr cn1, stvmajr cn1_2, stvmajr mn1, stvmajr mn1_2, stvmajr m2, stvmajr cn2, stvmajr cn2_2, stvmajr mn2, stvmajr mn2_2 where shrdgmr_pidm = my_stu_pidm and shrdgmr_term_code_completed=stvterm_code(+) and shrdgmr_degs_code in ('AW', 'PN') and shrdgmr_degs_code=stvdegs_code(+) and shrdgmr_degc_code=stvdegc_code(+) and shrdgmr_majr_code_1=m.stvmajr_code(+) and shrdgmr_coll_code_1=c1.stvcoll_code(+) and shrdgmr_coll_code_2=c2.stvcoll_code(+) and shrdgmr_majr_code_1=m1.stvmajr_code(+) and shrdgmr_majr_code_conc_1=cn1.stvmajr_code(+) and shrdgmr_majr_code_conc_1_2=cn1_2.stvmajr_code(+) and shrdgmr_majr_code_minr_1=mn1.stvmajr_code(+) and shrdgmr_majr_code_minr_1_2=mn1_2.stvmajr_code(+) and shrdgmr_majr_code_2=m2.stvmajr_code(+) and shrdgmr_majr_code_conc_2=cn2.stvmajr_code(+) and shrdgmr_majr_code_conc_2_2=cn2_2.stvmajr_code(+) and shrdgmr_majr_code_minr_2=mn2.stvmajr_code(+) and shrdgmr_majr_code_minr_2_2=mn2_2.stvmajr_code(+) and shrdgmr_pidm=shbdipl_pidm(+) and shrdgmr_seq_no=shbdipl_dgmr_seq_no(+) and shrdgmr_pidm=shrdgih_pidm(+) and shrdgmr_seq_no=shrdgih_dgmr_seq_no(+) and shrdgih_honr_code=stvhonr_code(+) and shrdgmr_pidm=spriden_pidm and spriden_change_ind is null order by term_code DESC, seqno; begin IF NOT twgkwbis.F_ValidUser(pidm) THEN return; END IF; my_stu_pidm := NVL(stu_pidm, pidm); BEGIN select count(*) into dcount from shrdgmr where shrdgmr_pidm = my_stu_pidm and shrdgmr_degs_code in ('PN', 'AW'); EXCEPTION WHEN OTHERS THEN dcount := 0; END; /* Display message if no degree records found */ IF dcount = 0 THEN twgkwbis.P_OpenDoc('lmuhwskdegr.LMUDegreeInfo',header_text=> '*** No Degree Information (Awarded or Pending) Exists ***'); twgkwbis.P_DispInfo('lmuhwskdegr.LMUDegreeInfo','NO_DEGREE'); twgkwbis.P_CloseDoc; return; END IF; /* Show Degree Information For Student */ twgkwbis.P_OpenDoc('lmuhwskdegr.LMUDegreeInfo',header_text=> 'For: '|| f_format_name(my_stu_pidm, 'FMIL')); twgkwbis.P_DispInfo('lmuhwskdegr.LMUDegreeInfo','DEFAULT'); twgkfrmt.P_TableOpen('DATADISPLAY'); twgkfrmt.P_TableRowOpen('left'); twgkfrmt.P_TableDataLabel('Degree',calign=>'center'); twgkfrmt.P_TableDataLabel('Major',calign=>'center'); twgkfrmt.P_TableDataLabel('Honors',calign=>'center'); twgkfrmt.P_TableDataLabel('Status',calign=>'center',ccolspan=>'2'); twgkfrmt.P_TableDataLabel('Degree Awarded Date',calign=>'center'); twgkfrmt.P_TableRowClose; twgkfrmt.P_TableRowOpen('left'); twgkfrmt.P_TableDataLabel('Name to Appear on Diploma',calign=>'center',ccolspan=>'3'); twgkfrmt.P_TableDataLabel('Diploma Order Date',calign=>'center',ccolspan=>'3'); twgkfrmt.P_TableRowClose; twgkfrmt.P_TableRowOpen('left'); twgkfrmt.P_TableDataLabel('Details of Degree',calign=>'center',ccolspan=>'6'); twgkfrmt.P_TableRowClose; twgkfrmt.P_TableRowOpen('left'); twgkfrmt.P_TableDataLabel('College 1',calign=>'center'); twgkfrmt.P_TableDataLabel('Major',calign=>'center'); twgkfrmt.P_TableDataLabel('Conc 1',calign=>'center'); twgkfrmt.P_TableDataLabel('Conc 2',calign=>'center'); twgkfrmt.P_TableDataLabel('Minor 1',calign=>'center'); twgkfrmt.P_TableDataLabel('Minor 2',calign=>'center'); twgkfrmt.P_TableRowClose; twgkfrmt.P_TableRowOpen('left'); twgkfrmt.P_TableDataLabel('College 2',calign=>'center'); twgkfrmt.P_TableDataLabel('Major',calign=>'center'); twgkfrmt.P_TableDataLabel('Conc 1',calign=>'center'); twgkfrmt.P_TableDataLabel('Conc 2',calign=>'center'); twgkfrmt.P_TableDataLabel('Minor 1',calign=>'center'); twgkfrmt.P_TableDataLabel('Minor 2',calign=>'center'); twgkfrmt.P_TableRowClose; FOR mystuff IN degreeinfo LOOP twgkfrmt.P_TableRowOpen('left'); twgkfrmt.P_TableData('***',calign=>'center',ccolspan=>'6'); twgkfrmt.P_TableRowClose; twgkfrmt.P_TableRowOpen('left'); twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.degree),calign=>'center'); twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.major),calign=>'center'); twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.honors),calign=>'center'); twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.dstatus||' for '||mystuff.term),calign=>'center',ccolspan=>'2'); twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.grad_date),calign=>'center'); twgkfrmt.P_TableRowClose; twgkfrmt.P_TableRowOpen('left'); twgkfrmt.P_TableData(mystuff.diploma_name,calign=>'center',ccolspan=>'3'); IF mystuff.order_date is null THEN IF mystuff.term_code<'199930' THEN twgkfrmt.P_TableData('N/A',calign=>'center',ccolspan=>'3'); ELSE twgkfrmt.P_TableData('Not Yet Ordered',calign=>'center',ccolspan=>'3'); END IF; ELSE twgkfrmt.P_TableData('Ordered: '||mystuff.order_date,calign=>'center',ccolspan=>'3'); END IF; twgkfrmt.P_TableRowClose; twgkfrmt.P_TableRowOpen('left'); twgkfrmt.P_TableData(mystuff.college1,calign=>'center'); twgkfrmt.P_TableData(mystuff.major_desc1,calign=>'center'); twgkfrmt.P_TableData(mystuff.conc_desc1,calign=>'center'); twgkfrmt.P_TableData(mystuff.conc_desc1_2,calign=>'center'); twgkfrmt.P_TableData(mystuff.minor_desc1,calign=>'center'); twgkfrmt.P_TableData(mystuff.minor_desc1_2,calign=>'center'); twgkfrmt.P_TableRowClose; IF mystuff.major2 is not null THEN twgkfrmt.P_TableRowOpen('left'); twgkfrmt.P_TableData(mystuff.college2,calign=>'center'); twgkfrmt.P_TableData(mystuff.major_desc2,calign=>'center'); twgkfrmt.P_TableData(mystuff.conc_desc2,calign=>'center'); twgkfrmt.P_TableData(mystuff.conc_desc2_2,calign=>'center'); twgkfrmt.P_TableData(mystuff.minor_desc2,calign=>'center'); twgkfrmt.P_TableData(mystuff.minor_desc2_2,calign=>'center'); twgkfrmt.P_TableRowClose; END IF; END LOOP; twgkfrmt.P_TableClose; twgkwbis.P_CloseDoc; end LMUDegreeInfo; end lmuhwskdegr; / show errors whenever sqlerror continue; drop public synonym lmuhwskdegr; whenever sqlerror exit rollback; create public synonym lmuhwskdegr for lmuhwskdegr; grant execute on lmuhwskdegr to public; set scan on