set scan off
set echo on
-- Created for Loyola Marymount University
-- By Robert M. Nitsos
-- 20-MAR-2002
-- 
-- This package creates a Banner Web page that displays information about the student
-- which relates to registration.  Students can see their registration date and time,
-- their current advisor, academic standing, hold status, earned hours, level, class
-- standing, college, major and campus.  Students can use this page to determine if
-- and when they can register for a selected term.
--

create or replace package lmuhwskrgtm is

    TYPE varchar2_tabtype IS TABLE OF varchar2(26)
      INDEX BY BINARY_INTEGER;

procedure LMURegTimes (term_in in stvterm.stvterm_code%type default null);

END lmuhwskrgtm;
/
show errors

CREATE OR REPLACE PACKAGE BODY lmuhwskrgtm AS
/* make sure registered then continue to process */

  /* Global type and variable declarations for package */

    pidm spriden.spriden_pidm%TYPE;

/****************************** LMU Registration Status ***********************/
procedure LMURegTimes (term_in in stvterm.stvterm_code%type default null)
 is
   hold_term	STVTERM.STVTERM_CODE%TYPE;
   term_desc	stvterm.stvterm_desc%type;
   hold_pidm    spriden.spriden_pidm%type;
   dcount		number;
   hcount		number;
   acount		number;
   ascount		number;
   reg_date		sfrctrl.sfrctrl_begin_date%type;
   reg_time		varchar2(8);
   stvterm_rec	stvterm%ROWTYPE;
   sorrtrm_rec	sorrtrm%ROWTYPE;
   found		varchar2(1);
   stu_levl		sgbstdn.sgbstdn_levl_code%type;
   stu_levl_desc	stvlevl.stvlevl_desc%type;
   stu_degc		sgbstdn.sgbstdn_degc_code_1%type;
   st_status	stvstst.stvstst_desc%type;
   reg_ind		varchar2(50);
   stu_hrs		shrlgpa.shrlgpa_hours_earned%type;
   stu_lname	spriden.spriden_last_name%type;
   stu_class	stvclas.stvclas_desc%type;
   stu_coll		stvcoll.stvcoll_desc%type;
   stu_majr		stvmajr.stvmajr_desc%type;
   stu_camp		stvcamp.stvcamp_desc%type;
   stu_apin	sprapin.sprapin_pin%type;
   stu_advr		varchar2(50);
   stu_astd		stvastd.stvastd_desc%type default null;
   stu_astd_pri	stvastd.stvastd_prevent_reg_ind%type default null;

   cursor reginfo
   is
   select sfrctrl_term_code_host term,
     sfrctrl_seq_no seq_no,
     sfrctrl_begin_date begin_date,
     sfrctrl_end_date end_date,
     sfrctrl_hour_begin hour_begin,
     sfrctrl_hour_end hour_end,
     sfrctrl_pin_start pin_start,
     sfrctrl_pin_end pin_end,
     sfrctrl_last_nam_start lname_start,
     sfrctrl_last_nam_end lname_end,
     sfrctrl_levl_1 levl1,
     sfrctrl_levl_2 levl2,
     sfrctrl_degr_incl_excl degr_ind,
     sfrctrl_degr_1 degr1,
     sfrctrl_degr_2 degr2,
     nvl(sfrctrl_earn_hrs_begin, 0) hrs_begin,
     nvl(sfrctrl_earn_hrs_end, 999) hrs_end
   from sfrctrl
   where sfrctrl_term_code_host=hold_term
   order by term, seq_no;

