set scan off
set echo on
-- Created for Loyola Marymount University
-- By Robert M. Nitsos
-- 17-JUL-2001
-- 
-- This package creates a Banner Web page that displays hold information in the
-- Faculty Module. Faculty can see the current holds on a student record and
-- remove the hold if the student is in their college.
--
-- Modified 22-NOV-2001
-- By Robert M. Nitsos
-- Added ability to view past and future (in addition to current)holds for a
-- selected student.  Added ability for faculty to remove future holds fsfor
-- students in their college.
--
-- Modified 24-FEB-2003
-- By Robert M. Nitsos
-- Updated package to use FacWeb 5.3 UI and procedures.
--
-- Modified 03-APR-2003
-- By Robert M. Nitsos
-- Added ability for user to assign Advisor Hold to student.  User must have
-- a college assigned in sirdpcl.
--
-- Modified 21-MAR-2005
-- By Robert M. Nitsos
-- Updated package to have look and feel of 6.x version.
--

create or replace package lmuhwskhold is

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

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

procedure delete_hold (dpidm in spriden.spriden_pidm%type default null,
	dhldd in sprhold.sprhold_hldd_code%type default null,
	duser in sprhold.sprhold_user%type default null,
	dreason in sprhold.sprhold_reason%type default null,
	dfdate in sprhold.sprhold_from_date%type default null,
	dtdate in sprhold.sprhold_to_date%type default null);

procedure delete_future_hold (dpidm in spriden.spriden_pidm%type default null,
	dhldd in sprhold.sprhold_hldd_code%type default null,
	duser in sprhold.sprhold_user%type default null,
	dreason in sprhold.sprhold_reason%type default null,
	dfdate in sprhold.sprhold_from_date%type default null,
	dtdate in sprhold.sprhold_to_date%type default null);

procedure insert_ah_hold (apidm in spriden.spriden_pidm%type default null,
	areason in sprhold.sprhold_reason%type default null);

END lmuhwskhold;
/
show errors

CREATE OR REPLACE PACKAGE BODY lmuhwskhold 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;
	
