set scan off set echo on -- Created for Loyola Marymount University -- By Robert M. Nitsos -- 27-JAN-2003 -- -- This package creates a Banner Web page that displays information about -- athletes, including major and advisor info, in the Athletics module -- (accessed from the Faculty Services page). The data can also be downloaded -- into a comma-separated file. -- create or replace package lmuhwskainf is procedure LMUAthleteInfo (term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, team in sgrsprt.sgrsprt_actc_code%type default null, error_mess in varchar2 default null); procedure LMUDLAthleteInfo (term in stvterm.stvterm_code%type default null, team in sgrsprt.sgrsprt_actc_code%type default null); procedure LMUAthleteMTG (term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, team in sgrsprt.sgrsprt_actc_code%type default null, error_mess in varchar2 default null); procedure LMUDLAthleteMTG (term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, team in sgrsprt.sgrsprt_actc_code%type default null); procedure LMUTeamRosters (term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, team in sgrsprt.sgrsprt_actc_code%type default null, disp_ethn in varchar2 default 'N', error_mess in varchar2 default null); procedure LMUDLTeamRosters (term in stvterm.stvterm_code%type default null, team in sgrsprt.sgrsprt_actc_code%type default null, disp_ethn in varchar2 default 'N'); procedure LMUAthleteEnrlHrs (term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, team in sgrsprt.sgrsprt_actc_code%type default null, hrs in number default null, error_mess in varchar2 default null); procedure LMUDLAthleteEnrlHrs (term in stvterm.stvterm_code%type default null, team in sgrsprt.sgrsprt_actc_code%type default null, hrs in number default null); procedure LMUAthletesNotEnrolled (term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, team in sgrsprt.sgrsprt_actc_code%type default null, regterm in stvterm.stvterm_code%type default null, error_mess in varchar2 default null); procedure LMUDLAthletesNotEnrolled (term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, team in sgrsprt.sgrsprt_actc_code%type default null, regterm in stvterm.stvterm_code%type default null); procedure LMUAthleteDrops (term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, team in sgrsprt.sgrsprt_actc_code%type default null, error_mess in varchar2 default null); procedure LMUDLAthleteDrops (term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, team in sgrsprt.sgrsprt_actc_code%type default null); END lmuhwskainf; / show errors CREATE OR REPLACE PACKAGE BODY lmuhwskainf AS /* make sure registered then continue to process */ /* Global type and variable declarations for package */ pidm spriden.spriden_pidm%TYPE; row_count NUMBER; /****************************** LMU Show Athlete Information ***********************/ procedure LMUAthleteInfo (term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, team in sgrsprt.sgrsprt_actc_code%type default null, error_mess in varchar2 default null) is curr_release varchar2(10) := '5.5'; hold_term stvterm.stvterm_code%TYPE; hold_team sgrsprt.sgrsprt_actc_code%TYPE; msg varchar2(255); term_desc stvterm.stvterm_desc%type default null; rcount number; /* Get Team Codes for Drop-Down List */ cursor lmuteams is select distinct sgrsprt_actc_code team_code, stvactc_desc team_desc from sgrsprt, stvactc where sgrsprt_term_code = hold_term and sgrsprt_actc_code = stvactc_code(+); /* Get information that is to be displayed */ cursor teaminfo is select stvactc_desc team, s.spriden_id id, s.spriden_last_name||', '||s.spriden_first_name||' '||s.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, ltrim(rtrim(adv.spriden_first_name||' '||adv.spriden_last_name)) advisor from sgrsprt, stvactc, spriden s, sgbstdn a, sgradvr x, spriden adv where sgrsprt_term_code = hold_term and sgrsprt_actc_code like hold_team and sgrsprt_actc_code = stvactc_code(+) and sgrsprt_pidm = s.spriden_pidm and s.spriden_change_ind is null and sgrsprt_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 sgrsprt_pidm = x.sgradvr_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_prim_ind = 'Y' and x.sgradvr_advr_pidm = adv.spriden_pidm(+) and adv.spriden_change_ind(+) is null order by team, name; trec teaminfo%rowtype; 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=>'lmuhwskainf.LMUAthleteInfo'); RETURN; END IF; /* Check to see if all teams are to be returned */ if team = 'ALL' then hold_team := '%'; else hold_team := team; end if; if hold_team is null then twbkwbis.P_OpenDoc('lmuhwskainf.LMUAthleteInfo'); twbkwbis.P_DispInfo('lmuhwskainf.LMUAthleteInfo','GENERAL'); else open teaminfo; fetch teaminfo into trec; if teaminfo%notfound then /* No Data to Display */ twbkwbis.P_OpenDoc('lmuhwskainf.LMUAthleteInfo', header_text=>'*** No Data Exists For Selected Term/Team***'); /* Be sure to enter appropriate info text in Web Tailor for NO_DATA */ twbkwbis.P_DispInfo('lmuhwskainf.LMUAthleteInfo','NO_DATA'); else /* Get Term Desc */ begin select stvterm_desc into term_desc from stvterm where stvterm_code = hold_term; exception when others then term_desc := 'Unknown'; end; twbkwbis.P_OpenDoc('lmuhwskainf.LMUAthleteInfo'); /* Enter info text for GENERAL if you want to display additional information */ twbkwbis.P_DispInfo('lmuhwskainf.LMUAthleteInfo','GENERAL'); /* Display Button for User to Download Data */ HTP.formOpen(twbklibs.twgbwrul_rec.twgbwrul_cgibin_dir||'/'||'lmuhwskainf.LMUDLAthleteInfo'); HTP.formHidden('term',hold_term); HTP.formHidden('team',team); HTP.formsubmit(NULL,'DOWNLOAD TEAM INFORMATION'); HTP.formClose; /* Open Table to Display Data */ twbkfrmt.P_TableOpen('DATADISPLAY'); /* Create Table Headers */ twbkfrmt.P_TableRowOpen('left'); twbkfrmt.P_TableDataLabel('Team',calign=>'left'); twbkfrmt.P_TableDataLabel('ID',calign=>'left'); twbkfrmt.P_TableDataLabel('Name',calign=>'left'); twbkfrmt.P_TableDataLabel('Class',calign=>'center'); twbkfrmt.P_TableDataLabel('COLL',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('Advisor',calign=>'left'); twbkfrmt.P_TableRowClose; rcount := 0; LOOP if rcount > 0 then fetch teaminfo into trec; end if; EXIT when teaminfo%notfound; twbkfrmt.P_TableRowOpen('left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(trec.team),calign=>'left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(trec.id),calign=>'left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(trec.name),calign=>'left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(trec.class),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(trec.coll),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(trec.major1),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(trec.conc1),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(trec.conc1_2),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(trec.minor1),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(trec.minor1_2),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(trec.advisor),calign=>'left'); twbkfrmt.P_TableRowClose; rcount := rcount + 1; END LOOP; twbkfrmt.P_TableClose; /* Display Number of Records Returned */ htp.nl; twbkfrmt.P_PrintBold('Total Records Returned: '||to_char(rcount)); end if; close teaminfo; end if; /* Display Drop-Down Criteria Boxes */ htp.nl; HTP.formOpen(twbklibs.twgbwrul_rec.twgbwrul_cgibin_dir|| '/'||'lmuhwskainf.LMUAthleteInfo'); HTP.formHidden('term',hold_term); row_count := 0; FOR team_rec in lmuteams LOOP IF lmuteams%rowcount = 1 THEN twbkfrmt.p_tableopen( 'DATAENTRY'); twbkfrmt.p_tablerowopen; twbkfrmt.p_tabledatalabel( 'Select Team:'); twbkfrmt.p_tabledataopen; HTP.formSelectOpen('team',NULL,1); HTP.P(twbkwbis.F_formSelectOption('All Teams', 'ALL')); END IF; IF team IS NOT NULL AND team_rec.team_code = team THEN HTP.P(twbkwbis.F_formSelectOption(team_rec.team_desc, team_rec.team_code,'SELECTED')); ELSE HTP.P(twbkwbis.F_formSelectOption(team_rec.team_desc, team_rec.team_code)); END IF; row_count := lmuteams%rowcount; END LOOP; IF row_count = 0 THEN twbkfrmt.p_printtext('No Teams Available'); ELSE HTP.formSelectClose; twbkfrmt.p_tablerowclose; twbkfrmt.p_tableclose; HTP.nl; HTP.formsubmit(NULL,'Submit Team'); END IF; HTP.formClose; htp.nl; twbkwbis.P_CloseDoc(curr_release); end LMUAthleteInfo; /*************************** LMU Download Athlete Information ***********************/ procedure LMUDLAthleteInfo (term in stvterm.stvterm_code%type default null, team in sgrsprt.sgrsprt_actc_code%type default null) is mytab varchar2(1); hold_team sgrsprt.sgrsprt_actc_code%type; cursor dlteaminfo is select stvactc_desc team, s.spriden_id id, s.spriden_last_name||', '||s.spriden_first_name||' '||s.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, ltrim(rtrim(adv.spriden_first_name||' '||adv.spriden_last_name)) advisor from sgrsprt, stvactc, spriden s, sgbstdn a, sgradvr x, spriden adv where sgrsprt_term_code = term and sgrsprt_actc_code like hold_team and sgrsprt_actc_code = stvactc_code(+) and sgrsprt_pidm = s.spriden_pidm and s.spriden_change_ind is null and sgrsprt_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 sgrsprt_pidm = x.sgradvr_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_prim_ind = 'Y' and x.sgradvr_advr_pidm = adv.spriden_pidm(+) and adv.spriden_change_ind(+) is null order by team, name; begin mytab:=' '; /* mytab:=','; */ owa_util.mime_header('application/vnd.ms-excel', TRUE); if team = 'ALL' then hold_team := '%'; else hold_team := team; end if; htp.print('TEAM'||mytab||'ID'||mytab||'NAME'||mytab||'CLASS'||mytab||'COLL'||mytab||'MAJOR1'||mytab||'CONC1'||mytab||'CONC1_2'||mytab||'MINOR1'||mytab||'MINOR1_2'||mytab||'ADVISOR'); FOR mystuff in dlteaminfo LOOP htp.print(mystuff.team||mytab||''''||mystuff.id||mytab||mystuff.name||mytab||mystuff.class||mytab||mystuff.coll||mytab||mystuff.major1||mytab|| mystuff.conc1||mytab||mystuff.conc1_2||mytab||mystuff.minor1||mytab||mystuff.minor1_2||mytab||mystuff.advisor); END LOOP; end LMUDLAthleteInfo; /****************************** LMU Show MTG for Athletes ***********************/ procedure LMUAthleteMTG (term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, team in sgrsprt.sgrsprt_actc_code%type default null, error_mess in varchar2 default null) is curr_release varchar2(10) := '5.5'; hold_term stvterm.stvterm_code%TYPE; term_desc stvterm.stvterm_desc%type default null; hold_team sgrsprt.sgrsprt_actc_code%TYPE; team_desc stvactc.stvactc_desc%type default null; msg varchar2(255); rcount number; /* Get Team Codes for Drop-Down List */ cursor lmuteams is select distinct sgrsprt_actc_code team_code, stvactc_desc team_desc from sgrsprt, stvactc where sgrsprt_term_code = hold_term and sgrsprt_actc_code = stvactc_code(+); /* Get information that is to be displayed */ cursor mtginfo is select sfrstcr_pidm pidm, s.spriden_id id, s.spriden_last_name lname, s.spriden_first_name fname, s.spriden_mi mi, decode(spbpers_confid_ind, '', '', 'Y') confid, stvactc_desc sport, 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, i.spriden_last_name||', '||i.spriden_first_name||' '||i.spriden_mi inst, 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 s, spbpers, ssbsect, scbcrse x, sgbstdn a, stvstyp, sgrsprt, stvactc, sirasgn, spriden i where sfrstcr_term_code = hold_term and sfrstcr_rsts_code=stvrsts_code and stvrsts_incl_sect_enrl='Y' and sfrstcr_pidm=sgrsprt_pidm and sgrsprt_term_code=sfrstcr_term_code and sgrsprt_actc_code like hold_team and sgrsprt_actc_code=stvactc_code(+) and sfrstcr_pidm=s.spriden_pidm and s.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_crn=sirasgn_crn(+) and sfrstcr_term_code=sirasgn_term_code(+) and sirasgn_primary_ind(+)='Y' and sirasgn_pidm=i.spriden_pidm(+) and i.spriden_change_ind(+) is null and sfrstcr_grde_code_mid is not null order by sport, lname, fname, 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=>'lmuhwskainf.LMUAthleteMTG'); RETURN; END IF; /* Check to see if all teams are to be returned */ if team = 'ALL' then hold_team := '%'; team_desc := 'All Teams'; else hold_team := team; begin select stvactc_desc into team_desc from stvactc where stvactc_code = hold_team; exception when others then team_desc := 'Unknown'; end; end if; /* Get Term Description */ begin select stvterm_desc into term_desc from stvterm where stvterm_code = hold_term; exception when others then term_desc := 'Unknown'; end; begin select count(*) into rcount from sfrstcr, stvrsts, sgrsprt where sfrstcr_term_code = hold_term and sfrstcr_rsts_code=stvrsts_code and stvrsts_incl_sect_enrl='Y' and sfrstcr_pidm=sgrsprt_pidm and sfrstcr_term_code=sgrsprt_term_code and sgrsprt_actc_code like hold_team and sfrstcr_grde_code_mid is not null; exception when others then rcount := 0; end; if rcount = 0 then if team is not null then /* No Mid-Term Grades */ twbkwbis.P_OpenDoc('lmuhwskainf.LMUAthleteMTG',header_text=> '*** No Mid-Term Grades for Selected Team Exist ***'); /* Be sure to enter appropriate info text in Web Tailor for NO_DATA */ twbkwbis.P_DispInfo('lmuhwskainf.LMUAthleteMTG','NO_DATA'); else twbkwbis.P_OpenDoc('lmuhwskainf.LMUAthleteMTG'); /* You can enter info text for GENERAL if you want to display any additional information */ twbkwbis.P_DispInfo('lmuhwskainf.LMUAthleteMTG','GENERAL'); end if; else /* Display Data */ twbkwbis.P_OpenDoc('lmuhwskainf.LMUAthleteMTG',header_text=> 'For: '||team_desc||' ('||term_desc||')'); /* You can enter info text for GENERAL if you want to display any additional information */ twbkwbis.P_DispInfo('lmuhwskainf.LMUAthleteMTG','GENERAL'); htp.anchor(twbklibs.twgbwrul_rec.twgbwrul_cgibin_dir || '/lmuhwskainf.LMUDLAthleteMTG'|| '?term=' || hold_term|| '&team=' || team, 'DOWNLOAD MID-TERM GRADE INFORMATION'); htp.nl; twbkfrmt.P_TableOpen('DATADISPLAY'); twbkfrmt.P_TableRowOpen('left'); twbkfrmt.P_TableDataLabel('Team',calign=>'left'); twbkfrmt.P_TableDataLabel('ID',calign=>'left'); twbkfrmt.P_TableDataLabel('Last Name',calign=>'left'); twbkfrmt.P_TableDataLabel('First Name',calign=>'left'); twbkfrmt.P_TableDataLabel('MI',calign=>'left'); twbkfrmt.P_TableDataLabel('Confid',calign=>'left'); twbkfrmt.P_TableDataLabel('Level',calign=>'center'); twbkfrmt.P_TableDataLabel('Class',calign=>'center'); twbkfrmt.P_TableDataLabel('College',calign=>'center'); twbkfrmt.P_TableDataLabel('Major 1',calign=>'center'); twbkfrmt.P_TableDataLabel('Major 2',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('MTG',calign=>'center'); twbkfrmt.P_TableDataLabel('Credits',calign=>'center'); twbkfrmt.P_TableDataLabel('Instructor',calign=>'Left'); twbkfrmt.P_TableRowClose; FOR mystuff IN mtginfo LOOP twbkfrmt.P_TableRowOpen('left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.sport),calign=>'left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.id),calign=>'left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.lname),calign=>'left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.fname),calign=>'left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.mi),calign=>'left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.confid),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.levl),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.class),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.coll),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.major1),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.major2),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.crn),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.subj),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.crs),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.sec),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.title),calign=>'left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.mtg),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.cr_hr),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.inst),calign=>'left'); twbkfrmt.P_TableRowClose; END LOOP; twbkfrmt.P_TableClose; end if; htp.nl; HTP.formOpen(twbklibs.twgbwrul_rec.twgbwrul_cgibin_dir|| '/'||'lmuhwskainf.LMUAthleteMTG'); HTP.formHidden('term',hold_term); row_count := 0; FOR team_rec in lmuteams LOOP IF lmuteams%rowcount = 1 THEN twbkfrmt.p_tableopen( 'DATAENTRY'); twbkfrmt.p_tablerowopen; twbkfrmt.p_tabledatalabel( 'Select Team:'); twbkfrmt.p_tabledataopen; HTP.formSelectOpen('team',NULL,1); HTP.P(twbkwbis.F_formSelectOption('All Teams', 'ALL')); END IF; IF team IS NOT NULL AND team_rec.team_code = team THEN HTP.P(twbkwbis.F_formSelectOption(team_rec.team_desc, team_rec.team_code,'SELECTED')); ELSE HTP.P(twbkwbis.F_formSelectOption(team_rec.team_desc, team_rec.team_code)); END IF; row_count := lmuteams%rowcount; END LOOP; IF row_count = 0 THEN twbkfrmt.p_printtext('No Teams Available'); ELSE HTP.formSelectClose; twbkfrmt.p_tabledataopen; twbkfrmt.p_tablerowclose; twbkfrmt.p_tableclose; HTP.nl; HTP.formsubmit(NULL,'Submit Team'); END IF; HTP.formClose; htp.nl; twbkwbis.P_CloseDoc(curr_release); end LMUAthleteMTG; /************************** LMU Download MTG for Athletes ***********************/ procedure LMUDLAthleteMTG (term in stvterm.stvterm_code%type default null, team in sgrsprt.sgrsprt_actc_code%type default null) is mytab varchar2(1); hold_team sgrsprt.sgrsprt_actc_code%type; cursor dlmtginfo is select sfrstcr_pidm pidm, s.spriden_id id, s.spriden_last_name lname, s.spriden_first_name fname, s.spriden_mi mi, decode(spbpers_confid_ind, '', '', 'Y') confid, stvactc_desc sport, 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, i.spriden_last_name||', '||i.spriden_first_name||' '||i.spriden_mi inst, 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 from sfrstcr, stvrsts, spriden s, spbpers, ssbsect, scbcrse x, sgbstdn a, stvstyp, sgrsprt, stvactc, sirasgn, spriden i where sfrstcr_term_code = term and sfrstcr_rsts_code=stvrsts_code and stvrsts_incl_sect_enrl='Y' and sfrstcr_pidm=sgrsprt_pidm and sgrsprt_term_code=sfrstcr_term_code and sgrsprt_actc_code like hold_team and sgrsprt_actc_code=stvactc_code(+) and sfrstcr_pidm=s.spriden_pidm and s.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_crn=sirasgn_crn(+) and sfrstcr_term_code=sirasgn_term_code(+) and sirasgn_primary_ind(+)='Y' and sirasgn_pidm=i.spriden_pidm(+) and i.spriden_change_ind(+) is null and sfrstcr_grde_code_mid is not null order by sport, lname, fname, mi, subj, crs, sec; begin mytab:=' '; /* mytab:=','; */ owa_util.mime_header('application/vnd.ms-excel', TRUE); if team = 'ALL' then hold_team := '%'; else hold_team := team; end if; htp.print('TEAM'||mytab||'ID'||mytab||'LNAME'||mytab||'FNAME'||mytab||'MI'||mytab||'CONFID'||mytab||'LEVEL'||mytab||'CLASS'||mytab||'COLL'||mytab|| 'MAJOR1'||mytab||'CONC1'||mytab||'CONC1_2'||mytab||'MINOR1'||mytab||'MINOR1_2'||mytab||'MAJOR2'||mytab||'CONC2'||mytab||'CONC2_2'||mytab|| 'MINOR2'||mytab||'MINOR2_2'||mytab||'CRN'||mytab||'SUBJ'||mytab||'CRS'||mytab||'SEC'||mytab||'TITLE'||mytab||'MTG'||mytab||'CREDITS'||mytab||'INSTRUCTOR'); FOR mystuff in dlmtginfo LOOP htp.print(mystuff.sport||mytab||''''||mystuff.id||mytab||mystuff.lname||mytab||mystuff.fname||mytab||mystuff.mi||mytab||mystuff.confid||mytab|| mystuff.levl||mytab||mystuff.class||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.crn||mytab||mystuff.subj||mytab||mystuff.crs||mytab||mystuff.sec||mytab||mystuff.title||mytab|| mystuff.mtg||mytab||mystuff.cr_hr||mytab||mystuff.inst); END LOOP; end LMUDLAthleteMTG; /****************************** LMU Show Team Rosters ***********************/ procedure LMUTeamRosters (term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, team in sgrsprt.sgrsprt_actc_code%type default null, disp_ethn in varchar2 default 'N', error_mess in varchar2 default null) is curr_release varchar2(10) := '5.5'; hold_term stvterm.stvterm_code%TYPE; hold_team sgrsprt.sgrsprt_actc_code%TYPE; msg varchar2(255); term_desc stvterm.stvterm_desc%type default null; rcount number; /* Get Team Codes for Drop-Down List */ cursor lmuteams is select distinct sgrsprt_actc_code team_code, stvactc_desc team_desc from sgrsprt, stvactc where sgrsprt_term_code = hold_term and sgrsprt_actc_code = stvactc_code(+); /* Get information that is to be displayed */ cursor teaminfo is select stvactc_desc team, spriden_id id, spriden_last_name||', '||spriden_first_name||' '||spriden_mi name, sgrsprt_spst_code spst, stvethn_desc ethn from sgrsprt, stvactc, spriden, spbpers, stvethn where sgrsprt_term_code = hold_term and sgrsprt_actc_code like hold_team and sgrsprt_actc_code = stvactc_code(+) and sgrsprt_pidm = spriden_pidm and spriden_change_ind is null and sgrsprt_pidm = spbpers_pidm and substr(spbpers_ethn_code, 1, 1) = stvethn_code(+) order by team, 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=>'lmuhwskainf.LMUTeamRosters'); RETURN; END IF; /* Check to see if all teams are to be returned */ if team = 'ALL' then hold_team := '%'; else hold_team := team; end if; /* Check to see if there are any records to display */ BEGIN select count(*) into rcount from sgrsprt where sgrsprt_term_code = hold_term and sgrsprt_actc_code like hold_team; EXCEPTION WHEN OTHERS THEN rcount := 0; END; /* Display message if no team records found */ IF rcount = 0 THEN if team is null then twbkwbis.P_OpenDoc('lmuhwskainf.LMUTeamRosters'); twbkwbis.P_DispInfo('lmuhwskainf.LMUTeamRosters','GENERAL'); else twbkwbis.P_OpenDoc('lmuhwskainf.LMUTeamRosters',header_text=> '*** No Team Data Exists ***'); /* Be sure to enter appropriate info text in Web Tailor for NO_ROSTER */ twbkwbis.P_DispInfo('lmuhwskainf.LMUTeamRosters','NO_ROSTER'); end if; ELSE /* Display Team Roster(s) */ twbkwbis.P_OpenDoc('lmuhwskainf.LMUTeamRosters'); /* You can enter info text for GENERAL if you want to display any additional information */ twbkwbis.P_DispInfo('lmuhwskainf.LMUTeamRosters','GENERAL'); htp.nl; htp.anchor(twbklibs.twgbwrul_rec.twgbwrul_cgibin_dir || '/lmuhwskainf.LMUDLTeamRosters'|| '?term=' || hold_term|| '&team=' || team|| '&disp_ethn=' || disp_ethn, 'DOWNLOAD TEAM ROSTER(S)'); htp.nl; twbkfrmt.P_TableOpen('DATADISPLAY'); twbkfrmt.P_TableRowOpen('left'); twbkfrmt.P_TableDataLabel('Team',calign=>'center'); twbkfrmt.P_TableDataLabel('ID',calign=>'center'); twbkfrmt.P_TableDataLabel('Name',calign=>'center'); twbkfrmt.P_TableDataLabel('Status',calign=>'center'); if disp_ethn = 'Y' then twbkfrmt.P_TableDataLabel('Ethnicity',calign=>'center'); end if; twbkfrmt.P_TableRowClose; FOR mystuff IN teaminfo LOOP twbkfrmt.P_TableRowOpen('left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.team),calign=>'left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.id),calign=>'left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.name),calign=>'left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.spst),calign=>'center'); if disp_ethn = 'Y' then twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.ethn),calign=>'left'); end if; twbkfrmt.P_TableRowClose; END LOOP; twbkfrmt.P_TableClose; END IF; htp.nl; HTP.formOpen(twbklibs.twgbwrul_rec.twgbwrul_cgibin_dir|| '/'||'lmuhwskainf.LMUTeamRosters'); HTP.formHidden('term',hold_term); row_count := 0; FOR team_rec in lmuteams LOOP IF lmuteams%rowcount = 1 THEN twbkfrmt.p_tableopen( 'DATAENTRY'); twbkfrmt.p_tablerowopen; twbkfrmt.p_tabledatalabel( 'Select Team:'); twbkfrmt.p_tabledataopen; HTP.formSelectOpen('team',NULL,1); HTP.P(twbkwbis.F_formSelectOption('All Teams', 'ALL')); END IF; IF team IS NOT NULL AND team_rec.team_code = team THEN HTP.P(twbkwbis.F_formSelectOption(team_rec.team_desc, team_rec.team_code,'SELECTED')); ELSE HTP.P(twbkwbis.F_formSelectOption(team_rec.team_desc, team_rec.team_code)); END IF; row_count := lmuteams%rowcount; END LOOP; IF row_count = 0 THEN twbkfrmt.p_printtext('No Teams Available'); ELSE HTP.formSelectClose; twbkfrmt.p_tabledataopen; twbkfrmt.p_tablerowclose; twbkfrmt.p_tableclose; htp.formCheckbox('disp_ethn', 'Y'); htp.bold('Display Student Ethnicity'); HTP.nl; HTP.formsubmit(NULL,'Submit Team'); END IF; HTP.formClose; htp.nl; twbkwbis.P_CloseDoc(curr_release); end LMUTeamRosters; /****************************** LMU Download Team Rosters ***********************/ procedure LMUDLTeamRosters (term in stvterm.stvterm_code%type default null, team in sgrsprt.sgrsprt_actc_code%type default null, disp_ethn in varchar2 default 'N') is mytab varchar2(1); hold_team sgrsprt.sgrsprt_actc_code%type; cursor dlteaminfo is select stvactc_desc team, spriden_id id, spriden_last_name||', '||spriden_first_name||' '||spriden_mi name, sgrsprt_spst_code spst, stvethn_desc ethn from sgrsprt, stvactc, spriden, spbpers, stvethn where sgrsprt_term_code = term and sgrsprt_actc_code like hold_team and sgrsprt_actc_code = stvactc_code(+) and sgrsprt_pidm = spriden_pidm and spriden_change_ind is null and sgrsprt_pidm = spbpers_pidm and substr(spbpers_ethn_code, 1, 1) = stvethn_code(+) order by team, name; begin mytab:=' '; /* mytab:=','; */ owa_util.mime_header('application/vnd.ms-excel', TRUE); if team = 'ALL' then hold_team := '%'; else hold_team := team; end if; if disp_ethn = 'Y' then htp.print('TEAM'||mytab||'ID'||mytab||'NAME'||mytab||'STATUS'||mytab||'ETHNICITY'); else htp.print('TEAM'||mytab||'ID'||mytab||'NAME'||mytab||'STATUS'); end if; FOR mystuff in dlteaminfo LOOP if disp_ethn = 'Y' then htp.print(mystuff.team||mytab||''''||mystuff.id||mytab||mystuff.name||mytab||mystuff.spst||mytab||mystuff.ethn); else htp.print(mystuff.team||mytab||''''||mystuff.id||mytab||mystuff.name||mytab||mystuff.spst); end if; END LOOP; end LMUDLTeamRosters; /****************************** LMU Athletes Enrolled < X Hours ***********************/ procedure LMUAthleteEnrlHrs (term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, team in sgrsprt.sgrsprt_actc_code%type default null, hrs in number default null, error_mess in varchar2 default null) is curr_release varchar2(10) := '5.5'; hold_term stvterm.stvterm_code%TYPE; hold_team sgrsprt.sgrsprt_actc_code%TYPE; msg varchar2(255); term_desc stvterm.stvterm_desc%type default null; rcount number; /* Get Team Codes for Drop-Down List */ cursor lmuteams is select distinct sgrsprt_actc_code team_code, stvactc_desc team_desc from sgrsprt, stvactc where sgrsprt_term_code = hold_term and sgrsprt_actc_code = stvactc_code(+); /* Get information that is to be displayed */ cursor teaminfo is select stvactc_desc team, s.spriden_id id, s.spriden_last_name||', '||s.spriden_first_name||' '||s.spriden_mi name, a.sgbstdn_levl_code levl, f_class_calc_fnc(a.sgbstdn_pidm, a.sgbstdn_levl_code, hold_term) class, f_calc_registration_hours(a.sgbstdn_pidm, hold_term, 'TOTAL', 'CREDIT') cr_hrs, lmu_f_get_attend_hrs(a.sgbstdn_pidm, hold_term) att_hrs from sgrsprt, stvactc, spriden s, sgbstdn a where sgrsprt_term_code = hold_term and sgrsprt_actc_code like hold_team and sgrsprt_actc_code = stvactc_code(+) and sgrsprt_pidm = s.spriden_pidm and s.spriden_change_ind is null and sgrsprt_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 <= sgrsprt_term_code and a.sgbstdn_pidm = b.sgbstdn_pidm) and lmu_f_get_attend_hrs(a.sgbstdn_pidm, hold_term)< hrs order by team, 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=>'lmuhwskainf.LMUAthleteEnrlHrs'); RETURN; END IF; /* Check to see if all teams are to be returned */ if team = 'ALL' then hold_team := '%'; else hold_team := team; end if; /* Check to see if there are any records to display */ BEGIN select count(*) into rcount from sgrsprt where sgrsprt_term_code = hold_term and sgrsprt_actc_code like hold_team and lmu_f_get_attend_hrs(sgrsprt_pidm, hold_term)< hrs; EXCEPTION WHEN OTHERS THEN rcount := 0; END; /* Display message if no team records found */ IF rcount = 0 THEN if hrs is null then twbkwbis.P_OpenDoc('lmuhwskainf.LMUAthleteEnrlHrs',header_text=> 'Enrolled in Less Than 12/15 Hours'); else twbkwbis.P_OpenDoc('lmuhwskainf.LMUAthleteEnrlHrs',header_text=> 'Enrolled in Less Than '||hrs||' Hours'); end if; if team is null then twbkwbis.P_DispInfo('lmuhwskainf.LMUAthleteEnrlHrs','GENERAL'); else /* Be sure to enter appropriate info text in Web Tailor for NO_DATA */ twbkwbis.P_DispInfo('lmuhwskainf.LMUAthleteEnrlHrs','NO_DATA'); end if; ELSE /* Display Data */ twbkwbis.P_OpenDoc('lmuhwskainf.LMUAthleteEnrlHrs',header_text=> 'Enrolled in Less Than '||hrs||' Hours'); /* You can enter info text for GENERAL if you want to display any additional information */ twbkwbis.P_DispInfo('lmuhwskainf.LMUAthleteEnrlHrs','GENERAL'); htp.nl; htp.anchor(twbklibs.twgbwrul_rec.twgbwrul_cgibin_dir || '/lmuhwskainf.LMUDLAthleteEnrlHrs'|| '?term=' || hold_term|| '&team=' || team|| '&hrs=' || hrs, 'DOWNLOAD INFORMATION'); htp.nl; twbkfrmt.P_TableOpen('DATADISPLAY'); twbkfrmt.P_TableRowOpen('left'); twbkfrmt.P_TableDataLabel('Team',calign=>'left'); twbkfrmt.P_TableDataLabel('ID',calign=>'left'); twbkfrmt.P_TableDataLabel('Name',calign=>'left'); twbkfrmt.P_TableDataLabel('Level',calign=>'center'); twbkfrmt.P_TableDataLabel('Class',calign=>'center'); twbkfrmt.P_TableDataLabel('CR HRS',calign=>'center'); twbkfrmt.P_TableDataLabel('ATTEND HRS',calign=>'center'); twbkfrmt.P_TableRowClose; FOR mystuff IN teaminfo LOOP twbkfrmt.P_TableRowOpen('left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.team),calign=>'left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.id),calign=>'left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.name),calign=>'left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.levl),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.class),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.cr_hrs),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.att_hrs),calign=>'center'); twbkfrmt.P_TableRowClose; END LOOP; twbkfrmt.P_TableClose; END IF; htp.nl; HTP.formOpen(twbklibs.twgbwrul_rec.twgbwrul_cgibin_dir|| '/'||'lmuhwskainf.LMUAthleteEnrlHrs'); HTP.formHidden('term',hold_term); row_count := 0; FOR team_rec in lmuteams LOOP IF lmuteams%rowcount = 1 THEN twbkfrmt.p_tableopen( 'DATAENTRY'); twbkfrmt.p_tablerowopen; twbkfrmt.p_tabledatalabel( 'Select Team:'); twbkfrmt.p_tabledataopen; HTP.formSelectOpen('team',NULL,1); HTP.P(twbkwbis.F_formSelectOption('All Teams', 'ALL')); END IF; IF team IS NOT NULL AND team_rec.team_code = team THEN HTP.P(twbkwbis.F_formSelectOption(team_rec.team_desc, team_rec.team_code,'SELECTED')); ELSE HTP.P(twbkwbis.F_formSelectOption(team_rec.team_desc, team_rec.team_code)); END IF; row_count := lmuteams%rowcount; END LOOP; IF row_count = 0 THEN twbkfrmt.p_printtext('No Teams Available'); ELSE HTP.formSelectClose; twbkfrmt.p_tabledataopen; twbkfrmt.p_tablerowclose; twbkfrmt.p_tablerowopen; twbkfrmt.p_tabledatalabel( 'Select Hours:'); twbkfrmt.p_tabledataopen; HTP.formSelectOpen('hrs',NULL,1); if hrs is null or hrs = 12 then HTP.P(twbkwbis.F_formSelectOption('12', 12, 'SELECTED')); HTP.P(twbkwbis.F_formSelectOption('15', 15)); else HTP.P(twbkwbis.F_formSelectOption('12', 12)); HTP.P(twbkwbis.F_formSelectOption('15', 15, 'SELECTED')); end if; HTP.formSelectClose; twbkfrmt.p_tabledataopen; twbkfrmt.p_tablerowclose; twbkfrmt.p_tableclose; HTP.nl; HTP.formsubmit(NULL,'Submit'); END IF; HTP.formClose; htp.nl; twbkwbis.P_CloseDoc(curr_release); end LMUAthleteEnrlHrs; procedure LMUDLAthleteEnrlHrs (term in stvterm.stvterm_code%type default null, team in sgrsprt.sgrsprt_actc_code%type default null, hrs in number default null) is mytab varchar2(1); hold_team sgrsprt.sgrsprt_actc_code%type; cursor dlteaminfo is select stvactc_desc team, s.spriden_id id, s.spriden_last_name||', '||s.spriden_first_name||' '||s.spriden_mi name, a.sgbstdn_levl_code levl, f_class_calc_fnc(a.sgbstdn_pidm, a.sgbstdn_levl_code, term) class, f_calc_registration_hours(a.sgbstdn_pidm, term, 'TOTAL', 'CREDIT') cr_hrs, lmu_f_get_attend_hrs(a.sgbstdn_pidm, term) att_hrs from sgrsprt, stvactc, spriden s, sgbstdn a where sgrsprt_term_code = term and sgrsprt_actc_code like hold_team and sgrsprt_actc_code = stvactc_code(+) and sgrsprt_pidm = s.spriden_pidm and s.spriden_change_ind is null and sgrsprt_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 <= sgrsprt_term_code and a.sgbstdn_pidm = b.sgbstdn_pidm) and lmu_f_get_attend_hrs(a.sgbstdn_pidm, term)< hrs order by team, name; begin mytab:=' '; owa_util.mime_header('application/vnd.ms-excel', TRUE); if team = 'ALL' then hold_team := '%'; else hold_team := team; end if; htp.print('TEAM'||mytab||'ID'||mytab||'NAME'||mytab||'LEVEL'||mytab||'CLASS'||mytab||'CR HRS'||mytab||'ATTEND HRS'); FOR mystuff in dlteaminfo LOOP htp.print(mystuff.team||mytab||''''||mystuff.id||mytab||mystuff.name||mytab||mystuff.levl||mytab||mystuff.class||mytab||mystuff.cr_hrs||mytab||mystuff.att_hrs); END LOOP; end LMUDLAthleteEnrlHrs; procedure LMUAthletesNotEnrolled (term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, team in sgrsprt.sgrsprt_actc_code%type default null, regterm 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_team sgrsprt.sgrsprt_actc_code%TYPE; msg varchar2(255); term_desc stvterm.stvterm_desc%type default null; rcount number; /* Get Team Codes for Drop-Down List */ cursor lmuteams is select distinct sgrsprt_actc_code team_code, stvactc_desc team_desc from sgrsprt, stvactc where sgrsprt_term_code = hold_term and sgrsprt_actc_code = stvactc_code(+); /* Get Term Codes for Registration Terms */ cursor rterms is select distinct sobterm_term_code term_code, stvterm_desc term_desc from sobterm, stvterm where sobterm_reg_allowed='Y' and substr(sobterm_term_code, 6, 1) not in ('3', '4') and sobterm_term_code=stvterm_code and sobterm_term_code > hold_term order by term_code DESC; cursor teaminfo is select stvactc_desc team, spriden_id id, spriden_last_name||', '||spriden_first_name||' '||spriden_mi name from sgrsprt, stvactc, spriden where sgrsprt_term_code = hold_term and sgrsprt_actc_code like hold_team and sgrsprt_actc_code=stvactc_code(+) and sgrsprt_pidm=spriden_pidm(+) and spriden_change_ind is null and not exists (select 'X' from sfrstcr, stvrsts where sfrstcr_pidm=sgrsprt_pidm and sfrstcr_term_code = regterm and sfrstcr_rsts_code=stvrsts_code and stvrsts_incl_sect_enrl='Y') order by team, 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=>'lmuhwskainf.LMUAthletesNotEnrolled'); RETURN; END IF; /* Check to see if all teams are to be returned */ if team = 'ALL' then hold_team := '%'; else hold_team := team; end if; /* Check to see if there are any records to display */ BEGIN select count(*) into rcount from sgrsprt where sgrsprt_term_code = hold_term and sgrsprt_actc_code like hold_team and not exists (select 'X' from sfrstcr, stvrsts where sfrstcr_pidm=sgrsprt_pidm and sfrstcr_term_code = regterm and sfrstcr_rsts_code=stvrsts_code and stvrsts_incl_sect_enrl='Y'); EXCEPTION WHEN OTHERS THEN rcount := 0; END; /* Display message if no team records found */ IF rcount = 0 THEN twbkwbis.P_OpenDoc('lmuhwskainf.LMUAthletesNotEnrolled'); if team is null then twbkwbis.P_DispInfo('lmuhwskainf.LMUAthletesNotEnrolled','GENERAL'); else /* Be sure to enter appropriate info text in Web Tailor for NO_DATA */ twbkwbis.P_DispInfo('lmuhwskainf.LMUAthletesNotEnrolled','NO_DATA'); end if; ELSE /* Display Data */ twbkwbis.P_OpenDoc('lmuhwskainf.LMUAthletesNotEnrolled'); /* You can enter info text for GENERAL if you want to display any additional information */ twbkwbis.P_DispInfo('lmuhwskainf.LMUAthletesNotEnrolled','GENERAL'); htp.nl; htp.anchor(twbklibs.twgbwrul_rec.twgbwrul_cgibin_dir || '/lmuhwskainf.LMUDLAthletesNotEnrolled'|| '?term=' || hold_term|| '&team=' || team|| '®term=' || regterm, 'DOWNLOAD INFORMATION'); htp.nl; twbkfrmt.P_TableOpen('DATADISPLAY'); twbkfrmt.P_TableRowOpen('left'); twbkfrmt.P_TableDataLabel('Team',calign=>'left'); twbkfrmt.P_TableDataLabel('ID',calign=>'left'); twbkfrmt.P_TableDataLabel('Name',calign=>'left'); twbkfrmt.P_TableRowClose; FOR mystuff IN teaminfo LOOP twbkfrmt.P_TableRowOpen('left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.team),calign=>'left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.id),calign=>'left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.name),calign=>'left'); twbkfrmt.P_TableRowClose; END LOOP; twbkfrmt.P_TableClose; END IF; htp.nl; HTP.formOpen(twbklibs.twgbwrul_rec.twgbwrul_cgibin_dir|| '/'||'lmuhwskainf.LMUAthletesNotEnrolled'); HTP.formHidden('term',hold_term); row_count := 0; FOR team_rec in lmuteams LOOP IF lmuteams%rowcount = 1 THEN twbkfrmt.p_tableopen( 'DATAENTRY'); twbkfrmt.p_tablerowopen; twbkfrmt.p_tabledatalabel( 'Select Team:'); twbkfrmt.p_tabledataopen; HTP.formSelectOpen('team',NULL,1); HTP.P(twbkwbis.F_formSelectOption('All Teams', 'ALL')); END IF; IF team IS NOT NULL AND team_rec.team_code = team THEN HTP.P(twbkwbis.F_formSelectOption(team_rec.team_desc, team_rec.team_code,'SELECTED')); ELSE HTP.P(twbkwbis.F_formSelectOption(team_rec.team_desc, team_rec.team_code)); END IF; row_count := lmuteams%rowcount; END LOOP; HTP.formSelectClose; twbkfrmt.p_tablerowclose; for rt_rec in rterms loop if rterms%rowcount = 1 then twbkfrmt.p_tablerowopen; twbkfrmt.p_tabledatalabel( 'Select Registration Term:'); twbkfrmt.p_tabledataopen; HTP.formSelectOpen('regterm',NULL,1); end if; if regterm is not null and rt_rec.term_code = regterm then HTP.P(twbkwbis.F_formSelectOption(rt_rec.term_desc, rt_rec.term_code, 'SELECTED')); else HTP.P(twbkwbis.F_formSelectOption(rt_rec.term_desc, rt_rec.term_code)); end if; row_count := rterms%rowcount; end loop; HTP.formSelectClose; twbkfrmt.p_tablerowclose; twbkfrmt.p_tableclose; HTP.nl; HTP.formsubmit(NULL,'Submit'); HTP.formClose; htp.nl; twbkwbis.P_CloseDoc(curr_release); end LMUAthletesNotEnrolled; procedure LMUDLAthletesNotEnrolled (term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, team in sgrsprt.sgrsprt_actc_code%type default null, regterm in stvterm.stvterm_code%type default null) is mytab varchar2(1); hold_team sgrsprt.sgrsprt_actc_code%type; cursor dlteaminfo is select stvactc_desc team, spriden_id id, spriden_last_name||', '||spriden_first_name||' '||spriden_mi name from sgrsprt, stvactc, spriden where sgrsprt_term_code = term and sgrsprt_actc_code like hold_team and sgrsprt_actc_code=stvactc_code(+) and sgrsprt_pidm=spriden_pidm(+) and spriden_change_ind is null and not exists (select 'X' from sfrstcr, stvrsts where sfrstcr_pidm=sgrsprt_pidm and sfrstcr_term_code = regterm and sfrstcr_rsts_code=stvrsts_code and stvrsts_incl_sect_enrl='Y') order by team, name; begin mytab:=' '; owa_util.mime_header('application/vnd.ms-excel', TRUE); if team = 'ALL' then hold_team := '%'; else hold_team := team; end if; htp.print('TEAM'||mytab||'ID'||mytab||'NAME'); FOR mystuff in dlteaminfo LOOP htp.print(mystuff.team||mytab||''''||mystuff.id||mytab||mystuff.name); END LOOP; end LMUDLAthletesNotEnrolled; procedure LMUAthleteDrops (term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, team in sgrsprt.sgrsprt_actc_code%type default null, error_mess in varchar2 default null) is curr_release varchar2(10) := '5.5'; hold_term stvterm.stvterm_code%TYPE; hold_team sgrsprt.sgrsprt_actc_code%TYPE; msg varchar2(255); term_desc stvterm.stvterm_desc%type default null; rcount number; /* Get Team Codes for Drop-Down List */ cursor lmuteams is select distinct sgrsprt_actc_code team_code, stvactc_desc team_desc from sgrsprt, stvactc where sgrsprt_term_code = hold_term and sgrsprt_actc_code = stvactc_code(+); /* Get information that is to be displayed */ cursor teaminfo is select stvactc_desc team, spriden_id id, spriden_last_name||', '||spriden_first_name||' '||spriden_mi name, a.sgbstdn_levl_code levl, f_class_calc_fnc(a.sgbstdn_pidm, a.sgbstdn_levl_code, hold_term) class, 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 from sgrsprt, stvactc, spriden, sgbstdn a, sfrstcr, stvrsts, ssbsect, scbcrse x where sgrsprt_term_code = hold_term and sgrsprt_actc_code like hold_team and sgrsprt_actc_code=stvactc_code(+) and sgrsprt_pidm=spriden_pidm and spriden_change_ind is null and sgrsprt_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<=sgrsprt_term_code and a.sgbstdn_pidm=b.sgbstdn_pidm) and sgrsprt_pidm=sfrstcr_pidm and sgrsprt_term_code=sfrstcr_term_code and sfrstcr_rsts_code=stvrsts_code and stvrsts_withdraw_ind='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) order by team, name, 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=>'lmuhwskainf.LMUAthleteDrops'); RETURN; END IF; /* Check to see if all teams are to be returned */ if team = 'ALL' then hold_team := '%'; else hold_team := team; end if; /*Get Term Description */ begin select stvterm_desc into term_desc from stvterm where stvterm_code = hold_term; exception when others then term_desc := 'Unknown'; end; /* Check to see if there are any records to display */ BEGIN select count(*) into rcount from sgrsprt, sfrstcr, stvrsts where sgrsprt_term_code = hold_term and sgrsprt_actc_code like hold_team and sgrsprt_pidm = sfrstcr_pidm and sgrsprt_term_code = sfrstcr_term_code and sfrstcr_rsts_code = stvrsts_code and stvrsts_withdraw_ind = 'Y'; EXCEPTION WHEN OTHERS THEN rcount := 0; END; /* Display message if no team records found */ IF rcount = 0 THEN twbkwbis.P_OpenDoc('lmuhwskainf.LMUAthleteDrops'); if team is null then twbkwbis.P_DispInfo('lmuhwskainf.LMUAthleteDrops','GENERAL'); else /* Be sure to enter appropriate info text in Web Tailor for NO_DATA */ twbkwbis.P_DispInfo('lmuhwskainf.LMUAthleteDrops','NO_DATA'); end if; ELSE /* Display Data */ twbkwbis.P_OpenDoc('lmuhwskainf.LMUAthleteDrops',header_text=> 'For: '||term_desc); /* You can enter info text for GENERAL if you want to display any additional information */ twbkwbis.P_DispInfo('lmuhwskainf.LMUAthleteDrops','GENERAL'); htp.nl; htp.anchor(twbklibs.twgbwrul_rec.twgbwrul_cgibin_dir || '/lmuhwskainf.LMUDLAthleteDrops'|| '?term=' || hold_term|| '&team=' || team, 'DOWNLOAD INFORMATION'); htp.nl; twbkfrmt.P_TableOpen('DATADISPLAY'); twbkfrmt.P_TableRowOpen('left'); twbkfrmt.P_TableDataLabel('Team',calign=>'left'); twbkfrmt.P_TableDataLabel('ID',calign=>'left'); twbkfrmt.P_TableDataLabel('Name',calign=>'left'); twbkfrmt.P_TableDataLabel('Level',calign=>'center'); 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('Code',calign=>'center'); twbkfrmt.P_TableDataLabel('Status',calign=>'left'); twbkfrmt.P_TableDataLabel('Status Date',calign=>'left'); twbkfrmt.P_TableRowClose; FOR mystuff IN teaminfo LOOP twbkfrmt.P_TableRowOpen('left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.team),calign=>'left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.id),calign=>'left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.name),calign=>'left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.levl),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.class),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.crn),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.subj),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.crs),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.sec),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.title),calign=>'left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.rsts),calign=>'center'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.rsts_desc),calign=>'left'); twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.rsts_date),calign=>'left'); twbkfrmt.P_TableRowClose; END LOOP; twbkfrmt.P_TableClose; END IF; htp.nl; HTP.formOpen(twbklibs.twgbwrul_rec.twgbwrul_cgibin_dir|| '/'||'lmuhwskainf.LMUAthleteDrops'); HTP.formHidden('term',hold_term); row_count := 0; FOR team_rec in lmuteams LOOP IF lmuteams%rowcount = 1 THEN twbkfrmt.p_tableopen( 'DATAENTRY'); twbkfrmt.p_tablerowopen; twbkfrmt.p_tabledatalabel( 'Select Team:'); twbkfrmt.p_tabledataopen; HTP.formSelectOpen('team',NULL,1); HTP.P(twbkwbis.F_formSelectOption('All Teams', 'ALL')); END IF; IF team IS NOT NULL AND team_rec.team_code = team THEN HTP.P(twbkwbis.F_formSelectOption(team_rec.team_desc, team_rec.team_code,'SELECTED')); ELSE HTP.P(twbkwbis.F_formSelectOption(team_rec.team_desc, team_rec.team_code)); END IF; row_count := lmuteams%rowcount; END LOOP; IF row_count = 0 THEN twbkfrmt.p_printtext('No Teams Available'); ELSE HTP.formSelectClose; twbkfrmt.p_tablerowclose; twbkfrmt.p_tableclose; HTP.nl; HTP.formsubmit(NULL,'Submit Team'); END IF; HTP.formClose; htp.nl; twbkwbis.P_CloseDoc(curr_release); end LMUAthleteDrops; procedure LMUDLAthleteDrops (term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL, team in sgrsprt.sgrsprt_actc_code%type default null) is mytab varchar2(1); hold_team sgrsprt.sgrsprt_actc_code%type; cursor dlteaminfo is select stvactc_desc team, spriden_id id, spriden_last_name||', '||spriden_first_name||' '||spriden_mi name, a.sgbstdn_levl_code levl, f_class_calc_fnc(a.sgbstdn_pidm, a.sgbstdn_levl_code, term) class, 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 from sgrsprt, stvactc, spriden, sgbstdn a, sfrstcr, stvrsts, ssbsect, scbcrse x where sgrsprt_term_code = term and sgrsprt_actc_code like hold_team and sgrsprt_actc_code=stvactc_code(+) and sgrsprt_pidm=spriden_pidm and spriden_change_ind is null and sgrsprt_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<=sgrsprt_term_code and a.sgbstdn_pidm=b.sgbstdn_pidm) and sgrsprt_pidm=sfrstcr_pidm and sgrsprt_term_code=sfrstcr_term_code and sfrstcr_rsts_code=stvrsts_code and stvrsts_withdraw_ind='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) order by team, name, subj, crs, sec; begin mytab:=' '; owa_util.mime_header('application/vnd.ms-excel', TRUE); if team = 'ALL' then hold_team := '%'; else hold_team := team; end if; htp.print('TEAM'||mytab||'ID'||mytab||'NAME'||mytab||'LEVEL'||mytab||'CLASS'||mytab||'CRN'|| mytab||'SUBJ'||mytab||'CRS'||mytab||'SEC'||mytab||'TITLE'||mytab||'CODE'||mytab||'STATUS'|| mytab||'STATUS DATE'); FOR mystuff in dlteaminfo LOOP htp.print(mystuff.team||mytab||''''||mystuff.id||mytab||mystuff.name||mytab||mystuff.levl|| mytab||mystuff.class||mytab||mystuff.crn||mytab||mystuff.subj||mytab||mystuff.crs|| mytab||mystuff.sec||mytab||mystuff.title||mytab||mystuff.rsts||mytab||mystuff.rsts_desc|| mytab||mystuff.rsts_date); END LOOP; end LMUDLAthleteDrops; end lmuhwskainf; / show errors whenever sqlerror continue; drop public synonym lmuhwskainf; whenever sqlerror exit rollback; create public synonym lmuhwskainf for lmuhwskainf; grant execute on lmuhwskainf to WWW_USER; set scan on