begin

   /* Check for Valid User */
   IF NOT twgkwbis.F_ValidUser(pidm) THEN
        return;
   END IF;
   /* hold_pidm := NVL(stu_pidm, pidm); */
   hold_pidm := pidm;

   /* Check for Valid Term */
   IF term_in IS NOT NULL
   THEN
      IF hwskflib.f_validterm (term_in, stvterm_rec, sorrtrm_rec)
      THEN
         twgkwbis.p_setparam (pidm, 'TERM', term_in);
      END IF;
    hold_term := term_in;
   ELSE
      hold_term := twgkwbis.f_getparam (pidm, 'TERM');
      IF NOT hwskflib.f_validterm (hold_term, stvterm_rec, sorrtrm_rec)
      THEN
         hwskflib.p_seldefterm (hold_term, 'lmuhwskrgtm.LMURegTimes');
		 return;
      END IF;
   END IF;
   
   /*get term description */
   select stvterm_desc
   into term_desc
   from stvterm
   where stvterm_code=hold_term;

   /* Check for control file records */
   BEGIN
      select count(*)
      into   dcount
      from   sfrctrl
      where  sfrctrl_term_code_host          = hold_term;
   EXCEPTION WHEN OTHERS THEN
      dcount := 0;
   END;

   /* Display message if no registration records found */
   IF dcount = 0 THEN
      twgkwbis.P_OpenDoc('lmuhwskrgtm.LMURegTimes',header_text=>
                      '*** No Registration Information Exists for Selected Term ***');
      twgkwbis.P_DispInfo('lmuhwskrgtm.LMURegTimes','NO_REGINFO');
      twgkwbis.P_CloseDoc;
      return;
   END IF;

   /* Get Student Information */
   select a.sgbstdn_levl_code,
     stvlevl_desc,
     a.sgbstdn_degc_code_1,
	 stvstst_desc,
	 decode(stvstst_reg_ind,
	   'Y', 'allows registration.', 'does not allow registration.'),
	 stvclas_desc,
	 stvcoll_desc,
	 stvmajr_desc,
	 stvcamp_desc,
	 sprapin_pin,
	 spriden_last_name,
	 nvl(shrlgpa_hours_earned, 0)
   into stu_levl, stu_levl_desc, stu_degc, st_status, reg_ind, stu_class, stu_coll, stu_majr, stu_camp, stu_apin, stu_lname, stu_hrs
   from sgbstdn a,
     stvlevl,
     stvstst,
	 stvclas,
     stvcoll,
	 stvmajr,
	 stvcamp,
     spriden,
	 sprapin,
	 shrlgpa
   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_levl_code=stvlevl_code(+)
   and a.sgbstdn_stst_code=stvstst_code(+)
   and f_class_calc_fnc(a.sgbstdn_pidm, a.sgbstdn_levl_code, hold_term)=stvclas_code(+)
   and a.sgbstdn_coll_code_1=stvcoll_code(+)
   and a.sgbstdn_majr_code_1=stvmajr_code(+)
   and a.sgbstdn_camp_code=stvcamp_code(+)
   and a.sgbstdn_pidm=spriden_pidm
   and spriden_change_ind is null
   and a.sgbstdn_pidm=sprapin_pidm(+)
   and sprapin_term_code(+)=hold_term
   and a.sgbstdn_pidm=shrlgpa_pidm(+)
   and a.sgbstdn_levl_code=shrlgpa_levl_code(+)
   and shrlgpa_gpa_type_ind(+)='O'
   and a.sgbstdn_pidm=hold_pidm;
   
   /* Get Hold Info */
   BEGIN
      select count(*)
      into   hcount
      from   sprhold
      where  sprhold_pidm=hold_pidm
	  and sysdate between sprhold_from_date and sprhold_to_date;
   EXCEPTION WHEN OTHERS THEN
      hcount := 0;
   END;
   
   /* Get Advisor Info */
   acount:=1;
   begin
   	select spbpers_name_prefix||' '||spriden_first_name||' '||spriden_last_name
   	into stu_advr
   	from sgradvr a,
   	  spriden,
		 spbpers
   	where a.sgradvr_pidm=hold_pidm
   	and 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_code='MAJ1'
   	and a.sgradvr_prim_ind='Y'
   	and a.sgradvr_advr_pidm=spbpers_pidm
   	and a.sgradvr_advr_pidm=spriden_pidm
   	and spriden_change_ind is null;
   exception when others then
   	acount:=0;
   end;
   
   /* Get Academic Standing */
   ascount:=1;
   begin
   select stvastd_desc,
     stvastd_prevent_reg_ind
   into stu_astd, stu_astd_pri
   from shrttrm a,
     stvastd
   where a.shrttrm_pidm=hold_pidm
   and a.shrttrm_term_code=
    (select max(b.shrttrm_term_code) from shrttrm b
     where a.shrttrm_pidm=b.shrttrm_pidm)
   and a.shrttrm_astd_code_end_of_term=stvastd_code(+);
   exception when others then
   	ascount:=0;
   end;

   /* Find Registration Information For Student */
   found:='N';
   for mystuff in reginfo
   loop
   		if found<>'Y' then
	   		if stu_apin>=mystuff.pin_start and stu_apin<=mystuff.pin_end then
				reg_date:=mystuff.begin_date;
				reg_time:=mystuff.hour_begin;
				found:='Y';
			else
				if (stu_levl=mystuff.levl1 or stu_levl=mystuff.levl2) then
					if stu_hrs>=mystuff.hrs_begin and stu_hrs<=mystuff.hrs_end then
						if mystuff.lname_start is not null then
							if upper(stu_lname)>=upper(mystuff.lname_start) and upper(stu_lname)<=upper(mystuff.lname_end) then
								reg_date:=mystuff.begin_date;
								reg_time:=mystuff.hour_begin;
								found:='Y';
							end if;
						else
							reg_date:=mystuff.begin_date;
							reg_time:=mystuff.hour_begin;
							found:='Y';		
						end if;
					end if;
				end if;
			end if;
		end if;
   end loop;
   
   IF found = 'N' THEN
      /* Display message if no matching records found */
      twgkwbis.P_OpenDoc('lmuhwskrgtm.LMURegTimes',header_text=>
                      '*** Unable to Locate Registration Information ***');
      twgkwbis.P_DispInfo('lmuhwskrgtm.LMURegTimes','NO_REG_MATCH');

   Else
   		twgkwbis.P_OpenDoc('lmuhwskrgtm.LMURegTimes',header_text=>
			'For: '||
			f_format_name(hold_pidm, 'FMIL')||' ('||term_desc||')');
		twgkwbis.P_DispInfo('lmuhwskrgtm.LMURegTimes','DEFAULT');
		
		/* Format Time */
		if substr(reg_time, 1, 2)>='12' then
			if substr(reg_time, 1, 2)='12' then
				reg_time:=substr(reg_time, 1, 2)||':'||substr(reg_time, 3, 4)||' pm';
			else
				if reg_time='1300' then
					reg_time:='0100';
				elsif reg_time='1400' then
					reg_time:='0200';
				elsif reg_time='1500' then
					reg_time:='0300';
				elsif reg_time='1600' then
					reg_time:='0400';
				elsif reg_time='1700' then
					reg_time:='0500';
				elsif reg_time='1800' then
					reg_time:='0600';
				elsif reg_time='1900' then
					reg_time:='0700';
				elsif reg_time='2000' then
					reg_time:='0800';
				elsif reg_time='2100' then
					reg_time:='0900';
				elsif reg_time='2200' then
					reg_time:='1000';
				elsif reg_time='2300' then
					reg_time:='1100';
				elsif reg_time='2400' then
					reg_time:='1200';
				end if;
				reg_time:=substr(reg_time, 1, 2)||':'||substr(reg_time, 3, 4)||' pm';
			end if;
		else
			if reg_time is null then
				reg_time:='0800';
			end if;
			reg_time:=substr(reg_time, 1, 2)||':'||substr(reg_time, 3, 4)||' am';
		end if;
		twgkfrmt.P_TableOpen('DATADISPLAY');
		twgkfrmt.P_TableRowOpen('left');
		twgkfrmt.P_TableDataLabel('You may register on or after:',calign=>'left');
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(to_char(reg_date, 'MM/DD/YYYY')),calign=>'center');
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(reg_time),calign=>'center');
		twgkfrmt.P_TableRowClose;
		twgkfrmt.P_TableClose;

		twgkfrmt.P_TableOpen('DATADISPLAY');
		twgkfrmt.P_TableRowOpen('left');
		twgkfrmt.P_TableDataLabel('Your Major Advisor is:',calign=>'left');
		if acount=0 then
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold('To Be Determined'),calign=>'left');
		else
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(stu_advr),calign=>'left');
		end if;
		twgkfrmt.P_TableRowClose;
		twgkfrmt.P_TableRowOpen('left');
		twgkfrmt.P_TableDataLabel('Academic Standing:',calign=>'left');
		if stu_astd is null then
			stu_astd:='Good Standing';
		end if;
		if stu_astd_pri='N' then
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold('Your Academic Standing is '||stu_astd||' which prevents registration.'),calign=>'left');
		else
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold('Your Academic Standing is '||stu_astd||' which permits registration.'),calign=>'left');
		end if;
		twgkfrmt.P_TableRowClose;
		twgkfrmt.P_TableRowOpen('left');
		twgkfrmt.P_TableDataLabel('Holds:',calign=>'left');
		if hcount=0 then
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold('You currently do not have any holds on your record.'),calign=>'left');
		else
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold('You currently have holds on your record that prevent registration.'),calign=>'left');
		end if;
		twgkfrmt.P_TableRowClose;
		twgkfrmt.P_TableRowOpen('left');
		twgkfrmt.P_TableDataLabel('Status:',calign=>'left');
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold('Your student status is '||st_status||' which '||reg_ind),calign=>'left');
		twgkfrmt.P_TableRowClose;
		twgkfrmt.P_TableRowOpen('left');
		twgkfrmt.P_TableDataLabel('Earned Hours:',calign=>'left');
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold('You currently have '||stu_hrs||' earned hours.'),calign=>'left');
		twgkfrmt.P_TableRowClose;
		twgkfrmt.P_TableRowOpen('left');
		twgkfrmt.P_TableDataLabel('Level:',calign=>'left');
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold('Your current level is '||stu_levl_desc||'.'),calign=>'left');
		twgkfrmt.P_TableRowClose;
		twgkfrmt.P_TableRowOpen('left');
		twgkfrmt.P_TableDataLabel('Class:',calign=>'left');
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold('Your class standing is '||stu_class||'.'),calign=>'left');
		twgkfrmt.P_TableRowClose;
		twgkfrmt.P_TableRowOpen('left');
		twgkfrmt.P_TableDataLabel('College:',calign=>'left');
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold('Your current college is '||stu_coll||'.'),calign=>'left');
		twgkfrmt.P_TableRowClose;
		twgkfrmt.P_TableRowOpen('left');
		twgkfrmt.P_TableDataLabel('Major:',calign=>'left');
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold('Your current major is '||stu_majr||'.'),calign=>'left');
		twgkfrmt.P_TableRowClose;
		twgkfrmt.P_TableRowOpen('left');
		twgkfrmt.P_TableDataLabel('Campus:',calign=>'left');
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold('Your current campus is '||stu_camp||'.'),calign=>'left');
		twgkfrmt.P_TableRowClose;
		twgkfrmt.P_TableClose;
	end if;
   
	twgkwbis.P_CloseDoc;
end LMURegTimes;

end lmuhwskrgtm;
/

show errors

whenever sqlerror continue;
drop public synonym lmuhwskrgtm;
whenever sqlerror exit rollback;
create public synonym lmuhwskrgtm for lmuhwskrgtm;
grant execute on lmuhwskrgtm to public;

set scan on