/****************************** LMU Display Holds ***********************/
procedure LMUHolds (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) := '6.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);
        cnt		number;
	rcount		number;
	fac_advr_ind	varchar2(1);
	stvhlddc_rec	stvhldd%ROWTYPE;
		
	cursor allholds
	is
	select sprhold_pidm pidm,
	  sprhold_hldd_code hldd,
	  stvhldd_desc hold_desc,
	  sprhold_user userid,
	  to_char(sprhold_from_date, 'MM/DD/YYYY') fdate,
	  to_char(sprhold_to_date, 'MM/DD/YYYY') tdate,
	  sprhold_from_date from_date,
	  sprhold_to_date t_date,
	  sprhold_release_ind rel_ind,
	  sprhold_reason reason,
	  sprhold_amount_owed amt_owe,
	  sprhold_orig_code orig,
	  stvorig_desc orig_desc,
	  to_char(sprhold_activity_date, 'MM/DD/YYYY') act_date
	from sprhold,
	  stvhldd,
	  stvorig
	where sprhold_pidm = hold_stupidm
	and sprhold_hldd_code=stvhldd_code(+)
	and sprhold_orig_code=stvorig_code(+);
	
	ahrec	allholds%rowtype;

	cursor holdinfo
	is
	select sprhold_pidm pidm,
	  sprhold_hldd_code hldd,
	  stvhldd_desc hold_desc,
	  sprhold_user userid,
	  to_char(sprhold_from_date, 'MM/DD/YYYY') fdate,
	  to_char(sprhold_to_date, 'MM/DD/YYYY') tdate,
	  sprhold_from_date from_date,
	  sprhold_to_date t_date,
	  sprhold_release_ind rel_ind,
	  sprhold_reason reason,
	  sprhold_amount_owed amt_owe,
	  sprhold_orig_code orig,
	  stvorig_desc orig_desc,
	  to_char(sprhold_activity_date, 'MM/DD/YYYY') act_date
	from sprhold,
	  stvhldd,
	  stvorig
	where sprhold_pidm = hold_stupidm
	and sprhold_hldd_code=stvhldd_code(+)
	and sprhold_orig_code=stvorig_code(+)
	and trunc(sysdate) between
	  trunc(sprhold_from_date) and trunc(sprhold_to_date)
	order by hldd,
	  fdate,
	  tdate;

	hrec	holdinfo%rowtype;
	  
	cursor expholdinfo
	is
	select sprhold_pidm pidm,
	  sprhold_hldd_code hldd,
	  stvhldd_desc hold_desc,
	  sprhold_user userid,
	  to_char(sprhold_from_date, 'MM/DD/YYYY') fdate,
	  to_char(sprhold_to_date, 'MM/DD/YYYY') tdate,
	  sprhold_from_date from_date,
	  sprhold_to_date t_date,
	  sprhold_release_ind rel_ind,
	  sprhold_reason reason,
	  sprhold_amount_owed amt_owe,
	  sprhold_orig_code orig,
	  stvorig_desc orig_desc,
	  to_char(sprhold_activity_date, 'MM/DD/YYYY') act_date
	from sprhold,
	  stvhldd,
	  stvorig
	where sprhold_pidm = hold_stupidm
	and sprhold_hldd_code=stvhldd_code(+)
	and sprhold_orig_code=stvorig_code(+)
	and trunc(sysdate)>trunc(sprhold_to_date)
	order by hldd,
	  fdate,
	  tdate;

	ehrec	expholdinfo%rowtype;
	  
	cursor futureholdinfo
	is
	select sprhold_pidm pidm,
	  sprhold_hldd_code hldd,
	  stvhldd_desc hold_desc,
	  sprhold_user userid,
	  to_char(sprhold_from_date, 'MM/DD/YYYY') fdate,
	  to_char(sprhold_to_date, 'MM/DD/YYYY') tdate,
	  sprhold_from_date from_date,
	  sprhold_to_date t_date,
	  sprhold_release_ind rel_ind,
	  sprhold_reason reason,
	  sprhold_amount_owed amt_owe,
	  sprhold_orig_code orig,
	  stvorig_desc orig_desc,
	  to_char(sprhold_activity_date, 'MM/DD/YYYY') act_date
	from sprhold,
	  stvhldd,
	  stvorig
	where sprhold_pidm = hold_stupidm
	and sprhold_hldd_code=stvhldd_code(+)
	and sprhold_orig_code=stvorig_code(+)
	and trunc(sysdate)<trunc(sprhold_from_date)
	order by hldd,
	  fdate,
	  tdate;

	fhrec	futureholdinfo%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.              */
   hold_term := term;
   
   IF hold_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=> 'lmuhwskhold.LMUHolds');
       hold_term :=- twbkwbis.f_getparam(pidm, 'TERM');
       RETURN;
   END IF;
   
   /* Check to see if valid faculty member */
   IF NOT bwlkilib.F_ValidFac(hold_term, pidm) THEN
   	msg := 'You must be a valid faculty member to access this page.';
   	twbkfrmt.P_PrintMessage(msg, 'ERROR');
   	twbkfrmt.P_Paragraph(1);
   	twbkwbis.P_CloseDoc(curr_release);
   	RETURN;
   ELSE /* Indicate in GORWPRM that user is a faculty member */
   	twbkwbis.P_SetParam(pidm, 'STUFAC_IND', 'FAC');
   END IF;

   /* 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'));
      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,
      		calling_proc_name => 'lmuhwskhold.LMUHolds',
      		calling_proc_name2 => 'lmuhwskhold.LMUHolds');
      RETURN;
   END IF;
   
   fac_advr_ind := SUBSTR(bwlkilib.F_ActiveFacAdvr(pidm, hold_term), 1, 1);
	
   /* Get Totoal Number of Hold Records */
   BEGIN
      select count(*)
      into   rcount
      from   sprhold
      where  sprhold_pidm          = hold_stupidm;
   EXCEPTION WHEN OTHERS THEN
      rcount := 0;
   END;

   /* Show Information For Student */
   bwckfrmt.p_open_doc('lmuhwskhold.LMUHolds');
   twbkfrmt.P_Paragraph(1);
   
   bwcklibs.P_ConfidStudInfo(hold_stupidm, hold_term);
   twbkfrmt.P_Paragraph(2);
   
   twbkwbis.P_DispInfo('lmuhwskhold.LMUHolds','GENERAL');
   
   /* Show Error Message, if any */
   if error_mess is not null then
	htp.p('Error: '||error_mess||'<br>');
	htp.nl;
   end if;
   
   open allholds;
   fetch allholds into ahrec;
   if allholds%notfound then
   	twbkwbis.P_DispInfo('lmuhwskhold.LMUHolds','NO_HOLDS');
   else
	/* Display Box & Button to Add AH Hold */
	twbkwbis.P_DispInfo('lmuhwskhold.LMUHolds','ADD_AH_HOLD');
	twbkfrmt.P_TableOpen('DATAENTRY');
		htp.formOpen('lmuhwskhold.insert_ah_hold', 'post');
		htp.formHidden('apidm', hold_stupidm);
		twbkfrmt.P_TableRowOpen;
		twbkfrmt.P_TableDataLabel('Enter Reason for Hold: ');
		twbkfrmt.P_TableDataOpen;
		HTP.FormText('areason',30,30);
		twbkfrmt.P_TableDataClose;
		twbkfrmt.P_TableDataOpen;
		htp.formsubmit(NULL, 'Add Advisor Hold');
		twbkfrmt.P_TableDataClose;
		twbkfrmt.P_TableRowClose;
		htp.formClose;
	twbkfrmt.P_TableClose;
   
	   twbkfrmt.P_Paragraph(1);

	   cnt := 0;
   
	   open holdinfo;
	   fetch holdinfo into hrec;
	   if holdinfo%notfound then
	   	null;
	   else
	   	LOOP
	   		if cnt > 0 then
	   			fetch holdinfo into hrec;
	   		end if;
	   		EXIT WHEN holdinfo%notfound;
	   		
	   		if cnt = 0 then
			   	twbkfrmt.P_TableOpen('DATADISPLAY',
			   		cattributes	=> 'summary="This table displays current, active holds for the student."',
			   		ccaption	=> 'Current, Active Administrative Holds');
			   	twbkfrmt.P_TableRowOpen();
			   	twbkfrmt.P_TableDataHeader('Type',
			   		calign		=> 'center');
			   	twbkfrmt.P_TableDataHeader('Description',
			   		calign		=> 'left');
			   	twbkfrmt.P_TableDataHeader('From',
			   		calign		=> 'left');
			   	twbkfrmt.P_TableDataHeader('To',
			   		calign		=> 'left');
			   	twbkfrmt.P_TableDataHeader('User',
			   		calign		=> 'left');
			   	twbkfrmt.P_TableDataHeader('Reason',
			   		calign		=> 'left');
			   	twbkfrmt.P_TableDataHeader('Amount',
			   		calign		=> 'left');
			   	twbkfrmt.P_TableDataHeader('Orig',
			   		calign		=> 'left');
			   	twbkfrmt.P_TableDataHeader('Processes Affected',
			   		calign		=> 'left');
			   	twbkfrmt.P_TableDataHeader('Action',
			   		calign		=> 'center');
			   	twbkfrmt.P_TableRowClose;
	   		end if;
	   		
			if hrec.hldd='AH' or hrec.hldd='EI' then
				htp.formOpen('lmuhwskhold.delete_hold', 'post');
				htp.formHidden('dpidm', hrec.pidm);
				htp.formHidden('dhldd', hrec.hldd);
				htp.formHidden('duser', hrec.userid);
				htp.formHidden('dreason', hrec.reason);
				htp.formHidden('dfdate', hrec.from_date);
				htp.formHidden('dtdate', hrec.t_date);
			end if;
			
			twbkfrmt.P_TableRowOpen();	
			twbkfrmt.P_TableData(hrec.hldd,calign=>'center');
			twbkfrmt.P_TableData(hrec.hold_desc,calign=>'left');
			twbkfrmt.P_TableData(hrec.fdate,calign=>'left');
			twbkfrmt.P_TableData(hrec.tdate,calign=>'left');
			twbkfrmt.P_TableData(hrec.userid,calign=>'left');
			twbkfrmt.P_TableData(hrec.reason,calign=>'left');
			twbkfrmt.P_TableData(hrec.amt_owe,calign=>'left');
			twbkfrmt.P_TableData(hrec.orig_desc,calign=>'left');
			/* Display Processes Affected by Hold */
			OPEN stkhldd.stvhlddc(hrec.hldd);
			FETCH stkhldd.stvhlddc INTO stvhlddc_rec;
			CLOSE stkhldd.stvhlddc;
			twbkfrmt.P_TableDataopen ();
			IF stvhlddc_rec.stvhldd_reg_hold_ind IS NOT NULL
				OR stvhlddc_rec.stvhldd_trans_hold_ind IS NOT NULL
				OR stvhlddc_rec.stvhldd_grad_hold_ind IS NOT NULL
				OR stvhlddc_rec.stvhldd_grade_hold_ind IS NOT NULL
				OR stvhlddc_rec.stvhldd_ar_hold_ind IS NOT NULL
				OR stvhlddc_rec.stvhldd_env_hold_ind IS NOT NULL
				OR stvhlddc_rec.stvhldd_application_hold_ind IS NOT NULL
				OR stvhlddc_rec.stvhldd_compliance_hold_ind IS NOT NULL
			THEN
				IF stvhlddc_rec.stvhldd_reg_hold_ind = 'Y' THEN
					twbkfrmt.P_PrintText ('Registration');
				END IF;
				IF stvhlddc_rec.stvhldd_trans_hold_ind = 'Y' THEN
					twbkfrmt.P_PrintText ('Transcripts');
				END IF;
				IF stvhlddc_rec.stvhldd_grad_hold_ind = 'Y' THEN
					twbkfrmt.P_PrintText ('Graduation');
				END IF;
				IF stvhlddc_rec.stvhldd_grade_hold_ind = 'Y' THEN
					twbkfrmt.P_PrintText ('Grades');
				END IF;
				IF stvhlddc_rec.stvhldd_ar_hold_ind = 'Y' THEN
					twbkfrmt.P_PrintText ('Accounts Receivable');
				END IF;
				IF stvhlddc_rec.stvhldd_env_hold_ind = 'Y' THEN
					twbkfrmt.P_PrintText ('Enrollment Verification');
				END IF;
				IF stvhlddc_rec.stvhldd_application_hold_ind = 'Y' THEN
					twbkfrmt.P_PrintText ('Application');
				END IF;
				IF stvhlddc_rec.stvhldd_compliance_hold_ind = 'Y' THEN
					twbkfrmt.P_PrintText ('Degree Evaluation (CAPP)');
				END IF;
			END IF;
			twbkfrmt.P_TableDataclose;
			if hrec.hldd='AH' or hrec.hldd='EI' then
				/* Add button for Hold Removal */
				twbkfrmt.P_TableDataOpen(calign=>'center');
				htp.formSubmit(null, 'Remove');
				twbkfrmt.P_TableDataClose;
			else
				twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(null),calign=>'left');	  
			end if;
			twbkfrmt.P_TableRowClose;
			if hrec.hldd = 'AH' or hrec.hldd = 'EI' then
				htp.formClose;
			end if;
	
	   		cnt := cnt + 1;
	   	END LOOP;
	   	twbkfrmt.P_TableClose;
	   end if;
	   close holdinfo;
	   
	   /* Display Any Future Holds */
	   cnt := 0;
	   
	   open futureholdinfo;
	   fetch futureholdinfo into fhrec;
	   if futureholdinfo%notfound then
	   	null;
	   else
	   	twbkfrmt.P_Paragraph(1);
	   	LOOP
	   		if cnt > 0 then
	   			fetch futureholdinfo into fhrec;
	   		end if;
	   		EXIT WHEN futureholdinfo%notfound;
	   		
	   		if cnt = 0 then
			   	twbkfrmt.P_TableOpen('DATADISPLAY',
			   		cattributes	=> 'summary="This table displays future holds for the student."',
			   		ccaption	=> 'Future Administrative Holds - Active on "From Date"');
			   	twbkfrmt.P_TableRowOpen();
			   	twbkfrmt.P_TableDataHeader('Type',
			   		calign		=> 'center');
			   	twbkfrmt.P_TableDataHeader('Description',
			   		calign		=> 'left');
			   	twbkfrmt.P_TableDataHeader('From',
			   		calign		=> 'left');
			   	twbkfrmt.P_TableDataHeader('To',
			   		calign		=> 'left');
			   	twbkfrmt.P_TableDataHeader('User',
			   		calign		=> 'left');
			   	twbkfrmt.P_TableDataHeader('Reason',
			   		calign		=> 'left');
			   	twbkfrmt.P_TableDataHeader('Amount',
			   		calign		=> 'left');
			   	twbkfrmt.P_TableDataHeader('Orig',
			   		calign		=> 'left');
			   	twbkfrmt.P_TableDataHeader('Processes Affected',
			   		calign		=> 'left');
			   	twbkfrmt.P_TableDataHeader('Action',
			   		calign		=> 'center');
			   	twbkfrmt.P_TableRowClose;
	   		end if;
	
			if fhrec.hldd='AH' or fhrec.hldd='EI' then
				htp.formOpen('lmuhwskhold.delete_hold', 'post');
				htp.formHidden('dpidm', fhrec.pidm);
				htp.formHidden('dhldd', fhrec.hldd);
				htp.formHidden('duser', fhrec.userid);
				htp.formHidden('dreason', fhrec.reason);
				htp.formHidden('dfdate', fhrec.from_date);
				htp.formHidden('dtdate', fhrec.t_date);
			end if;
			
			twbkfrmt.P_TableRowOpen();	
			twbkfrmt.P_TableData(fhrec.hldd,calign=>'center');
			twbkfrmt.P_TableData(fhrec.hold_desc,calign=>'left');
			twbkfrmt.P_TableData(fhrec.fdate,calign=>'left');
			twbkfrmt.P_TableData(fhrec.tdate,calign=>'left');
			twbkfrmt.P_TableData(fhrec.userid,calign=>'left');
			twbkfrmt.P_TableData(fhrec.reason,calign=>'left');
			twbkfrmt.P_TableData(fhrec.amt_owe,calign=>'left');
			twbkfrmt.P_TableData(fhrec.orig_desc,calign=>'left');
			/* Display Processes Affected by Hold */
			OPEN stkhldd.stvhlddc(fhrec.hldd);
			FETCH stkhldd.stvhlddc INTO stvhlddc_rec;
			CLOSE stkhldd.stvhlddc;
			twbkfrmt.P_TableDataopen ();
			IF stvhlddc_rec.stvhldd_reg_hold_ind IS NOT NULL
				OR stvhlddc_rec.stvhldd_trans_hold_ind IS NOT NULL
				OR stvhlddc_rec.stvhldd_grad_hold_ind IS NOT NULL
				OR stvhlddc_rec.stvhldd_grade_hold_ind IS NOT NULL
				OR stvhlddc_rec.stvhldd_ar_hold_ind IS NOT NULL
				OR stvhlddc_rec.stvhldd_env_hold_ind IS NOT NULL
				OR stvhlddc_rec.stvhldd_application_hold_ind IS NOT NULL
				OR stvhlddc_rec.stvhldd_compliance_hold_ind IS NOT NULL
			THEN
				IF stvhlddc_rec.stvhldd_reg_hold_ind = 'Y' THEN
					twbkfrmt.P_PrintText ('Registration');
				END IF;
				IF stvhlddc_rec.stvhldd_trans_hold_ind = 'Y' THEN
					twbkfrmt.P_PrintText ('Transcripts');
				END IF;
				IF stvhlddc_rec.stvhldd_grad_hold_ind = 'Y' THEN
					twbkfrmt.P_PrintText ('Graduation');
				END IF;
				IF stvhlddc_rec.stvhldd_grade_hold_ind = 'Y' THEN
					twbkfrmt.P_PrintText ('Grades');
				END IF;
				IF stvhlddc_rec.stvhldd_ar_hold_ind = 'Y' THEN
					twbkfrmt.P_PrintText ('Accounts Receivable');
				END IF;
				IF stvhlddc_rec.stvhldd_env_hold_ind = 'Y' THEN
					twbkfrmt.P_PrintText ('Enrollment Verification');
				END IF;
				IF stvhlddc_rec.stvhldd_application_hold_ind = 'Y' THEN
					twbkfrmt.P_PrintText ('Application');
				END IF;
				IF stvhlddc_rec.stvhldd_compliance_hold_ind = 'Y' THEN
					twbkfrmt.P_PrintText ('Degree Evaluation (CAPP)');
				END IF;
			END IF;
			twbkfrmt.P_TableDataclose;
			if fhrec.hldd='AH' or fhrec.hldd='EI' then
				/* Add button for Hold Removal */
				twbkfrmt.P_TableDataOpen(calign=>'center');
				htp.formSubmit(null, 'Remove');
				twbkfrmt.P_TableDataClose;
			else
				twbkfrmt.P_TableData(null,calign=>'left');	  
			end if;
			twbkfrmt.P_TableRowClose;
			if fhrec.hldd = 'AH' or fhrec.hldd = 'EI' then
				htp.formClose;
			end if;
	
	   		cnt := cnt + 1;
	   	END LOOP;
	   	twbkfrmt.P_TableClose;
	   end if;
	   close futureholdinfo;
	   
	   /* Display Any Expired Holds */
	   cnt := 0;
	   
	   open expholdinfo;
	   fetch expholdinfo into ehrec;
	   if expholdinfo%notfound then
	   	null;
	   else
	   	twbkfrmt.P_Paragraph(1);
	   	LOOP
	   		if cnt > 0 then
	   			fetch expholdinfo into ehrec;
	   		end if;
	   		EXIT WHEN expholdinfo%notfound;
	   		
	   		if cnt = 0 then
			   	twbkfrmt.P_TableOpen('DATADISPLAY',
			   		cattributes	=> 'summary="This table displays expried holds for the student."',
			   		ccaption	=> 'Expired Administrative Holds - No Longer Active');
			   	twbkfrmt.P_TableRowOpen();
			   	twbkfrmt.P_TableDataHeader('Type',
			   		calign		=> 'center');
			   	twbkfrmt.P_TableDataHeader('Description',
			   		calign		=> 'left');
			   	twbkfrmt.P_TableDataHeader('From',
			   		calign		=> 'left');
			   	twbkfrmt.P_TableDataHeader('To',
			   		calign		=> 'left');
			   	twbkfrmt.P_TableDataHeader('User',
			   		calign		=> 'left');
			   	twbkfrmt.P_TableDataHeader('Reason',
			   		calign		=> 'left');
			   	twbkfrmt.P_TableDataHeader('Amount',
			   		calign		=> 'left');
			   	twbkfrmt.P_TableDataHeader('Orig',
			   		calign		=> 'left');
			   	twbkfrmt.P_TableRowClose;
	   		end if;
			
			twbkfrmt.P_TableRowOpen();	
			twbkfrmt.P_TableData(ehrec.hldd,calign=>'center');
			twbkfrmt.P_TableData(ehrec.hold_desc,calign=>'left');
			twbkfrmt.P_TableData(ehrec.fdate,calign=>'left');
			twbkfrmt.P_TableData(ehrec.tdate,calign=>'left');
			twbkfrmt.P_TableData(ehrec.userid,calign=>'left');
			twbkfrmt.P_TableData(ehrec.reason,calign=>'left');
			twbkfrmt.P_TableData(ehrec.amt_owe,calign=>'left');
			twbkfrmt.P_TableData(ehrec.orig_desc,calign=>'left');
			twbkfrmt.P_TableRowClose;
	
	   		cnt := cnt + 1;
	   	END LOOP;
	   	twbkfrmt.P_TableClose;
	   end if;
	   close expholdinfo;
   
   end if;
   close allholds;
   
   twbkfrmt.P_Paragraph(1);
   twbkwbis.P_DispInfo('lmuhwskhold.LMUHolds','FOOTER');
   
   twbkwbis.P_CloseDoc(curr_release);
