set scan off
set echo on
-- Created for Loyola Marymount University
-- By Robert M. Nitsos
-- 13-JUL-2001
-- 
-- This package creates a Banner Web page that allows faculty members to view
-- and update course overrides for students. This page is located in the
-- Faculty Module. Faculty can see the current overrides for a selected student
-- and add additional or remove existing overrides.
--
-- Modified 24-FEB-2003
-- By Robert M. Nitsos
-- Updated package to use FacWeb 5.3 UI and procedures.
--

create or replace package lmuhwsksrpo is

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

procedure LMUOverride (STUPIDM in spriden.spriden_pidm%type default null,
					    term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL,
                        error_mess in varchar2 default null);

procedure delete_override (dpidm in spriden.spriden_pidm%type default null,
	dterm in stvterm.stvterm_code%type default null,
	drovr in sfrsrpo.sfrsrpo_rovr_code%type default null,
	dcrn in sfrsrpo.sfrsrpo_crn%type default null,
	dsubj in sfrsrpo.sfrsrpo_subj_code%type default null,
	dcrs in sfrsrpo.sfrsrpo_crse_numb%type default null,
	dsec in sfrsrpo.sfrsrpo_seq_numb%type default null,
	duser in sfrsrpo.sfrsrpo_user%type default null);

procedure add_override (apidm in spriden.spriden_pidm%type default null,
	aterm in stvterm.stvterm_code%type default null,
	arovr in sfrsrpo.sfrsrpo_rovr_code%type default null,
	acrn in sfrsrpo.sfrsrpo_crn%type default null,
	asubj in sfrsrpo.sfrsrpo_subj_code%type default null,
	acrs in sfrsrpo.sfrsrpo_crse_numb%type default null,
	asec in sfrsrpo.sfrsrpo_seq_numb%type default null,
	auser in sfrsrpo.sfrsrpo_user%type default null);

END lmuhwsksrpo;
/
show errors

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

  /* Global type and variable declarations for package */
/* UI MOD */
 /* The following broken line is required */
    NEWLINE      constant varchar2(1) := '
';
    pidm spriden.spriden_pidm%TYPE;
	row_count NUMBER;
	
