set scan off
set echo on
-- Created for Loyola Marymount University
-- By Robert M. Nitsos
-- 21-MAR-2001
-- 
-- This package creates a Banner Web page that displays Class Count information in the
-- Faculty Module. The is also alble to download the information into an Excel spreadsheet
-- (tab-delimited file).  A button can also be clicked to view cross-listed course data.
--

create or replace package lmuhwskccnt is

procedure LMUClassCounts (term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL,
						subj in stvsubj.stvsubj_code%type default null,
						firsttime in varchar2 default 'Y',
                        error_mess in varchar2 default null);

procedure LMUDownloadCCnt (term in stvterm.stvterm_code%type default null,
						subj in stvsubj.stvsubj_code%type default null);
						
procedure LMUCrossListInfo (term in stvterm.stvterm_code%type default null,
						xlst in ssrxlst.ssrxlst_xlst_group%type default null);

END lmuhwskccnt;
/
show errors

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

  /* Global type and variable declarations for package */
    pidm spriden.spriden_pidm%TYPE;
    row_count NUMBER;	
	
/****************************** LMU Show Class Counts ***********************/
procedure LMUClassCounts (term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL,
						subj in stvsubj.stvsubj_code%type default null,
						firsttime in varchar2 default 'Y',
                        error_mess in varchar2 default null)
 is
 	    curr_release  varchar2(10) := '4.4';
	    hold_term     stvterm.stvterm_code%TYPE;
		hold_subj	  stvsubj.stvsubj_code%TYPE;
        confid_msg    varchar2(30) default null;
        msg           varchar2(255);
        term_desc     stvterm.stvterm_desc%type default null;
		subj_desc	  stvsubj.stvsubj_desc%type default null;
		rcount		  number;
		cur_crn		  ssbsect.ssbsect_crn%type default null;
		
   /* Get Subject Codes for Drop-Down List */
   cursor subjinfo
   is
   select distinct ssbsect_subj_code subj,
   			stvsubj_desc subject
   from ssbsect, stvsubj
   where ssbsect_term_code	= hold_term
   and ssbsect_ssts_code	<> 'C'
   and ssbsect_subj_code=stvsubj_code(+)
   order by subject;
   
   /* Get Term Codes for Drop-Down List */
   cursor terminfo
   is
   select distinct ssbsect_term_code term_code,
   			stvterm_desc term_desc
   from ssbsect, stvterm
   where ssbsect_term_code=stvterm_code(+)
   and ssbsect_ssts_code	<> 'C'
   order by term_code DESC;

   /* Get information that is to be displayed */
   cursor courseinfo
   is
   select ssbsect_term_code term,
			ssbsect_crn crn,
			ssbsect_subj_code subj,
			ssbsect_crse_numb crs,
			ssbsect_seq_numb sec,
			decode(ssbsect_crse_title,
			  '', a.scbcrse_title, ssbsect_crse_title) title,
			decode(ssrmeet_sun_day||ssrmeet_mon_day||ssrmeet_tue_day||ssrmeet_wed_day||ssrmeet_thu_day||ssrmeet_fri_day||ssrmeet_sat_day,
			  '', 'TBA',
			  ssrmeet_sun_day||ssrmeet_mon_day||ssrmeet_tue_day||ssrmeet_wed_day||ssrmeet_thu_day||ssrmeet_fri_day||ssrmeet_sat_day) day,
			decode(ssrmeet_begin_time,
			  '', 'TBA', ssrmeet_begin_time||'-'||ssrmeet_end_time) time,
			decode(ltrim(rtrim(ssrmeet_bldg_code)),
			  '', 'TBA',
			  ltrim(rtrim(ssrmeet_bldg_code))||' '||ltrim(rtrim(ssrmeet_room_code))) loc,
			decode(i.spriden_last_name,
			  '', 'FACULTY',
			  i.spriden_last_name||', '||i.spriden_first_name||' '||i.spriden_mi) instructor,
			ssrxlst_xlst_group xlst,
			decode(ssrxlst_xlst_group,
			  '', ssbsect_enrl, ssbxlst_enrl) enrl,
			decode(ssrxlst_xlst_group,
			  '', ssbsect_max_enrl, ssbxlst_max_enrl) maxenrl
		  from ssbsect,
		    scbcrse a,
			ssrmeet,
			sirasgn,
			spriden i,
			ssrxlst,
			ssbxlst
		  where ssbsect_term_code = hold_term
		  and ssbsect_ssts_code<>'C'
		  and ssbsect_subj_code=a.scbcrse_subj_code
		  and ssbsect_crse_numb=a.scbcrse_crse_numb
		  and a.scbcrse_eff_term=
		   (select max(b.scbcrse_eff_term) from scbcrse b
		    where b.scbcrse_eff_term<=ssbsect_term_code
			and a.scbcrse_subj_code=b.scbcrse_subj_code
			and a.scbcrse_crse_numb=b.scbcrse_crse_numb)
		  and ssbsect_crn=ssrmeet_crn(+)
		  and ssbsect_term_code=ssrmeet_term_code(+)
		  and ssbsect_crn=sirasgn_crn(+)
		  and ssbsect_term_code=sirasgn_term_code(+)
		  and sirasgn_primary_ind(+)='Y'
		  and sirasgn_pidm=i.spriden_pidm(+)
		  and i.spriden_change_ind(+) is null
		  and ssbsect_crn=ssrxlst_crn(+)
		  and ssbsect_term_code=ssrxlst_term_code(+)
		  and ssrxlst_xlst_group=ssbxlst_xlst_group(+)
		  and ssrxlst_term_code=ssbxlst_term_code(+)
		  and ssbsect_subj_code = subj
		  and firsttime<>'Y'
		  order by subj,
		    crs,
			sec;

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_AdvSelTerm(calling_proc_name=> 'lmuhwskccnt.LMUClassCounts');
       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('lmuhwskccnt.LMUClassCounts',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;


  IF firsttime <> 'Y' THEN
    /* Check to see if there are any records to display */
    BEGIN
      select count(*)
      into   rcount
      from   ssbsect
      where  ssbsect_term_code          = hold_term
	  and ssbsect_subj_code				= subj;
    EXCEPTION WHEN OTHERS THEN
      rcount := 0;
    END;

    /* Display message if no test records found */
    IF rcount = 0 THEN
      twgkwbis.P_OpenDoc('lmuhwskccnt.LMUClassCounts',header_text=>
                      '*** No Course Information Exists For Subject ***');
	  /* Be sure to enter appropriate info text in Web Tailor for NO_COUNTS */
      twgkwbis.P_DispInfo('lmuhwskccnt.LMUClassCounts','NO_COUNTS');
	  
     /* You can enter info text for GENERAL if you want to display any additional information */
     twgkwbis.P_DispInfo('lmuhwskccnt.LMUClassCounts','GENERAL');
	  
	  htp.nl;
	  
	  HTP.formOpen(twgklibs.twgbwrul_rec.twgbwrul_cgibin_dir||
                     '/'||'lmuhwskccnt.LMUClassCounts');
      HTP.formHidden('term',hold_term);
	  HTP.formHidden('firsttime','N');
	  /* HTP.formHidden('error_msg',''); */
	  
	  row_count := 0;
	  FOR subj_rec in subjinfo LOOP
	  	IF subjinfo%rowcount = 1 THEN
	    	twgkfrmt.p_tableopen( 'DATAENTRY');
	        twgkfrmt.p_tablerowopen;
	        twgkfrmt.p_tabledatalabel( 'Select Subject:');
	        twgkfrmt.p_tabledataopen;
	        HTP.formSelectOpen('subj',NULL,1);
	    END IF;
	    IF subj IS NOT NULL AND subj_rec.subj = subj THEN
	    	HTP.P(twgkwbis.F_formSelectOption(subj_rec.subject||' ('||subj_rec.subj||')', subj_rec.subj,'SELECTED'));
	    ELSE
	        HTP.P(twgkwbis.F_formSelectOption(subj_rec.subject||' ('||subj_rec.subj||')', subj_rec.subj));
	    END IF;
		row_count := subjinfo%rowcount;
      END LOOP;

      IF row_count = 0 THEN 
         twgkfrmt.p_printtext('No subject available');
      ELSE
         HTP.formSelectClose;
         twgkfrmt.p_tabledataopen;
         twgkfrmt.p_tablerowclose;
         twgkfrmt.p_tableclose;
         HTP.nl;
         HTP.nl;
         HTP.formsubmit(NULL,'Submit Subject');
         HTP.formreset('Reset');
      END IF;
      HTP.formClose;
	  
	  htp.nl;
	  
      twgkwbis.P_CloseDoc(curr_release);
      return;
    END IF;
	
	BEGIN
	  select stvterm_desc
	  into term_desc
	  from stvterm
	  where stvterm_code	= hold_term;
	EXCEPTION WHEN OTHERS THEN
	  term_desc := 'Term Unknown';
	END;

	BEGIN
	  select stvsubj_desc
	  into subj_desc
	  from stvsubj
	  where stvsubj_code	= subj;
	EXCEPTION WHEN OTHERS THEN
	  subj_desc := 'Subject Unknown';
	END;

	/* Show Class Information */
    twgkwbis.P_OpenDoc('lmuhwskccnt.LMUClassCounts',header_text=>
                      'For: '||subj_desc||' ('||term_desc||') - '||rcount||' Course(s)');
   /* You can enter info text for GENERAL if you want to display any additional information */
   twgkwbis.P_DispInfo('lmuhwskccnt.LMUClassCounts','GENERAL');
   
   	if error_mess is not null then
		htp.nl;
		htp.p('Error: '||error_mess||'<br>');
   	end if;

   	htp.nl;

	htp.anchor(twgklibs.twgbwrul_rec.twgbwrul_cgibin_dir ||
	              '/lmuhwskccnt.LMUDownloadCCnt'||
	              '?term='   || hold_term||
				  '&subj='	||subj,
	              'DOWNLOAD CLASS COUNTS');
	
	htp.nl;

	
	rcount:=0;
	cur_crn:='XXXXX';

   FOR mystuff IN courseinfo
   LOOP
		if rcount=0 then
			twgkfrmt.P_TableOpen('DATADISPLAY');
   			twgkfrmt.P_TableRowOpen('left');
   			twgkfrmt.P_TableDataLabel('CRN',calign=>'center');
   			twgkfrmt.P_TableDataLabel('SUBJ',calign=>'center');
   			twgkfrmt.P_TableDataLabel('CRS',calign=>'center');
   			twgkfrmt.P_TableDataLabel('SEC',calign=>'center');
   			twgkfrmt.P_TableDataLabel('TITLE',calign=>'left');
   			twgkfrmt.P_TableDataLabel('DAYS',calign=>'center');
   			twgkfrmt.P_TableDataLabel('TIMES',calign=>'center');
			twgkfrmt.P_TableDataLabel('LOCATION',calign=>'center');
			twgkfrmt.P_TableDataLabel('INSTRUCTOR',calign=>'left');
			twgkfrmt.P_TableDataLabel('CL',calign=>'center');			
			twgkfrmt.P_TableDataLabel('ENRL',calign=>'center');
			twgkfrmt.P_TableDataLabel('MAX',calign=>'center');
   			twgkfrmt.P_TableRowClose;
			rcount:=1;
		end if;
		if mystuff.crn=cur_crn then
			twgkfrmt.P_TableRowOpen('left');
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(''),calign=>'center');
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(''),calign=>'center');
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(''),calign=>'center');
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(''),calign=>'center');
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(''),calign=>'left');
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.day),calign=>'center');
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.time),calign=>'center');
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.loc),calign=>'center');
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(''),calign=>'left');
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(''),calign=>'center');
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(''),calign=>'center');
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(''),calign=>'center');
			twgkfrmt.P_TableRowClose;
		else
			cur_crn:=mystuff.crn;
			twgkfrmt.P_TableRowOpen('left');
			if mystuff.xlst is not null then
				htp.formOpen('lmuhwskccnt.LMUCrossListInfo', 'post');
				htp.formHidden('term', hold_term);
				htp.formHidden('xlst', mystuff.xlst);
			end if;
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.crn),calign=>'center');
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.subj),calign=>'center');
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.crs),calign=>'center');
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.sec),calign=>'center');
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.title),calign=>'left');
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.day),calign=>'center');
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.time),calign=>'center');
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.loc),calign=>'center');
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.instructor),calign=>'left');
			if mystuff.xlst is not null then
		  		/* Add button for XLST Info */
			 	twgkfrmt.P_TableDataOpen(calign=>'center');
		  		htp.formSubmit(null, mystuff.xlst);
		  		twgkfrmt.P_TableDataClose;
			else
		  		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(null),calign=>'center');	  
			end if;
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.enrl),calign=>'center');
			twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.maxenrl),calign=>'center');
			twgkfrmt.P_TableRowClose;
			if mystuff.xlst is not null then
		  		htp.formClose;
			end if;
		end if;
   END LOOP;
   twgkfrmt.P_TableClose;
  ELSE
	/* Show No Class Information */
    twgkwbis.P_OpenDoc('lmuhwskccnt.LMUClassCounts');
   /* You can enter info text for GENERAL if you want to display any additional information */
   twgkwbis.P_DispInfo('lmuhwskccnt.LMUClassCounts','GENERAL');
   
   	if error_mess is not null then
		htp.nl;
		htp.p('Error: '||error_mess||'<br>');
   	end if;

   	htp.nl;
  
  END IF;
   
	  htp.nl;
	  
	  HTP.formOpen(twgklibs.twgbwrul_rec.twgbwrul_cgibin_dir||
                     '/'||'lmuhwskccnt.LMUClassCounts');
      HTP.formHidden('term',hold_term);
	  HTP.formHidden('firsttime','N');
	  /* HTP.formHidden('error_msg',''); */
	  
	  row_count := 0;
	  FOR subj_rec in subjinfo LOOP
	  	IF subjinfo%rowcount = 1 THEN
	    	twgkfrmt.p_tableopen( 'DATAENTRY');
	        twgkfrmt.p_tablerowopen;
	        twgkfrmt.p_tabledatalabel( 'Select Subject:');
	        twgkfrmt.p_tabledataopen;
	        HTP.formSelectOpen('subj',NULL,1);
	    END IF;
	    IF subj IS NOT NULL AND subj_rec.subj = subj THEN
	    	HTP.P(twgkwbis.F_formSelectOption(subj_rec.subject||' ('||subj_rec.subj||')', subj_rec.subj,'SELECTED'));
	    ELSE
	        HTP.P(twgkwbis.F_formSelectOption(subj_rec.subject||' ('||subj_rec.subj||')', subj_rec.subj));
	    END IF;
		row_count := subjinfo%rowcount;
      END LOOP;

      IF row_count = 0 THEN 
         twgkfrmt.p_printtext('No subject available');
      ELSE
         HTP.formSelectClose;
         twgkfrmt.p_tabledataopen;
         twgkfrmt.p_tablerowclose;
         twgkfrmt.p_tableclose;
         HTP.nl;
         HTP.nl;
         HTP.formsubmit(NULL,'Submit Subject');
         HTP.formreset('Reset');
      END IF;
      HTP.formClose;
	  
	  htp.nl;

   twgkwbis.P_CloseDoc;