end LMUHolds;

procedure delete_hold (dpidm in spriden.spriden_pidm%type default null,
	dhldd in sprhold.sprhold_hldd_code%type default null,
	duser in sprhold.sprhold_user%type default null,
	dreason in sprhold.sprhold_reason%type default null,
	dfdate in sprhold.sprhold_from_date%type default null,
	dtdate in sprhold.sprhold_to_date%type default null)
IS
	my_pidm		spriden.spriden_pidm%type;
	tmp_error	varchar2(500) default null;
	cur_user	varchar2(30) default null;
	fac_coll	varchar2(2)  default null;
	fac_coll_desc	varchar2(2)  default null;
	hold_coll	varchar2(2)  default null;
	hold_coll_desc	varchar2(30) default null;
	nrec		number;
	new_to_date	date default null;
begin
	IF NOT twbkwbis.F_ValidUser(pidm) THEN	
	  return;
	END IF;
	my_pidm := NVL(dpidm, pidm);

	new_to_date := dtdate;
	if to_char(dtdate, 'MM/DD/YYYY')='12/31/1999' then
		new_to_date := to_date('12/31/2099', 'MM/DD/YYYY');
	end if;
	
	begin
		select upper(substr(spriden_first_name, 1, 1)||substr(spriden_last_name, 1, 7))
		into cur_user
		from spriden
		where spriden_change_ind is null
		and spriden_pidm=pidm;
	exception when others then
		cur_user := 'XXX';
	end;

	select substr(duser, 4, 2)
	into hold_coll
	from dual;

	begin
		select stvcoll_desc
		into hold_coll_desc
		from stvcoll
		where stvcoll_code=hold_coll;
	exception when others then
		hold_coll_desc := 'Unknown';
	end;

	begin
		select count(*)
		into nrec
		from sirdpcl a
		where a.sirdpcl_term_code_eff=
		 (select max(b.sirdpcl_term_code_eff) from sirdpcl b
		  where a.sirdpcl_pidm=b.sirdpcl_pidm)
		and a.sirdpcl_pidm=pidm;
	exception when others then
		nrec := 0;
	end;

	if nrec=0 then
	  if cur_user<>'RNITSOS' then
	  	if hold_coll = 'ED' or hold_coll = 'FT' then
			tmp_error:='You must be an active member of the '||hold_coll_desc||' to remove this hold. ';
			tmp_error:=tmp_error||'Either refer the student to the '||hold_coll_desc||' or contact the ';
		else
	    	tmp_error:='You must be an active member of the college of '||hold_coll_desc||' to remove this hold. ';
			tmp_error:=tmp_error||'Either refer the student to the college of '||hold_coll_desc||' or contact the ';
		end if;
	    tmp_error:=tmp_error||'Office of the Registrar.';
	  else
	    if dpidm is not null then
	      /* Attempt to update hold record with yesterday's date */
	      begin
			update sprhold
				set sprhold_to_date=sysdate-1,
				  sprhold_activity_date=sysdate
	        where sprhold_pidm=dpidm
	        and sprhold_hldd_code=dhldd
			and sprhold_user=duser
			and sprhold_reason=dreason
			and to_char(sprhold_from_date, 'MM/DD/YYYY')=to_char(dfdate, 'MM/DD/YYYY')
			and to_char(sprhold_to_date, 'MM/DD/YYYY')=to_char(new_to_date, 'MM/DD/YYYY');
	      exception when others then
	        tmp_error:='Could not delete hold record.';
	      end;
		  commit;
	    end if;
	  end if;
	else
	  begin
		  select a.sirdpcl_coll_code
		  into fac_coll
		  from sirdpcl a
		  where a.sirdpcl_term_code_eff=
		   (select max(b.sirdpcl_term_code_eff) from sirdpcl b
		    where a.sirdpcl_pidm=b.sirdpcl_pidm)
		  and a.sirdpcl_home_ind='Y'
		  and a.sirdpcl_pidm=pidm;
	  exception when others then
	  	fac_coll := 'XX';
	  end;

	  if hold_coll<>fac_coll then
	  	if hold_coll = 'ED' or hold_coll = 'FT' then
			tmp_error:='You must be an active member of the '||hold_coll_desc||' to remove this hold. ';
			tmp_error:=tmp_error||'Either refer the student to the '||hold_coll_desc||' or contact the ';
		else
	    	tmp_error:='You must be an active member of the college of '||hold_coll_desc||' to remove this hold. ';
			tmp_error:=tmp_error||'Either refer the student to the college of '||hold_coll_desc||' or contact the ';
		end if;
	    tmp_error:=tmp_error||'Office of the Registrar.';
	  else
	    if dpidm is not null then
	      /* Attempt to update hold record with yesterday's date */
	      begin
			update sprhold
				set sprhold_to_date=sysdate-1,
				  sprhold_activity_date=sysdate
	        where sprhold_pidm=dpidm
	        and sprhold_hldd_code=dhldd
			and sprhold_user=duser
			and sprhold_reason=dreason
			and to_char(sprhold_from_date, 'MM/DD/YYYY')=to_char(dfdate, 'MM/DD/YYYY')
			and to_char(sprhold_to_date, 'MM/DD/YYYY')=to_char(new_to_date, 'MM/DD/YYYY');
	      exception when others then
	        tmp_error:='Could not update hold record.';
	      end;
	    end if;
	  end if;
	end if;

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

