--======================================================================== -- OBJECT TYPE: PL/SQL -- OBJECT NAME: SZP3011.SQL -- AUTHOR: M Chen -- DATE WRITTEN: 02/14/2007 -- DESCRIPTION: Update SFRSTCR Table. Update null grades to "NR" grades prior to -- rolling to academic history -- TABLES: SATURN.SFRSTCR -- PARMS: #1: TERM_CODE FOR GRADE UPDATE (vterm) -- #2: RUNMODE for A(udit) or U(pdate) (v_runmode) -- GJAPDEF Name: Update null grades to NR,IN,IP -- -- REVISION HISTORY -- WHEN WHO WHAT -- 02/20/2007 MC Changed v_jobname = '&2' to '&1' -- 10/30/2007 MC Changed to print only 20 records on the report -- 12/14/2007 MC Changed to print the entire report -- 12/17/2007 MC Changed to include gradable indicator check and Registration Status -- 12/18/2007 MC Changed to keep grade_date untouched -- 05/30/2008 ?? Changed to add part_of_term parm (Log added by MC on 4/27/2011) -- 05/02/2011 MC Changed to add a parm VET_MED (Y/N) and skip ssbsect_subj_code = ('VMED', 'VBMS') if Y. -- 01/27/2014 RC Removed the course VBMS from the VET_MED Skip -- 03/28/2014 Added logic to set some courses to IP -- 05/23/2008 Rodney Clark Added the ability to request part of term -- 07/03/2014 Rodney Clark Added various courses to set to IP -- 08/02/2016 Rodney Clark Made the following changes -- BIOL2425 (sections: 002) – change -- BIOL3075 (all sections) - add -- BIOL4515 (all sections) - keep -- BIOL4565 (all sections) - delete -- BIOL4575 (all sections) - delete -- BIOL4920 (Sections: 003) -delete -- BIOL4970 (sections: 014, 021, 031) - change -- BIOL5455 (all sections) -delete -- BIOL7970 (sections: 005, 009) - change -- 05/12/2017 LZ StuDev #1725 Externalized the changes needed -- in the process for the Marine Biology courses -- 07/16/2019 FCN0001 StuDev#2690 Remove section for AVMF --======================================================================== set echo off; set verify off; set feedback off; set serveroutput on size unlimited; whenever sqlerror exit failure; DECLARE --Pull Rows from SFRSTCR cursor sfrstcr_c1 (vterm varchar2, v_pot varchar2)is select sfrstcr_pidm, sfrstcr_crn, sfrstcr_grde_code, ssbsect_subj_code, ssbsect_crse_numb, ssbsect_seq_numb, sfrstcr_rsts_code, ssbsect_gradable_ind, spriden_id, spriden_first_name || ' ' || spriden_last_name NAME from sfrstcr, ssbsect, spriden where sfrstcr_term_code = vterm and sfrstcr_ptrm_code = v_pot and sfrstcr_grde_code is null and sfrstcr_term_code = ssbsect_term_code and sfrstcr_crn = ssbsect_crn and sfrstcr_rsts_code in ('RW', 'RE') and sfrstcr_pidm = spriden_pidm and spriden_change_ind is null and ssbsect_gradable_ind = 'Y' ; -- get individual courses and include Vet Med cursor get_vet_med_ind_cur(v_term varchar2, v_pot varchar2) is select sfrstcr_pidm, sfrstcr_crn, sfrstcr_grde_code, ssbsect_subj_code, ssbsect_crse_numb, ssbsect_seq_numb, sfrstcr_rsts_code, ssbsect_gradable_ind, spriden_id, spriden_first_name || ' ' || spriden_last_name NAME from sfrstcr, ssbsect, spriden where sfrstcr_term_code = v_term and sfrstcr_ptrm_code = v_pot and sfrstcr_grde_code is null and sfrstcr_term_code = ssbsect_term_code and sfrstcr_crn = ssbsect_crn and sfrstcr_rsts_code in ('RW', 'RE') and sfrstcr_pidm = spriden_pidm and spriden_change_ind is null and ssbsect_subj_code || ' ' || ssbsect_crse_numb in ( 'VMED 5601', 'VMED 5052', 'PYDI 5090', 'PYDI 5190', 'PYDI 5290', 'PYDI 5390', 'PYDI 5490', 'PYDI 5590') and ssbsect_gradable_ind = 'Y'; cursor get_non_vet_med_ind_cur(v_term varchar2, v_pot varchar2) is select sfrstcr_pidm, sfrstcr_crn, sfrstcr_grde_code, ssbsect_subj_code, ssbsect_crse_numb, ssbsect_seq_numb, sfrstcr_rsts_code, ssbsect_gradable_ind, spriden_id, spriden_first_name || ' ' || spriden_last_name NAME from sfrstcr, ssbsect, spriden where sfrstcr_term_code = v_term and sfrstcr_ptrm_code = v_pot and sfrstcr_grde_code is null and sfrstcr_term_code = ssbsect_term_code and sfrstcr_crn = ssbsect_crn and sfrstcr_rsts_code in ('RW', 'RE') and sfrstcr_pidm = spriden_pidm and spriden_change_ind is null and ssbsect_subj_code || ' ' || ssbsect_crse_numb in ( 'PYDI 5090', 'PYDI 5190', 'PYDI 5290', 'PYDI 5390', 'PYDI 5490', 'PYDI 5590') and ssbsect_gradable_ind = 'Y'; cursor get_mult_pydi_cur(v_term varchar2, v_pot varchar2) is select sfrstcr_pidm, sfrstcr_crn, sfrstcr_grde_code, ssbsect_subj_code, ssbsect_crse_numb, ssbsect_seq_numb, sfrstcr_rsts_code, ssbsect_gradable_ind, spriden_id, spriden_first_name || ' ' || spriden_last_name NAME from sfrstcr, ssbsect, spriden where sfrstcr_term_code = v_term and sfrstcr_ptrm_code = v_pot and sfrstcr_grde_code is null and sfrstcr_term_code = ssbsect_term_code and sfrstcr_crn = ssbsect_crn and sfrstcr_rsts_code in ('RW', 'RE') and sfrstcr_pidm = spriden_pidm and spriden_change_ind is null and (ssbsect_subj_code = 'PYDI' and ssbsect_crse_numb between 9000 and 9999) -- PYDI9000 through PYDI9999 and ssbsect_gradable_ind = 'Y'; cursor get_mult_pypp_cur(v_term varchar2, v_pot varchar2) is select sfrstcr_pidm, sfrstcr_crn, sfrstcr_grde_code, ssbsect_subj_code, ssbsect_crse_numb, ssbsect_seq_numb, sfrstcr_rsts_code, ssbsect_gradable_ind, spriden_id, spriden_first_name || ' ' || spriden_last_name NAME from sfrstcr, ssbsect, spriden where sfrstcr_term_code = v_term and sfrstcr_ptrm_code = v_pot and sfrstcr_grde_code is null and sfrstcr_term_code = ssbsect_term_code and sfrstcr_crn = ssbsect_crn and sfrstcr_rsts_code in ('RW', 'RE') and sfrstcr_pidm = spriden_pidm and spriden_change_ind is null and (ssbsect_subj_code = 'PYPP' and ssbsect_crse_numb between 5600 and 5700) -- PYPP5600 through PYPP5700 and ssbsect_gradable_ind = 'Y'; cursor get_mult_6_cur(v_term varchar2, v_pot varchar2) is select sfrstcr_pidm, sfrstcr_crn, sfrstcr_grde_code, ssbsect_subj_code, ssbsect_crse_numb, ssbsect_seq_numb, sfrstcr_rsts_code, ssbsect_gradable_ind, spriden_id, spriden_first_name || ' ' || spriden_last_name NAME from sfrstcr, ssbsect, spriden where sfrstcr_term_code = v_term and sfrstcr_ptrm_code = v_pot and sfrstcr_grde_code is null and sfrstcr_term_code = ssbsect_term_code and sfrstcr_crn = ssbsect_crn and sfrstcr_rsts_code in ('RW', 'RE') and sfrstcr_pidm = spriden_pidm and spriden_change_ind is null and ((ssbsect_subj_code not like ('PY%') or ssbsect_subj_code not like ('VMED') or ssbsect_subj_code not like ('VBMS')) and ssbsect_crse_numb > 5999 and ssbsect_crse_numb like '___6') -- Graduate Distant Learning Courses: Non-Pharmacy ('PY%') and Non-Vet School (VMED VBMS) Graduate courses ( numbers >5999) ending in a "6" and ssbsect_gradable_ind = 'Y'; cursor get_mult_acct_cur(v_term varchar2, v_pot varchar2) is select sfrstcr_pidm, sfrstcr_crn, sfrstcr_grde_code, ssbsect_subj_code, ssbsect_crse_numb, ssbsect_seq_numb, sfrstcr_rsts_code, ssbsect_gradable_ind, spriden_id, spriden_first_name || ' ' || spriden_last_name NAME from sfrstcr, ssbsect, spriden where sfrstcr_term_code = v_term and sfrstcr_ptrm_code = v_pot and sfrstcr_grde_code is null and sfrstcr_term_code = ssbsect_term_code and sfrstcr_crn = ssbsect_crn and sfrstcr_rsts_code in ('RW', 'RE') and sfrstcr_pidm = spriden_pidm and spriden_change_ind is null and (ssbsect_subj_code = 'ACCT' and ssbsect_crse_numb < 6000 and ssbsect_crse_numb like ('___3')) -- Accounting (ACCT) undergraduate (<6000) courses ending in a '3'. and ssbsect_gradable_ind = 'Y'; cursor get_mult_avmf_cur(v_term varchar2, v_pot varchar2) is select sfrstcr_pidm, sfrstcr_crn, sfrstcr_grde_code, ssbsect_subj_code, ssbsect_crse_numb, ssbsect_seq_numb, sfrstcr_rsts_code, ssbsect_gradable_ind, spriden_id, spriden_first_name || ' ' || spriden_last_name NAME from sfrstcr, ssbsect, spriden where sfrstcr_term_code = v_term and sfrstcr_ptrm_code = v_pot and sfrstcr_grde_code is null and sfrstcr_term_code = ssbsect_term_code and sfrstcr_crn = ssbsect_crn and sfrstcr_rsts_code in ('RW', 'RE') and sfrstcr_pidm = spriden_pidm and spriden_change_ind is null and (ssbsect_subj_code = 'AVMF' and ssbsect_crse_numb like ('___1')) -- Accounting (ACCT) undergraduate (<6000) courses ending in a '3'. and ssbsect_gradable_ind = 'Y'; -- Start Biology cursor get_szc3011_cur is select szc3011_term_code as term_code, szc3011_crse_numb as course_numb, szc3011_seq_numb as section_numb from szc3011; cursor get_biol_0_cur(v_term varchar2, v_pot varchar2, v_course_numb varchar2) is select sfrstcr_pidm, sfrstcr_crn, sfrstcr_grde_code, ssbsect_subj_code, ssbsect_crse_numb, ssbsect_seq_numb, sfrstcr_rsts_code, ssbsect_gradable_ind, spriden_id, spriden_first_name || ' ' || spriden_last_name NAME from sfrstcr, ssbsect, spriden where sfrstcr_term_code = v_term and sfrstcr_ptrm_code = v_pot and sfrstcr_grde_code is null and sfrstcr_term_code = ssbsect_term_code and sfrstcr_crn = ssbsect_crn and sfrstcr_rsts_code in ('RW', 'RE') and sfrstcr_pidm = spriden_pidm and spriden_change_ind is null and (ssbsect_subj_code = 'BIOL' and ssbsect_crse_numb = v_course_numb) and ssbsect_gradable_ind = 'Y'; cursor get_biol_1_cur(v_term varchar2, v_pot varchar2, v_course_numb varchar2, v_section_numb varchar2) is select sfrstcr_pidm, sfrstcr_crn, sfrstcr_grde_code, ssbsect_subj_code, ssbsect_crse_numb, ssbsect_seq_numb, sfrstcr_rsts_code, ssbsect_gradable_ind, spriden_id, spriden_first_name || ' ' || spriden_last_name NAME from sfrstcr, ssbsect, spriden where sfrstcr_term_code = v_term and sfrstcr_ptrm_code = v_pot and sfrstcr_grde_code is null and sfrstcr_term_code = ssbsect_term_code and sfrstcr_crn = ssbsect_crn and sfrstcr_rsts_code in ('RW', 'RE') and sfrstcr_pidm = spriden_pidm and spriden_change_ind is null and (ssbsect_subj_code = 'BIOL' and ssbsect_crse_numb = v_course_numb) and ssbsect_seq_numb = v_section_numb and ssbsect_gradable_ind = 'Y'; --End Biology --variable declaration v_pidm NUMBER; v_crn VARCHAR2(5); -- v_grde_code VARCHAR2(6); v_subj_code VARCHAR2(4); v_crse_numb VARCHAR2(5); v_seq_numb VARCHAR2(3); v_name VARCHAR2(100) := null; v_jobname VARCHAR2(7) := '&1'; v_user VARCHAR2(30) := '&3'; v_id VARCHAR2(9); v_term VARCHAR2(6); -- v_errmsg VARCHAR2(2000); -- LZ 5/16/17 Commented out -- v_errcode NUMBER; -- LZ 5/16/17 Commented out v_cntr NUMBER(8) := 0; v_err VARCHAR2(1) := 'N'; -- v_commit_count NUMBER(8) := 0; v_commit_total NUMBER(8) := 0; v_err_count NUMBER(8) := 0; v_runmode VARCHAR2(1) := 'A'; -- default to Audit mode v_grad_ind VARCHAR2 (1) := null ; v_pot VARCHAR2(3) := null ; v_vet_med VARCHAR2(1) := null; v_spool_commit boolean := true; --LZ 5/15/17 changed to true from false since should --write to .lis file no matter the actual transactions should 'commit' or 'rollback' v_course_numb VARCHAR2(5); v_section_numb VARCHAR2(3); BEGIN DBMS_OUTPUT.ENABLE(null); --LZ 5/16/17 changed to 'null' for unlimited size DBMS_OUTPUT.PUT_LINE('BEGINNING OF SZP3011 AT ' || systimestamp); DBMS_OUTPUT.PUT_LINE(' '); --Get term parm from Banner v_term := Gzpadmn.F_Get_Parm('&1','&2','&4','01'); v_runmode:= Gzpadmn.F_Get_Parm('&1','&2','&4','02'); v_pot := Gzpadmn.F_Get_Parm('&1','&2','&4','03'); v_vet_med := Gzpadmn.F_Get_Parm('&1','&2','&4','04'); --Output parm values to log file 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 NUMBER: 01'||' Term entered: '||v_term); DBMS_OUTPUT.PUT_LINE('PARM NUMBER: 02'||' Runmode (A-Audit, U-Update): '||v_runmode); DBMS_OUTPUT.PUT_LINE('PARM NUMBER: 03'||' Part of Term: '||v_pot); DBMS_OUTPUT.PUT_LINE('PARM NUMBER: 04'||' Include Vet Med (Y or N)? '||v_vet_med); --LZ 5/15/2017 Let user check if he/she enter a correct list of Marine Biology courses in szc3011.csv file DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------'); DBMS_OUTPUT.PUT_LINE('List of Marine Biology Courses: '); for get_szc3011_rec in get_szc3011_cur loop DBMS_OUTPUT.PUT_LINE('Term Code: '|| get_szc3011_rec.term_code ||' Course Number: '|| get_szc3011_rec.course_numb || ' Section Number: '||get_szc3011_rec.section_numb ); end loop; DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------------------'); IF upper(v_runmode) <> 'A' AND upper(v_runmode) <> 'U' THEN gzpadmn.p_spool_lis('&1', &2, '', 'INFO','Invalid Run type: ' || v_runmode, v_spool_commit); ELSE /* --LZ 5/15/17 --should be true always since should write to .lis file no matter the actual transactions should 'commit' or 'rollback' --main processing loop if v_runmode = 'U' then v_spool_commit := false; else v_spool_commit := true; end if; */ gzpadmn.p_spool_lis('&1', &2, '', 'INFO','Starting.... SZP3011 at ' || systimestamp,v_spool_commit); gzpadmn.p_spool_lis('&1', &2, '', 'INFO','Run Mode: ' || v_runmode, v_spool_commit); gzpadmn.p_spool_lis('&1', &2, '', 'INFO', ' Auburn Unversity', v_spool_commit); gzpadmn.p_spool_lis('&1', &2, '', 'INFO', ' Banner Student System', v_spool_commit); gzpadmn.p_spool_lis('&1', &2, '', 'INFO', ' Update null grades to NR', v_spool_commit); gzpadmn.p_spool_lis('&1', &2, '', 'INFO', ' Include Vet Med? ' || v_vet_med, v_spool_commit); gzpadmn.p_spool_lis('&1', &2, '', 'INFO', ' Term: ' || v_term, v_spool_commit); gzpadmn.p_spool_lis('&1', &2, '', 'INFO', ' PGM=SZP3011', v_spool_commit); gzpadmn.p_spool_lis('&1', &2, '', 'INFO', ' ', v_spool_commit); gzpadmn.p_spool_lis('&1', &2, '', 'INFO', ' New', v_spool_commit); gzpadmn.p_spool_lis('&1', &2, '', 'INFO', 'CRN Subject Course_# Section Regs Status Grade ID Name', v_spool_commit); -- Set to IP if v_vet_med = 'Y' then for get_vet_med_ind_rec in get_vet_med_ind_cur(v_term, v_pot) loop v_pidm := get_vet_med_ind_rec.sfrstcr_pidm; v_crn := get_vet_med_ind_rec.sfrstcr_crn; v_subj_code := get_vet_med_ind_rec.ssbsect_subj_code; v_crse_numb := get_vet_med_ind_rec.ssbsect_crse_numb; v_seq_numb := get_vet_med_ind_rec.ssbsect_seq_numb; v_grad_ind := get_vet_med_ind_rec.ssbsect_gradable_ind; v_id := get_vet_med_ind_rec.spriden_id; v_name := get_vet_med_ind_rec.name; update sfrstcr set sfrstcr_grde_code = 'IP', sfrstcr_data_origin = v_jobname, sfrstcr_user = upper(v_user), sfrstcr_activity_date = sysdate where sfrstcr_pidm = v_pidm and sfrstcr_term_code = v_term and sfrstcr_ptrm_code = v_pot and sfrstcr_grde_code is null and sfrstcr_crn = v_crn and sfrstcr_rsts_code in ('RW', 'RE'); gzpadmn.p_spool_lis('&1', &2, '', 'INFO', v_crn || ' ' || v_subj_code || ' ' || v_crse_numb || ' ' || v_seq_numb || ' ' || get_vet_med_ind_rec.sfrstcr_rsts_code || ' ' || ' IP ' || ' ' || v_id || ' ' || v_name, v_spool_commit); end loop; else for get_non_vet_med_ind_rec in get_non_vet_med_ind_cur(v_term, v_pot) loop v_pidm := get_non_vet_med_ind_rec.sfrstcr_pidm; v_crn := get_non_vet_med_ind_rec.sfrstcr_crn; v_subj_code := get_non_vet_med_ind_rec.ssbsect_subj_code; v_crse_numb := get_non_vet_med_ind_rec.ssbsect_crse_numb; v_seq_numb := get_non_vet_med_ind_rec.ssbsect_seq_numb; v_grad_ind := get_non_vet_med_ind_rec.ssbsect_gradable_ind; v_id := get_non_vet_med_ind_rec.spriden_id; v_name := get_non_vet_med_ind_rec.name; update sfrstcr set sfrstcr_grde_code = 'IP', sfrstcr_data_origin = v_jobname, sfrstcr_user = upper(v_user), sfrstcr_activity_date = sysdate where sfrstcr_pidm = v_pidm and sfrstcr_term_code = v_term and sfrstcr_ptrm_code = v_pot and sfrstcr_grde_code is null and sfrstcr_crn = v_crn and sfrstcr_rsts_code in ('RW', 'RE'); gzpadmn.p_spool_lis('&1', &2, '', 'INFO', v_crn || ' ' || v_subj_code || ' ' || v_crse_numb || ' ' || v_seq_numb || ' ' || get_non_vet_med_ind_rec.sfrstcr_rsts_code || ' ' || ' IP ' || ' ' || v_id || ' ' || v_name, v_spool_commit); end loop; end if; -- set other courses to IP for get_mult_pydi_rec in get_mult_pydi_cur(v_term, v_pot) loop v_pidm := get_mult_pydi_rec.sfrstcr_pidm; v_crn := get_mult_pydi_rec.sfrstcr_crn; v_subj_code := get_mult_pydi_rec.ssbsect_subj_code; v_crse_numb := get_mult_pydi_rec.ssbsect_crse_numb; v_seq_numb := get_mult_pydi_rec.ssbsect_seq_numb; v_grad_ind := get_mult_pydi_rec.ssbsect_gradable_ind; v_id := get_mult_pydi_rec.spriden_id; v_name := get_mult_pydi_rec.name; update sfrstcr set sfrstcr_grde_code = 'IP', sfrstcr_data_origin = v_jobname, sfrstcr_user = upper(v_user), sfrstcr_activity_date = sysdate where sfrstcr_pidm = v_pidm and sfrstcr_term_code = v_term and sfrstcr_ptrm_code = v_pot and sfrstcr_grde_code is null and sfrstcr_crn = v_crn and sfrstcr_rsts_code in ('RW', 'RE'); gzpadmn.p_spool_lis('&1', &2, '', 'INFO', v_crn || ' ' || v_subj_code || ' ' || v_crse_numb || ' ' || v_seq_numb || ' ' || get_mult_pydi_rec.sfrstcr_rsts_code || ' ' || ' IP ' || ' ' || v_id || ' ' || v_name, v_spool_commit); end loop; for get_mult_pypp_rec in get_mult_pypp_cur(v_term, v_pot) loop v_pidm := get_mult_pypp_rec.sfrstcr_pidm; v_crn := get_mult_pypp_rec.sfrstcr_crn; v_subj_code := get_mult_pypp_rec.ssbsect_subj_code; v_crse_numb := get_mult_pypp_rec.ssbsect_crse_numb; v_seq_numb := get_mult_pypp_rec.ssbsect_seq_numb; v_grad_ind := get_mult_pypp_rec.ssbsect_gradable_ind; v_id := get_mult_pypp_rec.spriden_id; v_name := get_mult_pypp_rec.name; update sfrstcr set sfrstcr_grde_code = 'IP', sfrstcr_data_origin = v_jobname, sfrstcr_user = upper(v_user), sfrstcr_activity_date = sysdate where sfrstcr_pidm = v_pidm and sfrstcr_term_code = v_term and sfrstcr_ptrm_code = v_pot and sfrstcr_grde_code is null and sfrstcr_crn = v_crn and sfrstcr_rsts_code in ('RW', 'RE'); gzpadmn.p_spool_lis('&1', &2, '', 'INFO', v_crn || ' ' || v_subj_code || ' ' || v_crse_numb || ' ' || v_seq_numb || ' ' || get_mult_pypp_rec.sfrstcr_rsts_code || ' ' || ' IP ' || ' ' || v_id || ' ' || v_name, v_spool_commit); end loop; for get_mult_6_rec in get_mult_6_cur(v_term, v_pot) loop v_pidm := get_mult_6_rec.sfrstcr_pidm; v_crn := get_mult_6_rec.sfrstcr_crn; v_subj_code := get_mult_6_rec.ssbsect_subj_code; v_crse_numb := get_mult_6_rec.ssbsect_crse_numb; v_seq_numb := get_mult_6_rec.ssbsect_seq_numb; v_grad_ind := get_mult_6_rec.ssbsect_gradable_ind; v_id := get_mult_6_rec.spriden_id; v_name := get_mult_6_rec.name; update sfrstcr set sfrstcr_grde_code = 'IP', sfrstcr_data_origin = v_jobname, sfrstcr_user = upper(v_user), sfrstcr_activity_date = sysdate where sfrstcr_pidm = v_pidm and sfrstcr_term_code = v_term and sfrstcr_ptrm_code = v_pot and sfrstcr_grde_code is null and sfrstcr_crn = v_crn and sfrstcr_rsts_code in ('RW', 'RE'); gzpadmn.p_spool_lis('&1', &2, '', 'INFO', v_crn || ' ' || v_subj_code || ' ' || v_crse_numb || ' ' || v_seq_numb || ' ' || get_mult_6_rec.sfrstcr_rsts_code || ' ' || ' IP ' || ' ' || v_id || ' ' || v_name, v_spool_commit); end loop; for get_mult_acct_rec in get_mult_acct_cur(v_term, v_pot) loop v_pidm := get_mult_acct_rec.sfrstcr_pidm; v_crn := get_mult_acct_rec.sfrstcr_crn; v_subj_code := get_mult_acct_rec.ssbsect_subj_code; v_crse_numb := get_mult_acct_rec.ssbsect_crse_numb; v_seq_numb := get_mult_acct_rec.ssbsect_seq_numb; v_grad_ind := get_mult_acct_rec.ssbsect_gradable_ind; v_id := get_mult_acct_rec.spriden_id; v_name := get_mult_acct_rec.name; update sfrstcr set sfrstcr_grde_code = 'IP', sfrstcr_data_origin = v_jobname, sfrstcr_user = upper(v_user), sfrstcr_activity_date = sysdate where sfrstcr_pidm = v_pidm and sfrstcr_term_code = v_term and sfrstcr_ptrm_code = v_pot and sfrstcr_grde_code is null and sfrstcr_crn = v_crn and sfrstcr_rsts_code in ('RW', 'RE'); gzpadmn.p_spool_lis('&1', &2, '', 'INFO', v_crn || ' ' || v_subj_code || ' ' || v_crse_numb || ' ' || v_seq_numb || ' ' || get_mult_acct_rec.sfrstcr_rsts_code || ' ' || ' IP ' || ' ' || v_id || ' ' || v_name, v_spool_commit); end loop; /* --Studev #2690 Exclude students in AVMF courses. for get_mult_avmf_rec in get_mult_avmf_cur(v_term, v_pot) loop v_pidm := get_mult_avmf_rec.sfrstcr_pidm; v_crn := get_mult_avmf_rec.sfrstcr_crn; v_subj_code := get_mult_avmf_rec.ssbsect_subj_code; v_crse_numb := get_mult_avmf_rec.ssbsect_crse_numb; v_seq_numb := get_mult_avmf_rec.ssbsect_seq_numb; v_grad_ind := get_mult_avmf_rec.ssbsect_gradable_ind; v_id := get_mult_avmf_rec.spriden_id; v_name := get_mult_avmf_rec.name; update sfrstcr set sfrstcr_grde_code = 'IP', sfrstcr_data_origin = v_jobname, sfrstcr_user = upper(v_user), sfrstcr_activity_date = sysdate where sfrstcr_pidm = v_pidm and sfrstcr_term_code = v_term and sfrstcr_ptrm_code = v_pot and sfrstcr_grde_code is null and sfrstcr_crn = v_crn and sfrstcr_rsts_code in ('RW', 'RE'); gzpadmn.p_spool_lis('&1', &2, '', 'INFO', v_crn || ' ' || v_subj_code || ' ' || v_crse_numb || ' ' || v_seq_numb || ' ' || get_mult_avmf_rec.sfrstcr_rsts_code || ' ' || ' IP ' || ' ' || v_id || ' ' || v_name, v_spool_commit); end loop; */ --Start Biology -- if substr(v_term, 5, 2)='30' then ---only for summer term LZ 6/20/2017 -- DBMS_OUTPUT.PUT_LINE('This is summer!!!!!!!!!!!!!!!!'); for get_szc3011_rec in get_szc3011_cur loop if get_szc3011_rec.term_code = v_term then -- making sure that parameter term code matches -- term code in the external file by LZ on 6/20/2017 v_course_numb := get_szc3011_rec.course_numb; v_section_numb := get_szc3011_rec.section_numb; if UPPER(v_section_numb) in ('ALL') then for get_biol_0_rec in get_biol_0_cur(v_term, v_pot, v_course_numb) loop v_pidm := get_biol_0_rec.sfrstcr_pidm; v_crn := get_biol_0_rec.sfrstcr_crn; v_subj_code := get_biol_0_rec.ssbsect_subj_code; v_crse_numb := get_biol_0_rec.ssbsect_crse_numb; v_seq_numb := get_biol_0_rec.ssbsect_seq_numb; v_grad_ind := get_biol_0_rec.ssbsect_gradable_ind; v_id := get_biol_0_rec.spriden_id; v_name := get_biol_0_rec.name; update sfrstcr set sfrstcr_grde_code = 'IP', sfrstcr_data_origin = v_jobname, sfrstcr_user = upper(v_user), sfrstcr_activity_date = sysdate where sfrstcr_pidm = v_pidm and sfrstcr_term_code = v_term and sfrstcr_ptrm_code = v_pot and sfrstcr_grde_code is null and sfrstcr_crn = v_crn and sfrstcr_rsts_code in ('RW', 'RE'); gzpadmn.p_spool_lis('&1', &2, '', 'INFO', v_crn || ' ' || v_subj_code || ' ' || v_crse_numb || ' ' || v_seq_numb || ' ' || get_biol_0_rec.sfrstcr_rsts_code || ' ' || ' IP ' || ' ' || v_id || ' ' || v_name, v_spool_commit); end loop; else for get_biol_1_rec in get_biol_1_cur(v_term, v_pot, v_course_numb,v_section_numb) loop v_pidm := get_biol_1_rec.sfrstcr_pidm; v_crn := get_biol_1_rec.sfrstcr_crn; v_subj_code := get_biol_1_rec.ssbsect_subj_code; v_crse_numb := get_biol_1_rec.ssbsect_crse_numb; v_seq_numb := get_biol_1_rec.ssbsect_seq_numb; v_grad_ind := get_biol_1_rec.ssbsect_gradable_ind; v_id := get_biol_1_rec.spriden_id; v_name := get_biol_1_rec.name; update sfrstcr set sfrstcr_grde_code = 'IP', sfrstcr_data_origin = v_jobname, sfrstcr_user = upper(v_user), sfrstcr_activity_date = sysdate where sfrstcr_pidm = v_pidm and sfrstcr_term_code = v_term and sfrstcr_ptrm_code = v_pot and sfrstcr_grde_code is null and sfrstcr_crn = v_crn and sfrstcr_rsts_code in ('RW', 'RE'); gzpadmn.p_spool_lis('&1', &2, '', 'INFO', v_crn || ' ' || v_subj_code || ' ' || v_crse_numb || ' ' || v_seq_numb || ' ' || get_biol_1_rec.sfrstcr_rsts_code || ' ' || ' IP ' || ' ' || v_id || ' ' || v_name, v_spool_commit); end loop; end if; end if; end loop; /* for get_biol_2_rec in get_biol_2_cur(v_term, v_pot) loop v_pidm := get_biol_2_rec.sfrstcr_pidm; v_crn := get_biol_2_rec.sfrstcr_crn; v_subj_code := get_biol_2_rec.ssbsect_subj_code; v_crse_numb := get_biol_2_rec.ssbsect_crse_numb; v_seq_numb := get_biol_2_rec.ssbsect_seq_numb; v_grad_ind := get_biol_2_rec.ssbsect_gradable_ind; v_id := get_biol_2_rec.spriden_id; v_name := get_biol_2_rec.name; update sfrstcr set sfrstcr_grde_code = 'IP', sfrstcr_data_origin = v_jobname, sfrstcr_user = upper(v_user), sfrstcr_activity_date = sysdate where sfrstcr_pidm = v_pidm and sfrstcr_term_code = v_term and sfrstcr_ptrm_code = v_pot and sfrstcr_grde_code is null and sfrstcr_crn = v_crn and sfrstcr_rsts_code in ('RW', 'RE'); gzpadmn.p_spool_lis('&1', &2, '', 'INFO', v_crn || ' ' || v_subj_code || ' ' || v_crse_numb || ' ' || v_seq_numb || ' ' || get_biol_2_rec.sfrstcr_rsts_code || ' ' || ' IP ' || ' ' || v_id || ' ' || v_name, v_spool_commit); end loop; for get_biol_3_rec in get_biol_3_cur(v_term, v_pot) loop v_pidm := get_biol_3_rec.sfrstcr_pidm; v_crn := get_biol_3_rec.sfrstcr_crn; v_subj_code := get_biol_3_rec.ssbsect_subj_code; v_crse_numb := get_biol_3_rec.ssbsect_crse_numb; v_seq_numb := get_biol_3_rec.ssbsect_seq_numb; v_grad_ind := get_biol_3_rec.ssbsect_gradable_ind; v_id := get_biol_3_rec.spriden_id; v_name := get_biol_3_rec.name; update sfrstcr set sfrstcr_grde_code = 'IP', sfrstcr_data_origin = v_jobname, sfrstcr_user = upper(v_user), sfrstcr_activity_date = sysdate where sfrstcr_pidm = v_pidm and sfrstcr_term_code = v_term and sfrstcr_ptrm_code = v_pot and sfrstcr_grde_code is null and sfrstcr_crn = v_crn and sfrstcr_rsts_code in ('RW', 'RE'); gzpadmn.p_spool_lis('&1', &2, '', 'INFO', v_crn || ' ' || v_subj_code || ' ' || v_crse_numb || ' ' || v_seq_numb || ' ' || get_biol_3_rec.sfrstcr_rsts_code || ' ' || ' IP ' || ' ' || v_id || ' ' || v_name, v_spool_commit); end loop; for get_biol_4_rec in get_biol_4_cur(v_term, v_pot) loop v_pidm := get_biol_4_rec.sfrstcr_pidm; v_crn := get_biol_4_rec.sfrstcr_crn; v_subj_code := get_biol_4_rec.ssbsect_subj_code; v_crse_numb := get_biol_4_rec.ssbsect_crse_numb; v_seq_numb := get_biol_4_rec.ssbsect_seq_numb; v_grad_ind := get_biol_4_rec.ssbsect_gradable_ind; v_id := get_biol_4_rec.spriden_id; v_name := get_biol_4_rec.name; update sfrstcr set sfrstcr_grde_code = 'IP', sfrstcr_data_origin = v_jobname, sfrstcr_user = upper(v_user), sfrstcr_activity_date = sysdate where sfrstcr_pidm = v_pidm and sfrstcr_term_code = v_term and sfrstcr_ptrm_code = v_pot and sfrstcr_grde_code is null and sfrstcr_crn = v_crn and sfrstcr_rsts_code in ('RW', 'RE'); gzpadmn.p_spool_lis('&1', &2, '', 'INFO', v_crn || ' ' || v_subj_code || ' ' || v_crse_numb || ' ' || v_seq_numb || ' ' || get_biol_4_rec.sfrstcr_rsts_code || ' ' || ' IP ' || ' ' || v_id || ' ' || v_name, v_spool_commit); end loop; */ --End Biology -- Pull records from sfrstcr_c1 FOR sfrstcr_row IN sfrstcr_c1 (v_term, v_pot) LOOP BEGIN v_pidm := sfrstcr_row.sfrstcr_pidm; v_crn := sfrstcr_row.sfrstcr_crn; v_subj_code := sfrstcr_row.ssbsect_subj_code; v_crse_numb := sfrstcr_row.ssbsect_crse_numb; v_seq_numb := sfrstcr_row.ssbsect_seq_numb; v_grad_ind := sfrstcr_row.ssbsect_gradable_ind; v_id := sfrstcr_row.spriden_id; v_name := sfrstcr_row.name; -- Check errors IF v_err = 'Y' THEN v_err_count := v_err_count + 1; v_err := 'N'; END IF; v_cntr := v_cntr + 1; if v_vet_med = 'Y' then -- Include all courses if sfrstcr_row.sfrstcr_grde_code is null and sfrstcr_row.sfrstcr_rsts_code in ('RW', 'RE') and v_grad_ind = 'Y' then -- Update null sfrstcr_grde_code to NR update sfrstcr set sfrstcr_grde_code = 'NR', sfrstcr_data_origin = v_jobname, sfrstcr_user = upper(v_user), sfrstcr_activity_date = sysdate where sfrstcr_pidm = v_pidm and sfrstcr_term_code = v_term and sfrstcr_ptrm_code = v_pot and sfrstcr_grde_code is null and sfrstcr_crn = v_crn and sfrstcr_rsts_code in ('RW', 'RE'); gzpadmn.p_spool_lis('&1', &2, '', 'INFO', v_crn || ' ' || v_subj_code || ' ' || v_crse_numb || ' ' || v_seq_numb || ' ' || sfrstcr_row.sfrstcr_rsts_code || ' ' || ' NR ' || ' ' || v_id || ' ' || v_name, v_spool_commit); v_commit_total := v_commit_total + 1; end if; elsif v_vet_med = 'N' then -- include courses not in VET_MED only if sfrstcr_row.sfrstcr_grde_code is null and sfrstcr_row.sfrstcr_rsts_code in ('RW', 'RE') and v_grad_ind = 'Y' and v_subj_code not in ('VMED') then -- Update null sfrstcr_grde_code to NR update sfrstcr set sfrstcr_grde_code = 'NR', sfrstcr_data_origin = v_jobname, sfrstcr_user = upper(v_user), sfrstcr_activity_date = sysdate where sfrstcr_pidm = v_pidm and sfrstcr_term_code = v_term and sfrstcr_ptrm_code = v_pot and sfrstcr_grde_code is null and sfrstcr_crn = v_crn and sfrstcr_rsts_code in ('RW', 'RE'); gzpadmn.p_spool_lis('&1', &2, '', 'INFO', v_crn || ' ' || v_subj_code || ' ' || v_crse_numb || ' ' || v_seq_numb || ' ' || sfrstcr_row.sfrstcr_rsts_code || ' ' || ' NR ' || ' ' || v_id || ' ' || v_name, v_spool_commit); v_commit_total := v_commit_total + 1; end if; else DBMS_OUTPUT.PUT_LINE('Invalid parm for Vet_Med ' || v_vet_med); end if; -- error handler EXCEPTION WHEN OTHERS THEN --v_errmsg := SQLERRM; --v_errcode := SQLCODE; DBMS_OUTPUT.PUT_LINE('Error at ' || v_jobname || ' update for PIDM: '|| v_pidm || ' term: ' || v_term || ' and CRN '|| v_crn); DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.format_error_stack); DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_backtrace); v_commit_total := v_commit_total - 1; v_err := 'Y'; END; END LOOP; -- Print the count gzpadmn.p_spool_lis('&1', &2, '', 'INFO', ' ', v_spool_commit); gzpadmn.p_spool_lis('&1', &2, '', 'INFO', 'Total Courses Updated ' || v_commit_total, v_spool_commit); dbms_output.put_line('Record count ' || v_commit_total); if upper(v_runmode) = 'U' then commit; --rollback; gzpadmn.p_spool_lis('&1', &2, '', 'INFO','Commit Occurred. ' || systimestamp, v_spool_commit); else rollback; gzpadmn.p_spool_lis('&1', &2, '', 'INFO','Audit Run Mode Rollback Occurred. ' || systimestamp, v_spool_commit); end if; END IF; dbms_output.put_line('Completed ' || systimestamp); dbms_output.put_line('Completion ERRM:[' || SQLERRM || ']'); dbms_output.put_line('Completion CODE:[' || SQLCODE || ']'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Rolling Back - Unhanlded Error Exception:'); DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack); DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_backtrace); gzpadmn.p_spool_lis('&1', &2, '', 'INFO','Rolling Back - Unhanlded Error Exception: ' || DBMS_UTILITY.format_error_stack || ' ' || systimestamp, v_spool_commit); rollback; END; / EXIT SUCCESS COMMIT;