end LMUClassCounts;

procedure LMUDownloadCCnt (term in stvterm.stvterm_code%type default null,
						subj in stvsubj.stvsubj_code%type default null)

is

   mytab	varchar2(1);
   q		varchar2(1);
   curcrn	ssbsect.ssbsect_crn%type default null;
   
   cursor dlcourseinfo
   is
   select ssbsect_term_code term,
			ssbsect_crn crn,
			ssbsect_subj_code subj,
			ssbsect_crse_numb crs,
			ssbsect_seq_numb sec,
			decode(ssbsect_crse_title,
			  '', a.scbcrse_title, ssbsect_crse_title) title,
			decode(ssrmeet_sun_day||ssrmeet_mon_day||ssrmeet_tue_day||ssrmeet_wed_day||ssrmeet_thu_day||ssrmeet_fri_day||ssrmeet_sat_day,
			  '', 'TBA',
			  ssrmeet_sun_day||ssrmeet_mon_day||ssrmeet_tue_day||ssrmeet_wed_day||ssrmeet_thu_day||ssrmeet_fri_day||ssrmeet_sat_day) day,
			decode(ssrmeet_begin_time,
			  '', 'TBA', ssrmeet_begin_time||'-'||ssrmeet_end_time) time,
			decode(ltrim(rtrim(ssrmeet_bldg_code)),
			  '', 'TBA',
			  ltrim(rtrim(ssrmeet_bldg_code))||' '||ltrim(rtrim(ssrmeet_room_code))) loc,
			decode(i.spriden_last_name,
			  '', 'FACULTY',
			  i.spriden_last_name||', '||i.spriden_first_name||' '||i.spriden_mi) instructor,
			ssrxlst_xlst_group xlst,
			decode(ssrxlst_xlst_group,
			  '', ssbsect_enrl, ssbxlst_enrl) enrl,
			decode(ssrxlst_xlst_group,
			  '', ssbsect_max_enrl, ssbxlst_max_enrl) maxenrl
		  from ssbsect,
		    scbcrse a,
			ssrmeet,
			sirasgn,
			spriden i,
			ssrxlst,
			ssbxlst
		  where ssbsect_term_code = term
		  and ssbsect_ssts_code<>'C'
		  and ssbsect_subj_code=a.scbcrse_subj_code
		  and ssbsect_crse_numb=a.scbcrse_crse_numb
		  and a.scbcrse_eff_term=
		   (select max(b.scbcrse_eff_term) from scbcrse b
		    where b.scbcrse_eff_term<=ssbsect_term_code
			and a.scbcrse_subj_code=b.scbcrse_subj_code
			and a.scbcrse_crse_numb=b.scbcrse_crse_numb)
		  and ssbsect_crn=ssrmeet_crn(+)
		  and ssbsect_term_code=ssrmeet_term_code(+)
		  and ssbsect_crn=sirasgn_crn(+)
		  and ssbsect_term_code=sirasgn_term_code(+)
		  and sirasgn_primary_ind(+)='Y'
		  and sirasgn_pidm=i.spriden_pidm(+)
		  and i.spriden_change_ind(+) is null
		  and ssbsect_crn=ssrxlst_crn(+)
		  and ssbsect_term_code=ssrxlst_term_code(+)
		  and ssrxlst_xlst_group=ssbxlst_xlst_group(+)
		  and ssrxlst_term_code=ssbxlst_term_code(+)
		  and ssbsect_subj_code = subj
		  order by subj,
		    crs,
			sec;
			
