set scan off
set echo on
-- Created for Loyola Marymount University
-- By Robert M. Nitsos
-- 07-FEB-2003
-- 
-- This package creates a Web page that displays Transfer Articulation History
-- for a selected college.
-- 
-- The page is accessable from outside of the Login page (insecure access).
--

create or replace package lmuhwskthst is

procedure LMUTransHist (sbgi IN SHBTATC.SHBTATC_SBGI_CODE%TYPE DEFAULT NULL,
                        error_mess in varchar2 default null);

END lmuhwskthst;
/
show errors

CREATE OR REPLACE PACKAGE BODY lmuhwskthst AS

	/* Global type and variable declarations for package */
    pidm spriden.spriden_pidm%TYPE;
	row_count NUMBER;	
	
/****************************** LMU Show Transfer Articulation History ***********************/
procedure LMUTransHist (sbgi IN SHBTATC.SHBTATC_SBGI_CODE%TYPE DEFAULT NULL,
                        error_mess in varchar2 default null)
 is
	curr_release	varchar2(10) := '5.5';
	hold_sbgi		shbtatc.shbtatc_sbgi_code%type;
	coll_name		stvsbgi.stvsbgi_desc%type;
	msg				varchar2(255);
	rcount			number;

	/* Get Schools to Be Selected */
	cursor schoolinfo
	is
	select stvsbgi_desc school,
		stvsbgi_code sbgi
	from stvsbgi
	where exists
		(select 'X'
		 from shbtatc
		 where shbtatc_sbgi_code=stvsbgi_code
		 and lmu_r_f_get_aok(shbtatc_sbgi_code,
			shbtatc_tlvl_code,
			shbtatc_subj_code_trns,
			shbtatc_crse_numb_trns,
			shbtatc_term_code_eff_trns)='Y')
	order by school;
	
	/* Get information that is to be displayed */
	cursor trnsinfo
	is
	select sovsbgv_desc t_school,
		stvterm_desc term,
		shbtatc_subj_code_trns t_subj,
		shbtatc_crse_numb_trns t_crs,
		shbtatc_trns_title t_title,
		shrtatc_subj_code_inst lmu_subj,
		shrtatc_crse_numb_inst lmu_crs,
		shrtatc_inst_title lmu_title
	from shbtatc a, shrtatc, sovsbgv, stvterm
	where shbtatc_sbgi_code = shrtatc_sbgi_code
		and shbtatc_subj_code_trns = shrtatc_subj_code_trns
		and shbtatc_crse_numb_trns = shrtatc_crse_numb_trns
		and shbtatc_term_code_eff_trns=stvterm_code(+)
		and shbtatc_term_code_eff_trns = shrtatc_term_code_eff_trns
		and shrtatc_term_code_eff_trns =
			(select max(shrtatc_term_code_eff_trns)
			 from shrtatc b where
			 a.shbtatc_sbgi_code = b.shrtatc_sbgi_code
			 and a.shbtatc_subj_code_trns = b.shrtatc_subj_code_trns
			 and a.shbtatc_crse_numb_trns = b.shrtatc_crse_numb_trns)
		and shbtatc_sbgi_code = sovsbgv_code(+)
		and shbtatc_sbgi_code = hold_sbgi
		and lmu_r_f_get_aok(shbtatc_sbgi_code,
			shbtatc_tlvl_code,
			shbtatc_subj_code_trns,
			shbtatc_crse_numb_trns,
			shbtatc_term_code_eff_trns)='Y'
	order by sovsbgv_desc, shbtatc_subj_code_trns, shbtatc_crse_numb_trns;

