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.
--
-- 26-MAR-2003 (RNITSOS)
-- Updated package to use FacWeb 5.3 UI and procedures.
--

create or replace package lmuhwskiden is

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

  /* 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) := '5.5';
	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

   /* Check if Valid User */
   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_AdvSelTerm(calling_proc_name=> 'lmuhwskiden.LMU_ID_Search');
       RETURN;
   END IF;

   /* set paramater to indicate we are a faculty user */
   twbkwbis.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 BWLKILIB.F_ValidFac(hold_term,pidm) THEN
      twbkwbis.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;
      twbkfrmt.P_PrintImage(twbklibs.twgbwrul_rec.twgbwrul_error_gif);
      HTP.bold(msg);
      HTP.para;
      twbkWBIS.P_CloseDoc(curr_release);
      RETURN;
   END IF;

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

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

	if firstsrch <> 'Y' then
		begin
			select count(*)
			into rcount
			from spriden
			where 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||'%');
		exception when others then
			rcount := 0;
		end;
	else
		rcount := 1;
	end if;
		
	if rcount = 0 then
		/* No records returned */
		twbkwbis.P_DispInfo('lmuhwskiden.LMU_ID_Search','NODATA');
	else
	
   /* Show List of Matching Students */
   rcount:=0;

   FOR mystuff IN personinfo
   LOOP
		if rcount=0 then
			twbkwbis.P_DispInfo('lmuhwskiden.LMU_ID_Search','SELSTU');
			twbkfrmt.P_TableOpen('DATADISPLAY');
   			twbkfrmt.P_TableRowOpen('left');
   			twbkfrmt.P_TableDataLabel('Action',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('Birth Date',calign=>'left');
   			twbkfrmt.P_TableDataLabel('Chg',calign=>'center');
   			twbkfrmt.P_TableRowClose;
			rcount:=1;
		end if;
		htp.formOpen('lmuhwskiden.select_id', 'post');
		htp.formHidden('spidm', mystuff.pidm);
		twbkfrmt.P_TableRowOpen('left');	
		twbkfrmt.P_TableDataOpen(calign=>'center');
		htp.formSubmit(null, 'Select');
		twbkfrmt.P_TableDataClose;
		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.dob),calign=>'left');
		twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.chg),calign=>'center');
		twbkfrmt.P_TableRowClose;
		htp.formClose;
   END LOOP;
   twbkfrmt.P_TableClose;
   
   end if;
   
   twbkwbis.P_CloseDoc(curr_release);
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 */

begin
	IF NOT twbkwbis.F_ValidUser(pidm) THEN	
	  return;
	END IF;
	
	/* Set current student to that of passed spidm */
	twbkwbis.P_SetParam(pidm,'STUPIDM',to_char(spidm,'999999999'));
	hwskoids.P_FacResetPin(pidm);

	/* Return to the Menu */
	twbkwbis.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