procedure delete_future_hold (dpidm in spriden.spriden_pidm%type default null,
	dhldd in sprhold.sprhold_hldd_code%type default null,
	duser in sprhold.sprhold_user%type default null,
	dreason in sprhold.sprhold_reason%type default null,
	dfdate in sprhold.sprhold_from_date%type default null,
	dtdate in sprhold.sprhold_to_date%type default null)
IS
	my_pidm		spriden.spriden_pidm%type;
	tmp_error	varchar2(500) default null;
	cur_user	varchar2(30) default null;
	fac_coll	varchar2(2)  default null;
	fac_coll_desc	varchar2(2)  default null;
	hold_coll	varchar2(2)  default null;
	hold_coll_desc	varchar2(30) default null;
	nrec		number;
	new_to_date	date default null;
begin
	IF NOT twbkwbis.F_ValidUser(pidm) THEN	
	  return;
	END IF;
	my_pidm := NVL(dpidm, pidm);

	new_to_date := dtdate;
	if to_char(dtdate, 'MM/DD/YYYY')='12/31/1999' then
		new_to_date := to_date('12/31/2099', 'MM/DD/YYYY');
	end if;

	select upper(substr(spriden_first_name, 1, 1)||substr(spriden_last_name, 1, 7))
	into cur_user
	from spriden
	where spriden_change_ind is null
	and spriden_pidm=pidm;

	select substr(duser, 4, 2)
	into hold_coll
	from dual;

	begin
		select stvcoll_desc
		into hold_coll_desc
		from stvcoll
		where stvcoll_code=hold_coll;
	exception when others then
		hold_coll_desc:='Unknown';
	end;

	begin
		select count(*)
		into nrec
		from sirdpcl a
		where a.sirdpcl_term_code_eff=
		 (select max(b.sirdpcl_term_code_eff) from sirdpcl b
		  where a.sirdpcl_pidm=b.sirdpcl_pidm)
		and a.sirdpcl_pidm=pidm;
	exception when others then
		nrec := 0;
	end;

	if nrec=0 then
	  if cur_user<>'RNITSOS' then
	  	if hold_coll = 'ED' or hold_coll = 'FT' then
			tmp_error:='You must be an active member of the '||hold_coll_desc||' to remove this hold. ';
			tmp_error:=tmp_error||'Either refer the student to the '||hold_coll_desc||' or contact the ';
		else
	    	tmp_error:='You must be an active member of the college of '||hold_coll_desc||' to remove this hold. ';
			tmp_error:=tmp_error||'Either refer the student to the college of '||hold_coll_desc||' or contact the ';
		end if;
	    tmp_error:=tmp_error||'Office of the Registrar.';
	  else
	    if dpidm is not null then
	      /* Attempt to update hold record with yesterday's date */
	      begin
			update sprhold
			set sprhold_from_date=sysdate-2,
			  sprhold_to_date=sysdate-1,
			  sprhold_activity_date=sysdate
	    	    where sprhold_pidm=dpidm
	    	    and sprhold_hldd_code=dhldd
			and sprhold_user=duser
			and sprhold_reason=dreason
			and to_char(sprhold_from_date, 'MM/DD/YYYY')=to_char(dfdate, 'MM/DD/YYYY')
			and to_char(sprhold_to_date, 'MM/DD/YYYY')=to_char(new_to_date, 'MM/DD/YYYY');
			commit;
	      exception when others then
	        tmp_error:='Could not update hold record.';
			rollback;
	      end;
	    end if;
	  end if;
	else
	  begin
		  select a.sirdpcl_coll_code
		  into fac_coll
		  from sirdpcl a
		  where a.sirdpcl_term_code_eff=
		   (select max(b.sirdpcl_term_code_eff) from sirdpcl b
		    where a.sirdpcl_pidm=b.sirdpcl_pidm)
		  and a.sirdpcl_home_ind='Y'
		  and a.sirdpcl_pidm=pidm;
	  exception when others then
	  	fac_coll := 'XX';
	  end;

	  if hold_coll<>fac_coll then
	  	if hold_coll = 'ED' or hold_coll = 'FT' then
			tmp_error:='You must be an active member of the '||hold_coll_desc||' to remove this hold. ';
			tmp_error:=tmp_error||'Either refer the student to the '||hold_coll_desc||' or contact the ';
		else
	    	tmp_error:='You must be an active member of the college of '||hold_coll_desc||' to remove this hold. ';
			tmp_error:=tmp_error||'Either refer the student to the college of '||hold_coll_desc||' or contact the ';
		end if;
	    tmp_error:=tmp_error||'Office of the Registrar.';
	  else
	    if dpidm is not null then
	      /* Attempt to update hold record with yesterday's date */
	      begin
			update sprhold
			  set sprhold_from_date=sysdate-2,
			  sprhold_to_date=sysdate-1,
			  sprhold_activity_date=sysdate
	    	where sprhold_pidm=dpidm
	    	and sprhold_hldd_code=dhldd
			and sprhold_user=duser
			and sprhold_reason=dreason
			and to_char(sprhold_from_date, 'MM/DD/YYYY')=to_char(dfdate, 'MM/DD/YYYY')
			and to_char(sprhold_to_date, 'MM/DD/YYYY')=to_char(new_to_date, 'MM/DD/YYYY');
			commit;
	      exception when others then
	        tmp_error:='Could not update hold record.';
			rollback;
	      end;
	    end if;
	  end if;
	end if;

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

