set scan off
set echo on
-- Created for Loyola Marymount University
-- By Robert M. Nitsos
-- 16-OCT-2001
-- 
-- This package creates a Banner Web page that allows the user to search
-- for a person using any combination of ID, last name, first name or middle
-- initial.  This package is available in the Faculty Web module.
--

create or replace package lmuhwskiden is

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

procedure LMU_ID_Search (stuid in spriden.spriden_id%type default null,
	stulname in spriden.spriden_last_name%type default null,
	stufname in spriden.spriden_first_name%type default null,
	stumi in spriden.spriden_mi%type default null,
	firstsrch in varchar2 default 'Y',
	term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL,
	error_mess in varchar2 default null);

procedure select_id (spidm in spriden.spriden_pidm%type default null);

END lmuhwskiden;
/
show errors

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

  /* Global type and variable declarations for package */

    pidm spriden.spriden_pidm%TYPE;
	
/****************************** LMU Person Search ***********************/
procedure LMU_ID_Search (stuid in spriden.spriden_id%type default null,
	stulname in spriden.spriden_last_name%type default null,
	stufname in spriden.spriden_first_name%type default null,
	stumi in spriden.spriden_mi%type default null,
	firstsrch in varchar2 default 'Y',
	term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL,
	error_mess in varchar2 default null)
is
	curr_release  varchar2(10) := '3.2';
	studob			spbpers.spbpers_birth_date%type default null;
	hold_term     stvterm.stvterm_code%TYPE;
    msg           varchar2(255);
	rcount		  number;
		
	cursor personinfo
	is
	select spriden_pidm pidm,
	  spriden_id id,
	  spriden_last_name lname,
	  spriden_first_name fname,
	  spriden_mi mi,
	  spriden_change_ind chg,
	  spbpers_birth_date dob
	from spriden,
	  spbpers
	where spriden_pidm=spbpers_pidm
	and spriden_entity_ind='P'
	and spriden_id like stuid||'%'
	and upper(spriden_last_name) like upper(stulname||'%')
	and upper(spriden_first_name) like upper(stufname||'%')
	and nvl(upper(spriden_mi), 'X') like upper(stumi||'%')
	and firstsrch<>'Y'
	order by lname,
	  fname,
	  mi,
	  id;

