set scan off set echo on -- Created for Loyola Marymount University -- By Robert M. Nitsos -- 05-MAR-2003 -- -- This package creates Banner Web pages that display Mid Term Deficiency data -- in the Faculty Module. -- create or replace package lmuhwskmtgd is procedure LMUStudentMTG (STUPIDM in spriden.spriden_pidm%type default null, term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, error_mess in varchar2 default null); procedure LMUCollegeMTG (stu_coll in sgbstdn.sgbstdn_coll_code_1%type default null, stu_levl in sgbstdn.sgbstdn_levl_code%type default null, term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, error_mess in varchar2 default null); procedure LMUMajorMTG (stu_major in sgbstdn.sgbstdn_majr_code_1%type default null, stu_levl in sgbstdn.sgbstdn_levl_code%type default null, term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, error_mess in varchar2 default null); procedure LMUAdviseeMTG (term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, error_mess in varchar2 default null); procedure LMUDownloadCollegeMTG (stu_coll in sgbstdn.sgbstdn_coll_code_1%type default null, stu_levl in sgbstdn.sgbstdn_levl_code%type default null, term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL); procedure LMUDownloadMajorMTG (stu_major in sgbstdn.sgbstdn_majr_code_1%type default null, stu_levl in sgbstdn.sgbstdn_levl_code%type default null, term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL); procedure LMUDownloadAdviseeMTG (mypidm in spriden.spriden_pidm%type default null, term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL); END lmuhwskmtgd; / show errors CREATE OR REPLACE PACKAGE BODY lmuhwskmtgd AS /* make sure registered then continue to process */ /* Global type and variable declarations for package */ /* The following broken line is required */ NEWLINE constant varchar2(1) := ' '; pidm spriden.spriden_pidm%TYPE; row_count NUMBER; /****************************** LMU Show Mid-Term Deficiencies ***********************/ procedure LMUStudentMTG (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) := '5.5'; 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); term_desc stvterm.stvterm_desc%type; msg varchar2(255); term_rec stvterm%rowtype; rcount number; cursor stumtginfo is select sfrstcr_crn crn, ssbsect_subj_code subj, ssbsect_crse_numb crs, ssbsect_seq_numb sec, decode(ssbsect_crse_title, '', x.scbcrse_title, ssbsect_crse_title) title, sfrstcr_rsts_code rsts, stvrsts_desc rsts_desc, sfrstcr_rsts_date rsts_date, sfrstcr_gmod_code gmod, sfrstcr_credit_hr cr_hr, sfrstcr_grde_code_mid mtg, sfrstcr_grde_code grade from sfrstcr, stvrsts, ssbsect, scbcrse x where sfrstcr_pidm = hold_stupidm and sfrstcr_term_code = hold_term and sfrstcr_rsts_code=stvrsts_code and stvrsts_incl_sect_enrl='Y' and sfrstcr_crn=ssbsect_crn and sfrstcr_term_code=ssbsect_term_code and ssbsect_subj_code=x.scbcrse_subj_code and ssbsect_crse_numb=x.scbcrse_crse_numb and x.scbcrse_eff_term= (select max(y.scbcrse_eff_term) from scbcrse y where y.scbcrse_eff_term<=ssbsect_term_code and x.scbcrse_subj_code=y.scbcrse_subj_code and x.scbcrse_crse_numb=y.scbcrse_crse_numb) and sfrstcr_grde_code_mid is not null order by subj, crs, sec; 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=> 'lmuhwskmtgd.LMUStudentMTG'); 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, 'lmuhwskmtgd.LMUStudentMTG'); 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('lmuhwskmtgd.LMUStudentMTG',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 sfrstcr, stvrsts where sfrstcr_pidm = hold_stupidm and sfrstcr_term_code = hold_term and sfrstcr_rsts_code=stvrsts_code and stvrsts_incl_sect_enrl='Y' and sfrstcr_grde_code_mid is not null; EXCEPTION WHEN OTHERS THEN rcount := 0; END; /* Display message if no degree records found */ IF rcount = 0 THEN twbkwbis.P_OpenDoc('lmuhwskmtgd.LMUStudentMTG',header_text=> '*** No Mid-Term Deficiencies for Student ***'); twbkwbis.P_DispInfo('lmuhwskmtgd.LMUStudentMTG','NO_MTG'); twbkwbis.P_CloseDoc(curr_release); return; END IF; begin select stvterm_desc into term_desc from stvterm where stvterm_code = hold_term; exception when others then term_desc := 'Unknown'; end; /* Show MTGs For Student */ twbkwbis.P_OpenDoc('lmuhwskmtgd.LMUStudentMTG',header_text=> 'For: '|| f_format_name(hold_stupidm, 'FMIL')||' ('||term_desc||')'); twbkwbis.P_DispInfo('lmuhwskmtgd.LMUStudentMTG','GENERAL'); twbkfrmt.P_TableOpen('DATADISPLAY'); twbkfrmt.P_TableRowOpen('left'); twbkfrmt.P_TableDataLabel('CRN',calign=>'center'); twbkfrmt.P_TableDataLabel('SUBJ',calign=>'center'); twbkfrmt.P_TableDataLabel('CRS',calign=>'center'); twbkfrmt.P_TableDataLabel('SEC',calign=>'center'); twbkfrmt.P_TableDataLabel('TITLE',calign=>'left'); twbkfrmt.P_TableDataLabel('REG STATUS',calign=>'left'); twbkfrmt.P_TableDataLabel('STATUS DATE',calign=>'center'); twbkfrmt.P_TableDataLabel('MID TERM GRADE',calign=>'center'); twbkfrmt.P_TableDataLabel('FINAL GRADE',calign=>'center'); twbkfrmt.P_TableRowClose; FOR mystuff IN stumtginfo LOOP twbkfrmt.P_TableRowOpen('left'); twbkfrmt.P_TableData(mystuff.crn,calign=>'center'); twbkfrmt.P_TableData(mystuff.subj,calign=>'center'); twbkfrmt.P_TableData(mystuff.crs,calign=>'center'); twbkfrmt.P_TableData(mystuff.sec,calign=>'center'); twbkfrmt.P_TableData(mystuff.title,calign=>'left'); twbkfrmt.P_TableData(mystuff.rsts_desc,calign=>'left'); twbkfrmt.P_TableData(mystuff.rsts_date,calign=>'center'); twbkfrmt.P_TableData(mystuff.mtg,calign=>'center'); twbkfrmt.P_TableData(mystuff.grade,calign=>'center'); twbkfrmt.P_TableRowClose; END LOOP; twbkfrmt.P_TableClose; twbkwbis.P_CloseDoc(curr_release); end LMUStudentMTG; procedure LMUCollegeMTG (stu_coll in sgbstdn.sgbstdn_coll_code_1%type default null, stu_levl in sgbstdn.sgbstdn_levl_code%type default null, term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, error_mess in varchar2 default null) is curr_release varchar2(10) := '5.5'; hold_term stvterm.stvterm_code%TYPE; hold_stupidm spriden.spriden_pidm%TYPE; hold_stupidm_char varchar2(30) DEFAULT NULL; hold_coll sgbstdn.sgbstdn_coll_code_1%type; hold_levl sgbstdn.sgbstdn_levl_code%type; confid_msg varchar2(30) default null; student_name varchar2(124); term_desc stvterm.stvterm_desc%type; coll_desc stvcoll.stvcoll_desc%type; confid_info varchar2(120); dead_info varchar2(120); msg varchar2(255); term_rec stvterm%rowtype; rcount number; cursor mtginfo is select sfrstcr_pidm pidm, spriden_id id, sfrstcr_crn crn, ssbsect_subj_code subj, ssbsect_crse_numb crs, ssbsect_seq_numb sec, decode(ssbsect_crse_title, '', x.scbcrse_title, ssbsect_crse_title) title, sfrstcr_rsts_code rsts, stvrsts_desc rsts_desc, sfrstcr_rsts_date rsts_date, sfrstcr_gmod_code gmod, sfrstcr_credit_hr cr_hr, sfrstcr_grde_code_mid mtg, sfrstcr_grde_code grade, a.sgbstdn_levl_code levl, f_class_calc_fnc(a.sgbstdn_pidm, a.sgbstdn_levl_code, hold_term) class, stvstyp_desc stu_type, a.sgbstdn_coll_code_1 coll, a.sgbstdn_majr_code_1 major1, a.sgbstdn_majr_code_conc_1 conc1, a.sgbstdn_majr_code_conc_1_2 conc1_2, a.sgbstdn_majr_code_minr_1 minor1, a.sgbstdn_majr_code_minr_1_2 minor1_2, a.sgbstdn_majr_code_2 major2, a.sgbstdn_majr_code_conc_2 conc2, a.sgbstdn_majr_code_conc_2_2 conc2_2, a.sgbstdn_majr_code_minr_2 minor2, a.sgbstdn_majr_code_minr_2_2 minor2_2 from sfrstcr, stvrsts, spriden, ssbsect, scbcrse x, sgbstdn a, stvstyp where sfrstcr_term_code = hold_term and sfrstcr_rsts_code=stvrsts_code and stvrsts_incl_sect_enrl='Y' and sfrstcr_pidm=spriden_pidm and spriden_change_ind is null and sfrstcr_pidm=a.sgbstdn_pidm and a.sgbstdn_term_code_eff= (select max(b.sgbstdn_term_code_eff) from sgbstdn b where b.sgbstdn_term_code_eff <= sfrstcr_term_code and a.sgbstdn_pidm=b.sgbstdn_pidm) and a.sgbstdn_coll_code_1 = hold_coll and a.sgbstdn_levl_code = hold_levl and a.sgbstdn_styp_code=stvstyp_code(+) and sfrstcr_crn=ssbsect_crn and sfrstcr_term_code=ssbsect_term_code and ssbsect_subj_code=x.scbcrse_subj_code and ssbsect_crse_numb=x.scbcrse_crse_numb and x.scbcrse_eff_term= (select max(y.scbcrse_eff_term) from scbcrse y where y.scbcrse_eff_term<=ssbsect_term_code and x.scbcrse_subj_code=y.scbcrse_subj_code and x.scbcrse_crse_numb=y.scbcrse_crse_numb) and sfrstcr_grde_code_mid is not null order by spriden_last_name, spriden_first_name, spriden_mi, subj, crs, sec; 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=> 'lmuhwskmtgd.LMUCollegeMTG'); RETURN; END IF; /* set paramater to indicate we are a faculty user */ twbkwbis.P_SetParam(pidm,'STUFAC_IND','FAC'); /* 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('lmuhwskmtgd.LMUCollegeMTG',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; hold_coll := stu_coll; hold_levl := stu_levl; if hold_coll is null and hold_levl is null then rcount := 0; else BEGIN select 1 into rcount from dual where exists (select 'X' from sgbstdn a where a.sgbstdn_term_code_eff= (select max(b.sgbstdn_term_code_eff) from sgbstdn b where b.sgbstdn_term_code_eff<=hold_term and a.sgbstdn_pidm=b.sgbstdn_pidm) and a.sgbstdn_coll_code_1 = hold_coll and exists (select 'Y' from sfrstcr, stvrsts where sfrstcr_pidm=a.sgbstdn_pidm and sfrstcr_term_code = hold_term and sfrstcr_rsts_code=stvrsts_code and stvrsts_incl_sect_enrl='Y' and sfrstcr_grde_code_mid is not null)); EXCEPTION WHEN OTHERS THEN rcount := 0; END; end if; if rcount = 0 then if hold_coll is null then twbkwbis.P_OpenDoc('lmuhwskmtgd.LMUCollegeMTG'); twbkwbis.P_DispInfo('lmuhwskmtgd.LMUCollegeMTG','GENERAL'); else twbkwbis.P_OpenDoc('lmuhwskmtgd.LMUCollegeMTG',header_text=> '*** No Mid-Term Deficiencies for Selected College ***'); twbkwbis.P_DispInfo('lmuhwskmtgd.LMUCollegeMTG','NO_MTG'); end if; else begin select stvterm_desc into term_desc from stvterm where stvterm_code = hold_term; exception when others then term_desc := 'Unknown'; end; begin select stvcoll_desc into coll_desc from stvcoll where stvcoll_code = hold_coll; exception when others then coll_desc := 'Unknown'; end; /* Show MTGs For Student */ twbkwbis.P_OpenDoc('lmuhwskmtgd.LMUCollegeMTG',header_text=> 'For: '||coll_desc||' ('||term_desc||')'); twbkwbis.P_DispInfo('lmuhwskmtgd.LMUCollegeMTG','GENERAL'); /* Display Download Link */ htp.nl; htp.anchor(twbklibs.twgbwrul_rec.twgbwrul_cgibin_dir || '/lmuhwskmtgd.LMUDownloadCollegeMTG'|| '?stu_coll='||hold_coll||'&stu_levl='||hold_levl||'&term='||hold_term, 'DOWNLOAD DATA'); htp.nl; twbkfrmt.P_TableOpen('DATADISPLAY'); twbkfrmt.P_TableRowOpen('left'); /* twbkfrmt.P_TableDataLabel('ID',calign=>'center'); */ twbkfrmt.p_tabledatalabel('' || g$_nls.get ('BWLKFCW1-0267', 'SQL', 'ID') || ''); twbkfrmt.P_TableDataLabel('NAME',calign=>'left'); twbkfrmt.P_TableDataLabel('CLASS',calign=>'center'); twbkfrmt.P_TableDataLabel('CRN',calign=>'center'); twbkfrmt.P_TableDataLabel('SUBJ',calign=>'center'); twbkfrmt.P_TableDataLabel('CRS',calign=>'center'); twbkfrmt.P_TableDataLabel('SEC',calign=>'center'); twbkfrmt.P_TableDataLabel('TITLE',calign=>'left'); twbkfrmt.P_TableDataLabel('REG STATUS',calign=>'left'); twbkfrmt.P_TableDataLabel('STATUS DATE',calign=>'center'); twbkfrmt.P_TableDataLabel('MID TERM GRADE',calign=>'center'); twbkfrmt.P_TableDataLabel('FINAL GRADE',calign=>'center'); twbkfrmt.P_TableRowClose; FOR mystuff IN mtginfo LOOP twbkfrmt.P_TableRowOpen('left'); twbkfrmt.P_TableData(mystuff.id,calign=>'center'); student_name := f_format_name (mystuff.pidm, 'LFMI'); twbkfrmt.P_TableData(student_name,calign=>'left'); twbkfrmt.P_TableData(mystuff.class,calign=>'center'); twbkfrmt.P_TableData(mystuff.crn,calign=>'center'); twbkfrmt.P_TableData(mystuff.subj,calign=>'center'); twbkfrmt.P_TableData(mystuff.crs,calign=>'center'); twbkfrmt.P_TableData(mystuff.sec,calign=>'center'); twbkfrmt.P_TableData(mystuff.title,calign=>'left'); twbkfrmt.P_TableData(mystuff.rsts_desc,calign=>'left'); twbkfrmt.P_TableData(mystuff.rsts_date,calign=>'center'); twbkfrmt.P_TableData(mystuff.mtg,calign=>'center'); twbkfrmt.P_TableData(mystuff.grade,calign=>'center'); twbkfrmt.P_TableRowClose; END LOOP; twbkfrmt.P_TableClose; end if; /* Display List of Colleges */ htp.nl; HTP.formOpen(twbklibs.twgbwrul_rec.twgbwrul_cgibin_dir||'/'||'lmuhwskmtgd.LMUCollegeMTG'); htp.formhidden('term', hold_term); twbkfrmt.p_tableopen( 'DATAENTRY'); twbkfrmt.p_tablerowopen; twbkfrmt.p_tabledatalabel( 'Select College:'); twbkfrmt.p_tabledataopen; HTP.formSelectOpen('stu_coll',NULL,1); if hold_coll is null then HTP.P(twbkwbis.F_formSelectOption('Business Administration', 'BA','SELECTED')); HTP.P(twbkwbis.F_formSelectOption('Communication & Fine Arts', 'CF')); HTP.P(twbkwbis.F_formSelectOption('School of Education', 'ED')); HTP.P(twbkwbis.F_formSelectOption('School of Film & Television', 'FT')); HTP.P(twbkwbis.F_formSelectOption('Liberal Arts', 'LA')); HTP.P(twbkwbis.F_formSelectOption('Science & Engineering', 'SE')); else if hold_coll = 'BA' then HTP.P(twbkwbis.F_formSelectOption('Business Administration', 'BA','SELECTED')); else HTP.P(twbkwbis.F_formSelectOption('Business Administration', 'BA')); end if; if hold_coll = 'CF' then HTP.P(twbkwbis.F_formSelectOption('Communication & Fine Arts', 'CF','SELECTED')); else HTP.P(twbkwbis.F_formSelectOption('Communication & Fine Arts', 'CF')); end if; if hold_coll = 'ED' then HTP.P(twbkwbis.F_formSelectOption('School of Education', 'ED','SELECTED')); else HTP.P(twbkwbis.F_formSelectOption('School of Education', 'ED')); end if; if hold_coll = 'FT' then HTP.P(twbkwbis.F_formSelectOption('School of Film & Television', 'FT','SELECTED')); else HTP.P(twbkwbis.F_formSelectOption('School of Film & Television', 'FT')); end if; if hold_coll = 'LA' then HTP.P(twbkwbis.F_formSelectOption('Liberal Arts', 'LA','SELECTED')); else HTP.P(twbkwbis.F_formSelectOption('Liberal Arts', 'LA')); end if; if hold_coll = 'SE' then HTP.P(twbkwbis.F_formSelectOption('Science & Engineering', 'SE','SELECTED')); else HTP.P(twbkwbis.F_formSelectOption('Science & Engineering', 'SE')); end if; end if; HTP.formSelectClose; twbkfrmt.p_tabledataopen; twbkfrmt.p_tablerowclose; twbkfrmt.p_tablerowopen; twbkfrmt.p_tabledatalabel( 'Select Level:'); twbkfrmt.p_tabledataopen; HTP.formSelectOpen('stu_levl',NULL,1); if (hold_levl is null) or hold_levl = 'UG' then HTP.P(twbkwbis.F_formSelectOption('Undergraduate', 'UG','SELECTED')); HTP.P(twbkwbis.F_formSelectOption('Graduate', 'GR')); else HTP.P(twbkwbis.F_formSelectOption('Undergraduate', 'UG')); HTP.P(twbkwbis.F_formSelectOption('Graduate', 'GR','SELECTED')); end if; HTP.formSelectClose; twbkfrmt.p_tabledataopen; twbkfrmt.p_tablerowclose; twbkfrmt.p_tableclose; HTP.nl; HTP.formsubmit(NULL,'Submit'); HTP.formClose; htp.nl; twbkwbis.P_CloseDoc(curr_release); end LMUCollegeMTG; procedure LMUMajorMTG (stu_major in sgbstdn.sgbstdn_majr_code_1%type default null, stu_levl in sgbstdn.sgbstdn_levl_code%type default null, term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, error_mess in varchar2 default null) is curr_release varchar2(10) := '5.5'; hold_term stvterm.stvterm_code%TYPE; hold_stupidm spriden.spriden_pidm%TYPE; hold_stupidm_char varchar2(30) DEFAULT NULL; hold_major sgbstdn.sgbstdn_majr_code_1%type; hold_levl sgbstdn.sgbstdn_levl_code%type; confid_msg varchar2(30) default null; student_name varchar2(124); term_desc stvterm.stvterm_desc%type; coll_desc stvcoll.stvcoll_desc%type; major_desc stvmajr.stvmajr_desc%type; confid_info varchar2(120); dead_info varchar2(120); msg varchar2(255); term_rec stvterm%rowtype; rcount number; cursor majorinfo is select distinct a.sgbstdn_majr_code_1 major, stvmajr_desc major_desc from sgbstdn a, stvmajr where a.sgbstdn_term_code_eff= (select max(b.sgbstdn_term_code_eff) from sgbstdn b where b.sgbstdn_term_code_eff <= hold_term and a.sgbstdn_pidm=b.sgbstdn_pidm) and a.sgbstdn_majr_code_1=stvmajr_code(+) and exists (select 'X' from sfrstcr, stvrsts where sfrstcr_pidm=a.sgbstdn_pidm and sfrstcr_term_code = hold_term and sfrstcr_rsts_code=stvrsts_code and stvrsts_incl_sect_enrl='Y') order by major_desc; cursor mtginfo is select sfrstcr_pidm pidm, spriden_id id, sfrstcr_crn crn, ssbsect_subj_code subj, ssbsect_crse_numb crs, ssbsect_seq_numb sec, decode(ssbsect_crse_title, '', x.scbcrse_title, ssbsect_crse_title) title, sfrstcr_rsts_code rsts, stvrsts_desc rsts_desc, sfrstcr_rsts_date rsts_date, sfrstcr_gmod_code gmod, sfrstcr_credit_hr cr_hr, sfrstcr_grde_code_mid mtg, sfrstcr_grde_code grade, a.sgbstdn_levl_code levl, f_class_calc_fnc(a.sgbstdn_pidm, a.sgbstdn_levl_code, hold_term) class, stvstyp_desc stu_type, a.sgbstdn_coll_code_1 coll, a.sgbstdn_majr_code_1 major1, a.sgbstdn_majr_code_conc_1 conc1, a.sgbstdn_majr_code_conc_1_2 conc1_2, a.sgbstdn_majr_code_minr_1 minor1, a.sgbstdn_majr_code_minr_1_2 minor1_2, a.sgbstdn_majr_code_2 major2, a.sgbstdn_majr_code_conc_2 conc2, a.sgbstdn_majr_code_conc_2_2 conc2_2, a.sgbstdn_majr_code_minr_2 minor2, a.sgbstdn_majr_code_minr_2_2 minor2_2 from sfrstcr, stvrsts, spriden, ssbsect, scbcrse x, sgbstdn a, stvstyp where sfrstcr_term_code = hold_term and sfrstcr_rsts_code=stvrsts_code and stvrsts_incl_sect_enrl='Y' and sfrstcr_pidm=spriden_pidm and spriden_change_ind is null and sfrstcr_pidm=a.sgbstdn_pidm and a.sgbstdn_term_code_eff= (select max(b.sgbstdn_term_code_eff) from sgbstdn b where b.sgbstdn_term_code_eff <= sfrstcr_term_code and a.sgbstdn_pidm=b.sgbstdn_pidm) and a.sgbstdn_levl_code = hold_levl and (a.sgbstdn_majr_code_1 = hold_major or a.sgbstdn_majr_code_2 = hold_major) and a.sgbstdn_styp_code=stvstyp_code(+) and sfrstcr_crn=ssbsect_crn and sfrstcr_term_code=ssbsect_term_code and ssbsect_subj_code=x.scbcrse_subj_code and ssbsect_crse_numb=x.scbcrse_crse_numb and x.scbcrse_eff_term= (select max(y.scbcrse_eff_term) from scbcrse y where y.scbcrse_eff_term<=ssbsect_term_code and x.scbcrse_subj_code=y.scbcrse_subj_code and x.scbcrse_crse_numb=y.scbcrse_crse_numb) and sfrstcr_grde_code_mid is not null order by spriden_last_name, spriden_first_name, spriden_mi, subj, crs, sec; 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=> 'lmuhwskmtgd.LMUMajorMTG'); RETURN; END IF; /* set paramater to indicate we are a faculty user */ twbkwbis.P_SetParam(pidm,'STUFAC_IND','FAC'); /* 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('lmuhwskmtgd.LMUMajorMTG',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; hold_major := stu_major; hold_levl := stu_levl; if hold_major is null and hold_levl is null then rcount := 0; else BEGIN select 1 into rcount from dual where exists (select 'X' from sgbstdn a where a.sgbstdn_term_code_eff= (select max(b.sgbstdn_term_code_eff) from sgbstdn b where b.sgbstdn_term_code_eff<=hold_term and a.sgbstdn_pidm=b.sgbstdn_pidm) and (a.sgbstdn_majr_code_1 = hold_major or a.sgbstdn_majr_code_2 = hold_major) and exists (select 'Y' from sfrstcr, stvrsts where sfrstcr_pidm=a.sgbstdn_pidm and sfrstcr_term_code = hold_term and sfrstcr_rsts_code=stvrsts_code and stvrsts_incl_sect_enrl='Y' and sfrstcr_grde_code_mid is not null)); EXCEPTION WHEN OTHERS THEN rcount := 0; END; end if; if rcount = 0 then if hold_major is null then twbkwbis.P_OpenDoc('lmuhwskmtgd.LMUMajorMTG'); twbkwbis.P_DispInfo('lmuhwskmtgd.LMUMajorMTG','GENERAL'); else twbkwbis.P_OpenDoc('lmuhwskmtgd.LMUMajorMTG',header_text=> '*** No Mid-Term Deficiencies for Selected Major ***'); twbkwbis.P_DispInfo('lmuhwskmtgd.LMUMajorMTG','NO_MTG'); end if; else begin select stvterm_desc into term_desc from stvterm where stvterm_code = hold_term; exception when others then term_desc := 'Unknown'; end; begin select stvmajr_desc into major_desc from stvmajr where stvmajr_code = hold_major; exception when others then major_desc := 'Unknown'; end; /* Show MTGs For Students */ twbkwbis.P_OpenDoc('lmuhwskmtgd.LMUMajorMTG',header_text=> 'For: '||major_desc||' ('||term_desc||')'); twbkwbis.P_DispInfo('lmuhwskmtgd.LMUMajorMTG','GENERAL'); /* Display Download Link */ htp.nl; htp.anchor(twbklibs.twgbwrul_rec.twgbwrul_cgibin_dir || '/lmuhwskmtgd.LMUDownloadMajorMTG'|| '?stu_major='||hold_major||'&stu_levl='||hold_levl||'&term='||hold_term, 'DOWNLOAD DATA'); htp.nl; twbkfrmt.P_TableOpen('DATADISPLAY'); twbkfrmt.P_TableRowOpen('left'); twbkfrmt.P_TableDataLabel('ID',calign=>'center'); twbkfrmt.P_TableDataLabel('NAME',calign=>'left'); twbkfrmt.P_TableDataLabel('CLASS',calign=>'center'); twbkfrmt.P_TableDataLabel('MAJOR1',calign=>'center'); twbkfrmt.P_TableDataLabel('MAJOR2',calign=>'center'); twbkfrmt.P_TableDataLabel('CRN',calign=>'center'); twbkfrmt.P_TableDataLabel('SUBJ',calign=>'center'); twbkfrmt.P_TableDataLabel('CRS',calign=>'center'); twbkfrmt.P_TableDataLabel('SEC',calign=>'center'); twbkfrmt.P_TableDataLabel('TITLE',calign=>'left'); twbkfrmt.P_TableDataLabel('REG STATUS',calign=>'left'); twbkfrmt.P_TableDataLabel('STATUS DATE',calign=>'center'); twbkfrmt.P_TableDataLabel('MID TERM GRADE',calign=>'center'); twbkfrmt.P_TableDataLabel('FINAL GRADE',calign=>'center'); twbkfrmt.P_TableRowClose; FOR mystuff IN mtginfo LOOP twbkfrmt.P_TableRowOpen('left'); twbkfrmt.P_TableData(mystuff.id,calign=>'center'); student_name := f_format_name (mystuff.pidm, 'LFMI'); twbkfrmt.P_TableData(student_name,calign=>'left'); twbkfrmt.P_TableData(mystuff.class,calign=>'center'); twbkfrmt.P_TableData(mystuff.major1,calign=>'center'); twbkfrmt.P_TableData(mystuff.major2,calign=>'center'); twbkfrmt.P_TableData(mystuff.crn,calign=>'center'); twbkfrmt.P_TableData(mystuff.subj,calign=>'center'); twbkfrmt.P_TableData(mystuff.crs,calign=>'center'); twbkfrmt.P_TableData(mystuff.sec,calign=>'center'); twbkfrmt.P_TableData(mystuff.title,calign=>'left'); twbkfrmt.P_TableData(mystuff.rsts_desc,calign=>'left'); twbkfrmt.P_TableData(mystuff.rsts_date,calign=>'center'); twbkfrmt.P_TableData(mystuff.mtg,calign=>'center'); twbkfrmt.P_TableData(mystuff.grade,calign=>'center'); twbkfrmt.P_TableRowClose; END LOOP; twbkfrmt.P_TableClose; end if; /* Display List of Majors */ htp.nl; HTP.formOpen(twbklibs.twgbwrul_rec.twgbwrul_cgibin_dir||'/'||'lmuhwskmtgd.LMUMajorMTG'); htp.formhidden('term', hold_term); twbkfrmt.p_tableopen( 'DATAENTRY'); twbkfrmt.p_tablerowopen; twbkfrmt.p_tabledatalabel( 'Select Major:'); twbkfrmt.p_tabledataopen; HTP.formSelectOpen('stu_major',NULL,1); row_count := 0; for mystuff in majorinfo loop IF hold_major = mystuff.major THEN htp.p(twbkwbis.F_formSelectOption(mystuff.major_desc, mystuff.major, 'SELECTED')); ELSE htp.p(twbkwbis.F_formSelectOption(mystuff.major_desc, mystuff.major)); END IF; end loop; HTP.formSelectClose; twbkfrmt.p_tabledataopen; twbkfrmt.p_tablerowclose; twbkfrmt.p_tablerowopen; twbkfrmt.p_tabledatalabel( 'Select Level:'); twbkfrmt.p_tabledataopen; HTP.formSelectOpen('stu_levl',NULL,1); if (hold_levl is null) or hold_levl = 'UG' then HTP.P(twbkwbis.F_formSelectOption('Undergraduate', 'UG','SELECTED')); HTP.P(twbkwbis.F_formSelectOption('Graduate', 'GR')); else HTP.P(twbkwbis.F_formSelectOption('Undergraduate', 'UG')); HTP.P(twbkwbis.F_formSelectOption('Graduate', 'GR','SELECTED')); end if; HTP.formSelectClose; twbkfrmt.p_tabledataopen; twbkfrmt.p_tablerowclose; twbkfrmt.p_tableclose; HTP.nl; HTP.formsubmit(NULL,'Submit'); HTP.formClose; htp.nl; twbkwbis.P_CloseDoc(curr_release); end LMUMajorMTG; procedure LMUAdviseeMTG (term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, error_mess in varchar2 default null) is curr_release varchar2(10) := '5.5'; hold_term stvterm.stvterm_code%TYPE; hold_pidm spriden.spriden_pidm%TYPE; confid_msg varchar2(30) default null; student_name varchar2(124); term_desc stvterm.stvterm_desc%type; confid_info varchar2(120); dead_info varchar2(120); msg varchar2(255); term_rec stvterm%rowtype; rcount number; cursor mtginfo is select sfrstcr_pidm pidm, spriden_id id, sfrstcr_crn crn, ssbsect_subj_code subj, ssbsect_crse_numb crs, ssbsect_seq_numb sec, decode(ssbsect_crse_title, '', x.scbcrse_title, ssbsect_crse_title) title, sfrstcr_rsts_code rsts, stvrsts_desc rsts_desc, sfrstcr_rsts_date rsts_date, sfrstcr_gmod_code gmod, sfrstcr_credit_hr cr_hr, sfrstcr_grde_code_mid mtg, sfrstcr_grde_code grade, a.sgbstdn_levl_code levl, f_class_calc_fnc(a.sgbstdn_pidm, a.sgbstdn_levl_code, hold_term) class, stvstyp_desc stu_type, a.sgbstdn_coll_code_1 coll, a.sgbstdn_majr_code_1 major1, a.sgbstdn_majr_code_conc_1 conc1, a.sgbstdn_majr_code_conc_1_2 conc1_2, a.sgbstdn_majr_code_minr_1 minor1, a.sgbstdn_majr_code_minr_1_2 minor1_2, a.sgbstdn_majr_code_2 major2, a.sgbstdn_majr_code_conc_2 conc2, a.sgbstdn_majr_code_conc_2_2 conc2_2, a.sgbstdn_majr_code_minr_2 minor2, a.sgbstdn_majr_code_minr_2_2 minor2_2 from sfrstcr, stvrsts, spriden, ssbsect, scbcrse x, sgbstdn a, stvstyp where sfrstcr_term_code = hold_term and sfrstcr_rsts_code=stvrsts_code and stvrsts_incl_sect_enrl='Y' and sfrstcr_pidm=spriden_pidm and spriden_change_ind is null and sfrstcr_pidm=a.sgbstdn_pidm and a.sgbstdn_term_code_eff= (select max(b.sgbstdn_term_code_eff) from sgbstdn b where b.sgbstdn_term_code_eff <= sfrstcr_term_code and a.sgbstdn_pidm=b.sgbstdn_pidm) and a.sgbstdn_styp_code=stvstyp_code(+) and sfrstcr_crn=ssbsect_crn and sfrstcr_term_code=ssbsect_term_code and ssbsect_subj_code=x.scbcrse_subj_code and ssbsect_crse_numb=x.scbcrse_crse_numb and x.scbcrse_eff_term= (select max(y.scbcrse_eff_term) from scbcrse y where y.scbcrse_eff_term<=ssbsect_term_code and x.scbcrse_subj_code=y.scbcrse_subj_code and x.scbcrse_crse_numb=y.scbcrse_crse_numb) and sfrstcr_grde_code_mid is not null and exists (select 'X' from sgradvr x where x.sgradvr_pidm = sfrstcr_pidm and x.sgradvr_term_code_eff= (select max(y.sgradvr_term_code_eff) from sgradvr y where y.sgradvr_term_code_eff <= hold_term and x.sgradvr_pidm=y.sgradvr_pidm) and x.sgradvr_advr_pidm = hold_pidm) order by spriden_last_name, spriden_first_name, spriden_mi, subj, crs, sec; begin IF NOT twbkwbis.F_ValidUser(pidm) THEN return; END IF; hold_pidm := pidm; /* 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=> 'lmuhwskmtgd.LMUAdviseeMTG'); RETURN; END IF; /* set paramater to indicate we are a faculty user */ twbkwbis.P_SetParam(pidm,'STUFAC_IND','FAC'); /* 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('lmuhwskmtgd.LMUAdviseeMTG',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 1 into rcount from dual where exists (select 'X' from sgbstdn a where a.sgbstdn_term_code_eff= (select max(b.sgbstdn_term_code_eff) from sgbstdn b where b.sgbstdn_term_code_eff<=hold_term and a.sgbstdn_pidm=b.sgbstdn_pidm) and exists (select 'A' from sgradvr x where x.sgradvr_pidm = a.sgbstdn_pidm and x.sgradvr_advr_pidm = hold_pidm and x.sgradvr_term_code_eff= (select max(y.sgradvr_term_code_eff) from sgradvr y where y.sgradvr_term_code_eff <= hold_term and x.sgradvr_pidm=y.sgradvr_pidm)) and exists (select 'Y' from sfrstcr, stvrsts where sfrstcr_pidm=a.sgbstdn_pidm and sfrstcr_term_code = hold_term and sfrstcr_rsts_code=stvrsts_code and stvrsts_incl_sect_enrl='Y' and sfrstcr_grde_code_mid is not null)); EXCEPTION WHEN OTHERS THEN rcount := 0; END; if rcount = 0 then twbkwbis.P_OpenDoc('lmuhwskmtgd.LMUAdviseeMTG',header_text=> '*** No Mid-Term Deficiencies for Advisees ***'); twbkwbis.P_DispInfo('lmuhwskmtgd.LMUAdviseeMTG','NO_MTG'); else begin select stvterm_desc into term_desc from stvterm where stvterm_code = hold_term; exception when others then term_desc := 'Unknown'; end; /* Show MTGs For Students */ twbkwbis.P_OpenDoc('lmuhwskmtgd.LMUAdviseeMTG',header_text=> 'For: '||term_desc); twbkwbis.P_DispInfo('lmuhwskmtgd.LMUAdviseeMTG','GENERAL'); /* Display Download Link */ htp.nl; htp.anchor(twbklibs.twgbwrul_rec.twgbwrul_cgibin_dir || '/lmuhwskmtgd.LMUDownloadAdviseeMTG'|| '?mypidm='||hold_pidm||'&term='||hold_term, 'DOWNLOAD DATA'); htp.nl; twbkfrmt.P_TableOpen('DATADISPLAY'); twbkfrmt.P_TableRowOpen('left'); twbkfrmt.P_TableDataLabel('ID',calign=>'center'); twbkfrmt.P_TableDataLabel('NAME',calign=>'left'); twbkfrmt.P_TableDataLabel('CLASS',calign=>'center'); twbkfrmt.P_TableDataLabel('MAJOR1',calign=>'center'); twbkfrmt.P_TableDataLabel('MAJOR2',calign=>'center'); twbkfrmt.P_TableDataLabel('CRN',calign=>'center'); twbkfrmt.P_TableDataLabel('SUBJ',calign=>'center'); twbkfrmt.P_TableDataLabel('CRS',calign=>'center'); twbkfrmt.P_TableDataLabel('SEC',calign=>'center'); twbkfrmt.P_TableDataLabel('TITLE',calign=>'left'); twbkfrmt.P_TableDataLabel('REG STATUS',calign=>'left'); twbkfrmt.P_TableDataLabel('STATUS DATE',calign=>'center'); twbkfrmt.P_TableDataLabel('MID TERM GRADE',calign=>'center'); twbkfrmt.P_TableDataLabel('FINAL GRADE',calign=>'center'); twbkfrmt.P_TableRowClose; FOR mystuff IN mtginfo LOOP twbkfrmt.P_TableRowOpen('left'); twbkfrmt.P_TableData(mystuff.id,calign=>'center'); student_name := f_format_name (mystuff.pidm, 'LFMI'); twbkfrmt.P_TableData(student_name,calign=>'left'); twbkfrmt.P_TableData(mystuff.class,calign=>'center'); twbkfrmt.P_TableData(mystuff.major1,calign=>'center'); twbkfrmt.P_TableData(mystuff.major2,calign=>'center'); twbkfrmt.P_TableData(mystuff.crn,calign=>'center'); twbkfrmt.P_TableData(mystuff.subj,calign=>'center'); twbkfrmt.P_TableData(mystuff.crs,calign=>'center'); twbkfrmt.P_TableData(mystuff.sec,calign=>'center'); twbkfrmt.P_TableData(mystuff.title,calign=>'left'); twbkfrmt.P_TableData(mystuff.rsts_desc,calign=>'left'); twbkfrmt.P_TableData(mystuff.rsts_date,calign=>'center'); twbkfrmt.P_TableData(mystuff.mtg,calign=>'center'); twbkfrmt.P_TableData(mystuff.grade,calign=>'center'); twbkfrmt.P_TableRowClose; END LOOP; twbkfrmt.P_TableClose; end if; twbkwbis.P_CloseDoc(curr_release); end LMUAdviseeMTG; procedure LMUDownloadCollegeMTG (stu_coll in sgbstdn.sgbstdn_coll_code_1%type default null, stu_levl in sgbstdn.sgbstdn_levl_code%type default null, term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL) is mytab varchar2(1); cursor mtginfo is select sfrstcr_pidm pidm, spriden_id id, spriden_last_name lname, spriden_first_name fname, spriden_mi mi, decode(spbpers_confid_ind, 'Y', '***Confidential***', '') confid, sfrstcr_crn crn, ssbsect_subj_code subj, ssbsect_crse_numb crs, ssbsect_seq_numb sec, decode(ssbsect_crse_title, '', x.scbcrse_title, ssbsect_crse_title) title, sfrstcr_rsts_code rsts, stvrsts_desc rsts_desc, sfrstcr_rsts_date rsts_date, sfrstcr_gmod_code gmod, sfrstcr_credit_hr cr_hr, sfrstcr_grde_code_mid mtg, sfrstcr_grde_code grade, a.sgbstdn_levl_code levl, f_class_calc_fnc(a.sgbstdn_pidm, a.sgbstdn_levl_code, term) class, stvstyp_desc stu_type, a.sgbstdn_coll_code_1 coll, a.sgbstdn_majr_code_1 major1, a.sgbstdn_majr_code_conc_1 conc1, a.sgbstdn_majr_code_conc_1_2 conc1_2, a.sgbstdn_majr_code_minr_1 minor1, a.sgbstdn_majr_code_minr_1_2 minor1_2, a.sgbstdn_majr_code_2 major2, a.sgbstdn_majr_code_conc_2 conc2, a.sgbstdn_majr_code_conc_2_2 conc2_2, a.sgbstdn_majr_code_minr_2 minor2, a.sgbstdn_majr_code_minr_2_2 minor2_2, decode(cm.spraddr_street_line1, '', decode(ma.spraddr_street_line1, '', pr.spraddr_street_line1, ma.spraddr_street_line1), cm.spraddr_street_line1) street1, decode(cm.spraddr_street_line1, '', decode(ma.spraddr_street_line1, '', pr.spraddr_street_line2, ma.spraddr_street_line2), cm.spraddr_street_line2) street2, decode(cm.spraddr_street_line1, '', decode(ma.spraddr_street_line1, '', pr.spraddr_street_line3, ma.spraddr_street_line3), cm.spraddr_street_line3) street3, decode(cm.spraddr_street_line1, '', decode(ma.spraddr_street_line1, '', pr.spraddr_city, ma.spraddr_city), cm.spraddr_city) city, decode(cm.spraddr_street_line1, '', decode(ma.spraddr_street_line1, '', pr.spraddr_stat_code, ma.spraddr_stat_code), cm.spraddr_stat_code) state, decode(cm.spraddr_street_line1, '', decode(ma.spraddr_street_line1, '', pr.spraddr_zip, ma.spraddr_zip), cm.spraddr_zip) zip, decode(cm.spraddr_street_line1, '', decode(ma.spraddr_street_line1, '', pn.stvnatn_nation, mn.stvnatn_nation), cn.stvnatn_nation) nation, decode(ct.sprtele_phone_area, '', decode(mt.sprtele_phone_area, '', pt.sprtele_phone_area||'-'||substr(pt.sprtele_phone_number, 1, 3)||'-'||substr(pt.sprtele_phone_number, 4, 4), mt.sprtele_phone_area||'-'||substr(mt.sprtele_phone_number, 1, 3)||'-'||substr(mt.sprtele_phone_number, 4, 4)), ct.sprtele_phone_area||'-'||substr(ct.sprtele_phone_number, 1, 3)||'-'||substr(ct.sprtele_phone_number, 4, 4)) phone, goremal_email_address email from sfrstcr, stvrsts, spriden, spbpers, ssbsect, scbcrse x, sgbstdn a, stvstyp, spraddr cm, stvnatn cn, sprtele ct, spraddr ma, stvnatn mn, sprtele mt, spraddr pr, stvnatn pn, sprtele pt, goremal where sfrstcr_term_code = term and sfrstcr_rsts_code=stvrsts_code and stvrsts_incl_sect_enrl='Y' and sfrstcr_grde_code_mid is not null and sfrstcr_pidm=spriden_pidm and spriden_change_ind is null and sfrstcr_pidm=spbpers_pidm and sfrstcr_pidm=a.sgbstdn_pidm and a.sgbstdn_term_code_eff= (select max(b.sgbstdn_term_code_eff) from sgbstdn b where b.sgbstdn_term_code_eff <= sfrstcr_term_code and a.sgbstdn_pidm=b.sgbstdn_pidm) and a.sgbstdn_coll_code_1 = stu_coll and a.sgbstdn_levl_code = stu_levl and a.sgbstdn_styp_code=stvstyp_code(+) and sfrstcr_crn=ssbsect_crn and sfrstcr_term_code=ssbsect_term_code and ssbsect_subj_code=x.scbcrse_subj_code and ssbsect_crse_numb=x.scbcrse_crse_numb and x.scbcrse_eff_term= (select max(y.scbcrse_eff_term) from scbcrse y where y.scbcrse_eff_term<=ssbsect_term_code and x.scbcrse_subj_code=y.scbcrse_subj_code and x.scbcrse_crse_numb=y.scbcrse_crse_numb) and sfrstcr_pidm=cm.spraddr_pidm(+) and cm.spraddr_atyp_code(+)='IM' and cm.spraddr_status_ind(+) is null and cm.spraddr_natn_code=cn.stvnatn_code(+) and cm.spraddr_pidm=ct.sprtele_pidm(+) and cm.spraddr_atyp_code=ct.sprtele_atyp_code(+) and cm.spraddr_seqno=ct.sprtele_addr_seqno(+) and ct.sprtele_tele_code(+)='IM' and ct.sprtele_status_ind(+) is null and ct.sprtele_primary_ind(+)='Y' and sfrstcr_pidm=ma.spraddr_pidm(+) and ma.spraddr_atyp_code(+)='MA' and ma.spraddr_status_ind(+) is null and ma.spraddr_natn_code=mn.stvnatn_code(+) and ma.spraddr_pidm=mt.sprtele_pidm(+) and ma.spraddr_atyp_code=mt.sprtele_atyp_code(+) and ma.spraddr_seqno=mt.sprtele_addr_seqno(+) and mt.sprtele_tele_code(+)='MA' and mt.sprtele_status_ind(+) is null and mt.sprtele_primary_ind(+)='Y' and sfrstcr_pidm=pr.spraddr_pidm(+) and pr.spraddr_atyp_code(+)='PR' and pr.spraddr_status_ind(+) is null and pr.spraddr_natn_code=pn.stvnatn_code(+) and pr.spraddr_pidm=pt.sprtele_pidm(+) and pr.spraddr_atyp_code=pt.sprtele_atyp_code(+) and pr.spraddr_seqno=pt.sprtele_addr_seqno(+) and pt.sprtele_tele_code(+)='PR' and pt.sprtele_status_ind(+) is null and pt.sprtele_primary_ind(+)='Y' and sfrstcr_pidm=goremal_pidm(+) and goremal_preferred_ind(+)='Y' and goremal_status_ind(+)='A' order by spriden_last_name, spriden_first_name, spriden_mi, subj, crs, sec; begin mytab:=' '; owa_util.mime_header('application/vnd.ms-excel'); owa_util.http_header_close; htp.print('ID'||mytab||'Last Name'||mytab||'First Name'||mytab||'MI'||mytab||'Confidential'||mytab||'CRN'||mytab||'SUBJ'||mytab|| 'CRS'||mytab||'SEC'||mytab||'TITLE'||mytab||'STATUS'||mytab||'STATUS DATE'||mytab||'GRADE MODE'||mytab||'CR HOUR'||mytab|| 'MTG'||mytab||'FINAL GRADE'||mytab||'STU LEVEL'||mytab||'CLASS'||mytab||'STU TYPE'||mytab||'COLL'||mytab||'MAJOR 1'||mytab|| 'CONC 1'||mytab||'CONC 1_2'||mytab||'MINOR 1'||mytab||'MINOR 1_2'||mytab||'MAJOR 2'||mytab||'CONC 2'||mytab||'CONC 2_2'||mytab|| 'MINOR 2'||mytab||'MINOR 2_2'||mytab||'Street 1'||mytab||'Street 2'||mytab||'Street 3'||mytab||'City'||mytab||'State'||mytab|| 'Zip'||mytab||'Nation'||mytab||'Phone'||mytab||'Email'); FOR mystuff in mtginfo LOOP htp.print(''''||mystuff.id||mytab||mystuff.lname||mytab||mystuff.fname||mytab||mystuff.mi||mytab||mystuff.confid||mytab|| mystuff.crn||mytab||mystuff.subj||mytab||mystuff.crs||mytab||mystuff.sec||mytab||mystuff.title||mytab||mystuff.rsts||mytab|| mystuff.rsts_date||mytab||mystuff.gmod||mytab||mystuff.cr_hr||mytab||mystuff.mtg||mytab||mystuff.grade||mytab|| mystuff.levl||mytab||mystuff.class||mytab||mystuff.stu_type||mytab||mystuff.coll||mytab||mystuff.major1||mytab||mystuff.conc1||mytab|| mystuff.conc1_2||mytab||mystuff.minor1||mytab||mystuff.minor1_2||mytab||mystuff.major2||mytab||mystuff.conc2||mytab|| mystuff.conc2_2||mytab||mystuff.minor2||mytab||mystuff.minor2_2||mytab||mystuff.street1||mytab||mystuff.street2||mytab|| mystuff.street3||mytab||mystuff.city||mytab||mystuff.state||mytab||mystuff.zip||mytab||mystuff.nation||mytab|| mystuff.phone||mytab||mystuff.email); END LOOP; end LMUDownloadCollegeMTG; procedure LMUDownloadMajorMTG (stu_major in sgbstdn.sgbstdn_majr_code_1%type default null, stu_levl in sgbstdn.sgbstdn_levl_code%type default null, term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL) is mytab varchar2(1); cursor mtginfo is select sfrstcr_pidm pidm, spriden_id id, spriden_last_name lname, spriden_first_name fname, spriden_mi mi, decode(spbpers_confid_ind, 'Y', '***Confidential***', '') confid, sfrstcr_crn crn, ssbsect_subj_code subj, ssbsect_crse_numb crs, ssbsect_seq_numb sec, decode(ssbsect_crse_title, '', x.scbcrse_title, ssbsect_crse_title) title, sfrstcr_rsts_code rsts, stvrsts_desc rsts_desc, sfrstcr_rsts_date rsts_date, sfrstcr_gmod_code gmod, sfrstcr_credit_hr cr_hr, sfrstcr_grde_code_mid mtg, sfrstcr_grde_code grade, a.sgbstdn_levl_code levl, f_class_calc_fnc(a.sgbstdn_pidm, a.sgbstdn_levl_code, term) class, stvstyp_desc stu_type, a.sgbstdn_coll_code_1 coll, a.sgbstdn_majr_code_1 major1, a.sgbstdn_majr_code_conc_1 conc1, a.sgbstdn_majr_code_conc_1_2 conc1_2, a.sgbstdn_majr_code_minr_1 minor1, a.sgbstdn_majr_code_minr_1_2 minor1_2, a.sgbstdn_majr_code_2 major2, a.sgbstdn_majr_code_conc_2 conc2, a.sgbstdn_majr_code_conc_2_2 conc2_2, a.sgbstdn_majr_code_minr_2 minor2, a.sgbstdn_majr_code_minr_2_2 minor2_2, decode(cm.spraddr_street_line1, '', decode(ma.spraddr_street_line1, '', pr.spraddr_street_line1, ma.spraddr_street_line1), cm.spraddr_street_line1) street1, decode(cm.spraddr_street_line1, '', decode(ma.spraddr_street_line1, '', pr.spraddr_street_line2, ma.spraddr_street_line2), cm.spraddr_street_line2) street2, decode(cm.spraddr_street_line1, '', decode(ma.spraddr_street_line1, '', pr.spraddr_street_line3, ma.spraddr_street_line3), cm.spraddr_street_line3) street3, decode(cm.spraddr_street_line1, '', decode(ma.spraddr_street_line1, '', pr.spraddr_city, ma.spraddr_city), cm.spraddr_city) city, decode(cm.spraddr_street_line1, '', decode(ma.spraddr_street_line1, '', pr.spraddr_stat_code, ma.spraddr_stat_code), cm.spraddr_stat_code) state, decode(cm.spraddr_street_line1, '', decode(ma.spraddr_street_line1, '', pr.spraddr_zip, ma.spraddr_zip), cm.spraddr_zip) zip, decode(cm.spraddr_street_line1, '', decode(ma.spraddr_street_line1, '', pn.stvnatn_nation, mn.stvnatn_nation), cn.stvnatn_nation) nation, decode(ct.sprtele_phone_area, '', decode(mt.sprtele_phone_area, '', pt.sprtele_phone_area||'-'||substr(pt.sprtele_phone_number, 1, 3)||'-'||substr(pt.sprtele_phone_number, 4, 4), mt.sprtele_phone_area||'-'||substr(mt.sprtele_phone_number, 1, 3)||'-'||substr(mt.sprtele_phone_number, 4, 4)), ct.sprtele_phone_area||'-'||substr(ct.sprtele_phone_number, 1, 3)||'-'||substr(ct.sprtele_phone_number, 4, 4)) phone, goremal_email_address email from sfrstcr, stvrsts, spriden, spbpers, ssbsect, scbcrse x, sgbstdn a, stvstyp, spraddr cm, stvnatn cn, sprtele ct, spraddr ma, stvnatn mn, sprtele mt, spraddr pr, stvnatn pn, sprtele pt, goremal where sfrstcr_term_code = term and sfrstcr_rsts_code=stvrsts_code and stvrsts_incl_sect_enrl='Y' and sfrstcr_grde_code_mid is not null and sfrstcr_pidm=spriden_pidm and spriden_change_ind is null and sfrstcr_pidm=spbpers_pidm and sfrstcr_pidm=a.sgbstdn_pidm and a.sgbstdn_term_code_eff= (select max(b.sgbstdn_term_code_eff) from sgbstdn b where b.sgbstdn_term_code_eff <= sfrstcr_term_code and a.sgbstdn_pidm=b.sgbstdn_pidm) and (a.sgbstdn_majr_code_1 = stu_major or a.sgbstdn_majr_code_2 = stu_major) and a.sgbstdn_levl_code = stu_levl and a.sgbstdn_styp_code=stvstyp_code(+) and sfrstcr_crn=ssbsect_crn and sfrstcr_term_code=ssbsect_term_code and ssbsect_subj_code=x.scbcrse_subj_code and ssbsect_crse_numb=x.scbcrse_crse_numb and x.scbcrse_eff_term= (select max(y.scbcrse_eff_term) from scbcrse y where y.scbcrse_eff_term<=ssbsect_term_code and x.scbcrse_subj_code=y.scbcrse_subj_code and x.scbcrse_crse_numb=y.scbcrse_crse_numb) and sfrstcr_pidm=cm.spraddr_pidm(+) and cm.spraddr_atyp_code(+)='IM' and cm.spraddr_status_ind(+) is null and cm.spraddr_natn_code=cn.stvnatn_code(+) and cm.spraddr_pidm=ct.sprtele_pidm(+) and cm.spraddr_atyp_code=ct.sprtele_atyp_code(+) and cm.spraddr_seqno=ct.sprtele_addr_seqno(+) and ct.sprtele_tele_code(+)='IM' and ct.sprtele_status_ind(+) is null and ct.sprtele_primary_ind(+)='Y' and sfrstcr_pidm=ma.spraddr_pidm(+) and ma.spraddr_atyp_code(+)='MA' and ma.spraddr_status_ind(+) is null and ma.spraddr_natn_code=mn.stvnatn_code(+) and ma.spraddr_pidm=mt.sprtele_pidm(+) and ma.spraddr_atyp_code=mt.sprtele_atyp_code(+) and ma.spraddr_seqno=mt.sprtele_addr_seqno(+) and mt.sprtele_tele_code(+)='MA' and mt.sprtele_status_ind(+) is null and mt.sprtele_primary_ind(+)='Y' and sfrstcr_pidm=pr.spraddr_pidm(+) and pr.spraddr_atyp_code(+)='PR' and pr.spraddr_status_ind(+) is null and pr.spraddr_natn_code=pn.stvnatn_code(+) and pr.spraddr_pidm=pt.sprtele_pidm(+) and pr.spraddr_atyp_code=pt.sprtele_atyp_code(+) and pr.spraddr_seqno=pt.sprtele_addr_seqno(+) and pt.sprtele_tele_code(+)='PR' and pt.sprtele_status_ind(+) is null and pt.sprtele_primary_ind(+)='Y' and sfrstcr_pidm=goremal_pidm(+) and goremal_preferred_ind(+)='Y' and goremal_status_ind(+)='A' order by spriden_last_name, spriden_first_name, spriden_mi, subj, crs, sec; begin mytab:=' '; owa_util.mime_header('application/vnd.ms-excel'); owa_util.http_header_close; htp.print('ID'||mytab||'Last Name'||mytab||'First Name'||mytab||'MI'||mytab||'Confidential'||mytab||'CRN'||mytab||'SUBJ'||mytab|| 'CRS'||mytab||'SEC'||mytab||'TITLE'||mytab||'STATUS'||mytab||'STATUS DATE'||mytab||'GRADE MODE'||mytab||'CR HOUR'||mytab|| 'MTG'||mytab||'FINAL GRADE'||mytab||'STU LEVEL'||mytab||'CLASS'||mytab||'STU TYPE'||mytab||'COLL'||mytab||'MAJOR 1'||mytab|| 'CONC 1'||mytab||'CONC 1_2'||mytab||'MINOR 1'||mytab||'MINOR 1_2'||mytab||'MAJOR 2'||mytab||'CONC 2'||mytab||'CONC 2_2'||mytab|| 'MINOR 2'||mytab||'MINOR 2_2'||mytab||'Street 1'||mytab||'Street 2'||mytab||'Street 3'||mytab||'City'||mytab||'State'||mytab|| 'Zip'||mytab||'Nation'||mytab||'Phone'||mytab||'Email'); FOR mystuff in mtginfo LOOP htp.print(''''||mystuff.id||mytab||mystuff.lname||mytab||mystuff.fname||mytab||mystuff.mi||mytab||mystuff.confid||mytab|| mystuff.crn||mytab||mystuff.subj||mytab||mystuff.crs||mytab||mystuff.sec||mytab||mystuff.title||mytab||mystuff.rsts||mytab|| mystuff.rsts_date||mytab||mystuff.gmod||mytab||mystuff.cr_hr||mytab||mystuff.mtg||mytab||mystuff.grade||mytab|| mystuff.levl||mytab||mystuff.class||mytab||mystuff.stu_type||mytab||mystuff.coll||mytab||mystuff.major1||mytab||mystuff.conc1||mytab|| mystuff.conc1_2||mytab||mystuff.minor1||mytab||mystuff.minor1_2||mytab||mystuff.major2||mytab||mystuff.conc2||mytab|| mystuff.conc2_2||mytab||mystuff.minor2||mytab||mystuff.minor2_2||mytab||mystuff.street1||mytab||mystuff.street2||mytab|| mystuff.street3||mytab||mystuff.city||mytab||mystuff.state||mytab||mystuff.zip||mytab||mystuff.nation||mytab|| mystuff.phone||mytab||mystuff.email); END LOOP; end LMUDownloadMajorMTG; procedure LMUDownloadAdviseeMTG (mypidm in spriden.spriden_pidm%type default null, term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL) is mytab varchar2(1); cursor mtginfo is select sfrstcr_pidm pidm, spriden_id id, spriden_last_name lname, spriden_first_name fname, spriden_mi mi, decode(spbpers_confid_ind, 'Y', '***Confidential***', '') confid, sfrstcr_crn crn, ssbsect_subj_code subj, ssbsect_crse_numb crs, ssbsect_seq_numb sec, decode(ssbsect_crse_title, '', x.scbcrse_title, ssbsect_crse_title) title, sfrstcr_rsts_code rsts, stvrsts_desc rsts_desc, sfrstcr_rsts_date rsts_date, sfrstcr_gmod_code gmod, sfrstcr_credit_hr cr_hr, sfrstcr_grde_code_mid mtg, sfrstcr_grde_code grade, a.sgbstdn_levl_code levl, f_class_calc_fnc(a.sgbstdn_pidm, a.sgbstdn_levl_code, term) class, stvstyp_desc stu_type, a.sgbstdn_coll_code_1 coll, a.sgbstdn_majr_code_1 major1, a.sgbstdn_majr_code_conc_1 conc1, a.sgbstdn_majr_code_conc_1_2 conc1_2, a.sgbstdn_majr_code_minr_1 minor1, a.sgbstdn_majr_code_minr_1_2 minor1_2, a.sgbstdn_majr_code_2 major2, a.sgbstdn_majr_code_conc_2 conc2, a.sgbstdn_majr_code_conc_2_2 conc2_2, a.sgbstdn_majr_code_minr_2 minor2, a.sgbstdn_majr_code_minr_2_2 minor2_2, decode(cm.spraddr_street_line1, '', decode(ma.spraddr_street_line1, '', pr.spraddr_street_line1, ma.spraddr_street_line1), cm.spraddr_street_line1) street1, decode(cm.spraddr_street_line1, '', decode(ma.spraddr_street_line1, '', pr.spraddr_street_line2, ma.spraddr_street_line2), cm.spraddr_street_line2) street2, decode(cm.spraddr_street_line1, '', decode(ma.spraddr_street_line1, '', pr.spraddr_street_line3, ma.spraddr_street_line3), cm.spraddr_street_line3) street3, decode(cm.spraddr_street_line1, '', decode(ma.spraddr_street_line1, '', pr.spraddr_city, ma.spraddr_city), cm.spraddr_city) city, decode(cm.spraddr_street_line1, '', decode(ma.spraddr_street_line1, '', pr.spraddr_stat_code, ma.spraddr_stat_code), cm.spraddr_stat_code) state, decode(cm.spraddr_street_line1, '', decode(ma.spraddr_street_line1, '', pr.spraddr_zip, ma.spraddr_zip), cm.spraddr_zip) zip, decode(cm.spraddr_street_line1, '', decode(ma.spraddr_street_line1, '', pn.stvnatn_nation, mn.stvnatn_nation), cn.stvnatn_nation) nation, decode(ct.sprtele_phone_area, '', decode(mt.sprtele_phone_area, '', pt.sprtele_phone_area||'-'||substr(pt.sprtele_phone_number, 1, 3)||'-'||substr(pt.sprtele_phone_number, 4, 4), mt.sprtele_phone_area||'-'||substr(mt.sprtele_phone_number, 1, 3)||'-'||substr(mt.sprtele_phone_number, 4, 4)), ct.sprtele_phone_area||'-'||substr(ct.sprtele_phone_number, 1, 3)||'-'||substr(ct.sprtele_phone_number, 4, 4)) phone, goremal_email_address email from sfrstcr, stvrsts, spriden, spbpers, ssbsect, scbcrse x, sgbstdn a, stvstyp, spraddr cm, stvnatn cn, sprtele ct, spraddr ma, stvnatn mn, sprtele mt, spraddr pr, stvnatn pn, sprtele pt, goremal where sfrstcr_term_code = term and sfrstcr_rsts_code=stvrsts_code and stvrsts_incl_sect_enrl='Y' and sfrstcr_grde_code_mid is not null and sfrstcr_pidm=spriden_pidm and spriden_change_ind is null and sfrstcr_pidm=spbpers_pidm and sfrstcr_pidm=a.sgbstdn_pidm and a.sgbstdn_term_code_eff= (select max(b.sgbstdn_term_code_eff) from sgbstdn b where b.sgbstdn_term_code_eff <= sfrstcr_term_code and a.sgbstdn_pidm=b.sgbstdn_pidm) and a.sgbstdn_styp_code=stvstyp_code(+) and sfrstcr_crn=ssbsect_crn and sfrstcr_term_code=ssbsect_term_code and ssbsect_subj_code=x.scbcrse_subj_code and ssbsect_crse_numb=x.scbcrse_crse_numb and x.scbcrse_eff_term= (select max(y.scbcrse_eff_term) from scbcrse y where y.scbcrse_eff_term<=ssbsect_term_code and x.scbcrse_subj_code=y.scbcrse_subj_code and x.scbcrse_crse_numb=y.scbcrse_crse_numb) and sfrstcr_pidm=cm.spraddr_pidm(+) and cm.spraddr_atyp_code(+)='IM' and cm.spraddr_status_ind(+) is null and cm.spraddr_natn_code=cn.stvnatn_code(+) and cm.spraddr_pidm=ct.sprtele_pidm(+) and cm.spraddr_atyp_code=ct.sprtele_atyp_code(+) and cm.spraddr_seqno=ct.sprtele_addr_seqno(+) and ct.sprtele_tele_code(+)='IM' and ct.sprtele_status_ind(+) is null and ct.sprtele_primary_ind(+)='Y' and sfrstcr_pidm=ma.spraddr_pidm(+) and ma.spraddr_atyp_code(+)='MA' and ma.spraddr_status_ind(+) is null and ma.spraddr_natn_code=mn.stvnatn_code(+) and ma.spraddr_pidm=mt.sprtele_pidm(+) and ma.spraddr_atyp_code=mt.sprtele_atyp_code(+) and ma.spraddr_seqno=mt.sprtele_addr_seqno(+) and mt.sprtele_tele_code(+)='MA' and mt.sprtele_status_ind(+) is null and mt.sprtele_primary_ind(+)='Y' and sfrstcr_pidm=pr.spraddr_pidm(+) and pr.spraddr_atyp_code(+)='PR' and pr.spraddr_status_ind(+) is null and pr.spraddr_natn_code=pn.stvnatn_code(+) and pr.spraddr_pidm=pt.sprtele_pidm(+) and pr.spraddr_atyp_code=pt.sprtele_atyp_code(+) and pr.spraddr_seqno=pt.sprtele_addr_seqno(+) and pt.sprtele_tele_code(+)='PR' and pt.sprtele_status_ind(+) is null and pt.sprtele_primary_ind(+)='Y' and sfrstcr_pidm=goremal_pidm(+) and goremal_preferred_ind(+)='Y' and goremal_status_ind(+)='A' and exists (select 'X' from sgradvr x where x.sgradvr_pidm = sfrstcr_pidm and x.sgradvr_term_code_eff= (select max(y.sgradvr_term_code_eff) from sgradvr y where y.sgradvr_term_code_eff <= term and x.sgradvr_pidm = y.sgradvr_pidm) and x.sgradvr_advr_pidm = mypidm) order by spriden_last_name, spriden_first_name, spriden_mi, subj, crs, sec; begin mytab:=' '; owa_util.mime_header('application/vnd.ms-excel'); owa_util.http_header_close; htp.print('ID'||mytab||'Last Name'||mytab||'First Name'||mytab||'MI'||mytab||'Confidential'||mytab||'CRN'||mytab||'SUBJ'||mytab|| 'CRS'||mytab||'SEC'||mytab||'TITLE'||mytab||'STATUS'||mytab||'STATUS DATE'||mytab||'GRADE MODE'||mytab||'CR HOUR'||mytab|| 'MTG'||mytab||'FINAL GRADE'||mytab||'STU LEVEL'||mytab||'CLASS'||mytab||'STU TYPE'||mytab||'COLL'||mytab||'MAJOR 1'||mytab|| 'CONC 1'||mytab||'CONC 1_2'||mytab||'MINOR 1'||mytab||'MINOR 1_2'||mytab||'MAJOR 2'||mytab||'CONC 2'||mytab||'CONC 2_2'||mytab|| 'MINOR 2'||mytab||'MINOR 2_2'||mytab||'Street 1'||mytab||'Street 2'||mytab||'Street 3'||mytab||'City'||mytab||'State'||mytab|| 'Zip'||mytab||'Nation'||mytab||'Phone'||mytab||'Email'); FOR mystuff in mtginfo LOOP htp.print(''''||mystuff.id||mytab||mystuff.lname||mytab||mystuff.fname||mytab||mystuff.mi||mytab||mystuff.confid||mytab|| mystuff.crn||mytab||mystuff.subj||mytab||mystuff.crs||mytab||mystuff.sec||mytab||mystuff.title||mytab||mystuff.rsts||mytab|| mystuff.rsts_date||mytab||mystuff.gmod||mytab||mystuff.cr_hr||mytab||mystuff.mtg||mytab||mystuff.grade||mytab|| mystuff.levl||mytab||mystuff.class||mytab||mystuff.stu_type||mytab||mystuff.coll||mytab||mystuff.major1||mytab||mystuff.conc1||mytab|| mystuff.conc1_2||mytab||mystuff.minor1||mytab||mystuff.minor1_2||mytab||mystuff.major2||mytab||mystuff.conc2||mytab|| mystuff.conc2_2||mytab||mystuff.minor2||mytab||mystuff.minor2_2||mytab||mystuff.street1||mytab||mystuff.street2||mytab|| mystuff.street3||mytab||mystuff.city||mytab||mystuff.state||mytab||mystuff.zip||mytab||mystuff.nation||mytab|| mystuff.phone||mytab||mystuff.email); END LOOP; end LMUDownloadAdviseeMTG; end lmuhwskmtgd; / show errors whenever sqlerror continue; drop public synonym lmuhwskmtgd; whenever sqlerror exit rollback; create public synonym lmuhwskmtgd for lmuhwskmtgd; grant execute on lmuhwskmtgd to public; set scan on