set scan off set echo on -- Created for Loyola Marymount University -- By Robert M. Nitsos -- 19-FEB-2003 -- -- This package creates a Banner Web page that displays advisee information in the -- Faculty Module. -- -- Modified 24-FEB-2003 -- By Robert M. Nitsos -- Updated package to use FacWeb 5.3 UI and procedures. -- create or replace package lmuhwskadvl is procedure LMUAdvisees (term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, error_mess in varchar2 default null); procedure LMUDownloadAdvisees (mypidm in sgradvr.sgradvr_advr_pidm%type default null, term in stvterm.stvterm_code%type default null); END lmuhwskadvl; / show errors CREATE OR REPLACE PACKAGE BODY lmuhwskadvl AS /* make sure registered then continue to process */ /* Global type and variable declarations for package */ pidm spriden.spriden_pidm%TYPE; /****************************** LMU Show Advisee List ***********************/ procedure LMUAdvisees (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; confid_msg varchar2(30) default null; msg varchar2(255); term_rec stvterm%rowtype; rcount number; /* Get information that is to be displayed */ cursor adviseeinfo is select spriden_id id, decode(spbpers_confid_ind, '', '', 'Y') confid, spriden_last_name||', '||spriden_first_name||' '||spriden_mi name, f_class_calc_fnc(a.sgbstdn_pidm, a.sgbstdn_levl_code, hold_term) class, 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, stvadvr_desc atyp, x.sgradvr_prim_ind prim, 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_line2, '', 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(cm.spraddr_street_line1, '', decode(ma.spraddr_street_line1, '', 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 sgradvr x, stvadvr, spriden, spbpers, sgbstdn a, spraddr cm, stvnatn cn, sprtele ct, spraddr ma, stvnatn mn, sprtele mt, spraddr pr, stvnatn pn, sprtele pt, goremal where 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 = pidm and x.sgradvr_advr_code=stvadvr_code(+) and x.sgradvr_pidm=spriden_pidm and spriden_change_ind is null and x.sgradvr_pidm=spbpers_pidm and x.sgradvr_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 <= hold_term and a.sgbstdn_pidm=b.sgbstdn_pidm) and x.sgradvr_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 x.sgradvr_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 x.sgradvr_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 x.sgradvr_pidm=goremal_pidm(+) and goremal_preferred_ind(+)='Y' and goremal_status_ind(+)='A' and exists (select 'X' from sfrstcr, stvrsts where sfrstcr_pidm=x.sgradvr_pidm and sfrstcr_term_code = hold_term and sfrstcr_rsts_code=stvrsts_code and stvrsts_incl_sect_enrl='Y') order by name; 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=> 'lmuhwskadvl.LMUAdvisees'); 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('lmuhwskadvl.LMUAdvisees',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; /* Check to see if there are any records to display */ BEGIN select count(*) into rcount from sgradvr a where a.sgradvr_term_code_eff= (select max(b.sgradvr_term_code_eff) from sgradvr b where b.sgradvr_term_code_eff <= hold_term and a.sgradvr_pidm=b.sgradvr_pidm) and a.sgradvr_advr_pidm = pidm; EXCEPTION WHEN OTHERS THEN rcount := 0; END; /* Display message if no records found */ IF rcount = 0 THEN twbkwbis.P_OpenDoc('lmuhwskadvl.LMUAdvisees',header_text=> '*** No Advisee Information Exists ***'); /* Be sure to enter appropriate info text in Web Tailor for NO_ADVISEES */ twbkwbis.P_DispInfo('lmuhwskadvl.LMUAdvisees','NO_ADVISEES'); twbkwbis.P_CloseDoc(curr_release); return; END IF; /* Show Advisee List */ twbkwbis.P_OpenDoc('lmuhwskadvl.LMUAdvisees',header_text=> 'For: '|| f_format_name(pidm, 'FMIL')); /* You can enter info text for GENERAL if you want to display any additional information */ twbkwbis.P_DispInfo('lmuhwskadvl.LMUAdvisees','GENERAL'); /* Create Link to Download Data */ htp.nl; htp.anchor(twbklibs.twgbwrul_rec.twgbwrul_cgibin_dir || '/lmuhwskadvl.LMUDownloadAdvisees'|| '?mypidm='||pidm||'&term='||hold_term, 'DOWNLOAD ADVISEE LIST'); 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('Major 1',calign=>'center'); twbkfrmt.P_TableDataLabel('Conc 1',calign=>'center'); twbkfrmt.P_TableDataLabel('Conc 1_2',calign=>'center'); twbkfrmt.P_TableDataLabel('Minor 1',calign=>'center'); twbkfrmt.P_TableDataLabel('Minor 1_2',calign=>'center'); twbkfrmt.P_TableDataLabel('Major 2',calign=>'center'); twbkfrmt.P_TableDataLabel('Conc 2',calign=>'center'); twbkfrmt.P_TableDataLabel('Conc 2_2',calign=>'center'); twbkfrmt.P_TableDataLabel('Minor 2',calign=>'center'); twbkfrmt.P_TableDataLabel('Minor 2_2',calign=>'center'); twbkfrmt.P_TableDataLabel('Type',calign=>'left'); twbkfrmt.P_TableDataLabel('Prim',calign=>'center'); twbkfrmt.P_TableRowClose; FOR mystuff IN adviseeinfo LOOP twbkfrmt.P_TableRowOpen('left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.id),calign=>'center'); /* twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.name),calign=>'left'); */ htp.print(''||mystuff.name||''); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.class),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.major1),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.conc1),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.conc1_2),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.minor1),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.minor1_2),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.major2),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.conc2),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.conc2_2),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.minor2),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.minor2_2),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.atyp),calign=>'left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.prim),calign=>'center'); twbkfrmt.P_TableRowClose; END LOOP; twbkfrmt.P_TableClose; twbkwbis.P_CloseDoc; end LMUAdvisees; procedure LMUDownloadAdvisees (mypidm in sgradvr.sgradvr_advr_pidm%type default null, term in stvterm.stvterm_code%type default null) is mytab varchar2(1); cursor adviseeinfo is select spriden_id id, decode(spbpers_confid_ind, '', '', 'Y') confid, spriden_last_name||', '||spriden_first_name||' '||spriden_mi name, f_class_calc_fnc(a.sgbstdn_pidm, a.sgbstdn_levl_code, term) class, 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, stvadvr_desc atyp, x.sgradvr_prim_ind prim, 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_line2, '', 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(cm.spraddr_street_line1, '', decode(ma.spraddr_street_line1, '', 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 sgradvr x, stvadvr, spriden, spbpers, sgbstdn a, spraddr cm, stvnatn cn, sprtele ct, spraddr ma, stvnatn mn, sprtele mt, spraddr pr, stvnatn pn, sprtele pt, goremal where 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 and x.sgradvr_advr_code=stvadvr_code(+) and x.sgradvr_pidm=spriden_pidm and spriden_change_ind is null and x.sgradvr_pidm=spbpers_pidm and x.sgradvr_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 <= term and a.sgbstdn_pidm=b.sgbstdn_pidm) and x.sgradvr_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 x.sgradvr_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 x.sgradvr_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 x.sgradvr_pidm=goremal_pidm(+) and goremal_preferred_ind(+)='Y' and goremal_status_ind(+)='A' and exists (select 'X' from sfrstcr, stvrsts where sfrstcr_pidm=x.sgradvr_pidm and sfrstcr_term_code = term and sfrstcr_rsts_code=stvrsts_code and stvrsts_incl_sect_enrl='Y') order by name; begin mytab:=' '; owa_util.mime_header('application/vnd.ms-excel'); owa_util.http_header_close; htp.print('ID'||mytab||'Name'||mytab||'Class'||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||'Type'||mytab||'Prim'||mytab||'Street 1'||mytab|| 'Street 2'||mytab||'Street 3'||mytab||'City'||mytab||'State'||mytab||'Zip'||mytab||'Nation'||mytab||'Phone'||mytab||'Email'); FOR mystuff in adviseeinfo LOOP htp.print(''''||mystuff.id||mytab||mystuff.name||mytab||mystuff.class||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.atyp||mytab||mystuff.prim||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 LMUDownloadAdvisees; end lmuhwskadvl; / show errors whenever sqlerror continue; drop public synonym lmuhwskadvl; whenever sqlerror exit rollback; create public synonym lmuhwskadvl for lmuhwskadvl; grant execute on lmuhwskadvl to public; set scan on