set scan off
set echo on
-- Created for Loyola Marymount University
-- By Robert M. Nitsos
-- 28-AUG-2002
-- 
-- This package creates a Banner Web page that allows students to view
-- and update their demographic data (e.g. Ethnicity, Religion, etc.) in
-- the Student Module. This page will be called prior to the Registration
-- page to ensure students update their information.
--
-- Modified 24-FEB-2003
-- By Robert M. Nitsos
-- Updated package to use StuWeb 5.5 UI and procedures.
--


/* Create Package Header */
create or replace package lmuhwskdemo is

procedure LMUUpdateDemo (STUPIDM in spriden.spriden_pidm%type default null,
                        error_mess in varchar2 default null);

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

procedure LMUUpdateDemo_Update(u_pidm in spriden.spriden_pidm%type default null,
    u_term in stvterm.stvterm_code%type default null,
	r_proc in varchar2 default null,
	u_ethn in spbpers.spbpers_ethn_code%type default null,
	c_ethn in spbpers.spbpers_ethn_code%type default null,
	u_relg in spbpers.spbpers_relg_code%type default null,
	c_relg in spbpers.spbpers_relg_code%type default null,
	u_mrtl in spbpers.spbpers_mrtl_code%type default null,
	c_mrtl in spbpers.spbpers_mrtl_code%type default null);

END lmuhwskdemo;
/
show errors

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

  /* Global type and variable declarations for package */
    pidm spriden.spriden_pidm%TYPE;
	
/****************************** LMU Update Demographic Data ***********************/
procedure LMUUpdateDemo (STUPIDM in spriden.spriden_pidm%type default null,
                        error_mess in varchar2 default null)
 is
	curr_release		varchar2(10) := '4.2';
	hold_term		stvterm.stvterm_code%TYPE;
    hold_stupidm		spriden.spriden_pidm%TYPE;
    rcount			number;
	rec_upd			varchar2(1);
	dum				number;
		
   cursor demo_info
   is
   select spbpers_ethn_code ethn,
     stvethn_desc ethnicity,
	 spbpers_relg_code relg,
	 stvrelg_desc religion,
	 spbpers_mrtl_code mrtl,
	 stvmrtl_desc marital
   from spbpers, stvethn, stvrelg, stvmrtl
   where spbpers_pidm = hold_stupidm
   and spbpers_ethn_code=stvethn_code(+)
   and spbpers_relg_code=stvrelg_code(+)
   and spbpers_mrtl_code=stvmrtl_code(+);
   
   cursor ethn_info
   is
   select stvethn_code ethn,
     stvethn_desc ethnicity
   from stvethn
   order by ethnicity;
   
   cursor relg_info
   is
   select stvrelg_code relg,
     stvrelg_desc religion
   from stvrelg
   order by religion;
   
   cursor mrtl_info
   is
   select stvmrtl_code mrtl,
     stvmrtl_desc marital
   from stvmrtl
   order by marital;