begin
	mytab:='	';
	/* mytab:=','; */
	curcrn:='XXXXX';
	
	owa_util.mime_header('application/vnd.ms-excel', TRUE);

--	IF NOT twgkwbis.F_ValidUser(pidm, close_http_header=>FALSE) THEN
--		return;
--	END IF;
	
	/* owa_util.http_header_close; */
	htp.print('TERM'||mytab||'CRN'||mytab||'SUBJ'||mytab||'CRS'||mytab||'SEC'||mytab||'TITLE'||mytab||'DAYS'||mytab||'TIMES'||mytab||
	          'LOCATION'||mytab||'INSTRUCTOR'||mytab||'CL'||mytab||'ENRL'||mytab||'MAX');
	FOR mystuff in dlcourseinfo
	LOOP
		if mystuff.crn=curcrn then
			htp.print(''||mytab||''||mytab||''||mytab||''||mytab||''||mytab||
		          ''||mytab||mystuff.day||mytab||mystuff.time||mytab||mystuff.loc||mytab||
				  ''||mytab||''||mytab||''||mytab||'');
		else
			curcrn:=mystuff.crn;
			htp.print(mystuff.term||mytab||mystuff.crn||mytab||mystuff.subj||mytab||mystuff.crs||mytab||mystuff.sec||mytab||
		          mystuff.title||mytab||mystuff.day||mytab||mystuff.time||mytab||mystuff.loc||mytab||
				  mystuff.instructor||mytab||mystuff.xlst||mytab||mystuff.enrl||mytab||mystuff.maxenrl);
		end if;
	END LOOP;