procedure insert_ah_hold (apidm in spriden.spriden_pidm%type default null,
	areason in sprhold.sprhold_reason%type default null)
is

	my_pidm		spriden.spriden_pidm%type;
	tmp_error	varchar2(500) default null;
	cur_user	varchar2(30) default null;
	fac_coll	varchar2(2)  default null;
	fac_coll_desc	varchar2(2)  default null;
	hold_coll	varchar2(2)  default null;
	hold_coll_desc	varchar2(30) default null;
	nrec		number;
	new_to_date	date default null;
	userid		sprhold.sprhold_user%type;

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

	/* check to see if user is valid faculty member */
	begin
		select upper(substr(spriden_first_name, 1, 1)||substr(spriden_last_name, 1, 7))
		into cur_user
		from spriden
		where spriden_change_ind is null
		and spriden_pidm=pidm;
	exception when others then
		cur_user := 'Unknown';
	end;
	
	begin
		select a.sirdpcl_coll_code
		into fac_coll
		from sirdpcl a
		where a.sirdpcl_term_code_eff=
			(select max(b.sirdpcl_term_code_eff) from sirdpcl b
			 where a.sirdpcl_pidm=b.sirdpcl_pidm)
		and a.sirdpcl_home_ind = 'Y'
		and a.sirdpcl_pidm=pidm;
	exception when others then
		fac_coll := null;
	end;
	if cur_user = 'RNITSOS' then
		fac_coll := 'SE';
	end if;
	
	if areason is null then
		tmp_error := 'You must enter a reason for the hold.';
	else
		tmp_error := null;
	end if;
	
  if tmp_error is null then
	if fac_coll is null then
		tmp_error := 'You must be a valid faculty member assigned to one of the schools or colleges to add an Advisor hold.  '||
			'Please contact the Registrar''s office if you believe you are receiving this message in error.';
	else
		if fac_coll not in ('BA', 'CF', 'ED', 'FT', 'LA', 'SE') then
		tmp_error := 'You must be a valid faculty member assigned to one of the schools or colleges to add an Advisor hold.  '||
			'Please contact the Registrar''s office if you believe you are receiving this message in error.';
		else
			userid := 'REG'||fac_coll;
			if areason is null then
				tmp_error := 'Please provide a reason for the hold in the box below.';
			else
				/* Check if any existing AH Holds */
				begin
					select count(*)
					into nrec
					from sprhold
					where sprhold_pidm = my_pidm
					and sprhold_hldd_code = 'AH'
					and trunc(sysdate) <= trunc(sprhold_to_date);
				exception when others then
					nrec := 0;
				end;
				if nrec = 0 then
					/* Insert Hold */
					begin
						insert into sprhold
						(sprhold_pidm, sprhold_hldd_code, sprhold_user, sprhold_from_date, sprhold_to_date,
						 sprhold_release_ind, sprhold_reason, sprhold_activity_date)
						 values(my_pidm, 'AH', userid, sysdate, to_date('12/31/2099', 'MM/DD/YYYY'),
						 		'Y', areason, sysdate);
					exception when others then
						tmp_error := 'Error inserting hold.  Please contact the office of the Registrar.';
					end;
				else
					tmp_error := 'Unable to insert - an Advisor Hold already exists for this student.';
				end if;
			end if;
		end if;
	end if;
  end if;
	
	LMUHolds(STUPIDM=>my_pidm, error_mess=>tmp_error);
end insert_ah_hold;

end lmuhwskhold;
/

show errors

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

set scan on