begin

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

	/* Display Existing Demographic Data For Student */
	twbkwbis.P_OpenDoc('lmuhwskdemo.LMUUpdateDemo');
	twbkwbis.P_DispInfo('lmuhwskdemo.LMUUpdateDemo','GENERAL');

	HTP.formOpen(twbklibs.twgbwrul_rec.twgbwrul_cgibin_dir||
                     '/'||'lmuhwskdemo.LMUUpdateDemo_Update');
	HTP.formHidden('u_pidm',hold_stupidm);
	/* HTP.formHidden('u_term',hold_term); */
	HTP.formHidden('r_proc','DEMO');

	twbkfrmt.P_TableOpen('DATADISPLAY');
	twbkfrmt.P_TableDataLabel('',calign=>'center');
	twbkfrmt.P_TableDataLabel('Currently',calign=>'center');
	twbkfrmt.P_TableDataLabel('Update To',calign=>'center');
	twbkfrmt.P_TableRowClose;
	
	for mystuff in demo_info
	loop
		HTP.formHidden('c_ethn',mystuff.ethn);
		twbkfrmt.P_TableRowOpen('left');
		twbkfrmt.P_TableDataLabel('Ethnicity:',calign=>'left');
		if mystuff.ethn = 'X' then
			twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(null),calign=>'left');
		else
			twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.ethnicity),calign=>'left');
		end if;
		for ethn_rec in ethn_info
		loop
			if ethn_info%rowcount = 1 then
				twbkfrmt.P_TableDataOpen;
				htp.formSelectOpen('u_ethn',NULL,1);
				htp.p(twbkwbis.F_formSelectOption('No Changes', 'NOCHG', 'SELECTED'));
			end if;
			if mystuff.ethn is not null AND mystuff.ethn = ethn_rec.ethn then
				htp.p(twbkwbis.F_formSelectOption(ethn_rec.ethnicity, ethn_rec.ethn));
			else
				if ethn_rec.ethn <> 'X' then
					htp.p(twbkwbis.F_formSelectOption(ethn_rec.ethnicity, ethn_rec.ethn));
				end if;
			end if;
		end loop;
		htp.formSelectClose;
		twbkfrmt.P_TableRowClose;
		HTP.formHidden('c_relg',mystuff.relg);
		twbkfrmt.P_TableRowOpen('left');		
		twbkfrmt.P_TableDataLabel('Religion:',calign=>'left');
		if mystuff.relg = 'XX' then
			twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(null),calign=>'left');
		else
			twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.religion),calign=>'left');
		end if;
		for relg_rec in relg_info
		loop
			if relg_info%rowcount = 1 then
				twbkfrmt.P_TableDataOpen;
				htp.formSelectOpen('u_relg',NULL,1);
				htp.p(twbkwbis.F_formSelectOption('No Changes', 'NOCHG', 'SELECTED'));				
			end if;
			if mystuff.relg is not null AND mystuff.relg = relg_rec.relg then
				htp.p(twbkwbis.F_formSelectOption(relg_rec.religion, relg_rec.relg));
			else
				if relg_rec.relg <> 'XX' then
					htp.p(twbkwbis.F_formSelectOption(relg_rec.religion, relg_rec.relg));
				end if;
			end if;
		end loop;
		htp.formSelectClose;
		twbkfrmt.P_TableRowClose;
		HTP.formHidden('c_mrtl',mystuff.mrtl);
		twbkfrmt.P_TableRowOpen('left');		
		twbkfrmt.P_TableDataLabel('Marital Status:',calign=>'left');
		twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.marital),calign=>'left');
		for mrtl_rec in mrtl_info
		loop
			if mrtl_info%rowcount = 1 then
				twbkfrmt.P_TableDataOpen;
				htp.formSelectOpen('u_mrtl',NULL,1);
				htp.p(twbkwbis.F_formSelectOption('No Changes', 'NOCHG', 'SELECTED'));
			end if;
			htp.p(twbkwbis.F_formSelectOption(mrtl_rec.marital, mrtl_rec.mrtl));
		end loop;
		htp.formSelectClose;
		twbkfrmt.P_TableRowClose;
	end loop;
	
	twbkfrmt.P_TableClose;

	htp.nl;

	htp.formsubmit(NULL, 'Update');
	htp.formClose;
	
	twbkwbis.P_DispInfo('lmuhwskdemo.LMUUpdateDemo','DISCLAIM');

	twbkwbis.P_CloseDoc;
end LMUUpdateDemo;

/****************************** LMU Update Demographic Data, Continue to Reg Status ***********************/
procedure LMUUpdateDemoRegStat (STUPIDM in spriden.spriden_pidm%type default null,
					    term_in IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL,
                        error_mess in varchar2 default null)
 is
	curr_release		varchar2(10) := '4.2';
	stvterm_rec	stvterm%ROWTYPE;
	sorrtrm_rec	sorrtrm%ROWTYPE;
	hold_term		stvterm.stvterm_code%TYPE;
    hold_stupidm		spriden.spriden_pidm%TYPE;
    rcount			number;
	rec_upd			varchar2(1);
	dum				number;
	updcount		number;
		
   cursor demo_info
   is
   select spbpers_ethn_code ethn,
     stvethn_desc ethnicity,
	 spbpers_relg_code relg,
	 stvrelg_desc religion,
	 spbpers_mrtl_code mrtl,
	 stvmrtl_desc marital
   from spbpers, stvethn, stvrelg, stvmrtl
   where spbpers_pidm = hold_stupidm
   and spbpers_ethn_code=stvethn_code(+)
   and spbpers_relg_code=stvrelg_code(+)
   and spbpers_mrtl_code=stvmrtl_code(+);
   
   cursor ethn_info
   is
   select stvethn_code ethn,
     stvethn_desc ethnicity
   from stvethn
   order by ethnicity;
   
   cursor relg_info
   is
   select stvrelg_code relg,
     stvrelg_desc religion
   from stvrelg
   order by religion;
   
   cursor mrtl_info
   is
   select stvmrtl_code mrtl,
     stvmrtl_desc marital
   from stvmrtl
   order by marital;

