--======================================================================== -- NAME: SZPSCHD.SQL -- PURPOSE: SSB Schedule of subjects in Spreadsheet format -- DATE WRITTEN: 06/17/2011 M CHEN -- NOTE: -- TABLES: SCBCRSE, SSBSECT -- RELATED FORMS: SCACRSE -- PARMS: #1: Term Code -- #2: College Code -- #3: Subject code -- Revision History -- WHEN WHO WHAT -- 09/07/2011 MC Changed to display middle initial instead of full middle name for instructor -- 09/12/2011 MC Changed to correct attribute fetching -- 10/18/2013 MC Ticket # 792 Removed college check in course selction -- 12/14/2015 FCN Ticket #1531 Add "Grade Mode" and "Grade Mode Default" columns -- 8/6/2019 LZ TASK0018279 AND commented out "AND ssbsect_voice_avail = 'Y'" -- 2/7/2022 LZ TASK0040615 Add the Course’s Primary Instructor's Active Auburn Email to the SZPSCHD report after Instructor name. --======================================================================== set echo off; set verify off; set feedback off; set serveroutput ON SIZE UNLIMITED; whenever sqlerror exit failure; DECLARE v_term_code varchar2(6); v_college_code varchar2(2); v_subjects varchar2(120); v_output_line varchar2(2000); function f_get_default_grade_mode( in_subj varchar2, in_numb varchar2, in_term varchar2) return varchar2 is v_return_val varchar2(1); v_default_gmod varchar2(1); v_line_out varchar2(1000); cursor c_get_default_mode is select scrgmod_subj_code, scrgmod_crse_numb, scrgmod_gmod_code, scrgmod_default_ind from scrgmod where scrgmod_subj_code = in_subj and scrgmod_crse_numb = in_numb and scrgmod_default_ind = 'D' and scrgmod_eff_term <= in_term; v_default_mode_record c_get_default_mode%rowtype; begin v_default_gmod := null; open c_get_default_mode; fetch c_get_default_mode into v_default_mode_record; if (c_get_default_mode%found) then v_default_gmod := v_default_mode_record.scrgmod_gmod_code; else v_default_gmod := null; end if; close c_get_default_mode; v_return_val := null; v_return_val := v_default_gmod; return v_return_val; exception when others then dbms_output.put_line('Error found in f_get_default_grade_mode on SUBJ/NUMB['||in_subj || '/' || in_numb ||']'); dbms_output.put_line('SQLCODE:[' || SQLCODE || ']'); dbms_output.put_line('SQLERRM:[' || SQLERRM || ']'); v_line_out := dbms_utility.format_error_backtrace; dbms_output.put_line('ERROR BACKTRACE:[' ||v_line_out || ']'); v_line_out := dbms_utility.format_call_stack; dbms_output.put_line('ERROR CALL STACK:[' ||v_line_out || ']'); end f_get_default_grade_mode ; PROCEDURE P_Get_Multi_Parm(JOB_NAME IN varchar2, ONE_UP_NO IN number, DB_NAME IN varchar2, PARM_NO IN varchar2, PARM_STRING OUT varchar2) IS CURSOR parm_cursor IS SELECT gjbprun_value FROM gjbprun WHERE upper(gjbprun_job) = upper(JOB_NAME) AND gjbprun_one_up_no = ONE_UP_NO AND gjbprun_number = PARM_NO; BEGIN FOR parm_rec IN parm_cursor LOOP parm_string := parm_string || parm_rec.gjbprun_value ; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN parm_string := NULL; END P_Get_Multi_Parm; --======================================================================================================== PROCEDURE p_getsubj(p_term_code IN varchar2, p_college_code IN varchar2, p_subjects IN varchar2) IS v_number_of_subjects number(2) := 0; v_term_code varchar2(6); v_college_code varchar2(2); v_subjects varchar2(120); v_subject varchar2(4); v_subject_code varchar2(4); v_crn ssbsect.ssbsect_crn%TYPE; v_grade_mod ssbsect.ssbsect_gmod_code%type; v_from number(5); v_comma number(5); v_comma_char varchar2(1); v_size number(5); PROCEDURE p_listcrse(p_term_code IN varchar2, p_college_code IN varchar2, p_subject IN varchar2, p_number_of_subjects IN number) IS -- CURSOR sirasgn_c (term_code_in stvterm.stvterm_code%TYPE, crn_in ssbsect.ssbsect_crn%TYPE) IS SELECT spriden_first_name || ' ' || substr (spriden_mi, 1,1) || ' ' || spriden_last_name instr_name, goremal_email_address email_addr FROM sirasgn join spriden on sirasgn_pidm = spriden_pidm left join goremal on sirasgn_pidm = goremal_pidm where 1=1 AND sirasgn_term_code = term_code_in AND sirasgn_crn = crn_in AND sirasgn_primary_ind = 'Y' AND spriden_change_ind IS NULL and goremal_emal_code = 'AU' and goremal_status_ind = 'A'; -- CURSOR ssrattr_c (term_code_in stvterm.stvterm_code%TYPE, crn_in ssbsect.ssbsect_crn%TYPE) IS SELECT * FROM stvattr, ssrattr WHERE ssrattr_attr_code = stvattr_code AND ssrattr_term_code = term_code_in AND ssrattr_crn = crn_in; --(5) CURSOR ssbsect_camp_c(term_in stvterm.stvterm_code%TYPE, crn_in ssbsect.ssbsect_crn%TYPE) IS SELECT ssbsect_camp_code FROM stvcamp, ssbsect WHERE stvcamp_code = ssbsect_camp_code AND ssbsect_term_code = term_in AND ssbsect_crn = crn_in AND ssbsect_camp_code IS NOT NULL; --(11) CURSOR scbcrse_cred_c(term_code_in stvterm.stvterm_code%TYPE, subj_in stvsubj.stvsubj_code%TYPE, crse_in scrlevl.scrlevl_crse_numb%TYPE) IS SELECT * FROM scbcrse A WHERE A.scbcrse_subj_code = subj_in AND A.scbcrse_crse_numb = crse_in AND A.scbcrse_eff_term = (SELECT MAX (b.scbcrse_eff_term) FROM scbcrse b WHERE b.scbcrse_subj_code = A.scbcrse_subj_code AND b.scbcrse_crse_numb = A.scbcrse_crse_numb AND b.scbcrse_eff_term <= term_code_in); scbcrse_cred_rec scbcrse_cred_c%ROWTYPE; --(10) CURSOR ssrsyln_c (term_in stvterm.stvterm_code%TYPE, crn_in ssbsect.ssbsect_crn%TYPE) IS SELECT ssrsyln_long_course_title FROM ssrsyln WHERE ssrsyln_crn = crn_in AND ssrsyln_term_code = term_in; --(4) CURSOR ssrmeet_exists_c (term_code_in ssrmeet.ssrmeet_term_code%TYPE, crn_in ssrmeet.ssrmeet_crn%TYPE) IS SELECT 1 FROM ssrmeet WHERE ssrmeet_term_code = term_code_in AND ssrmeet_crn = crn_in; --(12) CURSOR crsesect_c (term VARCHAR2, college VARCHAR2, subj VARCHAR2) IS SELECT * FROM stvssts, stvsubj, ssrmeet, ssbsect, scbcrse A WHERE ssbsect_term_code = term AND (subj IS NULL OR subj = '%' OR INSTR (subj, ssbsect_subj_code ) > 0 -- INSTR (:subj, CHR (9) || ssbsect_subj_code || CHR (9)) > 0 ) -- AND UPPER (ssbsect_crse_numb) LIKE -- UPPER (crse) -- AND ssbsect_voice_avail = 'Y' --8/6/2019 LZ AND ssrmeet_term_code (+) = ssbsect_term_code AND ssrmeet_crn (+) = ssbsect_crn AND stvsubj_code = ssbsect_subj_code AND stvsubj_disp_web_ind = 'Y' AND stvssts_code = ssbsect_ssts_code AND stvssts_reg_ind = 'Y' AND stvssts_active_ind = 'A' AND ssbsect_subj_code = A.scbcrse_subj_code AND ssbsect_crse_numb = A.scbcrse_crse_numb AND A.scbcrse_coll_code = college -- AND A.scbcrse_coll_code is not null AND A.scbcrse_eff_term = (SELECT MAX (scbcrse_eff_term) FROM scbcrse x WHERE x.scbcrse_subj_code = ssbsect_subj_code AND x.scbcrse_crse_numb = ssbsect_crse_numb -- AND x.scbcrse_coll_code = A.scbcrse_coll_code -- mod made on 10/18/2013 AND x.scbcrse_eff_term <= ssbsect_term_code) ORDER BY stvsubj_desc, ssbsect_crse_numb, ssbsect_seq_numb, ssbsect_crn; --(13) CURSOR sfrrsts_c (term_in VARCHAR2, ptrm_in VARCHAR2) IS SELECT 'X' FROM sfrrsts WHERE sfrrsts_rsts_code = SUBSTR (f_stu_getwebregsrsts ('R'), 1, 2) AND sfrrsts_term_code = term_in AND sfrrsts_ptrm_code = ptrm_in AND trunc(sysdate) BETWEEN TRUNC (sfrrsts_start_date) AND TRUNC (sfrrsts_end_date); --(14) CURSOR ssrrsts_c (term VARCHAR2, crn VARCHAR2) IS SELECT * FROM ssrrsts -- it is empty WHERE ssrrsts_rsts_code = SUBSTR (f_stu_getwebregsrsts ('R'), 1, 2) AND ssrrsts_term_code = term AND ssrrsts_crn = crn; v_number_of_subjects number(2); v_term_code varchar2(6); v_college_code varchar2(2); v_subject varchar2(4); v_subject_code varchar2(4); v_crn ssbsect.ssbsect_crn%TYPE; v_output_line varchar2(2000); v_credit_hr_desc varchar2(20); v_camp ssbsect.ssbsect_camp_code%TYPE; v_title ssrsyln.ssrsyln_long_course_title%TYPE; v_xseats_max_enrl ssbxlst.ssbxlst_max_enrl%TYPE; v_xseats_enrl ssbxlst.ssbxlst_enrl%TYPE; v_xseats_avail ssbxlst.ssbxlst_seats_avail%TYPE; v_seats_avail ssbsect.ssbsect_seats_avail%TYPE; v_meet_days_desc varchar2(10); v_meet_time_desc varchar2(20); v_meet_row_count number (2); v_status varchar2(10); v_ssb_term_code ssbsect.ssbsect_term_code%type; v_crse_desc varchar2(300); v_rpt_crn ssbsect.ssbsect_crn%type; v_ssbsect_crn ssbsect.ssbsect_crn%type; v_ssbsect_subj_code ssbsect.ssbsect_subj_code%type; v_ssbsect_crse_numb ssbsect.ssbsect_crse_numb%type; v_ssbsect_seq_numb ssbsect.ssbsect_seq_numb%type; v_ssbsect_gmod_code ssbsect.ssbsect_gmod_code%type; v_default_gmod scrgmod.scrgmod_gmod_code%type; v_rpt_ssbsect_ssts ssbsect.ssbsect_ssts_code%type; v_meet_desc varchar2(100); v_counts_desc varchar2(100); v_instr_count number (2); v_instr_name varchar2(100); v_email_addr goremal.GOREMAL_EMAIL_ADDRESS%type; v_ptrm_desc varchar2(20); v_room_desc varchar2(20); v_attr_desc varchar2(200); v_search_term_ind varchar2(1); v_sfrrsts_ind varchar2(1); v_search_term_allowed BOOLEAN := FALSE; v_sfrrsts_found BOOLEAN := FALSE; v_olr_rw_defined BOOLEAN := FALSE; v_hold_crn ssbsect.ssbsect_crn%TYPE; PROCEDURE check_xlist (term_code_in stvterm.stvterm_code%TYPE, crn_in ssbsect.ssbsect_subj_code%TYPE, xseats_max_enrl OUT ssbxlst.ssbxlst_max_enrl%TYPE, xseats_enrl OUT ssbxlst.ssbxlst_enrl%TYPE, xseats_avail OUT ssbxlst.ssbxlst_seats_avail%TYPE) IS ssbxlst_row ssbxlst%ROWTYPE; BEGIN xseats_max_enrl := NULL; xseats_enrl := NULL; xseats_avail := NULL; SELECT B.* INTO ssbxlst_row FROM SSBXLST B,SSRXLST WHERE SSRXLST_CRN = crn_in AND SSRXLST_TERM_CODE = term_code_in AND SSBXLST_XLST_GROUP = SSRXLST_XLST_GROUP AND SSRXLST_TERM_CODE = SSBXLST_TERM_CODE AND ROWNUM = 1; xseats_max_enrl := ssbxlst_row.ssbxlst_max_enrl; xseats_enrl := ssbxlst_row.ssbxlst_enrl; xseats_avail := ssbxlst_row.ssbxlst_seats_avail; EXCEPTION WHEN OTHERS THEN NULL; END check_xlist ; -------------------------------------------------------------------------- PROCEDURE p_searchchk_term ( term_in stvterm.stvterm_code%TYPE, search_term_allow IN OUT VARCHAR2 ) IS stvterm_rec soklibs.update_term_select_c%ROWTYPE; BEGIN search_term_allow := 'Y'; -- -- Check if term is registerable or view only. -- And check if web registration open for current date. -- ===================================================== OPEN soklibs.update_term_select_c (term_in); FETCH soklibs.update_term_select_c INTO stvterm_rec; IF soklibs.update_term_select_c%NOTFOUND THEN search_term_allow := 'N'; CLOSE soklibs.update_term_select_c; RETURN; END IF; CLOSE soklibs.update_term_select_c; -- -- Check if register code defined for the current term/date. -- ========================================================= IF NOT bwcksams.F_ValidRegDate (term_in) THEN search_term_allow := 'N'; RETURN; END IF; END p_searchchk_term; BEGIN -- p_listcrse v_term_code := p_term_code; v_college_code := p_college_code; v_subject := p_subject; v_number_of_subjects := p_number_of_subjects; -- dbms_output.put_line ('I am here, inside p_listcrse, v_term_code = ' || v_term_code || ' v_subject ' || v_subject); v_search_term_ind := NULL; p_searchchk_term (v_term_code, v_search_term_ind); IF v_search_term_ind = 'Y' THEN v_search_term_allowed := TRUE; ELSE v_search_term_allowed := FALSE; END IF; v_output_line := NULL; <> FOR sect_rec IN crsesect_c (v_term_code,v_college_code, v_subject) LOOP v_output_line := NULL; v_status := NULL; v_crse_desc := NULL; v_meet_desc := NULL; v_counts_desc := NULL; v_crn := null; v_crn := sect_rec.ssbsect_crn; v_rpt_ssbsect_ssts := null; v_rpt_ssbsect_ssts := sect_rec.ssbsect_ssts_code; -- dbms_output.put_line ('I am here, inside p_listcrse, v_crn = ' || v_crn); -- Don't display duplicates IF v_hold_crn IS NOT NULL AND sect_rec.ssrmeet_crn IS NOT NULL AND sect_rec.ssrmeet_crn = v_hold_crn THEN GOTO next_row; END IF; IF sect_rec.ssbsect_credit_hrs IS NOT NULL THEN v_credit_hr_desc := LTRIM (to_char(sect_rec.ssbsect_credit_hrs,'9990D990')); ELSE scbcrse_cred_rec := NULL; OPEN scbcrse_cred_c (v_term_code, sect_rec.ssbsect_subj_code, sect_rec.ssbsect_crse_numb); FETCH scbcrse_cred_c INTO scbcrse_cred_rec; CLOSE scbcrse_cred_c; IF scbcrse_cred_rec.scbcrse_credit_hr_ind = 'TO' THEN v_credit_hr_desc := NVL ( LTRIM (TO_CHAR (scbcrse_cred_rec.scbcrse_credit_hr_low, '9990D990' ) ),TO_CHAR (0, '0D99')) || '-' || LTRIM (TO_CHAR (scbcrse_cred_rec.scbcrse_credit_hr_high,'9990D990' )); ELSIF scbcrse_cred_rec.scbcrse_credit_hr_ind = 'OR' THEN v_credit_hr_desc := NVL ( LTRIM (TO_CHAR (scbcrse_cred_rec.scbcrse_credit_hr_low, '9990D990' ) ),TO_CHAR (0, '0D99')) || '/' || LTRIM (TO_CHAR (scbcrse_cred_rec.scbcrse_credit_hr_high,'9990D990' )); ELSE v_credit_hr_desc := LTRIM (TO_CHAR (scbcrse_cred_rec.scbcrse_credit_hr_low,'9990D990' )); END IF; END IF; v_ssbsect_crn := sect_rec.ssbsect_crn; v_ssbsect_subj_code := sect_rec.ssbsect_subj_code; v_ssbsect_crse_numb := sect_rec.ssbsect_crse_numb; v_ssbsect_seq_numb := sect_rec.ssbsect_seq_numb; v_ssbsect_gmod_code := sect_rec.ssbsect_gmod_code; v_default_gmod := null; v_default_gmod := f_get_default_grade_mode(sect_rec.ssbsect_subj_code, sect_rec.ssbsect_crse_numb, v_term_code); v_rpt_crn := null; v_rpt_crn := sect_rec.ssbsect_crn; v_camp := NULL; OPEN ssbsect_camp_c (v_term_code, v_crn); FETCH ssbsect_camp_c INTO v_camp; CLOSE ssbsect_camp_c; v_crse_desc := -- sect_rec.ssbsect_crn || ',' || sect_rec.ssbsect_subj_code || ',' || sect_rec.ssbsect_crse_numb || ',' || sect_rec.ssbsect_seq_numb || ',' || sect_rec.ssbsect_gmod_code || ',' || v_default_gmod; v_title := NULL; OPEN ssrsyln_c (v_term_code, v_crn); FETCH ssrsyln_c INTO v_title; CLOSE ssrsyln_c; IF v_title IS NULL THEN v_title := sect_rec.scbcrse_title; END IF; -- this is the crse info before checking the meeting info -- v_crse_desc := v_crse_desc || ',' || v_camp || ',' || v_credit_hr_desc || ',"' || v_title || '"'; v_crse_desc := v_crse_desc || ',' || v_credit_hr_desc || ',"' || v_title || '"'; -- -- For Traditional courses, check whether ptrm rsts_code RE/RW's date -- range excludes today -- ================================================================== v_sfrrsts_found := FALSE; IF sect_rec.ssbsect_ptrm_code IS NOT NULL THEN OPEN sfrrsts_c (v_term_code, sect_rec.ssbsect_ptrm_code); FETCH sfrrsts_c INTO v_sfrrsts_ind; IF sfrrsts_c%FOUND THEN v_sfrrsts_found := TRUE; END IF; CLOSE sfrrsts_c; END IF; -- -- For OLR courses, check whether rsts_code RW is defined for the term -- =================================================================== v_olr_rw_defined := FALSE; IF sect_rec.ssbsect_reg_from_date IS NOT NULL AND sect_rec.ssbsect_ptrm_code IS NULL THEN FOR ssrrsts IN ssrrsts_c (v_term_code, sect_rec.ssbsect_crn) LOOP v_olr_rw_defined := TRUE; EXIT; END LOOP; END IF; -- -- If meeting times have not been assigned, then show TBA -- for day and time. Show capacity numbers. v_attr_desc := NULL; IF sect_rec.ssrmeet_crn IS NULL THEN v_meet_days_desc := 'TBA'; v_meet_time_desc := 'TBA'; v_meet_desc := v_meet_days_desc || ',' || v_meet_time_desc ; --- Check for cross list courses. check_xlist (v_term_code, v_crn, v_xseats_max_enrl, v_xseats_enrl,v_xseats_avail); v_seats_avail := sect_rec.ssbsect_seats_avail; IF ( v_seats_avail <= 0 OR v_xseats_avail <= 0) THEN v_status := 'C' ; -- Closed ELSIF sect_rec.ssbsect_reg_from_date IS NOT NULL -- else check different conditions. (1) OLR course, whose reg dates range excludes today AND sect_rec.ssbsect_ptrm_code IS NULL AND ( (TRUNC (SYSDATE) NOT BETWEEN sect_rec.ssbsect_reg_from_date AND sect_rec.ssbsect_reg_to_date) OR (TRUNC (SYSDATE) > sect_rec.ssbsect_learner_regstart_tdate) OR NOT v_olr_rw_defined ) THEN v_status := 'NR'; ELSIF sect_rec.ssbsect_ptrm_code IS NOT NULL -- Traditional course, and ptrm rsts_code date range excludes today AND NOT v_sfrrsts_found THEN v_status := 'NR'; ELSIF NOT v_search_term_allowed -- Registration restricted for the current term/date THEN v_status := 'NR'; ELSE v_status := NULL; END IF; v_ssb_term_code := sect_rec.ssbsect_term_code ; v_counts_desc := sect_rec.ssbsect_max_enrl || ',' || sect_rec.ssbsect_enrl || ',' || v_seats_avail || ',' || sect_rec.ssbsect_wait_capacity || ',' || sect_rec.ssbsect_wait_count || ',' || sect_rec.ssbsect_wait_avail || ',' || nvl(v_xseats_max_enrl,0) || ',' || nvl(v_xseats_enrl,0) || ',' || nvl(v_xseats_avail,0); v_ptrm_desc := TO_CHAR (sect_rec.ssbsect_ptrm_start_date, 'MM/DD') || '-' || TO_CHAR (sect_rec.ssbsect_ptrm_end_date, 'MM/DD'); IF v_attr_desc IS NULL THEN FOR ssrattr_rec IN ssrattr_c (v_term_code , v_crn) LOOP IF ssrattr_c%NOTFOUND THEN EXIT; ELSE v_attr_desc := v_attr_desc ||' ' || ssrattr_rec.stvattr_desc; END IF; END LOOP; END IF; --BEGIN fcN deug if (v_crse_desc like 'GLOB%') then dbms_output.put_line('593:[' || sect_rec.scbcrse_coll_code || ']'); end if; ---FCN DEBUG --dbms_output.put_line (v_output_line); v_output_line := v_ssb_term_code || ',' || v_camp || ',' || v_rpt_ssbsect_ssts || ',' || -- v_status || ',' || v_rpt_crn || ',' || v_crse_desc || ',' || v_meet_desc || ',' || v_counts_desc; v_instr_count := 0; FOR sirasgn_rec IN sirasgn_c (v_term_code, v_crn) LOOP v_email_addr := null; v_instr_count := v_instr_count + 1; v_instr_name := sirasgn_rec.instr_name; --sirasgn_rec.spriden_first_name || ' ' || sirasgn_rec.spriden_mi || ' ' || sirasgn_rec.spriden_last_name; v_email_addr := sirasgn_rec.email_addr; IF v_instr_count > 1 THEN v_output_line := ',,,,,,,,,,,,,,,,,,' ; END IF; v_output_line := v_output_line || ',"' || v_instr_name || '",' || v_email_addr || ','|| v_ptrm_desc || ',' || 'TBA' || ',' || v_attr_desc ; gzpadmn.p_spool_lis('&1','&2',NULL ,NULL,v_output_line); --BEGIN FCN DEBUG if (regexp_like(v_crse_desc,'GLOB')) then dbms_output.put_line('620:['|| v_crse_desc || ']'); gzpadmn.p_spool_lis('&1','&2',NULL ,NULL,v_output_line); end if; --END FCN DEBUG END LOOP; IF v_instr_count = 0 THEN v_output_line := v_output_line || ',TBA' || ',' || ' ' || ',' || v_ptrm_desc || ',' || 'TBA' || ',' || v_attr_desc ; --BEGIN FCN DEBUG if (regexp_like(v_crse_desc,'GLOB')) then dbms_output.put_line('633:['|| v_crse_desc || ']'); -- gzpadmn.p_spool_lis('&1','&2',NULL ,NULL,v_output_line); end if; --END FCN DEBUG gzpadmn.p_spool_lis('&1','&2',NULL ,NULL,v_output_line); --dbms_output.put_line (v_output_line); END IF; --we will add instructor, pterm, building and attr here -- dbms_output.put_line (v_output_line); v_output_line := NULL; v_status := NULL; v_crse_desc := NULL; v_meet_desc := NULL; v_counts_desc := NULL; GOTO next_row; END IF; -- IF sect_rec.ssrmeet_crn IS NULL v_meet_row_count := 0; v_attr_desc := NULL; -- If meeting times DO exist, loop through the meeting times. FOR ssrmeet IN ssklibs.ssrmeetc (sect_rec.ssrmeet_crn, sect_rec.ssrmeet_term_code) LOOP v_meet_row_count := v_meet_row_count + 1; v_meet_days_desc := ( g$_date.nls_abv_day(ssrmeet.ssrmeet_mon_day) || g$_date.nls_abv_day(ssrmeet.ssrmeet_tue_day) || g$_date.nls_abv_day(ssrmeet.ssrmeet_wed_day) || g$_date.nls_abv_day(ssrmeet.ssrmeet_thu_day) || g$_date.nls_abv_day(ssrmeet.ssrmeet_fri_day) || g$_date.nls_abv_day(ssrmeet.ssrmeet_sat_day) || g$_date.nls_abv_day(ssrmeet.ssrmeet_sun_day) ); IF ssrmeet.ssrmeet_begin_time || ssrmeet.ssrmeet_end_time IS NULL THEN v_meet_time_desc := 'TBA'; ELSE v_meet_time_desc := (TO_CHAR (TO_DATE (ssrmeet.ssrmeet_begin_time, 'HH24MI'),'HH:MI AM') || '-' || TO_CHAR (TO_DATE (ssrmeet.ssrmeet_end_time, 'HH24MI'),'HH:MI AM')); END IF; v_meet_desc := v_meet_days_desc || ',' || v_meet_time_desc ; IF v_meet_row_count > 1 THEN v_output_line := ',,,,,,,,'; END IF; -- this is the crse info before checking the meeting info --v_crse_desc := v_crse_desc || v_camp || ',' || v_credit_hr_desc || ',"' || v_title || '",'; v_output_line := v_status || ',' || v_crse_desc || ',' || v_meet_desc || ',' || v_counts_desc ; --- Check for cross list courses. check_xlist (v_term_code, v_crn, v_xseats_max_enrl, v_xseats_enrl,v_xseats_avail); v_seats_avail := sect_rec.ssbsect_seats_avail; IF ( v_seats_avail <= 0 OR v_xseats_avail <= 0) THEN v_status := 'C' ; -- Closed ELSIF sect_rec.ssbsect_reg_from_date IS NOT NULL -- else check different conditions. (1) OLR course, whose reg dates range excludes today AND sect_rec.ssbsect_ptrm_code IS NULL AND ( (TRUNC (SYSDATE) NOT BETWEEN sect_rec.ssbsect_reg_from_date AND sect_rec.ssbsect_reg_to_date) OR (TRUNC (SYSDATE) > sect_rec.ssbsect_learner_regstart_tdate) OR NOT v_olr_rw_defined ) THEN v_status := 'NR'; ELSIF sect_rec.ssbsect_ptrm_code IS NOT NULL -- Traditional course, and ptrm rsts_code date range excludes today AND NOT v_sfrrsts_found THEN v_status := 'NR'; ELSIF NOT v_search_term_allowed -- Registration restricted for the current term/date THEN v_status := 'NR'; ELSE v_status := NULL; END IF; v_counts_desc := sect_rec.ssbsect_max_enrl || ',' || sect_rec.ssbsect_enrl || ',' || v_seats_avail || ',' || sect_rec.ssbsect_wait_capacity || ',' || sect_rec.ssbsect_wait_count || ',' || sect_rec.ssbsect_wait_avail || ',' || nvl(v_xseats_max_enrl,0) || ',' || nvl(v_xseats_enrl,0) || ',' || nvl(v_xseats_avail,0); v_ptrm_desc := TO_CHAR (sect_rec.ssbsect_ptrm_start_date, 'MM/DD') || '-' || TO_CHAR (sect_rec.ssbsect_ptrm_end_date, 'MM/DD'); IF (ssrmeet.ssrmeet_bldg_code IS NOT NULL) OR (ssrmeet.ssrmeet_room_code IS NOT NULL) THEN v_room_desc := ssrmeet.ssrmeet_bldg_code || ' ' || ssrmeet.ssrmeet_room_code; ELSE v_room_desc := 'TBA'; END IF; IF v_attr_desc IS NULL THEN FOR ssrattr_rec IN ssrattr_c (v_term_code , v_crn) LOOP IF ssrattr_c%NOTFOUND THEN EXIT; ELSE v_attr_desc := v_attr_desc || ' ' || ssrattr_rec.stvattr_desc; END IF; END LOOP; END IF; v_ssb_term_code := sect_rec.ssbsect_term_code; v_rpt_crn := sect_rec.ssbsect_crn; -- v_output_line := v_status || ',' || v_crse_desc || ',' || v_meet_desc || ',' || v_counts_desc; v_output_line := v_ssb_term_code || ',' || v_camp || ',' || v_rpt_ssbsect_ssts || ',' || -- v_status || ',' || v_rpt_crn || ',' || v_crse_desc || ',' || v_meet_desc || ',' || v_counts_desc; v_instr_count := 0; FOR sirasgn_rec IN sirasgn_c (v_term_code, v_crn) LOOP v_email_addr := null; v_instr_count := v_instr_count + 1; v_instr_name := sirasgn_rec.instr_name; --sirasgn_rec.spriden_first_name || ' ' || sirasgn_rec.spriden_mi || ' ' || sirasgn_rec.spriden_last_name; v_email_addr := sirasgn_rec.email_addr; IF v_instr_count > 1 THEN v_output_line := ',,,,,,,,,,,,,,,,,,' ; END IF; v_output_line := v_output_line || ',"' || v_instr_name || '",' || v_email_addr || ','|| v_ptrm_desc || ',' || v_room_desc || ',' || v_attr_desc ; gzpadmn.p_spool_lis('&1','&2',NULL ,NULL,v_output_line); --BEGIN fcN deug if (v_crse_desc like 'GLOB%') then dbms_output.put_line('777:[' || sect_rec.scbcrse_coll_code || ']'); end if; ---FCN DEBUG --dbms_output.put_line (v_output_line); END LOOP; IF v_instr_count = 0 THEN v_output_line := v_output_line || ',TBA' || ','|| ' ' || ',' || v_ptrm_desc || ',' || v_room_desc || ',' || v_attr_desc ; gzpadmn.p_spool_lis('&1','&2',NULL ,NULL,v_output_line); --BEGIN fcN deug if (v_crse_desc like 'GLOB%') then dbms_output.put_line('789:[' || sect_rec.scbcrse_coll_code || ']'); end if; ---END FCN DEBUG --dbms_output.put_line (v_output_line); END IF; v_status := NULL; END LOOP; -- ssrmee loop --dbms_output.put_line('CRN for course ' || v_number_of_subjects || ' : ' || v_subject || ssbsect_crn_rec.ssbsect_crse_numb || ' is ' || --ssbsect_crn_rec.ssbsect_crn); <> v_hold_crn := sect_rec.ssbsect_crn; NULL; END LOOP display_sections_loop; END p_listcrse; --============================================================================= function f_get_grade_mode(in_term varchar2, in_crn varchar2) return varchar2 is v_return_val varchar2(10); v_grade_mode varchar2(10); v_line_out varchar2(1000); cursor c_get_grade_mode is select ssbsect_term_code, ssbsect_crn, ssbsect_subj_code, ssbsect_crse_numb, ssbsect_gmod_code from ssbsect where ssbsect_term_code = in_term and ssbsect_crn = in_crn; v_grade_mode_record c_get_grade_mode%rowtype; begin open c_get_grade_mode; fetch c_get_grade_mode into v_grade_mode_record; if (c_get_grade_mode%found) then v_grade_mode :=v_grade_mode_record.ssbsect_gmod_code; else v_grade_mode := null; end if; close c_get_grade_mode; v_return_val := null; v_return_val := v_grade_mode; return v_return_val; exception when others then dbms_output.put_line('Error found in f_get_grade_mode on TERM:['||in_term|| '] in_crn:[' || in_crn || ']' ); dbms_output.put_line('SQLCODE:[' || SQLCODE || ']'); dbms_output.put_line('SQLERRM:[' || SQLERRM || ']'); v_line_out := dbms_utility.format_error_backtrace; dbms_output.put_line('ERROR BACKTRACE:[' ||v_line_out || ']'); v_line_out := dbms_utility.format_call_stack; dbms_output.put_line('ERROR CALL STACK:[' ||v_line_out || ']'); end f_get_grade_mode; --============================================================================= BEGIN -- get subject v_term_code := p_term_code; v_college_code := p_college_code; v_subjects := p_subjects; v_output_line := 'Term,Campus,Status,CRN,Subj,Crse,Sec,Grade Mode,Grade Mode Catalog Default,' || 'Cred,Title,Days,Time,Cap,Act,Rem,' || 'WL Cap,WL Act,WL Rem,XL Cap,XL Act,XL Rem,' || 'Instructor, AU Email, Date (MM/DD), Location,Attribute'; gzpadmn.p_spool_lis('&1','&2',NULL ,NULL,v_output_line); -- dbms_output.put_line (v_output_line); IF v_subjects = '%' THEN p_listcrse(v_term_code,v_college_code,v_subjects, v_number_of_subjects ); ELSE v_from := 1; IF v_subjects IS NOT NULL THEN v_size := instr(v_subjects,',',1) - 1; v_comma := v_size + 1; v_comma_char := substr(v_subjects, v_comma, 1); IF v_comma_char = ',' THEN -- more than one subjects have been entered with comma v_subject := substr(v_subjects, v_from, v_size); ELSE -- there is only one subject v_subject := v_subjects; -- only one subject dbms_output.put_line('Parm 03 single subject ' || v_subject); -- and do whatever you need to do with v_subject -- v_subject := null; -- no more subject so skip the while loop below END IF; ELSE -- no subject was entered dbms_output.put_line('Parm 02 subject is null'); END IF; -- dbms_output.put_line('1. v_from ' || v_from || ' v_comma ' || v_comma || ' v_comma_char ' || v_comma_char || ' v_size ' || v_size); WHILE v_subject IS NOT NULL LOOP v_number_of_subjects := v_number_of_subjects + 1; dbms_output.put_line('Parm 02 subject ' || v_number_of_subjects || ' : ' || v_subject); -- dbms_output.put_line('before p_listcrse: v_term_code ' || v_term_code || ' v_college_code ' || v_college_code || ' v_subject ' || v_subject || -- ' v_number_of_subjects ' || v_number_of_subjects); -- pass one subject to listcrse procedure p_listcrse(v_term_code, v_college_code, v_subject, v_number_of_subjects ); -- display info after pass one subject -- dbms_output.put_line('after p_listcres: v_from ' || v_from || ' v_comma ' || v_comma || ' v_comma_char ' || v_comma_char || ' v_size ' || v_size); -- dbms_output.put_line('v_subject = ' || v_subject); -- set the pointer for the next subject v_from := v_from + v_size + 1; v_comma := v_from + v_size ; v_subject := substr(v_subjects, v_from, v_size); v_comma_char := substr(v_subjects, v_comma, 1); -- dbms_output.put_line('loop:. v_from ' || v_from || ' v_comma ' || v_comma || ' v_comma_char ' || v_comma_char || ' v_size ' || v_size); IF v_comma_char <> ',' THEN -- two courese are entered together or it is the last subject IF v_comma_char IS NOT NULL AND v_size > 0 THEN -- two subjects are entered together v_from := v_from - 1; dbms_output.put_line('It is okay to leave out the comma for the last subject on the parm line, but we need to move one char back.' ); dbms_output.put_line('fixed: v_from ' || v_from || ' v_comma ' || v_comma || ' v_comma_char ' || v_comma_char || ' v_size ' || v_size); ELSE dbms_output.put_line('This is the last subject ' || v_subject); END IF; END IF; END LOOP; END IF; END p_getsubj; --============================================================================= -- BEGIN MAIN --============================================================================= BEGIN dbms_output.enable(null); v_term_code := gzpadmn.F_Get_Parm('&1','&2','&4','01'); v_college_code := gzpadmn.F_get_parm ('&1','&2','&4','02'); dbms_output.put_line('Job_Name: &1'||' '||'One_Up_No: &2'||' '||'DB_Name: &4'); dbms_output.put_line('Run by: &3'); dbms_output.put_line('Parm 01: Term code ' || v_term_code ); dbms_output.put_line('Parm 02: College code ' || v_college_code ); P_Get_Multi_Parm('&1','&2','&4','03', v_subjects); dbms_output.put_line('Multiple Parm 03 entered : ' || v_subjects ); dbms_output.put_line(NULL); p_getsubj(v_term_code,v_college_code,v_subjects); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line('An Error occured in SZPSCHD'); DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack); DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_backtrace); END; / EXIT SUCCESS COMMIT;