/****************************** LMU Display Course Overrides ***********************/
procedure LMUOverride (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) := '3.2';
	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);
        msg			varchar2(255);
	tmp_tdesc		stvterm.stvterm_desc%type;
        term_rec		stvterm%rowtype;
	rcount			number;
	
	cursor or_codes
	is
	select sfrrovr_rovr_code rovr_code,
	  stvrovr_desc rovr_desc
	from sfrrovr, stvrovr
	where sfrrovr_term_code = hold_term
	and sfrrovr_rovr_code = stvrovr_code(+)
	order by rovr_desc;
		
   cursor or_info
   is
   select sfrsrpo_pidm po_pidm,
     sfrsrpo_term_code po_term,
     sfrsrpo_rovr_code rovr_code,
     stvrovr_desc override,
     sfrsrpo_crn crn,
     sfrsrpo_subj_code subj,
     sfrsrpo_crse_numb crs,
     sfrsrpo_seq_numb sec,
     sfrsrpo_user userid,
     sfrsrpo_activity_date adate
   from sfrsrpo,
     stvrovr
   where sfrsrpo_pidm=hold_stupidm
   and sfrsrpo_term_code=hold_term
   and sfrsrpo_rovr_code=stvrovr_code(+)
   order by subj, crs, rovr_code, sec, adate;

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=> 'lmuhwsksrpo.LMUOverride');
       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, 'lmuhwsksrpo.LMUOverride');
      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('lmuhwsksrpo.LMUOverride',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   sfrsrpo
      where  sfrsrpo_pidm          = hold_stupidm
      and    sfrsrpo_term_code     = hold_term;

   EXCEPTION WHEN OTHERS THEN
      rcount := 0;
   END;

   select stvterm_desc
   into tmp_tdesc
   from stvterm
   where stvterm_code=hold_term;

   /* Show Existing Overrides For Student (If Any) */
   twbkwbis.P_OpenDoc('lmuhwsksrpo.LMUOverride',header_text=>
                      'For: '||
                      f_format_name(hold_stupidm, 'FMIL')||' ('||tmp_tdesc||')');
   twbkwbis.P_DispInfo('lmuhwsksrpo.LMUOverride','GENERAL');

   if rcount=0 then
	twbkwbis.P_DispInfo('lmuhwsksrpo.LMUOverride','NORECS');
   else
   twbkfrmt.P_TableOpen('DATADISPLAY');
   twbkfrmt.P_TableRowOpen('left');
   twbkfrmt.P_TableDataLabel('OVERRIDE',calign=>'left',ccolspan=>'2');
   twbkfrmt.P_TableDataLabel('CODE',calign=>'center');
   twbkfrmt.P_TableDataLabel('CRN',calign=>'left');
   twbkfrmt.P_TableDataLabel('SUBJ',calign=>'left');
   twbkfrmt.P_TableDataLabel('CRS',calign=>'left');
   twbkfrmt.P_TableDataLabel('SEC',calign=>'left');
   twbkfrmt.P_TableDataLabel('USER',calign=>'left');
   twbkfrmt.P_TableDataLabel('DATE',calign=>'left');
   twbkfrmt.P_TableDataLabel('ACTION',calign=>'center');
   twbkfrmt.P_TableRowClose;

   FOR mystuff IN or_info
   LOOP
	htp.formOpen('lmuhwsksrpo.delete_override', 'post');
	htp.formHidden('dpidm', mystuff.po_pidm);
	htp.formHidden('drovr', mystuff.rovr_code);
	htp.formHidden('dterm', mystuff.po_term);
	htp.formHidden('dcrn', mystuff.crn);
	htp.formHidden('dsubj', mystuff.subj);
	htp.formHidden('dcrs', mystuff.crs);
	htp.formHidden('dsec', mystuff.sec);
	htp.formHidden('duser', mystuff.userid);

      twbkfrmt.P_TableRowOpen('left');

      twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.override),calign=>'left',ccolspan=>'2');
      twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.rovr_code),calign=>'center');
      twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.crn),calign=>'left');
      twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.subj),calign=>'left');
      twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.crs),calign=>'left');
      twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.sec),calign=>'left');
      twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.userid),calign=>'left');
      twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.adate),calign=>'left');

	twbkfrmt.P_TableDataOpen(calign=>'center');
	htp.formSubmit(null, 'Remove');
	twbkfrmt.P_TableDataClose;
  
      twbkfrmt.P_TableRowClose;
	htp.formClose;
   END LOOP;
   twbkfrmt.P_TableClose;

   htp.nl;
   htp.nl;
   end if;

   twbkwbis.P_DispInfo('lmuhwsksrpo.LMUOverride','DEFAULT');

   if error_mess is not null then
      htp.p('Error: '||error_mess||'<br>');
   end if;

   htp.nl;

   /* Add Fields for New Override */
   htp.formOpen('lmuhwsksrpo.add_override', 'post');
   htp.formHidden('apidm', hold_stupidm);
   htp.formHidden('aterm', hold_term);
   twbkfrmt.P_TableOpen('DATAENTRY');
   twbkfrmt.P_TableRowOpen;
   twbkfrmt.P_TableDataLabel('Select Override: ');
   twbkfrmt.P_TableDataOpen;
   htp.formSelectOpen('arovr',NULL,1);
   row_count := 0;
   for mystuff in or_codes
   loop
   	IF or_codes%rowcount = 1 THEN
   		htp.p(twbkwbis.F_formSelectOption(mystuff.rovr_desc, mystuff.rovr_code, 'SELECTED'));
	ELSE
		htp.p(twbkwbis.F_formSelectOption(mystuff.rovr_desc, mystuff.rovr_code));
	END IF;
   /* htp.p(twbkwbis.F_formSelectOption('Capacity', 'A', 'SELECTED')); */
   /* htp.p(twbkwbis.F_formSelectOption('Links', 'B')); */
   /* htp.p(twbkwbis.F_formSelectOption('Co-Requisites', 'C')); */
   /* htp.p(twbkwbis.F_formSelectOption('Pre-Requisites', 'D')); */
   /* htp.p(twbkwbis.F_formSelectOption('Special Approval', 'F')); */
   /* htp.p(twbkwbis.F_formSelectOption('Major', 'G')); */
   /* htp.p(twbkwbis.F_formSelectOption('College', 'H')); */
   /* htp.p(twbkwbis.F_formSelectOption('Class', 'J')); */
   end loop;
   htp.formSelectClose;
   twbkfrmt.P_TableRowClose;
   twbkfrmt.P_TableRowOpen;
   twbkfrmt.P_TableDataLabel('CRN: ');
   twbkfrmt.P_TableDataOpen;
   HTP.FormText('acrn',5,5);
   twbkfrmt.P_TableDataClose;
   twbkfrmt.P_TableRowClose;
   twbkfrmt.P_TableRowOpen;
   twbkfrmt.P_TableDataLabel('SUBJ: ');
   twbkfrmt.P_TableDataOpen;
   HTP.FormText('asubj',4,4);
   twbkfrmt.P_TableDataClose;
   twbkfrmt.P_TableRowClose;
   twbkfrmt.P_TableRowOpen;
   twbkfrmt.P_TableDataLabel('CRS: ');
   twbkfrmt.P_TableDataOpen;
   HTP.FormText('acrs',3,3);
   twbkfrmt.P_TableDataClose;
   twbkfrmt.P_TableRowClose;
   twbkfrmt.P_TableRowOpen;
   twbkfrmt.P_TableDataLabel('SEC: ');
   twbkfrmt.P_TableDataOpen;
   HTP.FormText('asec',2,2);
   twbkfrmt.P_TableDataClose;
   twbkfrmt.P_TableRowClose;
   twbkfrmt.P_TableClose;

   htp.nl;
   htp.nl;

   htp.formsubmit(NULL, 'Add Override');
   htp.formreset('Reset');

   htp.formClose;
   twbkfrmt.P_TableDataClose;

   twbkwbis.P_CloseDoc;