begin

	/* Check for Valid User */
	IF NOT twbkwbis.F_ValidUser(pidm) THEN
		return;
	END IF;
	hold_stupidm := pidm;

   /* Check for Valid Term */
   IF term_in IS NOT NULL THEN
      IF bwskflib.f_validterm (term_in, stvterm_rec, sorrtrm_rec) THEN
         twbkwbis.p_setparam (pidm, 'TERM', term_in);
      END IF;
    hold_term := term_in;
   ELSE
      hold_term := twbkwbis.f_getparam (pidm, 'TERM');
   END IF;
   IF NOT bwskflib.f_validterm1 (pidm, hold_term, stvterm_rec, sorrtrm_rec)
   THEN
         bwskflib.p_seldefterm (hold_term, 'lmuhwskdemo.LMUUpdateDemoRegStat');
		 return;
   END IF;

   
   /* Check to see if data has been updated for term */
   rec_upd := 'N';
   updcount := 0;
	begin
		select count(*)
		into updcount
		from baninst1.lmu_reg_t_demographic_update
		where baninst1.lmu_reg_t_demographic_update.pidm = hold_stupidm
		and baninst1.lmu_reg_t_demographic_update.term_code = hold_term;
	exception when others then
		updcount := 0;
	end;
	if updcount > 0 then
		rec_upd := 'Y';
	else
		rec_upd := 'N';
	end if;

   if rec_upd = 'Y' then
   		/* Continue on to registration status screen */
		lmuhwskrgtm.LMURegTimes;
		return;
   end if;

	/* Display Existing Demographic Data For Student */
	twbkwbis.P_OpenDoc('lmuhwskdemo.LMUUpdateDemoRegStat');
	twbkwbis.P_DispInfo('lmuhwskdemo.LMUUpdateDemo','GENERAL');

	HTP.formOpen(twbklibs.twgbwrul_rec.twgbwrul_cgibin_dir||
                     '/'||'lmuhwskdemo.LMUUpdateDemo_Update');
	HTP.formHidden('u_pidm',hold_stupidm);
	HTP.formHidden('u_term',hold_term);
	HTP.formHidden('r_proc','REGSTAT');

	twbkfrmt.P_TableOpen('DATADISPLAY');
	twbkfrmt.P_TableDataLabel('',calign=>'center');
	twbkfrmt.P_TableDataLabel('Currently',calign=>'center');
	twbkfrmt.P_TableDataLabel('Update To',calign=>'center');
	twbkfrmt.P_TableRowClose;
	
	for mystuff in demo_info
	loop
		HTP.formHidden('c_ethn',mystuff.ethn);
		twbkfrmt.P_TableRowOpen('left');
		twbkfrmt.P_TableDataLabel('Ethnicity:',calign=>'left');
		if mystuff.ethn = 'X' then
			twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(null),calign=>'left');
		else
			twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.ethnicity),calign=>'left');
		end if;
		for ethn_rec in ethn_info
		loop
			if ethn_info%rowcount = 1 then
				twbkfrmt.P_TableDataOpen;
				htp.formSelectOpen('u_ethn',NULL,1);
				htp.p(twbkwbis.F_formSelectOption('No Changes', 'NOCHG', 'SELECTED'));
			end if;
			if mystuff.ethn is not null AND mystuff.ethn = ethn_rec.ethn then
				htp.p(twbkwbis.F_formSelectOption(ethn_rec.ethnicity, ethn_rec.ethn));
			else
				if ethn_rec.ethn <> 'X' then
					htp.p(twbkwbis.F_formSelectOption(ethn_rec.ethnicity, ethn_rec.ethn));
				end if;
			end if;
		end loop;
		htp.p(twbkwbis.F_formSelectOption('Decline to State', 'X'));
		htp.formSelectClose;
		twbkfrmt.P_TableRowClose;
		HTP.formHidden('c_relg',mystuff.relg);
		twbkfrmt.P_TableRowOpen('left');		
		twbkfrmt.P_TableDataLabel('Religion:',calign=>'left');
		if mystuff.relg = 'XX' then
			twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(null),calign=>'left');
		else
			twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.religion),calign=>'left');
		end if;
		for relg_rec in relg_info
		loop
			if relg_info%rowcount = 1 then
				twbkfrmt.P_TableDataOpen;
				htp.formSelectOpen('u_relg',NULL,1);
				htp.p(twbkwbis.F_formSelectOption('No Changes', 'NOCHG', 'SELECTED'));				
			end if;
			if mystuff.relg is not null AND mystuff.relg = relg_rec.relg then
				htp.p(twbkwbis.F_formSelectOption(relg_rec.religion, relg_rec.relg));
			else
				if relg_rec.relg <> 'XX' then
					htp.p(twbkwbis.F_formSelectOption(relg_rec.religion, relg_rec.relg));
				end if;
			end if;
		end loop;
		htp.p(twbkwbis.F_formSelectOption('Decline to State', 'XX'));
		htp.formSelectClose;
		twbkfrmt.P_TableRowClose;
		HTP.formHidden('c_mrtl',mystuff.mrtl);
		twbkfrmt.P_TableRowOpen('left');		
		twbkfrmt.P_TableDataLabel('Marital Status:',calign=>'left');
		twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.marital),calign=>'left');
		for mrtl_rec in mrtl_info
		loop
			if mrtl_info%rowcount = 1 then
				twbkfrmt.P_TableDataOpen;
				htp.formSelectOpen('u_mrtl',NULL,1);
				htp.p(twbkwbis.F_formSelectOption('No Changes', 'NOCHG', 'SELECTED'));
			end if;
			htp.p(twbkwbis.F_formSelectOption(mrtl_rec.marital, mrtl_rec.mrtl));
		end loop;
		htp.formSelectClose;
		twbkfrmt.P_TableRowClose;
	end loop;
	
	twbkfrmt.P_TableClose;

	htp.nl;

	htp.formsubmit(NULL, 'Continue');
	htp.formClose;
	
	twbkwbis.P_DispInfo('lmuhwskdemo.LMUUpdateDemo','DISCLAIM');

	twbkwbis.P_CloseDoc;