end LMUDownloadCCnt;

procedure LMUCrossListInfo (term in stvterm.stvterm_code%type default null,
						xlst in ssrxlst.ssrxlst_xlst_group%type default null)
is
  rcount	number;

   cursor xlstinfo
   is
   select ssbsect_term_code term,
			ssbsect_crn crn,
			ssbsect_subj_code subj,
			ssbsect_crse_numb crs,
			ssbsect_seq_numb sec,
			decode(ssbsect_crse_title,
			  '', a.scbcrse_title, ssbsect_crse_title) title,
			decode(ssrmeet_sun_day||ssrmeet_mon_day||ssrmeet_tue_day||ssrmeet_wed_day||ssrmeet_thu_day||ssrmeet_fri_day||ssrmeet_sat_day,
			  '', 'TBA',
			  ssrmeet_sun_day||ssrmeet_mon_day||ssrmeet_tue_day||ssrmeet_wed_day||ssrmeet_thu_day||ssrmeet_fri_day||ssrmeet_sat_day) day,
			decode(ssrmeet_begin_time,
			  '', 'TBA',
			  ssrmeet_begin_time||'-'||ssrmeet_end_time) time,
			decode(ltrim(rtrim(ssrmeet_bldg_code)),
			  '', 'TBA',
			  ltrim(rtrim(ssrmeet_bldg_code))||' '||ltrim(rtrim(ssrmeet_room_code))) loc,
			decode(i.spriden_last_name,
			  '', 'FACULTY',
			  i.spriden_last_name||', '||i.spriden_first_name||' '||i.spriden_mi) instructor,
			ssbsect_enrl enrl,
			ssbsect_max_enrl maxenrl
		  from ssbsect,
		    scbcrse a,
			ssrmeet,
			sirasgn,
			spriden i,
			ssrxlst
		  where ssrxlst_term_code = term
		  and ssrxlst_xlst_group = xlst
		  and ssrxlst_crn=ssbsect_crn
		  and ssrxlst_term_code=ssbsect_term_code
		  and ssbsect_subj_code=a.scbcrse_subj_code
		  and ssbsect_crse_numb=a.scbcrse_crse_numb
		  and a.scbcrse_eff_term=
		   (select max(b.scbcrse_eff_term) from scbcrse b
		    where b.scbcrse_eff_term<=ssbsect_term_code
			and a.scbcrse_subj_code=b.scbcrse_subj_code
			and a.scbcrse_crse_numb=b.scbcrse_crse_numb)
		  and ssbsect_crn=ssrmeet_crn(+)
		  and ssbsect_term_code=ssrmeet_term_code(+)
		  and ssbsect_crn=sirasgn_crn(+)
		  and ssbsect_term_code=sirasgn_term_code(+)
		  and sirasgn_primary_ind(+)='Y'
		  and sirasgn_pidm=i.spriden_pidm(+)
		  and i.spriden_change_ind(+) is null
		  order by subj,
		    crs,
			sec;