end LMUOverride;

/******************* Delete Override Record from SFRSRPO ******************/
procedure delete_override(dpidm in spriden.spriden_pidm%type default null,
	dterm in stvterm.stvterm_code%type default null,
	drovr in sfrsrpo.sfrsrpo_rovr_code%type default null,
	dcrn in sfrsrpo.sfrsrpo_crn%type default null,
	dsubj in sfrsrpo.sfrsrpo_subj_code%type default null,
	dcrs in sfrsrpo.sfrsrpo_crse_numb%type default null,
	dsec in sfrsrpo.sfrsrpo_seq_numb%type default null,
	duser in sfrsrpo.sfrsrpo_user%type default null)

IS
   my_pidm spriden.spriden_pidm%type;
begin
   IF NOT twbkwbis.F_ValidUser(pidm) THEN
        return;
   END IF;
   my_pidm := NVL(dpidm, pidm);

   if dpidm is not null then
	if dcrn is not null then
	  /* delete record using crn */
	  delete from sfrsrpo
	  where sfrsrpo_pidm=dpidm
	  and sfrsrpo_term_code=dterm
	  and sfrsrpo_rovr_code=drovr
	  and sfrsrpo_crn=dcrn;
	else
	  /* delete record using subj and crs */
	  delete from sfrsrpo
	  where sfrsrpo_pidm=dpidm
	  and sfrsrpo_term_code=dterm
	  and sfrsrpo_rovr_code=drovr
	  and sfrsrpo_crn is null
	  and sfrsrpo_subj_code=dsubj
	  and sfrsrpo_crse_numb=dcrs;
	end if;
   end if;

   LMUOverride(STUPIDM=>my_pidm);
   return;
