--============================================================================== -- OBJECT TYPE: PL/SQL -- OBJECT NAME: SZPSSIS.SQL -- AUTHOR: Rodney Clark -- DATE WRITTEN: 10/20/16 -- INPUT: -- GZAMIGR JOB DESC: Set Student Inactive -- DESCRIPTION: Set Student to inactive bassed on codes in SGBSTDN and lack of registration -- REVISION HISTORY -- -- -- --============================================================================== --SET SCAN OFF; set serveroutput ON SIZE UNLIMITED; SET echo OFF; SET verify OFF; SET feedback OFF; SET tab OFF; DECLARE v_term stvterm.stvterm_code%type; v_pidm spriden.spriden_pidm%type; v_line_out varchar2(3000); v_run_mode varchar2(1); f3 varchar2(3) := '","'; v_sysbeg TIMESTAMP; v_sysend TIMESTAMP; v_duration INTERVAL DAY TO SECOND; v_tally number; v_first_term varchar2(6); v_second_term varchar2(6); v_third_term varchar2(6); v_fourth_term varchar2(6); v_set_record varchar2(1) := 'Y'; -- used to see if we set the record at the end when all checks are made v_has_enrollment varchar2(1) := 'N'; -- Will be Y is the student has enrollment in the past 3 terms v_message varchar2(1000); cnter number; learn_ref sb_learner.learner_ref; learn_rec sb_learner.learner_rec; cursor students_with_ae_cur is select * from sgbstdn a where sgbstdn_term_code_eff >= '200910' and sgbstdn_term_code_eff = (select max(sgbstdn_term_code_eff) from sgbstdn b where a.sgbstdn_pidm = b.sgbstdn_pidm /* and a.sgbstdn_levl_code = b.sgbstdn_levl_code*/) and sgbstdn_stst_code not in ('IS','IG'); --check to see if student has enrlooment in the past 4 terms cursor enrollment_exists_cur is select 'Y' from sfrstcr where sfrstcr_pidm = v_pidm and sfrstcr_term_code in (v_first_term, v_second_term, v_third_term, v_fourth_term); cursor get_spriden_cur is select * from spriden where spriden_pidm = v_pidm and spriden_change_ind is null; spriden_record spriden%rowtype; --============================================================================== --============================================================================== -- BEGIN MAIN --============================================================================== BEGIN v_sysbeg := systimestamp; v_term := UPPER(gzpadmn.f_get_parm('&1','&2','&4','01')); v_run_mode := UPPER(gzpadmn.f_get_parm('&1','&2','&4','02')); DBMS_OUTPUT.ENABLE(NULL); DBMS_OUTPUT.PUT_LINE('BEGINNING OF SZPSSIS ' ||TO_CHAR(SYSDATE,'dd-MON-yyyy - hh:mi:ss')); DBMS_OUTPUT.PUT_LINE('Input Parms: '); DBMS_OUTPUT.PUT_LINE('v_term: ' || v_term); DBMS_OUTPUT.PUT_LINE('v_run_mode: ' || v_run_mode); DBMS_OUTPUT.PUT_LINE(' '); v_line_out := '"BANNER ID' || f3 || 'LAST NAME' || f3 || 'FIRST NAME' || f3 || 'PRIOR TERM' || f3 || 'TERM SET' || f3 || 'STUDENT LEVEL' || f3 || 'PROGRAM' || f3 || 'MAJOR' || f3 || 'PROGRAM' || f3 || 'Major' || f3 || 'STST Code"'; -- DBMS_OUTPUT.PUT_LINE(v_line_out); gzpadmn.p_spool_lis('&1','&2',NULL,NULL,v_line_out); v_first_term := v_term; v_second_term := szg8001.f_get_prior_term(v_term, 1); v_third_term := szg8001.f_get_prior_term(v_term, 2); v_fourth_term := szg8001.f_get_prior_term(v_term, 3); for students_with_ae_rec in students_with_ae_cur loop v_message := null; v_set_record:= 'Y'; v_pidm := students_with_ae_rec.sgbstdn_pidm; --check for already being set to IS ir IG if students_with_ae_rec.sgbstdn_stst_code in ('IS','IG') then v_set_record := 'N'; v_message := 'Already set to ' || students_with_ae_rec.sgbstdn_stst_code; else --continue to process the record v_has_enrollment := 'N'; open enrollment_exists_cur; fetch enrollment_exists_cur into v_has_enrollment; close enrollment_exists_cur; if v_has_enrollment = 'Y' then -- has enrollment do not set record v_set_record := 'N'; v_message := 'Not Set because of enrollment'; else --continue to process the record --if the student has a new sgbstdn_record if students_with_ae_rec.sgbstdn_term_code_eff >= v_third_term then v_set_record := 'N'; v_message := 'Not Set because of current sgastdn record (' || students_with_ae_rec.sgbstdn_term_code_eff || ')' ; end if; end if; end if; --check for AE record if students_with_ae_rec.sgbstdn_astd_code = 'AE' then --DBMS_OUTPUT.PUT_LINE(v_pidm || ' in Set because of sgbstdn_astd_code = AE'); v_set_record := 'Y'; v_message := 'Set because of sgbstdn_astd_code = AE'; end if; -- DBMS_OUTPUT.PUT_LINE(v_pidm || ' v_set_record =' || v_set_record); if v_set_record = 'Y' then if v_message is null then v_message := 'Set to IS'; end if; spriden_record := null; open get_spriden_cur; fetch get_spriden_cur into spriden_record; close get_spriden_cur; v_line_out := '"' || spriden_record.spriden_id || f3 || spriden_record.spriden_last_name || f3 || spriden_record.spriden_first_name || f3 || students_with_ae_rec.sgbstdn_term_code_eff || f3 || v_term || f3 || students_with_ae_rec.sgbstdn_levl_code || f3 || students_with_ae_rec.sgbstdn_coll_code_1 || f3 || students_with_ae_rec.sgbstdn_degc_code_1 || f3 || students_with_ae_rec.sgbstdn_program_1 || f3 || students_with_ae_rec.sgbstdn_majr_code_1 || f3 || students_with_ae_rec.sgbstdn_stst_code || f3 || v_message || '"'; if upper(v_run_mode) = 'U' then if students_with_ae_rec.sgbstdn_term_code_eff = v_term then -- if its the same term update sgbstdn set sgbstdn_stst_code = 'IS', sgbstdn_user_id = 'SZPSSIS', sgbstdn_activity_date = sysdate where sgbstdn_pidm = v_pidm and sgbstdn_term_code_eff = v_term; else sb_learner.p_copy_learner( v_pidm, students_with_ae_rec.sgbstdn_term_code_eff, v_term); --cannot use api to update the record because stst_code is one of the required keys update sgbstdn set sgbstdn_stst_code = 'IS' where sgbstdn_pidm = v_pidm and sgbstdn_term_code_eff = v_term; --backfill the record from sorlcur learn_ref := sb_learner.f_query_all(p_pidm => v_pidm); loop Fetch learn_ref into learn_rec; Exit when learn_ref%notfound; -- dbms_output.put_line(bbid || ' Learner : ' || learn_rec.r_term_code_eff ); cnter := cnter + 1; soklcur.p_backload_curr (p_lmod => sb_curriculum_str.f_learner, p_pidm => v_pidm, p_term_code => learn_rec.r_term_code_eff, p_keyseqno => 99); End loop; Close learn_ref; gb_common.p_commit; END IF; end if; gzpadmn.p_spool_lis('&1','&2',NULL,NULL,v_line_out); -- DBMS_OUTPUT.PUT_LINE(v_line_out); v_tally := v_tally + 1; else spriden_record := null; open get_spriden_cur; fetch get_spriden_cur into spriden_record; close get_spriden_cur; v_line_out := '"' || spriden_record.spriden_id || f3 || spriden_record.spriden_last_name || f3 || spriden_record.spriden_first_name || f3 || students_with_ae_rec.sgbstdn_term_code_eff || f3 || v_term || f3 || students_with_ae_rec.sgbstdn_levl_code || f3 || students_with_ae_rec.sgbstdn_coll_code_1 || f3 || students_with_ae_rec.sgbstdn_degc_code_1 || f3 || students_with_ae_rec.sgbstdn_program_1 || f3 || students_with_ae_rec.sgbstdn_majr_code_1 || f3 || students_with_ae_rec.sgbstdn_stst_code || f3 || v_message || '"'; end if; end loop; v_sysend := systimestamp; v_duration := v_sysend - v_sysbeg; --end comments DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE('END OF SZPSSIS ' ||TO_CHAR(SYSDATE,'dd-MON-yyyy - hh:mi:ss')); DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE('TOTAL RECORDS IN REPORT:[' || v_tally || ']'); DBMS_OUTPUT.PUT_LINE('Duration:[' || v_duration || ']'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error found in PIDM: '||v_pidm|| ' ' || SQLERRM); dbms_output.put_line('424:SQLCODE:[' || SQLCODE || ']'); dbms_output.put_line('425:SQLERRM:[' || SQLERRM || ']'); v_line_out := dbms_utility.format_error_backtrace; dbms_output.put_line('425:ERROR BACKTRACE:[' ||v_line_out || ']'); v_line_out := dbms_utility.format_call_stack; dbms_output.put_line('425:ERROR CALL STACK:[' ||v_line_out || ']'); END; / EXIT SUCCESS COMMIT;