begin

   IF NOT twgkwbis.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 := twgkwbis.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
       twgkwbis.P_SetParam(pidm,'TERM',term);
       hold_term := term;
   END IF;
   /* Make sure a term has been selected */
   IF hold_term is null THEN
       hwskostm.P_FacSelTerm(calling_proc_name=> 'lmuhwskiden.LMU_ID_Search');
       RETURN;
   END IF;

   /* set paramater to indicate we are a faculty user */

   twgkwbis.P_SetParam(pidm,'STUFAC_IND','FAC');

   /*  If the user is not a valid faculty member for the selected */
   /*   term, print a message, close the page, and exit.          */
   IF NOT HWSKILIB.F_ValidFac(hold_term,pidm) THEN
      twgkwbis.P_OpenDoc('lmuhwskiden.LMU_ID_Search',header_text=>
                      '*** Invalid Faculty ID ***');
      msg := 'You must be a valid faculty member for the selected term'||
              ' to access this page.';
      HTP.hr;
      twgkfrmt.P_PrintImage(twgklibs.twgbwrul_rec.twgbwrul_error_gif);
      HTP.bold(msg);
      HTP.para;
      TWGKWBIS.P_CloseDoc(curr_release);
      RETURN;
   END IF;

	twgkwbis.P_OpenDoc('lmuhwskiden.LMU_ID_Search',header_text=>'');
	
	/* Put your instructions for the page in the Information Text vairable HOWTO */
	twgkwbis.P_DispInfo('lmuhwskiden.LMU_ID_Search','HOWTO');
	
	/* Put any other information in the Information Text variable DEFAULT */
	twgkwbis.P_DispInfo('lmuhwskiden.LMU_ID_Search','DEFAULT');

	/* Show Search Criteria Boxes */
	htp.formOpen('lmuhwskiden.LMU_ID_Search', 'post');
   	twgkfrmt.P_TableOpen('DATADISPLAY');
   	twgkfrmt.P_TableRowOpen('left');
   	twgkfrmt.P_TableDataLabel('ID',calign=>'left');
   	twgkfrmt.P_TableDataLabel('Last Name',calign=>'left');
   	twgkfrmt.P_TableDataLabel('First Name',calign=>'left');
   	twgkfrmt.P_TableDataLabel('Middle Name',calign=>'left');
	twgkfrmt.P_TableDataLabel('',calign=>'left');
   	twgkfrmt.P_TableRowClose;
	twgkfrmt.P_TableRowOpen('left');	
	twgkfrmt.P_TableDataOpen(calign=>'left');
	htp.formtext('stuid', 9, 9);
	twgkfrmt.P_TableDataClose;
	twgkfrmt.P_TableDataOpen(calign=>'left');
	htp.formtext('stulname', 60, 60);
	twgkfrmt.P_TableDataClose;
	twgkfrmt.P_TableDataOpen(calign=>'left');
	htp.formtext('stufname', 15, 15);
	twgkfrmt.P_TableDataClose;
	twgkfrmt.P_TableDataOpen(calign=>'left');
	htp.formtext('stumi', 15, 15);
	twgkfrmt.P_TableDataClose;
	twgkfrmt.P_TableDataOpen(calign=>'left');
	htp.formhidden('firstsrch', 'N');
	htp.formSubmit(null, 'Search');
	twgkfrmt.P_TableDataClose;
	twgkfrmt.P_TableRowClose;
	htp.formClose;
	twgkfrmt.P_TableClose;

	/* Show any error message(s) */
   	if error_mess is not null then
		htp.nl;
		htp.p('Error: '||error_mess||'<br>');
   	end if;

   	htp.nl;
	
   /* Show List of Matching Students */
   rcount:=0;

   FOR mystuff IN personinfo
   LOOP
		if rcount=0 then
			twgkwbis.P_DispInfo('lmuhwskiden.LMU_ID_Search','SELSTU');
			twgkfrmt.P_TableOpen('DATADISPLAY');
   			twgkfrmt.P_TableRowOpen('left');
   			twgkfrmt.P_TableDataLabel('Action',calign=>'left');
   			twgkfrmt.P_TableDataLabel('ID',calign=>'left');
   			twgkfrmt.P_TableDataLabel('Last Name',calign=>'left');
   			twgkfrmt.P_TableDataLabel('First Name',calign=>'left');
   			twgkfrmt.P_TableDataLabel('MI',calign=>'left');
   			twgkfrmt.P_TableDataLabel('Birth Date',calign=>'left');
   			twgkfrmt.P_TableDataLabel('Chg',calign=>'center');
   			twgkfrmt.P_TableRowClose;
			rcount:=1;
		end if;
		htp.formOpen('lmuhwskiden.select_id', 'post');
		htp.formHidden('spidm', mystuff.pidm);
		twgkfrmt.P_TableRowOpen('left');	
		twgkfrmt.P_TableDataOpen(calign=>'center');
		htp.formSubmit(null, 'Select');
		twgkfrmt.P_TableDataClose;
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.id),calign=>'left');
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.lname),calign=>'left');
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.fname),calign=>'left');
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.mi),calign=>'left');
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.dob),calign=>'left');
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.chg),calign=>'center');
		twgkfrmt.P_TableRowClose;
		htp.formClose;
   END LOOP;
   twgkfrmt.P_TableClose;
   
   twgkwbis.P_CloseDoc;
end LMU_ID_Search;

procedure select_id (spidm in spriden.spriden_pidm%type default null)
IS
    /* This function allows the user to select a student from the list */
	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;
begin
	IF NOT twgkwbis.F_ValidUser(pidm) THEN	
	  return;
	END IF;
	
	/* Set current student to that of passed spidm */
	twgkwbis.P_SetParam(pidm,'STUPIDM',to_char(spidm,'999999999'));
	hwskoids.P_FacResetPin(pidm);

	/* Return to the Menu */
	twgkwbis.P_GenMenu('amenu.P_FacStuMnu');
	return;
end;

end lmuhwskiden;
/

show errors

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

set scan on