end;

/******************* Add Override Record to SFRSRPO ******************/
procedure add_override(apidm in spriden.spriden_pidm%type default null,
	aterm in stvterm.stvterm_code%type default null,
	arovr in sfrsrpo.sfrsrpo_rovr_code%type default null,
	acrn in sfrsrpo.sfrsrpo_crn%type default null,
	asubj in sfrsrpo.sfrsrpo_subj_code%type default null,
	acrs in sfrsrpo.sfrsrpo_crse_numb%type default null,
	asec in sfrsrpo.sfrsrpo_seq_numb%type default null,
	auser in sfrsrpo.sfrsrpo_user%type default null)

IS
   my_pidm	spriden.spriden_pidm%type;
   tmp_rovr	stvrovr.stvrovr_desc%type;
   tmp_crn	ssbsect.ssbsect_crn%type;
   tmp_subj	ssbsect.ssbsect_subj_code%type;
   tmp_crs	ssbsect.ssbsect_crse_numb%type;
   tmp_sec	ssbsect.ssbsect_seq_numb%type;
   tmp_user	varchar2(30);
   tmp_error	varchar2(200);
   nrec		number;

begin
   IF NOT twbkwbis.F_ValidUser(pidm) THEN
        return;
   END IF;
   my_pidm := NVL(apidm, pidm);

   /* get user name from current user */
   select upper(substr(spriden_first_name, 1, 1)||substr(spriden_last_name, 1, 7))
   into tmp_user
   from spriden
   where spriden_change_ind is null
   and spriden_pidm=pidm;

   if apidm is not null then
	if acrn is not null then
	  /* insert record using crn */

	  select count(*)
	  into nrec
	  from ssbsect
	  where ssbsect_term_code=aterm
	  and ssbsect_crn=acrn;

	  if nrec=0 then
	    tmp_error:='Invalid CRN entered for term.';
	  else
	    /* get course info */
	    select ssbsect_subj_code,
	      ssbsect_crse_numb,
	      ssbsect_seq_numb
	    into tmp_subj, tmp_crs, tmp_sec
	    from ssbsect
	    where ssbsect_term_code=aterm
	    and ssbsect_crn=acrn;

	    if tmp_subj is null then
	      tmp_error:='Invalid CRN entered for term.';
	    else
	      /* check if SUBJ CRS already in table */
	      select count(*)
	      into nrec
	      from sfrsrpo
	      where sfrsrpo_term_code=aterm
	      and sfrsrpo_pidm=apidm
	      and sfrsrpo_subj_code=tmp_subj
	      and sfrsrpo_crse_numb=tmp_crs
	      and sfrsrpo_rovr_code=arovr;

	      if nrec>0 then
		select stvrovr_desc
		into tmp_rovr
		from stvrovr
		where stvrovr_code=arovr;

		tmp_error:=tmp_rovr||' override already entered for '||tmp_subj||' '||tmp_crs||'.';
	      else
		/* insert record */
		insert into sfrsrpo
		(sfrsrpo_term_code, sfrsrpo_pidm, sfrsrpo_rovr_code, sfrsrpo_subj_code,
		 sfrsrpo_crse_numb, sfrsrpo_activity_date, sfrsrpo_seq_numb, sfrsrpo_crn,
		 sfrsrpo_user)
		values(aterm, apidm, arovr, tmp_subj, tmp_crs, sysdate, tmp_sec, acrn, tmp_user);
	      end if;
	    end if;
	  end if;
	else
	  if asec is not null then

	    select count(*)
	    into nrec
	    from ssbsect
	    where ssbsect_term_code=aterm
	    and ssbsect_subj_code=upper(asubj)
	    and ssbsect_crse_numb=upper(acrs)
	    and ssbsect_seq_numb=upper(asec);

	    if nrec=0 then
	      tmp_error:='Invalid SUBJ, CRS, SEC combination entered for term.';
	    else
	      /* get specific course info and insert into sfrsrpo */
	      select ssbsect_crn
	      into tmp_crn
	      from ssbsect
	      where ssbsect_term_code=aterm
	      and ssbsect_subj_code=upper(asubj)
	      and ssbsect_crse_numb=upper(acrs)
	      and ssbsect_seq_numb=upper(asec);

	      if tmp_crn is null then
	        tmp_error:='Invalid SUBJ, CRS, SEC combination entered for term.';
	      else
		/* check if SUBJ CRS already in table */
		select count(*)
		into nrec
		from sfrsrpo
		where sfrsrpo_term_code=aterm
		and sfrsrpo_pidm=apidm
		and sfrsrpo_subj_code=upper(asubj)
		and sfrsrpo_crse_numb=upper(acrs)
		and sfrsrpo_rovr_code=arovr;

		if nrec>0 then
		  select stvrovr_desc
		  into tmp_rovr
		  from stvrovr
		  where stvrovr_code=arovr;

		  tmp_error:=tmp_rovr||' override already entered for '||upper(asubj)||' '||upper(acrs)||'.';
		else
		  /* insert record */
		  insert into sfrsrpo
		  (sfrsrpo_term_code, sfrsrpo_pidm, sfrsrpo_rovr_code, sfrsrpo_subj_code,
		   sfrsrpo_crse_numb, sfrsrpo_activity_date, sfrsrpo_seq_numb, sfrsrpo_crn,
		   sfrsrpo_user)
		  values(aterm, apidm, arovr, upper(asubj), upper(acrs), sysdate, upper(asec), tmp_crn, tmp_user);
		end if;
	      end if;
	    end if;
	  else
	    if asubj is null then
	      tmp_error:='SUBJ/CRS or CRN field must be entered.';
	    else
	      /* Make sure SUBJ exists */
	      select count(*)
	      into nrec
	      from ssbsect
	      where ssbsect_term_code=aterm
	      and ssbsect_subj_code=upper(asubj);

	      if nrec=0 then
	        tmp_error:='Invalid SUBJ code entered for term.';
	      else
		if acrs is not null then
		  select count(*)
		  into nrec
		  from ssbsect
		  where ssbsect_term_code=aterm
		  and ssbsect_subj_code=upper(asubj)
		  and ssbsect_crse_numb=upper(acrs);

		  if nrec=0 then
		    tmp_error:='Invalid SUBJ and CRS combination entered for term.';
		  else
		    /* check if SUBJ CRS already in table */
		    select count(*)
		    into nrec
		    from sfrsrpo
		    where sfrsrpo_term_code=aterm
		    and sfrsrpo_pidm=apidm
		    and sfrsrpo_subj_code=upper(asubj)
		    and sfrsrpo_crse_numb=upper(acrs)
		    and sfrsrpo_rovr_code=arovr;

		    if nrec>0 then
		      select stvrovr_desc
		      into tmp_rovr
		      from stvrovr
		      where stvrovr_code=arovr;

		      tmp_error:=tmp_rovr||' override already entered for '||upper(asubj)||' '||upper(acrs)||'.';
		    else
	              /* insert record using subj and crs */
	              insert into sfrsrpo
	              (sfrsrpo_term_code, sfrsrpo_pidm, sfrsrpo_rovr_code, sfrsrpo_subj_code,
	               sfrsrpo_crse_numb, sfrsrpo_activity_date, sfrsrpo_seq_numb, sfrsrpo_crn,
                       sfrsrpo_user)
	              values(aterm, apidm, arovr, upper(asubj), upper(acrs), sysdate, null, null, tmp_user);
		    end if;
		  end if;
		else
	          tmp_error:='SUBJ and CRS fields required for term.';
		end if;
	      end if;
	    end if;
	  end if;
	end if;
   end if;

   LMUOverride(STUPIDM=>my_pidm, error_mess=>tmp_error);
   return;
end;

end lmuhwsksrpo;
/

show errors

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

set scan on