begin
   IF NOT twgkwbis.F_ValidUser(pidm) THEN
        return;
   END IF;

	/* Show Cross-Listed Course Information */
    twgkwbis.P_OpenDoc('lmuhwskccnt.LMUCrossListInfo');
   /* You can enter info text for GENERAL if you want to display any additional information */
   twgkwbis.P_DispInfo('lmuhwskccnt.LMUCrossListInfo','GENERAL');
   
   	htp.nl;

	rcount:=0;

   FOR mystuff IN xlstinfo
   LOOP
		if rcount=0 then
			twgkfrmt.P_TableOpen('DATADISPLAY');
   			twgkfrmt.P_TableRowOpen('left');
   			twgkfrmt.P_TableDataLabel('CRN',calign=>'center');
   			twgkfrmt.P_TableDataLabel('SUBJ',calign=>'center');
   			twgkfrmt.P_TableDataLabel('CRS',calign=>'center');
   			twgkfrmt.P_TableDataLabel('SEC',calign=>'center');
   			twgkfrmt.P_TableDataLabel('TITLE',calign=>'left');
   			twgkfrmt.P_TableDataLabel('DAYS',calign=>'center');
   			twgkfrmt.P_TableDataLabel('TIMES',calign=>'center');
			twgkfrmt.P_TableDataLabel('LOCATION',calign=>'center');
			twgkfrmt.P_TableDataLabel('INSTRUCTOR',calign=>'left');
			twgkfrmt.P_TableDataLabel('ENRL',calign=>'center');
			twgkfrmt.P_TableDataLabel('MAX',calign=>'center');
   			twgkfrmt.P_TableRowClose;
			rcount:=1;
		end if;
		twgkfrmt.P_TableRowOpen('left');	
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.crn),calign=>'center');
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.subj),calign=>'center');
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.crs),calign=>'center');
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.sec),calign=>'center');
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.title),calign=>'left');
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.day),calign=>'center');
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.time),calign=>'center');
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.loc),calign=>'center');
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.instructor),calign=>'left');
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.enrl),calign=>'center');
		twgkfrmt.P_TableData(twgkfrmt.F_PrintBold(mystuff.maxenrl),calign=>'center');
		twgkfrmt.P_TableRowClose;
   END LOOP;
   twgkfrmt.P_TableClose;

	  htp.nl;

   twgkwbis.P_CloseDoc;
end LMUCrossListInfo;

end lmuhwskccnt;
/

show errors

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

set scan on