end LMUUpdateDemoRegStat;

/******************* Update Demographic Data ******************/
procedure LMUUpdateDemo_Update(u_pidm in spriden.spriden_pidm%type default null,
    u_term in stvterm.stvterm_code%type default null,
	r_proc in varchar2 default null,
	u_ethn in spbpers.spbpers_ethn_code%type default null,
	c_ethn in spbpers.spbpers_ethn_code%type default null,
	u_relg in spbpers.spbpers_relg_code%type default null,
	c_relg in spbpers.spbpers_relg_code%type default null,
	u_mrtl in spbpers.spbpers_mrtl_code%type default null,
	c_mrtl in spbpers.spbpers_mrtl_code%type default null)

IS
	rcount	number;
begin
	IF NOT twbkwbis.F_ValidUser(pidm) THEN
	return;
	END IF;

	if u_pidm is not null then
		if u_ethn <> 'NOCHG' then
			if u_ethn <> c_ethn then
				/* Update Ethnicity */
				update spbpers
				set spbpers_ethn_code = u_ethn
				where spbpers_pidm = u_pidm;
			end if;
		end if;
		if u_relg <> 'NOCHG' Then
			if u_relg <> c_relg then
				/* Update Religion */
				update spbpers
				set spbpers_relg_code = u_relg
				where spbpers_pidm = u_pidm;
			end if;
		end if;
		if u_mrtl <> 'NOCHG' then
			if u_mrtl <> c_mrtl then
				/* Update Marital Status */
				update spbpers
				set spbpers_mrtl_code = u_mrtl
				where spbpers_pidm = u_pidm;
			end if;
		end if;
	end if;
	
	if r_proc = 'REGSTAT' then
		rcount := 0;
		begin
			select count(*)
			into rcount
			from baninst1.lmu_reg_t_demographic_update
			where baninst1.lmu_reg_t_demographic_update.pidm = u_pidm
			and baninst1.lmu_reg_t_demographic_update.term_code = u_term;
		exception when others then
			rcount := 0;
		end;
		if rcount = 0 then
			/* Update Table to Indicate Demographic Data Has Been Verified */
			insert into baninst1.lmu_reg_t_demographic_update
			(pidm, term_code, update_ind, activity_date)
			values(u_pidm, u_term, 'Y', sysdate);
		end if;

		LMUUpdateDemoRegStat(STUPIDM=>u_pidm,term_in=>u_term);
	else
		LMUUpdateDemo(STUPIDM=>u_pidm);
	end if;
	return;
end;

end lmuhwskdemo;
/

show errors

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

set scan on