begin

	hold_sbgi := sbgi;

	/* Check to see if there are any records to display */
	BEGIN
		select count(*)
		into rcount
		from shbtatc a, shrtatc, sovsbgv, stvterm
		where shbtatc_sbgi_code = shrtatc_sbgi_code
			and shbtatc_subj_code_trns = shrtatc_subj_code_trns
			and shbtatc_crse_numb_trns = shrtatc_crse_numb_trns
			and shbtatc_term_code_eff_trns=stvterm_code(+)
			and shbtatc_term_code_eff_trns = shrtatc_term_code_eff_trns
			and shrtatc_term_code_eff_trns =
				(select max(shrtatc_term_code_eff_trns)
				 from shrtatc b where
				 a.shbtatc_sbgi_code = b.shrtatc_sbgi_code
				 and a.shbtatc_subj_code_trns = b.shrtatc_subj_code_trns
				 and a.shbtatc_crse_numb_trns = b.shrtatc_crse_numb_trns)
			and shbtatc_sbgi_code = sovsbgv_code(+)
			and shbtatc_sbgi_code = hold_sbgi;
	EXCEPTION WHEN OTHERS THEN
		rcount := 0;
	END;

	/* Display message if no records found */
	IF rcount = 0 THEN
		if hold_sbgi is null then
			twbkwbis.P_OpenDoc('lmuhwskthst.LMUTransHist');
		else
			twbkwbis.P_OpenDoc('lmuhwskthst.LMUTransHist',header_text=>
        	              '*** No Transfer History Exists for Selected Institution ***');
			/* Be sure to enter appropriate info text in Web Tailor for NO_DATA */
			/* twbkwbis.P_DispInfo('lmuhwskthst.LMUTransHist','NO_DATA'); */
		end if;
		/* You can enter info text for GENERAL if you want to display any additional information */
		twbkwbis.P_DispInfo('lmuhwskthst.LMUTransHist','GENERAL');
	else	
		/* Show Transfer History for College */
			begin
				select stvsbgi_desc
				into coll_name
				from stvsbgi
				where stvsbgi_code = hold_sbgi;
			exception when others then
				coll_name := 'Unknown';
			end;

		twbkwbis.P_OpenDoc('lmuhwskthst.LMUTransHist',header_text=>'For: '|| coll_name);
		/* You can enter info text for GENERAL if you want to display any additional information */
		twbkwbis.P_DispInfo('lmuhwskthst.LMUTransHist','GENERAL');
   
		twbkfrmt.P_TableOpen('DATADISPLAY');
		twbkfrmt.P_TableRowOpen('left');
		twbkfrmt.P_TableDataLabel('Transfer Institution Course(s)',calign=>'CENTER',ccolspan=>'3');
		twbkfrmt.P_TableDataLabel('LMU Equivalent Course(s)',calign=>'CENTER',ccolspan=>'3');
		twbkfrmt.P_TableRowClose;
		twbkfrmt.P_TableRowOpen('left');
		twbkfrmt.P_TableDataLabel('TRNS SUBJ',calign=>'CENTER');
		twbkfrmt.P_TableDataLabel('TRNS CRS',calign=>'CENTER');
		twbkfrmt.P_TableDataLabel('TRNS TITLE',calign=>'LEFT');
		twbkfrmt.P_TableDataLabel('LMU SUBJ',calign=>'CENTER');
		twbkfrmt.P_TableDataLabel('LMU CRS',calign=>'CENTER');
		twbkfrmt.P_TableDataLabel('LMU TITLE',calign=>'LEFT');
		twbkfrmt.P_TableRowClose;

		FOR mystuff IN trnsinfo
		LOOP
			twbkfrmt.P_TableRowOpen('left');

			twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.t_subj),calign=>'CENTER');
			twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.t_crs),calign=>'CENTER');
			twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.t_title),calign=>'LEFT');
			twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.lmu_subj),calign=>'CENTER');
			twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.lmu_crs),calign=>'CENTER');
			twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.lmu_title),calign=>'LEFT');
			
			twbkfrmt.P_TableRowClose;
		END LOOP;

		twbkfrmt.P_TableClose;
	END IF;
	
	/* Display List of Colleges */
	htp.nl;

	HTP.formOpen(twbklibs.twgbwrul_rec.twgbwrul_cgibin_dir||'/'||'lmuhwskthst.LMUTransHist');

	row_count := 0;
	FOR coll_rec in schoolinfo LOOP
		IF schoolinfo%rowcount = 1 THEN
	    	twbkfrmt.p_tableopen( 'DATAENTRY');
	        twbkfrmt.p_tablerowopen;
	        twbkfrmt.p_tabledatalabel( 'Select Institution:');
	        twbkfrmt.p_tabledataopen;
	        HTP.formSelectOpen('sbgi',NULL,1);
	    END IF;
	    IF hold_sbgi IS NOT NULL AND coll_rec.sbgi = hold_sbgi THEN
	    	HTP.P(twbkwbis.F_formSelectOption(coll_rec.school, coll_rec.sbgi,'SELECTED'));
	    ELSE
	        HTP.P(twbkwbis.F_formSelectOption(coll_rec.school, coll_rec.sbgi));
	    END IF;
		row_count := schoolinfo%rowcount;
      END LOOP;

	IF row_count = 0 THEN 
		twbkfrmt.p_printtext('No institutions available');
	ELSE
		HTP.formSelectClose;
		twbkfrmt.p_tabledataopen;
		twbkfrmt.p_tablerowclose;
		twbkfrmt.p_tableclose;
		HTP.nl;
		HTP.nl;
		HTP.formsubmit(NULL,'Submit');
	END IF;
	HTP.formClose;
	htp.nl;
	
	htp.anchor('http://registrar.lmu.edu', 'Office of the Registrar');
	
	twbkwbis.P_CloseDoc(curr_release);
end LMUTransHist;

end lmuhwskthst;
/

show errors

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

set scan on